Re: oid's in views. - Mailing list pgsql-sql

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:
yourusq is very much like postgres' oid's. I have read somewhere that the postgres oid's are not really unique, is this
true?secondly do you use your usq's to perform updates on views which are joins of tables where rows in two or more
tablesshare a usq (but being unique in each table).
 

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

Regards,

Aasmund.

On Wed, 24 Oct 2001 08:28:46 -0700, "Josh Berkus" <josh@agliodbs.com> wrote:
> Hey, Dado,
> 
> 
> Well, if you have to go at it from that angle (hey, I have this USQ,
> where did it come from) then you're in trouble.  However, I never use it
> that way.  Let me give you an example of USQ use:
> 
> Modifications table
> 
> TABLE candidates
>   usq  INT4  DEFAULT NEXTVAL ('universal_sq'),
>   first_name VARCHAR NULL,
>   etc.
> 
> TABLE orders
>   usq INT4 DEFAULT NEXTVAL ('universal_sq'),
>   client_usq INT4 NOT NULL REFERENCES clients(usq),
>   etc.
> 
> TABLE mod_data
>   ref_usq INT4 NOT NULL PRIMARY KEY,
>   entry_date DATETIME NOT NULL,
>   entry_user INT4 NOT NULL references users(usq),
>   mod_date DATETIME NOT NULL,
>   mod_user INT4 NOT NULL references users(usq)
>   
> Thus I effectively have a One-to-One relationship between all of the
> tables posessing USQs and the mod_data table.  This means I can use one
> function to update this timestamp information, regardless of table,
> whenever a record is inserted or updated.
> 
> When I'm retrieving modification information, I never start with the
> mod_data table.  To do so would be asking the question, "What records,
> in any table, were modified by Josh on Decemebr 12th?" which really
> isn't useful and would be very difficult (but possible) to query.
> 
> Instead, the question I'm usually asking is, "When and by who was the
> current record on the screen modified?"  Which means that I am
> retrieving a single, unique, row from mod_data (SELECT * FROM mod_data
> WHERE ref_usq = 451).
> 
> This whole scheme, which has been very convenient for me, would not have
> been possible without a good way of insuring USQ uniqueness between
> tables, which, thankfully, our core team was foresighted enough to
> supply.  Unfortunately, that does mean that this solution is not
> portable to other RDBMSs, but as PostgreSQL grows in market share,
> that's less of a concern.
> 
> -Josh Berkus
> 
> 
> ______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
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46