For reasons I've brought up before (http://archives.postgresql.org/pgsql-general/2012-06/msg00174.php), I need to stop some of my extension tables from dumping data when a schema only dump is done because they have the potential to contain A LOT of data. For reference my extension is https://github.com/omniti-labs/pg_jobmon The problem I'm having is changing this with an extension update. I want to turn off the data dumping for the job_log and job_detail tables. The only method I've found to do this is by directly updating the extconfig column in the pg_extensions table. If I run this update directly via psql, it works fine. db=# select unnest(extconfig) as oid, split_part(unnest(extconfig)::regclass::text, '.', 2) as tablename from pg_extension where extname = 'pg_jobmon'; oid | tablename -----------+------------------ 214224990 | job_log 214225005 | job_detail 214225022 | job_check_log 214225028 | job_check_config 214225038 | job_status_text 214972369 | dblink_mapping (6 rows) db=# begin; BEGIN db=# UPDATE pg_extension SET extconfig = (SELECT array_agg(t.oid) FROM ( db(# SELECT unnest(extconfig) AS oid, split_part(unnest(extconfig)::regclass::text, '.', 2) AS tablename db(# FROM pg_extension WHERE extname = 'pg_jobmon') t db(# WHERE t.tablename NOT IN ('job_log', 'job_detail') ) WHERE extname = 'pg_jobmon'; UPDATE 1 db=# select unnest(extconfig) as oid, split_part(unnest(extconfig)::regclass::text, '.', 2) as tablename from pg_extension where extname = 'pg_jobmon'; oid | tablename -----------+------------------ 214225022 | job_check_log 214225028 | job_check_config 214225038 | job_status_text 214972369 | dblink_mapping (4 rows) db=# rollback; ROLLBACK However, if I run this exact same query as an extension update, it does nothing db=# select unnest(extconfig) as oid, split_part(unnest(extconfig)::regclass::text, '.', 2) as tablename from pg_extension where extname = 'pg_jobmon'; oid | tablename -----------+------------------ 214224990 | job_log 214225005 | job_detail 214225022 | job_check_log 214225028 | job_check_config 214225038 | job_status_text 214972369 | dblink_mapping (6 rows) db=# alter extension pg_jobmon update to '0.3.3'; ALTER EXTENSION db=# select unnest(extconfig) as oid, split_part(unnest(extconfig)::regclass::text, '.', 2) as tablename from pg_extension where extname = 'pg_jobmon'; oid | tablename -----------+------------------ 214224990 | job_log 214225005 | job_detail 214225022 | job_check_log 214225028 | job_check_config 214225038 | job_status_text 214972369 | dblink_mapping (6 rows) I know this isn't really something that would be done often, but it just seemed a rather odd behavior so I thought I'd bring it up in case it's something that can be easily fixed. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. 443.325.1357 x251 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
