citusdata / citusdata/citus

UPDATE queries on distributed/reference table with volatile functions error out

Open
#4,795 4 comments 0 reactions 0 assignees View on GitHub
sql
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Citus version: 10.0
Customer reported issue.

**Steps to repro:**
CREATE TABLE reference_key (id int, s text);
SELECT create_reference_table('reference_key');
citus=> UPDATE reference_key SET s=PGP_SYM_ENCRYPT('123', 'sai_test') where id=1;
ERROR: functions used in UPDATE queries on distributed tables must not be VOLATILE

CREATE TABLE distributed_key (id int, s text);
select create_distributed_table('distributed_key','id');
citus=> UPDATE distributed_key SET s=PGP_SYM_ENCRYPT('123', 'sai_test');
ERROR: functions used in UPDATE queries on distributed tables must not be VOLATILE

citus=> UPDATE distributed_key SET s=PGP_SYM_ENCRYPT('123', 'sai_test') where id=1;
ERROR: functions used in UPDATE queries on distributed tables must not be VOLATILE

**Workaround** Use a CTE to materialize the results of the volatile function on the coordinator.
```
WITH key AS (SELECT PGP_SYM_ENCRYPT('123', 'sai_test')k)
UPDATE distributed_key SET s=key.k from key;
UPDATE 0
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.