- Saving, searching
- Geospatial queries (e.g. which nomads have checked in within x kilometers of point x)
- Full-text search, including bios
- Enforcing data access permissions
- Send emails
Joe Nelson begriffs
begriffs
/ postgresql95-setup
Created
April 22, 2016 17:42
This file contains hidden or 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 | |
| # | |
| # postgresql-setup Initialization and upgrade operations for PostgreSQL | |
| # PGVERSION is the full package version, e.g., 9.5.0 | |
| # Note: the specfile inserts the correct value during package build | |
| PGVERSION=9.5.2 | |
| # PGMAJORVERSION is major version, e.g., 9.5 (this should match PG_VERSION) | |
| PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'` | |
| # PGENGINE is the directory containing the postmaster executable |
begriffs
/ rpm-try-citus.md
Last active
April 8, 2016 00:01
Using the RPM packages to simulate the try-citus development machine style
sudo yum install -y https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-ami201503-95-9.5-2.noarch.rpm
sudo yum install -y citus_95
mkdir data{,.9700}
/usr/pgsql-9.5/bin/initdb data
/usr/pgsql-9.5/bin/initdb data.9700
echo 'localhost 9700' >> data/pg_worker_list.conf
begriffs
/ gist:d52482e4b6f01648a721
Created
March 24, 2016 22:58
This file contains hidden or 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
| $ pg_config | |
| BINDIR = /usr/local/Cellar/postgresql/9.5.1/bin | |
| DOCDIR = /usr/local/Cellar/postgresql/9.5.1/share/doc/postgresql | |
| HTMLDIR = /usr/local/Cellar/postgresql/9.5.1/share/doc/postgresql | |
| INCLUDEDIR = /usr/local/Cellar/postgresql/9.5.1/include | |
| PKGINCLUDEDIR = /usr/local/Cellar/postgresql/9.5.1/include | |
| INCLUDEDIR-SERVER = /usr/local/Cellar/postgresql/9.5.1/include/server | |
| LIBDIR = /usr/local/lib | |
| PKGLIBDIR = /usr/local/lib/postgresql | |
| LOCALEDIR = /usr/local/Cellar/postgresql/9.5.1/share/locale |
begriffs
/ postgres-9.5.1-lib
Created
March 23, 2016 22:29
This file contains hidden or 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
| $ tree /usr/local/Cellar/postgresql/9.5.1/lib | |
| /usr/local/Cellar/postgresql/9.5.1/lib | |
| ├── libecpg.6.7.dylib | |
| ├── libecpg.6.dylib -> libecpg.6.7.dylib | |
| ├── libecpg.a | |
| ├── libecpg.dylib -> libecpg.6.7.dylib | |
| ├── libecpg_compat.3.7.dylib | |
| ├── libecpg_compat.3.dylib -> libecpg_compat.3.7.dylib | |
| ├── libecpg_compat.a | |
| ├── libecpg_compat.dylib -> libecpg_compat.3.7.dylib |
This file contains hidden or 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
| QUERY PLAN | |
| -------------------------------------------------------------------------------------------------- | |
| Merge Join (cost=186.30..212.30 rows=500 width=6) | |
| Merge Cond: (film.ctid = (((('(0,'::text || (s.i)::text) || ')'::text))::tid)) | |
| -> Sort (cost=113.83..116.33 rows=1000 width=6) | |
| Sort Key: film.ctid | |
| -> Seq Scan on film (cost=0.00..64.00 rows=1000 width=6) | |
| -> Sort (cost=72.48..72.98 rows=200 width=4) | |
| Sort Key: (((('(0,'::text || (s.i)::text) || ')'::text))::tid) | |
| -> Unique (cost=59.83..64.83 rows=200 width=4) |
begriffs
/ where_is.sql
Last active
February 23, 2016 05:56
This file contains hidden or 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
| -- Find location of an IP address by reading ip2location_db5 | |
| -- (available from https://lite.ip2location.com/database-ip-country-region-city-latitude-longitude) | |
| -- | |
| -- For instance the following returns San Francisco | |
| -- select where_is('23.235.47.133'); | |
| CREATE OR REPLACE FUNCTION public.where_is(ip inet) | |
| RETURNS SETOF ip2location_db5 | |
| LANGUAGE plpgsql | |
| AS $function$ |
begriffs
/ reset_password.sql
Created
February 15, 2016 05:51
This file contains hidden or 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 or replace function | |
| reset_password(email text, token uuid, pass text) | |
| returns void | |
| language plpgsql | |
| as $$ | |
| declare | |
| tok uuid; | |
| begin | |
| if exists(select 1 from basic_auth.tokens | |
| where tokens.email = reset_password.email |
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 23 columns, instead of 6 in line 6.
This file contains hidden or 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
| 2016-02-08 20:59:30.004 PST,,,18427,,56b97201.47fb,20,,2016-02-08 20:58:41 PST,1/5,0,DEBUG,00000,"StartTransaction",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18427,,56b97201.47fb,21,,2016-02-08 20:58:41 PST,1/5,0,DEBUG,00000,"name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: ",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18427,,56b97201.47fb,22,,2016-02-08 20:58:41 PST,1/5,0,DEBUG,00000,"CommitTransaction",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18427,,56b97201.47fb,23,,2016-02-08 20:58:41 PST,1/5,0,DEBUG,00000,"name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: ",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18428,,56b97201.47fc,29,,2016-02-08 20:58:41 PST,,0,DEBUG,00000,"received inquiry for database 0",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18428,,56b97201.47fc,30,,2016-02-08 20:58:41 PST,,0,DEBUG,00000,"writing stats file ""pg_stat_tmp/global.stat""",,,,,,,,,"" | |
| 2016-02-08 20:59:30.004 PST,,,18428,,56b97201.47fc |
begriffs
/ onlylocks.csv
Created
February 8, 2016 23:32
We can make this file beautiful and searchable if this error is corrected: Unclosed quoted field in line 6.
This file contains hidden or 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
| 2016-02-08 15:26:19.809 PST,"j","demo1",93674,"::1:50887",56b9241a.16dea,1,"idle",2016-02-08 15:26:18 PST,2/26,0,LOG,00000,"statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;",,,,,,,,,"" | |
| 2016-02-08 15:26:20.311 PST,"j","demo1",93674,"::1:50887",56b9241a.16dea,2,"idle",2016-02-08 15:26:18 PST,2/27,0,LOG,00000,"statement: begin ISOLATION LEVEL READ COMMITTED;",,,,,,,,,"" | |
| 2016-02-08 15:26:20.812 PST,"j","demo1",93674,"::1:50887",56b9241a.16dea,3,"idle in transaction",2016-02-08 15:26:18 PST,2/27,0,LOG,00000,"statement: set local role 'j';",,,,,,,,,"" | |
| 2016-02-08 15:26:22.819 PST,"j","demo1",93675,"::1:50895",56b9241d.16deb,1,"idle",2016-02-08 15:26:21 PST,3/4,0,LOG,00000,"statement: SET client_encoding = 'UTF8';SET client_min_messages TO WARNING;",,,,,,,,,"" | |
| 2016-02-08 15:26:23.319 PST,"j","demo1",93675,"::1:50895",56b9241d.16deb,2,"idle",2016-02-08 15:26:21 PST,3/5,0,LOG,00000,"statement: begin ISOLATION LEVEL READ COMMITTED;",,,,,,,,,"" | |
| 2016-02-08 15:26:25.327 PST,"j","demo1",93676,"::1 |