Thread: transactions, functions, foreign keys

transactions, functions, foreign keys

From

Larry White

Date:

15 December 2004, 19:54:14

Hi,

I've run into a situation (I should have forseen) and was hoping
someone could show me a way out.

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.)

Is there anyway to cleanly handle this kind of situation?    I'm
working on the initialization piece of a fairly complex database and
there are a large number of these relations to setup.

I'd prefer not to have to call each separately from the command line
because of the possibility of error.  They could also be called
sequentially in a .sql file, but there's no way to pass variables
between them then.

Thanks for your help.

Re: transactions, functions, foreign keys

From

Terry Lee Tucker

Date:

15 December 2004, 20:19:13

I have never tested a particular scenario like this out, but would AFTER
INSERT triggers resolve this issue for you?

On Wednesday 15 December 2004 11:53 am, Larry White saith:
> Hi,
>
> I've run into a situation (I should have forseen) and was hoping
> someone could show me a way out.
>
> 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.)
>
> Is there anyway to cleanly handle this kind of situation?    I'm
> working on the initialization piece of a fairly complex database and
> there are a large number of these relations to setup.
>
> I'd prefer not to have to call each separately from the command line
> because of the possibility of error.  They could also be called
> sequentially in a .sql file, but there's no way to pass variables
> between them then.
>
> Thanks for your help.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: transactions, functions, foreign keys

From

Michael Fuhr

Date:

15 December 2004, 20:35:28

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/

Re: transactions, functions, foreign keys

From

Larry White

Date:

15 December 2004, 21:18:22

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@fuhr.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/
>