set local statement_timeout within a stored procedure
Hi, Is there a way to integrate " set local statement_timeout" in a stored procedure? Something like create or replace procedure call_sp() language plpgsql as $$ begin set local statement_timeout = 1; call sp(); end; $$
Base files compatibility between PG11 and PG15
Hi, I have a database on Aurora@RDS. It Used V11.9, and then upgraded automatically by AWS to V15.2 AWS states that it relies on compatibility provided by the PG community. We now experience problems with the data. Has anyone met similar issues? Compatibility of data files? Thanks Danny
RE: Re: WARNING: skipping "pg_database" --- only superuser can analyze it
Right to the point ==> set client_min_messages=error; Solved it Thanks -Original Message- From: Tom Lane Sent: Monday, June 28, 2021 5:53 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: WARNING: skipping "pg_database" --- only superuser can analyze it "Abraham, Danny" writes: > Good old "analyze;" will run smoothly on 11.5... > But on 12.6 will skip and warn on any pg_catalog/information_schema table. Hm, for me there are such warnings on 11.x too. Not as many --- it looks like indexes are mentioned as well in 12.x. But it's never been the case that this was entirely silent for an unprivileged user. > Questions: 1. Has the behavior changed in 12.6? 2.How can I analyze on > public tables (No syntax like analyze schema=public) If you don't want to see the warnings, consider set client_min_messages to error; regards, tom lane
WARNING: skipping "pg_database" --- only superuser can analyze it
Hi, Good old "analyze;" will run smoothly on 11.5... But on 12.6 will skip and warn on any pg_catalog/information_schema table. Pg_catalog is on Usage only on both databases (11.5 and 12.6). Questions: 1. Has the behavior changed in 12.6? 2.How can I analyze on public tables (No syntax like analyze schema=public) Thanks Danny
RE: Re: Reuse of REF Cursor
I speculate that I am in the good old problem .. of a very slow plan of NOT IN (We used to convert it to NOT EXISTS in V8). Is this planner issue still in V9? Has the planner fixed for it in V10? Thanks Danny
RE: Re: Reuse of REF Cursor
2021-04-09 08:00:08.692 IDTERROR: canceling statement due to statement timeout 2021-04-09 08:00:08.692 IDTCONTEXT: PL/pgSQL function orhpans_active_clean_table(character varying,integer) line 42 at FETCH PL/pgSQL function orhpans_active_removal() line 31 at assignment PL/pgSQL function ajf_backup(integer) line 39 at assignment Can a FETCH fail if the table is locked? The FETCH is stuck for the time. Should I lock all tables involved with the query? Any specific time-out on the fetch? Or should I use the general statement-timeout? I mean move from regular programming mode to paranoidic mode The failure is inconsistent.. Never fails in PG 11.5, but fails in PG9.5.5 about once a week... I need a full understanding of the problem in order to force big,slow customers to migrate to PG11.5. Thanks Danny
RE: Re: Reuse of REF Cursor
stmt := 'select count(*) from ' || table_name;
open C1 for execute stmt;
fetch C1 into rc;
close C1;
if (debug_level = 1) then
if rc > 0 then
perform diag_print(func_name,format('Counted %s records in table
%s',rc,table_name) );
else
perform diag_print(func_name,format('Table %s is empty.',table_name) );
return 0;
end if;
end if;
stmt := 'select count(*) from ' || table_name || ' where orderno not in (
select orderno from cmr_ajf) ' ;
if lower(table_name) = 'cmr_setvar' then
stmt := stmt || ' and orderno <> 0';
end if;
open C1 for execute stmt;
fetch C1 into rc; <= Sometimes fail here
close C1;
if rc>0 then
Should I use a different variable for every refcursor usage ?
Reuse of REF Cursor
Has anybody faced a problem with reusing a ref cursor? C1 refcursor; stmt := 'select1 ' ; open C1 for execute stmt; fetch C1 into rc1; close C1; stmt := 'select2 ...' ; open C1 for execute stmt; fetch C1 into rc2; close C1; ??? I am getting inconsistent errors: IDTCONTEXT: PL/pgSQL function .. ) line 42 at FETCH PG Version 9.5.5 on Linux. Thanks Danny
PKEY getting corrupted
Hi, We have seen several times a situation where a PKEY is compromised and duplicate values are created within a table. This has happened so far on PG 928 on Linux and Windows, and also on PG955 on AIX. We ran massive test on PG10.4 but it has not recreated the problem. Has anyone faced the same issue? Any known bug? Thanks Danny
RE: Re: PG 9.5.5 cores on AIX 7.1
Customer is using 10.4 , not 9.5.5. Does the same argument apply for upgrading to 10.12 ? Thanks Danny -Original Message- From: Tom Lane Sent: Sunday, July 19, 2020 6:04 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: PG 9.5.5 cores on AIX 7.1 "Abraham, Danny" writes: > Slow machine, high stress. > I think/hope that PG is the victim of an overstressed machine. > Has anyone faced this issue in the past? Not to point out the obvious, but you're evidently using parallel queries, which was a brand new thing in 9.5; and it had its share of teething problems. I can't say whether updating to current (9.5.22) would fix this particular issue, but it would definitely fix a bunch of instabilities in that general area. regards, tom lane
PG 9.5.5 cores on AIX 7.1
Slow machine, high stress. I think/hope that PG is the victim of an overstressed machine. Has anyone faced this issue in the past? Thanks Danny Segmentation fault in _alloc_initial_pthread at 0x9521474 0x9521474 (_alloc_initial_pthread+0x1d4) e803 ld r0,0x0(r3) (dbx) where _alloc_initial_pthread(??) at 0x9521474 __pth_init(??) at 0x951f390 uload(??, ??, ??, ??, ??, ??) at 0x9fff000ab70 load1(??, ??, ??, ??) at 0x9000b74 load(??, ??, ??) at 0x9001ef0 loadAndInit(??, ??, ??) at 0x905b38c dlopen(??, ??) at 0x909bfe0 internal_load_library(??) at 0x10014c684 RestoreLibraryState(??) at 0x10014d79c ParallelWorkerMain(??) at 0x1000bb2d0 StartBackgroundWorker() at 0x10026cd94 maybe_start_bgworkers() at 0x10003834c sigusr1_handler(??) at 0x10003902c __fd_select(??, ??, ??, ??, ??) at 0x91567fc ServerLoop() at 0x1004cec90 PostmasterMain(??, ??) at 0x10003a4e8 main(??, ??) at 0x108f8
BUG #11141: Duplicate primary key values corruption
Hi, PG 9.5.5 on AIX tends to compromise Primary Key and create duplicates when in stress and many concurrent threads updating. Is it BUG #11141 ( Duplicate primary key values corruption ). ? Anyone aware of a resolution for this ? B.T.W - Looks like it happens less on Linux. Thanks Danny
RE: Re: Very frequent "Too many clients" eventually crashes postmaster
Will setting max_parallel_workers_per_gather to 0 (In fact, no parallel queries) Solve this issue? I can sustain the slowness. We are on 11.5, and the fix is on 11.6. -Original Message- From: Tom Lane Sent: Sunday, May 03, 2020 5:37 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: Very frequent "Too many clients" eventually crashes postmaster "Abraham, Danny" writes: > Is there a way to eliminate postmaster crash/core ? > A surge in short-term connections eventually crashes PG on "no free slots in > PMChildFlags array". Update to a current release, maybe? We fixed a bug with symptoms like that last fall [1]. regards, tom lane [1] https://urldefense.proofpoint.com/v2/url?u=https-3A__git.postgresql.org_gitweb_-3Fp-3Dpostgresql.git-26a-3Dcommitdiff-26h-3D3887e9455=DwIFAg=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E=rZ6QRG5QsrF-veoN0_ehDrT6IsQzka7-fVfEhCfor3I=LoZHLvaRhw48Dvx9n1OtgDV_SA0hse7hNTIl0fZh0RQ=u3c5XpUBsGhiPxGk5KFwOO_Aj-ToS4YN4pPBuVVZRGI=
Very frequent "Too many clients" eventually crashes postmaster
Is there a way to eliminate postmaster crash/core ? A surge in short-term connections eventually crashes PG on "no free slots in PMChildFlags array". Happens on AIX 7.2 =>grep "2020-05-02 08:16" * | grep clients | wc -l 0 =>grep "2020-05-02 08:17" * | grep clients | wc -l 0 =>grep "2020-05-02 08:18" * | grep clients | wc -l 309 =>grep "2020-05-02 08:19" * | grep clients | wc -l 603 =>grep "2020-05-02 08:20" * | grep clients | wc -l 288 =>grep "2020-05-02 08:21" * | grep clients | wc -l 230 2020-05-02 08:21:27.653 CDTFATAL: no free slots in PMChildFlags array
RE: Re: too many clients already
Agree. I suspect that this is a mal configured pgpool - the developer thinks that the pool is reusing connections, While it is, in fact, reopening them. -Original Message- From: Tom Lane Sent: Thursday, April 02, 2020 7:40 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already "Abraham, Danny" writes: > Well, I guess the questions is - how do I optimize PG for a stream of very > short life checks... You should be using a connection pooler for a load like that. PG backends are fairly heavyweight things --- you don't want to fire one up for just a single query, at least not when there are many such queries per second. I think pgbouncer and pgpool are the most widely used options, but this is a bit outside my expertise. regards, tom lane
RE: Re: too many clients already
Well, I guess the questions is - how do I optimize PG for a stream of very short life checks... See below: 2020-04-02 11:05:37.010 CDTLOG: connection received: host=10.64.72.157 port=45799 2020-04-02 11:05:37.014 CDTLOG: connection received: host=10.64.72.157 port=45814 2020-04-02 11:05:37.014 CDTLOG: connection received: host=10.64.72.157 port=45813 2020-04-02 11:05:37.018 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.015 CDTLOG: connection received: host=10.64.72.157 port=45815 2020-04-02 11:05:37.015 CDTLOG: connection received: host=10.64.72.157 port=45817 2020-04-02 11:05:37.015 CDTLOG: connection received: host=10.64.72.157 port=45809 2020-04-02 11:05:37.015 CDTLOG: connection received: host=10.64.72.157 port=45818 2020-04-02 11:05:37.016 CDTLOG: connection received: host=10.64.72.157 port=45819 2020-04-02 11:05:37.021 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.021 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.021 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.021 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.021 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.022 CDTFATAL: sorry, too many clients already 2020-04-02 11:05:37.022 CDTFATAL: sorry, too many clients already -Original Message- From: Tom Lane Sent: Thursday, April 02, 2020 6:52 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already "Abraham, Danny" writes: > Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: > sorry, too many clients already" > and transient difficulty to log in. > Happens on all PG versions (Tested 9.5,10.4,11.5) Big installation: > max_connections is 1200, shared_buffers is 2GB > But .. select count(*) from pg_stat_activity is only 66. I'd be suspicious that there are a lot of clients stuck in connection startup (likely the authentication phase); those connections aren't going to show in pg_stat_activity until they finish connecting. The "ps" suggestion Adrian gave you would not show them either, because they're not going to say "idle". Enabling log_connections and watching the postmaster log would help prove or disprove that theory. regards, tom lane
RE: Re: too many clients already
va-tlv-ctm-qa22.isr.bmc.com% sql psql: FATAL: sorry, too many clients already va-tlv-ctm-qa22.isr.bmc.com% sql psql (11.5) Type "help" for help. ctrlmdb=> show max_connections; max_connections - 1200 (1 row) ctrlmdb=> show shared_buffers; shared_buffers 2000MB (1 row) -Original Message- From: Adrian Klaver Sent: Thursday, April 02, 2020 6:37 PM To: Abraham, Danny ; pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already On 4/2/20 8:35 AM, Abraham, Danny wrote: > Big installation: max_connections is 1200, shared_buffers is 2GB Have you confirmed that the above is actually in effect by doing?: show max_connections; > > -Original Message- > From: Adrian Klaver > Sent: Thursday, April 02, 2020 6:30 PM > To: Abraham, Danny ; pgsql-gene...@postgresql.org > Subject: [EXTERNAL] Re: too many clients already > > On 4/2/20 8:22 AM, Abraham, Danny wrote: >> No pg-bouncer or connection pooling. >> ps -elf | grep postgres | grep idle | wc -l ==>61 >> >> and BTW: Running, say 500 one command psql in parallel will have the same >> affect.. > > Hmm. In psql on the cluster in question what does below return?: > > show max_connections; > >> >> -----Original Message- >> From: Rob Sargent >> Sent: Thursday, April 02, 2020 6:10 PM >> To: Abraham, Danny >> Cc: pgsql-gene...@postgresql.org >> Subject: [EXTERNAL] Re: too many clients already >> >> >> >>> On Apr 2, 2020, at 9:06 AM, Abraham, Danny wrote: >>> >>> Hi, >>> >>> Will appreciate a hint here. >>> >>> Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: >>> sorry, too many clients already" >>> and transient difficulty to log in. >>> >>> Happens on all PG versions (Tested 9.5,10.4,11.5) >>> >>> Big installation: max_connections is 1200, shared_buffers is 2GB >>> >>> But .. select count(*) from pg_stat_activity is only 66. >>> >>> Thanks >>> >>> Danny >>> >>> >> Lots of idle, kept-alive clients? Do you have a connection pooler (e.g. >> pg-bouncer)? >> >> > > -- Adrian Klaver adrian.kla...@aklaver.com
RE: Re: too many clients already
Big installation: max_connections is 1200, shared_buffers is 2GB -Original Message- From: Adrian Klaver Sent: Thursday, April 02, 2020 6:30 PM To: Abraham, Danny ; pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already On 4/2/20 8:22 AM, Abraham, Danny wrote: > No pg-bouncer or connection pooling. > ps -elf | grep postgres | grep idle | wc -l ==>61 > > and BTW: Running, say 500 one command psql in parallel will have the same > affect.. Hmm. In psql on the cluster in question what does below return?: show max_connections; > > -Original Message- > From: Rob Sargent > Sent: Thursday, April 02, 2020 6:10 PM > To: Abraham, Danny > Cc: pgsql-gene...@postgresql.org > Subject: [EXTERNAL] Re: too many clients already > > > >> On Apr 2, 2020, at 9:06 AM, Abraham, Danny wrote: >> >> Hi, >> >> Will appreciate a hint here. >> >> Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: >> sorry, too many clients already" >> and transient difficulty to log in. >> >> Happens on all PG versions (Tested 9.5,10.4,11.5) >> >> Big installation: max_connections is 1200, shared_buffers is 2GB >> >> But .. select count(*) from pg_stat_activity is only 66. >> >> Thanks >> >> Danny >> >> > Lots of idle, kept-alive clients? Do you have a connection pooler (e.g. > pg-bouncer)? > > -- Adrian Klaver adrian.kla...@aklaver.com
RE: Re: too many clients already
No pg-bouncer or connection pooling. ps -elf | grep postgres | grep idle | wc -l ==>61 and BTW: Running, say 500 one command psql in parallel will have the same affect.. -Original Message- From: Rob Sargent Sent: Thursday, April 02, 2020 6:10 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: too many clients already > On Apr 2, 2020, at 9:06 AM, Abraham, Danny wrote: > > Hi, > > Will appreciate a hint here. > > Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: > sorry, too many clients already" > and transient difficulty to log in. > > Happens on all PG versions (Tested 9.5,10.4,11.5) > > Big installation: max_connections is 1200, shared_buffers is 2GB > > But .. select count(*) from pg_stat_activity is only 66. > > Thanks > > Danny > > Lots of idle, kept-alive clients? Do you have a connection pooler (e.g. pg-bouncer)?
too many clients already
Hi, Will appreciate a hint here. Running on a big and stressed AIX platform and receiving lots of "CDTFATAL: sorry, too many clients already" and transient difficulty to log in. Happens on all PG versions (Tested 9.5,10.4,11.5) Big installation: max_connections is 1200, shared_buffers is 2GB But .. select count(*) from pg_stat_activity is only 66. Thanks Danny
Is there any tool that provides Physical Backup plus PITR for a single database ( Not the whole PG instance ) ?
I assume that this magic does not exist. Am I right ? Thanks Danny
Is there any tool that provides Physical Backup plus PITR for a single database ( Not the whole PG instance ) ?
I assume that this magic does not exist. Am I right ?
RE: Re: date function bug
20181501 is illegal. Working OK.
ctrlmdb=> select to_date('20181501','MMDD')
ctrlmdb-> \g
ERROR: date/time field value out of range: "20181501"
From: Ravi Krishna
Sent: Wednesday, October 23, 2019 5:28 PM
To: Abraham, Danny ; pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: date function bug
> ctrlmdb=> select to_date('2018100X','MMDD');
> to_date
>2018-10-01
>(1 row)
I am able to reproduce this in 11.5 It seems PG can take a single digit for Day
too.
select to_date('2018109','MMDD') produces 2018-10-09.
date function bug
Hi,
The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?
ctrlmdb=> select to_date('2018100X','MMDD');
to_date
2018-10-01
(1 row)
RE: Re: day interval
Thanks everyone.
EDB installs oracle compatible parameters.
See below
C:\Program Files\edb\as9.6\bin>psql -p5488 postgres enterprisedb
Password for user enterprisedb:
psql (9.6.2.7)
...
### Oracle compatible mode
postgres=# select date('20191001') - date('20190101');
?column?
--
273 days
(1 row)
## Postgres compatible mode
postgres=# set edb_redwood_date=off;
SET
postgres=# select date('20191001') - date('20190101');
?column?
--
273
(1 row)
-Original Message-
From: Andrew Gierth
Sent: Saturday, October 12, 2019 8:48 PM
To: Abraham, Danny
Cc: Adrian Klaver ; pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: day interval
>>>>> "Abraham" == Abraham, Danny writes:
Abraham> Thanks for the clarification.
Abraham> The problem is still this:
Abraham> select date('20191001') - date('20190101') ; Abraham> in my servers
it is always '273'.
Abraham> In the customer's DB it is '273 days';
Then you need to establish why that is.
For example, try these in psql on the customer's db and show us the
outputs:
\dT *.date
\df *.date
select castsource::regtype,
casttarget::regtype,
castfunc::regprocedure,
castcontext,
castmethod
from pg_cast c join pg_type t on (casttarget=t.oid) where typname='date';
select oprresult::regtype
from pg_operator
join pg_type t1 on (t1.oid=oprleft)
join pg_type t2 on (t2.oid=oprright) where oprname='-' and
t1.typname='date' and t2.typname='date';
--
Andrew (irc:RhodiumToad)
RE: Re: day interval
Thanks for the clarification.
The problem is still this:
select date('20191001') - date('20190101') ;
in my servers it is always '273'.
In the customer's DB it is '273 days';
Thanks
Danny
-Original Message-
From: Andrew Gierth
Sent: Saturday, October 12, 2019 7:53 PM
To: Abraham, Danny
Cc: Adrian Klaver ; pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: day interval
>>>>> "Abraham" == Abraham, Danny writes:
Abraham> The problematic code is:
Abraham> select date(cm_curr_date) - date(CM_DATE) into diff_days from
CMS_SYSPRM;
This will always return an integer, unless either the date() cast or the
-(date,date) operator have been redefined or modified.
Abraham> The fix is:
Abraham> select date_part ('day', age( date(cm_curr_date), date(CM_DATE)))
into diff_days from CMS_SYSPRM;
This doesn't do the same thing, it will give a different result if the dates
differ by a month or more.
Abraham> The problem:
Abraham> How to recreate the problem. (You know - QA).
Abraham> Tried changing lc_time, timezone and datestyle .. but nothing
Abraham> seems to work
None of these things can affect data types.
--
Andrew (irc:RhodiumToad)
RE: Re: day interval
Thanks Andrew.
My code fails since the expression (In a PG/PG SQL function) which assumes
integer result
Now produces the string '8 day';
This has been working for years on all PG community servers.
This happens on an EDB PG 9.6.3.
I know the fix, but I need the ability to create the bug in my server, and I do
not know how.
Thanks
Danny
-Original Message-
From: Andrew Gierth
Sent: Saturday, October 12, 2019 6:26 PM
To: Abraham, Danny
Cc: pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: day interval
>>>>> "Abraham" == Abraham, Danny writes:
Abraham> Hi
Abraham> A question on day interval
Abraham> select date('20191001') - date('20190923');
Abraham> Will provide sometimes '8' - an integer , but sometimes '8 Abraham>
day' - a string
No, it'll always return an integer. You will only get an interval result if you
subtract timestamps rather than dates, for example if one of the operands is
actually an expression returning a timestamp.
Give an example of an actual expression you used that returned an interval
instead, and we may be able to tell you how to fix it.
--
Andrew (irc:RhodiumToad)
day interval
Hi
A question on day interval
select date('20191001') - date('20190923');
Will provide sometimes '8' - an integer , but sometimes '8 day' - a string
How can I control it to return integer always?
Thanks
Danny
psql output in Japanese Code Page
Hi, While under Code Page 932 (=Japanese) the old PSQL 9.5.5 - used to talk plain English, like in \d This behavior has changed in PSQL 10. We are now forced to run "chcp 1252" before any PSQL activity. Does anyone know about it? Any suggestions? I tried \encoding WIN1252. Did not work. Thanks Danny Abraham
