Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create View failing

Create View failing

From
"Bryan White"
Date:
I am trying to play with views.  Every time I try to create one I get a
message like this:

ERROR:  pg_atoi: error reading "3642040800": Numerical result out of range

The reported number is incremented by a small amount each time.  Here is the
create statement:

create view ordertotals as select * from orders;

The orignal statement was more complex (and useful) but I simplified it to
try and narrow down the problem.  I always get the same error message.

Here is the definition of the 'orders' table.
ec=# \d orders
                        Table "orders"
   Attribute    |     Type      |           Modifier
----------------+---------------+------------------------------
 orderid        | integer       | not null
 custid         | integer       | not null
 employee       | text          | not null default ''
 date           | date          | not null default date(now())
 leadsource     | text          | not null default ''
 ordersource    | text          | not null default ''
 paymenttype    | text          | not null default ''
 paymentinfo    | text          | not null default ''
 paymentexpdate | text          | not null default ''
 paymentstatus  | text          | not null default ''
 tax            | numeric(9,2)  | not null default 0
 shipping       | numeric(9,2)  | not null default 0
 shipmethod     | text          | not null default ''
 note           | text          | not null default ''
 shipdate       | date          |
 camptail       | text          | not null default ''
 company        | text          | not null default ''
 title          | text          | not null default ''
 lname          | text          | not null default ''
 fname          | text          | not null default ''
 addr1          | text          | not null default ''
 addr2          | text          | not null default ''
 city           | text          | not null default ''
 state          | text          | not null default ''
 zip            | text          | not null default ''
 country        | text          | not null default ''
 phone          | text          | not null default ''
 batchid        | text          | not null default ''
 paydate        | date          |
 shipemp        | text          |
 couponid       | integer       |
 couponamt      | numeric(10,2) | default '0.00'
Indices: iordcus3,
         iorddate3,
         iordid3,
         iordldsrc3

Bryan White, ArcaMax.com, VP of Technology
You can't deny that it is not impossible, can you.

Re: Create View failing

From
Tom Lane
Date:
"Bryan White" <bryan@arcamax.com> writes:
> ERROR:  pg_atoi: error reading "3642040800": Numerical result out of range

> The reported number is incremented by a small amount each time.

Hmm.  Have you been running that database long enough that the OID
counter could be up to 3.6 billion?  It sounds like there might be some
confusion in the view save/read routines about whether OIDs are signed
or unsigned.

I'll take a look, but in the meantime you might be faced with an initdb
to bring the OID counter back under 2G :-(

            regards, tom lane

Re: Create View failing

From
"Bryan White"
Date:
> I'll take a look, but in the meantime you might be faced with an initdb
> to bring the OID counter back under 2G :-(

I am doing this on my backup db server.  The database is recreated nightly
from a dump from the main database server.  Maybe I need to do an initdb
nightly as well?

I tried creating the view on the main server and it worked just fine.  If
the OID limit has been exceeded, would not the database load fail miserably?
Or are you saying that the limit is really 4G but there is a bug in 'create
view' that limits it to 2G?

>
> regards, tom lane

Re: Create View failing

From
Tom Lane
Date:
"Bryan White" <bryan@arcamax.com> writes:
>> I'll take a look, but in the meantime you might be faced with an initdb
>> to bring the OID counter back under 2G :-(

> I tried creating the view on the main server and it worked just fine.  If
> the OID limit has been exceeded, would not the database load fail miserably?
> Or are you saying that the limit is really 4G but there is a bug in 'create
> view' that limits it to 2G?

It should be 4G, but not all the code has been checked to make sure that
OIDs are always treated as unsigned rather than signed :-(.  There are
probably more weak spots than just views ...

            regards, tom lane