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

Issue Type:
Bug
Created:
2012-01-02T20:51:38.000+0000
Last Updated:
2012-02-26T07:02:37.000+0000
Status:
Closed
Fix version(s):
    Reporter:
    Vincent Tap (vtap)
    Assignee:
    Adam Lundrigan (adamlundrigan)
    Tags:
    • Zend_Db_Table
    • Zend_Db
    Related issues:
    Attachments:

    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

    Posted by Vincent Tap (vtap) on 2012-01-02T21:03:44.000+0000

    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" );

    Posted by Vincent Tap (vtap) on 2012-01-02T21:04:57.000+0000

    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" );

    Posted by Vincent Tap (vtap) on 2012-01-11T23:38:40.000+0000

    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.

    Posted by Vincent Tap (vtap) on 2012-01-12T07:50:33.000+0000

    Fixed by using protected $_sequence = 'devise_id_seq';

    Posted by Adam Lundrigan (adamlundrigan) on 2012-02-26T07:02:37.000+0000

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