Skip site navigation (1) Skip section navigation (2)

Re: PL/pgSQL bug?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us, JanWieck(at)Yahoo(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: PL/pgSQL bug?
Date: 2001-09-07 01:46:38
Message-ID: 200109070146.f871kcj17605@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Is this something that still needs fixing?


> > I believe the reason for this is that in Read Committed mode,
> > each separate query from the client computes a new snapshot (see
> > SetQuerySnapshot calls in postgres.c).  So, when your
> > "select ctid, i from t1" query executes, it computes a snapshot
> > that says T1 is committed, and then it doesn't see the row left
> > over from T1.  On the other hand, your plpgsql function operates
> > inside a single client query and so it's using just one QuerySnaphot.
> 
> Oh I see. So the "problem" is not specific to PL/pgSQL, but exists in
> all our procedual languages.
> 
> > One way to make the results equivalent is to compute a new QuerySnapshot
> > for each SPI query.  Quite aside from the cost of doing so, I do not
> > think it makes sense, considering that the previous QuerySnapshot must
> > be restored when we return from the function.  Do we really want
> > functions to see transaction status different from what's seen outside
> > the function call?  I doubt it.
> > 
> > The other way to make the results the same is to omit the
> > SetQuerySnapshot calls for successive client-issued queries in one
> > transaction.  This could perhaps be defended on logical grounds,
> > but considering your complaint I'm not sure it would make people
> > happier.
> 
> Ok, maybe another workaround might be adding a checking for cmax in
> the subselect:
> 
>    SELECT INTO myid i FROM t1 WHERE i = (SELECT i FROM t1 WHERE i = 1);
> 
> to make sure that cmax > 0?
> --
> Tatsuo Ishii
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

In response to

pgsql-hackers by date

Next:From: Mark PritchardDate: 2001-09-07 02:05:16
Subject: Re: [Pgreplication-general] GreatBridge ceases operations
Previous:From: John SummerfieldDate: 2001-09-06 23:46:30
Subject: Re: Build problem with CVS version

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group