cant delete row

Started by Tamsinabout 25 years ago5 messagesgeneral
Jump to latest
#1Tamsin
tg_mail@bryncadfan.co.uk

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)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Tamsin (#1)
Re: 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

#3Oliver Elphick
olly@lfix.co.uk
In reply to: Tom Lane (#2)
Re: cant delete row

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

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Oliver Elphick (#3)
Re: cant delete row

"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

#5Tamsin
tg_mail@bryncadfan.co.uk
In reply to: Tom Lane (#2)
RE: cant delete row

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

Show quoted text

-----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