Fix update triggers issue caused by decompression

[antekresic]

Update triggers were broken due to snapshot manipulation and usage of dynamic snapshots for scanning tuples. Using a static snapshot guarantees we cannot see tuples that would be invisible during an update trigger.

Fixes #6858

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 86.95652% with 3 lines in your changes are missing coverage. Please review.

Project coverage is 81.01%. Comparing base (59f50f2) to head (4bfbedb). Report is 182 commits behind head on main.

:exclamation: Current head 4bfbedb differs from pull request most recent head 156e1cc

Please upload reports for the commit 156e1cc to get more accurate results.

Files	Patch %	Lines
tsl/src/compression/compression.c	84.21%	2 Missing and 1 partial :warning:

Additional details and impacted files

@@            Coverage Diff             @@
##             main    #6903      +/-   ##
==========================================
+ Coverage   80.06%   81.01%   +0.94%     
==========================================
  Files         190      199       +9     
  Lines       37181    37317     +136     
  Branches     9450     9740     +290     
==========================================
+ Hits        29770    30231     +461     
- Misses       2997     3186     +189     
+ Partials     4414     3900     -514     

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[akuzm]

@antekresic can you explain more about this issue? What row was it trying to lock that was invisible?

[timescale-automation]

Automated backport to 2.15.x not done: cherry-pick failed.

Git status

HEAD detached at origin/2.15.x
You are currently cherry-picking commit cec6b37fb.
  (fix conflicts and run "git cherry-pick --continue")
  (use "git cherry-pick --skip" to skip this patch)
  (use "git cherry-pick --abort" to cancel the cherry-pick operation)

Changes to be committed:
	new file:   .unreleased/fix_6858
	modified:   src/nodes/hypertable_modify.c
	modified:   tsl/src/compression/compression.c
	modified:   tsl/test/isolation/expected/compression_dml_iso.out
	modified:   tsl/test/isolation/specs/compression_dml_iso.spec
	modified:   tsl/test/sql/compression_update_delete.sql

Unmerged paths:
  (use "git add <file>..." to mark resolution)
	both modified:   tsl/test/expected/compression_update_delete.out

Job log