[DB error] PostgreSQL, for tables without sequences get "Undefined table: ERROR: relation '%_seq' does not exist"

[annechko]

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?

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

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?