Josh Berkus hoje no Boteco 4linux
Josh Berkus, que faz parte da equipe Core do PostgreSQL desde 2002, falará sobre as principais características da nova versão 8.3, lançada no último mês de fevereiro. Entre as novas features que fazem parte do PostgreSQL 8.3 estão: suporte a XML, log em CSV (Comma Separated Values), Tsearch integrado, melhor monitoramento e HOT (Heap Only Tuples).
Para maiores informações acesse o site:
www.4linux.com.br
-Leo
Dia PostgreSQL na Unicamp
Maiores informações clique aqui.
Recordar é viver …
É antigo, mas somente pra descontrair:
pgfouine e log_line_prefix hacking
A ferramenta de análise de logs pgfouine é sem dúvida nenhuma uma ferramenta indispensável para qualquer DBA no quesito de análise de logs.
Confesso que fui utilizador assíduo do PQA (antecessor falecido do pgfouine) e quando conheci o pgfouine foi amor à primeira vista e acabei largando um pouco o ruby em troca de um php bem escrito ;-).
Deixarei um pouco a paixão de lado pra falar de uma funcionalidade caracterizada como deficiente no pgfouine, IMHO.
A questão é que para funcionamento correto, o pgfouine exige um formato padrão da variável de configuração log_line_prefix (‘%t [%p]: [%l-1]), e diga-se de passagem é bem conciso, mas muitas vezes não aceitável.
Quem já precisou alterar uma variável de um servidor que não pode ser reiniciado vai entender de que estou falando; ou pior ainda, quem precisa manter um formato consistente com normas internas porque existe uma política na empresa para arquivamento de logs também vai perceber.
Então, antes que tudo se desabe e voce no 5o. andar de seu prédio saia de maneira inconsolável de sua mesa em direção a janela mais próxima, eis que surgem as expressões regulares. Sim, sim elas!
Basta entender a forma como o parser do pgfouine trabalha para alterar nada mais de que (com sorte) uma única linha do núcleo desta ferramenta.
Deixemos de enrolação e vamos realmente ao que interessa:
O programa pgfouine.php inicializa o percurso que os trechos de log deverão seguir até o parser. Esta parte do fluxo tem por objetivo fazer algumas validações e configurações iniciais (informações de banco, opções escolhidas, &ca) e por fim carregar a classe GenericLogReader que baseada nas opções do usuário invoca o acumulador (Acummulator) de logs específico. A função do acumulador é avaliar o eventType de listener e armazenar o resultado em um stream para análise futura pelo parser mais adequado. Basicamente existem duas classes (tipos) maiores de eventType: uma de erro e outra de instruções SQL. Os detalhes sobre os event listeners podem ser localizados em $PGFHOME/include/listeners. A próxima etapa (e a que mais nos interessa), é a fase de parser os logs.
O pgfouine ainda não foi internacionalizado (i18n) portanto pode ser necessário alterar as expressões regulares em $PGFHOME/include/postgresql/PostgreSQLRegexps.lib.php. Basta ver qual o formato de seu arquivo de log e literalmente substituir as strings desse arquivo pelas correspondentes nos logs. Agora os arquivos responsáveis por gerar a informação que o pgfouine “confia” para criação dos gráficos, estatísticas e demais recursos visualizados no produto final, são os arquivos StderrPostgreSQLParser.class.php e SyslogPostgreSQLParser.class.php localizados em $PGFHOME/include/postgresql/parsers e estão na última etapa do percurso da conversão log -> documento. As classes StderrPostgreSQLParser e
SyslogPostgreSQLParser fazem a análise de stderr e syslog respectivamente como é óbviamente notado. Ambas possuem em seus construtores uma propriedade inicializando um objeto do tipo RegExp que é o assunto principal desse post e deve ser alterado para “casar” com o formato de seu log_line_prefix. Em nosso caso estava bem simples e foi apenas alterar o caracter especial de início de linha para “casar” com o formato do log, mas conhecendo um pouquinho de expressões regulares voce será capaz de combinar qualquer formato da linha de seus logs com o pgfouine sem a alteração da variável de configuração log_line_prefix e sem reiniciar o servidor de banco de dados.
-Leo
patch retira autovaccum do backend
Pessoal,
Conforme prometido no último pgcon e com apoio financeiro da BEA Systems, é com orgulho que envio o patch que tem como objetivo retirar o autovaccum do backend.
A principal idéia responsável por esse avanço foi a criação de uma ferramenta chamada autoclean.
O componente autoclean atua como um processo filho de postmaster e toda vez que uma página é alterada, o autoclean detecta quais páginas estão disponíveis para armazenar e em seguida mover para áreas disponíveis em VacPageData. Para isso free_space_pages deve estar configurado corretamente.
Outra função do autoclean é detectar páginas sujas e enviá-las para uma área reservada de reciclagem (C:\Meu Computador\Desktop\Lixeira) aliviando a carga do background writer.
Existe ainda a possibilidade de uma página, já reciclada, estar “parcialmente” suja e o autoclean avaliar de acordo com heurísticas uma chamada para a rotina void AutoCleanDirtyClothes(Page *page) que irá causar uma limpeza total daquela página e coloca-la em VacPageListData.
TransactionId wraparound
Com a implementação do autoclean foi criado também um novo tipo para a variável TransactionId para suprir a necessidade de “zerar” o contador de transações, o uint128, um novo tipo com suporte para 128 bits (o segredo aqui foi a geração de um algoritmo de ponto flutuante para suporte ao tipo em servidores de 32 e 64 bits). Dessa forma XID agora foi tipado com uint128.
Outra novidade é que transações já nascem marcadas como XID=2 (Frozen), então o MVCC não precisa mais “adivinhar” qual é a transação mais recente e … Espera aí!! Mas então porque precisamos tipar XID com 128 bits uma vez que usa apenas 1 ??? Hmm, hora de volta à prancheta …
Gente, quanta besteira!!!
Só pra não esquecer que hoje é 1º. de abril.
Abraço!
-Leo
plpgsql profiling
Um profiler é uma ferramenta para análise de performance de chamadas e procedures internas de sua aplicação.
Desde a versão 8.2 do postgres está disponível no pgfoundry o pldebugger, uma ferramenta de análise de plpgsql e depuração desenvolvida pela enterprisedb.
O modo de instalação é semelhante a qualquer outro módulo do contrib:
– colocar o pldebugger.tgz na raiz do código fonte do postgres;
– tar -zxvf pldebugger.tgz;
– cd pldebugger && make && make install;
– psql -c “LOAD ‘`pg_config –pkglibdir`/plugins/plugin_profiler'”; // ou adicione no postgresql.conf
Após carregar a biblioteca do profiler no banco são criadas novas variáveis de configuração runtime no postgres: plpgsql.profiler_tablename e profiler_filename.
O pldebugger possibilita encaminhar os resultados de uma sessão do profiler em arquivos no formato XML ou armazenar em uma tabela do banco.
Inicialmente vamos verificar como ficam os resultados em uma tabela.
Criar uma função para analisar (espero que tenham problemas mais complexos para profiling…):
-- Comment: Sem inspiração: Desculpe mas não estava inspirado pra arrumar outra forma de gravar no disco ... 1 CREATE OR REPLACE FUNCTION itera(OUT x NAME, OUT y OID) 2 RETURNS SETOF record 3 LANGUAGE 'plpgsql' VOLATILE 4 COST 100 5 ROWS 240 6 SET plpgsql.profiler_tablename TO profiler_itera 7 AS 8 $_$ 9 DECLARE 10 i RECORD; 11 j RECORD; 12 BEGIN 13 DROP TABLE IF EXISTS foobar; 14 CREATE TEMP TABLE foobar(a OID,b NAME); 15 FOR i IN SELECT OID,relname 16 FROM pg_class 17 GROUP BY 1,2 18 ORDER BY 2,1 19 LOOP 20 FOR j IN SELECT attrelid,attname FROM pg_attribute 21 WHERE attrelid = i.OID 22 GROUP BY 2,1 23 ORDER BY 1,2 24 LOOP 25 x := j.attname; 26 y := j.attrelid; 27 INSERT INTO foobar VALUES (y,x); 28 END LOOP; 29 RETURN NEXT; 30 END LOOP; 31 RETURN; 32 END; 33 $_$;
De acordo com a definição de nossa funcão, na linha seis, configuramos a variável profiler_tablename com o valor profilter_itera. Isto quer dizer que os resultados serão armazenados em uma tabela dentro da base atual com a seguinte definição:
Table "public.profiler_itera"
+-----------------+------------------+-----------+
| Column | Type | Modifiers |
+-----------------+------------------+-----------+
| sourcecode | text | |
| func_oid | oid | |
| line_number | integer | |
| exec_count | bigint | |
| tuples_returned | bigint | |
| time_total | double precision | |
| time_longest | double precision | |
| num_scans | bigint | |
| tuples_fetched | bigint | |
| tuples_inserted | bigint | |
| tuples_updated | bigint | |
| tuples_deleted | bigint | |
| blocks_fetched | bigint | |
| blocks_hit | bigint | |
+-----------------+------------------+-----------+
Cada linha desta tabela traz a informação relevante a um deteminado ponto de execução da função. Então para esta função que possue dezessete linhas de código fonte (contando quebras e identação), essa tabela possuirá 17 tuplas (registros/linhas) com informações pertencentes a execução da função itera() óbviamente não estimando o custo computacional de uma quebra de linha.
A saída é algo parecido com:
"Saida de profiling"
+----------------------------------------------------------+------------+------------+--------------+
| sourcecode | exec_count | time_total | time_longest |
+----------------------------------------------------------+------------+------------+--------------+
| | 0 | 0 | 0 |
| DECLARE | 0 | 0 | 0 |
| i RECORD; | 0 | 0 | 0 |
| j RECORD; | 0 | 0 | 0 |
| BEGIN | 0 | 0 | 0 |
| DROP TABLE IF EXISTS foobar; | 2 | 0.004682 | 0.004682 |
| CREATE TEMP TABLE foobar(a OID,b NAME); | 2 | 0.041431 | 0.041431 |
| FOR i IN SELECT OID,relname | 2 | 0.521529 | 0.521529 |
| FROM pg_class | 0 | 0 | 0 |
| GROUP BY 2,1 | 0 | 0 | 0 |
| ORDER BY 1,2 | 0 | 0 | 0 |
| LOOP | 0 | 0 | 0 |
| FOR j IN SELECT attrelid,attname FROM pg_attribute | 468 | 0.348036 | 0.054492 |
| WHERE attrelid = i.OID | 0 | 0 | 0 |
| GROUP BY 1,2 | 0 | 0 | 0 |
| ORDER BY 2,1 | 0 | 0 | 0 |
| LOOP | 0 | 0 | 0 |
| x := j.attname; | 3659 | 0.058126 | 0.017285 |
| y := j.attrelid; | 3659 | 0.005371 | 0.000207 |
| INSERT INTO foobar VALUES (y,x); | 3659 | 0.143374 | 0.03 |
| END LOOP; | 0 | 0 | 0 |
| RETURN NEXT; | 468 | 0.011699 | 0.010148 |
| END LOOP; | 0 | 0 | 0 |
| RETURN; | 2 | 0.000002 | 0.000002 |
| END; | 0 | 0 | 0 |
+----------------------------------------------------------+------------+------------+--------------+
Para os usuários de versões < 8.3, é possível utilizar um arquivo externo no formato de XML com o profiler. Basta configurar a diretira plpgsql.profiler_filename para um arquivo: SET plpgsql.profiler_filename TO ‘/home/leo/path_to_xml.xml’
O plugin traz ainda estatísticas mais detalhadas utilizando-se do módulo pgstattuple e um depurador de stored procedures que veremos no próximo blog.
Abraço!
-Leo
psql com extensao SQL
Todo mundo sabe o quão poderoso é o console interativo do psql.
Sinceramente não conheço nenhuma ferramenta que se iguale em qualquer plataforma em relação a funcionalidades (é lógico que os consoles irb1.8 e python também chegam bem próximo, mas para propósitos distintos).
A parte chata é quando voce digita linhas e linhas de consulta e toda vez que precisa alterar com o meta comando “\e” voce se depara com a necessidade de digitar :se syn=sql (vim). Lembro que vimrc não adianta por que ele considera a extensão do arquivo.
Não consegui fazer o vim (ou outro editor) converter o número do final do arquivo temporario do psql (tmp/psql.edit.9999) para syntax=sql e mesmo que conseguisse precisaria ainda assim fazer set de todos os plugins assistentes que são carregados em tempo de execução a partir da extensão .sql (diga-se de passagem, eu tenho vários plugins).
Então precisei alterar apenas uma linha do source do psql para incluir o sufixo .sql no nome do arquivo temporário. Imagino que se for para propor um patch para o PGDG, deveria ser criado uma variavel \pset para especificar qual a extensão do arquivo, mas aí a história já é outra…
Segue o patch:
Index: command.c
===================================================================
RCS file: /home/leo/pgrepo/pgsql/src/bin/psql/command.c,v
retrieving revision 1.186
diff -c -r1.186 command.c
*** command.c 1 Jan 2008 19:45:55 -0000 1.186
--- command.c 20 Feb 2008 20:37:40 -0000
***************
*** 1362,1371 ****
*/
#endif
#ifndef WIN32
! snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.edit.%d", tmpdir,
"/", (int) getpid());
#else
! snprintf(fnametmp, sizeof(fnametmp), "%s%spsql.edit.%d", tmpdir,
"" /* trailing separator already present */ , (int) getpid());
#endif
— 1362,1371 —-
*/
#endif
#ifndef WIN32
! snprintf(fnametmp, sizeof(fnametmp), “%s%spsql.edit.%d.sql”, tmpdir,
“/”, (int) getpid());
#else
! snprintf(fnametmp, sizeof(fnametmp), “%s%spsql.edit.%d.sql”, tmpdir,
“” /* trailing separator already present */ , (int) getpid());
#endif
plperl uncrosstab (descruzamento de tabelas)
Alguns já devem conhecer no módulo contrib/tablefunc as funções de cruzamento de tabelas (crosstab*). Para aqueles que não conhecem trata-se de módulos para manipular dados de referência cruzada, utilizado principalmente para manipular a dimensão dos dados.
A idéia é inversa ao objetivo do crosstab.
A estrutura da tabela é a seguinte:
+--------+---------+-----------------------------------------------------+
| Coluna | Tipo | Modificadores |
+--------+---------+-----------------------------------------------------+
| id | integer | not null default nextval('carros_id_seq'::regclass) |
| dono | text | |
| marca | text | |
| modelo | text | |
| ano | text | |
| placa | text | |
+--------+---------+-----------------------------------------------------+
e a tabela foi populada como segue:
carros
+----+----------+--------+--------+------+---------+
| id | dono | marca | modelo | ano | placa |
+----+----------+--------+--------+------+---------+
| 1 | Leo | Corsa | 2006 | 2006 | BBB9999 |
| 2 | Leonardo | C3 | 2006 | 2008 | AAA1111 |
| 3 | Foo | Audi | 2008 | 2008 | XXX1234 |
| 4 | Bar | Belina | 1970 | 1971 | XXX1234 |
+----+----------+--------+--------+------+---------+
(4 registros)
A brincadeira agora seria inverter a ordem dos registros, de forma que as colunas passassem a gerar um produto cartesiano com de forma que o atributo “dono” passe a ser cabeçalho de linha.
A versão em plpgsql ficou assim:
CREATE OR REPLACE FUNCTION reorganiza()
RETURNS SETOF carros_inverso
LANGUAGE 'PLPGSQL'
AS $_$
DECLARE
vRecordSet carros%ROWTYPE;
vNovaMatriz carros_inverso%ROWTYPE;
columns varchar[] := '{"marca","modelo","ano","placa"}';
i RECORD;
BEGIN
FOR vRecordSet IN SELECT * FROM carros LOOP
vNovaMatriz.dono := vRecordSet.dono;
FOR i IN 1..array_upper(columns,1) LOOP
IF columns[i] = 'marca' THEN
vNovaMatriz.valor := vRecordSet.marca;
ELSIF columns[i] = 'modelo' THEN
vNovaMatriz.valor := vRecordSet.modelo;
ELSIF columns[i] = 'ano' THEN
vNovaMatriz.valor := vRecordSet.ano;
ELSIF columns[i] = 'placa' THEN
vNovaMatriz.valor := vRecordSet.placa;
END IF;
RETURN NEXT vNovaMatriz;
END LOOP;
END LOOP;
END;
$_$
;
Esquisito, né? Eu também achei…
Ficou ruim para dar manutenção e restrita há um tipo de estrutura (tabela) apenas. Porém existem limitações da plpgsql que nos impedem de ir muito mais além disso, embora ainda seja possível melhorar um pouco. Mas deixemos plpgsql e vamos ver um modelo construído com as facilidades de plperl:
CREATE OR REPLACE FUNCTION reorganiza_p(IN colunas varchar,IN varchar, OUT nome TEXT, OUT valor TEXT)
RETURNS SETOF RECORD
LANGUAGE 'plperl'
AS $$
my ($c,$s) = @_;
my @columns = split(',',$c);
my $heap;
$_SHARED{nova_lista} = \$heap;
my $sql = sprintf('SELECT %s FROM %s',join(',',@columns),$s);
$_SHARED{exec_plan} = spi_prepare($sql);
my $sth = spi_query_prepared($_SHARED{exec_plan});
while (defined (my $row = spi_fetchrow($sth))){
foreach(@columns[1..$#columns]){
push @$heap, {nome =>$row->{@columns[0]},valor => $row->{$_}}
}
}
return ${$_SHARED{nova_lista}};
$$;
Hmm … ganhamos em alguns aspectos: Conseguimos fazer um modelo dinâmico que trabalha com qualquer tabela. Experimente, chame a função passando como argumentos uma string contendo todos os campos que se deseja colocar na formação cartesiana (o primeiro atributo da string será o cabeçalho de linha). O segundo parametro é o nome da tabela à que se quer fazer a reorganização (ou baderna …).
Veja o exemplo de utilização:
SELECT * from reorganiza_p(‘dono,marca,modelo,ano,placa’,’carros’);
carros
+----------+---------+
| nome | valor |
+----------+---------+
| Leo | Corsa |
| Leo | 2006 |
| Leo | 2006 |
| Leo | BBB9999 |
| Leonardo | C3 |
| Leonardo | 2006 |
| Leonardo | 2008 |
| Leonardo | AAA1111 |
| Foo | Audi |
| Foo | 2008 |
| Foo | 2008 |
| Foo | XXX1234 |
| Bar | Belina |
| Bar | 1970 |
| Bar | 1971 |
| Bar | XXX1234 |
+----------+---------+
(16 registros)
Bloguei!
Conversões implícitas no PostgreSQL (8.3)
Vejam bem, o assunto é polêmico, inconclusivo, recorrente… mas ainda assim vou dar continuidade.
Na versão 8.3 algumas conversões implícitas para o tipo texto foram retiradas à favor de avaliação de expressões envolvendo este tipo um pouco mais confiáveis. Então as expressões:
postgres=# \d foobar
Table “public.foobar”
Column | Type | Modifiers
——–+———+———–
a | integer |
b | text |
SELECT a=b FROM foo;
SELECT substring(9999,2);
[…]
… não devem mais funcionar.
Nosso problema é que a aplicação possuía muitos trechos de código utilizando este tipo de expressão. Ok, falha na aplicação, mas vai explicar isso pro cliente …
Os próximos parágrafos não pretendem de forma alguma discordar das decisões do PGDG em relação à retirada das conversões do núcleo do PostgreSQL e também não são aconselhados em hipótese alguma, salvo a minha situação que era emergencial 🙂 e decidi compartilhar. Estas medidas devem ser tomadas em caráter paliativo, enquanto a aplicação cliente não é corrigida.
Criando funções internas para as converções:
/* Baseado em $SRC/backend/utils/adt/int.c, int_text */
#include “postgres.h”
#include “fmgr.h”
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(int4_text);
Datum
int4_text(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
text *result = (text *) palloc(12 + VARHDRSZ);
pg_ltoa(arg, VARDATA(result));
SET_VARSIZE(result,strlen(VARDATA(result)) + VARHDRSZ);
PG_RETURN_TEXT_P(result);
}
O código acima deverá gerar um objeto compartilhado e colocado em libdir (pg_config –libdir) do servidor.
Em seguida podemos criar o objeto para gerenciar as conversões:
CREATE OR REPLACE FUNCTION text(int4) RETURNS TEXT AS ‘int4_text’,’int4_text’ LANGUAGE ‘C’;
E depois a conversão propriamente dita:
CREATE CAST(int4 as text) WITH FUNCTION text(int4) AS IMPLICIT;
Testando:
SELECT 1=’1′::text;
?column?
———-
t
(1 row)
#regression: gmake installation check
Pronto, deve ser suficiente para este tipo de conversão funcionar.
Não esqueça que deve ser uma medida paleativa apenas. Existe muito mais por trás de uma conversão do que se pode imaginar. Talvez assunto para um próximo post.
Sobre sequences e segurança multi-usuarios
Existem alguns motivos para utilizar a função embutida nextval() ao inicializar uma sessão no postgres e querer-se acessar uma sequence.
Um dos motivos internos é necessidade de inicialização de itens compartilhados em SeqTableData. Embora possua um tipo semelhante com Relation uma sequence não poderia ser armazenada e recuperada no índice do descritor de cache de relações pois as entradas poderiam não mais estar disponíveis no próximo acesso.
A solução então é armazenar os valores necessários para se manipular as sequences em uma estrutura própria do tipo SeqDataTable. Dentre outros atributos este tipo armazena informações necessárias para validar o acesso, o oid e último valor conforme a descrição:
struct SeqTableData{
Oid relid,
bool last_valid,
int64 last,
[…]
} SeqTableData;
Uma vez que já possuímos o tipo, agora é necessário inicializar as informações com uma versão interna de nextval() conhecida neste ponto por nextval_internal(). A função de nextval_internal é receber o OID da sequence à que se deseja obter informação em pg_class e atribuir em SeqTable; associar páginas no buffer; inicializar operações de bloqueio (AccessShare) para permitir leituras concorrentes e, como já é conhecido, incrementar o valor de last com o valor do atributo increment_by desta sequence.
Postgreslogia's Weblog 