58

When drop or truncate a not too big table(4M rows) in my redshift database, it take very very long(hours) to complete. Does anybody experience the same issue?

Thanks

Pat Myron
  • 4,437
  • 2
  • 20
  • 39
user2916054
  • 601
  • 1
  • 6
  • 3

4 Answers4

85

Redshift has very fast I/O, so that opeation should take less than 1 second for any cluster type or size. As diemacht said, the issue is caused because you have another connection with an open transaction.

I had a similar issue: A crash on the client left a transaction 'open' but unreacheable. No db locks appeared on the STV_LOCKS table: (using select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;)

Also, no query was still running: (checked with: select pid, trim(user_name), starttime, query , substring(query,1,20), status from stv_recents where status='Running';)

So the solution was to list the user sessions: SELECT * FROM STV_SESSIONS And then kill it using: SELECT pg_terminate_backend(pid)

Or the KILL'EM ALL version:

SELECT pg_terminate_backend(process) FROM STV_SESSIONS where user_name='user_name' and process != pg_backend_pid();

Note that CANCEL {pid} did not work! (the query was cancelled but the transaction was still open and locking).

Unknown
  • 5,722
  • 5
  • 43
  • 64
Gerardo Grignoli
  • 14,058
  • 7
  • 57
  • 68
  • 9
    `SELECT pg_terminate_backend(process) FROM STV_SESSIONS where user_name='user_name' and process != pg_backend_pid();` doesn't work now. It returns `INFO: Function "pg_terminate_backend(integer)" not supported.` message. – Masashi M Jun 05 '15 at 19:48
  • @masashimiyazaki, `pg_terminate_backend` won't work when selecting from a Redshift table'. There was another message that indicated the function is not available on Redshift tables. Take the list of pids and apply pg_terminate_backend() to each separately. Perhaps that behavior changed since the parent post. – Thinkable Sep 10 '15 at 19:49
  • isn't it possible to just add a timeout for the user queries in the WLM? – vishnu viswanath Feb 05 '18 at 02:42
43

In my experience, as @Gerardo Grignoli says, locks don't show up in the stv_locks table, but they do show up in pg_locks. Depending on your environment it may not be acceptable to kill an arbitrary long-running session listed in stv_sessions. I find the pg_locks table to be very reliable for detecting this type of lock:

select * from pg_locks where relation = (select oid from pg_class where relname = 'the_table')
select pg_cancel_backend(pid)

Typically, the issue is an ACCESS EXCLUSIVE lock that's deadlocking the table. So, if many locks are listed, find and kill the ACCESS EXCLUSIVE one.

kuujo
  • 7,785
  • 1
  • 26
  • 21
21

IMO AccessShareLock on tables also causes DDL commands to get stuck.

Run this query to figure out pids of AccessShareLock

select
  current_time,
  c.relname,
  l.database,
  l.transaction,
  l.pid,
  a.usename,
  l.mode,
  l.granted
from pg_locks l
join pg_catalog.pg_class c ON c.oid = l.relation
join pg_catalog.pg_stat_activity a ON a.procpid = l.pid
where l.pid <> pg_backend_pid();

Kill the processes using select pg_terminate_backend(<pid>);

Ensure that all your read-only applications close and releases all connections and hence these locks!

swatisinghi
  • 667
  • 7
  • 9
7

I've experienced the same problem. It turned out to be opened transaction ran from somewhere else.

For example, if you have 2 shells open with redshift shell, you will not be able to drop a table from the first shell, that participate in an open transaction in the second shell.

After I committed / rolled back in the second window, truncate worked perfectly.

Hope it helped.

diemacht
  • 2,022
  • 7
  • 30
  • 44