Thread: pgsql: Extract catalog info for error reporting before an error actually

Log Message:
-----------
Extract catalog info for error reporting before an error actually happens.
Also, remove redundant reset of for-wraparound PGPROC flag.

Thanks to Tom Lane for noticing both bogosities.

Modified Files:
--------------
    pgsql/src/backend/postmaster:
        autovacuum.c (r1.63 -> r1.64)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c?r1=1.63&r2=1.64)

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?).

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

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"

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 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

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

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

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