Home » Database » DB » Bug #42

[2003-09-28 21:26 UTC] pvenegas at student dot ateneo dot edu

Description:
------------
Calling DataObject::insert(), which calls DB_pgsql::nextID(), which calls DB_common::getSequenceName(), when using PostgreSQL may produce an erroneous sequence name, which leads to the creation of a new sequence ($ondemand = true), which is certainly not what was expected of it.

I'll just hack this for now, to rename generated sequences my way, but this is a quirky bug, and should get fixed.

(Note: To glark the following code and results better, use fixed-width fonts.)

Reproduce code:
---------------
// DB setup (from PostgreSQL)

photodb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | client | table | paolo
public | client_id_seq | sequence | paolo
...

photodb=# select nextval ('client_id_seq');
nextval
---------
3
(1 row)
...

// in PHP

$client = new DataObject_Client();
$client->name = 'foo';
$client->insert();

Expected result:
----------------
// from PHP (using PEAR (debugLevel(7))) :

sql QUERYINSERT INTO client (id , name , contact , contactnum , address ) VALUES ( 4 , 'foo' , '' , '' , '' )

// and

photodb=# select nextval ('client_id_seq');
nextval
---------
4

Actual result:
--------------
// from PHP (using PEAR (debugLevel(7))) :

sql QUERYINSERT INTO client (id , name , contact , contactnum , address ) VALUES ( 1 , 'foo' , '' , '' , '' )

photodb=# \d
...
public | client | table | paolo
public | client_id_seq | sequence | paolo
public | client_seq | sequence | raf

photodb=# select nextval ('client_id_seq');
nextval
---------
3

photodb=# select nextval ('client_seq');
nextval
---------
2

[2003-09-28 21:49 UTC] pvenegas at student dot ateneo dot edu

I understand that the sequence name is supposed to depend on the field name. I'll look around to see if there's any way to do this.

I've been reading the code and comments around it. Hmm... Is this another sign of how MySQL-centric people can be, implementing functions for it and leaving work for other DBs to other people who actually care? ;) *kidding*

/me loves Postgres though. I'll be hacking on this.

[2005-03-22 16:11 UTC] holger42 at gmx dot de

This bug is still present with:
DB 1.7.4 stable
DB_DataObject 1.7.11 stable

Hope you can fix this soon.

[2005-03-22 16:22 UTC] smith at backendmedia dot com

Actually its not MySQL centric, but maybe a bit auto increment centric. Alot of people want to have their sequences names like their tables because it makes it easier to associate the sequence with a table like with auto increment. However alot (all?) RDBMS dont allow sequences and tables names to be the same. Therefore I have for example readded the getSequenceName() stuff after dropping it MDB2 CVS a while ago.