RE: Huge archive log generate in Postgresql-13

[Ram Pratap Maurya]

Dear Adrian,



We are using binary replication and par day  pg_log_archive total size is 
increase after upgrade for PG11 to PG13.







Regards,

Ram Pratap.



-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: 19 April 2022 22:51
To: Ram Pratap Maurya ; 
pgsql-gene...@postgresql.org
Subject: Re: Huge archive log generate in Postgresql-13



On 4/18/22 21:41, Ram Pratap Maurya wrote:

> Dear Adrian,

>

> If we set  parameter *"PG-WAL  = replica"*  in PG13 conf file , there

> is any issue  in replication through PG_WAL  and pg_log_archive log.

>



In versions of Postgres from 9.6+ your setting of hot_standby is mapped to the 
replica setting so it is really not a change. In other words the hot_standby 
setting = replica setting as of 9.6. I made the suggestion to change just so 
the conf setting matches the choices in current documentation. Less confusion 
for someone looking through the postgresql.conf file and  then looking up the 
values in the documentation.





As to your replication is that binary replication or logical replication?



Also you have not said where you are measuring the size growth in WAL files?



>

> Regards,

>

> Ram Pratap.

>

> -Original Message-

> From: Ram Pratap Maurya

> Sent: 19 April 2022 09:00

> To: Adrian Klaver 
> mailto:adrian.kla...@aklaver.com>>;

> pgsql-gene...@postgresql.org

> Cc: Manu Saxena 
> mailto:manu.sax...@lavainternational.in>>

> Subject: RE: Huge archive log generate in Postgresql-13

>

> Dear Adrian,

>

> We have two replica system one is Slave and other id DR server .

>

> Salve server replicate from PG_WAL and DR system replicate from

> pg_log_archive.

>

> Can you please suggest what changes need to required in PG13 conf file.

>

> Regards,

>

> Ram Pratap.

>

> -Original Message-

>

> From: Adrian Klaver [mailto:adrian.kla...@aklaver.com

> ]

>

> Sent: 18 April 2022 21:30

>

> To: Ram Pratap Maurya  >;

> pgsql-gene...@postgresql.org 
> 

>

> Cc: Manu Saxena  >

>

> Subject: Re: Huge archive log generate in Postgresql-13

>

> On 4/18/22 04:34, Ram Pratap Maurya wrote:

>

>  > Hi Support,

>

>  >

>

>  > We have upgraded postgresql DB from version 11 to 13 .  after

> upgrade

>

>  > to

>

>  > 13  huge archive log generate in system .

>

>  >

>

>  > Before upgrade  per day 120GB to 150 GB log generated but after

>

>  > upgrade per day approx. 250 to 300 GB log generated.

>

> Where are you measuring this in the WAL directory or the archive directory?

>

> Do you have replication set up from this server and if so what type?

>

> FYI, the wal_level setting of hot_standby is deprecated and maps to

> replica since version 9.6. At some point you might want to change to

> match current documentation.

>

>  >

>

>  > Can you please suggest why huge archive log generated after upgrade

>

>  >   there any configure setting or this is Postgresql-13 behaviour.

>

>  >

>

>  > Postgresql-13 Postgresql conf file attached for your references.

>

>  >

>

>  > Regards,

>

>  >

>

>  > Ram Pratap.

>

>  >

>

> --

>

> Adrian Klaver

>

> adrian.kla...@aklaver.com 
> 

>





--

Adrian Klaver

adrian.kla...@aklaver.com

Re: Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Tom Lane]

"David G. Johnston"  writes:
> Might I suggest the following:
> + /*
> + * For each action, modify procForm to type-safely set the new value.
> + * However, because the SET clause is repeatable we handle it
> + * a bit differently, modifying the underlying tuple directly.  So
> + * make sure to leave that conditional block for last.
  + */

Actually, the reason proconfig is handled differently is that it's
a variable-length field, so it can't be represented in the C struct
that we overlay onto the catalog tuple to access the fixed-width
fields cheaply.  I'm not sure that insisting that that stanza be
last is especially useful advice for future hackers, because someday
there might be more than one variable-length field that this function
needs to update.

regards, tom lane

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[David G. Johnston]

On Tue, Apr 19, 2022 at 7:47 PM Julien Rouhaud  wrote:

>
> On Tue, Apr 19, 2022 at 07:21:19PM -0700, David G. Johnston wrote:
> > On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn 
> wrote:
> >
>
> > > *alter function s1.f()security invokerset timezone = 'UTC'stable*
> > > *parallel safe*
> > > *;*
> > >
> > > It brings this new status:
> > >
> > >
> > >
> > > * name | type | security |proconfig
> > > | volatility
> > > |  parallel
> --+--+--+-++
> f
> > >| func | invoker  | {TimeZone=UTC}
> > >| stable | restricted*
> > >
> > > This is the bug.
> > >
> >
> > It has room for improvement from a user experience perspective.
> >
> > While I haven't experimented with this for confirmation, what you are
> > proposing here (set + parallel safe) is an impossible runtime combination
> > (semantic rule) but perfectly valid to write syntactically.  Your
> function
> > must either be restricted or unsafe per the rules for specifying parallel
> > mode.
> >



>
> That's not the problem here though, as you can still end up with the wanted
> result using 2 queries.  Also, the PARALLEL part is entirely ignored, so
> if you
> wanted to mark the function as PARALLEL UNSAFE because you're also doing a
> SET
> that would make it incompatible it would also be ignored, same if you use a
> RESET clause.
>
> AFAICT the problem is that SET / RESET part is messing with the HeapTuple,
> so
> you can't use the procForm reference afterwards.  Simply processing
> parallel_item before set_items fixes the problem, as in the attached.
>

Thank you for the explanation.

Might I suggest the following:

diff --git a/src/backend/commands/functioncmds.c
b/src/backend/commands/functioncmds.c
index 91f02a7eb2..2790c64121 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1416,12 +1416,20 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt
*stmt)
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
}

+   /*
+* For each action, modify procForm to type-safely set the new
value.
+* However, because the SET clause is repeatable we handle it
+* a bit differently, modifying the underlying tuple directly.  So
+* make sure to leave that conditional block for last.
+*/
if (volatility_item)
procForm->provolatile =
interpret_func_volatility(volatility_item);
if (strict_item)
procForm->proisstrict = boolVal(strict_item->arg);
if (security_def_item)
procForm->prosecdef = boolVal(security_def_item->arg);
+   if (parallel_item)
+   procForm->proparallel =
interpret_func_parallel(parallel_item);
if (leakproof_item)
{
procForm->proleakproof = boolVal(leakproof_item->arg);
@@ -1506,8 +1514,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt
*stmt)
tup = heap_modify_tuple(tup, RelationGetDescr(rel),
repl_val,
repl_null, repl_repl);
}
-   if (parallel_item)
-   procForm->proparallel =
interpret_func_parallel(parallel_item);
+   /* The previous block must come last because it modifies tup
directly instead of via procForm */

/* Do the update */
CatalogTupleUpdate(rel, >t_self, tup);

I placed the parallel_item block at the end of the four blocks that all
have single line bodies to keep the consistency of that form.

David J.

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

On Tue, Apr 19, 2022 at 11:06:30PM -0400, Tom Lane wrote:
> Julien Rouhaud  writes:
> > On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote:
> >> 
> >> AFAICT the problem is that SET / RESET part is messing with the
> >> HeapTuple, so you can't use the procForm reference afterwards.  Simply
> >> processing parallel_item before set_items fixes the problem, as in the
> >> attached.
> 
> > This time with the file.
> 
> Yeah, I arrived at the same fix.  Another possibility would be to
> make the procForm pointer valid again after heap_modify_tuple,
> but that seemed like it'd add more code for no really good reason.

Yeah I agree.  The comment you added seems enough as a future-proof security.

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Tom Lane]

Julien Rouhaud  writes:
> On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote:
>> 
>> AFAICT the problem is that SET / RESET part is messing with the
>> HeapTuple, so you can't use the procForm reference afterwards.  Simply
>> processing parallel_item before set_items fixes the problem, as in the
>> attached.

> This time with the file.

Yeah, I arrived at the same fix.  Another possibility would be to
make the procForm pointer valid again after heap_modify_tuple,
but that seemed like it'd add more code for no really good reason.

regards, tom lane

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote:
> 
> AFAICT the problem is that SET / RESET part is messing with the HeapTuple, so
> you can't use the procForm reference afterwards.  Simply processing
> parallel_item before set_items fixes the problem, as in the attached.

This time with the file.
diff --git a/src/backend/commands/functioncmds.c 
b/src/backend/commands/functioncmds.c
index 91f02a7eb2..c227fbde19 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1472,6 +1472,8 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
 
procForm->prosupport = newsupport;
}
+   if (parallel_item)
+   procForm->proparallel = interpret_func_parallel(parallel_item);
if (set_items)
{
Datum   datum;
@@ -1506,8 +1508,6 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
tup = heap_modify_tuple(tup, RelationGetDescr(rel),
repl_val, 
repl_null, repl_repl);
}
-   if (parallel_item)
-   procForm->proparallel = interpret_func_parallel(parallel_item);
 
/* Do the update */
CatalogTupleUpdate(rel, >t_self, tup);

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Julien Rouhaud]

Hi,

On Tue, Apr 19, 2022 at 07:21:19PM -0700, David G. Johnston wrote:
> On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn  wrote:
> 
> > *SUMMARY*
> >
> > This part of the syntax diagram for "alter function":
> >
> > *ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
> > action [ … ]*
> >
> > says that the first "action" can be followed (without punctuation) by
> > zero, one, or many other actions. A semantic rule says that no particular
> > action can be specified more than once. My tests used these possible
> > actions:
> >
> > *alter function s1.f()security invokerset timezone = 'UTC'stable*
> > *parallel safe*
> > *;*
> >
> > It brings this new status:
> >
> >
> >
> > * name | type | security |proconfig
> > | volatility
> > |  parallel  
> > --+--+--+-++
> >  f
> >| func | invoker  | {TimeZone=UTC}
> >| stable | restricted*
> >
> > This is the bug.
> >
> 
> It has room for improvement from a user experience perspective.
> 
> While I haven't experimented with this for confirmation, what you are
> proposing here (set + parallel safe) is an impossible runtime combination
> (semantic rule) but perfectly valid to write syntactically.  Your function
> must either be restricted or unsafe per the rules for specifying parallel
> mode.
> 
> If this is indeed what is happening then the documentation should make note
> of it.  Whether the server should emit a notice or warning in this
> situation is less clear.  I'm doubting we would introduce an error at this
> point but probably should have when parallelism was first added to the
> system.

That's not the problem here though, as you can still end up with the wanted
result using 2 queries.  Also, the PARALLEL part is entirely ignored, so if you
wanted to mark the function as PARALLEL UNSAFE because you're also doing a SET
that would make it incompatible it would also be ignored, same if you use a
RESET clause.

AFAICT the problem is that SET / RESET part is messing with the HeapTuple, so
you can't use the procForm reference afterwards.  Simply processing
parallel_item before set_items fixes the problem, as in the attached.

Re: Re: Unexpected result from ALTER FUNCTION— looks like a bug

[Tom Lane]

"David G. Johnston"  writes:
> On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn 
> wrote:
>> This is the bug.

> While I haven't experimented with this for confirmation, what you are
> proposing here (set + parallel safe) is an impossible runtime
> combination (semantic rule) but perfectly valid to write syntactically.

I'm not sure that that's actually disallowed.  In any case, Bryn's
right, the combination of a SET clause and a PARALLEL clause is
implemented incorrectly in AlterFunction.  Careless coding :-(

regards, tom lane

Re: Unexpected result from ALTER FUNCTION— looks like a bug

[David G. Johnston]

On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn  wrote:

> *SUMMARY*
>
> This part of the syntax diagram for "alter function":
>
> *ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
> action [ … ]*
>
> says that the first "action" can be followed (without punctuation) by
> zero, one, or many other actions. A semantic rule says that no particular
> action can be specified more than once. My tests used these possible
> actions:
>
>

>
>
>
>
> *alter function s1.f()security invokerset timezone = 'UTC'stable*
> *parallel safe*
> *;*
>
> It brings this new status:
>
>
>
> * name | type | security |proconfig
> | volatility
> |  parallel  
> --+--+--+-++
>  f
>| func | invoker  | {TimeZone=UTC}
>| stable | restricted*
>
> This is the bug.
>

It has room for improvement from a user experience perspective.

While I haven't experimented with this for confirmation, what you are
proposing here (set + parallel safe) is an impossible runtime combination
(semantic rule) but perfectly valid to write syntactically.  Your function
must either be restricted or unsafe per the rules for specifying parallel
mode.

If this is indeed what is happening then the documentation should make note
of it.  Whether the server should emit a notice or warning in this
situation is less clear.  I'm doubting we would introduce an error at this
point but probably should have when parallelism was first added to the
system.

David J.

Unexpected result from ALTER FUNCTION— looks like a bug

[Bryn Llewellyn]

SUMMARY

This part of the syntax diagram for "alter function":

ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] action 
[ … ]

says that the first "action" can be followed (without punctuation) by zero, 
one, or many other actions. A semantic rule says that no particular action can 
be specified more than once. My tests used these possible actions:

SECURITY { INVOKER |  DEFINER }
SET configuration_parameter TO value 
IMMUTABLE | STABLE | VOLATILE
PARALLEL { UNSAFE | RESTRICTED | SAFE }

The values of the properties set this way can be seen with a suitable query 
against "pg_catalog.pg_proc". (See the complete testcase below.) Suppose that 
the history of events shows this status for the function s1.f():

 name | type | security |proconfig  
  | volatility |  parallel  
--+--+--+-++
 f| func | invoker  |   
  | volatile   | unsafe   

This statement:

alter function s1.f()
security definer
immutable
parallel restricted;

brings this new status:

 name | type | security |proconfig  
  | volatility |  parallel  
--+--+--+-++
 f| func | definer  |   
  | immutable  | restricted

confirming that the three specified changes have been made using just a single 
"alter function" statement.

However, when "SET configuration_parameter" is specified along with other 
changes, then the "parallel" specification (but only this) is ignored. The 
other three specifications are honored.

alter function s1.f()
security invoker
set timezone = 'UTC'
stable
parallel safe;

It brings this new status:

 name | type | security |proconfig  
  | volatility |  parallel  
--+--+--+-++
 f| func | invoker  | {TimeZone=UTC}
  | stable | restricted

This is the bug.

Notice that with "alter procedure", the semantic difference between a procedure 
and a function means that you cannot specify "parallel" here, and so you can't 
demonstrate the bug here.

SELF-CONTAINED, RE-RUNNABLE TESTCASE tested using PG Version 14.1


-- demo.sql
---

\o spool.txt

\c postgres postgres
set client_min_messages = warning;
drop database if exists db;
create database db owner postgres;

\c db postgres
set client_min_messages = warning;
drop schema if exists public cascade;
create schema s1 authorization postgres;

\i prepare-qry.sql

create function s1.f()
  returns int
  language plpgsql
as $body$
begin
  return 0;
end;
$body$;

\t off
execute qry;

alter function s1.f()
security definer
immutable
parallel restricted;

\t on
execute qry;

-- Here is the bug. The test is meaningful only for a function.
alter function s1.f()
security invoker
set timezone = 'UTC'
stable
parallel safe;

execute qry;

\o


-- prepare-qry.sql
--

drop view if exists s1.subprograms cascade;
create view s1.subprograms(
  name,
  pronamespace,
  type,
  security,
  proconfig,
  volatility,
  parallel)
as
select
  proname::text as name,
  pronamespace::regnamespace::text,
  case prokind
when 'a' then 'agg'
when 'w' then 'window'
when 'p' then 'proc'
else 'func'
  end,
 case
when prosecdef then 'definer'
else 'invoker'
  end,
  coalesce(proconfig::text, '') as proconfig,
  case
when provolatile = 'i' then 'immutable'
when provolatile = 's' then 'stable'
when provolatile = 'v' then 'volatile'
  end,
  case
when proparallel = 'r' then 'restricted'
when proparallel = 's' then 'safe'
when proparallel = 'u' then 'unsafe'
  end
from pg_catalog.pg_proc
where
  proowner::regrole::text = 'postgres' and
  pronamespace::regnamespace::text = 's1' and
  pronargs = 0;

prepare qry as
select
  rpad(name,4) as name,
  rpad(type,4) as type,
  rpad(security,8) as security,
  rpad(proconfig,  55) as proconfig,
  rpad(volatility, 10) as volatility,
  rpad(parallel,   10) as parallel
from s1.subprograms
where type in ('func', 'proc')
and   pronamespace::regnamespace::text = 's1'
order by name;


spool.txt
-

 name | type | security |proconfig  
  | volatility |  parallel  
--+--+--+-++
 f| func | invoker  |

Repeated, never-ending deadlock

[andrew cooke]

Hi All,

OK, apologies in advance, I feel like this is a rather unintelligent
question that I probably know the answer to, but I am banging my head
against a wall trying to understand what is happening.

I have an ETL program, written in Python, that uses mutiple processes
to read and load a bunch of files into a database.  Sometimes
(especially early on, when the database is new) common information
that occurs in several files, and which is normalized out into
sub-tables, causes deadlocks.  That's OK; the code should receive an
exception, back off, try again, reading first, etc.  And I see
exceptions occuring in the Python code log as expected.

However, today, I saw the same deadlock error repeated in the Postgres
log every second for minutes.  The same two processes, with the same
data, and the same tables.  I can't work out how this can happen
without a crazy tight loop in the Python code that's just repeatedly
trying to commit a value (which afaik doesn't exist!)  A fragment of
the Postgres log is below.  The Python logs show nothing.

So my question is - is there some circumstance where instead of
raising an error to the calling code, Postgres instead goes round in
circles?

An answer of "nope, it must be a bug in your code" is fine / expected.
It's just one of those days...

This is Postgres 14 with a fairly default config (except that logical
replication is enabled).

Thanks,
Andrew


2022-04-19 19:55:54.482 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:54.482 UTC [28560] DETAIL:  Process 28560 waits for ShareLock 
on transaction 609318483; blocked by process 28559.
Process 28559 waits for ShareLock on transaction 609318682; blocked by 
process 28560.
Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'CRY', 'HNZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
2022-04-19 19:55:54.482 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:54.482 UTC [28560] CONTEXT:  while inserting index tuple 
(25,3) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:54.482 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, 
sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, 
channel_nscl.publisher
2022-04-19 19:55:55.488 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:55.488 UTC [28560] DETAIL:  Process 28560 waits for ShareLock 
on transaction 609318483; blocked by process 28559.
Process 28559 waits for ShareLock on transaction 609318682; blocked by 
process 28560.
Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'CRY', 'HNZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
2022-04-19 19:55:55.488 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:55.488 UTC [28560] CONTEXT:  while inserting index tuple 
(25,4) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:55.488 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, 
sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, 
channel_nscl.publisher
2022-04-19 19:55:56.495 UTC [28560] ERROR:  deadlock detected
2022-04-19 19:55:56.495 UTC [28560] DETAIL:  Process 28560 waits for ShareLock 
on transaction 609318483; blocked by process 28559.
Process 28559 waits for ShareLock on transaction 609318682; blocked by 
process 28560.
Process 28560: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
Process 28559: INSERT INTO channel_nscl (net, sta, chan, loc) VALUES 
('AZ', 'CRY', 'HNZ', '--') RETURNING channel_nscl.rowid, channel_nscl.publisher
2022-04-19 19:55:56.495 UTC [28560] HINT:  See server log for query details.
2022-04-19 19:55:56.495 UTC [28560] CONTEXT:  while inserting index tuple 
(25,5) in relation "channel_nscl_nscl_idx"
2022-04-19 19:55:56.495 UTC [28560] STATEMENT:  INSERT INTO channel_nscl (net, 
sta, chan, loc) VALUES ('AZ', 'KNW', 'HHZ', '--') RETURNING channel_nscl.rowid, 
channel_nscl.publisher

psql timeout: who's waiting for whom

[Rob Sargent]

I'll be asking 'those in charge' to reset some timeout/keep-alive 
setting /somewhere/. I need help in hopefully naming the correct 
setting.  The server is running version 14



Am I dealing with a client issue, a server issue or a mis-match in 
host/postgres configuration values?  If I cannot have psql wait forever, 
I would at least like it to die/reconnect quickly.  What are my options?


If I leave my psql session for long enough, then the first interaction 
(with some exceptions) will be unresponsive for /some time/.  The 
typical, eventual response is a successful re-connection though last one 
was fatal:


   barnard=# \i
   
~/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql

   
psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:22:
   SSL SYSCALL error: Connection timed out
   
psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:23:
   no connection to the server
   
psql:/uufs/chpc.utah.edu/common/HIPAA/u0138544/gits/gitlab/sgs5/jooq/src/main/resources/db/scripts/functions/showSegmentCalls.sql:23:
   fatal: connection to server was lost

More typically I get this

   barnard=# select 1;
  C-c C-cCancel request sent
   SSL SYSCALL error: Connection timed out
   barnard=# \c barnard
   psql (12.2, server 14.2)
   WARNING: psql major version 12, server major version 14.
 Some psql features might not work.
   SSL connection (protocol: TLSv1.2, cipher:
   ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
   You are now connected to database "barnard" as user "postgres".



The client machine is using default TCP values,

   tcp_keepalive_intvl: 75
   tcp_keepalive_probes: 9
   tcp_keepalive_time: 7200

the dbhost postgresql.conf has tcp_* set to zero (use default)

   #tcp_keepalives_idle = 0    # TCP_KEEPIDLE, in seconds;
    # 0 selects the system default
   #tcp_keepalives_interval = 0    # TCP_KEEPINTVL, in seconds;
    # 0 selects the system default
   #tcp_keepalives_count = 0   # TCP_KEEPCNT;
    # 0 selects the system default
   #tcp_user_timeout = 0   # TCP_USER_TIMEOUT, in
   milliseconds;
    # 0 selects the system default

   #client_connection_check_interval = 0   # time between checks for client
    # disconnection while
   running queries;
    # 0 for never
   [postgres@csgsdb ipv4]$ for t in tcp_keep*;do printf "%s: " $t; cat
   $t; done
   tcp_keepalive_intvl: 75
   tcp_keepalive_probes: 9
   tcp_keepalive_time: 7200

Thanks

Re: Joining with calendar table

[Adrian Klaver]

On 4/19/22 00:34, Pól Ua L. wrote:



Bonjour a tous/Hello all,


Small problem - I hope it not to trivial for here.

We created a table, then someone deleted some records and we want to put 
them back, but we not sure how.


CREATE TABLE dat AS
   SELECT
     GENERATE_SERIES
     (
       '2022-03-01'::DATE,
       '2022-04-18'::DATE,
       '1 DAY'
     ) AS jour;

So, all days from March 01 to Easter Monday.

Then someone delete the weekends.


delete from dat where extract(isodow from jour) IN (6, 7);



But, there are also actvities on these days  also, so I would like to 
put them back in.


I think I need a left join with the calendar table, but am not sure how 
to do this so we have full months again.





Could someone show me how this to be done please?


BEGIN;
insert into dat select j.a from GENERATE_SERIES
(
  '2022-03-01'::DATE,
  '2022-04-18'::DATE,
  '1 DAY'
) as j(a) left join dat on j.a = dat.jour  where dat.jour is null;

INSERT 0 14


Verify the dates where added then:

COMMIT;

--
Adrian Klaver
adrian.kla...@aklaver.com

Joining with calendar table

[Pól Ua L .]

Bonjour a tous/Hello all,

Small problem - I hope it not to trivial for here.

We created a table, then someone deleted some records and we want to put them 
back, but we not sure how.

CREATE TABLE dat AS
SELECT
GENERATE_SERIES
(
'2022-03-01'::DATE,
'2022-04-18'::DATE,
'1 DAY' ) AS jour;

So, all days from March 01 to Easter Monday.

Then someone delete the weekends.

delete from dat where extract(isodow from jour) IN (6, 7);

But, there are also actvities on these days also, so I would like to put them 
back in.

I think I need a left join with the calendar table, but am not sure how to do 
this so we have full months again.

Could someone show me how this to be done please?

There is a fiddle here with the code explained above.

https://dbfiddle.uk/?rdbms=postgres_14=9a0220303051dd5da57cd1bd5b132e94

Thank you for your time and attentioin - salutations.

Re: Huge archive log generate in Postgresql-13

[Adrian Klaver]

On 4/18/22 21:41, Ram Pratap Maurya wrote:

Dear Adrian,

If we set  parameter *"PG-WAL  = replica"*  in PG13 conf file , there is 
any issue  in replication through PG_WAL  and pg_log_archive log.




In versions of Postgres from 9.6+ your setting of hot_standby is mapped 
to the replica setting so it is really not a change. In other words the 
hot_standby setting = replica setting as of 9.6. I made the suggestion 
to change just so the conf setting matches the choices in current 
documentation. Less confusion for someone looking through the 
postgresql.conf file and  then looking up the values in the documentation.



As to your replication is that binary replication or logical replication?

Also you have not said where you are measuring the size growth in WAL files?



Regards,

Ram Pratap.

-Original Message-
From: Ram Pratap Maurya
Sent: 19 April 2022 09:00
To: Adrian Klaver ; pgsql-gene...@postgresql.org
Cc: Manu Saxena 
Subject: RE: Huge archive log generate in Postgresql-13

Dear Adrian,

We have two replica system one is Slave and other id DR server .

Salve server replicate from PG_WAL and DR system replicate from  
pg_log_archive.


Can you please suggest what changes need to required in PG13 conf file.

Regards,

Ram Pratap.

-Original Message-

From: Adrian Klaver [mailto:adrian.kla...@aklaver.com 
]


Sent: 18 April 2022 21:30

To: Ram Pratap Maurya >; pgsql-gene...@postgresql.org 



Cc: Manu Saxena >


Subject: Re: Huge archive log generate in Postgresql-13

On 4/18/22 04:34, Ram Pratap Maurya wrote:

 > Hi Support,

 >

 > We have upgraded postgresql DB from version 11 to 13 .  after upgrade

 > to

 > 13  huge archive log generate in system .

 >

 > Before upgrade  per day 120GB to 150 GB log generated but after

 > upgrade per day approx. 250 to 300 GB log generated.

Where are you measuring this in the WAL directory or the archive directory?

Do you have replication set up from this server and if so what type?

FYI, the wal_level setting of hot_standby is deprecated and maps to 
replica since version 9.6. At some point you might want to change to 
match current documentation.


 >

 > Can you please suggest why huge archive log generated after upgrade

 >   there any configure setting or this is Postgresql-13 behaviour.

 >

 > Postgresql-13 Postgresql conf file attached for your references.

 >

 > Regards,

 >

 > Ram Pratap.

 >

--

Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL 10.20 crashes / Antivirus

[Adrian Klaver]

On 4/19/22 04:59, Thomas, Richard wrote:

A request for further suggestions for how to fix/diagnose this PG crash (having 
just got back from holiday I discover that PG is still crashing even though I 
have changed the pgdump target output location to a folder excluded from the 
McAfee Antivirus scanning - but thanks for the suggestion Adrian). The last few 
crashes have occurred only during the backup process, when database pg_dump 
completes (successfully - including the disconnection message in the log) but 
before the next database pgdump can begin (that pg_dump command gets the error 
that it cannot connect to the database as the server is no longer responding). 
This will happen after dumping several databases - sometimes it manages the 
complete multiple database dump fine. One new thing I've noticed is that when 
it fails the file access 0xC022 error occurs a second time when PG is 
trying to restart itself after shutting down all the other processes, 
suggesting whatever caused the crash is still around (a later manual restart 
works fine):


From you original post:

"... although the new pg_dump log file was created (is empty) it does 
not get as far as creating the .backup file ..."


What are the actual commands you are using to do the above?





Richard



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: oldest xmin is far in the past :: BUT xmin is not available in system

[Peter Geoghegan]

On Mon, Apr 18, 2022 at 11:37 PM bhargav kamineni  wrote:
> executing the vacuum on the entire cluster is also giving the same HINTS and 
> WARNING's

You're using Aurora, not PostgreSQL. Perhaps this is actually a bug,
but there is no way for anybody here to know.

-- 
Peter Geoghegan

Re: oldest xmin is far in the past :: BUT xmin is not available in system

[Tom Lane]

Rob Sargent  writes:
> On 4/19/22 00:06, David G. Johnston wrote:
>> On Monday, April 18, 2022, bhargav kamineni  wrote:
>> It seems vacuum is behaving somewhat weird on postgres database ,
>> observing below HINTS on the vacuum logs
>> WARNING:  oldest xmin is far in the past

> This site has lots of useful queries for this sort of issue:
> https://www.shanelynn.ie/postgresql-find-slow-long-running-and-blocked-queries.
>  
> I think you're looking for a very old transaction that is probably not 
> going to finish, must be terminated.

Yeah, that.  Manual vacuuming isn't going to help until you get rid
of the old open transaction.  Look into pg_prepared_xacts and
pg_stat_activity.

regards, tom lane

RE: PostgreSQL 10.20 crashes / Antivirus

[Thomas, Richard]

A request for further suggestions for how to fix/diagnose this PG crash (having 
just got back from holiday I discover that PG is still crashing even though I 
have changed the pgdump target output location to a folder excluded from the 
McAfee Antivirus scanning - but thanks for the suggestion Adrian). The last few 
crashes have occurred only during the backup process, when database pg_dump 
completes (successfully - including the disconnection message in the log) but 
before the next database pgdump can begin (that pg_dump command gets the error 
that it cannot connect to the database as the server is no longer responding). 
This will happen after dumping several databases - sometimes it manages the 
complete multiple database dump fine. One new thing I've noticed is that when 
it fails the file access 0xC022 error occurs a second time when PG is 
trying to restart itself after shutting down all the other processes, 
suggesting whatever caused the crash is still around (a later manual restart 
works fine):

2022-04-15 03:54:06 BST: [3720] WARNING:  57P02: terminating connection because 
of crash of another server process
2022-04-15 03:54:06 BST: [3720] DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because another 
server process exited abnormally and possibly corrupted shared memory.
2022-04-15 03:54:06 BST: [3720] HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
2022-04-15 03:54:06 BST: [3720] LOCATION:  quickdie, postgres.c:2622
2022-04-15 03:54:06 BST: [7900] LOG:  0: all server processes terminated; 
reinitializing
2022-04-15 03:54:06 BST: [7900] LOCATION:  PostmasterStateMachine, 
postmaster.c:3924
2022-04-15 03:54:08 BST: [7900] LOG:  0: startup process (PID 3652) was 
terminated by exception 0xC022
2022-04-15 03:54:08 BST: [7900] HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.
2022-04-15 03:54:08 BST: [7900] LOCATION:  LogChildExit, postmaster.c:3650
2022-04-15 03:54:08 BST: [7900] LOG:  0: aborting startup due to startup 
process failure
2022-04-15 03:54:08 BST: [7900] LOCATION:  reaper, postmaster.c:2907
2022-04-15 03:54:09 BST: [7900] LOG:  0: database system is shut down
2022-04-15 03:54:09 BST: [7900] LOCATION:  UnlinkLockFiles, miscinit.c:765

Some details from my original email (as it was 2 weeks ago):

I’ve been having problems with several PostgreSQL crashes over the last month 
(originally with 10.16, then more recently with 10.20, all on a Windows Server 
2012R2 platform), always with a low level permissions error:
"server process (PID 5664) was terminated by exception 0xC022"
(This NT error translates as "Access Denied: A process has requested access to 
an object but has not been granted those access rights").

I was certainly having issues earlier with McAfee Antivirus causing some of 
these problems but this has been reduced after following the advice here to 
prevent scanning of PGDATA folders and postgres.exe (if someone can confirm 
that reference to “postgresql.exe” is a typo on that page I will correct it, 
but don’t want to in case it refers to a different version of PostgreSQL):
https://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Antivirus_software
I have also got our IT to disable antivirus on our tablespace folder and on the 
folder running our backup PowerShell script (which calls pg_dump).

Any suggestions for how to fix/diagnose? Some things I have tried already:
- Ran “sfc” (System File Checker) to check for/fix any Windows OS corruptions 
(none found that were relevant)
- Ran “amcheck” on several of the databases
- Unfortunately checksums are not turned on with the database (not an option 
with the EDB PostgreSQL Windows Installer) and can’t be turned on after initdb 
on version 10
- I hoped to run pg_catcheck but cannot find a binary version of this online 
and I think it would take me quite a lot of work to build it myself (I don’t 
have Visual Studio, only VSC).

Richard

2022-04-02 02:14:53 BST: [7480] 
user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOG:  
0: duration: 0.816 ms  statement: COPY sde.sde_xml_indexes (index_id, 
index_name, owner, index_type, description) TO stdout;
2022-04-02 02:14:53 BST: [7480] 
user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com 
LOCATION:  exec_simple_query, postgres.c:1200
2022-04-02 02:14:54 BST: [7480] 
user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com LOG:  
0: disconnection: session time: 0:36:52.430 user=scde_suad database=a9sde 
host=a9dualling.atkinsgeospatial.com port=59467
2022-04-02 02:14:54 BST: [7480] 
user=scde_suad,db=a9sde,app=pg_dump,host=a9dualling.atkinsgeospatial.com 
LOCATION:  log_disconnections, postgres.c:4577
2022-04-02 02:14:55 BST: [5016] LOG:  0: server process (PID 100) was 
terminated by exception 0xC022

At Atkins - 

Re: oldest xmin is far in the past :: BUT xmin is not available in system

[Rob Sargent]

On 4/19/22 00:06, David G. Johnston wrote:



On Monday, April 18, 2022, bhargav kamineni  wrote:

Hi Team,

It seems vacuum is behaving somewhat weird on postgres database ,
observing below HINTS on the vacuum logs

WARNING:  oldest xmin is far in the past

HINT:  Close open transactions soon to avoid wraparound problems.

You might also need to commit or roll back old prepared
transactions, or drop stale replication slots.


What version?

What other databases are present?

Others can give better (more detailed/nuanced) guidance but if you can 
just start vacuuming every table in every database manually, you 
probably should just do that.  Vacuum freeze specifically.


David J.


|This site has lots of useful queries for this sort of issue:
https://www.shanelynn.ie/postgresql-find-slow-long-running-and-blocked-queries. 
I think you're looking for a very old transaction that is probably not 
going to finish, must be terminated.


|

Re: oldest xmin is far in the past :: BUT xmin is not available in system

[bhargav kamineni]

Other details:

postgres=> select version();
   version
-
 PostgreSQL 13.5 on aarch64-unknown-linux-gnu, compiled by
aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit
(1 row)

postgres=> select aurora_version();
 aurora_version

 13.5.1
(1 row)


postgres=> \l+

 List
of databases

   Name|  Owner   | Encoding |   Collate   |Ctype|   Access
privileges   |   Size| Tablespace |Description

---+--+--+-+-+---+---++

 Postgres | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/root
+| 361 GB| pg_default |

   |  |  | | |
root=CTc/root+|   ||

   |  |  | | |
pmm=CTc/root  |   ||

 Test  | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
  | 8391 kB   | pg_default | default administrative connection
database

 rdsadmin  | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin=CTc/rdsadmin | No Access | pg_default |

 template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/rdsadmin  +| 16 MB | pg_default | unmodifiable empty database

   |  |  | | |
rdsadmin=CTc/rdsadmin |   ||

 template1 | root | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
root=CTc/root+| 8215 kB   | pg_default | default template for new
databases

   |  |  | | |
=c/root   |   ||

(5 rows)

executing the vacuum on the entire cluster is also giving the same HINTS
and WARNING's

WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
WARNING:  oldest xmin is far in the past

Regards,
BK

On Tue, Apr 19, 2022 at 11:36 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
>
> On Monday, April 18, 2022, bhargav kamineni  wrote:
>
>> Hi Team,
>>
>> It seems vacuum is behaving somewhat weird on postgres database ,
>> observing below HINTS on the vacuum logs
>>
>> WARNING:  oldest xmin is far in the past
>>
>> HINT:  Close open transactions soon to avoid wraparound problems.
>>
>> You might also need to commit or roll back old prepared transactions, or
>> drop stale replication slots.
>>
>>
>> What version?
>
> What other databases are present?
>
> Others can give better (more detailed/nuanced) guidance but if you can
> just start vacuuming every table in every database manually, you probably
> should just do that.  Vacuum freeze specifically.
>
> David J.
>
>

Re: oldest xmin is far in the past :: BUT xmin is not available in system

[David G. Johnston]

On Monday, April 18, 2022, bhargav kamineni  wrote:

> Hi Team,
>
> It seems vacuum is behaving somewhat weird on postgres database ,
> observing below HINTS on the vacuum logs
>
> WARNING:  oldest xmin is far in the past
>
> HINT:  Close open transactions soon to avoid wraparound problems.
>
> You might also need to commit or roll back old prepared transactions, or
> drop stale replication slots.
>
>
> What version?

What other databases are present?

Others can give better (more detailed/nuanced) guidance but if you can just
start vacuuming every table in every database manually, you probably should
just do that.  Vacuum freeze specifically.

David J.