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

I've been tearing my hair out over this one issue and I'm hoping that
someone on this list will have an insight on the matter that will shed
some light on why the system is doing what it's doing.

I have a database with a number of tables, two of which are projects and
resources.  We also have a users table and tables for files and folders
and such.  Files, folders, users, and so forth are considered resources
and their identifiers (UUID's) are listed in the resources table.
Entries in the resources table are used to do things like attach
metadata to resources and to set permissions to allow users other than
the owner to access specified resources.  Because permissions are
associated with resources, there is a foreign key constraint between the
resourceid field in the permissions table and the resourceid column of
the resource table, which is the primary key of the resource table.

The system itself is written in C++ and database wrappers have been
created which work with the original database (MS SQL Server) and we are
porting to Postgres.  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.  Those three are necessary because we're
returning data in cursors, so each time one of those functions is
executed it's done inside a transaction and a status code is returned
which determines whether the transaction is committed or rolled back.

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.

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.  (It's called five times and
the all fail.)  The logging generated by the foreign key constraint
failure includes the UUID for the resource and I can go back in later
and do a query for that record in the resource table and that query
successfully returns a single row.  Also, I can go in at a later time
and execute the function that sets the permissions and it works.

To me, this implies that it's a race condition.  When I first ran into
this problem last week I set the foreign key check to deferred and it
seemed to stop complaining although I don't remember what steps (if any)
I did to test that conclusion.  In any case, it happens with the check
set to deferred, so that didn't fix the problem.  I don't think it
should have had an effect anyway.  I spent the afternoon reading
documentation about constraints and keys and various options associated
with them

Anyway, I need for these operations to succeed because the lack of
permissions causes odd problems in other parts of the system.  I don't
even know where to begin looking for the problem or what magic might be
useful at allowing the system to work as intended.  I'm sure I'm doing
something wrong, but I don't know what.  Does this problem ring a bell
with anyone?  Is there any information that isn't clear from my
description or which I could gather which would be helpful?



!DSPAM:1544,490fb75c40305259311678!