(PL/pg)SQL patterns I use

(PL/pg)SQL patterns I use

This is supposed to be the addition to the other patterns post "Bash patterns I use" but for SQL and/or PL/pgSQL.

Create partitions with a function (hacky variant)

This comes in handy when you need a bunch of partitions on a PostgreSQL table. Sure, there are tools like pg_partman, but, sometimes, you just need something that gets the job done without installing an extension. Or your schema is just fixed for testing purposes. Hence, I commonly use something like:

CREATE FUNCTION create_partitions(
      table_name VARCHAR
    , start_year INT
    , end_year INT
) RETURNS VOID AS $BODY$
DECLARE
  yr INT;
BEGIN
    FOR yr IN start_year..end_year LOOP
        RAISE NOTICE 'Creating partition for year %', yr;
        EXECUTE '
          CREATE TABLE ' || table_name || '__part_' || yr || '
          PARTITION OF ' || table_name || '
          FOR VALUES FROM ($$ ' || yr || '-01-01$$) TO ($$ ' || yr + 1 || '-01-01$$);'
        ;
    END LOOP;
END;
$BODY$ LANGUAGE plpgsql;

Which would create partitions from start_year to end_year where each partition holds a full year. Watch out for the $$ syntax. It makes quoting a bit easier but can conflict with the outer function definition. Therefore, I used $BODY$ here to mark beginning and end of the function definition.

As pointed out by Nikolay Samokhvalov of Postgres.ai, the better way is to use format().

And therefore we get to:

Create partitions with a function (non-hacky variant)

...which uses format() and looks as follows. Note, that it now also makes use of generate_series to generate the date ranges.

CREATE FUNCTION create_partitions(
      table_name VARCHAR
    , start_year DATE
    , end_year DATE
) RETURNS VOID AS $BODY$
DECLARE
  yr RECORD;
BEGIN
    FOR yr IN
      SELECT d::DATE AS _start, (d + '1 YEAR')::DATE AS _end
      FROM generate_series(start_year, end_year, '1 YEAR'::INTERVAL) d LOOP
        RAISE NOTICE 'Creating partition for year %', yr._start;
        EXECUTE format(
          'CREATE TABLE %s__part_%s PARTITION OF %s '
          'FOR VALUES FROM(%L) TO (%L);',
          table_name, to_char(yr._start, 'YYYY'), table_name, yr._start, yr._end
        );
    END LOOP;
END;
$BODY$ LANGUAGE plpgsql;

Much more readable, isn't it?

Stop all these queries

This pattern will cancel all queries given the filter on the WHERE clause. Always watch out, that you are not in the same database when you run it. Otherwise, you might kill your own session accidentally. I typically use this pattern when things go too wild (i.e. short before 100% RAM and SWAP ... hint hint). It is a bit brute-force, so feel free to adjust the WHERE clause according to your needs.

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE datname = 'this_db_with_too_much_stuff';

Also, if it is too late to be nice, consider replacing pg_cancel_backend with pg_terminate_backend, your last resort before pg_ctl restart.

Notes, References and Changelog

  • Photo by vedanti from Pexels
  • 2020/10/06: added non-hacky variant of create_partitions and added pg_cancel_backend