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

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> 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

> > 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

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 jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby <decibel(at)decibel(dot)org> 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

I

2007/7/2, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Jim Nasby <decibel(at)decibel(dot)org> 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

Tom Lane escribió:
> Jim Nasby <decibel(at)decibel(dot)org> 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"

On 7/3/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Tom Lane escribió:
> > Jim Nasby <decibel(at)decibel(dot)org> 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

"Jaime Casanova" <systemguards(at)gmail(dot)com> 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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

2007/7/3, Gregory Stark <stark(at)enterprisedb(dot)com>:
>
> "Jaime Casanova" <systemguards(at)gmail(dot)com> 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

>
> > 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

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> 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

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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

> > 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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tue, Jul 03, 2007 at 11:49:05AM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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 decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

> 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
> > 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

Pavel Stehule wrote:
> 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
> > > 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:

> 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
>> > 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

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
> 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

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>>> 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
>> 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

On Wed, 2007-07-04 at 22:27 +0100, Gregory Stark wrote:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:

> > 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:

IIRC there were similar problems with temp table usage at many DW sites
using Teradata. The issue was about locking, specifically the access
rights required. We might have that problem, or not, but the issues
related to significant numbers of temp tables effect many types of
application., not just OLTP.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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/msg00006.php

---------------------------------------------------------------------------

Gregory Stark wrote:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>
> > 2007/7/4, Bruce Momjian <bruce(at)momjian(dot)us>:
> >> > 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +