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

On Wed, 2008-11-05 at 04:40 +0900, Craig Ringer wrote:
> The point is that if your initial create and the setting of the initial
> permissions must succeed or fail together, they MUST be done within a
> single transaction. That is, in fact, the fundamental point of database
> transactions.

I understand that.  Honestly, I do.  If I hadn't ever said that odd
things happen when the permissions aren't set, then maybe I could find
out what I'm doing wrong.

> What you should avoid doing is:
>
> TRANSACTION 1                 TRANSACTION 2
>
> BEGIN;
>                               BEGIN;
> SELECT create_it(blah);
>                               SELECT set_permissions(blah, perms);
> COMMIT;
>                               COMMIT;
>
> ... because that just won't work. It sounds like you've got that right,
> but you might be doing this:

> TRANSACTION 1                 TRANSACTION 2
>
> BEGIN;
>                               BEGIN;
>                               SET transaction_isolation = SERIALIZABLE;
>                               -- do something else that triggers
>                               -- freezing of the transaction's snapshot,
>                               -- even something like:
>                               SELECT 1;
> SELECT create_it(blah);
> COMMIT;
>                               SELECT set_permissions(blah, perms);
>                               COMMIT;
>
> ... which will also fail.

The thing is, the C++ code does this

BEGIN transaction 1
INSERT project
COMMIT

BEGIN transaction 2
SET permissions
COMMIT

or, at least, it's supposed to.  Those two operations are not supposed
to overlap at all even if they're on two different connections.  I
thought I had verified this by looking at the log file.  I mean, I can
look at the log file and see things like

2008-11-03 16:29:22 CST DEBUG:  00000: StartTransactionCommand
and
2008-11-03 16:29:22 CST DEBUG:  00000: CommitTransactionCommand

where I would expect them to if what I'm expecting is going on, but the
log file doesn't appear to have enough information to see a transaction
created, proceed, and then end.  That is, how do I know which
transaction was started and which one was committed?

I'm kind of confused by lines like this:

2008-11-03 16:29:22 CST DEBUG:  00000: name: unnamed; blockState:    INPROGRESS; state: INPROGR, xid/subid/cid:
678145/1/4,nestlvl: 1, children: 678146 678147 

Is there an easy explanation somewhere?