Re: observations about temporary tables and schemas

On Tue, 16 Sep 2003, Merlin Moncure wrote:

> I have been playing with temporary tables a little bit and noticed some
> interesting things.

Something else I've noticed about temp tables is that you are prohibited
from having a permanent table contain a foreign key reference to a temp
table, but you are allowed to reference a permanent table from a temp
table. The triggers don't work correctly when the table is
modified by another backend:

Backend 1:
CREATE TABLE t1(a int PRIMARY KEY);
CREATE TEMP TABLE t2(a int REFERENCES t1 ON DELETE CASCADE);

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

Backend 2:
DELETE FROM t1;

Backend 1:
SELECT * FROM t2 WHERE a NOT IN (SELECT a FROM t1);

After some further investigation this problem can also be generated by two
temp tables:

BEGIN;
CREATE TEMP TABLE t3 (a int PRIMARY KEY) ON COMMIT DELETE ROWS;
CREATE TEMP TABLE t4 (a int REFERENCES t3 ON DELETE CASCADE);
INSERT INTO t3 VALUES(1);
INSERT INTO t4 VALUES(1);
COMMIT;
SELECT * FROM t4 WHERE a NOT IN (SELECT a FROM t3);

Kris Jurka