5 September 2017
08:51
I spent a few minutes worrying I had lost my mind: Results coming back to my application were different from those obtained by running psql directly on the database server, for identical queries.
As is often the case, the problem was time zones.
In particular, when casting a TIMESTAMPTZ to a DATE, the current client time zone setting is used. So:
xof=# BEGIN;
BEGIN
xof=# set time zone 'UTC';
SET
xof=# select now()::date;
now
------------
2017-09-05
(1 row)
xof=# set time zone 'Australia/Lord_Howe';
SET
xof=# select now()::date;
now
------------
2017-09-06
(1 row)
xof=# COMMIT;
COMMIT
(Extra credit for knowing why I wrapped this example in a transaction block!)
Just a reminder to be very careful when converting items from time zone-aware formats (like TIMESTAMPTZ) to non-aware ones (like TIMESTAMP or DATE). And always store data as a time zone-aware type, unless there is a very compelling reason to do otherwise.
4 September 2017
01:18
This is probably no big deal to anyone, but the blog has moved to a new server. Whee!
9 June 2017
11:27
There’s a lot of mystery-cult information floating around out there about what to set shared_buffers to in PostgreSQL. Most of it is, at best, overthinking a fairly simple setting.
You can get 90% of the way there, on 95%+ of PostgreSQL systems, just by setting it to 25% of total system memory, to a maximum of 32GB. (It used to be 8GB, and then 16GB; as processors get faster, the number tends to go up.)
In particular, the difference between 24GB and 32GB, for example, will almost certainly be unmeasurable, on a system with 128GB or more memory.
It can be of benefit to lower shared_buffers on systems that have a high write rate, to reduce the total amount of data flushed on one checkpoint. I don’t have analytic values for that (shame on me!), but I’ve seen benefits by reducing it to as low as 2GB on a very high write-rate system.
But don’t agonize.
7 June 2017
15:03
For some reason, this is something that even experienced PostgreSQL people don’t know about, which is: What do you do if you’ve forgotten the postgres user’s password, and you have no other superuser available?
The answer is: Bring up PostgreSQL in single-user mode, reset the password, bring it back up in standard mode.
Of course, this requires access to the shell on the machine running PostgreSQL.
28 April 2017
09:00
The slides from my talk at Percona Live 2017, A PostgreSQL Response to Uber, which I also gave as “Why Uber Was (Mostly) Wrong” at PGDay Nordic 2017, are now available.
31 March 2017
09:34
The slides from my presentation, Corruption War Stories, are now available.
09:33
The slides from my presentation, Django and PostgreSQL, are now available.
29 March 2017
07:08
My slides from my PGConf US 2017 tutorial, PostgreSQL When It’s Not Your Job, are available now.
7 February 2017
03:14
The slides for my talk Corruption War Stories are now available.
4 January 2017
12:00
(One of an occasional series of posts about strange errors you can get out of Django, and what it is trying to tell you.)
This exception was being thrown when I was trying to render a template:
dictionary update sequence element #0 has length 1; 2 is required
It turned out to be a slightly bad url() in a urlconf. I had forgotten the name= before the URL’s name. Oops!