Opened 5 months ago

Closed 5 months ago

Last modified 5 months ago

#5115 closed defect (fixed)

Deferred constraint prevents dropping topology

Reported by: ccrook Owned by: strk
Priority: medium Milestone: PostGIS 3.0.6
Component: topology Version: 3.0.x
Keywords: Cc:

Description

I have a use case for creating and dropping a temporary topology in a plpgsql procedure. This fails because of the pending constraint checks. The example code is:

DO $script$
BEGIN
    PERFORM topology.createtopology('mytopology',4167,0.00001);
    PERFORM topology.topogeo_addlinestring('mytopology',ST_SetSRID(
ST_MakeLine(
              ST_MakePoint(167.0,-45.0),
  ST_MakePoint(167.00001,-45.00001)
),4167));
    PERFORM topology.droptopology('mytopology');
END
$script$

This generates an error:

ERROR:  cannot DROP TABLE "edge_data" because it has pending trigger events
CONTEXT:  SQL statement "DROP SCHEMA mytopology CASCADE"
PL/pgSQL function droptopology(character varying) line 38 at EXECUTE
SQL statement "SELECT topology.droptopology('mytopology')"
PL/pgSQL function inline_code_block line 9 at PERFORM
SQL state: 55006

Change History (7)

comment:1 by strk, 5 months ago

Status: newassigned

Issue confirmed. The issue is present also in PostGIS-3.0 (and probably earlier too). Work in progress fix is here: https://gitlab.com/postgis/postgis/-/merge_requests/73

Those pending constraints need to be executed in order for the drop to be accepted. You can force executing them with this:

SET constraints ALL IMMEDIATE;

Or, to be stricter:

SET constraints mytopology.next_right_edge_exists, mytopology.next_left_edge_exists IMMEDIATE;

To be honest I forgot why we're making those constraints deferred, it may have been a bad choice

comment:2 by Sandro Santilli <strk@…>, 5 months ago

In 7a5ae3e/git:

Execute pending constraints before dropping topology

References #5115 in master branch (3.3.0dev)
Includes test

comment:3 by strk, 5 months ago

Milestone: PostGIS 3.3.0PostGIS 3.0.6
Version: 3.2.x3.0.x

I now remembered: constraints are deferred because it would otherwise be hard to construct topologies as the first edge in a ring of edges would need to reference the id of an edge which was not inserted yet… THere are probably ways to let the caller deal with deferring constraints but doing it at this time would be a behaviour change so I'd avoid to do that unless there's a very good reason.

comment:4 by Sandro Santilli <strk@…>, 5 months ago

In ad7dc374/git:

Execute pending constraints before dropping topology

References #5115 in 3.2 branch (3.2.2dev)
Includes test and NEWS item

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

In f3fa9aa/git:

Execute pending constraints before dropping topology

References #5115 in 3.1 branch (3.1.6dev)
Includes test and NEWS item

comment:6 by Sandro Santilli <strk@…>, 5 months ago

Resolution: fixed
Status: assignedclosed

In 5c1acb6/git:

Execute pending constraints before dropping topology

Closes #5115 in 3.0 branch (3.0.6dev)
Includes test and NEWS item

comment:7 by ccrook, 5 months ago

Thanks Sandro. Really appreciate the extraordinarily fast fix and also providing a good workaround which has resolved this problem for me in the short term.

Note: See TracTickets for help on using tickets.