Stephane Rolland's questions

Eu uso o PostgreSQL 9.1 no Ubuntu 12.04.

Preciso selecionar registros dentro de um intervalo de tempo: minha tabela time_limitstem dois timestampcampos e uma integerpropriedade. Existem colunas adicionais na minha tabela real que não estão envolvidas com esta consulta.

create table (
   start_date_time timestamp,
   end_date_time timestamp, 
   id_phi integer, 
   primary key(start_date_time, end_date_time,id_phi);

Esta tabela contém aproximadamente 2 milhões de registros.

Consultas como as seguintes levavam muito tempo:

select * from time_limits as t 
where t.id_phi=0 
and t.start_date_time <= timestamp'2010-08-08 00:00:00'
and t.end_date_time   >= timestamp'2010-08-08 00:05:00';

Então tentei adicionar outro índice - o inverso do PK:

create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);

Fiquei com a impressão de que o desempenho melhorou: O tempo de acesso aos registros no meio da tabela parece ser mais razoável: algo entre 40 e 90 segundos.

Mas ainda são várias dezenas de segundos para valores no meio do intervalo de tempo. E mais duas vezes ao mirar no final da mesa (cronologicamente falando).

Tentei explain analyzepela primeira vez obter este plano de consulta:

 Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
 Total runtime: 44.507 ms

Veja os resultados em depesz.com.

O que eu poderia fazer para otimizar a pesquisa? Você pode ver todo o tempo gasto verificando as duas colunas de carimbos de data/hora uma vez id_phidefinido como 0. E não entendo a grande varredura (60 mil linhas!) nos carimbos de data e hora. Eles não são indexados pela chave primária e idx_inversedeu adicionei?

Devo mudar de tipos de carimbo de data/hora para outra coisa?

Li um pouco sobre os índices GIST e GIN. Eu entendo que eles podem ser mais eficientes em certas condições para tipos personalizados. É uma opção viável para o meu caso de uso?

Eu uso Postgresql 9.1, com Ubuntu 12.04.

Inspirado pela resposta de Craig à minha pergunta Concatenação do tipo setof ou setof record , pensei em usar return query, setof record, e um gerador de série nesta função plpgsql:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns setof record as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$    language plpgsql;

Durante a execução, recebo o erro:

ERROR: set_valued function called in context that cannot accept a set

O que está errado ? Ao contrário de Craig, digo à função para retornar setof record.

Posso conseguir algo que funcione exatamente como Craig, ou seja, definindo um tipo create type pair_id_value as (idx bigint, value integer)e fazendo com que minha função plpgsql retorne um setof of pair_id_valueem vez de um setof record.

Mas mesmo com esta solução funcional, ainda não entendo porque select id, generate_series(0,13)sozinho retornará um resultado em duas colunas ... e ao contrário, chamar a função (retorna setof pair_id_value) com return query select id, generate_series(0,my_obj.value) from my_objretornará um resultado em apenas uma coluna cujo campo se parece this "(123123,0)" "(123123,1)" "(123123,2)" (3 linhas) que obviamente são tuplas.

É um caso em que uma tabela temporária deve/deve ser criada?

Eu uso Postgresql 9.1 com Ubuntu 12.04.

Em uma plpgsqlfunção tento concatenar o setoftipo retornado de outra função.

o type pair_id_valueem questão é criado comcreate type pair_id_value as (id bigint, value integer);

a função que retorna elementar setof pair_id_value(aquelas que serão concatenadas posteriormente) é esta:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
    listResults = []
    for x in range(0,value+1):
        listResults.append({ "id": id, "value": x})
    return listResults
$$
language plpython3u;

este código plpython direto deve ser bom, por exemplo, a consulta: select * from compute_pair_id_value(1712437,2);retorna bem:

  id            | value 
 ---------------+-----------
        1712437 |         0
        1712437 |         1
        1712437 |         2
 (3 rows)

esta função python é bastante simples por enquanto, para este exemplo, mas acima de tudo para minha prova de conceito. Será mais complexo no futuro próximo.

O problema surge quando tento concatenar todas as tabelas de resultados de múltiplos id.

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof pair_id_value as $$
declare
    pair pair_id_value;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

Recebo o erro: invalid input syntax for integer "(1712437,0)"como se não fosse mais visto como pair_id_value com duas colunas, mas como uma tupla (1712437,0).

Então mudei o tipo de saída da função de setof pair_id_value para setof record... e se eu executar esta função de concatenação semelhante:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value)  from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

Eu recebo o erro:a column definition list is required for functions returning "record"

Tentando seguir a resposta para esta pergunta do SO : tentei definir a definição da coluna no select desta forma select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer), o código completo está aqui:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return; 
end; $$ language plpgsql;

Mas ao lançar o script sql, o psql não aceita criar a função: syntax error at or near "(" select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)... apontando o dedo para o f(

Alguma idéia de como fazê-lo corretamente?

Devo considerar a criação de uma tabela temporária para fazer o trabalho?

Nesta pergunta do SO sobre procedimentos armazenados em plpgsql, o procedimento armazenado se parece com sth.name(). não sei qual é o significado do prefixosth.

Por exemplo:

create or replace function something.function_name()
returns setof record as
$$
-- code
$$ language plpgsql;

Olhando neste livro na "Parte IV: Programando com PostgreSQL" não encontrei nenhuma menção a este tipo de declaração de criação com um nome de função em duas partes.

Na documentação do postgresql, na seção create function , a única coisa parecida é quando eles tratam de argtypeou regtypedo que poderia ser escrito na forma: table_name.column_name%TYPE. Mas não está relacionado ao nome da função.

Então, com o que essa sintaxe está relacionada?

Enquanto observava vários exemplos de pl/pythonand pl/pgsql, vi muitos - mas não todos - usando volatile cost.

ou seja:

CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- code
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Procurando mais informações sobre volatile cost, descobri (à primeira vista) que aproximadamente pelo menos 90% dos exemplos da web estão usando volatile cost 100, e às vezes às vezes volatile cost 1. ( rowspois é 1000).

Pelo que entendi, essa indicação ajuda o otimizador do plano de consulta a decidir como definir prioridades em operações booleanas de curto-circuito.

É uma otimização prematura se eu começar a dar uma estimativa costou rowspara cada um dos meus procedimentos armazenados? Devo fazer isso apenas quando quiser otimizar determinada consulta? É uma arte escolher o bom valor do custo?

Eu sei sobre o comando explain, que ainda não aprendi. Este comando é útil para estimar cost?

Como usuário postgres, tenho create extension plpython3u;em meu banco de dados

então eu configurei o plpython3upara confiável: select lanpltrusted from pg_language where lanname like 'plpython3u';retorna verdadeiro

mas quando meu db_user tenta

  create function check_data_valid(id bigint)
    returns boolean
    as $$
    -- ...
    return true
    $$ language plpython3u;

Eu recebi o erro:permission denied for the language plpython3u

Então, com meu usuário postgres, tentei: grant usage on plpython3u to db_usere grant execute on plpython3u to db_userambos retornam o erro:

relation python doesn't exist

talvez seja porque é uma extensão ... porém, não sei o que fazer para criar meu procedimento armazenado.

Eu quero ter python3 em meu banco de dados postgresql para escrever procedimentos armazenados.

Estando no psqlcliente, quando digito o comando create extension plpython3urecebo o erro:

couldn't open extension control file /usr/share/postgresql/9.1/extension/plpython3u.control : No such file or directory

Eu verifiquei, há plpythonu.control e plpython2u.control no diretório, mas não o da versão 3.

No entanto, instalei os pacotes python3e python3-postgresql(entre outros) do depot. Estou usando o Ubuntu 12.04, kernel 3.2.0.38, com o postgresql 9.1 instalado.

O que devo instalar (ou fazer) para ter o arquivo plpython3u.control em minha máquina e ter python3 disponível em meu banco de dados?

Eu uso o postgresql 9.1, no ubuntu 12.04. Eu tinha instalado o pacote depot.

Eu adicionei uma nova partição ao meu sistema e gostaria de criar um banco de dados postgresql nesta partição (que será inteiramente dedicado a este banco de dados).

Meu pgdata está localizado em var/lib/postgresql/9.1/main. Eu pretendo parar o serviço postgresql, copiar o conteúdo pgdata para a nova partição, então fazer um link simbólico para a nova partição, chowno novo diretório para o usuário postgres, reiniciar o postgresql... hack.

Existe uma maneira de criar um banco de dados especificamente em uma partição especificada? Algo mais "canônico" (não um jogo de palavras com ubuntu)

essa minha configuração:

#postgres.conf:

listen_addresses = 'localhost'

#pg_hba.conf:

local all postgres md5
local all appuser trust
local all devuser trust
local all all peer

estou conectando no local usando o C-Library libpq. Minha string de conexão é:

host=localhost user=appuser dbname=mydbname port='5432'

No entanto, a menos que eu tenha um arquivo .pgpass na casa do meu usuário desenvolvedor (devuser), (aquele que inicia o aplicativo vinculado à libpq), meu executável não pode se conectar, recebo o errofe_sendauth : no password supplied

Como eu disse, se o arquivo .pgpass estiver aqui, então ele conecta ok.

O que não entendo: configurei local all appuser trust . Por que preciso de um arquivo .pgpass mesmo assim?

(Não é um problema sério em si, porque mais tarde por medida de segurança, não estará mais em trust, mas md5, então terei o arquivo .pgpass de qualquer maneira. Mas gostaria de saber)

eu queria saber sobre esse exemplo

CREATE TABLE cities (
        city     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(city),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

A definição de citycomo varchar(80) é duplicada aqui. Existe uma sintaxe do PostgreSQL que permita não duplicar varchar(80), apenas se baseando references cities(city)para criar a tabela meteorológica?

Eu fiz um rascunho de aplicativo remoto em cima da libpq para PostrgreSQL . Ele se comporta bem, mas eu perfilei o funcionamento geral do aplicativo. Para cada resultado final de negócio que eu produzo, acontece que eu chamo algo como 40 cláusula select (sobre tcpip).

Tenho reminiscências do SQL-Server me lembrando de minimizar o número de interações entre meu aplicativo remoto e o banco de dados. Tendo analisado meus selects, acho que poderia reduzir esse número para 3 SELECTcláusulas, usando joins. Mas não me lembro da sintaxe para usar o resultado de a SELECTem outro arquivo SELECT.

Por exemplo:

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

Este outro SELECTseria simplesmente do tipo:

SELECT identifier FROM another_table WHERE something='something'

Aqui está o layout das tabelas simplificado, declinado várias vezes para diferentes item_types ... (3 tipos totalmente diferentes, daí as 3 consultas SQL se otimizadas).

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

Existem vários id_itempara um id_passage.
Existem vários id_item_detailpara um id_item.

Como você escreveria isso?
Qual é o nome para descrever a ação de redirecionar um select para outro (se houver)?

Quando tento executar pg_restore.exeum arquivo de despejo de um banco de dados, ele lança dezenas de erros, todos iguais:

ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"

Isso se deve obviamente ao fato de que eu esvaziei o banco de dados antes de restaurá-lo do arquivo de despejo (este arquivo vem de um banco de dados de produção) ... ...

Existe uma maneira de desabilitar as restrições e todas as chaves estrangeiras, para todas as tabelas, antes de chamar pg_restore.exee, depois, reativar as restrições e chaves estrangeiras.

No SO eu encontrei algo interessante: adiar a verificação de restrição para confirmar o tempo . Mas acho que não posso ligar pg_restore.exede dentro psql.exedepois de adiar as restrições.

Há também este post , que data de 10 anos atrás, sugerindo a retirada e a adição das restrições. Ou alterar o valor de pg_class reltriggers para 0 e isso também seria possível para restrições ... mas temo que seja mais hacking do que uma boa prática ...

O que você aconselha, qual é a melhor prática neste caso? O uso pg_dump.exe com o -cleansinalizador cria um dump que ignora as restrições de verificação ao restaurar o banco de dados?

Não sei se essa pergunta se encaixa melhor aqui ou em SO...

Este é um script que gostaria de iniciar (o código da função foi copiado de uma pergunta no SO ):

\c mydb

create or replace function truncate_tables(username in varchar) returns void as $$
declare
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
begin 
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
end;
$$ language 'plpgsql';

Estou tendo o erro a seguir:

ERROR: syntax at or near "$1"    LINE1:   $1
QUERY $1
CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5

Sou novo em Postgres e PL/pgSQL e não sei o que essa mensagem de erro significa.

Gostaria de iniciar o comando: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydbde um computador remoto. Tanto o banco de dados quanto o computador remoto estão sob xp.

Enfrento dois problemas:

1. quando executado na linha de comando do servidor, o pgdump primeiro solicitará uma senha. Como posso conceder a senha na linha de comando? Uma rápida olhada na documentação do pg_dump obviamente não aceita um sinalizador para fazer isso (embora eu possa ter perdido).

2. se ambas as máquinas estivessem no Linux, eu teria tentado uma conexão SSH com o Putty para executar alguns comandos no servidor psql ... mas com as duas máquinas no Windows, não tenho ideia de por onde começar. O driver psqlODBC permite acesso a pg_dump? Existe um equivalente ao SSL para Windows?

Apenas para explicar o contexto, de fato, meu objetivo principal é obter um script de diagnóstico na máquina remota que reúna algumas informações significativas, incluindo um despejo de algumas ou todas as tabelas da base postgresql. O script de diagnóstico copiará o despejo em outro local no domínio onde todos os resultados de diagnóstico são reunidos.

O script deve ser executado na máquina remota, pois também deve realizar outras operações em outros computadores conectados ao domínio que não sejam o banco de dados postgresql.

Por motivos de segurança, é comum alterar a porta do Microsoft Sql Server. O assunto é discutido aqui em Mudar a porta do SQL Server é realmente muito mais seguro?

minha pergunta é: isso também se aplica ao PostgreSQL? Ou não tem relação?

Estou procurando uma informação precisa em um banco de dados sobre o qual não tenho conhecimento. O banco de dados está em uma máquina separada, mas posso fazer login nele e iniciar uma psqllinha de comando, com direitos de administrador.

É um produto de terceiros e eles demoram a responder perguntas. Eu sei que os dados estão dentro desse banco de dados, então quero fazer um pouco de engenharia reversa.

Dado um nome de tabela, é possível obter uma lista dos nomes das colunas dessa tabela?

Por exemplo, no SQL Server, é possível despejar uma tabela em uma CREATEinstrução reutilizável, que lista textualmente todas as colunas das quais a tabela é composta.