Início / dba / Perguntas / 153981
Accepted
Andy
Asked: 2016-11-02 21:41:59 +0800 CST

Queda de colunas do PostgreSQL 9.6 e efeitos colaterais em funções SQL com CTEs

  • 772

Se eu tivesse uma tabela com 3 colunas - digamos A, B e D - e tivesse que introduzir uma nova - digamos C para substituir a posição atual de D. Eu usaria o seguinte método:

  1. Introduzir 2 novas colunas como C e D2.
  2. Copie o conteúdo de D para D2.
  3. Excluir D.
  4. Renomeie D2 para D.

A nova ordem seria A, B, C e D.

Eu pensei que esta era uma prática legítima, pois (até agora) não produziu problemas.

Porém, hoje me deparei com um problema quando uma função realizando um comando na mesma tabela retornou o seguinte erro:

table row type and query-specified row type do not match

E o seguinte detalhe:

Query provides a value for a dropped column at ordinal position 13

Tentei reiniciar o PostgreSQL, fazer um VACUUM FULLe finalmente excluir e recriar a função conforme sugerido aqui e aqui, mas essas soluções não funcionaram (além do fato de tentarem resolver uma situação em que uma tabela do sistema foi alterada).

Tendo o luxo de trabalhar com um banco de dados muito pequeno, exportei-o, excluí-o e importei-o novamente e isso corrigiu o problema com minha função.

Eu estava ciente do fato de que não se deve mexer com a ordem natural das colunas modificando as tabelas do sistema (sujando as mãos com pg_attribute, etc.) como visto aqui:

É possível alterar a ordem natural das colunas no Postgres?

A julgar pelo erro lançado pela minha função, agora percebo que mudar a ordem das colunas com meu método também é um não-não. Alguém pode esclarecer por que o que estou fazendo também está errado?

A versão do Postgres é 9.6.0.

Aqui está a função:

CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '

-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),

-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
authentication_date = current_timestamp,
facebook_id = facebookid
WHERE EXISTS (SELECT * FROM select_user)
AND
useremail = users.email
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id),

-- Third, insert the user (if user does not exist):
insert_user AS
(INSERT INTO
users (authentication_code, authentication_date, email, key, name, facebook_id)
SELECT
GEN_RANDOM_UUID(),
current_timestamp,
useremail,
GEN_RANDOM_UUID(),
COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
facebookid
WHERE NOT EXISTS (SELECT * FROM select_user)
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id)

-- Finally, select the authentication code, ID, key and Stripe ID:
SELECT
*
FROM
update_user
UNION ALL
SELECT
*
FROM
insert_user' LANGUAGE "sql" COST 100 ROWS 1
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

Realizei a renomeação/reordenação em ambas as colunas facebook_ide stripe_id(uma nova coluna foi adicionada antes delas, que é o motivo da renomeação, mas não é tocada por esta consulta).

Ter as colunas em uma determinada ordem é puramente interessante para a ordem. No entanto, o motivo de fazer essa pergunta é a preocupação de que uma simples renomeação e exclusão de uma coluna possa desencadear problemas reais para alguém que usa funções no modo de produção (como aconteceu comigo).

postgresql functions

4 respostas

  1. Best Answer

    Provável bug em 9.6 e 9.6.1

    Isso parece completamente um bug para mim ...

    Não sei por que isso acontece, mas posso confirmar que acontece. Esta é a configuração mais simples encontrada que reproduz o problema (na versão 9.6.0 e 9.6.1).

    CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    column_that_we_will_drop TEXT
) ;

-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
    (_useremail text) 
RETURNS integer AS
$$
-- Need a CTE to produce the error. A 'constant' one suffices.
WITH something_even_if_useless(a) AS
(
    VALUES (1)
)
UPDATE
    users
SET
    id = id
WHERE 
    -- The CTE needs to be referenced, if the next
    -- condition were not in place, the problem is not reproduced
    EXISTS (SELECT * FROM something_even_if_useless)
    AND email = _useremail
RETURNING
    id
$$
LANGUAGE "sql" ;

    Após esta configuração, a próxima instrução simplesmente funciona

    SELECT * FROM __post_users('[email protected]');

    Neste ponto, DROP uma coluna:

    ALTER TABLE users 
    DROP COLUMN column_that_we_will_drop ;

    Essa alteração faz com que a próxima instrução gere um erro

    SELECT * FROM __post_users('[email protected]');

    que é o mesmo mencionado por @Andy:

    ERROR: table row type and query-specified row type do not match
SQL state: 42804
Detail: Query provides a value for a dropped column at ordinal position 3.
Context: SQL function "__post_users" statement 1
    SELECT * FROM __post_users('[email protected]');

    Soltar e recriar a função NÃO resolve o problema.
    VACUUM FULL (a tabela ou todo o banco de dados) não resolve o problema.

    O relatório do bug foi passado para a lista de discussão apropriada do PostgreSQL e tivemos uma resposta muito rápida :

    Não consigo reproduzir isso no HEAD ou no branch tip 9.6. Acredito que já foi corrigido por este patch, que saiu um pouco depois do 9.6.1:

    https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22

    Mas obrigado pelo relato!

    cumprimentos, tom lane

    Versão 9.6.2

    Em 2017-03-06, posso confirmar que não consigo reproduzir esse comportamento na versão 9.6.2. Ou seja, o bug parece ter sido corrigido nesta versão.

    ATUALIZAR

    Por comentário de @Jana: "Posso confirmar que o bug está presente em 9.6.1 e foi corrigido em 9.6.2. A correção também está listada no site de lançamento do postgres : Corrija erros espúrios de "consulta fornece um valor para uma coluna descartada" durante INSERT ou UPDATE em uma tabela com uma coluna descartada"

    • 16

  2. Eu sei que você provavelmente já ouviu isso antes, mas é uma ideia horrível.

    • A ordem lógica só afeta coisas comoSELECT *
    • O efeito da ordenação lógica é limitado à aparência.

    Portanto, se não importar nada não o dissuade e reconhecemos que estamos apenas brincando com o Photoshop com estrutura de linha e obcecados com a exibição, vamos explicar mais algumas coisas.

    • A ordem lógica não existe no PostgreSQL
    • O pedido físico tem benefícios reais (empacotamento de mesa)
    • O PostgreSQL não oferece nenhum controle sobre a ordem física depois CREATE TABLE(embora seja uma prioridade muito maior)

    Portanto, o PostgreSQL é uma camada de exibição ruim. Além de tudo isso, embora ALTERfuncione bem, você não deve tentar o dragão. Ambos ALTER TABLE, e a seção CAVEAT mencionam isso,

    Alguns comandos DDL, apenas atualmente TRUNCATEe as formas de reescrita de tabela de ALTER TABLE, não são seguros para MVCC. Isso significa que, após o truncamento ou as confirmações de reescrita, a tabela aparecerá vazia para transações simultâneas, se elas estiverem usando um instantâneo obtido antes do comando DDL confirmado. Isso será um problema apenas para uma transação que não acessou a tabela em questão antes do início do comando DDL — qualquer transação que tenha feito isso manteria pelo menos um bloqueio de tabela ACCESS SHARE, que bloquearia o comando DDL até que a transação fosse concluída. Portanto, esses comandos não causarão nenhuma inconsistência aparente no conteúdo da tabela para consultas sucessivas na tabela de destino, mas podem causar inconsistência visível entre o conteúdo da tabela de destino e outras tabelas no banco de dados.

    E, se tudo isso não for suficiente, e você ainda quiser fingir que esta é uma boa ideia, em vez de uma ideia horrível. Então tente isso,

    1. Despeje a mesa compg_dump -t
    2. Reordene as colunas manualmente no dump.
    3. Carregue o material em uma tabela TEMP.
    4. BEGINuma transação
    5. DROPa velha mesa inteiramente,
    6. RENAMEa tabela temporária para a tabela prod.
    7. COMMIT

    Se tudo isso parecer excessivo, lembre-se de que atualizar linhas no banco de dados requer reescrever as linhas (supondo que não sejam TOAST . Você está tendo que analisar os dados e reconstruir o esquema da tabela, mas de qualquer forma você tem que reescrever a linha. Se eu tivesse que fazer esta tarefa, é assim que eu faria.

    Mas, tudo isso falando de forma geral. Ninguém reproduziu seus resultados.

    Você deu um caso de teste que não podemos executar

    ERROR:  column users.authentication_code does not exist
LINE 24: users.authentication_code,

    E você não nos disse a versão exata em que está.

    • 0

  3. Contornei esse bug fazendo backup e restaurando meu banco de dados.

    Passos para Heroku

    • Ative o modo de manutenção:heroku maintenance:on
    • Banco de dados de backup:heroku pg:backups:capture
    • Restaurar banco de dados:heroku pg:backups:restore
    • Reinicie o aplicativo:heroku restart
    • Desligue o modo de manutenção:heroku maintenance:off
    • 0

  4. Também me deparei com esse bug. Para aqueles que não desejam fazer backup/restaurar totalmente seu banco de dados. Saiba que simplesmente copiar a tabela funciona. Porém, não existe uma maneira "mágica" de copiar uma tabela. Eu fiz isso usando:

    SELECT * INTO mytable_copy FROM mytable;
ALTER TABLE mytable RENAME TO mytable_backup; -- just in case. you never know
ALTER TABLE mytable_copy RENAME TO mytable;

    Depois disso, você ainda precisará recriar manualmente seus índices, chaves estrangeiras e padrões. Recriar a tabela dessa forma fez com que o bug desaparecesse.

    • 0

relate perguntas