Thread: Connection specific information - Temporary table used in Sybase to store information
Connection specific information - Temporary table used in Sybase to store information
From
pgsql.waldvogel@mac.com
Date:
Hello everybody, Since I am new to PostgreSQL mailing lists, I am not sure which mailing list to use for my problem, I issue it to this group.If there is a more appropriate list, please let me know and I will happily post there. The situation: While porting an existing application from Sybase ASA to PostgreSQL 7.4 I need to access connection specific login data (userdata). In the Sybase solution I used a temporary table (CREATE TEMPORARY TABLE). Each connection than had the tableon connect; each client could then perform the INSERT statement without creating table. PostgreSQL differs from thebehaviour as documented. A lot of (very) different clients rely upon this this standard behaviour. The question: Is there any feature/technique that a) can create temporary tables as in the SQL Standard defined b) something like "ON CONNECT DO BEGIN .... END"? So that the temporary table can be automatically created upon connect? Thanks in advance, Kind regards, Ralf
pgsql.waldvogel@mac.com wrote: > The situation: While porting an existing application from Sybase ASA > to PostgreSQL 7.4 I need to access connection specific login data > (user data). In the Sybase solution I used a temporary table (CREATE > TEMPORARY TABLE). Each connection than had the table on connect; each > client could then perform the INSERT statement without creating > table. PostgreSQL differs from the behaviour as documented. A lot of > (very) different clients rely upon this this standard behaviour. > > The question: Is there any feature/technique that a) can create > temporary tables as in the SQL Standard defined CREATE TEMPORARY TABLE, as per the SQL spec probably. PostgreSQL is pretty standards compliant, I'd be surprised if it isn't in this respect. You seem to want a temporary table that is persistent across transactions. Isn't that a violation of the SQL spec instead? > b) something like "ON CONNECT DO BEGIN .... END"? So that the > temporary table can be automatically created upon connect? No, but why do you use a _temporary_ table? If you'd just use a normal table and never commit your inserts, data will only be visible to the current transaction. It would be a good idea to delete those records afterwards, though. This has the drawback that the same counts for all inserts and updates, but you'd be safe if you delete the user data record before committing everything. If you need finer grained transaction handling than that, I'd look at PostgreSQL 8, which has savepoints. I suppose the login information is also available by other means, in that case you might want to create a view around the appropriate functionality instead of your temporary table trick. Just a few ideas... -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World//
Alban Hertroys <alban@magproductions.nl> writes: > pgsql.waldvogel@mac.com wrote: >> The question: Is there any feature/technique that a) can create >> temporary tables as in the SQL Standard defined > CREATE TEMPORARY TABLE, as per the SQL spec probably. PostgreSQL is > pretty standards compliant, I'd be surprised if it isn't in this respect. No, the spec actually does say that temp tables should work that way. See the "compatibility" section of our CREATE TABLE reference page: 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. regards, tom lane