Re: ERROR: invalid memory alloc request size when committing transaction
On Thu, 12 Aug 2021 at 06:42, Michael Harris wrote: > > Thanks Tom, > > > How many is "a large number"? > > 377k approx. I'm going to guess that it is the invalidation messages for all the DDL that is causing the memory allocation error. If you set wal_level = minimal then this might work. The total number indicates you are using too many partitions and should probably lower that by a factor of about 100. If you do that, you probably won't need to set wal_level. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: 3867653, EMM1 cluster issue on 23rd May--core generated --design feedback
On Mon, 12 Jul 2021 at 16:07, M Tarkeshwar Rao wrote: > > Hi All, > > We are getting following core with following use case: > > Case: We made max_connections as 15 and 10 different processes opening 15 > connections with server. It is running fine. But after few hours it giving > following > core. When we increase the max_connections then core come again but > it take more hours to come. > > Program terminated with signal 11, Segmentation fault. Sounds like a memory leak. In libpq programs this is often caused by forgetting to run PQclear(). -- Simon Riggshttp://www.EnterpriseDB.com/
Re: Getting pg_stat_database data takes significant time
On Wed, Aug 11, 2021 at 6:34 PM hubert depesz lubaczewski wrote: > > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > 1. this depends on reading the stats file; that's done once per > > transaction. So if you run the query twice in a transaction, the second > > time will take less time. You can know how much time is spent reading > > that file by subtracting both times. > > Yeah. I noticed. Looks like loading the stats file is the culprit. But > does that mean that the whole stats file has to be read at once? I just > need stats on db, not on relations? The backend will read and cache the per database file on all those calls for the current database, and it is read and cached as a whole, along with global and shared stats. Which database are you connected to? If you just want to look at the global stats, it might help to be connected to a database that is *not* the one with all the tables in -- e.g. connect to "postgres" and query pg_stat_database looking for values on a different database? In this case it would open files for "global", for "database postgres" and "shared relations" only and skip the file for your db with many objects. I think. > > 2. EXPLAIN (VERBOSE) will tell you which functions are being called by > > the query. One of those loops across all live backends. Is that > > significant? You could measure by creating an identical view but > > omitting pg_stat_db_numbackends. Does it take the same time as the > > view? If not, then you know that looping around all live backends is > > slow. > > Even `select datid, datname, xact_commit, xact_rollback from > pg_stat_database;` > takes (now), a second. Second call in the same connection, different txn, > 0.8s. > > Second call in the same transaction as first - 0.053ms. > > So it definitely suggests that loading the stats file is the problem. Yes, definitely. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
move data repository : server does not restart (windows 10)
Good morning, I want to move the data folder from default path to another drive/path. I made an attempt thaks to this link ressource : https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows I checked the 4 requirements. But the postgres server does not restart. Could you tell me the best way to do this action please ? Thanks a lot.
Re: move data repository : server does not restart (windows 10)
It would be helpful to see the logs from the failed start. Dave Cramer www.postgres.rocks On Thu, 12 Aug 2021 at 07:59, celati Laurent wrote: > Good morning, > > I want to move the data folder from default path to another drive/path. > I made an attempt thaks to this link ressource : > > > https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows > > I checked the 4 requirements. But the postgres server does not restart. > > > Could you tell me the best way to do this action please ? > > Thanks a lot. > >
Re: Getting pg_stat_database data takes significant time
On 2021-Aug-11, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > 1. this depends on reading the stats file; that's done once per > > transaction. So if you run the query twice in a transaction, the second > > time will take less time. You can know how much time is spent reading > > that file by subtracting both times. > > Yeah. I noticed. Looks like loading the stats file is the culprit. But > does that mean that the whole stats file has to be read at once? I just > need stats on db, not on relations? As I recall there is one file per database containing everything pertaining to that database, and you cannot read it partially. Maybe you can use stats_temp_directory to put these files in faster or less busy storage -- a RAM disk perhaps? -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "Those who use electric razors are infidels destined to burn in hell while we drink from rivers of beer, download free vids and mingle with naked well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 09:08:27AM -0400, Alvaro Herrera wrote: > On 2021-Aug-11, hubert depesz lubaczewski wrote: > > > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > > 1. this depends on reading the stats file; that's done once per > > > transaction. So if you run the query twice in a transaction, the second > > > time will take less time. You can know how much time is spent reading > > > that file by subtracting both times. > > > > Yeah. I noticed. Looks like loading the stats file is the culprit. But > > does that mean that the whole stats file has to be read at once? I just > > need stats on db, not on relations? > > As I recall there is one file per database containing everything > pertaining to that database, and you cannot read it partially. > > Maybe you can use stats_temp_directory to put these files in faster > or less busy storage -- a RAM disk perhaps? The fie is 120MB, and is stored in tmpfs, which I assume, on Linux, is ram disk. depesz
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > Which database are you connected to? If you just want to look at the > global stats, it might help to be connected to a database that is > *not* the one with all the tables in -- e.g. connect to "postgres" and > query pg_stat_database looking for values on a different database? In > this case it would open files for "global", for "database postgres" > and "shared relations" only and skip the file for your db with many > objects. I think. I'm connected to the db I need data about, and I need data from virtualy all columns of pg_stat_database. depesz
Re: Getting pg_stat_database data takes significant time
On Thu, Aug 12, 2021 at 4:38 PM hubert depesz lubaczewski wrote: > > On Thu, Aug 12, 2021 at 11:32:15AM +0200, Magnus Hagander wrote: > > Which database are you connected to? If you just want to look at the > > global stats, it might help to be connected to a database that is > > *not* the one with all the tables in -- e.g. connect to "postgres" and > > query pg_stat_database looking for values on a different database? In > > this case it would open files for "global", for "database postgres" > > and "shared relations" only and skip the file for your db with many > > objects. I think. > > I'm connected to the db I need data about, and I need data from virtualy > all columns of pg_stat_database. Try connecting to a different database, while still querying all columns. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
log_statement GUC parameter
Hi! Unfortunately, only a superuser can set log_statement='all'; Would it be possible to execute set session log_statement='all'; as an ordinary user? I am trying to execute it from login.sql, a part of login_hook extension which implements on-login triggers in PostgreSQL. I will create a procedure with security definer, owned by the role "postgres", and grant it to public. That should do the trick. However, it would be much nicer if PostgreSQL allowed me to set the parameter as a part of the normal session. The idea is to log all statements by the particular user, not by everybody. The user in question uses Weblogic 12.2.1.4 and creates a connection pool so I need to find out which statements are the longest running ones and make them perform. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: log_statement GUC parameter
On Thu, Aug 12, 2021 at 04:19:18PM -0400, Mladen Gogala wrote: > Hi! > > Unfortunately, only a superuser can set log_statement='all'; Would it be > possible to execute set session log_statement='all'; as an ordinary user? I > am trying to execute it from login.sql, a part of login_hook extension which > implements on-login triggers in PostgreSQL. I will create a procedure with > security definer, owned by the role "postgres", and grant it to public. That > should do the trick. However, it would be much nicer if PostgreSQL allowed > me to set the parameter as a part of the normal session. > > The idea is to log all statements by the particular user, not by everybody. > The user in question uses Weblogic 12.2.1.4 and creates a connection pool > so I need to find out which statements are the longest running ones and make > them perform. I think you can write a SECURITY DEFINER function that calls SET, call that function at login. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: log_statement GUC parameter
On 8/12/21 1:19 PM, Mladen Gogala wrote: Hi! The idea is to log all statements by the particular user, not by everybody. The user in question uses Weblogic 12.2.1.4 and creates a connection pool so I need to find out which statements are the longest running ones and make them perform. Would pg_stat_statements work for you?: https://www.postgresql.org/docs/current/pgstatstatements.html Regards -- Adrian Klaver adrian.kla...@aklaver.com
Re: log_statement GUC parameter
Mladen Gogala writes: > Unfortunately, only a superuser can set log_statement='all'; Would it be > possible to execute set session log_statement='all'; as an ordinary > user? I am trying to execute it from login.sql, a part of login_hook > extension which implements on-login triggers in PostgreSQL. I will > create a procedure with security definer, owned by the role "postgres", > and grant it to public. That should do the trick. However, it would be > much nicer if PostgreSQL allowed me to set the parameter as a part of > the normal session. If an ordinary user could manipulate that parameter, he could equally well hide his activities from the system log. Conversely, if the postmaster log setup is such that not a lot of volume is expected, then flooding it with extra traffic could create its own set of problems. Thus, basically all parameters that affect what is logged are superuser-only, and it'd be a hard sell to weaken that. The SECURITY DEFINER function approach is your best bet for poking local exceptions into that policy. regards, tom lane
Re: log_statement GUC parameter
On Thu, Aug 12, 2021 at 04:30:12PM -0400, Tom Lane wrote: > Mladen Gogala writes: > > Unfortunately, only a superuser can set log_statement='all'; Would it be > > possible to execute set session log_statement='all'; as an ordinary > > user? I am trying to execute it from login.sql, a part of login_hook > > extension which implements on-login triggers in PostgreSQL. I will > > create a procedure with security definer, owned by the role "postgres", > > and grant it to public. That should do the trick. However, it would be > > much nicer if PostgreSQL allowed me to set the parameter as a part of > > the normal session. > > If an ordinary user could manipulate that parameter, he could equally > well hide his activities from the system log. Conversely, if the > postmaster log setup is such that not a lot of volume is expected, > then flooding it with extra traffic could create its own set of > problems. Thus, basically all parameters that affect what is logged > are superuser-only, and it'd be a hard sell to weaken that. The > SECURITY DEFINER function approach is your best bet for poking local > exceptions into that policy. The nice thing about SECURITY DEFINER is that the super user controls what values it can be set to. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: log_statement GUC parameter
[ dept. of second thoughts ] Adrian Klaver writes: > On 8/12/21 1:19 PM, Mladen Gogala wrote: >> The idea is to log all statements by the particular user, not by >> everybody. > Would pg_stat_statements work for you?: > https://www.postgresql.org/docs/current/pgstatstatements.html Actually, for that specific requirement, there's an easier way: ALTER USER target_user SET log_statement = 'all'; While the target_user can't do that for himself, a superuser can. regards, tom lane
Re: log_statement GUC parameter
On Thu, Aug 12, 2021 at 04:37:16PM -0400, Tom Lane wrote: > [ dept. of second thoughts ] > > Adrian Klaver writes: > > On 8/12/21 1:19 PM, Mladen Gogala wrote: > >> The idea is to log all statements by the particular user, not by > >> everybody. > > > Would pg_stat_statements work for you?: > > https://www.postgresql.org/docs/current/pgstatstatements.html > > Actually, for that specific requirement, there's an easier way: > > ALTER USER target_user SET log_statement = 'all'; > > While the target_user can't do that for himself, a superuser > can. Ah, yes, I hadn't considered that ALTER USER bypasses those user checks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: log_statement GUC parameter
Hi Bruce Yes, I can. I have already done so and it works. I wrote a procedure because of my Oracle background, but it doesn't really matter. This was just a suggestion for the session settable parameters. Regards On 8/12/21 4:23 PM, Bruce Momjian wrote: I think you can write a SECURITY DEFINER function that calls SET, call that function at login. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: log_statement GUC parameter
Thank you Tom! It turns out that the Oracle way of doing things (SECURITY DEFINER) was the wrong way here. Thanks a bunch. On 8/12/21 4:37 PM, Tom Lane wrote: ctually, for that specific requirement, there's an easier way: ALTER USER target_user SET log_statement = 'all'; While the target_user can't do that for himself, a superuser can. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Partitioning a table by integer value (preferably in place)
Hi all - I resolved my issue - and took a 25 minute query down to 5 seconds.
> I have a 400GB joining table (one SMALLINT and the other INTEGER -
> What I would like to do is to partition by the SMALLINT (1 - 1000)
> value - which would give 1,000 tables of 400MB each.
I found this site very helpful for explaining the basics of
partitioning (LIST, RANGE and HASH):
https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql
I then found this absolute beaut of a site which was like manna from heaven:
https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts/
which explained (they had a different issue - I adapted the code) how
what I required can be done entirely from the psql client without the
need for bash or PL/pgSQL or anything else. The "trick" here is to
combine the FORMAT function with GENERATE_SERIES as follows (code from
site):
CREATE TABLE test_ranged (id serial PRIMARY KEY, payload TEXT)
partition BY range (id);
SELECT FORMAT ('CREATE TABLE %I partition OF test_ranged FOR VALUES
FROM (%s) to (%s);', 'test_ranged_' || i, i, i+1) FROM
generate_series(1, 2) i \gexec
Output of this (having removed \gexec - another thing I learnt):
format
-
CREATE TABLE test_ranged_1 partition OF test_ranged FOR VALUES FROM (1) to (2);
CREATE TABLE test_ranged_2 partition OF test_ranged FOR VALUES FROM (2) to (3);
So, I generated the series for (1, 1000) with my own fields using the
LIST method with a single INTEGER value in the list. Ran the script -
had my 1000 partitions in a matter of seconds. Loading them (750GB
with indexes) was an overnight job however - but that's not
PostgreSQL's fault! :-)
I really love the way that PostgreSQL/psql is so flexible that it's
possible to do heaps of stuff without having to resort to other tools.
I posted this answer to my own question in the hope that it may help
others in my situation. If I haven't been clear, or there's something
missing, please let me know - or add your own opinions/experience if
there's an alternative which may or may not be as efficient. I'm
trying to collect as many strings to my bow as possible!
Rgs,
Pól...
