[mapserver-users] OIDS in postgresql Postgis

[Jeff McKenna]

Hi Magnus,

This morning I did more testing on this, and discovered in the source 
code that MapServer still does in fact fallback to looking for an OID 
column, when no unique ID column is found (or specified in the DATA 
statement).  I've filed the ticket at 
https://github.com/MapServer/MapServer/issues/6367 (and EvenR has 
already applied a fix for this [very elegantly checking PG version 
before relying on OIDs as fallback] at 
https://github.com/MapServer/MapServer/pull/6368).  It would be great if 
you can test the fix locally and give feedback.

I've also added a note and warning about OIDs and unique IDs at 
https://mapserver.org/input/vector/postgis.html

So, now that I understand the issue, here is a very important point:

- for every PostGIS layer in my MapServer mapfile, I always specify a 
unique ID column with the "using unique" syntax, in the DATA statement
- that avoids any issue (such as your case, where you likely did not 
specify a unique ID column, and MapServer tried to guess)
- (and of course, I no longer use OIDs in my DATA statements, as these 
were removed in PostgreSQL 12.0)

Thanks again Magnus for this report, you spawned some great changes to 
both the source code and documentation!

-jeff



-- 
Jeff McKenna
GatewayGeo: Developers of MS4W, MapServer Consulting and Training
co-founder of FOSS4G
http://gatewaygeo.com/




On 2021-07-08 5:54 a.m., Magnus Askaner wrote:
> Support for OIDS has been dropped from Postgresql in recent versions.
> After postgresql upgrade, trying to select a feature from a Postgis 
> postgresql table gives an error  in postgresql logs: (sensitive info 
> redacted)
> 
>> 2021-07-08 08:05:45.756 UTC [2270616] user_name at db ERROR:  column 
>> "oid" does not exist at character 70
>> 2021-07-08 08:05:45.756 UTC [2270616] user_name at db HINT: Perhaps you 
>> meant to reference the column "[table_name].gid".
>> 2021-07-08 08:05:45.756 UTC [2270616] user_name at db STATEMENT: select 
>> "[column_name]"::text,ST_AsBinary(ST_Force2D("the_geom"),'NDR') as 
>> geom,"oid"::text from [table_name] where "the_geom" && 
>> ST_GeomFromText('POLYGON(([....]))',find_srid('','[table_name]','the_geom')) 
>> and ([column_name]='value')
> 
> It seems like mapserver still tries to look up oids.
>  From earlier documentation I have got the impression that mapserver 
> depends on oids when selecting from a postgis table.
> Could this be avoided?
> 
> Calling mapserv cgi with wget gives "Missing Schema".
> The connection string works.
> 
> Versions:
> $ mapserv -v
> MapServer version 7.4.3 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ 
> SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS 
> SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER 
> SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT 
> SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI 
> SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS 
> INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
> 
> Postgreql:
> version(): PostgreSQL 12.7 (Ubuntu 12.7-0ubuntu0.20.04.1) on 
> x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 
> 9.3.0, 64-bit
> POSTGIS_full_version() : POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" 
> GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.4" LIBJSON="0.13.1" 
> LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>