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 addedpg_cancel_backend