Обсуждение: OID Wrap

Поиск
Список
Период
Сортировка

OID Wrap

От
mlw
Дата:
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)



Re: OID Wrap

От
Bruce Momjian
Дата:
> 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
 


Re: OID Wrap

От
mlw
Дата:
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!





Re: OID Wrap

От
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.
        regards, tom lane


Re: OID Wrap

От
Bruce Momjian
Дата:
> 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