citusdata / citusdata/citus

SET CONSTRAINTS ... DEFERRED doesn't work

Open
#2,234 0 comments 0 reactions 0 assignees View on GitHub
bug warm-up
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

In foreign constraints, both from distributed to distributed or from distributed to reference tables, if we create the foreign constraint as the following;
```
CREATE TABLE test_table_1(id int PRIMARY KEY);
CREATE TABLE test_table_2(id int, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id) DEFERRABLE INITIALLY IMMEDIATE);
SELECT create_distributed_table('test_table_1', 'id');
SELECT create_distributed_table('test_table_2', 'value_1');
```

The foreign constraint is deferrable and initially immediate. Thus if we defer the constraint in the beginning of a transaction, it should not affect our commands until `COMMIT`. However, when we run the following commands, we see that it does not get deferred.

```
postgres=# begin;
BEGIN
Time: 0.631 ms
postgres=# set constraints test_table_2_value_1_fkey deferred;
SET CONSTRAINTS
Time: 0.563 ms
postgres=# insert into test_table_2 values (1,1);
ERROR: 23503: insert or update on table "test_table_2_103849" violates foreign key constraint "test_table_2_value_1_fkey_103849"
DETAIL: Key (value_1)=(1) is not present in table "test_table_1_103785".
CONTEXT: while executing command on localhost:9702
LOCATION: ReportResultError, remote_commands.c:302
Time: 17.863 ms
```

Well, more importantly, we stay quite when `SET CONSTRAINTS` is issued, we don't even give an error saying that we do not support this feature. The reason is that in multi_ProcessUtility function, we do not have the action item for this command. We should catch it there with
```
if (IsA(parsetree, ConstraintsSetStmt))
{
ddlJobs = [PLAN THE QUERY]
}
```

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.