Re: transactions, functions, foreign keys

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(at)esc1(dot)com