Обсуждение: Ever increasing OIDs - gonna run out soon?
Back in April 2006 I emailed about high OIDs in a 7.4.6 database. I've just added new tables to the database (and the slony-relication set) and I'm seeing much higher OID values for the new tables. In April I added a table, it was assigned an OID value of 94198669 (94 million). I've just added three new tables (using an interactive psql session), and the OIDs assigned are (now in the 182 million range): wcpartner 182027615 wccustomdata 182027995 wccustpartnerdata 182028076 All the tables are created without OIDs, eg create table customer.wcpartner ( wcpartnerid integer, name varchar(32), wduserid integer, primary key(wcpartnerid) ) without oids; And the three tables were created in the same session within about 1 minute of each other. By way of comparison, the oids for the Slony slave (7.4.11) are wcpartner 38220869 wccustomdata 38221080 wccustpartnerdata 38221139 This is a normal production database with slony replication 1.1.5 and is mainly accessed from a web application using JDBC. I don't use any temporary tables - so what can be using up all the OIDs (especially between two successive create table statements)? It looks like the db is using them at the rate of 1.5 million per day. At what value will I hit a wraparound, and what options do I have to identify/fix the (impending) problem. Thanks. John
"John Sidney-Woollett" <johnsw@wardbrook.com> wrote > > It looks like the db is using them at the rate of 1.5 million per day. > At what value will I hit a wraparound, and what options do I have to > identify/fix the (impending) problem. > The Oid will wraparound when it reaches the 32bits unsigned integer limit. If you don't use the oid explicitely in your application, then you don't worry about it. Regards, Qingqing
On Mon, Jun 12, 2006 at 10:01:43AM +0800, Qingqing Zhou wrote: > > "John Sidney-Woollett" <johnsw@wardbrook.com> wrote > > > > It looks like the db is using them at the rate of 1.5 million per day. > > At what value will I hit a wraparound, and what options do I have to > > identify/fix the (impending) problem. > > > > The Oid will wraparound when it reaches the 32bits unsigned integer limit. > If you don't use the oid explicitely in your application, then you don't > worry about it. Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > Except IIRC the OP is running 7.4 which doesn't have checks in DDL > code to deal with OID collisions. :( This is not good news! :( What about other long runing 7.4.x DBs? Do you really have to dump, init and restore every once in a while? Also, do you know what is actually using the OIDs - transactions? John
On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: > Jim C. Nasby wrote: > > Except IIRC the OP is running 7.4 which doesn't have checks in DDL > > code to deal with OID collisions. :( > > This is not good news! :( > > What about other long runing 7.4.x DBs? Do you really have to dump, init > and restore every once in a while? Well, you have to be using a lot of OIDs for this to be an issue. At your stated rate of 1.5 million OIDs per day it will take just under eight years before you wraparound. That's a lot of OIDs and most databases don't get anywhere near that many, which is why it's not a big deal for most people... > Also, do you know what is actually using the OIDs - transactions? Inserting new rows into a table somewhere that has OIDs. Just using transactions won't do it. Note, some system catalogs use oids, so some DDL statements can do it. This gives you a list of tables that use OIDs. Maybe it can help you track down the problem. select attrelid::regclass from pg_attribute inner join pg_class on (attrelid = oid) where attname = 'oid' and relkind = 'r'; Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: > Jim C. Nasby wrote: > > Except IIRC the OP is running 7.4 which doesn't have checks in DDL > > code to deal with OID collisions. :( > > This is not good news! :( > > What about other long runing 7.4.x DBs? Do you really have to dump, init > and restore every once in a while? > > Also, do you know what is actually using the OIDs - transactions? Since you're running Slony, I suspect it's using them somehow. Or maybe it doesn't create it's tables WITHOUT OIDs. Also note that any time you create an object you burn through an OID. Probably your best bet is to just upgrade to 8.1, which will gracefully handle OID collisions. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Martijn van Oosterhout <kleptog@svana.org> writes:
> Well, you have to be using a lot of OIDs for this to be an issue. At
> your stated rate of 1.5 million OIDs per day it will take just under
> eight years before you wraparound. That's a lot of OIDs and most
> databases don't get anywhere near that many, which is why it's not a
> big deal for most people...
It should also be pointed out that OID wraparound is not a fatal
condition. Pre-8.1 you might get occasional query failures due to
trying to insert duplicate OIDs, but that's about it.
> This gives you a list of tables that use OIDs. Maybe it can help you
> track down the problem.
Look at pg_class.relhasoids --- easier, and more reliable.
regards, tom lane
We'll probably upgrade to 8.1.x before we hit the wraparound problem! :)
Hmm, looks like slony uses OIDs... And I found a couple of my own tables
which were incorrectly created with OIDs.
select relname, relnamespace, reltype from pg_catalog.pg_class where
relhasoids=true;
relname | relnamespace | reltype
----------------+--------------+----------
pg_attrdef | 11 | 16385
pg_constraint | 11 | 16387
pg_database | 11 | 88
pg_proc | 11 | 81
pg_rewrite | 11 | 16411
pg_type | 11 | 71
pg_class | 11 | 83
pg_operator | 11 | 16393
pg_opclass | 11 | 16395
pg_am | 11 | 16397
pg_language | 11 | 16403
pg_trigger | 11 | 16413
pg_cast | 11 | 16419
pg_namespace | 11 | 16596
pg_conversion | 11 | 16598
sturllog | 18161 | 18519 <-- MINE
stsession | 18161 | 18504
sl_trigger | 82061042 | 82061126 <-- SLONY
sl_table | 82061042 | 82061113
sl_nodelock | 82061042 | 82061082
sl_setsync | 82061042 | 82061098
sl_sequence | 82061042 | 82061134
sl_node | 82061042 | 82061073
sl_listen | 82061042 | 82061162
sl_path | 82061042 | 82061147
sl_subscribe | 82061042 | 82061174
sl_set | 82061042 | 82061087
sl_event | 82061042 | 82061186
sl_confirm | 82061042 | 82061193
sl_seqlog | 82061042 | 82061198
sl_log_1 | 82061042 | 82061202
sl_log_2 | 82061042 | 82061209
sl_config_lock | 82061042 | 82061229
Thanks
John
Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>
>>Well, you have to be using a lot of OIDs for this to be an issue. At
>>your stated rate of 1.5 million OIDs per day it will take just under
>>eight years before you wraparound. That's a lot of OIDs and most
>>databases don't get anywhere near that many, which is why it's not a
>>big deal for most people...
>
>
> It should also be pointed out that OID wraparound is not a fatal
> condition. Pre-8.1 you might get occasional query failures due to
> trying to insert duplicate OIDs, but that's about it.
>
>
>>This gives you a list of tables that use OIDs. Maybe it can help you
>>track down the problem.
>
>
> Look at pg_class.relhasoids --- easier, and more reliable.
>
> regards, tom lane
Just a quick thought - I know that I don't fully understand tables with oids, and table without oids, is there a link to some more information about why you need oids, or why you don't that I could reference as I'm a bit lost on the subject of oids
Alex.
Alex.
On 6/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Martijn van Oosterhout <kleptog@svana.org> writes:
> Well, you have to be using a lot of OIDs for this to be an issue. At
> your stated rate of 1.5 million OIDs per day it will take just under
> eight years before you wraparound. That's a lot of OIDs and most
> databases don't get anywhere near that many, which is why it's not a
> big deal for most people...
It should also be pointed out that OID wraparound is not a fatal
condition. Pre-8.1 you might get occasional query failures due to
trying to insert duplicate OIDs, but that's about it.
> This gives you a list of tables that use OIDs. Maybe it can help you
> track down the problem.
Look at pg_class.relhasoids --- easier, and more reliable.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Slony does appear to use OIDs. John Jim C. Nasby wrote: > On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: > >>Jim C. Nasby wrote: >> >>>Except IIRC the OP is running 7.4 which doesn't have checks in DDL >>>code to deal with OID collisions. :( >> >>This is not good news! :( >> >>What about other long runing 7.4.x DBs? Do you really have to dump, init >>and restore every once in a while? >> >>Also, do you know what is actually using the OIDs - transactions? > > > Since you're running Slony, I suspect it's using them somehow. Or maybe > it doesn't create it's tables WITHOUT OIDs. Also note that any time you > create an object you burn through an OID. > > Probably your best bet is to just upgrade to 8.1, which will gracefully > handle OID collisions.
On 6/12/06, Alex Turner <armtuk@gmail.com> wrote: > Just a quick thought - I know that I don't fully understand tables with > oids, and table without oids, is there a link to some more information about > why you need oids, or why you don't that I could reference as I'm a bit lost > on the subject of oids dont get lost, just forget you ever heard about them :). oid is a 'free' userland autoincrement counter which has some problems. It was a hidden column that in older versions of postgresql was implicitly added to your tables. newer versions of pg assume you dont want OIDs on your table. (system tables still use them, tho). for purposes of a global counter or table level ID generator, sequences are basically better in every way. use them. some middleware such as the odbc driver used to work better/easier if you had a column but afaik this is not the case anymore. Merlin
On Mon, Jun 12, 2006 at 03:26:56PM -0400, Alex Turner wrote: > Just a quick thought - I know that I don't fully understand tables with > oids, and table without oids, is there a link to some more information about > why you need oids, or why you don't that I could reference as I'm a bit lost > on the subject of oids http://www.postgresql.org/docs/faqs.FAQ.html#item4.12 Basically, it's best if you just don't use them. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461