Re: ERROR: found multixact from before relminmxid

2018-11-09 Thread Adrien NAYRAT
On 11/7/18 1:21 PM, Alexandre Arruda wrote: The best solution that I have found is kick all connections and execute a select for update to /dev/null in the affected tables, i.e.: psql -o /dev/null -c "select * from table for update" database After this, the vacuum is executed w/o problems agai

Re: ERROR: found multixact from before relminmxid

2018-11-07 Thread Alexandre Arruda
The best solution that I have found is kick all connections and execute a select for update to /dev/null in the affected tables, i.e.: psql -o /dev/null -c "select * from table for update" database After this, the vacuum is executed w/o problems again. Best regards, Alexandre Em qua, 7 de nov

Re: ERROR: found multixact from before relminmxid

2018-11-07 Thread Adrien NAYRAT
On 11/5/18 5:41 PM, Adrien NAYRAT wrote: We "solved" with a dump/restore on another server, also we kept previous cluster to investigate. I want to be sure we encountered the bug solved in 9.6.9 and it is not a new one. FYI, I am not sure I will be able to keep previous cluster many days. I

Re: ERROR: found multixact from before relminmxid

2018-11-05 Thread Adrien NAYRAT
On 6/8/18 8:30 PM, Jeremy Finzel wrote:  No I was referring to this from the documentation: Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, Álvaro Herrera) This could happen if some tuples were locked (but not deleted). While queries would still functi

Re: ERROR: found multixact from before relminmxid

2018-07-13 Thread Alvaro Herrera
On 2018-Jul-13, Sean McIntyre wrote: > Quick correction :) > > I am running PostgreSQL *9.6.8* on AWS RDS. I'm going to try to pg-repack > the table and will look to upgrade to *9.6.9* (though it's not presently > available on RDS). Yay RDS ... -- Álvaro Herrerahttps://www.2ndQ

Re: ERROR: found multixact from before relminmxid

2018-07-13 Thread Sean McIntyre
ote: > Dear all, > > I just wanted to report that I found similar error "found multixact from > before relminmxid" that Alexandre Arruda and Jeremy Finzel recently > reported and discussed with Andres Freund. (Thanks all for good notes.) > > I get this error when vacuuming

ERROR: found multixact from before relminmxid

2018-07-13 Thread Sean McIntyre
Dear all, I just wanted to report that I found similar error "found multixact from before relminmxid" that Alexandre Arruda and Jeremy Finzel recently reported and discussed with Andres Freund. (Thanks all for good notes.) I get this error when vacuuming a specific table in my high-t

Re: ERROR: found multixact from before relminmxid

2018-06-12 Thread Andres Freund
Hi, On 2018-06-08 13:30:33 -0500, Jeremy Finzel wrote: >Avoid spuriously marking pages as all-visible (Dan Wood, Pavan Deolasee, >Álvaro Herrera) > >This could happen if some tuples were locked (but not deleted). While >queries would still function correctly, vacuum would normally

Re: ERROR: found multixact from before relminmxid

2018-06-11 Thread Jeremy Finzel
> > Is there any chance I could get access > to a copy of the data? It's very hard to debug something like this > without something that can reproduce the issue... > It would be very difficult for us to be able to clean and anonymize this data and provide a snapshot publicly. But I am very willin

Re: ERROR: found multixact from before relminmxid

2018-06-08 Thread Jeremy Finzel
On Fri, Jun 8, 2018 at 1:08 PM Andres Freund wrote: > On 2018-06-08 12:38:03 -0500, Jeremy Finzel wrote: > > On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda > wrote: > > > > > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > > > escreveu: > > > > > > > > Hi, > > > > > > > > I think I found

Re: ERROR: found multixact from before relminmxid

2018-06-08 Thread Andres Freund
On 2018-06-08 12:38:03 -0500, Jeremy Finzel wrote: > On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda wrote: > > > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > > escreveu: > > > > > > Hi, > > > > > > I think I found the bug, and am about to post a fix for it belo > > > https://postgr.es/m

Re: ERROR: found multixact from before relminmxid

2018-06-08 Thread Jeremy Finzel
On Tue, Jun 5, 2018 at 8:42 PM, Alexandre Arruda wrote: > Em seg, 28 de mai de 2018 às 16:44, Andres Freund > escreveu: > > > > Hi, > > > > I think I found the bug, and am about to post a fix for it belo > > https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. > > > > Greetings

Re: ERROR: found multixact from before relminmxid

2018-06-05 Thread Alexandre Arruda
Em seg, 28 de mai de 2018 às 16:44, Andres Freund escreveu: > > Hi, > > I think I found the bug, and am about to post a fix for it belo > https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. > > Greetings, > > Andres Freund Hi Andres, In end of April we did a complete dump/relo

Re: ERROR: found multixact from before relminmxid

2018-05-28 Thread Andres Freund
Hi, I think I found the bug, and am about to post a fix for it belo https://postgr.es/m/20180525203736.crkbg36muzxrj...@alap3.anarazel.de. Greetings, Andres Freund

Re: ERROR: found multixact from before relminmxid

2018-04-11 Thread Alexandre Arruda
2018-04-10 19:53 GMT-03:00 Andres Freund : > On 2018-04-10 08:31:20 -0300, Alexandre Arruda wrote: >> 2018-04-09 23:51 GMT-03:00 Peter Geoghegan : >> > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda >> > wrote: >> >> (... and all other indexes returns null too) >> >> >> >> I tried with bt_index

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Andres Freund
On April 10, 2018 11:51:40 PM PDT, Pavan Deolasee wrote: >On Wed, Apr 11, 2018 at 8:31 AM, Alexandre Arruda >wrote: > >> 2018-04-10 19:09 GMT-03:00 Peter Geoghegan : >> > On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda > >> wrote: >> >> Actualy, I first notice the problem in logs by autovacu

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Pavan Deolasee
On Wed, Apr 11, 2018 at 8:31 AM, Alexandre Arruda wrote: > 2018-04-10 19:09 GMT-03:00 Peter Geoghegan : > > On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda > wrote: > >> Actualy, I first notice the problem in logs by autovacuum: > >> > >> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT: automatic

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Alexandre Arruda
2018-04-10 19:09 GMT-03:00 Peter Geoghegan : > On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda wrote: >> Actualy, I first notice the problem in logs by autovacuum: >> >> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT: automatic vacuum of >> table "production.public.fn06t" >> 2018-04-10 08:22:16.81

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Peter Geoghegan
On Tue, Apr 10, 2018 at 7:54 PM, Alexandre Arruda wrote: > pg_control version number:1002 Andres was also asking about his check_rel() function, from https://www.postgresql.org/message-id/20180319181723.ugaf7hfkluqyo...@alap3.anarazel.de. Can you check that out as well, please? You'll

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Alexandre Arruda
2018-04-10 19:53 GMT-03:00 Andres Freund : > On 2018-04-10 08:31:20 -0300, Alexandre Arruda wrote: >> 2018-04-09 23:51 GMT-03:00 Peter Geoghegan : >> > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda >> > wrote: >> >> (... and all other indexes returns null too) >> >> >> >> I tried with bt_index

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Andres Freund
On 2018-04-10 08:31:20 -0300, Alexandre Arruda wrote: > 2018-04-09 23:51 GMT-03:00 Peter Geoghegan : > > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda wrote: > >> (... and all other indexes returns null too) > >> > >> I tried with bt_index_check too. Same results. > > > > That's interesting, be

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Peter Geoghegan
On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda wrote: > Actualy, I first notice the problem in logs by autovacuum: > > 2018-04-10 08:22:15.385 -03 [55477] CONTEXT: automatic vacuum of > table "production.public.fn06t" > 2018-04-10 08:22:16.815 -03 [55477] ERROR: found multixact 68834765 > fro

Re: ERROR: found multixact from before relminmxid

2018-04-10 Thread Alexandre Arruda
2018-04-09 23:51 GMT-03:00 Peter Geoghegan : > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda wrote: >> (... and all other indexes returns null too) >> >> I tried with bt_index_check too. Same results. > > That's interesting, because it tells me that you have a table that > appears to not be cor

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Peter Geoghegan
On Mon, Apr 9, 2018 at 7:53 PM, Andres Freund wrote: > I've not followed this thread. Possible it's the overeager check for pg > upgraded tuples from before 9.3 that Alvaro fixed recently? I was aware of commit 477ad05e, which must be what you're referring to. I don't think that that's what this

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Andres Freund
On April 9, 2018 7:51:19 PM PDT, Peter Geoghegan wrote: >On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda >wrote: >> (... and all other indexes returns null too) >> >> I tried with bt_index_check too. Same results. > >That's interesting, because it tells me that you have a table that >appears t

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Peter Geoghegan
On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda wrote: > (... and all other indexes returns null too) > > I tried with bt_index_check too. Same results. That's interesting, because it tells me that you have a table that appears to not be corrupt, despite the CLUSTER error. Also, the error itself

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Alexandre Arruda
2018-04-09 22:16 GMT-03:00 Peter Geoghegan : > On Mon, Apr 9, 2018 at 5:55 PM, Alexandre Arruda wrote: >> I ran amcheck in all index of a table and I only get empty returns. > > Did you try doing so with the "heapallindexed" option? That's what's > really interesting here. > > -- > Peter Geoghegan

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Peter Geoghegan
On Mon, Apr 9, 2018 at 5:55 PM, Alexandre Arruda wrote: > I ran amcheck in all index of a table and I only get empty returns. Did you try doing so with the "heapallindexed" option? That's what's really interesting here. -- Peter Geoghegan

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Alexandre Arruda
2018-04-09 18:23 GMT-03:00 Peter Geoghegan : > > On Mon, Apr 9, 2018 at 7:01 AM, Tomas Vondra > wrote: > > The bigger question is whether this can actually detect the issue. If > > it's due to an storage issue, then perhaps yes. But if you only see > > multixact issues consistently and nothing els

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Peter Geoghegan
On Mon, Apr 9, 2018 at 7:01 AM, Tomas Vondra wrote: > The bigger question is whether this can actually detect the issue. If > it's due to an storage issue, then perhaps yes. But if you only see > multixact issues consistently and nothing else, it might easily be a > PostgreSQL bug (in which case t

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Tomas Vondra
On 04/09/2018 01:49 PM, Alexandre Arruda wrote: > > > 2018-04-06 13:11 GMT-03:00 Tomas Vondra >: > > > > On 04/06/2018 04:29 PM, Alexandre Arruda wrote: > > 2018-04-06 9:39 GMT-03:00 Tomas Vondra > >

Re: ERROR: found multixact from before relminmxid

2018-04-09 Thread Alexandre Arruda
2018-04-06 13:11 GMT-03:00 Tomas Vondra : > > > On 04/06/2018 04:29 PM, Alexandre Arruda wrote: > > 2018-04-06 9:39 GMT-03:00 Tomas Vondra > >: > > > > > > > > On 04/06/2018 02:09 AM, Alexandre Arruda wrote: > > > Hi, > > > > > > Some time ago,

Re: ERROR: found multixact from before relminmxid

2018-04-06 Thread Tomas Vondra
On 04/06/2018 04:29 PM, Alexandre Arruda wrote: > 2018-04-06 9:39 GMT-03:00 Tomas Vondra >: > > > > On 04/06/2018 02:09 AM, Alexandre Arruda wrote: > > Hi, > > > > Some time ago, I had this errors frequently showed in logs after some >

Re: ERROR: found multixact from before relminmxid

2018-04-06 Thread Alexandre Arruda
2018-04-06 9:39 GMT-03:00 Tomas Vondra : > > > On 04/06/2018 02:09 AM, Alexandre Arruda wrote: > > Hi, > > > > Some time ago, I had this errors frequently showed in logs after some > > autovacuum in some tables(pg 9.6). VACUUM FULL or CLUSTER in this tables > > show the same and not complete the t

Re: ERROR: found multixact from before relminmxid

2018-04-06 Thread Tomas Vondra
On 04/06/2018 02:09 AM, Alexandre Arruda wrote: > Hi, > > Some time ago, I had this errors frequently showed in logs after some > autovacuum in some tables(pg 9.6). VACUUM FULL or CLUSTER in this tables > show the same and not complete the tasks (showed by some table bloat > select). > Then, I d

ERROR: found multixact from before relminmxid

2018-04-05 Thread Alexandre Arruda
Hi, Some time ago, I had this errors frequently showed in logs after some autovacuum in some tables(pg 9.6). VACUUM FULL or CLUSTER in this tables show the same and not complete the tasks (showed by some table bloat select). Then, I did a full dump/restore into a new version (10.2) and everything