Thread: Add more docs for pg_surgery?
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
```
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?
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?
Regards,
Zhang Mingli
On Mon, Sep 26, 2022 at 9:29 PM Zhang Mingli <zmlpostgres@gmail.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
bydesign and using them may corrupt (or further corrupt) your database. For example, these functions can easily be used
tomake a table inconsistent with its own indexes, to cause UNIQUE or FOREIGN KEY constraint violations, or even to make
tuplesvisible which, when read, will cause a database server crash. They should be used with great caution and only as
alast 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.
Regards,
Zhang Mingli
On Sep 27, 2022, 00:47 +0800, Ashutosh Sharma <ashu.coek88@gmail.com>, wrote:
And further it looks like you are doing an
experiment on undamaged relation which is not recommended as
documented.
Yeah.
If the relation would have been damaged, you probably may
not be able to access it.
That make some sense.
--
With Regards,
Ashutosh Sharma.