pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedobservations about temporary tables and schemas
7+ messages / 5 participants
[nested] [flat]
* observations about temporary tables and schemas
@ 2003-09-16 18:37 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 2 replies; 7+ messages in thread
From: Merlin Moncure @ 2003-09-16 18:37 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Following tests were made in linux server running pg 7.4 beta 2.
I have been playing with temporary tables a little bit and noticed some
interesting things. I'm not sure if this is a part of the standard
canon or not but I thought it worth mentioning. Sorry if I'm bleating
out the obvious!
According to the docs, all temporary tables are local and scoped to the
current backend. AFAICT, this principle can be violated in two ways,
one expected and one not. The first and obvious way is to make a query
vs. pg_class and you can see temporary tables from other users. This is
expected and IMO a useful property of temporary tables.
The other and more interesting way is to manually jump into the
temporary schema (eg. pg_temp_x) that hosts a temp table constructed by
another backend. While this is unlikely to happen in a normal setting,
the server does allow it. Following this, backend X can both see and
manipulate a temporary table set up by backend Y, including table schema
manipulation.
Both backends can insert records into the table but each can only see
the records they inserted. However, changes to table structure (e.g.
alter table) are visible to both backends. Interestingly, if backend X
does a drop column on a temp table, this shows up as ..pg.droppped.1...
in a psql \d on backend Y. Following this, a count(*) from backend Y
counts the records from backend X even though the records are not
visible.
Also, Is the prohibition of using schemas for temp tables a SQL spec
requirement or a technical consideration?
Regards,
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-16 18:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-09-16 18:55 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-hackers@postgresql.org
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> The other and more interesting way is to manually jump into the
> temporary schema (eg. pg_temp_x) that hosts a temp table constructed by
> another backend. While this is unlikely to happen in a normal setting,
> the server does allow it. Following this, backend X can both see and
> manipulate a temporary table set up by backend Y, including table schema
> manipulation.
Not unless backend X is run by a superuser. There are privilege
restrictions against this for ordinary users. The other behaviors you
note are consequences of the current implementation and might change
in future.
> Also, Is the prohibition of using schemas for temp tables a SQL spec
> requirement or a technical consideration?
The SQL spec's notion of temp tables is only tenuously related to ours
in the first place :-(. However, the spec appears to require that
references to temp tables be unqualified names, so AFAICT it's not
expected that applications can reference more than one schema's worth
of temp tables anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-16 23:17 Kris Jurka <books@ejurka.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 1 reply; 7+ messages in thread
From: Kris Jurka @ 2003-09-16 23:17 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-17 14:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kris Jurka <books@ejurka.com>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2003-09-17 14:26 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-hackers@postgresql.org, Rod Taylor <rbt@rbt.ca>; Gavin Sherry <swm@linuxworld.com.au>
Kris Jurka <books@ejurka.com> writes:
> ... 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:
Hmm, yeah. That worked when we put in the temp-vs-permanent check in
foreign key creation, but it doesn't work anymore because temp table
pages are now kept in per-backend local buffers; so there's no guarantee
that another backend can see recent changes to the contents of a temp
table.
I think we have two choices: disallow foreign-key references from temp
tables to permanent tables, or take out the optimization of storing
temp table pages in private memory. (That would leave the whole "local
buffer manager" module as dead code, I think.) I'm kinda leaning
towards the first; does anyone feel that it's a valuable feature to keep?
> After some further investigation this problem can also be generated by two
> temp tables:
That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
simply does an unconditional heap_truncate without bothering to run any
deletion triggers. We could make it apply the same checks TRUNCATE
TABLE does, whereupon you'd get some sort of "can't truncate table"
error when you try to set up a foreign key reference to it. That could
be extended to disallowing the FK reference in the first place, perhaps.
Or we could turn it into a "DELETE FROM temptable", which would be a lot
slower but would "do the right thing". Comments?
BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
referenced by foreign keys, but this is really not a correct/complete
test. What about user-defined deletion triggers? Arguably it should
refuse to truncate if there are any ON DELETE triggers at all.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-17 15:08 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2003-09-17 15:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kris Jurka <books@ejurka.com>; Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-hackers@postgresql.org, Rod Taylor <rbt@rbt.ca>; Gavin Sherry <swm@linuxworld.com.au>
On Wed, 17 Sep 2003, Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
> > ... 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:
>
> I think we have two choices: disallow foreign-key references from temp
> tables to permanent tables, or take out the optimization of storing
> temp table pages in private memory. (That would leave the whole "local
> buffer manager" module as dead code, I think.) I'm kinda leaning
> towards the first; does anyone feel that it's a valuable feature to keep?
I think the first is probably better all in all.
> > After some further investigation this problem can also be generated by two
> > temp tables:
>
> That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
> simply does an unconditional heap_truncate without bothering to run any
> deletion triggers. We could make it apply the same checks TRUNCATE
> TABLE does, whereupon you'd get some sort of "can't truncate table"
> error when you try to set up a foreign key reference to it. That could
> be extended to disallowing the FK reference in the first place, perhaps.
> Or we could turn it into a "DELETE FROM temptable", which would be a lot
> slower but would "do the right thing". Comments?
Since it's documented as doing a truncate, I think disallowing non-self
referential FK constraints is probably a good idea. I'm not sure that
doing all the work on commit to make the table rows delete individually
for the trigger/foreign key case is really worth it.
> BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
> referenced by foreign keys, but this is really not a correct/complete
> test. What about user-defined deletion triggers? Arguably it should
> refuse to truncate if there are any ON DELETE triggers at all.
Oracle doesn't seem to list those as being a problem (it explicitly lists
the foreign key constraint). It looks like they have a separate action
type for truncate so you can make before/after truncate triggers.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-18 12:36 Gaetano Mendola <mendola@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Gaetano Mendola @ 2003-09-18 12:36 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> I think we have two choices: disallow foreign-key references from temp
> tables to permanent tables, or take out the optimization of storing
> temp table pages in private memory. (That would leave the whole "local
> buffer manager" module as dead code, I think.) I'm kinda leaning
> towards the first; does anyone feel that it's a valuable feature to keep?
My vote is for remove this feature.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: observations about temporary tables and schemas
@ 2003-09-19 21:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-09-19 21:10 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Kris Jurka <books@ejurka.com>; Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-hackers@postgresql.org, Rod Taylor <rbt@rbt.ca>; Gavin Sherry <swm@linuxworld.com.au>
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 17 Sep 2003, Tom Lane wrote:
>> I think we have two choices: disallow foreign-key references from temp
>> tables to permanent tables, or take out the optimization of storing
>> temp table pages in private memory.
> I think the first is probably better all in all.
Done.
>> That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
>> simply does an unconditional heap_truncate without bothering to run any
>> deletion triggers. We could make it apply the same checks TRUNCATE
>> TABLE does, whereupon you'd get some sort of "can't truncate table"
>> error when you try to set up a foreign key reference to it. That could
>> be extended to disallowing the FK reference in the first place, perhaps.
>> Or we could turn it into a "DELETE FROM temptable", which would be a lot
>> slower but would "do the right thing". Comments?
> Since it's documented as doing a truncate, I think disallowing non-self
> referential FK constraints is probably a good idea. I'm not sure that
> doing all the work on commit to make the table rows delete individually
> for the trigger/foreign key case is really worth it.
Okay, I have made it do this. It's sort of an indirect thing: when you
try to set up the reference, the end-of-transaction ON COMMIT DELETE
ROWS barfs:
regression=# create temp table t1 (f1 int primary key) on commit delete rows;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
regression=# create temp table t2(f1 int references t1);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "t2" references "t1" via foreign key constraint "$1".
I'm not sure if it's worth trying to improve the error message by
detecting the conflict more directly (ie, checking for ON COMMIT DELETE
ROWS status when creating the FK in the first place).
>> BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
>> referenced by foreign keys, but this is really not a correct/complete
>> test. What about user-defined deletion triggers? Arguably it should
>> refuse to truncate if there are any ON DELETE triggers at all.
> Oracle doesn't seem to list those as being a problem (it explicitly lists
> the foreign key constraint). It looks like they have a separate action
> type for truncate so you can make before/after truncate triggers.
Okay, I just documented the existing behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-09-19 21:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-16 18:37 observations about temporary tables and schemas Merlin Moncure <merlin.moncure@rcsonline.com>
2003-09-16 18:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-16 23:17 ` Kris Jurka <books@ejurka.com>
2003-09-17 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-17 15:08 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-09-19 21:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-18 12:36 ` Gaetano Mendola <mendola@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox