Re: transactions, functions, foreign keys

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/