Concurrent shard moves with colocated table creation might fail to create placement for the new table

[onderkalaci]

Steps to repro:

-- session 1:
CREATE TABLE test_1 (a int PRIMARY KEY);
SELECT create_distributed_table('test_1', 'a', colocate_with:='none');

-- session 2

BEGIN;
	CREATE TABLE test_2 (a int PRIMARY KEY);
	SELECT create_distributed_table('test_2', 'a', colocate_with:='test_1');

-- back to session 1
-- this command got blocked

 WITH shardid AS (SELECT shardid FROM pg_dist_shard where logicalrelid = 'test_1'::regclass)
 	SELECT citus_move_Shard_placement(shardid.shardid, 'localhost', 9700, 'localhost', 9701) FROM shardid LIMIT 1;

-- switch to session 2 again

COMMIT;

-- now, from any node see the the placement for test_2 is lost

SELECT * FROM test_1 JOIN test_2 USING (a);
WARNING:  could not find any shard placements for shardId 107186
WARNING:  could not find any shard placements for shardId 107186
ERROR:  could not find length of shard 107186
DETAIL:  Could not find any shard placements for the shard.
Time: 2.359 ms

-- ops, one shard DOES NOT HAVE any placements

 SELECT count(*) FROM pg_dist_shard LEFT JOIN pg_dist_shard_placement USING(shardid) WHERE nodename IS NULL;
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
(1 row)


Time: 1.865 ms

[marcocitus]

This happens in parallel for different tenants

I don't think we support parallel shard moves. We might be missing a lock somewhere.

[onderkalaci]

I created a script which does almost the above, but couldn't reproduce the problem yet: https://gist.github.com/onderkalaci/9c72d32adea42848e6368a7e943cf246

Needs more investigatio

[onderkalaci]

I don't think we support parallel shard moves. We might be missing a lock somewhere.

We do seem to acquire proper lock here:

Two shard moves block each other:

 select * from citus_lock_waits;
┌─[ RECORD 1 ]──────────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ waiting_gpid                          │ 10000049454                                                                                       │
│ blocking_gpid                         │ 10000049328                                                                                       │
│ blocked_statement                     │ select citus_move_shard_placement(120072, 'localhost', 9700, 'localhost', 9702, 'force_logical'); │
│ current_statement_in_blocking_process │ SELECT citus_move_shard_placement(120066, 'localhost', 9700, 'localhost', 9702, 'force_logical'); │
│ waiting_nodeid                        │ 1                                                                                                 │
│ blocking_nodeid                       │ 1                                                                                                 │
└───────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────┘

Time: 87.205 ms

-- waiting on an advisory lock
 select * from pg_locks where pid = 49454 and not granted;
┌─[ RECORD 1 ]───────┬───────────────────────────────┐
│ locktype           │ advisory                      │
│ database           │ 13236                         │
│ relation           │                               │
│ page               │                               │
│ tuple              │                               │
│ virtualxid         │                               │
│ transactionid      │                               │
│ classid            │ 0                             │
│ objid              │ 0                             │
│ objsubid           │ 12                            │
│ virtualtransaction │ 12/39861                      │
│ pid                │ 49454                         │
│ mode               │ ExclusiveLock                 │
│ granted            │ f                             │
│ fastpath           │ f                             │
│ waitstart          │ 2022-07-14 14:47:02.270842+02 │
└────────────────────┴───────────────────────────────┘

[onderkalaci]

I'm not investigating whether there are any issues with deferred drop. Note that they ensured that there is only one maintenance daemon running on the server

[onderkalaci]

Might be related to #4909, marking for visibility