Início / dba / Perguntas / 91632
Accepted
John
Asked: 2015-02-11 13:27:49 +0800 CST

Tabela de cópia com sequências separadas para colunas seriais

  • 772

Ao criar uma ferramenta de gerenciamento de colunas, me deparei com a necessidade de replicar tabelas rapidamente no PostgreSQL para não testar novas ferramentas com tabelas que não são de teste. Para testar efetivamente a nova ferramenta de coluna que pretendo usar na tabela parts, criei essa nova ferramenta para duplicar parts, para terminar com uma parts1tabela. Quando pensei que finalmente tinha resolvido todos os problemas, encontrei o seguinte erro quando a ferramenta de coluna exclui a tabela:

ERRO: não é possível descartar partes da tabela porque outros objetos dependem dela DETALHE: padrão para o ID da coluna parts1 da tabela depende da sequência parts_id_seq1

Passei a maior parte do meu dia trabalhando nessa solução, então, resumindo, posso simplesmente usar funções de string para renomear a SEQUENCE_NAMEvariável para desassociar a partstabela da partstabela ou é um problema mais complicado do que isso? Aqui está a consulta:

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid 
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid 
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace 
    WHERE s.relkind = 'S' 
    AND n.nspname = 'public' 
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;
database-design postgresql

3 respostas

  1. Best Answer

    Para criar uma cópia o mais próxima possível, use INCLUDING ALLwith CREATE TABLE .. (LIKE ..), pois pode haver qualquer número de colunas com padrões que você obviamente deseja copiar.

    Você só quer serialque as colunas tenham sua própria sequência independente, o que faz muito sentido e provavelmente deveria ter sido o comportamento padrão para começar.

    O Postgres 10 "consertou" isso adicionando IDENTITYcolunas em conformidade com o padrão SQL, que possuem sequências internas exclusivamente dedicadas e se comportam conforme desejado com arquivos CREATE TABLE .. (LIKE ..). O manual:

    INCLUDING IDENTITY

    Quaisquer especificações de identidade de definições de coluna copiadas serão copiadas. Uma nova sequência é criada para cada coluna de identidade da nova tabela, separada das sequências associadas à tabela antiga .

    Ênfase em negrito minha. As colunas seriais existentes permanecem inalteradas. Considere a substituição serialde colunas. Ver:

    Função para copiar tabelas com (ou sem) serialcolunas

    Embora as serialcolunas ainda estejam envolvidas, esta função deve fazer o trabalho:

    Copia qualquer tabela fornecida (deve existir) com um novo nome fornecido e colunas independentesserial (se houver).
    Os dados não estão incluídos, é trivial copiá-los também.

    CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text, _newschema text = NULL)
  RETURNS void
  LANGUAGE plpgsql VOLATILE AS
$func$
DECLARE
   _newtbl_qual_n_quot text;
   _sql text;
BEGIN
   -- If target schema is not given, default to schema of source
   IF _newschema IS NULL THEN      
      SELECT INTO _newschema  relnamespace::regnamespace::text
      FROM   pg_class
      WHERE  oid = _tbl;
   END IF;
   
   _newtbl_qual_n_quot := format('%I.%I', _newschema, _newtbl);
   
   -- Copy table
   EXECUTE format('CREATE TABLE %s (LIKE %s INCLUDING ALL);', _newtbl_qual_n_quot, _tbl);

   -- Fix serial columns, if any
   SELECT INTO _sql
          string_agg('CREATE SEQUENCE ' || seq_qual_n_quot, E';\n') || E';\n'
       || string_agg(format('ALTER SEQUENCE %s OWNED BY %s.%I'
                           , seq_qual_n_quot, _newtbl_qual_n_quot, a.attname), E';\n') || E';\n'
       || 'ALTER TABLE ' || _newtbl_qual_n_quot || E'\n  '
       || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                          , a.attname, seq_qual_n_quot), E'\n, ')
   FROM   pg_attribute  a
   JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                          AND ad.adnum   = a.attnum
        , format('%I.%I'
               , _newschema
               , _newtbl || '_' || a.attname || '_seq'
                ) AS seq_qual_n_quot  -- new seq name, qualified & quoted where necessary
   WHERE  a.attrelid = _tbl
   AND    a.attnum > 0
   AND    NOT a.attisdropped
   AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
   AND    pg_get_expr(ad.adbin, ad.adrelid) = 'nextval('''
            || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
            || '''::regclass)'
   ;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;
END
$func$;

    Ligar:

    SELECT f_copy_tbl('tbl', 'tbl1');

    Ou:

    SELECT f_copy_tbl('myschema.tbl', 'CpY_tbl_odd_name', 'other_schema');

    Produz e executa o código SQL do formulário:

    CREATE TABLE tbl1 (LIKE tbl INCLUDING ALL);
-- only if there are serial columns:
CREATE SEQUENCE tbl1_tbl_id_seq;     -- one line per serial type ...
CREATE SEQUENCE "tbl1_Odd_COL_seq";  -- .. two in this example
ALTER SEQUENCE tbl1_tbl_id_seq OWNED BY tbl1.tbl_id;
ALTER SEQUENCE "tbl1_Odd_COL_seq" OWNED BY tbl1."Odd_COL";
ALTER TABLE tbl1
  ALTER tbl_id SET DEFAULT nextval('tbl1_tbl_id_seq'::regclass)
, ALTER "Odd_COL" SET DEFAULT nextval('"tbl1_Odd_COL_seq"'::regclass);

    • A fonte ( 1º parâmetro) deve ser uma tabela, visualização, visualização materializada, tipo composto ou tabela externa. Opcionalmente qualificado pelo esquema.

    • O segundo parâmetro é o novo nome da tabela.

    • O terceiro parâmetro é o esquema da nova tabela. Se não for fornecido, o padrão é o esquema da fonte.

    • A coluna do sistema pg_attrdef.adsrcfoi descartada no Postgres 12. Usar pg_get_expr(ad.adbin, ad.adrelid)em vez disso conforme instruído no manual Funciona em versões anteriores também.

    • Somente serialas colunas obtêm sua própria sequência. Outros padrões de coluna são copiados inalterados - inclusive nextval()de uma sequência que não pertence à coluna ou difere de alguma forma de um arquivo serial.

    • A função é segura contra injeção de SQL e deve funcionar com tabelas arbitrárias e nomes de colunas.

    db<>fiddle aqui
    Old sqlfiddle

    • 6

  2. O problema é que sua tabela peças1 usa a sequência da tabela de peças (ao invés de sua própria sequência)... assim ele reclama que você não pode descartar a tabela de peças porque a tabela de peças1 depende dela (via valor padrão para um dos as colunas)... Você está obtendo esse comportamento em como está criando a tabela parts1...

    CRIAR TABELA - veja a LIKE source_table [ like_option ... ]seção

    Você pode obter os resultados desejados criando a tabela como você fez e, em seguida, criando novas sequências, definindo novos valores padrão e criando novas dependências para cada coluna SERIAL (já que o comportamento da opção LIKE aparentemente não cria suas próprias sequências a partir de desta vez).

    As etapas para realizar o acima são fornecidas na resposta de Deszo aqui

    (copiei por comodidade.. alterei o passo 2 dele já terá uma tabela criada)

    CREATE SEQUENCE tablename_colname_seq; -- step 1
ALTER TABLE tablename ALTER COLUMN colname SET DEFAULT nextval('tablename_colname_seq') -- step 2
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; -- step 3

    Você também pode usar este SQL para ajudar a identificar quais colunas pertencem à pkey para a tabela em questão, embora isso não ajude a identificar TODAS as colunas seriais (se uma não for uma pkey).

    select
  sch.nspname as schema_name,
  tab.relname as table_name,
  col.attname as column_name,
  pk.conname as pkey
from pg_constraint pk
join pg_class tab on pk.conrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
join pg_attribute col on tab.oid = col.attrelid and pk.conkey @> ARRAY[col.attnum]
where pk.contype = 'p' and sch.nspname = '<your schema>' and tab.relname = '<your table>';

    Consulte a resposta de Erwin Brandstetter para (suponho) uma solução mais completa.

    • 4

  3. Eu queria liberar pelo menos a função PHP direta (eventualmente lançarei as ferramentas que estou construindo para algo como o Github), embora ainda não tenha tempo para fazer muito mais do que postar aqui, pelo menos. Espero que isso economize o tempo dos outros e sinta-se à vontade para sugerir melhorias. Não tenho certeza de quão seguro é o código (é apenas uma ferramenta que estou usando apenas para desenvolvimento e atualmente para um cliente que estou configurando um site somente para intranet), embora tenha feito o possível para seguir o conselho do outras perguntas/respostas de Erwin Brandstetter e Joishi Bodio.

    Exemplo de solicitação de URL:

    db_table_duplicate.php?db=database_name&table1=parts2&table2=parts3

    Eu simplesmente tenho funções de chamada PHP e, como frequentemente repito as ações que minhas ferramentas PostgreSQL executam, simplesmente armazeno as informações em uma $_GETmatriz.

    function dbtd_04_commit()
{
 echo '<p><strong>Step 4/4:</strong> Duplicating the table `'.$_GET['table1'].'`...</p>'."\n";
 $table2 = pg_escape_string($_SESSION['db'],$_GET['table2']);
 $query1 = "SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name='$table2';";
 $result1 = pg_query($_SESSION['db'],$query1);

 if ($result1)
 {
  $count1 = pg_num_rows($result1);
  $table1 = pg_escape_string($_SESSION['db'],$_GET['table1']);

  if ($count1>0)
  {
   echo '<div><p><strong class="error">Error:</strong> the table '.$table2.' already exists; the table '.$table1.' was <em>not</em> duplicated. Would you like to <a href="tools/'.$GLOBALS['paths']->file.'?db='.$_GET['db'].'&#38;table1='.$_GET['table1'].'">try a different name</a>?</p></div>'."\n";
  }
  else
  {//Check for custom table-structure duplication function.
   $query2 = "SELECT p.proname FROM pg_catalog.pg_namespace AS n JOIN pg_catalog.pg_proc AS p ON p.pronamespace=n.oid WHERE n.nspname='public' AND proname='f_copy_tbl';";
   $result2 = pg_query($_SESSION['db'],$query2);

   if ($result2)
   {
    $count2 = pg_num_rows($result2);

    if ($count2==0)
    {
     $query3 = "CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text) RETURNS void AS $func$"."\n";
     $query3 .= "DECLARE _sql text;"."\n";
     $query3 .= "BEGIN"."\n";
     //-- Copy table
     $query3 .= "EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);"."\n";
     //-- Fix serial columns, if any
     $query3 .= "SELECT INTO _sql"."\n";
     $query3 .= " string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'"."\n";
     $query3 .= " || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'"."\n";
     $query3 .= " , seq, _newtbl, a.attname), E';\n') || E';\n'"."\n";
     $query3 .= " || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n  '"."\n";
     $query3 .= " || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$"."\n";
     $query3 .= " , a.attname, seq), E'\n, ')"."\n";
     $query3 .= "FROM pg_attribute a"."\n";
     $query3 .= "INNER JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum, quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq"."\n";// -- new seq name
     $query3 .= "WHERE a.attrelid = _tbl"."\n";
     $query3 .= "AND a.attnum > 0"."\n";
     $query3 .= "AND NOT a.attisdropped"."\n";
     $query3 .= "AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])"."\n";
     $query3 .= "AND ad.adsrc = 'nextval('''"."\n";
     $query3 .= " || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass"."\n";
     $query3 .= " || '''::regclass)';"."\n";
     $query3 .= "IF _sql IS NOT NULL THEN"."\n";
     $query3 .= " EXECUTE _sql;"."\n";
     $query3 .= "END IF;"."\n";
     $query3 .= "END"."\n";
     $query3 .= "$func$  LANGUAGE plpgsql VOLATILE;"."\n";
     $result3 = pg_query($_SESSION['db'],$query3);

     if ($result3) {}
     else {sql_error_report($query3,pg_last_error($_SESSION['db']),__FUNCTION__);}
    }

    $query4 = "SELECT f_copy_tbl('$table1', '$table2');";
    $result4 = pg_query($_SESSION['db'],$query4);

    if ($result4)
    {
     $query5 = 'DO'."\n";
     $query5 .= '$do$'."\n";
     $query5 .= 'BEGIN'."\n";
     $query5 .= ' INSERT INTO '.$table2.' SELECT * FROM '.$table1.';'."\n";
     $query5 .= ' EXECUTE ('."\n";
     $query5 .= ' SELECT format($$SELECT setval('."'%s'::regclass, max(%I)) FROM %s".'$$, pg_get_serial_sequence(a.attrelid::regclass::text, a.attname), a.attname, a.attrelid::regclass'."\n";
     $query5 .= ' )'."\n";
     $query5 .= ' FROM pg_index i'."\n";
     $query5 .= ' JOIN pg_attribute a ON a.attrelid = i.indrelid'."\n";
     $query5 .= '  AND a.attnum = i.indkey[0]'."\n";
     $query5 .= " WHERE i.indrelid = '".$table2."'::regclass"."\n";
     $query5 .= ' AND i.indisprimary'."\n";
     $query5 .= ' );'."\n";
     $query5 .= 'END'."\n";
     $query5 .= '$do$ LANGUAGE plpgsql;';
     $result5 = pg_query($_SESSION['db'],$query5);

     if ($result5)
     {
      $query6 = "VACUUM $table2;";
      $result6 = pg_query($_SESSION['db'],$query6);

      if ($result6)
      {
       echo '<div><p><strong>SUCCESS!</strong> The table '.$table1.' has been duplicated.</p></div>'."\n";
      }
      else {sql_error_report($query6,pg_last_error($_SESSION['db']),__FUNCTION__);}
     }
     else {sql_error_report($query5,pg_last_error($_SESSION['db']),__FUNCTION__);}
    }
    else {sql_error_report($query4,pg_last_error($_SESSION['db']),__FUNCTION__);}
   }
   else {sql_error_report($query2,pg_last_error($_SESSION['db']),__FUNCTION__);}
  }
 }
 else {sql_error_report($query1,pg_last_error($_SESSION['db']),__FUNCTION__);}
}
    • 1

relate perguntas