CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.6postgresql-10
Justinas Marozas
Justinas Marozas

Reputation: 2652

Make postgres ignore statement that current version does not support

Background

A service running same tasks against a number of similar PostgreSQL instances. Most environments are on version 10, but some are on 9. Upgrading them is not an option in short term at least.

Problem

To improve performance, we used PostgreSQL 10 feature CREATE STATISTICS. It works just fine on on environments on v10, but is not supported on v9.

One way to deal with it could be to duplicate each script that uses CREATE STATISTICS, maintain a copy of it without those statement and choose which script to run at application level. I'd like to avoid it as it's a lot of duplicated code to maintain.

I've tried to cheat it by only creating the statistics if the script finds appropriate version (code below), but on v9 it still gets picked up as a syntax error.

DO $$

-- server_version_num is a version number melted to an integer:
--  9.6.6 -> 09.06.06 ->  90606
-- 10.0.1 -> 10.00.01 -> 100001
DECLARE pg_version int := current_setting('server_version_num');

BEGIN

IF pg_version >= 100000 THEN
  CREATE STATISTICS table_1_related_col_group_a (NDISTINCT)
  ON col_a1, col_a2
  FROM schema_1.table_1;

  CREATE STATISTICS table_2_related_col_group_b (NDISTINCT)
  ON col_b1, col_b2, col_b3
  FROM schema_1.table_2;
END IF;

END $$ LANGUAGE plpgsql;

Question

Is there a way to run a script that has an unsupported statement like CREATE STATISTICS in it without tipping postgres 9 off?

Upvotes: 0

Views: 142

Answers (1)

404
404

Reputation: 8542

Use dynamic SQL. It won't be evaluated unless executed.

DO $$

-- server_version_num is a version number melted to an integer:
--  9.6.6 -> 09.06.06 ->  90606
-- 10.0.1 -> 10.00.01 -> 100001
DECLARE pg_version int := current_setting('server_version_num');

BEGIN

IF pg_version >= 100000 THEN
  EXECUTE 'CREATE STATISTICS table_1_related_col_group_a (NDISTINCT)
           ON col_a1, col_a2
           FROM schema_1.table_1;

           CREATE STATISTICS table_2_related_col_group_b (NDISTINCT)
           ON col_b1, col_b2, col_b3
           FROM schema_1.table_2;';
END IF;

END $$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions