Re: temp tables - Mailing list pgsql-novice

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@postgresql.org
> [mailto: pgsql-novice-owner@postgresql.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@postgresql.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@postgresql.org so that your
      message can get through to the mailing list cleanly

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