[dev] CRITICAL: Postgresql broken(?)

[Jan Schneider]

Zitat von Michael M Slusarz <slusarz at horde.org>:

> Quoting Michael M Slusarz <slusarz at horde.org>:
>
>> Don't want to post a bug yet in case I am missing something  
>> obvious, but this change to Horde/Db has completely broken  
>> autoincrementing in Postgresql:
>>
>>
>> commit 324f067adc7d36c9efff1641fcd8b08b5c6ad26a
>> Author: Jan Schneider <jan at horde.org>
>> Date:   Fri Mar 4 14:13:07 2011 +0100
>>
>>    Changing arbitrary columns to autoincrementing simply doesn't work,
>>    because this is not supported by all databases. Use the virtual column
>>    type "primaryKey" instead, which does exactly what we want.
>>
>>
>> This is an incorrect statement.  In Postgresql primary keys in  
>> postgres are defined as a combination of a unique constraint and a  
>> *not-null* constraint.  Obviously, primary keys can not be used for  
>> an autoincrementing column, since this column will always be null  
>> when adding an entry.

Part of the autoincrementing magic in the PostgreSQL driver is that  
for the column a DEFAULT NEXTVAL() is set. Thus we don't need to  
provide a column value and works fine as a primary key.
Do any of the unit tests not pass for you?

>> So it looks like another 'virtual column' will need to be created -  
>> e.g. 'autoincrementKey'.
>
> Forgot to add - in Postgresql, we need to be using a UNIQUE  
> constraint, not a PRIMARY KEY constraint:
>
> http://www.postgresql.org/docs/9.0/static/ddl-constraints.html

I think we use SERIAL PRIMARY KEY which should be what we want. But I  
have to admit that I'm really not a PostgreSQL expert, though I got  
some help from the PostgreSQL folks to create the correct sequence for  
creating the primary key columns.

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/