<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2722" name=GENERATOR><o:SmartTagType
name="PostalCode"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="Street"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="address"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="PlaceType"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="PlaceName"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="State"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="City"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="place"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><!--[if !mso]>
<STYLE>st1\:* {
BEHAVIOR: url(#default#ieooui)
}
</STYLE>
<![endif]-->
<STYLE>@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
H3 {
FONT-WEIGHT: bold; FONT-SIZE: 13.5pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
PRE {
FONT-SIZE: 10pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Courier New"
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff size=2>Hi
Paul,</FONT></SPAN></DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff size=2>Kind
regards,</FONT></SPAN></DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=246020910-29092005><FONT face=Arial color=#0000ff
size=2>Mark.</FONT></SPAN></DIV><!-- Converted from text/plain format -->
<P><FONT size=2>------------------------<BR>WebBased Ltd<BR>South West
Technology Centre<BR>Tamar Science Park<BR>Plymouth<BR>PL6 8BT<BR><BR>T: +44
(0)1752 791021<BR>F: +44 (0)1752 791023<BR>W: <A
href="http://www.webbased.co.uk/">http://www.webbased.co.uk</A><BR> </FONT>
</P>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>R.
Paul Warriner<BR><B>Sent:</B> 28 September 2005 17:12<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] OID
usage with PosGIS<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hello
Folks,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Are OIDs something to be worried
about with PostGIS, since they will not be manadatory in the future for
PostgreSQL?<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Partial PostgreSQL
FAQ:<o:p></o:p></SPAN></FONT></P>
<H3 style="MARGIN: 0in 0in 0pt"><A name=4.12><B><FONT face=Arial size=2><SPAN
style="FONT-WEIGHT: normal; FONT-SIZE: 10pt; FONT-FAMILY: Arial">4.12</SPAN></FONT></B></A><FONT
face=Arial size=2><SPAN
style="FONT-WEIGHT: normal; FONT-SIZE: 10pt; FONT-FAMILY: Arial">) What is an
OID? What is a CTID?<o:p></o:p></SPAN></FONT></H3>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Partial PostGIS mail from <A
href="http://postgis.refractions.net/pipermail/postgis-users/2005-May/007995.html">http://postgis.refractions.net/pipermail/postgis-users/2005-May/007995.html</A>:<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Ok. Next release will have OID
usage forced.<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Both 1.0 and HEAD branches updated
like this.<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I wouldn't know where to put the
workaround documentation, hopefully<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">affected people are ok with this
list's archive.<o:p></o:p></SPAN></FONT></P>
<P style="MARGIN: 0in 0in 0pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Regards,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Paul<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">R. Paul
Warriner<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Network
Coordinator<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Town of <st1:place
w:st="on"><st1:PlaceName w:st="on">Orchard</st1:PlaceName> <st1:PlaceType
w:st="on">Park</st1:PlaceType></st1:place><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><st1:Street w:st="on"><st1:address w:st="on"><FONT
face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">4295 South Buffalo
St</SPAN></FONT></st1:address></st1:Street><FONT face=Arial color=navy
size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><st1:place w:st="on"><st1:City w:st="on"><FONT face=Arial
color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Orchard
Park</SPAN></FONT></st1:City><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">, <st1:State
w:st="on">NY</st1:State> <st1:PostalCode
w:st="on">14127</st1:PostalCode></SPAN></FONT></st1:place><FONT face=Arial
color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">(716)
662-6403<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">(716) 662-6479
fax<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV></BLOCKQUOTE></BODY></HTML>