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