Re: Read Uncommitted

On Fri, 20 Dec 2019 at 12:18, Tom Lane wrote: > Craig Ringer writes: > > My understanding from reading the above is that Simon didn't propose to > > make aborted txns visible, only in-progress uncommitted txns. > > Yeah, but an "in-progress uncommitted txn" can become an "aborted txn" > at any m

Re: Read Uncommitted

Craig Ringer writes: > My understanding from reading the above is that Simon didn't propose to > make aborted txns visible, only in-progress uncommitted txns. Yeah, but an "in-progress uncommitted txn" can become an "aborted txn" at any moment, and there's no interlock that would prevent its gene

Re: Read Uncommitted

On Thu, 19 Dec 2019 at 23:36, Andres Freund wrote: > Hi, > > > On the patch as proposed this wouldn't be possible because a toast row > > can't be vacuumed and then reused while holding back xmin, at least as I > > understand it. > > Vacuum and pruning remove rows where xmin didn't commit, withou

Re: Read Uncommitted

Hi, On 2019-12-19 07:08:06 -0800, Mark Dilger wrote: > > As soon as a transaction aborts, the TOAST rows can be vacuumed > > away, but the READ UNCOMMITTED transaction might've already seen the > > main tuple. This is not even a particularly tight race, necessarily, > > since for example the table

Re: Read Uncommitted

Hi, On 2019-12-19 09:50:44 +, Simon Riggs wrote: > On Thu, 19 Dec 2019 at 02:22, Andres Freund wrote: > > > Hi, > > > > On 2019-12-18 18:06:21 +, Simon Riggs wrote: > > > On Wed, 18 Dec 2019 at 17:35, Robert Haas wrote: > > > > > > > On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs > > > >

Re: Read Uncommitted

On 12/19/19 7:08 AM, Mark Dilger wrote: and instead get NULLs for all such rows To clarify, I mean the toasted column is null for rows where the value was stored in the toast table rather than stored inline. I'd prefer some special value that means "this datum unavailable" so that it could

Re: Read Uncommitted

On 12/19/19 1:50 AM, Simon Riggs wrote: It seems possible that catalog access would be the thing that makes this difficult. Cache invalidations wouldn't yet have been fired, so that would lead to rather weird errors, and as you say, potential issues from data type changes which would not be

Re: Read Uncommitted

On Thu, 19 Dec 2019 at 12:42, Bernd Helmle wrote: > Am Donnerstag, den 19.12.2019, 00:13 + schrieb Simon Riggs: > > So the consensus is for a more-specifically named facility. > > > > I was aiming for something that would allow general SELECTs to run > > with a > > snapshot that can see uncom

Re: Read Uncommitted

Am Donnerstag, den 19.12.2019, 00:13 + schrieb Simon Riggs: > So the consensus is for a more-specifically named facility. > > I was aiming for something that would allow general SELECTs to run > with a > snapshot that can see uncommitted xacts, so making it a SRF wouldn't > really > allow that

Re: Read Uncommitted

On 2019-12-18 16:14, Simon Riggs wrote: On Wed, 18 Dec 2019 at 12:11, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: As far as I understand with "read uncommitted" policy we can see two versions of the same tuple if it was updated by two transactions both of which wer

Re: Read Uncommitted

On Thu, 19 Dec 2019 at 02:22, Andres Freund wrote: > Hi, > > On 2019-12-18 18:06:21 +, Simon Riggs wrote: > > On Wed, 18 Dec 2019 at 17:35, Robert Haas wrote: > > > > > On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs > > > wrote: > > > > This was my first concern when I thought about it, but I

Re: Read Uncommitted regression test coverage

On 12/18/19 2:17 PM, Tom Lane wrote: Mark Dilger writes: The one in src/test/isolation doesn't look very comprehensive.  I'd at least expect a test that verifies you don't get a syntax error when you request READ UNCOMMITTED isolation from SQL. The attached patch set adds a modicum of test

Re: Read Uncommitted

Hi, On 2019-12-18 18:06:21 +, Simon Riggs wrote: > On Wed, 18 Dec 2019 at 17:35, Robert Haas wrote: > > > On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs > > wrote: > > > This was my first concern when I thought about it, but I realised that > > by taking a snapshot and then calculating xmin n

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 19:29, Heikki Linnakangas wrote: > On 18/12/2019 20:46, Mark Dilger wrote: > > On 12/18/19 10:06 AM, Simon Riggs wrote: > >> Just consider this part of the recovery toolkit. > > > > In that case, don't call it "read uncommitted". Call it some other > > thing entirely. Use

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 20:36, Tom Lane wrote: > "Finnerty, Jim" writes: > > Many will want to use it to do aggregation, e.g. a much more efficient > COUNT(*), because they want performance and don't care very much about > transaction consistency. E.g. they want to compute SUM(sales) by > salesp

Re: Read Uncommitted

On 12/18/19 2:29 PM, Heikki Linnakangas wrote: On 18/12/2019 20:46, Mark Dilger wrote: On 12/18/19 10:06 AM, Simon Riggs wrote: Just consider this part of the recovery toolkit. In that case, don't call it "read uncommitted".  Call it some other thing entirely.  Users coming from other databas

Re: Read Uncommitted regression test coverage

Mark Dilger writes: >> The one in src/test/isolation doesn't look very comprehensive.  I'd >> at least expect a test that verifies you don't get a syntax error >> when you request READ UNCOMMITTED isolation from SQL. > The attached patch set adds a modicum of test coverage for this. > Do others f

Re: Read Uncommitted

On Wed, Dec 18, 2019 at 2:29 PM Heikki Linnakangas wrote: > I agree that if we have a user-exposed READ UNCOMMITTED isolation level, > it shouldn't be just a recovery tool. For a recovery tool, I think a > set-returning function as part of contrib/pageinspect, for example, > would be more appropri

Re: Read Uncommitted

"Finnerty, Jim" writes: > Many will want to use it to do aggregation, e.g. a much more efficient > COUNT(*), because they want performance and don't care very much about > transaction consistency. E.g. they want to compute SUM(sales) by > salesperson, region for the past 5 years, and don't car

Re: Read Uncommitted

Many will want to use it to do aggregation, e.g. a much more efficient COUNT(*), because they want performance and don't care very much about transaction consistency. E.g. they want to compute SUM(sales) by salesperson, region for the past 5 years, and don't care very much if some concurrent t

Re: Read Uncommitted

Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Dec 18, 2019 at 1:06 PM Simon Riggs wrote: > > Just consider this part of the recovery toolkit. > > I agree that it would be useful to have a recovery toolkit for reading > uncommitted data, but I think a lot more thought needs to

Re: Read Uncommitted

On 18/12/2019 20:46, Mark Dilger wrote: On 12/18/19 10:06 AM, Simon Riggs wrote: Just consider this part of the recovery toolkit. In that case, don't call it "read uncommitted". Call it some other thing entirely. Users coming from other databases may request "read uncommitted" isolation expe

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 18:37, Tom Lane wrote: > Simon Riggs writes: > > So this is the same discussion as elsewhere about potentially aborted > > transactions... > > AFAIK, the worst that happens in that case is that the reading > transaction > > will end with an ERROR, similar to a serializable

Re: Read Uncommitted

On 12/18/19 10:06 AM, Simon Riggs wrote: On Wed, 18 Dec 2019 at 17:35, Robert Haas > wrote: On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs > wrote: This was my first concern when I thought about it, but I realised that by taking a sna

Re: Read Uncommitted

Simon Riggs writes: > So this is the same discussion as elsewhere about potentially aborted > transactions... > AFAIK, the worst that happens in that case is that the reading transaction > will end with an ERROR, similar to a serializable error. No, the worst case is transactions trying to read i

Re: Read Uncommitted

On Wed, Dec 18, 2019 at 1:06 PM Simon Riggs wrote: > So this is the same discussion as elsewhere about potentially aborted > transactions... Yep. > AFAIK, the worst that happens in that case is that the reading transaction > will end with an ERROR, similar to a serializable error. I'm not con

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 17:35, Robert Haas wrote: > On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs > wrote: > > This was my first concern when I thought about it, but I realised that > by taking a snapshot and then calculating xmin normally, this problem would > go away. > > Why? As soon as a trans

Re: Read Uncommitted

On Wed, Dec 18, 2019 at 10:18 AM Simon Riggs wrote: > This was my first concern when I thought about it, but I realised that by > taking a snapshot and then calculating xmin normally, this problem would go > away. Why? As soon as a transaction aborts, the TOAST rows can be vacuumed away, but th

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 14:06, Tom Lane wrote: > Simon Riggs writes: > > I present a patch to allow READ UNCOMMITTED that is simple, useful and > > efficient. > > Won't this break entirely the moment you try to read a tuple containing > toasted-out-of-line values? There's no guarantee that the t

Re: Read Uncommitted

On Wed, 18 Dec 2019 at 12:11, Konstantin Knizhnik wrote: As far as I understand with "read uncommitted" policy we can see two > versions of the same tuple if it was updated by two transactions both of > which were started before us and committed during table traversal by > transaction with "read

Re: Read Uncommitted

Simon Riggs writes: > I present a patch to allow READ UNCOMMITTED that is simple, useful and > efficient. Won't this break entirely the moment you try to read a tuple containing toasted-out-of-line values? There's no guarantee that the toast-table entries haven't been vacuumed away. I suspect i

Re: Read Uncommitted

On 18.12.2019 13:01, Simon Riggs wrote: I present a patch to allow READ UNCOMMITTED that is simple, useful and efficient.  This was previously thought to have no useful definition within PostgreSQL, though I have identified a use case for diagnostics and recovery that merits adding a short pa