citusdata / citusdata/citus

Alternative approach for DML implementation

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

Description

Right now we implement, or are planning to implement, DML like INSERT/UPDATE/DELETE and COPY via various hooks. An alternative approach of doing so would be to use INSTEAD OF triggers as much as possible.

The current approach has a number of problems:
- Several places in the system need to be modified, using different types of hooks
- It is kind of hard to force evaluation of sequences and expressions to happen on the master; which is kind of important to make results consistent across now (think NOW()) and to support sequences
- RETURNING is not supported
- Multi-row DML statements are nontrivial to implement
- It is comparatively hard to support INSERT INTO distributed_table SELECT not_distributed_query;

The idea would be to change the type of distributed tables on the master to views (can be done with CREATE RULE "_RETURN" ON SELECT ...) and create INSTEAD triggers on them. Those get called for every DML statement, including when COPY inserts rows. They implicitly evaluate expressions on the master.

For plain INSERTs we'd, at least initially, basically do the equivalent of ExecuteDistributedModify() inside the trigger. For COPY we'd check whether we already (previous trigger invocation or utility hook) set up a "copy state" (essentially the stuff before the loop in `CitusCopyFrom()`` from #366) and then execute the equivalent of`CopyRowToPlacements()``` from #366.

Eventually we could also always use a bulk method like the proposed copy support uses for inserts - that'd e.g. make INSERT INTO ... VALUES (),(),(),...; and INSERT INTO ... SELECT \* FROM plain_rel; quite fast.

UPDATEs and DELETEs are a bit more finnicky, since we don't want to first pull the row to the master, but it nonetheless seems possible to handle them similar to what I've described above; alternatively it could also make sense to continue push UPDATEs DELETEs down like we currently do - essentially UPDATE/DELETE are much closer to SELECTs than INSERT is; and there's also far fewer places that enter UPDATE/DELETE codepaths.

@fdr That's basically my proposal from lunch.
@marcocitus Do you have any comments about the idea?

Contributor guide

Open the contributing guide

Research direction

Start by reading the existing DML hook paths and the CitusCopyFrom(), ExecuteDistributedModify(), and CopyRowToPlacements() entry points mentioned in the issue, along with issue #366. Compare how INSERT, COPY, UPDATE, and DELETE currently flow through the system. The issue has no concrete acceptance criteria, so completion would require an agreed design and scope before implementation.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql
Domain
databases, distributed-systems
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
20/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.