FW: [postgis-users] OID usage with PosGIS

[R. Paul Warriner]

Mark, thank you.

 

I am in the early stages of our design, and I didn't want to head off in a
direction that would require signifigant upgrades so soon.

The part that concerned me, was the "OID usage forced" in the postgis-users
list, but I see now it is the geometry_columns table 

that is predominately affected.

 

Regards,

Paul

 

R. Paul Warriner

Network Coordinator

Town of Orchard Park

4295 South Buffalo St

Orchard Park, NY 14127

(716) 662-6403

(716) 662-6479 fax

 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Thursday, September 29, 2005 6:24 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] OID usage with PosGIS

 

Hi Paul,

 

Row-level OIDS have not been mandatory in PostgreSQL for several releases
now; in previous releases specifying a CREATE TABLE statement without any
additional parameters would automatically generate a table with row-level
OIDS. However, as of 8.1 this behaviour will change and so if OIDS are
required then an explicit WITH OIDS clause must be added to the CREATE TABLE
clause of any table that requires them.

 

The reason this is done for the geometry_columns table is that it needs to
refer to its own rows during certain updates, and there is no unique key
given in the Simple Features spec. I don't think that the "churn rate" would
be particularly high for a geometry_columns table compared to normal tables
anyway, so I can't see this being a great problem.

 

Also if you are worried about general PostGIS operation, PostGIS works fine
without row level OIDS on tables. I've recently dropped the OID column on
some of our larger geometry tables in production, and the only change I
needed to make was to add a "using UNIQUE" clause to the mapserver data
clause used on our vector server.  So in short, regardless of whether OIDS
are enabled or disabled by default with PostgreSQL, PostGIS will continue to
work fine.

 

 

Kind regards,

 

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk <http://www.webbased.co.uk/> 
  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of R. Paul
Warriner
Sent: 28 September 2005 17:12
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] OID usage with PosGIS

Hello Folks,

 

Are OIDs something to be worried about with PostGIS, since they will not be
manadatory in the future for PostgreSQL?

 

Partial PostgreSQL FAQ:


4.12) What is an OID? What is a CTID?


Every row that is created in PostgreSQL gets a unique OID unless created
WITHOUT OIDS. OIDs are autotomatically assigned unique 4-byte integers that
are unique across the entire installation. However, they overflow at 4
billion, and then the OIDs start being duplicated. PostgreSQL uses OIDs to
link its internal system tables together.

To uniquely number columns in user tables, it is best to use SERIAL rather
than OIDs because SERIAL sequences are unique only within a single table.
and are therefore less likely to overflow. SERIAL8 is available for storing
eight-byte sequence values.

CTIDs are used to identify specific physical rows with block and offset
values. CTIDs change after rows are modified or reloaded. They are used by
index entries to point to physical rows.

 

Partial PostGIS mail from
http://postgis.refractions.net/pipermail/postgis-users/2005-May/007995.html:

Ok. Next release will have OID usage forced.

Both 1.0 and HEAD branches updated like this.

I wouldn't know where to put the workaround documentation, hopefully

affected people are ok with this list's archive.

 

Regards,

Paul

 

R. Paul Warriner

Network Coordinator

Town of Orchard Park

4295 South Buffalo St

Orchard Park, NY 14127

(716) 662-6403

(716) 662-6479 fax

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050929/071fa51e/attachment.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: ATT00150.txt
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050929/071fa51e/attachment.txt>