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

Re: Bugs in planner's equivalence-class processing

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joel Jacobson <joel(at)trustly(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Bugs in planner's equivalence-class processing
Date: 2012-11-02 23:27:16
Message-ID: 7225.1351898836@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Joel Jacobson <joel(at)trustly(dot)com> writes:
> If helpful, here is a simple test to reproduce the problem:
> http://pgsql.privatepaste.com/6429e8a200

FWIW, this is fixed already in git, or at least this particular example
gives what seems the right answer:

 fooid | barid | fooint 
-------+-------+--------
     2 |       |      1
(1 row)

> Would you recommend me to rewrite all queries of this particular
> type, where you have COALESCE in the WHERE statement,
> as a precaution?

No, but you might want to grab the appropriate patch and apply it
locally, if you tend to write queries like this.  You want one
of these:

Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Branch: master [72a4231f0] 2012-10-18 12:30:10 -0400
Branch: REL9_2_STABLE [0237b3945] 2012-10-18 12:30:25 -0400
Branch: REL9_1_STABLE [447dad719] 2012-10-18 12:29:00 -0400
Branch: REL9_0_STABLE [afdc7515f] 2012-10-18 12:29:06 -0400
Branch: REL8_4_STABLE [779016271] 2012-10-18 12:29:13 -0400
Branch: REL8_3_STABLE [c29a91037] 2012-10-18 12:29:19 -0400

    Fix planning of non-strict equivalence clauses above outer joins.

> We haven't migrated to 9.2 yet, but perhaps there is a risk
> similar queries can render the same problems even in 9.1?

9.2 has considerably more scope to make this kind of error, but related
bugs can be demonstrated as far back as 7.4.  It's a bit surprising
nobody noticed until now.

			regards, tom lane


In response to

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2012-11-03 00:08:18
Subject: Re: Synchronous commit not... synchronous?
Previous:From: Alexander KorotkovDate: 2012-11-02 21:07:39
Subject: Re: [v9.3] writable foreign tables

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