CakePHP: Unique violation: 7 ERROR: duplicate key value violates unique constraint

Question

I'm running into the following error after trying to delete a bunch of records and then insert new ones:

Error: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "routes_pkey" DETAIL: Key (id)=(1328) already exists.

SQL Query:
INSERT INTO routes (agency_id, route_identifier, short_name, long_name, description, route_color, text_color) VALUES (:c0, :c1, :c2, :c3, :c4, :c5, :c6) RETURNING *

Here's a simplified version of the code:

    $routes = TableRegistry::get('Routes');
    $routes->deleteAll(['agency_id' => $agency->id]);

    # Data to be updated
    $patchData = [
        'stops' => $stopData,
        'static_data_modified' => Time::now()
    ];

    $patchOptions = [
        'associated' => ['Stops.Routes']
    ];

    # If: The stops have already been added to the DB
    # Then: Remove them from the patch data
    if (isset($stopData['_ids'])) {
        unset($patchData['stops']);

        # Change settings for this implementation type
        $patchOptions = [];
        $stopCount = count($stopData['_ids']);
    }

    $agency = $this->Agencies->patchEntity($agency, $patchData, $patchOptions);

    $this->Agencies->save($agency);

It seems like for some reason Postgres thinks I'm inserting a record with a duplicate primary key. But I can't see how that would be possible from my code.

Here's what I see at the end of the SQL Log:

DELETE FROM 
  routes 
WHERE 
  agency_id = 51

BEGIN

SELECT 
  1 AS "existing" 
FROM 
  agencies Agencies 
WHERE 
  Agencies.id = 51 
LIMIT 
  1

INSERT INTO routes (
  agency_id, route_identifier, short_name, 
  long_name, description, route_color, 
  text_color
) 
VALUES 
  (
    51, '100001', '1', '', 'Kinnear - Downtown Seattle', 
    '', ''
  ) RETURNING *

ROLLBACK

Any ideas why I'm seeing this error?

I'm on CakePHP v3.1 with Postgresql 9.4

I tried to add this but it didn't change anything:

$connection = ConnectionManager::get('default');
$results = $connection->execute('SET CONSTRAINT = routes_pkey DEFERRED');

Here are similar questions I've read without finding a solution:

UPDATE Following muistooshort's comment, I deleted all records from the routes table and re-ran the code and it worked fine. It also worked fine when I ran it a second time after that. So I think this supports mu's theory that something is wrong with the existing records in the db (not my code). I think the better question now is what exactly are the circumstances in the DB that are causing this and how do I fix them?

Have you been manually assigning ids or doing something else that would get the id values out of sync with the sequence that supplies id values? — mu is too short
@muistooshort Certainly not intentionally, but perhaps that happened inadvertently? Can you elaborate slightly on what I should be looking for? Is the issue that Postgres will get fooled if a record gets a low ID number but has higher IDs already in the DB? Something like that? — emersonthis
@muistooshort I guess my question is what symptom should I be looking for in the DB to determine if your theory is the problem? — emersonthis
serial columns in PostgreSQL get their defaults from sequences. If you insert a new row with an explicit id then that id will be used but the sequence won't know about it so it will give you that value (when it gets to it) when the serial column asks for a default value (e.g.: sqlfiddle.com/#!15/17534/1). You can use setval to reset the sequence to match the ids in your table. — mu is too short

mu is too short mu is too short · Accepted Answer · 2016-04-07T06:27:23

The serial type in PostgreSQL is pretty simple: it is essentially an integer column whose default value comes from a sequence. But the sequence doesn't know what you're doing to the table so things can get confused if you specify a value for the serial without using or updating the sequence.

For example:

create table t (
  id serial not null primary key
);
insert into t (id) values (1);
insert into t (id) values (DEFAULT);

will produce a uniqueness violation because 1 was explicitly used for id but the sequence had no way of knowing that it was used.

Demo: http://sqlfiddle.com/#!15/17534/1

Presumably somewhere at sometime something added a row with id = 1328 without that id coming from the sequence. Or perhaps the sequence used for your PK's default was adjusted using setval to start returning values that were already in the table.

In any case, the easiest thing to do is adjust the sequence to match the table's current content using setval:

select setval('routes_id_seq', (select max(id) from routes));

The sequence should be called routes_id_seq but if it isn't, you can use \d routes inside psql to find out what its name is.

So if we update the previous example to this:

create table t (
  id serial not null primary key
);
insert into t (id) values (1);
select setval('t_id_seq', (select max(id) from t));
insert into t (id) values (DEFAULT);

then we'll get 1 and 2 in our table instead of 1 and an error.

Demo: http://sqlfiddle.com/#!15/17534/7

CakePHP: Unique violation: 7 ERROR: duplicate key value violates unique constraint

1 Answers