[YSQL] Trigger + FK Constraint discards Single Row Optimization during UPDATE TABLE

[karthik-ramanathan-3006]

Jira Link: DB-7859

Description

Consider a simple table with the following schema:

CREATE TABLE test(k INT PRIMARY KEY, v INT);

yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v      | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (k HASH)

Insert a row into the table, and update the non-index column 'v'. Observe the query plan for the update command:

INSERT INTO test VALUES(1, 1);
UPDATE test SET v = 2 WHERE k = 1;

-- The update query yields the following plan:
yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v = 2 WHERE k = 1;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..4.11 rows=1 width=40) (actual time=12.930..12.930 rows=0 loops=1)
   ->  Result  (cost=0.00..4.11 rows=1 width=40) (actual time=0.005..0.006 rows=1 loops=1)
         Storage Table Write Requests: 1
 Planning Time: 0.315 ms
 Execution Time: 13.016 ms
 Storage Read Requests: 0
 Storage Write Requests: 1
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 0
 Storage Execution Time: 0.000 ms
 Peak Memory Usage: 0 kB
(12 rows)

Create a parent table to be used for foreign key references:

CREATE TABLE parent(h INT PRIMARY KEY);
INSERT INTO parent VALUES(1);
ALTER TABLE test ADD CONSTRAINT "test_fk" FOREIGN KEY (k) REFERENCES parent(h);

yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v      | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (k HASH)
Foreign-key constraints:
    "test_fk" FOREIGN KEY (k) REFERENCES parent(h)

Now, observe the query plan on issuing the same update statement: it should remain the same.

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v = 2 WHERE k = 1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..4.11 rows=1 width=72) (actual time=7.451..7.451 rows=0 loops=1)
   ->  Result  (cost=0.00..4.11 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=1)
         Storage Table Write Requests: 1
 Planning Time: 6.482 ms
 Execution Time: 7.540 ms
 Storage Read Requests: 0
 Storage Write Requests: 1
 Catalog Read Requests: 3
 Catalog Read Execution Time: 7.740 ms
 Catalog Write Requests: 0
 Storage Flush Requests: 0
 Storage Execution Time: 7.740 ms
 Peak Memory Usage: 8 kB
(13 rows)

Next, remove the foreign key constrain and add a simple no-op trigger.

ALTER TABLE test DROP CONSTRAINT test_fk;

CREATE FUNCTION simple_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN NEW;
    END;
    $$;

CREATE TRIGGER trigger_test BEFORE INSERT ON test FOR EACH ROW EXECUTE FUNCTION simple_trigger();

yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v      | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (k HASH)
Triggers:
    trigger_test BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE simple_trigger()

Now, observe the query plan on issuing the same update statement: it should remain the same.

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v = 2 WHERE k = 1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..4.11 rows=1 width=40) (actual time=6.454..6.454 rows=0 loops=1)
   ->  Result  (cost=0.00..4.11 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=1)
         Storage Table Write Requests: 1
 Planning Time: 3.879 ms
 Execution Time: 6.519 ms
 Storage Read Requests: 0
 Storage Write Requests: 1
 Catalog Read Requests: 1
 Catalog Read Execution Time: 1.819 ms
 Catalog Write Requests: 0
 Storage Flush Requests: 0
 Storage Execution Time: 1.819 ms
 Peak Memory Usage: 0 kB
(13 rows)

Finally, add back the foreign key constraint:

ALTER TABLE test ADD CONSTRAINT "test_fk" FOREIGN KEY (k) REFERENCES parent(h);

yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v      | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (k HASH)
Foreign-key constraints:
    "test_fk" FOREIGN KEY (k) REFERENCES parent(h)
Triggers:
    trigger_test BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE simple_trigger()

The update statement now results in a different query plan that no longer performs the single RPC update:

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v = 2 WHERE k = 1;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..4.11 rows=1 width=72) (actual time=3.713..3.713 rows=0 loops=1)
   ->  Index Scan using test_pkey on test  (cost=0.00..4.11 rows=1 width=72) (actual time=3.295..3.304 rows=1 loops=1)
         Index Cond: (k = 1)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 2.428 ms
         Storage Table Write Requests: 1
 Planning Time: 7.289 ms
 Execution Time: 13.016 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 2.428 ms
 Storage Write Requests: 1
 Catalog Read Requests: 3
 Catalog Read Execution Time: 6.119 ms
 Catalog Write Requests: 0
 Storage Flush Requests: 1
 Storage Flush Execution Time: 5.517 ms
 Storage Execution Time: 14.064 ms
 Peak Memory Usage: 43 kB
(18 rows)

Summary: Updates to a table having both a FK constraint and a trigger are performed over multiple RPCs when they should ideally be performed over a single RPC.

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[karthik-ramanathan-3006]

Related to #18822