#5344 closed defect (fixed)
Restoring extension based topology dump fails resetting the topology sequence
| Reported by: | strk | Owned by: | strk |
|---|---|---|---|
| Priority: | medium | Milestone: | PostGIS 3.0.9 |
| Component: | topology | Version: | master |
| Keywords: | Cc: |
Description
Restored dump of database with extension-based topology and a topology fails to restore the topology sequence, so that next invocation of CreateTopology finds duplicated key.
To reproduce:
touch empty.sql empty_expected
regress/run_test.pl -v \
--dumprestore --upgrade \
--topology --extension \
--before-upgrade-script topology/test/regress/hooks/hook-before-upgrade-topology.sql \
--after-upgrade-script topology/test/regress/hooks/hook-after-upgrade-topology.sql \
empty
Caught by CI here: https://gitlab.com/postgis/postgis/-/jobs/3797640891#L5292
Change History (13)
comment:1 by , 14 months ago
follow-up: 4 comment:2 by , 14 months ago
I've pushed the fix of the bug, but I've seen that upgrading from a version of the extension PRIOR to the fix DOES NOT fix the database. That is, the pg_catalog.pg_extension_config_dump call does not end in the extension upgrade script.
I suspect this is NOT just an issue with topology but also with all other extensions.
Regina: do you have experience with this ? Is it ok to call pg_catalog.pg_extension_config_dump multiple times ? (idempotent?)
follow-up: 5 comment:3 by , 14 months ago
Figured: we DO re-run pg_extension_config_dump multiple times, so upgrades are ok. My only problem was that run_test.pl is only upgrading AFTER dumping the source DB, thus failing to recover the sequence in an appropriate way. The only way to support in some automatic way the restore of dumps from pre-fix versions would be to re-set the sequence in the upgrade script ALWAYS, after looking at the max value of the topology.id. Would youf ind that too invasive Regina ?
comment:4 by , 14 months ago
Replying to strk:
I've pushed the fix of the bug, but I've seen that upgrading from a version of the extension PRIOR to the fix DOES NOT fix the database. That is, the pg_catalog.pg_extension_config_dump call does not end in the extension upgrade script.
I suspect this is NOT just an issue with topology but also with all other extensions.
Regina: do you have experience with this ? Is it ok to call pg_catalog.pg_extension_config_dump multiple times ? (idempotent?)
Yes we used to have issue with postgis with the spatial_ref_sys dump that it would call for each upgrade and then the config table would explode. They fixed this issue a while ago, so should be safe to call for every ALTER EXTENSION update. In fact I think we do that for spatial_ref_sys
comment:5 by , 14 months ago
Replying to strk:
Figured: we DO re-run pg_extension_config_dump multiple times, so upgrades are ok. My only problem was that run_test.pl is only upgrading AFTER dumping the source DB, thus failing to recover the sequence in an appropriate way. The only way to support in some automatic way the restore of dumps from pre-fix versions would be to re-set the sequence in the upgrade script ALWAYS, after looking at the max value of the topology.id. Would youf ind that too invasive Regina ?
No I wouldn't
comment:6 by , 14 months ago
I just realized it isn't on upgrade that we can fix the sequence as there's no extension upgrade being performed, it's just a restore from a dump. And we cannot fix anything upon creating the extension either because at that time the topology.topology table would be empty. The only way to fix a restore from old dump would be to have an "after-restore-hook" of some kind, or manual intervention by DBA.
It could maybe be the CreateTopology function itself looping over sequence values until it finds a good fit, if we want it to be easier for users.
comment:11 by , 14 months ago
| Milestone: | PostGIS 3.4.0 → PostGIS 3.0.9 |
|---|
In 9dec1cfe/git: