# (PL/pg)SQL patterns I use

This is supposed to be the addition to the other patterns post ["Bash patterns I use"](https://fatstacks.hashnode.dev/bash-patterns-i-use-ckfw6lr6p0be6v6s1fbydbf69) 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](https://github.com/pgpartman/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:

```sql
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](https://postgres.ai/), the better way is to use [format()](https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT).

%[https://twitter.com/samokhvalov/status/1313281438481956864?s=20]

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.

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

```sql
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`