Executes statement on every replication group.

Functions as shardman.broadcast_all_sql and returns an executed SQL statement results.

You may optionally set include_rgid to true, then the resulting tuples will have a number of the node the tuple originated from.

Example with include_rgid set to false:

select shardman.broadcast_query('SELECT relname from pg_class where relkind=''f''');
broadcast_query
-----------------
(t_1_fdw)
(t_2_fdw)
(t_0_fdw)
(t_2_fdw)
(t_0_fdw)
(t_1_fdw)
(6 rows)

Example with include_rgid set to true:

select shardman.broadcast_query('SELECT relname from pg_class where relkind=''f''', include_rgid => true);
broadcast_query
-----------------
(1,t_1_fdw)
(1,t_2_fdw)
(2,t_0_fdw)
(2,t_2_fdw)
(3,t_0_fdw)
(3,t_1_fdw)
(6 rows)

Executes statement on every replication group but the current one.

Finds out which partition of a sharded table with oid relid the val belongs to. Returns NULL if the sharded table with oid relid does not exist. Returns the local schema name and relation names. If the value belongs to a partition stored in another replication group, also returns the remote schema and relation name. Returns only rgid if second-level partitioning is used.

Example:

select * from  shardman.get_partition_for_value('pgbench_branches'::regclass, 20);
rgid | local_nspname |      local_relname      | remote_nspname |   remote_relname
------+---------------+-------------------------+----------------+---------------------
  3 | public        | pgbench_branches_17_fdw | public         | pgbench_branches_17
                

Performs cluster-wide analysis of sharded and replicated tables. First, this function executes ANALYZE on all local partitions of sharded tables on each node, then sends this statistics to other nodes. Next, it selects one node per replicated table and runs ANALYZE of this table on the selected node. Gathered statistics is broadcast to all other nodes in the cluster. Note that replicated tables are only analysed once, unlike if you run ANALYZE on each node.

Example:

select shardman.global_analyze();

It results in the statistics gathered for all partitions and the replicated table, except for the statistics for the partitioned table.pg_class

Performs cluster-wide analysis of sharded and global tables in parallel. Unlike shardman.global_analyze, this procedure executes ANALYZE on all local partitions of sharded tables on all nodes simultaneously. Also, the procedure does not keep partitions locked until the end of the operation.

The force parameter determines how often the procedure executes ANALYZE. If true (default), the procedure always executes ANALYZE. If false, the procedure uses autovacuum_analyze_threshold and autovacuum_analyze_scale_factor to prevent frequent ANALYZE calls.

The manage_txn parameter determines the way COMMIT operations are processed after executing ANALYZE. If true (default), a COMMIT is performed after executing ANALYZE on each sharded or global table. If false, no COMMIT operations are performed.

Example:

CALL shardman.global_analyze_async(force="false",manage_txn="true");

Attaches a previously detached subpartition number snum to a locally-partitioned table relid as a partition for the values within partition_bound. All subpartition tables and foreign tables should already exist. The partition_bound parameter is a pair of lower and upper bounds for the partition. If lower and upper bounds are both NULL, the subpartition is attached as the default one.

The operation is performed cluster-wide.

Example:

select shardman.attach_subpart('pgbench_history'::regclass, 1,$${'2021-01-01 00:00', '2022-01-01 00:00'}$$);

Creates a subpartition number snum for a locally-partitioned table relid as a partition for the values within partition_bound. The partition_bound parameter is a pair of lower and upper bounds for the partition. If lower and upper bounds are both NULL, the subpartition is created as the default one. If the subpartition number is not specified, it will be selected as the next available partition number.

The operation is performed cluster-wide.

Examples:

select shardman.create_subpart('pgbench_history'::regclass, 1, $${'2021-01-01 00:00', '2022-01-01 00:00'}$$);
select shardman.create_subpart('pgbench_history'::regclass, partition_bound:=$${'2022-01-01 00:00', '2023-01-01 00:00'}$$);

Detaches a subpartition number snum from a locally-partitioned table relid. The partition number can be determined from the shardman.subparts view.

The operation is performed cluster-wide.

Example:

select shardman.detach_subpart('pgbench_history'::regclass, 1);

Drops subpartition number snum from locally-partitioned table relid. Partition number can be determined from the shardman.subparts view.

The operation is performed cluster-wide.

Example:

select shardman.drop_subpart('pgbench_history'::regclass, 1);

Returns whether the current session is the query coordinator. This check allows avoiding cases where managed table triggers fire twice, first on the query coordinator, then on the remote nodes when data is modified.

SELECT shardman.am_coordinator();
am_coordinator
----------------
t
(1 row)

Example of the trigger function checking the query coordinator:

CREATE OR REPLACE FUNCTION trg_func() RETURNS TRIGGER
AS $$
BEGIN
IF NOT shardman.am_coordinator() THEN
      -- exit on non coordinator
      RETURN NEW;
END IF;
-- execute only by coordinator
RAISE WARNING 'Trigger fired!';
END
$$ LANGUAGE plpgsql;