SET search_path TO nested_execution; SET citus.enable_local_execution TO on; \set VERBOSITY terse -- nested execution from queries on distributed tables is generally disallowed SELECT dist_query_single_shard(key) FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard SELECT dist_query_multi_shard() FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard SELECT ref_query() FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard SELECT dist_query_single_shard(key) FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard SELECT dist_query_multi_shard() FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard SELECT ref_query() FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard -- nested execution is allowed outside of an aggregate -- note that this behaviour is different if distributed has only 1 shard -- however, this test always uses 4 shards SELECT dist_query_single_shard(count(*)::int) FROM distributed; dist_query_single_shard --------------------------------------------------------------------- 1 (1 row) SELECT dist_query_multi_shard()+count(*) FROM distributed; ?column? --------------------------------------------------------------------- 20 (1 row) SELECT ref_query()+count(*) FROM distributed; ?column? --------------------------------------------------------------------- 20 (1 row) -- nested execution is allowed in a query that only has intermediate results SELECT dist_query_single_shard(key) FROM (SELECT key FROM distributed LIMIT 1) s; dist_query_single_shard --------------------------------------------------------------------- 1 (1 row) SELECT dist_query_multi_shard() FROM (SELECT key FROM distributed LIMIT 1) s; dist_query_multi_shard --------------------------------------------------------------------- 10 (1 row) SELECT ref_query() FROM (SELECT key FROM distributed LIMIT 1) s; ref_query --------------------------------------------------------------------- 10 (1 row) -- nested execution from queries on reference tables is generally allowed SELECT dist_query_single_shard(id::int) FROM reference WHERE id = 1; dist_query_single_shard --------------------------------------------------------------------- 1 (1 row) SELECT dist_query_multi_shard() FROM reference WHERE id = 1; dist_query_multi_shard --------------------------------------------------------------------- 10 (1 row) SELECT ref_query() FROM reference WHERE id = 1; ref_query --------------------------------------------------------------------- 10 (1 row) -- repeat checks in insert..select (somewhat different code path) INSERT INTO distributed SELECT dist_query_single_shard(key) FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard INSERT INTO distributed SELECT dist_query_multi_shard() FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard INSERT INTO distributed SELECT ref_query() FROM distributed WHERE key = 1; ERROR: cannot execute a distributed query from a query on a shard INSERT INTO distributed SELECT dist_query_single_shard(key) FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard INSERT INTO distributed SELECT dist_query_multi_shard() FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard INSERT INTO distributed SELECT ref_query() FROM distributed LIMIT 1; ERROR: cannot execute a distributed query from a query on a shard BEGIN; INSERT INTO distributed SELECT dist_query_single_shard(count(*)::int) FROM distributed; INSERT INTO distributed SELECT dist_query_multi_shard()+count(*) FROM distributed; INSERT INTO distributed SELECT ref_query()+count(*) FROM distributed; ROLLBACK; BEGIN; INSERT INTO distributed SELECT dist_query_single_shard(key) FROM (SELECT key FROM distributed LIMIT 1) s; INSERT INTO distributed SELECT dist_query_multi_shard() FROM (SELECT key FROM distributed LIMIT 1) s; INSERT INTO distributed SELECT ref_query() FROM (SELECT key FROM distributed LIMIT 1) s; ROLLBACK; BEGIN; INSERT INTO distributed SELECT dist_query_single_shard(id::int) FROM reference WHERE id = 1; INSERT INTO distributed SELECT dist_query_multi_shard() FROM reference WHERE id = 1; INSERT INTO distributed SELECT ref_query() FROM reference WHERE id = 1; ROLLBACK; -- nested execution without local execution is disallowed (not distinguishable from queries on shard) SET citus.enable_local_execution TO off; SELECT dist_query_single_shard(id::int) FROM reference WHERE id = 1; ERROR: cannot execute a distributed query from a query on a shard SELECT dist_query_multi_shard() FROM reference WHERE id = 1; ERROR: cannot execute a distributed query from a query on a shard SELECT ref_query() FROM reference WHERE id = 1; ERROR: cannot execute a distributed query from a query on a shard