UPDATE statement hangs because of locks

Question

I'm ran my .pgsql script using /i blah.pgsql script and then using my .java function to call that pgsql to test it.

However, after I ran it. I realized it corrupted my data, and I am not sure how to debug and what's causing the corrupt data. I can't DROP SCHEMA because it hangs, so I have to create a new SHOW SEARCH_PATH Schema to test it again. I did it three times already, and I want to avoid the problem. Can someone please help me identify why it caused the corrupt data? Thank you! Honestly, this is the only problem I have, and I cannot figure out why this cause a really bad corrupt database. If you need more information / database let me know so I can edit and provide more.

The point in creating this file is to get the count of every update is made. but it seems like updating is corrupting the whole database file

Here's my code

CREATE OR REPLACE function assignDelinquents (theAgent char(6), theCount integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
max_update integer := 0;
counter integer := 0;

BEGIN

  LOOP
    UPDATE Delinquents d
    -- code here 

    counter := counter + 1;

    END LOOP;

   RETURN counter;

END $$;

Edit: pg_stat_activity result: https://pastebin.com/46hN1uj9 I'm fairly new to pgsql, so it's really weird why i can't really do anything.

What exactly do you mean with "corrupt data"? What is the error message you get? — , Mar 14 '18 at 08:23
why you think its corrupted? from your description it looks more like locked resources. what's in `pg_stat_activity`?.. — Vao Tsun, Mar 14 '18 at 08:25
@Vao Tsun it just hangs. when I try to drop the scheme, nothing happens. it still runs infinitely. here's the pg_stat_activity https://pastebin.com/46hN1uj9 — Marvel Marbel, Mar 14 '18 at 08:30
yes - you have wait_event not null. run https://wiki.postgresql.org/wiki/Lock_Monitoring to get the blocker - and either terminate it or wait for completion or timeout... — Vao Tsun, Mar 14 '18 at 08:38
interesting, do you know why it locks?? How would I terminate the blocker/timeout? — Marvel Marbel, Mar 14 '18 at 08:44

score 1 · Accepted Answer · answered Mar 14 '18 at 19:50

There is no database corruption, everything is fine.

The explanation is simple if you look at your pg_stat_activity output.

At 2018-03-13 23:05:37.362666-07 you started
```
SELECT * FROM assignDelinquents($1,$2)
```
which is still running (so it has been running for over 1.5 hours).
This is not surprising since the function contains an endless loop.
At 2018-03-13 23:55:33.097579-07 you started
```
DROP SCHEMA Lab4 CASCADE;
```
which is trying to get a ACCESS EXCLUSIVE lock on all tables in the schema, but obviously some of these tables are used by your long running query, so the statement is blocked.
All later statements that try to use a table in the schema that is already locked by the previous statement will have to wait as well.

The solution is to kill the long running query (and fix your function).

SELECT pg_terminate_backend(8838);

actually found why, i just went to an infinite loop (without declaring). I set a a while loop condition and it worked. thanks! — Marvel Marbel, Mar 14 '18 at 20:01

UPDATE statement hangs because of locks

1 Answers1