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, Pennsylvania 19026Bruce 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 quitVery 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
rangeIs 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);
CREATE
test=> 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, Pennsylvania 19026