Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-21 Thread Michael Paquier
On Tue, Nov 22, 2016 at 3:06 AM, Robert Haas wrote: > I don't think that you should skip it in the wraparound case, because > it might be one of the temp tables that is threatening wraparound. > > I've committed the patch after doing some work on the comments. OK, thanks.

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-21 Thread Robert Haas
On Sun, Nov 20, 2016 at 10:42 PM, Michael Paquier wrote: > On Sat, Nov 19, 2016 at 2:16 PM, Michael Paquier > wrote: >> On Fri, Nov 18, 2016 at 1:11 PM, Robert Haas wrote: >>> That might sound adding unnecessary work

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-20 Thread Michael Paquier
On Sat, Nov 19, 2016 at 2:16 PM, Michael Paquier wrote: > On Fri, Nov 18, 2016 at 1:11 PM, Robert Haas wrote: >> That might sound adding unnecessary work just for the sake of >> paranoia, but I don't think it is. Failures here won't be common,

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-18 Thread Michael Paquier
On Fri, Nov 18, 2016 at 1:11 PM, Robert Haas wrote: > So now I think that we probably need to make this logic a bit smarter. > Add all of the OIDs that need to be dropped to a list. Then have a > loop prior to the main loop (where it says "Perform operations on > collected

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-18 Thread Robert Haas
On Thu, Nov 17, 2016 at 4:25 PM, Michael Paquier wrote: > On Thu, Nov 17, 2016 at 11:16 AM, Robert Haas wrote: >> On Thu, Nov 17, 2016 at 1:41 PM, Michael Paquier >> wrote: >>> Okay, let's remove the documentation

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-18 Thread Dilip Kumar
On Thu, Nov 17, 2016 at 6:54 AM, Haribabu Kommi wrote: > you assigned as reviewer to the current patch in the 11-2016 commitfest. > But you haven't shared your review yet. Can you please try to share your > views > about the patch. This will help us in smoother operation

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-17 Thread Michael Paquier
On Thu, Nov 17, 2016 at 11:16 AM, Robert Haas wrote: > On Thu, Nov 17, 2016 at 1:41 PM, Michael Paquier > wrote: >> Okay, let's remove the documentation then. What do you think about the >> updated version attached? >> (Even this patch enters

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-17 Thread Robert Haas
On Thu, Nov 17, 2016 at 1:41 PM, Michael Paquier wrote: > Okay, let's remove the documentation then. What do you think about the > updated version attached? > (Even this patch enters into "Needs Review" state). LGTM. I'll commit it if there are not objections. --

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-17 Thread Michael Paquier
On Thu, Nov 17, 2016 at 7:37 AM, Robert Haas wrote: > The whole point of the review process is to get an opinion from > somebody other than the original author on the patch in question. If > you write a patch and then mark your own patch Ready for Committer, > you're

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-17 Thread Robert Haas
On Wed, Nov 16, 2016 at 11:14 PM, Michael Paquier wrote: > Hm. Thinking about that again, having a GUC to control if orphaned > temp tables in autovacuum is an overkill (who is going to go into this > level of tuning!?) and that we had better do something more

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-16 Thread Michael Paquier
On Wed, Nov 16, 2016 at 5:24 PM, Haribabu Kommi wrote: > This is a gentle reminder. > > you assigned as reviewer to the current patch in the 11-2016 commitfest. > But you haven't shared your review yet. Can you please try to share your > views > about the patch. This

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-11-16 Thread Haribabu Kommi
Hi Dilip, This is a gentle reminder. you assigned as reviewer to the current patch in the 11-2016 commitfest. But you haven't shared your review yet. Can you please try to share your views about the patch. This will help us in smoother operation of commitfest. Michael had sent an updated patch

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-22 Thread Michael Paquier
On Sat, Oct 22, 2016 at 9:45 AM, Tom Lane wrote: > Michael Paquier writes: >> On Sat, Oct 22, 2016 at 12:15 AM, Jim Nasby wrote: >>> On 10/21/16 8:47 AM, Tom Lane wrote: If we are willing to do that then we don't

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-21 Thread Tom Lane
Michael Paquier writes: > On Sat, Oct 22, 2016 at 12:15 AM, Jim Nasby wrote: >> On 10/21/16 8:47 AM, Tom Lane wrote: >>> If we are willing to do that then we don't really have to solve the >>> problem on the backend side. One could expect

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-21 Thread Michael Paquier
On Sat, Oct 22, 2016 at 12:15 AM, Jim Nasby wrote: > On 10/21/16 8:47 AM, Tom Lane wrote: >>> >>> It seems to me that you'd even want to make the drop of orphaned >>> > tables mandatory once it is detected even it is not a wraparound >>> > autovacuum. >> >> If we are

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-21 Thread Jim Nasby
On 10/21/16 8:47 AM, Tom Lane wrote: It seems to me that you'd even want to make the drop of orphaned > tables mandatory once it is detected even it is not a wraparound > autovacuum. If we are willing to do that then we don't really have to solve the problem on the backend side. One could

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-21 Thread Tom Lane
Michael Paquier writes: > On Thu, Oct 20, 2016 at 9:30 PM, Constantin S. Pan wrote: >> I tried to fix the problem with a new backend not being >> able to reuse a temporary namespace when it contains >> thousands of temporary tables. I disabled locking

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-21 Thread Constantin S. Pan
On Fri, 21 Oct 2016 14:29:24 +0900 Michael Paquier wrote: > That's invasive. I am wondering if a cleaner approach here would be a > flag in deleteOneObject() that performs the lock cleanup, as that's > what you are trying to solve here. The problem occurs earlier, at

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-20 Thread Michael Paquier
On Fri, Oct 21, 2016 at 2:29 PM, Michael Paquier wrote: > On Thu, Oct 20, 2016 at 9:30 PM, Constantin S. Pan wrote: >> I tried to fix the problem with a new backend not being >> able to reuse a temporary namespace when it contains >> thousands of

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-20 Thread Michael Paquier
On Thu, Oct 20, 2016 at 9:30 PM, Constantin S. Pan wrote: > I tried to fix the problem with a new backend not being > able to reuse a temporary namespace when it contains > thousands of temporary tables. I disabled locking of objects > during namespace clearing process. See the

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-20 Thread Michael Paquier
On Thu, Sep 8, 2016 at 12:38 AM, Robert Haas wrote: > On Mon, Sep 5, 2016 at 1:14 PM, Bruce Momjian wrote: >> I don't think we look at those temp tables frequently enough to justify >> keeping them around for all users. > > +1. I think it would be much

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-10-20 Thread Constantin S. Pan
On Mon, 5 Sep 2016 14:54:05 +0300 Grigory Smolkin wrote: > Hello, hackers! > > We were testing how well some application works with PostgreSQL and > stumbled upon an autovacuum behavior which I fail to understand. > Application in question have a habit to heavily use

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-07 Thread Robert Haas
On Mon, Sep 5, 2016 at 1:14 PM, Bruce Momjian wrote: > On Mon, Sep 5, 2016 at 12:48:32PM -0300, Alvaro Herrera wrote: >> > The least invasive solution would be to have a guc, something like >> > 'keep_orphan_temp_tables' with boolean value. >> > Which would determine a

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-06 Thread Jim Nasby
On 9/5/16 12:14 PM, Bruce Momjian wrote: > I have certainly faced my fair share of customers with dangling temp > tables, and would like to see this changed in some way or another. I don't think we look at those temp tables frequently enough to justify keeping them around for all users. Plus,

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Bruce Momjian
On Mon, Sep 5, 2016 at 12:48:32PM -0300, Alvaro Herrera wrote: > > The least invasive solution would be to have a guc, something like > > 'keep_orphan_temp_tables' with boolean value. > > Which would determine a autovacuum worker policy toward encountered orphan > > temp tables. > > The stated

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Alvaro Herrera
Grigory Smolkin wrote: > > On 09/05/2016 04:34 PM, Alvaro Herrera wrote: > >Grigory Smolkin wrote: > > > >>Funny part is that it never drops them. So when backend is finally > >>terminated, it tries to drop them and fails with error: > >> > >>FATAL: out of shared memory > >>HINT: You might need

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Grigory Smolkin
On 09/05/2016 04:34 PM, Alvaro Herrera wrote: Grigory Smolkin wrote: Funny part is that it never drops them. So when backend is finally terminated, it tries to drop them and fails with error: FATAL: out of shared memory HINT: You might need to increase max_locks_per_transaction If I

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Alvaro Herrera
Grigory Smolkin wrote: > Funny part is that it never drops them. So when backend is finally > terminated, it tries to drop them and fails with error: > > FATAL: out of shared memory > HINT: You might need to increase max_locks_per_transaction > > If I understand that rigth, we are trying to

Re: [HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Vik Fearing
On 09/05/2016 01:54 PM, Grigory Smolkin wrote: > What is the purpose of keeping orphan tables around and not dropping > them on the spot? You can read the discussion about it here: https://www.postgresql.org/message-id/flat/3507.1214581513%40sss.pgh.pa.us -- Vik Fearing

[HACKERS] Fun fact about autovacuum and orphan temp tables

2016-09-05 Thread Grigory Smolkin
Hello, hackers! We were testing how well some application works with PostgreSQL and stumbled upon an autovacuum behavior which I fail to understand. Application in question have a habit to heavily use temporary tables in funny ways. For example it creates A LOT of them. Which is ok. Funny