2

We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test db and explain everything to the rest later. I'm no dba, I'm a perl developer.

Focused on just one master table, I've produced a function to create a new children table, to create children tables for the existing information and to move things from master to children by batches. I don't think it's relevant, but our checks are on the year and month of the created field.

Now I'm building the insert trigger in the master database that would detect the period for the new record, look for the corresponding children table, creates it if missing, and inserts. Everything is ready but the insert is not working!

Our inserts statements are usually partial: will add the basic fields, do some things and fill in the rest (the fill in part will come later). So the insert statement is something like

INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');

and leave Postgres to fill in the id. But in between created and tr_status, the schema has a field called modified. And, when the data gets to the trigger function, this field is empty. Trying to insert the row will throw an error:

EXECUTE (
    'INSERT INTO ' || child || ' VALUES ' || NEW.* 
);
LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed...
                                                             ^

How can I use in the trigger the same fields that were in the insert statement?

  • Maybe test the empty condition and insert null in the query string? – Fabrizio Mazzoni Jul 3 '15 at 15:21
  • The essential part of the error message is missing, as well as your version of Postgres. And the essential information: do parent and child tables always share the exact same set of columns? – Erwin Brandstetter Jul 3 '15 at 19:59
2

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;
  • this works like a charm! But how? this $1 variable, isn't it the input parameter? Where can I read more about this? – Juan Clavero Jul 6 '15 at 7:16
  • 1
    @JuanClavero: when used like this, it's the parameter after USING. See Executing Dynamic Commands in plpgsql doc. – Daniel Vérité Jul 6 '15 at 8:50
2

You already found that you have to use dynamic SQL with EXECUTE. What you are missing:

  • If child tables are not guaranteed to share the same row type , you must add a target column list to your INSERT statement or you are bound to run into errors or worse: it might work in surprising ways.

  • You need to defend against SQL injection. Table names have to be treated as potentially dangerous user input.

  • Don't concatenate values, pass them as values with the USING clause.

EXECUTE (format('
   INSERT INTO %I(created, modified, tr_status, ...)
   VALUES (($1).created, ($1).modified, ($1).tr_status, ...)'
   , child)
USING NEW;

Or, for many columns:

EXECUTE (format('
   INSERT INTO %I(created, modified, tr_status, ...)
   SELECT created, modified, tr_status, ...
   FROM  (SELECT ($1).*) t'
   , child)
USING NEW;

Related:

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.