matheusoliveira

postgres=# CREATE TEMP TABLE foo(a int);
CREATE TABLE
Time: 301.708 ms
postgres=# CREATE TEMP TABLE bar(a int);
CREATE TABLE
Time: 2.748 ms
postgres=# SELECT foo.* FROM foo LEFT JOIN bar ON foo.a = bar.a;
 a 
---
(0 rows)

matheusoliveira

/*

Recheck all the FK constraints to see if they are "still" valids. They
may have been invalid for two reasons:

1) Someone disabled all triggers by mystake
2) A PostgreSQL bug (as one corrected on 9.3.3 - http://www.postgresql.org/docs/9.3/static/release-9-3-3.html)

To run

matheusoliveira

#!/bin/sh

JOBS=8
export PGDATABASE=<your database name>

psql -A0Xtc "SELECT oid::regclass::text FROM pg_class WHERE relkind IN ('r','m')" | xargs -P $JOBS -0 -I {} bash -c 'echo "`date`: started {}"; vacuumdb --analyze --table={} && echo "`date`: {}: OK" || echo "`date`: {}: FAILED!"'

matheusoliveira

SELECT (SELECT
	string_agg(
		CASE
			WHEN lag IS NULL THEN to_char(code, 'FM000')
			WHEN lag <> code - 1 THEN to_char(code, '","FM000')
			WHEN lead IS NULL OR lead <> code + 1 THEN to_char(code, '"-"FM000')
		END
	, '')
FROM (SELECT code, lead(code) OVER(ORDER BY code), lag(code) OVER(ORDER BY code) FROM unnest((string_to_array(replace(code, ' ', ''), ','))::INT[]) AS code ORDER BY code) t1 WHERE lead IS NULL OR lag IS NULL OR lead <> code + 1 OR lag <> code - 1)
FROM (

matheusoliveira

DO $$
DECLARE
	v_schema text;
	v_table text;
	v_column text;
	v_look_for text := 'foo';
	v_ret bool;
BEGIN
	FOR v_schema, v_table IN
		SELECT table_schema, table_name

matheusoliveira

while true; do ( psql -AXtqc "SELECT 'atstart='||extract(epoch from now())||E'\n'||'before='||sum(xact_commit + xact_rollback) FROM pg_stat_database;"; sleep 1; psql -AXtqc "SELECT 'atend='||extract(epoch from now())||E'\n'||'after='||sum(xact_commit + xact_rollback) FROM pg_stat_database;"; echo -e 'after-before\natend-atstart\nafter\nbefore\n(after-before) / (atend-atstart)' ) | bc -l; echo; done

matheusoliveira

CREATE TABLE venda(venda_id serial primary key, ...);

CREATE TABLE venda_item(venda_id integer references venda(venda_id), num_item integer, ...);

CREATE OR REPLACE FUNCTION tg_item_venda_sequencia()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
    -- Bloqueia acesso concorrente!

matheusoliveira

DO $$
DECLARE
        r RECORD;
        ret BOOLEAN;
        cmd TEXT;
        check_table regclass = '<table name here>'::regclass;
BEGIN
        FOR r IN
                SELECT
                        c.conname,

matheusoliveira

/* One random row from table "tbl" */
WITH RECURSIVE r AS (
	SELECT NULL::int AS id, min(id) AS min_id, max(id) AS max_id, 0 AS cnt
	FROM tbl
	UNION ALL
	SELECT tbl.id, r.min_id, r.max_id, r.cnt + 1
	FROM r LEFT JOIN tbl
		ON tbl.id = (SELECT floor(random() * (r.max_id - r.min_id + 1))::int)
	WHERE r.id IS NULL
)

matheusoliveira

/*-------------------------------------------------------------------------
 *
 * partition_insert_trigger.c
 *    A function to redirect insertions from parent table to child
 *	  partitions.
 *	  
 *	  IMPORTANT: This is just an experimental code, and is based on
 *	             PostgreSQL  version 9.2.1, and will probably not work
 *	             on different versions (although not tested)
 *