[PostGIS] #6038: Using public.geometry_columns causes 'could not open relation with OID 188954716' after drop geometry tables
PostGIS
trac at osgeo.org
Tue Jan 20 03:24:51 PST 2026
#6038: Using public.geometry_columns causes 'could not open relation with OID
188954716' after drop geometry tables
--------------------------------+---------------------
Reporter: Lars Aksel Opsahl | Owner: pramsey
Type: defect | Status: new
Priority: blocker | Milestone:
Component: postgis | Version: master
Resolution: | Keywords:
--------------------------------+---------------------
Old description:
> I import the attached file to at database with postgis topology installed
>
> {{{
> pgtopo_import -f /tmp/test_data.gz gsk_utdata_25_784|psql t1
> }}}
>
> Then I create a new topology (may not be needed)
>
> {{{
> SELECT topology.createtopology('t1',4258);
>
> -- check that you have this the database
>
> SELECT * from topology.topology;
> id | name | srid | precision | hasz | useslargeids
> ----+-------------------+------+-----------+------+--------------
> 3 | gsk_utdata_25_784 | 4258 | 0 | f | f
> 4 | t1 | 4258 | 0 | f | f
> (2 rows)
>
> }}}
>
> The I close all sessions to database and start two new session and starts
> two transactions.
>
> Session 1
>
> {{{
> [local] lop at t1=# BEGIN;
> BEGIN
> [local] lop at t1=#
> }}}
>
> Session 2
>
> {{{
> [local] lop at t1=# BEGIN;
> BEGIN
> [local] lop at t1=#
> }}}
>
> Then in session one drop the imported topology schema
>
> {{{
>
> [local] lop at t1=# SELECT topology.droptopology('gsk_utdata_25_784');
> NOTICE: 00000: Dropping all layers from topology 'gsk_utdata_25_784' (3)
> LOCATION: exec_stmt_raise, pl_exec.c:3925
> NOTICE: 00000: drop cascades to 8 other objects
> DETAIL: drop cascades to table gsk_utdata_25_784.face
> drop cascades to table gsk_utdata_25_784.node
> drop cascades to table gsk_utdata_25_784.edge_data
> drop cascades to view gsk_utdata_25_784.edge
> drop cascades to sequence gsk_utdata_25_784.layer_id_seq
> drop cascades to table gsk_utdata_25_784.relation
> drop cascades to table gsk_utdata_25_784.edge_attributes
> drop cascades to table gsk_utdata_25_784.face_attributes
> LOCATION: reportDependentObjects, dependency.c:1227
> droptopology
> --------------------------------------
> Topology 'gsk_utdata_25_784' dropped
> (1 row)
>
> }}}
>
> In session two run this command inside the transaction and this hangs for
> ever.
>
> {{{
> select SRID from public.geometry_columns where (f_table_schema = 't1')
> and f_table_name = 'edge_data' and f_geometry_column = 'geom';
>
> }}}
>
> Go back two session one run commit
>
> {{{
> [local] lop at t1=# commit;
> COMMIT
> [local] lop at t1=#
>
> }}}
>
> Go back to session two and you see this error message
>
> {{{
> ERROR: XX000: could not open relation with OID 188955003
> LOCATION: relation_open, relation.c:62
>
> }}}
>
> This is tested on different servers with configs
>
> {{{
> PostgreSQL 16.10 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by
> Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
> POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
> GEOS="3.14.0-CAPI-1.20.4" PROJ="9.7.0 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
> DATABASE_PATH=/opt/homebrew/Cellar/proj/9.7.0/sharpr
>
> PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
>
> POSTGIS="3.7.0dev 3.6.0rc2-284-g013e9b0fd" [EXTENSION] PGSQL="160"
> GEOS="3.14.0-CAPI-1.20.4" (compiled against GEOS 3.13.1) PROJ="8.2.1
> NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
> DATABASE_PATH=/usr/share/oj
>
> }}}
New description:
I import the attached file to at database with postgis topology installed
{{{
pgtopo_import -f /tmp/test_data.gz gsk_utdata_25_784|psql t1
}}}
Then I create a new topology (may not be needed)
{{{
SELECT topology.createtopology('t1',4258);
-- check that you have this the database
SELECT * from topology.topology;
id | name | srid | precision | hasz | useslargeids
----+-------------------+------+-----------+------+--------------
3 | gsk_utdata_25_784 | 4258 | 0 | f | f
4 | t1 | 4258 | 0 | f | f
(2 rows)
}}}
The I close all sessions to database and start two new session and starts
two transactions.
Session 1
{{{
[local] lop at t1=# BEGIN;
BEGIN
[local] lop at t1=#
}}}
Session 2
{{{
[local] lop at t1=# BEGIN;
BEGIN
[local] lop at t1=#
}}}
Then in session one drop the imported topology schema
{{{
[local] lop at t1=# SELECT topology.droptopology('gsk_utdata_25_784');
NOTICE: 00000: Dropping all layers from topology 'gsk_utdata_25_784' (3)
LOCATION: exec_stmt_raise, pl_exec.c:3925
NOTICE: 00000: drop cascades to 8 other objects
DETAIL: drop cascades to table gsk_utdata_25_784.face
drop cascades to table gsk_utdata_25_784.node
drop cascades to table gsk_utdata_25_784.edge_data
drop cascades to view gsk_utdata_25_784.edge
drop cascades to sequence gsk_utdata_25_784.layer_id_seq
drop cascades to table gsk_utdata_25_784.relation
drop cascades to table gsk_utdata_25_784.edge_attributes
drop cascades to table gsk_utdata_25_784.face_attributes
LOCATION: reportDependentObjects, dependency.c:1227
droptopology
--------------------------------------
Topology 'gsk_utdata_25_784' dropped
(1 row)
}}}
In session two run this command inside the transaction and this hangs for
ever.
{{{
select SRID from public.geometry_columns where (f_table_schema = 't1') and
f_table_name = 'edge_data' and f_geometry_column = 'geom';
}}}
Go back two session one run commit
{{{
[local] lop at t1=# commit;
COMMIT
[local] lop at t1=#
}}}
Go back to session two and you see this error message
{{{
ERROR: XX000: could not open relation with OID 188955003
LOCATION: relation_open, relation.c:62
}}}
This is tested on different servers with configs
{{{
PostgreSQL 16.10 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by
Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit
POSTGIS="3.7.0dev 3.6.0rc2-152-g19e25a659" [EXTENSION] PGSQL="160"
GEOS="3.14.0-CAPI-1.20.4" PROJ="9.7.0 NETWORK_ENABLED=OFF
URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
DATABASE_PATH=/opt/homebrew/Cellar/proj/9.7.0/sharpr
PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
POSTGIS="3.7.0dev 3.6.0rc2-284-g013e9b0fd" [EXTENSION] PGSQL="160"
GEOS="3.14.0-CAPI-1.20.4" (compiled against GEOS 3.13.1) PROJ="8.2.1
NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
DATABASE_PATH=/usr/share/oj
}}}
--
Comment (by Lars Aksel Opsahl):
And after commit in session I have to run a new select to the data and
then works OK again
{{{
select SRID from public.geometry_columns where (f_table_schema = 't1') and
f_table_name = 'edge_data' and f_geometry_column = 'geom';
srid
------
4258
(1 row)
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6038#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list