Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Added to TODO: o Allow GLOBAL temporary tables to exist as empty by default in all sessions http://archives.postgresql.org/pgsql-hackers/2007-07/msg6.php --- Gregory Stark wrote: Pavel Stehule [EMAIL PROTECTED] writes: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). The solution is to fix the bloat, not add a work-around. The bloat is a direct consequence of performing DDL in the midst of an OLTP transaction. And it's not the only consequence either. Off the top of my head trying to do DDL in an OLTP environment will cause OID inflation, locking issues, catcache problems, unnecessary prepared query replans, and the list goes on, what happens to views defined on the temporary tables? Foreign key references to the temporary tables? You've got it backwards: addressing the artificially imposed requirement to do DDL to create new tables for what should be purely DML operations is fixing the root problem, not a work-around. What would be a work-around is trying to deal with the consequences as they come up. Catalog bloat is one unwanted effect. Second is different behave of temp tables than other mayor rdbms, and uncomfortable work with temp tables in stored procedures. Third argument for implementation of global temp tables is full support of ANSI SQL, I think the ANSI concept of temporary tables which are defined once but give you a fresh empty work-space for each transaction only makes sense if you're thinking in terms of an OLTP environment. Otherwise you would just go ahead and do the DDL to create new tables for each query and not worry about the down-sides. The advantages of the ANSI temporary tables are all things you would worry about in an OLTP environment but not a data warehousing environment: 1) Overhead to perform DDL 2) Replanning overhead 3) Security issues of doing DDL at run-time 4) Difficulty structuring code when multiple procedures need the same temporary tables but the procedures may be called in different orders for different jobs and need different sets of tables. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The solution is to fix the bloat, not add a work-around. The bloat is a direct consequence of performing DDL in the midst of an OLTP transaction. Hardly. It's a consequence of our current implementation of temp tables; that does not necessarily imply that we cannot fix it without an API change. Sure, we could change our regular temporary tables to not create new records in pg_class at all, but I don't think it would make a big difference to DSS users. And I think for OLTP you would still want all the other advantages the standard api gives you. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). The solution is to fix the bloat, not add a work-around. Catalog bloat is one unwanted effect. Second is different behave of temp tables than other mayor rdbms, and uncomfortable work with temp tables in stored procedures. Third argument for implementation of global temp tables is full support of ANSI SQL, Regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Pavel Stehule wrote: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). The solution is to fix the bloat, not add a work-around. Catalog bloat is one unwanted effect. Second is different behave of temp tables than other mayor rdbms, and uncomfortable work with temp tables in stored procedures. Third argument for implementation of global temp tables is full support of ANSI SQL, OK, so the idea of global temp tables is actually implemented in other dbmss. OK. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Pavel Stehule [EMAIL PROTECTED] writes: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). The solution is to fix the bloat, not add a work-around. The bloat is a direct consequence of performing DDL in the midst of an OLTP transaction. And it's not the only consequence either. Off the top of my head trying to do DDL in an OLTP environment will cause OID inflation, locking issues, catcache problems, unnecessary prepared query replans, and the list goes on, what happens to views defined on the temporary tables? Foreign key references to the temporary tables? You've got it backwards: addressing the artificially imposed requirement to do DDL to create new tables for what should be purely DML operations is fixing the root problem, not a work-around. What would be a work-around is trying to deal with the consequences as they come up. Catalog bloat is one unwanted effect. Second is different behave of temp tables than other mayor rdbms, and uncomfortable work with temp tables in stored procedures. Third argument for implementation of global temp tables is full support of ANSI SQL, I think the ANSI concept of temporary tables which are defined once but give you a fresh empty work-space for each transaction only makes sense if you're thinking in terms of an OLTP environment. Otherwise you would just go ahead and do the DDL to create new tables for each query and not worry about the down-sides. The advantages of the ANSI temporary tables are all things you would worry about in an OLTP environment but not a data warehousing environment: 1) Overhead to perform DDL 2) Replanning overhead 3) Security issues of doing DDL at run-time 4) Difficulty structuring code when multiple procedures need the same temporary tables but the procedures may be called in different orders for different jobs and need different sets of tables. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Gregory Stark [EMAIL PROTECTED] writes: 2007/7/4, Bruce Momjian [EMAIL PROTECTED]: The solution is to fix the bloat, not add a work-around. The bloat is a direct consequence of performing DDL in the midst of an OLTP transaction. Hardly. It's a consequence of our current implementation of temp tables; that does not necessarily imply that we cannot fix it without an API change. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Pavel Stehule [EMAIL PROTECTED] writes: Global temp table can be created from template only when is used. It's has not negative efect on app which doesn't use it. The benefit of g.t.t. is simplifycation of stored procedures. And if it's used in 200 txns/s? Imagine the earlier poster who was looking for a way to display the count of records matching a search followed by the ten records on the page without re-executing the search. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Tom Lane [EMAIL PROTECTED] writes: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into this? I would have suggested that when we construct the relcache entry for the table we substitute a local version of refilenode for the global one. None of those sound like hard problems. Certainly it's more invasive this way but the other way is just a hack for complying with the letter of the spec without actually making it work right. It would be silly and in many use cases useless to have regular DML operating on data which has no business being anything but backend-local generate garbage in on-disk catalog tables. I had a strange thought though. The ideal data structure for local pg_statistic data in the unlikely case that users analyze their local tables would in fact be a global temporary table as well. I wonder if we could bootstrap something similar for pg_class as well. Incidentally, for what would imagine relfozenxid would be useful for these tables anyways? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Global temp table can be created from template only when is used. It's has not negative efect on app which doesn't use it. The benefit of g.t.t. is simplifycation of stored procedures. And if it's used in 200 txns/s? Imagine the earlier poster who was looking for a way to display the count of records matching a search followed by the ten records on the page without re-executing the search. I wrote about comparation global temp tables and current temp tables. Counting of result's records is problem. I know. It's incompleteness of current cursor's implementation. Every cursor can be materialised and then can be counted. We need operation OPEN which matarialise cursor and returns real row_count. Regards Pavel ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribi?: I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into this? And what is the use-case for this functionality? What does it give us that we don't already have? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
On Tue, Jul 03, 2007 at 11:49:05AM -0400, Bruce Momjian wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribi?: I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into this? And what is the use-case for this functionality? What does it give us that we don't already have? The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp6y7cHzcrFd.pgp Description: PGP signature
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
The use case is any system that uses temp tables in an OLTP setting, which certainly isn't uncommon. The problem is that today (and as well with a global temp table that is still writing to the catalogs) is that every OLTP operation that creates or drops a temp table is doing DDL. At best, that leads to a lot of catalog bloat. Right now, it appears to also expose some race conditions (we've got a customer that's been bit by this and we've been able to reproduce some odd behavior in the lab). The solution is to fix the bloat, not add a work-around. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
On Jul 1, 2007, at 4:46 PM, Tom Lane wrote: I have question. Is correct implementation of global temp in Oracle or Firebird, where content of glob.temp table is session visible and metadata of g.t.t is persistent? It's correct per spec. Whether it's more useful than what we do is highly debatable --- it forces all sessions to use the same definition of any given temp table name, which is a bit silly for something that's supposed to support session-local data. Would it be possible to support both global and local? I've often thought that having global temp tables would be a really good idea, since it would drastically reduce the need to vacuum catalog tables, but I've never looked into what would be required to do so. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Jim Nasby [EMAIL PROTECTED] writes: I've often thought that having global temp tables would be a really good idea, since it would drastically reduce the need to vacuum catalog tables, I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
I 2007/7/2, Tom Lane [EMAIL PROTECTED]: Jim Nasby [EMAIL PROTECTED] writes: I've often thought that having global temp tables would be a really good idea, since it would drastically reduce the need to vacuum catalog tables, I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. I am working on prototype, and cloning of template entry is propably one possible solution. Every session's clon needs own statistic and then needs own table oid. Nice a day Pavel Stehule ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Tom Lane escribió: Jim Nasby [EMAIL PROTECTED] writes: I've often thought that having global temp tables would be a really good idea, since it would drastically reduce the need to vacuum catalog tables, I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 La victoria es para quien se atreve a estar solo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
On 7/3/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Tom Lane escribió: Jim Nasby [EMAIL PROTECTED] writes: I've often thought that having global temp tables would be a really good idea, since it would drastically reduce the need to vacuum catalog tables, I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. we recently make the path for temp files to be just base/pgsql_tmp or pg_tblspc/tblspc_oid/pgsql_tmp. do we want to complicate things again? while not just a new rekind indicating this is a template and not and actual table. and using that template for creating the actual tables? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Jaime Casanova [EMAIL PROTECTED] writes: while not just a new rekind indicating this is a template and not and actual table. and using that template for creating the actual tables? For precisely the reason stated upthread. That would mean creating and deleting catalog entries for every transaction. Imagine a busy OLTP system running hundreds of transactions per second trying to use a temporary table for intermediate results. Mixing DDL and DML is just as bad an idea behind the scenes as it is for users. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane escribió: I rather doubt that. The most likely implementation would involve cloning a template entry into pg_class. How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into this? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
How about a new relkind which causes the table to be located in PGDATA/base/dboid/pg_temp_backendid/relfilenode So each backend can have its own copy of the table with the same relfilenode; there's no need for extra catalog entries. Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into this? This entries can be teoreticly virtual (in memory). If we have some memory storage we can use it for it. nice a day Pavel Stehule ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
2007/7/3, Gregory Stark [EMAIL PROTECTED]: Jaime Casanova [EMAIL PROTECTED] writes: while not just a new rekind indicating this is a template and not and actual table. and using that template for creating the actual tables? For precisely the reason stated upthread. That would mean creating and deleting catalog entries for every transaction. Imagine a busy OLTP system running hundreds of transactions per second trying to use a temporary table for intermediate results. Mixing DDL and DML is just as bad an idea behind the scenes as it is for users. Global temp table can be created from template only when is used. It's has not negative efect on app which doesn't use it. The benefit of g.t.t. is simplifycation of stored procedures. regards Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Hello if I understand well, there isn't any difference between local and global temp tables in postgresql. I have question. Is correct implementation of global temp in Oracle or Firebird, where content of glob.temp table is session visible and metadata of g.t.t is persistent? Standard is unclean and speak more about local temp tables. The materialization of a temporary table does not persist beyond the end of the SQL-session in which the table was materialized. Temporary tables are effectively empty at the start of an SQL-session.' -- It means so temp table exists on the start of session. What is your opinion about implementation this feature into postgresql (persistent temp tables)? Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
Pavel Stehule [EMAIL PROTECTED] writes: if I understand well, there isn't any difference between local and global temp tables in postgresql. See the archives; some time ago we determined that the correct reading of the spec is that global/local determines visibility of temp tables across modules, but still within a single session. Since we don't have modules there is no difference for us. I have question. Is correct implementation of global temp in Oracle or Firebird, where content of glob.temp table is session visible and metadata of g.t.t is persistent? It's correct per spec. Whether it's more useful than what we do is highly debatable --- it forces all sessions to use the same definition of any given temp table name, which is a bit silly for something that's supposed to support session-local data. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL
I have question. Is correct implementation of global temp in Oracle or Firebird, where content of glob.temp table is session visible and metadata of g.t.t is persistent? It's correct per spec. Whether it's more useful than what we do is highly debatable --- it forces all sessions to use the same definition of any given temp table name, which is a bit silly for something that's supposed to support session-local data. hmm. ALTER OR DROP is really strange. By contrast others op can be relative simple - maybe only change in heap_insert and relationisvisible. Thenk you Pavel Stehule ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
