Diez B. Roggisch wrote:
>> Within the boundaries of my understanding, this recipe should work...
>> the PRESERVE ROWS option leaves the table for the whole session,
>> otherwise it would get destroied a microsecond later with the first commit.
>>
>
> I doubt it works. How do you ensure that you get the proper user when
> several
> users access your site at once, creating/overriding rows in that temporary
> table of yours?
>
Overriding rows should not be possible at all.
Temporary tables are created in a schema that is named after the session
(something like pg_temp_{session number}).
If a web request is served by a thread, that keeps hold of the db
session until it finishes processing the request, nobody else can use
the table in the meanwhile.
After that, another request comes in and the table is cleared again
inside sa_rwt().
>From the manual:
TEMPORARY or TEMP
If specified, the table is created as a temporary table. Temporary
tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction (see ON COMMIT
below). Existing permanent tables with the same name are not visible
to the current session while the temporary table exists, unless they
are referenced with schema-qualified names. Any indexes created on a
temporary table are automatically temporary as well.
[...]
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.
> What _could_ work is to enhance that table with a session-id column, and
> somehow obtain a session id from the current connection inside the database.
>
If that is more secure thread-wise and standard-wise I'll do so...
altough it would need more careful housekeeping (i.e. turbogears
crashes, another program comes in and the session numbers are recycled..)
Thank you
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
