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@db ERROR: column
"oid" does not exist at character 70
2021-07-08 08:05:45.756 UTC [2270616] user_name@db HINT: Perhaps you
meant to reference the column "[table_name].gid".
2021-07-08 08:05:45.756 UTC [2270616] user_name@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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users
