vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
Hi, Please suggest me on the following, 1. Is it better to configure autovacuum threshold at table level? 2. Is there any discussions in this forum which I can refer for understanding vacuum/autovacuum? Thanks in advance. Rajan. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
Thanks, amul. I have already gone through this. What I would like to understand is the performance impact on autovacuum launcher and worker process when autovacuum is running from configurations done by *ALTER TABLE autvac_test SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);* at table level. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
thanks for the reply, amul. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
where clause not working through psql command line client
postgres@Server2[DEV][backup] $ psql -c 'select * from pg_class where relname = pg_toast_22345' ERROR: column "pg_toast_16387" does not exist LINE 1: select * from pg_class where relname = pg_toast_22345 ----- -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: where clause not working through psql command line client
Thanks a lot, Amul. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner
Please help to understand the following. Where the User(who is not the owner of a table) is able to ALTER DEFAULT PRIVILEGES and GRANT SELECT rights for all tables Is providing USAGE on schema is enough to do that? How is this secure? learning=> select current_user; current_user -- student (1 row) learning=> \dn List of schemas Name | Owner -+-- academics | head board_exams | head public | postgres (3 rows) learning=> set role head; SET learning=> CREATE SCHEMA additional; CREATE SCHEMA learning=> learning=> \dn List of schemas Name | Owner -+-- academics | head * additional | head* Schema's owner is the user head board_exams | head public | postgres (4 rows) learning=> CREATE TABLE additional.chess(id serial not null, marks varchar); CREATE TABLE learning=> GRANT USAGE ON SCHEMA additional TO student; GRANT learning=> set role student; SET learning=> \z additional.chess Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +---+---+---+---+-- * additional | chess | table | | |* -- USER student has no privilege on the table (1 row) learning=> SELECT current_user; current_user -- student (1 row) --with the student user have no privilege how ALTER DEFAULT PRIVILEGES works *learning=> ALTER DEFAULT PRIVILEGES IN SCHEMA additional GRANT INSERT ON TABLES TO student; ALTER DEFAULT PRIVILEGES learning=> \ddp Default access privileges Owner | Schema| Type | Access privileges -+-+---+ student | academics | table | student=aD/student student | additional | table | student=a/student student | board_exams | table | student=r/student (3 rows)* learning=> GRANT INSERT ON TABLES TO student; ERROR: relation "tables" does not exist learning=> GRANT INSERT ON TABLE additional.chess TO student; ERROR: permission denied for relation chess learning=> - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner
THanks for the response, Andrew. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner
Andrew, Another question, If the user student is not the owner of the Schema(additional) and has only USAGE / no privileges, How come it is able to modify permissions at schema level? - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: Able to do ALTER DEFAULT PRIVILEGES from a user who is not the owner
Thanks Andrew for the reply. Based on the answer, Is there a way to provide read access on all tables( *created by any user* ) to a Read Only user? - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
Hello Amul, I have a doubt. Please find below the details, 1. autovacuum_vacuum_threshold and autovacuum_analyse_threshold are set at 300 2. autovacuum_vacuum_scale_factor and autovacuum_analyse_scale_face are set at 300 3. there is table which has only 6 records 4. and the number of updates happened in the table is 944 5. now when I fetch data from pg_stat_user_table, i find that only last_autoanalyse column is updated with datetime and last_autovacuum column is not having a value does this mean only autoanalyse was executed for this table? But when I have both vacuum_threshold and analyse_threshold set at 300, how can only analyse run and vacuum does not? This may be a stupid question. But any answer can help me understand. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Re: vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?
Thanks for the reply Amul. Btw, No my table does not has any index. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Is there any way that one of the Postgres Background/Utility process may go down?
Is there any way that one of the Postgres Background process may go down? meaning the process getting stopped? For example, can the wal sender process alone stop working? If it does so, which part of the logs I must check to proceed further. - -- Thanks, Rajan. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Possible to exclude a database from loading a shared_preload_libraries module?
Hi, I have a use case where I want a particular database to not load a few modules in shared_preload_libraries. I was wondering if there's any way to tweak the codebase to achieve this. Otherwise, can I block the modules' hooks/bgw from performing actions on my particular database? -- Regards Rajan Pandey
[no subject]
Hi everyone, I just installed Postgres and pg_tle extension as I was looking to contribute to pg_tle. Somehow, I am unable to update the shared_preload_libraries. It feels like ALTER has happened but the SPL value is not updated: > test=# show shared_preload_libraries; > shared_preload_libraries > -- > > (1 row) > > test=# ALTER SYSTEM SET shared_preload_libraries TO 'pg_tle'; > ALTER SYSTEM > test=# SELECT pg_reload_conf(); > pg_reload_conf > > t > (1 row) > > test=# show shared_preload_libraries; > shared_preload_libraries > -- > > (1 row) > > test=# > I'm unable to open the postgresql.conf file to update it either. I provided the correct macbook password above. But it is not accepted! :/ > rajanx@b0be835adb74 postgresql % cat > /usr/local/pgsql/data/postgresql.auto.conf > cat: /usr/local/pgsql/data/postgresql.auto.conf: Permission denied rajanx@b0be835adb74 postgresql % su cat > /usr/local/pgsql/data/postgresql.auto.conf > Password: > su: Sorry > Please help! Thank you. :) -- Regards Rajan Pandey
Why is 'use_alias' hardcoded to true in deparseFromExprForRel() for some cases
Hi everyone, In the postgrs_fdw deparser code, deparseFromExprForRel() appends an alias to the remote query based on the 'use_alias' boolean flag. For a simple query, 'use_alias' is determined by `bms_membership(scanrel->relids) == BMS_MULTIPLE` condition. Example: https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/deparse.c#L1385C2-L1388 But for JOINs, 'use_alias' is always hardcoded to true. Examples: 1. https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/deparse.c#L2067-L2069 2. https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/deparse.c#L2336-L2337 3. https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/deparse.c#L2419-L2420 This seems like an extra-protection in case of joins. But it could happen that the join is across 2 different foreign postgres-servers (means each foreign server will do SCAN only, and the JOIN will happen at the upper layer). In that case, using aliases in the remote queries seem redundant to me. Please correct me if I am missing something. Can we note pass `bms_membership(foreignrel->relids) == BMS_MULTIPLE` instead? -- Regards Rajan Pandey Software Developer, AWS
