ZF-11962: Unable to insert in a PostgreSQL table having a primary key <> "id" using PDO

Description

I have duplicated a very small test project from MySQL to PostgreSQL, then I just changed the application.ini file. The field used as primary key is called "dev_id" and is auto-incremented. In my Application_Model_DbTable_ class, I added

protected $_primary = 'dev_id';

update and delete methods work perfectly but insert crashes with a "An error occurred Application error" message. Both versions use UTF8 (added resources.db.params.charset = utf8 in application.ini) As I use french characters, I also have modified my controller class

class DeviseController extends Zend_Controller_Action {

public function init() {
    $this->view->setEscape('htmlentities');
}

Windows 7 / Apache 2.2.21 / PHP 5.3.8 / MySQL 5.1.54 / PostgreSQL 9.1.2

Here is the DDL for the table in MySQL

CREATE TABLE IF NOT EXISTS devise ( dev_id int(10) unsigned zerofill NOT NULL AUTO_INCREMENT, codedevise char(3) NOT NULL, libelle varchar(20) DEFAULT NULL, taux decimal(10,5) DEFAULT NULL, defaut enum('N','O','S') NOT NULL DEFAULT 'N', PRIMARY KEY (dev_id) USING BTREE, UNIQUE KEY dev_cledevise (codedevise), KEY dev_clealpha (libelle) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

Comments

I have added the MySQL description (which works perfectly) instead of the PosgreSQL

-- Table: devise

-- DROP TABLE devise;

CREATE TABLE devise ( dev_id integer NOT NULL DEFAULT nextval('devise_id_seq'::regclass), codedevise character(3) NOT NULL, libelle character varying(20), taux numeric(10,5), defaut character(1) NOT NULL DEFAULT 'N'::bpchar, CONSTRAINT dev_primary PRIMARY KEY (dev_id ), CONSTRAINT defaut CHECK (defaut = ANY (ARRAY['N'::bpchar, 'O'::bpchar, 'S'::bpchar])) ) WITH ( OIDS=FALSE ); ALTER TABLE devise OWNER TO pgbanque;

-- Index: dev_clealpha

-- DROP INDEX dev_clealpha;

CREATE INDEX dev_clealpha ON devise USING btree (libelle COLLATE pg_catalog."default" );

-- Index: dev_cledevise

-- DROP INDEX dev_cledevise;

CREATE UNIQUE INDEX dev_cledevise ON devise USING btree (codedevise COLLATE pg_catalog."default" );

I have added the MySQL description (which works perfectly) instead of the PosgreSQL

-- Table: devise

-- DROP TABLE devise;

CREATE TABLE devise ( dev_id integer NOT NULL DEFAULT nextval('devise_id_seq'::regclass), codedevise character(3) NOT NULL, libelle character varying(20), taux numeric(10,5), defaut character(1) NOT NULL DEFAULT 'N'::bpchar, CONSTRAINT dev_primary PRIMARY KEY (dev_id ), CONSTRAINT defaut CHECK (defaut = ANY (ARRAY['N'::bpchar, 'O'::bpchar, 'S'::bpchar])) ) WITH ( OIDS=FALSE ); ALTER TABLE devise OWNER TO pgbanque;

-- Index: dev_clealpha

-- DROP INDEX dev_clealpha;

CREATE INDEX dev_clealpha ON devise USING btree (libelle COLLATE pg_catalog."default" );

-- Index: dev_cledevise

-- DROP INDEX dev_cledevise;

CREATE UNIQUE INDEX dev_cledevise ON devise USING btree (codedevise COLLATE pg_catalog."default" );

Probably found the root cause : ZF automatically generates nextval('devise_dev_id_seq'::regclass) based on

<

table name>__seq instead of uusing the actual sequence defined in PostgreSQL. Work around : changed the sequence name to fit ZF generated name.

Fixed by using protected $_sequence = 'devise_id_seq';

Reported found solution to their issue, so marking as "Not an Issue"