Обсуждение: cant delete row

Hi,
I have a row in a table which I cannot update/delete. I think it might be
something to do with the numeric(12,2) fields - I know during testing I
tried to insert values which were too big.
I can update other rows in the table, but if I try to update/delete this
one, I get the error
ERROR:  overflow on numeric ABS(value) >= 10^9 for field with precision 8
scale 2

[guilty admission - I think maybe the numeric(12,2) fields used to be
numeric(8,2) fields and I  increased them by messing with pg_attribute.
which could have something to do with it, although it seemed to work at the
time.  that'll teach me]

I've tried dumping/reimporting the database, vacuum/analyzing the table etc.

I'm using Postgres 7.0.2 on linux.  All the table details are below.
Thanks for any ideas!
Tamsin




test=# \d order_head

                      Table "order_head"
      Attribute      |     Type      |        Modifier
---------------------+---------------+------------------------
 order_head_id       | integer       | not null
  order_value         | numeric(12,2) | not null default 0
  order_cost          | numeric(12,2) | not null default 0
 shipping_cost       | numeric(8,2)  |

test=# select order_cost, order_value, shipping_cost from order_head where
order_head_id = 1581;
  order_cost   |  order_value  | shipping_cost
---------------+---------------+---------------
 5397499900.00 | 9714420700.00 |
(1 row)

test=# delete from order_head where order_head_id = 1581;
ERROR:  overflow on numeric ABS(value) >= 10^9 for field with precision 8
scale 2
test=#

test=# select * from pg_attribute where attname in
('order_cost','order_value','shipping_cost') and attrelid in (select oid
from pg_class where relname = 'order_head');
 attrelid |    attname    | atttypid | attdisbursion | attlen | attnum |
attnelems | attcacheoff | atttypmod | attbyval | attstorage | attisset |
attalign | attnotnull | atthasdef
----------+---------------+----------+---------------+--------+--------+----
-------+-------------+-----------+----------+------------+----------+-------
---+------------+-----------
  1145957 | order_value   |     1700 |     0.0283164 |     -1 |     15 |
0 |          -1 |    786438 | f        | p          | f        | i        |
t          | t
  1145957 | order_cost    |     1700 |     0.0283164 |     -1 |     20 |
0 |          -1 |    786438 | f        | p          | f        | i        |
t          | t
  1145957 | shipping_cost |     1700 |      0.945491 |     -1 |     23 |
0 |          -1 |    524294 | f        | p          | f        | i        |
f          | f
(3 rows)

"Tamsin" <tg_mail@bryncadfan.co.uk> writes:
> test=# delete from order_head where order_head_id = 1581;
> ERROR:  overflow on numeric ABS(value) >= 10^9 for field with precision 8
> scale 2

This is extremely bizarre.  A DELETE does not care what data is in the
row.  Do you perhaps have rules or triggers on this table that you
haven't told us about?

            regards, tom lane

Tom Lane wrote:
  >"Tamsin" <tg_mail@bryncadfan.co.uk> writes:
  >> test=# delete from order_head where order_head_id = 1581;
  >> ERROR:  overflow on numeric ABS(value) >= 10^9 for field with precision 8
  >> scale 2
  >
  >This is extremely bizarre.  A DELETE does not care what data is in the
  >row.  Do you perhaps have rules or triggers on this table that you
  >haven't told us about?

But surely the WHERE clause on this DELETE will care?  I don't know
how the internal details are arranged, but it sounds as though it is
the comparison that is blowing up.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "My dear brothers, take note of this: Everyone should
      be quick to listen, slow to speak and slow to become
      angry, for man's anger does not bring about the
      righteous life that God desires."  James 1:19,20

"Oliver Elphick" <olly@lfix.co.uk> writes:
> Tom Lane wrote:
>>>> "Tamsin" <tg_mail@bryncadfan.co.uk> writes:
> test=# delete from order_head where order_head_id = 1581;
> ERROR:  overflow on numeric ABS(value) >= 10^9 for field with precision 8
> scale 2
>>>>
>>>> This is extremely bizarre.  A DELETE does not care what data is in the
>>>> row.  Do you perhaps have rules or triggers on this table that you
>>>> haven't told us about?

> But surely the WHERE clause on this DELETE will care?  I don't know
> how the internal details are arranged, but it sounds as though it is
> the comparison that is blowing up.

No, because the WHERE's comparison is on an integer field.  Besides,
that message comes out when trying to store a numeric value into a
column, not when fetching or doing arithmetic on one.

            regards, tom lane

doh!  now i feel _really_ stupid!
of course, I managed to forget all about the rule i made which inserts into
another table when a row is deleted. altered that table & recreated the
rule, and all is fine.
thanks tom!
tamsin

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: 05 April 2001 15:33
> To: Tamsin
> Cc: Postgres General
> Subject: Re: [GENERAL] cant delete row
>
>
> "Tamsin" <tg_mail@bryncadfan.co.uk> writes:
> > test=# delete from order_head where order_head_id = 1581;
> > ERROR:  overflow on numeric ABS(value) >= 10^9 for field with
> precision 8
> > scale 2
>
> This is extremely bizarre.  A DELETE does not care what data is in the
> row.  Do you perhaps have rules or triggers on this table that you
> haven't told us about?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>