On Tue, 2002-09-10 at 21:44, Curt Sampson wrote: > But there were some issues with rolling back and SET commands, > weren't there? I remember a long discussion about this that I'm > not sure I want to go back to. :-)
So.. Unless explicitly requested, a SET command should have immediate effect? The other constrictive value I can think of is search_path. -- Must be transaction safe BEGIN; CREATE SCHEMA <newschema>; SET search_path = <newschema>; ROLLBACK; CREATE TABLE... -- This should be ok BEGIN; SET autocommit = on; INSERT ... COMMIT; -- SET takes place on commit, as it was an explicit transaction -- This is requested behavior SET autocommit = off; SET autocommit = on; INSERT... -- immediate effect, since autocommit is on -- This gets interesting be ok as the schema must exist SET autocommit = off; CREATE SCHEMA <newschema>; SET search_path = <newschema>; -- implicit commit here? ROLLBACK; CREATE TABLE ... -- search_path must roll back or schema must have been created -- Similar to the above SET autocommit = off; CREATE TABLE ... SET autocommit = on; -- implicit commit here? ROLLBACK; -- Does this rollback anything? -- Was CREATE TABLE committed with the second SET statement? > Well, I'm not going to go chase it down right now, but ISTR that > DECLAREing a cursor just allocates a variable name or the storage for it > or something like that; it doesn't actually create an active cursor. Indeed, this is how the cursor is able to cross transactions. It is closed at transaction commit, and re-created in next use. 4.29: For every <declare cursor> in an SQL-client module, a cursor is effectively created when an SQLtransaction (see Subclause 4.32, SQL-transactions ) referencing the SQL-client module is initiated. -- Rod Taylor ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org