Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-04-21 Thread Jeff Davis
On Sun, 2012-03-04 at 16:39 +, Simon Riggs wrote: v3 attached. Added to next commitfest. Regards, Jeff Davis -- 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] RFC: Making TRUNCATE more MVCC-safe

2012-03-09 Thread Simon Riggs
On Fri, Mar 9, 2012 at 3:46 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 7, 2012 at 5:41 PM, Simon Riggs si...@2ndquadrant.com wrote: Case #2 is certainly a problem for FrozenXID as well, because anything that's marked with FrozenXID is going to look visible to everybody, including

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-09 Thread Robert Haas
On Fri, Mar 9, 2012 at 2:59 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Mar 9, 2012 at 3:46 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 7, 2012 at 5:41 PM, Simon Riggs si...@2ndquadrant.com wrote: Case #2 is certainly a problem for FrozenXID as well, because anything

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-08 Thread Robert Haas
On Wed, Mar 7, 2012 at 5:41 PM, Simon Riggs si...@2ndquadrant.com wrote: Case #2 is certainly a problem for FrozenXID as well, because anything that's marked with FrozenXID is going to look visible to everybody, including our older snapshots.  And I gather you're saying it's also a problem for

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Noah Misch
On Tue, Mar 06, 2012 at 08:36:05AM -0500, Robert Haas wrote: On Tue, Mar 6, 2012 at 5:43 AM, Noah Misch n...@leadboat.com wrote: Well, consider something like CLUSTER. ?It's perfectly OK for CLUSTER to operate on a table that has been truncated since CLUSTER's snapshot was taken, and no

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Robert Haas
On Wed, Mar 7, 2012 at 7:49 AM, Noah Misch n...@leadboat.com wrote: On Tue, Mar 06, 2012 at 08:36:05AM -0500, Robert Haas wrote: On Tue, Mar 6, 2012 at 5:43 AM, Noah Misch n...@leadboat.com wrote: Well, consider something like CLUSTER. ?It's perfectly OK for CLUSTER to operate on a table

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Simon Riggs
On Wed, Mar 7, 2012 at 2:59 PM, Robert Haas robertmh...@gmail.com wrote: All true. So gentlemen, do we think this is worth pursuing further for this release? I'm sure usual arguments apply all round, so I'll skip that part. --  Simon Riggs   http://www.2ndQuadrant.com/  

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Robert Haas
On Wed, Mar 7, 2012 at 10:26 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Mar 7, 2012 at 2:59 PM, Robert Haas robertmh...@gmail.com wrote: All true. So gentlemen, do we think this is worth pursuing further for this release? I'm sure usual arguments apply all round, so I'll skip that

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Simon Riggs
On Wed, Mar 7, 2012 at 5:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 7, 2012 at 10:26 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Mar 7, 2012 at 2:59 PM, Robert Haas robertmh...@gmail.com wrote: All true. So gentlemen, do we think this is worth pursuing further for

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Robert Haas
On Wed, Mar 7, 2012 at 2:06 PM, Simon Riggs si...@2ndquadrant.com wrote: I am not thrilled with the design as it stands, but bulk loading is a known and serious pain point for us, so it would be awfully nice to improve it.  I'm not sure whether we should only go as far as setting

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-07 Thread Simon Riggs
On Wed, Mar 7, 2012 at 8:21 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 7, 2012 at 2:06 PM, Simon Riggs si...@2ndquadrant.com wrote: I am not thrilled with the design as it stands, but bulk loading is a known and serious pain point for us, so it would be awfully nice to improve

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-06 Thread Noah Misch
On Sun, Mar 04, 2012 at 01:02:57PM +, Simon Riggs wrote: More detailed thoughts show that the test in heap_beginscan_internal() is not right enough, i.e. wrong. We need a specific XidInMVCCSnapshot test on the relvalidxid, so it needs to be a specific xid, not an xmin because otherwise

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-06 Thread Noah Misch
On Mon, Mar 05, 2012 at 03:46:16PM -0500, Robert Haas wrote: On Mon, Mar 5, 2012 at 2:22 PM, Noah Misch n...@leadboat.com wrote: I can see this strategy applying to many relation-pertinent system catalogs. Do you foresee applications to non-relation catalogs? Well, in theory, we have

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-06 Thread Robert Haas
On Tue, Mar 6, 2012 at 5:43 AM, Noah Misch n...@leadboat.com wrote: Now, maybe we're never going to fix those kinds of anomalies anyway, but if we go with this architecture, then I think the chances of it ever being palatable to try are pretty low. Why? Because it'll require at least one XID

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Robert Haas
On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote: Marti, please review this latest version which has new isolation tests added. This does both TRUNCATE and CREATE TABLE. I don't see any need for a GUC to control this behavior. The current behavior is wrong, so if we're

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Simon Riggs
On Mon, Mar 5, 2012 at 4:32 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote: Marti, please review this latest version which has new isolation tests added. This does both TRUNCATE and CREATE TABLE. I don't see any need for

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Simon Riggs
On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote: It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal().  Surely this is just as much of a problem for an index-scan or index-only-scan. err, very good point. Doh.

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Robert Haas
On Mon, Mar 5, 2012 at 11:46 AM, Simon Riggs si...@2ndquadrant.com wrote: I agree behaviour is wrong, the only question is whether our users rely in some way on that behaviour. Given the long discussion on that point earlier I thought it best to add a GUC. Easy to remove, now or later.

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Robert Haas
On Mon, Mar 5, 2012 at 12:42 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote: It does not seem right that the logic for detecting the serialization error is in heap_beginscan_internal().  Surely this is just as much of a

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Noah Misch
On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ and not at READ COMMITTED. ?They tend to be narrow race conditions at READ

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Simon Riggs
On Mon, Mar 5, 2012 at 5:49 PM, Robert Haas robertmh...@gmail.com wrote: Well, there's no point that I can see in having two checks.  I just dislike the idea that we have to remember to add this check for every method of accessing the relation - doesn't seem terribly future-proof.  It gets

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Robert Haas
On Mon, Mar 5, 2012 at 2:22 PM, Noah Misch n...@leadboat.com wrote: On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ and not at READ

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Simon Riggs
On Mon, Mar 5, 2012 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote: In any event, I think a pg_class.relvalidxmin is the right starting point. One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin (already exists), inhvalidxmin, and attvalidxmin.  relvalidxmin is like the

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-04 Thread Simon Riggs
On Sun, Mar 4, 2012 at 2:28 AM, Marti Raudsepp ma...@juffo.org wrote: On Sat, Mar 3, 2012 at 14:53, Simon Riggs si...@2ndquadrant.com wrote: Thanks Noah for drawing attention to this thread. I hadn't been watching. As you say, this work would allow me to freeze rows at load time and avoid the

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-04 Thread Simon Riggs
On Sun, Mar 4, 2012 at 9:59 AM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, Mar 4, 2012 at 2:28 AM, Marti Raudsepp ma...@juffo.org wrote: On Sat, Mar 3, 2012 at 14:53, Simon Riggs si...@2ndquadrant.com wrote: Thanks Noah for drawing attention to this thread. I hadn't been watching. As you

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-04 Thread Simon Riggs
On Sun, Mar 4, 2012 at 1:02 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, Mar 4, 2012 at 9:59 AM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, Mar 4, 2012 at 2:28 AM, Marti Raudsepp ma...@juffo.org wrote: On Sat, Mar 3, 2012 at 14:53, Simon Riggs si...@2ndquadrant.com wrote: Thanks

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-03 Thread Simon Riggs
On Sat, Feb 11, 2012 at 4:46 AM, Noah Misch n...@leadboat.com wrote: But I have to admit I'm intrigued by the idea of extending this to other cases, if there's a reasonable way to do that.  For example, if we could fix things up so that we don't see a table at all if it was created after we

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-03-03 Thread Marti Raudsepp
On Sat, Mar 3, 2012 at 14:53, Simon Riggs si...@2ndquadrant.com wrote: Thanks Noah for drawing attention to this thread. I hadn't been watching. As you say, this work would allow me to freeze rows at load time and avoid the overhead of hint bit setting, which avoids performance issues from

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Robert Haas
On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote: On Fri, Feb 10, 2012 at 01:59:18PM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 6:42 AM, Noah Misch n...@leadboat.com wrote: I like the design you have chosen. ?It would find applications beyond TRUNCATE, so your use

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: The example that I remember was related to SELECT FOR UPDATE/SELECT FOR SHARE. The idea of those statements is that you want to prevent the row from being updated or deleted until some other concurrent action is complete; for example, in the case of

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Robert Haas
On Mon, Feb 13, 2012 at 10:48 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Well, personally I have a hard time calling READ COMMITTED behavior sensible.  Consider this: [ gigantic pile of fail ] Yeah, that's bad all right. I think it's hard to argue that the current behavior is

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-13 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Well, personally I have a hard time calling READ COMMITTED behavior sensible. Consider this: [ gigantic pile of fail ] Yeah, that's bad all right. I think it's hard to argue that the current

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-11 Thread Dan Ports
On Fri, Feb 10, 2012 at 01:59:18PM -0500, Robert Haas wrote: I guess I'm not particularly excited by the idea of trying to make TRUNCATE MVCC-safe. I notice that the example involves the REPEATABLE READ isolation level, which is already known to be busted in a variety of ways; that's why we

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-10 Thread Noah Misch
On Thu, Feb 09, 2012 at 11:11:16PM +0200, Marti Raudsepp wrote: I've always been a little wary of using the TRUNCATE command due to the warning in the documentation about it not being MVCC-safe: queries may silently give wrong results and it's hard to tell when they are affected. That got

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-10 Thread Robert Haas
On Fri, Feb 10, 2012 at 6:42 AM, Noah Misch n...@leadboat.com wrote: I like the design you have chosen.  It would find applications beyond TRUNCATE, so your use of non-specific naming is sound.  For example, older snapshots will see an empty table t after CREATE TABLE t AS SELECT 1 commits;

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

2012-02-10 Thread Noah Misch
On Fri, Feb 10, 2012 at 01:59:18PM -0500, Robert Haas wrote: On Fri, Feb 10, 2012 at 6:42 AM, Noah Misch n...@leadboat.com wrote: I like the design you have chosen. ?It would find applications beyond TRUNCATE, so your use of non-specific naming is sound. ?For example, older snapshots will