Portage

Como o David Feeter me prometeu na pg-con ano passado o pessoal do postgreSQL implementou as famosas window functions. As “windows” são os dados “sobre” os quais você irá trabalhar. Daí o nome window e daí também o noe da mais famosa delas over.

Um exemplo simples tirado direto da documentação oficial do 8.4 pode nos ajudar a entender o que isso significa na pratica.

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname  | empno | salary |          avg
———–+——-+——–+———————–
develop     |    11|   5200 | 5020.0000000000000000
develop     |     7 |   4200 | 5020.0000000000000000
develop     |     9 |   4500 | 5020.0000000000000000
develop     |     8 |   6000 | 5020.0000000000000000
develop     |    10|   5200 | 5020.0000000000000000
personnel  |     5 |   3500 | 3700.0000000000000000
personnel  |     2 |   3900 | 3700.0000000000000000
sales          |     3 |   4800 | 4866.6666666666666667
sales          |     1 |   5000 | 4866.6666666666666667
sales          |     4 |   4800 | 4866.6666666666666667
(10 rows)
O que vemos aí é que foi pedido para calcular a média dos salarios “em cima do departamento”. Algo parecido com um agrupamento pelo departamento, só que sem agrupar na verdade. Com o uso da função partition by o que nós fizemos foi o seguinte, pedimos que o resultado fosse particionado ao invéz de agrupado.

A diferença entre particionar e agrupar é que no particionamento nós mostramos o resultado de nossa função de agregação (avg) para cada uma das linhas que nos fizeram chegar a esse resultado. No caso de um agrupamento nós traríamos apenas a média por desses salários por setor.

Em muitos casos pode ser útil ver não só a media dos salários por setor mas ainda cada linha que nos fez chegar a essa média.

Outro caso o qual me deparei varias vezes em uma migração foi o que a funcao escrita em Pl/SQL precisava apenas mostrar um numero de linha que deveria reinicar a cada mudança de um determinado campo.

Ex:

row_number() | funcionario | setor
——————+—————+————————-
1                      | coutinho     | db
2                      | magno        | db
3                      | marcos       | db
1                      | joao            | desenvolvimento
2                      | chico          | desenvolvimento
3                      | jose            | desenvolvimento
4                      | paulo          | desenvolvimento
(7 rows)

Isso é o resultado de um:

select row_numer() over(partition by setor), funcionario, setor from vw_funcionario_por_setor

Isso que nos dizer que queremos o numero de cada linha dentro da janela delimitada pelo setor.

O resultado visual é uma sequencia numérica que é reiniciada quando muda de setor.

Essas funções me fizeram muita falta, estou ancioso pelo 8.4.

Se alguém aqui já teve que fazer uma aplicação razoavelmente grande com rails e PostgreSQL, ou se mesmo sem a aplicação ser muito grande teve necessidade usar os “esquemas” do postgresql, viu que o rails, mesmo com o ruby, tem alguma dificuldade para trabalhar com eles.

Usando rails 1.x você podia usar algo como:

set_table_name “financeiro.pagamentos”

Já usando o rails 2.x isso gera um erro nas consultas no postgresql pois em um find normal a consulta é montada assim:

select * from “financeiro.pagamentos”

ao invés de

select * from “financeiro”.“pagamentos”

No rails 2 você consegue usar tabelas em vários esquemas desde que eles estejam no search_path, o que nem sempre é viável em algumas aplicações, sem contar que isso não funciona para a operações onde se precisa ler os metadados da tabela, uma vez que é necessário pesquisar os metadados no PostgreSQL usando o nome da tabela e o nome do esquema.

O bom é que a gente quase não usa consultas de metadados.
O ruim é que no Jruby elas são executadas a todo momento, pois o driver JDBC precisa saber os tipos de cada coluna para montar o result set e retornar para a aplicação cliente. Assim uma aplicação rails que acessa tabelas em diferentes esquemas ao ser migrada para de ruby para jruby simplesmente para de funcionar.

A solução que encontrei para isso foi criar um plugin através do qual eu tenho um metodo set_schema_name, semelhante ao set_table_name, assim eu posso especificar o nome do esquema onde está a tabela. Esse plugin pode ser usado também nso migrates para definir o esquema onde você está executando seus migrates.

Veja abaixo como é facil:

class CreatePagamento < ActiveRecord::Migration
set_schema_name :financeiro
def self.up
…
end

class Pagamento < activeRecord::Base
acts_as_schema_support
set_schema_name :financeiro
end

Por enquanto o plugin é só para JRuby, mas em breve vou fazer ele funcionar no Ruby também.

Aproveitei para ver como funciona o github e publiquei o plugin lá:
http://github.com/mondrian/acts_as_schema_support

Dúvidas, críticas e sugetões, enviem e-mail pra mim: coutinho em mondriantecnologia.com

Outra coisa interessante que ocorreu na migração da base de dados do Detran foi quando tivemos que migrar uma rotina que importava uns dados vindos de um Main Frame. Um arquivo em formato texto plano com uns 800 mil registros.

O detalhe interessante é que não havia um delimitador de campo. Os registros eram o que muita gente chamada de “tripa” de tamanho fixo. Um regostro lá era mais ou menos assim:

00001232000000023ANTONIO LUIZ DE EXEMPLO SILVARUA ALMERINDA DE EXEMPLO SILVA230320089874561239876567SSPRJ100001

Tempo Total do Processo usando Oracle: 2 Horas

Como o pessoal do oracle tratava isso? Eles usavam um recurso que permite que isso seja vinculado ao oracle como uma tabela, no DDL usada para fazer essa operação o usuário ou DBA pode mapear os campos dizendo o inicio e tamanho de cada campo. Após isso eles copiavam esses dados para outras tabelas usando selects.

Como o pessoal do PostgreSQL (equipe da Mondrian Tecnologia) tratou isso?

Fizemos uma função usando plpython que transformou o tal arquivo em um csv e a nossa linha de exemplo ficou mais menos assim:

“00001232″,”000000023″,”ANTONIO LUIZ DE EXEMPLO SILVA”,”RUA ALMERINDA DE EXEMPLO SILVA”,”2303200″,”8987456123″,”9876567″,”SSPRJ”,”10000″,”1″

Após isso nossa função executa um copy desse arquivo para uma tabela real do PostgreSQL onde o acesso é bem mais rápido que a uma tabela vinculada do oracle como na situação anterior, a partir daí copiamos os dados necessários para as demais tabelas também usando copy.

Tempo Total do Processo usando o estilo PostgreSQL de ser: 6 min

Enquanto o Ceará On Rails não chega e enquanto não se decide se a Pgcon 2009 vem para Fortaleza, eu vou aproveitar para compartilhar com os velhos amigos e novos amigos conseguidos durante a Pgcon 2008, as fotos do Happy Hour do último dia do evento:

Eu e David Fetter

–

A turma

–

Dextra

–

No Ceará On Rails eu tiro uma foto com o Akita

Atendendo a pedidos um exemplo de uma das funções que tivemos que implementar no PostgreSQL afim de diminuir a edição das funções oracle a serem migradas. A função months_between retorna a quantidade de meses entre 2 datas:

CREATE OR REPLACE FUNCTION public.months_between(datafinal date, datainicial date)
  RETURNS integer AS
$BODY$
declare
  retorno integer;
begin
  begin
	 select extract('month' from age(datafinal, datainicial))  into retorno;
  exception
	 when others then
	 RETURN null;
  end;
  return retorno;
end;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT

Espero que sirva de alguma coisa

Durante a migração da base de dados do Detran-CE para PostgreSQL, nos deparamos com algo interessante, o uso frequente de arrays passados como parâmetros de umas funções para outras, os quais eram manipulados para compor critérios de consultas e outras coisas. O interessante que eu vi nisso foi o modo como eles trabalhavam esses arrays como se eles fossem tabelas.

Daí surgiu a necessidades de nós também no PostgreSQL manipular-mos estes arrays, como se eles fossem tabelas, assim poderiamos fazer select neles e coisas parecidas, para isso criamos uma função em pl-python chamada cast_as_table, veja abaixo a implementação e uso da função cast_as_table:

CREATE OR REPLACE FUNCTION cast_as_table(a character varying[])
  RETURNS SETOF character varying AS
$BODY$
vall = []
x = a.replace('{','').replace('}','').split(',')
ant = ''
for v1 in x:
	if v1.startswith('"'):
		ant = v1
	elif v1.endswith('"'):
		n = ant + ',' + v1
		n = n.replace('"','')
	vall.append(n)
	ant = ''
	elif ant != '':
		ant = ant + v1
	else:
		vall.append(v1)
return vall
$BODY$
  LANGUAGE 'plpythonu' VOLATILE

A função acima é um exemplo parecido com uma das que utilizamos na migração do detran,  com ela podemos fazer um cast de um array para um conjunto de registros como sugere a declaração de retorno da função.  Famos ver agora como fica o select em uma das funções que recebia como parâmetro um array de string para ser usado na montagem do critério da consulta:

select
A.SF_BOR_SIT_CODIGO,
A.MU_MUL_DATA_SITUACAO,
A.MU_MUL_SEQ_HISTORICO
from cast_as_table(P_TAB_MULTA) A into
VSF_BOR_SIT_CODIGO,
VMU_MUL_DATA_SITUACAO,
VMU_MUL_SEQ_HISTORICO
where (mult.mm_oau_codigo, mult.mu_mul_numero,mult.mu_mul_sequencial)
IN (select A2.mm_oau_codigo,A2.mu_mul_numero,A2.mu_mul_sequencial from cast_as_table(P_TAB_MULTA) A2);

O exemplo acima foi um tanto ingênuo mas em muitos casos isso pode ser bem útil pois uma vez que podemos tratar o array como um conjunto de registro podemos nos utilizar de diversos recursos como filtros com where, junções com outros conjuntos de registros, etc.

Em breve disponibilizaremos o código fonte de nossa implementação em python da utl_file que é um recurso muito utilizado no Oracle para manipulação de arquivos. Com este pacote nós conseguimos migrar de forma transparente varios objetos os quais faziam uso desse recurso nativo do oracle.

Aguardem.

Essa pequena história é sobre a rotina do dia-a-dia da migração de Oracle para PostgreSQL no Detran-CE.

O que acontecia é que o pessoal da equipe da Mondrian Tecnologia sempre esquecia que o PostgreSQL estava rodando numa máquina igual os nossos desktops e sempre ficava com a mania de comparar a performance com a obtida no SGBD oracle de produção. Após fazer a comparação, quando os números não agradavam, o Marcos começava a re-escrever as instruções até obter uma performance superior a apresentada pelo SGBD Oracle em produção.

Ambiente de produção Oracle (rápido)
—————————-
Dell PowerEdge XXX
Processador: 2 Intel Quad Core Xeon
RAM: 8GB
Discos: 3 discos sata 300 GB, 10.000 rpm

Ambiente Desenvolvimento PostgreSQL (super rápido)
————————————
Lenovo
Processador: Intel Dual Core (1600 mhz)
RAM: 2GB
Disco: 160 GB sata 7.200

Dá para acreditar?