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

[Vladimir G. <vla...@du...>]

On Jan 28, 2010, at 8:52 AM, William Piel wrote:

> On Jan 27, 2010, at 4:48 PM, Vladimir Gapeyev wrote:
>> The original version of this schema (in SVN since mid-June-2009)  
>> did not have any auto-increment declarations on PKs, as far as I  
>> can see.
> Is this the DB2 schema, or the Pg schema?  Rutger and Mark J. D.  
> converted the DB2 schema into the Pg schema -- and maybe that's what  
> you're looking at.
>  (Rutger -- can you give Vladimir access to the SDSC DB2 schema for  
> him to see how this was done?)

I attach the history of the schema after this message.  (It might shed  
light on some points below.)


> All existing migrated records (i.e., where all tables have id  
> numbers that start at one and increase by one -- no big jumps and no  
> order-of-magnitude-bigger) were done on the DB2 schema.

Bill, do you mean the data migration was done on the DB2 instance  
(it's impossible to have a DB schema in a postgres instance!), and  
then the result was ported to Postgres (via an SQL dump and restore?).

If so, was the web app connected to the DB2 instance ever used to  
enter new data after the migration?  Inspecting IDs for that data may  
shed some light on how Hibernate sequencing behaved back then.  Based  
on Youjun's assessment that TB2 DAO layer uses default Hibernate  
settings, my guess to explain our troubles would be this: Hibernate  
uses different default conventions on DB2 vs PostgreSQL. While the web  
app was developed, against DB2, Hibernate relied on DB2 auto- 
increments for PK's.  When the database was suddenly switched from DB2  
to Postgres, Hibernate created hibernate_sequence, as per its default  
for Postgres.

If this guess is right, the logic of the DAO layer was built in the  
context "PKs are incremented one-by-one, by the DBMS".  Ditto for the  
migration scripts.

> So it's possible that: (1) the DB2 schema had a different sequencing  
> system; (2) when the Pg schema was first designed, we forgot to  
> include auto-increment annotations; (3) the auto-increment was later  
> added by Rutger, but that (4) something wasn't working right with  
> them, and hence we switched to database-wide hibernate_sequence.
>
> If it is the case that the original DB2 way of doing this allows us  
> to bypass the DAO layer, can we revert to that in our Pg deployment?

I agree that all this argues in favor of ditching the  
hibernate_sequence switch and finding out the most appropriate way to  
instruct Hibernate to rely on DBMS id generation.  I do not know what  
it should be exactly.  If I recall right, Youjun's proposal in this  
direction called for annotations on each DAO class to instruct it  
which sequence to use.  This does not sound right to me, since this  
instruction is already in the schema, and I'd trust Hibernate  
implementers to have designed how to take advantage of that.

--Vladimir


========================================================
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,

- 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.

- 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');

- 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.

- Dec 9, and final, version defines a few indexes.

None of these versions defines hibernate_sequence.