Início / dba / Perguntas / 159372
Accepted
Christian Bongiorno
Asked: 2016-12-29 12:19:25 +0800 CST

gatilho de partição postgres e verificação de tabela filha

  • 772

Encontrei um código de amostra para criar uma tabela de partições.

Basicamente, configure a tabela mestre e particione no campo de data concatenado com o tipo de entidade que é então, herde do mestre.

A primeira chamada do gatilho é boa, mas as subseqüentes falham porque a condição 'if' para verificar uma tabela filho existente resulta em falso, mas ao tentar criar a tabela falha para uma tabela já existente.

Eu tentei 3 maneiras de verificar a existência de tabelas filhas e nenhuma fornece respostas corretas.

Estas 2 declarações demonstram a contradição:

SELECT count(*) FROM pg_class WHERE relname='DRIVER_2016_12_28';
count(*)
0

select count(*) from DRIVER_2016_12_28;
count(*)
1

Tabela principal:

CREATE TABLE partition_test
(
  pk SERIAL PRIMARY KEY NOT NULL,
  id VARCHAR(36) NOT NULL,
  organizationid VARCHAR(36) NOT NULL,
  lat REAL NOT NULL,
  lon REAL NOT NULL,
  basetype VARCHAR(16) NOT NULL,
  name VARCHAR(64) NOT NULL,
  updatetimestamp TIMESTAMP NOT NULL
) WITHOUT OIDS;

código de gatilho:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE ' || partition || ' (check (updatetimestamp = ''' || NEW.updatetimestamp || ''')) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %',partition;
  END IF;
  EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER telemetry_partition_insert_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE telemetry_insert_trigger();

inserir exemplo:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

primeira saída

[2016-12-28 12:15:12] [00000] Uma partição foi criada DRIVER_2016_12_28 [2016-12-28 12:15:12] concluída em 33ms

segundo:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

[2016-12-28 12:16:15] [42P07] ERRO: relação "driver_2016_12_28" já existe [2016-12-28 12:16:15] Onde: instrução SQL "CREATE TABLE DRIVER_2016_12_28 (check (updatetimestamp = '2016 -12-28 12:16:15.467012')) INHERITS (partition_test);" [2016-12-28 12:16:15] Função PL/pgSQL telemetry_insert_trigger() linha 11 na instrução EXECUTE

A pergunta : Como posso verificar efetivamente a existência da tabela filha para não receber esse erro? Se você gostaria de apontar problemas existentes com meu gatilho, isso também seria ótimo

postgresql trigger

2 respostas

  1. Best Answer

    Aconselho usar sempre quote_identao gerar sentenças dinâmicas, para garantir que seus idents não tenham problemas com maiúsculas/minúsculas, ou que você possa usar identificadores com espaços ou caracteres especiais, e que você evite injeção de SQL se seus idents forem parâmetros fora de seu controle.

    Ou seja, sua função de gatilho deve se parecer com:

    CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
  start_ts TIMESTAMP ;
  end_ts TIMESTAMP ;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;
  start_ts := partition_date::timestamp ;
  end_ts := start_ts + interval '1 day' ;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE '    || quote_ident(partition) 
        || ' (check (updatetimestamp >= ''' || start_ts || ''' ' 
        || '     AND updatetimestamp <  ''' || end_ts || ''')) ' 
        || ' INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %', partition;
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

    Verifique um exemplo de uso para quote_literal na documentação do PostgreSQL .

    De preferência, prefiro ter os nomes de todas as tabelas em letras minúsculas e sem caracteres especiais (para não ter "Table Names with Double Quotes", mas table_names_without_quotes), pois acho o código menos ruidoso e mais legível. Mas, novamente, isso é uma questão de gosto.

    • 1

  2. Como observação, você está particionando na data. Um patch já foi confirmado para 9.7 que torna isso muito mais fácil com o particionamento declarativo. Você pode considerar construir o devel e esperar por um lançamento em 2017.

    Dos documentos..

    CREATE TABLE measurement (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
    • -1

relate perguntas