Re: Help with transactions - Mailing list pgsql-general

Thanks for the reply.

I've notice a couple things.  I ran a couple test and at first I
couldn't duplicate my error on some test tables.  But I then added
inheritance to one of the tables and thats when I got the error again.
It looks like there is an error when obtaining the seq id (nextval) from
the original table and using it on a table that has a foreign key to the
original table by inserting it into the table that inherits the original
table, within a transaction.  What I think is happening is since the
insert is on the inherited table the foreign key doesn't see the insert
into the original table until the transaction is committed.

Here is a sample of how I duplicated my error.

By running

SELECT insert_data('A123456789','A','A2345');

on the below schema layout I get this error

ERROR:  insert or update on table "table2" violates foreign key
constraint "table2_fk_id_fk"

=====START========
CREATE TABLE table1 (
    id serial NOT NULL,
    data character(10) NOT NULL
);

CREATE TABLE table2 (
    id serial NOT NULL,
    fk_id integer NOT NULL,
    more_data character(5) NOT NULL
);

CREATE TABLE inherit_table (
    even_more_data character(1) NOT NULL
)
INHERITS (table1);

ALTER TABLE ONLY table1
    ADD CONSTRAINT table1_pkey PRIMARY KEY (id);

ALTER TABLE ONLY table2
    ADD CONSTRAINT table2_pkey PRIMARY KEY (id);

ALTER TABLE ONLY table2
    ADD CONSTRAINT table2_fk_id_fk FOREIGN KEY (fk_id) REFERENCES
table1(id) ON UPDATE RESTRICT ON DELETE RESTRICT;

CREATE VIEW view_table1 AS
SELECT table1.id, table1.data
FROM table1;

CREATE VIEW view_table2 AS
SELECT table2.id, table2.fk_id, table2.more_data
FROM table2;

CREATE VIEW view_inherit_table AS
SELECT inherit_table.id, inherit_table.data, inherit_table.even_more_data
FROM inherit_table;

CREATE RULE view_table1_insert AS ON INSERT TO view_table1 DO INSTEAD
INSERT INTO table1 (id, data) VALUES (new.id, new.data);
CREATE RULE view_table2_insert AS ON INSERT TO view_table2 DO INSTEAD
INSERT INTO table2 (id, fk_id, more_data) VALUES (new.id, new.fk_id,
new.more_data);
CREATE RULE view_inherit_table_insert AS ON INSERT TO view_inherit_table
DO INSTEAD INSERT INTO inherit_table (id, data, even_more_data) VALUES
(new.id, new.data, new.even_more_data);

CREATE FUNCTION insert_table2 (integer, character) RETURNS integer
    AS '
DECLARE

       table2_id INTEGER;
       table1_id ALIAS FOR $1;
       newdata ALIAS FOR $2;

BEGIN

       table2_id = nextval(''table2_id_seq'');
       INSERT INTO view_table2 (id, fk_id, more_data) VALUES (table2_id,
table1_id, newdata);

       RETURN table2_id;

END;
'
    LANGUAGE plpgsql SECURITY DEFINER;


CREATE FUNCTION insert_inherit_table (character, character) RETURNS integer
    AS '
DECLARE

       table1_id INTEGER;
       newdata ALIAS FOR $1;
       new_even_more_data ALIAS FOR $2;

BEGIN

       table1_id = nextval(''public.table1_id_seq'');
       INSERT INTO view_inherit_table (id, data, even_more_data) VALUES
(table1_id, newdata, new_even_more_data);

       RETURN table1_id;
END;
'
    LANGUAGE plpgsql SECURITY DEFINER;

CREATE FUNCTION insert_data (character, character, character) RETURNS
boolean
    AS '
DECLARE

       newdata1 ALIAS FOR $1;
       newdata2 ALIAS FOR $2;
       newdata3 ALIAS FOR $3;
       table1_id INTEGER = 0;
       table2_id INTEGER = 0;

BEGIN

       table1_id = insert_inherit_table(newdata1, newdata2 );

       RAISE LOG ''Table1 ID: %'', table1_id;

       table2_id = insert_table2(table1_id, newdata3);

       IF table2_id > 0 THEN
           RETURN TRUE;
       ELSE
           RETURN FALSE;
       END IF;

END;
'
    LANGUAGE plpgsql SECURITY DEFINER;
========END===========

Also, in my original schema I'm getting an increment of 2 every time I
run nextval.  I can't duplicate this yet but I'm looking into it.
Possibly my error somewhere in the function.

Thanks

-------------------------
Stephen Howie




Michael Fuhr wrote:

>On Fri, Mar 18, 2005 at 09:22:52AM -0500, Stephen Howie wrote:
>
>
>
>>I have a java program that excepts print streams and inserts in into a
>>spool table as a bytea.  This fires a pl/pgsql trigger that passes the
>>bytea to a pl/perl function to process the bytea and spits the results
>>as an array back.  It then proceeds to insert the data into multiple
>>tables.  Problem is that two of the tables data is inserted into inside
>>this transaction, one has a foreign key to the other.  As you can guess
>>I get a foreign key violation because the transaction is not committed
>>
>>
>
>A transaction doesn't need to be committed for operations to see
>the effects of previous operations in the same transaction, but
>there could be visibility problems related to what happens when.
>
>Could you post the simplest self-contained example that demonstrates
>the problem?  It'll be easier to understand the interactions if we
>can see the exact code.  In simple tests I successfully did what
>you describe, so apparently my experiment didn't duplicate what
>you're doing.
>
>What version of PostgreSQL are you using?
>
>
>
>>and as far as I understand PostgreSQL does not support dirty reads or
>>nested transactions.
>>
>>
>
>You probably need neither.  PostgreSQL 8.0 supports nested transactions
>in the form of savepoints.
>
>
>
>>1) what is there another way to handle this transaction that would
>>resolve this violation without using dirty reads and
>>
>>
>
>Let's see what you're doing before thinking dirty reads would be a
>solution.  Or perhaps you're thinking about them in a slightly
>different sense than transaction literature usually describes.
>
>
>
>>2) It looks like running the trigger after insert on a table does
>>not run as a separate transaction.
>>
>>
>
>Why would it?
>
>
>
>>Is the insert to that table suppose to fail if the trigger fails?
>>To me that defeats the purpose of having a trigger after insert.
>>
>>
>
>An after trigger is about visibility.  Here's an excerpt from the
>"Triggers" chapter in the documentation:
>
>  Row after triggers are most sensibly used to propagate the updates
>  to other tables, or make consistency checks against other tables.
>  The reason for this division of labor is that an after trigger
>  can be certain it is seeing the final value of the row, while a
>  before trigger cannot; there might be other before triggers firing
>  after it.
>
>An operation ain't over 'til it's over: if an after trigger doesn't
>like what it sees, it can still abort the operation by raising an
>exception.  That doesn't defeat the purpose -- it's part of the
>purpose.
>
>
>