Re: temp tables

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