X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 76FDAD1EDB3 for ; Fri, 7 May 2004 15:17:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 83258-01 for ; Fri, 7 May 2004 15:16:47 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 738E5D1E9C1 for ; Fri, 7 May 2004 15:16:46 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i47IGkSx032520 for ; Fri, 7 May 2004 18:16:46 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i47IEkX3029680 for pgsql-admin@postgresql.org; Fri, 7 May 2004 18:14:46 GMT X-Abuse-Report: abuse@teranews.com Message-ID: <4cb583f0740a5ee302887ff58f59665d@news.teranews.com> Date: Fri, 07 May 2004 18:07:38 GMT Lines: 30 From: Jeff Boes X-Newsgroups: comp.databases.postgresql.admin Subject: OID Overflow for large objects Organization: Nexcerpt, Inc. Reply-To: jboes@nexcerpt.com X-Newsreader: Forte Agent 2.0/32.652 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/69 X-Sequence-Number: 13385 We had a puzzling situation occur last weekend. Subsequently, I figured out how to work around it for now, but of course those who sign my checks want to know how we can nail down forever the possibility that something like that will ever happen again ... The OID value for large objects crossed the 2**31 boundary, and some PHP code stopped working (it would pull the OID value from one of our tables, then do a lo_export call to retrieve the BLOB; however, once the value passed 2**31, it failed because the internal library was treating the value as a signed rather than unsigned integer). I didn't write the PHP code, and the library is (I'm told) the PHP equivalent to the DBI layer for Perl (which doesn't seem to have any such problem, so there PHP freeks! 8-), so I couldn't really change that. What I did was to cast the OID to a float, which fooled the library into treating it properly. (Thank you, Google Groups!) Meanwhile, we're busy counting on our fingers and toes to figure out when the 2**32 boundary will be crossed. Our best guess is that it took us around a year to make it to the first threshold. We plan to upgrade that DB soon, which means dump and reload, which means resetting the OID counter. So that might give us a couple of years. I found some indication of the problem referenced in the Pg FAQ (http://www3.sk.postgresql.org/docs/faqs/FAQ.html): OIDs are stored as 4-byte integers, and will overflow at 4 billion. No one has reported this ever happening, and we plan to have the limit removed before anyone does.