Issues

ZF-78: PostgreSQL: Zend_Db_Table insert problems with last_insert_id

Description

http://framework.zend.com/developer/ticket/88

Zend_Db_Table insert method returns bad lastInsertId because sequence is not passed, fix:


public function insert( &$data ) {
    $this->_db->insert(
        $this->_name, 
        $data 
    );
    return $this->_db->lastInsertId(
        $this->_name . '_' . $this->_primary . '_seq'
    );
}

Comments

http://postgresql.org/docs/8.1/…

There are two functions available, lastval(), and currval().

All the other existing adapters, except mssql, rely on the behavior described by lastval() -obviously not the same code/implementation, but neverytheless, the same behavior. In examining PHP5's PDO implementation for PDO::lastInsertId, in pgsql_driver.c:pdo_pgsql_last_insert_id(), we see the use of CURRVAL(), not LASTVAL(). Thus, unlike some of the other PDO drivers, a correct sequence name must be supplied (not optional) to PDO::lastInsertId().

Since the proposed change in this issue's description alters a generic class (Zend_Db_Table) with code specific only to PgSQL, we can not apply the change as described to Zend_Db_Table. Instead, every adapter must be supplied enough information to construct a sequence name (if needed) according to the format required for that adapter's DB.

Please see the FishEye link above for changeset information.

Help solicited for unit tests.

0.1.5 released

Current code is incompatible with sequence name not automatically generated.

Index: library/Zend/Db/Adapter/Pdo/Pgsql.php

--- library/Zend/Db/Adapter/Pdo/Pgsql.php (revision 923) +++ library/Zend/Db/Adapter/Pdo/Pgsql.php (working copy) @@ -146,13 +146,17 @@ /** * Gets the last inserted ID. * - * @param string $tableName table or sequence name needed for some PDO drivers + * @param string $tableName table or sequence name (if primaryKey is null) needed for some PDO drivers * @param string $primaryKey primary key in $tableName need for some PDO drivers * @return integer */ public function lastInsertId($tableName = null, $primaryKey = null) { $this->_connect(); - return $this->_connection->lastInsertId($tableName .'_'. $primaryKey .'_seq'); + if (is_null($primaryKey)) { + return $this->_connection->lastInsertId($tableName); + } else { + return $this->_connection->lastInsertId($tableName .'_'. $primaryKey .'_seq'); + } } }

Ludovic Levesque supplied an example use case where the issue still occurs.

example use case where the issue still occurs:

i have table: product view: vproduct

so $_name='vproduct'

and error on insert

relation: vproduct.product_id_seq does not exist

any ideas?

Change fix version to 0.8.0.

Recategorize as Zend_Db_Table component.

Should be fixed in 0.9.3 beta. See ZF-1140.

Linking to ZF-1140.