Hi All.
I dug around a bit in the archives to see if anything related to this
has been discussed, but to no avail. If anyone knows where it has
been, please point me in that direction.
I am using PostgreSQL 8.3 and Rose::DB 0.763.
My problem is basically that, I have a table with a Rose::DB::Object
derived class that corresponds to it. I have two triggers on this
table. Not triggers like Rose::DB::Object::Metadata::Column triggers,
but triggers like PostgreSQL triggers.
They do what they're supposed to do when I test them with psql and
the perl DBI. But since I added them, my Rose::DB::Object derived
class blows up. I don't understand how this could happen, or what
difference it makes to Rose::DB::Object whether my database is going
to call a trigger after the insert or not. Or am I missing something?
These are my triggers:
CREATE TRIGGER sync_queue_insert AFTER INSERT ON dropsites FOR EACH
ROW EXECUTE PROCEDURE sync_queue_insert();
CREATE TRIGGER sync_queue_update AFTER UPDATE ON dropsites FOR EACH
ROW EXECUTE PROCEDURE sync_queue_update();
This is a (simplified version of) my Rose::DB::Object class for this
table:
package ORM::Dropsite;
use base 'IFDB::Object';
__PACKAGE__->meta->setup(
table => 'dropsites',
columns => [ qw( ... ) ],
pk_columns => 'id',
relationships => [
...
},
],
);
This is the program that generates the error:
#!/usr/bin/perl
use ORM::Dropsite;
my $obj = ORM::Dropsite->new();
$obj->column('blah');
$obj->column2('blah blah');
$obj->save;
the error is generated this simple test program, with the
sync_queue_inert trigger in place:
DBD::Pg::st execute failed: ERROR: insert or update on table
"queue" violates foreign key constraint "queue_dropsite_id_fkey"
DETAIL: Key (dropsite_id)=(19) is not present in table "dropsites".
CONTEXT: SQL statement "INSERT INTO queue VALUES
( next_queue_position(), $1 , DEFAULT, DEFAULT, now(), DEFAULT )"
PL/pgSQL function "sync_queue_insert" line 2 at SQL statement
insert() - DBD::Pg::st execute failed: ERROR: insert or update on
table "queue" violates foreign key constraint "queue_dropsite_id_fkey"
DETAIL: Key (dropsite_id)=(19) is not present in table "dropsites".
CONTEXT: SQL statement "INSERT INTO queue VALUES
( next_queue_position(), $1 , DEFAULT, DEFAULT, now(), DEFAULT )"
PL/pgSQL function "sync_queue_insert" line 2 at SQL statement
so, it this key constraint is being violated because the dropsite
that was just ->save'd, which would have had an id of 19, was in fact
not inserted. Yet it does increment my dropsites_id_seq, because
overtime i run this the row that "is not present in table
'dropsites'" goes up by one.
Here's what really trips me out. If i delete the trigger:
DROP TRIGGER sync_queue_insert ON dropsites;
and re-run my program... my row shows up!
id | column1 | column2 |
- -----+--------------+-----------+
20 | blah | blah blah |
any thoughts?
Thanks,
Neal
--
public key: http://thrownproject.com/8C02CC33.asc
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
