Re: Timing out A Blocker Based on Time or Count of Waiters

2024-03-22 Thread Fred Habash
#x27;alter table ... alter column ...' would cause all DML and SELECT statements to wait/block. Hope this answers your question. Thanks for your interest. On Fri, Mar 22, 2024 at 12:32 PM Adrian Klaver wrote: > On 3/22/24 09:25, Fred Habash wrote: > > Facing an issue where som

Timing out A Blocker Based on Time or Count of Waiters

2024-03-22 Thread Fred Habash
Facing an issue where sometimes humans login to a database and run DDL statements causing a long locking tree of over 1000 waiters. As a workaround, we asked developers to always start their DDL sessions with 'SET lock_timeout = 'Xs'. I reviewed the native lock timeout parameter in Postgres and fo

Re: PostgreSQL as advanced job queuing system

2024-03-22 Thread Fred Habash
We developed a home-grown queue system using Postgres, but its performance was largely hindered by que tables bloating and the need to continuously vacuum them. It did not scale whatsoever. With some workarounds, we ended up designing three sets of queue tables, switching between them based on some

Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"

2023-01-16 Thread Fred Habash
This is a puzzle I have not been able to crack yet. We have a single-page table with 28 rows that is purely read-only. There isn't a way in postgres to make a table RO, but I say this with confidence because pg_stat_user_tables has always showed 0 updates/deletes/inserts. Furthermore, the schema

Re: Autovacuum Hung Due to Bufferpin

2023-01-11 Thread Fred Habash
27.15 |0 | 0 | 0 | 5764 |70.36 (1 row) On Wed, Jan 11, 2023 at 10:32 AM Tom Lane wrote: > Fred Habash writes: > > pg_locks shows no blockers while this is happening. This view shows a > > constant 13 sessions running SELECT stateme

Autovacuum Hung Due to Bufferpin

2023-01-11 Thread Fred Habash
I have a very small table of 28 rows that is less than 10kB. One of the AV workers has been stuck autovac'ing it for over 20 hrs now with no progress in heap blocks scanned or vac'd. I terminated the AV worker and ran a manual vac which also ended up stuck waiting for a bufferpin. pg_locks shows n

pg_create_logical_replication_slot in DB1 is blocked by a session in DB2

2022-05-25 Thread Fred Habash
I'm running this command while connected to pg cluster DB1: SELECT * FROM pg_create_logical_replication_slot('test_slot_99', 'test_decoding'); When I examine pg_locks, I see the session is waiting on virtualxid and blocked and blocking sessions are on two different DBs. After doing some research

Re: Allowing John to Drop Triggers On Chad's Tables

2021-03-16 Thread Fred Habash
Thank you all for taking the time to respond. As always, great community support. On Mon, Mar 15, 2021 at 5:10 PM Tom Lane wrote: > Fred Habash writes: > > Based on my research, There are the options I have identified to allow > user > > 'John' to drop a trigg

Allowing John to Drop Triggers On Chad's Tables

2021-03-15 Thread Fred Habash
Based on my research, There are the options I have identified to allow user 'John' to drop a trigger on a table owner by 'Chad' .. 1. Make John a superuser 2. grant chad to john ; 3. Have Chad create a wrapper definer-rights procedure to drop triggers & grant execute to John. 4. Give John Chad's p