Deferred constraint prevents dropping topology

[ccrook]

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

[strk]

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

[Sandro Santilli <strk@…>]

In 7a5ae3e/git:

Execute pending constraints before dropping topology

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

[strk]

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.

[Sandro Santilli <strk@…>]

In ad7dc374/git:

Execute pending constraints before dropping topology

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

[Sandro Santilli <strk@…>]

In f3fa9aa/git:

Execute pending constraints before dropping topology

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

[Sandro Santilli <strk@…>]

In 5c1acb6/git:

Execute pending constraints before dropping topology

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

[ccrook]

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.