Обсуждение: Hash table scans outside transactions

Hi,
Hash table scans (seq_scan_table/level) are cleaned up at the end of a
transaction in AtEOXact_HashTables(). If a hash seq scan continues
beyond transaction end it will meet "ERROR: no hash_seq_search scan
for hash table" in deregister_seq_scan(). That seems like a limiting
the hash table usage.

Our use case is
1. Add/update/remove entries in hash table
2. Scan the existing entries and perform one transaction per entry
3. Close scan

repeat above steps in an infinite loop. Note that we do not
add/modify/delete entries in step 2. We can't use linked lists since
the entries need to be updated or deleted using hash keys. Because the
hash seq scan is cleaned up at the end of the transaction, we
encounter error in the 3rd step. I don't see that the actual hash
table scan depends upon the seq_scan_table/level[] which is cleaned up
at the end of the transaction.

I have following questions
1. Is there a way to avoid cleaning up seq_scan_table/level() when the
transaction ends?
2. Is there a way that we can use hash table implementation in
PostgreSQL code for our purpose?


-- 
Best Wishes,
Ashutosh Bapat

Bumping it to attract some attention.

On Tue, Mar 21, 2023 at 12:51 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> Hi,
> Hash table scans (seq_scan_table/level) are cleaned up at the end of a
> transaction in AtEOXact_HashTables(). If a hash seq scan continues
> beyond transaction end it will meet "ERROR: no hash_seq_search scan
> for hash table" in deregister_seq_scan(). That seems like a limiting
> the hash table usage.
>
> Our use case is
> 1. Add/update/remove entries in hash table
> 2. Scan the existing entries and perform one transaction per entry
> 3. Close scan
>
> repeat above steps in an infinite loop. Note that we do not
> add/modify/delete entries in step 2. We can't use linked lists since
> the entries need to be updated or deleted using hash keys. Because the
> hash seq scan is cleaned up at the end of the transaction, we
> encounter error in the 3rd step. I don't see that the actual hash
> table scan depends upon the seq_scan_table/level[] which is cleaned up
> at the end of the transaction.
>
> I have following questions
> 1. Is there a way to avoid cleaning up seq_scan_table/level() when the
> transaction ends?
> 2. Is there a way that we can use hash table implementation in
> PostgreSQL code for our purpose?
>
>
> --
> Best Wishes,
> Ashutosh Bapat



--
Best Wishes,
Ashutosh Bapat