This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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", |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE small ( | |
a smallint, | |
b smallint, | |
c integer, | |
d integer, | |
e date | |
) | |
CREATE TABLE big ( | |
c integer, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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` |
NewerOlder