Re: Update taking forever

On Mon, Jun 15, 2015 at 11:45 AM, Keith <keith(at)keithf4(dot)com> wrote:

>
>
> On Mon, Jun 15, 2015 at 8:30 AM, Bianca Stephani <
> bianca(dot)stephani(at)gmail(dot)com> wrote:
>
>>
>>
>> On Mon, Jun 15, 2015 at 9:17 AM, David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Monday, June 15, 2015, Bianca Stephani <bianca(dot)stephani(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Hi everyone.
>>>> I want to update the value of a column of 25 thousand rows and a sql
>>>> update is taking forever (literally, more then one day). There's any other
>>>> way so i can do that?
>>>>
>>>
>>> This would normally takes seconds (at most) if everything is working
>>> properly, so yes. I would suspect your where clause is not using an index
>>> to find those rows and there are a lot more than 25k rows on the table in
>>> question. Show us schemas and explain output if you want a more definitive
>>> answer.
>>>
>> It really wasn't, I've added indexes.

>
>> Ops, i must have looked on the wrong place. There are 420k rows.
>> My update have no "where" clause because i want to update all rows. I'm
>> adding two columns to the table and i'm setting one of them with a select
>> count and the other with the result of a recursive sql (defined before the
>> update, not at each row).
>>
>>
>>
>>> Or you have locking issue...
>>>
>>> David J.
>>>
>>
>>
>>
>> --
>> Bianca Stephani.
>>
>>
>>
>>
>>
>>
>>
>> *"Killing time before time kill us""Panic called you out and took you in,
>> giving you an easy game and letting you win""Who's gonna love you when you
>> reach the end?""A diferença entre a vida e a arte é que a arte é mais
>> suportável""Smile like you mean it""I told you I was brave, but I've
>> lied""If you don't do anything, nothing can happen"*
>>
>
> Even with that many rows, it shouldn't take that long. Likely there is
> something that has a lock on either the table you're trying to update or
> the rows. Look in the pg_stat_activity view from another session and look
> at the "waiting" column. If it says "t" or "true", then something else has
> a lock and it must finish what it's doing or be released before your update
> can finish. Look in the other sessions to see if you can identify what else
> may be causing it. Otherwise, you're going to have to dig into the pg_locks
> system catalog and learn how to debug things from there.
>

I've looked at the pg_locks and nothing appeared. :/

>
> http://www.postgresql.org/docs/current/static/view-pg-locks.html
> https://wiki.postgresql.org/wiki/Lock_Monitoring
> https://momjian.us/main/presentations/internals.html - Unlocking the
> Postgres Lock Manager
>
>
>
>
My update is something like:

WITH RECURSIVE new_list(id, column1, column2, path, column3) AS (
SELECT id, column1, column2, ARRAY[id], column3
FROM table
UNION ALL
SELECT table.id, table.column1, table.column2, path || table.id
, table.column3
FROM new_list
JOIN table ON table.column1 = new_list.id
WHERE NOT table.id = ANY(path)
)

UPDATE table SET
column2=( SELECT path[1] FROM new_list WHERE
column3=array_length(new_list.path, 1) AND new_list.id = table.id LIMIT 1);

I beleave that it is taking forever beacause it has to do a select at each
update of each row... But i don't know how i could do this other way :/

--
Bianca Stephani.

*"Killing time before time kill us""Panic called you out and took you in,
giving you an easy game and letting you win""Who's gonna love you when you
reach the end?""A diferença entre a vida e a arte é que a arte é mais
suportável""Smile like you mean it""I told you I was brave, but I've
lied""If you don't do anything, nothing can happen"*