rapimo

-- sample data
set max_parallel_workers_per_gather=1;

CREATE TABLE data AS
SELECT n, chr(n) as g, i FROM generate_series(97,122) n, generate_series(1,1e6) i ORDER BY i;

SELECT CASE g
    WHEN 'a' THEN 'one'
    WHEN 'b' THEN 'one'
    WHEN 'c' THEN 'one'

rapimo

CREATE EXTENSION istore;
CREATE TABLE data AS
SELECT (i%30)::int as event_id, (random()*100 + 10)::int as events
FROM generate_series(1,1e6) i;

SELECT
SUM(events) FILTER (WHERE event_id = 0) as "0_events",
SUM(events) FILTER (WHERE event_id = 1) as "1_events",
SUM(events) FILTER (WHERE event_id = 2) as "2_events",
SUM(events) FILTER (WHERE event_id = 3) as "3_events",

rapimo

\timing

CREATE EXTENSION istore;

CREATE TABLE foo AS SELECT i%10 as id, i FROM generate_series(1,1e7::int) i;

SELECT CASE 
WHEN id = 1 THEN 10 WHEN id = 2 THEN 20 WHEN id = 3 THEN 30 WHEN id = 4 THEN 40 WHEN id = 5 THEN 50 
WHEN id = 6 THEN 60 WHEN id = 7 THEN 70 WHEN id = 8 THEN 80 WHEN id = 9 THEN 90 
ELSE 0 END, 

rapimo

This note explains how to build Postgres from source and setup to debug it using LLDB on a Mac. I used this technique to research this article:
http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals

1. Shut down existing postgres if necessary - you don’t want to mess up your existing DB or work :)

$ ps aux | grep postgres
pat               456   0.0  0.0  2503812    828   ??  Ss   Sun10AM   0:11.59 postgres: stats collector process       
pat               455   0.0  0.0  2649692   2536   ??  Ss   Sun10AM   0:05.00 postgres: autovacuum launcher process       
pat               454   0.0  0.0  2640476    304   ??  Ss   Sun10AM   0:00.74 postgres: wal writer process       
pat               453   0.0  0.0  2640476    336   ??  Ss   Sun10AM   0:00.76 postgres: writer process       

rapimo

CREATE TABLE foo(
  id integer
);

CREATE FUNCTION foo_trigger() RETURNS trigger LANGUAGE plpgsql AS $_$
DECLARE
  r foo%rowtype;
BEGIN
  SELECT NEW.* INTO r;
  RETURN r;

rapimo

require 'active_record'
require 'parallel'
ActiveRecord::Base.establish_connection(adapter: 'postgresql', pool: 25, username: 'postgres', host: 'localhost', database: 'postgres')
ActiveRecord::Base.connection.execute "DROP DATABASE IF EXISTS segfault"
ActiveRecord::Base.connection.execute "CREATE DATABASE segfault"
ActiveRecord::Base.establish_connection(adapter: 'postgresql', pool: 25, username: 'postgres', host: 'localhost', database: 'segfault')
ActiveRecord::Base.connection.execute <<-SQL
CREATE TABLE test_data(
  tracker_id  integer not null,
  created_at timestamp,

rapimo

#!/bin/sh

# script to add apt.postgresql.org to sources.list

# from command like
CODENAME="$1"
# lsb_release is the best interface, but not always available
if [ -z "$CODENAME" ]; then
    CODENAME=$(lsb_release -cs 2>/dev/null)
fi

rapimo

CREATE TABLE small (
    a smallint,
    b smallint,
    c integer,
    d integer,
    e date
)

CREATE TABLE big (
    c integer,

rapimo

# create ramdisk of 500MB (500 x 2048)
diskutil erasevolume HFS+ "ramdisk" `hdiutil attach -nomount ram://1024000`
# create pg tablespace to use ramdisk
psql -c "CREATE TABLESPACE ramdisk LOCATION '/Volumes/ramdisk/'" postgres
# delete ramdisk
hdiutil eject /Volumes/ramdisk/

rapimo

#!/bin/sh

BREW_POSTGRES_DIR=`brew info postgres | awk '{print $1"/bin"}' | grep "/postgresql/"`
LION_POSTGRES_DIR=`which postgres | xargs dirname`
LION_PSQL_DIR=`which psql | xargs dirname`

sudo mkdir -p $LION_POSTGRES_DIR/archive
sudo mkdir -p $LION_PSQL_DIR/archive

for i in `ls $BREW_POSTGRES_DIR`