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