[mapserver-users] OIDS in postgresql Postgis

[Jeff McKenna]

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 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)"
>