Thread: oid's in views.
CREATE VIEW testview AS SELECT test.oid, tull FROM test; ERROR: Attribute 'oid' has a name conflict Name matches an existing system attribute I think this should be allowed, because if you do: CREATE VIEW testview AS SELECT tull FROM test; SELECT oid, tull FROM testview; The oid column will always be blank. The oid column can never be used for anything usefull. There are many workarounds to this problem - and I appreciate that altering this behaviour might be contradictory to somedesign philosophy you may have. Just my comment that's all! Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Aasmund,
> The oid column will always be blank. The oid column can never be used
> for anything usefull.
I believe the *official* reccommendation now is that you leave the OIDs
to the system, and create your own SERIAL values for row identification.
There are *lots* of problems with using OIDs as an index, and you've
just found one more!
-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
Attachment
On Fri, 19 Oct 2001 08:07:50 -0700, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > I believe the *official* reccommendation now is that you leave the OIDs > to the system, and create your own SERIAL values for row identification. > There are *lots* of problems with using OIDs as an index, and you've > just found one more! > > -Josh I don't use them in indexes foreign keys etc., however they are very usefull when your application tries to edit/update individualrows, as the oid is like a universal primary key. > > > ______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 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Aasmund, > I don't use them in indexes foreign keys etc., however they are very > usefull when your application tries to edit/update individual rows, > as the oid is like a universal primary key. You'd still be better off defining your own SERIAL columns and/or primary keys and using those. Some Trigger types require referencing the OID, but that's about it. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I apologize for not expressing myself clearly, and for replying without really giving any new information. In hindsight Iunderstand perfectly well, why you did not want to continue communication, but I hope that you or someone else, will giveme a second chance on the issue. Let me try to explain what I really meant: Let's say you are trying to make a generic database maintenance system. One of the tasks of this system is to update individualrows. For various reasons you may want to deal with views and not only tables. You may also want these views tobe updated on and inserted on. I completely agree with you that using OID as a primary key as in PRIMARY KEY(oid), andthen REFERENCES etc, or generally storing the oid anywhere in tables. However if you are simply trying to update it isvery usefull - especially with views as you have no way of determining what the primary key of the table is! Therefore I would suggest that one could assign the field oid in a view, (but that it still remains hidden from *) such thatthese kinds of issues are simpler. I have solved the problem in my case otherwise, and I totally agree that it is notimportant - it's just very usefull, in this and a few other cases. Regards, Aasmund. On Sat, 20 Oct 2001 12:58:09 -0700, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > This is my last e-mail, as you are not listening to my advice. > > *Do NOT use OIDs as your primary key for any table!* If you persist in > doing so, the problems you encounter are your own fault, and you will > get no help with them from the list or the developers. > > -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 > > > Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
That is what i did... Regards, Aasmund On Sat, 20 Oct 2001 23:34:44 -0400 (EDT), Joel Burton <joel@joelburton.com> wrote: > On Sun, 21 Oct 2001, Aasmund Midttun Godal wrote: > > > > Aasmund -- > > If your problem is that you want to update VIEWs and aren't sure what the > PK for the view is, could you follow a standard like this: > > CREATE TABLE person (social_security CHAR(9), full_name TEXT); > > CREATE VIEW pers_view AS select social_security AS primkey, > social_security, > full_name); > > and know that you can always find the "primkey" field in the view as one > to use in where clauses for updates? > > HTH, > -- > > Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton > Independent Knowledge Management Consultant > Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
On Mon, 22 Oct 2001, Josh Berkus wrote: > Each significant data table contains one column, the first column, > called "usq", for "universal sequence". This usq field may or may not > be the primary key for the table, but does have a unique index. The usq > is populated by a single sequence "universal_sq" which is shared between > tables, thus allowing all tables usq uniqueness between them. > > This strategy has allowed me to write a number of functions which are > table-agnostic, needing only the usq to do their job (such as a function > that creates modification hisotry). Josh -- Good example! I'll bet a lot of PG users may have never realized that you can use the same sequence across several tables. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
Aasmund,
Thank you for the clarification. Now that I know what you are doing, I
went through exactly the same thing about a year ago ... which is how we
discovered some additional problems with using OIDs in database design.
I was trying to spare you the same dead end.
> > If your problem is that you want to update VIEWs and aren't sure
> what the
> > PK for the view is, could you follow a standard like this:
> >
> > CREATE TABLE person (social_security CHAR(9), full_name TEXT);
> >
> > CREATE VIEW pers_view AS select social_security AS primkey,
> > social_security,
> > full_name);
> >
> > and know that you can always find the "primkey" field in the view
> as one
> > to use in where clauses for updates?
This is more-or-less a correct approach. As it does not address the
issue of different data types, let me tell you what I did:
Each significant data table contains one column, the first column,
called "usq", for "universal sequence". This usq field may or may not
be the primary key for the table, but does have a unique index. The usq
is populated by a single sequence "universal_sq" which is shared between
tables, thus allowing all tables usq uniqueness between them.
This strategy has allowed me to write a number of functions which are
table-agnostic, needing only the usq to do their job (such as a function
that creates modification hisotry).
-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
Attachment
No, many of the tables have primary keys already, serial, single key or several keys. I don't want to 'serialize' every table,because it does not make sense. Oid's however are very usefull as I can use them as a primary key on any table. Regards, Aasmund. On Fri, 19 Oct 2001 15:38:16 -0700, "Josh Berkus" <josh@agliodbs.com> wrote: > Aasmund, > > > You'd still be better off defining your own SERIAL columns and/or > primary keys and using those. Some Trigger types require referencing > the OID, but that's about it. > > -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 Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Josh Berkus wrote: >Aasmund, > > Thank you for the clarification. Now that I know what you are doing, I >went through exactly the same thing about a year ago ... which is how we >discovered some additional problems with using OIDs in database design. >I was trying to spare you the same dead end. > >>>If your problem is that you want to update VIEWs and aren't sure >>> >>what the >> >>>PK for the view is, could you follow a standard like this: >>> >>>CREATE TABLE person (social_security CHAR(9), full_name TEXT); >>> >>>CREATE VIEW pers_view AS select social_security AS primkey, >>> social_security, >>> full_name); >>> >>>and know that you can always find the "primkey" field in the view as one >>> >>>to use in where clauses for updates? >>> > >This is more-or-less a correct approach. As it does not address the >issue of different data types, let me tell you what I did: > >Each significant data table contains one column, the first column, >called "usq", for "universal sequence". This usq field may or may not >be the primary key for the table, but does have a unique index. The usq >is populated by a single sequence "universal_sq" which is shared between >tables, thus allowing all tables usq uniqueness between them. > >This strategy has allowed me to write a number of functions which are >table-agnostic, needing only the usq to do their job (such as a function >that creates modification hisotry). > >-Josh Berkus > Hi Josh! Once you have your usq, how do you get more info about that row? How do you know which table it came from? Thanks -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 dado@wildbrain.com San Francisco, CA.
Hey, Dado,
> Hi Josh!
> Once you have your usq, how do you get more info about that row?
> How do you know which table it came from?
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
Attachment
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
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