ZF-78: PostgreSQL: Zend_Db_Table insert problems with last_insert_id

Issue Type: Bug Created: 2006-06-20T23:17:15.000+0000 Last Updated: 2007-07-05T14:43:08.000+0000 Status: Resolved Fix version(s): - 0.9.3 (05/May/07)

Reporter: Zend Framework (zend_framework) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db_Table

Related issues: - ZF-1140



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

<pre class="highlight">
public function insert( &$data ) {
    return $this->_db->lastInsertId(
        $this->_name . '_' . $this->_primary . '_seq'


Posted by Gavin (gavin) on 2006-07-04T17:00:11.000+0000…

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.

Posted by Jayson Minard (jayson) on 2006-07-11T11:54:37.000+0000

0.1.5 released

Posted by Ludovic Levesque (luddic) on 2006-07-27T06:10:25.000+0000

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

Posted by Gavin (gavin) on 2006-07-27T14:43:05.000+0000

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

Posted by restman (restman) on 2006-08-04T06:43:08.000+0000

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?

Posted by Bill Karwin (bkarwin) on 2006-11-13T16:49:27.000+0000

Change fix version to 0.8.0.

Posted by Bill Karwin (bkarwin) on 2007-01-05T17:06:23.000+0000

Recategorize as Zend_Db_Table component.

Posted by Bill Karwin (bkarwin) on 2007-05-12T12:37:48.000+0000

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

Posted by Bill Karwin (bkarwin) on 2007-05-12T12:38:04.000+0000

Linking to ZF-1140.

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.