Skip to content

Instantly share code, notes, and snippets.

View ringerc's full-sized avatar

Craig Ringer ringerc

View GitHub Profile
CREATE UNLOGGED TABLE exclude_test(id integer primary key);
INSERT INTO exclude_test(id) SELECT generate_series(1,50000);
CREATE UNLOGGED TABLE exclude AS SELECT x AS item FROM generate_series(1,40000,4) x;
-- Horrific AND list, takes 80s to plan and execute here:
EXPLAIN ANALYZE SELECT id FROM exclude_test WHERE id <> 1 AND id <> 5 AND id <> 9 AND id <> 13 AND id <> 17 AND id <> 21 AND id <> 25 AND id <> 29 AND id <> 33 AND id <> 37 AND id <> 41 AND id <> 45 AND id <> 49 AND id <> 53 AND id <> 57 AND id <> 61 AND id <> 65 AND id <> 69 AND id <> 73 AND id <> 77 AND id <> 81 AND id <> 85 AND id <> 89 AND id <> 93 AND id <> 97 AND id <> 101 AND id <> 105 AND id <> 109 AND id <> 113 AND id <> 117 AND id <> 121 AND id <> 125 AND id <> 129 AND id <> 133 AND id <> 137 AND id <> 141 AND id <> 145 AND id <> 149 AND id <> 153 AND id <> 157 AND id <> 161 AND id <> 165 AND id <> 169 AND id <> 173 AND id <> 177 AND id <> 181 AND id <> 185 AND id <> 189 AND id <> 193 AND id <> 197 AND id <> 201 AND id <> 205 AND id <>
@ringerc
ringerc / simple_spi_boilerplate.c
Last active September 9, 2021 04:06
SPI bgworker boilerplate
/* Simpler version that assumes there is no active tx or snapshot and SPI isn't connected yet */
const char * some_sql = "SELECT 1;";
/* setup */
Assert(!IsTransactionState());
StartTransactionCommand();
SetCurrentStatementStartTimestamp();
SPI_connect();
PushActiveSnapshot(GetTransactionSnapshot());
@ringerc
ringerc / schema.sql
Created February 8, 2016 14:34
Demo Python script for replicating to Solr
CREATE TABLE book_lines
(
id serial primary key,
book_line text not null
);
BEGIN;
SET LOCAL bdr.skip_ddl_locking=on;
SET LOCAL bdr.skip_ddl_replication=on;
CREATE OR REPLACE FUNCTION public.simple_log_row()
RETURNS trigger LANGUAGE plpgsql
SET search_path = 'pg_catalog'
AS $$
BEGIN
@ringerc
ringerc / gist:d4a8fe97f5fd332d8b883d596d61e257
Created August 11, 2017 02:56
Failover slots with streaming rep proto
Phys rep Phys rep
using phys using
slot "B" phys slot "C"
+-------+ +--------+ +-------+
T | A <^--------+ B <---------+ C |
I | | | | | |
M +-------+ +--------+ +-------+
E | | |
| | | |CREATEs
| | | |logical slot X
From a7a69e8dc87504163222ba2570bda6920114a5c3 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@redacted>
Date: Sun, 26 Jun 2016 19:58:48 +0800
Subject: [PATCH] SPK utils build fixes
---
.gitignore | 4 ++++
src/SPK/File_IO.cpp | 2 ++
src/SPK/Makefile | 9 +++++----
src/SPK/String.h | 3 +++
@ringerc
ringerc / pg_elephant.c
Created May 18, 2016 13:16
pg_elephant()
#include "postgres.h"
#include "fmgr.h"
PG_MODULE_MAGIC;
PGDLLEXPORT Datum pg_elephant(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_elephant);
static const char * const elephant =
"............MMMMMMMMMMM8..........MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM ...........\n"
@ringerc
ringerc / gist:7429182
Created November 12, 2013 11:04
Parse, rewrite and plan trees of a simple SELECT from a view, showing subquery expansion
regress=> SELECT * FROM t_even;
LOG: parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
@ringerc
ringerc / gist:7423819
Last active December 28, 2015 01:49
Diff of parse, rewritten and plan trees from "DELETE FROM t;" against "DELETE FROM t USING t2 WHERE t.id = t2.id;"
--- /tmp/simple-delete 2013-11-12 09:26:55.077406915 +0800
+++ /tmp/join-delete 2013-11-12 09:49:06.172045074 +0800
@@ -1,4 +1,4 @@
-regress=> DELETE FROM t;
+regress=> DELETE FROM t USING t2 WHERE t.id = t2.id;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
@@ -28,9 +28,27 @@
:lateral false
regress=> DELETE FROM t;
LOG: parse tree:
DETAIL: {QUERY
:commandType 4
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 1
:hasAggs false
:hasWindowFuncs false