Thread: Re: AUTO_INCREMENT patch
Moving to -hackers > I'm working on getting auto-incrementing CMP fields in JBoss 3.2.1 to > work with PostgreSQL data sources. There are currently two obstacles > to this. Yeah.. JBoss is very annoying in this regard. A temporary solution seems to be to use BEFORE triggers to force the sequence to be used for the default value. You could also do this with an INSTEAD rule (something like the below): CREATE OR REPLACE RULE rulename AS ON INSERT TO tablename DO INSTEAD INSERT INTO tablename ( id, col1, ...) VALUES ( DEFAULT, NEW.col1, ...); AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X proposals do have support for the more common IDENTITY syntax which can accomplish the same job as well as many others. (PostgreSQL does NOT have the general identity implementation yet) What you're looking for is the ability to force the column to use the IDENTITY even when the client provides a specific value: CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY); See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft for full details. ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf DB2 and MSSql have implementations of the IDENTITY syntax if you want a reference point.
Rod Taylor <rbt@rbt.ca> writes:
> Yeah.. JBoss is very annoying in this regard. A temporary solution seems
> to be to use BEFORE triggers to force the sequence to be used for the
> default value. You could also do this with an INSTEAD rule (something
> like the below):
>
> CREATE OR REPLACE RULE rulename AS ON INSERT
> TO tablename
> DO INSTEAD
> INSERT INTO tablename
> ( id, col1, ...)
> VALUES ( DEFAULT, NEW.col1, ...);
That's a good workaround for 3.2.1. As regards the upcoming 3.2.2, I
just found out that it has PostgreSQL-specific code to handle this,
though it's an incomplete fix. To summarize:
- add <entity-command>postgresql-fetch-seq</entity-command> to each affected entity (or the <defaults> section) in
jbosscmp-jdbc.xml; this resolves the "failed to insert null" problem and also makes sure JBoss knows the id of the
newlycreated row.
- apply my auto_increment patch
- replace the bogus auto-increment template in the PostgreSQL and PostgreSQL 7.2 mappings in standardjbosscmp-jdbc.xml
withthe following:
<auto-increment-template>?1 AUTO_INCREMENT</auto-increment-template>
the current template (?1) works if you set the corresponding <cmp-field>'s <sql-type> to SERIAL - but if you define
arelation that involves that field, the other endpoint will also be defined as SERIAL! Obviously that's not a recipe
forsuccess.
> AUTO_INCREMENT is non-standard (MySQL only?), however the SQL200X
> proposals do have support for the more common IDENTITY syntax which can
> accomplish the same job as well as many others. (PostgreSQL does NOT
> have the general identity implementation yet)
the IDENTITY syntax is very similar to AUTO_INCREMENT, in fact you can
apply s/auto_increment/identity/ to my patch and have a useful subset
of it :) JBoss already knows about IDENTITY, since Hypersonic SQL and
MS SQL both support it.
(interestingly, JBoss doesn't seem to know that DB/2 also supports it)
> What you're looking for is the ability to force the column to use the
> IDENTITY even when the client provides a specific value:
>
> CREATE TABLE test(col integer GENERATED ALWAYS AS IDENTITY);
as mentioned above, that's no longer a problem with JBoss 3.2.2.
> See sections 10.22, 10.23, 11.3, and 11.4 of the SQL200X working draft
> for full details.
>
> ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf
I believe this is more up to date:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FDIS/4FDIS1-02-Foundation-2003.pdf
DES
--
Dag-Erling Smørgrav - des@des.no
Rod Taylor <rbt@rbt.ca> writes:
> CREATE OR REPLACE RULE rulename AS ON INSERT
> TO tablename
> DO INSTEAD
> INSERT INTO tablename
> ( id, col1, ...)
> VALUES ( DEFAULT, NEW.col1, ...);
>
I now have a patch that adds support for the GENERATED ... AS ...
syntax and implements the "GENERATED BY DEFAULT AS IDENTITY" case.
I'm trying to figure out how to implement the other two cases
("GENERATED ALWAYS AS IDENTITY" and "GENERATED ALWAYS AS ( expr )").
I thought I'd try your trick:
des=# create table test ( id serial, word text );
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column "test.id"
CREATE TABLE
des=# create rule test_id_generate as
des-# on insert to test do instead
des-# insert into test ( id, word ) values ( default, new.word );
CREATE RULE
des=# insert into test ( id, word ) values ( 42, 'hello' );
ERROR: infinite recursion detected in rules for relation "test"
des=# insert into test ( word ) values ( 'hello' );
ERROR: infinite recursion detected in rules for relation "test"
any suggestions?
DES
--
Dag-Erling Smørgrav - des@des.no
> des=# create table test ( id serial, word text );
> NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column "test.id"
> CREATE TABLE
> des=# create rule test_id_generate as
> des-# on insert to test do instead
> des-# insert into test ( id, word ) values ( default, new.word );
> CREATE RULE
> des=# insert into test ( id, word ) values ( 42, 'hello' );
> ERROR: infinite recursion detected in rules for relation "test"
> des=# insert into test ( word ) values ( 'hello' );
> ERROR: infinite recursion detected in rules for relation "test"
Shoot, sorry, I forgot you will want to do this on a view. In my case I
have actually used 2 different schemas. The JBoss user(s) have their
default search_path setup with jboss, data. Updates and deletes are
equally fun.
You may find you prefer a trigger for this instead -- it won't complain
about recursion.
t=# begin;
BEGIN
t=# create schema data
t-# create table test (id serial, word text);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
SERIAL column "test.id"
CREATE SCHEMA
t=#
t=# create schema jboss
t-# create view test as select * from data.test;
CREATE SCHEMA
t=#
t=# create rule test_id_generate as on insert to jboss.test
t-# do instead
t-# insert into data.test (id, word) values (default, new.word);
CREATE RULE
t=#
t=# insert into jboss.test (word) values ('hello');
INSERT 17347 1
t=#
t=# insert into jboss.test (id, word) values (null, 'hello');
INSERT 17348 1
t=#
t=# insert into jboss.test (id, word) values ('22', 'hello');
INSERT 17349 1
t=#
t=#
t=# select * from jboss.test;id | word
----+------- 1 | hello 2 | hello 3 | hello
(3 rows)
t=# select * from data.test;id | word
----+------- 1 | hello 2 | hello 3 | hello
(3 rows)
Rod Taylor wrote about using views for identity cols, thus:
t=# create schema jboss
t-# create view test as select * from data.test;
CREATE SCHEMA
t=#
t=# create rule test_id_generate as on insert to jboss.test
t-# do instead
t-# insert into data.test (id, word) values (default, new.word);
CREATE RULE
t=#
t=# insert into jboss.test (word) values ('hello');
INSERT 17347 1
The docs currently state that views are read-only. Is this no longer true?
andrew