Início / dba / Perguntas / 62739
Accepted
Niels Kristian
Asked: 2014-04-09 00:01:38 +0800 CST

Como faço para validar todas as minhas restrições no postgresql?

  • 772

Parece que fui mordido pelo primeiro bug descrito nas notas de lançamento do postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html

Agora tenho, por exemplo, chaves primárias duplicadas. Qual é a melhor abordagem para fazer um loop de nova verificação de todas as minhas restrições (pkeys, fkeys) -> corrigir problemas -> verificar novamente, para garantir que meus dados estejam corretos?

ATUALIZAR

Decidi seguir uma abordagem para corrigir o problema, excluindo todas as restrições e, em seguida, recriando-as usando o seguinte método http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html . No entanto, estou preso em uma mensagem de erro, tentando recriar uma pkey:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

O que isso significa e como faço para corrigir isso (posso viver com a exclusão, se for uma opção)?

Outra pergunta: meus dados seriam realmente bons, se eu apenas me livrasse de todas as linhas duplicadas excluindo-as e, em seguida, fizesse um pg_dump e restaurasse o banco de dados desse despejo. Isso reconstruiria a estrutura de dados - certo?

postgresql foreign-key

3 respostas

  1. Best Answer

    Bem, se você precisar de uma maneira de verificar se todas as chaves estrangeiras em sua tabela são válidas, isso pode ajudar (apenas valida todas as chaves estrangeiras em seu esquema)

    do $$
  declare r record;
BEGIN 
FOR r IN  (
  SELECT FORMAT(
    'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc  
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE' 
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
  WHERE  constraint_type = 'FOREIGN KEY' 
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);  
  END LOOP;
END;
$$;
    • 7

  2. A solução proposta por Evan Carroll não estava funcionando para mim.

    Eu tive que adaptá-lo para marcar antes de todas as restrições como não válidas.

    do $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'UPDATE pg_constraint SET convalidated=false WHERE conname = ''%I''; ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.constraint_name,
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;
    • 6

  3. Isso funcionará em todas as restrições com segurança. ,

    SELECT FORMAT(
  'ALTER TABLE %I.%I.%I VALIDATE CONSTRAINT %I;',
  current_database(),
  nsp.nspname,
  cls.relname,
  con.conname
)                                         
FROM pg_constraint AS con
JOIN pg_class AS cls
  ON con.conrelid = cls.oid
JOIN pg_namespace AS nsp
  ON cls.relnamespace = nsp.oid
WHERE convalidated IS FALSE
  -- or delete it for all constraints in all schemas
  AND nsp.nspname = 'mySchema';

    Você pode salvar isso em um arquivo e fazer perguntas/respostas ou executá-lo de uma só vez se estiver usando psqlcom \gexec.

    • 4

relate perguntas