Issue: Errors when inserting explicit ID in Postgresql table with auto-generated primary key
Author: elgow
State: closed
Created: 2015-03-17 23:34:21

Postgresql behavior differs from Sqlite and Mysql for auto-generated keys. While Sqlite and Mysql handle it seamlessly, Postgresql will fail in two ways if an insert with an explicitly specified key value is made in a table with an auto-generated key. 

1) If the explicit key insert is the first insert in a session, the insert will fail with an error of "currval of sequence "pk_sequence_name" is not yet defined in this session". 

2) The key generation sequence is not updated by the explicit key insert so, unless the update is done, the sequence is guaranteed to produce a duplicate key at some point in the future. 

Problem 2 poses problems to fix in a transparent way, though it could be done. Problem 1 can be easily fixed through use of the Postgresql "returning" clause in the explicit key insert SQL statement instead of a  call to currval() on the sequence. 

While auto-generated keys would seem to preclude it, explicit key insertion often happens when restoring databases from saved or imported data, and when loading test data. 


I think this is going to be a wontfix for me. If you are relying on specific autogenerated primary key values in your tests, that seems problematic -- perhaps they should have a separate unique identifier. Similarly, when restoring backups, I don't imagine you would do that using peewee but use postgresql pg_restore instead.


Hello Charles,

I am relying on peewee to import data because I want to be able to use either Postgresql or Sqlite underneath my application. I thought that portability was one of the main value propositions of peewee.

More importantly, the way that peewee gets the id of inserted records is non-standard for Postgresql. Since version 8.2 Postgresql has had the INSERT ... RETURNING clause.  With that clause Postgresql will support inserting either an implicit or explicit key into an auto-increment column and capturing it without generating an error. Peewee will blow up. Two of the DBs supported by peewee work flawlessly to support that usage pattern, and I don't think it's an accident that they do. Peewee could make it so that all three worked properly.  By not using the RETURNING clause, peewee creates a problem for the user and also degrades the performance of every insert statement by requiring a second query to retrieve the key.

As I said in the issue, the integrity of the sequence value is trickier, and it could be left to the user to handle with an explicit update to the sequence. I hope, though, that you will reconsider at least switching to using RETURNING clause for inserts in Postgresql.

Thanks,

```
 Ed
```

On Wed, 18 Mar 2015, Charles Leifer wrote:

> I think this is going to be a wontfix for me. If you are relying on specific autogenerated primary key values in your tests, that seems problematic -- perhaps they should have a separate unique identifier. Similarly, when restoring
> backups, I don't imagine you would do that using peewee but use postgresql pg_restore instead.
> 
> —
> Reply to this email directly or view it on GitHub.[AK_suUhbUTiZFbmk5zpNl_SCzHs5T0iOks5n2Y23gaJpZM4DwV3M.gif]


Not only will the INSERT...RETURNING feature in 2.5.0 speed up inserts in Postgresql, but it also fixes the explicit key insert issue. 

Thank you very much for this fix. 

   Ed


Sure thing, thanks for suggesting I implement it! It definitely needed to happen :)