Posts

Beyond the Single PostgreSQL Node, Part One

When Disco was the hot new music, the people who built database management systems based their praxis around the then-correct assumption that hardware was extremely expensive, and hence that procuring things like more storage, let alone more machines on which they would run, could be counted on to require significant capital resources. This could only happen on the time scale of quarters, even if it was deemed worthwhile to fight the fights needed to procure them. The above conditions informed the practice of hardening single nodes against failure without adding significant hardware resources in the process. This practice continues, and it's a good thing to have. However. Over the past few decades, hardware resources have been dropping precipitously in cost even when acquired via capital expenditure, and tax (read subsidy here) regimes have been altered to put a massive thumb on the scale to favor operational expenditures. This in turn has driven technologies that made it easy to m
Post a Comment
Read more

You don't need PL/pgsql!

You don't need PL/pgsql to create functions that do some pretty sophisticated things. Here's a function written entirely in SQL that returns the inverse cumulative distribution function known in Microsoft Excel™ circles as NORMSINV. CREATE OR REPLACE FUNCTION normsinv(prob float8) RETURNS float8 STRICT LANGUAGE SQL AS $$ WITH constants(a,b,c,d,p_low, p_high) AS ( VALUES( ARRAY[-3.969683028665376e+01::float8 , 2.209460984245205e+02 , -2.759285104469687e+02 , 1.383577518672690e+02 , -3.066479806614716e+01 , 2.506628277459239e+00], ARRAY[-5.447609879822406e+01::float8 , 1.615858368580409e+02 , -1.556989798598866e+02 , 6.680131188771972e+01 , -1.328068155288572e+01], ARRAY[-7.784894002430293e-03::float8 , -3.223964580411365e-01 , -2.400758277161838e+00 , -2.549732539343734e+00 , 4.374664141464968e+00 , 2.938163982698783e+00], ARRAY[7.784695709041462e-03::float8 , 3.224671290700398e-01 , 2.445134137142996e+00 , 3.754408661907416e+00],
5 comments
Read more

psql: A New Edit

Have you ever found yourself in the middle of a long statement in psql and wanted to pull up your favorite editor? Now, you can, using the same shortcut of control-x control-e that you would in bash! Here's how: $EDITOR ~/.inputrc Now add the following lines: $if psql     "\C-x\C-e": "\C-e\\e\C-m" $endif ...save the file, and you're good to go.
Post a Comment
Read more

Slides from "ASSERTIONs and how to use them"

I've posted the slides for my ASSERTIONs and how to use them" talk from PGCon 2018. The files are in this repository .
Post a Comment
Read more

Cool Stuff in PostgreSQL 10: Auto-logging

We started off by creating a logging infrastructure, then arranging for a single table to use it. Rather than repeat that work for each table, let's use a relatively although not completely new feature: `EVENT TRIGGER`. The idea here is that we fire a trigger on `CREATE TABLE` and see to it that the table is logged. We'll write the trigger first, even though in reality, we'd need to load the function it calls first. ```sql CREATE EVENT TRIGGER add_logger ON ddl_command_end WHEN tag IN ('create table') EXECUTE PROCEDURE add_logger(); COMMENT ON EVENT TRIGGER add_logger IS 'Ensure that each table which is not a log gets logged'; ``` The magic happens inside `add_logger()`, but it's magic we've already seen. First, we'll get the table's name and schema using `pg_event_trigger_ddl_commands()`, filtering out tables which are already log tables. The test here is crude and string-based, but we could easily go to schema-based
1 comment
Read more

Cool Stuff in PostgreSQL 10: Transition Table Triggers

Last time , we created a heirarchy of tables for logging, but we don't have anything they'd log, and we don't have a way to ensure the logging happens automagically.  This time, we'll fix those problems. First, a thing to log: CREATE TABLE foo(     id BIGSERIAL PRIMARY KEY,     t TEXT ); Next, let's make an efficient trigger function.  You may have seen one of these before, but it was probably row-based, so it couldn't be super efficient.  This one is statement-based, and uses the new transition table feature in PostgreSQL 10.  We only need to build one trigger function because our logging infrastructure is schema-agnostic.  We could have separated it into three trigger functions, but that's just more code to maintain.  It starts off as usual: CREATE OR REPLACE FUNCTION log() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN     IF TG_OP NOT IN ('INSERT', 'UPDATE', 'DELETE') THEN         RAISE EXCEPTION 'This function
10 comments
Read more

Cool Stuff in PostgreSQL 10: Partitioned Audit Table

If you've dealt with PostgreSQL's answer to partitioning in the past, you may have put a lot of work into it.  Happily, you have a lot less to bother with as of PostgreSQL 10, which is not out yet as of this writing. Let's dive right in with a table that's a little like an audit trail. CREATE TABLE IF NOT EXISTS the_log (     "timestamp" timestamp with time zone DEFAULT now() NOT NULL,     "user" text NOT NULL DEFAULT CURRENT_USER,     action text NOT NULL,     table_schema text NOT NULL,     table_name text NOT NULL,     old_row jsonb,     new_row jsonb,     CONSTRAINT the_log_check CHECK (         (old_row IS NOT NULL)::integer +         (new_row IS NOT NULL)::integer > 0     ) ) PARTITION BY LIST(table_schema); COMMENT ON CONSTRAINT the_log_check ON the_log IS 'Make sure at least one of the columns is NOT NULL';   A few things are going on here which are likely familiar to you if you're a PostgreSQL user: CREATE ... IF
2 comments
Read more