rake gitlab:db:drop_tables doesn't drop pre-defined functions
In gitlab-org/charts/gitlab#3178 (closed) we found that rake gitlab:db:drop_tables
doesn't drop a number of functions that are used for triggers:
git grep -i "CREATE FUNCTION" db/structure.sql
db/structure.sql:CREATE FUNCTION delete_associated_project_namespace() RETURNS trigger
db/structure.sql:CREATE FUNCTION insert_into_loose_foreign_keys_deleted_records() RETURNS trigger
db/structure.sql:CREATE FUNCTION insert_namespaces_sync_event() RETURNS trigger
db/structure.sql:CREATE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
db/structure.sql:CREATE FUNCTION insert_projects_sync_event() RETURNS trigger
db/structure.sql:CREATE FUNCTION integrations_set_type_new() RETURNS trigger
db/structure.sql:CREATE FUNCTION next_traversal_ids_sibling(traversal_ids integer[]) RETURNS integer[]
db/structure.sql:CREATE FUNCTION set_has_external_issue_tracker() RETURNS trigger
db/structure.sql:CREATE FUNCTION set_has_external_wiki() RETURNS trigger
db/structure.sql:CREATE FUNCTION set_has_issues_on_vulnerability_reads() RETURNS trigger
db/structure.sql:CREATE FUNCTION unset_has_issues_on_vulnerability_reads() RETURNS trigger
db/structure.sql:CREATE FUNCTION update_location_from_vulnerability_occurrences() RETURNS trigger
db/structure.sql:CREATE FUNCTION update_vulnerability_reads_from_vulnerability() RETURNS trigger
For example:
-
delete_associated_project_namespace
(introduced in 14.3 via !69200 (merged)) -
insert_or_update_vulnerability_reads
(introduced in 14.8 via !75230 (merged))
As a result, a restore from backup that only drops the tables (vs. dropping the entire database) fails because the delete_associated_project_namespace
function already exists:
Performing migrations (this will initialized if needed)
psql:/srv/gitlab/db/structure.sql:23: ERROR: function "delete_associated_project_namespace" already exists with same argument types
rake aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --file /srv/gitlab/db/structure.sql --single-transaction gitlabhq_production
Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions
In psql --echo-hidden
, \df+
shows the magic query to show all functions defined. I simplified it to this query:
SELECT p.proname FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND l.lanname = 'plpgsql';
proname
------------------------------------------------
insert_namespaces_sync_event
insert_or_update_vulnerability_reads
insert_projects_sync_event
next_traversal_ids_sibling
set_has_issues_on_vulnerability_reads
unset_has_issues_on_vulnerability_reads
update_location_from_vulnerability_occurrences
update_vulnerability_reads_from_vulnerability
delete_associated_project_namespace
insert_into_loose_foreign_keys_deleted_records
integrations_set_type_new
set_has_external_issue_tracker
set_has_external_wiki
trigger_91dc388a5fe6
(14 rows)
I think we'll need to drop these functions.