[postgis-users] oid indexes issue

[Mark Cave-Ayland]

>
> Hi,
>
> I'm attempting to recast a database instance with some scripts developed
> against PostGIS 1.1.1.  Below is a code snippet of the init:
>
> # CREATE TABLE service_endpoints (
>  resource_id serial primary key,
>  title text,
> );
>
> # SELECT AddGeometryColumn
> ('service_endpoints','the_geom',4326,'MULTIPOLYGON',2);
>
> # CREATE INDEX service_endpoints_idx ON service_endpoints USING GIST (
> the_geom GIST_GEOMETRY_OPS );
>
> # CREATE INDEX service_endpoints_idx_oid ON service_endpoints ( oid );
>
> This works successfully against
>
> - POSTGIS="1.1.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
> USE_STATS DBPROC="0.3.0" RELPROC="1.1.1" (needs proc upgrade)
> (1 row)
> - PostgreSQL 8.1.2
> - FC4
>
> This does NOT work successfully against:
>
> - POSTGIS="1.1.6" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
> USE_STATS
> - PostgreSQL 8.1.5
> - MS Windows Server 2003
>
> The error message I received when running against the latter environment
> is:
>
> "ERROR:  column "oid" does not exist"
>
> Are oid's no longer supported?
>
> Thanks
>
> ..Tom


Hi Tom,

Oids have been deprecated in PostgreSQL for several versions now, and
since PostgreSQL 8.1 you need to explicitly enable them in postgresql.conf
to support legacy applications. The chances are that your FC4 installation
has had this setting changed from the default installation, while the new
one hasn't.

I would highly recommend that for future compatibility you should modify
your application so it uses a different field (such as a primary key)
instead of oid to help avoid this issue in future. One of the benefits of
dropping oids is that you save an extra 4 bytes per row which can really
add up when you are working with large tables.


Kind regards,

Mark.