-- Create 2 partition tables and load 5 rows in each partition
CREATE TABLE p (
    v         int
) PARTITION BY RANGE (v);
CREATE TABLE p1 PARTITION OF p
    FOR VALUES FROM (0) TO (5);
CREATE TABLE p2 PARTITION OF p
    FOR VALUES FROM (5) TO (10);
INSERT INTO p SELECT n FROM generate_series(0, 9) as n;

-- Start a RR transaction and performa simple select to generate a transaction level snapshot
begin; set transaction isolation level repeatable read ; select;

-- in the background start a select on "p" to take a lock on the table. 
-- Run it will sleep so it keeps running.
\! psql -c "select pg_sleep(10) from p" >/dev/null>/dev/null &
select pg_sleep(1);

-- in the background start DETACH PARTITION CONCURRENTLY, which will hang due to the previous select on p with the sleep.
\! psql -c "ALTER TABLE p DETACH PARTITION p1 CONCURRENTLY" >/dev/null>/dev/null &
select pg_sleep(1);

-- terminate the DETACH PARTITION CONCURRENTLY and the select pg_sleep
\! psql -c "select query, pg_terminate_backend(pid) from pg_stat_activity where query IN ('select pg_sleep(10) from p', 'ALTER TABLE p DETACH PARTITION p1 CONCURRENTLY') and pid <> pg_backend_pid()"
select pg_sleep(1);

-- now we see that the partition is detached from the perspective of other transactions, but not this RR transaction
\! psql -c "select inhrelid::regclass, inhparent::regclass, inhdetachpending from pg_inherits;" 
select inhrelid::regclass, inhparent::regclass, inhdetachpending from pg_inherits;

-- kick off a sinalize in the background, which will hang waiting on the snapshot of this ongoing RR transactions
\! psql -c "ALTER TABLE p DETACH PARTITION p1 FINALIZE" >/dev/null>/dev/null &
select pg_sleep(1);
select query, state from pg_stat_activity where query  = 'ALTER TABLE p DETACH PARTITION p1 FINALIZE';

-- selecting from "p" now results in a deadlock
select * from p;

END;
\d+ p


