Jonathan Guthrie wrote: > The stored procedures (250 or so of them) have > been converted into Postgres functions using a tool called SQLWays. I'm > using named prepared procedures for each of the Postgres functions that > are called, plus three, "BEGIN", "COMMIT", and "ROLLBACK", which consist > of the named operation. You're talking about prepared statements at the ODBC level, right? Is this with client-side or with server-side prepare? PostgreSQL won't actually let you use these in prepared statements at the SQL level: craig=> PREPARE teststm AS BEGIN; ERROR: syntax error at or near "BEGIN" LINE 1: PREPARE teststm AS BEGIN; Nor can you use them in a function: craig=> CREATE OR REPLACE FUNCTION testfn() RETURNS void AS $$ craig$> BEGIN; craig$> $$ LANGUAGE 'sql'; CREATE FUNCTION craig=> SELECT testfn(); ERROR: BEGIN is not allowed in a SQL function CONTEXT: SQL function "testfn" during startup This makes sense, given that to invoke a function without a containing transaction is impossible; PostgreSQL will implicitly wrap it in a transaction that's committed as soon as the statement is executed. I don't *think* you can use BEGIN etc in prepared statements at the v3 protocol level for the same reasons, but I'm not 100% certain of that. Given those limitations, I'm assuming you're talking about named prepared statements on the client side. > Now, I've turned the logging as far up as it will go, and I seem to have > verified that the first transaction, the one that populates the resource > table, is completed and committed before any calls are made to the > function that sets the permissions value. What transaction isolation level are you using? If you're on the READ COMMITTED level, then yes all you should need is for the transaction that creates the records of interest to commit before another transaction (even one that was already running) can see the values. In any case, I'm a little puzzled as to why you're not doing the creation of the initial records and the related permissions records etc all in the one transaction. > Anyway, I need for these operations to succeed because the lack of > permissions causes odd problems in other parts of the system. That really shows that you need to do it all in one transaction, then. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
