Re: Is replacing transactions with CTE a good idea?

On Fri, Apr 9, 2021 at 11:05:34PM +0800, Glen Huang wrote: > This discussion really questioned my understanding of concurrency in > PostgreSQL, thanks a lot. > > I gave the corresponding part of the doc some more read, and I’m now > in the option that insolation level has no effect on CTEs, but

Re: Is replacing transactions with CTE a good idea?

This discussion really questioned my understanding of concurrency in PostgreSQL, thanks a lot. I gave the corresponding part of the doc some more read, and I’m now in the option that insolation level has no effect on CTEs, but please correct me if I’m wrong. If notionally all queries execute

Re: Is replacing transactions with CTE a good idea?

On Mon, Apr 5, 2021 at 02:32:36PM -0400, Dave Cramer wrote: > On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: > I think we are in agreement. My point was that WITH queries don't change the > isolation semantics.  My point is that when you combine individual queries in a single WITH query,

Re: Is replacing transactions with CTE a good idea?

On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: > On Sun, Apr 4, 2021 at 10:02:20AM -0400, Dave Cramer wrote: > > On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > > > OK, that makes sense, but I think it is wrong minded to think that > this > > > absolves one of taking isolation

Re: Is replacing transactions with CTE a good idea?

On Sun, Apr 4, 2021 at 10:02:20AM -0400, Dave Cramer wrote: > On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > > OK, that makes sense, but I think it is wrong minded to think that this > > absolves one of taking isolation into account. > > > > When you make the first read you

Re: Is replacing transactions with CTE a good idea?

On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > On Sun, Apr 4, 2021 at 08:35:41AM -0400, Dave Cramer wrote: > > > > > > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > > > > On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > > CTE's don't change the isolation

Re: Is replacing transactions with CTE a good idea?

On Sun, Apr 4, 2021 at 08:35:41AM -0400, Dave Cramer wrote: > > > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > > On Thu, Apr  1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > CTE's don't change the isolation level. I'm not sure what you are > getting > at > > here ?

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > CTE's don't change the isolation level. I'm not sure what you are > getting at > > here ? > > I think what he/she means here is that all queries in a CTE use a single > snapshot,

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 10:04 AM, Rob Sargent wrote: On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? I think what he/she means here is that all queries in a CTE use a single snapshot, meaning you don't see changes by commits that happen between

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:06 AM Glen Huang wrote: > Care to expand why they are tricker? I presume they run the risk of being > referenced more than once? > There are lots of gotchas. It's also been a few years since I dug deep into this, so some of this may have changed in more recent

Re: Is replacing transactions with CTE a good idea?

I had the impression that since they are chained together, somehow they run “tighter” . Thanks for pointing out that mistake. > On Apr 1, 2021, at 11:25 PM, Dave Cramer wrote: > >  > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? > > Dave Cramer >

Re: Is replacing transactions with CTE a good idea?

Ah, I see what you mean. You still have to wrap a CTE inside a transaction to specify the isolation level? By default, queries in a CTE run with the read committed isolation level? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > >  > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote:

Re: Is replacing transactions with CTE a good idea?

CTE's don't change the isolation level. I'm not sure what you are getting at here ? Dave Cramer www.postgres.rocks On Thu, 1 Apr 2021 at 11:20, Glen Huang wrote: > Sorry, my mistake. I misunderstood serializable. Are queries in a CTE > equivalent to those in a repeatable read transaction? > >

Re: Is replacing transactions with CTE a good idea?

Sorry, my mistake. I misunderstood serializable. Are queries in a CTE equivalent to those in a repeatable read transaction? > On Apr 1, 2021, at 11:10 PM, Dave Cramer wrote: > >  > > >> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >> No, but are they equivalent to serializable

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: > No, but are they equivalent to serializable transactions? > No, they are not. Dave Cramer www.postgres.rocks > > On Apr 1, 2021, at 11:04 PM, Dave Cramer > wrote: > >  > > > > On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: > >> Hi all, >>

Re: Is replacing transactions with CTE a good idea?

No, but are they equivalent to serializable transactions? > On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: > >  > > > >> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >> Hi all, >> >> From application’s standpoint, it seems using CTE saves a lot work. You no >> longer need to parse

Re: Is replacing transactions with CTE a good idea?

> When you deal with updates/deletes, things can be trickier Care to expand why they are tricker? I presume they run the risk of being referenced more than once? > On Apr 1, 2021, at 10:58 PM, Brian Dunavant wrote: > >  >> On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: >> If I decide to

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time and makes code way cleaner.   It's very

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: > Hi all, > > From application’s standpoint, it seems using CTE saves a lot work. You no > longer need to parse values out only to pass them back in, and only one > round-trip to the db server. > > If I’m not wrong, CTE is equivalent to serializable

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: > If I decide to replace all my transaction code with CTE, will I shoot > myself in the foot down the road? > I do this all the time and makes code way cleaner. It's very straightforward with inserts queries. When you deal with

Is replacing transactions with CTE a good idea?

Hi all, From application’s standpoint, it seems using CTE saves a lot work. You no longer need to parse values out only to pass them back in, and only one round-trip to the db server. If I’m not wrong, CTE is equivalent to serializable transactions? So I guess the downsize is that quarries