Re: Users and object privileges maintenance
On 18.02.2024 15 :19, Dominique Devienne wrote: ... ... But you of course can connect only to DBs you have explicit access to. Just beware that before 15, the built-in PUBLIC role, which all ROLEs are implicitly members of, had implicit access to newly created DBs. It's still the same concerning default database CONNECT privilege for PUBLIC. You must confuse with the fact that v15+ removes PUBLIC creation permission on the [ https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PUBLIC | public schema ] Regards Gilles
Re: Not able to purge partition
- Mail original - > De: "Laurenz Albe" > À: "veem v" , "pgsql-general" > > Envoyé: Jeudi 21 Mars 2024 13:48:19 > Objet: Re: Not able to purge partition > On Thu, 2024-03-21 at 11:07 +0530, veem v wrote: >> CREATE TABLE schema1.test_part_drop_parent >> ( >> c1 varchar(36) NOT NULL , >> c2_part_date timestamp with time zone NOT NULL , >> c3 numeric(12,0) NOT NULL , >> CONSTRAINT test_part_drop_parent_PK PRIMARY KEY (c1,c2_part_date) >> ) PARTITION BY RANGE (c2_part_date); >> >> CREATE INDEX idx1_test_part_parent ON schema1.test_part_drop_parent( C3 ASC); >> >> >> CREATE TABLE schema1.test_part_drop_child >> ( >> C1_child varchar(36) NOT NULL , >> C1 varchar(36) NOT NULL , >> c2_part_date timestamp with time zone NOT NULL , >> C3 numeric(12,0) NOT NULL , >> CONSTRAINT test_part_drop_child_PK PRIMARY KEY (C1_child,c2_part_date), >> CONSTRAINT test_part_drop_child_FK1 FOREIGN KEY (C1,c2_part_date) REFERENCES >> schema1.test_part_drop_parent(c1,c2_part_date) >> ) PARTITION BY RANGE (c2_part_date); >> >> CREATE INDEX test_part_drop_child_fk ON schema1.test_part_drop_child( c1, >> c2_part_date); >> >> CREATE INDEX test_part_drop_child_idx ON schema1.test_part_drop_child( >> c1_child, >> c2_part_date); >> >> [create some partitions, then drop a partition of the referenced table] >> > >> SQL Error [P0001]: ERROR: cannot drop table >> schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it >> CONTEXT: SQL statement "DROP TABLE schema1.test_part_drop_parent_p2024_02_01" > > That's normal. If you create a foreign key constraint to a partitioned table, > you > can no longer drop a partition of the referenced table. > > What you *can* do is detach the partition and then drop it, but detatching > will > be slow because PostgreSQL has to check for referencing rows. > > The best solution is to create the foreign key *not* between the partitioned > tables, but between the individual table partitions. That should be easy if > you have the same partition boundaries for both. > Then you can simply drop a partition from both tables at the same time. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com Also, as an aside, test_part_drop_child_idx index is redundant with test_part_drop_child_pk index created for the primary key Regards Gilles
Re: Controlling resource utilization
> De: "Juan Rodrigo Alejandro Burgos Mella" > À: "yudhi s" > Cc: "pgsql-general" > Envoyé: Mardi 16 Avril 2024 22:29:35 > Objet: Re: Controlling resource utilization > 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 (< [ > mailto:learnerdatabas...@gmail.com > | learnerdatabas...@gmail.com ] >) escribió: >> On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, < [ >> mailto:rodrigoburgosme...@gmail.com | rodrigoburgosme...@gmail.com ] > wrote: >>> Yes sir >>> SET statement_timeout TO '' >>> Atte >>> JRBM >>> El mar, 16 abr 2024 a las 14:46, yudhi s (< [ >>> mailto:learnerdatabas...@gmail.com >>> | learnerdatabas...@gmail.com ] >) 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. Or also at role/user level : ALTER ROLE < your - username > SET statement_timeout = '' ; Regards Gilles
Re: Controlling resource utilization
> De: "yudhi" > À: "gparc" > Cc: "Juan Rodrigo Alejandro Burgos Mella" , > "pgsql-general" > Envoyé: Mercredi 17 Avril 2024 09:42:49 > Objet: Re: Controlling resource utilization > On Wed, 17 Apr, 2024, 12:40 pm , < [ mailto:gp...@free.fr | gp...@free.fr ] > > wrote: >>> De: "Juan Rodrigo Alejandro Burgos Mella" < [ >>> mailto:rodrigoburgosme...@gmail.com | rodrigoburgosme...@gmail.com ] > >>> À: "yudhi s" < [ mailto:learnerdatabas...@gmail.com | >>> learnerdatabas...@gmail.com ] > >>> Cc: "pgsql-general" < [ mailto:pgsql-general@lists.postgresql.org | >>> pgsql-general@lists.postgresql.org ] > >>> Envoyé: Mardi 16 Avril 2024 22:29:35 >>> Objet: Re: Controlling resource utilization >> ALTER ROLE SET statement_timeout = ''; >> Regards >> Gilles > Thank you so much. That helps. > This statement is succeeding for user as I executed. So it's working I > believe. > But to immediately verify without manually running queries and waiting for it > to > be auto killed to confirm, Is there any system table which we can verify to > see > if this setting is effective, as because I don't see any such columns in > pg_user or pg_role which shows the statement_timeout. > And is there a way to put similar cap/restrictions on other db resources like > cpu, memory, I/O at specific user/role level? To verify the setting, you can use this command in psql : \drds Concerning system resources like CPUs it's not possible. You can use pg_settings view to see which setting you can change and in which context : [ https://www.postgresql.org/docs/current/view-pg-settings.html | https://www.postgresql.org/docs/current/view-pg-settings.html ] Regards Gilles
Re: Customize psql prompt to show current_role
- Mail original - > De: "Dominique Devienne" > À: "Asad Ali" > Cc: pgsql-gene...@postgresql.org > Envoyé: Lundi 23 Septembre 2024 14:34:39 > Objet: Re: Customize psql prompt to show current_role > On Mon, Sep 23, 2024 at 2:22 PM Asad Ali wrote: >> There is no direct prompt escape sequence like %n for displaying the >> current_role in the psql prompt. >> However, you can work around this by using a \set command to define a custom >> prompt that includes the result of current_role. > > Hi Ali. Doesn't seem to be working for me. Did I do something wrong? --DD > > ``` > D:\>psql service=pau16 > psql (17beta3, server 16.1) > Type "help" for help. > > ddevienne=> \set current_role 'SELECT current_role;' > ddevienne=> \set PROMPT1 '%n@%/ (%`current_role`)=%# ' > 'current_role' is not recognized as an internal or external command, > operable program or batch file. > ddevienne@ddevienne ()=> select 1; > ... > 'current_role' is not recognized as an internal or external command, > operable program or batch file. > ddevienne@ddevienne ()=> > ``` Hello Dominique, I will use psql \gset command for this Regards Gilles
