This is an automated email from the ASF dual-hosted git repository. zrhoffman pushed a commit to branch 6.0.x in repository https://gitbox.apache.org/repos/asf/trafficcontrol.git
commit 77a79b55022ba1342573983c834d0e907ad5532c Author: Zach Hoffman <[email protected]> AuthorDate: Thu Aug 26 17:31:35 2021 -0600 Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations (#6143) * Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations * Reference NEW and OLD in trigger functions * Bugfix: Drop correct trigger name * Add parentheses for PostgreSQL 9.6 compatibility * Trigger FOR EACH ROW for NEW/OLD access (cherry picked from commit 328250d0ab7672892e6e36e7b321086b8d811550) --- traffic_ops/app/db/migrations/00000000000000_init.up.sql | 2 +- .../db/migrations/2021061100000000_ds_tls_versions.down.sql | 6 +++--- .../app/db/migrations/2021061100000000_ds_tls_versions.up.sql | 10 ++++------ traffic_ops/app/db/trafficvault/create_tables.sql | 10 ++++------ 4 files changed, 12 insertions(+), 16 deletions(-) diff --git a/traffic_ops/app/db/migrations/00000000000000_init.up.sql b/traffic_ops/app/db/migrations/00000000000000_init.up.sql index 08bf05a..7e8a022 100644 --- a/traffic_ops/app/db/migrations/00000000000000_init.up.sql +++ b/traffic_ops/app/db/migrations/00000000000000_init.up.sql @@ -28,7 +28,7 @@ BEGIN RENAME TO goose_db_version_unused; END IF; DROP TRIGGER fast_forward_schema_migrations_trigger ON schema_migrations; - DROP FUNCTION fast_forward_schema_migrations_version; + DROP FUNCTION fast_forward_schema_migrations_version(); RETURN NULL; END$$; diff --git a/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.down.sql b/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.down.sql index a60944b..46ce8c5 100644 --- a/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.down.sql +++ b/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.down.sql @@ -23,8 +23,8 @@ SET WHERE original IS NOT NULL; -DROP TRIGGER IF EXISTS update_ds_timestamp_on_tls_version_insertion_or_update ON public.deliveryservice_tls_version; +DROP TRIGGER IF EXISTS update_ds_timestamp_on_tls_version_insertion ON public.deliveryservice_tls_version; DROP TRIGGER IF EXISTS update_ds_timestamp_on_tls_version_delete ON public.deliveryservice_tls_version; DROP TABLE IF EXISTS public.deliveryservice_tls_version; -DROP FUNCTION IF EXISTS update_ds_timestamp_on_insert; -DROP FUNCTION IF EXISTS update_ds_timestamp_on_delete; +DROP FUNCTION IF EXISTS update_ds_timestamp_on_insert(); +DROP FUNCTION IF EXISTS update_ds_timestamp_on_delete(); diff --git a/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql b/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql index 9382610..d49c6ba 100644 --- a/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql +++ b/traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql @@ -25,7 +25,7 @@ BEGIN SET last_updated=now() WHERE id IN ( SELECT deliveryservice - FROM new_table + FROM CAST(NEW AS deliveryservice_tls_version) ); RETURN NULL; END; @@ -39,7 +39,7 @@ BEGIN SET last_updated=now() WHERE id IN ( SELECT deliveryservice - FROM old_table + FROM CAST(OLD AS deliveryservice_tls_version) ); RETURN NULL; END; @@ -47,13 +47,11 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER update_ds_timestamp_on_tls_version_insertion AFTER INSERT ON public.deliveryservice_tls_version - REFERENCING NEW TABLE AS new_table - FOR EACH STATEMENT EXECUTE PROCEDURE update_ds_timestamp_on_insert(); + FOR EACH ROW EXECUTE PROCEDURE update_ds_timestamp_on_insert(); CREATE TRIGGER update_ds_timestamp_on_tls_version_delete AFTER DELETE ON public.deliveryservice_tls_version - REFERENCING OLD TABLE AS old_table - FOR EACH STATEMENT EXECUTE PROCEDURE update_ds_timestamp_on_delete(); + FOR EACH ROW EXECUTE PROCEDURE update_ds_timestamp_on_delete(); UPDATE public.deliveryservice_request SET diff --git a/traffic_ops/app/db/trafficvault/create_tables.sql b/traffic_ops/app/db/trafficvault/create_tables.sql index bc59b8f..2fcb97e 100644 --- a/traffic_ops/app/db/trafficvault/create_tables.sql +++ b/traffic_ops/app/db/trafficvault/create_tables.sql @@ -54,8 +54,6 @@ ALTER FUNCTION on_update_current_timestamp_last_updated() OWNER TO traffic_vault SET default_tablespace = ''; -SET default_table_access_method = heap; - -- -- Name: dnssec; Type: TABLE; Schema: public; Owner: traffic_vault -- @@ -169,28 +167,28 @@ CREATE INDEX sslkey_version_idx ON sslkey USING btree (version); -- Name: dnssec dnssec_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault -- -CREATE TRIGGER dnssec_last_updated BEFORE UPDATE ON dnssec FOR EACH ROW EXECUTE FUNCTION on_update_current_timestamp_last_updated(); +CREATE TRIGGER dnssec_last_updated BEFORE UPDATE ON dnssec FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated(); -- -- Name: sslkey sslkey_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault -- -CREATE TRIGGER sslkey_last_updated BEFORE UPDATE ON sslkey FOR EACH ROW EXECUTE FUNCTION on_update_current_timestamp_last_updated(); +CREATE TRIGGER sslkey_last_updated BEFORE UPDATE ON sslkey FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated(); -- -- Name: uri_signing_key uri_signing_key_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault -- -CREATE TRIGGER uri_signing_key_last_updated BEFORE UPDATE ON uri_signing_key FOR EACH ROW EXECUTE FUNCTION on_update_current_timestamp_last_updated(); +CREATE TRIGGER uri_signing_key_last_updated BEFORE UPDATE ON uri_signing_key FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated(); -- -- Name: url_sig_key url_sig_key_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault -- -CREATE TRIGGER url_sig_key_last_updated BEFORE UPDATE ON url_sig_key FOR EACH ROW EXECUTE FUNCTION on_update_current_timestamp_last_updated(); +CREATE TRIGGER url_sig_key_last_updated BEFORE UPDATE ON url_sig_key FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated(); --
