[postgis-users] OIDS, PostGIS and Quantum GIS

[Paul Ramsey]

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,  <pcreso at 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.neumann at carto.net> wrote:
>
>> From: Andreas Neumann <a.neumann at carto.net>
>> Subject: Re: [postgis-users] OIDS, PostGIS and Quantum GIS
>> To: jp.alcantara at geo-st.com, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> Cc: pcreso at 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, pcreso at 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 <ben at remoteinformation.com.au>
>> wrote:
>> >>
>> >>
>> >>> From: Ben Madin <ben at remoteinformation.com.au>
>> >>> Subject: Re: [postgis-users] OIDS, PostGIS and
>> Quantum GIS
>> >>> To: "PostGIS Users Discussion" <postgis-users at 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 at 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 at 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
>> >>>
>> >>> ben at remoteinformation.com.au
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>     Out here, it pays to
>> know...
>> >>>
>> >>>
>> >>>
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>