Re: Long running query causing XID limit breach
Greetings, In high-transaction environments like yours, it may be necessary to supplement this with manual vacuuming. Few Recommendations Monitor Long-Running Queries try to optimize. Optimize Autovacuum. Partitioning. Adopt Vacuum Strategy after peak hours. *Salahuddin (살라후딘**)* On Thu, 23 May 2024 at 02:16, sud wrote: > Hello , > It's RDS postgres version 15.4. We suddenly saw the > "MaximumUsedTransactionIDs" reach to ~1.5billion and got alerted by team > members who mentioned the database is going to be in shutdown/hung if this > value reaches to ~2billion and won't be able to serve any incoming > transactions. It was a panic situation. > > I have heard of it before , because of the way postgres works and the XID > being a datatype of length 32 bit integer can only represent (2^32)/2=~2 > billion transactions. However, as RDS performs the auto vacuum , we thought > that we need not worry about this issue. But it seems we were wrong. And we > found one adhoc "SELECT '' query was running on the reader instance since > the last couple of days and when that was killed, the max xid > (MaximumUsedTransactionIDs) dropped to 50million immediately. > > So I have few questions, > > 1)This system is going to be a 24/7 up and running system which will > process ~500million business transactions/day in future i.e. ~4-5billion > rows/day inserted across multiple tables each day. And as I understand each > row will have XID allocated. So in that case , does it mean that, we will > need (5billion/24)=~200million XID/hour and thus , if any such legitimate > application "SELECT" query keeps running for ~10 hours (and thus keep the > historical XID alive) , then it can saturate the > "MaximumUsedTransactionIDs" and make the database standstill in > 2billion/200million=~10hrs. Is this understanding correct? Seems we are > prone to hit this limit sooner going forward. > > 2)We have some legitimate cases where the reporting queries can run for > 5-6hrs. So in such cases if the start of this SELECT query happen at 100th > XID on table TAB1, then whatever transactions happen after that time, > across all other tables(table2, table3 etc) in the database won't get > vacuum until that SELECT query on table1 get vacuumed(as database will try > to keep that same 100th XID image) and the XID will just keep incrementing > for new transaction, eventually reaching the max limit. Is my understanding > correct here? > > 3)Although RDS does the auto vacuum by default. but should we also > consider doing manual vacuum without impacting ongoing transactions? > Something as below options > vacuum freeze tab1; > vacuum freeze; > vacuum; > vacuum analyze tab1; > vacuum tab1; > > 4)Had worked in past in oracle database where the similar transaction > identifier is called as "system change number" , but never encountered that > being exhausted and also there it used to have UNDO record and if a SELECT > query needs anything beyond certain limit(set undo_retention parameter) the > select query used to fail with snapshot too old error but not impacting any > write transactions. But in postgres it seems nothing like that happens and > every "Select query" will try to run till its completion without any such > failure, until it gets skilled by someone. Is my understanding correct? > > And in that case, It seems we have to mandatorily set "statement_timeout" > to some value e.g. 4hrs(also i am not seeing a way to set it for any > specific user level, so it will be set for all queries including > application level) and also "idle_in_transaction_session_timeout" to > 5minutes, even on all the prod and non prod databases, to restrict the long > running transactions/queries and avoid such issues in future. Correct me if > I'm wrong. > > Regards > Sud >
Re: Json table/column design question
Greetings, Storing unrelated JSON objects in the same table with distinct columns for each type (e.g., "Users" and "Inventory") is generally not a sound good approach may affect Query Performance and Optimization, Storage Efficiency, scalability and Maintenance, Data Integrity. Recommended approach is to have separate tables. *Salahuddin (살라후딘* *)* On Thu, 23 May 2024 at 08:39, Skorpeo Skorpeo wrote: > Hi, > > I was wondering if having unrelated columns in a table is a sound approach > when using json. In other words, if I have two collections of unrelated > json objects, for example "Users" and "Inventory", would it be ok to have > one table with a "Users" column and a "Inventory" column? My concern is > that from a row perspective the columns could be different lengths, such as > more inventory items as users. And for any given row the data in one > column would have no relation to another column. I would only query a > single column at a time. > > Would this approach be ok or are there pitfalls such that it would be > advantageous/recommended to have a separate table for each column? > > Any thoughts/inputs are greatly appreciated. > > Many thanks. >
Re: Long running query causing XID limit breach
Greetings, Running `VACUUM table_name;` on a partitioned table will vacuum each partition individually, not the whole table as a single unit. Yes, running `VACUUM table_name;` frequently on tables or partitions with heavy DML is recommended. Regular `VACUUM` does not lock the table for reads or writes, so it won't disrupt ongoing 24/7 data operations. "optimize autovacuum" Yes. Adjust following parameters as per your system/environment requirement autovacuum_max_workers, autovacuum_freeze_max_age , autovacuum_vacuum_cost_delay Following need to be first tested thoroughly in a test environment. Recommended Alert Threshold Alert at 50% Usage: Set the alert threshold at 1 billion used XIDs. This provides a significant buffer, giving you ample time to take corrective action before reaching the critical limit. Calculation Rationale Daily XID Usage: Approximately 4 billion rows per day implies high XID consumption. Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs remaining, giving you roughly 12 hours to address the issue if your system consumes 200 million XIDs per hour. *Salahuddin (살라후딘**)* On Thu, 23 May 2024 at 09:48, sud wrote: > On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor < > salahuddi...@bitnine.net> wrote: > >> Greetings, >> >> In high-transaction environments like yours, it may be necessary to >> supplement this with manual vacuuming. >> >> Few Recommendations >> >> Monitor Long-Running Queries try to optimize. >> Optimize Autovacuum. >> Partitioning. >> Adopt Vacuum Strategy after peak hours. >> >> We have these big tables already partitioned. So does "vacuum table_name" > will endup scanning whole table or just the latest/live partition which is > getting loaded currently? and do you mean to say running command "vacuum > table_name;" frequently on selective tables that are experiencing heavy DML > ? Hope this won't lock the table anyway because the data will be > written/read from these tables 24/7. > > When you say, "optimize autovacuum" does it mean to set a higher value of > "autovacuum_max_workers" > and "autovacuum_freeze_max_age"? > > Considering we have ~4 billion rows inserted daily into the table and > there is limit of ~2billion to the "Maximumusedtxnids", what threshold > should we set for the alerting and to have enough time at hand to fix this > issue? > >
Re: Long running query causing XID limit breach
Greetings, Yes, monitoring and alerting for VACUUM operations are crucial. Track VACUUM Duration and Success: SELECT pid, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE query LIKE 'VACUUM%' ORDER BY duration DESC; Check Autovacuum Activity: SELECT relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_all_tables WHERE relname = 'your_table_name'; Log and Monitor VACUUM Failures: log_autovacuum_min_duration = 0 # Log all autovacuum actions log_min_messages = 'WARNING' # Ensure warnings and above are logged Use tools like pgBadger to analyze PostgreSQL logs and identify any issues with autovacuum operations. Set Up Alerts for Long-Running VACUUMs: Use monitoring tools such as pgMonitor, Nagios, Prometheus with Grafana, or New Relic to set up alerts for long-running VACUUM processes. Yes, your understanding is correct. In a high-transaction environment like yours, long-running transactions, including legitimate reporting queries, can hold back the advancement of the transaction ID (XID) horizon. This can prevent VACUUM from properly cleaning up old XIDs, leading to the risk of XID wraparound and potential system failure. Use some Mitigation Strategies to handle long running quires like Set Transaction Timeouts, Monitor and Kill Long-Running Queries, Optimize Query Performance, Schedule heavy reporting queries during periods of lower transactional activity. *Salahuddin (살라후딘* *)* On Thu, 23 May 2024 at 11:25, sud wrote: > Also,if i am getting it correct, it means we should not run any > transaction (even if it's legitimate one like for e.g. a big Reporting > "SELECT" query) beyond 10hrs, as that will end up consuming 10*200million > XID per hour= 2billion XID limit saturation and thus causing system > failure. Hope my understanding is correct here. > > On Thu, May 23, 2024 at 11:41 AM sud wrote: > >> >> On Thu, May 23, 2024 at 10:42 AM Muhammad Salahuddin Manzoor < >> salahuddi...@bitnine.net> wrote: >> >>> Greetings, >>> >>> Running `VACUUM table_name;` on a partitioned table will vacuum each >>> partition individually, not the whole table as a single unit. >>> >>> Yes, running `VACUUM table_name;` frequently on tables or partitions >>> with heavy DML is recommended. >>> >>> Regular `VACUUM` does not lock the table for reads or writes, so it >>> won't disrupt ongoing 24/7 data operations. >>> >>> "optimize autovacuum" >>> Yes. Adjust following parameters as per your system/environment >>> requirement >>> autovacuum_max_workers, >>> autovacuum_freeze_max_age , >>> autovacuum_vacuum_cost_delay >>> >>> Following need to be first tested thoroughly in a test environment. >>> Recommended Alert Threshold >>> Alert at 50% Usage: Set the alert threshold at 1 billion used XIDs. This >>> provides a significant buffer, giving you ample time to take corrective >>> action before reaching the critical limit. >>> >>> Calculation Rationale >>> Daily XID Usage: Approximately 4 billion rows per day implies high XID >>> consumption. >>> Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs >>> remaining, giving you roughly 12 hours to address the issue if your system >>> consumes 200 million XIDs per hour. >>> >>> >>> >> Thank you so much. That helps. >> So apart from setting these alerts on "Maximumusedtxnids" and making the >> vacuum optimized by tweaking above parameters, should we also need to have >> monitoring in place to ensure the Vacuum is not taking longer as compared >> to its normal runtime and also if it's getting blocked/failed by something? >> Like for example in our case where the select query was running longer , so >> the vacuum must not be able to succeed every time it attempts, so is it >> really worth having that level of alerting? and also how can we get an >> idea regarding if the vacuum is not succeeding or getting failed etc to >> avoid such upcoming issues? >> >>
Re: prevent users from SELECT-ing from pg_roles/pg_database
Greetings,
To prevent a user or role from selecting data from certain system tables in
PostgreSQL, you can revoke the default select permissions on those tables.
Here’s how you can do it:
1. Revoke SELECT permission on the system tables from the public role.
2. Grant SELECT permission only to specific roles that need it.
Here’s a step-by-step guide on how to achieve this:
Salahuddin.
On Fri, 24 May 2024, 20:52 Andreas Joseph Krogh, wrote:
> Hi, is there a way to prevent a user/role from SELECT-ing from certain
> system-tables?
>
>
>
> I'd like the contents of pg_{user,roles,database} to not be visible to all
> users.
>
>
>
> Thanks.
>
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
>
>
Re: prevent users from SELECT-ing from pg_roles/pg_database
Greetings,
Yes, you are correct. And
For applications/systems/scripts relying on this information may require
sgnificent modifications to handle the restricted access.
Alternative approches can be.
Auditing and monitoring.
You can use pgaudit extension for auditing and minitoring.
Use SE-PostgeSQL extension that give fine grained access control other than
PG standard permissions.
Try RLS row level security. May involve careful planning and may not cover
all use cases.
Regards,
Salahuddin.
On Fri, 24 May 2024, 22:02 Tom Lane, wrote:
> Andreas Joseph Krogh writes:
> > Hi, is there a way to prevent a user/role from SELECT-ing from certain
> > system-tables?
> > I'd like the contents of pg_{user,roles,database} to not be visible to
> all
> > users.
>
> As noted, you can in principle revoke the public SELECT grant from
> those views/catalogs. However, the system is not designed to hide
> such information, which means you'll have (at least) two kinds of
> issues to worry about:
>
> 1. Queries or tools breaking that you didn't expect to break.
> It's hardly uncommon for instance for queries on pg_class to
> join to pg_roles to get the owner names for tables.
>
> 2. Information leaks. For example, mapping role OID to role name
> is trivial with either regrole or pg_get_userbyid(), so it
> wouldn't take long to scan the plausible range of role OIDs and
> get all their names, even without SQL access to the underlying
> catalog.
>
> regards, tom lane
>
>
>
Re: Memory issues with PostgreSQL 15
Greetings, The error message you encountered, "could not fork autovacuum worker process: Cannot allocate memory," indicates that your PostgreSQL server attempted to start an autovacuum worker process but failed because the system ran out of memory. Steps to verify. 1 Check system available memory with commands. free -m top 2. Check PG configurations. shared_buffers --Typically 25% of total mem. work_mem maintenance_work_mem--For maintenance op like autovaccume create index etc. Increase it to 64MB or appropriate to your requirement. max_connections Monitor /var/log/messages file for errors. 2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device Check share memory limits. /etc/sysctl.conf kernel.shmmax = 68719476736 # Example value, adjust as needed kernel.shmall = 16777216 # Example value, adjust as needed Restart system and db Ensure you have enough disk space available check and monitor disk space with command df -h Reduce max_parallel_workers_per_gather = 2; If it is set to high value. I think setting up OS parameter. Increasing maintenance mem value and reducing max paralell workers xan help in solution. Regards, Salahuddin. On Tue, 28 May 2024, 21:40 Christian Schröder, wrote: > Hi all, > We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, > we have a lot of memory issues in our QA environment (which is a bit tense > in resources). We did not have these problems before the migration, and we > do not have them in our production environment, which has a lot more > memory. So, it is not super critical for us, but I would still like to > understand better how we can improve our configuration. > > Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The > database server is a dedicated server with 15 GB RAM (and 4 cores, if this > matters). > We used the following settings: > shared_buffers = 4GB > work_mem = 4MB > > After a while, we saw the following error in the logs: > > <2024-05-20 12:01:03 CEST - > LOG: could not fork autovacuum worker > process: Cannot allocate memory > > However, according to "free", a lot of memory was available: > > # free -m > totalusedfree shared buff/cache > available > Mem: 158824992 4634195 10427 > 6365 > Swap: 1999 2711728 > > Our Grafana charts showed a slow increase in memory consumption until it > plateaus at 4.66 GB. > We also found the following error: > > <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared > memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on > device > > I thought this could all be related to our "shared_buffers" setting, so I > increased it to 8 GB. This almost immediately (after a few minutes) gave me > these errors: > > <2024-05-27 11:45:59 CEST - > ERROR: out of memory > <2024-05-27 11:45:59 CEST - > DETAIL: Failed on request of size 201088574 > in memory context "TopTransactionContext". > ... > <2024-05-27 11:58:02 CEST - > ERROR: out of memory > <2024-05-27 11:58:02 CEST - > DETAIL: Failed while creating memory > context "dynahash". > <2024-05-27 11:58:02 CEST - > LOG: background worker "parallel worker" > (PID 21480) exited with exit code 1 > ... > <2024-05-27 12:01:02 CEST - > LOG: could not fork new process for > connection: Cannot allocate memory > <2024-05-27 12:01:03 CEST - > LOG: could not fork autovacuum worker > process: Cannot allocate memory > <2024-05-27 12:02:02 CEST - > LOG: could not fork new process for > connection: Cannot allocate memory > > Since this seemed worse than before, I changed the setting back to 4 GB. I > noticed that "free" now reports even more available memory: > > # free -m > totalusedfree shared buff/cache > available > Mem: 15882 621 3202256 14940 > 12674 > Swap: 1999 1991800 > > So, does the "shared_buffers" setting have the opposite effect than I > though? If I correctly remember similar discussions years ago, the database > needs both "normal" and shared memory. By increasing the "shared_buffers" > to 8 GB, I may have deprived it of "normal" memory. On the other hand, I > would have expected the remaining 7 GB to still be enough. > > At this point, I am out of ideas. I clearly seem to misunderstand how the > database manages its memory. This may have changed between 9.4 and 15, so > my prior knowledge may be useless. I definitely need some help. ☹ > > Thanks in advance, > Christian > > > -- > SUPPORT: > For any issues, inquiries, or assistance, please contact our support team > at supp...@wsd.com. Our dedicated team is available to help you and > provide prompt assistance. > > CONFID
Re: Unable to connect to any data source for foreign server
Greetings, While configuring ORG_FWD. I hope you are setting path like server name and absolute path to file on your network share and network share has appropriate permissions. '\\remote_server\path\to\file.xlsx' on network share Create user mapping if required. check your config through ogr_fdw_info -s "remote_server\\path\\to\\file.xlsx" -l To diagnose the issue you can check permissions, network accessability, path format should include server name and absolute file path and any compatability issues. Regards, Salahuddin. On Wed, 29 May 2024, 14:42 Russell Mercer, wrote: > Hi, > > I'm running into a problem connecting to a foreign server with my > Postgres database. I am using the OGR_FDW trying to connect to an Excel > "XLSX". I had this database on another server, and the foreign server > was set up and working properly there. I am trying to change the path > to the file which it is referencing, and I am now receiving an error. > In addition, I tried to just create a new foreign server to that same > "XLSX" file and it also gave the error of "Failed to connect to data > source". > > I used the OGR_FDW_INfo tool to make sure I had the foreign server > reference structured correctly, but it matches what I had before, and > didn't make a difference. > > I'm a bit lost as to where to look for possible solutions. One idea I > have is that perhaps it is a permissions issue with accessing other > files on the server where Postgres is installed, with the "postgres" user. > > Some basic info: > > Server: Windows Server 2022 > PostgreSQL 16.2 - Installed using Stack Builder > > Any information or ideas as to a solution, would be very much appreciated. > > Thanks, > Russell > > > >
Bugs details.
Greetings, I need to get detailed information about PG bugs and fixes. I can get the information about new features and Fixes in the release document but I find only descriptions for each fix. I need detailed information about these fixes. How can I get details about the fix. Suppose the release document says. *Fix INSERT from multiple VALUES rows into a target column that is a domain over an array or composite type (Tom Lane)* I want to get more details about this fix. How can I search this. I want to reproduce it and demonstrate the fix. For that I need detailed information. *Salahuddin (살라후딘**)*
Re: UPDATE with multiple WHERE conditions
Greetings,
You can use Temporary table. You could create a temporary table with one
column containing the condition values and then use it to update your main
table. This approach can be more flexible and cleaner than writing a script
with multiple update statements.
-- Create a temporary table with one column containing the condition values
CREATE TEMPORARY TABLE temp_conditions (condition_value TEXT);
-- Insert the condition values into the temporary table
INSERT INTO temp_conditions (condition_value) VALUES
('value1'),
('value2'),
('value3'),
-- Add more values as needed...
('value295');
-- Update the boolean column based on the condition values
UPDATE your_table
SET boolean_column = true
WHERE condition_column IN (SELECT condition_value FROM temp_conditions);
-- Clean up: drop the temporary table
DROP TABLE IF EXISTS temp_conditions;
*Salahuddin (살라후딘**)*
On Thu, 13 Jun 2024 at 02:28, Rich Shepard wrote:
> I have a table with 3492 rows. I want to update a boolean column from
> 'false' to 'true' for 295 rows based on the value of another column.
>
> Is there a way to access a file with those condition values? If not, should
> I create a temporary table with one column containing those values, or do I
> write a psql script with 295 lines, one for each row to be updated?
>
> TIA,
>
> Rich
>
>
>
