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

From Richard Huxton
Subject Re: I'm puzzled by a foreign key constraint problem
Date
Msg-id 490FFE8D.9020606@archonet.com
Whole thread Raw
In response to I'm puzzled by a foreign key constraint problem  (Jonathan Guthrie <jguthrie@brokersys.com>)
Responses Re: I'm puzzled by a foreign key constraint problem
List pgsql-general
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.

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

--
  Richard Huxton
  Archonet Ltd

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: INSERT with RETURNING clause inside SQL function
Next
From: "Thom Brown"
Date:
Subject: Best memory/planner settings for Postgres