Hacktoberfest 2026: as issues que os mantenedores marcaram para outubro, abertas e boas para iniciantes. Ver issues do Hacktoberfest

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

Aberta
  • Algo errado?
#54 0 comentários 0 reações 0 responsáveis Ver no GitHub

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
3/5
Tempo estimado
1-2 dias
Facilidade para iniciantes
45/100
Tipo de issue
Bug
Clareza
Razoavelmente clara
Status de atividade
Estagnada
Stack de tecnologia
php, postgresql
Domínio
database

Direção de pesquisa

Comece em Codeception\Lib\Driver\PostgreSql::lastInsertId e, em seguida, rastreie como Db::haveInDatabase o utiliza para o driver PostgreSQL. Reproduza o exemplo com uma tabela que tenha uma chave primária inteira, mas nenhuma sequência, e adicione cobertura mostrando que a inserção é concluída sem o erro undefined-relation quando nenhuma sequência existe.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

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
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

    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?

Linguagem predominante
PHP
Estrelas
23
Forks
31
Nenhum PR com merge em 30d

Preparar o ambiente

  • Inclui um Dockerfile ou arquivo Docker Compose
  • Sem modelo de pull request
  • Sem guia de contribuição

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de Codeception/module-db

Todas as issues de Codeception/module-db

Issues semelhantes

Mais issues de PHP

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.