Hi, found this little issue, I get an error when insert a row into a table that has NO sequence, one possible use case - it could happen with UUID as primary key that are generated on an application side, so it's not completely imaginary case :)
I'm using postgres:15, codeception/module-db looks like version 3.1.0,
and for example I have a table (for simplicity, integer primary key, no sequence)
create table t
(
c integer not null primary key
);
Then I call \Codeception\Module\Db::haveInDatabase ($this->tester->haveInDatabase('t', ['c' => 1]);) in my test and see in the console
[DB error] SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "t_c_seq" does not exist
Row is inserted, but after it is trying to extract last inserted ID and (cause it's not possible) it fails.
Happens here in \Codeception\Lib\Driver\PostgreSql::lastInsertId
public function lastInsertId(string $tableName): string
{
$sequenceName = $this->getQuotedName($tableName . '_id_seq');
$lastSequence = null;
try {
$lastSequence = $this->getDbh()->lastInsertId($sequenceName);
} catch (PDOException $exception) {
// in this case, the sequence name might be combined with the primary key name
}
if (!$lastSequence) {
$primaryKeys = $this->getPrimaryKey($tableName);
$pkName = array_shift($primaryKeys);
// next line we get an error
$lastSequence = $this->getDbh()->lastInsertId($this->getQuotedName($tableName . '_' . $pkName . '_seq'));
}
return $lastSequence;
}
And I guess technically that is not an error, because we never should be checking for a last inserted ID in those cases.
Not really sure how to better handle this case, maybe it is possible to add some additional checks before calling \Codeception\Lib\Driver\Db::lastInsertId, trying to detect if a sequence for the given table even exists. If there are no sequence - no need to call \Codeception\Lib\Driver\Db::lastInsertIdmaybe?
SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S';
Maybe it is possible to filter by table and if there is no sequences - return 0 or smth like that. But I'm not sure how to handle different postgresql's version issues - if there's any...
Or add a custom exception and throw it in the lastInsertId method, checking sequence's existence before calling \PDO::lastInsertId. Don't like that one though) But the names for a sequence are building and checking inside this method..
Would be happy to discuss or just hear your thoughts about it, and if I'm lucky even make MR :)
I could try to provide a test that covers that case and fails?
Hi, found this little issue, I get an error when insert a row into a table that has NO sequence, one possible use case - it could happen with UUID as primary key that are generated on an application side, so it's not completely imaginary case :)
I'm using
postgres:15
,codeception/module-db
looks like version 3.1.0, and for example I have a table (for simplicity, integer primary key, no sequence)Then I call
\Codeception\Module\Db::haveInDatabase
($this->tester->haveInDatabase('t', ['c' => 1]);
) in my test and see in the consoleRow is inserted, but after it is trying to extract last inserted ID and (cause it's not possible) it fails.
Happens here in
\Codeception\Lib\Driver\PostgreSql::lastInsertId
And I guess technically that is not an error, because we never should be checking for a last inserted ID in those cases.
Not really sure how to better handle this case, maybe it is possible to add some additional checks before calling
\Codeception\Lib\Driver\Db::lastInsertId
, trying to detect if a sequence for the given table even exists. If there are no sequence - no need to call\Codeception\Lib\Driver\Db::lastInsertId
maybe?In that case I found this monster here https://dba.stackexchange.com/questions/260975/postgresql-how-can-i-list-the-tables-to-which-a-sequence-belongs
Maybe it is possible to filter by table and if there is no sequences - return 0 or smth like that. But I'm not sure how to handle different postgresql's version issues - if there's any...
Or add a custom exception and throw it in the
lastInsertId
method, checking sequence's existence before calling\PDO::lastInsertId
. Don't like that one though) But the names for a sequence are building and checking inside this method..Would be happy to discuss or just hear your thoughts about it, and if I'm lucky even make MR :) I could try to provide a test that covers that case and fails?