[postgis-users] oid indexes issue

[Kralidis,Tom [Burlington]]

Sorry, hit send too early :)  Response below.

 > > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of 
> > Mark Cave-Ayland
> > Sent: 24 November, 2006 9:12 AM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] oid indexes issue
> > 
> > >
> > > 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.
> > 
> > 
> 

Thanks for the info.  So should one still invoke BOTH of the following
index creations:

# CREATE INDEX service_endpoints_idx ON service_endpoints USING GIST (
the_geom GIST_GEOMETRY_OPS );
# CREATE INDEX service_endpoints_idx_oid ON service_endpoints (
some_other_id );

Or just the first one?

..Tom