Subject Re: [Firebird-Architect] Global Temporary Tables
Author Vlad Horsun
> > 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
> 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?

This limit came from SQL standard, Ann wrote about it in this list.

From implementation point of view references from GTT to permanent tables also
not good. Referential integrity between persistent table (master) and all instances
of GTT (detail) hard to check in SS and near impossible in CS since different
attachment "live" in different processes. I don't know how to check detail records
when master record is deleted and instance of detail GTT live in another process.

> 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?

Yes, i mean all kind of constraints.

> > 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?

Page space is internal concept. It has no interface in public API. Later
we can implement something like "table space" in ORACLE on the top of
page space but not now.

About blobs

ibase.h :

#define isc_bpb_storage 7

#define isc_bpb_storage_main 0
#define isc_bpb_storage_temp 2

To create blob in main ("base") storage :

isc_create_blob2(status, &db_handle, &trans, &blob_handle, &blob_id, 0, NULL);


char bpb[] = {isc_bpb_version1, isc_bpb_storage, isc_bpb_storage_main};
isc_create_blob2(status, &db_handle, &trans, &blob_handle, &blob_id, sizeof(bpb), bpb);

i.e. by default blobs are initially stored in "base" page space

To create blob in temporary storage

char bpb[] = {isc_bpb_version1, isc_bpb_storage, isc_bpb_storage_temp};
isc_create_blob2(status, &db_handle, &trans, &blob_handle, &blob_id, sizeof(bpb), bpb);