db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Henning P. Schmiedehausen" <...@intermeta.de>
Subject Re: PostgreSQL ID generation
Date Mon, 30 Aug 2004 09:09:24 GMT
Scott Eade <seade@backstagetech.com.au> writes:

>Henning P. Schmiedehausen wrote:

[... my message hung in moderation for a few days. ...]

>What am I missing Henning?  If my schema is:

>  <table name="category" idMethod="native">
>    <column name="category_id" required="true" autoIncrement="true"
>        primaryKey="true" type="INTEGER"/>
>    <column name="name" size="100" type="VARCHAR"/>
>    <id-method-parameter name="seqName" value="CATEGORY_CATEGORY_ID_SEQ"/>
>  </table>

>The generated sql is:

>-----------------------------------------------------------------------------
>-- category
>-----------------------------------------------------------------------------
>DROP TABLE category CASCADE;

>CREATE TABLE category
>(
>                                    category_id serial,
>                                    name varchar (100),
>    PRIMARY KEY (category_id)
>);

>When this is executed PostgreSQL automatically creates a sequence (from 
>pgAdmin III):

>CREATE SEQUENCE public.category_category_id_seq
>  INCREMENT 1
>  MINVALUE 1
>  MAXVALUE 9223372036854775807
>  START 1
>  CACHE 1;

With the patch mentioned above applied, the id column will be a
regular integer column without any serial modifiers or relation to a
given sequence. The ID Generator will use "select nextval('foo_seq')"
and foo_seq is defined nowhere.

I don't like "autoIncrement = true" for the ID Column because the ID
management should (IMHO) done by the ID generator. Personally, I don't
like MySQL AUTO_INCREMENT columns very much. These are handy for a 20
line script where you don't want to do the ID generation all by
yourself, but if we have an O/R layer which contains ID Generators,
these IDs should be either generated with the ID Table using a Hi/Lo
scheme or with a sequence and select nextval.

Just reverting the mentioned revision won't work either, because if
you create a database schema just like the one that I use (where a lot
of tables use the same sequence for ID generation, ensuring that an ID
is unique in the whole database), then the resulting sql will contain
a lot of

create sequence foo_seq;

create sequence foo_seq;

statements which leads to (probably non-fatal but annoying) errors
when inserting this SQL.

... must think some more.

	Regards
		Henning

-- 
Dipl.-Inf. (Univ.) Henning P. Schmiedehausen          INTERMETA GmbH
hps@intermeta.de        +49 9131 50 654 0   http://www.intermeta.de/

RedHat Certified Engineer -- Jakarta Turbine Development  -- hero for hire
   Linux, Java, perl, Solaris -- Consulting, Training, Development

"Fighting for one's political stand is an honorable action, but re-
 fusing to acknowledge that there might be weaknesses in one's
 position - in order to identify them so that they can be remedied -
 is a large enough problem with the Open Source movement that it
 deserves to be on this list of the top five problems."
                       -- Michelle Levesque, "Fundamental Issues with
                                    Open Source Software Development"

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message