On Tue, Nov 4, 2008 at 11:18 AM, Jonathan Guthrie
<jguthrie@brokersys.com> 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?
Sure. But, if the query to add the permissions is running under an
already active transaction, and you're running in serializable mode,
it can't see the changes because it started before they were
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.
>
> 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.
Mr Ringer was right. So was Jonathan.
> 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.
Even if they're broken?
> 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.
Then they might be broken and need fixing as well. Blindly converting
broken code that just happened to work is not the best way to approach
a project.
> The second reason is because adding permissions doesn't just happen at
> project creation time.
Is there some reason you can call the external permission setting
function from within the function that adds the users? This would
seem the simplest and cleanest solution, since they would then
automatically share a transaction. Other operations could still call
the permissions setting function as they used to.