Codeception / Codeception/module-db

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

Ouverte
#54 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
Langage dominant
PHP
Étoiles
23
Forks
31
Métriques de merge des PR
Aucune PR mergée en 30 j

Description

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?

Guide de contribution

Aucun guide de contribution indexé pour ce dépôt

Évaluation

Cette issue n'a pas encore été évaluée.

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.