Mercurial > gemma
changeset 4366:81dc260b38aa
Fixup database schema update
published_services.name is of type regclass and the underlying OID
likely changes if a referenced VIEW is replaced by dropping and
re-creating it. Thus, restore the field from temporary storage in
a varchar field.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 09 Sep 2019 18:51:42 +0200 |
| parents | e739a4806d7c |
| children | 6a985796f401 |
| files | schema/updates/1112/01.cleanup_views.sql |
| diffstat | 1 files changed, 8 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/updates/1112/01.cleanup_views.sql Mon Sep 09 18:07:10 2019 +0200 +++ b/schema/updates/1112/01.cleanup_views.sql Mon Sep 09 18:51:42 2019 +0200 @@ -1,3 +1,7 @@ +-- Avoid orphaned entries due to changed OIDs +ALTER TABLE sys_admin.published_services ADD _name varchar; +UPDATE sys_admin.published_services SET _name = name; + DROP VIEW waterway.gauges_geoserver CASCADE; CREATE VIEW waterway.gauges_geoserver AS SELECT @@ -114,6 +118,10 @@ ON isrs_fromtext(g.isrs_code) <@ s.section GROUP BY s.id; +-- Avoid orphaned entries due to changed OIDs +UPDATE sys_admin.published_services SET name = _name; +ALTER TABLE sys_admin.published_services DROP _name; + UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code' WHERE table_schema = 'waterway' AND table_name = 'gauges_geoserver';