SEO

Hello all,

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
and as far as I understand PostgreSQL does not support dirty reads or
nested transactions. I have two questions. 1) what is there another
way to handle this transaction that would resolve this violation without
using dirty reads and 2) It looks like running the trigger after insert
on a table does not run as a separate transaction. 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.

Thanks for any help

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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, Mar 21, 2005 at 10:58:27AM -0500, Stephen Howie wrote:

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

Are you aware that foreign keys and inheritance don't play well
together? See the comments at the bottom of the following page:

http://www.postgresql.org/docs/8.0/s...l-inherit.html

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

Have you tested that hypothesis by doing an insert with a foreign
key value that *has* been committed? Reducing the problem to the
simplest possible test case can reveal what's really happening --
the views, rules, and functions might not be relevant to the problem
and they're just adding unnecessary complexity to the investigation.

CREATE TABLE parent (
id integer PRIMARY KEY,
data text NOT NULL
);

CREATE TABLE child (
more_data text NOT NULL
) INHERITS (parent);

CREATE TABLE other (
fkid integer NOT NULL REFERENCES parent
);

BEGIN;
INSERT INTO parent (id, data) VALUES (1, 'foo');
INSERT INTO child (id, data, more_data) VALUES (2, 'bar', 'baz');
COMMIT;

SELECT * FROM parent;
id | data
----+------
1 | foo
2 | bar
(2 rows)

INSERT INTO other (fkid) VALUES (1);
INSERT INTO other (fkid) VALUES (2);
ERROR: insert or update on table "other" violates foreign key constraint "other_fkid_fkey"
DETAIL: Key (fkid)=(2) is not present in table "parent".

I suspect the problem is simply that inheritance has known and
documented limitations with constraints like foreign keys.

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

Could be one of the "gotchas" with using rules. Search the archives
for past discussion.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org