Skip to content

Instantly share code, notes, and snippets.

gurjeet / gist:088a91d6f45cfe1e842d
Last active August 29, 2015 14:01
Why timestamp(0) rounding is horrible
edb=# select now() as "time", now()::timestamptz(0) as "time_round", date_trunc('seconds', now()) as "time_trunc";
time | time_round | time_trunc
21-MAY-14 14:19:04.549852 -04:00 | 21-MAY-14 14:19:05 -04:00 | 21-MAY-14 14:19:04 -04:00
(1 row)
gurjeet / gist:31c17ae70766f0de084f
Created June 25, 2014 15:07
PPAS bug: WARNING: unrecognized node type: 306
create table public.traffic_analysis(date_text text, load_avg_1_min double precision, hostname text not null, ts timestamp);
create type public.weekdays as enum (
gurjeet / test_case
Created July 7, 2014 19:20
Postgres' join-based UPDATE updates just once instead of multiple times
/* Item names */
create table item (i_id int, i_name text);
/* Stock counts of items */
create table stock (s_i_id int, s_quantity int);
/* All rows here represent one order; each row is its line items */
create table order_line (ol_i_id int, ol_quantity int);
insert into item values(1, 'item 1');
gurjeet / weird_interval_output
Last active August 29, 2015 14:03
Postgres Weird interval representation
postgres=# select '6 days 23:45:00'::interval - '6 days 23:23:00'::interval;
(1 row)
postgres=# select '00:46:00'::interval - '00:24:00'::interval;
[bart] #global section
backup_path = /path/to/backup/dir
pg_basebackup_path = /path/to/pg_basebackup
log_file= /path/to/logfile
[Server1] # name the server being configured.
gurjeet / DropTablespaceProblem
Created November 9, 2010 09:27
DROP TABLESPACE with crash-resistance (remove unused files)
We are facing a problem in dropping a tablespace after crash recovery. The
recovery starts from the last checkpoint, but the tables that were created by
a transaction in a tablespace before the checkpoint are still lying around; the
transaction had not finished by the time of crash.
After recovery, when the app tries to drop the tablespace, the command fails
since the tablespace directory is not empty.
Solving this problem has become quite critical since the the platform where
Postgres is being used is supposed to run unattended for a few years. The
gurjeet / Quotes_tech.txt
Created December 13, 2010 14:04
Quotes heard in technical discussions
This proposal strikes me as something mysql would do, not the standards committee.
- Tom Lane responding to an interpretation of SQL:2011.
DBAs hate getting eaten by crocodiles.
- Robert Haas.
Get on the stick, Cupertino ... everybody else had this fixed six months ago.
- Tom Lane; prompting Apple to fix their OpenSSL libraries.
sfdisk delete a partition
gurjeet / gist:3887248
Created October 14, 2012 04:03 — forked from pedromtavares/gist:1136121
nodejs streamer
var http = require('http');
var url = ''; // URL to a known Icecast stream
var icecast = require('icecast-stack');
var stream = icecast.createReadStream(url);
// var radio = require("radio-stream");
// var stream = radio.createReadStream(url);
gurjeet / build-farm.conf.diff
Created January 4, 2013 20:01
The diff of what Andrew Dunstan changed on my Windows Server 2012 + MinGW to get proper builds of Postgres.
Administrator@AMAZONA-AK1BTP3 ~
$ diff -c -r del/build-farm-4.9/ bf/
diff -c -r del/build-farm-4.9//build-farm.conf bf//build-farm.conf
*** del/build-farm-4.9//build-farm.conf Mon Nov 12 09:47:55 2012
--- bf//build-farm.conf Thu Jan 3 20:51:07 2013
*** 35,41 ****
tar_log_cmd => undef, # default is "tar -z -cf runlogs.tgz *.log"
# replacement must have the same effect
# must be absolute, can be either Unix or Windows style for MSVC