PDOException: 'SQLSTATE[55000]' on insert (lastInsertId)

[James-A1]

Hi guys, I'm using postgres with uuid's as primary keys, not auto incremental keys. When I try to insert using pixie, I'm getting the error:

PDOException with message 'SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval is not yet defined in this session'

It does insert the record, however I get this error afterwards. The error is being caused by trying to get the last insert id from pdo:

//Line:327@Pixie/QueryBuilder/QueryBuilderHandler.php
            $return = $result->rowCount() === 1 ? $this->pdo->lastInsertId() : null;

PDO is apparently trying to call lastval but it causes an error since there is no sequence defined, i.e, no auto increment of keys.

Reading up on the issue, when dealing with last inserted id's, the TLDR from this stackoverflow post suggests that returning id_column is appended to the end of the query. Using the returning query is also the strategy used by Eloquent.

Would it be viable to have the query builder be aware of the database driver so we can apply driver-specific code? Or have a grammar instead?

[TCB13]

Hi @James-A1 many thanks for your feedback and research on the issue. I never used postgres in my life but this issue seems to the same reported in #146, potentially fixed by PR #147 . Can you test the PR?

[James-A1]

Hi @TCB13 , I took a look at the code in the PR. It allows you to define a sequence to defer to, which is great if you have sequenced indices like auto incremented id's. In our case it won't work if you are not using them.