Criando Ranks no Postgres usando Window Functions

Vamos supor que você tenha a seguinte tabela:

E você tenha que ordená-la de acordo com os seguintes critérios: a maior média por departamento, seguido de maior nota a, maior nota b e finalmente maior nota c. Fica bem simples se usarmos a cláusula order by:


SELECT department, name, grade_a, grade_b, grade_c
FROM candidates
ORDER BY department, ((grade_a + grade_b + grade_c) / 3) DESC,
grade_a DESC, grade_b DESC, grade_c DESC


Resultado:

Mas agora queremos criar uma qualificação indicando a posição do candidato de acordo com os critérios acima e esta posição deve ser relativa ao departamento que ele pertence, ou seja queremos saber os trê melhores qualificados no departamento "Development" e os três melhores no departamento "Marketing". Talvez muitos já estejam pensando em usar sua linguagem de programação favorita para resolver este problema mas no Postgres temos as Window Functions:


SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates


Resultado:

Legal, agora temos um número indicando a posição de cada candidato relativo ao seu departamento. Mas digamos agora que apenas os dois melhores candidatos de cada departamento devem ser retornados. Neste caso a seguinte query deve ser satisfatória:


SELECT *
FROM
(SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates) AS sub_query
WHERE rank < 3


Resultado:

Espero que estes exemplos possam te ajudar a entender como as Window Functions funcionam e como elas podem te ajudar.

Referência:http://postgresguide.com/tips/window.html

GROUP_CONCAT no Postgres

O MySQL tem uma função muito legal chamada GROUP_CONCAT. Ela concatena os valores de uma coluna em uma string separados por um separador que por padrão é uma vírgula(,). Para exemplificar considere a seguinte estrutura no banco de dados(pode ser usada tanto no MySQL quanto no Postgres):

create table countries_languages(
country_code varchar(4) not null,
language varchar(32) not null,
primary key(country_code, language)
);

insert into countries_languages(country_code, language) values('BR', 'Portuguese');
insert into countries_languages(country_code, language) values('THA', 'Chinese');
insert into countries_languages(country_code, language) values('THA', 'Khmer');
insert into countries_languages(country_code, language) values('THA', 'Kuy');
insert into countries_languages(country_code, language) values('THA', 'Lao');


Ao executarmos a seguinte consulta:

select country_code, language as languages
from countries_languages
where country_code = 'THA'


Obteremos a seguinte saída:

THA	Chinese
THA	Khmer
THA	Kuy
THA	Lao

Usando o GROUP_CONCAT nesta outra consulta:

select country_code, GROUP_CONCAT(language) as languages
from countries_languages
where country_code = 'THA'
GROUP BY country_code


A saída será:

THA	Chinese,Khmer,Kuy,Lao

Certo! Bem legal e útil quando quisermos exportar dados ou apresentá-los, mas como podemos fazer isso no banco de dados Postgres?

No Postgres não existe a função GROUP_CONCAT mas nele temos arrays. Uma forma de ter a mesma saída que o MySQL usando arrays em Postgres é:

select country_code, array_to_string(array(SELECT language FROM countries_languages WHERE country_code = 'THA'), ',')
from countries_languages
where country_code = 'THA'
GROUP BY country_code


O resultado da sub-consulta "SELECT language FROM countries_languages WHERE country_code = 'THA'" é convertido para um array que por sua vez é convertido para uma string pela função array_to_string com cada valor do array separado por uma vírgula. É mais verboso e exige uma sub-query mas o resultado é o mesmo.

Referência:http://stackoverflow.com/a/4469002

Truques SQL

Inserindo a partir de uma consulta SQL

Supondo que temos a seguinte estrutura de tabelas:

CREATE TABLE users(

id int not null,

name text not null,

email text

);

CREATE TABLE user_searches_results(

id int not null,

id_user int not null,

search_date date

);

Agora como nós podemos guardar todos os id's da tabela users na tabela user_searches_results retornados na seguinte consulta: SELECT id FROM users WHERE name LIKE 'Maria%' ?

Este seguinte comando sql é capaz de fazer a tarefa de forma muito eficiente:

INSERT INTO user_searches_results(id, id_user, search_date)

SELECT 1, id, now() FROM users WHERE name LIKE 'Maria%';

Simples não?

Retornando um registro aleatório a partir de uma consulta

MySQL

SELECT column1, column2, ... FROM table ORDER BY rand() LIMIT 1

PostgreSQL

SELECT column1, column2, ... FROM table ORDER BY random() LIMIT 1

SQL Server

SELECT TOP 1 column1, column2, ... FROM table

ORDER BY NEWID()

DB2

SELECT column1, column2, RAND() as IDX 

FROM table 

ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Oracle

SELECT column1 FROM

( SELECT column1 FROM table

ORDER BY dbms_random.value )

WHERE rownum = 1

Selecionar o segundo maior(ou menor valor) de uma coluna

SELECT MAX(column)

  FROM table

 WHERE column < (SELECT MAX(column) FROM table )

Use MIN se você quiser o segundo menor valor e não se esqueça do operador >.

Copiar a estrutura de uma tabela sem copiar os dados(MySQL, PostgresSQL)

CREATE TABLE users_2 AS (SELECT * FROM users WHERE 1=0);

Observe que índices e constraints não são duplicados.

Gerar SQL para atualizar os dados de uma tabela baseado no conteúdo de outra tabela(PostgresSQL)

Considerem a seguinte estrutura e dados:

CREATE TABLE departments(

id int not null,

name text not null,

employee_count int

);

INSERT INTO departments values(1, 'Development', 0);

INSERT INTO departments values(2, 'Sales', 0);

INSERT INTO departments values(3, 'Marketing', 0);

CREATE TABLE employees(

id int not null,

id_department int not null,

name text not null

);

INSERT INTO employees VALUES(1, 2, 'Ryu');

INSERT INTO employees VALUES(2, 2, 'Ken');

INSERT INTO employees VALUES(3, 3, 'Chun-Li');

INSERT INTO employees VALUES(4, 1, 'Guile');

Certo. Agora precisamos atualizar a tabela departments com o número de empregados que cada uma contém. Com o seguinte sql os comandos de update serão gerados automaticamente:

SELECT 'UPDATE departments SET employee_count = ' || COUNT(e.id) 

  || ' WHERE id = ' || e.id_department || ';'

FROM employees e

GROUP BY e.id_department;

A linguagem SQL pode ser mais poderosa e útil do você imagina.

Achando a chave primária anterior e posterior de uma chave primária existente

Estava eu trabalhando em uma pequena aplicação e o cliente me pediu para acrescentar um recurso onde ele poderia navegar entre os registros um a um. É um esquema muito parecido com grids de dados. Você está no registro número 50 e tem dois botões, um te leva para o registro 49 e outro para o registro 51.

Pensei rapidamente e primeira solução que veio foi obter todos os id's da tabela ordenados e desta forma usando o id atual obter o id imediatamente anterior e o id imediatamente posterior. Lembrando que os id's podem ter "falhas" entre eles, o registro depois do id 25 pode ser o registro de id número 30. Não é uma solução eficiente mas como a aplicação é pequena, vai ser usada por um pequeno número de usuários e aplicando o princípio "Premature Optimization is the root of all Evil" eu decidi deixar assim mesmo.

Mas mais tarde eu voltei a pensar sobre isso: "Puxa vida! Retornar 5000 valores para se usar apenas dois deles?! Deve existir uma solução que seja pelo menos elegante para este problema!". E existe! É possível retornar o id anterior e posterior de um dado id com apenas uma consulta, e aqui está ela:

Que legal! Vai retornar somente o que preciso e não tem problemas com "falhas" entre os valores e caso o valor não exista será retornado NULL. Tudo o que você tem a fazer é passar o id de referência para que possam ser encontrados os valores imediatamente anterior e posterior a ele, neste exemplo eu passei o valor 100 os valores retornados foram 99 e 101.

Eu não fiz nenhum teste de performance sério, mas usando a tabela students do banco de dados do post anterior sobre transações, que tem 5000 registros, a consulta por todos os id's demora em média 92ms no meu computador (o explain da consulta indica uma leitura de todos os registros da tabela como era de se esperar). Esta nova consulta que retorna apenas o que precisamos levou apenas 12ms em média para retornar os resultados. Eu não conheço muito sobre sql ou bancos relacionais, portanto, é bem provável que existam formas melhores de se fazer o que eu fiz, mas espero que este pequeno exemplo possa ajudá-los.

Aplicações com muitas Transações Concorrentes

Uma vez uma pessoa com quem trabalhei, por um breve período, me contou uma experiência que ela teve ao reescrever a lógica de uma aplicação diretamente no banco de dados. Eles passaram a usar triggers, stored procedures e funções que eram escritas e executadas no servidor de banco de dados. Eu perguntei a esta pessoa qual foi a razão deles terem feito isso. A pessoa me explicou que eles tiveram problemas de forçar a consistência das regras da aplicação quando muitos usuários acessavam a aplicação ao mesmo tempo. Por exemplo: A aplicação era usada em uma universidade para permitir que os alunos se registrassem para diferentes disciplinas. Ela reforçava regras para limitar a quantidade de alunos por disciplina e também para evitar que os alunos escolhecem disciplinas com horários conflitantes.

A pessoa envolvida nesta reescrita me explicou que não era possível reforçar as regras da aplicação somente na linguagem de programação cliente. Que em ambientes como muitas transações concorrentes a aplicação cliente do banco de dados vez ou outra vai ler dados obsoletos e gerar resultados errados. Eu não estava tão certo quanto esta pessoa de que isso era verdade. Eu sei que os bancos de dados providenciam comandos, que podem ser iniciados a partir do cliente, para iniciar transações e persistir os dados envolvidos ou simplesmente não persistir nada. E também temos os níveis de isolamento de transações.

Eu resolvi escrever um pequeno programa para testar se é realmente impossível escrever lógica de negócios usando uma linguagem de programação cliente, em um ambiente de muitas transações concorrentes. Para simular as muitas transações concorrentes eu vou usar o utilitário Apache Benchmark ou ab.

O banco de dados (dump postgres) é bem simples e consiste de apenas três tabelas: students(id, name, date_of_birth), courses(id, name, workload) e students_courses(id_student, id_course). O servidor é postgres versão 8.4. Quanto aos dados temos 5000 estudantes e 500 cursos. A regra de negócio bem simples, cada curso pode ter no máximo 10 estudantes registrados. Ou seja, na tabela sutdents_courses um curso pode ter no máximo 10 linhas com seu id na coluna id_course.

Quando o código da aplicação é executado ele escolhe um estudante e um curso ao acaso e tenta registrar este estudante para este curso escolhido. Primeiro a aplicação faz uma verificação para ver se o curso está disponível (verifica se existem menos de 10 estudantes registrados para o curso). Caso o curso esteja lotado o estudante não é registrado. Caso contrário uma linha é inserida na tabela students_courses.

A minha primeira tentativa foi usando PHP junto com a versão mais recente do CodeIgniter. Que pode ser baixada aqui. O código principal é este aqui:

Eu usei os comandos trans_start e trans_complete de acordo com a documentação deles.

O teste de concorrência foi feito chamando-se o ab da seguinte maneira:
ab -c 50 -t 30 http://localhost/www/transactions/index.php/course_registration_codeigniter_version

Os parâmetros indicam que o servidor será acessado durante 30 segundos por 50 conexões simultâneas. O último parâmetro é a url do servidor.

Depois do teste executando-se a seguinte consulta:

Eu percebi que a regra da aplicação tinha sido violada. Existiam cursos com mais de 10 alunos. Após este primeiro teste me perguntei se isso poderia ter sido resultado da implementação interna do CodeIgniter. Não cheguei a investigar como o CodeIgniter implementa transações mas resolvi fazer outro teste. Desta vez usando um script php simples que por sua vez usa PDO para acesso ao banco de dados. A parte mais importante do código está logo abaixo:

Mais uma vez o comando ab é executado apenas mudando-se a url:
ab -c 50 -t 30 http://localhost/www/transactions/course_registration_pdo_sem_locks.php

E mais uma vez eu vejo que a regra da aplicação foi quebrada com cursos com mais de 10 estudantes. Será que esta pessoa com quem trabalhei tinha razão?

No meu teste usando-se apenas transações não foi o suficiente para garantir a integridade das regras da aplicação. Fiz algumas pesquisas e achei este documento. Foi nele que ouvi falar do SELECT FOR UPDATE. O que também não funcionou. O problema é que a opção bloqueia as linhas retornadas pelo SELECT executado de sofrerem operações as operações UPDATE, DELETE e SELECT FOR UPDATE. Como meu objetivo não é alterar nenhuma das linhas retornadas mas sim inserir na tabela o SELECT FOR UPDATE não adianta neste caso.

O que eu realmente preciso é bloquear a tabela para que não sofra atualizações enquanto eu estou checando a regra da aplicação. Uma operação de LOCK. Basicamente vou bloquear a tabela para que não sofra alterações, checar a regra da aplicação e desbloqueá-la no fim da transação. A parte principal do código está logo abaixo:
Novamente testo com o comando ab:
ab -c 50 -t 30 http://localhost/www/transactions/course_registration_pdo.php

E agora sim. A regra da aplicação foi satisfeita, não temos mais cursos com mais de 10 alunos. Uma coisa que percebi foi que as consultas durante o teste aparentemente ficaram bem mais lentas, o LOCK de toda a tabela deve cobrar seu preço. Outra observação sobre o LOCK é que ele não faz parte do padrão SQL, portanto um código que possa rodar em diferentes bancos de dados deve ficar sob a responsabilidade do desenvolvedor.

Conclusão

Sim é possível utilizarmos uma linguagem de programação cliente para validarmos e reforçarmos as regras de aplicação com alta concorrência de transações. Mas para isso temos que usar recursos oferecidos pelo próprio banco de dados.

Acredito que o uso de linguagens de programação cliente nestes casos traz facilidades de desenvolvimento e manutenção associadas aos recursos das linguagens e das bibliotecas disponíveis. Mas também temos que lembrar que existe o outro lado da moeda, apesar de não ter testado isso, acredito que haveria ganhos de velocidade significativos programando-se a lógica da aplicação diretamente no banco de dados.

Como sempre não existe bala de prata, você deve analisar o contexto, requisitos e recursos disponíveis para tomar estas decisões.

Todo o código da aplicação pode ser obtido aqui. Não se esqueça configurar os dados de acesso ao banco de dados nos arquivos pdo e no arquivo application/config/database.php.

Como saber o tamanho de um banco de dados Posgre

Dica rápida! Como obter o tamanho do espaço em disco utilizado por um banco de dados postgre? O processo é tão fácil quanto executar um comando sql:

SELECT pg_database_size('nome_do_banco');

Este comando vai retornar o tamanho em bytes, existe uma outra função que ajuda a converter este valor para um formato mais fácil de se ler:

SELECT pg_size_pretty(pg_database_size('nome_do_banco'));

É isso aí!

Número de conexões no banco de dados Postgre

No lugar onde trabalho não possuo acesso a estatísticas e recusos administrativos do servidor de banco de dados postgre. Talvez fruto de um ciúme doentio da equipe de banco de dados com seus preciosos servidores. De qualquer forma precisei ver como estava o número de conexões com o servidor, e felizmente o postgre permite que estes número sejam acessados através de simples comandos sql.

Lembre-se de que as configurações para coleta de estatísticas devem estar habilitadas. Estas opções adicionam alguma penalidade na execução de queries. Por padrão as opções que permitem usarmos o comando sql apresentado aqui ficam habilitadas. Caso este não seja seu caso dê uma olhada no arquivo postgresql.conf. Mais detalhes no manual.

Finalmente aqui vai o comando:
select datname, count(*) from pg_stat_activity group by datname;

Com este comando podemos ver o número de conexões ativas no momento de execução agrupando-as pelo nome do banco de dados.
A saída será semelhante a isso:

  datname  | count
-----------+-------
 testdb    |     5
 template1 |     1
(2 rows)

A tabela pg_stat_activity também tem outras colunas com outras informações interessantes:

datid - oid do banco de dados
datname - nome do banco de dados
procpid - id do processo
usesysid - OID do usuário
usename - nome do usuário
current_query - query sendo executada atualmente
waiting - status de espera da query
xact_start - horário em que a transação atual começou a executar
query_start - horário em que a query começou a executar
backend_start - horário em que o processo foi iniciado
client_addr - Endereço do cliente
client_port - Porta do cliente

As colunas que mostram informações sobre a query só podem ser vistas pelo super usuário ou se o usuário que estiver vendo as informações for o mesmo do processo que está sendo listado.
Está aí pessoal uma dica rápida, espero que seja útil à vocês.

Criar banco de dados Postgre com encoding Latin1 em um sistema UTF-8

Estes dias eu estava passando por uma pequena dor de cabeça. Meu sistema operacional que uso para desenvolvimento (Ubuntu 10.10) está configurado com o encoding utf-8 e o locale para pt_BR. Acontece que eu estava tentando usar o pgAdmin III para criar um banco de dados com encoding Latin1 (o nosso famoso ISO-8859-1). Eu abria o pgAdmin, mandava criar um novo banco de dados, dava um nome para ele, na opção codificação escolhia LATIN1, dava ok, e pimba! Recebia a seguinte mensagem:
Um erro ocorreu:

ERRO:  codificação LATIN1 não corresponde a configuração regional pt_BR.utf8
DETALHE:  A definição de LC_TYPE escolhida requer codificação UTF8.

Eu recitava uma meia-dúzia de nomes feitos mas não sabia o que fazer. Eis que a luz veio da singela resposta deste cara no StackOverflow. Fui para o terminal, loguei como o usuário postgres, e executei o comando da seguinte forma:
createdb -T template0 NOME_BANCO -E LATIN1 --locale=pt_BR
E pimba novamente! (Desta vez um pimba bom). Funcionou mesmo, tenho um banco chamado NOME_BANCO com encoding LATIN1 em meio a outros bancos com encoding UTF-8.

StackOverflow salva o dia novamente.

Tamanho em disco de um banco de dados Postgre

Como saber quanto espaço em disco seu banco de dados Postgre está usando?
Simples, fácil e rápido:
SELECT pg_database_size('nome_banco');
Este comando exibe o tamanho em bytes.

Que tal exibir de uma forma mais amigável?
SELECT pg_size_pretty(pg_database_size('nome_banco'));