Codeception / Codeception/module-db

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

Offen
#54 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
Vorherrschende Sprache
PHP
Sterne
23
Forks
31
PR-Merge-Kennzahlen
Keine gemergten PRs in 30 T.

Beschreibung

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)
```sql
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
```
image

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`
```php
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::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
```sql
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?

Beitragsleitfaden

Für dieses Repository ist kein Beitragsleitfaden indexiert

Bewertung

Dieses Issue wurde noch nicht bewertet.

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.