Thread: OID Wrap
You guys said you've never seen it.
You said you wanted to know if anyone has ever run into it!
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
cdinfo=# create view test as select * from zsong ;
ERROR: pg_atoi: error reading "2156109797": Numerical result out of
range
cdinfo=# vacuum analyze zsong ;
VACUUM
cdinfo=# create view test as select * from zsong ;
ERROR: pg_atoi: error reading "2156109823": Numerical result out of
range
cdinfo=# create view test as select * from zsong ;
ERROR: pg_atoi: error reading "2156109840": Numerical result out of
range
cdinfo=# vaanalyze zsong ;
cdinfo=# \d zsong Table "zsong"Attribute | Type | Modifier
-----------+-----------+----------muzenbr | integer |disc | integer |trk | integer |song |
varchar()|artistid | integer |acd | varchar() |trackid | integer |datasrc | integer |extid |
integer |
cdinfo=# select max(oid) from zsong ; max
--------------2138857719
(1 row)
> You guys said you've never seen it. > You said you wanted to know if anyone has ever run into it! > > Welcome to psql, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit Very interesting. Also, it points out that we don't have OID as unsigned in all places. Are you running 7.1.X? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > You guys said you've never seen it. > > You said you wanted to know if anyone has ever run into it! > > > > Welcome to psql, the PostgreSQL interactive terminal. > > > > Type: \copyright for distribution terms > > \h for help with SQL commands > > \? for help on internal slash commands > > \g or terminate with semicolon to execute query > > \q to quit > > Very interesting. Also, it points out that we don't have OID as > unsigned in all places. Are you running 7.1.X? This system is currently 7.0.3, but we are working at moving it to 7.1.x very soon. The issue is that we backup the database, run update scripts. If the update scripts fail, we drop the database and reload from backup (It is a very interdependent amalgam of several vendor's databases). The database is well over 50 million records and almost a hundred indexes, and we have been doing this for a while. I have had it in the back of my mind that I should keep an eye on this, but it bit me before I really focused. It isn't a big deal for us, we just have to run initdb once in a while. lol. But I thought you guys would like to know that it did happen!
mlw <markw@mohawksoft.com> writes:
> cdinfo=# create view test as select * from zsong ;
> ERROR: pg_atoi: error reading "2156109797": Numerical result out of
> range
Is this 7.1? I thought we'd fixed all the places that treated OID
values as signed.
regards, tom lane
> mlw <markw@mohawksoft.com> writes: > > cdinfo=# create view test as select * from zsong ; > > ERROR: pg_atoi: error reading "2156109797": Numerical result out of > > range > > Is this 7.1? I thought we'd fixed all the places that treated OID > values as signed. It is 7.0.3. 7.1 has doubled the size of oid. As a test using the CVS copy, I ran:CREATE TEMPORARY TABLE pgdump_oid (dummy int4);COPY pgdump_oid WITH OIDS FROM stdin;3000000000 0\.DROP TABLE pgdump_oid; and then to show it worked:test=> create table test (x int);CREATEtest=> insert into test values (1);INSERT 3000000011 1 I then ran the regression tests, and they all passed. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026