User-defined SRIDs are not working after Postgis 3.3.2 to 3.5.3 update

[Alexander Trufanov]

Well, unfortunately none of that works.

I spent a few hours digging in pg_catalog oids and trying to REINDEX
everything: DB, TABLES, indexes directly. Nothing helps.
I tried to change the extension name in pg_extension and then change it
back - that didn't help.
And then I found a solution:

1. rename postgis to something else in pg_extension
2. RUN THE BUGGY SQL - it works!
3. rename extension back to postgis
4. Run the code - it stil works!

Problem solved. 2nd step is mandatory.
It seems all one need is to force execution flow in this if expression
```
Oid ext_oid = get_extension_oid("postgis", true);
if (ext_oid != InvalidOid)
{
nsp_oid = postgis_get_extension_schema(ext_oid);
}
else
{
nsp_oid = postgis_get_full_version_schema();
}
```
to reach postgis_get_full_version_schema() instead of
postgis_get_extension_schema() at least once. And this fixes the problem in
scope of the current db for some reason....


пт, 25 июл. 2025 г. в 20:45, Erik Wienhold <ewie at ewie.name>:

> On 2025-07-25 15:53 +0200, Alexander Trufanov wrote:
> > >Can you please check which namespaces PostGIS finds with these two
> > queries:
> > > SELECT extnamespace::regnamespace FROM pg_extension WHERE extname =
> > 'postgis';
> > > SELECT pronamespace::regnamespace FROM pg_proc WHERE proname =
> > 'postgis_full_version';
> >
> > Both return "public".
> >
> > The full result record from pg_extension is
> > https://pastebin.com/raw/6mwrGhmH
> > For pg_proc it's: https://pastebin.com/a4pTVs9U
>
> I wouldn't rule out an index corruption in pg_catalog.  PostGIS gets the
> extension namespace by first getting the OID of extension "postgis" and
> then looking up the pg_extension row with that OID.  Maybe index
> "pg_extension_oid_index" is corrupted.
>
> Can you fix it by rebuilding the catalog indexes?:
>
>         REINDEX SCHEMA pg_catalog;
>
> --
> Erik Wienhold
>


-- 
With best regards,
Alexander Trufanov
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250725/bbcdde1a/attachment.htm>