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.
begin:vcard fn:Stephen Howie n:Howie;Stephen email;internet:[EMAIL PROTECTED] tel;work:260-760-5910 tel;fax:260-436-9472 tel;cell:260-704-6262 x-mozilla-html:FALSE version:2.1 end:vcard
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
