Re: POC: Lock updated tuples in tuple_update() and tuple_delete() - Mailing list pgsql-hackers

From Aleksander Alekseev
Subject Re: POC: Lock updated tuples in tuple_update() and tuple_delete()
Date
Msg-id CAJ7c6TP3FqS6f_8eiWBBNcf6wDUM1QtbwQnTxqCXwS_nxhM3nw@mail.gmail.com
Whole thread Raw
In response to Re: POC: Lock updated tuples in tuple_update() and tuple_delete()  (Aleksander Alekseev <aleksander@timescale.com>)
Responses Re: POC: Lock updated tuples in tuple_update() and tuple_delete()
List pgsql-hackers
Hi Alexander,

> I'm going to need more time to meditate on the proposed changes and to figure out the performance impact.

OK, turned out this patch is slightly more complicated than I
initially thought, but I think I managed to get some vague
understanding of what's going on.

I tried to reproduce the case with concurrently updated tuples you
described on the current `master` branch. I created a new table:

```
CREATE TABLE phonebook(
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" NAME NOT NULL,
  "phone" INT NOT NULL);

INSERT INTO phonebook ("name", "phone")
VALUES ('Alice', 123), ('Bob', 456), ('Charlie', 789);
```

Then I opened two sessions and attached them with LLDB. I did:

```
(lldb) b heapam_tuple_update
(lldb) c
```

... in both cases because I wanted to see two calls (steps 2 and 4) to
heapam_tuple_update() and check the return values.

Then I did:

```
session1 =# BEGIN;
session2 =# BEGIN;
session1 =# UPDATE phonebook SET name = 'Alex' WHERE name = 'Alice';
```

This update succeeds and I see heapam_tuple_update() returning TM_Ok.

```
session2 =# UPDATE phonebook SET name = 'Alfred' WHERE name = 'Alice';
```

This update hangs on a lock.

```
session1 =# COMMIT;
```

Now session2 unfreezes and returns 'UPDATE 0'. table_tuple_update()
was called once and returned TM_Updated. Also session2 sees an updated
tuple now. So apparently the visibility check (step 3) didn't pass.

At this point I'm slightly confused. I don't see where a performance
improvement is expected, considering that session2 gets blocked until
session1 commits.

Could you please walk me through here? Am I using the right test case
or maybe you had another one in mind? Which steps do you consider
expensive and expect to be mitigated by the patch?

-- 
Best regards,
Aleksander Alekseev



pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: explain analyze rows=%.0f
Next
From: Peter Eisentraut
Date:
Subject: Re: [PATCH] Log details for client certificate failures