2

I'm searching my ass of so you're my last hope. The task is quite simple:

I want to execute a trigger for each statement attached to a parent table (parent of partition tables) in Postgres 10.1. The triggered function will create a partition in case the corresponding range does not yet exist. The following function does not yet work for multiple years, I know ;).

Now my problem is, that I have no clue how to access the new data. When creating FOR EACH ROW, I have a variable 'NEW' (not allowed for partitions). When using INSTEAD OF INSERT, I should have something like 'inserted' as variable (not allowed for tables). So how can I access the new rows when using BEFORE INSERT FOR EACH STATEMENT? I just want all unique years which would be inserted afterwards.

Table:

CREATE TABLE ts AS (
  id BIGSERIAL         NOT NULL,
  start_ts TIMESTAMPTZ NOT NULL
);

Partitions:

CREATE TABLE ts_2014
  PARTITION OF ts 
  FOR VALUES FROM ('2014-01-01') TO ('2015-01-01');

Trigger Function:

CREATE OR REPLACE FUNCTION create_ts_partition()
  RETURNS TRIGGER AS $$
DECLARE
  row_year           INT;
  dst_partition_name VARCHAR;
  partition_found    BOOLEAN;
  lower_bound        VARCHAR;
  upper_bound        VARCHAR;
BEGIN
  row_year = (
    SELECT 
      extract(YEAR FROM start_ts)
    FROM NEW
    LIMIT 1);

  dst_partition_name = tg_table_name || '_' || row_year;

  partition_found = (
    SELECT
      exists(pg_c1.relname)
    FROM pg_class AS pg_c1
    WHERE pg_c1.relname LIKE dst_partition_name
  );

  IF NOT partition_found
  THEN
    lower_bound = row_year || '-01-01';
    upper_bound = (row_year + 1) || '-01-01';
    EXECUTE 'CREATE TABLE ' || dst_partition_name || ' [...]';
  END IF;
END;
$$ LANGUAGE 'plpgsql' PARALLEL UNSAFE;

Trigger:

CREATE TRIGGER ts_partition_creation
  BEFORE INSERT
  ON ts
  FOR EACH STATEMENT
EXECUTE PROCEDURE create_ts_partition();

Thanks in advance. Kenneth

-- edit Thanks to Daniel Vérité, I've encountered some inconsistencies with the names inside the example. Thanks!

1

The doc for CREATE TRIGGER in PostgreSQL 10 says:

Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views

Assuming nvme_ts is a view, you're trying to define a FOR EACH STATEMENT trigger in a case that the doc explicitly disallows.

Besides, the code shown in create_ts_partition() is meant for a single row, not for multiple rows with potentially different years. You probably simply need for a FOR EACH ROW trigger, if you're set on using a trigger at all to manage the partitions.

  • The quote you've mentioned adds to the reasons why I can't use this ;). Sadly nvme_ts is no view but a table (apparently multiple names had sneak into the code. I'll change this. – K. Kunze Jan 23 '18 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.