When load fixture with postgres, sequence numbers updated to wrong values/did not upgraded

[tino415]

When I load fixtures, sequence number of tables are not updated or updated to wrong values,
I'm using this fix:

<?php

namespace tests\codeception\fixtures;

use yii\test\ActiveFixture;
class DocumentTypeFixture extends ActiveFixture {

    public $modelClass = 'app\models\DocumentType';

    public function load() {
        parent::load();

        if($this->db->drivername === 'pgsql') {
            $this->db->createCommand("SELECT pg_catalog.setval(".
                "pg_get_serial_sequence('document_type', 'id'),".
                "(SELECT MAX(id)+1 FROM document_type)".
            ")")->execute();
        }
    }
}

Error message:

1) tests\codeception\unit\crawlers\SuperMusicDocumentCrawlerTest::testCrawling
yii\db\IntegrityException: SQLSTATE[23505]: 
Unique violation: 7 ERROR:  duplicate key value violates unique constraint "document_type_pkey"
DETAIL:  Key (id)=(3) already exists.
The SQL being executed was: INSERT INTO "document_type" ("name") VALUES ('taby')

Fixture data:

<?php

return [
    'type' => [
        'id' => 1,
        'name' => 'type',
    ],
    'type' => [
        'id' => 2,
        'name' => 'type2',
    ],
    'type' => [
        'id' => 3,
        'name' => 'type333',
    ],
];

[dynasource]

why would this occur with id=3 and not already with id=2?

INSERT INTO "document_type" ("name") VALUES ('taby')

this does also not match with the fixture data you supplied. Hard to verify.

Please reopen with correct information

[tino415]

It is long time closed project, I'm unable to verify.

[roslov]

I have the same problem.

Fixture for table push_notification:

<?php

return [
    'push1' => [
        'id' => 1,
        'customer_id' => 1,
        'type_id' => 1,
        'data' => '{"likedById":2}',
        'status' => 'new',
        'errors' => 0,
        'updated_at' => '2017-06-23 00:00:04',
    ],
    'push2' => [
        'id' => 2,
        'customer_id' => 2,
        'type_id' => 2,
        'data' => '{"likedById":1}',
        'status' => 'processing',
        'errors' => 0,
        'updated_at' => '2017-06-23 00:00:05',
    ],
    'push3' => [
        'id' => 3,
        'customer_id' => 1,
        'type_id' => 1,
        'data' => '{"likedById":3}',
        'status' => 'done',
        'errors' => 0,
        'updated_at' => '2017-06-23 00:00:02',
    ],
    'push4' => [
        'id' => 4,
        'customer_id' => 5,
        'type_id' => 1,
        'data' => '{"likedById":4}',
        'status' => 'not found',
        'errors' => 1,
        'updated_at' => '2017-06-23 00:00:07',
    ],
    'push5' => [
        'id' => 5,
        'customer_id' => 4,
        'type_id' => 2,
        'data' => '{"likedById":2}',
        'status' => 'failed',
        'errors' => 1,
        'updated_at' => '2017-06-23 00:00:06',
    ],
    'push6' => [
        'id' => 6,
        'customer_id' => 4,
        'type_id' => 2,
        'data' => '{"likedById":1}',
        'status' => 'new',
        'errors' => 0,
        'updated_at' => '2017-06-23 00:00:01',
    ],
    'push7' => [
        'id' => 7,
        'customer_id' => 2,
        'type_id' => 1,
        'data' => '{"likedById":5}',
        'status' => 'done',
        'errors' => 0,
        'updated_at' => '2017-06-23 00:00:00',
    ],
    'push8' => [
        'id' => 8,
        'customer_id' => 1,
        'type_id' => 2,
        'data' => '{"likedById":4}',
        'status' => 'failed',
        'errors' => 20,
        'updated_at' => '2017-06-23 00:00:03',
    ],
    'push9' => [
        'id' => 9,
        'customer_id' => 2,
        'type_id' => 2,
        'data' => '{"likedById":1}',
        'status' => 'processing',
        'errors' => 15,
        'updated_at' => '2017-06-23 00:00:09',
    ],
];

Then try to do

INSERT INTO "push_notification" ("customer_id", "type_id", "data", "status") VALUES (1, 1, '[]', 'new') RETURNING "id";

The error will be:

ERROR:  duplicate key value violates unique constraint "push_notification_pkey"
DETAIL:  Key (id)=(1) already exists.

After sending the query 10 times the error disappears (when id becames 10).

[roslov]

I’m fixing it this way:

protected function _before()
{
    $this->tester->haveFixtures([
        'notifications' => PushNotificationFixture::className(),
    ]);
    // Postgres fix
    Yii::$app->db
        ->createCommand("SELECT setval('push_notification_id_seq', max(id)) FROM push_notification;")
        ->execute();
}

But it’s a kludge.

[StalkAlex]

What about something like this? Related method.

Rough but working implementation.

    public function load()
    {
        $this->data = [];
        $table = $this->getTableSchema();
        foreach ($this->getData() as $alias => $row) {
            $primaryKeys = $this->db->schema->insert($table->fullName, $row);
            $this->data[$alias] = array_merge($row, $primaryKeys);
        }
        if ($table->sequenceName !== null && count($primaryKeys) > 0) {
            //recalculate sequence based on last inserted id
            $primaryKey = key($primaryKeys);
            $lastId = (new Query())->select('max('. $primaryKey .')')->from($table->fullName)->scalar();
            $this->db->createCommand()->resetSequence($table->fullName, $lastId)->execute();
        }
    }

[samdark]

@StalkAlex if you have time please do a pull request.