Re: Controlling resource utilization

[Juan Rodrigo Alejandro Burgos Mella]

In postgreSQL, that can be done at a session level, or at a general level
(in the postgresql.conf configuration file)

Atte
JRBM

El mar, 16 abr 2024 a las 15:18, yudhi s ()
escribió:

>
>
> On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, <
> rodrigoburgosme...@gmail.com> wrote:
>
>> Yes sir
>>
>> SET statement_timeout TO ''
>>
>> Atte
>> JRBM
>>
>> El mar, 16 abr 2024 a las 14:46, yudhi s ()
>> escribió:
>>
>>> Hi ,
>>> We want to have controls around the DB resource utilization by the adhoc
>>> user queries, so that it won't impact the application queries negatively.
>>> Its RDS postgresql database version 15.4.
>>>
>>> Saw one parameter as statement_timeout which restricts the queries to
>>> not run after a certain time duration and queries will be automatically
>>> killed/cancelled. However, I don't see any other options to set this at
>>> user level, rather it's getting set for all or at session level. So I want
>>> to know if there exists, anyway to control the database resource
>>> utilization specific to users?
>>>
>>> Regards
>>> Yudhi
>>>
>>
>
> This will set the timeout at session level. However, We want to
> understand, if it can be done at user/role level, so that any such adhoc
> user queries can be auto killed or cancelled after the set time.
>
>>
>>>

Re: Question on trigger

[Adrian Klaver]

On 4/16/24 12:39, veem v wrote:



On Sat, 13 Apr 2024 at 21:44, Adrian Klaver > wrote:


On 4/13/24 00:03, veem v wrote:
 > Thank you Adrian.
 >
 > So it seems the heavy DML tables will see an impact if having
triggers
 > (mainly for each row trigger) created on them.
 >
 > And also the bulk DML/array based insert (which inserts multiple
rows in
 > one short or one batch) , in those cases it seems the trigger
will not
 > make that happen as it will force it to make it happen row by
row, as
 > the trigger is row based. Will test anyway though.

You said you have triggers in the Oracle database and I assumed they
worked and where not a show stopping issue there. What makes you think
that would be different in Postgres?

What type of triggers where there in Oracle, per row, per statement
or a
mix?


Actually we have row level triggers  in oracle which are running for 
smaller volume DML and are making the direct path inserts to happen in 
conventional row by row insert, in presence of trigger. So was wondering 


Not sure what the above means, you will need to provide a more detailed 
description. Though any DML you are doing on table that has any sort of 
constraint, index, trigger, foreign key, default values, etc is going to 
have more overhead then into an unencumbered table. FYI, some of the 
preceding are system triggers, for example foreign keys.


if it postgres we will be encountering a similar issue and batch inserts 
may be converted back to row by row automatically. And here we are going 
to process higher volume DMLS in postgresql database.




Hard to say with the information provided. Easiest way to find out is 
create a test setup  and run the code. Though I guess, as I have not 
actually tried this, you could have a per row trigger and per statement 
trigger for the same action and disable the per row and enable the per 
statement trigger for batch operations. Then once the batch operation is 
done reverse the process. Again something to test to verify.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Controlling resource utilization

[yudhi s]

On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, <
rodrigoburgosme...@gmail.com> wrote:

> Yes sir
>
> SET statement_timeout TO ''
>
> Atte
> JRBM
>
> El mar, 16 abr 2024 a las 14:46, yudhi s ()
> escribió:
>
>> Hi ,
>> We want to have controls around the DB resource utilization by the adhoc
>> user queries, so that it won't impact the application queries negatively.
>> Its RDS postgresql database version 15.4.
>>
>> Saw one parameter as statement_timeout which restricts the queries to not
>> run after a certain time duration and queries will be automatically
>> killed/cancelled. However, I don't see any other options to set this at
>> user level, rather it's getting set for all or at session level. So I want
>> to know if there exists, anyway to control the database resource
>> utilization specific to users?
>>
>> Regards
>> Yudhi
>>
>

This will set the timeout at session level. However, We want to understand,
if it can be done at user/role level, so that any such adhoc user queries
can be auto killed or cancelled after the set time.

>
>>

Re: Controlling resource utilization

[Juan Rodrigo Alejandro Burgos Mella]

Yes sir

SET statement_timeout TO ''

Atte
JRBM

El mar, 16 abr 2024 a las 14:46, yudhi s ()
escribió:

> Hi ,
> We want to have controls around the DB resource utilization by the adhoc
> user queries, so that it won't impact the application queries negatively.
> Its RDS postgresql database version 15.4.
>
> Saw one parameter as statement_timeout which restricts the queries to not
> run after a certain time duration and queries will be automatically
> killed/cancelled. However, I don't see any other options to set this at
> user level, rather it's getting set for all or at session level. So I want
> to know if there exists, anyway to control the database resource
> utilization specific to users?
>
> Regards
> Yudhi
>
>

Controlling resource utilization

[yudhi s]

Hi ,
We want to have controls around the DB resource utilization by the adhoc
user queries, so that it won't impact the application queries negatively.
Its RDS postgresql database version 15.4.

Saw one parameter as statement_timeout which restricts the queries to not
run after a certain time duration and queries will be automatically
killed/cancelled. However, I don't see any other options to set this at
user level, rather it's getting set for all or at session level. So I want
to know if there exists, anyway to control the database resource
utilization specific to users?

Regards
Yudhi

Re: Question on trigger

[veem v]

On Sat, 13 Apr 2024 at 21:44, Adrian Klaver 
wrote:

> On 4/13/24 00:03, veem v wrote:
> > Thank you Adrian.
> >
> > So it seems the heavy DML tables will see an impact if having triggers
> > (mainly for each row trigger) created on them.
> >
> > And also the bulk DML/array based insert (which inserts multiple rows in
> > one short or one batch) , in those cases it seems the trigger will not
> > make that happen as it will force it to make it happen row by row, as
> > the trigger is row based. Will test anyway though.
>
> You said you have triggers in the Oracle database and I assumed they
> worked and where not a show stopping issue there. What makes you think
> that would be different in Postgres?
>
> What type of triggers where there in Oracle, per row, per statement or a
> mix?
>
>
Actually we have row level triggers  in oracle which are running for
smaller volume DML and are making the direct path inserts to happen in
conventional row by row insert, in presence of trigger. So was wondering if
it postgres we will be encountering a similar issue and batch inserts may
be converted back to row by row automatically. And here we are going to
process higher volume DMLS in postgresql database.

AWS RDS Postgres and the DBA: Which/how many aws permissions/access do we really need?

[Gus Spier]

So, I'm looking for advice here.  Can anyone recommend a list of
useful/required AWS RDS permissions for a pro-active DBA? We are taking
custody of a set of databases that will need  sane backup and recovery
plans; table partitioning; undiscovered postgres extensions and we don't
yet know what else.

RDSADMIN is out of the question, even though it would be the one-stop shop
for managing the clusters and databases. But, unless I have to, I do not
care to administrate by trial and error ((tripping over each mine in the
field.)

Any advice would be appreciated.

Thanks,
Gus

Re: efficiency random values / sequential ID values in indexes

[Sanjay Minni]

Thanks

On Mon, Apr 15, 2024 at 6:19 PM Ron Johnson  wrote:

> On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni 
> wrote:
>
>> Hi
>>
>> Is there any appreciable difference in using random values or sequential
>> values in indexes
>>
>> in a multi tenanted application there is a choice that the single field
>> ID's value is totally random / UUID or the numbers are created with a
>> prefix of the tenant. Since all access will be within a tenant only, will
>> it make any performance difference between using purely random values vs
>> +.
>>
>
> Two benefits of +:
>
> 1. In a non-partitioned table, it gives your index "locality of data":
> all of customer X's record pointers are in *This* subtree.  Makes buffers
> more efficient when a customer runs reports.  Bonus points if you then
> regularly CLUSTER using that table.
> 2. Makes table partitioning by  much easier.  That also
> enhances locality of data.
>
> Just make sure that the field ID is BIGINT...
>

Re: Assistance needed for the query execution in non-public schema

[Laurenz Albe]

On Tue, 2024-04-16 at 16:30 +0530, Sasmit Utkarsh wrote:
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'), 
> coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> ERROR:  permission denied for sequence mqa_flfo_cstr_id_seq
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'), 
> coalesce(MAX(id), 1)) from mqa_ffp_nval;
> ERROR:  permission denied for sequence mqa_ffp_nval_id_seq
> 
> msshctd=> \z mqa_flfo_cstr_id_seq
>                                            Access privileges
>  Schema  |         Name         |   Type   |      Access privileges      | 
> Column privileges | Policies
> -+--+--+-+---+--
>  shc_tty | mqa_flfo_cstr_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|    
>                |
>          |                      |          | shc_uadmin=rU/pgddb_admin   |    
>                |
> (1 row)
> 
> msshctd=> \z mqa_ffp_nval_id_seq
>                                            Access privileges
>  Schema  |        Name         |   Type   |      Access privileges      | 
> Column privileges | Policies
> -+-+--+-+---+--
>  shc_tty | mqa_ffp_nval_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|     
>               |
>          |                     |          | shc_uadmin=rU/pgddb_admin   |     
>               |
> (1 row)
>  
> Above query executes fine when I try to execute as user "pgddb_admin"(Super 
> User).
> but when I switch to shc_uadmin, I see a permission error.

That's because the user is lacking the UPDATE privilege ("U" is USAGE).

> GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> ERROR: unrecognized privilege type "alter" SQL state: 42601

There is no ALTER privilege.  Try

  GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq TO 
shc_uadmin;

Yours,
Laurenz Albe

Re: Assistance needed for the query execution in non-public schema

[David Rowley]

On Tue, 16 Apr 2024 at 23:00, Sasmit Utkarsh  wrote:
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'), 
> coalesce(MAX(id), 1)) from mqa_flfo_cstr;
> ERROR:  permission denied for sequence mqa_flfo_cstr_id_seq
> msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'), 
> coalesce(MAX(id), 1)) from mqa_ffp_nval;
> ERROR:  permission denied for sequence mqa_ffp_nval_id_seq

> GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> ERROR: unrecognized privilege type "alter" SQL state: 42601

According to our documentation for setval():

"This function requires UPDATE privilege on the sequence."

https://www.postgresql.org/docs/current/functions-sequence.html

David

Assistance needed for the query execution in non-public schema

[Sasmit Utkarsh]

Hi Postgresql Team,

I'm trying to execute the below query using the below

psql 'host=cucmpsgsu0.postgres.database.azure.com port=5432 dbname=msshctd
user=shc_uadmin password=x sslmode=require
options=--search_path=shc_tty'

msshctd=> show search_path;
 search_path
-
 shc_tty
(1 row)
msshctd=> SELECT setval(pg_get_serial_sequence('mqa_flfo_cstr', 'id'),
coalesce(MAX(id), 1)) from mqa_flfo_cstr;
*ERROR:  permission denied for sequence mqa_flfo_cstr_id_seq*
msshctd=> SELECT setval(pg_get_serial_sequence('mqa_ffp_nval', 'id'),
coalesce(MAX(id), 1)) from mqa_ffp_nval;
*ERROR:  permission denied for sequence mqa_ffp_nval_id_seq*

msshctd=> \z mqa_flfo_cstr_id_seq
   Access privileges
 Schema  | Name |   Type   |  Access privileges  |
Column privileges | Policies
-+--+--+-+---+--
 shc_tty | mqa_flfo_cstr_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
  |
 |  |  | *shc_uadmin=rU/pgddb_admin*
|   |
(1 row)

msshctd=> \z mqa_ffp_nval_id_seq
   Access privileges
 Schema  |Name |   Type   |  Access privileges  |
Column privileges | Policies
-+-+--+-+---+--
 shc_tty | mqa_ffp_nval_id_seq | sequence | pgddb_admin=rwU/pgddb_admin+|
|
 | |  | *shc_uadmin=rU/pgddb_admin *  |
  |
(1 row)

Above query executes fine when I try to execute as user *"pgddb_admin"(Super
User)*. but when I switch to shc_uadmin, I see a permission error. Above
I've included the permissions listed for both the users. I also tried to
set the alter permissions for one of  the sequence for the shc_uadmin user
with the below

GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
*ERROR: unrecognized privilege type "alter" SQL state: 42601*


Is there any other way i can execute the queries, with user *"shc_uadmin"*
for the *setval()* to work properly without giving complete privileges same
as the owner *"pgddb_admin"* ? Any workaround/ alternate option which would
work here? Please assist



Regards,
Sasmit Utkarsh
+91-7674022625

Re: Obsolete Linux downloads (Debian) instructions

[Christoph Berg]

Re: Daniel Gustafsson
> > On 12 Apr 2024, at 12:43, Laura Smith  
> > wrote:
> 
> > Who do I have to badger to get the obsolete and frankly dangerous Debian 
> > repo instructions fixed @ https://www.postgresql.org/download/linux/debian/ 
> > ?

I still think that "frankly dangerous" is a gross exaggeration, but
the long-due update of the Debian and Ubuntu download pages has now
happened.

Christoph

Re: Failing streaming replication on PostgreSQL 14

[Nicolas Seinlet]

Hello,

> What exactly is "cyphered ZFS"? Can you reproduce the problem with some
> other filesystem? If it's something very unusual, it might well be a
> bug in the filesystem.

The filesystem is openzfs with native aes-256-gcm encryption:
https://openzfs.github.io/openzfs-docs/man/master/7/zfsprops.7.html#encryption

I've not tested if we get the same issue on another filesystem.

I don't face the issue on Ubuntu 20.04/openzfs 0.8/PostgreSQL 12, but I have 
fewer systems with this deployment.
On Ubuntu 22.04/openzfs 2.1.5/PostgreSQL 14, I face the issue from time to 
time, without knowing what triggers the error.

thanks for helping,

Nicolas.



publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature