Planeta PostgreSQL-BR

Olá Amigos!

Hoje eu vou tratar de um assunto da época da faculdade, o cálculo da previsão de vendas de um determinado produto, com base nas vendas anteriores, para tal iremos utilizar o PostgreSQL e PHP.

Mas antes de mais nada, estou disponibilizando um documento do Excel com os mesmos dados da figura abaixo, juntamente com um gráfico contendo os resultados e informações importantes como o valor do r² e a equação do nosso modelo. Também achei importante disponibilizar os slides utilizados pelo Prof. Darlan quando este assunto foi ministrado, lá você irá encontrar mais conteúdo e uma bagagem teórica solida e enxuta.

Primeiramente vamos ao nosso problema:

Quanto eu devo investir em publicidade para ter um determinado ganho em vendas?

Quando nós chegarmos ao ponto de extrair os dados do Postgres perceberemos que na modelagem do banco estes dados (investimento e vendas) não tem nenhuma correlação, veja bem, do ponto de vista da modelagem. Por isso é muito importante que você compreenda bem nosso problema, e para simplificar na tabela abaixo temos os investimentos (ou gastos) com publicidade representado pela variável x, e as vendas representadas pela variável y. Note que além desses dados temos outros como x², y² e xy, que iremos utilizar posteriormente.

Veremos estes mesmos dados a seguir quando estivermos trabalhando no banco de dados, coloquei a tabela de ante-mão para abstrair melhor nosso trabalho, e  na seção seguinte do post iremos abordar os conceitos matemáticos envolvidos e os nomes das colunas (as variáveis) irão aparecer nas fórmulas, e como já vimos estruturadamente, ficará mais fácil.

O objetivo então é que possamos construir uma aplicação que nos informe qual será o valor que iremos vender, baseando-se no valor que iremos investir em publicidade.

Ahh, antes que os mais críticos se envolvam, gostaria de dizer que este é um exemplo simples, se você quiser pode melhora-lo de acordo com a sua necessidade

 

A matemática por traz do cálculo

Antes que muitos torçam o nariz ou pulem essa parte, já vou adiantando que nossa matemática será breve e simples. É fundamental que você entenda essas fórmulas (além de ler os slides que falei acima) para entender como funciona o nosso modelo.

E por falar em modelo, aqui temos um estatístico probabilístico, de regressão linear simples. Veja a representação do nosso modelo de equação abaixo:

Aqui temos que a variável y é explicada pela variável x, que por sua vez é explicativa. Então veja que, se estamos representando os gastos com publicidade por x, e as vendas por y, temos que os gastos com publicidade é que vão determinar o valor das vendas.

Note também que temos dois coeficientes, chamados de a e b. As duas fórmulas a seguir irão utilizar os nossos dados que estão na tabela (conforme a figura da primeira seção), que na  verdade iremos regastar do Postgres. Veja que abaixo temos a representação da fórmula onde iremos encontrar o valor de a:

Você deve ter percebido que para calcular o valor de a, antes é necessário obter o valor de b, pois bem, abaixo está a fórmula para o cálculo, veja um detalhe importante: o valor de n é exatamente o número de linhas da nossa tabela (ou o número de registros no banco de dados).

Agora que você já tem as duas fórmulas poderá chegar na equeção linear do nosso modelo, que será parecida com esta:

 y = 2,0754 + 2,0884.x

 

Como saber se o modelo é confiável?

Para sabermos se o nosso modelo é confiável contaremos com o Coeficiente de Correlação de Person, ou o famoso r². Podemos calcular o valor do nosso coeficiente com a singela fórmula abaixo:

O resultado de r², irá variar entre -1 e 1, de forma que dependendo da variação, ela terá estes significados:

• r² = -1 : Existe uma correlação inversa entra as variáveis, ou seja, quando uma aumenta a outra diminui.
• r² = 0 : Modelo ruim, quanto mais próximo de 0, menos acertivo será seu modelo.
• r² = 1 : Significa que seu modelo é muito bom! Ou seja, quanto mais próximo de 1, melhor!

No nosso exemplo, r² é de aproximadamente 0,93 e observe que ele está bem próximo de 1, o que nos indica que nosso modelo é consistente.

 

Construção das tabelas, queries, e dados no PostgreSQL

Este é o ponto mais importante do trabalho, aqui é onde iremos armazenar os dados que servirão de base para construírmos o modelo estatístico de previsão, para isso tome como base as duas tabelas a seguir conforme o modelo mostrado anteriormente:

CREATE TABLE investimento_publicidade (
	cod_investimento SERIAL,
	valor_investimento DOUBLE PRECISION NOT NULL,
	data_realizacao TIMESTAMP NOT NULL DEFAULT NOW(),
	PRIMARY KEY (cod_investimento)
);

CREATE TABLE vendas (
	id_produto SERIAL,
	nome VARCHAR(100) NOT NULL,
	preco_unitario DOUBLE PRECISION NOT NULL,
	quantidade_vendida INTEGER NOT NULL,
	PRIMARY KEY (id_produto)
);

Agora iremos abastacer o nosso modelo com os mesmos dados que utilizamos acima. Para agilizar e para podermos testar depois, disponibilizei o script SQL com as instruções INSERT abaixo:

INSERT INTO investimento_publicidade (valor_investimento) VALUES (3);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (4);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (8);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (12);
INSERT INTO investimento_publicidade (valor_investimento) VALUES (14);

INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto A', 7 , 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto B', 14, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto A', 15, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto C', 28, 1);
INSERT INTO vendas (nome, preco_unitario, quantidade_vendida) VALUES ('Produto C', 32, 1);

Muito bem! Agora temos os nossos dados, e neste ponto vamos extaí-los do banco para elaborarmos a equação do nosso modelo de previsão com o PHP!

Então, considere as duas queries abaixo, primeiramente a query que nos retornará os valores investidos em publicidade:

SELECT SUM(valor_inestimento) AS x_somatoria_invest_publicidade, SUM(valor_inestimento^2) AS x_quadrado
FROM investimento_publicidade

Veja o que estamos retornando nesta query: a nossa variável x, que é a somatória dos valores investidos em publicidade e o x², que iremos utilizar nas fórmulas.

E agora a query com os valores das vendas:

SELECT SUM((preco_unitario*quantidade_vendida)) AS y_somatoria_vendas, SUM((preco_unitario*quantidade_vendida)^2) AS y_quadrado
FROM vendas

Ok, temos retorno das variáveis y e y², repare que eu fiz umas brincadeirinhas, como a multiplicação do valor vendido pelo preço unitário, se você quiser pode brincar um pouco com os modelos depois alterando estes valores diretamente na tabela vendas.

NOTA: Eu poderia realizar toda a previsão de vendas somente dentro do Postgres, mas aqui quero utilizar o máximo de ferramentas possíveis, a previsão de vendas somente no PostgreSQL fica para um próximo post ok? =)

 

Exibição do modelo e simulação de dados com PHP

Veja abaixo o código de uma classe que irá realizar tanto os cálculos quanto as queries no Postgres, os comentários no código já explicam o que está acontecendo:

class PrevisaoVendas
{
	/**
	 * Atributo estático onde armazenaremos nosso objeto PDO para nossa comunicação com o banco de dados.
	 */
	private static $conn;

	/**
	 * Construtor da classe onde criamos e armazenamos nosso objeto PDO.
	 */
	public function __construct()
	{
		$opcoes_conexao = array(PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
								PDO::ATTR_PERSISTENT => true);

		self::$conn = new PDO("pgsql:host=127.0.0.1 dbname=PREVISAO_VENDAS", "postgres", "senha", $opcoes_conexao);
	}

	/**
	 * Método responsável por retornar um objeto (stdClass) com os valores do x e x quadrado (investimento em publicidade)
	 */
	private function getValoresInvestimentosX()
	{
		$sql = "SELECT SUM(valor_inestimento) AS x_somatoria_invest_publicidade, SUM(valor_inestimento^2) AS x_quadrado "
		     . "FROM investimento_publicidade";

		$stmt = self::$conn->prepare($sql);
		$stmt->execute();

		return $stmt->fetchObject();
	}

	/**
	 * Método responsável por retornar um objeto (stdClass) com os valores do y e y quadrado (vendas)
	 */
	private function getValoresVendasY()
	{
		$sql = "SELECT SUM((preco_unitario*quantidade_vendida)) AS y_somatoria_vendas, SUM((preco_unitario*quantidade_vendida)^2) AS y_quadrado "
			 . "FROM vendas";

		$stmt = self::$conn->prepare($sql);
		$stmt->execute();

		return $stmt->fetchObject();
	}

	/**
	 * Método que calcula o valor de xy, a dica é você olha no manual do PHP as funções array_combine e array_sum
	 */
	private function getValorXY()
	{
		/**
		 * Obtendo os valores de x;
		 */
		$stmt = self::$conn->prepare("SELECT valor_inestimento AS x FROM investimento_publicidade LIMIT 5");
		$stmt->execute();

		$x = array();

		while($rows = $stmt->fetchObject())
			$x[] = $rows->x;

		/**
		 * Obtendo os valores de y;
		 */
		$stmt = self::$conn->prepare("SELECT preco_unitario*quantidade_vendida AS y FROM vendas LIMIT 5");
		$stmt->execute();

		$y = array();

		while($rows = $stmt->fetchObject())
			$y[] = $rows->y;

		/**
		 * Calculando o valor da soma de xy
		 */
		$xy_aux = array_combine($x, $y);

		$xy = array();

		foreach($xy_aux as $key => $value)
			$xy[] = $key * $value;

		return array_sum($xy);
	}

	/**
	 * Obtem o número de registros de vendas, o "n" utilizado nas equações, observe que o número de vendas poderá não ser igual ao número de
	 * investimentos, caberá a você criar um mecanismo para definir que sempre os dados analizados estarão na mesma proporção e consistentes,
	 * uma dica é a utilização de LIMIT e OFFSET.
	 */
	private function getNumVendas()
	{
		$sql = "SELECT COUNT(*) FROM vendas";

		$stmt = self::$conn->prepare($sql);
		$stmt->execute();

		return $stmt->fetchColumn();
	}

	/**
	 * Método onde estamos resolvendo a equação para encontrar o valor de "a".
	 */
	private function getValorEquacaoA()
	{
		$x = $this->getValoresInvestimentosX();
		$y = $this->getValoresVendasY();
		$n = $this->getNumVendas();
		$b = $this->getValorEquacaoB();	

		$a = ($y->y_somatoria_vendas - ($b * $x->x_somatoria_invest_publicidade)) / $n;

		return $a;
	}

	/**
	 * Método onde estamos resolvendo a equação para encontrar o valor de "b".
	 */
	private function getValorEquacaoB()
	{
		$x  = $this->getValoresInvestimentosX();
		$y  = $this->getValoresVendasY();
		$n  = $this->getNumVendas();
		$xy = $this->getValorXY();		

		$b = (($n * $xy) - ($x->x_somatoria_invest_publicidade * $y->y_somatoria_vendas)) / (($n * $x->x_quadrado) - pow($x->x_somatoria_invest_publicidade, 2));

		return $b;
	}

	/**
	 * Método para a identificação do valor de r quadrado, onde veremos que conforme o valor do índice teremos o nível de confiabilidade do modelo.
	 */
	public function getValorRQuadrado()
	{
		$x  = $this->getValoresInvestimentosX();
		$y  = $this->getValoresVendasY();
		$n  = $this->getNumVendas();
		$xy = $this->getValorXY();	

		$r = (($n * $xy) - ($x->x_somatoria_invest_publicidade * $y->y_somatoria_vendas)) / (sqrt(($n * $x->x_quadrado) - pow($x->x_somatoria_invest_publicidade, 2)) * sqrt(($n * $y->y_quadrado) - pow($y->y_somatoria_vendas, 2)));

		$r_quadrado = pow($r, 2);

		return $r_quadrado;
	}

	/**
	 * Método para obteção de uma string contendo a fórmula do modelo.
	 */
	public function getStringFormulaModeloPrevisor()
	{
		$a = $this->getValorEquacaoA();
		$b = $this->getValorEquacaoB();

		$string = "y = " . number_format($a, 4, ",", "") . " + " . number_format($b, 4, ",", "") . ".x";

		return $string;
	}

	/**
	 * Método que capta um dado valor em investimento de publicidade, e por meio do nosso modelo, retorna a previsão das vendas.
	 */
	public function getValorSimuladoVendas($valor_invest_publicidade)
	{
		$a = $this->getValorEquacaoA();
		$b = $this->getValorEquacaoB();

		$y = $a + ($b * $valor_invest_publicidade);		

		return $y; // Nosso valor estimado das vendas.
	}
}

Veja um exemplo de utilização da classe:

$preview = new PrevisaoVendas();

$preview->getStringFormulaModeloPrevisor(); // Irá nos retornar a fórmula.
$preview->getValorRQuadrado();              // Valor do r quarado com a precisão do nosso modelo.
$preview->getValorSimuladoVendas(10.0);     // Retorna o valor das vendas, se no caso, invesrtirmos $ 10,00 em propaganda.

Então amigos, é isso ai, espero que vocês tenham gostado da simplória abordagem. Eu gostaria muito de saber das experiências de vocês, então fiquem a vontade para comentar!

 

Referências:

• Análise de Regressão – Slides
• Wikipedia – Coeficiente de Correlação de Person
• PHP Documentation – Função pow()
• PHP Documentation – Função sqrt()
• PHP Documentation – Função array_combine()
• PHP Documentation – Função array_sum()

Abraços,

Tiago.

Comecei com o postgresql de uma forma muito particular, nem desejava ser DBA na época. Hoje não somente trabalho com postgresql, como também adoro o que faço, não têm banco.de dados que me agrade mais de que postgresql; Oracle, Sql Server, DB2, firebird, nenhum deles tem o “sabor” do postgresql. Quando observo o mercado, preocupo-me [...]

Olá novamente caros leitores!

Primeiramente me desculpem pela demora para liberar uma nova postagem sobre o Postgres, é que as últimas semanas foram muito movimentadas, realizei minha entrevista na UFSCar para o ingresso no programa de mestrado do Departamento de Computação e ainda na semana retrasada recebi a notícia da minha aprovação, então imaginem o tamanho da felicidade e a correria com as festas de fim de ano.

Bom, mas vamos ao que interessa, hoje irei falar sobre as triggers, ou funções de gatilho, como você queria chamar. Uma das coisas mais interessantes da implementação das funções no Postgres é que você pode escolher qual linguagem vai usar para programar sua trigger! Isso mesmo, então antes de mais nada, eu separei abaixo a lista de algumas linguagens, e o link para mais informações sobre elas, veja:

• PL/PGSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java (veja aqui ou aqui como instalar a PL/Java)

Agora que você já sabe dessa maravilha do Postgres irei dizer que aqui, iremos exemplificar as triggers utilizando a PL/pgSQL. Mas antes da prática no código fonte, iremos trabalhar um pouco sobre a definição e funcionamento das triggers no PostgreSQL.

Você deve saber que os nossos gatilhos funcionam baseados em eventos, dois: before (antes de alguma coisa) e after (depois de alguma coisa). Explicando: você pode determinar que uma trigger dispare após (after) a inserção em determinada tabela do banco ou antes (before) da tal inserção, por exemplo.

É muito importante que você dê uma olhada abaixo na lista de variáveis que podem ser utilizadas dentro da estrutura da função da sua trigger, vale a pena dar uma lida, no entanto existem duas que a leitura é obrigatoria, que são a new e a old. Elas são usadas da seguinte maneira:

• NEW: No caso da trigger ser disparada por um INSERT a variável NEW irá abrigar os valores a ser inseridos, e caso seja um evento do tipo UPDATE, NEW irá conter a nova versão dos valores a serem atualizados (veja o exemplo no código abaixo).
• OLD: Esta variável é utilizada no caso do evento DELETE armazena os dados que estão sendo excluídos, e no caso do UPDATE a versão antiga dos dados.

O acesso dos dados se dá em ambas as variáveis dessa maneira: NEW.COD_CLIENTE, por exmplo. Abaixo você verá o exemplo de uma trigger.

 

Outras variáveis especiais que você pode usar:

• TG_NAME: Contém o nome do trigger que foi disparado.
• TG_WHEN: Contem quando o evento foi disparado, sendo BEFORE ou AFTER.
• TG_LEVEL: Diz em qual ponto da trigger.
• TG_OP: Tipo de operção que está sendo executada: INSERT, UPDATE ou DELETE.
• TG_RELID: Contém a ID do objeto que está disparando o gatilho.
• TG_RELNAME: Nome da tabela que disparou o gatilho (obsoleto).
• TG_TABLE_NAME: Nome da tabela que disparou o gatilho.
• TG_NAME_SCHEMA: Nome do schema onde está a tabela que disparou a trigger.
• TG_NARGS: Número de argumentos fornecidos para a Stored Function do trigger.
• TG_ARGV[]: Os argumentos que foram fornecidos.

 

Perguntas (quase) nunca feitas sobre triggers:

• Sabe-se que pode atrelar mais de uma trigger por tabela, então qual a ordem de execução das triggers?
  R: A ordem de execução é alfabética.
• Triggers podem ser recursivas?
  R: Sim! Os gatilhos podem executar comandos SQL, e os mesmos podem disparar outras triggers. Este cenário é conhecido como cascateamento de triggers, assim pode ser que a trigger passe a se “auto disparar”, ou seja, chamadas recursivas da própria trigger, fique atento a recursões infinitas!

 

Triggers na Prática

Agora vamos a parte pática irei realizar um único exemplo, neste link você e encontrar mais exemplos de triggers. No nosso, considere as duas tabelas abaixo:

CREATE TABLE funcionarios (
	ID_FUNCIONARIO SERIAL,
	NOME VARCHAR(255),
	IDADE INTEGER,
	FUNCAO VARCHAR(150),
	PRIMARY KEY(ID_FUNCIONARIO)
);

CREATE TABLE FUNCIONARIOS_LOG (
	COD_ALTERACAO SERIAL,
	USUARIO VARCHAR(150) NOT NULL,
	TIPO_ACAO VARCHAR(25) NOT NULL,
	DATA_ALTERACAO TIMESTAMP NOT NULL,
	PRIMARY KEY (COD_ALTERACAO)
);

Repare que na construção da tabela funcionarios eu não coloquei nenhuma regra de validação, como por exemplo not null, foi de propósito! Nossa trigger irá realizar a (simples) validação da entrada de dados na tabela e em seguida guardar que tipo de operação foi feita, por quem e a data na tabela funcionarios_log.

Agora a construção da stored function que será “chamada” pela trigger e sua agregação como trigger:

CREATE FUNCTION valida_dados_funcionario() RETURNS TRIGGER AS $valida_dados_funcionario$
BEGIN
	IF NEW.NOME IS NULL THEN
		RAISE EXCEPTION 'Por Favor, digite o nome do funcionario!';
	END IF;

	IF NEW.IDADE IS NULL THEN
		RAISE EXCEPTION 'Por favor, informe a idade!';
	END IF;

	IF NEW.IDADE < 0 THEN
		RAISE EXCEPTION 'Desculpe, o funcionario não pode ter % anos', NEW.IDADE;
	END IF;

	IF NEW.FUNCAO IS NULL THEN
		RAISE EXCEPTION 'Por favor, informe qual a função do funcinário!';
	END IF;

	-- Aqui iremos gravar no log o tipo de ação que foi realizada
	INSERT INTO FUNCIONARIOS_LOG (USUARIO, TIPO_ACAO, DATA_ALTERACAO) VALUES (CURRENT_USER, TG_OP, CURRENT_TIMESTAMP);

	RETURN NEW;
END;
$valida_dados_funcionario$
LANGUAGE plpgsql;

Note que realizei as devidas validações utilizando o operador NEW, porque agora iremos atrelar a trigger a tabela dizendo que ela será executada durante um insert ou update. Observe também que o declaramos um return “trigger” e especificamos os delimitadores $$, você pode encontrar mais informações sobre a estruturação de procedures de triggers na documentação do PostgreSQL, e por fim note que especificamos qual linguagem é utilizada para a construção da trigger, no caso plpgsql.

Veja abaixo como definimos a trigger, chamando a procedure que construímos anteriormente:

CREATE TRIGGER validacao_insert BEFORE INSERT OR UPDATE ON funcionarios
FOR EACH ROW EXECUTE PROCEDURE valida_dados_funcionario();

Pronto! Agora sua trigger já está funcionando nos eventos INSERT e UPDATE na tabela funcionario, faça os testes!

Agora para ilustrar ainda mais nosso exemplo irei alterar o nome da trigger, veja:

ALTER TRIGGER validacao_insert ON funcionarios RENAME TO a_validacao_insert

Para desabilitar (para habilitar troque o DISABLE por ENABLE) uma determinada trigger:

ALTER TABLE  funcionarios DISABLE TRIGGER a_validacao_insert

Ou para desabilitar todas as triggers de uma tabela:

ALTER TABLE  funcionarios DISABLE TRIGGER ALL

Agora, se eu desejar remover a trigger:

DROP TRIGGER a_validacao_insert ON funcionarios

Repare que não postei a imagem dos resultados para induzir você a testar o código! Faça os scripts de insert e update e veja as saídas produzidas pelo SGBD, acredito que você irá se divertir muito com os resultados!

Se você quiser saber mais sobre os triggers e sobre o PostgreSQL eu recomendo um livro (se você também for chegado a papel) que utilizei na minha monografia, intitulado PostgreSQL: Guia do Desenvolvedor, de André Milani.

Como você que leu (conseguiu chegar até aqui) triggers são um assunto muito extenso para se tratar aqui no blog, recomendo que você leia as referências e pesquisa mais sobre o assunto, é um tema muito interessante e útil para o dia a dia!

 

Referências:

• PostgreSQL Documentation – Triggers
• PostgreSQL Documentation – Trigger Procedures
• Wikibooks – PostgreSQL – Triggers
• iMasters – Triggers no PostgreSQL
• Ecoapre – Disparadores triggers en PostgreSQL (en espanhol)

Abraços,

Tiago.

    Na maioria dos casos de uma instalação do PostgreSQL é modificado o sysctl para usar melhor (tuning) os recursos disponíveis num servidor. O Debian tem um diretório chamado /etc/sysctl.d para configurações personalizadas em pacotes específicos como o pacote do PostgreSQL.

    Para fazer a modificação (tuning) do sysctl no modo Debian (Debian-like), o arquivo para fazer modificação é /etc/sysctl.d/30-postgresql-shm.conf.

    Efetivar a modificação sem reiniciar o servidor, é:

sysctl -p /etc/sysctl.d/30-postgresql-shm.conf

Olá meus queridos!

Na semana passada eu abordei a questão dos índices no PostgreSQL, onde citei que eles podem estar dentro de um dado tablespace, pois bem, neste artigo irei abordar como funciona e para que você pode utilizar esse fantástico recurso do nosso querido Sistema Gerenciador de Banco de Dados – Postgres.

Você se lembra dos Schemas, que fazem a divisão lógica do banco? Pois bem, um tablespace faz a divisão física, isto é, podemos determinar onde os elementos do banco de dados – leia-se tabelas e índices – irão ficar. Mas como assim “irão ficar”? Simples: você pode definir um dado lugar no disco rígido, ou até mesmo outro disco!

Imagine que você tem um índice que é muito acessado e uma tabela que muito raramente é acessada, pois bem, você pode colocar esse índice em um disco SCSI e a tal tabela em um HD SATA comum, tendo em vista que o adaptador SCSI é muito veloz em comparação com o SATA. Fazendo isso você terá um ganho de desempenho muito significativo.

Veja abaixo as instruções SQL para a manipulação dos tablespaces.

 

Sintaxe para a criação de um tablespace:

CREATE TABLESPACE “hd_scsi” LOCATION '/mnt/seu_scsi'

 

Criar tabela dentro de um dado tablespace:

CREATE TABLE foo (
    id integer,
    nome varchar(150),
    primary key(id)
) TABLESPACE hd_scsi;

 

Veja como alterar um índice de tablespace:

SELECT ‘ALTER INDEX’, n.nspname AS schemaname , ‘.’ ,c.relname AS tablename, ‘SET TABLESPACE hd_scsi;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_index x ON x.indexrelid = c.oid
WHERE c.relkind = ‘i’::”char”
AND x.indisprimary != ‘t’
AND x.indisunique != ‘t’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

 

Veja como alterar uma tabela de tablespace:

SELECT 'ALTER TABLE' ,n.nspname AS schemaname,’.', c.relname AS tablename, ‘SET TABLESPACE hd_scsi;’
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

 

Referências:

• Kenia Milene G.’s Blog - Alterando Tablespace de Tabelas e Indices no PostgreSQL
• PostgreSQL Manuals – Tablespaces
• PostgreSQL BR – Tablespaces no PostgreSQL
• Devmedia – Tablespace

Semana que vem irei abordar o funcionamento das triggers!

Abraços,

Tiago.

Outra forma de mostrar a descrição de uma função em postgreSQL é SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname ilike 'nome_funcao'; Nota: trocar nome_funcao pelo nome da função desejada. Abraços!

   

 

Participei de um excelente evento, provavelmente melhor evento PostgreSQL realizado no Brasil. Palestras de excelente nível técnico, boa organização de stand, coffe break, happy hour, etc. Tudo andou muito bem, os dois Kahuna (Telles e Flávio) estão de parabéns!!!

   Não pude ficar até o fim pois tinha um outro evento importante (Rubyconf era nos mesmo dias e gostaria de ir mas não era esse evento).

      

     

    Sobre Pearl Jam fica para outro post…

    Voltando, o evento foi extraordinário porém alguma coisa mudou e não foi o evento. Acho que eu mudei, não tenho a mesma pegada (empolgação) por coisas ligadas à Infraestrutura.

    Antes que digam que sou o maior defensor do mundo NoSQL, não sou defensor de uma tecnologia. A minha questão, talvez, esteja ligada aos desafios em desenvolvimento de sistemas (principalmente programação paralela e/ou funcional) e arquitetura distribuídas. Meu tempo de pegar um carro e passar horas/dias mexendo nele para conseguir o melhor desempenho possível já passou.

    Nada contra para quem continua fazendo, o mundo precisa muito de profissionais assim. Talvez parte da minha "frustração" seja pelo debate que teve na mesa redonda "O mercado de serviços em PostgreSQL no Brasil", esperava um pouco mais dos "empresários".

   Acho que a questão é que infraestrutura é commodity, como banco de dados é parte da infraestrutura e será cada vez um nicho segmentado. 

   Vamos ver daqui três meses se eu fiquei pirado na batatinha ou não.

 

 

PS. Alias, minha apresentação não teve pergunta. Então, foi um sucesso total ou fracasso retumbante. Comenta aí…

Agora que o PGBR2011 está quase acabando, que as últimas prestações de contas estão terminando, os fornecedores sendo pagos e as últimas palestras sendo publicadas no site, eu me deparei com mais uma boa provocação do Fernando Ike. Ok, o post é do dia 10/11, mas eu só vi hoje. Ainda estou bem atrasado com um monte de coisas e hoje de manhã comecei a passar varias coisas em revista. Então deu aquela vontade de escrever….

O #PGBR2011 foi um marco para mim. Morreu aquele jeito moleque de juntar a turma e ficou claro que a “pegada” da comunidade mudou. A turminha que se conheceu há 10 anos atrás nos FISLs da vida cresceu e tem desafios para lá de cabeludos na mão. Eu mesmo jamais imaginaria que estaria trabalhando com um dos cases mais importantes de Postgres do Brasil há 10 anos atrás. Acho que eu nem imaginava que daria para fazer o que a gente faz com o Postgres hoje naquele tempo. Mas a gente apostou, eu consegui abandonar a minha vida de DBA Oracle e estou feliz da vida me matando de trabalhar com Postgres quase em tempo integral (aka 24/7).

O evento foi num hotel. Não foi numa universidade, não foi num lugar barato. E para a nossa surpresa: fomos nós mesmos que pagamos boa parte da conta: Eu, o Euler, Fabrízio, o Charly, o Coutinho, etc. Nós patrocinamos o evento. Nós pudemos tirar $$ do bolso para fazer o evento acontecer. Para quem não sabe, eu e o Euler Taveira, somos Sócios da Timbira, que está já há uns 2 anos na estrada. A gente nunca teve muita pressa de crescer. Nem no nosso site a gente investiu muito. Na verdade a gente está tendo de expandir para dar conta da demanda. Não, ninguém está ganhando muito dinheiro. Talvez empresas maiores como a Dextra e a 4Linux estejam melhor. Certamente a VMware vai bem. Mas estamos vivendo disso. No caso da Timbira, vivendo só disso. E temos encontrado bons parceiros no caminho. Direta ou indiretamente, estivemos presentes em várias palestras comigo, com o Euler Taveira, com o Dickson Guedes, com o Roberto Mello, com o Fabrízio Mello e tem mais um que logo logo deve estar finalmente assumindo seu lugar junto a Timbira. E tem mais, eu, o Euler e o Guedes (os 3 dos 4 fundadores da Timbira) ganharam prêmios pela nossa atuação na comunidade durante o evento. Não, nenhum de nós fez parte da comissão que elegeu os premiados. Acho que dificilmente vamos encontrar alguém que respira mais Postgres no Brasil do que o Euler Taveira. Não foi por acaso que ele foi nomeado para 4 das 5 categorias do “Prêmio PGBR2011″. É realmente uma honra ter estes caras como colegas de trabalho. Mas eu também estou finalmente dando meus pulinhos. Hoje eu teria tanta coisa para escrever aqui no blog que eu nem sei por onde começar. Preciso de um pouco de tempo para voltar a escrever para valer.

Não é pouca coisa né? Mas este ano foi a primeira vez que mandamos fazer um folder da Timbira. A gente passa tanto tempo trabalhando com Postgres que não tivemos tempo de “aparelhar” a nossa empresa. Sim, temos que melhorar isso. Bom, mas tirando a propaganda toda, (que é a primeira vez que eu cito aqui no blog), a questão é que agora a nossa pegada mudou. Não somos mais moleques. Ninguém mais é. Mesmo o Diogo Biazus com o seu novo visual Punk, sabe que a fase da molecagem passou. Ele também é empresário. E são empresários a maioria dos palestrantes que estiveram no evento. Nem tudo são flores. Eu descobri por exemplo que o Carlos Smanioto, um dos ícones que começou a publicar os primeiros artigos sobre Postgres na SQL Magazine estava com dificuldades alguns meses antes do PGBR2011. Outros dois grandes colegas excepcionais ficaram desempregado recentemente. Eu também tenho minhas dívidas para pagar…. Não estamos vivendo de brisa. Estamos trabalhando muito, errando muito e aprendendo muito. No entanto, me arrisco a dizer que estamos felizes. Não é fácil explicar isso para nossas famílias. Mas é bem verdade. Eu posso dizer que trabalho com o que eu gosto e isso não é para qualquer um.

Mas voltando a provocação do Sr. Fernando Ike. Eu tenho sempre medo do que esse cara diz, pois ele tem a mania de acertar – ele também tem a mania de ser sorteado para alguma coisa em todo evento que vai. Já vi ele ser sorteado 2 vezes num único evento. E essa história de banco de dados virar comodity, já está escrito nas estrelas faz um tempo. Quando você vira especialista numa coisa, não quer que ela deixe de existir, pois você se dedicou por anos a fio para adquirir um bom domínio naquilo. Os bancos da dados relacionais não vão deixar de existir. Mas quem acha que eles vão continuar com o seu “reinado”, como o centro do universo que muito DBA acha que é… já caiu do cavalo. Isso está ruindo mesmo. Claro, isso que não muda tão rápido assim. Ninguém acha que as transações bancárias vão começar a rodar em bases NoSQL. Mas é inútil negar que tem muita informação que não precisa de um banco de dados transacional 100% ACID para cuidar. E os novos desafios estão aí para quem quiser enxergar ou não.

Enquanto a adoção do Postgres cresce no mundo todo e notoriamente no Brasil, um novo movimento surge quebrando paradigmas e a gente vai ter de aprender a conviver com isso. A palestra da VMware estava lá para quem quisesse enxergar. A revolução acontecendo bem na nossa cara. Eu sei que o Ike tem uma visão clara disso. Quando você se afasta e olha mais de longe, consegue ver o mundo girar. Esse negócio de nuvem… tem muita balela e gente querendo vender coisa velha com nome novo. Mas quando eu olho o número de clientes nossos que estão colocando seus software como SaaS, é notavel que as coisas estão mudando. E olha só, a Locaweb estava lá no evento…

É pessoal, eu não sei o próximo PGBR vai rolar ou como ele será. Sei que é bem possível que ele ocorra. Não sei se eu terei tempo para me dedicar a ele como me dediquei em 2011. Nem se as minhas prioridades estarão neste rumo até lá. Mas o mundo está girando… independente de nós sentirmos isso ou não. Os polos magnéticos vão se inverter, uma nova era do gelo vai vir e um novo super continente vai se formar. Precisamos mesmo olhar para fora da caixa. Mas enquanto isso, posso dizer que o Postgres tem me surpreendido muito. Tem se mostrado um grande parceiro, e trabalhar com ele tem sido divertido.

Bom, agora tem um monte de coisas para fazer aqui, até a próxima cervejada pessoal!

OBS: Antes que alguém pergunte… o nome Timbira foi escolhido pelo Euler que tem um blog com esse nome bem antes da “Empresa Timbira nascer”. Ele ajudou muito na tradução da documentação e internacionalização do Postgres. Como ele é Goiano e tem lá uma paixão louca pelo Brasil, eu acho que escolher um nome indigena para a nossa “Empresa Brasileira de Postgres” faz até muito sentido. Claro, é bem engraçado quando as pessoas pedem para repetir o nome da empresa umas 2 ou 3 vezes até entender. Todo mundo espera um buzzword em inglês. Mas depois de um tempo, eu achei o nome tão bacana quanto continuar escrevendo um blog em pt_BR e fazer parte do nosso planeta tupiniquim, . De toda forma, quanto mais masterfucking é o cliente, mais legal é ver a Timbira entrando lá de verde e amarelo…

Você já criou uma tabela cruzada, ou crosstab, utilizando SQL? Você sabia que era possível? Possivelmente você nunca precisou, mas criar crosstabs é um recurso à disposição que pode ser bastante útil!

Tabelas cruzadas apresentam mais de uma dimensão de forma integrada aos usuários, sendo uma forma importante para a melhor visualização de dados consolidados. Geralmente o cruzamento de dados neste tipo de tabela é feito na camada de apresentação das aplicações ou em componentes geradores de relatórios, mas isso não impede que você também possa implementar crosstabs via banco de dados.

Vamos exemplificar a criação de crosstabs no postgresql utilizando as tabelas abaixo:

* Tabela Loja - Basicamente apresenta a descrição da loja

-- Tabela Loja
CREATE TABLE loja (codigo integer, nomeloja varchar(20), observacao varchar(250));
INSERT INTO loja VALUES (1, 'Loja 1', 'Matriz');
INSERT INTO loja VALUES (2, 'Loja 2', 'Filial 1');
INSERT INTO loja VALUES (3, 'Loja 3', 'Filial 2');
INSERT INTO loja VALUES (4, 'Loja 4', 'Filial 3');

* Tabela Vendas - Apresenta as vendas realizadas para cada mês em cada loja.

-- Tabela Vendas

CREATE TABLE vendas (seqvenda integer, codloja integer, mesvenda integer, valor integer);
INSERT INTO vendas VALUES (1, 1, 1, 100);
INSERT INTO vendas VALUES (2, 1, 2, 100);
INSERT INTO vendas VALUES (3, 1, 3, 100);
INSERT INTO vendas VALUES (4, 1, 1, 100);
INSERT INTO vendas VALUES (5, 2, 2, 100);
INSERT INTO vendas VALUES (6, 2, 3, 100);
INSERT INTO vendas VALUES (7, 2, 1, 100);
INSERT INTO vendas VALUES (8, 2, 2, 100);
INSERT INTO vendas VALUES (9, 4, 3, 100);
INSERT INTO vendas VALUES (10, 4, 1, 100);
INSERT INTO vendas VALUES (11, 4, 2, 100);
INSERT INTO vendas VALUES (12, 4, 3, 100);

Vamos fazer consultas para mostrar as vendas com base em duas dimensões: a loja que fez a vendas e o período (mês) das vendas.

Em primeiro lugar, vamos colocar como colunas as lojas da tabela pai (loja), e como linhas as vendas da tabela filha, agrupando os dados por mês. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for da loja a ser apresentada na coluna.

-- CROSSTAB
-- COLUNAS - LOJAS
-- LINHAS - Vendas em cada Mês
SELECT
(CASE vendas.mesvenda WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEV' WHEN 3 THEN 'MAR' ELSE 'ERRO' END) AS MES,
SUM (CASE vendas.codloja WHEN 1 THEN vendas.valor ELSE 0 END) AS MATRIZ,
SUM (CASE vendas.codloja WHEN 2 THEN vendas.valor ELSE 0 END) AS FILIAL_1,
SUM (CASE vendas.codloja WHEN 3 THEN vendas.valor ELSE 0 END) AS FILIAL_2,
SUM (CASE vendas.codloja WHEN 4 THEN vendas.valor ELSE 0 END) AS FILIAL_3
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY vendas.mesvenda
ORDER BY vendas.mesvenda;

O resultado da consulta mostra inclusive a filial 2, que esteve fechada durante o período:

 mes | matriz | filial_1 | filial_2 | filial_3
-----+--------+----------+----------+----------
 JAN |    200 |      100 |        0 |      100
 FEV |    100 |      200 |        0 |      100
 MAR |    100 |      100 |        0 |      200
(3 registros)

Agora vamos fazer uma inversão: vamos colocar como colunas as lojas da tabela filha, vendas, e como linhas as informações da tabela pai, loja, com os dados agrupados por loja. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for do mês a ser apresentado na coluna.

-- CROSSTAB 2
-- COLUNAS - Vendas em cada Mês
-- LINHAS - LOJAS
SELECT
(CASE loja.codigo WHEN 1 THEN 'MATRIZ' WHEN 2 THEN 'FILIAL 1' WHEN 3 THEN 'FILIAL 2' WHEN 4 THEN 'FILIAL 3' ELSE 'ERRO' END) AS LOJA,
SUM (CASE vendas.mesvenda WHEN 1 THEN vendas.valor ELSE 0 END) AS JAN,
SUM (CASE vendas.mesvenda WHEN 2 THEN vendas.valor ELSE 0 END) AS FEV,
SUM (CASE vendas.mesvenda WHEN 3 THEN vendas.valor ELSE 0 END) AS MAR
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY loja.codigo
ORDER BY LOJA;

Resultado da consulta:

   loja   | jan | fev | mar
----------+-----+-----+-----
 FILIAL 1 | 100 | 200 | 100
 FILIAL 3 | 100 | 100 | 200
 MATRIZ   | 200 | 100 | 100
(3 registros)

O exemplo acima pode ser incrementado e melhorado de várias formas (por exemplo, as tabelas não têm índices). Se você tem alguma sugestão ou forma alternativa de fazer crosstabs ou de melhorar os exemplos abaixo, não deixe de postar um comentário neste post!

Obs.: Existe um módulo do postgresql já bastante estável, chamado tablefunc que apresenta funções para a criação de tabelas cruzadas. É uma forma de criar crosstabs com menos trabalho, mas também com menos diversão e portabilidade! Mais informações sobre o tablefunc aqui.

Agradecimentos PGDay RR

A coordenação do evento PGDay Roraima, agradece aos participantes que mostraram-se interessados na tecnologia de banco de dados PostgreSQL e compareceram ao evento. Também um agradecimento ao CECOMP/DARI, CECOMP/DSM que apoiaram o evento de maneira geral e estiveram presentes durante as palestras e minicursos.

Justificativa quanto ao PGDay RORAIMA

Atualmente o mundo inclusive o estado de Roraima vem buscando atualizar-se no que tange as tecnologias da informação. E o fato que o maior patrimônio das instituições, são os dados armazenados em seus bancos de dados, podemos ressaltar que o processamento destes dados resultam em informações e a partir destas informações é possível gerar o “conhecimento” necessário para a sobrevivência das instituições. Com isso o nosso anseio em efetivar tal evento, é justificável.

O que é?

Postgres ou PostgreSQL é um projeto de Sistema Gerenciador de Banco de Dados open-source que foi iniciado em 1986, na Universidade de Berkeley, na Califórnia.

PGDay, ou Dia do Postgres, é um evento não tão formal quanto o PGBR (Conferência sobre PostgreSQL no Brasil) e de caráter regional, podendo ser realizado com poucas pessoas em qualquer/vários estados do País.

leia mais

   Já tem alguns dias mas falei algumas bobagens sobre  PostgreSQL e a nuvem num evento de NoSQL, aliás foi um evento muito bacana. Tá aí embaixo…

 

Finito, senhores. O PGBR2011 já se foi. Ok, ainda tem prestação de contas, avaliações a serem feitas, dinheiro de empenhos para receber, etc e tal. Mas agora até mesmo o Koichi Suzuki já terminou de dar a volta no mundo e voltou para o Japão.

Fazer um evento num hotel tornou tudo muito mais caro. Foram mais de R$ 16K com aluguel das salas, projetores, sonorização, sala VIP, cofee break e claro, a cervejada. As despesas com hospedagem de palestrantes também foram altas, gastamos uns R$ 8K com isso. Mas o retorno foi incrível. O preço da diária não foi tão alto para quem veio de fora (negociamos isso em abril) e estavam todos lá. Sair para dar um passeio em São Paulo com a turma, dormir até mais tarde ou mesmo ficar de bobeira no saguão com os palestrantes internacionais tomando uma caipirinha. Isso tudo ficou mais fácil. Foi um enorme avanço em relação a 2009. Em 2009 só a organização pode desfrutar do excelente hotel da Unicamp. Sim, o hotel da Unicamp era muito mais agradável. A Unicamp em si é muito mais agradável. O mar de prédios de São Paulo não é algo tão bacana assim. Mesmo tirando a nossa tradicional “foto do Bozo” na piscina, a sensação de estar espremido entre prédios é meio bizarra. Mas o evento se tornou mais acessível para todos. No final, quem mais ganhou foi quem pagou para ir ao evento e pode estar mais próximo dos palestrantes e demais congressistas.

Trazer tanta gente de fora, não foi moleza. O Flávio perdeu a conta de quantas vezes foi até o aeroporto. Além de convidar os Srs. Alvaro Herrera, Bruce Momjian, Dave Page, Greg Smith e Koichi Suzuki, o Sr. Jaime Casanova passou pela chamada de trabalhos e houveram pessoas que saíram de outros países só para vir ao PGBR2011, como o Sr. Joe Abbate que também aproveitou para passear pelo Brasil, claro. Bom, o fato é que o evento novamente chamou a atenção de pessoas de outros países e isso foi algo muito interessante de ver. Tenho de reconhecer que assistir uma palestra em espanhol parece mais difícil que assistir uma palestra em inglês. Acho que a nossa overdose diária de documentação em inglês deve ter algo haver com isso. Vamos ter de repensar nisso no próximo evento.

Houveram alguns problemas menores como erros na impressão dos certificados e o atraso no recebimento dos chaveiros e adesivos. O elefante de pelúcia não ficou uma maravilha, mas se esgotou nos primeiros minutos do evento. O chaveiro ficou muito bacana também. No final acho que ficamos bem. A comida do hotel era realmente muito boa. O almoço não tinha muita variedade mas o peixe deles estava ótimo. A sala VIP era VIP mesmo. Dois bons sofás onde era perfeitamente possível tirar um bom cochilo, uma mesa para quem precisasse terminar uma palestra, fazer uma reunião de negócios ou atender um cliente remoto (sim, isto SEMPRE acontece).

Achei a estrutura vertical do hotel um pouco incômoda. Você tinha que ficar subindo e descendo escadas dos auditórios para o salão de exposições. Os patrocinadores podem não ter gostado muito disso também. Mas acho que não foi algo terrível. Por outro lado, o salão de exposições era bem espaçoso, e conseguiu comportar todos no intervalo para o café sem problemas. E olha que era muuuita gente.

As inscrições foram muito mais tranquilas que em 2009. Mais antecedência, o apoio com atendimento telefônico e um sistema de inscrições mais maduro ajudaram muito. Não que não houvessem problemas. O fato da prefeitura de Porto Alegre não emitir nota fiscal eletrônica, junto com a greve dos correios foi um tiro no pé. Mas o Euler e a Débora estão de parabéns pelo trabalho nesta área.

Então vejamos alguns números:

• Quase R$ 65K de custo no evento, sendo R$ 23K recebido pelas inscrições e o restante de 13 patrocinadores nacionais e internacionais. Este também foi o primeiro ano que não precisamos de ajuda da SPI.
• Mais de 240 pessoas inscritas no evento;
• Mais de 50 propostas de palestras na chamada de trabalhos e um total de 32 palestrantes presentes, fora os Lightning Talks.
• 400 ecobags, 200 chaveiros, 100 squeezes, 100 camisetas, 60 elefantes de pelúcia, 5 banners, 1000 cartazes, 1000 adesivos pequenos e 100 adesivos normais.
• 200 litros de chopp e inúmeras histórias para contar.

Olá, pessoal

Depois de um grande tempo de inatividade estou voltando a escrever e neste retorno vou rascunhar sobre o #pgbr2011 que aconteceu em São Paulo.

Como todos devem saber sou um entusiasta do PostgreSQL e estou presente desde o primeiro evento que ocorreu em São Paulo em 2007. De lá para cá muita coisa aconteceu: o PostgreSQL evolui bastante, e está se tornando cada vez mais sólido no mercado de Banco de Dados, além dele a comunidade brasileira também cresceu, evoluiu tanto no aprendizado técnico como o que tange a questão organizacional (Este será o tema do post).

Precisamos pensar no seguinte: Por que em 2010 não tivemos PGBr e em 2011 sim? A resposta é simples. Em 2010, algumas pessoas se propuseram a organizar o evento, mas por alguns motivos isso não aconteceu, mas agora isso não vem ao caso, afinal já é passado.

O que importa é que em 2011 a mesma comissão organizadora de 2007, 2008 e 2009 e mais alguns agredados tomaram a frente da organização e fizeram acontecer e por este motivo novamente tivemos o evento. Um evento com sucesso total, tanto na quantidade de pessoas que prestigiaram o evento, como também no nível das palestras apresentadas. E isso é algo que precisa/precisava ser elogiado, por isso que quando o meu amigo Dickson Guedes conversou comigo sobre apresentar um Lightning Talk eu topei na hora, na verdade eu já tinha em mente fazer um LT, mas seria uma apresentação sobre Backup, mas depois resolvi mudar o meu tema porque achei que precisava falar nem que rapidamente da organização do #pgbr2011 e foi o que tentei fazer.

Eu gostaria de parabenizar toda a comissão pela organização do evento. Conheços todos e sei que vocês trabalharam arduamente para que o evento fosse possível. Organizar um evento deste porte é muito complicado, exige sacríficos na vida pessoal, profissional e em alguns momentos de ambas, por isso eu fiz questão de falar sobre isso no Lightning Talk e agora faço este registro aqui.

Faço questão de não citar nomes porque certamente vou esquecer de alguém e isso seria completamente injusto porque todos trabalharam duro, claro que alguns mais e outros menos, mas é assim mesmo, uma equipe funciona assim, então pessoal sintam-se todos parabenizados e agradecidos pela organização do evento. Foi um tremendo sucesso e agora é esperarmos os PGDays de 2012 e o PGBr2012.

Um grande abraço a todos.

Minha programação de palestras do PgBr2011:

2011-11-3

9h30 Bruce Momjian, MVCC Unmasked
11h vmWare vPostgres
14h Eu!
15h Leonardo César, \dfS pg_*: Um passeio pelas funções administrativas do postgres
16h20 Greg(ory) Smiþ, Bottom-up Database Benchmarking

2011-11-4

9h Koichi Suzuki, PostgreSQL and Postgres-XC in NTT Group
10h30 Fernando Ike de Oliveira, Escalabilidade, As Modas e (No)SQL
10h30–12h30 Dickson S. Guedes, Estripando o Elefante - dividindo seus problemas em problemas menores
14h30 Euler Taveira de Oliveira, Tudo o que você queria saber sobre PostgreSQL mas tinha vergonha de perguntar
16h50 Flavio Henrique Araque Gurgel, Meu ambiente cresceu e eu não planejei. E agora?

Olá, pessoal

Boa tarde!!! Gostaria de comentar que nesta quinta-feira (03/11) acontecerá a terceira edição do [PGBr 2011], antigo PGCon.

Se possível apareçam por lá. Aguardamos vocês por lá.

Abraços

Para obter o código de uma função em Postgresql, podemos usar de pequenos artifícios (lembre-se de trocar #nome_da_funcao# pelo nome da função em questão: 1) se estivermos no psql, podemos rodar \df+ #nome_da_funcao# ou \ef #nome_da_funcao# sendo que na segunda opção(\ef) estaremos editando o código no editor padrão e não apenas vendo o código da [...]

 

    Tenho que agradecer o Alexandre Porcelli pela a oportunidade de participar com um lighting talk. O evento foi excelente, apresentações muito boas e técnicas (exceto a minha. ).

    Algumas palestras como da Microsoft e da Oracle fizeram-me refletir sobre algumas coisas do mercado de TI. Basicamente no mundo da Operação ou Sustentação:

 

- Big Data e Storages: 
   Com cada vez mais equipamentos conectados a Internet ou sistemas corporativos, está cada vez mais complexo gerenciar sistemas e infraestrutura. Tem-se informação demais e a necessidade de extrair tendências, relações, histórico, etc. Qualquer sistema a ser pensando (principalmente que envolva a internet) pode ter volume de transação ou acesso muito acima do que tinha-se planejado. 

    Os dados para armazenar, principalmente informação histórica ou legado crescerá exponencialmente. Storage do jeito tradicional, com a preocupação de Raid Group, Raid Type e etc. terá a importância menor. Pensarão em Provisionamento Dinâmico, Virtualização de Storages e robôs de Fitotecas.  

    Também ficará cada vez mais usado a "Tierização" ou seja, conforme o uso de um determinado dado tem acesso recorrente, o Storage automaticamente movimenta este de discos mais lentos para mais rápidos. 

    Nunca pensei em afirmar isso com grande convicção mas cada vez mais o uso de NFS (arquitetura Network Attach Storage) ou outro sistema de arquivo distribuído será cada vez maior pois conectar tantos equipamentos com "fibra ótica"  (SAN)  tem um custo operacional de crescimento do que uso de sistema de arquivo distribuído quando é necessário pensar em escalabilidade rapidamente.

    Só para deixar claro que transações OLTP ainda confio em Storages SAN mas confesso que a arquitetura Scale Out para armazenamento tem chamado minha atenção ultimamente, principalmente para uso de dado não estruturado como bancos de dados NoSQL, Áudio, Vïdeo, etc.

- NoSQL e Big Data: 

   Então, basicamente em qualquer sistema pode ocorrer o problema do sucesso além do esperado. Aí é cache, cache, cache onde puder ter para dar conta e ou abrir mão de alguma coisa de uma aplicação tradicional CRUD ou banco de dados relacional.

- Pradonização de produto não, processo:

  Isso é um pouco ainda difícil de aceitar mas é um fato. Se você é responsável por uma operação de infraestrutura ou serviço, perceberá que não adianta adotar um tipo de virtualização, um banco de dados, um tipo de sistema operacional, etc. Cada vez mais tem-se que encontrar soluções rápidas e adequadas para sustentar o negócio.

  O mais importante é o controle dos processo da operação de infraestrutura. Mesmo porque a operação não está, necessariamente na sede da empresa, está na nuvem.

- Virtualização é um commodite:

  Não sei se ambientes pequenos isso acontece/acontecerá mas em muitas situações tem hypevisor (Xen, KVM, VMware, Hyper-V, etc.) diferentes com ferramentas para gerenciar.  Felizmente existe algumas soluções interessantes para esse tipo de gerenciamento como Openebula e OpenStack.

-  Soluções poliglotas:

  Então fã de uma linguagem de programação, pode continuar encantado com ela mas a oferta é tanta de soluções que muitas delas estão desenvolvidas em uma linguagem diferente que você gosta. Terá que aprender como adequá-las para suas necessidades (Perl, Python, Ruby, Java, Scala, etc… ). Um aparte: Gostei muito do Elastic Search.

- DBA, é o fim: 

  Não é o fim do DBA mas tornara cada vez mais um nicho especifíco. Muito da responsabilidade do DBA com o Big Data está com o desenvolvedor ou arquiteto da aplicação. Principalmente com os bancos de dados NoSQL.

- Sysadmin/Sysops/Devops/FuckOps

  De certa maneira também o sysadmin tem suas funções alteradas, os conhecimentos de ferramentas de configuração (Chef, Puppet, Cfengine, etc…) e administração de servidores na "nuvem" é cada vez mais frequente. Ah, se você é um sysadmin que não tem caguete de programação é melhor rever seus conceitos senão também sua área de atuação ficará mais limitada.

- Enfim, ganhamos e não sabemos:

   Os grandes, aparentemente perderam o bonde da virtualização de servidores, estão brigando numa "camada" acima. A tal de Sky Computing, tanto que a Microsoft com o Azure, IBM com Smart Cloud, Oracle com a Public Cloud, Google com App Engine. E eu achando que Daas com PostgreSQL era o bacana.

 

Obs. Tá meio troll esse post. Se tiver afim de aprofundar algum assunto, comenta aí que depois comentou ou posto algo menos genérico.

    Salve meus caros!!!

 

 

 

    Para nem vocês e eu esquecermos, minha agenda de palestras:

 

- NoSQLBR V2: PostgreSQL nas Nuvens (já apresentei. Falta um post sobre… )

- PGBR2011: Escalabilidade, as modas e o No(SQL)

- Linuxcon Brasil: Desmitificando gargalos de performance (Prefiro o título como: Lendas Urbanas de Performance)

Mais um episódio do PGCasts no ar, e hoje eu falo sobre o comando COPY, espero que gostem e aguardo sugestões!

Todos sempre buscamos melhorar o desempenho das operações de banco de dados. E um dos recursos de performance ainda pouco utilizados da versão 9.1 do postgres são as chamadas unlogged tables.

O que são Unlogged Tables?

Unlogged Tables são tabelas que não apresentam suporte a recuperação pós-falha. Não apresentam portanto log de transações (write-ahead-log - WAL). Essa característica possibilita um grande ganho de desempenho em todas as operações realizadas. O ganho de desempenho obtido se deve ao sacrifício da possibilidade de recuperar os dados em caso de falha de sistema.

Uma unlogged table tem seus dados automaticamente perdidos em caso de falha, pois é truncada automaticamente, o que gera um ganho no tempo de recuperação do banco de dados.

Os dados de uma unlogged table não sofrem replicação dentro do postgresql.

Em unlogged tables não há necessidade de se manter o log e sincronizá-lo com o banco de dados, fator importante para o de ganho de desempenho.

Em que situações é recomendado utilizar este tipo de tabela?

Em situações em que a durabilidade dos dados não seja realmente importante:
- Para parâmetros de aplicações web;
- Cache de dados em geral;
- Tabelas de status de aplicações, entre outras possibilidades.


Acredito que apenas uma pequena parte de sistemas de bancos de dados possa ser armazenada em tabelas unlogged.

As operações de inserção, alteração, alteração e consulta a dados de uma "tabela sem log" são diferentes de uma tabela "normal"?
A forma de fazer e os comandos utilizados permanecem os mesmos. No entanto, internamente, não há write-ahead-log (WAL), o que faz com que os dados da tabela seja perdidos em caso de quedas de sistema. A velocidade das operações tende a ser bem maior.

Como criar Unlogged Tables?

A criação de tabelas sem log é bastante simples. Basta colocar a cláusula "UNLOGGED" no comando de criação da tabela.

teste=> CREATE TABLE LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=> CREATE UNLOGGED TABLE NAO_LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=>
É permitido indexar este tipo de tabela?

Não existem restrições à indexação, exceto para índices GIST em que este recurso não está implementado. É possível inclusive reindexar, se for o caso! Os índices de uma unlogged table também são "unlogged", isto é, são truncados em caso de falha do sistema.

teste=> CREATE INDEX UNLOGT ON NAO_LOGADA(cod);
CREATE INDEX
teste=>
teste=> insert into NAO_LOGADA values (1, 'Teste 1');
INSERT 0 1
teste=> insert into NAO_LOGADA values (2, 'Teste 2');
INSERT 0 1
teste=> insert into NAO_LOGADA values (3, 'Teste 3');
INSERT 0 1
teste=> REINDEX TABLE NAO_LOGADA;
REINDEX
De quanto é o ganho esperado em desempenho?

DEPENDE da operações realizada. Veja o link abaixo e depois faça seus próprios testes:
http://pgsnaga.blogspot.com/2011/10/pgbench-on-unlogged-tables.html

Considerações Finais

Unlogged Tables são um recurso válido para ganho de performance em certos casos específicos. No entanto, a definição de que tabelas devem ser unlogged pode gerar erros graves e impossibilitar a recuperação de dados relevantes. Esta decisão deve ser sempre bastante embasada e levar em conta as necessidades de todos os usuários do banco.

O que é?

Postgres ou PostgreSQL é um projeto de Sistema Gerenciador de Banco de Dados open-source que foi iniciado em 1986, na Universidade de Berkeley, na Califórnia.

 

leia mais

Uma das funcionalidades mais esperadas (e pedidas), estará disponível na próxima versão do PostgreSQL: index-only scans ...

Zilhões de coisas rolando ao mesmo tempo:

• Saiu a lista dos palestrantes do PGBR2011. A grade ainda estamos montando.
• Sim, uma palestra minha foi aprovada (não, eu não faço parte da banca avaliadora), vou falar sobre processos de melhoria de desempenho em ambiente OLTP de alta concorrência. A palestra se chama: “Fazendo uma manada passar debaixo da porta”
• O link onde o site do PGBR está hospedado está com problemas sérios no link. Esperamos que isto esteja normalizado em breve.
• Foi publicada a minha participação no #DatabaseCast sobre PostgreSQL, no Imasters.
• Estamos mandando bala no material promocional. Tirando a sacola, tudo será vendido praticamente a preço de custo. Até agora serão:
•  400 ecobags para o material do evento;
• 100 squeezes em alumínio;
• 100 elefantes de pelúcia;
• 100 camisetas pólo;
• 200 chaveiros;
• 1000 adesivos;
• Um mês antes do evento ocorrer, temos 120 inscritos. Sim, brasileiro deixa tudo para a última hora… então devemos ter um número bem superior até lá.
• Teremos um prêmio para as pessoas que mais se destacaram nos últimos tempos. Estamos organizando isso ainda, mas será uma forma de valorizar aqueles que tem contribuído para o fortalecimento da comunidade brasileira.

O chopp está garantido no final do evento!!!

(ok, vai ter refrigerante também… mas não consegui achar o Guaraná Jesus por aqui)

Seguindo a mesma linha do Fike resolvi escrever aqui meu itinerário para o PGBR 2011.

Dando sequência ao post anterior "Full Text Search em português no PostgreSQL", veremos como manter atualizado o índice de busca textual, particularmente no caso de os dados da tabela sofrerem muitas atualizações (i.e., inclusões e modificações).


Só para relembrar, o diagrama a seguir ilustra o funcionamento do mecanismo de Full Text Search no PostgreSQL, em que fazem parte processos como parser, normalizador e indexador:


Para exemplificar o problema apresentado no início desse post, considere que a tabela MUNICIPIOS esteja devidamente populada, tendo a coluna de suporte para a busca textual criada e o índice do tipo GIN (ou GiST) associado. Sendo assim, podemos fazer consultas SQL desse tipo:

SELECT nome, uf FROM municipios
WHERE busca @@ plainto_tsquery(simples('agua lindoia'));

Veja o resultado:

curso=# SELECT nome, uf FROM municipios
curso-# WHERE busca @@ plainto_tsquery(simples('agua lindoia'))
       nome       | uf 
------------------+----
 Águas de Lindóia | SP
(1 registro)

O que acontece agora se incluirmos um novo registro na tabela? Veja:

INSERT INTO municipios (codigo, nome, uf)
VALUES (100, 'Águas de Lindóia do Sul', 'RS');

Ao executarmos a busca anterior, o município recém-incluído não aparecerá... Isso porque a coluna de suporte à busca não foi atualizada. Veja o conteúdo dela:

curso=# SELECT nome, uf, busca FROM municipios WHERE codigo = 100;
          nome           | uf | busca 
-------------------------+----+-------
 Águas de Lindóia do Sul | RS | 
(1 registro)

Para manter essa coluna atualizada automaticamente em operações de INSERT ou UPDATE, precisamos de um disparador (trigger). No PostgreSQL criamos uma função em linguagem procedural (geralmente em PL/pgSQL) e em seguida a associamos a um disparador.

Sendo assim, crie a função de trigger com o código abaixo:

CREATE FUNCTION municipios_trigger()
RETURNS trigger AS $$
begin
  new.busca := to_tsvector(simples(new.nome));
  return new;
end
$$ LANGUAGE plpgsql;

Ao ser chamada, ela fará com que o conteúdo da coluna "busca" seja preenchido com o texto gerado da normalização da coluna "nome".

Em seguida, crie o disparador de atualização:

CREATE TRIGGER municipios_tsupdate
BEFORE INSERT OR UPDATE ON municipios
FOR EACH ROW EXECUTE PROCEDURE municipios_trigger();

Ou seja, antes de cada INSERT ou UPDATE na tabela, a função municipios_trigger() será invocada para preencher automaticamente a coluna "busca".

Agora experimente modificar aquele registro inserido acima:

UPDATE municipios SET uf = uf WHERE codigo = 100;

Observe como ficou o conteúdo daquela linha:

curso=# SELECT nome, uf, busca FROM municipios WHERE codigo = 100;
          nome           | uf |           busca           
-------------------------+----+---------------------------
 Águas de Lindóia do Sul | RS | 'agu':1 'lindo':3 'sul':5
(1 registro)

Finalmente efetue a busca textual que havia falhado:

curso=# SELECT nome, uf FROM municipios
curso-# WHERE busca @@ plainto_tsquery(simples('agua lindoia'));
          nome           | uf 
-------------------------+----
 Águas de Lindóia do Sul | RS
 Águas de Lindóia        | SP
(2 registros)

Tente agora fazer uma modificação no nome desse município:

UPDATE municipios SET nome = 'Águas Quentes do Sul' WHERE codigo = 100;

E então refaça a busca considerando o novo nome:

curso=# SELECT codigo, nome, uf FROM municipios
WHERE busca @@ plainto_tsquery(simples('agua quente'));
 codigo |         nome         | uf 
--------+----------------------+----
    100 | Águas Quentes do Sul | RS
(1 registro)

Índice sempre up-to-date! Muito fácil, né? :D

Referências

[1] PostgreSQL 8.3 Documentation - Full Text Search

Full Text Searching (ou simplesmente busca textual ou FTS) é uma poderosa ferramenta disponível em bancos de dados que visa aumentar a probabilidade de sucesso nas consultas efetuadas pelos usuários.



Operadores de busca textual existem nos SGBDs há anos. O PostgreSQL oferece, além do LIKE, o ILIKE (versão que desconsidera a capitalização) e operadores baseados em expressão regular (i.e., ~ e ~*) para tipos de dados textuais. Entretanto, tais operadores falham ao não prover muitas propriedades essenciais requeridas por sistemas de informações modernos, tais como suporte linguístico, ranking dos resultados e indexação [1].

Na Universidade de Moscou foi criado o projeto Tsearch2 [2], no qual os russos Oleg Bartunov e Teodor Sigaev desenvolveram um motor do tipo full text inteiramente integrado ao SGBD PostgreSQL. Além da referida solução de busca textual, foram criados dois poderosos mecanismos de indexação: GiST e GIN.



O Tsearch2 residiu no diretório contrib do PostgreSQL até a sua versão 8.1, sendo finalmente incorporado ao core do SGBD na versão 8.3, tornando-se a opção default para FTS nele.



Para ilustrar, tomemos como exemplo o clássico cadastro de municípios brasileiros. A missão é efetuar buscas textuais (neste caso pelo nome dos municípios) usando FTS.

Eis uma tabela no PostgreSQL populada com as cidades:

        nome         | uf 
---------------------+----
 Abadia de Goiás     | GO
 Abadia dos Dourados | MG
 Abadiânia           | GO
 Abaeté              | MG
 Abaetetuba          | PA
 Abaiara             | CE
 Abaíra              | BA
 Abaré               | BA
 Abatiá              | PR
 Abdon Batista       | SC
 ...

A ideia é fazer com que buscas aproximadas possam ser efetuadas pelo usuário, ou seja, algo mais "poderoso" que um falho operador LIKE.

A primeira modificação será criar uma coluna adicional, chamada de "busca", a fim de armazenar os vetores já normalizados dos termos para ser usada posteriormente nas consultas.

ALTER TABLE municipios ADD busca tsvector;

Eis a estrutura da tabela após a execução do comando:

         Table "public.municipios"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 nome   | character varying(50) | 
 uf     | character(2)          | 
 busca  | tsvector              | 

Como o foco deste estudo é usar o idioma português, e considerando que o servidor PostgreSQL esteja configurado com a codificação UTF-8 (i.e., Unicode), precisaremos criar algumas funções adicionais. Baseando-se em [3] e [4], criei essas funções:

CREATE FUNCTION to_ascii(bytea, name) RETURNS text AS
 'to_ascii_encname'
 LANGUAGE internal STRICT;

CREATE FUNCTION simples(texto varchar) RETURNS varchar AS
 'select lower(to_ascii(convert_to($1, ''latin1''), ''latin1''))'
 LANGUAGE sql IMMUTABLE STRICT;

Veja o que essa nova função simples() é capaz de fazer:

brasil=# select simples('Pinhão com Açaí');
    simples     
----------------
 pinhao com acai
(1 row)

Ou seja, trata-se de uma função de transformação que normaliza o texto recebido como argumento. Ela será essencial para os passos a seguir.

Podemos usar a função simples() em conjunto com to_tsvector() a fim de produzir o vetor de lexemas (termos processados a serem usados nas buscas). Veja:

brasil=# select to_tsvector(simples('Pinhão com Açaí'));
    to_tsvector    
-------------------
 'aca':3 'pinha':1
(1 row)

Voltando à tabela de municípios, execute o comando SQL a seguir para popular a recém-criada coluna "busca" com o vetor transformado em cada uma de suas tuplas.

UPDATE municipios SET busca = to_tsvector(simples(nome));

Pronto! Agora que os textos foram pré-processados, basta executar as consultas SQL usando o operador especial @@ no PostgreSQL. Veja um exemplo de uso no qual o usuário digitou o texto "sao mateus" na aplicação. A consulta SQL fica assim:

brasil=# select nome, uf from municipios
brasil=# where busca @@ plainto_tsquery(simples('sao mateus'));
          nome          | uf 
------------------------+----
 São Mateus             | ES
 São Mateus do Maranhão | MA
 São Mateus do Sul      | PR
(3 rows)

Note que além do vetor (coluna "busca" do tipo tsvector) é preciso especificar um tipo tsquery, fornecido pelas funções to_tsquery() e plainto_tsquery(). A função simples() foi usada novamente para realizar a transformação do texto informado.

Ótimo! É só isso?

Não... Funcionou, mas o desempenho não pode ser garantido. Na realidade a busca com essa abordagem será mais feliz que se usássemos um ardiloso LIKE "%sao mateus%". (Jogue a primeira pedra quem nunca encontrou um monstrinho desses em uma aplicação!).

Vejamos como o SGBD está tratando a consulta SQL acima:

brasil=# explain select nome, uf from municipios
brasil=# where busca @@ plainto_tsquery(simples('sao mateus'));
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on municipios  (cost=0.00..163.60 rows=6 width=16)
   Filter: (busca @@ plainto_tsquery('sao mateus'::text))
(2 rows)

Ops, uma varredura sequencial (também conhecido por full scan ou sequential scan) está sendo efetuada! Se você não sabe o que é isso, consulte o seu DBA. Se ele for teu amigo, não irá te xingar.

Bom, e como podemos corrigir isso? Criando um índice! :D

Neste caso específico do FTS no PostgreSQL, temos a escolha de usar uma das duas opções de índices, GiST ou GIN (lembra dos russos?). Cada uma tem a sua aplicação. Escolhi o GIN por esta apresentar maior precisão para esta aplicação. Veja como criar o índice:

CREATE INDEX municipios_gidx ON municipios USING gin(busca);

E agora, será que funcionou? Roda o EXPLAIN novamente! Veja:

brasil=# explain select nome, uf from municipios
brasil=# where busca @@ plainto_tsquery(simples('sao mateus'));
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on municipios  (cost=4.30..23.49 rows=6 width=16)
   Recheck Cond: (busca @@ plainto_tsquery('sao mateus'::text))
   ->  Bitmap Index Scan on municipios_gidx  (cost=0.00..4.30 rows=6 width=0)
         Index Cond: (busca @@ plainto_tsquery('sao mateus'::text))
(4 rows)

Sucesso! Agora a busca textual com FTS está devidamente indexada no PostgreSQL.

Referências

[1] PostgreSQL 8.3 Documentation - Full Text Search

[2] Tsearch2 - full text search extension for PostgreSQL

[3] PostgreSQL: TO_ASCII & UTF8

[4] PostgreSQL 8.3 Documentation - String Functions and Operators

       

  O temário da PGBR 2011 fez uma excelente escolha de temas. É muito difícil escolher qual assistir, de qualquer forma fiz uma seleção de palestras que tentarei assisti. (sem ordem de prioridade e com alguns comentários)

- PostSemantic: Integrando dados legados através de semântica

     Está na moda ontologia e Web semântica. Um dos caminhos que para integração de sistemas "autônomos" é uso do SPARQL. Minha apresentação para PGBR tem isso na agenda mas sem a profundidade que o Rodrigo deva abordar. Um projeto interessante de uso do SPARQL e RDF é DBpedia.

- Meu ambiente cresceu e eu não planejei. E agora? 

     Sempre pensei em apresentar uma palestra dessa. Até rascunhei mas  fiquei adiando, adiando termirnar  e (finalmente) alguém mais qualificado (Flávio Gurgel) do que eu falará de um assunto tão bacana e que poucos dão atenção (só depois de ver o tamanho da fatura por não ter planejado direito…).

- PostgreSQL Performance Pitfalls

     Greg Smith é um dos desenvolvedores do PostreSQL e falará um "pouco" sobre performance. Escreveu um livro recomendadíssimo sobre o tema.

- Viva a Revolução Geoespacial

    Para quem acha que Geoespaciais é o Google Maps, Foursquare ou informações de uma galáxia, não perca a palestra do Bueno. Aliás, ele (ao menos para mim) é um dos pioneiros do Postgis no Brasil.  Se quiser de autógrafos dele, fale comigo no evento.

- Escalabilidade horizontal com PostgreSQL 9.0 e Pgpool II

    Escalabilidade horizontal, Grids e sistemas distribuídos são alguns dos meus temas preferidos. Alguns serviços da "nuvem" de PostgreSQL usam bastante os recursos que serão apresentados pelo Matheus.

- MVCC Unmasked

    Bruce Momjian falará uma das funcionalidades de um banco de dados mais fantásticas de um banco dedados, seja SQL ou não: MVCC.

- PostgreSQL at the center of your dataverse

    Implamentação da especificação ISO SQL/MED, talvez o Dave Page fale que é possível buscar informações online do telescópio Hubble.

- PostgreSQL: heavyweight locks, lwlocks, deadlocks, spinlocks, pg_locks, row locks … isálvese quien pueda!

    Locks, locks, deadlocks. O terror de qualquer pessoa que tem envolvimento com banco de dados. Álvaro Herrera falará de um pouco sobre magia negra chamada locks (bloqueios). 

- PostgreSQL and Postgres-XC in NTT Group

    A NTT é uma empresa de telecomunicações do Japão, vai ser interessante ver o que eles estão fazendo com PostgreSQL para manter seus serviços. Ah, os japoneses são os que mais usam telefone no mundo, imagina quanto um banco de dados é exigido???

- Fazendo uma manada de elefantes passar por baixo da porta 

  Provavelmente a palestra mais esperada da PGBR 2011. Dicas importantes de sobre hardware, sistemas de arquivos, etc.  Recomendado para qualquer um que é ou será responsável por banco de dados, principalmente para sistemas OLTP (Online Transaction Processing) e também, porque é o brasileiro que trabalha com PostgreSQL sensação de 2011. Também conhecido como Telles Demolidor (Homem sem medo). 

PS. Autógrafos do Telles, basta agendar comigo.

PS2. Hey, essa é a minha lista de intenção. As outras que não listei aqui serão apresentadas por pessoas de alta qualificação. Qual a sua lista?

   

   Telles é atualmente a celebridade brasileira do PostgreSQL em 2011.  Por que?

   Simples, ele tem email de grife e foi entrevistado pelo pessoal do DatabaseCast para falar de PostgreSQL.  E não ache que é pouca coisa, porque iá apresentar uma das palestras mais quentes da PGBR 2011 que o título "Fazendo um manada de elefantes passar por baixo da porta".

   Basicamente a palestra dele é sobre o uso do PostgreSQL em ambientes de altíssima concorrência, coisa para gente de conhecimento super elevado. Não perca. DD

   

 

P.S. Deixando a badalação de lado. Boa entrevista do Telles, recomendo a audição.

 

Ouça o Telles falando sobre PostgreSQL no DatabaseCast #15: História do PostgreSQL – iMasters.

Apesar da grade do PGBR 2011 não ter sido divulgada ainda, já é possível ver a lista dos palestrantes ...

O Grupo de Desenvolvimento Global do PostgreSQL liberou hoje atualizações (minor version) para as versões: 9.1.1, 9.0.5, 8.4.9, 8.3.16 e 8.2.22. É estremamente recomendável que todos os usuários atualizem as suas instalações atuais. Em tempo, a comunidade PostgreSQL, até o final do ano provavelmente, vai parar de dar manutenção para a versão 8.2. Tendo em vista que [...]

O Grupo de Desenvolvimento Global do PostgreSQL liberou hoje atualizações (minor version) para as versões: 9.1.1, 9.0.5, 8.4.9, 8.3.16 e 8.2.22.

É estremamente recomendável que todos os usuários atualizem as suas instalações atuais.

leia mais

Depois de um período longo sem um computador decente para gravar os screencasts, voltei com o PGCasts. Último episódio saindo do forno. Confira!

Ao ler o post do Greg, resolvi apresentar a minha opinião sobre o $TITULO. É bom ser cauteloso quando estamos lidando com nossos dados. Mas será que devemos esperar quanto tempo antes de considerar uma atualização para uma versão mais nova?

Quando estamos lidando com versões antigas de uma tecnologia sempre nos deparamos com desenvolvedores de aplicações que estão tentando utilizar as últimas funcionalidades que o software oferece. No universo do PostgreSQL, isso não é diferente; há sempre uma nova função, uma sintaxe diferente, a otimização de um tipo de consulta, um novo tipo de índice e por aí vai.

Além disso, os DBAs enfrentam no dia-a-dia as limitações de uma versão que vai se tornando obsoleta ao longo dos anos (tenho clientes utilizando a versão 8.1 e que ainda sofrem dos picos de escrita durante o checkpoint). Os DBAs são os profissionais mais cautelosos dentre aqueles que administram serviços; mesmo assim, consideram atualizar entre versões para (i) diminuírem a dor de cabeça com as limitações pré-existentes e (ii) beneficiarem das novas otimizações e oportunidades (de ajuste e monitoramento).

Voltando a questão da versão, o PostgreSQL sempre considera novas versões aquelas que mudam o X e/ou Y em X.Y.Z (o Z é reservado para correção de bugs). Assim, os primeiros lançamentos das novas versões são 7.4.0, 8.0.0, 8.1.0, 8.2.0, 8.3.0, 8.4.0, 9.0.0, 9.1.0 (preferem omitir o .0 pois afinal de contas, uma vez na versão X.Y os binários podem ser atualizados para Z+n sem precisar fazer uma migração entre versões -- cópia de segurança -> restauração ou pg_upgrade). Dentre os lançamentos mencionados há uma diferença: aqueles que mudam o X e os que não mudam. É uma diferença na qualidade? Não. A mudança no X ocorre quando há um grande número de funcionalidades impactantes em uma nova versão com relação as anteriores. Foi assim da 7.4 -> 8.0 (suporte a Windows, tablespaces, PITR, savepoints) e da 8.4 -> 9.0 (replicação embutida, Hot Standby, bloco de código anônimo aka DO).

Olhando do ponto de vista de evolução de um software, mais mudanças código geram mais bugs! Então para aqueles DBAs mais cautelosos eu não aconselho que migrem rapidamente quando o X mudar. Costumo aconselhar que esperem alguns lançamentos de versões de correção (talvez 3 ou 4) para pensar em adotá-las. Quando quem muda é o Y, o conselho é esperar 1 ou duas versões de correção. Geralmente, logo após o lançamento de uma versão (como foi o caso da 9.1 recentemente), o grupo de desenvolvimento lança versões corretivas pouco tempo depois para corrigir aqueles bugs que passaram despercebidos durante o longo ciclo de testes (beta e RC -- 6 meses).

Como estamos na 9.1, se você estiver considerando uma atualização de versão, faça um planejamento com a 9.1 ao invés da 9.0 (por exemplo). Apesar de ser uma nova versão, o tempo que as suas aplicações ficarem em teste é exatamente aquele em que o grupo de desenvolvimento terá lançado 1 ou 2 versões corretivas.

Seja feliz com 9.1 até que o 9.2 ou 9.3 saia do forno e você considere atualizar novamente. ;)

Mais um ano, e volta a Conferência Braſileira de PoſtgreSQL, agora chamada PgBr, e não mais PgConBr. Além de economizar três caracteres (¡dã!), o novo nome evita coliſão com a Conferência global de PoſtgreSQL, realizada todo ano em Otava, Ontário, Canadá, no primeiro ſemeſtre.

Ainda não ſei ſe poderei ir. Eſte ano, apeſar de ſer realizada na capital de São Paulo dos Campos de Piratininga, SP, a conferência realizar‐ſe‐á durante a ſemana de trabalho, como ſe fôra planejada para uma cidade de funcionários públicos, como Braſília, DF. Eu meſmo, funcionário público, ſaio do eſperado e me acho aßoberbado de trabalho, o que me dificulta a ida. Ißo ainda poderia ſer negociado; o que realmente me atrapalha é juſtificar ir ſem paleſtrar, e tenho já mais de um ano afaſtado da área de dados, o que me gera falta de aßunto.

Não que não haja aßuntos intereßantes. Mas após duas experiências ſofridas de paleſtrar ſem preparação adequada (PgBr MMIX, ſobre expreßões comuns de tabelas e PgCon MMX, ſobre modelagem literária), além de meu preſtígio ter baixado baſtante na comunidade, reluto em achar que conſeguirei preparar e apreſentar algo que valha a pena, ainda mais que ainda eſtou ſem meu Debian GNU/Linux em caſa, ainda com os problemas do EFI da Apple — no trabalho, onde ainda é Bios, eſtou no conforto do Debian com GNU Emacs — ſaudades do Open Firmware…

Idéias até tenho — por exemplo, ‘O Paquiderme univerſal’, ſobre como o PoſtgreSQL, ao rodar em todo tipo de plataforma, eſcalando do portátil ao grande porte, com todo tipo de linguagem e extenſão, atende a praticamente todas as demandas poßíveis e imagináveis de computação e geſtão de dados deſte lado do paraíſo relacional; ou ‘A Evolução paquidérmica: para o alto, e ¡avante!’, repaßando a liſta de afazeres do PoſtgreSQL, moſtrando como vamos melhorar, talvez colocando em perſpectiva tanto das noßas verſões mais recentes quanto do eſtado e melhorias mais recentes dos principais concorrentes; ou ‘O Elefante: ſua verdadeira forma, e diſfarces’, moſtrando as facilidades de migração de código, com ênfaſe dividida entre facilidades de compatibilidade com concorrentes e aplicação de padrões que facilitam a migração de e para o PoſtgreSQL. A queſtão é que, no momento, creio que há muito mais gente que poßa fazê-lo muito melhor do que eu.

Vejamos. No momento, não eſtou paßando bem. Talvez amanhã reſolva apreſentar algo, o prazo já ſe encerrará. Mas eu bem preferia que outros apreſentaßem eßes temas, e outros melhores ainda.

Se eu não puder ir, peço que alguém levante a diſcußão: regiſtremos os domínios Poſtgres, PoſtgreSQL e Postgres.org.br?

A muito aguardada versão 9.1 do PostgreSQL está disponível para download. O destaque são as mudanças relacionadas a performance e replicação, mas existem melhorias em praticamente todos os campos. É baixar, instalar e usar!

Abaixo o texto do anúncio oficial:


The PostgreSQL Global Development Group announces the release of
PostgreSQL 9.1.  This latest version of the leading open source database
offers innovative technology, unmatched extensibility, and new features
such as synchronous replication, K-Nearest Neighbor indexing, and
foreign data wrappers.

"PostgreSQL 9.1 provides some of the most advanced enterprise
capabilities of any open source database, and is backed by a vibrant and
innovative community with proven customer success.  PostgreSQL is well
positioned for building and running applications in the cloud," said
Charles Fan, Sr. VP R&D, VMware.

Responding to Users

Version 9.1 delivers several features which users have been requesting
for years, removing roadblocks to deploying new or ported applications
on PostgreSQL.  These include:

* Synchronous Replication: enable high-availability
  with consistency across multiple servers
* Per-Column Collations: support linguistically-correct
  sorting per database, table or column.
* Unlogged Tables: greatly improves performance for ephemeral data

"Heroku runs the largest PostgreSQL database-as-a-service in the world,"
said James Lindenbaum, Heroku co-founder. "The release of synchronous
data replication with 9.1 provides our customers with innovative new
ways of protecting mission-critical data, and validates PostgreSQL as
one of the fastest-moving datastores available."

Advancing the State of the Art

Our community of contributors innovates with cutting-edge features.
Version 9.1 includes several which are new to the database industry,
such as:

* K-Nearest-Neighbor Indexing: index on "distance" for
  faster location and text-search queries
* Serializable Snapshot Isolation: keeps concurrent transactions
  consistent without blocking, using "true serializability"
* Writeable Common Table Expressions: execute complex multi-stage
  data updates in a single query
* Security-Enhanced Postgres: deploy military-grade security
  and Mandatory Access Control

"OpenERP has always relied on the enterprise-class features of
PostgreSQL to provide a fast, reliable and scalable foundation for the
Business Applications supporting our customers' operations every day.
Data integrity in highly concurrent and transactional contexts is a
critical topic for us, and we're very enthusiastic about the new
Serializable Snapshot Isolation of PostgreSQL 9.1!"  said Olivier Dony,
OpenERP Community Manager.

Extending the Database Engine

PostgreSQL's extensibility enables users to add new functionality to a
running production database, and use them for tasks no other database
system can perform.  Version 9.1 adds new extensibility tools, including:

* Foreign Data Wrappers: attach and query other databases
  from PostgreSQL
* Extensions: easily create, load, and manage new database features

In PostgreSQL's 25th year of database development, our community
continues to advance database technology with every annual release.
Download version 9.1 now and experience the most advanced open source
database system in the world.

More information on PostgreSQL 9.1:
* Release notes
  http://www.postgresql.org/docs/9.1/static/release-9-1
* Presskit
  http://www.postgresql.org/about/press/presskit91
* Guide to 9.0:
  http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1

Download 9.1 now:
* Main download page:
  http://www.postgresql.org/download
* Source code:
  http://www.postgresql.org/ftp/source/v9.1.0
* One-click installer, including Windows installer:
  http://www.enterprisedb.com/products/pgdownload.do

Ao atingir o volume de 220 bancos de dados, o PostgreSQL passa a ocupar a
posição de destaque como tecnologia livre de bancos de dados no Serpro - Serviço Federal de Processamento de Dados. O MySQl apresenta 200 bancos de dados implementados.

Confira esta informação na página 19 da edição 207 da versão online de revista TEMA. Outro artigo com destaque para o Postgres é o do encarte TEMATEC "Expresso em Nuvem", que mostra uma implementação de grande porte com Postgres e PgPool.

Esta semana encerra o prazo para o envio de palestras para o PGBR 2001 que acontecerá nos dias 3 e 4 de novembro em São Paulo, SP. Não deixe de enviar a sua proposta de palestra!

As palestras podem ser básicas ou avançadas, curtas (palestra) ou longas (tutorial), português, espanhol ou inglês. Só há um pré-requisito: ser relacionada ao PostgreSQL.

Todos os palestrantes terão entrada gratuita.

Vá a página de submissões de trabalhos e envie-nos as suas propostas! Você pode submeter até 5 ideias interessantes. Não deixe de divulgar esta nota para outras comunidades relacionadas; talvez alguém tem uma ideia interessante por lá.

Te espero no PGBR 2011.

Este ano tudo será diferente…. sim já é. Uma das maiores dificuldades do PGCon Brasil, agora rebatizado como PGBR, é a parte das inscrições. É o tipo de coisa difícil de fazer de forma voluntária. Nas edições anteriores o Euler Taveira e o Diogo Biazus se mataram para fazer as inscrições acontecerem. Bom, a gente continua se matando, mas desta vez temos uma forcinha. Temos algo revolucionário: um número de telefone!!! Ou seja, alguém poderá lhe ouvir, tirar suas dúvidas, falar como nós somos bacanas, ouvir reclamações e passar receitas de bolo pelo telefone. Não, a pessoa que irá atender o telefone não vai lhe ensinar a deixar o seu banco de dados mais rápido…. se a gente faz isso ninguém vai no evento,

Mas não é só isso, o Euler melhorou bastante a 3º versão do sistema de inscrições. As notas fiscais continuarão sendo um problema chato, pois a prefeitura de Porto Alegre – RS ainda não emite nota fiscal eletrônica. Ou seja, ainda vamos ter que continuar mandando pelo correio. Se você não precisa de nota fiscal, pegue o seu boleto, pague, autentique e use-o como comprovante. Dá um trabalhão despachar tudo pelo correio.

Se você pertence a um órgão público, se adiante por favor. Todo mundo sabe o mega trampo que é fazer o processo de empenho. Estamos com todos os documentos em ordem, a ASL (o PostgreSQL-BR é associado a ASL, que é quem nos dá respaldo jurídico e contábil), e já está cadastrada no SICAF. Facilite a sua vida e consulte o SICAF. Sim, o preço da inscrição para governo é mais caro. Também nos custa muito receber o dinheiro da inscrição de um órgão público. E o dinheiro só pode ser utilizado para custear o evento do próximo ano, uma vez que governo só paga depois do evento… e os nossos fornecedores não trabalham assim.

A grande novidade na minha opinião é a inscrição VIP. Todo mundo que se inscreve tem acesso às palestras nos 2 dias, coffe-break e a cervejada que deverá rolar no encerramento. Mas quem puder pagar um pouco mais, já pode incluir o custo do almoço no hotel. As vagas são limitadas, pois o restaurante do hotel não vai comportar todos. Mas para quem optar por isso, vai poder almoçar junto com os palestrantes, patrocinadores e a organização do evento. As vagas são limitadas a 80 pessoas, então corram, pois a maioria dos inscritos até agora optou mesmo pela inscrição VIP.

Bom, por enquanto é só, façam já sua inscrição e o chopp será por nossa conta. Em tempo: há também uma promoção para os 20 primeiros inscritos confirmados no evento. Eles ganharão um elefante de pelúcia do PGBR. Mas acredito que quando você estiver lendo isso, a promoção já tenha acabado. A não ser que o 20 primeiros demorem muito para confirmar (a.k.a. pagar) suas inscrições.

 As inscrições para o PGBR2011 se iniciaram hoje

Para aqueles que se inscreverem até 16/09 o preço estará bem mais em conta:

leia mais

   

    O telles fez a melhor chamada de trabalhos que alguém poderia escrever, portanto nem vou tentar fazer melhor. Vai lá e lê!!!

    Enviei duas propostas:

Escalabilidade, As Modas e (No)SQL

Escalabilidade e a miserabilidade do fracasso

 

Trabalhando com SQL

Quase todas estas dicas abaixo recebi na lista de PostgreSQL do http://postgresql.org.br. Sempre que lembrei concedi os devidos créditos.
1) Criar Tabela tendo outra outra como base e já importando todos os registros dessa outra:
CREATE TABLE tabelanova AS SELECT * FROM tabealexistente;
2) Inserindo com SELECT
Inserir todos os registros de uma tabela em outra:
INSERT INTO tabelaqueimporta SELECT * from tabelaqueexporta;

insert into engenharia.insumos (grupo,insumo,descricao,unidade) select grupo,insumo,descricao, CAST(unidade AS int2) AS “unidade” from engenharia.apagar

insert into engenharia.insumos (grupo,insumo,descricao,unidade) select grupo,insumo,descricao, cast(unidade AS INT2) AS unidade from engenharia.apagar

$conn = pg_connect(“host=10.40.100.186 dbname=apoena user=_postgresql”);
for($x=10;$x<=87;$x++){
$sql=”update engenharia.precos set custo_produtivo = (select custo_produtivo from engenharia.apagar where insumo=’$x’) where insumo=’00′ || ‘$x’”;
$ret=pg_query($conn,$sql);
}

3) Atualizar um campo em todos os registros de uma tabela recebendo de outra tabela:

UPDATE servicos s SET custo = total FROM composicoes c
WHERE s.tabela = c.tabela AND s.servico = c.servico

Uso do Like e de Expressões Regulares

Registros:
Ribamar Ferreira de Sousa
João Pereira Brito

Usando LIKE e ILIKE

SELECT * FROM clientes WHERE nome LIKE ‘Riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome LIKE ‘riba%’; // Nada retorna
SELECT * FROM clientes WHERE nome ILIKE ‘riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome NOT LIKE ‘pedro’; // Retorna ambos os registros

Usando Expressões Regulares

SELECT * FROM clientes WHERE nome ~~ ‘Riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome ~~ ‘riba%’; // Nada retorna
SELECT * FROM clientes WHERE nome ~~* ‘riba%’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome !~~ ‘pedro’; // Retorna ambos os registros
SELECT nome FROM clientes WHERE nome ~ ‘Ribamar Ferreira de Sousa’; // Retorna Ribamar Ferreira de Sousa
SELECT * FROM clientes WHERE nome !~ ‘jorge’; // Retorna ambos

4) Buscar nas tabelas de sistema do postgresql, todos as tabelas de um determinado schema, os campos que sejam do tipo boolean..

SELECT n.nspname AS Schema, c.relname AS Tabela, t.typname AS Tipo
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_type t ON t.oid = c.reltype
WHERE c.relkind = ‘r’::”char”
AND t.typname = ‘boolean’;

5) Exemplos de Joins

Join com 4 tabelas

$w_sql = ” TRUE “;

if ( $p_tabela != “”) { $w_sql = $w_sql . ” AND tabela ~~*’” . $p_tabela . “‘”; }
if ( $p_insumo_grupo != “”) { $w_sql = $w_sql . ” AND insumo_grupo ~~*’” .$p_insumo_grupo.”‘”; }
if ( $p_insumo != “”) { $w_sql = $w_sql . ” AND insumo ~~*’” . $p_insumo . “‘”; }
if ( $p_fornecedor != “”) { $w_sql = $w_sql . ” AND fornecedor ~~*’” .$p_fornecedor.”‘”; }

$w_sql=”SELECT distinct on (p.tabela, p.insumo_grupo, p.insumo, p.fornecedor) p.custo_produtivo, p.data_inclusao,
t.tabela, t.descricao as tabelad,
ig.grupo, ig.descricao as insumogd,
i.grupo, i.insumo, i.descricao as insumod,
f.codigo_fornecedor, f.razao_social as fornecedord
FROM $m_table as p, $m_table_tab as t, $m_table_ing as ig, $m_table_ins as i, $m_table_for as f
WHERE p.tabela=t.tabela AND p.insumo_grupo=ig.grupo AND p.insumo=i.insumo AND p.fornecedor=f.codigo_fornecedor
AND p.insumo_grupo = i.grupo ORDER BY p.tabela DESC, p.insumo_grupo;”;

/*
p – $m_table (engenharia.precos)
i – $m_table_ins (engenharia.insumos)
ig – $m_table_ing (engenharia.insumos_grupos)
t – $m_table_tab (engenharia.tabela)
*/

6) Mudar Tipo de Dados de Campo – CAST (Só >=8.0):

ALTER TABLE tabela ALTER COLUMN campo TYPE tipo;
ALTER TABLE produtos ALTER COLUMN preco TYPE numeric(10,2);
ALTER TABLE produtos ALTER COLUMN data TYPE DATE USING CAST (data AS DATE);

7) Renomear Tabela
ALTER TABLE tabela RENAME TO nomenovo;
ALTER TABLE produtos RENAME TO equipamentos;

Tamanho de Tabela, Banco ou Todos os Bancos do SGBD:

Tamanho de Banco de Dados (postgresql 8.1 ou superior):
select pg_database_size(‘nomebanco’);

Tamanho de Tabela
select pg_tablespace_size(‘nometabela’);

Tamanho de todos os bancos de dados do SGBD:
select (sum(relpages) * / 1024 || ‘ MB’ as tamanho from pg_class where relowner > 1;

Ou

select (sum(relpages) / 2^7) :: int || ‘ MB’ as tamanho from pg_class where relowner > 1;

9) Validação de e-mails

1 – select distinct(campo_email),campo_nome, campos_n from tabela where campo_email like ‘%@%.%’
2 – SELECT POSITION(‘@’, ‘ribafs@gmail.com’) > 0
3 – select ‘coutinho.php@gmail.com’ ~ ‘@’
4 – select ‘coutinho.php@gmail.com’ like ‘%@%’
5 – select if (‘campo_email’ like “%@%.%”,”TRUE”,”FALSE”) as flag, campo_adcional from tabela
6 – select ‘coutinho@gmail.com’ similar to ‘%@%.%’;

10) Temos um campo (insumo) com valores = 1, 2, 3, … 87
Queremos atualizar para 0001, 0002, 0003, … 0087

UPDATE equipamentos SET insumo = ’000′ || insumo WHERE LENGTH(insumo) = 1;
UPDATE equipamentos SET insumo = ’00′ || insumo WHERE LENGTH(insumo) = 2;

Outra saída mais elegante ainda:

UPDATE equipamentos SET insumo = REPEAT(’0′, 4-LENGTH(insumo)) || insumo;

11) Retornar o número de usuários conectados
select count(*) from pg_stat_activity

pg_stat_database que apresenta para cada banco de dados o número de conexões.
Eu particularmente acho que fica mais fácil de visualizar do que o pg_stat_activity quando se tem muitas conexões.

Mostrar uso dos índices dos bancos de dados:
select * from pg_statio_user_indexes;

select * from pg_stat_user_indexes;

Mostra estatística de uso das tabelas e manutenção:
select * from pg_stat_all_tables;

Mostra todas as tabelas do atual esquema do atual banco:
select * from pg_stat_user_tables;

pg_stat_get_tuples_returned(oid) bigint Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index
pg_stat_get_tuples_fetched(oid) bigint Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index
pg_stat_get_tuples_inserted(oid) bigint Number of rows inserted into table
pg_stat_get_tuples_updated(oid) bigint Number of rows updated in table
pg_stat_get_tuples_deleted(oid) bigint Number of rows deleted from table
pg_stat_get_blocks_fetched(oid) bigint Number of disk block fetch requests for table or index
pg_stat_get_blocks_hit(oid) bigint Number of disk block requests found in cache for table or index
pg_stat_get_last_vacuum_time(oid) timestamptz Time of the last vacuum initiated by the user on this table
pg_stat_get_last_autovacuum_time(oid) timestamptz Time of the last vacuum initiated by the autovacuum daemon on this table
pg_stat_get_last_analyze_time(oid) timestamptz Time of the last analyze initiated by the user on this table
pg_stat_get_last_autoanalyze_time(oid) timestamptz Time of the last analyze initiated by the autovacuum daemon on this table

This is controlled by configuration parameters that are normally set in postgresql.conf

The function pg_stat_get_backend_idset provides a convenient way to generate one row for each active server process. For example, to show the PIDs and current queries of all server processes:

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Visualizar os processos do portgresql num UNIX:

ps auxww | grep ^postgres

Formato de retorno:
postgres: user database host activity

12) Corrigindo Estouro do Máximo de transações (2 bilhões)
Constatando:

SELECT datname, age(datfrozenxid) FROM pg_database;

age acusa mais de 2 bilhões

Tarcizio Meurer

- Execute um dumpall na base
- drop a base e o agrupamento de dados
- recrie o agrupamento
- recrie a base
- carrege os dados novemente.
13) Total de Registros de Todos os Bancos do SGBD (PHP):

<?php

$conexao=pg_connect(“host=127.0.0.1 user=postgres password=postabir”);

$sql=”SELECT datname AS banco FROM pg_database ORDER BY datname”;
$consulta=pg_query($conexao,$sql);

$banco = array();
$c=0;
while ($data = @pg_fetch_object($consulta,$c)) {
$cons=$data->banco;

$banco[] .= $cons;
$c++;
}

$sql2=”SELECT n.nspname as esquema,c.relname as tabela FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace
and c.relkind = ‘r’ — no indices
and n.nspname not like ‘pg\\_%’ — no catalogs
and n.nspname != ‘information_schema’ — no information_schema
ORDER BY nspname, relname”;

for ($x=0; $x < count($banco);$x++){
if ($banco[$x] !=”template0″ && $banco[$x] != “template1″ && $banco[$x] !=”postgres”){
$conexao2=pg_connect(“host=127.0.0.1 dbname=$banco[$x] user=postgres password=postabir”);
$consulta2=pg_query( $conexao2, $sql2 );

while ($data = pg_fetch_object($consulta2)) {
$esquematab=$data->esquema.’.’.$data->tabela;
$sql3=”SELECT count(*) FROM $esquematab”;
$consulta3=pg_query($conexao2,$sql3);
$res=@pg_fetch_array($consulta3);

print ‘Banco.Esquema.Tabela -> ‘.$banco[$x].’.’.$data->esquema.’.’.$data->tabela.’ – Registro(s) – ‘.$res[0].”;
$total += $res[0];
}

}
}
print “Total de Registro de todas as tabelas de todos os bancos “. $total;

?>
14) Uso da Constraint check

CREATE TABLE testes(
codigo serial primary key,
idade integer,
check (idade > 18 AND idade < 70)
)

Alternativas:

check (preco > desconto)

check (desconto > 0 AND preco > desconto)

————-
Somente aceitar c ou e (simulando campo tipo enum do MySQL):
tipo char(1) check (tipo =’c’ OR tipo=’e')

Para este cria-se uma combo com values ‘c’ e ‘e’.
15) Manutenção do PostgreSQL:
No CRON:

/home/pgsql/bin/psql -c “vacuum full analyse” -d dadosadv -U postgres

Consultas no Pronpt do SO:
psql -U postgres -d banco -c “SELECT * FROM clientes”

Manutenção em Tabela
vacuum analize tabela;

Reindexar Banco, tabela ou índice
reindex database banco;

Exibir plano de consulta
explain select * from tabela;

Exibir todos os parâmetros de runtime
show all;
16) Consulta com Dias Úteis

Só para constar aqui vai uma expressão SQL que fornece os
dias úteis de um período. Considerei que existe uma tabela
com o registro dos feriados e outros dias que não devem ser
considerados (emendas, pontos facultativos, etc):

SELECT dia FROM
(SELECT (’2007-10-01′::date+s.a*’1 day’::interval) AS dia
FROM generate_series(0, ’2007-10-31′::date -
’2007-10-01′::date, 1) AS s(a)) foo
WHERE EXTRACT(DOW FROM dia) BETWEEN 1 AND 5
EXCEPT
SELECT dia FROM tab_feriado;

Osvaldo (na lista postgresql-br)
17) Update em uma chave primária sem causar duplicação de chave

UPDATE teste SET coluna1 = t_aux.coluna1+1
FROM (
SELECT coluna1
FROM teste
ORDER BY coluna1 DESC
) t_aux
WHERE teste.coluna1 = t_aux.coluna1;

Osvaldo (na lista postgresql-br)
18) Como saber se existe uma transação ativa

select pg_stat_activity;

Dica do João Paulo.
19)Inserir data como valor default:

Pode usar também o current_date ou o localtimestamp.

insert into tabela(data) values ((select current_date));

ou

insert into tabela(data) values ((select localtimestampo));
20) Ler último saldo de tabela

Tenho o seguinte conteudo numa tabela de contas:

Lancto–CCorrente–Banco–OP–DataLan——-Valor———Saldo–
1 12345-6 002 C 19/11/2007 1000.00 1000.00
2 12345-6 002 C 19/11/2007 2000.00 3000.00
3 12345-6 002 D 19/11/2007 100.00 2900.00
4 23450-6 001 C 19/11/2007 2000.00 3000.00
5 23450-6 001 D 19/11/2007 100.00 2900.00

Preciso retornar sempre o último SALDO registrado.
Como nunca vou saber a data exata da periodo de consulta.

Estou executando:

SELECT saldoatual FROM lanban WHERE contacorrente = ’12345-6′ and datalan <= ’2007/12/01′ ORDER BY datalan DESC LIMIT 1

Retona o Saldo: 1000.00, preciso pegar o ultimo saldo da conta 12345-6: que é 2900.00.

Isso porque tabelas são conjuntos de dados. O padrão SQL *não* garante a
ordem dos dados. Mesmo se ele garantisse, um simples UPDATE podia mudar
o ordem dos dados e o seu SELECT não retornaria o valor desejado.

> Alguem tem alguma dica?
>
O campo ‘Lancto’ é do tipo serial? Se for poderias utilizar:
SELECT saldoatual FROM lanban WHERE contacorrente = ’12345-6′ ORDER BY
“Lancto” DESC LIMIT 1.

Dica do Euler Taveira de Oliveira
21) Formato de moeda

O correto seria:
to_char(1030.52,’9G999D99′)
mas o resultado é: 1,030,52
como você pode observar existe um problema no
separador de milhar (indicado pelo G) que é
considerado como , e não como . que seria o esperado.

Uma maneira de contornar (não muito elegante) é:
to_char(1030.52,’9″.”999D99′)

Corrigido na versão 8.3
22) Saber o Tamanho de Tabela e de Índices

pg_relation_size()
pg_total_relation_size()

-Leo
–
Leonardo Cezar
23) Último Saldo
Fernando Brombatti

A situação é a seguinte. Não se sabe se o serial citado (por N razões) vai ser o último valor existente. Nada me garante que estes dados não sofreram algum UPDATE. Sendo assim, recomendo:
1) alterar o campo DATE para TIMESTAMP
2) alterar o query:
SELECT lan.saldoatual
FROM lanban lan
WHERE lan.contacorrente = ’12345-6′ AND lan.datalan = (SELECT MAX(maxlan.datalan)
FROM lanban maxlan
WHERE maxlan.contacorrente = lan.contacorrente)
Isso faz com que no primeiro SQL eu traga os lancamentos da conta e no segundo eu trago a máxima data de lançamento para a mesma conta. Como as contas são iguais, trago a máxima data da conta atual, logo tenho o saldo atual.
É confuso, mas é o mais seguro (podem haver UPDATES neste caso também, mas aí não se depende de um serial).
Para este query funcionar bem necessita mais um índice em datalan ao menos.
Nos nossos sistemas da prefeitura nunca usamos saldos desta forma, pois aí se é removido algum registro a informação não fica correta.

Espero não ter confundido tanto.
24) Encontrando tanela de sistema

Para localizar informações desse tipo existe o information_schema
(conforme citado pelo Leandro). Utilizando o catalogo poupa voce de
futuras dores de cabeça quando por exemplo houver alguma alteração
estrutural em tabelas do sistema em versõs futuras. As views do
catalogo deverão permanecer com o máximo de compatibilidade entre
versões (segundo padrão SQL).

Além de ser mais simples:

SELECT *
FROM information_schema.tables
WHERE table_name = ‘foobar’;

Infelizmente não possuimos referencias a outros banco de dados
(banco.schema.tabela), portanto o comando deverá ser executado em
todos seus bancos para localizar a tabela ou um programeta bash
parecido com isso:

$ ARG=$1 || “foo” && for DATABASE in `psql -U postgres -c “\l” \
| cut -d”|” -f1 | egrep ‘^(\ [a-z])’`
do
psql -U postgres -d $DATABASE -Atc \
“SELECT ‘O banco de dados $DATABASE possui a tabela: $RG’
FROM information_schema.tables
WHERE table_name = ‘$ARG’”;
done;

Abraço!

-Leo
25) Como Localizar e Deletar registros duplicados

1.Select para localizar duplicados
select campo,campo1,count(*)
from tabela group by campo,campo1 having count(*) > 1

2.Deletar duplicados:
delete from tab p1
where rowid < (select max(rowid)
from tab1 p2
where p1.primary_key = p2.primary_key);
26) Inserir registros em uma específica posição
> Hi, how are you? maybe you know how SQL insert data
> bellow or above in database tabe? example insert
> data from position table 5 thanks
>

No, I don’t known.
But if you make a copy from table,
create a new table with same structure,
insert a new register,
import register from old table, then first register
are this last register inserted.
27) Timezones do PostgreSQL (lista pgbr-geral)

No POSTGRESQL.CONF tem o timezone onde você pode colocar algo do tipo:

TIMEZONE=BRAZIL/EAST esta é minha configuração, ou seja, de minha região.

Analise.
Wandrey

Outra ———–
Na maioria dos casos é criado um link do diretório de timezones do
S.O. (/usr/share/zoneinfo//usr/share/zoneinfo/) para o diretório de
Timezones do Postgres ($PGDIR/share/timezone )Que possui seu próprio
sistema de controle de timezone, se não me engano a partir d versão

–
Att:
Thiago Risso
28) Inserir Número Aleatório em Tabela

CREATE TABLE page (
id SERIAL PRIMARY KEY,
about TEXT NULL,
);

ALTER TABLE page ADD myrand NUMERIC NOT NULL DEFAULT RANDOM();

UPDATE page SET myrand = DEFAULT;

SELECT id FROM page WHERE myrand >= RANDOM() ORDER BY myrand LIMIT 1;

This approach has some problems:

* If the number you pick is greater than the largest number in the myrand column, you will not find any matching rows.
* The gaps between the random values in the myrand column are not uniform, and thus the rows selected are not random. Imagine a table with two rows and myrand values of 0.8 and 0.9. If the random number compared to myrand is .8 or less, the first row is chosen. But the second row is only chosen if the value picked is between .8 and .9
* If more than one row has the exact same number, it is likely that one of them will never get picked.

Mais detalhes em: http://people.planetpostgresql.org/greg/index.php?/archives/118-guid.htm…
29) Desabilitar Triggers
Vinicius Santos – MSI escreveu:
Thiago Boufleuhr escreveu:

Como faço para desabilitar as triggers em uma sessão no PLSQL ?

Thiago Boufleuhr

ALTER TABLE [NOME DA TABELA]
DISABLE TRIGGER [NOME DA TRIGGER]
Ou
ALTER TABLE [NOME DA TABELA]
DISABLE TRIGGER ALL

ALERTA:
William Leite Araújo: MUITO CUIDADO AO USAR “DISABLE TRIGGER ALL”

As constraints de chave estrangeira são controladas via TRIGGER. Caso desabilite todos os gatilhos, a checagem da integridade referencial (chaves estrangeiras) serão desabilitadas!
30) Codificação de Caracteres
Euler Taveira de Oliveira
>Evandro Ricardo Silvestre wrote: Codificação de caracteres do cliente e
do servidor podem ser diferentes. Se a codificação do cliente é diferente da codificação do servidor, o servidor PostgreSQL tenta fazer uma conversão antes de armazenar/retornar os dados. Um problema que existia é que a aplicação cliente (no caso abaixo o psql) não avisava se a codificação informada ao servidor (client_encoding) era a mesma do ambiente (terminal).
Bem vindo ao psql 8.3.0, o terminal iterativo do PostgreSQL.
Digite: \copyright para mostrar termos de distribuição
\h para ajuda com comandos SQL
\? para ajuda com comandos do psql
\g ou terminar com ponto-e-vírgula para executar a consulta
\q para sair
template1=# show client_encoding;
client_encoding
—————–
LATIN1
(1 registro)
template1=# show server_encoding;
server_encoding
—————–
LATIN1
(1 registro)
template1=# select upper(‘áéíóú’);
upper
——-
ÁÉÍÓÚ
(1 registro)
template1=# set client_encoding to ‘utf-8′;
SET

template1=# show client_encoding;
client_encoding
—————–
utf-8
(1 registro)
template1=# select upper(‘áéíóú’);
ERRO: sequência de bytes é inválida para codificação “UTF8″: 0xe1e9ed
DICA: Este erro pode acontecer também se a sequência de bytes nãocorresponde a codificação esperado pelo servidor, que é controlada por “client_encoding”.
ERRO: sequência de bytes é inválida para codificação “UTF8″: 0xe1e9ed
DICA: Este erro pode acontecer também se a sequência de bytes não corresponde a codificação esperado pelo servidor, que é controlada por “client_encoding”.
[trocando a codificação de caracteres do terminal e digitando novamente]
template1=# select upper(‘áéí’);
upper
——-
ÁÉÍ
(1 registro)
31) Como visualizar as consultas correntes no Postgres

Colaboração: Frederico Palma

Data de Publicação: 16 de fevereiro de 2008

É necessário habilitar o stats_command_string no postgresql.conf:

stats_command_string = true

Essa configuração pode ser alterada em um banco que está ativo sem a necessidade de reiniciá-lo e sem afetar as conexões abertas para recarregar as configurações. Envie um SIGHUP ou use o comando:

pg_ctl reload

Quando stats_command_string está ativo a tabela pg_stat_activity armazena todas consultas correntes.

Realizando a consulta:

SELECT datname,procpid,current_query FROM pg_stat_activity

Teremos a lista dos bancos de dados utilizados com seus respectivos processos (PID) referente às consultas.

SELECT datname,procpid,current_query FROM pg_stat_activity ORDER BY procpid;

datname | procpid | current_query
————+———+—————–
mydatabase1 | 2587 | < IDLE>
mydatabase2 | 15726 | SELECT * FROM users WHERE id=123 ;
mydatabase3 | 15851 | < IDLE>

Publicado originalmente na Dicas-L – http://www.dicas-l.com.br/dicas-l/20080216.php

32)

O pessoal de RS se mexeu e mais um PGDay RS estará ocorrendo este ano. Será no dia 19 de agosto (sexta-feira) em Porto Alegre.

Os palestrantes, a programação e o local estão todos publicados no site do evento. Infelizmente não vou poder estar lá, mas já vi que a grade de palestrantes é excelente e será um evento bem bacana, incluindo um Dojo PL/pgSQL e os já conhecidos Lightning Talks.

Gostaria aqui de parabenizar os palestrantes Dickson Guedes, Diego Rossi, Diogo Biazus, Eduardo Wolak e Fabiano Machado Dias por palestrarem no evento e em especial para o Fabrízio de Royes Mello que é a pessoa que está liderando a organização do evento.

Não esqueçam de mandarem as fotos, publicarem as palestas, ok?

Nesta semana foi aberta a Chamada de Trabalhos do PGBR2011.

A chamada ficará aberta até o dia 09/09/2011 então não bobeie e mande logo as suas propostas. Este ano serão 4 modalidades diferentes (fora os Lightning Talks): Palestras normais de 50 minutos, tutoriais de 2 horas, hacker talks que podem der de 30 minutos até 2 horas e os painéis acadêmicos que serão expostos no saguão do evento. Tudo explicado no site do evento. Você pode mandar até 5 propostas diferentes para a banca avaliadora (da qual eu não faço parte) escolher.

Eu já estou preparando as minhas propostas, mas segue algumas que eu gostaria de ver lá. Eu sei que tem muita gente que já usa PostgreSQL há um tempão e acha que não teria nada de interessante para falar. Não se intimide, tenho certeza que se você usa o PostgreSQL há mais de um ano, deve se sentir à vontade para falar algum assunto como:

• Postgressssss, lendas urbanas sobre PostgreSQL e um apanhado da sua evolução nas últimas versões;

• Casos de sucesso de quem não tem vergonha de revelar que usa o PostgreSQL em negócios críticos;

• Porquê o PostgreSQL é cada vez mais amante predileta dos DBAs experientes;

• “Meu PostgreSQL não Conecta” e outras coisas que alguém tem de escrever para a gente não repetir novamente na lista…

• Ops, minha base já tem mais de 1TB… ;

• BI, Data Mining, OLAP, PL/R e outros bichos;

• Full Text Search, particularmente como raios se montam Ranks e dicionários personalizados;

• Funções de agregação personalizadas, operadores personalizados, tipos de dados personalizados e o que mais você tiver coragem de inventar;

• Bancos de dados em pesquisas científicas, como em biologia, física e outras ciências ocultas;

• Haks, muitos hacks! Queremos hacks em C, em Perl, Python, SPI, PL/pgSQL etc. Queremos nerds genuínos!!!

• Vou te apresentar a uma tal de libpq…

• Tuning em bases OLTP  com fucking hight concorrência;

• ETL (Extract Transform Load) em ambientes alucinógenos;

• pgbench e outras técnicas para emular a carga da aplicação em ambiente de homologação ou “hum…. então dá para fazer isso antes de sentar a aplicação no cliente?”

• Versionamento de DDL e como controlar do demônio da tasmânia das alterações de estrutura em desenvolvimento e produção sem ir parar no hospital;

• Segurem os trolls, vamos falar sobre sistemas de arquivos, tablespaces e discos de novo….

• Como fazer poesia com SQL e parar de fazer caquinha com PL/você_não_precisa_usar_isto_aqui.

• Técnicas (ok, se você gosta de buzzwords, pode chamar de “design patterns”) de otimização de processos e ajuste de SQL;

• “Rodei o EXPLAIN, e agora?”;

• Magia negra com Window Function;

• CTE, recursividade e como não destruir a memória do seu servidor;

• Localização, codificação de caracteres e coisas que infelizmente você tem de aprender se não quiser sofrer por toda a eternamente com isso;

• Segurança para preguiçosos (ou seja, para todo mundo);

• Técnicas de monitoramento, administração que funcionam e fazem sentido para seres mortais;

• Armadilhas no zoológico das replicações;

• Muito além das configurações do postgresql.conf: personalizando bases, roles , tabelas, tablespaces e até sessões para um ajuste perfeito;

• Eu sei, eu sei…. ACID, blá, blá, blá, Teoria Relacional, etc e tal, mas o C. J. Date diz que bi, bi, bi, bó, bó, bó…. mas eu quero muito ver “Técnicas de NoSQL integradas com PosgreSQL”;

• Backup e o “complexo de Chuck Norris”. (Quem respondeu a pesquisa sobre uso de PostgreSQL sabe do que eu estou falando);

• Técnicas de recuperação de desastres e como é chato ouvir “mas eu já faço assim há 5 anos e nunca deu nenhum problema…”;

• Não entendeu? Vou falar de novo: RECOVER, RECOVER, RECOVER!

• Quer dizer então que para ter um banco de dados é preciso ter um DBA na equipe ou aprender como fazer o trabalho de um?

OBS: Quem sabe este ano o indefectível Osvaldo Kussama não manda uma proposta?

OBS2: Se tiver alguma outra sugestão de palestra que você gostaria de ver no PGBR201, não deixe de deixar um comentário aqui! Vai que alguém gosta da ideia e você acaba tendo uma aula sobre aquele tema que vem lhe assombrando há tempos…

Você já ouviu da função clock_timestamp? Ela retorna a data e hora com bastante precisão junto com a time zone do servidor, da mesma forma que as funções now() e current_timestamp. Então para quê implementar esta nova função? O interessante da clock_timestamp é que a mesma retorna o timestamp do término da transação, enquanto now e current timestamp retornam a data/hora do início da transação corrente.

É uma função que pode ser importante para aqueles que necessitam de alta precisão ao lidar com variáveis temporais.

Exemplos:

1 - Sintaxe básica

teste=# SELECT clock_timestamp();
        clock_timestamp       
-------------------------------
 2011-07-21 09:34:12.645251-03
(1 registro)

2 - Extração de parte do valor retornado.

teste=# SELECT SUBSTRING(CAST(now() AS VARCHAR) FROM 1 FOR 10);
 substring 
------------
 2011-07-21
(1 registro)


3 - Lado a lado o resultado de clock_timestamp() e now() na mesma transação. Observe que os valores são diferentes e indicam o timestamp de início e de término da transação.

teste=# SELECT SUBSTRING(CAST(now() AS VARCHAR) FROM 1 FOR 10);
 substring 
------------
 2011-07-21
(1 registro)

teste=# SELECT now() || '   ' || clock_timestamp();
                           ?column?                           
---------------------------------------------------------------
 2011-07-21 10:21:46.592655-03   2011-07-21 10:21:46.592828-03
(1 registro)

4 - Diferença entre clock_timestamp() e now(), mostrando o tempo decorrido entre o início e o término da transação.

teste=# SELECT clock_timestamp() - now();
    ?column?    
-----------------
 00:00:00.000099
(1 registro)

Há muita coisa importante a se preocupar durante a modelagem de um banco de dados, contudo achei importante mostrar algumas delas. Sei que algumas pessoas não concordarão de imediato com todas as regras, mas ainda não vi muitos argumentos válidos contra as mesmas. Regra Nº1: evite valores Nulos em campos numéricos quando nulo for equivalente [...]

Os instaladores para linux da versão 9.1 Beta 2 estão disponíveis para quem quiser testar. Esse é mais um indício de que a versão 9.1 logo será oficialmente lançada.

Baixe os RPMs aqui e teste à vontade!

Testes de performance de servidores de bancos de dados são importantes para se avaliar ambientes de produção antes da implantação de projetos que envolvam muitos usuários e grande tráfego de informações. Uma ferramenta boa e estável para este tipo de teste é o Jakarta Jmeter.

Através do JMeter é possível configurar conexões ao servidor, criar requisições JDBC (comandos, chamadas de funções, etc.), submetê-las ao servidor e analisar o resultado da execução. A ferramenta permite ainda criar múltiplas threads simulando um grande número de usuários simultâneos, e executar para cada thread as requisições mais de uma vez, gerando uma grande carga de acessos que testa os limites de carga aceitos pelos bancos de dados.


Adicionalmente, é possível utilizar ouvintes (listeners) para apresentar os resultados da execução dos comandos na forma de tabelas, árvores e gráficos diversos.

O JMeter é compatível com qualquer banco que aceite conexão JDBC, incluindo o postgres e pode testar ainda outros tipos de requisição como ftp e http. Baixe o jmeter agora!

Vamos mostrar as principais etapas de utilização da ferramenta utilizando um teste feito no postgresql.

1. Instalação

Baixe a ferramenta, descompacte os arquivos em uma pasta. Observe que existem vários subdiretórios. As pastas mais relevantes são a LIB e a BIN.

Copie o arquivo do driver jdbc do postgresql (e o dos demais bancos com os quais trabalhar) para a pasta LIB do JMeter.

2. Execução

No windows pode ser utilizado o arquivo jmeter.bat, na pasta BIN da ferramenta (jakarta-jmeter-2.4/bin no meu caso). No Linux existe o arquivo jmeter.sh.

Existe a opção de rodar diretamente do arquivo .JAR, digitando:

java  -jar ApacheJMeter.jar

A tela inicial apresentada é bastante simples, com uma barra de menu e uma árvore onde são apresentados hierarquicamente os comandos dos planos de testes.

A árvore apresenta duas grandes divisões: plano de testes e área de trabalho. A área de trabalho pode ser utilizada para colocar comandos que não serão executados, enquanto que o plano de testes é a parte da treeview que apresenta os comandos do plano que serão executados, sendo que comandos de teste podem ser arrastados livremente entre área de trabalho e plano de testes.




3. Número de Usuários Simulados no Teste

O primeiro passo é definir o número de usuários que seu teste deseja simular.  Se o seu teste tiver muitos usuários, pode ser caracterizado como teste de carga. Caso tenha apenas um, executando uma vez cada comando, pode ser entendido como um teste funcional. Cada plano de testes pode agregar dezenas de requisições (testes) de banco de dados (JDBC), FTP, HTTP, entre outras possibilidades.


Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Threads (Users)\ Grupos de Usuários". A tela mostrada permite que se defina quantos usuários virtuais serão simulados, o tempo de inicialização de cada usuário e o número de vezes que cada usuário simulado executará os próximos comandos do plano de testes. Caso se deseje 100  usuários, executando 10 vezes cada teste, uma execução do plano de testes testará 10000 execuções do plano.



4. Configurando o JDBC

Para fazer testes de banco de dados, devemos configurar a conexão JDBC. 

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Elemento de Configuração\  Configuração da Conexão JDBC".

A tela mostrada permite que se defina os parâmetros de conexão com o servidor, tais como limite de conexões, tempo máximo de conexão, intervalo para timeout de conexão, entre outros. A figura abaixo mostra os principais parâmetros utilizados.


5. Criação do Teste de Banco

Para criar os testes de banco de dados em si, basta se definir o SQL a ser submetido. O comando será executando tantas vezes forem definidas na seção "Grupo de Usuários" do plano de testes.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Testador\ Requisição JDBC".

A tela mostrada permite que se forneça o comando SQL a ser testado. Não utilize ponto e vírgula ";", pois pode gerar erro de execução.



6. Adicionando Ouvintes (Listeners)

Antes de executar os testes, deve ser definido de que forma o resultado da execução será apresentado. Os ouvintes monitoram os testes e apresentam o resumo das execuções de várias formas.

Um mesmo teste pode ser visualizado de mais de uma forma, o que facilita o entendimento, seja como árvore, tabela, gráfico ou geração de arquivo.


Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Ouvinte\ Árvore de Resultados".  Acrescente outros ouvintes como "Relatório Agregado" e "Ver Resultados em Tabela". As figuras abaixo mostra o resultado de um teste visto por mais de um ouvinte.




7. Executando Testes

Utilize a barra de menu para executar os testes:
- "Executar\ Iniciar" - Executa os testes do plano de trabalho atual
- "Executar\ Limpar Tudo" - Limpa os resultados de testes anteriores para nova rodada de testes
- "Arquivo\ Salvar" - Salva o Plano de Testes


Independentemente de termos boas ferramentas como o JMeter, temos sempre de testar as nossas aplicações, e não podemos descuidar dos bancos de dados. O JMeter tem potencial para automatizar boa parte dos testes feitos com bancos de dados sem grande esforço, o que não elimina a necessidade de bons testadores e de cuidado na hora de se realizar e interpretar os resultados apresentados.

Teste o JMeter e me diga o que achou dele! Não se esqueça que a qualidade do seu teste é consequência de um bom plano de testes!

Você sabe qual é a versão do seu servidor Postgres? Sabe mesmo? E do cliente (estava pensando que é sempre a mesma?)? Ele é 32 ou 64 bits? Para muitos desenvolvedores a resposta é não, e em vários casos nem se sabe como recuperar estas informações.


A melhor maneira de se saber a versão de um servidor banco de dados é simplesmente consultando-a. E no caso do postgresql a função que retorna estas informações é a VERSION().

Exemplo: 
- Consulta padrão:
SELECT version();


Resultado:
PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
Para recuperar informações de versionamento do cliente do banco deve se utilizar o utilitário psql:

Exemplo:
psql --version

Resultado: 
psql (PostgreSQL) 9.0.4
contém suporte a edição em linha de comando

Agora você pode visualizar facilmente a versão atual do seu postgres. Então é bom passar mais alguma informação sobre como interpretar o número de versionamento.

O postgres é versionado em um sistema de numeração com três números, no formato "A.B.C". A versão de produção atual, seguindo este formato, é a 9.0.4. A sistemática de numeração de versão do postgres pode ser conferida aqui.

- A - Número de versão principal. Quando este número muda significa que aconteceram alterações radicais na estrutura e funcionamento do banco. A versão atual é 9, e não há planejamento para uma versão 10 no momento.

- B - Número de versão secundário. Quando este número muda significa que aconteceram alterações na estrutura e funcionamento do banco que demandam. Os números A e B devem ser considerados em conjunto, e não apenas o primeiro número de versão, pois indicam uma versão em especial. O B da versão atual é 0, mas existe uma versão beta do postgres 9.1 e planejamento da versão 9.2.

- C - Número de atualizações aplicadas à versão "A.B". Na versão 8.4, por exemplo, já foram aplicadas 10 atualizações. O valor inicial de C é 0 e atualmente estamos na versão 9.0.4, indicando que a versão 9.0 já sofreu 4 atualizações. É importante acompanhar os informes de atualização especialmente quando solucionam questões chave de segurança, performance e bugs que afetam de alguma forma o desenvolvimento dos sistemas e a disponibilidade dos servidores de banco.

Que tal conferir agora a versão que está no seu sistema?

Uma boa maneira de utilizar o utilitário psql é como meio para executar scripts que recuperem informações e as armazenem em arquivos. Desta forma, podemos gerar relatórios de alta relevância e complexidade a um custo mínimo.

A sintaxe abaixo, lê um script de um arquivo de entrada com o programa e o executa no PSQL:

psql -U usuario -d banco_de_dados -f arquivo_de_entrada

Mas como gerar relatórios de forma fácil com o psql? Simplesmente as opções são infinitas, pois podemos mesclar comandos SELECT, funções como a current_timestamp e comandos do psql. A solução depende da sua criatividade.

Abaixo coloco um script simples que recupera informações sobre os objetos do banco. A opção "\o" especifica um arquivo de saída do relatório. Para se executar o script, o mesmo foi gravado no arquivo "entrada.txt" e foi executado no prompt do psql através da chamada "psql -U postgres -d teste -f entrada.txt". O script foi testado no Postgresql 9.0.

\o saida.txt
\echo Cabecalho
SELECT 'Inicio: ' || current_timestamp as Inicio;
\echo Relatorio no Arquivo Saida.txt
SELECT '#####################################################################' AS Titulo UNION ALL
SELECT '### Relatorio de Banco de Dados 1.0 #################################' AS Titulo UNION ALL
SELECT '### Claudio Leopoldino              #################################' AS Titulo UNION ALL
SELECT '### Script para livre distribuição e utilização #####################' AS Titulo UNION ALL
SELECT '### http://postgresqlbr.blogspot.com/ ###############################' AS Titulo UNION ALL
SELECT '#####################################################################' AS Titulo;
\echo Lista de Bancos de Dados
\qecho '#########################################################################################'
\qecho '### Bancos de Dados #####################################################################'
\qecho '#########################################################################################'
\l
\echo Lista de Bancos de Dados com Detalhe
\l+
\echo Lista de Usuarios
\qecho '#########################################################################################'
\qecho '### Lista de Usuarios ###################################################################'
\qecho '#########################################################################################'
\du
\echo Lista de Tabelas
\qecho '#########################################################################################'
\qecho '### TABELAS #############################################################################'
\qecho '#########################################################################################'
\dt
\echo Lista de Tabelas com Detalhe
\dt+
\echo Lista de Tabelas de Sistema
\dtS
\echo Lista de Tabelas de Sistema com Detalhe
\dtS+
\echo Lista de Indices
\qecho '#########################################################################################'
\qecho '### INDICES #############################################################################'
\qecho '#########################################################################################'
\di
\echo Lista de Indices com Detalhe
\di+
\echo Lista de Sequencias
\qecho '#########################################################################################'
\qecho '### SEQUENCIAS###########################################################################'
\qecho '#########################################################################################'
\ds
\echo Lista de Visoes
\qecho '#########################################################################################'
\qecho '### VISOES ##############################################################################'
\qecho '#########################################################################################'
\dv
\echo Lista de Visoes com Detalhe
\dv+
\echo Lista de Visoes de Sistema
\dvS
\echo Lista de Visoes de Sistema com Detalhe
\dvS+
\echo Lista de Privilegios de Acesso
\qecho '#########################################################################################'
\qecho '### PRIVILEGIOS DE ACESSO ###############################################################'
\qecho '#########################################################################################'
\dp
\echo Lista de Large Objects
\qecho '#########################################################################################'
\qecho '### LARGE OBJECTS #######################################################################'
\qecho '#########################################################################################'
\dl
\echo Lista de Funcoes
\qecho '#########################################################################################'
\qecho '### FUNCOES #############################################################################'
\qecho '#########################################################################################'
\df
\echo Lista de Operadores
\qecho '#########################################################################################'
\qecho '### OPERADORES ##########################################################################'
\qecho '#########################################################################################'
\do
\echo Lista de Tipos de Dados
\qecho '#########################################################################################'
\qecho '### TIPOS DE DADOS ######################################################################'
\qecho '#########################################################################################'
\dT
\echo Rodape
SELECT 'Final: ' || current_timestamp as Final; \q

Agora você pode executá-lo e incrementá-lo para extrair e formatar toda informação que você desejar. Pode ainda criar novos e melhores scripts! Explore opções para layouts mais agradáveis, consultas mais específicas e o que mais a sua necessidade exigir e a sua criatividade for capaz de propor!

Como melhorar este relatório? Te convido a compartilhar com a comunidade nos comentários deste post. Sua contribuição é sempre bem vinda!

FOTOS PGDay/RS 2011

 

INCRIÇÕES ONLINE ENCERRADAS!

 

leia mais

Exemplos de bancos de dados são úteis para o ensino e a aprendizagem sobre as ferramentas e sobre situações práticas que ocorrem no cotidiano dos profissionais da área. Já falei previamente sobre o pagila, mas é sempre bom ter mais opções.

O Booktown é um script que cria o banco de dados de uma livraria completa, incluindo alguns registros e possibilitando um bom banco de dados para testes e ensino de banco de dados. Originalmente, a base booktown foi utilizada nos exemplos do livro Practical PostgreSQL.

Baixe o script aqui.

Vi esse script no site do David Goodwin e achei interessante. Mas ao executá-lo posteriormente tive outra boa surpresa: ele executa "de primeira", sem a necessidade de se fazer qualquer correção. A idéia é através de um programa em python chamar os utilitários de backup do postgres para realizar o backup, e o código deste script pode ser alterado para automatizar outras tarefas de modo bastante simples.

As etapas de utilização são simples:

- Instale o python e o postgres. Esta etapa eu não precisei fazer, porque já o tinha instalado aqui. Teste a versão digitando: "python --version". A versão da minha máquina é a 2.6.5 e do postgres é 9.0.
- Crie um arquivo de script com o código. Use a extensão ".py" como padrão para não misturar seus códigos python os de com outras aplicações.
- Edite os campos "usuário", "senha", "caminho do pg_dump", a "lista de bancos que devem sofrer backup" e salve o arquivo. Destaquei abaixo estes parâmetros em vermelho
- Execute o script. Utilizei "python -v py_backup.py". A opção -v significa VERBOSE, isto é, gera uma descrição de tudo o que está sendo feito durante o backup. Existem outras boas opções do pg_dump e do python, mas isso fica como pesquisa paras os interessados.

Abaixo coloco o código do script, mas acesse também o site original:


#!/usr/bin/python


Tente utilizar o python para chamar outros utilitários do postgres. Se quiser fazer um backup de todas as bases de dados, pode utilizar por exemplo o PG_DUMPALL.

Tem sugestões de melhoria para este script? Poste aqui no nosso fórum!

Acabo de ler o volume zero da nova revista de postgreSQL e é uma ótima iniciativa, se depender de mim terá muito futuro. Há uma entrevista com Bruce Momjian, uma apresentação de extensões (a nova forma de instalar e trabalhar com módulos do postgreSQL na versão 9.1, e que é uma das alterações mais bem [...]

leia mais

É apenas um teste, mas pode se concretizar. O primeiro número da revista oficial do PostgreSQL está no ar e você pode baixar gratuitamente. Acesse e confira! Caso a recepção seja boa, a revista ganhará novas edições.

O primeiro número me agradou bastante, explorando vários itens sobre as futuras versões do Postgres, questões de performance e dicas específicas para  sistemas operacionais, o que nem sempre é fácil de se encontrar.

Você pode colaborar adquirindo a versão impressa ou submetendo matérias para os editores. Confira!

 

O Sr. Dickson Guedes resurgiu das cinzas em grande estilo e com um projeto muito bacana. O pgcasts é um screencast, ou seja, áudio e vídeo com uma demonstração de como fazer alguma coisa no PostgreSQL. Ver a tela dele fazendo enquanto vai comentando, é muito mais simples do que ler um artigo enorme num blog como o meu…. assuntos que eu levaria páginas para abordar ele mostra como fazer na prática com 20 a 30 minutos de screencast.

 

Muito bacana, eu recomendo. Até hoje (02/05/11) são 3 episódios e ele vem mantendo a média de um novo screencast por semana:

1. Instalando PostgreSQL 9.0
2. Trabalhando com datas no PostgreSQL
3. Alterando colunas de tipos conflitantes

Divirtam-se.

Conexões são muito importantes e temos de gerenciá-las. Quando temos conexões para vários bancos de dados distintos em múltiplos servidores e ambientes de desenvolvimento, produção e homologação, esta gestão pode se tornar complicada. O Squirrel Client é uma boa ferramenta que permite a gestão das conexões, a visualização e a manipulação de dados do PostgreSQL e da maioria dos bancos de dados livres e proprietários, unificando em um ponto esta função. (eles só não têm um logotipo decente, então ilustrei com esse que encontrei na internet)



A instalação é bem fácil e se desejar baixar e executar direto do arquivo JAR ou do script .SH sem precisar instalar a ferramenta, não há segredos (é o que eu faço no cotidiano). No windows pode ser acionado pelo arquivo .BAT.

Para utilizar o Squirrel Client, basta baixar a ferramenta e o driver do banco de dados que se deseja acessar. A lista de bancos suportados pela ferramenta é muito vasta, e as configurações a serem feitas não são sofisticadas:

Primeiro passo: Configurar driver

No caso do PostgreSQL, o driver pode ser baixado facilmente. Neste exemplo foi utilizado um driver jdbc, copiado para a pasta lib do squirrel client.

Selecione a aba "Drivers" à esquerda da tela. Informe a string de conexão, dê uma descrição para o driver e confirme.




Segundo passo: Configurar conexão

Uma vez que um  driver pode ser utilizado em várias conexões, deve-se configurar a conexão que utiliza o driver.

Acione a aba"Aliases" e clique no ícone "+" para criar uma nova conexão. Informe o nome, o driver que você criou, a string de conexão, usuário e senha, conforme a figura e confirme.

Para conectar, clique no primeiro ícone da aba "Aliases".


Visualizando o banco de dados

A conexão faz com que sejam mostradas duas abas: "Objects" e "SQL".  A aba "Objects" permite a visualização do banco de dados através de uma treeview e de várias abas internas que são dinamicamente preenchidas à medida em que um objeto do banco de dados é selecionado.

A interface é bem detalhada e são apresentadas informações detalhadas a respeito dos metadados que não são comumente mostradas em outras ferramentas similares como permissões de acesso de tabelas e colunas.

Observe que as informações só serão 100% confiáveis se a coleta de estatísticas do banco de dados estiver atualizada.



Trabalhando com o banco de dados

A aba SQL permite a realização de consultas e a criação, salvamento e recuperação de scripts sql. 

Funções como auto correct/ abreviations e bookmarks podem aumentar a produtividade do usuário.

A execução simultânea de várias conexões é muito útil para evitar a abertura de vários visualizadores em ferramentas distintas.

É possível criar um ou mais diagramas utilizando o botão direito do mouse sobre as tabelas e selecionando a opção "add to a graph", um bônus muito interessante, pois mostra os relacionamentos e permite imprimir de várias formas! 



Limitações

Por ser uma ferramenta de acesso a dados, não apresenta recursos de design mais avançados para a opção de elaboração de diagramas, que gera diagramas através do esquema SQL, mas não gera esquema SQL com base no diagrama. No entanto isso não chega a ser um limitador, apenas não é o propósito da ferramenta.

O suporte a Hibernate não foi testado neste post, mas parece ser uma das features mais interessantes para os usuários deste framework.

A única restrição que encontrei foi o fato do sistema não apresentar o plano de execução das consultas que utilizei no teste (não testei outros bancos além do Postgresql). Possivelmente as novas versões supram esta necessidade futuramente.

O squirrel client se mostra adequado para manter e centralizar conexões a múltiplos bancos de dados, visualizar seus metadados e realizar operações no banco que demandem SQL. Veja os screenshots, baixe, instale e teste!