Re: Query running longer

[Laurenz Albe]

On Fri, 2024-02-02 at 10:14 +0530, veem v wrote:
> On Fri, 2 Feb 2024 at 02:43, Laurenz Albe  wrote:
> > On Fri, 2024-02-02 at 02:27 +0530, veem v wrote:
> > > We have the below query which is running for ~45 seconds on postgres 
> > > aurora reader instance.
> > > I have captured the explain analyze. Want to understand, where exactly 
> > > the resources are
> > > getting spent and if we can be able to optimize it further.
> > 
> > Aurora <> PostgreSQL, but here is what I can see:
> > 
> > - The index scan on SCHEMA1."TAB2" has to check 2 million extra
> >   rows because "work_mem" is too small.  Almost the complete time
> >   is spent there.
> > 
> > - You may be getting a bad plan, because the statistics on
> >   SCHEMA1.TAB4 are either out of date or not detailed enough,
> >   which makes PostgreSQL underestimate the result size.
> 
> As you mentioned below, So wondering how you got to know, if this is the step 
> where majority of the DB resources get spent. And as total time the query ran 
> was ~45 seconds, and out of that how much time it spent in this step, how can 
> i get that? And to fix this one line with regards to TAB2, should we increase 
> "work_mem" here?
> 
> - The index scan on SCHEMA1."TAB2" has to check 2 million extra
>   rows because "work_mem" is too small.  Almost the complete time
>   is spent there.
> 
>  ->  Parallel Bitmap Heap Scan on SCHEMA1.""TAB2"" TAB2  
> (cost=84860.50..13040301.00 rows=1175611 width=80) (actual 
> time=713.054..26942.082 rows=956249 loops=5)"
> "                     Output: TAB2.TAB2_time, TAB2.PRI, TAB2.AID"
>   Recheck Cond: (TAB2.MID = 'X'::numeric)
>   Rows Removed by Index Recheck: 2137395
>   Filter: ((TAB2.TAB2_time >= '2024-01-01 00:00:00+00'::timestamp with time 
> zone) AND (TAB2.TAB2_time <= '2024-01-31 00:00:00+00'::timestamp with time 
> zone))
>   Heap Blocks: exact=5300 lossy=782577
>   Buffers: shared hit=1651569 read=2245157
>   I/O Timings: shared/local read=29063.286  

The bitmap index scan builds a bitmap, the size of which is limited by 
"work_mem".
If that is big enough, the bitmap will contain a bit for each table row, if not,
it only contains a bit per table row for some table blocks (the "exact" ones),
then it degrades to a bit per block (whe "lossy" ones).

For lossy blocks, *all* rows in the block have to be rechecked, which is
overhead.  Given that only a small part of the time (26942.082 - 713.054) * 5
is spent doing I/O (29063.286), I guess that the rest is spent processing
table rows.

> Another point you mentioned as below , for this , should we run vacuum 
> analyze on the table TAB4?
> 
> - You may be getting a bad plan, because the statistics on
>   SCHEMA1.TAB4 are either out of date or not detailed enough,
>   which makes PostgreSQL underestimate the result size.
>   
>   ->  Parallel Bitmap Heap Scan on SCHEMA1.TAB4 TAB4  (cost=26.39..7042.63 
> rows=1049 width=37) (actual time=23.650..201.606 rows=27613 loops=5)
>    ->  Bitmap Index Scan on TAB4_idx1  (cost=0.00..25.95 rows=1784 
> width=0) (actual time=23.938..23.938 rows=138067 loops=1)
>    Index Cond: ((TAB4.TAB4_code)::text = 'XX'::text)
>    Buffers: shared hit=72

An ANALYZE might be enough.

If not, you can try to collect more detailed statistics for the column:

  ALTER TABLE SCHEMA1.TAB4 ALTER TAB4_code SET STATISTICS 1000;
  ANALYZE SCHEMA1.TAB4;

Yours,
Laurenz Albe

Re: using palloc/pfree for OpenSSL allocations with CRYPTO_set_mem_functions

[Evan Czaplicki]

Thank you for the explanation! I looked into what OpenSSL does when freeing
the underlying OSSLCipher resources, and as you say, it is more than just a
free. There is sometimes aditional values to free, and they go through and
overwrite the data in a special way regardless. So I see why the
TopMemoryContext approach is needed either way.

My particular interest was in using the BIGNUM implementation from OpenSSL
within my project, but the BIGNUM frees also do more than just freeing. So
it seems there is no way around the TopMemoryContext in my case either.

I could imagine that there may be some effect on the particular character
of memory fragmentation that comes with their malloc/free vs palloc/pfree,
but that's definitely outside of my personal purposes and outside of my
expertise to evaluate even at a high level!

So thank you again for helping me understand this! Happy to have a much
clear understanding of the TopMemoryContext approach!
Evan


On Thu, Feb 1, 2024 at 5:01 PM Tom Lane  wrote:

> Evan Czaplicki  writes:
> > I noticed that OpenSSL has a CRYPTO_set_mem_functions
> > 
> > function:
>
> >> If no allocations have been done, it is possible to “swap out” the
> default
> >> implementations for OPENSSL_malloc(), OPENSSL_realloc() and
> OPENSSL_free()
> >> and replace them with alternate versions.
>
> > But a different technique is used in contrib/pgcrypto/openssl.c
>
> >> To make sure we don't leak OpenSSL handles on abort, we keep OSSLCipher
> >> objects in a linked list, allocated in TopMemoryContext. We use the
> >> ResourceOwner mechanism to free them on abort.
>
> > Would it be desirable to do this? If not, why is the TopMemoryContext
> > approach a better option? I do not understand the code quite well enough
> to
> > evaluate the tradeoffs myself yet!
>
> Seems to me that these address different purposes.  If we put in a
> CRYPTO_set_mem_functions layer, I doubt that we'd have any good idea
> of which allocations are used for what.  So we could not replace what
> pgcrypto is doing with a simple MemoryContextReset (even if we cared
> to assume that freeing an OSSLCipher involves only free() operations
> and no other resources).  I think the only real win we'd get from
> such a layer is that OpenSSL's allocations would be better exposed
> for accounting purposes, eg the pg_backend_memory_contexts view.
> That's not negligible, but I don't find it a compelling reason to
> do the work, either.
>
> regards, tom lane
>

Re: vacuum freeze wait_event BufferPin

[Greg Sabino Mullane]

On Wed, Jan 31, 2024 at 2:05 PM abrahim abrahao 
wrote:

> There is a three-day vacuum freeze on a partition table with wait_event =
> BufferPin, no transactions (active or idle) older than it that are not
> blocked, but at least one select query is executing at all times related of
> this partition table.
> ...
>
Is there a wait to figure out which session vacuum freeze to wait for?
>

The vacuum needs a chance to get in and make changes to the table, but it's
not being blocked at the traditional lock level that shows up in
pg_blocking_pids. You can see what is going on with this:

select pid, mode, query_start, query from pg_locks join pg_stat_activity
using (pid) where relation::regclass::text = 'mytable' order by 3;

That may show your vacuum process with a ShareUpdateExclusiveLock and some
other processes with other locks, probably AccessShareLock. Those other
pids need to all finish or be killed - and not have any overlap between
them. In other words, that vacuum process needs to have exclusive access to
the table for a split second, no matter if the other process locked the
table before or after the vacuum started. One crude solution would be to
cancel any other existing backends interested in that table:

select pg_cancel_backend(pid), now()-query_start, query from pg_locks join
pg_stat_activity using (pid) where relation::regclass::text = 'mytable' and
lower(query) !~ 'vacuum';

Not a good long-term solution, but depending on how often the table is
updated, you might have other options. Perhaps disable  autovacuum for this
table and do a manual vacuum (e.g. in a cron script) that kills the other
backends as per above, or runs during a time with not-constant reads on the
table. Or have something that is able to pause the application. Or if this
is a partitioned table that might get dropped in the future or at least not
queried heavily, do not worry about vacuuming it now.

Cheers,
Greg

Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

[Marcelo Marques]

*PROBLEM*

*yum update nothing provides libarmadillo.so.12()(64bit) needed by
gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common  *

[root@rhel9-pg14 ~]# yum update --exclude=postgis33_14
--exclude=postgresql14*
Updating Subscription Management repositories.
EPEL_9

   26 kB/s | 2.3 kB
00:00
Red Hat Enterprise Linux 9 for x86_64 - AppStream (RPMs)

   50 kB/s | 4.5 kB
00:00
Red Hat CodeReady Linux Builder for RHEL 9 x86_64 (RPMs)

   49 kB/s | 4.5 kB
00:00
Red Hat Enterprise Linux 9 for x86_64 - BaseOS (RPMs)

  40 kB/s | 4.1 kB
00:00
Error:
 Problem: cannot install the best update candidate for package
gdal36-libs-3.6.4-5PGDG.rhel9.x86_64
 * - nothing provides libarmadillo.so.12()(64bit) needed by
gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common*
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to
use not only best candidate packages)
[root@rhel9-pg14 ~]#

*NOTE:*  --skip-broken still returns the same error message, and --nobest
just skips the gdal36-libs update

*REMARKS*
similar issue https://postgrespro.com/list/thread-id/2679095

*"Pushed GDAL 3.6.4-6 RPMS to the repos built against armadillo 12 for
RHEL 9, 8 and Fedora."*

*RHEL 9.3 DETAILS*

[root@rhel9-pg14 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux release 9.3 (Plow)
[root@rhel9-pg14 ~]#

[root@rhel9-pg14 ~]# rpm -qa | grep postgres | sort
postgresql14-14.10-1PGDG.rhel9.x86_64
postgresql14-contrib-14.10-1PGDG.rhel9.x86_64
postgresql14-libs-14.10-1PGDG.rhel9.x86_64
postgresql14-server-14.10-1PGDG.rhel9.x86_64

[root@rhel9-pg14 ~]# rpm -qa | grep postgis | sort
postgis34_14-3.4.0-1PGDG.rhel9.x86_64
[root@rhel9-pg14 ~]#

[root@rhel9-pg14 ~]# subscription-manager repos --list-enabled
+--+
Available Repositories in /etc/yum.repos.d/redhat.repo
+--+
Repo ID:   rhel-9-for-x86_64-appstream-rpms
Repo Name: Red Hat Enterprise Linux 9 for x86_64 - AppStream (RPMs)
Repo URL:
https://red-inf-cap-p01.esri.com/pulp/content/ESRI/LEP_Patch/cv_patch/content/dist/rhel9/$releasever/x86_64/appstream/os
Enabled:   1

Repo ID:   ESRI_EPEL_9_EPEL_9
Repo Name: EPEL_9
Repo URL:
https://red-inf-cap-p01.esri.com/pulp/content/ESRI/LEP_Patch/cv_patch/custom/EPEL_9/EPEL_9
Enabled:   1

Repo ID:   rhel-9-for-x86_64-baseos-rpms
Repo Name: Red Hat Enterprise Linux 9 for x86_64 - BaseOS (RPMs)
Repo URL:
https://red-inf-cap-p01.esri.com/pulp/content/ESRI/LEP_Patch/cv_patch/content/dist/rhel9/$releasever/x86_64/baseos/os
Enabled:   1

Repo ID:   codeready-builder-for-rhel-9-x86_64-rpms
Repo Name: Red Hat CodeReady Linux Builder for RHEL 9 x86_64 (RPMs)
Repo URL:
https://red-inf-cap-p01.esri.com/pulp/content/ESRI/LEP_Patch/cv_patch/content/dist/rhel9/$releasever/x86_64/codeready-builder/os
Enabled:   1

[root@rhel9-pg14 ~]# ls -la /etc/yum.repos.d/
total 52
drwxr-xr-x.   2 root root85 Jan 31 16:13 .
drwxr-xr-x. 136 root root  8192 Feb  2 10:33 ..
-rw-r--r--.   1 root root 13067 Dec 25 15:11 pgdg-redhat-all.repo
-rw-r--r--.   1 root root 14013 Oct 17 04:10 pgdg-redhat-all.repo.old
-rw-r--r--.   1 root root  4657 Jan 31 16:09 redhat.repo
[root@rhel9-pg14 ~]#

Thanks,
Marcelo Marques
Principal Product Engineer, Esri, www.esri.com

Re: vacuum freeze wait_event BufferPin

[abrahim abrahao]

Thanks Greg,  I really appreciated you message.I executed the query you shared, 
and it is showing exactly the same type of lock you talked, it help me a lot. 
ThanksIt is a usual behavior in some busy databases, I am trying to avoid 
cancel sessions. I would like also double check my understanding about locks on 
this documentation ( 
https://www.postgresql.org/docs/14/explicit-locking.html)Based on my 
understanding  on table 13.2  SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) 
should not be blocked by ACCESS SHARE (AccessShareLock). Am I wrong about it? 
If I am not wrong why it still locking it? 
Note: Information below come from the link above.Table-Level Lock Modes   
   - SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
   
   - Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, 
EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against 
concurrent schema changes and VACUUM runs.
   - Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, 
CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX 
and ALTER TABLE variants (for full details see the documentation of these 
commands).
   
   - ACCESS SHARE (AccessShareLock)
   
   - Conflicts with the ACCESS EXCLUSIVE lock mode only.
   - The SELECT command acquires a lock of this mode on referenced tables. In 
general, any query that only reads a table and does not modify it will acquire 
this lock mode.


Note: This information below come from another database,  the first lock was 
done.select pid, mode, query_start, SPLIT_PART(trim(query),' ', 1)  from 
pg_locks join pg_stat_activity using (pid) where relation::regclass::text = ' 
mytable' order by 3;  pid  |           mode           |          query_start    
      | 
split_part---+--+---+-
   376 | AccessShareLock          | 2024-02-02 08:11:08.938949+00 | SELECT   
508 | ShareUpdateExclusiveLock | 2024-02-02 08:11:17.822287+00 | vacuum 52767 | 
AccessShareLock          | 2024-02-02 19:43:40.110489+00 | SELECT 53137 | 
AccessShareLock          | 2024-02-02 19:44:19.331633+00 | SELECT 53460 | 
AccessShareLock          | 2024-02-02 19:54:00.315714+00 | SELECT 54203 | 
AccessShareLock          | 2024-02-02 19:54:39.449686+00 | SELECT 53164 | 
AccessShareLock          | 2024-02-02 20:01:26.429547+00 | SELECT 54002 | 
AccessShareLock          | 2024-02-02 20:01:32.749586+00 | SELECT 53583 | 
AccessShareLock          | 2024-02-02 20:01:34.624046+00 | SELECT





On Friday, February 2, 2024 at 01:37:19 p.m. EST, Greg Sabino Mullane 
 wrote:  
 
 On Wed, Jan 31, 2024 at 2:05 PM abrahim abrahao  wrote:

 There is a three-day vacuum freeze on a partition table with wait_event = 
BufferPin, no transactions (active or idle) older than it that are not blocked, 
but at least one select query is executing at all times related of this 
partition table. ... 

Is there a wait to figure out which session vacuum freeze to wait for?

The vacuum needs a chance to get in and make changes to the table, but it's not 
being blocked at the traditional lock level that shows up in pg_blocking_pids. 
You can see what is going on with this:
select pid, mode, query_start, query from pg_locks join pg_stat_activity using 
(pid) where relation::regclass::text = 'mytable' order by 3;

That may show your vacuum process with a ShareUpdateExclusiveLock and some 
other processes with other locks, probably AccessShareLock. Those other pids 
need to all finish or be killed - and not have any overlap between them. In 
other words, that vacuum process needs to have exclusive access to the table 
for a split second, no matter if the other process locked the table before or 
after the vacuum started. One crude solution would be to cancel any other 
existing backends interested in that table:
select pg_cancel_backend(pid), now()-query_start, query from pg_locks join 
pg_stat_activity using (pid) where relation::regclass::text = 'mytable' and 
lower(query) !~ 'vacuum';

Not a good long-term solution, but depending on how often the table is updated, 
you might have other options. Perhaps disable  autovacuum for this table and do 
a manual vacuum (e.g. in a cron script) that kills the other backends as per 
above, or runs during a time with not-constant reads on the table. Or have 
something that is able to pause the application. Or if this is a partitioned 
table that might get dropped in the future or at least not queried heavily, do 
not worry about vacuuming it now.
Cheers,Greg
  

update schema table permission denied

[Vano Beridze]

Hello,

I've got a table outbox_event in schema adm
The owner of this table is some role
I granted insert and update permission on the table outbox_event to the
user "app"

When the following command is run with the user app

update adm.outbox_event set .. where id = ..

Postgresql gives the notification:

ERROR: permission denied for table outbox_event

What could be the problem?

Version is 16.1

Kind regards,
Vano

Re: update schema table permission denied

[Tom Lane]

Vano Beridze  writes:
> I granted insert and update permission on the table outbox_event to the
> user "app"
> When the following command is run with the user app
> update adm.outbox_event set .. where id = ..
> Postgresql gives the notification:
> ERROR: permission denied for table outbox_event

You need SELECT privilege too in order to read the "id" column.

regards, tom lane

Re: vacuum freeze wait_event BufferPin

[Greg Sabino Mullane]

On Fri, Feb 2, 2024 at 3:25 PM abrahim abrahao 
wrote:

>  SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) should not be blocked
> by ACCESS SHARE (AccessShareLock). Am I wrong about it? If I am not wrong
> why it still locking it?
>

Those locks with no other context are somewhat  of a red herring. The
important part is not that the AccessShare is somehow blocking
ShareUpdateExclusive, but that the ShareUpdateExclusive process is NOT
blocking new AccessShare processes! In the internals of postgres,
vacuumlazy.c tries to grab a buffer lock (different concept from all the
user-visible locks above). It politely tries to wait[1] until nobody else
is grabbing it (aka pinning it), then proceeds. The problem is that other
processes are allowed to come along and put a pin in it as well - the
vacuum's shareupdateexclusive lock does not prevent that.

So the timeline is:

Process X runs a long select and pins the buffer
Process V runs a vacuum freeze and tries to lock the buffer. It detects
other pins, so it waits. It assumes that whoever is holding the pin will
release it someday.
Process Y runs another long select and also pins the buffer.
Process X ends, and removes its pins.
Process V still cannot move - it just knows there are still pins. Where
they come from does not matter.

As long as there is at least one other process holding a pin, the vacuum
freeze cannot continue[2].

That's my understanding of the code, anyway. This could be argued as a bug.
I am not sure what a solution would be. Cancelling user queries just for a
vacuum would not be cool, but we could maybe pause future pin-creating
actions somehow?

For the time being, forcing a super-quick moment of no table access would
seem to be your best bet, as described earlier.

Cheers,
Greg

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/vacuumlazy.c;hb=HEAD#l975
See backend/storage/buffer/bufmgr.c for LockBufferForCleanup()

[2] Quick duplication script:
drop table if exists foobar;
create table foobar as select 1 as id;
alter table foobar set (autovacuum_enabled = off);
update foobar set id = id;

Process 1:
begin; select *, pg_sleep(11) from foobar;

Process 2:
vacuum(freeze,verbose) foobar; /* blocked */

Process 3:
begin; select *, pg_sleep(33) from foobar;

Run in order. Kill Process 1 and Process 2 is still blocked. Kill Process 3
and Process 2 finished the vacuum.
Note that a regular vacuum (without a freeze) will not get blocked.

Cheers,
Greg

Re: Logical replication and AFTER UPDATE triggers [PG 16]

[Chris Angelico]

On Fri, 2 Feb 2024 at 13:20, Chris Angelico  wrote:
> create or replace function send_settings_notification() returns
> trigger language plpgsql as $$begin perform
> pg_notify('stillebot.settings', ''); return null; end$$;
> create trigger settings_update_notify after update on
> stillebot.settings execute function send_settings_notification();
> alter table stillebot.settings enable always trigger settings_update_notify;
>

Ah ha! A discovery. It may be that a FOR EACH STATEMENT trigger (which
is the default) does not fire on the subscriber. Converting to FOR
EACH ROW seems to make this function. Does this seem reasonable? I
can't find anything in the docs that confirms it.

ChrisA