Opened 22 months ago

Closed 22 months ago

Last modified 18 months ago

#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 Sandro Santilli <strk@…>, 22 months ago

In 9dec1cfe/git:

Mark topology.topology_id_seq as a user data

References #5344 in master branch (3.4.0dev)

comment:2 by strk, 22 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?)

comment:3 by strk, 22 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 ?

in reply to:  2 comment:4 by robe, 22 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

in reply to:  3 comment:5 by robe, 22 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 strk, 22 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:7 by Sandro Santilli <strk@…>, 22 months ago

In f23f6b7/git:

Mark topology.topology_id_seq as a user data

References #5344 in stable-3.3 branch (3.3.3dev)

comment:8 by Sandro Santilli <strk@…>, 22 months ago

In 3ce97f4/git:

Mark topology.topology_id_seq as a user data

References #5344 in stable-3.2 branch (3.2.5dev)

comment:9 by Sandro Santilli <strk@…>, 22 months ago

In a592821/git:

Mark topology.topology_id_seq as a user data

References #5344 in stable-3.1 branch (3.1.9dev)

comment:10 by Sandro Santilli <strk@…>, 22 months ago

Resolution: fixed
Status: newclosed

In 62a9a42/git:

Mark topology.topology_id_seq as a user data

Closes #5344 in stable-3.0 branch (3.0.9dev)

comment:11 by strk, 22 months ago

Milestone: PostGIS 3.4.0PostGIS 3.0.9

comment:12 by strk, 18 months ago

This change introduced a regression with pg_dump, see #5395

comment:13 by Sandro Santilli <strk@…>, 18 months ago

In 6f8b43f/git:

Grant select on topology sequences

Allows public dumping of postgis-topology enabled database
(as long as there are no topologies)

References #5344 in master branch (3.4.0dev)

Note: See TracTickets for help on using tickets.