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
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)
RE: Re: day interval
The problematic code is:
select date(cm_curr_date) - date(CM_DATE) into diff_days from CMS_SYSPRM;
The fix is:
select date_part ('day', age( date(cm_curr_date), date(CM_DATE))) into
diff_days from CMS_SYSPRM;
The problem:
How to recreate the problem. (You know - QA).
Tried changing lc_time, timezone and datestyle .. but nothing seems to work
Thanks
Danny
-Original Message-
From: Adrian Klaver
Sent: Saturday, October 12, 2019 7:27 PM
To: Abraham, Danny ; Andrew Gierth
Cc: pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: day interval
On 10/12/19 8:37 AM, Abraham, Danny wrote:
> Thanks Andrew.
>
> My code fails since the expression (In a PG/PG SQL function) which
> assumes integer result Now produces the string '8 day';
The code is?
>
> 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)
>
>
>
--
Adrian Klaver
adrian.kla...@aklaver.com
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 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)
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: 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.
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 ?
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
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
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)?
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
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
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
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
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: 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&d=DwIFAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=rZ6QRG5QsrF-veoN0_ehDrT6IsQzka7-fVfEhCfor3I&m=LoZHLvaRhw48Dvx9n1OtgDV_SA0hse7hNTIl0fZh0RQ&s=u3c5XpUBsGhiPxGk5KFwOO_Aj-ToS4YN4pPBuVVZRGI&e=
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
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
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
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
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
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
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; $$
Can't change tcp_keepalives_idle
Hi, Change tcp_keepalives_idle in postgresql.conf from 0 to 300 , then pg_ctl reload but it stays with the old 0 value. Tried restart and it did not work. PG Log says it was changed. Reset_cal marked OK, but no real change ( ins how) -[ RECORD 1 ]---+- name| tcp_keepalives_idle setting | 0 unit| s category| Connections and Authentication / Connection Settings short_desc | Time between issuing TCP keepalives. extra_desc | A value of 0 uses the system default. context | user vartype | integer source | session min_val | 0 max_val | 2147483647 enumvals| boot_val| 0 reset_val | 1800 sourcefile | sourceline | pending_restart | f Thanks Danny
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
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 ?
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
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
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: 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
Re: [EXTERNAL] Re: Asking for OK for a nasty trick to resolve PG CVE-2025-1094 i
Explanation. We have hundreds of pg servers (mainly linux). App is 7×24. We think that patching the server to 15.12.will cost about 30 times more compared to patching the pg client ( mainly qa effort). The app working fine using [libpq, psql] on both Linux as well as Windows. Would love to hear your opinion. Thanks Danny Sent from Workspace ONE Boxer On Mar 6, 2025 10:11, Laurenz Albe wrote: [redirecting to pgsql-general] On Thu, 2025-03-06 at 07:39 +, Abraham, Danny wrote: > I have many customers using PG 15.3 happily, and I cannot just snap upgrade > them all to 15.12. Why do you think you cannot do that? In the long run, you'll be sorry if you don't. It is just a matter of replacing the software and restarting the database server. > I have tested a nasty trick of replacing PSQL,LIBPQ and several other DLL's > so that > I have a PG client 15.12 within the folders of Server 15.3. > > All working just fine. > > I plan to ship it as a patch - but would like to hear you opinion on this > "merge". > > (Of course, the next version will use PG 17.4, so this is just an SOS action). > > Directory of C:\Users\dbauser\Desktop\15.12 > > 02/20/2025 11:48 AM 4,696,576 libcrypto-3-x64.dll > 02/20/2025 11:48 AM 1,850,401 libiconv-2.dll > 02/20/2025 11:48 AM 475,769 libintl-9.dll > 02/20/2025 11:48 AM 323,584 libpq.dll > 02/20/2025 11:48 AM 779,776 libssl-3-x64.dll > 02/20/2025 11:48 AM52,736 libwinpthread-1.dll > 02/20/2025 11:48 AM 604,160 psql.exe > > == > C:\Program Files\BMC Software\Control-M Server\pgsql\bin>postgres -V > postgres (PostgreSQL) 15.3 > > C:\Program Files\BMC Software\Control-M Server\pgsql\bin>psql -V > psql (PostgreSQL) 15.12 There is nothing fundamentally evil about upgrading the client. But what is the point? Why are you worried about client bugs more than about server bugs? The latter are much more likely to eat your data. But then, if you are using Windows, perhaps you don't care a lot about your data... Yours, Laurenz Albe
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Thanks a lot. As you mentioned , I have not cleared the environment vars prior to the call. ==> dba-tlv-x6y64k% cat d2 unsetenv PGPORT unsetenv PGSYSCONFDIR unsetenv PGUSER unsetenv PGDATABASE unsetenv PGSERVICE unsetenv PGHOST psql -Upostgres -p5548 -dpostgres dba-tlv-x6y64k% source d2 psql (17.4) Type "help" for help. postgres=# \q ==> -Original Message- From: Adrian Klaver Sent: Thursday, April 10, 2025 12:00 AM To: Abraham, Danny ; David G. Johnston ; Francisco Olarte ; pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 13:40, Abraham, Danny wrote: > Thanks again. > 4. I am not aware of an env variable that changes the top down matching > on pg_hba.conf Should have added to previous post: From here: https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime-config-logging.html*RUNTIME-CONFIG-LOGGING-WHAT__;Iw!!PEZBYkTc!fcUdccPVwqMg5ysFSTgyVxuCPBLFdOMxwtMMj5-n6H2aJ4D-3IzJZsdkw3lk43mUrVgdNhgWhX0O-ZGdU-6PAaXcmFI$ a) Set log_connections and log_disconnections to on if they are not already and reload the server conf. b) Then look at Postgres log to see what the connection settings are for the connections in question. > > Danny > -- Adrian Klaver adrian.kla...@aklaver.com
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Thanks again. 1. No other PG instances on this Linux machine. 2. RHEL9 3. Our compiled PG which is embedded within our product/site 4. I am not aware of an env variable that changes the top down matching on pg_hba.conf Danny From: David G. Johnston Sent: Wednesday, April 9, 2025 10:34 PM To: Abraham, Danny Cc: Adrian Klaver ; Francisco Olarte ; pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On Wed, Apr 9, 2025, 12:21 Abraham, Danny mailto:danny_abra...@bmc.com>> wrote: Thanks again. Here is the example: >>>>>>>> dba-tlv-x6y64k% cat pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD local all all trust hostall all 0.0.0.0/0<https://urldefense.com/v3/__http:/0.0.0.0/0__;!!PEZBYkTc!e_x_LCIABYSpqVum5S6Hr0TtEs6P4NaumHqEqyXEa3ErrZTPMmnUeC33JmyNsqr1kGf2DxtZKMbJ_ofkSSjZQC4hsuUN$> scram-sha-256 dba-tlv-x6y64k% pg_ctl reload server signaled dba-tlv-x6y64k% psql -Upostgres Password for user postgres: psql (17.4) Type "help" for help. ctrlmdb=# >>>>>>> PSQL should not have asked for the password. Thanks Danny Suggests there are environment variables coming into play making non-default choices for you. Namely, making you not use local as the connection method. David J.
Cannot connect local with ttrust (pg_upgrade assumes it)
Hi, Asking for help with inability to connect local using trust Fail #local all all trust #local all dbauser peer OK #hostall all 127.0.0.1/32 trust
RE: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Thanks again. Here is the example: >>>>>>>> dba-tlv-x6y64k% cat pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD local all all trust hostall all 0.0.0.0/0 scram-sha-256 dba-tlv-x6y64k% pg_ctl reload server signaled dba-tlv-x6y64k% psql -Upostgres Password for user postgres: psql (17.4) Type "help" for help. ctrlmdb=# >>>>>>> PSQL should not have asked for the password. Thanks Danny -Original Message- From: Adrian Klaver Sent: Wednesday, April 9, 2025 8:49 PM To: Abraham, Danny ; Francisco Olarte Cc: pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 10:45 AM, Abraham, Danny wrote: > Failure eans going down the hba list and asking for the password. In pg_hba.conf first match wins, sounds like you have password auth line before the trust lines. You need to provide the complete pg_hba.conf auth lines you are working with. > > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny wrote: >> Asking for help with inability to connect local using trust Fail >> #local all all trust #local >> all dbauser peer OK #host all all >> 127.0.0.1/32 trust > > How are you connecting? Your symptoms matches confusing local with > TCP/IP to localhost. They are not the same thing. > Also, are you on windows? ( which does not have unix sockets ). > > local > This record matches connection attempts using Unix-domain sockets. > Without a record of this type, Unix-domain socket connections are > disallowed. > host > This record matches connection attempts made using TCP/IP. host > records match SSL or non-SSL connection attempts as well as GSSAPI > encrypted or non-GSSAPI encrypted connection attempts. > > On a unix host psql -h localhost will use the host line, psql -h > /socket/path will go to the local one, and no -h defaults to socket. > On a windows host it default to tcp/ip to localhost. > > Francisco Olarte. -- Adrian Klaver adrian.kla...@aklaver.com
FW: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Thanks again. Here is the example: >>>>>>>> dba-tlv-x6y64k% cat pg_hba.conf # TYPE DATABASEUSERCIDR-ADDRESS METHOD local all all trust hostall all 0.0.0.0/0 scram-sha-256 dba-tlv-x6y64k% pg_ctl reload server signaled dba-tlv-x6y64k% psql -Upostgres Password for user postgres: psql (17.4) Type "help" for help. ctrlmdb=# >>>>>>> PSQL should not have asked for the password. Thanks Danny -Original Message- From: Adrian Klaver Sent: Wednesday, April 9, 2025 8:49 PM To: Abraham, Danny ; Francisco Olarte Cc: pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it) On 4/9/25 10:45 AM, Abraham, Danny wrote: > Failure eans going down the hba list and asking for the password. In pg_hba.conf first match wins, sounds like you have password auth line before the trust lines. You need to provide the complete pg_hba.conf auth lines you are working with. > > > Sent from Workspace ONE Boxer > > On Apr 9, 2025 20:04, Francisco Olarte wrote: > On Wed, 9 Apr 2025 at 18:20, Abraham, Danny wrote: >> Asking for help with inability to connect local using trust Fail >> #local all all trust #local >> all dbauser peer OK #host all all >> 127.0.0.1/32 trust > > How are you connecting? Your symptoms matches confusing local with > TCP/IP to localhost. They are not the same thing. > Also, are you on windows? ( which does not have unix sockets ). > > local > This record matches connection attempts using Unix-domain sockets. > Without a record of this type, Unix-domain socket connections are > disallowed. > host > This record matches connection attempts made using TCP/IP. host > records match SSL or non-SSL connection attempts as well as GSSAPI > encrypted or non-GSSAPI encrypted connection attempts. > > On a unix host psql -h localhost will use the host line, psql -h > /socket/path will go to the local one, and no -h defaults to socket. > On a windows host it default to tcp/ip to localhost. > > Francisco Olarte. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Failure eans going down the hba list and asking for the password. Sent from Workspace ONE Boxer On Apr 9, 2025 20:04, Francisco Olarte wrote: On Wed, 9 Apr 2025 at 18:20, Abraham, Danny wrote: > Asking for help with inability to connect local using trust > Fail > #local all all trust > #local all dbauser peer > OK > #hostall all 127.0.0.1/32 trust How are you connecting? Your symptoms matches confusing local with TCP/IP to localhost. They are not the same thing. Also, are you on windows? ( which does not have unix sockets ). local This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed. host This record matches connection attempts made using TCP/IP. host records match SSL or non-SSL connection attempts as well as GSSAPI encrypted or non-GSSAPI encrypted connection attempts. On a unix host psql -h localhost will use the host line, psql -h /socket/path will go to the local one, and no -h defaults to socket. On a windows host it default to tcp/ip to localhost. Francisco Olarte.
Re: [EXTERNAL] Re: Cannot connect local with ttrust (pg_upgrade assumes it)
Thanks for the answers. All the 3 lines are, of course, uncommented for the test. I am using Linux, socket is in /tmp, i see the .s files , but local trust still does not connect. Sent from Workspace ONE Boxer On Apr 9, 2025 20:04, Francisco Olarte wrote: On Wed, 9 Apr 2025 at 18:20, Abraham, Danny wrote: > Asking for help with inability to connect local using trust > Fail > #local all all trust > #local all dbauser peer > OK > #hostall all 127.0.0.1/32 trust How are you connecting? Your symptoms matches confusing local with TCP/IP to localhost. They are not the same thing. Also, are you on windows? ( which does not have unix sockets ). local This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed. host This record matches connection attempts made using TCP/IP. host records match SSL or non-SSL connection attempts as well as GSSAPI encrypted or non-GSSAPI encrypted connection attempts. On a unix host psql -h localhost will use the host line, psql -h /socket/path will go to the local one, and no -h defaults to socket. On a windows host it default to tcp/ip to localhost. Francisco Olarte.
