Re: Postgresql 11: terminating walsender process due to replication timeout
So is there any solution to this issue? I did try to increase the wal_sender_timeout and it broke the pub/sub. I increased the wal_receiver_timeout and it wouldn't attempt to restart the subscription until that time elapsed. Due to that, the WAL segments got removed by the time it came up again and it stopped working. So given that the publisher is publishing at a higher rate than the subscriber is subscribing, what can be done? On Fri, Sep 10, 2021 at 9:26 AM Kyotaro Horiguchi wrote: > At Thu, 9 Sep 2021 16:06:25 +0900, Abhishek Bhola < > abhishek.bh...@japannext.co.jp> 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 > -- _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).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_
Re: How to observe plan_cache_mode transition from custom to generic plan?
On Thu, 2021-09-09 at 16:51 +0200, Richard Michael wrote: > > > 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? My sloppiness. You are right, it does not show the query text. But no EXPLAIN shows the query text, right? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: How to observe plan_cache_mode transition from custom to generic plan?
On Thu, Sep 9, 2021 at 7:39 PM Tom Lane wrote: > 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.) > For reference, currently in PREPARE docs: "If a generic plan is in use, it will contain parameter symbols $n, while a custom plan will have the supplied parameter values substituted into it." Here is a rough stab at a few additional sentences which hopefully will not induce anaphylaxis; my motivation follows. "If a generic plan is in use, it will contain parameter symbols $n, while a custom plan will have the supplied parameter values substituted into plan nodes which use the parameter, such as filters or conditions. If the parameters are used in the output, `EXPLAIN VERBOSE` can be helpful, as it will display query output. Note the query text (displayed by `auto_explain`) is constant, and will not have parameters substituted, even when a generic plan is used." (Perhaps mentioning auto_explain is overly specific. Although, no other command or tooling prints the query text. This note could or should go in the auto_explain docs.) As you noticed up-thread, I was testing with a very basic query: `SELECT $1 AS number`, for which EXPLAIN EXECUTE shows no parameter related values (it occurs only in the output, requiring VERBOSE). Also, I was testing from several clients: psql, my test C program, and Elixir. For simplicity (oops), I enabled `auto_explain`. Auto-explain prints `plan: ... Query Text: SELECT $1 AS number`, and I see `ExplainQueryText()` was added to commands/explain.c years ago for this purpose. In my situation, only the auto_explain output was displaying parameter symbols ($1), in the query text, and I allowed myself to be misled. (In auto_explain output, the query text seems to be "part of the plan", and hence I thought it would have values substituted). Re-reading the original sentence, I concede it is sufficient given what I now know (especially static, non-substituted query text). It can definitely be argued that: (1) I don't know the planner or EXPLAIN or auto_explain well enough to be competent at this type of investigation, and (2) the PREPARE documentation did not mention `auto_explain`, so I should not have used it, and followed the documentation precisely. (The static query text matter is a bit subtle, I think.) However, I would also say the documentation could nudge the reader in a helpful direction, and my general position on documentation is that it's for newcomers not experts (who don't need any :). I'd like to believe those additional sentences would have spared you all this thread. Aside, might EXPLAIN VERBOSE be enhanced to also print Query Text, so that `auto_explain` really behaves like "automatic explain"? I note only `auto_explain` uses the function added to explain.c. Regards, richard > regards, tom lane >
Re: How to observe plan_cache_mode transition from custom to generic plan?
On Fri, Sep 10, 2021 at 10:38 AM Laurenz Albe wrote: > On Thu, 2021-09-09 at 16:51 +0200, Richard Michael wrote: > > > > 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? > > My sloppiness. You are right, it does not show the query text. But no > EXPLAIN > shows the query text, right? > That seems to be correct, yes. Somewhat related, I noted in my reply to Tom that `auto_explain` does log the query text, and ExplainQueryText() was added to commands/explain.c for just that purpose. auto_explain is the only code which uses that function. Regards, richard > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: Is there any technical reason why "alter table .. set not null" can't use index?
Would it help to create a new not null column in the target table, and then update the table by copying values from old column to the new, not null column? Of course you’d have to ignore errors, etc. but wouldn’t that perform at enough for your needs? Sent from my iPhone > On Sep 8, 2021, at 1:15 AM, hubert depesz lubaczewski > wrote: > > On Wed, Sep 08, 2021 at 07:09:31AM +0200, Alexander Kukushkin wrote: >> Hi, >> >>> On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, >>> wrote: >>> >>> Hi, >>> we needed recently to add not null constraint on some fields, and it >>> struck me that it took long. >>> Obviously - pg has to check the data. But it seems that it can't use >>> index. >>> >> >> It can't use the index, but can use an already existing CHECK CONSTRAINT, >> that could be created as NOT VALID and validated without holding heavy >> locks. After adding not null you can drop the constraint. > > Thanks. Forgot about these. > > Best regards, > > depesz > > >
Re: Is there any technical reason why "alter table .. set not null" can't use index?
On Fri, Sep 10, 2021 at 01:26:46PM -0400, Gus Spier wrote: > Would it help to create a new not null column in the target table, and > then update the table by copying values from old column to the new, > not null column? Of course you’d have to ignore errors, etc. but > wouldn’t that perform at enough for your needs? Why would you think that adding new column, with constraint, and copying data would be faster? depesz
PostgreSQL starting with a corrupted database or other fatal issues
Assuming that PostgreSQL is started by systemd, and encounters fatal issues, how often does systemd attempt to re-start it before giving up? I'm not familiar with that interaction and if systemd stops after X attempts, or what. What does postgres do when it encounters fatal issues and cannot start? It obviously abandons starting at some point. Where would I find details about this situation in the PG docs? THANKS.
Unable to drop invalid TOAST indexes
Hello! I ran into an interesting situation last week. Discovered on RDS Postgres version 13. Replicated on community Postgres version 13. A client wanted us to drop some invalid indexes, some of which turned out to be invalid indexes on TOAST tables. We don't know for sure how their database got this way; we suspect a failed 'REINDEX DATABASE CONCURRENTLY', possibly more than one. Attempts to drop the invalid index fail like so: testy=> drop index CONCURRENTLY pg_toast.pg_toast_14199_index_ccnew; ERROR: permission denied for schema pg_toast This fails even when I'm logged in as the owner of the toast table's parent table; I have to assume superuser privileges in order to drop them. On a hosted environment, this means a user could create an invalid index that they can't drop without provider intervention. Is this expected behavior? Thanks! gabrielle
Re: PostgreSQL starting with a corrupted database or other fatal issues
On Friday, September 10, 2021, Keith Christian wrote: > > Where would I find details about this situation in the PG docs?. > > As far as PostgreSQL is concerned if startup fails it tries to log that fact then stops. If doesn’t care whether it’s being started by hand, via systemd, or even cron, and it is out of scope for the server documentation to cover such things. Whomever wrote the systemd stuff for your O/S platform will hopefully have documented such things, otherwise I would advise to inspect the configuration files themselves. David J.
