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

Jonathan Guthrie wrote:
> 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?

You'll need to read the section of the manuals regarding transaction
isolation and how it impacts MVCC for full details, but the short answer
is "no". A pre-existing transaction might well see the database as it
was when its snapshot was first taken. More likely to happen if you have
a connection pool that issues BEGINs too early...

>>> 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 logic is wrong regardless of whether you use PostgreSQL, SQL Server,
Oracle or any other DB though. If you want a guarantee that both actions
succeed or fail together you'll need to wrap them in a transaction. What
you're saying is that at the moment there is no such guarantee with SQL
Server as your database, it just happens to work most (e.g. 99.99%) of
the time.

> 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.

This is separate from the issue of both actions succeeding or failing.

> 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?

It doesn't. They are. But I think your second connection is fixed to an
older snapshot.  Set aside an hour, read through the concurrency control
/ transaction-isolation section of the manuals and experiment with two
psql screens open at the same time until you're clear how it all works.
It'll probably take 5 mins to find the problem then (in consunction with
statment logging turned on at the server side).

I might be wrong about the cause, but since (1) foreign-keys work in PG,
(2) you seem to know what you're doing, I'm guessing it's a combination
of the subtleties of mvcc and your connection-pool interacting.

--
  Richard Huxton
  Archonet Ltd