[GENERAL] update impossible, constraint prevents it but it shouldn't

[lrotger]

I apologize if I'm running an old version (7.2.1) maybe this has been 
fixed or maybe it's not a bug but I can't look at the changelogs now.


UPDATE has a non-standard FROM clause where you can do joins etc so 
columns from other tables can appear in WHERE.


I run the following query:

update personal set closed_date = flt_date + 20
from enr inner join personal p on enr.id = p.id_enr
where (...columns from enr, columns from personal...)

this runs for a while then fails because of a constraint but if I 
rewrite the above as a select with the exact same from and where clauses 
looking for any offending rows there are none. The constraint says 
flt_date <= closed_date so it's clear the update cannot violate it.


- Has anyone encountered anything like this?

I can fix it creating functions that take the primary key and return the 
value I want from the related table I'm referencing (it's 1:1) so I can 
take the inner join out of the from, I know this works but I'd like to 
avoid so many function calls.


Thanks!
Lucia

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] update impossible, constraint prevents it but it shouldn't

[lrotger]

Martijn van Oosterhout wrote:

On Mon, Jun 26, 2006 at 01:00:19PM +0200, lrotger wrote:


I run the following query:

update personal set closed_date = flt_date + 20
from enr inner join personal p on enr.id = p.id_enr
where (...columns from enr, columns from personal...)



I think your problem is that "personal" and "personal p" refer to
different instances of the same table. Use EXPLAIN to check how many
times "personal" appears in the resulting query.


it worked, I thought I had to specify the table being updated in the 
from clause too if only to have something to join to the other table, as 
it turns out, it's not necessary. I hope the manual for 8.1.x explains 
this or at least gives some examples of using this nonstandard from clause.



Oh yeah, 7.2 will eat your data eventually, you have been warned.
Please upgrade to something newer.


I'll have to allocate some time to read changelogs before I upgrade 
across major versions.


thanks a lot,
Lucia

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster