[apologies if this comes through twice]

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.

The first problem is that the syntax JBoss uses for mapping Java types
onto SQL types isn't powerful enough to understand PostgreSQL's SERIAL
pseudo-types.  The attached patch therefore adds support for MySQL's
AUTO_INCREMENT syntax to PostgreSQL.  It works by defining a new
column constraint (CONSTR_AUTO_INCREMENT) which is handled specially
by transformColumnDefinition() - after it has transformed SERIAL
pseudo-types to the corresponding INT types, but before it actually
creates the sequence and adds the synthetic DEFAULT constraint.  It is
thus possible to specify AUTO_INCREMENT on a SERIAL column; this will
generate a warning but does not have any other negative side effects.
It will however generate an error if AUTO_INCREMENT is specified on a
non-integer column.

(given that this is my first experience with PostgreSQL sources, I'd
like some feedback about the use of elog() / ereport())

Usage test:

des=# create table test1 ( foo int auto_increment, bar text );
NOTICE:  CREATE TABLE will create implicit sequence "test1_foo_seq" for SERIAL column "test1.foo"
CREATE TABLE
des=# \d test1
                           Table "public.test1"
 Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
 foo    | integer | not null default nextval('public.test1_foo_seq'::text)
 bar    | text    |

des=# create table test2 ( foo int auto_increment, bar text );
NOTICE:  CREATE TABLE will create implicit sequence "test2_foo_seq" for SERIAL column "test2.foo"
CREATE TABLE
des=# \d test2
                           Table "public.test2"
 Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
 foo    | integer | not null default nextval('public.test2_foo_seq'::text)
 bar    | text    |

des=# create table test3 ( foo serial auto_increment, bar text );
WARNING:  both SERIAL and AUTO_INCREMENT specified for column 'test3.foo'
NOTICE:  CREATE TABLE will create implicit sequence "test3_foo_seq" for SERIAL column "test3.foo"
CREATE TABLE
des=# \d test3
                           Table "public.test3"
 Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
 foo    | integer | not null default nextval('public.test3_foo_seq'::text)
 bar    | text    |

des=# create table test4 ( foo text auto_increment, bar text );
ERROR:  AUTO_INCREMENT columns must be of integer type


The second problem is an issue which is probably best solved in JBoss
and not in PostgreSQL (JBoss insists on inserting null into the auto-
incrementing column, which obviously doesn't work)

DES
-- 
Dag-Erling Smørgrav - des@des.no
Sat, 02 Aug 2003 18:43:23 +0200
des@des.no (Dag-Erling =?iso-8859-1?q?Sm=F8rgrav?=)
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.
02 Aug 2003 16:26:35 -0400
Rod Taylor <rbt@rbt.ca>
Re: AUTO_INCREMENT patch
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 newly created row.

 - apply my auto_increment patch

 - replace the bogus auto-increment template in the PostgreSQL and
   PostgreSQL 7.2 mappings in standardjbosscmp-jdbc.xml with the
   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 a relation
   that involves that field, the other endpoint will also be defined
   as SERIAL!  Obviously that's not a recipe for success.

> 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

Sat, 02 Aug 2003 22:52:15 +0200
des@des.no (Dag-Erling =?iso-8859-1?q?Sm=F8rgrav?=)
Re: AUTO_INCREMENT patch
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

Sun, 03 Aug 2003 02:21:48 +0200
des@des.no (Dag-Erling =?iso-8859-1?q?Sm=F8rgrav?=)
Re: AUTO_INCREMENT patch
> 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)
02 Aug 2003 20:56:25 -0400
Rod Taylor <rbt@rbt.ca>
Re: AUTO_INCREMENT patch

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


Sat, 2 Aug 2003 22:09:04 -0400
"Andrew Dunstan" <andrew@dunslane.net>
Re: AUTO_INCREMENT patch
On Sat, 02 Aug 2003 18:43:23 +0200, Dag-Erling Smï¿½rgrav wrote:

> It works by defining a new
> column constraint (CONSTR_AUTO_INCREMENT) which is handled specially
> by transformColumnDefinition() - after it has transformed SERIAL
> pseudo-types to the corresponding INT types

Beware that MySQL's AUTO_INCREMENT and PostgreSQL's SERIAL are not
equivalent.
See http://troels.arvin.dk/db/rdbms/#mix-identity

-- 
Greetings from Troels Arvin, Copenhagen, Denmark



Sun, 03 Aug 2003 22:06:35 +0200
Troels Arvin <troels@arvin.dk>
Re: AUTO_INCREMENT patch
Troels Arvin <troels@arvin.dk> writes:
> Beware that MySQL's AUTO_INCREMENT and PostgreSQL's SERIAL are not
> equivalent.

Yes, we already know that MySQL s***s :)

DES
-- 
Dag-Erling Smørgrav - des@des.no

Tue, 05 Aug 2003 22:03:45 +0200
des@des.no (Dag-Erling =?iso-8859-1?q?Sm=F8rgrav?=)
Re: AUTO_INCREMENT patch