Skip site navigation (1) Skip section navigation (2)

Re: temp tables

From: Bob <luckyratfoot(at)gmail(dot)com>
To: "Schuhmacher, Bret" <Bret(dot)Schuhmacher(at)aspect(dot)com>
Cc: John DeSoi <desoi(at)pgedit(dot)com>,Cima <ruel(dot)cima(at)facinf(dot)uho(dot)edu(dot)cu>,PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: temp tables
Date: 2005-04-28 14:55:24
Message-ID: 762e5c05042807554bbd4526@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 4/28/05, Schuhmacher, Bret <Bret(dot)Schuhmacher(at)aspect(dot)com> wrote:
> 
> Is this safe? What happens if multiple users hit the PHP page nearly 
> simultaneously (or at least just after someone else) and the temp table 
> exists? I avoided the temp table approach in an app for exactly this 
> reason... What does PG do in this case? Do the temp tables belong to the 
> session/connection and they're insulated from each other?
> 
> Thanks,
> 
> Bret
> 
> 
> > -----Original Message-----
> > From: pgsql-novice-owner(at)postgresql(dot)org
> > [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of John DeSoi
> > Sent: Thursday, April 28, 2005 8:18 AM
> > To: Cima
> > Cc: PostgreSQL Novice
> > Subject: Re: [NOVICE] temp tables
> >
> >
> > On Apr 28, 2005, at 12:45 AM, Cima wrote:
> >
> > >
> > > im working with php 4 and postgresql 8 and in my php script
> > id like to
> > > create a temp table on my database server. how do i do
> > this? how do i
> > > verify it was created?
> > > 
> > > i tried the following:
> > > 
> > > $sql = "create temp table s_info(a int, b text) on commit
> > delete rows
> > > ";
> > > 
> > > pg_query($dbh,$sql);
> > > 
> > > 
> > > $dbh is my connection.
> > > 
> > > any help will be highly appreciated.
> >
> >
> > You can verify it was created by checking the result from
> > pg_query and looking at pg_last_error. The table is
> > automatically dropped at the end of the connection, most
> > likely when your PHP script ends.
> >
> >
> > John DeSoi, Ph.D.
> > http://pgedit.com/
> > Power Tools for PostgreSQL
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so
> > that your
> > message can get through to the mailing list cleanly
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
> 
See http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY


From the docs
"
Compatibility 

The CREATE TABLE command conforms to SQL-92 and to a subset of SQL:1999, 
with exceptions listed below. 
Temporary Tables

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL 
standard, the effect is not the same. In the standard, temporary tables are 
defined just once and automatically exist (starting with empty contents) in 
every session that needs them. PostgreSQL instead requires each session to 
issue its own CREATE TEMPORARY TABLE command for each temporary table to be 
used. This allows different sessions to use the same temporary table name 
for different purposes, whereas the standard's approach constrains all 
instances of a given temporary table name to have the same table structure. 

The standard's definition of the behavior of temporary tables is widely 
ignored. PostgreSQL's behavior on this point is similar to that of several 
other SQL databases. 

The standard's distinction between global and local temporary tables is not 
in PostgreSQL, since that distinction depends on the concept of modules, 
which PostgreSQL does not have. For compatibility's sake, PostgreSQL will 
accept the GLOBAL and LOCAL keywords in a temporary table declaration, but 
they have no effect. 

The ON COMMIT clause for temporary tables also resembles the SQL standard, 
but has some differences. If the ON COMMIT clause is omitted, SQL specifies 
that the default behavior is ON COMMIT DELETE ROWS. However, the default 
behavior in PostgreSQL is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option 
does not exist in SQL."

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2005-04-28 16:12:33
Subject: Re: updating my pg-drivers crashed my system...
Previous:From: Marc MaminDate: 2005-04-28 14:53:47
Subject: connect from perl: error in Carp.pm

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group