Evaluate `STABLE` functions on the coordinator for non-insert queries
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
There is an existing [`TODO`](https://github.com/citusdata/citus/blob/main/src/backend/distributed/executor/citus_custom_scan.c#L336) 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()`.
```sql
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:
```sql
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:
```sql
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.
Contributor guide
Assessment
This issue has not been assessed yet.