Ev4Ef1fU

1DROP TABLE some_table;
2DROP TABLE another_table; 
3     
4BEGIN;
5DROP TABLE some_table;
6CREATE TABLE some_table ( new_definition );
7INSERT INTO some_table (...) VALUES (...);
8COMMIT;
9
10
11BEGIN;
12DROP TABLE other_table;
13CREATE TABLE other_table ( new_definition );
14INSERT INTO other_table (...) VALUES (...);
15COMMIT; 
16     
17BEGIN;
18    -- look for any query locking the table and cancel it
19    SELECT 
20        pg_cancel_backend(pid)
21    FROM 
22        pg_stat_activity
23    WHERE 
24        pid IN (SELECT pid FROM pg_locks)
25    AND 
26        query LIKE '%temp_table%'
27    AND 
28        -- except this specific query
29        query not like '%pg_stat_activity%';
30
31    -- drop table and recreate it without issue
32    -- since nothing else is locking it
33    DROP TABLE IF EXISTS temp_table;
34    CREATE TABLE temp_table(input_id VARCHAR);
35COMMIT;