postgresql 2 Comments

Catalog SQL Friday: using DO when we’ve got tables as variables

Just a quick note about modifying constraints:

There’s no such thing as ALTER CONSTRAINT. So, if you want to safely change a CHECK constraint, like on a partition, you need to DROP and ADD it in a single transaction.

Below is a snippet for finding partitions, their CHECK constraints based on a WHERE clause. Then we DROP the existing constraint and add back the correct constraint. It doesn’t take much sleuthing to figure out what the problem was. 🙂

 DO $$
  DECLARE myrecord record;
  DECLARE theweek text;
  BEGIN
    FOR myrecord IN SELECT relname, conname from pg_constraint
      JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
      WHERE consrc ~ 'without' and split_part(relname, '_201', 1)
      IN (select table_name from report_partition_info
      WHERE partition_column = 'date_processed') LIMIT 1
    LOOP
       EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)
       || ' DROP CONSTRAINT IF EXISTS '
       || quote_ident(myrecord.conname) || ';';</p>

   theweek = substring(myrecord.relname from '........$');

   EXECUTE 'ALTER TABLE ' || quote_ident(myrecord.relname)
   || ' ADD CONSTRAINT ' || quote_ident(myrecord.conname)
   || ' CHECK ((date_processed >= timestamptz('
   || quote_literal(to_char(date(theweek), 'YYYY-MM-DD')) || '))'
   || ' AND (date_processed < timestamptz('
   || quote_literal(to_char(date(theweek) + 7, 'YYYY-MM-DD'))
   || ')));';

   RAISE NOTICE 'DONE: %', myrecord.relname;
END LOOP;

END$$;

Here’s the gist version for easier reading.

The couple things I learned in this process was a nice feature in substring() allowing me to return the date portion of my partition names easily, and split_part() which allowed me to return the parent table name and compare it to my list of partitionable tables for the specific partition column. I recently added support for partitioning on a different column for certain tables, so I have to differentiate for this fix. The string function docs are pretty great.

I didn’t do any optimization of this — just got it working and am now testing it in our stage environment. The final script is going to perform the changes on a month’s worth of partitions at a time to help reduce the chance of deadlocking.

If you have thoughts on how I could have done this more efficiently, let me know in the comments!

2 thoughts on Catalog SQL Friday: using DO when we’ve got tables as variables

Comments are closed.

  1. Isaac Morland · August 18, 2013 at 6:08 am ·

    I’m not sure about doing this more efficiently, but I’m wondering if you’re deliberately avoiding various 9.1 and 9.2 features for backward compatibility. Together with judicious use of the regclass and regprocedure types, I’ve found some recent features can dramatically improve code that generates SQL:

    theweek = date (substring(myrecord.relname from '........$'));
    EXECUTE format ('ALTER TABLE %s ' ||
    'DROP CONSTRAINT IF EXISTS %L, ' ||
    'ADD CONSTRAINT %L CHECK (date_processed <@ tstzrange (%L, %L))',
    myrecord.conrelid, myrecord.conname, myrecord.conname, theweek, theweek + 7);

    (also need to include conrelid in the SELECT clause up above)

    (Use of regclass with format() always looks wrong because the correct format string is %s, not %L or %I)