Cannot alter graph because it is being used by active queries in this session

[double-u-a]

Expected behavior and actual behavior

Expect an UPDATE query containing pgr_drivingDistance to no longer be actively running when the subsequent ALTER TABLE query on the same table is executed.

However, if I run ALTER TABLE in the same script right after the pgrouting UPDATE, I get the following SQL error in pgAdmin:

ERROR: cannot ALTER TABLE "network" because it is being used by active queries in this session
SQL state: 55006

I believe SQL generally goes sequentially through each query in a script, ensuring one has finished before moving onto the next one, however it seems I cannot modify the network table in the same script, I have to run the pgrouting UPDATE on its own in pgAdmin and then run the ALTER TABLE afterwards separately. This means I cannot alter tables after a pgRouting UPDATE.

Steps to reproduce the problem

A table called network with id, source, target and geom columns, cost is ST_Length(geom)

ALTER TABLE network
DROP COLUMN IF EXISTS node_count,
ADD COLUMN node_count INTEGER;

UPDATE network AS network
SET node_count=count.sum
FROM
        (SELECT
        from_v,
        sum(node) AS sum
        FROM
            pgr_drivingDistance(
            'SELECT id, source, target, ST_Length(geom) AS cost FROM network',
            ARRAY(SELECT DISTINCT source FROM network),
            1,
            false)
    GROUP BY from_v) AS count
    WHERE network.source=count.from_v
    ;

ALTER TABLE network
DROP COLUMN IF EXISTS another_column,
ADD COLUMN another_column INTEGER;

This returns the error

ERROR: cannot ALTER TABLE "network" because it is being used by active queries in this session
SQL state: 55006

If I remove that second ALTER TABLE it will run, and if I run the ALTER TABLE as a separate command immediately it will run.

I have also experienced similar issues with pgrDijkstra, so I have trouble running pgRouting queries in one script on the same table, I have to either run each separately or resort to creating a new table with each query so there is no chance of a lock, but this would mean filling the database up with many tables and making management difficult.

I'm not sure if I'm misusing the pgRouting function in any way, or if pgRouting functions are obstructing any subsequent queries on the same table in the same script.

Specifications like the version of pgRouting/PostGIS and PostgreSQL as well as Operating System

On Windows 7 64bit

PostgreSQL 9.6beta4, compiled by Visual C++ build 1800, 64-bit
POSTGIS="2.3.1 r15264" GEOS="3.6.0-CAPI-1.10.0 r4265" SFCGAL="1.3.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.1.1, released 2016/07/07 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="0.12" RASTER
(2.3.0,pgrouting-2.3.0,8c86efd,master,1.59.0)

Also replicated on Windows Server 2008 R2 Standard 64 bit with older versions of pgRouting and PostGIS

PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit
POSTGIS="2.1.7 r13414" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER
(2.2.3,pgrouting-2.2.3,6d05b9b,master,1.59.0)

[woodbri]

Try wrapping your update statement in a BEGIN; ... COMMIT; block and see if that helps.

[double-u-a]

Thanks for your reply. Wrapping the UPDATE in BEGIN; ... COMMIT; runs without any error. So in this specific case it is solved.

However this causes another issue in other cases because it is not possible to easily use BEGIN; ... COMMIT; within a PL/pgsql function as far as I am aware.

[woodbri]

Correct, all PL/psql function are run within an implicit transaction and you can not have sub transaction within them. I suspect this has something to do with the Windows client library, but I have not tried to reproduce this problem on Linux. I suppose that it is possible that we are not releasing some resource within our code that is in effect holding the transaction open and the BEGIN .. COMMIT is forcing the resource to be released, but this is only speculation. All of the develop is getting done on Linux.

[double-u-a]

Hello, I ran a standalone test as your reply asked on OSGeoLive Ubuntu 16.04 with this self contained code and got the same error:

DROP TABLE IF EXISTS network;
CREATE TABLE network AS
SELECT *
from (
   VALUES
    (1::int, 1::int, 2::int, 100::int),
    (2::int, 2::int, 3::int, 100::int),
    (3::int, 3::int, 4::int, 100::int),
    (4::int, 5::int, 6::int, 100::int)
) as t (id, source,target,cost);

ALTER TABLE network
DROP COLUMN IF EXISTS node_count,
ADD COLUMN node_count INTEGER;

UPDATE network AS network
SET node_count=count.sum
FROM
        (SELECT
        from_v,
        sum(node) AS sum
        FROM
            pgr_drivingDistance(
            'SELECT id, source, target, cost FROM network',
            ARRAY(SELECT DISTINCT source FROM network),
            1,
            false)
    GROUP BY from_v) AS count
    WHERE network.source=count.from_v
    ;

ALTER TABLE network
DROP COLUMN IF EXISTS another_column,
ADD COLUMN another_column INTEGER;

[cvvergara]

@double-u-a Sorry for my late response. I added a test on develop (currently holding verson 2.4, to be released in 2 weeks) where this does not happen. If you want to try it with 2.4-alpha please download and build: https://github.com/pgRouting/pgrouting/releases/tag/v2.4.0-alpha note: not all the packagers do a package for the alpha pre-releases. for windows: On this page: http://winnie.postgis.net/download/windows/pg96/buildbot/ try the one marked as develop

[double-u-a]

Thanks very much for looking into it; I tried the pgrouting-pg96-binaries-developw64gcc48 version on windows and the test case runs without error. So I suppose the 9.4 release solves the issue anyway?

[cvvergara]

no, that its the pgrouting 2.4 version, and it solves the issue.

[cvvergara]

The final pgrouting 2.4 release is due on march.