We recently enabled unattended-upgrades on our Ubuntu 18.04 LTS (Bionic) servers, including our PostgreSQL hosts. By default, unattended-upgrades will ignore PGDG packages (where all of our PostgreSQL packages come from), so I had assumed we wouldn’t have any interruptions due to apt installing updates and triggering a database restart. One...
[Read More]
Sequence Caching: Oracle vs. PostgreSQL
Many RDBMSes use sequence caching to pre-generate sequence values in advance and store them in memory, allowing them to return new values quickly. If you are doing a lot of inserts that each call the sequence to get the next value, sequence caching is a good thing. Having to wait...
[Read More]
NULL Means NULL
A Story of the Laziest User Recreation Script Ever
Encountering the NULL
[Read More]
Altering Default Privileges For Fun and Profit
What NOT to expect when you're expecting.
One of the big changes I came upon as I transitioned from Oracle to PostgreSQL was how privileges are handled. In Oracle, an object’s schema also determines its owner, as the schema is generally that user’s objects. PostgreSQL disassociates schemas from users. Schemas do have owners, but a given user...
[Read More]
USAGE Privilege Prevents Dropping Users
ACLs FTW
The Incident
While cleaning up some obsolete user accounts, one of my DROP USER commands failed with:
[Read More]