Re: Postgresql 11: terminating walsender process due to replication timeout
sourcedb:~$ postgres --version postgres (PostgreSQL) 11.6 Sorry for missing this information. But looks like this fix is already included in the version I am running. Regards, Abhishek Bhola On Thu, Sep 9, 2021 at 3:56 PM Kyotaro Horiguchi wrote: > At Thu, 9 Sep 2021 14:52:25 +0900, Abhishek Bhola < > abhishek.bh...@japannext.co.jp> wrote in > > I have found some questions about the same error, but didn't find any of > > them answering my problem. > > > > The setup is that I have two Postgres11 clusters (A and B) and they are > > making use of publication and subscription features to copy data from A > to > > B. > > > > A (source DB- publication) --> B (target DB - subscription) > > > > This works fine, but often (not always) when the data volume being > inserted > > on a table in node A increases, it gives the following error. > > > > "terminating walsender process due to replication timeout" > > > > The data volume at the moment being entered is about 30K rows per second > > continuously for hours through COPY command. > > > > Earlier the wal_sender_timeout was set to 5 sec and I would see this > error > > much often. I then increased it to 1 min and the frequency of this error > > reduced. But I don't want to keep increasing it without understanding > what > > is causing it. I looked at the code of walsender.c and know the exact > lines > > where it's coming from. > > > > But I am still not clear which parameter is making the sender assume that > > the receiver node is inactive and therefore it should stop the > wal_sender. > > > > Can anyone please suggest what changes I should make to remove this > error? > > What minor-version is the Postgres server mentioned? PostgreSQL 11 > have gotten the following fix at 11.6, which could be related to the > trouble. > > https://www.postgresql.org/docs/11/release-11-6.html > > > Fix timeout handling in logical replication walreceiver processes > > (Julien Rouhaud) > > > > Erroneous logic prevented wal_receiver_timeout from working in > > logical replication deployments. > > The details of the fix is here. > > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3f60f690fac1bf375b92cf2f8682e8fe8f69098 > > Fix timeout handling in logical replication worker > > > > The timestamp tracking the last moment a message is received in a > > logical replication worker was initialized in each loop checking if a > > message was received or not, causing wal_receiver_timeout to be ignored > > in basically any logical replication deployments. This also broke the > > ping sent to the server when reaching half of wal_receiver_timeout. > > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center > -- _This correspondence (including any attachments) is for the intended recipient(s) only. It may contain confidential or privileged information or both. No confidentiality or privilege is waived or lost by any mis-transmission. If you receive this correspondence by mistake, please contact the sender immediately, delete this correspondence (and all attachments) and destroy any hard copies. You must not use, disclose, copy, distribute or rely on any part of this correspondence (including any attachments) if you are not the intended recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_
ALTER DATABASE SET not working as expected?
This seems simple enough but it's not working.
alter database dbname set "blah.blah" = 'test_value' ;
The above statement returns with no errors.
select current_setting('blah.blah')
The above statement returns a record with null
Also how long does the "ALTER DATABASE SET" last? Will it survive a
shutdown and restart?
Re: ALTER DATABASE SET not working as expected?
2021年9月9日(木) 16:53 Tim Uckun :
>
> This seems simple enough but it's not working.
>
> alter database dbname set "blah.blah" = 'test_value' ;
>
> The above statement returns with no errors.
>
> select current_setting('blah.blah')
>
> The above statement returns a record with null
Open a new database session and you should see the setting.
> Also how long does the "ALTER DATABASE SET" last? Will it survive a
> shutdown and restart?
Yes.
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
Re: Subscriber to Get Only Some of The Tables From Publisher
On Thu, 9 Sept 2021 at 06:54, Avi Weinberg wrote: > I have a publisher with around 30 tables. I have two types of subscribers. > Both types needs 25 "common" tables from the publisher plus 2-3 specific > tables for each type of subscriber. > > For maintenance and monitoring reasons it is better for me to have both > subscribers point to the same publisher but "skip/ignore" 2-3 tables that are > not needed for them. If this is not possible, I will be forced to create 2 > publishers. > > Any idea hot to ignore tables from publisher? That's not the way the APIs are defined. Creating 2 publications should not be a problem. Specifying the metadata for your situation would be about the same complexity in all cases. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: ALTER DATABASE SET not working as expected?
Ok thanks.
On Thu, Sep 9, 2021 at 9:06 PM Ian Lawrence Barwick wrote:
>
> 2021年9月9日(木) 16:53 Tim Uckun :
> >
> > This seems simple enough but it's not working.
> >
> > alter database dbname set "blah.blah" = 'test_value' ;
> >
> > The above statement returns with no errors.
> >
> > select current_setting('blah.blah')
> >
> > The above statement returns a record with null
>
> Open a new database session and you should see the setting.
>
> > Also how long does the "ALTER DATABASE SET" last? Will it survive a
> > shutdown and restart?
>
> Yes.
>
>
> Regards
>
> Ian Barwick
>
> --
> EnterpriseDB: https://www.enterprisedb.com
Re: How to observe plan_cache_mode transition from custom to generic plan?
Hi Laurenz,
Thank you for the quick response and clear demonstration. I have a few
comments, inline below.
On Mon, Sep 6, 2021 at 8:26 AM Laurenz Albe
wrote:
> On Sun, 2021-09-05 at 14:20 -0400, Mladen Gogala wrote:
> > On 9/5/21 9:35 AM, Richard Michael wrote:
> > > TL;DR --
> > >
> > > 1/ My basic prepared statement doesn't seem to start out with a custom
> > > plan
> > > (i.e., no parameter symbols in query text).
>
> That is notmal for the first five executions. PostgreSQL will consider a
> generic plan only from the sixth execution on.
>
Yes, I understood from the documentation that I should expect the sixth
query to be generic. My comment was perhaps awkwardly phrased in the
negative-- I wondered why I was *not* seeing a custom query as the first
planned (indeed, first five), that is: one with substituted literals, not
placeholders.
I misunderstood that parameters only appear (either subtituted or as
placeholders, $1, etc.) in portions of the query *plan* output (for
example, "Filter:" and "Output:" lines). I overlooked that even my basic
query had a plan containing "Output: $1" on the sixth EXPLAIN EXECUTE.
Briefly:
PREPARE basic(int) AS SELECT $1 AS number;
EXPLAIN EXECUTE basic(10);
... Output: 10
-- Repeat 4 more times, then:
EXPLAIN EXECUTE basic(10);
... Output: $1
> > > 2/ EXPLAIN EXECUTE of prepared statement doesn't show query text, as
> the
> > > documentation seems to imply. Should it?
>
> Yes, and it does for me - see below.
>
I do not see the query text in your EXPLAIN EXECUTE output. Perhaps I am
misusing terminology?
By "query text", I mean-- I do not see: "SELECT * FROM jobs WHERE job_id =
1" (in the case of a custom plan), or "... job_id = $1" (in the case of a
generic query).
(I do, of course, see the substituted parameter value (1, 2, 3, etc. for $1
as job_id) on various lines, and thanks again for the quick demo.)
> > > 2/ The query text was logged by `auto_explain`. However, it contains a
> > > parameter symbol; why? Also, why is the logged query a `PREPARE`
> > > statement
> > > (as if a new prepared statement is being created), instead of only the
> > > `SELECT ..` which was executed?
>
> You saw the log entry for the PREPARE statement. Since your statement
> contains $1 in its query text, you'll get that logged.
>
> Look for the log entries for the EXECUTEs.
>
In my log (with auto_explain enabled), even for EXPLAIN EXECUTE, the plan
shows the query text as: PREPARE ...
In my initial post, I was trying to describe that I expected "EXPLAIN
EXECUTE foo('red')" to log, without "PREPARE ..." prefixed, e.g.,
Query Text: SELECT * FROM data WHERE colour = $1
Here is my psql session, interspersed with the postgres.log content:
~# PREPARE foo(text) AS SELECT * FROM data WHERE colour = $1 LIMIT 1;
PREPARE
2021-09-09 16:29:51.235 CEST [3680] LOG: statement: PREPARE foo(text) AS
SELECT * FROM data WHERE colour = $1 LIMIT 1;
2021-09-09 16:29:51.236 CEST [3680] LOG: duration: 0.840 ms
~# EXPLAIN EXECUTE foo('red');
QUERY PLAN
--
Limit (cost=0.00..0.04 rows=1 width=46)
-> Seq Scan on data (cost=0.00..109229.00 rows=250 width=46)
Filter: (colour = 'red'::text)
(3 rows)
2021-09-09 16:31:17.782 CEST [3680] LOG: statement: EXPLAIN EXECUTE
foo('red');
2021-09-09 16:31:17.783 CEST [3680] LOG: duration: 0.000 ms plan:
Query Text: PREPARE foo(text) AS SELECT * FROM data WHERE colour =
$1 LIMIT 1;< Why "PREPARE ...", not just "SELECT ..." ?
Limit (cost=0.00..0.04 rows=1 width=46)
Output: id, md5sum, colour
-> Seq Scan on public.data (cost=0.00..109229.00 rows=249
width=46)
Output: id, md5sum, colour
Filter: (data.colour = 'red'::text)
2021-09-09 16:31:17.783 CEST [3680] LOG: duration: 1.390 ms
Thanks again Laurenz, now I know what to look for in the output and logs.
Regards,
Richard
> By the way, here is one of the shortcomings of using prepared statements
> in my opinion: if EXECUTE is slow, you will get the EXECUTE logged, but
> not the statement text of the prepared statements.
>
> > Well, some other databases that shall remain unnamed have a thing called
> > "bind variable peeking". [more off-topic things skipped]
>
> Most of your explanations about PostgreSQL are correct, but:
>
> > Postgres has a different method: it executes the same statement with 5
> > different sets of bind variables and if it finds a plan that is cheaper
> > than the generic plan, it caches it and uses it in the future.
>
> That is wrong: If the estimate for the generic plan is more expensive
> than the plans for the first five executions, it will keep generating a
> custom plan for subsequent executions of the prepared statement.
>
> > People switching from Oracle, me being one of those, frequently make
> > mistake of using bind variables in Postgres.
>
> "Bind variable
Re: How to observe plan_cache_mode transition from custom to generic plan?
Hi David, On Sun, Sep 5, 2021 at 8:32 PM David G. Johnston wrote: > On Sunday, September 5, 2021, Richard Michael > wrote: > >> >> Based on the documentation, I expected the first planned query text to >> be: `SELECT 10 AS data`, since it should be a custom plan with >> substituted values. However, the query text always contains a parameter >> symbol: `SELECT $1 AS data`. >> >> > A query plan is not the same as the query text. While the executed plan > can be generic or custom the query text is constant. > Thanks for drawing my attention to the difference between the query text and query plan. > If you want to see the difference between a generic and a custom plan you > need to comprise a query that would actually have different custom and > generic plans. Queries that don’t involve tables, indexes, or joins don’t > have any choices to make with respect to how they are executed. > After reading Laurenz's reply, I experimented again with `PREPARE basic(int) AS SELECT $1 AS number;" and the query plan logged in the log file (by auto_explain) does indeed show "Output: $1" on the sixth EXPLAIN EXECUTE! Sorry I missed this earlier. (The EXPLAIN EXECUTE output itself does not contain an "Output:" line, so I didn't notice this lack of parameter substitution in psql.) I appreciate your point about query plans which may not involve choices; thank you for mentioning it. Regards, Richard > David J. > >
Re: How to observe plan_cache_mode transition from custom to generic plan?
Richard Michael writes: > After reading Laurenz's reply, I experimented again with `PREPARE > basic(int) AS SELECT $1 AS number;" and the query plan logged in the log > file (by auto_explain) does indeed show "Output: $1" on the sixth EXPLAIN > EXECUTE! Sorry I missed this earlier. (The EXPLAIN EXECUTE output itself > does not contain an "Output:" line, so I didn't notice this lack of > parameter substitution in psql.) I think EXPLAIN VERBOSE would add that bit of detail. regards, tom lane
Re: How to observe plan_cache_mode transition from custom to generic plan?
Hi Tom, On Thu, Sep 9, 2021 at 6:02 PM Tom Lane wrote: > Richard Michael writes: > > After reading Laurenz's reply, I experimented again with `PREPARE > > basic(int) AS SELECT $1 AS number;" and the query plan logged in the log > > file (by auto_explain) does indeed show "Output: $1" on the sixth EXPLAIN > > EXECUTE! Sorry I missed this earlier. (The EXPLAIN EXECUTE output > itself > > does not contain an "Output:" line, so I didn't notice this lack of > > parameter substitution in psql.) > > I think EXPLAIN VERBOSE would add that bit of detail. > Indeed it does! I literally just now experimented with this and was returning to the ML reply to myself to avoid wasting more of anyone's time. Would a tiny patch to the PREPARE docs be accepted? I'd like to help clarify investigating `plan_cache_mode` for future readers. Thanks for replying, regards, Richard > regards, tom lane >
Re: How to observe plan_cache_mode transition from custom to generic plan?
Richard Michael writes: > Would a tiny patch to the PREPARE docs be accepted? I'd like to help > clarify investigating `plan_cache_mode` for future readers. What did you have in mind? (I'm kind of allergic to documenting the plan-caching behavior in too much detail, because I keep expecting us to change it. On the other hand, nobody's put forward any improvement proposals.) regards, tom lane
Re: update non-indexed value is slow if some non-related index/fk are enabled
On 2021-09-06 07:13:07 -0700, David G. Johnston wrote: > On Monday, September 6, 2021, Philippe Doussot > wrote: > > I whas hopping the same optimisation as you: Write in place. > > How exactly would you expect “update-in-place” to work given the nature of > MVCC? Some databases (e.g. Oracle) store the old row in a separate place (called an UNDO segment in Oracle) and then overwrite the row in place. When another transaction tries to access the (not yet committed) row or when the transaction is rolled the old row is retrieved from the undo segment. There are pros and cons to both approaches. Personally, I had less problems with PostgreSQL's approach than with Oracle's. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Postgresql 11: terminating walsender process due to replication timeout
At Thu, 9 Sep 2021 16:06:25 +0900, Abhishek Bhola wrote in > sourcedb:~$ postgres --version > postgres (PostgreSQL) 11.6 > > Sorry for missing this information. > But looks like this fix is already included in the version I am running. Ok. I'm not sure but there may be a case where too-busy (or too poor relative to the publisher) subscriber cannot send a response for a long time. Usually keep-alive packets sent from publisher causes subscriber response even while busy time but it seems that if subscriber applies changes more than two times slower than the publisher sends, subscriber doesn't send a response in the timeout window. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
