Unlike a primary key, oids are not indexed (until/unless you explicitly index them). They are also not a good thing to depend on long-term, because the PgSQL dev team threatens to remove them complete from time to time. They are definitely a deprecated feature at this point.
P On Sat, May 9, 2009 at 1:12 PM, <pcr...@pcreso.com> wrote: > > Hi guys, > > Andreas is correct in that Postgres OIDs do not form a reliable primary key > for a table, and has explained why. > > However, in terms of providing an ad hoc column that QGIS can use to uniquely > identify a record, it is very unlikely that oids will not work. > > In theory it is possible that a given table may have two records with the > same oid value, but the odds against it are enormous, just not quite > impossible. > > I think it will be useful in this discussion to distinguish between a QGIS > unique identifier & a database primary key. They do not have to be the same > thing, and often are not, but when appropriate & convenient, a PK can also be > a QGIS unique identifier. > > > Brent Wood > > > > --- On Sat, 5/9/09, Andreas Neumann <a.neum...@carto.net> wrote: > >> From: Andreas Neumann <a.neum...@carto.net> >> Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS >> To: jp.alcant...@geo-st.com, "PostGIS Users Discussion" >> <postgis-users@postgis.refractions.net> >> Cc: pcr...@pcreso.com >> Date: Saturday, May 9, 2009, 11:04 PM >> Hi Juan, >> >> First let me say the following: >> >> Please include the QGIS developers list in the further >> discussion, since >> this seem to be more QGIS related than Postgis related? >> >> In fact, the QGIS developers may have already changed the >> pkey behavior >> in more recent releases - so please include them in the >> discussion. >> >> Regarding OIDs: those aren't really stable. They increase >> until a >> certain limit is reached and then they start again at zero >> or one. Here >> is what the PostgreSQL manual says about the topic: >> >> "The oid type is currently implemented as an unsigned >> four-byte integer. >> Therefore, it is not large enough to provide database-wide >> uniqueness in >> large databases, or even in large individual tables. So, >> using a >> user-created table's OID column as a primary key is >> discouraged. OIDs >> are best used only for references to system tables." >> (http://www.postgresql.org/docs/8.4/static/datatype-oid.html) >> >> So to be on the safe side, I wouldn't use oids as primary >> keys. >> >> In addition, I don't know if oids are still the same if you >> dump your >> data and import it on another machine. >> >> After all, primary keys are used as unique, stable >> identifiers, which I >> don't think that oids can reliably deliver. >> >> Thanks, >> Andreas >> >> Juan Pedro Pérez Alcántara wrote: >> > Hello all, >> > >> > thank you for all your responses, it is my first post >> to the list and I >> > thank you all for your interest in my question. >> > >> > Don't get me wrong for what I'm going to say, for, as >> said, I appreciate >> > all the ideas you say to me. Although I come from a >> GIS background and >> > I'm a geographer myself, the center of all my >> processes and projects is >> > and always will be the geographic relational model in >> PostGIS, so I >> > fully understand the importance of PK and the >> technicalities of building >> > and managing a relational model (in fact, this is a >> lecture I teach at >> > the university here in Spain). This issue of working >> with PK in QGIS has >> > been around for me for quite a long time, but, given >> that I've been >> > always dealed with it with no problems (int4 PK) I >> haven't had the need >> > to find a better solution. That's because I've been >> always able to find >> > a int4 PK suitable for my tables, just being because I >> designed them or >> > because I receive external data from, as you say, >> > "shapefiles-driven-minds" :) of fellow geographers >> here at the >> > university (no critics at all, I'm unable to perform >> the complex >> > geophysical and geosocial analysis they perform >> either) and it were easy >> > to adapt in the model. But now I'm receiving data from >> the government >> > that are well designed, with a "model-mind" approach, >> so this is not >> > always possible, due to the use of non-int4 PK or >> multi-field PK. >> > >> > I have experimented a little more with the OIDS >> approach and it seems >> > that they don't interfere with the actual PK fields of >> the tables. It >> > seems that although you created the table WITH OIDS, >> PK conflicts are >> > still successfully detected, so it seems that Postgre >> don't think of the >> > OID field as part of the PK fields and such. >> > >> > So, what do you think? Are my findings OK, or is there >> a, for me, >> > unforeseen reason why I should not use OIDS? >> > >> > Again, thank you all. >> > >> > Juan Pedro Pérez Alcántara >> > >> > >> > >> > On Fri, 2009-05-08 at 14:09 -0700, pcr...@pcreso.com >> wrote: >> > >> >> Hi, >> >> >> >> This is often an issue for people fromm a GIS >> background, where the issue of PK's is hidden or implicit, >> or ignored completely, as in shapefiles. >> >> >> >> You only need to lose a record from the dbf or shp >> file, or change the order of one of them, to find out why >> PK's are a good idea. Such things are safer explicit than >> implicit. They are a key reason for the success of the >> relational model, and relational databases. >> >> >> >> For me, the QGIS issue is not that it requires a >> PK, but that it can only use an int PK, wheras the DB >> supports PK's of any datatype, as well as composite keys >> using more than one column. (Also, there is at least one bug >> in QGIS when it comes to identifying a suitable column in a >> view, as in some unusual cases it fails to correctly >> identify a suitable column, but that is a separate issue, >> and only in particular circumstances) >> >> >> >> Generally this is not really a big issue, as QGIS >> can also use a unique index, and the DB supports multiple >> unique indexes on a table. So to work with QGIS, you can >> still use your non-int PK, or not use one at all, just add a >> new unique index on a suitable integer column for QGIS. >> >> >> >> A simple way to do this is: >> >> >> >> "alter table <table> add column qkey >> serial;" >> >> >> >> this is an easy way of creating an integer column >> automatically populated as a sequence, so it is filled with >> unique values as it is created. >> >> >> >> To be used by QGIS, this column must have a unique >> index created on it once it is there. >> >> >> >> "create unique index <table>_qkey_idx on >> <table>(qkey);" >> >> >> >> You can, of course, use your own names for the >> table, column & index.... >> >> >> >> >> >> HTH, >> >> >> >> Brent Wood >> >> >> >> >> >> >> >> --- On Sat, 5/9/09, Ben Madin <b...@remoteinformation.com.au> >> wrote: >> >> >> >> >> >>> From: Ben Madin <b...@remoteinformation.com.au> >> >>> Subject: Re: [postgis-users] OIDS, PostGIS and >> Quantum GIS >> >>> To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net> >> >>> Date: Saturday, May 9, 2009, 12:23 AM >> >>> Juan, >> >>> >> >>> Agreed that this can be annoying (especially >> in light of >> >>> some other GIS packages which don't seem to >> care at all, or >> >>> for instance shapefiles. >> >>> >> >>> I have had the same frustration when >> constructing >> >>> geometries in a query or for a view, or any >> type of >> >>> aggregate query really. This is also a problem >> for querymaps >> >>> in MapServer. >> >>> >> >>> The alternative I settled with was creating a >> false key - >> >>> for instance if the view was a makeline from >> points, making >> >>> an id that was the gid of the first * 100000 + >> the gid of >> >>> the second. >> >>> >> >>> That may help. It may not - but there are >> better minds than >> >>> mine hovering nearby, so I look forward to >> their >> >>> suggestions. >> >>> >> >>> cheers >> >>> >> >>> Ben >> >>> >> >>> >> >>> On 08/05/2009, at 7:36 PM, Andreas Neumann >> wrote: >> >>> >> >>> >> >>>> hm - what's wrong with having a primary >> key in every >> >>>> >> >>> table? Good DB-Design >> >>> >> >>>> requires primary keys. Other tools will >> refuse to work >> >>>> >> >>> with your data if >> >>> >> >>>> you don't use primary keys as well. As an >> example, >> >>>> >> >>> pgadmin3 requires a >> >>> >> >>>> primary key if you want to edit the data >> in the grid >> >>>> >> >>> view. >> >>> >> >>>> If you use the datatype "serial" or a >> sequence it is >> >>>> >> >>> not complicated at >> >>> >> >>>> all to use primary keys. >> >>>> >> >>>> Andreas >> >>>> >> >>>> On Fri, May 8, 2009 1:18 pm, Juan Pedro >> Pérez >> >>>> >> >>> Alcántara wrote: >> >>> >> >>>>> Hello, >> >>>>> >> >>>>> perhaps this is a silly one, but I'm >> having a hard >> >>>>> >> >> >>> time dealing with >> >>> >> >>>>> primary keys restrictions in Quantum >> GIS. Not >> >>>>> >> >> >>> always is possible or >> >>> >> >>>>> desirable to put a, sometimes, >> artificial int4 PK >> >>>>> >> >> >>> in some tables only to >> >>> >> >>>>> be able to load them in QGIS. Those >> restrictions >> >>>>> >> >> >>> are very frustrating. >> >>> >> >>>>> I have been messing around for a >> solution, and I >> >>>>> >> >> >>> have experimented with >> >>> >> >>>>> OIDS. This seems to be enough for >> QGIS, but I fear >> >>>>> >> >> >>> the behavior of OIDS. >> >>> >> >>>>> I don't like the idea of >> non-controlled PK in my >> >>>>> >> >> >>> tables. So my question >> >>> >> >>>>> is simple: does creating a table WITH >> OIDS means >> >>>>> >> >> >>> that the OIDS will be >> >>> >> >>>>> part of the PK of the table like you >> it or not, or >> >>>>> >> >> >>> you have to specify >> >>> >> >>>>> that the OIDS are part of the PK in >> the ADD >> >>>>> >> >> >>> CONSTRAINT statement? >> >>> >> >>>>> My hope is that PostgreSQL uses >> internally OIDS >> >>>>> >> >> >>> without interfering with >> >>> >> >>>>> the true PK of the table, while QGIS >> is happy by >> >>>>> >> >> >>> having them around. >> >>> >> >>>>> OIDS will not play any role in my >> model other than >> >>>>> >> >> >>> that. >> >>> >> >>>>> Greetings, >> >>>>> >> >>>>> Juan Pedro Pérez Alcántara >> >>>>> >> >>>>> >> >>>>> >> _______________________________________________ >> >>>>> postgis-users mailing list >> >>>>> postgis-users@postgis.refractions.net >> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >>>>> >> >>>>> >> >> >>>> --Andreas Neumann >> >>>> http://www.carto.net/neumann/ >> >>>> http://www.svgopen.org/ >> >>>> >> >>>> >> _______________________________________________ >> >>>> postgis-users mailing list >> >>>> postgis-users@postgis.refractions.net >> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >>>> >> >>> -- >> >>> Ben Madin >> >>> REMOTE INFORMATION >> >>> >> >>> t : +61 8 9192 5455 >> >>> f : +61 8 9192 5535 >> >>> m : 0448 887 220 >> >>> >> Broome WA 6725 >> >>> >> >>> b...@remoteinformation.com.au >> >>> >> >>> >> >>> >> >>> >> >>> >> >>> Out here, it pays to >> know... >> >>> >> >>> >> >>> >> >> > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
