Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-19 Thread Andres Freund
On 2014-02-18 18:10:02 -0800, Peter Geoghegan wrote:
 On Tue, Feb 18, 2014 at 5:50 PM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
  Peter Geoghegan wrote:
  I've had multiple complaints of apparent data loss on 9.3.2 customer
  databases. There are 2 total, both complaints from the past week, one
  of which I was able to confirm. The customer's complaint is that
  certain rows are either visible or invisible, depending on whether an
  index scan is used or a sequential scan (I confirmed this with an
  EXPLAIN ANALYZE).
 
  The multixact bugs would cause tuples to be hidden at the heap level.
  If the tuples are visible in a seqscan, then these are more likely to be
  related to index problems, not multixact problem.
 
 I guess I wasn't clear enough here: The row in question was visible
 with a sequential scans but *not* with an index scan. So you have it
 backwards here (understandably).

Was there an index only scan or just a index scan? Any chance of a
corrupted index?

Do you still have the page from before you did the VACUUM?

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-19 Thread Peter Geoghegan
On Wed, Feb 19, 2014 at 12:40 AM, Andres Freund and...@2ndquadrant.com wrote:
 Was there an index only scan or just a index scan? Any chance of a
 corrupted index?

Just an index scan. I think it's unlikely to be a corrupt index,
because the customer said that he dropped and restored the index, and
it's difficult to imagine how VACUUM FREEZE could then have impacted a
plan with only a sequential scan.

 Do you still have the page from before you did the VACUUM?

It seems likely that I could get it, given that the problem persisted
over several days (apparently there was an earlier, manual attempt to
repair the damage by hand). I'm reasonably confident that I could get
back an affected database by performing a point-in-time recovery. That
would also give me the benefit of an environment that I could break as
needed.


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-19 Thread Andres Freund
On 2014-02-19 00:55:03 -0800, Peter Geoghegan wrote:
 On Wed, Feb 19, 2014 at 12:40 AM, Andres Freund and...@2ndquadrant.com 
 wrote:
  Was there an index only scan or just a index scan? Any chance of a
  corrupted index?
 
 Just an index scan. I think it's unlikely to be a corrupt index,
 because the customer said that he dropped and restored the index, and
 it's difficult to imagine how VACUUM FREEZE could then have impacted a
 plan with only a sequential scan.

I am wondering whether it's possibly either the vm or the all-visible
flag on the page...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-18 Thread Alvaro Herrera
Peter Geoghegan wrote:
 I've had multiple complaints of apparent data loss on 9.3.2 customer
 databases. There are 2 total, both complaints from the past week, one
 of which I was able to confirm. The customer's complaint is that
 certain rows are either visible or invisible, depending on whether an
 index scan is used or a sequential scan (I confirmed this with an
 EXPLAIN ANALYZE).

The multixact bugs would cause tuples to be hidden at the heap level.
If the tuples are visible in a seqscan, then these are more likely to be
related to index problems, not multixact problem.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-18 Thread Peter Geoghegan
On Tue, Feb 18, 2014 at 5:50 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 The multixact bugs would cause tuples to be hidden at the heap level.
 If the tuples are visible in a seqscan, then these are more likely to be
 related to index problems, not multixact problem.

That was my first suspicion, but then re-indexing didn't help, while
VACUUM FREEZE had the immediate effect of making both plans give a
consistent answer.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-18 Thread Peter Geoghegan
On Tue, Feb 18, 2014 at 5:56 PM, Peter Geoghegan p...@heroku.com wrote:
 That was my first suspicion, but then re-indexing didn't help, while
 VACUUM FREEZE had the immediate effect of making both plans give a
 consistent answer.

I should add that this is an unremarkable int4 primary key (which was
dropped and recreated by the customer). There is no obvious reason why
we should see wrong answers that are attributable to application code
or environment.


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Is anyone aware of data loss causing MultiXact bugs in 9.3.2?

2014-02-18 Thread Peter Geoghegan
On Tue, Feb 18, 2014 at 5:50 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Peter Geoghegan wrote:
 I've had multiple complaints of apparent data loss on 9.3.2 customer
 databases. There are 2 total, both complaints from the past week, one
 of which I was able to confirm. The customer's complaint is that
 certain rows are either visible or invisible, depending on whether an
 index scan is used or a sequential scan (I confirmed this with an
 EXPLAIN ANALYZE).

 The multixact bugs would cause tuples to be hidden at the heap level.
 If the tuples are visible in a seqscan, then these are more likely to be
 related to index problems, not multixact problem.

I guess I wasn't clear enough here: The row in question was visible
with a sequential scans but *not* with an index scan. So you have it
backwards here (understandably).


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers