带有PostgreSQL的CakePHP 2.2失败的新行插入-数据库错误：未定义表：7错误：关系“ table_id_seq”不存在 [英] CakePHP 2.2 with PostgreSQL Failed new row insert - Database Error: Undefined table: 7 ERROR: relation "table_id_seq" does not exist

问题描述

我的问题如下。

从表中删除多行后，将新记录插入同一表会导致错误。

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

表

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 INTO "schema"."order_details" ("order_id", "field_1", "field_2", "field_3")
VALUES (37, 1, 2, 'value');

序列模式。使用的模式中存在 order_details_id_seq。

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;

模型。

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

点试图插入 $ this-> OrderDetail-> query（'VACUUM FULL ANALYZE order_details'）; 无效

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

我在第一个foreach循环中遇到错误。
最奇怪的是，问题随机出现并消失了。

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

它为删除语句的每行激发2个查询（在我的情况下为100个！），而不是两个如果是

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.

编辑：当前代码经过调整后的 DboSource 应该可以正常工作。

Currently code works as it should with tweaked DboSource.

推荐答案

似乎蛋糕正在公共模式中查找其不存在的序列。

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

通过调整以包含模式来修复它最后插入getter inf文件中的名称 Model / Datasource / DboSource.php create 方法与此差异

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 @@

@@ -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.

这篇关于带有PostgreSQL的CakePHP 2.2失败的新行插入-数据库错误：未定义表：7错误：关系“ table_id_seq”不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！