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? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
