Re: Checksum errors in pg_stat_database

Hi, On 2022-12-11 20:48:15 -0500, Tom Lane wrote: > Michael Paquier writes: > > On Sun, Dec 11, 2022 at 04:51:49PM -0800, Andres Freund wrote: > >> I think there's a good argument for starting to track some stats based on > >> the > >> relfilenode, rather the oid, because it'd allow us to track

Re: Checksum errors in pg_stat_database

On 12/12/22 8:15 AM, Drouvot, Bertrand wrote: On 12/12/22 5:09 AM, Michael Paquier wrote: On Sun, Dec 11, 2022 at 08:48:15PM -0500, Tom Lane wrote: I think a stats table indexed solely by relfilenode wouldn't be a great idea, because you'd lose all the stats about a table as soon as you

Re: Checksum errors in pg_stat_database

On Mon, Dec 12, 2022 at 12:40 AM Michael Paquier wrote: > On Sun, Dec 11, 2022 at 09:18:42PM +0100, Magnus Hagander wrote: > > It would be less of a concern yes, but I think it still would be a > concern. > > If you have a large amount of corruption you could quickly get to > millions > > of

Re: Checksum errors in pg_stat_database

On 12/12/22 5:09 AM, Michael Paquier wrote: On Sun, Dec 11, 2022 at 08:48:15PM -0500, Tom Lane wrote: I think a stats table indexed solely by relfilenode wouldn't be a great idea, because you'd lose all the stats about a table as soon as you do vacuum full/cluster/rewriting-alter-table/etc.

Re: Checksum errors in pg_stat_database

On 12/12/22 12:40 AM, Michael Paquier wrote: On Sun, Dec 11, 2022 at 09:18:42PM +0100, Magnus Hagander wrote: It would be less of a concern yes, but I think it still would be a concern. If you have a large amount of corruption you could quickly get to millions of rows to keep track of which

Re: Checksum errors in pg_stat_database

On Sun, Dec 11, 2022 at 08:48:15PM -0500, Tom Lane wrote: > I think a stats table indexed solely by relfilenode wouldn't be a great > idea, because you'd lose all the stats about a table as soon as you > do vacuum full/cluster/rewriting-alter-table/etc. Can we create two > index structures over

Re: Checksum errors in pg_stat_database

Michael Paquier writes: > On Sun, Dec 11, 2022 at 04:51:49PM -0800, Andres Freund wrote: >> I think there's a good argument for starting to track some stats based on the >> relfilenode, rather the oid, because it'd allow us to track e.g. the number >> of >> writes for a relation too (we don't

Re: Checksum errors in pg_stat_database

On Sun, Dec 11, 2022 at 04:51:49PM -0800, Andres Freund wrote: > Why were you thinking of tracking it separately from PgStat_StatTabEntry? We only know the relfilenode when loading the page on a checksum failure, not its parent relation, and there are things like physical base backups where we

Re: Checksum errors in pg_stat_database

Hi, On 2022-12-12 08:40:04 +0900, Michael Paquier wrote: > What about just adding a counter tracking the number of checksum > failures for relfilenodes in a new structure related to them (note > that I did not write PgStat_StatTabEntry)? Why were you thinking of tracking it separately from

Re: Checksum errors in pg_stat_database

On Sun, Dec 11, 2022 at 09:18:42PM +0100, Magnus Hagander wrote: > It would be less of a concern yes, but I think it still would be a concern. > If you have a large amount of corruption you could quickly get to millions > of rows to keep track of which would definitely be a problem in shared >

Re: Checksum errors in pg_stat_database

On Thu, Dec 8, 2022 at 2:35 PM Drouvot, Bertrand < bertranddrouvot...@gmail.com> wrote: > > > On 4/2/19 7:06 PM, Magnus Hagander wrote: > > On Tue, Apr 2, 2019 at 8:47 AM Michael Paquier > wrote: > > > > On Tue, Apr 02, 2019 at 07:43:12AM +0200, Julien Rouhaud

Re: Checksum errors in pg_stat_database

On 4/2/19 7:06 PM, Magnus Hagander wrote: On Tue, Apr 2, 2019 at 8:47 AM Michael Paquier mailto:mich...@paquier.xyz>> wrote: On Tue, Apr 02, 2019 at 07:43:12AM +0200, Julien Rouhaud wrote: > On Tue, Apr 2, 2019 at 6:56 AM Michael Paquier mailto:mich...@paquier.xyz>> wrote: >> 

Re: Checksum errors in pg_stat_database

On Wed, Apr 17, 2019 at 9:07 AM Julien Rouhaud wrote: > > On Wed, Apr 17, 2019 at 1:55 PM Magnus Hagander wrote: > > > > On Tue, Apr 16, 2019 at 5:39 PM Robert Treat wrote: > >> > >> On Mon, Apr 15, 2019 at 3:32 PM Julien Rouhaud wrote: > >> > > >> > I don't know if that counts as an open

Re: Checksum errors in pg_stat_database

On Wed, Apr 17, 2019 at 1:55 PM Magnus Hagander wrote: > > On Tue, Apr 16, 2019 at 5:39 PM Robert Treat wrote: >> >> On Mon, Apr 15, 2019 at 3:32 PM Julien Rouhaud wrote: >> > >> > I don't know if that counts as an open item, but I attach a patch for >> > all points discussed here. >> >> ISTM

Re: Checksum errors in pg_stat_database

On Tue, Apr 16, 2019 at 5:39 PM Robert Treat wrote: > On Mon, Apr 15, 2019 at 3:32 PM Julien Rouhaud wrote: > > > > Sorry for late reply, > > > > On Sun, Apr 14, 2019 at 7:12 PM Magnus Hagander > wrote: > > > > > > On Sat, Apr 13, 2019 at 8:46 PM Robert Treat wrote: > > >> > > >> On Fri, Apr

Re: Checksum errors in pg_stat_database

On Mon, Apr 15, 2019 at 3:32 PM Julien Rouhaud wrote: > > Sorry for late reply, > > On Sun, Apr 14, 2019 at 7:12 PM Magnus Hagander wrote: > > > > On Sat, Apr 13, 2019 at 8:46 PM Robert Treat wrote: > >> > >> On Fri, Apr 12, 2019 at 8:18 AM Magnus Hagander > >> wrote: > >> ISTM the argument

Re: Checksum errors in pg_stat_database

Sorry for late reply, On Sun, Apr 14, 2019 at 7:12 PM Magnus Hagander wrote: > > On Sat, Apr 13, 2019 at 8:46 PM Robert Treat wrote: >> >> On Fri, Apr 12, 2019 at 8:18 AM Magnus Hagander wrote: >> ISTM the argument here is go with zero since you have zero connections >> vs go with null since

Re: Checksum errors in pg_stat_database

On Sat, Apr 13, 2019 at 8:46 PM Robert Treat wrote: > > On Fri, Apr 12, 2019 at 8:18 AM Magnus Hagander > wrote: > > On Sun, Apr 7, 2019 at 6:28 PM Julien Rouhaud > wrote: > >> Thanks for looking it it! > >> On Sun, Apr 7, 2019 at 4:36 PM Magnus Hagander > wrote: > >> > > >> > I'm not sure I

Re: Checksum errors in pg_stat_database

I started looking at this the other night but I see Magnus beat me in committing it... On Fri, Apr 12, 2019 at 8:18 AM Magnus Hagander wrote: > On Sun, Apr 7, 2019 at 6:28 PM Julien Rouhaud wrote: >> Thanks for looking it it! >> On Sun, Apr 7, 2019 at 4:36 PM Magnus Hagander wrote: >> > >> >

Re: Checksum errors in pg_stat_database

On Fri, Apr 12, 2019 at 2:18 PM Magnus Hagander wrote: > > On Sun, Apr 7, 2019 at 6:28 PM Julien Rouhaud wrote: >> >> v5 attached. > > Thanks. Pushed! Thanks!

Re: Checksum errors in pg_stat_database

On Sun, Apr 7, 2019 at 6:28 PM Julien Rouhaud wrote: > Thanks for looking it it! > > On Sun, Apr 7, 2019 at 4:36 PM Magnus Hagander > wrote: > > > > I'm not sure I like the idea of using "" as the database > name. It's not very likely that somebody would be using that as a name for > their

Re: Checksum errors in pg_stat_database

Thanks for looking it it! On Sun, Apr 7, 2019 at 4:36 PM Magnus Hagander wrote: > > I'm not sure I like the idea of using "" as the database > name. It's not very likely that somebody would be using that as a name for > their database, but i's not impossible. But it also just looks strrange.

Re: Checksum errors in pg_stat_database

On Thu, Apr 4, 2019 at 2:52 PM Julien Rouhaud wrote: > On Thu, Apr 4, 2019 at 1:25 PM Magnus Hagander > wrote: > > > > On Thu, Apr 4, 2019 at 10:47 AM Julien Rouhaud > wrote: > >> > >> Actually we do track counters for shared relations (see > >> pgstat_report_stat), we just don't expose them

Re: Checksum errors in pg_stat_database

On Thu, Apr 4, 2019 at 1:25 PM Magnus Hagander wrote: > > On Thu, Apr 4, 2019 at 10:47 AM Julien Rouhaud wrote: >> >> Actually we do track counters for shared relations (see >> pgstat_report_stat), we just don't expose them in any view. But it's >> still possible to get the counters manually:

Re: Checksum errors in pg_stat_database

On Thu, Apr 4, 2019 at 10:47 AM Julien Rouhaud wrote: > On Thu, Apr 4, 2019 at 10:25 AM Magnus Hagander > wrote: > > > > On Thu, Apr 4, 2019 at 6:22 AM Michael Paquier > wrote: > >> > >> On Wed, Apr 03, 2019 at 11:56:14AM +0200, Julien Rouhaud wrote: > >> > But there's still the problem of

Re: Checksum errors in pg_stat_database

On Thu, Apr 4, 2019 at 10:25 AM Magnus Hagander wrote: > > On Thu, Apr 4, 2019 at 6:22 AM Michael Paquier wrote: >> >> On Wed, Apr 03, 2019 at 11:56:14AM +0200, Julien Rouhaud wrote: >> > But there's still the problem of reporting errors on shared relation, >> > so pg_stat_database doesn't

Re: Checksum errors in pg_stat_database

On Thu, Apr 4, 2019 at 6:22 AM Michael Paquier wrote: > On Wed, Apr 03, 2019 at 11:56:14AM +0200, Julien Rouhaud wrote: > > But there's still the problem of reporting errors on shared relation, > > so pg_stat_database doesn't really fit for that. If we go with a > > checksum centric view, it'd

Re: Checksum errors in pg_stat_database

On Wed, Apr 03, 2019 at 11:56:14AM +0200, Julien Rouhaud wrote: > But there's still the problem of reporting errors on shared relation, > so pg_stat_database doesn't really fit for that. If we go with a > checksum centric view, it'd be strange to have some of the counters in > another view.

Re: Checksum errors in pg_stat_database

On Wed, Apr 3, 2019 at 11:31 AM Magnus Hagander wrote: > > On Wed, Apr 3, 2019 at 10:44 AM Julien Rouhaud wrote: >> >> On Wed, Apr 3, 2019 at 3:43 AM Michael Paquier wrote: >> > >> > > I can somewhat agree that splitting it on a per database level might >> > > even >> > > at that be overdoing

Re: Checksum errors in pg_stat_database

On Wed, Apr 3, 2019 at 10:44 AM Julien Rouhaud wrote: > On Wed, Apr 3, 2019 at 3:43 AM Michael Paquier > wrote: > > > > > I can somewhat agree that splitting it on a per database level might > even > > > at that be overdoing it. What might actually be more interesting from a > > >

Re: Checksum errors in pg_stat_database

On Wed, Apr 3, 2019 at 3:43 AM Michael Paquier wrote: > > > I can somewhat agree that splitting it on a per database level might even > > at that be overdoing it. What might actually be more interesting from a > > failure-location perspective would be tablespace, rather than any of the > >

Re: Checksum errors in pg_stat_database

On Tue, Apr 02, 2019 at 07:06:35PM +0200, Magnus Hagander wrote: > I think having the count and hte last time make sense, but I'm very > sceptical about the rest. There may be other things which we are not considering on this thread. I don't know. > I can somewhat agree that splitting it on a

Re: Checksum errors in pg_stat_database

On Tue, Apr 2, 2019 at 8:47 AM Michael Paquier wrote: > On Tue, Apr 02, 2019 at 07:43:12AM +0200, Julien Rouhaud wrote: > > On Tue, Apr 2, 2019 at 6:56 AM Michael Paquier > wrote: > >> One thing which is not > >> proposed on this patch, and I am fine with it as a first draft, is > >> that we

Re: Checksum errors in pg_stat_database

On Tue, Apr 02, 2019 at 07:43:12AM +0200, Julien Rouhaud wrote: > On Tue, Apr 2, 2019 at 6:56 AM Michael Paquier wrote: >> One thing which is not >> proposed on this patch, and I am fine with it as a first draft, is >> that we don't have any information about the broken block number and >> the

Re: Checksum errors in pg_stat_database

On Tue, Apr 2, 2019 at 6:56 AM Michael Paquier wrote: > > On Sat, Mar 30, 2019 at 06:15:11PM +0100, Julien Rouhaud wrote: > > I'd also have to get more feedback on this. For now, I'll add this > > thread to the pg12 open items, as a follow up of the initial code > > drop. > > Catching up here...

Re: Checksum errors in pg_stat_database

On Sat, Mar 30, 2019 at 06:15:11PM +0100, Julien Rouhaud wrote: > I'd also have to get more feedback on this. For now, I'll add this > thread to the pg12 open items, as a follow up of the initial code > drop. Catching up here... I think that having a completely separate view with one row for

Re: Checksum errors in pg_stat_database

Sorry for delay, I had to catch a train. On Sat, Mar 30, 2019 at 4:02 PM Magnus Hagander wrote: > > My vote is still to drop it completely, but if we're keeping it, it has to go > in both paths. Ok. For now I'm attaching v2, which drops this field, rename the view to pg_stat_checksums

Re: Checksum errors in pg_stat_database

On Sat, Mar 30, 2019 at 3:55 PM Julien Rouhaud wrote: > On Sat, Mar 30, 2019 at 2:33 PM Magnus Hagander > wrote: > > > > On Wed, Mar 13, 2019 at 4:54 PM Julien Rouhaud > wrote: > >> > >> On Wed, Mar 13, 2019 at 4:53 PM Julien Rouhaud > wrote: > >> > > >> > As a result I ended up simply adding

Re: Checksum errors in pg_stat_database

On Sat, Mar 30, 2019 at 2:33 PM Magnus Hagander wrote: > > On Wed, Mar 13, 2019 at 4:54 PM Julien Rouhaud wrote: >> >> On Wed, Mar 13, 2019 at 4:53 PM Julien Rouhaud wrote: >> > >> > As a result I ended up simply adding counters for the number of total >> > checks and the timestamp of the last

Re: Checksum errors in pg_stat_database

On Wed, Mar 13, 2019 at 4:54 PM Julien Rouhaud wrote: > On Wed, Mar 13, 2019 at 4:53 PM Julien Rouhaud wrote: > > > > On Sun, Mar 10, 2019 at 1:13 PM Julien Rouhaud > wrote: > > > > > > On Sat, Mar 9, 2019 at 7:58 PM Julien Rouhaud > wrote: > > > > > > > > On Sat, Mar 9, 2019 at 7:50 PM

Re: Checksum errors in pg_stat_database

On Wed, Mar 13, 2019 at 4:53 PM Julien Rouhaud wrote: > > On Sun, Mar 10, 2019 at 1:13 PM Julien Rouhaud wrote: > > > > On Sat, Mar 9, 2019 at 7:58 PM Julien Rouhaud wrote: > > > > > > On Sat, Mar 9, 2019 at 7:50 PM Magnus Hagander > > > wrote: > > > > > > > > On Sat, Mar 9, 2019 at 10:41 AM

Re: Checksum errors in pg_stat_database

On Sun, Mar 10, 2019 at 1:13 PM Julien Rouhaud wrote: > > On Sat, Mar 9, 2019 at 7:58 PM Julien Rouhaud wrote: > > > > On Sat, Mar 9, 2019 at 7:50 PM Magnus Hagander wrote: > > > > > > On Sat, Mar 9, 2019 at 10:41 AM Julien Rouhaud wrote: > > >> > > >> Sorry, I have again new comments after a

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 7:58 PM Julien Rouhaud wrote: > > On Sat, Mar 9, 2019 at 7:50 PM Magnus Hagander wrote: > > > > On Sat, Mar 9, 2019 at 10:41 AM Julien Rouhaud wrote: > >> > >> Sorry, I have again new comments after a little bit more thinking. > >> I'm wondering if we can do something

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 7:50 PM Magnus Hagander wrote: > > On Sat, Mar 9, 2019 at 10:41 AM Julien Rouhaud wrote: >> >> Sorry, I have again new comments after a little bit more thinking. >> I'm wondering if we can do something about shared objects while we're >> at it. They don't belong to any

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 7:48 PM Magnus Hagander wrote: > > On Sat, Mar 9, 2019 at 12:33 AM Julien Rouhaud wrote: >> >> Thanks! Our implementations are quite similar, so I'm fine with most >> of the changes :) I'm just not sure about having two distinct >> functions for reporting failures, given

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 10:41 AM Julien Rouhaud wrote: > On Sat, Mar 9, 2019 at 9:34 AM Julien Rouhaud wrote: > > > > On Sat, Mar 9, 2019 at 12:35 AM Magnus Hagander > wrote: > > > > > > On Mon, Mar 4, 2019 at 11:31 AM Julien Rouhaud > wrote: > > >> > > >> On Fri, Feb 22, 2019 at 3:01 PM

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 12:33 AM Julien Rouhaud wrote: > On Sat, Mar 9, 2019 at 12:35 AM Magnus Hagander > wrote: > > > > On Mon, Mar 4, 2019 at 11:31 AM Julien Rouhaud > wrote: > >> > >> On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander > wrote: > >> > > >> > It tracks things that happen in the

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 9:34 AM Julien Rouhaud wrote: > > On Sat, Mar 9, 2019 at 12:35 AM Magnus Hagander wrote: > > > > On Mon, Mar 4, 2019 at 11:31 AM Julien Rouhaud wrote: > >> > >> On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander > >> wrote: > >> > > >> > It tracks things that happen in the

Re: Checksum errors in pg_stat_database

On Sat, Mar 9, 2019 at 12:35 AM Magnus Hagander wrote: > > On Mon, Mar 4, 2019 at 11:31 AM Julien Rouhaud wrote: >> >> On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander wrote: >> > >> > It tracks things that happen in the general backends. Possibly we should >> > also consider counting the

Re: Checksum errors in pg_stat_database

On Mon, Mar 4, 2019 at 11:31 AM Julien Rouhaud wrote: > On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander > wrote: > > > > It tracks things that happen in the general backends. Possibly we should > also consider counting the errors actually found when running base backups? > OTOH, that part of

Re: Checksum errors in pg_stat_database

On Mon, Mar 4, 2019 at 8:31 PM Julien Rouhaud wrote: > > On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander wrote: > > > > It tracks things that happen in the general backends. Possibly we should > > also consider counting the errors actually found when running base backups? > > OTOH, that part

Re: Checksum errors in pg_stat_database

On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander wrote: > > It tracks things that happen in the general backends. Possibly we should also > consider counting the errors actually found when running base backups? OTOH, > that part of the code doesn't really track things like databases (as it >

Re: Checksum errors in pg_stat_database

On Fri, Feb 22, 2019 at 3:25 PM Magnus Hagander wrote: > > On Fri, Feb 22, 2019 at 3:23 PM Magnus Hagander wrote: >> >> >> >> On Fri, Feb 22, 2019 at 3:16 PM Julien Rouhaud wrote: >>> >>> On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander wrote: >>> > >>> > PFA is a patch to do this. >>> >>>

Re: Checksum errors in pg_stat_database

On Fri, Feb 22, 2019 at 3:23 PM Magnus Hagander wrote: > > > On Fri, Feb 22, 2019 at 3:16 PM Julien Rouhaud wrote: > >> On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander >> wrote: >> > >> > PFA is a patch to do this. >> >> +void >> +pgstat_report_checksum_failure(void) >> +{ >> +

Re: Checksum errors in pg_stat_database

On Fri, Feb 22, 2019 at 3:16 PM Julien Rouhaud wrote: > On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander > wrote: > > > > PFA is a patch to do this. > > +void > +pgstat_report_checksum_failure(void) > +{ > + PgStat_MsgDeadlock msg; > > I think that you meant PgStat_MsgChecksumFailure :) > >

Re: Checksum errors in pg_stat_database

On Fri, Feb 22, 2019 at 3:01 PM Magnus Hagander wrote: > > PFA is a patch to do this. +void +pgstat_report_checksum_failure(void) +{ + PgStat_MsgDeadlock msg; I think that you meant PgStat_MsgChecksumFailure :) +/* -- + * pgstat_recv_checksum_failure() - + * + * Process a DEADLOCK

Re: Checksum errors in pg_stat_database

On Sat, Jan 12, 2019 at 5:16 AM David Steele wrote: > On 1/11/19 10:25 PM, Magnus Hagander wrote: > > On Fri, Jan 11, 2019 at 9:20 PM Tomas Vondra > > On 1/11/19 7:40 PM, Robert Haas wrote: > > > But I'm tentatively in favor of your proposal anyway, because it's > > > pretty simple

Re: Checksum errors in pg_stat_database

On 1/11/19 10:25 PM, Magnus Hagander wrote: On Fri, Jan 11, 2019 at 9:20 PM Tomas Vondra On 1/11/19 7:40 PM, Robert Haas wrote: > But I'm tentatively in favor of your proposal anyway, because it's > pretty simple and cheap and might help people, and doing something >

Re: Checksum errors in pg_stat_database

On Fri, Jan 11, 2019 at 9:20 PM Tomas Vondra wrote: > > > > On 1/11/19 7:40 PM, Robert Haas wrote: > > On Fri, Jan 11, 2019 at 5:21 AM Magnus Hagander > wrote: > >> Would it make sense to add a column to pg_stat_database showing > >> the total number of checksum errors that have occurred in a

Re: Checksum errors in pg_stat_database

On 1/11/19 7:40 PM, Robert Haas wrote: > On Fri, Jan 11, 2019 at 5:21 AM Magnus Hagander wrote: >> Would it make sense to add a column to pg_stat_database showing >> the total number of checksum errors that have occurred in a database? >> >> It's really a ">1 means it's bad", but it's a lot

Re: Checksum errors in pg_stat_database

On Fri, Jan 11, 2019 at 5:21 AM Magnus Hagander wrote: > Would it make sense to add a column to pg_stat_database showing the total > number of checksum errors that have occurred in a database? > > It's really a ">1 means it's bad", but it's a lot easier to monitor that in > the statistics

Checksum errors in pg_stat_database

Would it make sense to add a column to pg_stat_database showing the total number of checksum errors that have occurred in a database? It's really a ">1 means it's bad", but it's a lot easier to monitor that in the statistics views, and given how much a lot of people set their systems out to log,