Create Trigger / Drop Trigger on distributed tables

[ozgune]

We currently don't have native support built-in for distributed tables. Users who want to use triggers do so by manually creating them on a distributed table's shards on the worker nodes.

I'm opening this issue to track adding CREATE TRIGGER and DROP TRIGGER support on distributed tables. I'm also listing a few use-cases that relate to trigger support below.

1. Propagate CREATE TRIGGER to all shards and shard placements: This could for example be useful in the context of full text search.
2. Propagate CREATE TRIGGER to all shards but only one of the shard placements: #228 and the mailing list update there talks about an example usage.
3. Propagate CREATE TRIGGER to pass along a notification: For example, an IoT database may need to alarm if the underlying sensor reports that the battery's charge rate < 5%.
4. Ingest from source table to colocated destination table: In this example, the source table stores raw events, and the destination table has aggregated stats. The user creates a trigger to roll up raw events from the source table, and put them into the destination table.

[marcocitus]

Phase 1 for enabling triggers on distributed tables:

• Remove errors when using triggers on distributed tables, but disallow statement-level triggers, which would trigger on every shard
• Ensure the trigger function is distributed to workers
• Ensure metadata is synchronized, could reuse distributed function infrastructure.
• After any write to a reference table, enforce sequential execution for subsequent writes to any distributed table that has a trigger, such that we can safely allow trigger functions to read from reference tables. This is similar to the distributed table having a foreign key to all reference tables.
• Within the trigger, allow only router planner reads and writes that use the same partition column value as the row being modified. We could do this by having another (hidden) trigger that registers the partition column value of the row being modified, and then adding checks to the router planner/executor that verify that the partition column value used in operations within the trigger is the same.
• We could relieve the previous restriction for single-statement transactions. In particular, if there was no assign_distributed_transaction_id call, we can effectively treat the trigger as a distributed transaction in which 1 local execution happened (namely, the write to the shard that triggered the trigger). However, this inherently breaks multi-shard modifications. It may be useful to enable/disable this behaviour via a GUC.
• Verify that the trigger did not change the distribution column value by using an additional (hidden) AFTER trigger.

For reference tables, we will somehow need to distinguish between two types of triggers:

• Trigger modifies rows, needs to happen on all replicas
• Trigger writes to database, needs to happen only on coordinator. Writes to reference tables with triggers from workers cannot be supported.

The two types are mutually exclusive and it's not clear which is preferable or how to let the user pick.

[marcocitus]

A common scenario for triggers it insert into an event log when the table is modified. In most case the even log will not be co-located with the source table. It will in many cases be bigger than the source table, so it should be distributed.

An option could be to introduce a specialized table type where inserts are always local. Alternatively, we should support nested transactions and ensure that subsequent commands do not read from the table or read only via the owner of the nested transaction.