Mostrando postagens com marcador Postgres. Mostrar todas as postagens
Mostrando postagens com marcador Postgres. Mostrar todas as postagens
segunda-feira, 2 de março de 2015
Vida de DBA
Isso é a pura realidade de quem não sabe trabalhar com Postgres :)
Apenas compartilhando ...
Resultado, sobra pro DBA resolver a bagunça. Vida de DBA é assim!
...
Estraído do site www.vidadeprogramador.com.br
http://vidadeprogramador.com.br/2015/02/28/drop-database/
sexta-feira, 29 de novembro de 2013
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Desenvolvendo Querys Compativeis com Todos os SGBDRs com Ajuda do SQL Fiddle
Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.
Segue a dica para trabalhar com SQL Fiddle.
Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/
Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.
Mais uma vez espero ter ajudado.
APdSJC!
Sabe quando um amigo lhe pede uma ajuda sobre como desenvolver uma determinada query e você não sabe como explicar, essa é a dica para quem já passou por esse tipo de problema chama-se SQL Fiddle
Muitas vezes não é possível testar uma determinada query por não ter o SGBDR instalado ou está sem uma conexão segura ao SGBDR, então a dica é usar o SQL Fiddle.
Nesse site http://sqlfiddle.com/ ainda existe a possibilidade de verificar como funciona a mesma query nos diversos SGBDRs e suas diversas versões, uma feature interessante para quem desenvolve sistemas multi-SGBDRs.
Segue a dica para trabalhar com SQL Fiddle.
Acessar o SQL Fiddle no seguinte site: http://sqlfiddle.com/
Na primeira coluna coloca-se os comandos DDL (de criação de estrutura, tabelas, views, etc).
Executar com o botão Run Schema.
Na segunda coluna coloca-se os comandos DML (insert, update, delete, selects).
Executar com o botão Run SQL.
Outra observação é que o SQL Fiddle não armazena o cache dos scripts, então há de se rodar toda a query de uma única vez.
Mais uma vez espero ter ajudado.
APdSJC!
quarta-feira, 24 de julho de 2013
Remover caracteres especiais em campos do Postgres
--
-- Nome Artefato/Programa..: usf_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- ........................: O Peregrino / http://emersonhermann.blogspot.com
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 24/07/2012
-- Versao..................: 0.02
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS usf_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION usf_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
stextonovo TEXT;
BEGIN
-- Tabela ASCII
-- Caracteres não imprimiveis, 33 ao total, menos o NUL ficam 32
-- hex d abr ctl descricao
-- 00 0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata
stextonovo := translate(stexto, chr(1),''); -- 01 1 SOH ^A Start of Header - Início do cabeçalho
stextonovo := translate(stextonovo, chr(2),''); -- 02 2 STX ^B Start of Text - Início do texto
stextonovo := translate(stextonovo, chr(3),''); -- 03 3 ETX ^C End of Text - Fim do texto
stextonovo := translate(stextonovo, chr(4),''); -- 04 4 EOT ^D End of Tape - Fim de fita
stextonovo := translate(stextonovo, chr(5),''); -- 05 5 ENQ ^E Enquire - Interroga identidade do terminal
stextonovo := translate(stextonovo, chr(6),''); -- 06 6 ACK ^F Acknowledge - Reconhecimento
stextonovo := translate(stextonovo, chr(7),''); -- 07 7 BEL ^G Bell - Campainha
stextonovo := translate(stextonovo, chr(8),''); -- 08 8 BS ^H Back-space - Espaço atrás
stextonovo := translate(stextonovo, chr(9),''); -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal
stextonovo := translate(stextonovo, chr(10),''); -- 0A 10 LF ^J Line-Feed - Alimenta linha
stextonovo := translate(stextonovo, chr(11),''); -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical
stextonovo := translate(stextonovo, chr(12),''); -- 0C 12 FF ^L Form-Feed - Alimenta formulário
stextonovo := translate(stextonovo, chr(13),''); -- 0D 13 CR ^M Carriage-Return - Retorno do carro (enter)
stextonovo := translate(stextonovo, chr(14),''); -- 0E 14 SO ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
stextonovo := translate(stextonovo, chr(15),''); -- 0F 15 SI ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
stextonovo := translate(stextonovo, chr(16),''); -- 10 16 DLE ^P Data-Link Escape
stextonovo := translate(stextonovo, chr(17),''); -- 11 17 DC1 ^Q Device-Control 1
stextonovo := translate(stextonovo, chr(18),''); -- 12 18 DC2 ^R Device-Control 2
stextonovo := translate(stextonovo, chr(19),''); -- 13 19 DC3 ^S Device-Control 3
stextonovo := translate(stextonovo, chr(20),''); -- 14 20 DC4 ^T Device-Control 4
stextonovo := translate(stextonovo, chr(21),''); -- 15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento
stextonovo := translate(stextonovo, chr(22),''); -- 16 22 SYN ^V Synchronous Idle
stextonovo := translate(stextonovo, chr(23),''); -- 17 23 vETB^W End-of-Transmission Block
stextonovo := translate(stextonovo, chr(24),''); -- 18 24 CAN ^X Cancel
stextonovo := translate(stextonovo, chr(25),''); -- 19 25 EM ^Y End-Of-Medium
stextonovo := translate(stextonovo, chr(26),''); -- 1A 26 SUB ^Z Substitute
stextonovo := translate(stextonovo, chr(27),''); -- 1B 27 ESC ^[ Escape
stextonovo := translate(stextonovo, chr(28),''); -- 1C 28 FS ^\ File Separator
stextonovo := translate(stextonovo, chr(29),''); -- 1D 29 GS ^] Group Separator
stextonovo := translate(stextonovo, chr(30),''); -- 1E 30 RS ^^ Record Separator
stextonovo := translate(stextonovo, chr(31),''); -- 1F 31 US ^_ Unit Separator
stextonovo := translate(stextonovo, chr(127),''); -- 7F127 DEL ^? Delete
RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
Test 1
-- string com caracteres especiais, formato 2
SELECT 'SEM STRESS... ' || chr(1) || 'TECLA ENTER: ' || chr(13) || ' TECLA ESC: ' || chr(27) || ' TESTE OK ' AS test3;
-- string sem caracteres espciais usando a function, formato 2
SELECT usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test4;
-- verificando tamanho da string, 53 caracteres, incluindo 3 especiais
SELECT length('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ') AS test5;
-- verificando tamanho da string, 50 caracteres, removido 3 especiais
SELECT length(usf_rm_special_chars('SEM STRESS... ' || chr(1) || 'TECLA ENTER : ' || chr(13) || 'TECLA ESC: ' || chr(27) || ' TESTE OK ')) AS test6;
terça-feira, 29 de janeiro de 2013
Desenvolvendo querys SQL para Razão e Balancete Contábil.
Desenvolvendo querys SQL para Razão e Balancete Contábil.
Em um artigo escrito neste blog no dia 06-09-2012 Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo conforme link http://www.emersonhermann.blogspot.com.br/2012/09/consulta-sql-de-plano-de-contas-query.html, mostrei como desenvolver uma query em uma estrutura de plano de contas ou centro de custo, dessa vez irei apresentar de forma prática, gradual e por exemplos de como desenvolver uma query para relatório de Razão, Razão Sumarizado e Balancete Contábil.
Nivel de Complexidade: Intermediário, Avançado
Quem desenvolve sabe como é complicado criar relatórios para contabilidade ou centro de custos, seja codificando em alguma linguagem ou mesmo tentando encurtar o tempo ou a pressão, usando algum gerador de relatórios, a exemplo do MS Report Service, SAP Crystal Reports, Script Case, etc.
As querys scripts mostradas nesse artigo foram desenvolvidas para os seguintes SGBDRs em ordem alfabética:
Firebird 2.5.1
Oracle 11g R2
Postgres 9.1
SQL Server 2012
A modelagem aqui apresentada não seguiu o rigor acadêmico, a exemplo de implementação de constraints, chaves primárias, etc, bem como os exemplos citados para area Contabil.
O objetivo principal deste artigo é mostrar como desenvolver querys para Sistemas Contabeis, houve uma simplificação com intuíto de facilitar o entendimento, entretanto, com os exemplos expostos é possivel adotar em qualquer modelo relacional.
Houve também uma preocupação em apresentar os scripts desenvolvidos nos SGBDRs citados no inicio deste documento.
Pode-se chegar aos mesmos resultados de uma forma mais performática e simples; estou apto a sugestões.
Seguem os exemplos em scripts SQL:
Criando as tabelas ... (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.
Mais uma vez espero ter ajudado.
APDSJ!
-- USE tempdb; -- Descomentar caso use SQL Server, recomendo criar um banco de teste para os outros SGBDRs. --DROP TABLE plano_conta; CREATE TABLE plano_conta ( id_plano_conta varchar(12) PRIMARY KEY -- dados da conta ex. 1.02.01 , descricao varchar(50) -- descricao da conta cadastrada ex. Vendas Externas , tipo_conta varchar(1) -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check ); --DROP TABLE lancamento; CREATE TABLE lancamento ( id_lancamento integer PRIMARY KEY -- id do lancamento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento , dt_lancamento date -- data do lancamento , numero_doc varchar(40) -- numero do documento a ser informado , id_plano_conta varchar(12) -- chave estrangeira para a tabela plano_conta, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null , tipo_lancamento varchar(1) -- tipo de lancamento 'E' = Entrada 'S' = Saida , historico varchar(100) -- historico do lancamento , valor_lancamento numeric(15,2) -- valor informado );Povoando os tabelas...
-- Receitas
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
-- Despesas
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO plano_conta (id_plano_conta, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
-- Vamos povoar a tabela lancamento:
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (1,'2012-07-03','0000084','1.2.2','E',NULL,10.55);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (2,'2012-07-03','0000084','1.2.2','S',NULL,2.50);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (3,'2012-07-01','0000021','1.1.2','E',NULL,50.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento,numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (4,'2012-07-01','0000042','1.2.2','E',NULL,100.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (5,'2012-07-04','0000084','1.2.2','E',NULL,160.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (6,'2012-07-04','0000084','1.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (7,'2012-07-04','0000142','2.2.1','S',NULL,40.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (8,'2012-07-07','0000210','2.2.2','S',NULL,80.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (9,'2012-07-13','0000242','2.2.2','S',NULL,20.00);
INSERT INTO lancamento (id_lancamento, dt_lancamento, numero_doc, id_plano_conta, tipo_lancamento, historico, valor_lancamento) VALUES (10,'2012-07-13','0000284','2.2.1','S',NULL,15.00);
Vamos as querys relatórios...
Razão Detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao detalhado (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
SELECT lan.id_lancamento
, lan.dt_lancamento
, lan.numero_doc
, lan.id_plano_conta
, lan.tipo_lancamento
, lan.historico
,
coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < lan.dt_lancamento
AND id_plano_conta = lan.id_plano_conta
),0) AS saldo_inicial
, CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END AS saida
,
coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= lan.dt_lancamento
AND id_plano_conta = lan.id_plano_conta
),0) AS saldo_final
FROM lancamento AS lan
JOIN plano_conta AS plc
ON plc.id_plano_conta = lan.id_plano_conta
WHERE lan.dt_lancamento >= '2012-07-04'
AND lan.dt_lancamento <= '2012-07-14'
AND lan.id_plano_conta = '2.2.2'
ORDER BY lan.dt_lancamento ASC
;
Razão Sumarizado Por Plano de Conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
-- razao sumarizado por plano de conta (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1, SQL Server 2012)
SELECT x.id_plano_conta
, pcx.descricao
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, sum(x.entrada) AS entrada
, sum(x.saida) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT lan.id_plano_conta
, CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS plc
ON plc.id_plano_conta = lan.id_plano_conta
WHERE lan.dt_lancamento >= '2012-07-04'
AND lan.dt_lancamento <= '2012-07-14'
AND lan.id_plano_conta = '2.2.2'
) AS x
JOIN plano_conta AS pcx
ON pcx.id_plano_conta = x.id_plano_conta
GROUP BY x.id_plano_conta
, pcx.descricao
ORDER BY x.id_plano_conta ASC
;
Balancete Contabil (Script para SQL Server 2012)
-- balancete contabil (Script para SQL Server 2012)
SELECT pcx.id_plano_conta
, pcx.descricao
, sum(xx.saldo_inicial) AS saldo_inicial
, sum(xx.entrada) AS entrada
, sum(xx.saida) AS saida
, sum(xx.saldo_final) AS saldo_final
FROM
(
SELECT x.id_plano_conta
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT pla.id_plano_conta
, lan.dt_lancamento
, CASE WHEN lan.tipo_lancamento = 'E' THEN
lan.valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN lan.tipo_lancamento = 'S' THEN
lan.valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) AS x
GROUP BY x.id_plano_conta
) AS xx
JOIN plano_conta pcx
ON xx.id_plano_conta LIKE pcx.id_plano_conta + '%'
GROUP BY pcx.id_plano_conta
, pcx.descricao
ORDER BY pcx.id_plano_conta ASC
;
Balancete Contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
-- balancete contabil (Script para Firebird 2.5.1, Oracle 11g R2, Postgres 9.1)
SELECT pcx.id_plano_conta
, pcx.descricao
, sum(xx.saldo_inicial) AS saldo_inicial
, sum(xx.entrada) AS entrada
, sum(xx.saida) AS saida
, sum(xx.saldo_final) AS saldo_final
FROM
(
SELECT x.id_plano_conta
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento < '2012-07-04'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_inicial
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS entrada
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'S' THEN
valor_lancamento
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento >= '2012-07-04'
AND dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saida
, coalesce(
(
SELECT sum
(
CASE WHEN tipo_lancamento = 'E' THEN
valor_lancamento
WHEN tipo_lancamento = 'S' THEN
valor_lancamento * -1
ELSE
0.00
END
)
FROM lancamento
WHERE dt_lancamento <= '2012-07-14'
AND id_plano_conta = x.id_plano_conta
),0) AS saldo_final
FROM
(
SELECT pla.id_plano_conta
, lan.dt_lancamento
, CASE WHEN lan.tipo_lancamento = 'E' THEN
lan.valor_lancamento
ELSE
0.00
END AS entrada
, CASE WHEN lan.tipo_lancamento = 'S' THEN
lan.valor_lancamento
ELSE
0.00
END AS saida
FROM lancamento AS lan
JOIN plano_conta AS pla
ON pla.id_plano_conta = lan.id_plano_conta
WHERE 1=1
) AS x
GROUP BY x.id_plano_conta
) AS xx
JOIN plano_conta pcx
ON xx.id_plano_conta LIKE pcx.id_plano_conta || '%'
GROUP BY pcx.id_plano_conta
, pcx.descricao
ORDER BY pcx.id_plano_conta ASC
;
Figura 01 - Resultado das Querys
Para facilitar, caso haja interesse, recomendo criar views ou functions ou procedures das querys acimas, pois são demasiadamente extensas.
Mais uma vez espero ter ajudado.
APDSJ!
quinta-feira, 6 de setembro de 2012
Consulta SQL de Plano de Contas - Query Contabil - Query para Centro de Custo
O SGBDR testados foram:
Firebird 2.5.1
Postgres 9.1
SQL Server 2012
Oracle 11g R2
Expondo o problema, exemplo:
Montar uma query relatório que mostre o resultado totalizado por níveis de contas lançadas.
O centro de custo irá ser lançado sempre no ultimo nivel e/ou conta analitica, ou seja 1.1.1 ou 1.2.1, etc.
Conforme figura Figura 01, abaixo:
Figura 01 - Relatório Sumarizado por Plano de Contas
Temos uma estrutura de centro de custo modelada da seguinte maneira:
Script para Firebird 2.5.1, Postgres 9.1, SQL Server 2012, Oracle 11g R2
-- USE tempdb; -- Descomentar caso use SQL Server --DROP TABLE centro_custo; CREATE TABLE centro_custo ( id_centro_custo varchar(12) PRIMARY KEY -- dados da conta ex. 1.02.01 , descricao varchar(50) -- descricao da conta cadastrada ex. Vendas Externas , tipo_conta varchar(1) -- tipo de conta do cc Analitica ou Sintética, dominio discreto: A ou S, em situação de produção merece uma constraint check ); --DROP TABLE movimento; CREATE TABLE movimento ( id_movimento integer PRIMARY KEY -- id do movimento, recomenda-se auto incremento, mas para simplifcar fica sem auto incremento , numero_doc varchar(40) -- numero do documento a ser informado , id_centro_custo varchar(12) -- chave estrangeira para a tabela centro de custo, mas para simplificar apenas iremos convencionar, não será habilitado a FK, recomendo colocar not null , valor_movimento numeric(15,2) -- valor informado );
Agora iremos povoar a tabela de centro de custo
-- Receitas
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1','Receita','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1','Vendas Internas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.1','Escola','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.1.2','Escritório','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2','Vendas Externas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.1','Livro','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('1.2.2','Brinquedos','A');
-- Despesas
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2','Despesas','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1','Fornecedores','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.1','Nacional','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.1.2','Importado','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2','Escritório','S');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.1','Materiais de limpeza','A');
INSERT INTO centro_custo (id_centro_custo, descricao, tipo_conta) VALUES ('2.2.2','Materiais de Escritório','A');
-- Vamos povoar a tabela movimento:
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (1,'0000021','1.1.2',50.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (2,'0000042','1.2.2',100.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (3,'0000084','1.2.2',160.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (4,'0000142','2.2.1',40.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (5,'0000210','2.2.2',80.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (6,'0000242','2.2.2',20.00);
INSERT INTO movimento (id_movimento, numero_doc, id_centro_custo, valor_movimento) VALUES (7,'0000284','2.2.1',15.00);
Listando os centros de custos cadastrados ...
-- Listando conteudo de centro_custo SELECT * FROM centro_custo ORDER BY 1;
Figura 02 - Lista de Centro Custo
Listando os movimentos cadastrados, referenciando os centros de custos ...
-- Listando conteudo de movimento SELECT * FROM movimento;
Figura 03 - Lista de Movimento
Script apenas para SQL Server 2012
-- Query SQL Centro de Custo, para SQL Server 2012, exemplo:
SELECT cc.id_centro_custo
, cc.descricao
, sum(m.valor_movimento) AS total_conta
FROM centro_custo cc
JOIN movimento m
ON m.id_centro_custo LIKE cc.id_centro_custo + '%' -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo
, cc.descricao
ORDER BY cc.id_centro_custo ASC
;
Figura 04 - Query Centro Custo com Resultado em SQL Server 2012
Script apenas para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2
-- Query SQL Centro de Custo, para Firebird 2.5.1, Postgres 9.1, Oracle 11g R2, exemplo:
SELECT cc.id_centro_custo
, cc.descricao
, sum(m.valor_movimento) AS total_conta
FROM centro_custo cc
JOIN movimento m
ON m.id_centro_custo LIKE cc.id_centro_custo || '%' -- O segredo está aqui, o campo id_centro_custo vem depois do LIKE
GROUP BY cc.id_centro_custo
, cc.descricao
ORDER BY cc.id_centro_custo ASC
;
Figura 05 - Query Centro Custo com Resultado em Oracle 11g R2
A grande dica está na junção dos campos de conta usando like concatenado com '%' no final
Como no exemplo com Oracle 11g R2, abaixo descrito:
ON m.id_centro_custo LIKE cc.id_centro_custo || '%'
Existem outras formas de se chegar ao mesmo resultado, uma delas seria implementar union em contas de grupo e depois criar uma view, mas, fica complicado e não elegante, já que, será necessário alterar a query toda vez que o plano de contas mudar a estrutura.
Outra forma seria usar querys recursivas, que seria bem mais elegante que a opção anterior, entretanto, mais complexa, mas quanto ao uso de recursividade em querys, nem todos os SGBDRs atualmente dão suporte a essa técnica, outro problema reside no fato do desempenho e consumo de recursos, pois quanto mais níveis tiver o plano de contas maior será a pilha, em outras palavras, consumo de memória e processamento no servidor do banco de dados alto.
Eu particularmente já tive a oportunidade fazer parte de uma equipe de desenvolvimento, em que o meu chefe e a própria equipe insistiram em traçar uma modelagem que usava querys recursivas, apesar de não concordar, como era novato na equipe, não me deram crédito, então me reservei a sabedoria do silêncio e mesmo não concordando aproveitei a oportunidade para fazer acontecer e vê como ficaria um sistema de custos com uso maciço de querys recursivas, foi divertido, mas ainda não recomendo, pois a complexidade é alta das querys recursivas e da codificação da aplicação também, tornando o compartilhamento do conhecimento difícil, como também a manutenção da aplicação, considerando ainda o consumo extremo de memória e processamento a nível de infra-estrutura.
Mais uma vez espero ter ajudado.
Fique na Paz do Senhor Jesus Cristo !!!
domingo, 20 de novembro de 2011
Querys Recursivas no Postgres
Querys Recursivas no Postgres
Segue um exemplo prático de como fazer querys recursivas no Postgres, usando genealogia.
O conceito de query recursiva é montar o resultado por níveis, identificando quem são as ocorrências "pais", depois os "os filhos de primeiro nível", em seguida, "os filhos de segundo nível" e por conseguinte, mergulhando até encontrar, o último nível.
O Algorítmo da Query Recursiva é o seguinte:
Passo 1 - Encontrar todas as ocorrências de nível 1, o qual são chamados de ocorrências âncora.
Passo 2 - Baseado nas âncoras, identificar e selecionar todas as ocorrências de nível 2, recursividade
Passo 3 - Já em um processo recursivo, tomando por base o nível 2, identificar e selecionar todas as ocorrências de nível 3 e assim sucessivamente até chegar ao último nível.
O Modo como o Postgres, processa querys recursivas é bem semelhante ao do SGBDR SQL Server.
Obs: Use esse recurso com moderação, pois quanto mais níveis, mais depreciado tende a ser o desempenho desta query.
Segue script para teste na prática de como fazer:
--
-- Nome Artefato/Programa..: querys_recursivas_no_postgres
-- Empresa.................:
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) http://emersonhermann.blogspot.com
-- Data Inicio ............: 19/11/2011
-- Data Atual..............: 19/11/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: Postgres
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.4/9.0/9.1
-- Kernel..................: Nao informado!
-- Finalidade..............: Usar querys recursivas no Postgres
-- ........................:
-- OBS.....................:
--
/* testando no Postgres 8.4/9.0/9.1 */
--DROP TABLE IF EXISTS genealogia;
CREATE TABLE genealogia
(
id_genealogia integer PRIMARY KEY
, nome varchar(25) NOT NULL
, id_genealogia_pai integer NULL REFERENCES genealogia(id_genealogia)
);
--TRUNCATE TABLE genealogia;
SELECT * FROM genealogia;
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);
SELECT * FROM genealogia;
--query 1, AUTO RELACIONAMENTO
SELECT g1.nome
, g1.id_genealogia
, g2.id_genealogia_pai
FROM genealogia g1
LEFT JOIN genealogia g2
ON g1.id_genealogia = g2.id_genealogia_pai
;
--query 2, RECURSIVA
WITH RECURSIVE cte_recursiva (id_genealogia,nome,nivel,arvore)
AS
(
-- 1a PARTE ANCORA
SELECT id_genealogia
, nome
, 1 AS nivel
, CAST(nome AS VARCHAR(255)) AS arvore
FROM genealogia
WHERE id_genealogia_pai IS NULL
UNION ALL
-- 2a PARTE RECURSIVA
SELECT g.id_genealogia
, g.nome
, c.nivel + 1 AS nivel
, CAST((c.arvore || '/' || g.nome) AS VARCHAR(255)) AS arvore
FROM genealogia g
INNER JOIN cte_recursiva c
ON g.id_genealogia_pai = c.id_genealogia
)
SELECT nivel
, arvore
FROM cte_recursiva
;
Sem stress...
segunda-feira, 8 de agosto de 2011
Função isdigit no Postgres
Função isdigit no Postgres
Esta função escalar excelente para testar se um derterminado campo tem strings, letras ou apenas números em Postgres.
Retorna true para verdadeiro caso seja apenas números e false para falso, caso encontre textos dentro do campo.
Espero ter ajudado.
Segue fonte abaixo:
-- apagar se existir
DROP FUNCTION IF EXISTS usf_isdigit(text);
CREATE FUNCTION usf_isdigit(text) RETURNS BOOLEAN AS
$$
--
-- Nome Artefato/Programa..: usf_isdigit.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de Manuel Sugawara em http://bytes.com/topic/postgresql/answers/174214-determine-if-string-digit
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 12/04/2018
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.x/9.x/10.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (user store function - usf) para testa se um campo eh digito ou nao, retorna true para verdadeiro e false para falso
-- OBS.....................:
--
SELECT CASE WHEN (array_upper(string_to_array($1, '.'),1) > 2 ) THEN
FALSE
ELSE
$1 ~ '^(-)?\.|[0-9]+$'
END AS usf_isdigit
/*
Exemplo:
SELECT usf_isdigit('ISSO É UM VALOR NUMERICO?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMER.IC.O?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMERICO?'); -- retorna false
SELECT usf_isdigit('3000'); --retorna true
SELECT usf_isdigit('2700.00'); --retorno true
SELECT usf_isdigit('2700.00.00'); --retorno false
*/
$$
LANGUAGE SQL;
domingo, 7 de agosto de 2011
Remover Letras em campos do Postgres
Remover Letras em campos do Postgres
Mesmo teretêtê, que foi publicado no artigo Remover Letras em campos do Postgres, publicado no blog O Peregrino (http://emersonhermann.blogspot.com) para o SGBD SQL Server, só que dessa vez para o SGBD Postgres ... :)
Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.
Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, e nesses casos a coisa complica.
A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.
Então sendo assim:
Aqui segue a dica, de uma function desenvolvida em PL/pgSQL que resolve esse problema:
Que DEUS abençoe o Postgres e quem trabalha com o mesmo!
Valeu!
Mesmo teretêtê, que foi publicado no artigo Remover Letras em campos do Postgres, publicado no blog O Peregrino (http://emersonhermann.blogspot.com) para o SGBD SQL Server, só que dessa vez para o SGBD Postgres ... :)
Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.
Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, e nesses casos a coisa complica.
A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.
Então sendo assim:
Aqui segue a dica, de uma function desenvolvida em PL/pgSQL que resolve esse problema:
--
-- Nome Artefato/Programa..: sp_rm_letters_pg.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) O Peregrino http://emersonhermann.blogspot.com
-- Data Inicio ............: 25/07/2011
-- Data Atual..............: 16/08/2011
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remever letras das strings
-- OBS.....................:
--
-- DROP FUNCTION IF EXISTS sp_rm_letters(number text);
CREATE OR REPLACE FUNCTION sp_rm_letters(number text) RETURNS text AS
$$
DECLARE
c integer;
number_r text;
BEGIN
c := 65;
number_r := number;
--até 126, isto é, mais 62, contempla os caracteres padrão ANSI original, até 255 (191) contempla o ASCII extendido .
WHILE c<(65+62) LOOP
number_r := replace(number_r,chr(c),'');
c := c + 1;
END LOOP;
RETURN(number_r);
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
/*
-- Abaixo, letras, caracteres que serão removidos da string, pela function sp_rm_letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ
-- Chamado a função
SELECT sp_rm_letters('ABC123Z4') -- Retorno 1234
-- Ou
SELECT sp_rm_letters('éab~Z|||zcABC123éáúZ4'||chr(127)) -- Retorno 1234
-- Ou
SELECT sp_rm_letters('ABC123Z4'); -- Retorno 1234
-- A função sp_rm_letters não remove caracteres especiais mas pode ser combinada com a function sp_rm_special_chars, para remover caracteres especiais.
select sp_rm_special_chars(sp_rm_letters('éab~Z|||zcABC123éáúZ4z2'||chr(127)));
-- Uma implementação da função
SELECT * FROM tabela1 t1 JOIN tabela2 t2 ON t1.id_campo=sp_rm_letters(t2.id_campo);
*/
Que DEUS abençoe o Postgres e quem trabalha com o mesmo!
Valeu!
terça-feira, 5 de julho de 2011
Full Text Search, Busca Textual no PostgreSQL
Adaptei uma apresentação(slides) que fiz sobre FTS no PG ...
Full Text Search no PostgreSQL (FTS)
Teoria, Utilização, Possibilidades e Aplicabilidade
> Conceito de FTS (Full Text Search)
É uma técnica de pesquisa e recuperação de informações de texto armazenada em banco de dados,
usando linguagem natural como critério para busca em banco(querys),
opcionalmente podendo ordena-la por relevância da consulta.
> Busca Textual Tradicional
* Operadores de ~, ~ *, LIKE, ILIKE para tipos de dados textuais.
* Não há suporte linguístico.
* Não há ordenação do resultados de pesquisa(ranking)
* Há uma tendência a serem lentos por não haver apoio ao uso de índice.
> Busca Textual com FTS
* Indexação completa de texto e pré-processamento de documentos salva na própria entidade(tuplas).
* Uso de Dicionários.
* Busca por similaridade.
* Mesmo conceito de sites de busca a exemplo do Google (www.google.com)
> Definições
>> Documento: É unidade de busca do FTS, texto, atributo da entidade que sub-divide em:
>>> Tokens (símbolos): Texto classificado, fatiado em símbolos
>>> Lexema: É uma palavra única (palavra-chave) (token normalizado) normalizada de um documento.
>>> Palavras de Parada (Stop Words): São palavras muito comuns, aparece em quase todos os documentos, não tem valor de descriminação.
>> Tsvector: Conjunto de lexemas e posições, representação compacta de um documento.
>> Tsquery: Termos da busca, que deve ser normalizada já com uso de lexemas, e podem combinar vários termos usando operadores lógicos &, |, !
>> @@: Operador de casamento de padrões.
> Dicionários
>> Dicionários permitem controle detalhado sobre como os símbolos(tokens) são normalizados.
* Definir stop words que não devem ser indexados
* Mapa sinônimos para uma única palavra
* Mapa de frases a uma única palavra usando um dicionário de sinônimos
* Mapa de diferentes variações de uma palavra de uma forma canônica usando um dicionário.
* Mapa de diferentes variações de uma palavra de uma forma canônica usando regras stemmer Snowball.
>Precisão versus Recuperação
>> Uso de linguagem natural acarreta resultados imprecisos
* Ambiguidade
* Recuperação de documentos irrelevantes
* Vocabulários controlados resolvem esse problema de imprecisão
* Entretanto apresenta baixa de retorno de resultados nas consultas, não fazendo uso de derivação, sinônimos, etc.
> Ordenação, Desempenho, uso de Índices e Clusters
>> Falar sobre FTS, é quase que obrigatório falar sobre ordenação:
>> Índices: Agrupamento, ordenação lógica de entidades em arquivos separados, são atualizados conforme atualização de entidades.
>> Clusters: Agrupamento, ordenação física de dados da entidade, baseado em índices criados.
>Uso de Índices
>> É recomendável utilizar índices nas seguintes cláusulas SQL e atributos de entidade:
- FOREIGN KEY
- ORDER BY
- WHERE
- ON
- GROUP BY
- HAVING
- @@ (FTS)
> Tipos de Índices PostgreSQL
>> B-tree (padrão);
* Usado com Operadores: <, <=, =, >=, >, LIKE, ILIKE, ~, ~*
>> R-tree (espaciais);
* Usado com Operadores: <<, &<, &>, >>, @, ~=, &&
>>Hash (igualdade simples);
* Desancorajado, usar (B-tree ou GiST);
>>GiST e GIN
* Usados no FTS, não é obrigatório, mas recomendado;
> Comparativo GiST e GIN no FTS
>> GIN efetua pesquisas aprox. três vezes mais rápido do que GiST;
>> GIN demoram aprox. três vezes mais para serem construídos do que GiST; (pode ser contornado em alterando o parametro maintenance_work_mem postgresql.conf)
>> GIN são lentos para atualização de índices;
>> GiST são mais rápidos para atualização de índices;
>> GIN são de duas a três vezes maior do que GiST
Quando usar GiST ou GIN no FTS?
Como regra geral usar índices GIN para dados estáticos, porque as pesquisas são mais rápidas.
E usar índices GiST para dados dinâmicos, porque são mais rápidos para atualização.
> Limitações do FTS PostgreSQL
>> O comprimento de cada lexema deve ser inferior a 2K bytes
>> O comprimento de um tsvector (lexemas + posições) deve ser menor que 1 megabyte
>> O número de lexemas deve ser inferior a 2 64
>> Valores Posição no tsvector deve ser maior que 0 e não mais de 16.383
>> Não mais do que 256 posições por lexema
>> O número de nós (lexemas + operadores) em um tsquery deve ser inferior a 32.768
>Tsearch
>> Tsearch é o módulo de busca textual do PostgreSQL
>>> Tsearch1 já era poderoso mas não dava suporte a muitas features como ranking de relevância
>>> Tsearch2 já vem pré-instalado a partir da versão 8.3
* A versão 2 acrescentou ranking, headline, tabelas de configuração e etc.
* Mais fácil de configurar e usar
* Não é necessário compilar ou instalar módulos contrib/tsearch2
> Tipos de dados e Operadores do FTS
>> Tipos de Dados do FTS
>>> tsvector: tipo de dados que representa um documento
* Com lista ordenada de lexemas (tokens)
* Com posições no texto
>>> tsquery: tipo de dado para busca textual que suporta operadores booleanos |, & e !
* Ex.: ‘gato & rato’
>> Operadores do FTS
* @@: operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector
> FTS na Prática
* E o FTS no Postgres como fica na prática ?
>Considerações Finais
>>Observou-se que o uso de FTS na prática, consome muito processamento, é recomendável ter processador(es) de alto desempenho, para evitar gargalos.
>>Ainda não existe padronização para o FTS, isso implica dizer que cada SGBDR tem a sua forma de fazer FTS, recomenda-se interfacear na aplicação, para manter a portabilidade.
>>Entretando o custo benefício é viável, haja visto desempenho e funcionalidade.
Que DEUS te abençõe, sempre...
Full Text Search no PostgreSQL (FTS)
Teoria, Utilização, Possibilidades e Aplicabilidade
> Conceito de FTS (Full Text Search)
É uma técnica de pesquisa e recuperação de informações de texto armazenada em banco de dados,
usando linguagem natural como critério para busca em banco(querys),
opcionalmente podendo ordena-la por relevância da consulta.
> Busca Textual Tradicional
* Operadores de ~, ~ *, LIKE, ILIKE para tipos de dados textuais.
* Não há suporte linguístico.
* Não há ordenação do resultados de pesquisa(ranking)
* Há uma tendência a serem lentos por não haver apoio ao uso de índice.
> Busca Textual com FTS
* Indexação completa de texto e pré-processamento de documentos salva na própria entidade(tuplas).
* Uso de Dicionários.
* Busca por similaridade.
* Mesmo conceito de sites de busca a exemplo do Google (www.google.com)
> Definições
>> Documento: É unidade de busca do FTS, texto, atributo da entidade que sub-divide em:
>>> Tokens (símbolos): Texto classificado, fatiado em símbolos
>>> Lexema: É uma palavra única (palavra-chave) (token normalizado) normalizada de um documento.
>>> Palavras de Parada (Stop Words): São palavras muito comuns, aparece em quase todos os documentos, não tem valor de descriminação.
>> Tsvector: Conjunto de lexemas e posições, representação compacta de um documento.
>> Tsquery: Termos da busca, que deve ser normalizada já com uso de lexemas, e podem combinar vários termos usando operadores lógicos &, |, !
>> @@: Operador de casamento de padrões.
> Dicionários
>> Dicionários permitem controle detalhado sobre como os símbolos(tokens) são normalizados.
* Definir stop words que não devem ser indexados
* Mapa sinônimos para uma única palavra
* Mapa de frases a uma única palavra usando um dicionário de sinônimos
* Mapa de diferentes variações de uma palavra de uma forma canônica usando um dicionário.
* Mapa de diferentes variações de uma palavra de uma forma canônica usando regras stemmer Snowball.
>Precisão versus Recuperação
>> Uso de linguagem natural acarreta resultados imprecisos
* Ambiguidade
* Recuperação de documentos irrelevantes
* Vocabulários controlados resolvem esse problema de imprecisão
* Entretanto apresenta baixa de retorno de resultados nas consultas, não fazendo uso de derivação, sinônimos, etc.
> Ordenação, Desempenho, uso de Índices e Clusters
>> Falar sobre FTS, é quase que obrigatório falar sobre ordenação:
>> Índices: Agrupamento, ordenação lógica de entidades em arquivos separados, são atualizados conforme atualização de entidades.
>> Clusters: Agrupamento, ordenação física de dados da entidade, baseado em índices criados.
>Uso de Índices
>> É recomendável utilizar índices nas seguintes cláusulas SQL e atributos de entidade:
- FOREIGN KEY
- ORDER BY
- WHERE
- ON
- GROUP BY
- HAVING
- @@ (FTS)
> Tipos de Índices PostgreSQL
>> B-tree (padrão);
* Usado com Operadores: <, <=, =, >=, >, LIKE, ILIKE, ~, ~*
>> R-tree (espaciais);
* Usado com Operadores: <<, &<, &>, >>, @, ~=, &&
>>Hash (igualdade simples);
* Desancorajado, usar (B-tree ou GiST);
>>GiST e GIN
* Usados no FTS, não é obrigatório, mas recomendado;
> Comparativo GiST e GIN no FTS
>> GIN efetua pesquisas aprox. três vezes mais rápido do que GiST;
>> GIN demoram aprox. três vezes mais para serem construídos do que GiST; (pode ser contornado em alterando o parametro maintenance_work_mem postgresql.conf)
>> GIN são lentos para atualização de índices;
>> GiST são mais rápidos para atualização de índices;
>> GIN são de duas a três vezes maior do que GiST
Quando usar GiST ou GIN no FTS?
Como regra geral usar índices GIN para dados estáticos, porque as pesquisas são mais rápidas.
E usar índices GiST para dados dinâmicos, porque são mais rápidos para atualização.
> Limitações do FTS PostgreSQL
>> O comprimento de cada lexema deve ser inferior a 2K bytes
>> O comprimento de um tsvector (lexemas + posições) deve ser menor que 1 megabyte
>> O número de lexemas deve ser inferior a 2 64
>> Valores Posição no tsvector deve ser maior que 0 e não mais de 16.383
>> Não mais do que 256 posições por lexema
>> O número de nós (lexemas + operadores) em um tsquery deve ser inferior a 32.768
>Tsearch
>> Tsearch é o módulo de busca textual do PostgreSQL
>>> Tsearch1 já era poderoso mas não dava suporte a muitas features como ranking de relevância
>>> Tsearch2 já vem pré-instalado a partir da versão 8.3
* A versão 2 acrescentou ranking, headline, tabelas de configuração e etc.
* Mais fácil de configurar e usar
* Não é necessário compilar ou instalar módulos contrib/tsearch2
> Tipos de dados e Operadores do FTS
>> Tipos de Dados do FTS
>>> tsvector: tipo de dados que representa um documento
* Com lista ordenada de lexemas (tokens)
* Com posições no texto
>>> tsquery: tipo de dado para busca textual que suporta operadores booleanos |, & e !
* Ex.: ‘gato & rato’
>> Operadores do FTS
* @@: operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector
> FTS na Prática
* E o FTS no Postgres como fica na prática ?
/*
FTS na prática usando a base de dados da Bíblia.
Para praticar os conceitos expostos nesse artigo, usaremos a base de dados da Bíblia,
publicado nesse blog no seguinte endereço:
http://emersonhermann.blogspot.com/2011/04/biblia-do-dba.html
*/
-- Criando um indice ...
DROP INDEX IF EXISTS idx_palavra_texto;
CREATE INDEX idx_palavra_texto
ON palavra
USING btree
(texto);
-- Ordenacao fisica da tabela, com base em indice criado anteriormente,
-- a tabela fica indisponivel (em modo ACCESS EXCLUSIVE) para qualquer outra operação, no momento da execução do comando cluster.
-- Ordena fisicamente com base em indice criado anteriormente
CLUSTER idx_palavra_texto ON Palavra;
-- Reagrupando fisicamente
CLUSTER Palavra;
-- Todas as tabelas configuradas
CLUSTER;
-- Voltando ao FTS ...
-- Tsvector: Tipo de dados que representa um documento
-- Com lista ordenada de lexemas (tokens)
-- Com posições no texto
-- Tsquery: Tipo de dado para busca textual que suporta operadores booleanos |, & e !
-- Ex.: 'gato & rato'
-- @@: Operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector
--Operador &
SELECT 'gato & rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
SELECT 'gato & rato':: tsquery @@ 'O gato comeu o rato que roeu a roupa do rei de Roma'::tsvector; --true
-- Operador |
SELECT 'gato | rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT 'gato | cão':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
-- Operador !
SELECT '!rainha':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT '!rei':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
-- Pode-se obter um vetor de lexemas em tempo de execução, usando a função to_tsvector
SELECT to_tsvector('O gato comeu o rato que roeu a roupa do rei de Roma'); --'a':8 'comeu':3 'de':12 'do':10 'gato':2 'o':1,4 'que':6 'rato':5 'rei':11 'roeu':7 'roma':13 'roupa':9
-- Em um ambiente de produção, deve-se levar em conta o custo da criação do vetor em tempo de execução
-- Acredita-se que a melhor opção é criar um campo do tipo tsvector na tabela
-- A tabela biblioteca.cache_entidades_marc antes de adição do campo vetorfts
SELECT * FROM Palavra LIMIT 10;
-- Adicionando o campo vetorfts do tipo tsvector
ALTER TABLE Palavra ADD COLUMN vetorfts tsvector;
-- a tabela biblioteca.cache_entidades_marc depois de adição do campo vetorfts
SELECT vetorfts, * FROM Palavra LIMIT 10;
-- Vetorfts é um campo vetorizado para uso do índice FTS propriamente dito
-- Povoando a coluna vertorfts ...
-- Exemplo povoando o campo vetorizado criado anteriormente
UPDATE Palavra
SET vetorfts=to_tsvector(texto);
-- Exemplo povoando o campo vetorizado criado anteriormente, mas informando a linguagem do catálogo
UPDATE Palavra
SET vetorfts=to_tsvector('portuguese', texto);
-- Usa-se a função to_tsvector sobre o campo que se deseja indexar. Vários campos podem ser utilizados também no mesmo índice por concatenação, simulando o mesmo comportamento do Google:
UPDATE Palavra
SET vetorfts=to_tsvector('portuguese', id_livro || ' ' || capitulo || ' ' || versiculo || ' ' || texto);
-- Pode-se atribuir labels para os valores dos campos indexados pelo vetor, usando a função setweight
-- Ao mesmo tempo, são atribuídos pesos para valores de campos diferentes:
UPDATE Palavra
SET vetorfts = setweight(to_tsvector('portuguese',coalesce(cast (id_livro as text),'')), 'A') ||
setweight(to_tsvector('portuguese',coalesce(cast (capitulo as text),'')), 'B') ||
setweight(to_tsvector('portuguese',coalesce(cast (versiculo as text),'')), 'C') ||
setweight(to_tsvector('portuguese',coalesce(texto,'')), 'D')
;
-- Após criar o campo, e povoa-lo é recomendável criar um índice GiST ou GIN para ele
-- Exemplo de indice criado com vetortfs
DROP INDEX IF EXISTS idx_palavra_vetorfts;
CREATE
INDEX idx_palavra_vetorfts
ON Palavra
USING gin(vetorfts)
;
-- Exemplo de indice criado sem vetorfts
/*
DROP INDEX IF EXISTS idx_palavra_texto_fts;
CREATE
INDEX idx_palavra_texto_fts
ON Palavra
USING gin(to_tsvector('portuguese'::regconfig, texto))
;
*/
-- Para realizar uma consulta FTS sobre o vetor, utiliza-se a função to_tsquery juntamente como operador FTS booleano '@@':
--sem o campo vertorizado
SELECT *
FROM Palavra
WHERE to_tsvector('portuguese', texto) @@ to_tsquery( 'JESUS' )
;
--com o campo vetorizado
SELECT *
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS')
;
-- Será considerado o conteúdo de todos os campos indexados pelo índice vetorfts
-- Uma consulta normal (ANSI) próximo do equivalente ao FTS seria:
SELECT *
FROM Palavra
WHERE texto ILIKE '%JESUS%'
;
-- E se tivéssemos mais de um parâmetro?
-- OR OR OR OR OR OR OR OR OR OR OR OR OR OR OR
-- ou
-- LIKE '%param1%param2%paramN%'
-- É possível ainda definir o campo que deve ser consultado dentro do vetor FTS através do label:
SELECT *
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
;
-- Antes foi considerado o conteúdo de todos os campos indexados pelo índice vetorfts.
-- Nesta query foi considerado apenas os valores correspondentes ao campo do label informado.
-- Para obter o ranking das consultas, usa-se a função ts_rank_cd
SELECT *
, ts_rank_cd(vetorfts, to_tsquery('JESUS:D')) AS rank
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
ORDER BY rank DESC
;
-- Um parâmetro opcional pode ser especificado para definir se o tamanho do documento afetará o cálculo do ranking
-- CUIDADO: o uso de ranking pode ser caro pois é preciso consultar o tsvector de todos os documentos onde há matching
-- É possível alterar os pesos dos campos na cláusula SQL:
SELECT *
, ts_rank_cd('{0.8, 0.6, 0.4, 0.0}', vetorfts, to_tsquery('JESUS:D')) AS rank
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
ORDER BY rank DESC
;
-- A função ts_headline mostra um trecho do texto onde a palavra pesquisada foi encontrada, e ainda a destaca em negrito:
SELECT *
, ts_headline(texto , to_tsquery('JESUS:D') ) AS headline
FROM Palavra
WHERE vetorfts @@ to_tsquery('JESUS:D')
;
-- É possível também obter estatísticas dos lexemas em um vetor FTS usando ts_stat:
-- Valores retornados:
SELECT word -- Lexema
, ndoc -- Num. documentos
, nentry -- Num. ocorrências
FROM ts_stat('SELECT vetorfts FROM Palavra ')
ORDER BY ndoc DESC
, nentry DESC
, word ASC
;
-- A função ts_debug mostra informações de como uma palavra foi tratada pelo analisador e quais dicionários foram utilizados.
SELECT ts_debug('JESUS');
SELECT ts_debug('portuguese', 'JESUS');
SELECT ts_debug('english', 'JESUS');
>Considerações Finais
>>Observou-se que o uso de FTS na prática, consome muito processamento, é recomendável ter processador(es) de alto desempenho, para evitar gargalos.
>>Ainda não existe padronização para o FTS, isso implica dizer que cada SGBDR tem a sua forma de fazer FTS, recomenda-se interfacear na aplicação, para manter a portabilidade.
>>Entretando o custo benefício é viável, haja visto desempenho e funcionalidade.
Que DEUS te abençõe, sempre...
terça-feira, 31 de maio de 2011
3a. Conferência Brasileira de PostgreSQL - Novembro 2011
3a. Conferência Brasileira de PostgreSQL - Novembro 2011
O PGBR (antes conhecido como PGCon Brasil) é o maior evento sobre PostgreSQL das Américas: em 2009 e 2008, o evento trouxe mais de 300 profissionais de TI e, em 2007, mais de 200. Em 2011, serão 3 salas simultâneas com tutoriais, palestras e mesas de alto nível, contando com desenvolvedores nacionais e internacionais do PostgreSQL além de profissionais renomados no mercado brasileiro.
O evento acontecerá nos dias 3 e 4 de Novembro de 2011, no Hotel Century Paulista em São Paulo.
O PGBR é um evento de alta qualidade, e que vale a pena participar. Este ano acontecerá em São Paulo ao invés de Campinas, para facilitar o deslocamento de participantes.
Se você faz algo com o PostgreSQL, participe da lista de discussão e envie uma proposta de trabalho ou palestra (a chamada de trabalhos está prestes a ser publicada). Inscreva-se hoje mesmo!
PGBR 2011 - Conferência Brasileira PostgreSQL
O PGBR (antes conhecido como PGCon Brasil) é o maior evento sobre PostgreSQL das Américas: em 2009 e 2008, o evento trouxe mais de 300 profissionais de TI e, em 2007, mais de 200. Em 2011, serão 3 salas simultâneas com tutoriais, palestras e mesas de alto nível, contando com desenvolvedores nacionais e internacionais do PostgreSQL além de profissionais renomados no mercado brasileiro.
O evento acontecerá nos dias 3 e 4 de Novembro de 2011, no Hotel Century Paulista em São Paulo.
O PGBR é um evento de alta qualidade, e que vale a pena participar. Este ano acontecerá em São Paulo ao invés de Campinas, para facilitar o deslocamento de participantes.
Se você faz algo com o PostgreSQL, participe da lista de discussão e envie uma proposta de trabalho ou palestra (a chamada de trabalhos está prestes a ser publicada). Inscreva-se hoje mesmo!
PGBR 2011 - Conferência Brasileira PostgreSQL
sexta-feira, 8 de abril de 2011
A Bíblia do DBA
Serão usados algums recursos práticos de ETL no Postgres, e conversão de encondig de arquivos.
Tomei por base a Bíblia Sagrada na Tradução de João Ferreira de Almeida, Edição Revista e Corrigida em Português-Brasil.
Como estudioso da Bíblia e crente, entendo que o Autor da Bíblia está vivo, assim sempre que tenho alguma dúvida sobre sobre algum tópico deste livro consulto a Ele, o criador de todas as coisas, DEUS.
Essa base em csv pode ser também usada com algumas adaptações em outros SGBDs a exemplo Oracle, SQL Server, MySQL, Firebird ou outro que você queira usar, neste artigo usei
Postgres
Tomei como referencia uns arquivos gerados em csv com encoding em UTF8, neste caso podendo até serem abertos como planilhas Open-Office ou MS-Excel.
Deixo aqui meu profundo agradecimento ao irmãos que voluntariamente, tiveram o trabalho de converter uma base txt para csv, não é fácil, que DEUS os abençõe!
Mãos a massa!
Antes, apenas definindo o que é um arquivo CSV ou melhor lembrando... :)
Retirado do Wikipedia em: http://pt.wikipedia.org/wiki/Comma-separated_values
Comma-separated values (CSV), em português Valores Separados por Vírgula, é um formato de arquivo que armazena dados tabelados, cujo grande uso data da época dos mainframes. Por serem bastante simples, arquivos .csv são comuns em todas as plataformas de computador.
O CSV é um implementação particular de arquivos de texto separados por um delimitador, que usa a vírgula e a quebra de linha para separar os valores. O formato também usa as aspas em campos no qual são usados os caracteres reservados (vírgula e quebra de linha). Essa robustez no formato torna o CSV mais amplo que outros formatos digitais do mesmo segmento.
Caso queira mudar o encoding, você poderá utilizar o iconv do Linux em http://www.gnu.org/software/libiconv/documentation/libiconv/iconv.1.html ou salvar como txt e abrir pelo MS-Excel, que o mesmo pedirá qual encoding utilizar-converter.
Segue os arquivos em formato CSV da Bíblia:
Biblia_JFARC.zip
No nosso caso, o arquivo está zipado e contém três CSV 1-Testamento_JFARC, 2-Livro_JFARC, 3-Palavra_JFARC:
Para que o script SQL funcione sem alterações descompactei o arquivo no Windows em: c:\Biblia_JFARC, no Linux em: /home/postgres/Biblia_JFARC/ lembrando de dá permissões para acesso com usuário postgres
Além dos Scripts SQL para Postgres, também estão a base de dados para SQL Server e Firebird para Bíblia conforme link abaixo:
Biblia_JFARC.zip
Neste artigo iremos abordar a criação de scripts DDL/DML (SQL) para criação da base de dados da Bíblia no Postgres:
--
-- Nome Artefato/Programa..: Biblia_ddl.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com)
-- Data Inicio ............: 04/02/2011 as 20:45
-- Data Atual..............: 07/02/2011 as 18:50
-- Versao..................: 0.05
-- Compilador/Interpretador: PostgreSql/PLpgSQL
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: Script de criação de estrutura de base de dados da Bíblia Sagrada
-- ........................:
-- OBS.....................:
--
/*
--Poderia se criar dominio, como exemplo abaixo, já que é uma tabela pequena e imutavél,
--porém por questão de compatibilidade com outros SGBDRs, deixamos como um tabela.
DROP DOMAIN IF EXISTS id_testamento_dominio;
CREATE DOMAIN id_testamento_dominio AS smallint
CHECK
(
VALUE IN
(
1 --Antigo Testamento
, 2 --Novo Testamento
)
);
COMMENT ON DOMAIN id_testamento_dominio IS 'Dominio do Testamento';
*/
DROP TABLE IF EXISTS Testamento;
CREATE TABLE testamento
(
id_testamento smallint NOT NULL PRIMARY KEY
, nome varchar(21) NOT NULL
);
COMMENT ON TABLE Testamento IS 'Tabela que armazena os Testamentos da Bíblia';
COMMENT ON COLUMN Testamento.id_testamento IS 'Chave primária da tabela';
COMMENT ON COLUMN Testamento.nome IS 'Nome do Testamento';
DROP TABLE IF EXISTS Livro;
--DROP TABLE IF EXISTS Livro CASCADE;
CREATE TABLE Livro
(
id_livro serial
, ordem_livro smallint NOT NULL
, id_testamento smallint NOT NULL
, nome varchar(42) NOT NULL
, abrev varchar(4) NOT NULL
, CONSTRAINT Livro_pkey PRIMARY KEY (id_livro)
, CONSTRAINT Livro_id_testamento_fkey FOREIGN KEY (id_testamento)
REFERENCES Testamento (id_testamento)
);
COMMENT ON TABLE Livro IS 'Tabela que armazena os Livros da Bíblia';
COMMENT ON COLUMN Livro.id_livro IS 'Id do Livro da Bíblia';
COMMENT ON COLUMN Livro.ordem_livro IS 'Número de ordem do Livro da Bíblia';
COMMENT ON COLUMN Livro.id_testamento IS 'Número do Testamento';
COMMENT ON COLUMN Livro.nome IS 'Nome completo do Livro da Bíblia';
COMMENT ON COLUMN Livro.abrev IS 'Nome abreviado do Livro da Bíblia';
DROP TABLE IF EXISTS Palavra;
CREATE TABLE Palavra
(
id_palavra serial
, id_livro integer NOT NULL
, capitulo smallint NOT NULL
, versiculo smallint NOT NULL
, texto TEXT
, CONSTRAINT Palavra_pkey PRIMARY KEY (id_palavra)
, CONSTRAINT Palavra_id_livro_fkey FOREIGN KEY (id_livro)
REFERENCES Livro (id_livro)
);
COMMENT ON TABLE Palavra IS 'Tabela que armazena os versiculos da Bíblia';
COMMENT ON COLUMN Palavra.id_livro IS 'Id do Livro da Bíblia';
COMMENT ON COLUMN Palavra.capitulo IS 'Número do capítulo da Bíblia';
COMMENT ON COLUMN Palavra.versiculo IS 'Número do versículo da Bíblia';
COMMENT ON COLUMN Palavra.texto IS 'Texto do versículo da Bíblia';
INSERT INTO Testamento (id_testamento, nome) VALUES (1,'Velho Testamento');
INSERT INTO Testamento (id_testamento, nome) VALUES (2,'Novo Testamento');
SELECT * FROM Testamento;
--linux
COPY livro(ordem_livro, id_testamento, nome, abrev)
FROM '/home/postgres/Biblia_JFARC/2_Livro_JFARC.csv'
CSV
HEADER;
--windows
COPY livro(ordem_livro, id_testamento, nome, abrev)
FROM 'c:/Biblia_JFARC/2_Livro_JFARC.csv'
CSV
HEADER;
SELECT * FROM Livro;
--linux
COPY palavra (id_livro, capitulo, versiculo, texto)
FROM '/home/postgres/Biblia_JFARC/3_Palavra_JFARC.csv'
CSV
HEADER;
--windows
COPY palavra (id_livro, capitulo, versiculo, texto)
FROM 'c:/Biblia_JFARC/3_Palavra_JFARC.csv'
CSV
HEADER;
SELECT * FROM Palavra;
Eu sinceramente creio que DEUS fala pela sua Palavra, a Bíblia, além da leitura tradicional, já tive diversas experiências usando um recurso o qual chamo de Consulta a Palavra, no qual oro a DEUS, clamando pelo sangue de JESUS, peço que fale comigo através da sua Palavra; fecho os olhos e logo em seguida abro a Biblia (Livro) apontanto para um versiculo, abro os olhos e leio.
Uma dessas experiências vou citar agora:
Certa vez fui acusado injustamente de roubo no meu trabalho e por relexo meu, perdi o documento que comprovava minha inocência, diante de tal situação fui pra casa, na eminência de perder o emprego e pior, ser acusado de ladrão, mas a primeira coisa que fiz foi pedir socorro a quem podia me socorrer, DEUS, e orei ao Senhor que me desse livramento, pois havia perdido o documento, que iria me inocentar.
Então falei com DEUS da seguinte forma:
Fala comigo DEUS, pelo sangue de Jesus a ti clamo, me dá um livramento dessa situação, pois de alguma forma estou errado, porque não vigiei e acabei perdendo esse documento, agora estou sendo acusado injustamente de roubo e estou na eminência de perder o emprego, depois abri a Bíblia e pedi uma palavra, e DEUS falou comigo, através do seguinte versículo da Bíblia:
havendo riscado a cédula que era contra nós nas suas ordenanças, a qual de alguma maneira nos era contrária, e a tirou do meio de nós, cravando-a na cruz.
CL 2:14
No dia seguinte o fornecedor, reconheceu o erro, enviou a sua documentação comprovando a minha inocência e corrigindo a situação, quitando o débito; o montante que teria que pagar caso não conseguir provar minha inocência, era de aprox. 20 vezes o meu salário; fui inocentado, e ainda trabalhei um bom tempo nessa empresa, e após 3 anos de desligado dessa empresa, já trabalhando em outra lugar, um amigo encontrou o tal documento que finalmente provou a minha inocência.
Concluindo essa experiência... posso dizer que DEUS realmente riscou a escrito de dívida, que de alguma forma era contra mim. DEUS É FIEL!
Muitas sãos as aflições do justo, mas o Senhor o livra de todas. Salmos 34:19
Usando esse mesmo conceito de Consulta a Palavra na Bíblia, no universo digital, usei uma função randômica (function) que traz um versículo da Bíblia, aleatoriamente em PL/pgSQL
--
-- Nome Artefato/Programa..: sp_palavra.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com)
-- Data Inicio ............: 16/07/2009
-- Data Atual..............: 07/02/2011 as 18:50
-- Versao..................: 0.05
-- Compilador/Interpretador: PostgreSql/PLpgSQL
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure/Function Funcao de Consulta a Palavra, não é apenas uma
-- ........................: caixinha de promessa, pois toda a Biblia Sagrada é usada para o mesmo
-- OBS.....................:
--
DROP FUNCTION IF EXISTS Palavra();
CREATE OR REPLACE FUNCTION Palavra() RETURNS TEXT AS
$body$
SELECT p.texto
|| ' '
|| rtrim(l.abrev)
|| ' '
|| cast(p.capitulo AS text)
|| ':'
|| cast(p.versiculo AS text)
AS "Consulta a Palavra - Clame pelo Sangue de Jesus!!!"
FROM Palavra p
JOIN Livro l
ON l.id_livro = p.id_livro
JOIN Testamento te
ON te.id_testamento = l.id_testamento
ORDER BY RANDOM()
LIMIT 1
;
$body$
LANGUAGE 'sql';
COMMENT ON FUNCTION Palavra() IS 'Função de Consulta a Palavra (Bíblia Sagrada) não é apenas uma caixinha de promessa, pois toda a Bíblia Sagrada é usada para o mesmo';
SELECT * FROM Palavra();
A chamada dessa function em PL/pgSQL é:
SELECT * FROM Palavra();
Observações interessantes:
Os seguintes números são baseados na versão Nova Tradução na Linguagem de Hoje
Antigo Testamento | Novo Testamento | Total
Livros | 39 | 27 | 66
Capítulos | 929 | 260 | 1.189
Versículos | 23.146 | 7.957 | 31.103
Comparando com outras traduções, esses números podem ser um pouco diferentes:
A versão Almeida Revista e Atualizada, por exemplo, tem 31.104 versículos (o final de 1Samuel 20.42 se torna o versículo 43)
A versão Almeida Revista e Corrigida tem 31.105 versículos (além de 1Samuel 20, o final de Juízes 5.31 se torna o versículo 32).
A versão King James, por sua vez, tem 31.102 versículos, pois ajunta os versículos 14 e 15 de 3João.
Os textos originais em hebraico e grego, por sua vez, trazem um total de 31.171 versículos (21.213 no Antigo Testamento e 7.958 no Novo Testamento).
A maior diferença está no Livro de Salmos:
Vários deles trazem títulos que aparecem como o versículo 1 no texto hebraico e que, nas traduções, não são numerados.
Fonte: www.sbb.org.br
Este artigo usou a Versão Almeida Revista e Corrigida, porém com compatibilidade de versículos em quantidade com a versão King James, isto é, um total de 31102 versículos, conforme script abaixo:
-- Edição Almeida Revista e Corrigida select count(*) from Palavra; -- 31102 select 31105 - 31102; -- 3 select * from Livro; --o final de Juízes 5.31 se torna o versículo select * from Palavra where id_livro = 7 and capitulo= 5 and versiculo = 31; --o final de 1Samuel 20.42 se torna o versículo 43 select * from Palavra where id_livro = 9 and capitulo= 20 and versiculo = 42; --pois ajunta os versículos 14 e 15 de 3João select * from Palavra where id_livro = 64 and capitulo= 1 and versiculo = 14;
mas nós pregamos a Cristo crucificado, que é escândalo para os judeus e loucura para os gregos. 1CO 1:23
Que DEUS te abençõe sempre....
sexta-feira, 1 de abril de 2011
O moido do NOT IN, o Postgres estaria mentindo, seria um bug ?
Esse problema-desafio foi exposto por Leonardo Campos, amigo e companheiro de trabalho, poderia ser mais uma questão de concurso público voltado para SGBD Postgres, estilo casca de banana.
Vamos ao Problema:
O moido dessa vez é saber pq o NOT IN não estava funcionando corretamente em alguns casos, no PG 9.0, seria um bug ou banco estava mentindo ?
A experiencia tem me mostrado que o banco não mente, o irônico é dizer isso no dia primeiro de abril, mas, cito a fato de que SQL tem base matemática na teoria dos conjuntos e algebra relacional, e dizer isso de um o banco SGBD Relacional em particular o Postgres, um projeto de muitos anos; é dá murro em ponta de faca.
Apresentando o desafio...
Conclusão
No Postgres o null se propaga, isto é, qualquer coisa concatenada com NULL se torna NULL exemplo:
Esse mesmo conceito se aplica também ao NOT IN.
Então:
Deve ser observado que se o resultado da expressão à esquerda for nulo,
ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo,
o resultado da construção NOT IN será nulo, e não verdade.
Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos. [1]
Idem na versões demais versões do Postgres e a ultima 9.0 [2]
Concluindo isso não é um bug mas sim um comportamento padrão do NOT IN, que está em conformidade com as normas SQL/ANSI,
lembrando que uso de NOT IN não é recomendável quando se pensa em desempenho.
E finalmente o banco (SGBDR) não mente! :)
[1]
http://pgdocptbr.sourceforge.net/pg80/functions-subquery.html#FUNCTIONS-NOT-IN-SUBQ
[2]
http://www.postgresql.org/docs/9.0/static/functions-comparisons.html#AEN16842
Vamos ao Problema:
O moido dessa vez é saber pq o NOT IN não estava funcionando corretamente em alguns casos, no PG 9.0, seria um bug ou banco estava mentindo ?
A experiencia tem me mostrado que o banco não mente, o irônico é dizer isso no dia primeiro de abril, mas, cito a fato de que SQL tem base matemática na teoria dos conjuntos e algebra relacional, e dizer isso de um o banco SGBD Relacional em particular o Postgres, um projeto de muitos anos; é dá murro em ponta de faca.
Apresentando o desafio...
--Problema exposto
DROP TABLE IF EXISTS tb1;
CREATE TEMP TABLE tb1
(
id_tb1 integer PRIMARY KEY
, descricao varchar(28) NOT NULL
);
DROP TABLE IF EXISTS tb2;
CREATE TEMP TABLE tb2
(
id_tb2 integer PRIMARY KEY
, descricao varchar(28) NOT NULL
, id_tb1 integer NULL
, CONSTRAINT fk_tb2_id_tb1 FOREIGN KEY (id_tb1) REFERENCES tb1 (id_tb1)
);
INSERT INTO tb1 (id_tb1, descricao) VALUES (1,'TESTE1');
INSERT INTO tb1 (id_tb1, descricao) VALUES (2,'TESTE2');
INSERT INTO tb1 (id_tb1, descricao) VALUES (3,'TESTE3');
INSERT INTO tb1 (id_tb1, descricao) VALUES (4,'TESTE4');
INSERT INTO tb1 (id_tb1, descricao) VALUES (5,'TESTE5');
INSERT INTO tb1 (id_tb1, descricao) VALUES (6,'TESTE6');
INSERT INTO tb1 (id_tb1, descricao) VALUES (7,'TESTE7');
SELECT * FROM tb1;
/* Resultado
id_tb1 | descricao
--------+-----------
1 | TESTE1
2 | TESTE2
3 | TESTE3
4 | TESTE4
5 | TESTE5
6 | TESTE6
7 | TESTE7
(7 rows)
*/
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (8,'TESTE1',1);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (9,'TESTE2',2);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (10,'TESTE3',3);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (11,'TESTE4',4);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (12,'TESTE5',5);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (13,'TESTE7',NULL);
SELECT * FROM tb2;
/* Resultado
id_tb2 | descricao | id_tb1
--------+-----------+--------
8 | TESTE1 | 1
9 | TESTE2 | 2
10 | TESTE3 | 3
11 | TESTE4 | 4
12 | TESTE5 | 5
13 | TESTE7 | NULL
(6 rows)
*/
--Query 1
SELECT *
FROM tb1
WHERE id_tb1 IN
(
SELECT id_tb1
FROM tb2
)
;
/* Resultado
id_tb1 | descricao
--------+-----------
1 | TESTE1
2 | TESTE2
3 | TESTE3
4 | TESTE4
5 | TESTE5
(5 rows)
*/
--Query 2 (Seria um bug do Postgres? Retornou nada!)
SELECT *
FROM tb1
WHERE id_tb1 NOT IN
(
SELECT id_tb1
FROM tb2
)
;
--Nada, aparentemente o correto seria trazer os registros especificados, será o que SGBD (o banco) Postgres está mentindo ?
/* Resultado
id_tb1 | descricao
--------+-----------
(0 rows)
*/
--Análise 1, uma consulta equivalente com not in, apenas para testar
SELECT *
FROM tb1
WHERE id_tb1 <> ALL
(
SELECT id_tb1
FROM tb2
)
;
--Idem ao problema apresentado na Query 2
/* Resultado
id_tb1 | descricao
--------+-----------
(0 rows)
*/
--Análise 2, o que está havendo?
SELECT
DISTINCT *
FROM tb2
ORDER BY 1
;
--Existe um campo NULL no final do resultado. Será que esse NULL se propaga no NOT IN, mas pq não no IN ?
/* Resultado
id_tb2 | descricao | id_tb1
--------+-----------+--------
8 | TESTE1 | 1
9 | TESTE2 | 2
10 | TESTE3 | 3
11 | TESTE4 | 4
12 | TESTE5 | 5
13 | TESTE7 | NULL
(6 rows)
*/
-- Exemplo 1, Solução
SELECT *
FROM tb1
WHERE id_tb1 NOT IN
(
SELECT id_tb1
FROM tb2
WHERE id_tb1 IS NOT NULL
)
;
--Com a inclusão da condicional removendo os nulos, deu certo, pq?, Qual a explicação disso?
/* Resultado
id_tb1 | descricao
--------+-----------
6 | TESTE6
7 | TESTE7
(2 rows)
*/
Conclusão
No Postgres o null se propaga, isto é, qualquer coisa concatenada com NULL se torna NULL exemplo:
SELECT 'NULL' || NULL AS teste1; SELECT 'Isto ' || 'é' ||' apenas '|| ' um ' || NULL || ' teste .... ' AS teste2;
Esse mesmo conceito se aplica também ao NOT IN.
Então:
Deve ser observado que se o resultado da expressão à esquerda for nulo,
ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo,
o resultado da construção NOT IN será nulo, e não verdade.
Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos. [1]
Idem na versões demais versões do Postgres e a ultima 9.0 [2]
Concluindo isso não é um bug mas sim um comportamento padrão do NOT IN, que está em conformidade com as normas SQL/ANSI,
lembrando que uso de NOT IN não é recomendável quando se pensa em desempenho.
E finalmente o banco (SGBDR) não mente! :)
[1]
http://pgdocptbr.sourceforge.net/pg80/functions-subquery.html#FUNCTIONS-NOT-IN-SUBQ
[2]
http://www.postgresql.org/docs/9.0/static/functions-comparisons.html#AEN16842
segunda-feira, 14 de fevereiro de 2011
Formatando nomes, colocando iniciais em maiúsculas e demais em minúsculas
É raro, mas, as vezes acontece de que o DBA Postgres, ter que adaptar um estrutura de nomes em uma ou mais tabelas, com iniciais em maiúsculas e demais e minúsculas, aqui segue um store function para fazer esse moido.
--
-- Nome Artefato/Programa..: sf_formata_nome.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com)
-- Data Inicio ............: 07/02/2011
-- Data Atual..............: 14/02/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para formata um nome, colocando iniciais em maiúsculas e demais em minúsculas
-- OBS.....................: uso de função de agregacao array_agg funciona no PG 8.4 / 9.0, substituir por array_accum no PG anterior a essas versões .
--
-- Apaga function se existir
DROP FUNCTION IF EXISTS sf_formata_nome(text);
CREATE OR REPLACE FUNCTION sf_formata_nome(var text) RETURNS TEXT AS
$body$
SELECT array_to_string(array_agg(nomes),' ')
FROM
(
SELECT CASE WHEN lower(x.id_unico[i]) = 'de' THEN
lower(x.id_unico[i])
WHEN lower(x.id_unico[i]) = 'dos' THEN
lower(x.id_unico[i])
WHEN lower(x.id_unico[i]) = 'da' THEN
lower(x.id_unico[i])
WHEN lower(x.id_unico[i]) = 'e' THEN
lower(x.id_unico[i])
ELSE
upper(substring(x.id_unico[i],1,1)) || lower(substring(x.id_unico[i],2))
END AS nomes
FROM
(
SELECT *
FROM string_to_array(cast($1 AS text),' ') AS id_unico
) AS x,
generate_series(1,array_upper(string_to_array(cast($1 as text),' '),1)) AS i
) AS x
;
$body$
LANGUAGE 'sql';
COMMENT ON FUNCTION sf_formata_nome(text) IS 'Função que formata um nome, colocando iniciais em maiúsculas e demais em minúsculas';
SELECT sf_formata_nome('JOSE E SILVA DE LIRA DOS SANTOS');
Pós Replicação no Postgres 9.0
Em outubro de 2010, publiquei um artigo no blog sobre como fazer replicação no Postgres 9.0 com Slackware 13.0, este mini artigo que escrevo agora, segue uma sugestão de solução na pós replicação.
Um problema que foi detectado, era o que fazer com tantos arquivos gerados no ponto de montagem na partição nfs, remover a cada sete dias seria uma possível solução, mas não seria confiável, a dica sugerida aqui está no diretório contrib do próprio Postgres e que se chama pg_arquivecleanup, tomando por base os fontes do Postgres vamos instalar essa contrib
Feito isso será criado uma arquivo binário chamado chamado pg_arquivecleanup no diretório de binários, default do Postgres em /usr/local/pgsql/bin
Depois adicionar no final do arquivo recovery.conf, este usado apenas nos slaves, a chamada do pg_archivecleanup, para isso, adicionando a seguinte linha: archive_cleanup_command = 'pg_archivecleanup /path/for/backups %r', conforme exemplo abaixo:
Após isso dá um restart no serviço do Postgres no(s) servidor(es) slave(s).
A contrib pg_archivecleanup remove os fragmentos da replicação de forma confiável, evitando que ocupe espaço desnecessário no servidor do banco.
Espero ter ajudado
O Peregrino.
Um problema que foi detectado, era o que fazer com tantos arquivos gerados no ponto de montagem na partição nfs, remover a cada sete dias seria uma possível solução, mas não seria confiável, a dica sugerida aqui está no diretório contrib do próprio Postgres e que se chama pg_arquivecleanup, tomando por base os fontes do Postgres vamos instalar essa contrib
#cd /usr/local/postgres-9.0.2/contrib/pg_arquivecleanup #make pg_arquivecleanup #make install pg_arquivecleanup
Feito isso será criado uma arquivo binário chamado chamado pg_arquivecleanup no diretório de binários, default do Postgres em /usr/local/pgsql/bin
Depois adicionar no final do arquivo recovery.conf, este usado apenas nos slaves, a chamada do pg_archivecleanup, para isso, adicionando a seguinte linha: archive_cleanup_command = 'pg_archivecleanup /path/for/backups %r', conforme exemplo abaixo:
#echo archive_cleanup_command = 'pg_archivecleanup /mnt/postgres %r' >> /usr/local/pgsql/data/recovery.conf #cat /usr/local/pgsql/data/recovery.conf standby_mode = 'on' primary_conninfo = 'host=10.3.128.1 port=5432 user=postgres password=postgres' restore_command = 'cp /mnt/postgres/%f %p' trigger_file = '/tmp/trigger.pgsql.5432' archive_cleanup_command = 'pg_archivecleanup /mnt/postgres %r'
Após isso dá um restart no serviço do Postgres no(s) servidor(es) slave(s).
A contrib pg_archivecleanup remove os fragmentos da replicação de forma confiável, evitando que ocupe espaço desnecessário no servidor do banco.
Espero ter ajudado
O Peregrino.
segunda-feira, 31 de janeiro de 2011
Instalando o pgAdmin III 1.12.1 no Slackware
Essa dica vale para o pessoal que usa Slackware com Postgres 9, acho que pode funcionar com outras versões do Slackware,
testei apenas no Slackware 13.1
No próprio site do pgAdmin III www.pgadmin.org não tem a versão mais atual, ainda, (data de publicação desse post),
do mesmo para Slackware ou um tgz e instalar a partir dos fontes é um moido enorme, pois existem algumas dependências de
pacotes a serem instalados(wxGTK 2.8.x, libxml2 2.6.18 e libxslt 1.1.x) antes de fazer ./configure && make && make install, então simplificando...
O macete aqui é baixar uma versão disponibilizada para openSUSE no site oficial do pgAdmin III:
http://postgresql.org/download/mirrors-ftp/pgadmin3/release/v1.12.1/opensuse/11.1/pgadmin3-1.12.1-1.i586.rpm
e transformar em um pacote tgz do Slackware, usando o rpm2tgz que já vem na distro do Slackware, vamos lá!
Usando o konsole...
Depois, a critério, é só criar um ícone do pgAdmin no KDE apontando para o diretório-arquivo /bin/pgadmin3
ou executar direto via linha de comando no KDE, ALT + F2 pgadmin3
Espero ter ajudado !
O Peregrino
testei apenas no Slackware 13.1
No próprio site do pgAdmin III www.pgadmin.org não tem a versão mais atual, ainda, (data de publicação desse post),
do mesmo para Slackware ou um tgz e instalar a partir dos fontes é um moido enorme, pois existem algumas dependências de
pacotes a serem instalados(wxGTK 2.8.x, libxml2 2.6.18 e libxslt 1.1.x) antes de fazer ./configure && make && make install, então simplificando...
O macete aqui é baixar uma versão disponibilizada para openSUSE no site oficial do pgAdmin III:
http://postgresql.org/download/mirrors-ftp/pgadmin3/release/v1.12.1/opensuse/11.1/pgadmin3-1.12.1-1.i586.rpm
e transformar em um pacote tgz do Slackware, usando o rpm2tgz que já vem na distro do Slackware, vamos lá!
Usando o konsole...
rpm2tgz pgadmin3-1.12.1-1.i586.rpm installpkg pgadmin3-1.12.1-1.i586.tgz
Depois, a critério, é só criar um ícone do pgAdmin no KDE apontando para o diretório-arquivo /bin/pgadmin3
ou executar direto via linha de comando no KDE, ALT + F2 pgadmin3
Espero ter ajudado !
O Peregrino
quinta-feira, 11 de novembro de 2010
Por que usar o VACUUM
--Por que usar o VACUUM.
--Este artigo explica de forma prática por que usar o VACUUM
--Criando a tabela temporaria chamada teste
DROP TABLE IF EXISTS teste;
CREATE TEMP TABLE teste
(
id SERIAL PRIMARY KEY,
nome VARCHAR(10)
);
--Populando ...
INSERT INTO teste (nome) VALUES ('TESTE1');
INSERT INTO teste (nome) VALUES ('TESTE2');
INSERT INTO teste (nome) VALUES ('TESTE3');
INSERT INTO teste (nome) VALUES ('TESTE4');
INSERT INTO teste (nome) VALUES ('TESTE5');
INSERT INTO teste (nome) VALUES ('TESTE6');
INSERT INTO teste (nome) VALUES ('TESTE7');
INSERT INTO teste (nome) VALUES ('TESTE8');
INSERT INTO teste (nome) VALUES ('TESTE9');
INSERT INTO teste (nome) VALUES ('TESTE10');
INSERT INTO teste (nome) VALUES ('TESTE11');
INSERT INTO teste (nome) VALUES ('TESTE12');
INSERT INTO teste (nome) VALUES ('TESTE13');
INSERT INTO teste (nome) VALUES ('TESTE14');
INSERT INTO teste (nome) VALUES ('TESTE15');
INSERT INTO teste (nome) VALUES ('TESTE16');
INSERT INTO teste (nome) VALUES ('TESTE17');
INSERT INTO teste (nome) VALUES ('TESTE18');
INSERT INTO teste (nome) VALUES ('TESTE19');
INSERT INTO teste (nome) VALUES ('TESTE20');
INSERT INTO teste (nome) VALUES ('TESTE21');
INSERT INTO teste (nome) VALUES ('TESTE22');
INSERT INTO teste (nome) VALUES ('TESTE23');
INSERT INTO teste (nome) VALUES ('TESTE24');
INSERT INTO teste (nome) VALUES ('TESTE25');
--Fazendo uma simples consulta
SELECT * FROM teste; -- 25 tuplas na ordem de inserção
--Checando ultimo vacuum na tabela
SELECT schemaname AS esquema
, relname AS tabela
, pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
, pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
FROM pg_stat_all_tables
WHERE relname ILIKE 'teste'; -- null
--Checando quantas linhas mortas na tabela
SELECT *
, n_dead_tup AS linhas_mortas
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
AND relname ILIKE 'teste'
ORDER BY n_dead_tup DESC; -- nada
--Implentando o vacuum
VACUUM FULL VERBOSE teste;
--update 1
UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1;
--Fazendo uma simples consulta
SELECT * FROM teste; -- 25 tuplas com id 1 por ultimo, pq ?
--Checando ultimo vacuum na tabela
SELECT schemaname AS esquema
, relname AS tabela
, pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
, pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
FROM pg_stat_all_tables
WHERE relname ILIKE 'teste'; -- vacuum registrado
--Checando quantas linhas mortas na tabela
SELECT *
, n_dead_tup AS linhas_mortas
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
AND relname ILIKE 'teste'
ORDER BY n_dead_tup DESC; --1 linha morta
--update do id 1 a 10
UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1;
UPDATE teste SET nome = 'TESTE2 OK' WHERE id =2;
UPDATE teste SET nome = 'TESTE3 OK' WHERE id =3;
UPDATE teste SET nome = 'TESTE4 OK' WHERE id =4;
UPDATE teste SET nome = 'TESTE5 OK' WHERE id =5;
UPDATE teste SET nome = 'TESTE6 OK' WHERE id =6;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =7;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =8;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =9;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =10;
--delete do id 22 a 25
DELETE FROM teste WHERE id >= 22;
--Listando...
SELECT * FROM teste; --fora da ordem natural, pq ?
--Checando ultimo vacuum na tabela
SELECT schemaname AS esquema
, relname AS tabela
, pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
, pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
FROM pg_stat_all_tables
WHERE relname ILIKE 'teste'; -- vacuum registrado
--Checando quantas linhas mortas na tabela
SELECT *
, n_dead_tup AS linhas_mortas
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
AND relname ILIKE 'teste'
ORDER BY n_dead_tup DESC; --15 linhas mortas
--Dando um sumiço nos cadavers :)
VACUUM FULL VERBOSE teste;
--Apenas listando na ordem dos ids
SELECT *
FROM teste
ORDER BY 1 ASC; --agora com ordenacao pelo id
--Conclusão:
--Toda operação de update o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá, e dá um insert da(s) linha(s) novamente com as valores alterados, isso diminue o custo do update.
--Isso explica o motivo da ordem natural de inserção de linhas não aparecer, caso haja atualizações em numa consulta sem ordenação.
--Toda operação de delete o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá.
--A execução do VACUUM FULL dá um acesso exclusivo na tabela, e elimina as linhas mortas, dá um sumiço nos cadaveres, atualiza o índice da chave primaria e atualiza as estatisticas.
--A execução do VACUUM ANALYZE, não dá acesso exclusivo na tabela, atualiza apenas as estatísticas, usado para traçar a melhor rota ou forma de acesso a tabela, por exemplo na execução de uma consulta, podendo ser INDEXADA(INDEXSCAN) ou SEQUENCIAL(SEQSCAN)
--A opção VERBOSE mostra os detalhes da operação.
--Recomenda-se caso a tabela tenha mais índices, além da chave primaria dá um REINDEX TABLE teste;
Assinar:
Postagens (Atom)