Re: transactions, functions, foreign keys

My mistake. There was something else going on and I misinterpreted
the cause of the problem. The foreign key references are valid within
the transaction even though the initial updates are not yet committed.

apologies for the distraction and thanks again for your help.

On Wed, 15 Dec 2004 10:33:57 -0700, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Wed, Dec 15, 2004 at 11:53:55AM -0500, Larry White wrote:
>
> > I have a function that calls other functions. These other functions
> > are inserting rows and return the primary key for the inserted row.
> > Some of the tables are related in a way that they have a foreign key
> > reference to a table that was updated in a previous step.
> >
> > Here's an example in psuedocode
> >
> > create function foo() AS '
> > begin
> > select into key1 bar1( a, b);
> > select into key2 bar2,(e, f, key1);
> > etc...
> > end
> > '
> >
> > The call to bar2 uses the key from the call to bar1. The table
> > updated in bar2 has a foreign key constraint referencing the key1
> > column from bar1, but the bar1 transaction hasn't been committed.
> > Thus - a foreign key violation exception. (That's the part I should
> > have seen coming.)
>
> Have you actually seen this problem? I don't know if PostgreSQL's
> visibility rules have changed, but I just tested what you describe
> in 7.4.6 and 8.0.0rc1 and it works. What version of PostgreSQL are
> you using? Could you post a complete example that exhibits the
> problem?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>