Re: Add more docs for pg_surgery?

On Mon, Sep 26, 2022 at 9:29 PM Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
>
> Hi, hackers
>
> heap_force_kill/heap_force_freeze doesn’t consider other transactions that are using the same tuples even with tuple-locks.
> The functions may break transaction semantic, ex:
>
> session1
> ```
> create table htab(id int);
> insert into htab values (100), (200), (300), (400), (500);
> ```
>
> session2
> ```
> begin isolation level repeatable read;
> select * from htab for share;
> id
> -----
> 100
> 200
> 300
> 400
> 500
> (5 rows)
> ```
>
> session1
> ```
> select heap_force_kill('htab'::regclass, ARRAY['(0, 1)']::tid[]);
> heap_force_kill
> -----------------
>
> (1 row)
> ```
>
> session2
> ```
> select * from htab for share;
> id
> -----
> 200
> 300
> 400
> 500
> (4 rows)
> ```
>
> session2 should get the same results as it's repeatable read isolation level.
>
> By reading the doc:
> ```
> The pg_surgery module provides various functions to perform surgery on a damaged relation. These functions are unsafe by design and using them may corrupt (or further corrupt) your database. For example, these functions can easily be used to make a table inconsistent with its own indexes, to cause UNIQUE or FOREIGN KEY constraint violations, or even to make tuples visible which, when read, will cause a database server crash. They should be used with great caution and only as a last resort.
>
> ```
> I know they are powerful tools, but also a little surprise with the above example.
>
> Should we add more docs to tell the users that the tool will change the tuples anyway even there are tuple-locks on them?
>

As the name suggests and as documented, heap_force_kill will "force
kill" the tuple, regardless of whether it is visible to another
transaction or not. And further it looks like you are doing an
experiment on undamaged relation which is not recommended as
documented. If the relation would have been damaged, you probably may
not be able to access it.

--
With Regards,
Ashutosh Sharma.