Database Doings

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    ...

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...