AUTO_INCREMENT patch
[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
Attachments:
auto_increment.difftext/x-patchDownload
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/analyze.c,v
retrieving revision 1.283
diff -u -u -r1.283 analyze.c
--- src/backend/parser/analyze.c 1 Aug 2003 00:15:22 -0000 1.283
+++ src/backend/parser/analyze.c 2 Aug 2003 16:01:59 -0000
@@ -949,6 +949,28 @@
}
}
+ /* Check for AUTO_INCREMENT constraint */
+ foreach(clist, column->constraints)
+ {
+ constraint = lfirst(clist);
+ if (IsA(constraint, Constraint) &&
+ constraint->contype == CONSTR_AUTO_INCREMENT) {
+ column->typename->typeid = LookupTypeName(column->typename);
+ if (column->typename->typeid != InvalidOid)
+ column->typename->names = NIL;
+ if (is_serial)
+ elog(WARNING, "both SERIAL and AUTO_INCREMENT specified for column '%s.%s'",
+ cxt->relation->relname, column->colname);
+ else if (column->typename->typeid != INT4OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("AUTO_INCREMENT columns must be of integer type")));
+ else
+ is_serial = true;
+ break;
+ }
+ }
+
/* Do necessary work on the column type declaration */
transformColumnType(pstate, column);
@@ -1112,6 +1134,10 @@
case CONSTR_ATTR_DEFERRED:
case CONSTR_ATTR_IMMEDIATE:
/* transformConstraintAttrs took care of these */
+ break;
+
+ case CONSTR_AUTO_INCREMENT:
+ /* already processed */
break;
default:
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.427
diff -u -u -r2.427 gram.y
--- src/backend/parser/gram.y 19 Jul 2003 20:20:52 -0000 2.427
+++ src/backend/parser/gram.y 2 Aug 2003 12:01:23 -0000
@@ -327,7 +327,7 @@
/* ordinary key words in alphabetical order */
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD AFTER
AGGREGATE ALL ALTER ANALYSE ANALYZE AND ANY ARRAY AS ASC
- ASSERTION ASSIGNMENT AT AUTHORIZATION
+ ASSERTION ASSIGNMENT AT AUTHORIZATION AUTO_INCREMENT
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
BOOLEAN_P BOTH BY
@@ -1604,6 +1604,12 @@
n->fk_del_action = (char) ($5 & 0xFF);
n->deferrable = FALSE;
n->initdeferred = FALSE;
+ $$ = (Node *)n;
+ }
+ | AUTO_INCREMENT
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_AUTO_INCREMENT;
$$ = (Node *)n;
}
;
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /home/pqcvs/pgsql-server/src/backend/parser/keywords.c,v
retrieving revision 1.140
diff -u -u -r1.140 keywords.c
--- src/backend/parser/keywords.c 25 Jun 2003 03:40:18 -0000 1.140
+++ src/backend/parser/keywords.c 2 Aug 2003 12:00:54 -0000
@@ -50,6 +50,7 @@
{"assignment", ASSIGNMENT},
{"at", AT},
{"authorization", AUTHORIZATION},
+ {"auto_increment", AUTO_INCREMENT},
{"backward", BACKWARD},
{"before", BEFORE},
{"begin", BEGIN_P},
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 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
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
OK, I read further and now understand. Sorry.
andrew
----- Original Message -----
From: "Andrew Dunstan" <andrew@dunslane.net>
To: "PostgreSQL Development" <pgsql-hackers@postgresql.org>
Sent: Saturday, August 02, 2003 10:09 PM
Subject: Re: [HACKERS] 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 1The docs currently state that views are read-only. Is this no longer
true?
Show quoted text
andrew
Import Notes
Resolved by subject fallback
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