"Vlad Horsun" <hvlad@...> wrote:

>
> During last two months i did some research and implementation of
> global temporary tables. Here i want to show what i has done and listen
> your opinions and critics.

Just wanted to add that Vlad's work is approved by myself, mostly reflects
the Ann's proposal of GTTs, implements the separate page storage mechanism
we seemed to agree on, and is expected to become a low-level basis for
session scope temporary tables / transient datasets requested by the sponsor
(to be discussed later separately).

> GTT's can have indexes, triggers, field and table constraints as permanent
> tables. All constraints between any kind of permanent and temporary tables
> follow the rule below:
>
> a) references between permanent and temporary tables are forbidden
> b) GTT with ON COMMIT PRESERVE ROWS can't reference on GTT
> with ON COMMIT DELETE ROWS

IMO, these two rules are not consistent with each other. If a DELETE ROWS
table can reference a PRESERVE ROWS one, then why both cannot reference any
of persistent tables?

Vlad, I also expect that a "reference" term used here is not limited to FK
constraints, but covers also CHECK constraints, triggers, computed columns,
etc. Is it correct?

> Domain constraints also can't reference on GTT.

Agreed.

> At blob creation time engine not know in which relation (permanent or
> temporary) this blob will be attached when materialized. Therefore when

blob

> will be materialized may arise necessity to move it from initial ("base")

page

> space into page space of the appropriate relation. To avoid such waste of
> time i propose extension of blob parameter block (used when blob created).
> This extension allows to set initial page space in which blob pages will

be

> allocated before materialization. No public API change is necessary, only
> few new constants.

What is a "page space" at the API level? What value should I provide to use
it in BDB? How should I become aware of it?

> When user create new index already existing GTT's instances will not
> know about it (and don't build it of course). Only new instances will read

> new index definition from "base" index root page and build it.

Agreed.

> Current implementation allows to have follow kinds of storage for
> temporary page spaces:
>
> 1. No temporary page spaces - all inside database
> 2. Page space per attachment
> 3. Page space per engine process (one common page space for SS or
> separate page space per CS process)
>
> Now it is hardcoded but can easy be moved into configuration file.

I'd stick with hardcoded option (2), but this is just me.


Dmitry