Re: [Treebase-devel] we may have to give up on PK auto-generation by postgres

[Rutger V. <rut...@gm...>]

> Here is the history of the schema, as I can reconstruct it from SVN:
> - The June 2009 version of TBASE2_POSTGRES_CREATION.sql contains extensive
> commented-out SQL, for objects in the JRUAN schema.  I'd guess that's the
> DB2 code. In it, PKs are defined as
>   ALGORITHM_ID  BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1,
> INCREMENT BY 1, CACHE 20) NOT NULL,

That's right, the commented out code were the creation statements for
DB2, exported by Aqua data studio, which I translated by hand into
what seemed like the correct statements for PostgreSQL

> - The actual postgres code in the June 2009 version defines PKs as
>    algorithm_id bigint NOT NULL,
> There are no sequences defined in this code.

Right, which was *definitely* wrong. DB2 was instructed to make IDs
automatically ("GENERATED BY DEFAULT AS IDENTITY"), so by that same
logic PostgreSQL needed to do something functionally similar.

> - The next, Nov 18, version added table-specific sequences:
> CREATE SEQUENCE algorithm_id_sequence;
> ALTER TABLE algorithm ALTER COLUMN algorithm_id SET DEFAULT
> nextval('algorithm_id_sequence');

My understanding was that this was the closest way to mimic DB2's behaviour.

> - For the next, Nov 30 version, the SVN comment says "Recalculated sequence
> restart values".  It also dropped all commented-out DB2 code, which confuses
> my diff tool, so I am not sure what exactly happened.  All RESTART
> statements, like
> -- alter sequence analysis_id_sequence restart with 4887;
> are commented out in both November versions. The restart numbers are
> different in each version.

Yup, two things happened: I removed the old DB2 code, and I was trying
to get the sequences to work such that they would start
auto-incrementing beyond the highest value of the then-assigned IDs in
each table.

> - Dec 9, and final, version defines a few indexes.
> None of these versions defines hibernate_sequence.

That is correct, I have never defined a hibernate_sequence in the
PostgreSQL creation script.

-- 
Dr. Rutger A. Vos
School of Biological Sciences
Philip Lyle Building, Level 4
University of Reading
Reading
RG6 6BX
United Kingdom
Tel: +44 (0) 118 378 7535
http://www.nexml.org
http://rutgervos.blogspot.com