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)
 *

matheusoliveira

CREATE OR REPLACE FUNCTION public.json_append(data json, insert_data json)
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
    SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
    FROM (
        SELECT * FROM json_each(data)
        UNION ALL
        SELECT * FROM json_each(insert_data)

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

# Per database
psql -A0Xtc "SELECT datname FROM pg_database WHERE datname <> 'template0'" | xargs -P 12 -0 -I ? bash -c 'echo "`date`: started ?"; echo -e "SET vacuum_freeze_table_age TO 0;\\nVACUUM;" | psql -qX "?"; echo "`date`: finish ?"'

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

# Vaccum (kind-of) freeze per table - prevents bug fixed on 9.3.2 (and some 9.1.x, 9.2.x)
psql -A0Xtc "SELECT replace(replace(oid::regclass::text, '\\', '\\\\'), '\"', '\\\"') FROM pg_class WHERE relkind IN ('r','m')" | xargs -P 12 -0 -I {} bash -o pipefail -c 'echo "`date`: started {}"; echo -e "SET vacuum_freeze_table_age TO 0;\\nVACUUM {};" | psql -qX && echo "`date`: {}: OK" || echo "`date`: {}: FAILED!"' | tee -a /tmp/vacuum.log

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!