Re: I'm puzzled by a foreign key constraint problem - Mailing list pgsql-general

On Tue, 2008-11-04 at 07:49 +0000, Richard Huxton wrote:
> Jonathan Guthrie wrote:
> > When I create a project, entries in the project table and the resource
> > table are created in a single function.  Then, separate functions are
> > called to set the owner's access to the new project.  These other
> > functions are failing because of the resourceid foreign key constraint.
>
> Have you turned statement logging on? Your message suggests that's the
> case, but didn't say so explicitly.
>
> Are the two steps:
>   1. Create project, resource
>   2. Set access-rights
> done in separate connections by any chance? If so it's possible (due to
> MVCC) that #2 is still seeing the database as it was before #1 committed.

It's possible, likely even.  We use a connection pool to manage
connections to the database and they're doled out as the system sees
fit.  However, at some point every update has to finish such that any
view of the database will see that update as finished, right?

> > Anyway, I need for these operations to succeed because the lack of
> > permissions causes odd problems in other parts of the system.

> If you want both steps to succeed or fail together though, they need to
> be in the same transaction.

That's what Mr Ringer said, and although I understand that answer and I
understand the reason that two people have independently responded with
it, I'm dissatisfied with it.

There are two reasons why I'm dissatisfied with that answer.  First, my
mandate is basically to create an interface layer for Postgres and then
port the SQL Server stored procedures without changing how they work.
If I change the logic in this part, it will be different not only from
the mechanism used in the original SQL Server stored procedure, but also
different from the logic used in other stored procedures that do similar
things.

The second reason is because adding permissions doesn't just happen at
project creation time.  The software I work on is middleware for the
actual client applications and the client can assign any user
permissions to access the project just as soon as it knows the project's
ID, which is one of the values returned by the project creation
function.  If the issue is a difference in views because the requests
come in on different connections, then there's a time window during
which a valid and unanticipatable request from the client could fail if
the request happens to use a connection to communicate with the database
that is different from the one used to create the project.

Anyway, while I agree that adding the logic to set permissions to the
project create function seems the simplest approach to dealing with the
issue, I'd really rather not change the logic until I've thoroughly
explored all other options.  I CAN guarantee that the other operations
on a project definitely won't begin until the create is committed.  So,
is there any way of causing a commit to not return until all the views
are consistent?