Re: [GENERAL] OID's
thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID's
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's
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 [EMAIL PROTECTED] 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
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 [EMAIL PROTECTED] 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. pgpYDtH8fkXLx.pgp Description: PGP signature
Re: [GENERAL] OID's
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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID's
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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID's
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/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID's
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/ ---(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
Re: [GENERAL] OID's
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(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
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID's
-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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID's
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 ---(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
Re: [GENERAL] OID's
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.myoidb.myoid;
Dance and if you are done with it,
ALTER TABLE ... DROP COLUMN myoid;
DROP TEMP SEQUENCE myoidsequence;
HTH
Tino
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's
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! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's
--- Joolz [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
Matt [EMAIL PROTECTED] 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 ---(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
Re: [GENERAL] OID's
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 ---(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
Re: [GENERAL] OID's
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 [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] -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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
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 [EMAIL PROTECTED] 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
[GENERAL] 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
Re: [GENERAL] OID's
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 [EMAIL PROTECTED]
Re: [GENERAL] OID's
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] OID's
Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just
one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something
you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended
PostgreSQL-way and I agree):
id = nextval (whatever_id_seq);
insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of
the sequence, bummer.
So we constructed this query:
SELECT
pg_attrdef.adsrc
FROM
pg_attrdef,
pg_class,
pg_attribute
WHERE
pg_attrdef.adnum = pg_attribute.attnum
AND pg_attrdef.adrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attname = 'id'
AND pg_class.relname = 'whatever'
(pg_class is a table that holds for instance table-names, etc.,
pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means
you get something like this:
nextval('whatever_id_seq'::text)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatever_id_seq'::text);
done.
So, now all you have to know is:
- table
- field with ID + default-value
- insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day,
Lennie.
PS This has been tested with:
- 6.5.3 (Debian Linux Package)
- 8.0 Beta 3 Win32 (msi-install)
_
New things are always on the horizon.
---(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
Re: [GENERAL] OID's
I think you are correct in not using OIDs, as, firstly, as you point out
they are optional, also that they are not neccessarily unique.
The use of sequences is an idea, however, why the complication? Why not
simply use a sequence called mytable_sequence, or mytable_id where
mytable is the name of the table? (or some other such standard).
The other thing to be aware of is if a large number of people are
writing to the database concurrently it can go wrong (any method). That
is if you insert a record (using nextval for the sequence), then someone
else quickly inserts a row too before you have a chance to get the
sequence number at the next statement then the sequence number you get
will be wrong (it would be of the new one, not yours). This would be
the case regardless of how the records are committed.
A way around this is to create a function like
create function mytable_insert (varchar(50), varchar(50)) returns
integer as '
declare
wseq integer;
begin
select nextval(''mytable_seq'') into wseq;
insert into mytable(id, a, b)
values (wseq, $1, $2);
return wseq;
end' language 'plpgsql';
Then, executing select mytable_insert('xx', 'yy');
Will insert the record and return the inserted sequence number
regardless as to what is happening concurrently.
On Sat, 2004-10-23 at 13:52, Leen Besselink wrote:
Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just
one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something
you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended
PostgreSQL-way and I agree):
id = nextval (whatever_id_seq);
insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of
the sequence, bummer.
So we constructed this query:
SELECT
pg_attrdef.adsrc
FROM
pg_attrdef,
pg_class,
pg_attribute
WHERE
pg_attrdef.adnum = pg_attribute.attnum
AND pg_attrdef.adrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attname = 'id'
AND pg_class.relname = 'whatever'
(pg_class is a table that holds for instance table-names, etc.,
pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means
you get something like this:
nextval('whatever_id_seq'::text)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatever_id_seq'::text);
done.
So, now all you have to know is:
- table
- field with ID + default-value
- insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day,
Lennie.
PS This has been tested with:
- 6.5.3 (Debian Linux Package)
- 8.0 Beta 3 Win32 (msi-install)
_
New things are always on the horizon.
---(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
--
Edward A. Macnaghten
http://www.edlsystems.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] OID's
Eddy Macnaghten [EMAIL PROTECTED] writes: The other thing to be aware of is if a large number of people are writing to the database concurrently it can go wrong (any method). That is if you insert a record (using nextval for the sequence), then someone else quickly inserts a row too before you have a chance to get the sequence number at the next statement then the sequence number you get will be wrong (it would be of the new one, not yours). This would be the case regardless of how the records are committed. Not the case. If you use currval(), it will always be the last value the sequence took *in your session*, so it's immune to other sessions inserting at the same time. See the docs. -Doug ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID's
Eddy Macnaghten zei:
I think you are correct in not using OIDs, as, firstly, as you point out
they are optional, also that they are not neccessarily unique.
I'm sorry Eddy, but you most be mistaken:
Every row in POSTGRESQL is assigned a unique, normally invisible number
called an object identification number (OID). When the software is
initialized with initdb , 12.1 a counter is created and set to
approximately seventeen-thousand. The counter is used to uniquely number
every row. Although databases may be created and destroyed, the counter
continues to increase.
http://www.postgresql.org/docs/aw_pgsql_book/node71.html
The use of sequences is an idea, however, why the complication? Why not
simply use a sequence called mytable_sequence, or mytable_id where
mytable is the name of the table? (or some other such standard).
Because a lot of the time we query databases we did not create our selfs,
we were looking for a general way, to handle it.
The other thing to be aware of is if a large number of people are
writing to the database concurrently it can go wrong (any method). That
is if you insert a record (using nextval for the sequence), then someone
else quickly inserts a row too before you have a chance to get the
sequence number at the next statement then the sequence number you get
will be wrong (it would be of the new one, not yours). This would be
the case regardless of how the records are committed.
I thought that was the whole idea of sequences, each call to nextval ()
will actually give you a unique number for that sequence (unless ofcourse
it it wraps..)
A way around this is to create a function like
create function mytable_insert (varchar(50), varchar(50)) returns
integer as '
declare
wseq integer;
begin
select nextval(''mytable_seq'') into wseq;
insert into mytable(id, a, b)
values (wseq, $1, $2);
return wseq;
end' language 'plpgsql';
Then, executing select mytable_insert('xx', 'yy');
That just uses a plpgsql function to do what I suggested (other then you
need to know the sequence name)
Will insert the record and return the inserted sequence number
regardless as to what is happening concurrently.
Ohh, now I know what you mean, no we don't write concurrently, but a
nextval should be unique for that sequence anyway (otherwise, why even
have them ?).
On Sat, 2004-10-23 at 13:52, Leen Besselink wrote:
Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just
one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing
things.
But sometimes it just takes a lot more time and effort to find something
you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended
PostgreSQL-way and I agree):
id = nextval (whatever_id_seq);
insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of
the sequence, bummer.
So we constructed this query:
SELECT
pg_attrdef.adsrc
FROM
pg_attrdef,
pg_class,
pg_attribute
WHERE
pg_attrdef.adnum = pg_attribute.attnum
AND pg_attrdef.adrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attname = 'id'
AND pg_class.relname = 'whatever'
(pg_class is a table that holds for instance table-names, etc.,
pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which
means
you get something like this:
nextval('whatever_id_seq'::text)
so, now you have the sequence..., or atleast a way to get to the
nextval.
All you have to do is this:
SELECT nextval('whatever_id_seq'::text);
done.
So, now all you have to know is:
- table
- field with ID + default-value
- insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day,
Lennie.
PS This has been tested with:
- 6.5.3 (Debian Linux Package)
- 8.0 Beta 3 Win32 (msi-install)
_
New things are always on the horizon.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do
Re: [GENERAL] OID's
On Sat, 23 Oct 2004, Leen Besselink wrote: Eddy Macnaghten zei: I think you are correct in not using OIDs, as, firstly, as you point out they are optional, also that they are not neccessarily unique. I'm sorry Eddy, but you most be mistaken: Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , 12.1 a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase. http://www.postgresql.org/docs/aw_pgsql_book/node71.html Actually it's the book that's mistaken. The counter can roll over, and will on large databases (oids are only 4 bytes). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID's
On Sat, 2004-10-23 at 17:46 +0200, Leen Besselink wrote: Eddy Macnaghten zei: I think you are correct in not using OIDs, as, firstly, as you point out they are optional, also that they are not neccessarily unique. I'm sorry Eddy, but you most be mistaken: Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , 12.1 a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase. http://www.postgresql.org/docs/aw_pgsql_book/node71.html That is dated 2002. It is now possible to create a table without oids, and oids are not guaranteed always to exist in all future releases. It is likely that the default table creation will switch to being without oids soon; that can already be specified as the default (in 8.0beta3). Oids are not guaranteed to be unique, since they wrap round when they reach the end of their range. If you wanted to use an oid as a guaranteed unique id, you would need to add a unique index on the oid column for that table; that could then cause an insertion to fail if an oid in the table were to be reused. If it were a very large table, that would cause the application to fail, because many insertions would be likely to fail after the wrap-around. The use of sequences is an idea, however, why the complication? Why not simply use a sequence called mytable_sequence, or mytable_id where mytable is the name of the table? (or some other such standard). Because a lot of the time we query databases we did not create our selfs, we were looking for a general way, to handle it. Reliance on a database feature, such as oids, as a key is a sign of bad design; a table row ought to have a unique key of some kind, and if you insert that row, you must know what that key is. If there is no other way to distinguish it, you can add a serial column for the sole purpose of providing a primary key. That would be part of the data rather than a side-effect of the implementation. I can't see how the use of oids would help you with a database of someone else's design, unless the designer used that feature already. The other thing to be aware of is if a large number of people are writing to the database concurrently it can go wrong (any method). That is if you insert a record (using nextval for the sequence), then someone else quickly inserts a row too before you have a chance to get the sequence number at the next statement then the sequence number you get will be wrong (it would be of the new one, not yours). This would be the case regardless of how the records are committed. I thought that was the whole idea of sequences, each call to nextval () will actually give you a unique number for that sequence (unless ofcourse it it wraps..) You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value). A lot of people seem not to understand that. The trade-off is that sequences are not rolled back if a transaction is aborted. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA I press toward the mark for the prize of the high calling of God in Christ Jesus. Philippians 3:14 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID's
You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value). Or unless the sequence wraps around. That's less likely (and less dangerous) than having the OID wrap around, but not impossible. I personally believe that there is value in a database-generated unique value like Oracle's ROWID. (Part of what I like about it is that since it is a system column it simplifies some application issues, since the app never has to worry about that column unless it chooses to.) Making the OID sufficiently large to avoid virtually all wraparound issues would probably mean going to a 64 bit field, which would certainly be a non-trivial task. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
On Sat, 23 Oct 2004, Mike Nolan wrote: I personally believe that there is value in a database-generated unique value like Oracle's ROWID. (Part of what I like about it is that since it is a system column it simplifies some application issues, since the app never has to worry about that column unless it chooses to.) If an application needs a column called oid in each table it could very well just define the tables like that. You could also make a script that checks all tables and make sure there is a oid if you want to. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
Mike Nolan [EMAIL PROTECTED] writes: You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value). Or unless the sequence wraps around. That's less likely (and less dangerous) than having the OID wrap around, but not impossible. Sequences do not wrap by default (only if you use the CYCLE option). Anyway, if you use a bigint sequence field you are pretty safe from ever running out of values... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID's....
On Wed, 30 May 2001, Steve Wolfe wrote: I know that this topic comes up fairly often, so I tried to search the archives, but the search engine doesn't appear to have info on messages after 1999, so forgive me for repeating this topic. Recently, our OID usage has started to jump dramatically - today, we're using ten thousand or more in a few minutes. We're trying to figure out just what is using so many, without any luck. Aside from doing a lot of inserts (which we don't do many of), what would cause this? Are they pre-allocated for transactions and not freed? Also, when the OID's reach the limit of an int4, if I recall correctly, they simply wrap around, and things keep working unless you depend on unique OID's. Is that correct? That's correct. cheers, t. p.s. and rumor has it that the universe will start shrinking as soon as this happens ;-) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] OID's....
Also, when the OID's reach the limit of an int4, if I recall correctly, they simply wrap around, and things keep working unless you depend on unique OID's. Is that correct? That's correct. cheers, t. p.s. and rumor has it that the universe will start shrinking as soon as this happens ;-) Actually, in our case, it may happen more soon than I had thought. We were in the tens of millions not long ago, and are now over 100 million. At the rate we're going, we may very well be doing 5 million OID's per day in the very near future, which would give us about 6 months to wrap around. I'm not terribly worried about the wrap-around, but I would like to be as informed as possible regarding this situation. And since we do very few inserts relative to our selects (probably a 1:5,000 ratio), we probably don't need to be churning through them quite so fast. It appears to be pre-allocating 30 or 32 OID's per select, which in our case, is far too many, as it's a very rare case indeed where we insert more than one record at a time. Is there a way to change that behavior? steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] OID's....
Steve Wolfe [EMAIL PROTECTED] writes: Recently, our OID usage has started to jump dramatically - today, we're using ten thousand or more in a few minutes. We're trying to figure out just what is using so many, without any luck. Aside from doing a lot of inserts (which we don't do many of), what would cause this? Are they pre-allocated for transactions and not freed? What PG version are you using? IIRC, in pre-7.1 code, backends allocate OIDs in blocks of 32 (?? more or less anyway); so if a backend uses one OID and then exits, you wasted 31 OIDs. This does not happen anymore with 7.1, though. Another possibility is that you're creating lots of temp tables --- each one will cost you a dozen or so OIDs, depending on the number of columns. Also, when the OID's reach the limit of an int4, if I recall correctly, they simply wrap around, and things keep working unless you depend on unique OID's. Is that correct? That's the theory, anyway. After the wrap, you could see occasional failures due to OID conflicts in the system catalogs --- for example, if you try to create a table but the assigned OID duplicates some existing table's OID, you'd get a can't-insert-duplicate-into-unique-index kind of failure. The solution if that happens is just to try again; eventually you'll get an OID that doesn't conflict. But the odds of a conflict like that should be very low in practice. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID's....
What PG version are you using? IIRC, in pre-7.1 code, backends allocate OIDs in blocks of 32 (?? more or less anyway); so if a backend uses one OID and then exits, you wasted 31 OIDs. This does not happen anymore with 7.1, though. Another possibility is that you're creating lots of temp tables --- each one will cost you a dozen or so OIDs, depending on the number of columns. Thanks, Tom. We are using 7.0.x, and they do appear to be used in chunks of 32. One more reason for me to upgrade. : ) steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
