public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Burd <[email protected]>
To: Jeff Davis <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Expanding HOT updates for expression and partial indexes
Date: Tue, 17 Mar 2026 14:04:11 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<akciabcu3b2hchj7adxhu4kovfaozp2pcn2z7sdljfthxcyg4o@7e6sfyzipvyy>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<abMjC0jifWB0cs5F@nathan>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Tue, Mar 17, 2026, at 12:38 PM, Jeff Davis wrote:
> On Mon, 2026-03-16 at 16:51 -0400, Greg Burd wrote:
>> > Also, the "actually changed values" is only valid for a single
>> > tuple,
>> > and it would be good to clarify that and make sure there's not a
>> > lot of
>> > room for confusion there.
>>
>> Yes, that's true... too much confusion and not enough juice for the
>> squeeze. I'm dropping that.
>
> That is an interesting case you found in that the columns targeted by
> an update are not a superset of the columns with actually changed
> values. But I'm not sure exactly what to make of that fact, and if it's
> not important for your other changes then I agree that we should drop
> it.
>
> However, it might be good to comment somewhere that your changes (which
> are based on values in specific tuples) cannot rely on
> ExecGetAllUpdatedCols(), to avoid confusion in the future.
Fair point, I'll do that.
> Regards,
> Jeff Davis
v37 attached with changes you and Nathan asked for so far. More please! :)
thanks Jeff and Nathan!
best.
-greg
Attachments:
[text/x-patch] v37-0001-Add-tests-to-cover-a-variety-of-heap-HOT-update-.patch (45.3K, 2-v37-0001-Add-tests-to-cover-a-variety-of-heap-HOT-update-.patch)
download | inline diff:
From 6553faa775465e0d525450b6b3cf84f95a02e033 Mon Sep 17 00:00:00 2001
From: Greg Burd <[email protected]>
Date: Tue, 10 Mar 2026 09:28:15 -0400
Subject: [PATCH v37 1/4] Add tests to cover a variety of heap HOT update
behaviors
This commit introduces test infrastructure for verifying Heap-Only Tuple
(HOT) update functionality in PostgreSQL. It provides a baseline for
demonstrating and validating HOT update behavior.
Regression tests:
- Basic HOT vs non-HOT update decisions
- All-or-none property for multiple indexes
- Partial indexes and predicate handling
- BRIN (summarizing) indexes allowing HOT updates
- TOAST column handling with HOT
- Unique constraints behavior
- Multi-column indexes
- Partitioned table HOT updates
Isolation tests:
- HOT chain formation and maintenance
- Concurrent HOT update scenarios
- Index scan behavior with HOT chains
---
src/test/regress/expected/hot_updates.out | 745 ++++++++++++++++++++++
src/test/regress/parallel_schedule | 5 +
src/test/regress/sql/hot_updates.sql | 605 ++++++++++++++++++
3 files changed, 1355 insertions(+)
create mode 100644 src/test/regress/expected/hot_updates.out
create mode 100644 src/test/regress/sql/hot_updates.sql
diff --git a/src/test/regress/expected/hot_updates.out b/src/test/regress/expected/hot_updates.out
new file mode 100644
index 00000000000..273fe3310da
--- /dev/null
+++ b/src/test/regress/expected/hot_updates.out
@@ -0,0 +1,745 @@
+--
+-- HOT_UPDATES
+-- Test Heap-Only Tuple (HOT) update decisions
+--
+-- This test systematically verifies that HOT updates are used when appropriate
+-- and avoided when necessary (e.g., when indexed columns are modified).
+--
+-- We use multiple validation methods:
+-- 1. Statistics functions (pg_stat_get_tuples_hot_updated)
+-- 2. pageinspect extension for HOT chain examination
+-- 3. EXPLAIN to verify index usage after updates
+--
+-- Load required extensions
+CREATE EXTENSION IF NOT EXISTS pageinspect;
+-- Function to get HOT update count
+CREATE OR REPLACE FUNCTION get_hot_count(rel_name text)
+RETURNS TABLE (
+ updates BIGINT,
+ hot BIGINT
+) AS $$
+DECLARE
+ rel_oid oid;
+BEGIN
+ rel_oid := rel_name::regclass::oid;
+
+ -- Read both committed and transaction-local stats
+ -- In autocommit mode (default for regression tests), this works correctly
+ -- Note: In explicit transactions (BEGIN/COMMIT), committed stats already
+ -- include flushed updates, so this would double-count. For explicit
+ -- transaction testing, call pg_stat_force_next_flush() before this function.
+ updates := COALESCE(pg_stat_get_tuples_updated(rel_oid), 0) +
+ COALESCE(pg_stat_get_xact_tuples_updated(rel_oid), 0);
+ hot := COALESCE(pg_stat_get_tuples_hot_updated(rel_oid), 0) +
+ COALESCE(pg_stat_get_xact_tuples_hot_updated(rel_oid), 0);
+
+ RETURN NEXT;
+END;
+$$ LANGUAGE plpgsql;
+-- Check if a tuple is part of a HOT chain (has a predecessor on same page)
+CREATE OR REPLACE FUNCTION has_hot_chain(rel_name text, target_ctid tid)
+RETURNS boolean AS $$
+DECLARE
+ block_num int;
+ page_item record;
+BEGIN
+ block_num := (target_ctid::text::point)[0]::int;
+
+ -- Look for a different tuple on the same page that points to our target tuple
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp_flags = 1
+ AND t_ctid IS NOT NULL
+ AND t_ctid = target_ctid
+ AND ('(' || block_num::text || ',' || lp::text || ')')::tid != target_ctid
+ LOOP
+ RETURN true;
+ END LOOP;
+
+ RETURN false;
+END;
+$$ LANGUAGE plpgsql;
+-- Print the HOT chain starting from a given tuple
+CREATE OR REPLACE FUNCTION print_hot_chain(rel_name text, start_ctid tid)
+RETURNS TABLE(chain_position int, ctid tid, lp_flags text, t_ctid tid, chain_end boolean) AS
+$$
+#variable_conflict use_column
+DECLARE
+ block_num int;
+ line_ptr int;
+ current_ctid tid := start_ctid;
+ next_ctid tid;
+ position int := 0;
+ max_iterations int := 100;
+ page_item record;
+ found_predecessor boolean := false;
+ flags_name text;
+BEGIN
+ block_num := (start_ctid::text::point)[0]::int;
+
+ -- Find the predecessor (old tuple pointing to our start_ctid)
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp_flags = 1
+ AND t_ctid = start_ctid
+ LOOP
+ current_ctid := ('(' || block_num::text || ',' || page_item.lp::text || ')')::tid;
+ found_predecessor := true;
+ EXIT;
+ END LOOP;
+
+ -- If no predecessor found, start with the given ctid
+ IF NOT found_predecessor THEN
+ current_ctid := start_ctid;
+ END IF;
+
+ -- Follow the chain forward
+ WHILE position < max_iterations LOOP
+ line_ptr := (current_ctid::text::point)[1]::int;
+
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp = line_ptr
+ LOOP
+ -- Map lp_flags to names
+ flags_name := CASE page_item.lp_flags
+ WHEN 0 THEN 'unused (0)'
+ WHEN 1 THEN 'normal (1)'
+ WHEN 2 THEN 'redirect (2)'
+ WHEN 3 THEN 'dead (3)'
+ ELSE 'unknown (' || page_item.lp_flags::text || ')'
+ END;
+
+ RETURN QUERY SELECT
+ position,
+ current_ctid,
+ flags_name,
+ page_item.t_ctid,
+ (page_item.t_ctid IS NULL OR page_item.t_ctid = current_ctid)::boolean
+ ;
+
+ IF page_item.t_ctid IS NULL OR page_item.t_ctid = current_ctid THEN
+ RETURN;
+ END IF;
+
+ next_ctid := page_item.t_ctid;
+
+ IF (next_ctid::text::point)[0]::int != block_num THEN
+ RETURN;
+ END IF;
+
+ current_ctid := next_ctid;
+ position := position + 1;
+ END LOOP;
+
+ IF position = 0 THEN
+ RETURN;
+ END IF;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+-- Basic HOT update (update non-indexed column)
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ indexed_col int,
+ non_indexed_col text
+) WITH (fillfactor = 50);
+CREATE INDEX hot_test_indexed_idx ON hot_test(indexed_col);
+INSERT INTO hot_test VALUES (1, 100, 'initial');
+INSERT INTO hot_test VALUES (2, 200, 'initial');
+INSERT INTO hot_test VALUES (3, 300, 'initial');
+-- Get baseline
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Should be HOT updates (only non-indexed column modified)
+UPDATE hot_test SET non_indexed_col = 'updated1' WHERE id = 1;
+UPDATE hot_test SET non_indexed_col = 'updated2' WHERE id = 2;
+UPDATE hot_test SET non_indexed_col = 'updated3' WHERE id = 3;
+-- Verify HOT updates occurred
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 3 | 3
+(1 row)
+
+-- Dump the HOT chain before VACUUMing
+WITH current_tuple AS (
+ SELECT ctid FROM hot_test WHERE id = 1
+)
+SELECT
+ has_hot_chain('hot_test', current_tuple.ctid) AS has_chain,
+ chain_position,
+ print_hot_chain.ctid,
+ lp_flags,
+ t_ctid
+FROM current_tuple,
+LATERAL print_hot_chain('hot_test', current_tuple.ctid);
+ has_chain | chain_position | ctid | lp_flags | t_ctid
+-----------+----------------+-------+------------+--------
+ t | 0 | (0,1) | normal (1) | (0,4)
+ t | 1 | (0,4) | normal (1) | (0,4)
+(2 rows)
+
+-- Vacuum the relation, expect the HOT chain to collapse
+VACUUM hot_test;
+-- Show that there is no chain after vacuum
+WITH current_tuple AS (
+ SELECT ctid FROM hot_test WHERE id = 1
+)
+SELECT
+ has_hot_chain('hot_test', current_tuple.ctid) AS has_chain,
+ chain_position,
+ print_hot_chain.ctid,
+ lp_flags,
+ t_ctid
+FROM current_tuple,
+LATERAL print_hot_chain('hot_test', current_tuple.ctid);
+ has_chain | chain_position | ctid | lp_flags | t_ctid
+-----------+----------------+-------+------------+--------
+ f | 0 | (0,4) | normal (1) | (0,4)
+(1 row)
+
+-- Non-HOT update (update indexed column)
+UPDATE hot_test SET indexed_col = 150 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 4 | 3
+(1 row)
+
+-- Verify index was updated (new value findable)
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF) SELECT id, indexed_col FROM hot_test WHERE indexed_col = 150;
+ QUERY PLAN
+---------------------------------------------------
+ Index Scan using hot_test_indexed_idx on hot_test
+ Index Cond: (indexed_col = 150)
+(2 rows)
+
+SELECT id, indexed_col FROM hot_test WHERE indexed_col = 150;
+ id | indexed_col
+----+-------------
+ 1 | 150
+(1 row)
+
+-- Verify old value no longer in index
+EXPLAIN (COSTS OFF) SELECT id FROM hot_test WHERE indexed_col = 100;
+ QUERY PLAN
+---------------------------------------------------
+ Index Scan using hot_test_indexed_idx on hot_test
+ Index Cond: (indexed_col = 100)
+(2 rows)
+
+SELECT id FROM hot_test WHERE indexed_col = 100;
+ id
+----
+(0 rows)
+
+RESET enable_seqscan;
+-- All-or-none property: updating one indexed column requires ALL index updates
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ col_a int,
+ col_b int,
+ col_c int,
+ non_indexed text
+) WITH (fillfactor = 50);
+CREATE INDEX hot_test_a_idx ON hot_test(col_a);
+CREATE INDEX hot_test_b_idx ON hot_test(col_b);
+CREATE INDEX hot_test_c_idx ON hot_test(col_c);
+INSERT INTO hot_test VALUES (1, 10, 20, 30, 'initial');
+-- Update only col_a - should NOT be HOT because an indexed column changed
+-- This means ALL indexes must be updated (all-or-none property)
+UPDATE hot_test SET col_a = 15 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 1 | 0
+(1 row)
+
+-- Now update only non-indexed column - should be HOT
+UPDATE hot_test SET non_indexed = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 2 | 1
+(1 row)
+
+-- Partial index: both old and new outside predicate (conservative = non-HOT)
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ status text,
+ data text
+) WITH (fillfactor = 50);
+-- Partial index only covers status = 'active'
+CREATE INDEX hot_test_active_idx ON hot_test(status) WHERE status = 'active';
+INSERT INTO hot_test VALUES (1, 'active', 'data1');
+INSERT INTO hot_test VALUES (2, 'inactive', 'data2');
+INSERT INTO hot_test VALUES (3, 'deleted', 'data3');
+-- Update non-indexed column on 'active' row (in predicate, status unchanged)
+-- Should be HOT
+UPDATE hot_test SET data = 'updated1' WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 1 | 1
+(1 row)
+
+-- Update non-indexed column on 'inactive' row (outside predicate)
+-- Should be HOT
+UPDATE hot_test SET data = 'updated2' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 2 | 2
+(1 row)
+
+-- Update status from 'inactive' to 'deleted' (both outside predicate)
+-- PostgreSQL is conservative: heap insert happens before predicate check
+-- So this is NON-HOT even though both values are outside predicate
+UPDATE hot_test SET status = 'deleted' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 3 | 2
+(1 row)
+
+-- Verify index still works for 'active' rows
+SELECT id, status FROM hot_test WHERE status = 'active';
+ id | status
+----+--------
+ 1 | active
+(1 row)
+
+-- Only BRIN (summarizing) indexes on non-PK columns
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ ts timestamp,
+ value int,
+ brin_col int
+) WITH (fillfactor = 50);
+CREATE INDEX hot_test_ts_brin ON hot_test USING brin(ts);
+CREATE INDEX hot_test_brin_col_brin ON hot_test USING brin(brin_col);
+INSERT INTO hot_test VALUES (1, '2024-01-01', 100, 1000);
+-- Update both BRIN columns - should still be HOT (only summarizing indexes)
+UPDATE hot_test SET ts = '2024-01-02', brin_col = 2000 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 1 | 1
+(1 row)
+
+-- Update non-indexed column - should also be HOT
+UPDATE hot_test SET value = 200 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 2 | 2
+(1 row)
+
+-- TOAST and HOT: TOASTed columns can participate in HOT
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ indexed_col int,
+ large_text text,
+ small_text text
+) WITH (fillfactor = 50);
+CREATE INDEX hot_test_idx ON hot_test(indexed_col);
+-- Insert row with TOASTed column (> 2KB)
+INSERT INTO hot_test VALUES (1, 100, repeat('x', 3000), 'small');
+-- Update non-indexed, non-TOASTed column - should be HOT
+UPDATE hot_test SET small_text = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 1 | 1
+(1 row)
+
+-- Update TOASTed column - should be HOT if indexed column unchanged
+UPDATE hot_test SET large_text = repeat('y', 3000);
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 2 | 2
+(1 row)
+
+-- Update indexed column - should NOT be HOT
+UPDATE hot_test SET indexed_col = 200;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 3 | 2
+(1 row)
+
+-- Unique constraint (unique index) behaves like regular index
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ unique_col int UNIQUE,
+ data text
+) WITH (fillfactor = 50);
+INSERT INTO hot_test VALUES (1, 100, 'data1');
+INSERT INTO hot_test VALUES (2, 200, 'data2');
+-- Update data (non-indexed) - should be HOT
+UPDATE hot_test SET data = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 2 | 2
+(1 row)
+
+-- Verify unique constraint still enforced
+SELECT id, unique_col, data FROM hot_test ORDER BY id;
+ id | unique_col | data
+----+------------+---------
+ 1 | 100 | updated
+ 2 | 200 | updated
+(2 rows)
+
+-- This should fail (unique violation)
+UPDATE hot_test SET unique_col = 100 WHERE id = 2;
+ERROR: duplicate key value violates unique constraint "hot_test_unique_col_key"
+DETAIL: Key (unique_col)=(100) already exists.
+-- Multi-column index: any column change = non-HOT
+DROP TABLE hot_test;
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ col_a int,
+ col_b int,
+ col_c int,
+ data text
+) WITH (fillfactor = 50);
+CREATE INDEX hot_test_ab_idx ON hot_test(col_a, col_b);
+INSERT INTO hot_test VALUES (1, 10, 20, 30, 'data');
+-- Update col_a (part of multi-column index) - should NOT be HOT
+UPDATE hot_test SET col_a = 15;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 1 | 0
+(1 row)
+
+-- Reset
+UPDATE hot_test SET col_a = 10;
+-- Update col_b (part of multi-column index) - should NOT be HOT
+UPDATE hot_test SET col_b = 25;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 3 | 0
+(1 row)
+
+-- Reset
+UPDATE hot_test SET col_b = 20;
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 4 | 0
+(1 row)
+
+-- Update col_c (not indexed) - should be HOT
+UPDATE hot_test SET col_c = 35;
+-- Update data (not indexed) - should be HOT
+UPDATE hot_test SET data = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+ updates | hot
+---------+-----
+ 6 | 2
+(1 row)
+
+-- Partitioned tables: HOT works within partitions
+DROP TABLE IF EXISTS hot_test_partitioned CASCADE;
+NOTICE: table "hot_test_partitioned" does not exist, skipping
+CREATE TABLE hot_test_partitioned (
+ id int,
+ partition_key int,
+ indexed_col int,
+ data text,
+ PRIMARY KEY (id, partition_key)
+) PARTITION BY RANGE (partition_key);
+CREATE TABLE hot_test_part1 PARTITION OF hot_test_partitioned
+ FOR VALUES FROM (1) TO (100) WITH (fillfactor = 50);
+CREATE TABLE hot_test_part2 PARTITION OF hot_test_partitioned
+ FOR VALUES FROM (100) TO (200) WITH (fillfactor = 50);
+CREATE INDEX hot_test_part_idx ON hot_test_partitioned(indexed_col);
+INSERT INTO hot_test_partitioned VALUES (1, 50, 100, 'initial1');
+INSERT INTO hot_test_partitioned VALUES (2, 150, 200, 'initial2');
+-- Update in partition 1 (non-indexed column) - should be HOT
+UPDATE hot_test_partitioned SET data = 'updated1' WHERE id = 1;
+-- Update in partition 2 (non-indexed column) - should be HOT
+UPDATE hot_test_partitioned SET data = 'updated2' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_test_part1');
+ updates | hot
+---------+-----
+ 1 | 1
+(1 row)
+
+SELECT * FROM get_hot_count('hot_test_part2');
+ updates | hot
+---------+-----
+ 1 | 1
+(1 row)
+
+-- Verify indexes work on partitions
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 100;
+ id
+----
+ 1
+(1 row)
+
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 200;
+ id
+----
+ 2
+(1 row)
+
+-- Update indexed column in partition - should NOT be HOT
+UPDATE hot_test_partitioned SET indexed_col = 150 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test_part1');
+ updates | hot
+---------+-----
+ 2 | 1
+(1 row)
+
+-- Verify index was updated
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 150;
+ id
+----
+ 1
+(1 row)
+
+-- ============================================================================
+-- Trigger modifications: heap_modify_tuple() and HOT
+-- ============================================================================
+-- Test that we correctly detect when triggers modify indexed columns via
+-- heap_modify_tuple(), even when those columns aren't in the UPDATE's SET clause
+CREATE TABLE hot_trigger_test (
+ id int PRIMARY KEY,
+ triggered_col int,
+ data text
+) WITH (fillfactor = 50);
+CREATE INDEX hot_trigger_idx ON hot_trigger_test(triggered_col);
+-- Create a trigger that modifies an indexed column
+CREATE OR REPLACE FUNCTION modify_triggered_col()
+RETURNS TRIGGER AS $$
+BEGIN
+ NEW.triggered_col = NEW.triggered_col + 1;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER before_update_modify
+ BEFORE UPDATE ON hot_trigger_test
+ FOR EACH ROW
+ EXECUTE FUNCTION modify_triggered_col();
+INSERT INTO hot_trigger_test VALUES (1, 100, 'initial');
+SELECT * FROM get_hot_count('hot_trigger_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Update only data column, but trigger modifies indexed column
+-- Should NOT be HOT because trigger modified an indexed column
+UPDATE hot_trigger_test SET data = 'updated' WHERE id = 1;
+-- Verify it was NOT a HOT update (indexed column was modified by trigger)
+SELECT * FROM get_hot_count('hot_trigger_test');
+ updates | hot
+---------+-----
+ 1 | 0
+(1 row)
+
+-- Verify the triggered column was actually modified
+SELECT triggered_col FROM hot_trigger_test WHERE id = 1;
+ triggered_col
+---------------
+ 101
+(1 row)
+
+DROP TABLE hot_trigger_test CASCADE;
+DROP FUNCTION modify_triggered_col();
+-- ============================================================================
+-- JSONB expression indexes and sub-attribute tracking
+-- ============================================================================
+-- Test that updates to non-indexed JSONB paths can be HOT updates
+CREATE TABLE hot_jsonb_test (
+ id int PRIMARY KEY,
+ data jsonb
+) WITH (fillfactor = 50);
+-- Create expression index on a specific JSON path
+CREATE INDEX hot_jsonb_name_idx ON hot_jsonb_test ((data->>'name'));
+INSERT INTO hot_jsonb_test VALUES
+ (1, '{"name":"Alice","age":30,"city":"NYC"}'),
+ (2, '{"name":"Bob","age":25,"city":"LA"}');
+SELECT * FROM get_hot_count('hot_jsonb_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Update non-indexed JSON path (age) - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = jsonb_set(data, '{age}', '31') WHERE id = 1;
+SELECT * FROM get_hot_count('hot_jsonb_test');
+ updates | hot
+---------+-----
+ 1 | 0
+(1 row)
+
+-- Update indexed JSON path (name) - should NOT be HOT
+UPDATE hot_jsonb_test SET data = jsonb_set(data, '{name}', '"Alice2"') WHERE id = 1;
+SELECT * FROM get_hot_count('hot_jsonb_test');
+ updates | hot
+---------+-----
+ 2 | 0
+(1 row)
+
+-- Verify index works
+SELECT id FROM hot_jsonb_test WHERE data->>'name' = 'Alice2';
+ id
+----
+ 1
+(1 row)
+
+-- Test jsonb_delete on non-indexed path - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = data - 'city' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_jsonb_test');
+ updates | hot
+---------+-----
+ 3 | 0
+(1 row)
+
+-- Test jsonb_insert on non-indexed path - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = jsonb_insert(data, '{country}', '"USA"') WHERE id = 2;
+SELECT * FROM get_hot_count('hot_jsonb_test');
+ updates | hot
+---------+-----
+ 4 | 0
+(1 row)
+
+DROP TABLE hot_jsonb_test;
+-- ============================================================================
+-- XML expression indexes and sub-attribute tracking
+-- ============================================================================
+-- Test that updates to non-indexed XML paths can be HOT updates
+CREATE TABLE hot_xml_test (
+ id int PRIMARY KEY,
+ doc xml
+) WITH (fillfactor = 50);
+-- Create expression index on a specific XPath
+CREATE INDEX hot_xml_name_idx ON hot_xml_test ((xpath('/person/name/text()', doc)));
+INSERT INTO hot_xml_test VALUES
+ (1, '<person><name>Alice</name><age>30</age></person>'),
+ (2, '<person><name>Bob</name><age>25</age></person>');
+ERROR: could not identify a comparison function for type xml
+SELECT * FROM get_hot_count('hot_xml_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Update non-indexed XPath (age) - behavior depends on XML comparison fallback
+-- Full XML value replacement means non-indexed path updates still require index comparison
+UPDATE hot_xml_test SET doc = '<person><name>Alice</name><age>31</age></person>' WHERE id = 1;
+SELECT * FROM get_hot_count('hot_xml_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Update indexed XPath (name) - should NOT be HOT
+UPDATE hot_xml_test SET doc = '<person><name>Alice2</name><age>31</age></person>' WHERE id = 1;
+SELECT * FROM get_hot_count('hot_xml_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Verify index works
+SELECT id FROM hot_xml_test WHERE xpath('/person/name/text()', doc) = ARRAY['Alice2'::text];
+ERROR: operator does not exist: xml[] = text[]
+LINE 1: ..._xml_test WHERE xpath('/person/name/text()', doc) = ARRAY['A...
+ ^
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+DROP TABLE hot_xml_test;
+-- ============================================================================
+-- GIN indexes and amcomparedatums for JSONB
+-- ============================================================================
+-- Test that GIN indexes can use amcomparedatums to enable HOT when extracted keys match
+CREATE TABLE hot_gin_test (
+ id int PRIMARY KEY,
+ tags text[],
+ properties jsonb
+) WITH (fillfactor = 50);
+-- GIN index on text array
+CREATE INDEX hot_gin_tags_idx ON hot_gin_test USING gin (tags);
+-- GIN index on JSONB (jsonb_ops - keys and values)
+CREATE INDEX hot_gin_props_idx ON hot_gin_test USING gin (properties);
+INSERT INTO hot_gin_test VALUES
+ (1, ARRAY['tag1', 'tag2'], '{"key1":"val1","key2":"val2"}'),
+ (2, ARRAY['tag3', 'tag4'], '{"key3":"val3","key4":"val4"}');
+SELECT * FROM get_hot_count('hot_gin_test');
+ updates | hot
+---------+-----
+ 0 | 0
+(1 row)
+
+-- Update that changes tag order but not content - after amcomparedatums should be HOT
+-- (GIN extracts same keys, just different order)
+UPDATE hot_gin_test SET tags = ARRAY['tag2', 'tag1'] WHERE id = 1;
+SELECT * FROM get_hot_count('hot_gin_test');
+ updates | hot
+---------+-----
+ 1 | 0
+(1 row)
+
+-- Update JSONB value (not key) - after amcomparedatums may be HOT or non-HOT
+-- depending on GIN operator class (jsonb_ops indexes both keys and values)
+UPDATE hot_gin_test SET properties = '{"key1":"val1_new","key2":"val2"}' WHERE id = 1;
+SELECT * FROM get_hot_count('hot_gin_test');
+ updates | hot
+---------+-----
+ 2 | 0
+(1 row)
+
+-- Add new tag - should NOT be HOT (different extracted keys)
+UPDATE hot_gin_test SET tags = ARRAY['tag2', 'tag1', 'tag5'] WHERE id = 1;
+SELECT * FROM get_hot_count('hot_gin_test');
+ updates | hot
+---------+-----
+ 3 | 0
+(1 row)
+
+-- Verify GIN indexes work
+SELECT id FROM hot_gin_test WHERE tags @> ARRAY['tag5'];
+ id
+----
+ 1
+(1 row)
+
+SELECT id FROM hot_gin_test WHERE properties @> '{"key1":"val1_new"}';
+ id
+----
+ 1
+(1 row)
+
+DROP TABLE hot_gin_test;
+-- ============================================================================
+-- Cleanup
+-- ============================================================================
+DROP TABLE IF EXISTS hot_test;
+DROP TABLE IF EXISTS hot_test_partitioned CASCADE;
+DROP FUNCTION IF EXISTS has_hot_chain(text, tid);
+DROP FUNCTION IF EXISTS print_hot_chain(text, tid);
+DROP FUNCTION IF EXISTS get_hot_count(text);
+DROP EXTENSION pageinspect;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index e779ada70cb..05e63a5d76f 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -137,6 +137,11 @@ test: event_trigger_login
# this test also uses event triggers, so likewise run it by itself
test: fast_default
+# ----------
+# HOT updates tests
+# ----------
+test: hot_updates
+
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/hot_updates.sql b/src/test/regress/sql/hot_updates.sql
new file mode 100644
index 00000000000..a8894006177
--- /dev/null
+++ b/src/test/regress/sql/hot_updates.sql
@@ -0,0 +1,605 @@
+--
+-- HOT_UPDATES
+-- Test Heap-Only Tuple (HOT) update decisions
+--
+-- This test systematically verifies that HOT updates are used when appropriate
+-- and avoided when necessary (e.g., when indexed columns are modified).
+--
+-- We use multiple validation methods:
+-- 1. Statistics functions (pg_stat_get_tuples_hot_updated)
+-- 2. pageinspect extension for HOT chain examination
+-- 3. EXPLAIN to verify index usage after updates
+--
+
+-- Load required extensions
+CREATE EXTENSION IF NOT EXISTS pageinspect;
+
+-- Function to get HOT update count
+CREATE OR REPLACE FUNCTION get_hot_count(rel_name text)
+RETURNS TABLE (
+ updates BIGINT,
+ hot BIGINT
+) AS $$
+DECLARE
+ rel_oid oid;
+BEGIN
+ rel_oid := rel_name::regclass::oid;
+
+ -- Read both committed and transaction-local stats
+ -- In autocommit mode (default for regression tests), this works correctly
+ -- Note: In explicit transactions (BEGIN/COMMIT), committed stats already
+ -- include flushed updates, so this would double-count. For explicit
+ -- transaction testing, call pg_stat_force_next_flush() before this function.
+ updates := COALESCE(pg_stat_get_tuples_updated(rel_oid), 0) +
+ COALESCE(pg_stat_get_xact_tuples_updated(rel_oid), 0);
+ hot := COALESCE(pg_stat_get_tuples_hot_updated(rel_oid), 0) +
+ COALESCE(pg_stat_get_xact_tuples_hot_updated(rel_oid), 0);
+
+ RETURN NEXT;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Check if a tuple is part of a HOT chain (has a predecessor on same page)
+CREATE OR REPLACE FUNCTION has_hot_chain(rel_name text, target_ctid tid)
+RETURNS boolean AS $$
+DECLARE
+ block_num int;
+ page_item record;
+BEGIN
+ block_num := (target_ctid::text::point)[0]::int;
+
+ -- Look for a different tuple on the same page that points to our target tuple
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp_flags = 1
+ AND t_ctid IS NOT NULL
+ AND t_ctid = target_ctid
+ AND ('(' || block_num::text || ',' || lp::text || ')')::tid != target_ctid
+ LOOP
+ RETURN true;
+ END LOOP;
+
+ RETURN false;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Print the HOT chain starting from a given tuple
+CREATE OR REPLACE FUNCTION print_hot_chain(rel_name text, start_ctid tid)
+RETURNS TABLE(chain_position int, ctid tid, lp_flags text, t_ctid tid, chain_end boolean) AS
+$$
+#variable_conflict use_column
+DECLARE
+ block_num int;
+ line_ptr int;
+ current_ctid tid := start_ctid;
+ next_ctid tid;
+ position int := 0;
+ max_iterations int := 100;
+ page_item record;
+ found_predecessor boolean := false;
+ flags_name text;
+BEGIN
+ block_num := (start_ctid::text::point)[0]::int;
+
+ -- Find the predecessor (old tuple pointing to our start_ctid)
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp_flags = 1
+ AND t_ctid = start_ctid
+ LOOP
+ current_ctid := ('(' || block_num::text || ',' || page_item.lp::text || ')')::tid;
+ found_predecessor := true;
+ EXIT;
+ END LOOP;
+
+ -- If no predecessor found, start with the given ctid
+ IF NOT found_predecessor THEN
+ current_ctid := start_ctid;
+ END IF;
+
+ -- Follow the chain forward
+ WHILE position < max_iterations LOOP
+ line_ptr := (current_ctid::text::point)[1]::int;
+
+ FOR page_item IN
+ SELECT lp, lp_flags, t_ctid
+ FROM heap_page_items(get_raw_page(rel_name, block_num))
+ WHERE lp = line_ptr
+ LOOP
+ -- Map lp_flags to names
+ flags_name := CASE page_item.lp_flags
+ WHEN 0 THEN 'unused (0)'
+ WHEN 1 THEN 'normal (1)'
+ WHEN 2 THEN 'redirect (2)'
+ WHEN 3 THEN 'dead (3)'
+ ELSE 'unknown (' || page_item.lp_flags::text || ')'
+ END;
+
+ RETURN QUERY SELECT
+ position,
+ current_ctid,
+ flags_name,
+ page_item.t_ctid,
+ (page_item.t_ctid IS NULL OR page_item.t_ctid = current_ctid)::boolean
+ ;
+
+ IF page_item.t_ctid IS NULL OR page_item.t_ctid = current_ctid THEN
+ RETURN;
+ END IF;
+
+ next_ctid := page_item.t_ctid;
+
+ IF (next_ctid::text::point)[0]::int != block_num THEN
+ RETURN;
+ END IF;
+
+ current_ctid := next_ctid;
+ position := position + 1;
+ END LOOP;
+
+ IF position = 0 THEN
+ RETURN;
+ END IF;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Basic HOT update (update non-indexed column)
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ indexed_col int,
+ non_indexed_col text
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_indexed_idx ON hot_test(indexed_col);
+
+INSERT INTO hot_test VALUES (1, 100, 'initial');
+INSERT INTO hot_test VALUES (2, 200, 'initial');
+INSERT INTO hot_test VALUES (3, 300, 'initial');
+
+-- Get baseline
+SELECT * FROM get_hot_count('hot_test');
+
+-- Should be HOT updates (only non-indexed column modified)
+UPDATE hot_test SET non_indexed_col = 'updated1' WHERE id = 1;
+UPDATE hot_test SET non_indexed_col = 'updated2' WHERE id = 2;
+UPDATE hot_test SET non_indexed_col = 'updated3' WHERE id = 3;
+
+-- Verify HOT updates occurred
+SELECT * FROM get_hot_count('hot_test');
+
+-- Dump the HOT chain before VACUUMing
+WITH current_tuple AS (
+ SELECT ctid FROM hot_test WHERE id = 1
+)
+SELECT
+ has_hot_chain('hot_test', current_tuple.ctid) AS has_chain,
+ chain_position,
+ print_hot_chain.ctid,
+ lp_flags,
+ t_ctid
+FROM current_tuple,
+LATERAL print_hot_chain('hot_test', current_tuple.ctid);
+
+-- Vacuum the relation, expect the HOT chain to collapse
+VACUUM hot_test;
+
+-- Show that there is no chain after vacuum
+WITH current_tuple AS (
+ SELECT ctid FROM hot_test WHERE id = 1
+)
+SELECT
+ has_hot_chain('hot_test', current_tuple.ctid) AS has_chain,
+ chain_position,
+ print_hot_chain.ctid,
+ lp_flags,
+ t_ctid
+FROM current_tuple,
+LATERAL print_hot_chain('hot_test', current_tuple.ctid);
+
+-- Non-HOT update (update indexed column)
+UPDATE hot_test SET indexed_col = 150 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Verify index was updated (new value findable)
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF) SELECT id, indexed_col FROM hot_test WHERE indexed_col = 150;
+SELECT id, indexed_col FROM hot_test WHERE indexed_col = 150;
+
+-- Verify old value no longer in index
+EXPLAIN (COSTS OFF) SELECT id FROM hot_test WHERE indexed_col = 100;
+SELECT id FROM hot_test WHERE indexed_col = 100;
+RESET enable_seqscan;
+
+-- All-or-none property: updating one indexed column requires ALL index updates
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ col_a int,
+ col_b int,
+ col_c int,
+ non_indexed text
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_a_idx ON hot_test(col_a);
+CREATE INDEX hot_test_b_idx ON hot_test(col_b);
+CREATE INDEX hot_test_c_idx ON hot_test(col_c);
+
+INSERT INTO hot_test VALUES (1, 10, 20, 30, 'initial');
+
+-- Update only col_a - should NOT be HOT because an indexed column changed
+-- This means ALL indexes must be updated (all-or-none property)
+UPDATE hot_test SET col_a = 15 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Now update only non-indexed column - should be HOT
+UPDATE hot_test SET non_indexed = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+
+-- Partial index: both old and new outside predicate (conservative = non-HOT)
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ status text,
+ data text
+) WITH (fillfactor = 50);
+
+-- Partial index only covers status = 'active'
+CREATE INDEX hot_test_active_idx ON hot_test(status) WHERE status = 'active';
+
+INSERT INTO hot_test VALUES (1, 'active', 'data1');
+INSERT INTO hot_test VALUES (2, 'inactive', 'data2');
+INSERT INTO hot_test VALUES (3, 'deleted', 'data3');
+
+-- Update non-indexed column on 'active' row (in predicate, status unchanged)
+-- Should be HOT
+UPDATE hot_test SET data = 'updated1' WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update non-indexed column on 'inactive' row (outside predicate)
+-- Should be HOT
+UPDATE hot_test SET data = 'updated2' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update status from 'inactive' to 'deleted' (both outside predicate)
+-- PostgreSQL is conservative: heap insert happens before predicate check
+-- So this is NON-HOT even though both values are outside predicate
+UPDATE hot_test SET status = 'deleted' WHERE id = 2;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Verify index still works for 'active' rows
+SELECT id, status FROM hot_test WHERE status = 'active';
+
+-- Only BRIN (summarizing) indexes on non-PK columns
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ ts timestamp,
+ value int,
+ brin_col int
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_ts_brin ON hot_test USING brin(ts);
+CREATE INDEX hot_test_brin_col_brin ON hot_test USING brin(brin_col);
+
+INSERT INTO hot_test VALUES (1, '2024-01-01', 100, 1000);
+
+-- Update both BRIN columns - should still be HOT (only summarizing indexes)
+UPDATE hot_test SET ts = '2024-01-02', brin_col = 2000 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update non-indexed column - should also be HOT
+UPDATE hot_test SET value = 200 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test');
+
+-- TOAST and HOT: TOASTed columns can participate in HOT
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ indexed_col int,
+ large_text text,
+ small_text text
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_idx ON hot_test(indexed_col);
+
+-- Insert row with TOASTed column (> 2KB)
+INSERT INTO hot_test VALUES (1, 100, repeat('x', 3000), 'small');
+
+-- Update non-indexed, non-TOASTed column - should be HOT
+UPDATE hot_test SET small_text = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update TOASTed column - should be HOT if indexed column unchanged
+UPDATE hot_test SET large_text = repeat('y', 3000);
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update indexed column - should NOT be HOT
+UPDATE hot_test SET indexed_col = 200;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Unique constraint (unique index) behaves like regular index
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ unique_col int UNIQUE,
+ data text
+) WITH (fillfactor = 50);
+
+INSERT INTO hot_test VALUES (1, 100, 'data1');
+INSERT INTO hot_test VALUES (2, 200, 'data2');
+
+-- Update data (non-indexed) - should be HOT
+UPDATE hot_test SET data = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+
+-- Verify unique constraint still enforced
+SELECT id, unique_col, data FROM hot_test ORDER BY id;
+
+-- This should fail (unique violation)
+UPDATE hot_test SET unique_col = 100 WHERE id = 2;
+
+-- Multi-column index: any column change = non-HOT
+DROP TABLE hot_test;
+
+CREATE TABLE hot_test (
+ id int PRIMARY KEY,
+ col_a int,
+ col_b int,
+ col_c int,
+ data text
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_ab_idx ON hot_test(col_a, col_b);
+
+INSERT INTO hot_test VALUES (1, 10, 20, 30, 'data');
+
+-- Update col_a (part of multi-column index) - should NOT be HOT
+UPDATE hot_test SET col_a = 15;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Reset
+UPDATE hot_test SET col_a = 10;
+
+-- Update col_b (part of multi-column index) - should NOT be HOT
+UPDATE hot_test SET col_b = 25;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Reset
+UPDATE hot_test SET col_b = 20;
+SELECT * FROM get_hot_count('hot_test');
+
+-- Update col_c (not indexed) - should be HOT
+UPDATE hot_test SET col_c = 35;
+
+-- Update data (not indexed) - should be HOT
+UPDATE hot_test SET data = 'updated';
+SELECT * FROM get_hot_count('hot_test');
+
+-- Partitioned tables: HOT works within partitions
+DROP TABLE IF EXISTS hot_test_partitioned CASCADE;
+
+CREATE TABLE hot_test_partitioned (
+ id int,
+ partition_key int,
+ indexed_col int,
+ data text,
+ PRIMARY KEY (id, partition_key)
+) PARTITION BY RANGE (partition_key);
+
+CREATE TABLE hot_test_part1 PARTITION OF hot_test_partitioned
+ FOR VALUES FROM (1) TO (100) WITH (fillfactor = 50);
+CREATE TABLE hot_test_part2 PARTITION OF hot_test_partitioned
+ FOR VALUES FROM (100) TO (200) WITH (fillfactor = 50);
+
+CREATE INDEX hot_test_part_idx ON hot_test_partitioned(indexed_col);
+
+INSERT INTO hot_test_partitioned VALUES (1, 50, 100, 'initial1');
+INSERT INTO hot_test_partitioned VALUES (2, 150, 200, 'initial2');
+
+-- Update in partition 1 (non-indexed column) - should be HOT
+UPDATE hot_test_partitioned SET data = 'updated1' WHERE id = 1;
+
+-- Update in partition 2 (non-indexed column) - should be HOT
+UPDATE hot_test_partitioned SET data = 'updated2' WHERE id = 2;
+
+SELECT * FROM get_hot_count('hot_test_part1');
+SELECT * FROM get_hot_count('hot_test_part2');
+
+-- Verify indexes work on partitions
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 100;
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 200;
+
+-- Update indexed column in partition - should NOT be HOT
+UPDATE hot_test_partitioned SET indexed_col = 150 WHERE id = 1;
+SELECT * FROM get_hot_count('hot_test_part1');
+
+-- Verify index was updated
+SELECT id FROM hot_test_partitioned WHERE indexed_col = 150;
+
+-- ============================================================================
+-- Trigger modifications: heap_modify_tuple() and HOT
+-- ============================================================================
+-- Test that we correctly detect when triggers modify indexed columns via
+-- heap_modify_tuple(), even when those columns aren't in the UPDATE's SET clause
+
+CREATE TABLE hot_trigger_test (
+ id int PRIMARY KEY,
+ triggered_col int,
+ data text
+) WITH (fillfactor = 50);
+
+CREATE INDEX hot_trigger_idx ON hot_trigger_test(triggered_col);
+
+-- Create a trigger that modifies an indexed column
+CREATE OR REPLACE FUNCTION modify_triggered_col()
+RETURNS TRIGGER AS $$
+BEGIN
+ NEW.triggered_col = NEW.triggered_col + 1;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER before_update_modify
+ BEFORE UPDATE ON hot_trigger_test
+ FOR EACH ROW
+ EXECUTE FUNCTION modify_triggered_col();
+
+INSERT INTO hot_trigger_test VALUES (1, 100, 'initial');
+
+SELECT * FROM get_hot_count('hot_trigger_test');
+
+-- Update only data column, but trigger modifies indexed column
+-- Should NOT be HOT because trigger modified an indexed column
+UPDATE hot_trigger_test SET data = 'updated' WHERE id = 1;
+
+-- Verify it was NOT a HOT update (indexed column was modified by trigger)
+SELECT * FROM get_hot_count('hot_trigger_test');
+
+-- Verify the triggered column was actually modified
+SELECT triggered_col FROM hot_trigger_test WHERE id = 1;
+
+DROP TABLE hot_trigger_test CASCADE;
+DROP FUNCTION modify_triggered_col();
+
+-- ============================================================================
+-- JSONB expression indexes and sub-attribute tracking
+-- ============================================================================
+-- Test that updates to non-indexed JSONB paths can be HOT updates
+
+CREATE TABLE hot_jsonb_test (
+ id int PRIMARY KEY,
+ data jsonb
+) WITH (fillfactor = 50);
+
+-- Create expression index on a specific JSON path
+CREATE INDEX hot_jsonb_name_idx ON hot_jsonb_test ((data->>'name'));
+
+INSERT INTO hot_jsonb_test VALUES
+ (1, '{"name":"Alice","age":30,"city":"NYC"}'),
+ (2, '{"name":"Bob","age":25,"city":"LA"}');
+
+SELECT * FROM get_hot_count('hot_jsonb_test');
+
+-- Update non-indexed JSON path (age) - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = jsonb_set(data, '{age}', '31') WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_jsonb_test');
+
+-- Update indexed JSON path (name) - should NOT be HOT
+UPDATE hot_jsonb_test SET data = jsonb_set(data, '{name}', '"Alice2"') WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_jsonb_test');
+
+-- Verify index works
+SELECT id FROM hot_jsonb_test WHERE data->>'name' = 'Alice2';
+
+-- Test jsonb_delete on non-indexed path - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = data - 'city' WHERE id = 2;
+
+SELECT * FROM get_hot_count('hot_jsonb_test');
+
+-- Test jsonb_insert on non-indexed path - should be HOT after instrumentation
+UPDATE hot_jsonb_test SET data = jsonb_insert(data, '{country}', '"USA"') WHERE id = 2;
+
+SELECT * FROM get_hot_count('hot_jsonb_test');
+
+DROP TABLE hot_jsonb_test;
+
+-- ============================================================================
+-- XML expression indexes and sub-attribute tracking
+-- ============================================================================
+-- Test that updates to non-indexed XML paths can be HOT updates
+
+CREATE TABLE hot_xml_test (
+ id int PRIMARY KEY,
+ doc xml
+) WITH (fillfactor = 50);
+
+-- Create expression index on a specific XPath
+CREATE INDEX hot_xml_name_idx ON hot_xml_test ((xpath('/person/name/text()', doc)));
+
+INSERT INTO hot_xml_test VALUES
+ (1, '<person><name>Alice</name><age>30</age></person>'),
+ (2, '<person><name>Bob</name><age>25</age></person>');
+
+SELECT * FROM get_hot_count('hot_xml_test');
+
+-- Update non-indexed XPath (age) - behavior depends on XML comparison fallback
+-- Full XML value replacement means non-indexed path updates still require index comparison
+UPDATE hot_xml_test SET doc = '<person><name>Alice</name><age>31</age></person>' WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_xml_test');
+
+-- Update indexed XPath (name) - should NOT be HOT
+UPDATE hot_xml_test SET doc = '<person><name>Alice2</name><age>31</age></person>' WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_xml_test');
+
+-- Verify index works
+SELECT id FROM hot_xml_test WHERE xpath('/person/name/text()', doc) = ARRAY['Alice2'::text];
+
+DROP TABLE hot_xml_test;
+
+-- ============================================================================
+-- GIN indexes and amcomparedatums for JSONB
+-- ============================================================================
+-- Test that GIN indexes can use amcomparedatums to enable HOT when extracted keys match
+
+CREATE TABLE hot_gin_test (
+ id int PRIMARY KEY,
+ tags text[],
+ properties jsonb
+) WITH (fillfactor = 50);
+
+-- GIN index on text array
+CREATE INDEX hot_gin_tags_idx ON hot_gin_test USING gin (tags);
+
+-- GIN index on JSONB (jsonb_ops - keys and values)
+CREATE INDEX hot_gin_props_idx ON hot_gin_test USING gin (properties);
+
+INSERT INTO hot_gin_test VALUES
+ (1, ARRAY['tag1', 'tag2'], '{"key1":"val1","key2":"val2"}'),
+ (2, ARRAY['tag3', 'tag4'], '{"key3":"val3","key4":"val4"}');
+
+SELECT * FROM get_hot_count('hot_gin_test');
+
+-- Update that changes tag order but not content - after amcomparedatums should be HOT
+-- (GIN extracts same keys, just different order)
+UPDATE hot_gin_test SET tags = ARRAY['tag2', 'tag1'] WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_gin_test');
+
+-- Update JSONB value (not key) - after amcomparedatums may be HOT or non-HOT
+-- depending on GIN operator class (jsonb_ops indexes both keys and values)
+UPDATE hot_gin_test SET properties = '{"key1":"val1_new","key2":"val2"}' WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_gin_test');
+
+-- Add new tag - should NOT be HOT (different extracted keys)
+UPDATE hot_gin_test SET tags = ARRAY['tag2', 'tag1', 'tag5'] WHERE id = 1;
+
+SELECT * FROM get_hot_count('hot_gin_test');
+
+-- Verify GIN indexes work
+SELECT id FROM hot_gin_test WHERE tags @> ARRAY['tag5'];
+SELECT id FROM hot_gin_test WHERE properties @> '{"key1":"val1_new"}';
+
+DROP TABLE hot_gin_test;
+
+-- ============================================================================
+-- Cleanup
+-- ============================================================================
+DROP TABLE IF EXISTS hot_test;
+DROP TABLE IF EXISTS hot_test_partitioned CASCADE;
+DROP FUNCTION IF EXISTS has_hot_chain(text, tid);
+DROP FUNCTION IF EXISTS print_hot_chain(text, tid);
+DROP FUNCTION IF EXISTS get_hot_count(text);
+DROP EXTENSION pageinspect;
--
2.51.2
[text/x-patch] v37-0002-Identify-modified-indexed-attributes-in-the-exec.patch (61.4K, 3-v37-0002-Identify-modified-indexed-attributes-in-the-exec.patch)
download | inline diff:
From 06ea9702713f4852c18b1e726ad35e3ff80a56c7 Mon Sep 17 00:00:00 2001
From: Greg Burd <[email protected]>
Date: Tue, 10 Mar 2026 08:17:31 -0400
Subject: [PATCH v37 2/4] Identify modified indexed attributes in the executor
on UPDATE
Refactor executor update logic to determine which indexed columns have
actually changed during an UPDATE operation rather than leaving this up
to HeapDetermineColumnsInfo() in heap_update(). Finding this set of
attributes is not heap-specific, but more general to all table AMs and
having this information in the executor could inform other decisions
about when index inserts are required and when they are not regardless
of the table AM's MVCC implementation strategy.
The heap-only tuple decision (HOT) in heap functions as it always has,
but the determination of the "modified indexed attributes"
(modified_idx_attrs, formerly known as modified_attrs).
ExecUpdateModifiedIdxAttrs() replaces HeapDetermineColumnsInfo() and is
called before table_tuple_update() crucially without the need for an
exclusive buffer lock on the page that holds the tuple being updated.
This reduces the time the buffer lock is held later within
heapam_tuple_update() and heap_update().
Besides identifying the set of modified indexed attributes
HeapDetermineColumnsInfo() was also partially responsible for the
decision about what to WAL log for the replica identity key. This logic
moved into heap_update() and out of the replacement named
HeapUpdateModifiedIdxAttrs(). Doing this allows for
simple_heap_update() and heapam_tuple_update() to share the same logic
as they both call into heap_update().
Updates stemming from logical replication also use the new
ExecUpdateModifiedIdxAttrs() in ExecSimpleRelationUpdate().
ExecUpdateModifiedIdxAttrs() uses ExecCompareSlotAttrs() to identify
which attributes have changed and then intersects that with the set of
indexed attributes to identify the modified indexed set, the
modified_idx_attrs.
This patch introduces a few helper functions to reduce code duplication
and increase readability: HeapUpdateHotAllowable(),
HeapUpdateDetermineLockmode(). These are used in both heap_update() and
simple_heap_update().
The heap_update() function is called now with lockmode pre-determined
and a boolean indicating if the update allows HOT updates or not, both
const. If during heap_update() the new tuple will fit on the same page
and that boolean is true, the update is HOT. This means that although
the functions and timing of the code involed in HOT decisions have
changed, none of the logic related to when HOT is allowed has changed.
Development of this feature exposed nondeterministic behavior in three
existing tests which have been adjusted to avoid inconsistent test
results due to tuple ordering during heap page scans.
---
src/backend/access/heap/heapam.c | 480 ++++++++++++------
src/backend/access/heap/heapam_handler.c | 31 +-
src/backend/access/table/tableam.c | 5 +-
src/backend/executor/execReplication.c | 9 +-
src/backend/executor/execTuples.c | 70 +++
src/backend/executor/nodeModifyTable.c | 88 +++-
src/backend/utils/cache/relcache.c | 44 +-
src/include/access/heapam.h | 13 +-
src/include/access/tableam.h | 8 +-
src/include/executor/executor.h | 9 +
src/include/utils/rel.h | 2 +-
src/include/utils/relcache.h | 2 +-
.../expected/syscache-update-pruned.out | 12 +-
.../specs/syscache-update-pruned.spec | 6 +-
.../regress/expected/generated_virtual.out | 2 +-
src/test/regress/expected/triggers.out | 16 +-
src/test/regress/expected/tsearch.out | 3 +-
src/test/regress/expected/updatable_views.out | 4 +-
src/test/regress/sql/generated_virtual.sql | 2 +-
src/test/regress/sql/triggers.sql | 4 +-
src/test/regress/sql/tsearch.sql | 3 +-
src/test/regress/sql/updatable_views.sql | 2 +-
22 files changed, 583 insertions(+), 232 deletions(-)
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index e5bd062de77..307855fdd67 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -37,21 +37,26 @@
#include "access/multixact.h"
#include "access/subtrans.h"
#include "access/syncscan.h"
+#include "access/sysattr.h"
+#include "access/tableam.h"
#include "access/valid.h"
#include "access/visibilitymap.h"
#include "access/xloginsert.h"
#include "catalog/pg_database.h"
#include "catalog/pg_database_d.h"
#include "commands/vacuum.h"
+#include "executor/tuptable.h"
+#include "nodes/lockoptions.h"
#include "pgstat.h"
#include "port/pg_bitutils.h"
+#include "storage/buf.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
-#include "storage/proc.h"
#include "storage/procarray.h"
#include "utils/datum.h"
#include "utils/injection_point.h"
#include "utils/inval.h"
+#include "utils/relcache.h"
#include "utils/spccache.h"
#include "utils/syscache.h"
@@ -68,11 +73,8 @@ static void check_lock_if_inplace_updateable_rel(Relation relation,
HeapTuple newtup);
static void check_inplace_rel_lock(HeapTuple oldtup);
#endif
-static Bitmapset *HeapDetermineColumnsInfo(Relation relation,
- Bitmapset *interesting_cols,
- Bitmapset *external_cols,
- HeapTuple oldtup, HeapTuple newtup,
- bool *has_external);
+static Bitmapset *HeapUpdateModifiedIdxAttrs(Relation relation,
+ HeapTuple oldtup, HeapTuple newtup);
static bool heap_acquire_tuplock(Relation relation, const ItemPointerData *tid,
LockTupleMode mode, LockWaitPolicy wait_policy,
bool *have_tuple_lock);
@@ -3312,7 +3314,7 @@ simple_heap_delete(Relation relation, const ItemPointerData *tid)
* heap_update - replace a tuple
*
* See table_tuple_update() for an explanation of the parameters, except that
- * this routine directly takes a tuple rather than a slot.
+ * this routine directly takes a heap tuple rather than a slot.
*
* In the failure cases, the routine fills *tmfd with the tuple's t_ctid,
* t_xmax (resolving a possible MultiXact, if necessary), and t_cmax (the last
@@ -3322,17 +3324,13 @@ simple_heap_delete(Relation relation, const ItemPointerData *tid)
TM_Result
heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
CommandId cid, Snapshot crosscheck, bool wait,
- TM_FailureData *tmfd, LockTupleMode *lockmode,
- TU_UpdateIndexes *update_indexes)
+ TM_FailureData *tmfd, const LockTupleMode lockmode,
+ const Bitmapset *modified_idx_attrs, const bool hot_allowed)
{
TM_Result result;
TransactionId xid = GetCurrentTransactionId();
- Bitmapset *hot_attrs;
- Bitmapset *sum_attrs;
- Bitmapset *key_attrs;
- Bitmapset *id_attrs;
- Bitmapset *interesting_attrs;
- Bitmapset *modified_attrs;
+ Bitmapset *idx_attrs,
+ *rid_attrs;
ItemId lp;
HeapTupleData oldtup;
HeapTuple heaptup;
@@ -3352,13 +3350,12 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
bool have_tuple_lock = false;
bool iscombo;
bool use_hot_update = false;
- bool summarized_update = false;
bool key_intact;
bool all_visible_cleared = false;
bool all_visible_cleared_new = false;
bool checked_lockers;
bool locker_remains;
- bool id_has_external = false;
+ bool rep_id_key_required = false;
TransactionId xmax_new_tuple,
xmax_old_tuple;
uint16 infomask_old_tuple,
@@ -3389,36 +3386,21 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
#endif
/*
- * Fetch the list of attributes to be checked for various operations.
- *
- * For HOT considerations, this is wasted effort if we fail to update or
- * have to put the new tuple on a different page. But we must compute the
- * list before obtaining buffer lock --- in the worst case, if we are
- * doing an update on one of the relevant system catalogs, we could
- * deadlock if we try to fetch the list later. In any case, the relcache
- * caches the data so this is usually pretty cheap.
- *
- * We also need columns used by the replica identity and columns that are
- * considered the "key" of rows in the table.
+ * Fetch the attributes used across all indexes on this relation as well as
+ * the replica identity and columns.
*
- * Note that we get copies of each bitmap, so we need not worry about
- * relcache flush happening midway through.
- */
- hot_attrs = RelationGetIndexAttrBitmap(relation,
- INDEX_ATTR_BITMAP_HOT_BLOCKING);
- sum_attrs = RelationGetIndexAttrBitmap(relation,
- INDEX_ATTR_BITMAP_SUMMARIZED);
- key_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_KEY);
- id_attrs = RelationGetIndexAttrBitmap(relation,
- INDEX_ATTR_BITMAP_IDENTITY_KEY);
- interesting_attrs = NULL;
- interesting_attrs = bms_add_members(interesting_attrs, hot_attrs);
- interesting_attrs = bms_add_members(interesting_attrs, sum_attrs);
- interesting_attrs = bms_add_members(interesting_attrs, key_attrs);
- interesting_attrs = bms_add_members(interesting_attrs, id_attrs);
+ * NOTE: We must compute the list before obtaining buffer lock. In the
+ * worst case, if we are doing an update on one of the relevant system
+ * catalogs, we could deadlock if we try to fetch the list later. Keep in
+ * mind that relcache returns copies of each bitmap, so we need not worry
+ * about relcache flush happening midway through, but we do need to free
+ * them.
+ */
+ idx_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_INDEXED);
+ rid_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_IDENTITY_KEY);
block = ItemPointerGetBlockNumber(otid);
- INJECTION_POINT("heap_update-before-pin", NULL);
+ INJECTION_POINT("simple_heap_update-before-pin", NULL);
buffer = ReadBuffer(relation, block);
page = BufferGetPage(buffer);
@@ -3469,20 +3451,17 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
tmfd->ctid = *otid;
tmfd->xmax = InvalidTransactionId;
tmfd->cmax = InvalidCommandId;
- *update_indexes = TU_None;
- bms_free(hot_attrs);
- bms_free(sum_attrs);
- bms_free(key_attrs);
- bms_free(id_attrs);
- /* modified_attrs not yet initialized */
- bms_free(interesting_attrs);
+ bms_free(rid_attrs);
+ bms_free(idx_attrs);
+ /* modified_idx_attrs is owned by the caller, don't free it */
+
return TM_Deleted;
}
/*
- * Fill in enough data in oldtup for HeapDetermineColumnsInfo to work
- * properly.
+ * Fill in enough data in oldtup to determine replica identity attribute
+ * requirements.
*/
oldtup.t_tableOid = RelationGetRelid(relation);
oldtup.t_data = (HeapTupleHeader) PageGetItem(page, lp);
@@ -3493,16 +3472,59 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
newtup->t_tableOid = RelationGetRelid(relation);
/*
- * Determine columns modified by the update. Additionally, identify
- * whether any of the unmodified replica identity key attributes in the
- * old tuple is externally stored or not. This is required because for
- * such attributes the flattened value won't be WAL logged as part of the
- * new tuple so we must include it as part of the old_key_tuple. See
- * ExtractReplicaIdentity.
+ * ExtractReplicaIdentity() needs to know if a modified indexed attrbute
+ * is used as a replica indentity or if any of the replica identity
+ * attributes are referenced in an index, unmodified, and are stored
+ * externally in the old tuple being replaced. In those cases it may be
+ * necessary to WAL log them to so they are available to replicas.
*/
- modified_attrs = HeapDetermineColumnsInfo(relation, interesting_attrs,
- id_attrs, &oldtup,
- newtup, &id_has_external);
+ rep_id_key_required = bms_overlap(modified_idx_attrs, rid_attrs);
+ if (!rep_id_key_required)
+ {
+ Bitmapset *attrs;
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ int attidx = -1;
+
+ /*
+ * Reduce the set under review to only the unmodified indexed replica
+ * identity key attributes. idx_attrs is copied (by bms_difference())
+ * not modified here.
+ */
+ attrs = bms_difference(idx_attrs, modified_idx_attrs);
+ attrs = bms_int_members(attrs, rid_attrs);
+
+ while ((attidx = bms_next_member(attrs, attidx)) >= 0)
+ {
+ /*
+ * attidx is zero-based, attrnum is the normal attribute number
+ */
+ AttrNumber attrnum = attidx + FirstLowInvalidHeapAttributeNumber;
+ Datum value;
+ bool isnull;
+
+ /*
+ * System attributes are not added into INDEX_ATTR_BITMAP_INDEXED
+ * bitmap by relcache.
+ */
+ Assert(attrnum > 0);
+
+ value = heap_getattr(&oldtup, attrnum, tupdesc, &isnull);
+
+ /* No need to check attributes that can't be stored externally */
+ if (isnull ||
+ TupleDescCompactAttr(tupdesc, attrnum - 1)->attlen != -1)
+ continue;
+
+ /* Check if the old tuple's attribute is stored externally */
+ if (VARATT_IS_EXTERNAL((struct varlena *) DatumGetPointer(value)))
+ {
+ rep_id_key_required = true;
+ break;
+ }
+ }
+
+ bms_free(attrs);
+ }
/*
* If we're not updating any "key" column, we can grab a weaker lock type.
@@ -3515,9 +3537,8 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
* is updates that don't manipulate key columns, not those that
* serendipitously arrive at the same key values.
*/
- if (!bms_overlap(modified_attrs, key_attrs))
+ if (lockmode == LockTupleNoKeyExclusive)
{
- *lockmode = LockTupleNoKeyExclusive;
mxact_status = MultiXactStatusNoKeyUpdate;
key_intact = true;
@@ -3534,7 +3555,7 @@ heap_update(Relation relation, const ItemPointerData *otid, HeapTuple newtup,
}
else
{
- *lockmode = LockTupleExclusive;
+ Assert(lockmode == LockTupleExclusive);
mxact_status = MultiXactStatusUpdate;
key_intact = false;
}
@@ -3613,7 +3634,7 @@ l2:
bool current_is_member = false;
if (DoesMultiXactIdConflict((MultiXactId) xwait, infomask,
- *lockmode, ¤t_is_member))
+ lockmode, ¤t_is_member))
{
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
@@ -3622,7 +3643,7 @@ l2:
* requesting a lock and already have one; avoids deadlock).
*/
if (!current_is_member)
- heap_acquire_tuplock(relation, &(oldtup.t_self), *lockmode,
+ heap_acquire_tuplock(relation, &(oldtup.t_self), lockmode,
LockWaitBlock, &have_tuple_lock);
/* wait for multixact */
@@ -3707,7 +3728,7 @@ l2:
* lock.
*/
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
- heap_acquire_tuplock(relation, &(oldtup.t_self), *lockmode,
+ heap_acquire_tuplock(relation, &(oldtup.t_self), lockmode,
LockWaitBlock, &have_tuple_lock);
XactLockTableWait(xwait, relation, &oldtup.t_self,
XLTW_Update);
@@ -3767,17 +3788,14 @@ l2:
tmfd->cmax = InvalidCommandId;
UnlockReleaseBuffer(buffer);
if (have_tuple_lock)
- UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode);
+ UnlockTupleTuplock(relation, &(oldtup.t_self), lockmode);
if (vmbuffer != InvalidBuffer)
ReleaseBuffer(vmbuffer);
- *update_indexes = TU_None;
- bms_free(hot_attrs);
- bms_free(sum_attrs);
- bms_free(key_attrs);
- bms_free(id_attrs);
- bms_free(modified_attrs);
- bms_free(interesting_attrs);
+ bms_free(rid_attrs);
+ bms_free(idx_attrs);
+ /* modified_idx_attrs is owned by the caller, don't free it */
+
return result;
}
@@ -3807,7 +3825,7 @@ l2:
compute_new_xmax_infomask(HeapTupleHeaderGetRawXmax(oldtup.t_data),
oldtup.t_data->t_infomask,
oldtup.t_data->t_infomask2,
- xid, *lockmode, true,
+ xid, lockmode, true,
&xmax_old_tuple, &infomask_old_tuple,
&infomask2_old_tuple);
@@ -3924,7 +3942,7 @@ l2:
compute_new_xmax_infomask(HeapTupleHeaderGetRawXmax(oldtup.t_data),
oldtup.t_data->t_infomask,
oldtup.t_data->t_infomask2,
- xid, *lockmode, false,
+ xid, lockmode, false,
&xmax_lock_old_tuple, &infomask_lock_old_tuple,
&infomask2_lock_old_tuple);
@@ -4086,10 +4104,11 @@ l2:
/*
* At this point newbuf and buffer are both pinned and locked, and newbuf
- * has enough space for the new tuple. If they are the same buffer, only
- * one pin is held.
+ * has enough space for the new tuple so we can use the HOT update path if
+ * the caller determined that it is allowable.
+ *
+ * NOTE: If newbuf == buffer then only one pin is held.
*/
-
if (newbuf == buffer)
{
/*
@@ -4097,20 +4116,8 @@ l2:
* to do a HOT update. Check if any of the index columns have been
* changed.
*/
- if (!bms_overlap(modified_attrs, hot_attrs))
- {
+ if (hot_allowed)
use_hot_update = true;
-
- /*
- * If none of the columns that are used in hot-blocking indexes
- * were updated, we can apply HOT, but we do still need to check
- * if we need to update the summarizing indexes, and update those
- * indexes if the columns were updated, or we may fail to detect
- * e.g. value bound changes in BRIN minmax indexes.
- */
- if (bms_overlap(modified_attrs, sum_attrs))
- summarized_update = true;
- }
}
else
{
@@ -4126,8 +4133,7 @@ l2:
* columns are modified or it has external data.
*/
old_key_tuple = ExtractReplicaIdentity(relation, &oldtup,
- bms_overlap(modified_attrs, id_attrs) ||
- id_has_external,
+ rep_id_key_required,
&old_key_copied);
/* NO EREPORT(ERROR) from here till changes are logged */
@@ -4256,7 +4262,7 @@ l2:
* Release the lmgr tuple lock, if we had it.
*/
if (have_tuple_lock)
- UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode);
+ UnlockTupleTuplock(relation, &(oldtup.t_self), lockmode);
pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer);
@@ -4270,31 +4276,12 @@ l2:
heap_freetuple(heaptup);
}
- /*
- * If it is a HOT update, the update may still need to update summarized
- * indexes, lest we fail to update those summaries and get incorrect
- * results (for example, minmax bounds of the block may change with this
- * update).
- */
- if (use_hot_update)
- {
- if (summarized_update)
- *update_indexes = TU_Summarizing;
- else
- *update_indexes = TU_None;
- }
- else
- *update_indexes = TU_All;
-
if (old_key_tuple != NULL && old_key_copied)
heap_freetuple(old_key_tuple);
- bms_free(hot_attrs);
- bms_free(sum_attrs);
- bms_free(key_attrs);
- bms_free(id_attrs);
- bms_free(modified_attrs);
- bms_free(interesting_attrs);
+ bms_free(rid_attrs);
+ bms_free(idx_attrs);
+ /* modified_idx_attrs is owned by the caller, don't free it */
return TM_Ok;
}
@@ -4467,28 +4454,115 @@ heap_attr_equals(TupleDesc tupdesc, int attrnum, Datum value1, Datum value2,
}
/*
- * Check which columns are being updated.
- *
- * Given an updated tuple, determine (and return into the output bitmapset),
- * from those listed as interesting, the set of columns that changed.
- *
- * has_external indicates if any of the unmodified attributes (from those
- * listed as interesting) of the old tuple is a member of external_cols and is
- * stored externally.
+ * HOT updates are possible when either: a) there are no modified indexed
+ * attributes, or b) the modified attributes are all on summarizing indexes.
+ * Later, in heap_update(), we can choose to perform a HOT update if there is
+ * space on the page for the new tuple and the following code has determined
+ * that HOT is allowed.
+ */
+bool
+HeapUpdateHotAllowable(Relation relation, const Bitmapset *modified_idx_attrs,
+ bool *summarized_only)
+{
+ bool hot_allowed;
+
+ /*
+ * Let's be optimistic and start off by assuming the best case, no indexes
+ * need updating and HOT is allowable.
+ */
+ hot_allowed = true;
+ *summarized_only = false;
+
+ /*
+ * Check for case (a); when there are no modified index attributes HOT is
+ * allowed.
+ */
+ if (bms_is_empty(modified_idx_attrs))
+ hot_allowed = true;
+ else
+ {
+ Bitmapset *sum_attrs = RelationGetIndexAttrBitmap(relation,
+ INDEX_ATTR_BITMAP_SUMMARIZED);
+
+ /*
+ * At least one index attribute was modified, but is this case (b)
+ * where all the modified index attributes are only used by summarizing
+ * indexes? If it is, then we need to update those indexes, but this
+ * update can still be considered heap-only (HOT) and avoid updating
+ * any non-summarizing indexes on the relation.
+ */
+ if (bms_is_subset(modified_idx_attrs, sum_attrs))
+ {
+ hot_allowed = true;
+ *summarized_only = true;
+ }
+ else
+ {
+ /*
+ * Now we know a) one or more indexed attributes were modified
+ * (changed value, not just referenced within the UPDATE) and that
+ * b) at least one of those attributes is used by a non-summarizing
+ * index. HOT is not allowed.
+ */
+ hot_allowed = false;
+ }
+
+ bms_free(sum_attrs);
+ }
+
+ return hot_allowed;
+}
+
+/*
+ * If we're not updating any attributes used when forming the index keys we can
+ * grab a weaker lock type. This allows for more concurrency when we are
+ * running simultaneously with foreign key checks.
+ */
+LockTupleMode
+HeapUpdateDetermineLockmode(Relation relation, const Bitmapset *modified_idx_attrs)
+{
+ LockTupleMode lockmode = LockTupleExclusive;
+
+ Bitmapset *key_attrs = RelationGetIndexAttrBitmap(relation,
+ INDEX_ATTR_BITMAP_KEY);
+
+ if (!bms_overlap(modified_idx_attrs, key_attrs))
+ lockmode = LockTupleNoKeyExclusive;
+
+ bms_free(key_attrs);
+
+ return lockmode;
+}
+
+/*
+ * Return a Bitmapset that contains the set of modified (changed) indexed
+ * attributes between oldtup and newtup.
*/
static Bitmapset *
-HeapDetermineColumnsInfo(Relation relation,
- Bitmapset *interesting_cols,
- Bitmapset *external_cols,
- HeapTuple oldtup, HeapTuple newtup,
- bool *has_external)
+HeapUpdateModifiedIdxAttrs(Relation relation, HeapTuple oldtup, HeapTuple newtup)
{
int attidx;
- Bitmapset *modified = NULL;
+ Bitmapset *attrs,
+ *modified_idx_attrs = NULL;
TupleDesc tupdesc = RelationGetDescr(relation);
+ /* Get the set of all attributes across all indexes for this relation */
+ attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_INDEXED);
+
+ /* No indexed attributes, we're done */
+ if (bms_is_empty(attrs))
+ return NULL;
+
+ /*
+ * This heap update function is used outside the executor and so unlike
+ * heapam_tuple_update() where there is ResultRelInfo and EState to
+ * provide the concise set of attributes that might have been modified
+ * (via ExecGetAllUpdatedCols()) we simply check all indexed attributes to
+ * find the subset that changed value. That's the "modified indexed
+ * attributes" or "modified_idx_attrs".
+ */
attidx = -1;
- while ((attidx = bms_next_member(interesting_cols, attidx)) >= 0)
+ while ((attidx = bms_next_member(attrs, attidx)) >= 0)
{
/* attidx is zero-based, attrnum is the normal attribute number */
AttrNumber attrnum = attidx + FirstLowInvalidHeapAttributeNumber;
@@ -4504,7 +4578,7 @@ HeapDetermineColumnsInfo(Relation relation,
*/
if (attrnum == 0)
{
- modified = bms_add_member(modified, attidx);
+ modified_idx_attrs = bms_add_member(modified_idx_attrs, attidx);
continue;
}
@@ -4517,7 +4591,7 @@ HeapDetermineColumnsInfo(Relation relation,
{
if (attrnum != TableOidAttributeNumber)
{
- modified = bms_add_member(modified, attidx);
+ modified_idx_attrs = bms_add_member(modified_idx_attrs, attidx);
continue;
}
}
@@ -4533,29 +4607,12 @@ HeapDetermineColumnsInfo(Relation relation,
if (!heap_attr_equals(tupdesc, attrnum, value1,
value2, isnull1, isnull2))
- {
- modified = bms_add_member(modified, attidx);
- continue;
- }
-
- /*
- * No need to check attributes that can't be stored externally. Note
- * that system attributes can't be stored externally.
- */
- if (attrnum < 0 || isnull1 ||
- TupleDescCompactAttr(tupdesc, attrnum - 1)->attlen != -1)
- continue;
-
- /*
- * Check if the old tuple's attribute is stored externally and is a
- * member of external_cols.
- */
- if (VARATT_IS_EXTERNAL((varlena *) DatumGetPointer(value1)) &&
- bms_is_member(attidx, external_cols))
- *has_external = true;
+ modified_idx_attrs = bms_add_member(modified_idx_attrs, attidx);
}
- return modified;
+ bms_free(attrs);
+
+ return modified_idx_attrs;
}
/*
@@ -4567,17 +4624,112 @@ HeapDetermineColumnsInfo(Relation relation,
* via ereport().
*/
void
-simple_heap_update(Relation relation, const ItemPointerData *otid, HeapTuple tup,
+simple_heap_update(Relation relation, const ItemPointerData *otid, HeapTuple tuple,
TU_UpdateIndexes *update_indexes)
{
TM_Result result;
TM_FailureData tmfd;
LockTupleMode lockmode;
+ TupleTableSlot *slot;
+ BufferHeapTupleTableSlot *bslot;
+ HeapTuple oldtup;
+ bool shouldFree = true;
+ Bitmapset *idx_attrs,
+ *modified_idx_attrs;
+ bool hot_allowed,
+ summarized_only;
+ Buffer buffer;
- result = heap_update(relation, otid, tup,
- GetCurrentCommandId(true), InvalidSnapshot,
- true /* wait for commit */ ,
- &tmfd, &lockmode, update_indexes);
+ Assert(ItemPointerIsValid(otid));
+
+ /*
+ * Fetch this bitmap of interesting attributes from relcache before
+ * obtaining a buffer lock because if we are doing an update on one of the
+ * relevant system catalogs we could deadlock if we try to fetch them
+ * later on. Relcache will return copies of each bitmap, so we need not
+ * worry about relcache flush happening midway through this operation.
+ */
+ idx_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_INDEXED);
+
+ INJECTION_POINT("simple_heap_update-before-pin", NULL);
+
+ /*
+ * To update a heap tuple we need to find the set of modified indexed
+ * attributes ("modified_idx_attrs") and use that to determine if a HOT
+ * update is allowable or not. When updating heap tuples via execution of
+ * UPDATE statements this set is constructed before calling into the table
+ * AM's update function by ExecUpdateModifiedIdxAttrs() which compares the
+ * old/new TupleTableSlots.
+ *
+ * Here things are a bit different, we have the old TID and the new tuple,
+ * not two TupleTableSlots, but we still need to construct a similar bitmap
+ * so as to be able to know if HOT updates are allowed or not.
+ *
+ * To do that we first have to fetch the old tuple itself, but because
+ * heapam_fetch_row_version() is static, we replicate in part that code
+ * here.
+ *
+ * This is a bit repetitive because heap_update() will again find and form
+ * the old HeapTuple from the old TID and in most cases the callers
+ * (ignoring extensions, are always catalog tuple updates) already had the
+ * set of changed attributes (the "replaces" array), but for now this minor
+ * repetition of work is necessary.
+ */
+ slot = MakeTupleTableSlot(RelationGetDescr(relation), &TTSOpsBufferHeapTuple, 0);
+ bslot = (BufferHeapTupleTableSlot *) slot;
+
+ /*
+ * Set the TID in the slot and then fetch the old tuple so we can examine
+ * it
+ */
+ bslot->base.tupdata.t_self = *otid;
+ if (!heap_fetch(relation, SnapshotAny, &bslot->base.tupdata, &buffer, false))
+ {
+ /*
+ * heap_update() checks for !ItemIdIsNormal(lp) and will return false
+ * in those cases.
+ */
+ Assert(RelationSupportsSysCache(RelationGetRelid(relation)));
+
+ *update_indexes = TU_None;
+
+ /* modified_idx_attrs not yet initialized */
+ bms_free(idx_attrs);
+ ExecDropSingleTupleTableSlot(slot);
+
+ elog(ERROR, "tuple concurrently deleted");
+
+ return;
+ }
+
+ Assert(buffer != InvalidBuffer);
+
+ /* Store in slot, transferring existing pin */
+ ExecStorePinnedBufferHeapTuple(&bslot->base.tupdata, slot, buffer);
+ oldtup = ExecFetchSlotHeapTuple(slot, false, &shouldFree);
+
+ modified_idx_attrs = HeapUpdateModifiedIdxAttrs(relation, oldtup, tuple);
+ lockmode = HeapUpdateDetermineLockmode(relation, modified_idx_attrs);
+ hot_allowed = HeapUpdateHotAllowable(relation, modified_idx_attrs, &summarized_only);
+
+ result = heap_update(relation, otid, tuple, GetCurrentCommandId(true),
+ InvalidSnapshot, true /* wait for commit */ ,
+ &tmfd, lockmode, modified_idx_attrs, hot_allowed);
+
+ if (shouldFree)
+ heap_freetuple(oldtup);
+
+ ExecDropSingleTupleTableSlot(slot);
+ bms_free(idx_attrs);
+
+ /*
+ * Decide whether new index entries are needed for the tuple
+ *
+ * If the update is not HOT, we must update all indexes. If the update is
+ * HOT, it could be that we updated summarized columns, so we either
+ * update only summarized indexes, or none at all.
+ */
+ *update_indexes = TU_None;
switch (result)
{
case TM_SelfModified:
@@ -4587,6 +4739,10 @@ simple_heap_update(Relation relation, const ItemPointerData *otid, HeapTuple tup
case TM_Ok:
/* done successfully */
+ if (!HeapTupleIsHeapOnly(tuple))
+ *update_indexes = TU_All;
+ else if (summarized_only)
+ *update_indexes = TU_Summarizing;
break;
case TM_Updated:
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 253a735b6c1..3726c867c65 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -27,7 +27,6 @@
#include "access/syncscan.h"
#include "access/tableam.h"
#include "access/tsmapi.h"
-#include "access/visibilitymap.h"
#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/index.h"
@@ -325,19 +324,26 @@ heapam_tuple_delete(Relation relation, ItemPointer tid, CommandId cid,
static TM_Result
heapam_tuple_update(Relation relation, ItemPointer otid, TupleTableSlot *slot,
CommandId cid, Snapshot snapshot, Snapshot crosscheck,
- bool wait, TM_FailureData *tmfd,
- LockTupleMode *lockmode, TU_UpdateIndexes *update_indexes)
+ bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode,
+ const Bitmapset *modified_idx_attrs, TU_UpdateIndexes *update_indexes)
{
bool shouldFree = true;
HeapTuple tuple = ExecFetchSlotHeapTuple(slot, true, &shouldFree);
+ bool hot_allowed;
+ bool summarized_only;
TM_Result result;
+ Assert(ItemPointerIsValid(otid));
+
+ hot_allowed = HeapUpdateHotAllowable(relation, modified_idx_attrs, &summarized_only);
+ *lockmode = HeapUpdateDetermineLockmode(relation, modified_idx_attrs);
+
/* Update the tuple with table oid */
slot->tts_tableOid = RelationGetRelid(relation);
tuple->t_tableOid = slot->tts_tableOid;
result = heap_update(relation, otid, tuple, cid, crosscheck, wait,
- tmfd, lockmode, update_indexes);
+ tmfd, *lockmode, modified_idx_attrs, hot_allowed);
ItemPointerCopy(&tuple->t_self, &slot->tts_tid);
/*
@@ -350,16 +356,17 @@ heapam_tuple_update(Relation relation, ItemPointer otid, TupleTableSlot *slot,
* HOT, it could be that we updated summarized columns, so we either
* update only summarized indexes, or none at all.
*/
- if (result != TM_Ok)
+ *update_indexes = TU_None;
+ if (result == TM_Ok)
{
- Assert(*update_indexes == TU_None);
- *update_indexes = TU_None;
+ if (HeapTupleIsHeapOnly(tuple))
+ {
+ if (summarized_only)
+ *update_indexes = TU_Summarizing;
+ }
+ else
+ *update_indexes = TU_All;
}
- else if (!HeapTupleIsHeapOnly(tuple))
- Assert(*update_indexes == TU_All);
- else
- Assert((*update_indexes == TU_Summarizing) ||
- (*update_indexes == TU_None));
if (shouldFree)
pfree(tuple);
diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c
index dfda1af412e..9ba72d51dfa 100644
--- a/src/backend/access/table/tableam.c
+++ b/src/backend/access/table/tableam.c
@@ -359,6 +359,7 @@ void
simple_table_tuple_update(Relation rel, ItemPointer otid,
TupleTableSlot *slot,
Snapshot snapshot,
+ const Bitmapset *modified_idx_attrs,
TU_UpdateIndexes *update_indexes)
{
TM_Result result;
@@ -369,7 +370,9 @@ simple_table_tuple_update(Relation rel, ItemPointer otid,
GetCurrentCommandId(true),
snapshot, InvalidSnapshot,
true /* wait for commit */ ,
- &tmfd, &lockmode, update_indexes);
+ &tmfd, &lockmode,
+ modified_idx_attrs,
+ update_indexes);
switch (result)
{
diff --git a/src/backend/executor/execReplication.c b/src/backend/executor/execReplication.c
index 2497ee7edc5..74a7379186b 100644
--- a/src/backend/executor/execReplication.c
+++ b/src/backend/executor/execReplication.c
@@ -33,6 +33,7 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/relcache.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/typcache.h"
@@ -906,6 +907,7 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
bool skip_tuple = false;
Relation rel = resultRelInfo->ri_RelationDesc;
ItemPointer tid = &(searchslot->tts_tid);
+ Bitmapset *modified_idx_attrs;
/*
* We support only non-system tables, with
@@ -944,8 +946,13 @@ ExecSimpleRelationUpdate(ResultRelInfo *resultRelInfo,
if (rel->rd_rel->relispartition)
ExecPartitionCheck(resultRelInfo, slot, estate, true);
+ modified_idx_attrs = ExecUpdateModifiedIdxAttrs(resultRelInfo,
+ estate, searchslot, slot);
+
simple_table_tuple_update(rel, tid, slot, estate->es_snapshot,
- &update_indexes);
+ modified_idx_attrs, &update_indexes);
+ bms_free(modified_idx_attrs);
+
conflictindexes = resultRelInfo->ri_onConflictArbiterIndexes;
diff --git a/src/backend/executor/execTuples.c b/src/backend/executor/execTuples.c
index b717b03b3d2..e8c5639b61e 100644
--- a/src/backend/executor/execTuples.c
+++ b/src/backend/executor/execTuples.c
@@ -66,6 +66,7 @@
#include "nodes/nodeFuncs.h"
#include "storage/bufmgr.h"
#include "utils/builtins.h"
+#include "utils/datum.h"
#include "utils/expandeddatum.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
@@ -1999,6 +2000,75 @@ ExecFetchSlotHeapTupleDatum(TupleTableSlot *slot)
return ret;
}
+/*
+ * ExecCompareSlotAttrs
+ *
+ * Compare the subset of attributes in attrs bewtween TupleTableSlots to detect
+ * which attributes have changed.
+ *
+ * Returns a reused when possible Bitmapset of attribute indices (using
+ * FirstLowInvalidHeapAttributeNumber convention) that differ between the two
+ * slots.
+ */
+Bitmapset *
+ExecCompareSlotAttrs(Bitmapset *attrs, TupleDesc tupdesc,
+ TupleTableSlot *s1, TupleTableSlot *s2)
+{
+ int attidx = -1;
+
+ while ((attidx = bms_next_member(attrs, attidx)) >= 0)
+ {
+ /* attidx is zero-based, attrnum is the normal attribute number */
+ AttrNumber attrnum = attidx + FirstLowInvalidHeapAttributeNumber;
+ Datum value1,
+ value2;
+ bool null1,
+ null2;
+ CompactAttribute *att;
+
+ /*
+ * If it's a whole-tuple reference, say "not equal". It's not really
+ * worth supporting this case, since it could only succeed after a
+ * no-op update, which is hardly a case worth optimizing for.
+ */
+ if (attrnum == 0)
+ continue;
+
+ /*
+ * Likewise, automatically say "not equal" for any system attribute
+ * other than tableOID; we cannot expect these to be consistent in a
+ * HOT chain, or even to be set correctly yet in the new tuple.
+ */
+ if (attrnum < 0)
+ {
+ if (attrnum == TableOidAttributeNumber)
+ attrs = bms_del_member(attrs, attidx);
+ else
+ continue;
+ }
+
+ att = TupleDescCompactAttr(tupdesc, attrnum - 1);
+ value1 = slot_getattr(s1, attrnum, &null1);
+ value2 = slot_getattr(s2, attrnum, &null2);
+
+ /* A change to/from NULL, so not equal */
+ if (null1 != null2)
+ continue;
+
+ /* Both NULL, no change/unmodified */
+ if (null2)
+ {
+ attrs = bms_del_member(attrs, attidx);
+ continue;
+ }
+
+ if (datum_image_eq(value1, value2, att->attbyval, att->attlen))
+ attrs = bms_del_member(attrs, attidx);
+ }
+
+ return attrs;
+}
+
/* ----------------------------------------------------------------
* convenience initialization routines
* ----------------------------------------------------------------
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cd5e262e0f..ea5058c7a37 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -17,6 +17,7 @@
* ExecModifyTable - retrieve the next tuple from the node
* ExecEndModifyTable - shut down the ModifyTable node
* ExecReScanModifyTable - rescan the ModifyTable node
+ * ExecUpdateModifiedIdxAttrs - find set of updated indexed columns
*
* NOTES
* The ModifyTable node receives input from its outerPlan, which is
@@ -55,6 +56,7 @@
#include "access/htup_details.h"
#include "access/tableam.h"
#include "access/tupconvert.h"
+#include "access/tupdesc.h"
#include "access/xact.h"
#include "commands/trigger.h"
#include "executor/execPartition.h"
@@ -190,6 +192,63 @@ static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
bool canSetTag);
+/*
+ * ExecUpdateModifiedIdxAttrs
+ *
+ * Find the set of attributes referenced by this relation and used in this
+ * UPDATE that now differ in value. This is done by reviewing slot datum that
+ * are in the UPDATE statment and are known to be referenced by at least one
+ * index in some way. This set is called the "modified indexed attributes" or
+ * "modified_idx_attrs". An overlap of a single index's attributes and this
+ * modified_idx_attrs set signals that the attributes in the new_tts used to
+ * form the index datum have changed.
+ *
+ * Return a Bitmapset that contains the set of modified (changed) indexed
+ * attributes between oldtup and newtup.
+ *
+ * Note: There is a similar function called HeapUpdateModifiedIdxAttrs() that operates
+ * on the old TID and new HeapTuple rather than the old/new TupleTableSlots as
+ * this function does. These two functions should mirror one another until
+ * someday when catalog tuple updates track their changes avoiding the need to
+ * re-discover them in simple_heap_update().
+ */
+Bitmapset *
+ExecUpdateModifiedIdxAttrs(ResultRelInfo *resultRelInfo,
+ EState *estate,
+ TupleTableSlot *old_tts,
+ TupleTableSlot *new_tts)
+{
+ Relation relation = resultRelInfo->ri_RelationDesc;
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ Bitmapset *attrs;
+
+ /* If no indexes, we're done */
+ if (resultRelInfo->ri_NumIndices == 0)
+ return NULL;
+
+ /*
+ * Get the set of all attributes across all indexes for this relation from
+ * the relcache, it returns us a copy of the bitmap so we can modify it.
+ *
+ * Note: We intentionally scan all indexed columns when looking for changes
+ * rather than reduce that set by intersecting it with
+ * ExecGetAllUpdatedCols(). Desipte the name it provides the set of
+ * targeted attributes in the SQL used for the UPDATE and any triggers, but
+ * that doesn't include any attributes updated using heap_modifiy_tuple().
+ * There is one test in tsearch.sql that does just that, modifies an
+ * indexed attribute that isn't specified in the SQL and so isn't present
+ * in that bitmapset.
+ */
+ attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_INDEXED);
+
+ /*
+ * When there are indexed attributes mentioned in the UPDATE then we need
+ * to find the subset that changed value. That's the "modified_idx_attrs".
+ */
+ attrs = ExecCompareSlotAttrs(attrs, tupdesc, old_tts, new_tts);
+
+ return attrs;
+}
/*
* Verify that the tuples to be produced by INSERT match the
@@ -2197,14 +2256,17 @@ ExecUpdatePrepareSlot(ResultRelInfo *resultRelInfo,
*/
static TM_Result
ExecUpdateAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot,
- bool canSetTag, UpdateContext *updateCxt)
+ ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *oldSlot,
+ TupleTableSlot *slot, bool canSetTag, UpdateContext *updateCxt)
{
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
bool partition_constraint_failed;
TM_Result result;
+ /* The set of modified indexed attributes that trigger new index entries */
+ Bitmapset *modified_idx_attrs = NULL;
+
updateCxt->crossPartUpdate = false;
/*
@@ -2321,7 +2383,16 @@ lreplace:
ExecConstraints(resultRelInfo, slot, estate);
/*
- * replace the heap tuple
+ * Next up we need to find out the set of indexed attributes that have
+ * changed in value and should trigger a new index tuple. We could start
+ * with the set of updated columns via ExecGetUpdatedCols(), but if we do
+ * we will overlook attributes directly modified by heap_modify_tuple()
+ * which are not known to ExecGetUpdatedCols().
+ */
+ modified_idx_attrs = ExecUpdateModifiedIdxAttrs(resultRelInfo, estate, oldSlot, slot);
+
+ /*
+ * Call into the table AM to update the heap tuple.
*
* Note: if es_crosscheck_snapshot isn't InvalidSnapshot, we check that
* the row to be updated is visible to that snapshot, and throw a
@@ -2335,6 +2406,7 @@ lreplace:
estate->es_crosscheck_snapshot,
true /* wait for commit */ ,
&context->tmfd, &updateCxt->lockmode,
+ modified_idx_attrs,
&updateCxt->updateIndexes);
return result;
@@ -2557,8 +2629,8 @@ ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
*/
redo_act:
lockedtid = *tupleid;
- result = ExecUpdateAct(context, resultRelInfo, tupleid, oldtuple, slot,
- canSetTag, &updateCxt);
+ result = ExecUpdateAct(context, resultRelInfo, tupleid, oldtuple, oldSlot,
+ slot, canSetTag, &updateCxt);
/*
* If ExecUpdateAct reports that a cross-partition update was done,
@@ -3408,8 +3480,8 @@ lmerge_matched:
Assert(oldtuple == NULL);
result = ExecUpdateAct(context, resultRelInfo, tupleid,
- NULL, newslot, canSetTag,
- &updateCxt);
+ NULL, resultRelInfo->ri_oldTupleSlot,
+ newslot, canSetTag, &updateCxt);
/*
* As in ExecUpdate(), if ExecUpdateAct() reports that a
@@ -4546,7 +4618,7 @@ ExecModifyTable(PlanState *pstate)
* For UPDATE/DELETE/MERGE, fetch the row identity info for the tuple
* to be updated/deleted/merged. For a heap relation, that's a TID;
* otherwise we may have a wholerow junk attr that carries the old
- * tuple in toto. Keep this in step with the part of
+ * tuple in total. Keep this in step with the part of
* ExecInitModifyTable that sets up ri_RowIdAttNo.
*/
if (operation == CMD_UPDATE || operation == CMD_DELETE ||
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 3a4f19e8d58..f2b7fb8f444 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2469,7 +2469,7 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc)
bms_free(relation->rd_keyattr);
bms_free(relation->rd_pkattr);
bms_free(relation->rd_idattr);
- bms_free(relation->rd_hotblockingattr);
+ bms_free(relation->rd_indexedattr);
bms_free(relation->rd_summarizedattr);
if (relation->rd_pubdesc)
pfree(relation->rd_pubdesc);
@@ -5271,8 +5271,8 @@ RelationGetIndexPredicate(Relation relation)
* (beware: even if PK is deferrable!)
* INDEX_ATTR_BITMAP_IDENTITY_KEY Columns in the table's replica identity
* index (empty if FULL)
- * INDEX_ATTR_BITMAP_HOT_BLOCKING Columns that block updates from being HOT
- * INDEX_ATTR_BITMAP_SUMMARIZED Columns included in summarizing indexes
+ * INDEX_ATTR_BITMAP_INDEXED Columns referenced by indexes
+ * INDEX_ATTR_BITMAP_SUMMARIZED Columns only included in summarizing indexes
*
* Attribute numbers are offset by FirstLowInvalidHeapAttributeNumber so that
* we can include system attributes (e.g., OID) in the bitmap representation.
@@ -5295,8 +5295,8 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
Bitmapset *uindexattrs; /* columns in unique indexes */
Bitmapset *pkindexattrs; /* columns in the primary index */
Bitmapset *idindexattrs; /* columns in the replica identity */
- Bitmapset *hotblockingattrs; /* columns with HOT blocking indexes */
- Bitmapset *summarizedattrs; /* columns with summarizing indexes */
+ Bitmapset *indexedattrs; /* columns referenced by indexes */
+ Bitmapset *summarizedattrs; /* columns only in summarizing indexes */
List *indexoidlist;
List *newindexoidlist;
Oid relpkindex;
@@ -5315,8 +5315,8 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
return bms_copy(relation->rd_pkattr);
case INDEX_ATTR_BITMAP_IDENTITY_KEY:
return bms_copy(relation->rd_idattr);
- case INDEX_ATTR_BITMAP_HOT_BLOCKING:
- return bms_copy(relation->rd_hotblockingattr);
+ case INDEX_ATTR_BITMAP_INDEXED:
+ return bms_copy(relation->rd_indexedattr);
case INDEX_ATTR_BITMAP_SUMMARIZED:
return bms_copy(relation->rd_summarizedattr);
default:
@@ -5361,7 +5361,7 @@ restart:
uindexattrs = NULL;
pkindexattrs = NULL;
idindexattrs = NULL;
- hotblockingattrs = NULL;
+ indexedattrs = NULL;
summarizedattrs = NULL;
foreach(l, indexoidlist)
{
@@ -5421,7 +5421,7 @@ restart:
if (indexDesc->rd_indam->amsummarizing)
attrs = &summarizedattrs;
else
- attrs = &hotblockingattrs;
+ attrs = &indexedattrs;
/* Collect simple attribute references */
for (i = 0; i < indexDesc->rd_index->indnatts; i++)
@@ -5430,9 +5430,9 @@ restart:
/*
* Since we have covering indexes with non-key columns, we must
- * handle them accurately here. non-key columns must be added into
- * hotblockingattrs or summarizedattrs, since they are in index,
- * and update shouldn't miss them.
+ * handle them accurately here. Non-key columns must be added into
+ * indexedattrs or summarizedattrs, since they are in index, and
+ * update shouldn't miss them.
*
* Summarizing indexes do not block HOT, but do need to be updated
* when the column value changes, thus require a separate
@@ -5493,12 +5493,20 @@ restart:
bms_free(uindexattrs);
bms_free(pkindexattrs);
bms_free(idindexattrs);
- bms_free(hotblockingattrs);
+ bms_free(indexedattrs);
bms_free(summarizedattrs);
goto restart;
}
+ /*
+ * Record what attributes are only referenced by summarizing indexes. Then
+ * add that into the other indexed attributes to track all referenced
+ * attributes.
+ */
+ summarizedattrs = bms_del_members(summarizedattrs, indexedattrs);
+ indexedattrs = bms_add_members(indexedattrs, summarizedattrs);
+
/* Don't leak the old values of these bitmaps, if any */
relation->rd_attrsvalid = false;
bms_free(relation->rd_keyattr);
@@ -5507,8 +5515,8 @@ restart:
relation->rd_pkattr = NULL;
bms_free(relation->rd_idattr);
relation->rd_idattr = NULL;
- bms_free(relation->rd_hotblockingattr);
- relation->rd_hotblockingattr = NULL;
+ bms_free(relation->rd_indexedattr);
+ relation->rd_indexedattr = NULL;
bms_free(relation->rd_summarizedattr);
relation->rd_summarizedattr = NULL;
@@ -5523,7 +5531,7 @@ restart:
relation->rd_keyattr = bms_copy(uindexattrs);
relation->rd_pkattr = bms_copy(pkindexattrs);
relation->rd_idattr = bms_copy(idindexattrs);
- relation->rd_hotblockingattr = bms_copy(hotblockingattrs);
+ relation->rd_indexedattr = bms_copy(indexedattrs);
relation->rd_summarizedattr = bms_copy(summarizedattrs);
relation->rd_attrsvalid = true;
MemoryContextSwitchTo(oldcxt);
@@ -5537,8 +5545,8 @@ restart:
return pkindexattrs;
case INDEX_ATTR_BITMAP_IDENTITY_KEY:
return idindexattrs;
- case INDEX_ATTR_BITMAP_HOT_BLOCKING:
- return hotblockingattrs;
+ case INDEX_ATTR_BITMAP_INDEXED:
+ return indexedattrs;
case INDEX_ATTR_BITMAP_SUMMARIZED:
return summarizedattrs;
default:
diff --git a/src/include/access/heapam.h b/src/include/access/heapam.h
index 2fdc50b865b..088097a9188 100644
--- a/src/include/access/heapam.h
+++ b/src/include/access/heapam.h
@@ -390,10 +390,9 @@ extern TM_Result heap_delete(Relation relation, const ItemPointerData *tid,
extern void heap_finish_speculative(Relation relation, const ItemPointerData *tid);
extern void heap_abort_speculative(Relation relation, const ItemPointerData *tid);
extern TM_Result heap_update(Relation relation, const ItemPointerData *otid,
- HeapTuple newtup,
- CommandId cid, Snapshot crosscheck, bool wait,
- TM_FailureData *tmfd, LockTupleMode *lockmode,
- TU_UpdateIndexes *update_indexes);
+ HeapTuple newtup, CommandId cid, Snapshot crosscheck, bool wait,
+ TM_FailureData *tmfd, const LockTupleMode lockmode,
+ const Bitmapset *modified_idx_attrs, const bool hot_allowed);
extern TM_Result heap_lock_tuple(Relation relation, HeapTuple tuple,
CommandId cid, LockTupleMode mode, LockWaitPolicy wait_policy,
bool follow_updates,
@@ -456,6 +455,12 @@ extern void log_heap_prune_and_freeze(Relation relation, Buffer buffer,
OffsetNumber *dead, int ndead,
OffsetNumber *unused, int nunused);
+/* in heap/heapam.c */
+extern bool HeapUpdateHotAllowable(Relation relation, const Bitmapset *modified_idx_attrs,
+ bool *summarized_only);
+extern LockTupleMode HeapUpdateDetermineLockmode(Relation relation,
+ const Bitmapset *modified_idx_attrs);
+
/* in heap/vacuumlazy.c */
extern void heap_vacuum_rel(Relation rel,
const VacuumParams params, BufferAccessStrategy bstrategy);
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h
index 06084752245..8ec20dcfc11 100644
--- a/src/include/access/tableam.h
+++ b/src/include/access/tableam.h
@@ -549,6 +549,7 @@ typedef struct TableAmRoutine
bool wait,
TM_FailureData *tmfd,
LockTupleMode *lockmode,
+ const Bitmapset *modified_idx_attrs,
TU_UpdateIndexes *update_indexes);
/* see table_tuple_lock() for reference about parameters */
@@ -1523,12 +1524,12 @@ static inline TM_Result
table_tuple_update(Relation rel, ItemPointer otid, TupleTableSlot *slot,
CommandId cid, Snapshot snapshot, Snapshot crosscheck,
bool wait, TM_FailureData *tmfd, LockTupleMode *lockmode,
- TU_UpdateIndexes *update_indexes)
+ const Bitmapset *modified_idx_attrs, TU_UpdateIndexes *update_indexes)
{
return rel->rd_tableam->tuple_update(rel, otid, slot,
cid, snapshot, crosscheck,
- wait, tmfd,
- lockmode, update_indexes);
+ wait, tmfd, lockmode,
+ modified_idx_attrs, update_indexes);
}
/*
@@ -2009,6 +2010,7 @@ extern void simple_table_tuple_delete(Relation rel, ItemPointer tid,
Snapshot snapshot);
extern void simple_table_tuple_update(Relation rel, ItemPointer otid,
TupleTableSlot *slot, Snapshot snapshot,
+ const Bitmapset *modified_idx_attrs,
TU_UpdateIndexes *update_indexes);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 064df01811e..713ed35d8cf 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -18,6 +18,7 @@
#include "datatype/timestamp.h"
#include "executor/execdesc.h"
#include "fmgr.h"
+#include "nodes/execnodes.h"
#include "nodes/lockoptions.h"
#include "nodes/parsenodes.h"
#include "utils/memutils.h"
@@ -610,6 +611,10 @@ extern TupleDesc ExecCleanTypeFromTL(List *targetList);
extern TupleDesc ExecTypeFromExprList(List *exprList);
extern void ExecTypeSetColNames(TupleDesc typeInfo, List *namesList);
extern void UpdateChangedParamSet(PlanState *node, Bitmapset *newchg);
+extern Bitmapset *ExecCompareSlotAttrs(Bitmapset *attrs,
+ TupleDesc tupdesc,
+ TupleTableSlot *old_tts,
+ TupleTableSlot *new_tts);
typedef struct TupOutputState
{
@@ -807,5 +812,9 @@ extern ResultRelInfo *ExecLookupResultRelByOid(ModifyTableState *node,
Oid resultoid,
bool missing_ok,
bool update_cache);
+extern Bitmapset *ExecUpdateModifiedIdxAttrs(ResultRelInfo *relinfo,
+ EState *estate,
+ TupleTableSlot *old_tts,
+ TupleTableSlot *new_tts);
#endif /* EXECUTOR_H */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 236830f6b93..11460e134f0 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -162,7 +162,7 @@ typedef struct RelationData
Bitmapset *rd_keyattr; /* cols that can be ref'd by foreign keys */
Bitmapset *rd_pkattr; /* cols included in primary key */
Bitmapset *rd_idattr; /* included in replica identity index */
- Bitmapset *rd_hotblockingattr; /* cols blocking HOT update */
+ Bitmapset *rd_indexedattr; /* all cols referenced by indexes */
Bitmapset *rd_summarizedattr; /* cols indexed by summarizing indexes */
PublicationDesc *rd_pubdesc; /* publication descriptor, or NULL */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2700224939a..d4db82496b4 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -69,7 +69,7 @@ typedef enum IndexAttrBitmapKind
INDEX_ATTR_BITMAP_KEY,
INDEX_ATTR_BITMAP_PRIMARY_KEY,
INDEX_ATTR_BITMAP_IDENTITY_KEY,
- INDEX_ATTR_BITMAP_HOT_BLOCKING,
+ INDEX_ATTR_BITMAP_INDEXED,
INDEX_ATTR_BITMAP_SUMMARIZED,
} IndexAttrBitmapKind;
diff --git a/src/test/modules/injection_points/expected/syscache-update-pruned.out b/src/test/modules/injection_points/expected/syscache-update-pruned.out
index a6a4e8db996..07ef67a1eb4 100644
--- a/src/test/modules/injection_points/expected/syscache-update-pruned.out
+++ b/src/test/modules/injection_points/expected/syscache-update-pruned.out
@@ -16,8 +16,8 @@ step wakeinval4:
step at2: <... completed>
step wakeinval4: <... completed>
step wakegrant4:
- SELECT FROM injection_points_detach('heap_update-before-pin');
- SELECT FROM injection_points_wakeup('heap_update-before-pin');
+ SELECT FROM injection_points_detach('simple_heap_update-before-pin');
+ SELECT FROM injection_points_wakeup('simple_heap_update-before-pin');
<waiting ...>
step grant1: <... completed>
ERROR: tuple concurrently deleted
@@ -42,8 +42,8 @@ step mkrels4:
SELECT FROM vactest.mkrels('intruder', 1, 100); -- repopulate LP_UNUSED
step wakegrant4:
- SELECT FROM injection_points_detach('heap_update-before-pin');
- SELECT FROM injection_points_wakeup('heap_update-before-pin');
+ SELECT FROM injection_points_detach('simple_heap_update-before-pin');
+ SELECT FROM injection_points_wakeup('simple_heap_update-before-pin');
<waiting ...>
step grant1: <... completed>
ERROR: duplicate key value violates unique constraint "pg_class_oid_index"
@@ -71,8 +71,8 @@ step at2: <... completed>
step wakeinval4: <... completed>
step at4: ALTER TABLE vactest.child50 INHERIT vactest.orig50;
step wakegrant4:
- SELECT FROM injection_points_detach('heap_update-before-pin');
- SELECT FROM injection_points_wakeup('heap_update-before-pin');
+ SELECT FROM injection_points_detach('simple_heap_update-before-pin');
+ SELECT FROM injection_points_wakeup('simple_heap_update-before-pin');
<waiting ...>
step grant1: <... completed>
step wakegrant4: <... completed>
diff --git a/src/test/modules/injection_points/specs/syscache-update-pruned.spec b/src/test/modules/injection_points/specs/syscache-update-pruned.spec
index e3a4295bd12..fef9ac895a1 100644
--- a/src/test/modules/injection_points/specs/syscache-update-pruned.spec
+++ b/src/test/modules/injection_points/specs/syscache-update-pruned.spec
@@ -103,7 +103,7 @@ session s1
setup {
SET debug_discard_caches = 0;
SELECT FROM injection_points_set_local();
- SELECT FROM injection_points_attach('heap_update-before-pin', 'wait');
+ SELECT FROM injection_points_attach('simple_heap_update-before-pin', 'wait');
}
step cachefill1 { SELECT FROM vactest.reloid_catcache_set('vactest.orig50'); }
step grant1 { GRANT SELECT ON vactest.orig50 TO PUBLIC; }
@@ -140,8 +140,8 @@ step mkrels4 {
SELECT FROM vactest.mkrels('intruder', 1, 100); -- repopulate LP_UNUSED
}
step wakegrant4 {
- SELECT FROM injection_points_detach('heap_update-before-pin');
- SELECT FROM injection_points_wakeup('heap_update-before-pin');
+ SELECT FROM injection_points_detach('simple_heap_update-before-pin');
+ SELECT FROM injection_points_wakeup('simple_heap_update-before-pin');
}
step at4 { ALTER TABLE vactest.child50 INHERIT vactest.orig50; }
step wakeinval4 {
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 6dab60c937b..7ebb7890d96 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -287,7 +287,7 @@ DETAIL: Column "b" is a generated column.
INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
ERROR: cannot insert a non-DEFAULT value into column "b"
DETAIL: Column "b" is a generated column.
-SELECT * FROM gtest1v;
+SELECT * FROM gtest1v ORDER BY a;
a | b
---+----
3 | 6
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 98dee63b50a..ef98fd0cccf 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -959,16 +959,24 @@ NOTICE: main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
NOTICE: main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
UPDATE 0
-- Delete from view using trigger
-DELETE FROM main_view WHERE a IN (20,21);
+DELETE FROM main_view WHERE a = 20 AND b = 31;
NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
-NOTICE: OLD: (21,10)
-NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
NOTICE: OLD: (20,31)
+NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
+DELETE 1
+DELETE FROM main_view WHERE a = 21 AND b = 10;
+NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
+NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
+NOTICE: OLD: (21,10)
+NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
+DELETE 1
+DELETE FROM main_view WHERE a = 21 AND b = 32;
+NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
NOTICE: OLD: (21,32)
NOTICE: main_view AFTER DELETE STATEMENT (after_view_del_stmt)
-DELETE 3
+DELETE 1
DELETE FROM main_view WHERE a = 31 RETURNING a, b;
NOTICE: main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
NOTICE: main_view INSTEAD OF DELETE ROW (instead_of_del)
diff --git a/src/test/regress/expected/tsearch.out b/src/test/regress/expected/tsearch.out
index 9287c440709..c604ec35fa5 100644
--- a/src/test/regress/expected/tsearch.out
+++ b/src/test/regress/expected/tsearch.out
@@ -2483,7 +2483,8 @@ SELECT to_tsquery('SKIES & My | booKs');
'sky' | 'book'
(1 row)
---trigger
+-- tsvector_update_trigger() uses heap_modify_tuple() to set column 'a'
+-- without going through the executor's SET-clause tracking.
CREATE TRIGGER tsvectorupdate
BEFORE UPDATE OR INSERT ON test_tsvector
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 9cea538b8e8..4877a1ddce9 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -372,15 +372,15 @@ INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
ERROR: multiple assignments to same column "a"
UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY a;
a | b
----+--------
+ -3 | Row 3
-2 | Row -2
-1 | Row -1
0 | Row 0
1 | Row 1
2 | Row 2
- -3 | Row 3
(6 rows)
DELETE FROM rw_view16 WHERE a=-3; -- should be OK
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index e750866d2d8..877152d6d69 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -127,7 +127,7 @@ ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
-SELECT * FROM gtest1v;
+SELECT * FROM gtest1v ORDER BY a;
DELETE FROM gtest1v WHERE a >= 5;
DROP VIEW gtest1v;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index ea39817ee3d..6ceb61608ae 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -660,7 +660,9 @@ UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
UPDATE main_view SET b = 0 WHERE false;
-- Delete from view using trigger
-DELETE FROM main_view WHERE a IN (20,21);
+DELETE FROM main_view WHERE a = 20 AND b = 31;
+DELETE FROM main_view WHERE a = 21 AND b = 10;
+DELETE FROM main_view WHERE a = 21 AND b = 32;
DELETE FROM main_view WHERE a = 31 RETURNING a, b;
\set QUIET true
diff --git a/src/test/regress/sql/tsearch.sql b/src/test/regress/sql/tsearch.sql
index dc74aa0c889..77ac5fd3c5a 100644
--- a/src/test/regress/sql/tsearch.sql
+++ b/src/test/regress/sql/tsearch.sql
@@ -752,7 +752,8 @@ SELECT to_tsvector('SKIES My booKs');
SELECT plainto_tsquery('SKIES My booKs');
SELECT to_tsquery('SKIES & My | booKs');
---trigger
+-- tsvector_update_trigger() uses heap_modify_tuple() to set column 'a'
+-- without going through the executor's SET-clause tracking.
CREATE TRIGGER tsvectorupdate
BEFORE UPDATE OR INSERT ON test_tsvector
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index 1635adde2d4..160e7799715 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -125,7 +125,7 @@ INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail
INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
-SELECT * FROM base_tbl;
+SELECT * FROM base_tbl ORDER BY a;
DELETE FROM rw_view16 WHERE a=-3; -- should be OK
-- Read-only views
INSERT INTO ro_view17 VALUES (3, 'ROW 3');
--
2.51.2
view thread (44+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Expanding HOT updates for expression and partial indexes
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox