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/
Show quoted text
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:
Show quoted text
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.
Show quoted text
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:
Show quoted text
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