- Pressing 'f' toggles fullscreen viewing
- Pressing 'w' toggles widescreen
- Pressing 'o' toggles overview mode
- Pressing 'ESC' toggles off these goodies
Slides produced using code from Google io-2012.
Slides produced using code from Google io-2012.
Full project (small-ish)
Match on:
Order by:
Then:
-- Table: teacher.skills id longcode NOT NULL tid int NOT NULL REFERENCES teachers ON DELETE CASCADE ages int4range NOT NULL REFERENCES lookup.age_ranges (ages) subject longcode NOT NULL REFERENCES lookup.subjects (code) -- Sample data 1 101 [5,11) QTS 2 102 [11,16) MATHS 3 102 [16,18) CHEM
-- Table: teacher.availability wkend date NOT NULL tid int NOT NULL REFERENCES teachers ON DELETE CASCADE avail char(7) NOT NULL -- Sample data 2013-07-07 101 aaaaaaa 2013-07-14 101 HHHHHHH
UK Postcodes
data_20_map.sql data_21_map_traveltimes.sql ... rebuild_db.sh run_tests.sh ... schema_00_users.sql schema_01_database.sql schema_03_types.sql ... t/ testdata/
-- schema_40_teacher.sql BEGIN; SET search_path=teacher; -- ... CREATE TABLE lookup.age_ranges ( ages int4range NOT NULL, description text NOT NULL, PRIMARY KEY (ages) ); -- ... -- ROLLBACK; COMMIT;
BEGIN; COPY lookup.age_ranges (ages,description) FROM stdin; [1,5) Nursery [5,11) Primary [11,16) Secondary [16,18) F.E. \. // etc -- ROLLBACK; COMMIT;
Richard ❤ Rollback
Objectives:
Options:
PSQLCMD=${PSQLCMD:-/usr/lib/postgresql/9.2/bin/psql} PSQL="$PSQLCMD -X -h $PGHOST -p $PGPORT -U $DBUSER -d $DBNAME -v dbname=$DBNAME -v username=$DBUSER -v appuser=$APPUSER -v superuser=$SUPERUSER -v home=$APPDIR/ -v ON_ERROR_STOP=1 -q --pset pager=off" SU_PSQL=...as above but for superuser
# Halt on any error set -e if [ $REBUILD = "Y" ]; then echo "Creating db..." $SU_PSQL -f schema_01_database.sql # $PSQL -f schema_03_types.sql $PSQL -f schema_20_map_postcodes.sql
Simple wrapper around pg_prove:
BEGIN; SET search_path = pgtap, whatever; SELECT plan(3); SELECT ok(1 < 2, 'One less than two - phew!'); SELECT is(2, 1+1, 'Addition also ok'); SELECT throws_like('SELECT 1/0', '%division%', 'Dividing by zero still bad'); ROLLBACK;
Perl script to generate suitable amounts of test data
Reminder:
How to index?
CREATE TABLE search_flags ( tid int NOT NULL REFERENCES teachers ON DELETE CASCADE, is_active boolean NOT NULL, flags int[] NOT NULL, PRIMARY KEY (tid) );
-- gist|gin__int_ops is from the intarray extension CREATE INDEX tch_search_flags_idx ON teacher.search_flags USING gist (flags gist__int_ops) WHERE is_active;
SELECT tid FROM teacher.search_flags WHERE is_active AND flags @> ARRAY[1,2,3,1011,2035,10739]; QUERY PLAN ---------------------------------------------------------------------------------------- Bitmap Heap Scan on search_flags (cost=641.79..9653.96 rows=2502 width=4) Recheck Cond: ((flags @> '{1,2,3,1011,2035,10739}'::integer[]) AND is_active) -> Bitmap Index Scan on tch_search_flags_idx (cost=0.00..641.16 rows=2502 width=0) Index Cond: (flags @> '{1,2,3,1011,2035,10739}'::integer[])
CREATE FUNCTION map.pc_to_pt(tgt_pcode varchar(10)) RETURNS point LANGUAGE plpgsql AS $$ DECLARE e int; n int; BEGIN SELECT easting,northing INTO e,n FROM map.postcodes WHERE pcode = tgt_pcode; IF NOT FOUND THEN e := 0; n := 0; END IF; RETURN point(e,n); END; $$ IMMUTABLE;
WITH pcs AS (VALUES ('SE13 6RY'), ('SE9 3JN'), ('W12 9PT')) SELECT p0.column1 AS from_pc, p1.column1 AS to_pc, (map.pc_to_pt(p0.column1) <-> map.pc_to_pt(p1.column1)): from_pc | to_pc | dist ----------+----------+------- SE13 6RY | SE13 6RY | 0 SE13 6RY | SE9 3JN | 4877 SE13 6RY | W12 9PT | 17020 SE9 3JN | SE13 6RY | 4877 SE9 3JN | SE9 3JN | 0 SE9 3JN | W12 9PT | 21895 W12 9PT | SE13 6RY | 17020 W12 9PT | SE9 3JN | 21895 W12 9PT | W12 9PT | 0 (9 rows)
BEGIN; SET search_path = pgtap, teacher; SELECT plan(3); -- Bad postcode should give (0,0) SELECT ok( map.pc_to_pt('ZZ1 1ZZ') ~= point(0,0), 'Bad postcode is 0,0' ); -- NW postcode should be west of SE postcode SELECT ok( (map.pc_to_pt('NW1 8NH'))[0] < (map.pc_to_pt('SE13 7RY'))[0], 'NW is west of SE' ); -- NW postcode should be north of SE postcode SELECT ok( (map.pc_to_pt('NW1 8NH'))[1] > (map.pc_to_pt('SE13 7RY'))[1], 'NW is north of SE' ); ROLLBACK;
CREATE FUNCTION teacher.booking_candidates( tgt_pcode varchar, tgt_age int, tgt_subj varchar, tgt_days date[], max_results int = 10) RETURNS SETOF teacher.teachers ...
... BEGIN tgt_flags := [combine args into flags] RETURN QUERY SELECT t.* FROM teacher.teachers t JOIN teacher.search_flags sf USING (tid) WHERE is_active AND sf.flags @> tgt_flags ORDER BY map.pc_to_pt(postcode) <-> map.pc_to_pt(tgt_pcode) LIMIT max_results; END; $$;
SELECT * FROM booking_candidates('SE1 8SW', 9, 'QTS', '{2013-07-08,2013-07-09}'); tid | first_name | last_name | telnum | email | postcode | -------+------------+-----------+---------------+---------------------------+----------+ 31691 | Ray | Leopard | 071 0161 0691 | ray.leopard@google.com | SE17 3DT | 31771 | William | Bee | 071 0841 0771 | william.bee@google.com | SW1V 3EU | 35511 | May | Viper | 071 0381 0511 | may.viper@yahoo.com | SE5 0TS | 46431 | Gil | Viper | 071 0701 0431 | gil.viper@yahoo.com | EC1V 3SJ | 22473 | Yolanda | Dog | 071 0883 0473 | yolanda.dog@bt.com | SE5 0JD | 31191 | Ray | Viper | 071 0661 0191 | ray.viper@yahoo.com | N1 9QG | 44699 | Zack | Denmark | 071 0529 0699 | zack.denmark@google.com | E1 8HY | 2819 | Ulrica | Denmark | 071 0049 0819 | ulrica.denmark@google.com | SW8 4JS | 20411 | May | Leopard | 071 0281 0411 | may.leopard@google.com | SW3 2QS | 48329 | Elena | Denmark | 071 0259 0329 | elena.denmark@bt.com | N1 0WF | (10 rows)
Setup...
BEGIN; SET search_path=pgtap,teacher,public; SELECT plan(1); CREATE TEMP TABLE expected_vals (i int, tid int, pc varchar, wkend date); CREATE TEMP TABLE actual_vals (i int, tid int, pc varchar, wkend date);
Build our expected results...
WITH tgt_teachers AS ( SELECT * FROM teachers WHERE is_active AND tid < 1000 AND postcode <> 'SE10 8WT' ) ,tgt_wk AS ( SELECT min(wkend) AS wkend FROM availability ) ,selected_teachers AS ( SELECT DISTINCT t.tid, t.postcode, tw.wkend FROM tgt_teachers t JOIN skills ts USING (tid) JOIN availability ta USING (tid) JOIN tgt_wk tw USING (wkend) WHERE ts.ages = '[5,11)' AND ts.subject = 'QTS' AND ta.avail LIKE 'a%' ORDER BY tid LIMIT 5 ) INSERT INTO expected_vals SELECT row_number() OVER (), tid, postcode, wkend FROM selected_teachers;
Giving us this.
i | tid | pc | wkend ---+-----+----------+------------ 1 | 429 | E1 2JU | 2013-07-07 2 | 385 | E1W 3DB | 2013-07-07 3 | 289 | BR1 4LL | 2013-07-07 4 | 149 | SE10 0PU | 2013-07-07 5 | 105 | SE10 0DG | 2013-07-07 (5 rows)
Which we test like this.
DO $$ DECLARE tst RECORD; res RECORD; BEGIN FOR tst IN SELECT * FROM expected_vals LOOP res := booking_candidates(tst.pc, 9, 'QTS', ARRAY[tst.wkend-6], 1); INSERT INTO actual_vals VALUES (tst.i, res.tid, res.postcode, tst.wkend); END LOOP; END; $$; SELECT results_eq( $$SELECT i, pc, tid FROM actual_vals ORDER BY i$$, $$SELECT i, pc, tid FROM expected_vals ORDER BY i$$, 'booking_candidates() finds teacher at exact postcode' );
In a run like this.
$ pg_prove --port=5433 -U pgday t/* t/20_mode_agg.sql ..... ok t/20_pc_to_pt.sql ..... ok t/20_to_district.sql .. ok t/45_wkend_fns.sql .... ok t/50_bookings.sql ..... ok t/60_flags_set.sql .... ok t/61_avail_flags.sql .. ok t/62_pc_flags.sql ..... ok t/99_permissions.sql .. ok t/avail_rules.sql ..... ok All tests successful. Files=10, Tests=28, 25 wallclock secs ( 0.09 usr 0.03 sys ...) Result: PASS
--- - hosts: all user: root tasks: - name: install pgsql apt: pkg=$item with_items: - postgresql-9.2 - postgresql-client-9.2 - postgresql-contrib-9.2 - postgresql-server-dev-9.2 - pgxnclient
- name: set archive-mode (enabled) lineinfile: dest = /etc/postgresql/9.2/main/postgresql.conf insertbefore = "^#archive_mode" regexp = "^archive_mode =" line = "archive_mode = on" state = present ... - name: start pgsql service: name=postgresql enabled=yes state=started
Pros:
Cons:
# Nagios NRPE remote config command[check_root_disk] = /usr/lib/nagios/plugins/check_disk -w 20% -c 10% -p / command[pgday_lag] = /usr/lib/nagios/plugins/check_postgres.pl --action=hot_standby_delay --host=localhost,{{hostvars[other_host]['ansible_ssh_host']}} --dbuser=pgrepl --dbname=postgres --dbpass=abc123 --warning=123 --critical=456
Charts generated by pnp4nagios.
Need a simple script to test random queries.
Our target:
We got:
Try bigger:
OK, bigger again:
But that's because we're matching ~10,000 teachers and only keeping 10 of them.
Restrict the area of search so we only throw 5,000 away and we get...
Thanks to:
Code will be on github shortly. Check planet.postgresql.org for a notice.
Any questions?