Обсуждение: OID's
Hi, I am planning to use OID for referencing as instead PK -->> FK on this situation would require alot of tables, OID would seen to nice solution. My worry with OID's is when i do SQL dump and rebuild the Database will OID will change making referencing certain records impossible. -- Jamie Deppeler <font face="Verdana, Helvetica" size="2"> <font color="#800080" face="Verdana, Helvetica">
On Nov 15, 2004, at 3:52 PM, Jamie Deppeler wrote: > Hi, > > I am planning to use OID for referencing as instead PK -->> FK on > this situation would require alot of tables, OID would seen to nice > solution. > > My worry with OID's is when i do SQL dump and rebuild the Database > will OID will change making referencing certain records impossible. Don't use OIDS. Just add a nice SERIAL column to the tables you want as foreign keys (and if you have questions about sequences, check the FAQ). OIDS are a system level implementation. They are no longer required (you can make tables without OIDS) and they may go away someday. Plus, you avoid nastiness like OID wraparound. Michael Glaesemann grzm myrealbox com (Is it just me, or have there been a slew of these OID posts lately?)
On Nov 15, 2004, at 4:05 PM, Michael Glaesemann wrote: > Just add a nice SERIAL column to the tables you want as foreign keys > (and if you have questions about sequences, check the FAQ). Erg... SERIALs on the tables as primary keys. Integers on tables referencing the primary key for foreign keys. Michael Glaesemann grzm myrealbox com
Michael Glaesemann zei: > > OIDS are a system level implementation. They are no longer required > (you can make tables without OIDS) and they may go away someday. Out of curiosiry: how will we handle blobs once the OID's are gone?
Am Dienstag, 16. November 2004 10:01 schrieb Joolz: > Michael Glaesemann zei: > > OIDS are a system level implementation. They are no longer required > > (you can make tables without OIDS) and they may go away someday. > > Out of curiosiry: how will we handle blobs once the OID's are gone? They won't go away. This is one reason. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Nov 16, 2004, at 6:42 PM, Peter Eisentraut wrote: > Am Dienstag, 16. November 2004 10:01 schrieb Joolz: >> Michael Glaesemann zei: >>> OIDS are a system level implementation. They are no longer required >>> (you can make tables without OIDS) and they may go away someday. >> >> Out of curiosiry: how will we handle blobs once the OID's are gone? > > They won't go away. This is one reason. Peter, You sound pretty certain. I can imagine there might be a way to handle BLOBs without OIDs. I'm not saying that I know what it is, but I recognize the possibility. Michael Glaesemann grzm myrealbox com
Am Dienstag, 16. November 2004 10:52 schrieb Michael Glaesemann: > You sound pretty certain. I can imagine there might be a way to handle > BLOBs without OIDs. I'm not saying that I know what it is, but I > recognize the possibility. There are certainly ways to handle this. But no one has seriously proposed getting rid of OIDs and presented a plan for fixing all the other holes that move would leave. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Tue, 16 Nov 2004 08:01 pm, Joolz wrote: > Michael Glaesemann zei: > > > > OIDS are a system level implementation. They are no longer required > > (you can make tables without OIDS) and they may go away someday. > > Out of curiosiry: how will we handle blobs once the OID's are gone? > I would guess bytea would become the standard for blob use. The size is limited to about 1G compressed, but I would guess most people don't store 2G files in there DB at the moment, or have that much ram to be able to handle a value that big. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
Peter Eisentraut wrote: > There are certainly ways to handle this. But no one has seriously proposed > getting rid of OIDs and presented a plan for fixing all the other holes that > move would leave. Right; I certainly have no intention of trying to remove OIDs any time soon. However, I _will_ be proposing that we set default_with_oids to false by default in 8.1, per previous discussion on pgsql-hackers. Among other things, this will mean that CREATE TABLE will not include OIDs by default: if you want OIDs on a particular table, you can either specify WITH OIDS explicitly or change the default_with_oids configuration parameter. -Neil
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 A little bit OT, but: is there a way of removing duplicate rows in a table without OIDs? Mit freundlichem Gruß / With kind regards Holger Klawitter - -- lists <at> klawitter <dot> de -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q mwf/f5rI7VdckPfgfUotnSc= =qpV0 -----END PGP SIGNATURE-----
On Nov 16, 2004, at 8:32 PM, Holger Klawitter wrote: > A little bit OT, but: > is there a way of removing duplicate rows in a table without OIDs? One method that I believe works (haven't tried it in a while): BEGIN; CREATE TEMP TABLE foo_temp AS SELECT DISTINCT bar, bat, baz FROM foo; TRUNCATE foo; INSERT INTO TABLE foo (bar, bat, baz) SELECT bar, bat, baz FROM foo_temp; DROP TABLE foo_temp; COMMIT; There are others. Googling would probably reveal some. Michael Glaesemann grzm myrealbox com
> On Tue, 16 Nov 2004 08:01 pm, Joolz wrote: > > Michael Glaesemann zei: > > > > > > OIDS are a system level implementation. They are no longer required > > > (you can make tables without OIDS) and they may go away someday. > > > > Out of curiosiry: how will we handle blobs once the OID's are gone? > > > I would guess bytea would become the standard for blob use. The size > is limited to about 1G compressed, but I would guess most people don't > store 2G files in there DB at the moment, or have that much ram to be > able to handle a value that big. Bytea cannot be a replacement of large objects Besides the 1G limit of bytea, storing 1G requires over 2G RAM is a serious problem. To be honest I don't understand why people hate OIDs. Most of problems with OID just come from the fact that it's a 32bit. Once extending it 64bit, all problems would go away. However using OIDs with large object is not a very good idea IMO. I think using user specified key for large objects would be better. -- Tatsuo Ishii
Hi, > > > > OIDS are a system level implementation. They are no longer required > > > > (you can make tables without OIDS) and they may go away someday. What about tableoids? Are they from the same generator as row oids (and hence may suffer wrap-around)? Or are they unique across the db? I ask because I'm currently using them to join a single table to rows in arbitrary tables, something like: +-------------+ | JOINME | +-------------+ | foreign_oid | | foreign_id | | ... | +-------------+ Where foreign_oid is the tableoid of the table and foreign_id is the (serial, not oid) id of the row in that table. I dunno if it's bad design, but it's darn handy. M
On Tue, 2004-11-16 at 12:32, Holger Klawitter wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > A little bit OT, but: > is there a way of removing duplicate rows in a table without OIDs? > ALTER TABLE ... ADD COLUMN myoid int; CREATE TEMP SEQUENCE myoidsequence; UPDATE ... SET myoid=nextval('myoidsequence'); then do the usual SELECT a.* FROM ... a, ... b WHERE a.something=b.something AND a.myoid<b.myoid; Dance and if you are done with it, ALTER TABLE ... DROP COLUMN myoid; DROP TEMP SEQUENCE myoidsequence; HTH Tino
Peter Eisentraut zei: > Am Dienstag, 16. November 2004 10:01 schrieb Joolz: >> Michael Glaesemann zei: >> > OIDS are a system level implementation. They are no longer >> required >> > (you can make tables without OIDS) and they may go away someday. >> >> Out of curiosiry: how will we handle blobs once the OID's are >> gone? > > They won't go away. This is one reason. Thanks. Btw what's going on on the list??? I seems someone is posting a lot of obscene nonsense, using my email address. I saw this happen before with someone else. Is there anything I can do about it? Thanks again!
On Nov 16, 2004, at 11:44 PM, Joolz wrote: > Btw what's going on on the list??? I seems someone is posting a lot > of obscene nonsense, using my email address. net.kook > I saw this happen > before with someone else. Is there anything I can do about it? Filter aggressively and be patient. Not a happy situation, but the best we can do. Michael Glaesemann grzm myrealbox com
--- Joolz <joolz@arbodienst-limburg.nl> wrote: > > Thanks. > > Btw what's going on on the list??? I seems someone > is posting a lot > of obscene nonsense, using my email address. I saw > this happen > before with someone else. Is there anything I can do > about it? This is the down side of free will. Human cleverness can be used for bad purposes as well as good. Those who do things like this gain gratification from the responses that they get from others. If we ignore them (as everyone else on the list appears to be doing) they will go away eventually. > > Thanks again! > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com
Matt <matt@kynx.org> writes: > I ask because I'm currently using them to join a single table to rows in > arbitrary tables, something like: > > +-------------+ > | JOINME | > +-------------+ > | foreign_oid | > | foreign_id | > | ... | > +-------------+ > > Where foreign_oid is the tableoid of the table and foreign_id is the > (serial, not oid) id of the row in that table. > > I dunno if it's bad design, but it's darn handy. How do you make use of this? It seems like you would need your code to know which foreign_oid referred to which table to actually perform the join. -- greg
> How do you make use of this? It seems like you would need your code to know > which foreign_oid referred to which table to actually perform the join. Sorry, wasn't very clear about what it does: select * from mytable t left join joinme j on t.id = j.foreign_id and t.tableoid = j.foreign_oid; I use it for 'PostIt note' type data that I want to be able to stick to any other row in the DB. Keeping the referential integrity is a bit of extra work, but I'm working on it :) If you're going the other way, yes, you'll need to find out what tables are joined to your postit first. But that's easy with: select foreign_oid::regclass from joinme where... But back to my original question: are those tableoid's going to suddenly wrap around? M
If you have a primary key you can self join the table on the rows that would define a table as duplicate and delete the one with a higher primary key field. If the table is related to other tables (ie the pk is an fk in another table) you have to make sure you update all the rows to point to the new key. If there is no pkey, then you would do a select distinct into a temp table as was suggested by Michael in the post above mine "Holger Klawitter" <lists@klawitter.de> wrote in message news:200411161232.17642.lists@klawitter.de... -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 A little bit OT, but: is there a way of removing duplicate rows in a table without OIDs? Mit freundlichem Gru� / With kind regards Holger Klawitter - -- lists <at> klawitter <dot> de -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q mwf/f5rI7VdckPfgfUotnSc= =qpV0 -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
Michael Glaesemann wrote: > > On Nov 15, 2004, at 3:52 PM, Jamie Deppeler wrote: > >> Hi, >> >> I am planning to use OID for referencing as instead PK -->> FK on >> this situation would require alot of tables, OID would seen to nice >> solution. >> >> My worry with OID's is when i do SQL dump and rebuild the Database >> will OID will change making referencing certain records impossible. > > > Don't use OIDS. Just add a nice SERIAL column to the tables you want as > foreign keys (and if you have questions about sequences, check the FAQ). > > OIDS are a system level implementation. They are no longer required (you > can make tables without OIDS) and they may go away someday. Plus, you > avoid nastiness like OID wraparound. > > Michael Glaesemann > grzm myrealbox com > > (Is it just me, or have there been a slew of these OID posts lately?) > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > I have read about oid wraparound in many messages but I don't understand when it happens and when it is dangerus for my tables. It affects developers that uses OIDS in their queryies? What about database and tables (not total or total) disappearences? Kostis.
Just to clarify, there is a difference between OIDs and XIDs. Object IDs (OID) are a system assigned field to every row that eventually wraps around. If you don't use them in your application you'll hever really have a problem. The only exception is that statements that modify structures in the database (CREATE TRIGGER/TABLE/INDEX/etc) may fail if you're unlucky enough to try them and it happens to be exactly the OID of an existing thing of that type. Most people don't create 4 billion rows in their database so it's not an issue. People who do are recommended to create their high churn tables WITHOUT OIDS so they don't get used as fast. As an added bonus, in recent versions you can actually save diskspace by not having them. Transaction IDs (XID) are a different story, they track transactions and what is visible and what isn't. Transaction wraparound means that rows will disappear when their transaction ID (which was considered in the past) is now in the future. Since 7.2 this problem is avoided by doing a database wide VACUUM (not necessarily FULL) at least once every billion transactions. This is not an onerous requirement so people don't run into this anymore. Before 7.2 you'd simply find your data missing one morning as the only way to reset the XID was with an initdb. If you're still running a busy database on something older than that, you *really* need to consider taking appropriate measures! 7.2 is already fairly old now and all of the major database destroying issues from then are now fixed. Hope this helps, On Tue, Nov 16, 2004 at 01:06:57PM +0200, Kostis Mentzelos wrote: > I have read about oid wraparound in many messages but I don't understand > when it happens and when it is dangerus for my tables. > > It affects developers that uses OIDS in their queryies? > What about database and tables (not total or total) disappearences? > > Kostis. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Helps me. Thanks for the clairification. On Wednesday 17 November 2004 06:49 am, Martijn van Oosterhout saith: > Just to clarify, there is a difference between OIDs and XIDs. Object > IDs (OID) are a system assigned field to every row that eventually > wraps around. If you don't use them in your application you'll hever > really have a problem. The only exception is that statements that > modify structures in the database (CREATE TRIGGER/TABLE/INDEX/etc) may > fail if you're unlucky enough to try them and it happens to be exactly > the OID of an existing thing of that type. > > Most people don't create 4 billion rows in their database so it's not > an issue. People who do are recommended to create their high churn > tables WITHOUT OIDS so they don't get used as fast. As an added bonus, > in recent versions you can actually save diskspace by not having them. > > Transaction IDs (XID) are a different story, they track transactions > and what is visible and what isn't. Transaction wraparound means that > rows will disappear when their transaction ID (which was considered in > the past) is now in the future. Since 7.2 this problem is avoided by > doing a database wide VACUUM (not necessarily FULL) at least once every > billion transactions. This is not an onerous requirement so people > don't run into this anymore. > > Before 7.2 you'd simply find your data missing one morning as the only > way to reset the XID was with an initdb. If you're still running a busy > database on something older than that, you *really* need to consider > taking appropriate measures! 7.2 is already fairly old now and all of > the major database destroying issues from then are now fixed. > > Hope this helps, > > On Tue, Nov 16, 2004 at 01:06:57PM +0200, Kostis Mentzelos wrote: > > I have read about oid wraparound in many messages but I don't understand > > when it happens and when it is dangerus for my tables. > > > > It affects developers that uses OIDS in their queryies? > > What about database and tables (not total or total) disappearences? > > > > Kostis. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan if > > your joining column's datatypes do not match -- Quote: 28 "Without question, we need to disarm Saddam Hussein. He is a brutal, murderous dictator, leading an oppressive regime . . . He presents a particularly grievous threat because he is so consistently prone to miscalculation. . . . And now he is miscalculating America's response to his continued deceit and his consistent grasp for weapons of mass destruction. . . . So the threat of Saddam Hussein with weapons of mass destruction is real. . . ." -- Sen. John F. Kerry (D, MA), Jan. 23. 2003 Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On 11/16/2004 4:52 AM, Michael Glaesemann wrote: > On Nov 16, 2004, at 6:42 PM, Peter Eisentraut wrote: > >> Am Dienstag, 16. November 2004 10:01 schrieb Joolz: >>> Michael Glaesemann zei: >>>> OIDS are a system level implementation. They are no longer required >>>> (you can make tables without OIDS) and they may go away someday. >>> >>> Out of curiosiry: how will we handle blobs once the OID's are gone? >> >> They won't go away. This is one reason. > > Peter, > > You sound pretty certain. I can imagine there might be a way to handle > BLOBs without OIDs. I'm not saying that I know what it is, but I > recognize the possibility. A sequence and converting the blob identifier to int8 would be one ... Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On 11/16/2004 6:32 AM, Holger Klawitter wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > A little bit OT, but: > is there a way of removing duplicate rows in a table without OIDs? There is still the CTID. Jan > > Mit freundlichem Gruß / With kind regards > Holger Klawitter > - -- > lists <at> klawitter <dot> de > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.2 (GNU/Linux) > > iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q > mwf/f5rI7VdckPfgfUotnSc= > =qpV0 > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
thanks