--Por que usar o VACUUM. --Este artigo explica de forma prática por que usar o VACUUM --Criando a tabela temporaria chamada teste DROP TABLE IF EXISTS teste; CREATE TEMP TABLE teste ( id SERIAL PRIMARY KEY, nome VARCHAR(10) ); --Populando ... INSERT INTO teste (nome) VALUES ('TESTE1'); INSERT INTO teste (nome) VALUES ('TESTE2'); INSERT INTO teste (nome) VALUES ('TESTE3'); INSERT INTO teste (nome) VALUES ('TESTE4'); INSERT INTO teste (nome) VALUES ('TESTE5'); INSERT INTO teste (nome) VALUES ('TESTE6'); INSERT INTO teste (nome) VALUES ('TESTE7'); INSERT INTO teste (nome) VALUES ('TESTE8'); INSERT INTO teste (nome) VALUES ('TESTE9'); INSERT INTO teste (nome) VALUES ('TESTE10'); INSERT INTO teste (nome) VALUES ('TESTE11'); INSERT INTO teste (nome) VALUES ('TESTE12'); INSERT INTO teste (nome) VALUES ('TESTE13'); INSERT INTO teste (nome) VALUES ('TESTE14'); INSERT INTO teste (nome) VALUES ('TESTE15'); INSERT INTO teste (nome) VALUES ('TESTE16'); INSERT INTO teste (nome) VALUES ('TESTE17'); INSERT INTO teste (nome) VALUES ('TESTE18'); INSERT INTO teste (nome) VALUES ('TESTE19'); INSERT INTO teste (nome) VALUES ('TESTE20'); INSERT INTO teste (nome) VALUES ('TESTE21'); INSERT INTO teste (nome) VALUES ('TESTE22'); INSERT INTO teste (nome) VALUES ('TESTE23'); INSERT INTO teste (nome) VALUES ('TESTE24'); INSERT INTO teste (nome) VALUES ('TESTE25'); --Fazendo uma simples consulta SELECT * FROM teste; -- 25 tuplas na ordem de inserção --Checando ultimo vacuum na tabela SELECT schemaname AS esquema , relname AS tabela , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum FROM pg_stat_all_tables WHERE relname ILIKE 'teste'; -- null --Checando quantas linhas mortas na tabela SELECT * , n_dead_tup AS linhas_mortas FROM pg_stat_all_tables WHERE n_dead_tup > 0 AND relname ILIKE 'teste' ORDER BY n_dead_tup DESC; -- nada --Implentando o vacuum VACUUM FULL VERBOSE teste; --update 1 UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1; --Fazendo uma simples consulta SELECT * FROM teste; -- 25 tuplas com id 1 por ultimo, pq ? --Checando ultimo vacuum na tabela SELECT schemaname AS esquema , relname AS tabela , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum FROM pg_stat_all_tables WHERE relname ILIKE 'teste'; -- vacuum registrado --Checando quantas linhas mortas na tabela SELECT * , n_dead_tup AS linhas_mortas FROM pg_stat_all_tables WHERE n_dead_tup > 0 AND relname ILIKE 'teste' ORDER BY n_dead_tup DESC; --1 linha morta --update do id 1 a 10 UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1; UPDATE teste SET nome = 'TESTE2 OK' WHERE id =2; UPDATE teste SET nome = 'TESTE3 OK' WHERE id =3; UPDATE teste SET nome = 'TESTE4 OK' WHERE id =4; UPDATE teste SET nome = 'TESTE5 OK' WHERE id =5; UPDATE teste SET nome = 'TESTE6 OK' WHERE id =6; UPDATE teste SET nome = 'TESTE7 OK' WHERE id =7; UPDATE teste SET nome = 'TESTE7 OK' WHERE id =8; UPDATE teste SET nome = 'TESTE7 OK' WHERE id =9; UPDATE teste SET nome = 'TESTE7 OK' WHERE id =10; --delete do id 22 a 25 DELETE FROM teste WHERE id >= 22; --Listando... SELECT * FROM teste; --fora da ordem natural, pq ? --Checando ultimo vacuum na tabela SELECT schemaname AS esquema , relname AS tabela , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum FROM pg_stat_all_tables WHERE relname ILIKE 'teste'; -- vacuum registrado --Checando quantas linhas mortas na tabela SELECT * , n_dead_tup AS linhas_mortas FROM pg_stat_all_tables WHERE n_dead_tup > 0 AND relname ILIKE 'teste' ORDER BY n_dead_tup DESC; --15 linhas mortas --Dando um sumiço nos cadavers :) VACUUM FULL VERBOSE teste; --Apenas listando na ordem dos ids SELECT * FROM teste ORDER BY 1 ASC; --agora com ordenacao pelo id --Conclusão: --Toda operação de update o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá, e dá um insert da(s) linha(s) novamente com as valores alterados, isso diminue o custo do update. --Isso explica o motivo da ordem natural de inserção de linhas não aparecer, caso haja atualizações em numa consulta sem ordenação. --Toda operação de delete o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá. --A execução do VACUUM FULL dá um acesso exclusivo na tabela, e elimina as linhas mortas, dá um sumiço nos cadaveres, atualiza o índice da chave primaria e atualiza as estatisticas. --A execução do VACUUM ANALYZE, não dá acesso exclusivo na tabela, atualiza apenas as estatísticas, usado para traçar a melhor rota ou forma de acesso a tabela, por exemplo na execução de uma consulta, podendo ser INDEXADA(INDEXSCAN) ou SEQUENCIAL(SEQSCAN) --A opção VERBOSE mostra os detalhes da operação. --Recomenda-se caso a tabela tenha mais índices, além da chave primaria dá um REINDEX TABLE teste;
DBA, MySql, Oracle, Postgres, SQL Server, DB2, BI, TI, Business Intelligence, Data Science, Machine Learning, IoT, Arduino
quinta-feira, 11 de novembro de 2010
Por que usar o VACUUM
segunda-feira, 11 de outubro de 2010
Configurando Replicação Nativa do Postgres 9.0 no Slackware 13.0
Uma das grandes vantagens no Postgres 9.0 é a replicação nativa, assíncrona, baseada no log de transação (WAL-LOG), usando Hot Standby
e Stream Replication (Prontidão a Quente e Fluxo de Replicação), neste artigo irei descrever como configurar três servidores com IPs
distintos e com Linux Slackware 13 rodando o Postgres 9.0 replicado, sendo 1 Master como escrita e leitura e 2 Slaves (replicado)
apenas com leitura, mas podendo ser executados consultas.
Se você usa o pgAdmin III atualize para versão 1.12.0 ou superior, pois o acesso ao Postgres 9.0 com este client só é possível a partir
desta versão.
Se não tiver servidores com fartura, ou um cluster para testar, você pode simular as máquinas com IPs distintos na rede, usei o Oracle
Virtual Box versão 3.2.6 com as placas de redes configuradas para modo bridge, não esqueça de adaptar os seus IPs conforme a estrutura
da sua rede.
Iremos instalar o Postgres 9.0, mais exatamente a versão 9.0.5, tomando por base o código fonte, isto é, iremos baixar, desempacotar e
compilar e instalar e depois configurar.
No servidor Master (ip: 10.3.128.1):
Baixar o Postgres 9.0 na pasta logado com usuário root:
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
Mesmo terêtêtê do Master descrito anteriormente...
Até aqui foi instalado o Postgres no modo padrão nas três máquinas, porem ainda faltam alguns detalhes como arquivo padrão de inicialização,
habilitar o Linux ao reiniciar carregando o Postgres automaticamente, para isso iremos executar os comandos abaixo nas três máquinas servidoras:
Instalamos o Postgres em sua forma básica no Linux com a porta padrão 5432, nos três servidores, agora vamos configurar uma pasta compartilhada
NFS (Network File System) na máquina MASTER para que os SLAVES copiem WAL LOG desta pasta, não levei em consideração a segurança, na configuração
do NFS, mas você pode logo que usar essa receita de bolo, fazer uma ajuste fino no NFS, para tanto basta pedir uma ajudinha ao Google,
digitando: segurança nfs
Criando a pasta para replicação na máquina MASTER:
Configurando o NFS:
No servidor Master (ip: 10.3.128.1)
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
Pronto até aqui configuramos o NFS para que os SLAVES copiem WAL-LOG do MASTER; agora iremos realmente fazer a replicação.
No servidor Master (ip: 10.3.128.1): (por segurança vamos fazer uma cópia de postgresql.conf e pg_hba.conf)
Alguns comentários sobre o terêtêtê acima:
No postgresql.conf:
Os parâmetros: listem_address = '*' , port = 5432 habilitam qualquer maquina externa acessar com a porta mencionada.
O parâmetro: wal_level = hot_standby habilita a servidor para replicação, Master.
O parâmetro: max_wal_senders = 2 habilita dois servidores Slaves a conectarem ao Master.
O parâmetro: archive_command = 'cp %p /home/postgres/replication/%f' copia os arquivos WAL-LOG, para um diretório distinto.
O parâmetro: #wal_sender_delay = 200ms define o interervalo entre as operações de envio de fragamentos (stream)
pelo processo WALSENDER. Padrão: 200 ms.
O parâmetro: wal_keep_segments = 20 define número de arquivos de log que devem ser mantidos para recuperação em caso de problemas
com restauração automática
No arquivo pg_hba.conf:
O parâmetro: host replication all 0.0.0.0/0 trust habilita qualquer máquina a acessar o master para replicação,
isso pode ser restringido para apenas os ips dos Slaves.
Se tudo ocorreu bem, o servidor Master (ip: 10.3.128.1) com banco test está em condições de acesso, use o seguinte comando:
Outra forma de monitorar, o que está acontecendo com servidor Master é vendo os logs de Postgres, conforme exemplo abaixo:
No servidor Master (ip: 10.3.128.1)
Iniciando o backup do Master para os para os Slaves...
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
Copiando o diretório data do servidor Master para os Slaves e removendo o pg_xlog (WAL-LOG) e o arquivo postmaster.pid dos Slaves
Como copiamos o diretório data do Master para os Slaves, vamos pegar o backup postgresql.conf.backup e pg_hba.conf.backup
feito anteriormente no Master e aproveitar nos Slaves:
Ainda nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3) com instancias paradas do Postgres:
O parâmetro: trigger_file = '/tmp/trigger.pgsql.5432' é apenas um arquivo vazio que diz que é um servidor standby, isto é,
slave (read-only), daí nome trigger_file (arquivo_gatilho).
No servidor Master (ip: 10.3.128.1)
Concluindo o backup do Master para os Slaves...
Neste momento o Master estará em modo read-write (escrita e leitura) e os Slaves sicronizados podendo fazer consultas;
estarão em modo read-only (somente-leitura).
Monitoramento da Replicação:
Via Query: (Com o pgAdmin III)
Via Shell: (pg_controldata)
O aplicativo pg_controldata exibe varias informações de controle do WAL, a ser executado no shell,
para comparação entre o Master e os Slaves.
Via Shell: (ps –efH | grep postgres)
No servidor de produção (master) é criado um processo WALSENDER para enviar os fragmentos do streaming replication
No servidor de standby (slave) é criado um processo WALRECEIVER para receber os fragmentos do streaming replication
Via Shell: (tail –f /usr/local/pgsql/data/serverlog)
No servidores é possível identificar muitos problemas ocasionados durante a replicação apenas visualizando o serverlog do Postgres.
Algumas Considerações Importantes:
Consultas em servidores Slaves podem causar conflitos com operações de restauração que ocorrem em paralelo com o Master
Exemplo:
Uma operação de VACCUM FULL ao ser replicada pode remover um registro "morto" que esteja sendo utilizada por uma operação read-only.
Ao remover o registro a consulta poder trazer resultados incorretos.
Há duas maneiras de resolver esse problema:
Forma 1:
Parar a replicação até que a consulta finalize. (configurar no postgresql.conf do Master)
Aguardam finalização das transações que causam conflito no servidores Slaves.
Ideal para ambientes onde as consultas são mais importantes que a sincronização dos servidores.
Forma 2:
Matar a(s) consulta(s) no(s) servidor(es) Slaves. (configurar no postgresql.conf do Master)
Mata as consultas que estão causando conflito imediatamente.
Ideal para ambientes onde a sincronização é mais importante do que as consultas no servidores Slaves.
O Stream Replication isoladamente não disponibiliza servidor para consulta, apenas sincroniza os servidores
com os fragmentos (stream) dos logs de transação (WAL-LOG).
Algumas Dicas:
Recomendo para não consumir o link de banda, isto é a replicação não competir com o uso tradicional de servidor Master,
utilizar uma placa de rede adicional 10 Gigabits Ethernet, ou superior.
Automatizar o processo de failover usando Hearbeat.
Utilizar ferramentas para balanceamento de carga de consultas.
E concluindo o PostgreSQL 9.0 não contempla a Replicação Síncrona e a Replicação em Cascata,
mas há boas pespectivas futuras de que o PostgreSQL 9.1 venha a ser lançado com esses recursos,
além de um sistema de backup baseado no Streaming Replication.
Referencia:
http://www.postgresql.org
http://www.postgresql.org.br
http://planeta.postgresql.org.br/
http://blog.softa.com.br/
http://www.dextra.com.br
http://emersonhermann.blogspot.com
e Stream Replication (Prontidão a Quente e Fluxo de Replicação), neste artigo irei descrever como configurar três servidores com IPs
distintos e com Linux Slackware 13 rodando o Postgres 9.0 replicado, sendo 1 Master como escrita e leitura e 2 Slaves (replicado)
apenas com leitura, mas podendo ser executados consultas.
Se você usa o pgAdmin III atualize para versão 1.12.0 ou superior, pois o acesso ao Postgres 9.0 com este client só é possível a partir
desta versão.
Se não tiver servidores com fartura, ou um cluster para testar, você pode simular as máquinas com IPs distintos na rede, usei o Oracle
Virtual Box versão 3.2.6 com as placas de redes configuradas para modo bridge, não esqueça de adaptar os seus IPs conforme a estrutura
da sua rede.
Iremos instalar o Postgres 9.0, mais exatamente a versão 9.0.5, tomando por base o código fonte, isto é, iremos baixar, desempacotar e
compilar e instalar e depois configurar.
No servidor Master (ip: 10.3.128.1):
Baixar o Postgres 9.0 na pasta logado com usuário root:
#/usr/local #wget http://ftp.postgresql.org/pub/source/v9.0.5/postgresql-9.0.5.tar.gz Em seguida ainda logado com o usuário root: #cd /usr/local #cp /root/postgresql-9.0.5.tar.gz . #tar -zxvf postgresql-9.0.5.tar.gz #cd postgresql-9.0.5 #./configure #gmake #su #gmake install #adduser postgres #mkdir /usr/local/pgsql/data #chown postgres /usr/local/pgsql/data $su – postgres $/usr/local/pgsql/bin/initdb –D /usr/local/pgsql/data $/usr/local/pgsql/bin/postgres –D /usr/local/pgsql/data >logfile 2>&1 & $/usr/local/pgsql/bin/createdb test $/usr/local/pgsql/bin/psql test $exit
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
Mesmo terêtêtê do Master descrito anteriormente...
Até aqui foi instalado o Postgres no modo padrão nas três máquinas, porem ainda faltam alguns detalhes como arquivo padrão de inicialização,
habilitar o Linux ao reiniciar carregando o Postgres automaticamente, para isso iremos executar os comandos abaixo nas três máquinas servidoras:
#cp /usr/local/postgresql-9.0.5/contrib/start-scripts/linux /etc/rc.d/rc.pgsqld #chmod 755 /etc/rc.d/rc.pgsqld #./etc/rc.d/rc.pgsqld stop #./etc/rc.d/rc.pgsqld start #echo " # Start the PostgreSQL database: if [ -x /etc/rc.d/rc.pgsqld ]; then . /etc/rc.d/rc.pgsqld start fi " >> rc.M #reboot
Instalamos o Postgres em sua forma básica no Linux com a porta padrão 5432, nos três servidores, agora vamos configurar uma pasta compartilhada
NFS (Network File System) na máquina MASTER para que os SLAVES copiem WAL LOG desta pasta, não levei em consideração a segurança, na configuração
do NFS, mas você pode logo que usar essa receita de bolo, fazer uma ajuste fino no NFS, para tanto basta pedir uma ajudinha ao Google,
digitando: segurança nfs
Criando a pasta para replicação na máquina MASTER:
#mkdir /home/postgres/replication #chown postgres /home/postgres/replication
Configurando o NFS:
No servidor Master (ip: 10.3.128.1)
#echo " /home/postgres/replication 10.3.128.1(rw,subtree_check,no_root_squash,sync) /home/postgres/replication 10.3.128.2(rw,subtree_check,no_root_squash,sync) /home/postgres/replication 10.3.128.3(rw,subtree_check,no_root_squash,sync) " >> /etc/exports #echo " ALL: ALL@ALL " >> /etc/hosts.deny #echo " ALL : ALL@10.3.128.2 : ALLOW ALL : ALL@10.3.128.3 : ALLOW " >> /etc/hosts.allow #chmod 755 /etc/rc.d/rc.rpc #chmod 755 /etc/rc.d/rc.nfsd #. /etc/rc.d/rc.rpc start #. /etc/rc.d/rc.nfsd start #exportfs -a #rpcinfo –p #showmount –e localhost #mkdir /mnt/postgres #mount –t nfs 10.3.128.1:/home/postgres/replication /mnt/postgres #mount
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
#chmod 755 /etc/rc.d/rc.rpc #chmod 755 /etc/rc.d/rc.nfsd #. /etc/rc.d/rc.rpc start #. /etc/rc.d/rc.nfsd start #rpcinfo –p #showmount –e 10.3.128.1 #mkdir /mnt/postgres #mount –t nfs 10.3.128.1:/home/postgres/replication /mnt/postgres
Pronto até aqui configuramos o NFS para que os SLAVES copiem WAL-LOG do MASTER; agora iremos realmente fazer a replicação.
No servidor Master (ip: 10.3.128.1): (por segurança vamos fazer uma cópia de postgresql.conf e pg_hba.conf)
#cp /usr/local/pgsql/data/postgresql.conf /usr/local/pgsql/data/postgresql.conf.backup #cp /usr/local/pgsql/data/pg_hba.conf /usr/local/pgsql/data/pg_hba.conf.backup #echo " listen_addresses = '*' port = 5432 wal_level = hot_standby archive_mode = on archive_command = 'cp %p /home/postgres/replication/%f' max_wal_senders = 2 #wal_sender_delay = 200ms wal_keep_segments = 20 " >> /usr/local/pgsql/data/postgresql.conf #echo " host replication all 0.0.0.0/0 trust " >> /usr/local/pgsql/data/pg_hba.conf #. /etc/rc.d/rc.pgsqld restart
Alguns comentários sobre o terêtêtê acima:
No postgresql.conf:
Os parâmetros: listem_address = '*' , port = 5432 habilitam qualquer maquina externa acessar com a porta mencionada.
O parâmetro: wal_level = hot_standby habilita a servidor para replicação, Master.
O parâmetro: max_wal_senders = 2 habilita dois servidores Slaves a conectarem ao Master.
O parâmetro: archive_command = 'cp %p /home/postgres/replication/%f' copia os arquivos WAL-LOG, para um diretório distinto.
O parâmetro: #wal_sender_delay = 200ms define o interervalo entre as operações de envio de fragamentos (stream)
pelo processo WALSENDER. Padrão: 200 ms.
O parâmetro: wal_keep_segments = 20 define número de arquivos de log que devem ser mantidos para recuperação em caso de problemas
com restauração automática
No arquivo pg_hba.conf:
O parâmetro: host replication all 0.0.0.0/0 trust habilita qualquer máquina a acessar o master para replicação,
isso pode ser restringido para apenas os ips dos Slaves.
Se tudo ocorreu bem, o servidor Master (ip: 10.3.128.1) com banco test está em condições de acesso, use o seguinte comando:
#/usr/local/bin/psql –d test
Outra forma de monitorar, o que está acontecendo com servidor Master é vendo os logs de Postgres, conforme exemplo abaixo:
#tail –f /usr/local/pgsql/data/serverlog
No servidor Master (ip: 10.3.128.1)
Iniciando o backup do Master para os para os Slaves...
usr/local/data/pgsql/bin/psql –d postgres -c "SELECT pg_start_backup('ok');"
Nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3):
Copiando o diretório data do servidor Master para os Slaves e removendo o pg_xlog (WAL-LOG) e o arquivo postmaster.pid dos Slaves
#scp 10.3.128.1:/usr/local/pgsql/data/* /usr/local/pgsql/data #rm -f /usr/local/pgsql/data/pg_xlog/* #rm /usr/local/pgsql/data/postmaster.pid
Como copiamos o diretório data do Master para os Slaves, vamos pegar o backup postgresql.conf.backup e pg_hba.conf.backup
feito anteriormente no Master e aproveitar nos Slaves:
Ainda nos servidores Slaves (ip: 10.3.128.2 e ip:10.3.128.3) com instancias paradas do Postgres:
#cp /usr/local/pgsql/data/postgresql.conf.backup /usr/local/pgsql/data/postgresql.conf #cp /usr/local/pgsql/data/pg_hba.conf.backup /usr/local/pgsql/data/pg_hba.conf #echo " listen_addresses = '*' port = 5432 wal_level = minimal archive_mode = off hot_standby = on " >> /usr/local/pgsql/data/postgresql.conf #echo " standby_mode = 'on' primary_conninfo = 'host=10.3.128.1 port=5432 user=postgres password=postgres' restore_command = 'cp /mnt/postgres/%f %p' trigger_file = '/tmp/trigger.pgsql.5432' " > /usr/local/pgsql/data/recovery.conf #. /etc/rc.d/rc.pgsqld restart
O parâmetro: trigger_file = '/tmp/trigger.pgsql.5432' é apenas um arquivo vazio que diz que é um servidor standby, isto é,
slave (read-only), daí nome trigger_file (arquivo_gatilho).
No servidor Master (ip: 10.3.128.1)
Concluindo o backup do Master para os Slaves...
usr/local/data/pgsql/bin/psql -d postgres -c "SELECT pg_stop_backup();"
Neste momento o Master estará em modo read-write (escrita e leitura) e os Slaves sicronizados podendo fazer consultas;
estarão em modo read-only (somente-leitura).
Monitoramento da Replicação:
Via Query: (Com o pgAdmin III)
--Informações sobre o servidor standby (slave) --ultimo fragmento recebido do servidor de producao SELECT * FROM pg_last_xlog_receive_location() ; --ultimo fragmento aplicado durante a recuperacao --valores idênticos para as duas funções indicam que não há informações pendentes a serem aplicadas SELECT * FROM pg_last_xlog_replay_location() ; --indica se o servidor Postgres está em restauração SELECT * FROM pg_is_in_recovery() ;
Via Shell: (pg_controldata)
O aplicativo pg_controldata exibe varias informações de controle do WAL, a ser executado no shell,
para comparação entre o Master e os Slaves.
#./pg_controldata
Via Shell: (ps –efH | grep postgres)
ps –efH | grep postgres
No servidor de produção (master) é criado um processo WALSENDER para enviar os fragmentos do streaming replication
No servidor de standby (slave) é criado um processo WALRECEIVER para receber os fragmentos do streaming replication
Via Shell: (tail –f /usr/local/pgsql/data/serverlog)
No servidores é possível identificar muitos problemas ocasionados durante a replicação apenas visualizando o serverlog do Postgres.
#tail –f /usr/local/pgsql/data/serverlog
Algumas Considerações Importantes:
Consultas em servidores Slaves podem causar conflitos com operações de restauração que ocorrem em paralelo com o Master
Exemplo:
Uma operação de VACCUM FULL ao ser replicada pode remover um registro "morto" que esteja sendo utilizada por uma operação read-only.
Ao remover o registro a consulta poder trazer resultados incorretos.
Há duas maneiras de resolver esse problema:
Forma 1:
Parar a replicação até que a consulta finalize. (configurar no postgresql.conf do Master)
max_standby_delay = -1
Aguardam finalização das transações que causam conflito no servidores Slaves.
Ideal para ambientes onde as consultas são mais importantes que a sincronização dos servidores.
Forma 2:
Matar a(s) consulta(s) no(s) servidor(es) Slaves. (configurar no postgresql.conf do Master)
max_standby_delay = 0
Mata as consultas que estão causando conflito imediatamente.
Ideal para ambientes onde a sincronização é mais importante do que as consultas no servidores Slaves.
O Stream Replication isoladamente não disponibiliza servidor para consulta, apenas sincroniza os servidores
com os fragmentos (stream) dos logs de transação (WAL-LOG).
Algumas Dicas:
Recomendo para não consumir o link de banda, isto é a replicação não competir com o uso tradicional de servidor Master,
utilizar uma placa de rede adicional 10 Gigabits Ethernet, ou superior.
Automatizar o processo de failover usando Hearbeat.
Utilizar ferramentas para balanceamento de carga de consultas.
E concluindo o PostgreSQL 9.0 não contempla a Replicação Síncrona e a Replicação em Cascata,
mas há boas pespectivas futuras de que o PostgreSQL 9.1 venha a ser lançado com esses recursos,
além de um sistema de backup baseado no Streaming Replication.
Referencia:
http://www.postgresql.org
http://www.postgresql.org.br
http://planeta.postgresql.org.br/
http://blog.softa.com.br/
http://www.dextra.com.br
http://emersonhermann.blogspot.com
segunda-feira, 4 de outubro de 2010
Mais views para administração do PostgreSQL
Mais views para administração do PostgreSQL
--atualizada vw_find_seq -- view para procuar as sequencias DROP VIEW IF EXISTS vw_find_seq; CREATE OR REPLACE VIEW vw_find_seq AS ( SELECT c.oid AS oid_seq, n.nspname AS esquema, c.relname AS seq, pg_catalog.pg_get_userbyid(c.relowner) AS dono, pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer AS registros FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'S' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY n.nspname, c.relname ); COMMENT ON VIEW vw_find_seq IS ' Autor: O Peregrino View que procura por esquemas, sequencia, comentários, donos no banco --exemplos: --busca simples (tudo) SELECT * FROM vw_find_seq ; --busca por esquema SELECT * FROM vw_find_seq WHERE esquema ILIKE \\\'esquema\\\' ; --busca por tabelas SELECT * FROM vw_find_seq WHERE seq ILIKE \\\'%seq%\\\' ; ' ; --incrementada --View que retorna uma query com o objetivo de descobrir qual o maior id de todas as sequenciasa de um banco de dados. DROP VIEW IF EXISTS vw_max_id_seq CASCADE; CREATE OR REPLACE VIEW vw_max_id_seq AS ( ( SELECT 'SELECT ' || '\''||x.esquema||'.'||x.seq||'\'' || ' AS esquema_seq' || ',' || ' (SELECT last_value FROM ' || x.esquema || '.' || x.seq || ')::BIGINT AS maior_id_seq UNION ' AS maior_ids_seq FROM ( SELECT * FROM vw_find_seq WHERE esquema NOT IN ('z_deprecated') AND seq NOT ILIKE '@%' ORDER BY esquema ASC , seq ASC ) AS x ) UNION ( SELECT 'SELECT \'ZZZ-FIM-USADO-PARA-CONTROLE\' AS esquema_seq, 0::BIGINT AS maior_id_seq ORDER BY 2 DESC;' AS maior_ids_seq ) ORDER BY 1 ASC ); COMMENT ON VIEW vw_max_id IS ' Autor: O Peregrino View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados -- O resultado desta view gera um nova query para obter o resultado -- desejado. -- Esta consulta pega o maior id de todas tabelas de um banco. -- É gerado apenas um coluna como resultado da view com query. -- para obter o valor maximo gerado de um id em uma sequencia, de todas as tabelas de um banco. -- Dica para execução: -- No pgadmin selecione CTRL + A, CTRL + C no resultado da query, -- Abrir uma nova query, CTRL + V, CTRL + HOME, -- CTRL + F, substituir " por nada, recomendado, em vez de " ser |, se estiver configurado -- Clicar em (Substituir TODOS) e depois em (Fechar) -- Executar query (f5) -- Será retornada 2 colunas: esquema_seq e o maior_id baseado na sequencia -- Essa view vw_max_id_seq usa a view vw_find_seq --SELECT * FROM vw_max_id_seq; ' ;
quinta-feira, 16 de setembro de 2010
Efetuando exclusão segura de registro de uma tabela com store function
Efetuando exclusão segura de registro de uma tabela com store function
-- -- Nome Artefato/Programa..: exclusao.sql -- Instituicao.............: -- Autor(es)...............: O Peregrino (emersonhermann at gmail.com) -- Data Inicio ............: 29/06/2010 -- Data Atual..............: 29/06/2010 -- Versao..................: 0.01 -- Linguagem...............: PL/pgSQL -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: Efetuar exclusao segura de um registro -- OBS1....................: Efetuar exclusao segura de um registro DROP FUNCTION IF EXISTS public.exclusao(INTEGER); CREATE OR REPLACE FUNCTION public.exclusao(INTEGER) RETURNS BOOLEAN AS $$ BEGIN DELETE FROM tabela WHERE campo_chave=$1; IF FOUND THEN RAISE NOTICE 'O registro % foi excluido.',$1; RETURN TRUE; END IF; RAISE NOTICE 'O registro % não foi encontrado.',$1; RETURN FALSE; END; $$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT ; --polimorfismo DROP FUNCTION IF EXISTS public.exclusao(esquema_tabelax VARCHAR(255), campox VARCHAR(255), idx INTEGER); CREATE OR REPLACE FUNCTION public.exclusao(esquema_tabelax VARCHAR(255), campox VARCHAR(255), idx INTEGER) RETURNS BOOLEAN AS $$ DECLARE ds_comando TEXT; deletado BIGINT; BEGIN IF (esquema_tabelax IS NULL) THEN RAISE EXCEPTION 'ESQUEMA TABELA COM VALOR NULO!'; -- caso o parametro venha nulo END IF; IF (campox IS NULL) THEN RAISE EXCEPTION 'CAMPO COM VALOR NULO!'; -- caso o parametro venha nulo END IF; IF (idx IS NULL) THEN RAISE EXCEPTION 'ID COM VALOR NULO!'; -- caso o parametro venha nulo END IF; ds_comando := 'DELETE FROM ' || esquema_tabelax || ' WHERE ' || campox || '=' || idx; EXECUTE ds_comando; GET DIAGNOSTICS deletado = ROW_COUNT; IF deletado > 0 THEN RAISE NOTICE '%', ds_comando; RAISE NOTICE 'Total de registro(s) deletado(s): %',deletado; RETURN TRUE; END IF; RAISE NOTICE '%', ds_comando; RAISE NOTICE 'Total de registro(s) deletado(s): %',deletado; RAISE NOTICE 'O registro % não foi encontrado. NADA FOI DELETADO!!.',idx; RETURN FALSE; END; $$ LANGUAGE 'plpgsql' RETURNS NULL ON NULL INPUT ; DROP TABLE IF EXISTS "E PRA SER DELETADO"; CREATE TABLE "E PRA SER DELETADO" ( ID INTEGER ); INSERT INTO "E PRA SER DELETADO" (id) VALUES (6); INSERT INTO "E PRA SER DELETADO" (id) VALUES (6); INSERT INTO "E PRA SER DELETADO" (id) VALUES (6); SELECT * FROM "E PRA SER DELETADO"; DROP TABLE IF EXISTS apague_me; CREATE TABLE apague_me ( ID INTEGER ); INSERT INTO apague_me (id) VALUES (1); INSERT INTO apague_me (id) VALUES (2); INSERT INTO apague_me (id) VALUES (3); INSERT INTO apague_me (id) VALUES (4); INSERT INTO apague_me (id) VALUES (5); INSERT INTO apague_me (id) VALUES (6); INSERT INTO apague_me (id) VALUES (6); INSERT INTO apague_me (id) VALUES (6); INSERT INTO apague_me (id) VALUES (7); --DELETE FROM public.apague_me WHERE id = 6; SELECT * FROM apague_me; --testes --deletar pelos parametros SELECT EXCLUSAO('public.apague_me','ID',6); --ok --delete pelos parametros com null SELECT EXCLUSAO('public.apague_me','ID',NULL); --ok SELECT EXCLUSAO('public.apague_me',NULL,NULL); --ok SELECT EXCLUSAO(NULL,NULL,NULL); --ok --tabela ou campos inexistentes SELECT EXCLUSAO('public.apague_me','idx',6); --ok SELECT EXCLUSAO('public.apague_mex','id',6); --ok --com transacao BEGIN; SELECT EXCLUSAO('public.apague_me','id',6); --ok --ROLLBACK; --COMMIT; --sem parametros SELECT EXCLUSAO('public.apague_me','ID'); --ok --sql injection SELECT EXCLUSAO('drop database banco;','ID',6); --ok --case sensitve SELECT EXCLUSAO('"E PRA SER DELETADO"','ID',6); --ok SELECT EXCLUSAO('public."E PRA SER DELETADO"','ID',6); --ok
Algoritmos, Caixa Eletrônico
Algoritmos, Caixa Eletrônico
-- -- Nome Artefato/Programa..: sp_caixa_eletro.sql -- Instituicao.............: -- Autor(es)...............: O Peregrino (emersonhermann at gmail.com) ou (emerson at info.ufrn.br) -- Data Inicio ............: 29/06/2010 -- Data Atual..............: 29/06/2010 -- Versao..................: 0.01 -- Linguagem...............: PL/pgSQL -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: Caixa Eletronico -- OBS1....................: Caixa Eletronico -- /* Algoritmo Caixa Eletronico notasSaída = [] #guardar as notas que saírão do caixa eletrônico notas = [100, 50, 20, 10, 5, 1] #notas que podem ser sacadas valor = 375 #valor a ser sacado restante = valor #faz uma cópia do valor em "restante" inota = 0 #índice da nota: 0 é 100, 1 é 50, 2 é 20, 3 é 10, ... enquanto restante>0: #enquanto restante for maior que 0 resultado = restante-notas[inota] #calcula o resultado da subtração entre o valor e a nota se resultado<0: #se for negativo: inota++ #incrementa o índice para a próxima nota senão: #se for positivo ou zero: restante = resultado #deixa restante com o novo resultado notasSaída.adicionar(notas[inota]) #adiciona a nota utilizada nas que devem sair do caixa para nota em notasSaída: # escreve as notas que devem sair escreva nota */ --opcao 1 --Retornando notas do caixa eletrônico --Notas de 1, 2, 5, 10, 20, 50 e 100 DROP FUNCTION IF EXISTS sp_caixa_eletro(pvalor INTEGER); CREATE OR REPLACE FUNCTION sp_caixa_eletro (pvalor INTEGER) RETURNS text AS $$ DECLARE sretorno TEXT; qnota1 INTEGER; qnota2 INTEGER; qnota5 INTEGER; qnota10 INTEGER; qnota20 INTEGER; qnota50 INTEGER; qnota100 INTEGER; pvalorx INTEGER; residual INTEGER; restante INTEGER; vet_notas INTEGER ARRAY[7]; i INTEGER; resultado INTEGER; BEGIN vet_notas[1]=100; vet_notas[2]=50; vet_notas[3]=20; vet_notas[4]=10; vet_notas[5]=5; vet_notas[6]=2; vet_notas[7]=1; i := 1; qnota1 := 0; qnota2 := 0; qnota5 := 0; qnota10 := 0; qnota20 := 0; qnota50 := 0; qnota100 := 0; pvalorx := 0; resultado := 0; sretorno := ''; restante := pvalor; WHILE (i <= 7) LOOP resultado = restante - vet_notas[i]; IF (resultado < 0) THEN i := i + 1; ELSE restante := resultado; RAISE NOTICE 'Restante % -> Nota: R$% Qt: %', restante, vet_notas[i], resultado; IF vet_notas[i] = 100 THEN qnota100 := qnota100 + 1; ELSIF vet_notas[i] = 50 THEN qnota50 := qnota50 + 1; ELSIF vet_notas[i] = 20 THEN qnota20 := qnota20 + 1; ELSIF vet_notas[i] = 10 THEN qnota10 := qnota10 + 1; ELSIF vet_notas[i] = 5 THEN qnota5 := qnota5 + 1; ELSIF vet_notas[i] = 2 THEN qnota2 := qnota2 + 1; ELSIF vet_notas[i] = 1 THEN qnota1 := qnota1 + 1; END IF; END IF; END LOOP; sretorno := 'Total: ' || pvalor || ' ' --chr(10) || 'Notas de 100:' || qnota100 || ' ' --chr(10) || 'Notas de 50:' || qnota50 || ' ' --chr(10) || 'Notas de 20:' || qnota20 || ' ' --chr(10) || 'Notas de 10:' || qnota10 || ' ' --chr(10) || 'Notas de 5:' || qnota5 || ' ' --chr(10) || 'Notas de 2:' || qnota2 || ' ' --chr(10) || 'Notas de 1:' || qnota1; RETURN sretorno; -- Retorna as linhas END; $$ LANGUAGE plpgsql; --alguns testes --SELECT sp_caixa_eletro(2678); --SELECT sp_caixa_eletro(1078);
quarta-feira, 8 de setembro de 2010
Views com user functions para administração do PostgreSQL
Views com user functions para administração do PostgreSQL
-- -- Nome Artefato/Programa..: monitor_ddl.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 01/03/2010 -- Data Atual..............: 08/09/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: views para monitoramento das atividades do postgres 8.3 /8.4 -- OBS.....................: -- Script de monitoramento de banco de dados Postgres 8.x DLL -- Autor: O Peregrino -- functions -- -- Nome Artefato/Programa..: sp_tradutor.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 30/08/2010 -- Data Atual..............: 30/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para fazer tradução de english para portugues de algumas palavras usadas no postgres -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_tradutor (TEXT) CASCADE; CREATE OR REPLACE FUNCTION sp_tradutor (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; BEGIN stextonovo := REPLACE(stexto,'OR','OU'); stextonovo := REPLACE(stextonovo, 'AND','E'); stextonovo := REPLACE(stextonovo, 'NOT','NÃO'); stextonovo := REPLACE(stextonovo, 'IS',''); stextonovo := REPLACE(stextonovo, 'NULL','NULO'); stextonovo := REPLACE(stextonovo, 'integer','inteiro'); stextonovo := REPLACE(stextonovo, 'smallint','inteiro_pequeno'); stextonovo := REPLACE(stextonovo, 'bigint','inteiro_grande'); stextonovo := REPLACE(stextonovo, 'numeric','numerico'); stextonovo := REPLACE(stextonovo, 'double precision','dupla_precisao'); stextonovo := REPLACE(stextonovo, 'date','data'); stextonovo := REPLACE(stextonovo, 'timestamp without time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'timestamp with time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'timestamp(0) without time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'time without time zone','hora'); stextonovo := REPLACE(stextonovo, 'text','texto'); stextonovo := REPLACE(stextonovo, 'character varying','v_caracter'); stextonovo := REPLACE(stextonovo, 'character','caracter'); stextonovo := REPLACE(stextonovo, '"char"','caracter'); stextonovo := REPLACE(stextonovo, 'bpchar','caracter'); stextonovo := REPLACE(stextonovo, 'boolean','logico'); stextonovo := REPLACE(stextonovo, 'bytea','objeto_binario_grande'); stextonovo := REPLACE(stextonovo, 'year','ano'); stextonovo := REPLACE(stextonovo, 'month','mes'); stextonovo := REPLACE(stextonovo, 'day','dia'); stextonovo := REPLACE(stextonovo, 'now()','agora()'); RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER; --SELECT sp_tradutor('(year((data_cadastro)::timestamp with time zone) >= year(now()))') AS test1; -- -- Nome Artefato/Programa..: sp_rm_special_chars.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 09/10/2008 -- Data Atual..............: 17/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_rm_special_chars (TEXT); CREATE OR REPLACE FUNCTION sp_rm_special_chars (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; BEGIN -- Tabela ASCII -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32 -- hex d abr ctl descricao -- 00 0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata stextonovo := TRANSLATE(stexto, ' ',''); -- 01 1 SOH ^A Start of Header - Início do cabeçalho stextonovo := TRANSLATE(stextonovo, ' ',''); -- 02 2 STX ^B Start of Text - Início do texto stextonovo := TRANSLATE(stextonovo, ' ',''); -- 03 3 ETX ^C End of Text - Fim do texto stextonovo := TRANSLATE(stextonovo, ' ',''); -- 04 4 EOT ^D End of Tape - Fim de fita stextonovo := TRANSLATE(stextonovo, ' ',''); -- 05 5 ENQ ^E Enquire - Interroga identidade do terminal stextonovo := TRANSLATE(stextonovo, ' ',''); -- 06 6 ACK ^F Acknowledge - Reconhecimento stextonovo := TRANSLATE(stextonovo, ' ',''); -- 07 7 BEL ^G Bell - Campainha stextonovo := TRANSLATE(stextonovo, '\8',''); -- 08 8 BS ^H Back-space - Espaço atrás stextonovo := TRANSLATE(stextonovo, '\9',''); -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0A 10 LF ^J Line-Feed - Alimenta linha stextonovo := TRANSLATE(stextonovo, '\11',''); -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical stextonovo := TRANSLATE(stextonovo, '\12',''); -- 0C 12 FF ^L Form-Feed - Alimenta formulário stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0D 13 CR ^M Carriage-Return - Retorno do carro (enter) stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0E 14 SO ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.) stextonovo := TRANSLATE(stextonovo, '\15',''); -- 0F 15 SI ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.) stextonovo := TRANSLATE(stextonovo, ' ',''); -- 10 16 DLE ^P Data-Link Escape stextonovo := TRANSLATE(stextonovo, ' ',''); -- 11 17 DC1 ^Q Device-Control 1 stextonovo := TRANSLATE(stextonovo, ' 8',''); -- 12 18 DC2 ^R Device-Control 2 stextonovo := TRANSLATE(stextonovo, ' 9',''); -- 13 19 DC3 ^S Device-Control 3 stextonovo := TRANSLATE(stextonovo, ' ',''); -- 14 20 DC4 ^T Device-Control 4 stextonovo := TRANSLATE(stextonovo, ' ',''); -- 15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento stextonovo := TRANSLATE(stextonovo, ' ',''); -- 16 22 SYN ^V Synchronous Idle stextonovo := TRANSLATE(stextonovo, ' ',''); -- 17 23 vETB^W End-of-Transmission Block stextonovo := TRANSLATE(stextonovo, ' ',''); -- 18 24 CAN ^X Cancel stextonovo := TRANSLATE(stextonovo, ' ',''); -- 19 25 EM ^Y End-Of-Medium stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1A 26 SUB ^Z Substitute stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1B 27 ESC ^[ Escape stextonovo := TRANSLATE(stextonovo, ' 8',''); -- 1C 28 FS ^\ File Separator stextonovo := TRANSLATE(stextonovo, ' 9',''); -- 1D 29 GS ^] Group Separator stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1E 30 RS ^^ Record Separator stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1F 31 US ^_ Unit Separator stextonovo := TRANSLATE(stextonovo, '\127',''); -- 7F127 DEL ^? Delete RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --SELECT 'SEM STRESS... W TECLA ENTER TECLA ESC TESTE OK ' AS test1 -- -- Nome Artefato/Programa..: sp_comment_domain.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 31/08/2010 -- Data Atual..............: 31/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para fazer tratamento dos comentarios de dominio -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_comment_domain (TEXT); CREATE OR REPLACE FUNCTION sp_comment_domain (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; stexto_transitorio TEXT; limite INTEGER; BEGIN SELECT INTO limite array_upper(string_to_array(stexto, '§§'),1); IF limite > 1 THEN stextonovo := ''; FOR i IN 2 .. limite LOOP SELECT INTO stexto_transitorio a.ar[i] FROM (SELECT string_to_array(stexto, '§§') AS ar ) AS a ; stextonovo := stextonovo || stexto_transitorio || CHR(9); --9 tabulacao horizontal ou 10 proxima linha ou 11 tabulacao vertical END LOOP; END IF; RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER; /* SELECT sp_comment_domain ('Status do bem referente a sua situação negocial. Representado pela classe de constantes: br.ufrn.sipac.patrimonio.tombamento.dominio.TipoStatus, podendo ter tais características: §§ EFETIVADO = 1 (Quando o bem acaba de ser tombado, sua situação fica normal para realizar operações.), §§ DEVOLVIDO = 2 (Para bens de terceiros, quando devolvido a entidade terceira que o cedeu), §§PENDENTE = 3. (Bem terceiro não finalizado e que pertence à entidade que o adquiriu.), §§ ALIENADO = 4 (Bem que sofreu processo de alienação), §§ ACAUTELADO = 5 (Bem que sofreu processo de acautelamento), §§ EM_PROCESSO_ALIENACAO = 6 (Bem que está em processo de alienação), §§ EM_MOVIMENTACAO = 7 (Bem que está sob movimentação temporária).') AS test1 */ --views deprecated /* DROP VIEW IF EXISTS view_close_db; DROP VIEW IF EXISTS view_con; DROP VIEW IF EXISTS view_disk_mem; DROP VIEW IF EXISTS view_monitor; DROP VIEW IF EXISTS view_monitor2; DROP VIEW IF EXISTS view_size_db; DROP VIEW IF EXISTS view_status_locks; DROP VIEW IF EXISTS view_status_locks_tables; DROP VIEW IF EXISTS view_time_server; --view renomeada para vw_db_size; DROP VIEW IF EXISTS vw_size_db; --views criadas com dono errado DROP VIEW IF EXISTS vw_close_db; DROP VIEW IF EXISTS vw_con; DROP VIEW IF EXISTS vw_disk_mem; DROP VIEW IF EXISTS vw_find; DROP VIEW IF EXISTS vw_monitor; DROP VIEW IF EXISTS vw_size_db; DROP VIEW IF EXISTS vw_status_locks; DROP VIEW IF EXISTS vw_status_locks_tables; DROP VIEW IF EXISTS vw_time_server; -- */ -- view lista status das tabelas relativo a locks DROP VIEW IF EXISTS vw_status_locks; CREATE OR REPLACE VIEW vw_status_locks AS ( -- locks em andamento SELECT d.client_addr AS "maquina", c.datname AS "banco", e.schemaname AS "esquema", b.relname AS "tabela", a.mode, CASE WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela' WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela' WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO' WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela' WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela' END AS "modo" FROM pg_locks a INNER JOIN pg_class b ON b.oid = a.relation AND b.relkind = 'r' INNER JOIN pg_tables e ON b.relname = e.tablename INNER JOIN pg_database c ON c.oid = a.database INNER JOIN pg_stat_activity d ON d.procpid = a.pid WHERE relname NOT LIKE 'pg_%' --AND a.mode = 'ExclusiveLock' -- Travamento da tabela --AND a.mode = 'RowExclusiveLock' -- Travamento do registro da tabela ORDER BY modo DESC, --a.mode ASC, d.client_addr ASC, b.relname ASC ) ; COMMENT ON VIEW vw_status_locks IS ' Autor: O Peregrino View que verifica situacao de locks em andamento no banco' ; -- view que mostra locks em andamento de registros e tabelas DROP VIEW IF EXISTS vw_status_locks_tables; CREATE OR REPLACE VIEW vw_status_locks_tables AS ( SELECT d.client_addr AS "maquina", c.datname AS "banco", e.schemaname AS "esquema", b.relname AS "tabela", a.mode, CASE WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela' WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela' WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO' WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela' WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela' END AS "modo" FROM pg_locks a INNER JOIN pg_class b ON b.oid = a.relation AND b.relkind = 'r' INNER JOIN pg_tables e ON b.relname = e.tablename INNER JOIN pg_database c ON c.oid = a.database INNER JOIN pg_stat_activity d ON d.procpid = a.pid WHERE relname NOT LIKE 'pg_%' AND a.mode = 'ExclusiveLock' -- Travamento da tabela AND a.mode = 'RowExclusiveLock' -- Travamento do registro da tabela ORDER BY modo DESC, --a.mode ASC, d.client_addr ASC, b.relname ASC ) ; COMMENT ON VIEW vw_status_locks_tables IS ' Autor: O Peregrino View que verifica situacao apenas de locks de registros e tabelas no banco' ; --view para mostrar o uso de memoria disco do postgres DROP VIEW IF EXISTS vw_disk_mem; CREATE OR REPLACE VIEW vw_disk_mem AS ( SELECT (SUM(pg_stat_database.blks_hit) / SUM(pg_stat_database.blks_read + pg_stat_database.blks_hit) * 100::NUMERIC)::INTEGER AS "% de Utilização de Mem" FROM pg_stat_database ) ; COMMENT ON VIEW vw_disk_mem IS ' Autores: O Peregrino (apenas a view) Kenia Milene |http://keniamilene.wordpress.com| (query) View que calcula através das estatisticas o percentual de utilização disco / memória. Valores acima de 70% significa que o banco esta realizado mais tarefas em memória do que i/o em disco'; -- view para listar conexoes ativas e transações efetuadas (commit-rollback) DROP VIEW IF EXISTS vw_con; CREATE OR REPLACE VIEW vw_con AS ( SELECT DISTINCT datname, pg_stat_get_db_xact_commit(datid) AS "commits", pg_stat_get_db_xact_rollback(datid) AS "rollbacks", pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid) AS "transacoes_efetuadas", CASE WHEN pg_stat_get_db_xact_commit(datid) = 0 THEN -- evita divisao por zero 0 ELSE (pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid))/(pg_stat_get_db_xact_commit(datid)/100) END AS "%", pg_stat_get_db_numbackends(datid) AS conexoes_ativas FROM pg_stat_activity ORDER BY 5 DESC ) ; COMMENT ON VIEW vw_con IS ' Autor: O Peregrino View que mostra conexoes ativas e transações efetuadas (commit-rollback)' ; -- view para monitoramento de atividade do servidor postgres por usuario DROP VIEW IF EXISTS vw_monitor; CREATE OR REPLACE VIEW vw_monitor AS ( SELECT procpid, datname AS banco, usename AS usuario_banco, client_addr AS ip, CASE WHEN waiting IS TRUE THEN 'Aguardando' ELSE 'Executando' END AS status, AGE(now(),query_start) AS tempo_execucao, current_query FROM pg_stat_activity WHERE 1=1 AND current_query <> '' -- DESOCUPADO ORDER BY tempo_execucao DESC --current_query DESC --client_addr ASC, --procpid ASC ) ; COMMENT ON VIEW vw_monitor IS ' Autor: O Peregrino View que verifica o que está executando no banco no instante atual incluido transacoes, listado por usuario e ip e por tempo Adequado para super-usuario --SELECT * FROM vw_monitor; OBS: Para cancelar processo executar os comandos abaixos -- cancelar query >= 8.0 --SELECT pg_cancel_backend(21471); -- cancelar processo >= 8.4 --SELECT pg_terminate_backend(21471); ' ; /* -- DESATIVADA POIS NECESSITA DE TABELAS CRIADA PELO DBA E REDE COM IP FIXOS -- (DBA) - monitoramento de atividade do servidor postgres por usuario DROP VIEW IF EXISTS vw_monitor2; CREATE OR REPLACE VIEW vw_monitor2 AS ( SELECT a.procpid, a.datname AS banco, a.usename AS usuario_banco, a.client_addr AS ip, b.nome, b.talk, b.sistema, CASE WHEN a.waiting IS TRUE THEN 'Aguardando' ELSE 'Executando' END AS status, AGE(now(),a.query_start) AS tempo_execucao, a.current_query FROM pg_stat_activity a LEFT JOIN system.users b ON a.client_addr = TRIM(b.ip)::inet WHERE 1=1 --AND a.usename = 'sipac' --AND a.datname LIKE 'administrativo%' AND a.current_query <> ' ' -- DESOCUPADO --AND a.usename = 'postgres' --AND a.client_addr = '10.3.128.136' -- Emerson --AND a.client_addr = '10.3.128.70' -- Itamir ORDER BY --a.current_query DESC, tempo_execucao DESC, b.nome ASC --a.client_addr ASC, --a.procpid ASC ) ; COMMENT ON VIEW vw_monitor2 IS ' Verifica o que está executando no banco no instante atual, incluindo transacoes, listado por usuario e ip e por tempo usando tabela system.users Adequado para super-usuario --SELECT * FROM vw_monitor; OBS: Para cancelar processo executar os comandos abaixos -- cancelar query >= 8.0 --SELECT pg_cancel_backend(21471); -- cancelar processo >= 8.4 --SELECT pg_terminate_backend(21471); ' ; -- estrutura de tabela a ser criada pelo DBA para funcionamento da view vw_monitor2 DROP SCHEMA IF EXISTS "system"; CREATE SCHEMA "system"; DROP TABLE IF EXISTS "system".users; CREATE TABLE "system".users ( id SERIAL NOT NULL, nome CHARACTER VARYING(50) NOT NULL, talk CHARACTER VARYING(200), email CHARACTER VARYING(200), ramal CHARACTER VARYING(20), celular CHARACTER VARYING(350), residencial CHARACTER VARYING(350), computador CHARACTER VARYING(200), ip CHARACTER VARYING(200) NOT NULL, sistema CHARACTER VARYING(20), obs TEXT, CONSTRAINT users_pkey PRIMARY KEY (id) ); INSERT INTO "system".users (id, nome, talk, email, ramal, celular, residencial, computador, ip, sistema, obs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); SELECT * FROM "system".users; */ -- view que mostra o tempo de execucao do servidor corrente DROP VIEW IF EXISTS vw_time_server; CREATE OR REPLACE VIEW vw_time_server AS ( -- quando o servidor do postgres foi iniciado, tempo? SELECT TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in, AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age ) ; COMMENT ON VIEW vw_time_server IS ' Autor: O Peregrino View que mostra quanto o servidor do postgres foi iniciado, tempo de execucao' ; -- view que mostra tamanho dos bancos DROP VIEW IF EXISTS vw_db_size; CREATE OR REPLACE VIEW vw_db_size AS ( --tamanho dos bancos em MB SELECT datname AS banco, pg_database_size(datname)/1000000 || ' MB' AS tamanho_bd FROM pg_database ORDER BY tamanho_bd DESC ) ; COMMENT ON VIEW vw_db_size IS ' Autor: O Peregrino View que mostra o tamanho dos bancos em MB' ; -- view que executa tentativa de fechamento de conexoes, necessario permissao de super-usuario, -- em testes DROP VIEW IF EXISTS vw_close_db; CREATE OR REPLACE VIEW vw_close_db AS ( SELECT procpid , datname AS banco , client_addr AS ip , pg_cancel_backend(procpid) AS sit FROM pg_stat_activity ) ; COMMENT ON VIEW vw_close_db IS ' Autor: O Peregrino Derruba todos os usuario conectados. Necessário permissao de super-usuario OBS: Em testes' ; -- view para descobrir esquema de uma tabela ou quais as tabelas existentes em um esquema por quantidade de registros DROP VIEW IF EXISTS vw_find CASCADE; CREATE OR REPLACE VIEW vw_find AS ( SELECT n.nspname AS esquema , c.relname AS tabela , pg_catalog.pg_get_userbyid(c.relowner) AS dono , pg_catalog.obj_description(c.oid, 'pg_class') AS comentario , reltuples::integer AS registros , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_sem_indices , pg_size_pretty(pg_total_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_com_indices FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) AND n.nspname NOT ILIKE 'pg_temp_%' AND n.nspname NOT ILIKE 'pg_ts_%' AND c.relname NOT ILIKE 'pg_ts_%' ORDER BY 1 ASC ) ; COMMENT ON VIEW vw_find IS ' Autor: O Peregrino View que procura por tabelas, esquemas, comentários, donos no banco --exemplos: --busca simples (tudo) SELECT * FROM vw_find ; --busca por esquema SELECT * FROM vw_find WHERE esquema ILIKE \\\'esquema\\\' ; --busca por tabelas SELECT * FROM vw_find WHERE tabela ILIKE \\\'%tabela%\\\' ; ' ; /* --exemplos de vw_find: --busca simples (tudo) SELECT * FROM vw_find ; --busca por esquema SELECT * FROM vw_find WHERE esquema ILIKE 'esquema' ; --busca por tabelas SELECT * FROM vw_find WHERE tabela ILIKE '%tabela%' ; */ -- view para procuar as sequencias DROP VIEW IF EXISTS vw_find_seq; CREATE OR REPLACE VIEW vw_find_seq AS ( SELECT n.nspname AS esquema, c.relname AS seq, pg_catalog.pg_get_userbyid(c.relowner) AS dono, pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer AS registros FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'S' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY n.nspname, c.relname ); COMMENT ON VIEW vw_find_seq IS ' Autor: O Peregrino View que procura por esquemas, sequencia, comentários, donos no banco --exemplos: --busca simples (tudo) SELECT * FROM vw_find_seq ; --busca por esquema SELECT * FROM vw_find_seq WHERE esquema ILIKE \\\'esquema\\\' ; --busca por tabelas SELECT * FROM vw_find_seq WHERE seq ILIKE \\\'%seq%\\\' ; ' ; -- view para listar todas a views de usuario DROP VIEW IF EXISTS vw_list_views; CREATE OR REPLACE VIEW vw_list_views AS ( SELECT * FROM pg_views WHERE schemaname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ); COMMENT ON VIEW vw_list_views IS ' Autor: O Peregrino View que lista todas as views dos usuarios no banco --SELECT * FROM vw_list_views;' ; -- view que lista todas as colunas de todas as tabelas DROP VIEW IF EXISTS vw_find_attrib CASCADE; CREATE OR REPLACE VIEW vw_find_attrib AS ( SELECT b.esquema , b.tabela , b.oid_tabela , b.registros , a.attname AS "nome_atributo" , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "formato_tipo_atributo" , a.attnotnull AS "nao_nulo" , a.attnum AS "ordem_atributo_tabela" , pg_catalog.col_description(b.oid_tabela, a.attnum) AS "comentario_coluna" , ( SELECT SUBSTRING(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef ) AS padrao FROM ( SELECT c.oid AS "oid_tabela", c.relname AS "tabela", n.nspname AS "esquema", reltuples::integer AS "registros" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY reltuples::integer DESC ) AS b INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = b.oid_tabela WHERE 1=1 AND a.attnum > 0 -- despreza 'oid','cmax','xmax','cmin','xmin','ctid','tableoid' AND NOT a.attisdropped -- colunas excluidas ORDER BY b.registros DESC, b.esquema ASC, b.oid_tabela ASC, ordem_atributo_tabela ASC ) ; COMMENT ON VIEW vw_find_attrib IS ' Autor: O Peregrino View que procura por nome_atributo, formato_tipo_atributo, etc. --SELECT * FROM vw_find_attrib WHERE comentario_coluna IS NULL;' ; -- view para listar os bancos liberados ou nao para acesso DROP VIEW IF EXISTS vw_db_list_access; CREATE OR REPLACE VIEW vw_db_list_access AS ( SELECT a.datname AS banco, b.usename AS dba, CASE WHEN a.datallowconn = TRUE THEN 'LIBERADO ACESSO' ELSE 'BLOQUEADO ACESSO' END AS situacao FROM pg_database a INNER JOIN pg_user b ON b.usesysid = a.datdba ); COMMENT ON VIEW vw_db_list_access IS ' Autor: O Peregrino View que lista bancos com acesso liberados ou bloqueados. --OBS: Apenas super-usuario pode executar as clausulas abaixo --Para bloquear um banco --ALTER DATABASE foo CONNECTION LIMIT 0; --(so super-usuários podem se conectar) --Para desbloquear um banco --ALTER DATABASE foo CONNECTION LIMIT -1; --(so super-usuários podem se conectar) --SELECT * FROM vw_db_list_access; ' ; -- view que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido DROP VIEW IF EXISTS vw_table_relat; CREATE OR REPLACE VIEW vw_table_relat AS ( SELECT c.oid, n.nspname AS esquema, c.relname AS tabela, conname, consrc FROM ( SELECT conname, pg_catalog.pg_get_constraintdef(oid) AS consrc, conrelid as relid FROM pg_catalog.pg_constraint WHERE contype = 'f' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace ); COMMENT ON VIEW vw_table_relat IS ' Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query) View que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido --SELECT * FROM vw_table_relat;' ; --view que mostra as tabelas que estao com tuplas mortas e precisam de vacuum DROP VIEW IF EXISTS vw_dead_tuples; CREATE OR REPLACE VIEW vw_dead_tuples AS ( SELECT * FROM pg_stat_all_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC ) ; COMMENT ON VIEW vw_dead_tuples IS ' Autor: O Peregrino View que retorna no nome das tabelas que estao com tuplas mortas e precisam de vacuum --SELECT * FROM vw_dead_tuples;' ; -- view que mostrar quantidade de usuario conectados no banco DROP VIEW IF EXISTS vw_db_users_number; CREATE OR REPLACE VIEW vw_db_users_number AS ( SELECT COUNT(*) AS total FROM ( SELECT DISTINCT client_addr FROM pg_stat_activity ) AS x ); COMMENT ON VIEW vw_db_users_number IS ' Autor: O Peregrino View que retorna quantos usuarios conectados ao banco --SELECT * FROM vw_db_users_number; ' ; --view para identificação de índices duplicados DROP VIEW IF EXISTS vw_idx_duplicate; CREATE OR REPLACE VIEW vw_idx_duplicate AS ( SELECT pg_stat_user_indexes.schemaname AS esquema , pg_stat_user_indexes.relname AS tabela , pg_attribute.attname AS nome_atributo , pg_stat_user_indexes.indexrelname AS nome_indice , CASE pg_index.indisprimary WHEN 't' THEN 'Sim' ELSE 'Nao' END AS indice_na_chave_primaria FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid) JOIN ( SELECT pg_index.indrelid, pg_index.indkey, count(*) FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid) GROUP BY pg_index.indrelid , pg_index.indkey HAVING count(*)>1 ) ind_dup ON pg_index.indrelid=ind_dup.indrelid AND pg_index.indkey=ind_dup.indkey JOIN pg_attribute ON pg_attribute.attrelid=ind_dup.indrelid AND pg_attribute.attnum=SOME(pg_index.indkey) ORDER BY pg_stat_user_indexes.schemaname , pg_stat_user_indexes.relname , pg_index.indisprimary='t' DESC ); COMMENT ON VIEW vw_idx_duplicate IS ' Autores: O Peregrino (apenas a view) JotaComm |http://jotacomm.wordpress.com| (query) View que retorna identificação de índices duplicados no banco --SELECT * FROM vw_idx_duplicate;' ; --view que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique) DROP VIEW IF EXISTS vw_list_unique CASCADE; CREATE OR REPLACE VIEW vw_list_unique AS ( SELECT DISTINCT x.nome_constraint , x.esquema , c2.relname AS tabela , x.coluna AS nome_atributo FROM ( SELECT n.nspname AS esquema , c.relname AS nome_constraint , at.attname AS coluna , cn.conrelid AS oid FROM pg_catalog.pg_constraint cn INNER JOIN pg_catalog.pg_index i ON (cn.conrelid = i.indrelid) INNER JOIN pg_catalog.pg_attribute at ON at.attrelid = i.indexrelid INNER JOIN pg_catalog.pg_class c ON c.oid = at.attrelid LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE 1=1 AND i.indisunique IS TRUE AND i.indisprimary IS FALSE AND cn.contype = 'u' --UNIQUE ) AS x INNER JOIN pg_catalog.pg_class c2 ON c2.oid = x.oid WHERE 1=1 ) ; COMMENT ON VIEW vw_list_unique IS ' Autor: O Peregrino View que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique) Exemplo: CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor --SELECT * FROM vw_list_unique; --Exemplo: --CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor' ; --view que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check) -- em teste DROP VIEW IF EXISTS vw_list_check CASCADE; CREATE OR REPLACE VIEW vw_list_check AS ( SELECT DISTINCT x.nome_constraint , x.esquema , c2.relname AS tabela , x.coluna AS nome_atributo , x.conteudo FROM ( SELECT n.nspname AS esquema , c.relname AS nome_constraint , at.attname AS coluna , cn.conrelid AS oid , cn.consrc AS conteudo FROM pg_catalog.pg_constraint cn INNER JOIN pg_catalog.pg_index i ON (cn.conrelid = i.indrelid) INNER JOIN pg_catalog.pg_attribute at ON at.attrelid = i.indexrelid INNER JOIN pg_catalog.pg_class c ON c.oid = at.attrelid LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE 1=1 AND cn.contype = 'c' --CHECK ) AS x INNER JOIN pg_catalog.pg_class c2 ON c2.oid = x.oid WHERE 1=1 -- comentar essa linha se quiser listar todos ); COMMENT ON VIEW vw_list_check IS ' Autor: O Peregrino View que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check) OBS: Em testes --SELECT * FROM vw_list_check; ' ; -- view que lista todas as chaves primarias (pk) de todas as tabelas DROP VIEW IF EXISTS vw_list_pk CASCADE; CREATE OR REPLACE VIEW vw_list_pk AS ( SELECT d.esquema , b.relname AS tabela , c.attname AS nome_atributo , pg_catalog.format_type(c.atttypid, c.atttypmod) AS formato_tipo_atributo , pg_catalog.col_description(d.oid_tabela, c.attnum) AS comentario_coluna , c.attnum AS ordem_atributo_tabela , d.registros FROM ( SELECT c.oid AS "oid_tabela", c.relname AS "tabela", n.nspname AS "esquema", reltuples::integer AS "registros" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY reltuples::integer DESC ) AS d INNER JOIN pg_index a ON a.indrelid = d.oid_tabela INNER JOIN pg_class b ON b.oid = a.indrelid INNER JOIN pg_attribute c ON c.attrelid = b.oid WHERE b.oid IN ( SELECT oid FROM pg_catalog.pg_class WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%' ) AND c.attnum = ANY(a.indkey) AND a.indisprimary IS TRUE AND a.indisunique IS TRUE AND c.attnotnull IS TRUE AND c.attrelid = b.oid --AND c.attnum > 1 ORDER BY 1 ASC, 2 ASC ); COMMENT ON VIEW vw_list_pk IS ' Autores: O Peregrino / José Flávio DBA |zflavio at gmail.com| View que retorna uma lista das chaves primarias (pk) do banco --SELECT * FROM vw_list_pk; ' ; --View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados DROP VIEW IF EXISTS vw_max_id CASCADE; CREATE OR REPLACE VIEW vw_max_id AS ( ( SELECT 'SELECT ' || '\''||x.esquema||'.'||x.tabela||'\'' || ' AS esquema_tabela' || ',' || ' (SELECT MAX(' || x.nome_atributo || ')' || ' FROM ' || x.esquema || '.' || '\"'||x.tabela||'\"' || ')::BIGINT AS maior_id UNION ' AS maior_ids FROM ( SELECT * FROM vw_list_pk WHERE esquema NOT IN ('migracoes' ,'z_deprecated' ,'complexo_hospitalar' ,'espaco_fisico' ) AND formato_tipo_atributo NOT IN ('date') AND formato_tipo_atributo NOT ILIKE 'character%' AND tabela NOT ILIKE '@%' ORDER BY 7 DESC ) AS x ) UNION ( SELECT 'SELECT \'ZZZ-FIM-USADO-PARA-CONTROLE\' AS esquema_tabela, 0::BIGINT AS maior_id ORDER BY 2 DESC;' AS maior_ids ) ORDER BY 1 ASC ); COMMENT ON VIEW vw_max_id IS ' Autor: O Peregrino View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados -- O resultado desta view gera um nova query para obter o resultado -- desejado. -- Esta consulta pega o maior id de todas tabelas de um banco. -- É gerado apenas um coluna como resultado da view com query. -- para obter o valor maximo de um id em uma tabela, de todas as tabelas de um banco. -- Dica para execução: -- No pgadmin selecione CTRL + A, CTRL + C no resultado da query, -- Abrir uma nova query, CTRL + V, CTRL + HOME, -- CTRL + F, substituir " por nada, recomendado, em vez de " ser |, se estiver configurado -- Clicar em (Substituir TODOS) e depois em (Fechar) -- Executar query (f5) -- Será retornada 2 colunas: esquema_tabela e o maior_id -- Essa view vw_max_id usa a view vw_list_pk --SELECT * FROM vw_max_id; ' ; -- view para checar compatibilidade de tipo dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos compativeis DROP VIEW IF EXISTS vw_list_type_not_compatible_relat; CREATE OR REPLACE VIEW vw_list_type_not_compatible_relat AS ( SELECT tb_virtual.oid_fk , tb_virtual.esquema_fk , tb_virtual.tabela_fk , tb_virtual.coluna_fk , tb_virtual.oid_referenciada , tb_virtual.esquema_tabela_referenciada , tb_virtual.coluna_referenciada , tb_virtual.nome_constraint , tb_virtual.sql_constraint , pg_catalog.format_type(at.atttypid, at.atttypmod) AS formato_tipo_coluna_referenciada , tb_virtual.formato_tipo_coluna_fk FROM ( SELECT c.oid AS oid_fk , n.nspname AS esquema_fk , c.relname AS tabela_fk , SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME AS coluna_fk , subq.confrelid AS oid_referenciada , SUBSTRING(subq.consrc,'REFERENCES (.*)\\(')::NAME AS esquema_tabela_referenciada , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME AS coluna_referenciada , pg_catalog.format_type(a.atttypid, a.atttypmod) AS formato_tipo_coluna_fk , subq.conname AS nome_constraint , subq.consrc AS sql_constraint FROM ( SELECT c.conname , pg_catalog.pg_get_constraintdef(oid) AS consrc , c.conrelid AS relid , c.confrelid FROM pg_catalog.pg_constraint c WHERE c.contype = 'f' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid JOIN pg_type AS t ON t.oid = a.atttypid WHERE 1=1 AND a.attname IN ( SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME -- coluna_fk , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME -- coluna_referenciada ) ) AS tb_virtual INNER JOIN pg_catalog.pg_attribute AS at ON tb_virtual.oid_referenciada = attrelid JOIN pg_type AS tt ON tt.oid = at.atttypid WHERE attname = tb_virtual.coluna_fk AND tb_virtual.formato_tipo_coluna_fk <> pg_catalog.format_type(at.atttypid, at.atttypmod) -- formato_tipo_coluna_referenciada ORDER BY 1 ASC ); COMMENT ON VIEW vw_list_type_not_compatible_relat IS ' Autor: O Peregrino View que retorna uma lista de tipos dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos nao compativeis --SELECT * FROM vw_list_type_not_compatible_relat; ' ; -- View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos correntes no servidor DROP VIEW IF EXISTS vw_info_utils; CREATE OR REPLACE VIEW vw_info_utils AS ( SELECT TO_CHAR(NOW(),'DD/MM/YYYY HH:MI:SS') AS agora , SESSION_USER AS usuario_corrente , CURRENT_SCHEMA() AS esquema_corrente , CURRENT_DATABASE() AS banco_corrente , INET_CLIENT_ADDR() AS ip_cliente , INET_CLIENT_PORT() AS porta_cliente , INET_SERVER_ADDR() AS ip_servidor , INET_SERVER_PORT() AS porta_servidor , TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in , AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age , VERSION() AS versao_postgres , PG_BACKEND_PID() AS pid_corrente , (SELECT COUNT(*) FROM pg_stat_get_backend_idset()) AS qt_pid_processos_correntes_servidor ) ; COMMENT ON VIEW vw_info_utils IS ' Autor: O Peregrino View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos correntes no servidor --SELECT * FROM vw_info_utils;' ; -- view que mostra data do ultimo vacuum DROP VIEW IF EXISTS vw_list_last_vacuum; CREATE OR REPLACE VIEW vw_list_last_vacuum AS ( SELECT schemaname AS esquema , relname AS tabela , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum FROM pg_stat_all_tables ) ; COMMENT ON VIEW vw_list_last_vacuum IS ' Autor: O Peregrino View que retorna uma lista de tabelas com o data do ultimo vacuum --SELECT * FROM vw_list_last_vacuum;' ; -- lista funcoes e esquema DROP VIEW IF EXISTS vw_list_user_functions; CREATE OR REPLACE VIEW vw_list_user_functions AS ( SELECT a.routine_catalog , a.routine_schema , a.routine_name , a.routine_type , b.prosrc FROM information_schema.routines a INNER JOIN pg_proc b ON a.routine_name = b.proname WHERE 1=1 AND a.routine_schema NOT IN ( 'pg_catalog' ,'information_schema' ,'pg_toast' ) ) ; COMMENT ON VIEW vw_list_user_functions IS ' Autor: O Peregrino View que retorna uma lista funcoes por esquema --SELECT * FROM vw_list_user_functions;' ; -- view que lista as tablespaces criadas DROP VIEW IF EXISTS vw_list_tablespaces; CREATE OR REPLACE VIEW vw_list_tablespaces AS ( SELECT spcname , pg_catalog.pg_get_userbyid(spcowner) AS spcowner , spclocation FROM pg_catalog.pg_tablespace ); COMMENT ON VIEW vw_list_tablespaces IS ' Autor: O Peregrino View que retorna uma lista das tablespaces criadas --SELECT * FROM vw_list_tablespaces' ; -- view que lista os indices ajuda a descobrir os indices de um esquema e ou tabela DROP VIEW IF EXISTS vw_list_idx; CREATE OR REPLACE VIEW vw_list_idx AS ( SELECT n.nspname AS esquema , c.relname AS tabela , pg_catalog.pg_get_userbyid(c.relowner) AS dono_tabela , c2.relname AS nome_indice , CASE WHEN i.indisprimary = TRUE THEN 'SIM' ELSE 'NAO' END AS primary_key , CASE WHEN i.indisunique = TRUE THEN 'SIM' ELSE 'NAO' END AS unique , i.indisclustered , i.indisvalid , pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) --, c2.reltablespace , pg_catalog.obj_description(c2.oid, 'pg_class') AS comentario_indice , c.reltuples::integer AS registros_tabela , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_indice FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE 1=1 AND c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND n.nspname NOT ILIKE 'pg_temp_%' AND n.nspname NOT ILIKE 'pg_ts_%' AND c.relname NOT ILIKE 'pg_ts_%' ORDER BY n.nspname ASC , c.relname ASC , i.indisprimary DESC , i.indisunique DESC , c2.relname ASC ) ; COMMENT ON VIEW vw_list_idx IS ' Autor: O Peregrino View que lista os indices, isto e, descobrir os indices de um esquema e ou tabela --SELECT * FROM vw_list_idx;' ; -- view que lista regras DROP VIEW IF EXISTS vw_rules; CREATE OR REPLACE VIEW vw_rules AS ( SELECT * FROM pg_rules ); COMMENT ON VIEW vw_rules IS ' Autor: O Peregrino View que lista rules --SELECT * FROM vw_rules;' ; ---view que mostra qual o limite para estouro maximo de transacoes (2 bilhoes) DROP VIEW IF EXISTS vw_max_burst_transactions; CREATE OR REPLACE VIEW vw_max_burst_transactions AS ( SELECT datname AS banco , AGE(datfrozenxid) AS transacoes , 2000000000 AS nao_ultrapassar_esse_limite FROM pg_database ); COMMENT ON VIEW vw_max_burst_transactions IS ' Autor: O Peregrino, baseado na documentação do PostgreSQL 8.0 View que mostra a quantidade de transacoes por banco/servidor realizadas. OBS: O DBA NAO PODE DEIXAR ULTRAPASSAR 2 BILHOES TRANSACOES!!! --SELECT * FROM vw_max_burst_transactions;' ; -- view para tamanho do bloco no disco DROP VIEW IF EXISTS vw_db_size_block; CREATE OR REPLACE VIEW vw_db_size_block AS ( SELECT * FROM pg_settings WHERE name='block_size' ); COMMENT ON VIEW vw_db_size_block IS ' Autor: O Peregrino View que mostra o tamanho do block no disco --SELECT * FROM vw_db_size_block;' ; -- view que mostra o nivel de isolamento de transacoes DROP VIEW IF EXISTS vw_isolation_level; CREATE OR REPLACE VIEW vw_isolation_level AS ( SELECT name AS isolamento_transacao, setting AS configurado FROM pg_settings WHERE name LIKE 'transaction%' ); COMMENT ON VIEW vw_isolation_level IS ' Autor: O Peregrino View que mostra o nivel de isolamento de transacao --SELECT * FROM vw_isolation_level;' ; -- view que mostra as dependencias entre tabelas DROP VIEW IF EXISTS vw_depend_tables; CREATE OR REPLACE VIEW vw_depend_tables AS ( SELECT esq_pai.nspname AS esquema_pai , tab_pai.relname AS tabela_pai , esq_filho.nspname AS esquema_filho , tab_filho.relname AS tabela_filho , esq_pai.nspname ||'.'|| tab_pai.relname AS esquema_tabela_pai , esq_filho.nspname || '.' || tab_filho.relname AS esquema_tabela_filho FROM pg_class AS tab_pai INNER JOIN pg_constraint ON tab_pai.oid = pg_constraint.confrelid INNER JOIN pg_class AS tab_filho ON pg_constraint.conrelid = tab_filho.oid LEFT JOIN pg_catalog.pg_namespace esq_pai ON esq_pai.oid = tab_pai.relnamespace LEFT JOIN pg_catalog.pg_namespace esq_filho ON esq_filho.oid = tab_filho.relnamespace WHERE 1=1 AND tab_pai.relkind = 'r' ORDER BY esq_pai.nspname ASC, tab_pai.oid ASC ); COMMENT ON VIEW vw_depend_tables IS ' Autor: O Peregrino View que mostra as dependencias entre as tabelas --SELECT * FROM vw_depend_tables;' ; --view que mostra as chaves estrangeiras, constraints DROP VIEW IF EXISTS vw_list_fk CASCADE; CREATE OR REPLACE VIEW vw_list_fk AS ( SELECT c.oid , n.nspname AS esquema , c.relname AS tabela , SUBSTRING(consrc,'\\((.*?)\\)') AS nome_atributo , SUBSTRING(consrc,'\REFERENCES (.*?)\\(') AS nome_esquema_tabela_referencia , conname AS nome_constraint , consrc AS conteudo_constraint FROM ( SELECT conname, pg_catalog.pg_get_constraintdef(oid) AS consrc, conrelid as relid FROM pg_catalog.pg_constraint WHERE contype = 'f' --AND pg_catalog.pg_get_constraintdef(oid) ILIKE '%tabela%' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace ); COMMENT ON VIEW vw_list_fk IS ' Autor: O Peregrino View que mostra as chaves estrangeiras, constraints das tabelas --SELECT * FROM vw_list_fk;' ; --view que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela DROP VIEW IF EXISTS vw_find_pk_not_first; CREATE OR REPLACE VIEW vw_find_pk_not_first AS ( SELECT nspname AS esquema , relname AS tabela , pg_attribute.attname AS nome_atributo , attnum AS ordem_atributo_tabela FROM pg_index , pg_class , pg_attribute , pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = ANY(pg_index.indkey) AND indisprimary AND attrelid = pg_class.oid AND attnum > 1 AND pg_class.oid IN ( SELECT oid FROM pg_catalog.pg_class WHERE relkind = 'r' AND nspname NOT IN ( 'pg_catalog' , 'information_schema' , 'pg_toast' ) ) ORDER BY esquema ASC , tabela ASC ); COMMENT ON VIEW vw_find_pk_not_first IS ' Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query) View que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela --SELECT * FROM vw_find_pk_not_first;' ; --view que efetua a mesma visualizacao de view vw_find com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco DROP VIEW IF EXISTS vw_find_dml; CREATE OR REPLACE VIEW vw_find_dml AS ( SELECT x.esquema , x.tabela , x.dono , x.comentario , x.registros , x.tamanho_sem_indices , x.tamanho_com_indices , x.total_de_leituras_sequencial , x.total_de_leituras_indexada , x.total_de_leituras , x.total_de_inserts , x.total_de_updates , x.total_de_deletes FROM ( SELECT * FROM vw_find INNER JOIN ( SELECT relname AS tabelax , SUM(seq_scan) AS total_de_leituras_sequencial , SUM(idx_scan) AS total_de_leituras_indexada , SUM(seq_scan+idx_scan) AS total_de_leituras , SUM(n_tup_ins) AS total_de_inserts , SUM(n_tup_upd) AS total_de_updates , SUM(n_tup_del) AS total_de_deletes FROM pg_stat_user_tables GROUP BY relname ) AS psut ON psut.tabelax = vw_find.tabela ) AS x ); COMMENT ON VIEW vw_find_dml IS ' Autor: O Peregrino View que efetua a mesma visualizacao de view vw_find com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco --SELECT * FROM vw_find_dml;' ; --view que mostra os dominios DROP VIEW IF EXISTS vw_list_domain CASCADE; CREATE OR REPLACE VIEW vw_list_domain AS ( SELECT n.nspname as esquema , t.typname as nome_dominio , pg_catalog.format_type(t.typbasetype, t.typtypmod) as formato_tipo_dominio, CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null' WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault ELSE '' END as modifier, pg_catalog.array_to_string(ARRAY( SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid ), ' ') as check FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'd' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND pg_catalog.pg_type_is_visible(t.oid) ORDER BY 1, 2 ); COMMENT ON VIEW vw_list_domain IS ' Autor: O Peregrino View que mostra os dominios cadastrados no banco SELECT * FROM vw_list_domain; '; DROP VIEW IF EXISTS vw_list_dict_data CASCADE; CREATE OR REPLACE VIEW vw_list_dict_data AS ( SELECT at.esquema , at.tabela , at.esquema || '.' || at.tabela AS esquema_tabela , at.ordem_atributo_tabela , at.nome_atributo , at.formato_tipo_atributo , at.nao_nulo , at.comentario_coluna , CASE WHEN (fk.nome_esquema_tabela_referencia IS NOT NULL) THEN 'TABELA: ' || fk.nome_esquema_tabela_referencia WHEN (dn.nome_dominio IS NOT NULL) THEN 'LISTA: ' || dn.modifier || dn.check END AS dominio , CASE WHEN (pk.nome_atributo IS NOT NULL) THEN 'X' END AS pri , CASE WHEN (fk.nome_atributo IS NOT NULL) THEN 'X' END AS est , CASE WHEN (unq.nome_atributo IS NOT NULL) THEN 'X' END AS can , at.padrao , ck.conteudo AS check FROM vw_find_attrib at LEFT JOIN vw_list_pk pk ON ( at.esquema = pk.esquema AND at.tabela = pk.tabela AND at.nome_atributo = pk.nome_atributo ) LEFT JOIN vw_list_check ck ON ( at.esquema = ck.esquema AND at.tabela = ck.tabela AND at.nome_atributo = ck.nome_atributo ) LEFT JOIN vw_list_unique unq ON ( at.esquema = ck.esquema AND at.tabela = unq.tabela AND at.nome_atributo = unq.nome_atributo ) LEFT JOIN vw_list_fk fk ON ( at.esquema = fk.esquema AND at.tabela = fk.tabela AND at.nome_atributo = fk.nome_atributo ) LEFT JOIN vw_list_domain dn ON ( at.formato_tipo_atributo = dn.nome_dominio ) WHERE 1=1 ORDER BY at.esquema ASC , at.tabela ASC , at.ordem_atributo_tabela ASC ); COMMENT ON VIEW vw_list_dict_data IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados SELECT * FROM vw_list_dict_data; '; DROP VIEW IF EXISTS vw_list_dict_data_docx CASCADE; CREATE OR REPLACE VIEW vw_list_dict_data_docx AS ( SELECT esquema , tabela , esquema_tabela AS "Esquema.Tabela" , ordem_atributo_tabela , nome_atributo AS "Atributo" , CASE WHEN (formato_tipo_atributo = 'integer') THEN 'inteiro' WHEN (formato_tipo_atributo = 'smallint') THEN 'inteiro_pesqueno' WHEN (formato_tipo_atributo = 'bigint') THEN 'inteiro_grande' WHEN (formato_tipo_atributo = 'numeric') THEN 'numerico' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN 'numerico' || SUBSTRING(formato_tipo_atributo FROM 8) WHEN (formato_tipo_atributo = 'real') THEN 'real' WHEN (formato_tipo_atributo = 'double precision') THEN 'dapla_precisao' WHEN (formato_tipo_atributo = 'date') THEN 'data' WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'time without time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'time with time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'text') THEN 'texto' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN 'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18) WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN 'caracter' || SUBSTRING(formato_tipo_atributo FROM 10) WHEN (formato_tipo_atributo = '"char"') THEN 'caracter' WHEN (formato_tipo_atributo = 'bpchar') THEN 'caracter' WHEN (formato_tipo_atributo = 'boolean') THEN 'logico' WHEN (formato_tipo_atributo = 'bytea') THEN 'objeto_binario_grande' ELSE formato_tipo_atributo END AS "Tipo" , CASE WHEN (nao_nulo IS TRUE) THEN 'X' END AS "Não Nulo" , comentario_coluna AS "Descrição" , dominio AS "Dominio" , pri AS "PRI" , est AS "EST" , can AS "CAN" , CASE WHEN (padrao = 'falsevalue()') THEN 'falso' WHEN (padrao = 'false') THEN 'falso' WHEN (padrao = 'truevalue()') THEN 'verdadeiro' WHEN (padrao = 'true') THEN 'verdadeiro' WHEN (padrao = 'now()') THEN 'agora()' WHEN (padrao = '(\'now\'::text)::date') THEN 'agora() :: data' WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN 'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8) WHEN (POSITION('::' IN padrao) > 0) THEN SUBSTRING(padrao,'(.*?)\\::') ELSE padrao END AS "Padrão" , "check" AS "Check" FROM vw_list_dict_data ); COMMENT ON VIEW vw_list_dict_data_docx IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao SELECT * FROM vw_list_dict_data_docx; '; --view que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, usa funcao de usuario sp_tradutor, sp_rm_special_chars, sp_comment_domain, verificar dependencias DROP VIEW IF EXISTS vw_list_dict_data_doc; CREATE OR REPLACE VIEW vw_list_dict_data_doc AS ( SELECT esquema , tabela , ordem_atributo_tabela , "Esquema.Tabela" , "Atributo" , "Tipo" , "Não Nulo" , "Descrição" , CASE WHEN "Domínio" IS NOT NULL THEN "Domínio" || CHR(10) || 'DOMÍNIO DISCRETO: ' || COALESCE(sp_comment_domain("Descrição"),'') ELSE COALESCE(sp_comment_domain("Descrição"),'') END AS "Domínio" , "PRI" , "EST" , "CAN" , "Padrão" , "Check" FROM ( SELECT esquema , tabela , esquema_tabela AS "Esquema.Tabela" , ordem_atributo_tabela , nome_atributo AS "Atributo" , CASE WHEN (formato_tipo_atributo = 'integer') THEN 'inteiro' WHEN (formato_tipo_atributo = 'smallint') THEN 'inteiro_pesqueno' WHEN (formato_tipo_atributo = 'bigint') THEN 'inteiro_grande' WHEN (formato_tipo_atributo = 'numeric') THEN 'numerico' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN 'numerico' || SUBSTRING(formato_tipo_atributo FROM 8) WHEN (formato_tipo_atributo = 'real') THEN 'real' WHEN (formato_tipo_atributo = 'double precision') THEN 'dapla_precisao' WHEN (formato_tipo_atributo = 'date') THEN 'data' WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'time without time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'time with time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'text') THEN 'texto' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN 'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18) WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN 'caracter' || SUBSTRING(formato_tipo_atributo FROM 10) WHEN (formato_tipo_atributo = '"char"') THEN 'caracter' WHEN (formato_tipo_atributo = 'bpchar') THEN 'caracter' WHEN (formato_tipo_atributo = 'boolean') THEN 'logico' WHEN (formato_tipo_atributo = 'bytea') THEN 'objeto_binario_grande' ELSE formato_tipo_atributo END AS "Tipo" , CASE WHEN (nao_nulo IS TRUE) THEN 'X' END AS "Não Nulo" , sp_rm_special_chars(comentario_coluna) AS "Descrição" , dominio AS "Domínio" , pri AS "PRI" , est AS "EST" , can AS "CAN" , CASE WHEN (padrao = 'falsevalue()') THEN 'falso' WHEN (padrao = 'false') THEN 'falso' WHEN (padrao = 'truevalue()') THEN 'verdadeiro' WHEN (padrao = 'true') THEN 'verdadeiro' WHEN (padrao = 'now()') THEN 'agora()' WHEN (padrao = '(\'now\'::text)::date') THEN 'agora() :: data' WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN 'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8) WHEN (POSITION('::' IN padrao) > 0) THEN SUBSTRING(padrao,'(.*?)\\::') ELSE padrao END AS "Padrão" , sp_tradutor("check") AS "Check" FROM vw_list_dict_data ) AS z ); COMMENT ON VIEW vw_list_dict_data_doc IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, esta função sp_tradutor, sp_rm_special_chars, sp_comment_domain, ver dependencias. SELECT * FROM vw_list_dict_data_doc; '; --view que lista a ordem de criação das tabelas. DROP VIEW IF EXISTS vw_order_tables_created; CREATE OR REPLACE VIEW vw_order_tables_created AS ( SELECT tab_pk.relname AS pk, tab_fk.relname AS fk FROM pg_class AS tab_pk INNER JOIN pg_constraint ON tab_pk.oid = pg_constraint.confrelid INNER JOIN pg_class AS tab_fk ON pg_constraint.conrelid = tab_fk.oid WHERE tab_pk.relkind = 'r' ORDER BY tab_pk.oid ASC ); COMMENT ON VIEW vw_order_tables_created IS ' Autor: O Peregrino View que lista a ordem de criação das tabelas. OBS: Em testes. SELECT * FROM vw_order_table_created; ' ;
-- -- Nome Artefato/Programa..: monitor_ddl.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 01/03/2010 -- Data Atual..............: 08/09/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: views para monitoramento das atividades do postgres 8.3 /8.4 -- OBS.....................: -- Script de monitoramento de banco de dados Postgres 8.x DLL -- Autor: O Peregrino -- functions -- -- Nome Artefato/Programa..: sp_tradutor.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 30/08/2010 -- Data Atual..............: 30/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para fazer tradução de english para portugues de algumas palavras usadas no postgres -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_tradutor (TEXT) CASCADE; CREATE OR REPLACE FUNCTION sp_tradutor (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; BEGIN stextonovo := REPLACE(stexto,'OR','OU'); stextonovo := REPLACE(stextonovo, 'AND','E'); stextonovo := REPLACE(stextonovo, 'NOT','NÃO'); stextonovo := REPLACE(stextonovo, 'IS',''); stextonovo := REPLACE(stextonovo, 'NULL','NULO'); stextonovo := REPLACE(stextonovo, 'integer','inteiro'); stextonovo := REPLACE(stextonovo, 'smallint','inteiro_pequeno'); stextonovo := REPLACE(stextonovo, 'bigint','inteiro_grande'); stextonovo := REPLACE(stextonovo, 'numeric','numerico'); stextonovo := REPLACE(stextonovo, 'double precision','dupla_precisao'); stextonovo := REPLACE(stextonovo, 'date','data'); stextonovo := REPLACE(stextonovo, 'timestamp without time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'timestamp with time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'timestamp(0) without time zone','data_hora'); stextonovo := REPLACE(stextonovo, 'time without time zone','hora'); stextonovo := REPLACE(stextonovo, 'text','texto'); stextonovo := REPLACE(stextonovo, 'character varying','v_caracter'); stextonovo := REPLACE(stextonovo, 'character','caracter'); stextonovo := REPLACE(stextonovo, '"char"','caracter'); stextonovo := REPLACE(stextonovo, 'bpchar','caracter'); stextonovo := REPLACE(stextonovo, 'boolean','logico'); stextonovo := REPLACE(stextonovo, 'bytea','objeto_binario_grande'); stextonovo := REPLACE(stextonovo, 'year','ano'); stextonovo := REPLACE(stextonovo, 'month','mes'); stextonovo := REPLACE(stextonovo, 'day','dia'); stextonovo := REPLACE(stextonovo, 'now()','agora()'); RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER; --SELECT sp_tradutor('(year((data_cadastro)::timestamp with time zone) >= year(now()))') AS test1; -- -- Nome Artefato/Programa..: sp_rm_special_chars.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 09/10/2008 -- Data Atual..............: 17/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_rm_special_chars (TEXT); CREATE OR REPLACE FUNCTION sp_rm_special_chars (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; BEGIN -- Tabela ASCII -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32 -- hex d abr ctl descricao -- 00 0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata stextonovo := TRANSLATE(stexto, ' ',''); -- 01 1 SOH ^A Start of Header - Início do cabeçalho stextonovo := TRANSLATE(stextonovo, ' ',''); -- 02 2 STX ^B Start of Text - Início do texto stextonovo := TRANSLATE(stextonovo, ' ',''); -- 03 3 ETX ^C End of Text - Fim do texto stextonovo := TRANSLATE(stextonovo, ' ',''); -- 04 4 EOT ^D End of Tape - Fim de fita stextonovo := TRANSLATE(stextonovo, ' ',''); -- 05 5 ENQ ^E Enquire - Interroga identidade do terminal stextonovo := TRANSLATE(stextonovo, ' ',''); -- 06 6 ACK ^F Acknowledge - Reconhecimento stextonovo := TRANSLATE(stextonovo, ' ',''); -- 07 7 BEL ^G Bell - Campainha stextonovo := TRANSLATE(stextonovo, '\8',''); -- 08 8 BS ^H Back-space - Espaço atrás stextonovo := TRANSLATE(stextonovo, '\9',''); -- 09 9 HT ^I Horizontal Tabulation - Tabulação horizontal stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0A 10 LF ^J Line-Feed - Alimenta linha stextonovo := TRANSLATE(stextonovo, '\11',''); -- 0B 11 VT ^K Vertical Tabulation - Tabulação vertical stextonovo := TRANSLATE(stextonovo, '\12',''); -- 0C 12 FF ^L Form-Feed - Alimenta formulário stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0D 13 CR ^M Carriage-Return - Retorno do carro (enter) stextonovo := TRANSLATE(stextonovo, ' ',''); -- 0E 14 SO ^N Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.) stextonovo := TRANSLATE(stextonovo, '\15',''); -- 0F 15 SI ^O Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.) stextonovo := TRANSLATE(stextonovo, ' ',''); -- 10 16 DLE ^P Data-Link Escape stextonovo := TRANSLATE(stextonovo, ' ',''); -- 11 17 DC1 ^Q Device-Control 1 stextonovo := TRANSLATE(stextonovo, ' 8',''); -- 12 18 DC2 ^R Device-Control 2 stextonovo := TRANSLATE(stextonovo, ' 9',''); -- 13 19 DC3 ^S Device-Control 3 stextonovo := TRANSLATE(stextonovo, ' ',''); -- 14 20 DC4 ^T Device-Control 4 stextonovo := TRANSLATE(stextonovo, ' ',''); -- 15 21 NAK ^U Neg-Acknowledge - Não-reconhecimento stextonovo := TRANSLATE(stextonovo, ' ',''); -- 16 22 SYN ^V Synchronous Idle stextonovo := TRANSLATE(stextonovo, ' ',''); -- 17 23 vETB^W End-of-Transmission Block stextonovo := TRANSLATE(stextonovo, ' ',''); -- 18 24 CAN ^X Cancel stextonovo := TRANSLATE(stextonovo, ' ',''); -- 19 25 EM ^Y End-Of-Medium stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1A 26 SUB ^Z Substitute stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1B 27 ESC ^[ Escape stextonovo := TRANSLATE(stextonovo, ' 8',''); -- 1C 28 FS ^\ File Separator stextonovo := TRANSLATE(stextonovo, ' 9',''); -- 1D 29 GS ^] Group Separator stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1E 30 RS ^^ Record Separator stextonovo := TRANSLATE(stextonovo, ' ',''); -- 1F 31 US ^_ Unit Separator stextonovo := TRANSLATE(stextonovo, '\127',''); -- 7F127 DEL ^? Delete RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --SELECT 'SEM STRESS... W TECLA ENTER TECLA ESC TESTE OK ' AS test1 -- -- Nome Artefato/Programa..: sp_comment_domain.sql -- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) -- Data Inicio ............: 31/08/2010 -- Data Atual..............: 31/08/2010 -- Versao..................: 0.01 -- Compilador/Interpretador: PostgreSql -- Sistemas Operacionais...: Linux/Windows -- SGBD....................: PostgreSql 8.x -- Kernel..................: Nao informado! -- Finalidade..............: store procedure (function) para fazer tratamento dos comentarios de dominio -- OBS.....................: -- --habilitando linguagem plpgsql --CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- languages --SELECT * FROM pg_language; -- Apaga function se existir DROP FUNCTION IF EXISTS sp_comment_domain (TEXT); CREATE OR REPLACE FUNCTION sp_comment_domain (stexto TEXT) RETURNS TEXT AS $$ DECLARE stextonovo TEXT; stexto_transitorio TEXT; limite INTEGER; BEGIN SELECT INTO limite array_upper(string_to_array(stexto, '§§'),1); IF limite > 1 THEN stextonovo := ''; FOR i IN 2 .. limite LOOP SELECT INTO stexto_transitorio a.ar[i] FROM (SELECT string_to_array(stexto, '§§') AS ar ) AS a ; stextonovo := stextonovo || stexto_transitorio || CHR(9); --9 tabulacao horizontal ou 10 proxima linha ou 11 tabulacao vertical END LOOP; END IF; RETURN stextonovo; END; $$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED ON NULL INPUT SECURITY INVOKER; --STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER; /* SELECT sp_comment_domain ('Status do bem referente a sua situação negocial. Representado pela classe de constantes: br.ufrn.sipac.patrimonio.tombamento.dominio.TipoStatus, podendo ter tais características: §§ EFETIVADO = 1 (Quando o bem acaba de ser tombado, sua situação fica normal para realizar operações.), §§ DEVOLVIDO = 2 (Para bens de terceiros, quando devolvido a entidade terceira que o cedeu), §§PENDENTE = 3. (Bem terceiro não finalizado e que pertence à entidade que o adquiriu.), §§ ALIENADO = 4 (Bem que sofreu processo de alienação), §§ ACAUTELADO = 5 (Bem que sofreu processo de acautelamento), §§ EM_PROCESSO_ALIENACAO = 6 (Bem que está em processo de alienação), §§ EM_MOVIMENTACAO = 7 (Bem que está sob movimentação temporária).') AS test1 */ --views deprecated /* DROP VIEW IF EXISTS view_close_db; DROP VIEW IF EXISTS view_con; DROP VIEW IF EXISTS view_disk_mem; DROP VIEW IF EXISTS view_monitor; DROP VIEW IF EXISTS view_monitor2; DROP VIEW IF EXISTS view_size_db; DROP VIEW IF EXISTS view_status_locks; DROP VIEW IF EXISTS view_status_locks_tables; DROP VIEW IF EXISTS view_time_server; --view renomeada para vw_db_size; DROP VIEW IF EXISTS vw_size_db; --views criadas com dono errado DROP VIEW IF EXISTS vw_close_db; DROP VIEW IF EXISTS vw_con; DROP VIEW IF EXISTS vw_disk_mem; DROP VIEW IF EXISTS vw_find; DROP VIEW IF EXISTS vw_monitor; DROP VIEW IF EXISTS vw_size_db; DROP VIEW IF EXISTS vw_status_locks; DROP VIEW IF EXISTS vw_status_locks_tables; DROP VIEW IF EXISTS vw_time_server; -- */ -- view lista status das tabelas relativo a locks DROP VIEW IF EXISTS vw_status_locks; CREATE OR REPLACE VIEW vw_status_locks AS ( -- locks em andamento SELECT d.client_addr AS "maquina", c.datname AS "banco", e.schemaname AS "esquema", b.relname AS "tabela", a.mode, CASE WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela' WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela' WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO' WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela' WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela' END AS "modo" FROM pg_locks a INNER JOIN pg_class b ON b.oid = a.relation AND b.relkind = 'r' INNER JOIN pg_tables e ON b.relname = e.tablename INNER JOIN pg_database c ON c.oid = a.database INNER JOIN pg_stat_activity d ON d.procpid = a.pid WHERE relname NOT LIKE 'pg_%' --AND a.mode = 'ExclusiveLock' -- Travamento da tabela --AND a.mode = 'RowExclusiveLock' -- Travamento do registro da tabela ORDER BY modo DESC, --a.mode ASC, d.client_addr ASC, b.relname ASC ) ; COMMENT ON VIEW vw_status_locks IS ' Autor: O Peregrino View que verifica situacao de locks em andamento no banco' ; -- view que mostra locks em andamento de registros e tabelas DROP VIEW IF EXISTS vw_status_locks_tables; CREATE OR REPLACE VIEW vw_status_locks_tables AS ( SELECT d.client_addr AS "maquina", c.datname AS "banco", e.schemaname AS "esquema", b.relname AS "tabela", a.mode, CASE WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela' WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela' WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO' WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela' WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela' END AS "modo" FROM pg_locks a INNER JOIN pg_class b ON b.oid = a.relation AND b.relkind = 'r' INNER JOIN pg_tables e ON b.relname = e.tablename INNER JOIN pg_database c ON c.oid = a.database INNER JOIN pg_stat_activity d ON d.procpid = a.pid WHERE relname NOT LIKE 'pg_%' AND a.mode = 'ExclusiveLock' -- Travamento da tabela AND a.mode = 'RowExclusiveLock' -- Travamento do registro da tabela ORDER BY modo DESC, --a.mode ASC, d.client_addr ASC, b.relname ASC ) ; COMMENT ON VIEW vw_status_locks_tables IS ' Autor: O Peregrino View que verifica situacao apenas de locks de registros e tabelas no banco' ; --view para mostrar o uso de memoria disco do postgres DROP VIEW IF EXISTS vw_disk_mem; CREATE OR REPLACE VIEW vw_disk_mem AS ( SELECT (SUM(pg_stat_database.blks_hit) / SUM(pg_stat_database.blks_read + pg_stat_database.blks_hit) * 100::NUMERIC)::INTEGER AS "% de Utilização de Mem" FROM pg_stat_database ) ; COMMENT ON VIEW vw_disk_mem IS ' Autores: O Peregrino (apenas a view) Kenia Milene |http://keniamilene.wordpress.com| (query) View que calcula através das estatisticas o percentual de utilização disco / memória. Valores acima de 70% significa que o banco esta realizado mais tarefas em memória do que i/o em disco'; -- view para listar conexoes ativas e transações efetuadas (commit-rollback) DROP VIEW IF EXISTS vw_con; CREATE OR REPLACE VIEW vw_con AS ( SELECT DISTINCT datname, pg_stat_get_db_xact_commit(datid) AS "commits", pg_stat_get_db_xact_rollback(datid) AS "rollbacks", pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid) AS "transacoes_efetuadas", CASE WHEN pg_stat_get_db_xact_commit(datid) = 0 THEN -- evita divisao por zero 0 ELSE (pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid))/(pg_stat_get_db_xact_commit(datid)/100) END AS "%", pg_stat_get_db_numbackends(datid) AS conexoes_ativas FROM pg_stat_activity ORDER BY 5 DESC ) ; COMMENT ON VIEW vw_con IS ' Autor: O Peregrino View que mostra conexoes ativas e transações efetuadas (commit-rollback)' ; -- view para monitoramento de atividade do servidor postgres por usuario DROP VIEW IF EXISTS vw_monitor; CREATE OR REPLACE VIEW vw_monitor AS ( SELECT procpid, datname AS banco, usename AS usuario_banco, client_addr AS ip, CASE WHEN waiting IS TRUE THEN 'Aguardando' ELSE 'Executando' END AS status, AGE(now(),query_start) AS tempo_execucao, current_query FROM pg_stat_activity WHERE 1=1 AND current_query <> '' -- DESOCUPADO ORDER BY tempo_execucao DESC --current_query DESC --client_addr ASC, --procpid ASC ) ; COMMENT ON VIEW vw_monitor IS ' Autor: O Peregrino View que verifica o que está executando no banco no instante atual incluido transacoes, listado por usuario e ip e por tempo Adequado para super-usuario --SELECT * FROM vw_monitor; OBS: Para cancelar processo executar os comandos abaixos -- cancelar query >= 8.0 --SELECT pg_cancel_backend(21471); -- cancelar processo >= 8.4 --SELECT pg_terminate_backend(21471); ' ; /* -- DESATIVADA POIS NECESSITA DE TABELAS CRIADA PELO DBA E REDE COM IP FIXOS -- (DBA) - monitoramento de atividade do servidor postgres por usuario DROP VIEW IF EXISTS vw_monitor2; CREATE OR REPLACE VIEW vw_monitor2 AS ( SELECT a.procpid, a.datname AS banco, a.usename AS usuario_banco, a.client_addr AS ip, b.nome, b.talk, b.sistema, CASE WHEN a.waiting IS TRUE THEN 'Aguardando' ELSE 'Executando' END AS status, AGE(now(),a.query_start) AS tempo_execucao, a.current_query FROM pg_stat_activity a LEFT JOIN system.users b ON a.client_addr = TRIM(b.ip)::inet WHERE 1=1 --AND a.usename = 'sipac' --AND a.datname LIKE 'administrativo%' AND a.current_query <> ' ' -- DESOCUPADO --AND a.usename = 'postgres' --AND a.client_addr = '10.3.128.136' -- Emerson --AND a.client_addr = '10.3.128.70' -- Itamir ORDER BY --a.current_query DESC, tempo_execucao DESC, b.nome ASC --a.client_addr ASC, --a.procpid ASC ) ; COMMENT ON VIEW vw_monitor2 IS ' Verifica o que está executando no banco no instante atual, incluindo transacoes, listado por usuario e ip e por tempo usando tabela system.users Adequado para super-usuario --SELECT * FROM vw_monitor; OBS: Para cancelar processo executar os comandos abaixos -- cancelar query >= 8.0 --SELECT pg_cancel_backend(21471); -- cancelar processo >= 8.4 --SELECT pg_terminate_backend(21471); ' ; -- estrutura de tabela a ser criada pelo DBA para funcionamento da view vw_monitor2 DROP SCHEMA IF EXISTS "system"; CREATE SCHEMA "system"; DROP TABLE IF EXISTS "system".users; CREATE TABLE "system".users ( id SERIAL NOT NULL, nome CHARACTER VARYING(50) NOT NULL, talk CHARACTER VARYING(200), email CHARACTER VARYING(200), ramal CHARACTER VARYING(20), celular CHARACTER VARYING(350), residencial CHARACTER VARYING(350), computador CHARACTER VARYING(200), ip CHARACTER VARYING(200) NOT NULL, sistema CHARACTER VARYING(20), obs TEXT, CONSTRAINT users_pkey PRIMARY KEY (id) ); INSERT INTO "system".users (id, nome, talk, email, ramal, celular, residencial, computador, ip, sistema, obs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); SELECT * FROM "system".users; */ -- view que mostra o tempo de execucao do servidor corrente DROP VIEW IF EXISTS vw_time_server; CREATE OR REPLACE VIEW vw_time_server AS ( -- quando o servidor do postgres foi iniciado, tempo? SELECT TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in, AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age ) ; COMMENT ON VIEW vw_time_server IS ' Autor: O Peregrino View que mostra quanto o servidor do postgres foi iniciado, tempo de execucao' ; -- view que mostra tamanho dos bancos DROP VIEW IF EXISTS vw_db_size; CREATE OR REPLACE VIEW vw_db_size AS ( --tamanho dos bancos em MB SELECT datname AS banco, pg_database_size(datname)/1000000 || ' MB' AS tamanho_bd FROM pg_database ORDER BY tamanho_bd DESC ) ; COMMENT ON VIEW vw_db_size IS ' Autor: O Peregrino View que mostra o tamanho dos bancos em MB' ; -- view que executa tentativa de fechamento de conexoes, necessario permissao de super-usuario, -- em testes DROP VIEW IF EXISTS vw_close_db; CREATE OR REPLACE VIEW vw_close_db AS ( SELECT procpid , datname AS banco , client_addr AS ip , pg_cancel_backend(procpid) AS sit FROM pg_stat_activity ) ; COMMENT ON VIEW vw_close_db IS ' Autor: O Peregrino Derruba todos os usuario conectados. Necessário permissao de super-usuario OBS: Em testes' ; -- view para descobrir esquema de uma tabela ou quais as tabelas existentes em um esquema por quantidade de registros DROP VIEW IF EXISTS vw_find CASCADE; CREATE OR REPLACE VIEW vw_find AS ( SELECT n.nspname AS esquema , c.relname AS tabela , pg_catalog.pg_get_userbyid(c.relowner) AS dono , pg_catalog.obj_description(c.oid, 'pg_class') AS comentario , reltuples::integer AS registros , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_sem_indices , pg_size_pretty(pg_total_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_com_indices FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) AND n.nspname NOT ILIKE 'pg_temp_%' AND n.nspname NOT ILIKE 'pg_ts_%' AND c.relname NOT ILIKE 'pg_ts_%' ORDER BY 1 ASC ) ; COMMENT ON VIEW vw_find IS ' Autor: O Peregrino View que procura por tabelas, esquemas, comentários, donos no banco --exemplos: --busca simples (tudo) SELECT * FROM vw_find ; --busca por esquema SELECT * FROM vw_find WHERE esquema ILIKE \\\'esquema\\\' ; --busca por tabelas SELECT * FROM vw_find WHERE tabela ILIKE \\\'%tabela%\\\' ; ' ; /* --exemplos de vw_find: --busca simples (tudo) SELECT * FROM vw_find ; --busca por esquema SELECT * FROM vw_find WHERE esquema ILIKE 'esquema' ; --busca por tabelas SELECT * FROM vw_find WHERE tabela ILIKE '%tabela%' ; */ -- view para procuar as sequencias DROP VIEW IF EXISTS vw_find_seq; CREATE OR REPLACE VIEW vw_find_seq AS ( SELECT n.nspname AS esquema, c.relname AS seq, pg_catalog.pg_get_userbyid(c.relowner) AS dono, pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer AS registros FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'S' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY n.nspname, c.relname ); COMMENT ON VIEW vw_find_seq IS ' Autor: O Peregrino View que procura por esquemas, sequencia, comentários, donos no banco --exemplos: --busca simples (tudo) SELECT * FROM vw_find_seq ; --busca por esquema SELECT * FROM vw_find_seq WHERE esquema ILIKE \\\'esquema\\\' ; --busca por tabelas SELECT * FROM vw_find_seq WHERE seq ILIKE \\\'%seq%\\\' ; ' ; -- view para listar todas a views de usuario DROP VIEW IF EXISTS vw_list_views; CREATE OR REPLACE VIEW vw_list_views AS ( SELECT * FROM pg_views WHERE schemaname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ); COMMENT ON VIEW vw_list_views IS ' Autor: O Peregrino View que lista todas as views dos usuarios no banco --SELECT * FROM vw_list_views;' ; -- view que lista todas as colunas de todas as tabelas DROP VIEW IF EXISTS vw_find_attrib CASCADE; CREATE OR REPLACE VIEW vw_find_attrib AS ( SELECT b.esquema , b.tabela , b.oid_tabela , b.registros , a.attname AS "nome_atributo" , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "formato_tipo_atributo" , a.attnotnull AS "nao_nulo" , a.attnum AS "ordem_atributo_tabela" , pg_catalog.col_description(b.oid_tabela, a.attnum) AS "comentario_coluna" , ( SELECT SUBSTRING(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef ) AS padrao FROM ( SELECT c.oid AS "oid_tabela", c.relname AS "tabela", n.nspname AS "esquema", reltuples::integer AS "registros" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY reltuples::integer DESC ) AS b INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = b.oid_tabela WHERE 1=1 AND a.attnum > 0 -- despreza 'oid','cmax','xmax','cmin','xmin','ctid','tableoid' AND NOT a.attisdropped -- colunas excluidas ORDER BY b.registros DESC, b.esquema ASC, b.oid_tabela ASC, ordem_atributo_tabela ASC ) ; COMMENT ON VIEW vw_find_attrib IS ' Autor: O Peregrino View que procura por nome_atributo, formato_tipo_atributo, etc. --SELECT * FROM vw_find_attrib WHERE comentario_coluna IS NULL;' ; -- view para listar os bancos liberados ou nao para acesso DROP VIEW IF EXISTS vw_db_list_access; CREATE OR REPLACE VIEW vw_db_list_access AS ( SELECT a.datname AS banco, b.usename AS dba, CASE WHEN a.datallowconn = TRUE THEN 'LIBERADO ACESSO' ELSE 'BLOQUEADO ACESSO' END AS situacao FROM pg_database a INNER JOIN pg_user b ON b.usesysid = a.datdba ); COMMENT ON VIEW vw_db_list_access IS ' Autor: O Peregrino View que lista bancos com acesso liberados ou bloqueados. --OBS: Apenas super-usuario pode executar as clausulas abaixo --Para bloquear um banco --ALTER DATABASE foo CONNECTION LIMIT 0; --(so super-usuários podem se conectar) --Para desbloquear um banco --ALTER DATABASE foo CONNECTION LIMIT -1; --(so super-usuários podem se conectar) --SELECT * FROM vw_db_list_access; ' ; -- view que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido DROP VIEW IF EXISTS vw_table_relat; CREATE OR REPLACE VIEW vw_table_relat AS ( SELECT c.oid, n.nspname AS esquema, c.relname AS tabela, conname, consrc FROM ( SELECT conname, pg_catalog.pg_get_constraintdef(oid) AS consrc, conrelid as relid FROM pg_catalog.pg_constraint WHERE contype = 'f' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace ); COMMENT ON VIEW vw_table_relat IS ' Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query) View que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido --SELECT * FROM vw_table_relat;' ; --view que mostra as tabelas que estao com tuplas mortas e precisam de vacuum DROP VIEW IF EXISTS vw_dead_tuples; CREATE OR REPLACE VIEW vw_dead_tuples AS ( SELECT * FROM pg_stat_all_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC ) ; COMMENT ON VIEW vw_dead_tuples IS ' Autor: O Peregrino View que retorna no nome das tabelas que estao com tuplas mortas e precisam de vacuum --SELECT * FROM vw_dead_tuples;' ; -- view que mostrar quantidade de usuario conectados no banco DROP VIEW IF EXISTS vw_db_users_number; CREATE OR REPLACE VIEW vw_db_users_number AS ( SELECT COUNT(*) AS total FROM ( SELECT DISTINCT client_addr FROM pg_stat_activity ) AS x ); COMMENT ON VIEW vw_db_users_number IS ' Autor: O Peregrino View que retorna quantos usuarios conectados ao banco --SELECT * FROM vw_db_users_number; ' ; --view para identificação de índices duplicados DROP VIEW IF EXISTS vw_idx_duplicate; CREATE OR REPLACE VIEW vw_idx_duplicate AS ( SELECT pg_stat_user_indexes.schemaname AS esquema , pg_stat_user_indexes.relname AS tabela , pg_attribute.attname AS nome_atributo , pg_stat_user_indexes.indexrelname AS nome_indice , CASE pg_index.indisprimary WHEN 't' THEN 'Sim' ELSE 'Nao' END AS indice_na_chave_primaria FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid) JOIN ( SELECT pg_index.indrelid, pg_index.indkey, count(*) FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid) GROUP BY pg_index.indrelid , pg_index.indkey HAVING count(*)>1 ) ind_dup ON pg_index.indrelid=ind_dup.indrelid AND pg_index.indkey=ind_dup.indkey JOIN pg_attribute ON pg_attribute.attrelid=ind_dup.indrelid AND pg_attribute.attnum=SOME(pg_index.indkey) ORDER BY pg_stat_user_indexes.schemaname , pg_stat_user_indexes.relname , pg_index.indisprimary='t' DESC ); COMMENT ON VIEW vw_idx_duplicate IS ' Autores: O Peregrino (apenas a view) JotaComm |http://jotacomm.wordpress.com| (query) View que retorna identificação de índices duplicados no banco --SELECT * FROM vw_idx_duplicate;' ; --view que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique) DROP VIEW IF EXISTS vw_list_unique CASCADE; CREATE OR REPLACE VIEW vw_list_unique AS ( SELECT DISTINCT x.nome_constraint , x.esquema , c2.relname AS tabela , x.coluna AS nome_atributo FROM ( SELECT n.nspname AS esquema , c.relname AS nome_constraint , at.attname AS coluna , cn.conrelid AS oid FROM pg_catalog.pg_constraint cn INNER JOIN pg_catalog.pg_index i ON (cn.conrelid = i.indrelid) INNER JOIN pg_catalog.pg_attribute at ON at.attrelid = i.indexrelid INNER JOIN pg_catalog.pg_class c ON c.oid = at.attrelid LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE 1=1 AND i.indisunique IS TRUE AND i.indisprimary IS FALSE AND cn.contype = 'u' --UNIQUE ) AS x INNER JOIN pg_catalog.pg_class c2 ON c2.oid = x.oid WHERE 1=1 ) ; COMMENT ON VIEW vw_list_unique IS ' Autor: O Peregrino View que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique) Exemplo: CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor --SELECT * FROM vw_list_unique; --Exemplo: --CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor' ; --view que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check) -- em teste DROP VIEW IF EXISTS vw_list_check CASCADE; CREATE OR REPLACE VIEW vw_list_check AS ( SELECT DISTINCT x.nome_constraint , x.esquema , c2.relname AS tabela , x.coluna AS nome_atributo , x.conteudo FROM ( SELECT n.nspname AS esquema , c.relname AS nome_constraint , at.attname AS coluna , cn.conrelid AS oid , cn.consrc AS conteudo FROM pg_catalog.pg_constraint cn INNER JOIN pg_catalog.pg_index i ON (cn.conrelid = i.indrelid) INNER JOIN pg_catalog.pg_attribute at ON at.attrelid = i.indexrelid INNER JOIN pg_catalog.pg_class c ON c.oid = at.attrelid LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE 1=1 AND cn.contype = 'c' --CHECK ) AS x INNER JOIN pg_catalog.pg_class c2 ON c2.oid = x.oid WHERE 1=1 -- comentar essa linha se quiser listar todos ); COMMENT ON VIEW vw_list_check IS ' Autor: O Peregrino View que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check) OBS: Em testes --SELECT * FROM vw_list_check; ' ; -- view que lista todas as chaves primarias (pk) de todas as tabelas DROP VIEW IF EXISTS vw_list_pk CASCADE; CREATE OR REPLACE VIEW vw_list_pk AS ( SELECT d.esquema , b.relname AS tabela , c.attname AS nome_atributo , pg_catalog.format_type(c.atttypid, c.atttypmod) AS formato_tipo_atributo , pg_catalog.col_description(d.oid_tabela, c.attnum) AS comentario_coluna , c.attnum AS ordem_atributo_tabela , d.registros FROM ( SELECT c.oid AS "oid_tabela", c.relname AS "tabela", n.nspname AS "esquema", reltuples::integer AS "registros" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) ORDER BY reltuples::integer DESC ) AS d INNER JOIN pg_index a ON a.indrelid = d.oid_tabela INNER JOIN pg_class b ON b.oid = a.indrelid INNER JOIN pg_attribute c ON c.attrelid = b.oid WHERE b.oid IN ( SELECT oid FROM pg_catalog.pg_class WHERE relkind = 'r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%' ) AND c.attnum = ANY(a.indkey) AND a.indisprimary IS TRUE AND a.indisunique IS TRUE AND c.attnotnull IS TRUE AND c.attrelid = b.oid --AND c.attnum > 1 ORDER BY 1 ASC, 2 ASC ); COMMENT ON VIEW vw_list_pk IS ' Autores: O Peregrino / José Flávio DBA |zflavio at gmail.com| View que retorna uma lista das chaves primarias (pk) do banco --SELECT * FROM vw_list_pk; ' ; --View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados DROP VIEW IF EXISTS vw_max_id CASCADE; CREATE OR REPLACE VIEW vw_max_id AS ( ( SELECT 'SELECT ' || '\''||x.esquema||'.'||x.tabela||'\'' || ' AS esquema_tabela' || ',' || ' (SELECT MAX(' || x.nome_atributo || ')' || ' FROM ' || x.esquema || '.' || '\"'||x.tabela||'\"' || ')::BIGINT AS maior_id UNION ' AS maior_ids FROM ( SELECT * FROM vw_list_pk WHERE esquema NOT IN ('migracoes' ,'z_deprecated' ,'complexo_hospitalar' ,'espaco_fisico' ) AND formato_tipo_atributo NOT IN ('date') AND formato_tipo_atributo NOT ILIKE 'character%' AND tabela NOT ILIKE '@%' ORDER BY 7 DESC ) AS x ) UNION ( SELECT 'SELECT \'ZZZ-FIM-USADO-PARA-CONTROLE\' AS esquema_tabela, 0::BIGINT AS maior_id ORDER BY 2 DESC;' AS maior_ids ) ORDER BY 1 ASC ); COMMENT ON VIEW vw_max_id IS ' Autor: O Peregrino View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados -- O resultado desta view gera um nova query para obter o resultado -- desejado. -- Esta consulta pega o maior id de todas tabelas de um banco. -- É gerado apenas um coluna como resultado da view com query. -- para obter o valor maximo de um id em uma tabela, de todas as tabelas de um banco. -- Dica para execução: -- No pgadmin selecione CTRL + A, CTRL + C no resultado da query, -- Abrir uma nova query, CTRL + V, CTRL + HOME, -- CTRL + F, substituir " por nada, recomendado, em vez de " ser |, se estiver configurado -- Clicar em (Substituir TODOS) e depois em (Fechar) -- Executar query (f5) -- Será retornada 2 colunas: esquema_tabela e o maior_id -- Essa view vw_max_id usa a view vw_list_pk --SELECT * FROM vw_max_id; ' ; -- view para checar compatibilidade de tipo dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos compativeis DROP VIEW IF EXISTS vw_list_type_not_compatible_relat; CREATE OR REPLACE VIEW vw_list_type_not_compatible_relat AS ( SELECT tb_virtual.oid_fk , tb_virtual.esquema_fk , tb_virtual.tabela_fk , tb_virtual.coluna_fk , tb_virtual.oid_referenciada , tb_virtual.esquema_tabela_referenciada , tb_virtual.coluna_referenciada , tb_virtual.nome_constraint , tb_virtual.sql_constraint , pg_catalog.format_type(at.atttypid, at.atttypmod) AS formato_tipo_coluna_referenciada , tb_virtual.formato_tipo_coluna_fk FROM ( SELECT c.oid AS oid_fk , n.nspname AS esquema_fk , c.relname AS tabela_fk , SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME AS coluna_fk , subq.confrelid AS oid_referenciada , SUBSTRING(subq.consrc,'REFERENCES (.*)\\(')::NAME AS esquema_tabela_referenciada , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME AS coluna_referenciada , pg_catalog.format_type(a.atttypid, a.atttypmod) AS formato_tipo_coluna_fk , subq.conname AS nome_constraint , subq.consrc AS sql_constraint FROM ( SELECT c.conname , pg_catalog.pg_get_constraintdef(oid) AS consrc , c.conrelid AS relid , c.confrelid FROM pg_catalog.pg_constraint c WHERE c.contype = 'f' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid JOIN pg_type AS t ON t.oid = a.atttypid WHERE 1=1 AND a.attname IN ( SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME -- coluna_fk , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME -- coluna_referenciada ) ) AS tb_virtual INNER JOIN pg_catalog.pg_attribute AS at ON tb_virtual.oid_referenciada = attrelid JOIN pg_type AS tt ON tt.oid = at.atttypid WHERE attname = tb_virtual.coluna_fk AND tb_virtual.formato_tipo_coluna_fk <> pg_catalog.format_type(at.atttypid, at.atttypmod) -- formato_tipo_coluna_referenciada ORDER BY 1 ASC ); COMMENT ON VIEW vw_list_type_not_compatible_relat IS ' Autor: O Peregrino View que retorna uma lista de tipos dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos nao compativeis --SELECT * FROM vw_list_type_not_compatible_relat; ' ; -- View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos correntes no servidor DROP VIEW IF EXISTS vw_info_utils; CREATE OR REPLACE VIEW vw_info_utils AS ( SELECT TO_CHAR(NOW(),'DD/MM/YYYY HH:MI:SS') AS agora , SESSION_USER AS usuario_corrente , CURRENT_SCHEMA() AS esquema_corrente , CURRENT_DATABASE() AS banco_corrente , INET_CLIENT_ADDR() AS ip_cliente , INET_CLIENT_PORT() AS porta_cliente , INET_SERVER_ADDR() AS ip_servidor , INET_SERVER_PORT() AS porta_servidor , TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in , AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age , VERSION() AS versao_postgres , PG_BACKEND_PID() AS pid_corrente , (SELECT COUNT(*) FROM pg_stat_get_backend_idset()) AS qt_pid_processos_correntes_servidor ) ; COMMENT ON VIEW vw_info_utils IS ' Autor: O Peregrino View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos correntes no servidor --SELECT * FROM vw_info_utils;' ; -- view que mostra data do ultimo vacuum DROP VIEW IF EXISTS vw_list_last_vacuum; CREATE OR REPLACE VIEW vw_list_last_vacuum AS ( SELECT schemaname AS esquema , relname AS tabela , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum FROM pg_stat_all_tables ) ; COMMENT ON VIEW vw_list_last_vacuum IS ' Autor: O Peregrino View que retorna uma lista de tabelas com o data do ultimo vacuum --SELECT * FROM vw_list_last_vacuum;' ; -- lista funcoes e esquema DROP VIEW IF EXISTS vw_list_user_functions; CREATE OR REPLACE VIEW vw_list_user_functions AS ( SELECT a.routine_catalog , a.routine_schema , a.routine_name , a.routine_type , b.prosrc FROM information_schema.routines a INNER JOIN pg_proc b ON a.routine_name = b.proname WHERE 1=1 AND a.routine_schema NOT IN ( 'pg_catalog' ,'information_schema' ,'pg_toast' ) ) ; COMMENT ON VIEW vw_list_user_functions IS ' Autor: O Peregrino View que retorna uma lista funcoes por esquema --SELECT * FROM vw_list_user_functions;' ; -- view que lista as tablespaces criadas DROP VIEW IF EXISTS vw_list_tablespaces; CREATE OR REPLACE VIEW vw_list_tablespaces AS ( SELECT spcname , pg_catalog.pg_get_userbyid(spcowner) AS spcowner , spclocation FROM pg_catalog.pg_tablespace ); COMMENT ON VIEW vw_list_tablespaces IS ' Autor: O Peregrino View que retorna uma lista das tablespaces criadas --SELECT * FROM vw_list_tablespaces' ; -- view que lista os indices ajuda a descobrir os indices de um esquema e ou tabela DROP VIEW IF EXISTS vw_list_idx; CREATE OR REPLACE VIEW vw_list_idx AS ( SELECT n.nspname AS esquema , c.relname AS tabela , pg_catalog.pg_get_userbyid(c.relowner) AS dono_tabela , c2.relname AS nome_indice , CASE WHEN i.indisprimary = TRUE THEN 'SIM' ELSE 'NAO' END AS primary_key , CASE WHEN i.indisunique = TRUE THEN 'SIM' ELSE 'NAO' END AS unique , i.indisclustered , i.indisvalid , pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) --, c2.reltablespace , pg_catalog.obj_description(c2.oid, 'pg_class') AS comentario_indice , c.reltuples::integer AS registros_tabela , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_indice FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE 1=1 AND c.relkind = 'r' AND n.nspname NOT IN ( 'pg_catalog', 'information_schema', 'pg_toast' ) AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND n.nspname NOT ILIKE 'pg_temp_%' AND n.nspname NOT ILIKE 'pg_ts_%' AND c.relname NOT ILIKE 'pg_ts_%' ORDER BY n.nspname ASC , c.relname ASC , i.indisprimary DESC , i.indisunique DESC , c2.relname ASC ) ; COMMENT ON VIEW vw_list_idx IS ' Autor: O Peregrino View que lista os indices, isto e, descobrir os indices de um esquema e ou tabela --SELECT * FROM vw_list_idx;' ; -- view que lista regras DROP VIEW IF EXISTS vw_rules; CREATE OR REPLACE VIEW vw_rules AS ( SELECT * FROM pg_rules ); COMMENT ON VIEW vw_rules IS ' Autor: O Peregrino View que lista rules --SELECT * FROM vw_rules;' ; ---view que mostra qual o limite para estouro maximo de transacoes (2 bilhoes) DROP VIEW IF EXISTS vw_max_burst_transactions; CREATE OR REPLACE VIEW vw_max_burst_transactions AS ( SELECT datname AS banco , AGE(datfrozenxid) AS transacoes , 2000000000 AS nao_ultrapassar_esse_limite FROM pg_database ); COMMENT ON VIEW vw_max_burst_transactions IS ' Autor: O Peregrino, baseado na documentação do PostgreSQL 8.0 View que mostra a quantidade de transacoes por banco/servidor realizadas. OBS: O DBA NAO PODE DEIXAR ULTRAPASSAR 2 BILHOES TRANSACOES!!! --SELECT * FROM vw_max_burst_transactions;' ; -- view para tamanho do bloco no disco DROP VIEW IF EXISTS vw_db_size_block; CREATE OR REPLACE VIEW vw_db_size_block AS ( SELECT * FROM pg_settings WHERE name='block_size' ); COMMENT ON VIEW vw_db_size_block IS ' Autor: O Peregrino View que mostra o tamanho do block no disco --SELECT * FROM vw_db_size_block;' ; -- view que mostra o nivel de isolamento de transacoes DROP VIEW IF EXISTS vw_isolation_level; CREATE OR REPLACE VIEW vw_isolation_level AS ( SELECT name AS isolamento_transacao, setting AS configurado FROM pg_settings WHERE name LIKE 'transaction%' ); COMMENT ON VIEW vw_isolation_level IS ' Autor: O Peregrino View que mostra o nivel de isolamento de transacao --SELECT * FROM vw_isolation_level;' ; -- view que mostra as dependencias entre tabelas DROP VIEW IF EXISTS vw_depend_tables; CREATE OR REPLACE VIEW vw_depend_tables AS ( SELECT esq_pai.nspname AS esquema_pai , tab_pai.relname AS tabela_pai , esq_filho.nspname AS esquema_filho , tab_filho.relname AS tabela_filho , esq_pai.nspname ||'.'|| tab_pai.relname AS esquema_tabela_pai , esq_filho.nspname || '.' || tab_filho.relname AS esquema_tabela_filho FROM pg_class AS tab_pai INNER JOIN pg_constraint ON tab_pai.oid = pg_constraint.confrelid INNER JOIN pg_class AS tab_filho ON pg_constraint.conrelid = tab_filho.oid LEFT JOIN pg_catalog.pg_namespace esq_pai ON esq_pai.oid = tab_pai.relnamespace LEFT JOIN pg_catalog.pg_namespace esq_filho ON esq_filho.oid = tab_filho.relnamespace WHERE 1=1 AND tab_pai.relkind = 'r' ORDER BY esq_pai.nspname ASC, tab_pai.oid ASC ); COMMENT ON VIEW vw_depend_tables IS ' Autor: O Peregrino View que mostra as dependencias entre as tabelas --SELECT * FROM vw_depend_tables;' ; --view que mostra as chaves estrangeiras, constraints DROP VIEW IF EXISTS vw_list_fk CASCADE; CREATE OR REPLACE VIEW vw_list_fk AS ( SELECT c.oid , n.nspname AS esquema , c.relname AS tabela , SUBSTRING(consrc,'\\((.*?)\\)') AS nome_atributo , SUBSTRING(consrc,'\REFERENCES (.*?)\\(') AS nome_esquema_tabela_referencia , conname AS nome_constraint , consrc AS conteudo_constraint FROM ( SELECT conname, pg_catalog.pg_get_constraintdef(oid) AS consrc, conrelid as relid FROM pg_catalog.pg_constraint WHERE contype = 'f' --AND pg_catalog.pg_get_constraintdef(oid) ILIKE '%tabela%' ) AS subq JOIN pg_catalog.pg_class c ON relid = c.oid JOIN pg_namespace AS n ON n.oid = c.relnamespace ); COMMENT ON VIEW vw_list_fk IS ' Autor: O Peregrino View que mostra as chaves estrangeiras, constraints das tabelas --SELECT * FROM vw_list_fk;' ; --view que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela DROP VIEW IF EXISTS vw_find_pk_not_first; CREATE OR REPLACE VIEW vw_find_pk_not_first AS ( SELECT nspname AS esquema , relname AS tabela , pg_attribute.attname AS nome_atributo , attnum AS ordem_atributo_tabela FROM pg_index , pg_class , pg_attribute , pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = ANY(pg_index.indkey) AND indisprimary AND attrelid = pg_class.oid AND attnum > 1 AND pg_class.oid IN ( SELECT oid FROM pg_catalog.pg_class WHERE relkind = 'r' AND nspname NOT IN ( 'pg_catalog' , 'information_schema' , 'pg_toast' ) ) ORDER BY esquema ASC , tabela ASC ); COMMENT ON VIEW vw_find_pk_not_first IS ' Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query) View que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela --SELECT * FROM vw_find_pk_not_first;' ; --view que efetua a mesma visualizacao de view vw_find com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco DROP VIEW IF EXISTS vw_find_dml; CREATE OR REPLACE VIEW vw_find_dml AS ( SELECT x.esquema , x.tabela , x.dono , x.comentario , x.registros , x.tamanho_sem_indices , x.tamanho_com_indices , x.total_de_leituras_sequencial , x.total_de_leituras_indexada , x.total_de_leituras , x.total_de_inserts , x.total_de_updates , x.total_de_deletes FROM ( SELECT * FROM vw_find INNER JOIN ( SELECT relname AS tabelax , SUM(seq_scan) AS total_de_leituras_sequencial , SUM(idx_scan) AS total_de_leituras_indexada , SUM(seq_scan+idx_scan) AS total_de_leituras , SUM(n_tup_ins) AS total_de_inserts , SUM(n_tup_upd) AS total_de_updates , SUM(n_tup_del) AS total_de_deletes FROM pg_stat_user_tables GROUP BY relname ) AS psut ON psut.tabelax = vw_find.tabela ) AS x ); COMMENT ON VIEW vw_find_dml IS ' Autor: O Peregrino View que efetua a mesma visualizacao de view vw_find com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco --SELECT * FROM vw_find_dml;' ; --view que mostra os dominios DROP VIEW IF EXISTS vw_list_domain CASCADE; CREATE OR REPLACE VIEW vw_list_domain AS ( SELECT n.nspname as esquema , t.typname as nome_dominio , pg_catalog.format_type(t.typbasetype, t.typtypmod) as formato_tipo_dominio, CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null' WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault ELSE '' END as modifier, pg_catalog.array_to_string(ARRAY( SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid ), ' ') as check FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'd' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND pg_catalog.pg_type_is_visible(t.oid) ORDER BY 1, 2 ); COMMENT ON VIEW vw_list_domain IS ' Autor: O Peregrino View que mostra os dominios cadastrados no banco SELECT * FROM vw_list_domain; '; DROP VIEW IF EXISTS vw_list_dict_data CASCADE; CREATE OR REPLACE VIEW vw_list_dict_data AS ( SELECT at.esquema , at.tabela , at.esquema || '.' || at.tabela AS esquema_tabela , at.ordem_atributo_tabela , at.nome_atributo , at.formato_tipo_atributo , at.nao_nulo , at.comentario_coluna , CASE WHEN (fk.nome_esquema_tabela_referencia IS NOT NULL) THEN 'TABELA: ' || fk.nome_esquema_tabela_referencia WHEN (dn.nome_dominio IS NOT NULL) THEN 'LISTA: ' || dn.modifier || dn.check END AS dominio , CASE WHEN (pk.nome_atributo IS NOT NULL) THEN 'X' END AS pri , CASE WHEN (fk.nome_atributo IS NOT NULL) THEN 'X' END AS est , CASE WHEN (unq.nome_atributo IS NOT NULL) THEN 'X' END AS can , at.padrao , ck.conteudo AS check FROM vw_find_attrib at LEFT JOIN vw_list_pk pk ON ( at.esquema = pk.esquema AND at.tabela = pk.tabela AND at.nome_atributo = pk.nome_atributo ) LEFT JOIN vw_list_check ck ON ( at.esquema = ck.esquema AND at.tabela = ck.tabela AND at.nome_atributo = ck.nome_atributo ) LEFT JOIN vw_list_unique unq ON ( at.esquema = ck.esquema AND at.tabela = unq.tabela AND at.nome_atributo = unq.nome_atributo ) LEFT JOIN vw_list_fk fk ON ( at.esquema = fk.esquema AND at.tabela = fk.tabela AND at.nome_atributo = fk.nome_atributo ) LEFT JOIN vw_list_domain dn ON ( at.formato_tipo_atributo = dn.nome_dominio ) WHERE 1=1 ORDER BY at.esquema ASC , at.tabela ASC , at.ordem_atributo_tabela ASC ); COMMENT ON VIEW vw_list_dict_data IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados SELECT * FROM vw_list_dict_data; '; DROP VIEW IF EXISTS vw_list_dict_data_docx CASCADE; CREATE OR REPLACE VIEW vw_list_dict_data_docx AS ( SELECT esquema , tabela , esquema_tabela AS "Esquema.Tabela" , ordem_atributo_tabela , nome_atributo AS "Atributo" , CASE WHEN (formato_tipo_atributo = 'integer') THEN 'inteiro' WHEN (formato_tipo_atributo = 'smallint') THEN 'inteiro_pesqueno' WHEN (formato_tipo_atributo = 'bigint') THEN 'inteiro_grande' WHEN (formato_tipo_atributo = 'numeric') THEN 'numerico' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN 'numerico' || SUBSTRING(formato_tipo_atributo FROM 8) WHEN (formato_tipo_atributo = 'real') THEN 'real' WHEN (formato_tipo_atributo = 'double precision') THEN 'dapla_precisao' WHEN (formato_tipo_atributo = 'date') THEN 'data' WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'time without time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'time with time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'text') THEN 'texto' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN 'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18) WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN 'caracter' || SUBSTRING(formato_tipo_atributo FROM 10) WHEN (formato_tipo_atributo = '"char"') THEN 'caracter' WHEN (formato_tipo_atributo = 'bpchar') THEN 'caracter' WHEN (formato_tipo_atributo = 'boolean') THEN 'logico' WHEN (formato_tipo_atributo = 'bytea') THEN 'objeto_binario_grande' ELSE formato_tipo_atributo END AS "Tipo" , CASE WHEN (nao_nulo IS TRUE) THEN 'X' END AS "Não Nulo" , comentario_coluna AS "Descrição" , dominio AS "Dominio" , pri AS "PRI" , est AS "EST" , can AS "CAN" , CASE WHEN (padrao = 'falsevalue()') THEN 'falso' WHEN (padrao = 'false') THEN 'falso' WHEN (padrao = 'truevalue()') THEN 'verdadeiro' WHEN (padrao = 'true') THEN 'verdadeiro' WHEN (padrao = 'now()') THEN 'agora()' WHEN (padrao = '(\'now\'::text)::date') THEN 'agora() :: data' WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN 'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8) WHEN (POSITION('::' IN padrao) > 0) THEN SUBSTRING(padrao,'(.*?)\\::') ELSE padrao END AS "Padrão" , "check" AS "Check" FROM vw_list_dict_data ); COMMENT ON VIEW vw_list_dict_data_docx IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao SELECT * FROM vw_list_dict_data_docx; '; --view que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, usa funcao de usuario sp_tradutor, sp_rm_special_chars, sp_comment_domain, verificar dependencias DROP VIEW IF EXISTS vw_list_dict_data_doc; CREATE OR REPLACE VIEW vw_list_dict_data_doc AS ( SELECT esquema , tabela , ordem_atributo_tabela , "Esquema.Tabela" , "Atributo" , "Tipo" , "Não Nulo" , "Descrição" , CASE WHEN "Domínio" IS NOT NULL THEN "Domínio" || CHR(10) || 'DOMÍNIO DISCRETO: ' || COALESCE(sp_comment_domain("Descrição"),'') ELSE COALESCE(sp_comment_domain("Descrição"),'') END AS "Domínio" , "PRI" , "EST" , "CAN" , "Padrão" , "Check" FROM ( SELECT esquema , tabela , esquema_tabela AS "Esquema.Tabela" , ordem_atributo_tabela , nome_atributo AS "Atributo" , CASE WHEN (formato_tipo_atributo = 'integer') THEN 'inteiro' WHEN (formato_tipo_atributo = 'smallint') THEN 'inteiro_pesqueno' WHEN (formato_tipo_atributo = 'bigint') THEN 'inteiro_grande' WHEN (formato_tipo_atributo = 'numeric') THEN 'numerico' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN 'numerico' || SUBSTRING(formato_tipo_atributo FROM 8) WHEN (formato_tipo_atributo = 'real') THEN 'real' WHEN (formato_tipo_atributo = 'double precision') THEN 'dapla_precisao' WHEN (formato_tipo_atributo = 'date') THEN 'data' WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN 'data_hora' WHEN (formato_tipo_atributo = 'time without time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'time with time zone') THEN 'hora' WHEN (formato_tipo_atributo = 'text') THEN 'texto' WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN 'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18) WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN 'caracter' || SUBSTRING(formato_tipo_atributo FROM 10) WHEN (formato_tipo_atributo = '"char"') THEN 'caracter' WHEN (formato_tipo_atributo = 'bpchar') THEN 'caracter' WHEN (formato_tipo_atributo = 'boolean') THEN 'logico' WHEN (formato_tipo_atributo = 'bytea') THEN 'objeto_binario_grande' ELSE formato_tipo_atributo END AS "Tipo" , CASE WHEN (nao_nulo IS TRUE) THEN 'X' END AS "Não Nulo" , sp_rm_special_chars(comentario_coluna) AS "Descrição" , dominio AS "Domínio" , pri AS "PRI" , est AS "EST" , can AS "CAN" , CASE WHEN (padrao = 'falsevalue()') THEN 'falso' WHEN (padrao = 'false') THEN 'falso' WHEN (padrao = 'truevalue()') THEN 'verdadeiro' WHEN (padrao = 'true') THEN 'verdadeiro' WHEN (padrao = 'now()') THEN 'agora()' WHEN (padrao = '(\'now\'::text)::date') THEN 'agora() :: data' WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN 'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8) WHEN (POSITION('::' IN padrao) > 0) THEN SUBSTRING(padrao,'(.*?)\\::') ELSE padrao END AS "Padrão" , sp_tradutor("check") AS "Check" FROM vw_list_dict_data ) AS z ); COMMENT ON VIEW vw_list_dict_data_doc IS ' Autor: O Peregrino View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, esta função sp_tradutor, sp_rm_special_chars, sp_comment_domain, ver dependencias. SELECT * FROM vw_list_dict_data_doc; '; --view que lista a ordem de criação das tabelas. DROP VIEW IF EXISTS vw_order_tables_created; CREATE OR REPLACE VIEW vw_order_tables_created AS ( SELECT tab_pk.relname AS pk, tab_fk.relname AS fk FROM pg_class AS tab_pk INNER JOIN pg_constraint ON tab_pk.oid = pg_constraint.confrelid INNER JOIN pg_class AS tab_fk ON pg_constraint.conrelid = tab_fk.oid WHERE tab_pk.relkind = 'r' ORDER BY tab_pk.oid ASC ); COMMENT ON VIEW vw_order_tables_created IS ' Autor: O Peregrino View que lista a ordem de criação das tabelas. OBS: Em testes. SELECT * FROM vw_order_table_created; ' ;
Assinar:
Postagens (Atom)