Hi Magnus,
You are correct, that old (dirty) trick of using OIDs as the unique id
column, for MapServer, is no longer possible since the PostgreSQL 12.x
release. (using that trick is party my fault, for promoting it, for so
long)
So I was in the same boat as you, how to handle this with so many client
databases with gazillions of records. Here are some important points
that I now follow personally:
- for tables that do not have a unique column, or "gid" etc, I generate
a new "unique_id" column, such as:
ALTER TABLE mytable ADD COLUMN unique_id SERIAL PRIMARY KEY;
- then I change in the mapfile PostGIS layer:
DATA "geom FROM mydata USING UNIQUE unique_id USING SRID=3857"
- I was also concerned about what happens when restoring a dump (that
contains OIDs, from an old version) into a PostgreSQL >=12, but I've
done that so many times now that I can confidently tell you that there
is one single warning displayed during the restore, and then the
database is restored properly into PostgreSQL (without OIDs). I do this
on production databases, it works well.
Hope that helps a little.
-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
[email protected]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
