Re: I'm puzzled by a foreign key constraint problem

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