CakePHP 2.2 with PostgreSQL Failed new row insert

问题:

My problem is as follows.

After deleting multiple rows from table, inserting new record into same table results in error.

Database Error
Error: SQLSTATE[42P01]: 
Undefined table: 7 ERROR: relation "order_details_id_seq" does not exist

Table

CREATE TABLE schema.order_details (
    id serial NOT NULL,
    order_id integer NOT NULL,
    field_1 integer,
    field_2 real,
    field_3 character varying(15),
    CONSTRAINT order_details_pkey PRIMARY KEY (id )
)
WITH (
    OIDS=FALSE
);

Insert is

INSERT INTO "schema"."order_details" ("order_id", "field_1", "field_2", "field_3")
VALUES (37, 1, 2, 'value');

Sequence "schema"."order_details_id_seq" in used schema exists.

CREATE SEQUENCE schema.order_details_id_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 37
    CACHE 1;

Models.

// Model
class Order extends AppModel {
public $useDbConfig = 'other_data';
public $hasMany = array(
    'OrderDetail' => array(
        'className' => 'OrderDetail',
        'foreignKey' => 'order_id',
        'dependent' => true,
        'order' => array(
                      'OrderDetail.order_id',
                      'OrderDetail.field_1'
                     ))
);

class OrderDetail extends AppModel {
public $useDbConfig = 'other_data';
public $belongsTo = array(
    'Order' => array(
        'className' => 'Order',
        'foreignKey' => 'order_id',
        'dependent'    => true
    ),

// model Order save code on recreation of order
$this->OrderDetail->deleteAll(array('OrderDetail.order_id' => $this->id));

At this point tried to insert $this->OrderDetail->query('VACUUM FULL ANALYZE order_details'); with no effect

foreach ($details as $d) {
    $this->OrderDetail->create();
    $this->OrderDetail->save($d /*array(
        'order_id' => $this->id,
        'field_1' => 1, 
        'field_2' => 2, 
        'field_3' => 'value'
      )*/);
}

I get error on first foreach loop. Weirdest thing is that problem appears and disappears after some time randomly.

Any suggestions on what it could be and how to get rid of it?

---

Currently solved problem using code.

$this->Order->id = $id;
$this->Order->delete();

It fires 2 queries for each row (100 extra in my case!) of delete statements instead of two in case of

$this->OrderDetail->deleteAll(array('OrderDetail.order_id' => $id));

So for this time it has space for improvement.

EDIT: Currently code works as it should with tweaked DboSource.

回答1:

It seems that cake was looking in public schema for sequence where it is not located.

Fixed it by tweaking to include schema name in last insert getter inf file Model/Datasource/DboSource.php create method with this diff

@@ -1006,7 +1006,7 @@

if ($this->execute($this->renderStatement('create', $query))) {
    if (empty($id)) {
-       $id = $this->lastInsertId($this->fullTableName($model, false, false), $model->primaryKey);
+       $id = $this->lastInsertId($this->fullTableName($model, false, true), $model->primaryKey);
    }
    $model->setInsertID($id);
    $model->id = $id;

I know that modifying core is not the way to go, but as long as it is working it is fine with me.

回答2:

This happened to me because I modified the name of the table, but PostgreSQL did not change the name of the sequences. Knowing this, I changed the name of the sequences that affected this table and it was resolved.

回答3:

To prevent this error, use this convention to name your sequence when using cakephp: table_name_id_seq. For example:

table name: user
sequence name should be: user_id_seq

If you alredy have sequences, you can rename it in posgres like this

alter sequence user_seq rename to user_id_seq

I'm not a fun of this way to name sequence but it prenvent this kind of errors in my case