Evaluate `STABLE` functions on the coordinator for non-insert queries

[Drew-Kimberly]

There is an existing TODO to evaluate stable functions on the coordinator for read paths. This would be helpful for our use-case of using Citus with the row-based sharding model in a multi-tenant application that enforces tenant boundaries with Row Level Security (RLS).

Specifically, we have a STABLE function, get_tenant(), to retrieve the tenant ID (distribution column value) for a given transaction that wraps Postgres' current_setting().

CREATE OR REPLACE FUNCTION get_tenant()
RETURNS uuid AS $$
  SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid;
$$ LANGUAGE SQL STABLE;

On write paths, get_tenant() is executed on the coordinator to derive the distribution column value set via DEFAULT. Citus defers shard selection and properly routes these queries to a specific shard using the value derived from the execution of get_tenant(). An example table setup would be:

CREATE TABLE foo_distributed (
    "id" UUID NOT NULL,
    "tenant_id" UUID NOT NULL DEFAULT get_tenant(),

    CONSTRAINT "foo_distributed_pkey" PRIMARY KEY ("id", "tenant_id")
);

SELECT create_distributed_table('foo_distributed', 'tenant_id');

ALTER TABLE foo_distributed ENABLE ROW LEVEL SECURITY;

CREATE POLICY foo_distributed_tenant_rls ON foo_distributed
TO application_rls
USING (tenant_id = get_tenant())
WITH CHECK (tenant_id = get_tenant());

However, the behavior differs for SELECT queries. Consider the following:

SELECT * FROM foo_distributed
WHERE tenant_id=get_tenant();

The above query will always result in the coordinator performing a multi-shard query. If the coordinator is able to evaluate get_tenant(), shard selection should be consistent and Citus can efficiently route the query to the proper shard.

[Drew-Kimberly]

This applies to UPDATE and DELETE statements as well where a stable function is included within the WHERE clause