Most applications probably never issue an UPDATE that explicitly assigns a column back to itself.
For example:
UPDATE t
SET v = v,
ver = 0
WHERE k = 3;
The v = v assignment is normally redundant. The application is effectively saying, “leave this column unchanged.”
SET expressions when the optimized single-row UPDATE path is used. UPDATE. A quick check of pg_stat_statements can help determine whether the pattern exists in your workload.
The Pattern
Consider this simple table:
CREATE TABLE t (
k INT PRIMARY KEY,
v TEXT,
ver BIGINT
);
INSERT INTO t VALUES (3, 'a', 0);
Now execute:
UPDATE t
SET v = v,
ver = 0
WHERE k = 3;
The important part is:
v = v
This is sometimes called an identity assignment or self-assignment because the column is being assigned its existing value.
Normally, the easiest way to express “don’t change v” is simply not to include it:
UPDATE t
SET ver = 0
WHERE k = 3;
Why This Matters in YugabyteDB
YugabyteDB can optimize certain point UPDATE operations by using a single-row execution path that does not need to fetch the complete existing tuple first.
You can sometimes recognize this path in EXPLAIN by a plan similar to:
Update
-> Result
For an expression such as:
ver = 0
that optimization makes sense because the new value is already known.
But:
v = v
depends on the existing value of v.
That distinction exposes an edge case where the identity SET expression can be evaluated even though the existing column value was not fetched.
For example:
CREATE TABLE t (
k INT PRIMARY KEY,
v TEXT,
ver BIGINT
);
INSERT INTO t VALUES (3, 'a', 0);
UPDATE t
SET v = v,
ver = 0
WHERE k = 3;
SELECT * FROM t;
UPDATE reports success:
UPDATE 1
but the result can be:
.k | v | ver
---+---+-----
3 | | 0
(1 row)
Even though the statement specified:
v = v
the nullable column v becomes NULL.
UPDATE statements. It requires the much more specific pattern of explicitly assigning a column back to itself, such as SET v = v, while also performing another assignment. This pattern is likely uncommon in hand-written SQL, although ORMs or dynamically generated SQL may occasionally produce it.
A Related Fix in YugabyteDB 2025.2.6.0
There is an important related YugabyteDB issue that helps explain this behavior.
SET case involving a NOT NULL column. YugabyteDB 2025.2.6.0 includes a fix that avoids the optimized single-row path for the documented NOT NULL case.Issue #32716 uses an UPDATE similar to:
UPDATE t_foo
SET v1 = 2,
v2 = v2
WHERE k = 1;
where v2 is defined as NOT NULL.
On the affected release, the existing value of v2 was not available on the optimized single-row path. The operation therefore encountered a spurious NOT NULL constraint violation.
The issue shows the query taking the Result path:
Update on t_foo
-> Result t_foo
GitHub classifies #32716 as a YSQL bug and shows the self-assignment example producing a NULL constraint violation on the single-shard Result plan.
The official YugabyteDB 2025.2.6.0 release notes describe the fix as preventing spurious NOT NULL errors in single-row identity SET updates by avoiding the single-row path for NOT NULL columns.
Testing the Fix in 2025.2.6.0
Let’s verify the behavior directly.
First, confirm the version:
SELECT split_part(version(), '-', 3) yb_version;
Result:
.yb_version
------------
2025.2.6.0
(1 row)
Now create a table where the self-assigned column is NOT NULL:
CREATE TABLE t_test (
k INT PRIMARY KEY,
v TEXT NOT NULL,
ver BIGINT
);
INSERT INTO t_test VALUES (3, 'a', 0);
Check the plan:
EXPLAIN
UPDATE t_test
SET v = v,
ver = 0
WHERE k = 3;
On YugabyteDB 2025.2.6.0:
. QUERY PLAN
------------------------------------------------------------------------------------
Update on t_test (cost=21.00..21.11 rows=0 width=0)
-> Index Scan using t_test_pkey on t_test (cost=21.00..21.11 rows=1 width=140)
Index Cond: (k = 3)
(3 rows)
Notice the important difference:
Update
-> Index Scan
The existing row is fetched.
Now execute the UPDATE:
UPDATE t_test
SET v = v,
ver = 0
WHERE k = 3;
SELECT * FROM t_test;
Result:
.k | v | ver
---+---+-----
3 | a | 0
(1 row)
The existing value of v is preserved correctly.
NOT NULL identity-SET case documented in GitHub issue #32716 uses an Index Scan in YugabyteDB 2025.2.6.0 and preserves the existing column value correctly.YugabyteDB 2025.2.6.0 was released September 4, 2026, and the #32716 change is listed among its YSQL improvements.
What About Nullable Columns?
Now repeat the same test, but allow v to contain NULL:
CREATE TABLE t_nullable (
k INT PRIMARY KEY,
v TEXT,
ver BIGINT
);
INSERT INTO t_nullable VALUES (3, 'a', 0);
Check the plan:
EXPLAIN
UPDATE t_nullable
SET v = v,
ver = 0
WHERE k = 3;
On the same YugabyteDB 2025.2.6.0 release:
. QUERY PLAN
---------------------------------------------------------------
Update on t_nullable (cost=21.00..21.11 rows=0 width=0)
-> Result t_nullable (cost=21.00..21.11 rows=1 width=140)
(2 rows)
This time the optimized path is still used:
Update
-> Result
Execute the UPDATE:
UPDATE t_nullable
SET v = v,
ver = 0
WHERE k = 3;
SELECT * FROM t_nullable;
Result:
.k | v | ver
---+---+-----
3 | | 0
(1 row)
The UPDATE succeeds, but the nullable v column becomes NULL.
This demonstrates an important distinction: the fix in #32716 addresses the documented NOT NULL case, while the nullable variation can still use the optimized single-row path in 2025.2.6.0.
NOT NULL case. Testing shows that a nullable self-assigned column can still use the optimized Update → Result path. This is why checking pg_stat_statements for uncommon patterns such as SET column = column can be useful.Check Your Workload with pg_stat_statements
Rather than guessing whether an application generates this pattern, you can search statements that have actually executed.
The following query examines pg_stat_statements for UPDATE statements whose SET clause appears to contain:
column = column
WITH updates AS (
SELECT
s.userid,
s.dbid,
s.queryid,
s.calls,
s.query,
(
regexp_match(
regexp_replace(s.query, '[[:space:]]+', ' ', 'g'),
$re$\mSET\M(.*?)(\mWHERE\M|\mRETURNING\M|$)$re$,
'i'
)
)[1] AS set_clause
FROM pg_stat_statements s
WHERE s.query ~* '^[[:space:]]*UPDATE[[:space:]]'
),
matches AS (
SELECT
u.*,
regexp_match(
u.set_clause,
$re$\m([a-z_][a-z0-9_$]*)\M[[:space:]]*=[[:space:]]*(?:\m[a-z_][a-z0-9_$]*\M[[:space:]]*\.[[:space:]]*)?\1\M[[:space:]]*(?:,|$)$re$,
'i'
) AS self_assignment
FROM updates u
WHERE u.set_clause IS NOT NULL
)
SELECT
d.datname AS database_name,
pg_get_userbyid(m.userid) AS user_name,
m.queryid,
m.calls,
m.self_assignment[1] AS self_assigned_column,
m.set_clause,
m.query
FROM matches m
JOIN pg_database d
ON d.oid = m.dbid
WHERE m.self_assignment IS NOT NULL
ORDER BY m.calls DESC;
For example, after running:
UPDATE t
SET v = v,
ver = 0
WHERE k = 3;
the query returns:
.database_name | user_name | queryid | calls | self_assigned_column | set_clause | query
---------------+-----------+---------------------+-------+----------------------+-------------------+-------------------------------------------
yugabyte | yugabyte | 2088391622060455835 | 1 | v | v = v, ver = $1 | update t set v = v, ver = $1 where k = $2
(1 row)
Notice that pg_stat_statements normalizes literal values.
For example:
ver = 0
appears as:
ver = $1
while the important identity expression remains visible:
v = v
UPDATE statements, CTEs, or dynamically generated SQL may require additional review.
YugabyteDB documents pg_stat_statements as tracking planning and execution statistics for SQL statements executed by a server, including the normalized query text, query ID, user, database, and call count.
What Should I Do If I Find One?
The simplest workaround is to determine whether the self-assignment is actually necessary.
Instead of:
UPDATE customer
SET status = status,
version = 42
WHERE customer_id = 100;
prefer:
UPDATE customer
SET version = 42
WHERE customer_id = 100;
There is no need to assign status back to itself just to leave it unchanged.
If an ORM or application framework generates the SQL automatically, check whether it supports dirty updates, dynamic updates, or another option that includes only columns that actually need to be assigned.
SET list. Avoiding unnecessary expressions such as SET v = v is both the simplest workaround and generally clearer SQL.Check the Relevant TServers
One additional consideration when using pg_stat_statements in YugabyteDB is where application connections are being handled.
If application connections are distributed across multiple TServers, run the check against the relevant TServers to improve coverage rather than assuming the statistics observed through one server represent every application connection.
pg_stat_statements check against the relevant TServers so you have better coverage of the SQL being executed by the application.
Final Takeaway
Self-assignments such as:
SET column_name = column_name
are probably uncommon in most hand-written SQL, but ORMs and dynamically generated SQL can occasionally produce them.
GitHub issue #32716 documented one variation of this behavior involving NOT NULL columns, and YugabyteDB 2025.2.6.0 fixes that documented case.
Testing on 2025.2.6.0 also shows that the nullable-column variation can still take the optimized:
Update
-> Result
path.
The practical first step is therefore simple:
- Does my workload generate self-assignment
UPDATEstatements at all?
If the pg_stat_statements query returns no rows, great… there may be nothing further to investigate.
If it does return rows, review those statements and, where practical, remove unnecessary self-assignments such as:
v = v
and simply leave unchanged columns out of the SET list.
Have Fun!
It’s a little early for Christmas decorations, but my aunt gave me her vintage holiday set of Charlie Brown and the Peanuts gang, so of course I had to put them up on the mantel! 🎄😊
Now I just need to find a Sally doll to complete the set.
Sally is Charlie Brown’s little sister, and she has one of my favorite lines from A Charlie Brown Christmas. When Charlie Brown asks what she wants Santa to bring her, she basically says he can just send money… “tens and twenties.” 😂
And then the classic:
“All I want is what I have coming to me. All I want is my fair share.”
Gotta love Sally! 😄