Re: Custom FDW - the results of a nested query/join not being passed as qual to the outer query
Many thanks for the fast response. Using an SRF is an interesting idea, I'll have a play and see if we can make that work. Cheers, Kai On Wed, Jan 27, 2021 at 3:27 PM Tom Lane wrote: > Kai Daguerre writes: > > We often have virtual tables where a list operation is not > viable/possible > > without providing quals. For example we have implemented a 'whois' table, > > which will retrieve whois information for specified domains. It is > clearly > > not practical to do an unqualified 'list' of *all* domains. > > In that case you're going to have to resign yourself to some queries > failing. This is unavoidable, consider "select * from whois". But > just because the query has a WHERE condition doesn't mean that a useful > restriction clause can be extracted for any particular table. > > I think the best you can do is (1) fail at runtime if there's no qual > and (2) at plan time, return an extremely high cost estimate for a > qual-less scan, in hopes of discouraging the planner from choosing > that. (Instead of (2), you could perhaps not generate a scan path > at all, but that's likely to lead to an unintelligible error message.) > > Perhaps you should rethink whether you really want a foreign table > rather than a set-returning function. With the SRF approach it's > automatic that the user must supply the restricting argument(s) you need. > > regards, tom lane >
running vacuumlo periodically?
Short question. Is it recommended - or even best practice - to run vacuumlo periodically as a routine maintenance task? We don't do it. I think if this would be recommended it would have been implemented as an autotask like autovacuum. No? Thanks, Markus
How to post to this mailing list from a web based interface
I am planning to switch to a web based tool to read this mailing list. While reading is easy via web, how do I post a reply from web.I recollect there use to be a website from where one can reply from web. thanks
Postgres blog sites centrally
Hi Is there a central place where i can get postgres blogs as they are written by different blog sites, e.g. can google news app be tuned to just bring postgres blogs only?
Re: Postgres blog sites centrally
Yambu schrieb am 28.01.2021 um 16:21: > Is there a central place where i can get postgres blogs as they are > written by different blog sites That would be https://planet.postgresql.org/
re: Postgres blog sites centrally
>Is there a central place where i can get postgres blogs as they are >written by different blog sites, e.g. can google news app be tuned to just bring postgres blogs only? I use https://planet.postgresql.org/
Re: How to post to this mailing list from a web based interface
On 2021-Jan-28, Ravi Krishna wrote: > I am planning to switch to a web based tool to read this mailing list. That's great. > While reading is easy via web, how do I post a reply from web. Yeah, "how" indeed. > I recollect there use to be a website from where one can reply from web. The community does not maintain such a service. There used to be a Gmane archive of this list that you could use to post. Seems it's still online at postgresql-archive.org. They have a "Reply" button and it says to require your account, but given SPF and DMARC and other restrictions on email generation, it seems pretty uncertain that emails posted that way would work correctly. I think we would even reject such emails if they reached our mailing list servers. -- Álvaro Herrera39°49'30"S 73°17'W
Re: How to post to this mailing list from a web based interface
On 1/28/21 9:27 AM, Alvaro Herrera wrote: On 2021-Jan-28, Ravi Krishna wrote: I am planning to switch to a web based tool to read this mailing list. That's great. While reading is easy via web, how do I post a reply from web. Yeah, "how" indeed. Gmail is a web interface... :) -- Angular momentum makes the world go 'round.
Re: How to post to this mailing list from a web based interface
> Gmail is a web interface... :) I was gonna say that Ron, but I figured it would not be polite to say the least
vacuum is time consuming
Hi, We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3% only. We have configured maintenance_work_mem to 10GBs and restarted the postgres service. We have done upgrade from postgres 9.5 to 9.6, then when we run vacuum analyze on database, it is taking more than 5 hours and still running. Any suggestions for making the process(vacuum analyze) faster are welcome.
Re: How to post to this mailing list from a web based interface
El día jueves, enero 28, 2021 a las 12:27:21p. m. -0300, Alvaro Herrera escribió: > On 2021-Jan-28, Ravi Krishna wrote: > > > I am planning to switch to a web based tool to read this mailing list. > > That's great. > > > While reading is easy via web, how do I post a reply from web. Everyone is free to use whatever he/she wants. For me a we based MUA would be the worst thing ever. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub ¡Con Cuba no te metas! «» Don't mess with Cuba! «» Leg Dich nicht mit Kuba an! http://www.cubadebate.cu/noticias/2020/12/25/en-video-con-cuba-no-te-metas/
Re: How to post to this mailing list from a web based interface
On Thu, Jan 28, 2021 at 4:27 PM Alvaro Herrera wrote: > > On 2021-Jan-28, Ravi Krishna wrote: > > > I am planning to switch to a web based tool to read this mailing list. > > That's great. > > > While reading is easy via web, how do I post a reply from web. > > Yeah, "how" indeed. > > > I recollect there use to be a website from where one can reply from web. > > The community does not maintain such a service. > > There used to be a Gmane archive of this list that you could use to > post. Seems it's still online at postgresql-archive.org. They have a > "Reply" button and it says to require your account, but given SPF and > DMARC and other restrictions on email generation, it seems pretty > uncertain that emails posted that way would work correctly. I think we > would even reject such emails if they reached our mailing list servers. Yeah, given the amount of trouble we've had around that one, I would strongly advise you not to use it. Another option is to read it through the web interface and use the "resend to me" functionality once you want to reply to something, and then reply to that through your normal email. It's not the most convenient workflow, but if you mostly read and only very seldom post, it works. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: How to post to this mailing list from a web based interface
> Everyone is free to use whatever he/she wants. For me a we based MUA > would be the worst thing ever. Oh well. I have created a seperate email account for this to keep the clutter out. thanks all who took time to reply to this.
Re: How to post to this mailing list from a web based interface
On 2021-Jan-28, Ravi Krishna wrote: > > Everyone is free to use whatever he/she wants. For me a we based MUA > > would be the worst thing ever. > > Oh well. I have created a seperate email account for this to keep the > clutter out. In any half-decent email program, you can tag incoming email in such a way that it doesn't clutter your main "inbox" view, while still having such tagged messages if you specifically request them. Just saying. -- Álvaro Herrera39°49'30"S 73°17'W
Re: running vacuumlo periodically?
On Thu, 2021-01-28 at 13:18 +, Zwettler Markus (OIZ) wrote: > Short question. Is it recommended - or even best practice – to run vacuumlo > periodically as a routine maintenance task? > > We don't do it. I think if this would be recommended it would have been > implemented as an autotask like autovacuum. No? It is recommended to run it regularly if - you are using large objects - you don't have a trigger in place that deletes large objects that you don't need any more Only a small minority of people do that, so it wouldn't make sense to automatically run that on all databases. Avoid large objects if you can. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Postgres freelancing sites
Hello May I know where I can get freelancer jobs for postgres? regards
Re: Postgres freelancing sites
On Thu, Jan 28, 2021 at 06:44:37PM +0200, Yambu wrote: > Hello > > May I know where I can get freelancer jobs for postgres? Well, we have a jobs email list: https://wiki.postgresql.org/wiki/Job_postings and instructions on how to post job _openings_. I think it can also be used for people who post looking for jobs. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
AW: running vacuumlo periodically?
> -Ursprüngliche Nachricht- > Von: Laurenz Albe > Gesendet: Donnerstag, 28. Januar 2021 17:39 > An: Zwettler Markus (OIZ) ; pgsql- > gene...@postgresql.org > Betreff: Re: running vacuumlo periodically? > > On Thu, 2021-01-28 at 13:18 +, Zwettler Markus (OIZ) wrote: > > Short question. Is it recommended - or even best practice – to run vacuumlo > periodically as a routine maintenance task? > > > > We don't do it. I think if this would be recommended it would have been > implemented as an autotask like autovacuum. No? > > It is recommended to run it regularly if > - you are using large objects > - you don't have a trigger in place that deletes large objects that you don't > need any more > > Only a small minority of people do that, so it wouldn't make sense to > automatically > run that on all databases. > > Avoid large objects if you can. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com [Zwettler Markus (OIZ)] We didn't recognize that an application is using large objects and didn't delete them. Now we found >100G dead large objects within the database. :-( Is there any _GENERIC_ query which enables monitoring for orphaned objects (dead LO)? select oid from pg_largeobject_metadata m where not exists (select 1 from ANY_EXISTING_TABLE_WITHIN_THE_DATABASE where m.oid = ANY_COLUMN_CONTAINING_OIDs); check_postgres.pl doesn't have any generic check for it. :-( Thanks, Markus
Re: vacuum is time consuming
Assuming that the system is online and in use, you may want to consider doing analyze first to ensure stats are proper, and then vacuum all less-used tables that should be quick then larger/more active tables with the verbose option to see details of why it might be taking time. If you are seeing multiple index scan steps, then maintenance_work_mem is lower than ideal. If you are using default vacuum settings, there is no I/O throttling as I understand the vacuum_cost_delay and vacuum_cost_limit settings. What is your concern with it taking time? Depending how you did the upgrade, there may be no (new/special) need to do the vacuum at all also.
Does pg_ctl promote wait for pending WAL?
Does anybody know whether a standby server waits for pending WAL records/files while promotion is requested? I assume that no data update is performed on the primary server while promotion. I imagine that a standby server stops to replay WAL and promotes as soon as SIGUSR1 signal is received. The motivation of this question behind is I want to have a complete copy of the primary server using promote command. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Re: Does pg_ctl promote wait for pending WAL?
Hi Tatsuo, > On 29. Jan, 2021, at 03:51, Tatsuo Ishii wrote: > > Does anybody know whether a standby server waits for pending WAL > records/files while promotion is requested? I assume that no data > update is performed on the primary server while promotion. > > I imagine that a standby server stops to replay WAL and promotes as > soon as SIGUSR1 signal is received. > > The motivation of this question behind is I want to have a complete > copy of the primary server using promote command. do I understand you correctly that you wait until there is no activity on the primary and want to promote the replica only then? If this is the case, you should be able to safely shutdown the primary anyway at any time to be assured that the replica doesn't receive any more WAL files and is complete. Then promote the replica and restart the primary. From then on, the replica is no replica anymore but has a life on its own with its own timeline. Cheers, Paul
Re: Does pg_ctl promote wait for pending WAL?
Hi Ishii-san, On Fri, Jan 29, 2021 at 07:59:26AM +0100, Paul Förster wrote: > On 29. Jan, 2021, at 03:51, Tatsuo Ishii wrote: >> >> Does anybody know whether a standby server waits for pending WAL >> records/files while promotion is requested? I assume that no data >> update is performed on the primary server while promotion. >> >> I imagine that a standby server stops to replay WAL and promotes as >> soon as SIGUSR1 signal is received. To answer to your question based on the code, you can check for the code paths calling CheckForStandbyTrigger() in xlog.c when it comes to promotion detection in the WAL replay. In short, WaitForWALToBecomeAvailable() tells that after the promotion is detected in the startup process, then recovery would still try to replay as much WAL as possible from the archives or pg_wal before a failover. Equally, from the docs: "Before failover, any WAL immediately available in the archive or in pg_wal will be restored, but no attempt is made to connect to the primary." -- Michael signature.asc Description: PGP signature
Re: Does pg_ctl promote wait for pending WAL?
Hi Michael, > To answer to your question based on the code, you can check for the > code paths calling CheckForStandbyTrigger() in xlog.c when it comes to > promotion detection in the WAL replay. In short, > WaitForWALToBecomeAvailable() tells that after the promotion is > detected in the startup process, then recovery would still try to > replay as much WAL as possible from the archives or pg_wal before a > failover. Great. That should make my life a lot easier. I will look into the code to confirm it. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
