Bruno's questions

Estou utilizando o PostgreSQL (8.4) para armazenar dados produzidos por uma aplicação que faz inserções frequentes (na estrutura da tabela descrita abaixo).

O banco de dados continua crescendo com o tempo e, como os dados mais recentes são mais relevantes do que os dados mais antigos (neste aplicativo específico), excluir as linhas mais antigas é uma solução razoável (com base em lower idou old input_datetime, que é mais ou menos o mesmo) .

Para evitar que problemas relacionados a este banco de dados (o único banco de dados executado neste servidor) afetem o restante do sistema, coloquei o diretório de dados do PostgreSQL em sua própria partição (ext3, em um sistema Linux). No entanto, quando essa partição fica cheia, isso causa vários problemas.

Estou pensando em excluir dados antigos regularmente (por exemplo, por DELETE FROM data_group WHERE id <= ...meio de um trabalho cron) para lidar com isso.

Em primeiro lugar, meu entendimento VACUUM(conforme realizado pelo vácuo automático, que está ativado) é que, embora não devolva necessariamente o espaço em disco ao sistema operacional (como VACUUM FULLfaria), ele ainda permite que alguns novos dados sejam inseridos no espaço em disco já utilizado (ou seja, os DELETEs não afetam necessariamente o tamanho do arquivo, mas ainda liberam espaço nas próprias estruturas de dados do PostgreSQL). Isso está correto? (Percebi que VACUUM FULLcausou alguns problemas com o próprio aplicativo, provavelmente por causa dos bloqueios que ele usa.)

Nesse caso, também parece que SELECT pg_database_size('my_database')reflete o tamanho usado no disco, o que não necessariamente reflete o que está disponível para inserções posteriores. Existe outra maneira de estimar quanto espaço está disponível para novas inserções?

Além disso, quando é tarde demais e a partição está preenchida em 100%, executar esta DELETEinstrução causa este erro e trava o serviço PostgreSQL:

PANIC: não foi possível gravar no arquivo "pg_xlog/xlogtemp.7810": não há mais espaço no dispositivo

A parada do daemon do PostgreSQL é obviamente um grande problema (e não há outro disco para onde mover o cluster nesta máquina).

Existem estratégias gerais para evitar que esse tipo de problema ocorra (sabendo que o espaço em disco é limitado em uma determinada partição, mas que pode ser aceitável excluir dados mais antigos)? Eu gostaria de automatizar o máximo possível, sem rootou postgres(ou intervenção do administrador do PostgreSQL).

CREATE TABLE data_group (
    id SERIAL PRIMARY KEY,
    name TEXT,
    input_datetime TIMESTAMPTZ
);

CREATE TABLE data_item (
    id SERIAL PRIMARY KEY,
    group_id INTEGER NOT NULL REFERENCES data_group(id) ON DELETE CASCADE ON UPDATE CASCADE,
    position INTEGER NOT NULL,
    data BYTEA
);

Estou usando o PostgreSQL 8.4.15. Ao executar pg_dumpo backup de um banco de dados, recebi o seguinte erro:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 123456789 in pg_toast_987654321
pg_dump: The command was: COPY public.my_table (id, .... all the columns ...)

Ao procurar por essa mensagem de erro, encontrei algumas referências ( aqui e aqui ) que sugeriam reindexar a tabela. (Nessas discussões, havia uma referência a consultar a pg_classtabela para encontrar o pg_toast_XXXXXXvalor correto, mas parecia que era porque não era exibido nas mensagens de erro. Pulei esta parte porque tinha um valor exibido na mensagem de erro . Acho que pode ser uma conveniência devido a uma versão posterior do PostgreSQL.)

Eu corri o seguinte:

REINDEX table pg_toast.pg_toast_987654321;
VACUUM ANALYZE my_table;

Agora consigo usar pg_dumpsem erros.

O que é pg_toaste o que esses comandos realmente fazem? Trata-se apenas de uma limpeza simples ou eles poderiam ter se livrado de algumas linhas nessa tabela? O que poderia ter causado o problema em primeiro lugar?

Existem cerca de 300.000 linhas nesta tabela, mas eu esperaria que houvesse apenas cerca de 250 novas linhas desde o backup anterior bem-sucedido (esta tabela é usada apenas para INSERT/SELECT, sem UPDATEs).

Utilizando o PostgreSQL 8.4, tenho uma série de tabelas que possuem uma estrutura muito parecida, mas que pertencem a categorias diferentes:

CREATE TABLE table_a (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_a REAL
);

CREATE TABLE table_b (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_b REAL
);

CREATE TABLE table_c (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE NOT NULL,
    value_c REAL
);

Preciso vincular esses valores a uma tabela central (usando joins ou sub-selects dependendo da consulta):

CREATE TABLE periods_table (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    category TEXT NOT NULL
);

Aqui, categoryé um de 'Category A', 'Category B'ou 'Category C'.

Para abstrair as semelhanças entre as tabelas A, B e C, criei uma view:

CREATE VIEW table_values AS
    SELECT 'Category A' AS category, event_time, value_a AS value
        FROM table_a
    UNION
    SELECT 'Category B' AS category, event_time, value_b AS value
        FROM table_b
    UNION
    SELECT 'Category C' AS category, event_time, value_c AS value
        FROM table_c;

Uma consulta típica seria algo como:

SELECT p.start_time, p.end_time, p.category,
       (SELECT SUM(v.value) FROM table_values v
           WHERE v.category=p.category
             AND v.event_time >= t.start_time AND v.event_time < t.end_time)
    FROM periods_table p

O problema é que a categorycoluna que poderia ser usada para discriminar entre as tabelas separadas na exibição é usada apenas no final.

Mesmo um EXPLAIN ANALYZEon SELECT * FROM table_values WHERE category='Category A'mostra que todas as 3 tabelas são subconsultadas quando as linhas que correspondem a esse critério vêm apenas de table_a:

                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan table_values  (cost=176.02..295.50 rows=27 width=44) (actual time=0.135..0.135 rows=0 loops=1)
   Filter: (table_values.category = 'Category A'::text)
   ->  HashAggregate  (cost=176.02..229.12 rows=5310 width=12) (actual time=0.119..0.119 rows=0 loops=1)
         ->  Append  (cost=0.00..136.20 rows=5310 width=12) (actual time=0.089..0.089 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 1"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.025..0.025 rows=0 loops=1)
                     ->  Seq Scan on table_a  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.010..0.010 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 2"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_b  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
               ->  Subquery Scan "*SELECT* 3"  (cost=0.00..45.40 rows=1770 width=12) (actual time=0.020..0.020 rows=0 loops=1)
                     ->  Seq Scan on table_c  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.006..0.006 rows=0 loops=1)
 Total runtime: 0.437 ms
(11 rows)

(Não há dados nesta tabela fictícia, mas as tabelas reais têm cerca de 300.000 linhas. SELECT * FROM table_values WHERE category='Category A'Leva cerca de 15 vezes mais do que SELECT * FROM table_a, embora sejam mais ou menos iguais.)

Existem índices event_timeem cada tabela, mas como não pode haver um índice na exibição category, isso não ajuda. Também tentei substituir a exibição por um CTE (já que às vezes leva a um caminho de consulta diferente), mas não ajudou.

Considerando que não posso realmente alterar as tabelas existentes, existe uma maneira de "mesclar" algumas tabelas como essas que levariam a consultas mais rápidas?

EDIT: consulta semelhante em dados reais. (Na verdade, existem 5 tabelas semelhantes aqui.)

Curiosamente, embora eu esteja consultando "Categoria E" aqui, há uma chave de classificação em "Categoria A" que não vem de nenhum lugar especificamente na consulta (acho que deve vir da primeira seleção na exibição, ou talvez apenas usa o valor da primeira seleção para indicar o nome da coluna).

EXPLAIN ANALYZE SELECT * FROM table_values WHERE category='Category E':

Subquery Scan table_values  (cost=1573543.53..1755714.30 rows=40482 width=44) (actual time=221030.235..221234.162 rows=317676 loops=1)
  Filter: (table_values.category = 'Category E'::text)
  ->  Unique  (cost=1573543.53..1654508.32 rows=8096479 width=12) (actual time=212999.276..220240.297 rows=8097555 loops=1)
        ->  Sort  (cost=1573543.53..1593784.72 rows=8096479 width=12) (actual time=212999.275..218561.085 rows=8097555 loops=1)
              Sort Key: ('Category A'::text), "*SELECT* 1".event_time, "*SELECT* 1".value
              Sort Method:  external merge  Disk: 300792kB"
              ->  Append  (cost=0.00..229411.58 rows=8096479 width=12) (actual time=0.014..4683.734 rows=8097555 loops=1)
                    ->  Subquery Scan "*SELECT* 1"  (cost=0.00..80689.62 rows=2847831 width=12) (actual time=0.014..954.326 rows=2847951 loops=1)
                          ->  Seq Scan on table_a  (cost=0.00..52211.31 rows=2847831 width=12) (actual time=0.010..607.528 rows=2847951 loops=1)
                    ->  Subquery Scan "*SELECT* 2"  (cost=0.00..29304.52 rows=1033976 width=12) (actual time=9.738..576.803 rows=1034928 loops=1)
                          ->  Seq Scan on table_b  (cost=0.00..18964.76 rows=1033976 width=12) (actual time=9.737..450.619 rows=1034928 loops=1)
                    ->  Subquery Scan "*SELECT* 3"  (cost=0.00..30463.22 rows=1075161 width=12) (actual time=15.100..720.983 rows=1075157 loops=1)
                          ->  Seq Scan on table_c  (cost=0.00..19711.61 rows=1075161 width=12) (actual time=15.099..592.070 rows=1075157 loops=1)
                    ->  Subquery Scan "*SELECT* 4"  (cost=0.00..79952.70 rows=2821835 width=12) (actual time=20.098..1794.739 rows=2821843 loops=1)
                          ->  Seq Scan on table_d  (cost=0.00..51734.35 rows=2821835 width=12) (actual time=20.097..1441.719 rows=2821843 loops=1)
                    ->  Subquery Scan "*SELECT* 5"  (cost=0.00..9001.52 rows=317676 width=12) (actual time=0.016..108.768 rows=317676 loops=1)
                          ->  Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.016..69.732 rows=317676 loops=1)
Total runtime: 221299.573 ms

EXPLAIN ANALYZE SELECT * FROM table_e:

Seq Scan on table_e  (cost=0.00..5824.76 rows=317676 width=12) (actual time=0.025..54.143 rows=317676 loops=1)
Total runtime: 67.624 ms

Existe uma maneira de um usuário não administrador do PostgreSQL descobrir quais bancos de dados ele pode acessar no servidor ao qual está conectado, mais especificamente quais desses bancos de dados possuem uma tabela com um nome específico que o usuário tem permissões suficientes para ver (e possivelmente consulta)?

Tanto quanto eu posso dizer, o equivalente do MySQL seria algo como:

SELECT t.TABLE_SCHEMA FROM information_schema.`TABLES` AS t
    WHERE t.TABLE_NAME = 'the_table_name'

(Isso seria preferencialmente para PostgreSQL 8.4, mas também estou interessado em soluções para 9.1 se isso mudou.)

Estou tentando representar uma estrutura de árvore no PostgreSQL (8.4) para poder consultar o caminho da raiz até um determinado nó ou encontrar todos os nós dentro de um sub-ramo.

Aqui está uma tabela de teste:

CREATE TABLE tree_data_1 (
    forest_id TEXT NOT NULL,
    node_id TEXT NOT NULL,
    parent_id TEXT,
    node_type TEXT,
    description TEXT,
    PRIMARY KEY (forest_id, node_id),
    FOREIGN KEY (forest_id, parent_id) REFERENCES tree_data_1 (forest_id, node_id)
);
CREATE INDEX tree_data_1_forestid_parent_idx ON tree_data_1(forest_id, parent_id);
CREATE INDEX tree_data_1_forestid_idx ON tree_data_1(forest_id);
CREATE INDEX tree_data_1_nodeid_idx ON tree_data_1(node_id);
CREATE INDEX tree_data_1_parent_idx ON tree_data_1(parent_id);

Cada nó é identificado por (forest_id, node_id)(pode haver outro nó com o mesmo nome em outra floresta). Cada árvore começa em um nó raiz (onde parent_idé nulo), embora eu espere apenas um por floresta.

Aqui está a exibição que usa um CTE recursivo:

CREATE OR REPLACE VIEW tree_view_1 AS
    WITH RECURSIVE rec_sub_tree(forest_id, node_id, parent_id, depth, path, cycle) AS (
        SELECT td.forest_id, td.node_id, td.parent_id, 0, ARRAY[td.node_id], FALSE FROM tree_data_1 td
        UNION ALL
        SELECT td.forest_id, rec.node_id, td.parent_id, rec.depth+1, td.node_id || rec.path, td.node_id = ANY(rec.path)
            FROM tree_data_1 td, rec_sub_tree rec
            WHERE td.forest_id = rec.forest_id AND rec.parent_id = td.node_id AND NOT cycle
     )
     SELECT forest_id, node_id, parent_id, depth, path
         FROM rec_sub_tree;

Esta é uma versão ligeiramente modificada do exemplo na documentação , para levar em consideração o forest_id, e que retorna rec.node_idno recursivo SELECTem vez do que seria td.node_id.

Para obter o caminho da raiz para um determinado nó, esta consulta pode ser usada:

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND node_id='...' AND parent_id IS NULL

Para obter uma subárvore, esta consulta pode ser usada:

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND parent_id='...'

Para obter uma árvore completa dentro de uma determinada floresta:

SELECT * FROM tree_view_1 WHERE forest_id='Forest A' AND parent_id IS NULL

A última consulta usa o seguinte plano de consulta (que pode ser visualizado em Explain.depesz.com ):

 CTE Scan on rec_sub_tree  (cost=1465505.41..1472461.19 rows=8 width=132) (actual time=0.067..62480.876 rows=133495 loops=1)
   Filter: ((parent_id IS NULL) AND (forest_id = 'Forest A'::text))
   CTE rec_sub_tree
     ->  Recursive Union  (cost=0.00..1465505.41 rows=309146 width=150) (actual time=0.048..53736.585 rows=1645992 loops=1)
           ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.034..975.796 rows=247316 loops=1)
           ->  Hash Join  (cost=13097.90..145331.63 rows=6183 width=150) (actual time=2087.065..5842.870 rows=199811 loops=7)
                 Hash Cond: ((rec.forest_id = td.forest_id) AND (rec.parent_id = td.node_id))
                 ->  WorkTable Scan on rec_sub_tree rec  (cost=0.00..49463.20 rows=1236580 width=132) (actual time=0.017..915.814 rows=235142 loops=7)
                       Filter: (NOT cycle)
                 ->  Hash  (cost=6006.16..6006.16 rows=247316 width=82) (actual time=1871.964..1871.964 rows=247316 loops=7)
                       ->  Seq Scan on tree_data_1 td  (cost=0.00..6006.16 rows=247316 width=82) (actual time=0.017..872.725 rows=247316 loops=7)
 Total runtime: 62978.883 ms
(12 rows)

Como esperado, isso não é muito eficiente. Estou parcialmente surpreso por não parecer usar nenhum índice.

Considerando que esses dados seriam lidos com frequência, mas raramente modificados (talvez uma pequena modificação a cada duas semanas), quais são as técnicas possíveis para otimizar essas consultas e/ou representação de dados?

EDIT: Eu também gostaria de recuperar a árvore na primeira ordem de profundidade. O uso ORDER BY pathtambém degrada substancialmente a velocidade da consulta acima.

Exemplo de programa Python para preencher a tabela com dados de teste (requer Psycopg2 ), provavelmente um pouco mais do que eu esperava em uma situação mais realista:

from uuid import uuid4
import random
import psycopg2

random.seed(1234567890)
min_depth = 3
max_depth = 6
max_sub_width = 10
next_level_prob = 0.7

db_connection = psycopg2.connect(database='...')
cursor = db_connection.cursor()
query = "INSERT INTO tree_data_1(forest_id, node_id, parent_id) VALUES (%s, %s, %s)"

def generate_sub_tree(forest_id, parent_id=None, depth=0, node_ids=[]):
    if not node_ids:
        node_ids = [ str(uuid4()) for _ in range(random.randint(1, max_sub_width)) ]
    for node_id in node_ids:
        cursor.execute(query, [ forest_id, node_id, parent_id ])
        if depth < min_depth or (depth < max_depth and random.random() < next_level_prob):
            generate_sub_tree(forest_id, node_id, depth+1)

generate_sub_tree('Forest A', node_ids=['Node %d' % (i,) for i in range(10)])
generate_sub_tree('Forest B', node_ids=['Node %d' % (i,) for i in range(10)])

db_connection.commit()
db_connection.close()

Usando o PostgreSQL (8.4), estou criando uma visão que resume vários resultados de algumas tabelas (por exemplo, criando colunas a, b, cna visão) e, em seguida, preciso combinar alguns desses resultados na mesma consulta (por exemplo a+b, a-b, , (a+b)/c, ...), de modo a produzir os resultados finais. O que estou percebendo é que os resultados intermediários são totalmente computados cada vez que são usados, mesmo que seja feito dentro da mesma consulta.

Existe uma maneira de otimizar isso para evitar que os mesmos resultados sejam computados todas as vezes?

Aqui está um exemplo simplificado que reproduz o problema.

CREATE TABLE test1 (
    id SERIAL PRIMARY KEY,
    log_timestamp TIMESTAMP NOT NULL
);
CREATE TABLE test2 (
    test1_id INTEGER NOT NULL REFERENCES test1(id),
    category VARCHAR(10) NOT NULL,
    col1 INTEGER,
    col2 INTEGER
);
CREATE INDEX test_category_idx ON test2(category);

-- Added after edit to this question
CREATE INDEX test_id_idx ON test2(test1_id);

-- Populating with test data.
INSERT INTO test1(log_timestamp)
    SELECT * FROM generate_series('2011-01-01'::timestamp, '2012-01-01'::timestamp, '1 hour');
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (20000*random()-10000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (2000*random()-1000)::int, (3000*random()-200)::int FROM test1;
INSERT INTO test2
    SELECT id, substr(upper(md5(random()::TEXT)), 1, 1),
               (2000*random()-40)::int, (3000*random()-200)::int FROM test1;

Aqui está uma exibição que executa as operações mais demoradas:

CREATE VIEW testview1 AS
    SELECT
       t1.id,
       t1.log_timestamp,
       (SELECT SUM(t2.col1) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='A') AS a,
       (SELECT SUM(t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='B') AS b,
       (SELECT SUM(t2.col1 - t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='C') AS c
    FROM test1 t1;

• SELECT a FROM testview1produz este plano (via EXPLAIN ANALYZE):

   Seq Scan on test1 t1  (cost=0.00..1787086.55 rows=8761 width=4) (actual time=12.877..10517.575 rows=8761 loops=1)
     SubPlan 1
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.193..1.193 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.109..1.177 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.414..0.414 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
   Total runtime: 10522.346 ms
  

• SELECT a, a FROM testview1produz este plano :

   Seq Scan on test1 t1  (cost=0.00..3574037.50 rows=8761 width=4) (actual time=3.343..20550.817 rows=8761 loops=1)
     SubPlan 1
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.183..1.183 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.100..1.166 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.418..0.418 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
     SubPlan 2
       ->  Aggregate  (cost=203.96..203.97 rows=1 width=4) (actual time=1.154..1.154 rows=1 loops=8761)
             ->  Bitmap Heap Scan on test2 t2  (cost=36.49..203.95 rows=1 width=4) (actual time=1.083..1.143 rows=0 loops=8761)
                   Recheck Cond: ((category)::text = 'A'::text)
                   Filter: (test1_id = $0)
                   ->  Bitmap Index Scan on test_category_idx  (cost=0.00..36.49 rows=1631 width=0) (actual time=0.426..0.426 rows=1631 loops=8761)
                         Index Cond: ((category)::text = 'A'::text)
   Total runtime: 20557.581 ms
  

Aqui, selecionar a, aleva o dobro do tempo que selecionar a, enquanto na verdade eles podem ser calculados apenas uma vez. Por exemplo, com SELECT a, a+b, a-b FROM testview1, ele percorre o subplano a3 vezes e bduas vezes, enquanto o tempo de execução pode ser reduzido para 2/5 do tempo total (assumindo que + e - são insignificantes aqui).

É bom que ele não calcule as colunas não utilizadas ( be c) quando elas não são necessárias, mas existe uma maneira de fazê-lo calcular as mesmas colunas usadas da exibição apenas uma vez?

EDIT: @Frank Heikens sugeriu corretamente o uso de um índice, que estava faltando no exemplo acima. Embora melhore a velocidade de cada subplano, não impede que a mesma subconsulta seja computada várias vezes. Desculpe, eu deveria ter colocado isso na pergunta inicial para deixar claro.

No PostgreSQL (8.4), estou tentando converter um parâmetro de string em uma data dentro de uma consulta SQL, voltando para now()quando a string não é uma data válida (ou vazia).

Em "pseudo-SQL", seria algo assim:

SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;

Tentei simplificar o problema para detectar uma string vazia usando essas duas consultas:

SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ?  END);

Por exemplo, se o parâmetro for '', isso é equivalente a isto:

SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE ''  END);

Ambos falham com ERROR: invalid input syntax for type timestamp with time zone: "" Faz sentido, mas implica que o ELSEbloco seja avaliado (ou pelo menos que seus tipos sejam resolvidos) independentemente de a CASEcondição ser verdadeira ou não. O seguinte funciona, mas o que eu gostaria que a CASEcondição (ou semelhante) tratasse é precisamente o caso em que não é uma data válida.

SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;

O mais próximo que cheguei de uma solução funcional é o seguinte:

SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));

Neste caso, se o parâmetro for '', retorna a data atual, caso contrário, retorna a data passada no parâmetro string (desde que possa ser transformada em uma data válida).

O que eu gostaria é de dar um passo além e fazer com que qualquer coisa que não possa ser transformada em DATEuso seja a data atual. Acho que isso poderia ser feito usando uma função PL/pgSQL personalizada que interceptaria esse erro , mas isso pode ser feito sem essa função, em SQL "simples" (ou pelo menos usando as funções existentes do PostgreSQL)?

Um aplicativo está gravando em um banco de dados que segue uma estrutura EAV, semelhante a esta:

CREATE TABLE item (
    id INTEGER PRIMARY KEY,
    description TEXT
);

CREATE TABLE item_attr (
    item INTEGER REFERENCES item(id),
    name TEXT,
    value INTEGER,
    PRIMARY KEY (item, name)
);

INSERT INTO item VALUES (1, 'Item 1');
INSERT INTO item_attr VALUES (1, 'height', 20);
INSERT INTO item_attr VALUES (1, 'width', 30);
INSERT INTO item_attr VALUES (1, 'weight', 40);
INSERT INTO item VALUES (2, 'Item 2');
INSERT INTO item_attr VALUES (2, 'height', 10);
INSERT INTO item_attr VALUES (2, 'weight', 35);

(Entendo que o EAV é um pouco controverso, mas esta questão não é sobre o EAV: este aplicativo herdado não pode ser alterado de qualquer maneira.)

Pode haver vários atributos, mas geralmente até 200 atributos por itens (geralmente semelhantes). Desses 200 atributos, há um grupo de cerca de 25 que são mais comuns que os demais e que são usados ​​com mais frequência nas consultas.

Para tornar mais fácil escrever novas consultas com base em alguns desses 25 atributos (os requisitos tendem a mudar e preciso ser flexível), escrevi uma visão que une a tabela de atributos para esses 25 atributos. Seguindo o exemplo acima, fica assim:

CREATE VIEW exp_item AS SELECT
   i.id AS id,
   i.description AS description,
   ia_height.value AS height,
   ia_width.value AS width,
   ia_weight.value AS weight,
   ia_depth.value AS depth
FROM item i
  LEFT JOIN item_attr ia_height ON i.id=ia_height.item AND ia_height.name='height'
  LEFT JOIN item_attr ia_width ON i.id=ia_width.item AND ia_width.name='width'
  LEFT JOIN item_attr ia_weight ON i.id=ia_weight.item AND ia_weight.name='weight'
  LEFT JOIN item_attr ia_depth ON i.id=ia_depth.item AND ia_depth.name='depth';

Um relatório típico usaria apenas alguns desses 25 atributos, por exemplo:

SELECT id, description, height, width FROM exp_item;

Algumas dessas consultas não são tão rápidas quanto eu gostaria que fossem. Com EXPLAINo , notei que ainda eram feitos joins nas colunas não utilizadas, o que, em cerca de 25 joins quando apenas 3 ou 4 atributos são usados, está causando uma degradação desnecessária no desempenho.

É claro que executar todos os LEFT JOINs na exibição é normal, mas estou pensando se haveria uma maneira de manter essa exibição (ou algo semelhante: estou interessado principalmente em usar uma exibição para simplificar a maneira como me refiro aos atributos , mais ou menos como se fossem colunas) e evitar (automaticamente) o uso de joins nos atributos não utilizados para uma determinada consulta.

A única solução que encontrei até agora é definir uma visão específica para cada uma dessas consultas, que só se junta com base nos atributos que são usados. (Isso melhora a velocidade, como esperado, mas requer mais programação de visualizações todas as vezes, portanto, um pouco menos de flexibilidade.)

Existe uma maneira melhor de fazer isso? (Existe uma maneira melhor de "fingir" que a estrutura do EAV é uma única tabela bem estruturada, do ponto de vista de escrever as consultas, e não ter que fazer essas junções à esquerda desnecessárias?)

Estou usando o PostgreSQL 8.4. Existem cerca de 10.000 linhas iteme cerca de 500.000 linhas em item_attr. Eu não esperaria mais de 80 mil linhas iteme 4 milhões de linhas item_attr, o que acredito que um sistema moderno pode manipular sem muitos problemas. (Comentários sobre outros RDBMS/versões também são bem-vindos.)

EDIT : Apenas para expandir o uso de índices neste exemplo.

O PRIMARY KEY (item, name)cria implicitamente um índice em (item, name), conforme documentado na documentação CREATE TABLE . Considerando que ambos iteme namesão usados ​​com uma restrição de igualdade no JOIN, esse índice parece adequado de acordo com a documentação sobre índices multicolunas .

O exemplo a seguir mostra que esse índice parece ser usado, conforme esperado, sem nenhum índice adicional explícito:

EXPLAIN SELECT id, description, height, width FROM exp_item WHERE width < 100;

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=28.50..203.28 rows=10 width=20)
   ->  Nested Loop Left Join  (cost=28.50..196.73 rows=10 width=16)
         ->  Nested Loop Left Join  (cost=28.50..190.18 rows=10 width=16)
               ->  Hash Join  (cost=28.50..183.64 rows=10 width=16)
                     Hash Cond: (ia_width.item = i.id)
                     ->  Seq Scan on item_attr ia_width  (cost=0.00..155.00 rows=10 width=8)
                           Filter: ((value < 100) AND (name = 'width'::text))
                     ->  Hash  (cost=16.00..16.00 rows=1000 width=12)
                           ->  Seq Scan on item i  (cost=0.00..16.00 rows=1000 width=12)
               ->  Index Scan using item_attr_pkey on item_attr ia_depth  (cost=0.00..0.64 rows=1 width=4)
                     Index Cond: ((i.id = ia_depth.item) AND (ia_depth.name = 'depth'::text))
         ->  Index Scan using item_attr_pkey on item_attr ia_weight  (cost=0.00..0.64 rows=1 width=4)
               Index Cond: ((i.id = ia_weight.item) AND (ia_weight.name = 'weight'::text))
   ->  Index Scan using item_attr_pkey on item_attr ia_height  (cost=0.00..0.64 rows=1 width=8)
         Index Cond: ((i.id = ia_height.item) AND (ia_height.name = 'height'::text))