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

On Wed, 2008-11-05 at 12:14 +0900, Craig Ringer wrote:
> Jonathan Guthrie wrote:
>
> > 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.
>
> OK, and we know that if it is doing what it is supposed to, transaction
> 2 /must/ see the changes made by transaction 1. Either it's not doing
> what it's supposed to, or you've hit a mind bogglingly huge bug in
> PostgreSQL that's never been noticed before. I'm inclined to suspect the
> former.

As am I.  In fact, I'm rather counting on it, and that's the reason I
posted my question to pgsql-general rather than bugs.  As an aside, I
prefer any problems I run into to be in my code because I can most
readily fix those.

> Tom Lane pointed out where to go from here re the server logging. It'll
> be interesting to see what turns up once you've got new logs that show
> the backend pid and the involved xid.

I'll be keeping Mr. Lane's message for the logging tips.  For my own
part, since people kept making a strong distinction between the READ
COMMITTED transaction isolation level as opposed to the SERIALIZABLE
transaction level, I concluded that one likely explanation is that the
transaction isolation level was not what I thought it was and put an
explicit "ISOLATION LEVEL READ COMMITTED" in the code that issues the
BEGIN.

The problem has since gone away.  Of course, with a problem that isn't
perfectly understood one must guard carefully against the idea that
you've solved a problem that doesn't recur or that the solution is
certain to be what you thought it was.  I'm thinking that there may have
been some weird interaction between the business logic that's built in
to the executable and the database layer code that lives in a shared
object file.  Perhaps it wasn't the code I changed that did it, but the
fact that I had to recompile to apply the change.

> Sorry about hammering on the point re transaction interleaving and so
> on. You do see people here who've misunderstood how MVCC visibility
> works, and I wasn't sure from your mail that your transactions didn't
> overlap.

There's nothing to be sorry for.  I can now see several deficincies in
my original message that hindered rather than helped the communication.
The only thing I can say in my own defense is that it appears likely
that if I had known what sort of things I needed to be certain of, and
certain to mention, in my initial message, then I wouldn't have had to
write the message in the first place.

In any case, my thanks to all of you for your attention and time.  I am
profoundly grateful for it.