pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Aasmund Midttun Godal <postgresql@envisity.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Cc: dado@wildbrain.com
Subject: Re: oid's in views.
Date: Mon, 29 Oct 2001 08:35:13 -0800
Message-ID: <web-494080@davinci.ethosmedia.com> (raw)
In-Reply-To: <20011029005151.31231.qmail@ns.krot.org>

Aasmund,

> Well, the first time I thought your solution, I chose not to use it
> as it requires large modifications in my data-structure. However it
> has grown on me and I am now considering whether or not to use it. My
> first thought though: your usq is very much like postgres' oid's. 

Yes.  My original design had been to use the OIDs for this purpose, but
a couple members of the core team pointed out some difficulties in using
OIDs as an integral part of your data structure.

> I
> have read somewhere that the postgres oid's are not really unique, is
> this true? 

Only for very large databases.  OIDs are a 4-byte signed integer,
meaning an upper limit of 2.4 billion.  If your database accumulates
more total objects than that, the OIDs will start to "roll over".
However, this only becomes a problem for the database when objects of
the same type get assigned the same OID ... which nobody has yet
reported in the field.

>secondly do you use your usq's to perform updates on views
> which are joins of tables where rows in two or more tables share a
> usq (but being unique in each table).

Nope, since the whole point of USQs is to be unique in the database.
This is entirely possible due only to PostgreSQL's independent sequence
implementation, which allows me to be sure that now two distinct rows
will ever have the same USQ.

> Finally, do you not feel that these USQ are in contradiction to many
> of the philosophies entrenched in SQL? (not that it matters :).

Yes.  Fabian Pascal and CJ Date would chew me out for this design, I
think.  It does not adhere to the exisiting rules for Relational
Database Design, and the implementation is entirely proprietary, due to
the dependance on a platform-specific sequencing mechanism.  Further, as
Dado pointed out, it's very difficult from the multi-child table to tell
what where the parent record is.  Lastly, I cannot use standard
REFERENCES constraints, having to instead write my own constraints and
triggers, further limiting RDBMS platform independance.

However, given all that, the convenience of this design is enormous, and
consistency-checking procedures are easy to write given the
consolidation of most activity into a few tables.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







view thread (13+ messages)

Message-ID: <web-494080@davinci.ethosmedia.com>
Permalink:  ../web-494080@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-494080@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, postgresql@envisity.com, dado@wildbrain.com
  Subject: Re: oid's in views.
  In-Reply-To: <web-494080@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox