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 NOT EXISTS, which lets you move toward those single-transaction deployments that have made PostgreSQL justly famous
  • Smart defaults on columns
  • JSONB because you'll probably want to probe inside a structure that might change over time
  • A CHECK constraint that ensures at least one not-NULL among the columns it looks at.  This can be generalized in a few different directions.  (This one's not actually all that common, but it really should be.)
One thing is really new: PARTITION.  In this case, we're partitioning the overarching table by one or more values in the table_schema column.  We're using LIST rather than RANGE because while schemas can definitely be sorted, it's probably not something important that you might use in a query.  LIST partitions are meant to highlight the uniqueness of (a few) individuals.

A lot of hard work goes on in order to make this work, but now it's been amortized into the PostgreSQL software, where before it was externalized to you and me.




Since tables with a PARTITION clause can't actually store tuples, let's make a place to store them.

CREATE TABLE IF NOT EXISTS public_log
    PARTITION OF the_log
    FOR VALUES IN ('public')
        PARTITION BY LIST(table_name);

Now we're ready to add some tuples, right?  Not quite.  There's a PARTITION BY at the end there, and that means this one can't hold tuples either.  One more:



CREATE TABLE IF NOT EXISTS public_foo_log
    PARTITION OF public_log
    FOR VALUES IN ('foo');

No PARTITION clause, so this one should hold tuples, presumably logging INSERT, UPDATE, and DELETE on the foo table.

In the next installment, we'll use some new features to make those changes flow in.

Comments

  1. Fabrízio de Royes MelloJuly 8, 2017 at 11:13 AM

    Cool article... I think you can simplify the check constraint in "the_log" table using this instead:

    ...
    CONSTRAINT the_log_check CHECK (old_row IS DISTINCT FROM new_row)
    ...

    ReplyDelete
    Replies
    1. David FetterJuly 11, 2017 at 2:47 PM

      That constraint is not precisely identical, as it would disallow identity updates, which can sometimes happen and aren't necessarily errors.

      Also, it's hard to generalize to >2 expressions.

      Delete

Post a Comment

Popular posts from this blog

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