Re: [HACKERS] observations about temporary tables and schemas
Stephan Szabo <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] observations about temporary tables and schemas
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] observations about temporary tables and schemas
On Wed, 17 Sep 2003, Tom Lane wrote: > Kris Jurka <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] observations about temporary tables and schemas
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? I would hate to lose 'memory optimized' temp tables unless the arguments for were compelling and there was no other solution. > 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? This seems correct from one point of view, but what happens when a delete trigger fails? Instead of 'DELETE FROM', what about 'DROP...CASCADE'? This seems be consistent with other commands. For example, when a temp table has domain x and another backend does DROM DOMAIN...CASCADE on x, x is dropped from the temp table, which 'feels right'. If the temp table fails to drop for some reason, the memory could be bulldozed like it is now. BTW, domains are another way to bypass the 'isolation principle' for temp tables. Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] observations about temporary tables and schemas
Kris Jurka <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] observations about temporary tables and schemas
On Tue, 16 Sep 2003, Kris Jurka wrote: > 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: I had no idea this was even possible. IHRTS (I haven't read the spec), but again here temp tables seem to break rules in strange and unexpected ways. From the docs, we know that temp tables are 'dropped' after the connection is broken. With RI in mind, (and more generally, triggers) the nature and execution of that drop is important. All that being said, using RI in temp tables (especially with regular tables) is asking for trouble. The reason I was hoping to keep them in a special schema is that I use them as a kind of a database enforced mutex object using the name of the table as the mutex identifier. By holding them in a special schema (e.g. locks) that is not in the schema search path I can bypass name resolution problems that happened when the 'mutex' id is the same name as another table (actually, there are no problems, it simply isn't allowed!). Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] observations about temporary tables and schemas
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > ... In this case, I think the > SQL spec was not thought out very well. Many people think that about a lot of aspects of the spec ;-) > I did not see a TODO item regarding global temporary tables...has this > been attempted/done? We concluded in the last discussion of this subject that our existing temp table behavior actually approaches the spec's GLOBAL TEMP behavior more nearly than it does LOCAL TEMP; but in any case, without modules the difference is moot. Accordingly, 7.4 will accept either syntax, and there is no more TODO. See the archives from a few months back, or http://developer.postgresql.org/docs/postgres/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY which distills that discussion. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] observations about temporary tables and schemas
"Tom Lane" writes: >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. ISTM that if use of qualified names is disallowed you shouldn't be allowed to create a temporary table with the same name as an existing table. Otherwise you have an unclear (or arbitrary) definition of which table is used in an unqualified reference. In this case, I think the SQL spec was not thought out very well. In my way of thinking, temporary tables should behave just like normal tables wrt name resolution. Also, it's worth noting that qualified names for temp tables are an allowed syntax except during the create table statement (although in normal circumstances you would never know the namespace). Because of this, qualified name restriction appears to feel like an arbitrary exclusion. I did not see a TODO item regarding global temporary tables...has this been attempted/done? Regards, Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] observations about temporary tables and schemas
"Merlin Moncure" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] observations about temporary tables and schemas
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
