Adding information about involved lock levels of pg_squeeze execution

[CC-Hsu]

Summary

Hi, Team,

I read through the new extension documentation page,

but I find no detailed lock level information during different execution phase of pg_squeeze.

It would be great if the documentation can include detailed lock modes involved, so that users can clearly compared with the documentation page.
https://www.postgresql.org/docs/current/explicit-locking.html

Best Regards.

Where would you like to see this added?

https://github.com/EnterpriseDB/docs/blob/main/advocacy_docs/pg_extensions/pg_squeeze/using.mdx

Rationale

No response

[dwicinas]

@CC-Hsu Can you clarify what lock information you would like to see added? I assume you're talking about the "Controlling impact on other backends" section in the Using pg_squeeze documentation. The "Controlling impact" section mentions the need for an exclusive lock. Do you know what type of lock? Is it ACCESS EXCLUSIVE? Or maybe you want other information about locks that may be required while using pg_squeeze. If so, I would need more information. Thanks.

[CC-Hsu]

Hi, dwicinas,

I hope to know what exactly operations are allowed on a table during pg_squeeze is running on it.

For example, which operations will block pg_squeeze or what kind of operations might be blocked during pg_squeeze execution.

I try to search in the main source code pg_squeeze.c and guess that pg_squeeze might involve AccessShare, RowExclusive, and the short final stage AccessExclusive.

It would be great if such information can be provided as table like below in the section you mentioned Controlling impact on other backends (I am not sure the correctness of the below table; this is just an example).

Phase	Lock Acquired	Period
Phase1	AccessShare	Long
Phase2	RowExclusive	?
Final	AccessExclusive	Short

[dwicinas]

@shruthikc-gowda Hello. @nidhibhammar suggests you might be able to answer CC's questions (see above). Or, if you can't answer them, perhaps you can suggest who can answer this type of query. Thanks.

[shruthikc-gowda]

@dwicinas, I will go through the pg_squeeze code to collect the required information. I will post the information here once it is available.

[shruthikc-gowda]

Hi @dwicinas and @CC-Hsu, please find the requested information

To perform a table squeeze, pg_squeeze will:
1. Create a transient table and copy the content of the source table using the snapshot
2. Build indexes on the transient table
3. Decode and apply the concurrent changes that occurred on source table while the initial load was in progress
4. Apply the concurrent changes that might have taken place while waiting for the lock and swap the storage of the transient and the source table
5. Drop the transient table

pg_squeeze will hold an ACCESS SHARE lock on the source table during step 1 and step 2 and ACCESS EXCLUSIVE lock for a short period during the final processing and swap (step 4). The table being squeezed will be available for both read and write operations by other transactions most of the time.

@nidhibhammar, there is one update needed for "Using pg_squeeze" section

Registering a table for regular processing
First, ensure that your table has either a primary key or a unique constraint.

The above sentence should be updated to
First, ensure that your table has either a primary key or a replica identity

Let me know if the above change can be done part of this issue itself?

[CC-Hsu]

Hi, shruthikc-gowda,

Thanks a lot for such clear explanation. 😄

Both pieces of texts are very important for introducing this tool to EDB customers.

Thanks again.

[dwicinas]

@CC-Hsu and @shruthikc-gowda, I've incorporated the new information into the documentation for pg_squeeze. See this topic: https://deploy-preview-5464--edb-docs-staging.netlify.app/docs/pg_extensions/pg_squeeze/using/. There I added an introductory section describing the locks and revised the first sentence in the "Registering" section. You can also look at PR 5464.

[CC-Hsu]

Hi, dwicinas,

Thanks a lot for your work.

The information is clear to me to understand.

Best Regards.