Thread: Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually

Simon Riggs wrote:
> On Thu, 2007-10-25 at 14:45 +0000, Alvaro Herrera wrote:
> > Log Message:
> > -----------
> > Extract catalog info for error reporting before an error actually happens.
> > Also, remove redundant reset of for-wraparound PGPROC flag.
>
> Just noticed you've made these changes. I was working on them, but
> hadn't fully tested the patch because of all the different touch points.
> Sorry for the delay.
>
> Would you like me to refresh my earlier patch against the newly
> committed state (or did you commit that aspect already as well?).

I am doing just that.  I'll post it soon.

FWIW I disagree with cancelling just any autovac work automatically; in
my patch I'm only cancelling if it's analyze, on the grounds that if
you have really bad luck you can potentially lose a lot of work that
vacuum did.  We can relax this restriction when we have cancellable
vacuum.

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"La principal característica humana es la tontería"
(Augusto Monterroso)

On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > On Thu, 2007-10-25 at 14:45 +0000, Alvaro Herrera wrote:
> > > Log Message:
> > > -----------
> > > Extract catalog info for error reporting before an error actually happens.
> > > Also, remove redundant reset of for-wraparound PGPROC flag.
> >
> > Just noticed you've made these changes. I was working on them, but
> > hadn't fully tested the patch because of all the different touch points.
> > Sorry for the delay.
> >
> > Would you like me to refresh my earlier patch against the newly
> > committed state (or did you commit that aspect already as well?).
>
> I am doing just that.  I'll post it soon.

OK thanks.

> FWIW I disagree with cancelling just any autovac work automatically; in
> my patch I'm only cancelling if it's analyze, on the grounds that if
> you have really bad luck you can potentially lose a lot of work that
> vacuum did.  We can relax this restriction when we have cancellable
> vacuum.

That was requested by others, not myself, but I did agree with the
conclusions. The other bad luck might be that you don't complete some
critical piece of work in the available time window because an automated
job kicked in.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Simon Riggs wrote:
> On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote:
...
>> FWIW I disagree with cancelling just any autovac work automatically; in
>> my patch I'm only cancelling if it's analyze, on the grounds that if
>> you have really bad luck you can potentially lose a lot of work that
>> vacuum did.  We can relax this restriction when we have cancellable
>> vacuum.
>
> That was requested by others, not myself, but I did agree with the
> conclusions. The other bad luck might be that you don't complete some
> critical piece of work in the available time window because an automated
> job kicked in.

Yeah, I thought we had agreed that we must cancel all auto
vacuum/analyzes, on the ground that foreground operations are usually
more important than maintenance tasks. Remember the complaint we already
had on hackers just after beta1: auto *vacuum* blocked a schema change,
and of course the user complained.

Best Regards
Michael Paesold

Michael Paesold wrote:
> Simon Riggs wrote:
>> On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote:
> ...
>>> FWIW I disagree with cancelling just any autovac work automatically; in
>>> my patch I'm only cancelling if it's analyze, on the grounds that if
>>> you have really bad luck you can potentially lose a lot of work that
>>> vacuum did.  We can relax this restriction when we have cancellable
>>> vacuum.
>> That was requested by others, not myself, but I did agree with the
>> conclusions. The other bad luck might be that you don't complete some
>> critical piece of work in the available time window because an automated
>> job kicked in.
>
> Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes,
> on the ground that foreground operations are usually more important than
> maintenance tasks.

What this means is that autovacuum will be starved a lot of the time,
and in the end you will only vacuum the tables when you run out of time
for Xid wraparound.

> Remember the complaint we already had on hackers just after beta1:
> auto *vacuum* blocked a schema change, and of course the user
> complained.

Actually I can't remember it, but I think we should decree that this is
a known shortcoming; that we will fix it when we have cancellable
vacuum; and that the user is free to cancel the vacuuming on his own if
he so decides.

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"The ability to monopolize a planet is insignificant
next to the power of the source"

Alvaro Herrera wrote:
> Michael Paesold wrote:
>> Simon Riggs wrote:
>>> On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote:
>> ...
>>>> FWIW I disagree with cancelling just any autovac work automatically; in
>>>> my patch I'm only cancelling if it's analyze, on the grounds that if
>>>> you have really bad luck you can potentially lose a lot of work that
>>>> vacuum did.  We can relax this restriction when we have cancellable
>>>> vacuum.
>>> That was requested by others, not myself, but I did agree with the
>>> conclusions. The other bad luck might be that you don't complete some
>>> critical piece of work in the available time window because an automated
>>> job kicked in.
>> Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes,
>> on the ground that foreground operations are usually more important than
>> maintenance tasks.
>
> What this means is that autovacuum will be starved a lot of the time,
> and in the end you will only vacuum the tables when you run out of time
> for Xid wraparound.

Well, only if you do a lot of schema changes. In the previous
discussion, Simon and me agreed that schema changes should not happen on
a regular basis on production systems.

Shouldn't we rather support the regular usage pattern instead of the
uncommon one? Users doing a lot of schema changes are the ones who
should have to work around issues, not those using a DBMS sanely. No?

Best Regards
Michael Paesold

Michael Paesold wrote:
> In the previous discussion, Simon and me agreed that schema changes
> should not happen on a regular basis on production systems.
>
> Shouldn't we rather support the regular usage pattern instead of the
> uncommon one? Users doing a lot of schema changes are the ones who
> should have to work around issues, not those using a DBMS sanely. No?
>
>

Unfortunately, doing lots of schema changes is a very common phenomenon.
It makes me uncomfortable too, but saying that those who do it have to
work around issues isn't acceptable IMNSHO - it's far too widely done.

cheers

andrew

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Michael Paesold wrote:
>> Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, 
>> on the ground that foreground operations are usually more important than 
>> maintenance tasks.

> What this means is that autovacuum will be starved a lot of the time,

Not really, because DDL changes aren't *that* common (at least not for
non-temp tables).  I think the consensus was that we should cancel
autovac in these cases unless it is an anti-wraparound vacuum.  Isn't
that why you were putting in the flag to show it is for wraparound?
        regards, tom lane

Andrew Dunstan <andrew@dunslane.net> writes:
> Michael Paesold wrote:
>> Shouldn't we rather support the regular usage pattern instead of the 
>> uncommon one? Users doing a lot of schema changes are the ones who 
>> should have to work around issues, not those using a DBMS sanely. No?

> Unfortunately, doing lots of schema changes is a very common phenomenon. 
> It makes me uncomfortable too, but saying that those who do it have to 
> work around issues isn't acceptable IMNSHO - it's far too widely done.

Well, there's going to be pain *somewhere* here, and we already know
that users will find the current 8.3 behavior unacceptable.  I'd rather
have autovacuum not make progress than have users turn it off because it
gets in their way too much.  Which I think is exactly what will happen
if we ship it with the current behavior.
        regards, tom lane

On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote:
>
> Michael Paesold wrote:
> > In the previous discussion, Simon and me agreed that schema changes
> > should not happen on a regular basis on production systems.
> >
> > Shouldn't we rather support the regular usage pattern instead of the
> > uncommon one? Users doing a lot of schema changes are the ones who
> > should have to work around issues, not those using a DBMS sanely. No?
> >
> Unfortunately, doing lots of schema changes is a very common phenomenon.
> It makes me uncomfortable too, but saying that those who do it have to
> work around issues isn't acceptable IMNSHO - it's far too widely done.

We didn't agree that DDL was uncommon, we agreed that running DDL was
more important than running an auto VACUUM. DDL runs very quickly,
unless blocked, though holds up everybody else. So you must run it at
pre-planned windows. VACUUMs can run at any time, so a autoVACUUM
shouldn't be allowed to prevent DDL from running. The queuing DDL makes
other requests queue behind it, even ones that would normally have been
able to execute at same time as the VACUUM.

Anyway, we covered all this before. I started off saying we shouldn't do
this and Heikki and Michael came up with convincing arguments, for me,
so now I think we should allow autovacuums to be cancelled.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > Michael Paesold wrote:
> >> Shouldn't we rather support the regular usage pattern instead of the 
> >> uncommon one? Users doing a lot of schema changes are the ones who 
> >> should have to work around issues, not those using a DBMS sanely. No?
> 
> > Unfortunately, doing lots of schema changes is a very common phenomenon. 
> > It makes me uncomfortable too, but saying that those who do it have to 
> > work around issues isn't acceptable IMNSHO - it's far too widely done.
> 
> Well, there's going to be pain *somewhere* here, and we already know
> that users will find the current 8.3 behavior unacceptable.  I'd rather
> have autovacuum not make progress than have users turn it off because it
> gets in their way too much.  Which I think is exactly what will happen
> if we ship it with the current behavior.

Agreed.  If auto-vacuum is blocking activity, it isn't very 'auto' to
me.  If vacuum is blocking something, by definition it is a bad time for
it to be vacuuming and it should abort and try again later.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Simon Riggs wrote:
> On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote:
>
>> Michael Paesold wrote:
>>
>>> In the previous discussion, Simon and me agreed that schema changes
>>> should not happen on a regular basis on production systems.
>>>
>>> Shouldn't we rather support the regular usage pattern instead of the
>>> uncommon one? Users doing a lot of schema changes are the ones who
>>> should have to work around issues, not those using a DBMS sanely. No?
>>>
>>>
>> Unfortunately, doing lots of schema changes is a very common phenomenon.
>> It makes me uncomfortable too, but saying that those who do it have to
>> work around issues isn't acceptable IMNSHO - it's far too widely done.
>>
>
> We didn't agree that DDL was uncommon, we agreed that running DDL was
> more important than running an auto VACUUM. DDL runs very quickly,
> unless blocked, though holds up everybody else. So you must run it at
> pre-planned windows. VACUUMs can run at any time, so a autoVACUUM
> shouldn't be allowed to prevent DDL from running. The queuing DDL makes
> other requests queue behind it, even ones that would normally have been
> able to execute at same time as the VACUUM.
>
> Anyway, we covered all this before. I started off saying we shouldn't do
> this and Heikki and Michael came up with convincing arguments, for me,
> so now I think we should allow autovacuums to be cancelled.
>
>

Perhaps I misunderstood, or have been mistunderstood :-) - I am actually
agreeing that autovac should not block DDL.

cheers

andrew

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Michael Paesold wrote:
>>> Shouldn't we rather support the regular usage pattern instead of the 
>>> uncommon one? Users doing a lot of schema changes are the ones who 
>>> should have to work around issues, not those using a DBMS sanely. No?
> 
>> Unfortunately, doing lots of schema changes is a very common phenomenon. 
>> It makes me uncomfortable too, but saying that those who do it have to 
>> work around issues isn't acceptable IMNSHO - it's far too widely done.
> 
> Well, there's going to be pain *somewhere* here, and we already know
> that users will find the current 8.3 behavior unacceptable.  I'd rather
> have autovacuum not make progress than have users turn it off because it
> gets in their way too much.  Which I think is exactly what will happen
> if we ship it with the current behavior.

exactly - 8.3 will be the first release with autovac enabled by default
(and concurrent autovacuuming) and we really need to make sure that
people wont get surprised by it in the way I (and others) did when
playing with Beta1.
So my vote would be on cancelling always except in the case of a
wraparound vacuum.

Stefan

On Thu, Oct 25, 2007 at 03:54:28PM -0400, Andrew Dunstan wrote:
> Simon Riggs wrote:
> >On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote:
> >>Michael Paesold wrote:
> >>
> >>>In the previous discussion, Simon and me agreed that schema
> >>>changes should not happen on a regular basis on production
> >>>systems.
> >>>
> >>>Shouldn't we rather support the regular usage pattern instead of
> >>>the uncommon one? Users doing a lot of schema changes are the
> >>>ones who should have to work around issues, not those using a
> >>>DBMS sanely. No?
> >>>
> >>Unfortunately, doing lots of schema changes is a very common
> >>phenomenon.  It makes me uncomfortable too, but saying that those
> >>who do it have to work around issues isn't acceptable IMNSHO -
> >>it's far too widely done.
> >
> >We didn't agree that DDL was uncommon, we agreed that running DDL
> >was more important than running an auto VACUUM. DDL runs very
> >quickly, unless blocked, though holds up everybody else. So you
> >must run it at pre-planned windows. VACUUMs can run at any time, so
> >a autoVACUUM shouldn't be allowed to prevent DDL from running. The
> >queuing DDL makes other requests queue behind it, even ones that
> >would normally have been able to execute at same time as the
> >VACUUM.
> >
> >Anyway, we covered all this before. I started off saying we
> >shouldn't do this and Heikki and Michael came up with convincing
> >arguments, for me, so now I think we should allow autovacuums to be
> >cancelled.
>
> Perhaps I misunderstood, or have been mistunderstood :-) - I am
> actually agreeing that autovac should not block DDL.

+1 here for having autovacuum not block DDL :)

Cheers,
David (for what it's worth)
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate