Simple UPDATE command not terminating

Questions and answers regarding general SQL and backend databases

Simple UPDATE command not terminating

Postby Bernd.N » Mon Sep 18, 2017 11:25 pm

To fix data that was not correct because of a bug, I had to fire this most simple SQL command inside postgres:
Code: Select all
UPDATE projects SET p_pre_financing = p_cost_actual - p_invoiced WHERE p_pre_financing <> p_cost_actual - p_invoiced

However after 100 seconds I stopped it, as it seemed to run forever.
But it should have changed just 70 records out of 39.000.

The SELECT to get those records needed just some miliseconds:
Code: Select all
SELECT project_id FROM projects WHERE p_pre_financing <> p_cost_actual - p_invoiced


Then I got curious and fired
Code: Select all
UPDATE projects SET p_pre_financing = p_cost_actual - p_invoiced WHERE project_id = <specific-UUID>

Again no termination, although project_id is the primary key of that table. :?

All three fields are of type 'double precision'.
What could be the cause?

If that happend in Foxpro, I would guess that the index was defect and caused a loop internally somehow.
Could that have happend here too?
Maybe that UPDATE command is first building a list of UUIDs for the WHERE-clause, and then has problems to run through that list due to a defect index?
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 497
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Simple UPDATE command not terminating

Postby Bernd.N » Tue Sep 19, 2017 8:46 am

I reindexed the table, that solved it.
Interestingly, an "REINDEX TABLE projects" also did not terminate.
I then used "REINDEX INDEX index_name" for all indexes, including the primary key, and then it worked.

And both for the REINDEX commands and for the UPDATE command above, postgres just needed milliseconds.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 497
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests

cron