SET CONSTRAINTS ... DEFERRED doesn't work
- 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
Assessment
This issue has not been assessed yet.