Presented by Keith Fiske / @keithf4
Senior Database Engineer @ CrunchyData
(pg_partman, pgMonitor, pg_extractor)
Follow along at
http://slides.keithf4.com/extension_dev
psql -d mydb < myplpgsql_code.sql
CREATE EXTENSION ... [ SCHEMA ... ]
ALTER EXTENSION ... UPDATE [ TO '...' ]
# drop table partman.part_config;
ERROR: cannot drop table partman.part_config because extension pg_partman requires it
HINT: You can drop extension pg_partman instead.
requires = 'dblink,pgcrypto'
Existing version is 1.2.3
ALTER EXTENSION UPDATE extension TO '2.0.0';
ALTER EXTENSION UPDATE extension TO '1.2.4';
ALTER EXTENSION UPDATE extension TO '1.2.5';
ALTER EXTENSION UPDATE extension TO '2.0.0';
select * from pg_extension_update_paths('pg_partman') where source = '2.3.4' and target = '3.0.0';
source | target | path
--------+--------+-----------------------------------------------------------------------------
2.3.4 | 3.0.0 | 2.3.4--2.4.0--2.4.1--2.5.0--2.5.1--2.6.0--2.6.1--2.6.2--2.6.3--2.6.4--3.0.0
select * from pg_extension_update_paths('pg_partman') where source = '2.3.4' and target = '1.0.0';
source | target | path
--------+--------+--------
2.3.4 | 1.0.0 | «NULL»
mimeo--0.9.3--0.10.0.sql
mimeo--0.9.3--0.10.1.sql
select * from pg_extension_update_paths('mimeo') where source = '0.9.3' and target ~ '0.10.' order by 2;
source | target | path
--------+--------+-------------------------------------------------------
0.9.3 | 0.10.0 | 0.9.3--0.10.0
0.9.3 | 0.10.1 | 0.9.3--0.10.1
0.9.3 | 0.10.2 | 0.9.3--0.10.1--0.10.2
0.9.3 | 0.10.3 | 0.9.3--0.10.1--0.10.2--0.10.3
select * from pg_extension_update_paths('mimeo') where source = '0.10.0' and target ~ '0.10.' order by 2;
source | target | path
--------+--------+--------------------------------------------------------
0.10.0 | 0.10.1 | 0.10.0--0.10.1
0.10.0 | 0.10.2 | 0.10.0--0.10.1--0.10.2
ALTER EXTENSION dblink UPDATE FROM unpackaged;
SELECT pg_catalog.pg_extension_config_dump('part_config', '');
SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');
SELECT pg_catalog.pg_extension_config_dump('job_status_text', 'WHERE alert_code NOT IN (1,2,3)');
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
EXTVERSION = $(shell grep default_version $(EXTENSION).control | \
sed -e "s/default_version[[:space:]]*=[[:space:]]*'\([^']*\)'/\1/")
all: sql/$(EXTENSION)--$(EXTVERSION).sql
sql/$(EXTENSION)--$(EXTVERSION).sql: $(sort $(wildcard sql/types/*.sql)) $(sort $(wildcard sql/tables/*.sql)) $(sort $(wildcard sql/functions/*.sql)) $(sort $(wildcard sql/procedures/*.sql))
cat $^ > $@
DATA = $(wildcard updates/*--*.sql) sql/$(EXTENSION)--$(EXTVERSION).sql
EXTRA_CLEAN = sql/$(EXTENSION)--$(EXTVERSION).sql
PG94 = $(shell $(PG_CONFIG) --version | egrep " 8\.| 9\.0| 9\.1| 9\.2| 9\.3" > /dev/null && echo no || echo yes)
PG11 = $(shell $(PG_CONFIG) --version | egrep " 8\.| 9\.| 10\." > /dev/null && echo no || echo yes)
ifeq ($(PG94),yes)
[... Do all your build stuff here ...]
ifeq ($(PG11),yes)
[... Do only pg11+ stuff here ...]
else
[... Do pre-11 stuff here ... ]
# end PG11 if
endif
$(error Minimum version of PostgreSQL required is 9.4.0)
endif
CREATE INDEX part_config_type_idx ON @extschema@.part_config (partition_type);
CREATE TABLE part_config_sub (
sub_parent text
, sub_partition_type text NOT NULL
[...]
, CONSTRAINT part_config_sub_pkey PRIMARY KEY (sub_parent)
, CONSTRAINT part_config_sub_sub_parent_fkey FOREIGN KEY (sub_parent) REFERENCES @extschema@.part_config (parent_table) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
, CONSTRAINT positive_premake_check CHECK (sub_premake > 0)
);
CREATE TEMP TABLE partman_preserve_privs_temp (statement text);
INSERT INTO partman_preserve_privs_temp
SELECT 'GRANT EXECUTE ON FUNCTION @extschema@.undo_partition(text, int, text, boolean, numeric, text, text[], boolean) TO '||array_to_string(array_agg('"'||grantee::text||'"'), ',')||';'
FROM information_schema.routine_privileges
WHERE routine_schema = '@extschema@'
AND routine_name = 'undo_partition'
AND grantee != 'PUBLIC';
DROP FUNCTION @extschema@.undo_partition(text, int, text, boolean, numeric, text, text[]);
[... Do all your extension update code here ...]
[... Do all your extension update code here ...]
-- Restore dropped object privileges
DO $$
DECLARE
v_row record;
BEGIN
FOR v_row IN SELECT statement FROM partman_preserve_privs_temp LOOP
IF v_row.statement IS NOT NULL THEN
EXECUTE v_row.statement;
END IF;
END LOOP;
END
$$;
DROP TABLE IF EXISTS partman_preserve_privs_temp;
IF current_setting('server_version_num')::int < 100000 THEN
#if (PG_VERSION_NUM < 100000)
worker.bgw_main = pg_partman_bgw_main;
#endif
#if (PG_VERSION_NUM >= 100000)
sprintf(worker.bgw_library_name, "pg_partman_bgw");
sprintf(worker.bgw_function_name, "pg_partman_bgw_main");
#endif
DO $pg11only$
DECLARE
v_partition_data_sql text;
BEGIN
IF current_setting('server_version_num')::int >= 110000 THEN
v_partition_data_sql := $partition_data$
CREATE PROCEDURE @extschema@.partition_data_proc (p_parent_table text,
[...]
END;
$$;
$partition_data$;
EXECUTE v_partition_data_sql;
END IF; -- end pg11 version check
END
$pg11only$;
\echo Use "CREATE EXTENSION dblink" to load this file. \quit