Dear John,
Thank you for the prompt reply. I am not entirely sure I understood
it, and I probably didn't give the right details when I mailed the
list earlier today. If I change my id column from serial to integer
it will not, to put it in mysql terms, auto_increment. Also, I do not
understand why Rose::DB goes to the trouble of getting tangled up in
the serial sequence when a value 'DEFAULT' for the id to the
statement handler would do the same thing. I'm not sure how my stored
procedure would account for the fact that Rose::DB is retrieving the
nextval of my serial sequence; I'd rather expect that _something_
would, but as I just mentioned, I am confused why Rose::DB is doing
that instead of letting that fall on the DB side.
At any rate, I have either found a bug in Rose::DB using triggers in
PostgreSQL, or I am doing something totally wrong.
The problem I am having occurs when I use domains to switch between
my production and development schema in the Rose::DB derived class. I
have a production schema (as 'public', so in PostgreSQL I may
effectively ignore the fact that I am using a schema), and a
development schema (called 'development'). I'm setting an environment
variable in %ENV to switch between the two. Everything with the
triggers works fine under the default instantiation of my Rose::DB
derived class (i.e. the production stuff is working fine), but the
error occurs when I switch domains (in the register_db sense) to the
development schema.
I have created a stripped down example that include the bare minimum
to recreate my issue. I would appreciate it if you would peruse the
code. I spent a bit of time writing it out (I may not reveal the
details of my project to a public, archived mailing list). I do not
want to flood the list, so I have made the code available at the
following URL:
http://thrownproject.org/rose_db_test/
Basically, in this example I have two tables, 'table_with_trigger'
and 'table_without_trigger'. table_with_trigger has an AFTER INSERT
trigger to insert a subset of a newly created table_with_trigger row
into table_without_trigger. Both tables are defined in the public and
development schema, and the trigger is defined in both as well.
Below I am pasting the file from the URL I gave above, 'output.txt',
which shows my issue in broad strokes. The rest of it you will find
at the URL. I thank you for your time. I love Rose::DB and I want to
fix this issue without using some weird hackery, which is why I am
writing for your advice.
Thank you,
Neal Clark
output.txt:
====================
| 1. in unix shell |
====================
[EMAIL PROTECTED] psql -f test.sql
(copious output, schema is fine.)
[EMAIL PROTECTED] ./test.pl
[EMAIL PROTECTED] ./test.pl --use-dbi-instead-of-rose-db
(they both work)
==============================
| 2. in psql (public schema) |
==============================
nclark=# SELECT * FROM table_with_trigger;
id | date_added | column1 | column2
----+----------------------------+--------------+--------------
1 | 2007-06-06 23:03:48.392235 | column1 test | column2 test
2 | 2007-06-06 23:03:55.466877 | column1 test | column2 test
(2 rows)
nclark=# SELECT * FROM table_without_trigger;
id | date_added | column1
----+----------------------------+--------------
1 | 2007-06-06 23:03:48.392235 | column1 test
2 | 2007-06-06 23:03:55.466877 | column1 test
(2 rows)
====================
| 3. in unix shell |
====================
[EMAIL PROTECTED] ./test.pl --dev
DBD::Pg::st execute failed: ERROR: duplicate key violates unique
constraint "table_without_trigger_pkey"
CONTEXT: SQL statement "INSERT INTO table_without_trigger VALUES
( $1 , $2 , $3 )"
PL/pgSQL function "trigger" line 2 at SQL statement
insert() - DBD::Pg::st execute failed: ERROR: duplicate key violates
unique constraint "table_without_trigger_pkey"
CONTEXT: SQL statement "INSERT INTO table_without_trigger VALUES
( $1 , $2 , $3 )"
PL/pgSQL function "trigger" line 2 at SQL statement
at ./test.pl line 28
(encounters error, increments table_with_trigger_pkey and
table_without_trigger_pkey)
[EMAIL PROTECTED] ./test.pl --dev --use-dbi-instead-of-
rose-db
(works)
===================================
| 4. in psql (development schema) |
===================================
nclark=# SET search_path TO development;
SET
nclark=# SELECT * FROM table_with_trigger;
id | date_added | column1 | column2
----+----------------------------+--------------+--------------
2 | 2007-06-06 23:05:10.548957 | column1 test | column2 test
(1 row)
nclark=# SELECT * FROM table_without_trigger;
id | date_added | column1
----+----------------------------+--------------
2 | 2007-06-06 23:05:10.548957 | column1 test
(1 row)
On Jun 6, 2007, at 6:37 PM, John Siracusa wrote:
> On 6/6/07 9:28 PM, Neal Clark wrote:
>> 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.
>
> When RDBO saves an object with a serial column into a Postgres
> database, it
> pre-fetches the serial value by getting the next value in the
> sequence, then
> it passes that as the value of the serial column in the insert
> statement. I
> suspect that's what's throwing off your triggers. Now that you
> know what's
> happening, presumably you can account for it somehow in your
> triggers. If
> not, you can make the serial column an integer column in your RDBO
> class
> metadata to avoid the pre-fetching of the sequence value.
>
> -John
>
>
>
> ----------------------------------------------------------------------
> ---
> 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
>
-------------------------------------------------------------------------
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
