Thread: failed to build any 8-way joins: SQLSTATE: XX000 on PostgreSQL 8.2.6

Hi all, I've seen other mentions of this happening but this instance is
with a newer server version than the ones that had a patch applied.

Basically, 8 LEFT JOINs, and removing any one of them stop the symptom.
I'll provide more data if the query in itself doesn't not indicate
what's causing the problem...

-Ken

SELECT
NOW()            AS a,
host.hostname        AS b,
host.serverdom        AS c,
host.sitename        AS d,
host.appname        AS e,
site.rowid        AS f,
site.namefirst        AS g,
pers.rowid        AS h,
pers.uuid        AS i,
pers.namefirst        AS j,
pers.nameLast        AS k,
logon.token        AS l,
siteTz.timezone_id    AS o,
siteTz.id         AS p,
persTz.timezone_id    AS q,
persTz.id        AS r,
compTz.timezone_id    AS s,
compTz.id        AS t
FROM http_host AS host
LEFT JOIN contact AS site ON host.join_contact = site.rowid
LEFT JOIN contact_timezones AS siteTz ON site.timezoneId =
siteTz.timezone_id
LEFT JOIN contact AS pers ON LOWER(pers.logonName) = LOWER('ken')
LEFT JOIN contact_timezones AS persTz ON pers.timezoneId =
persTz.timezone_id
LEFT JOIN contact_rela AS persParent ON pers.rowid = persParent.childId
LEFT JOIN contact AS comp ON persParent.parentId = comp.rowid
LEFT JOIN contact_timezones AS compTz ON comp.timezoneId =
compTz.timezone_id
LEFT JOIN contact_logon AS logon ON pers.rowid = logon.join_contact
WHERE host.hostname = 'localhost'

Ken Johanson <pg-user@kensystem.com> writes:
> Hi all, I've seen other mentions of this happening but this instance is
> with a newer server version than the ones that had a patch applied.

Please see if this patch fixes it for you:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php

If not, we'll need a self-contained test case.

            regards, tom lane

Tom Lane wrote:
>
> Please see if this patch fixes it for you:
> http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php
>
> If not, we'll need a self-contained test case.

Please pardon my non-awareness, what comprises a test case for this; is
DDL sufficent? I'm electing to go that route for having only RPMs on
hand, slow CPU and unfamiliarity with the optimal configure options.

Also from the query can you tell if specific changes to it will prove
whether the patch will work? I see "clauseless joins of relations that
have unexploited join clauses", however I (interpreted this as needing)
adding a column from "persParent" to the select list, but the problem
persists. Also, I can remove any one of the timezone joins and the query
works.

Ken

Ken Johanson <pg-user@kensystem.com> writes:
> Sorry Tom, I just missed you and responded on list, also asking if there
> were a test implied in adjusting the query. Anyway, here is trimmed-down
> DDL.

Well, if I just load this script into an empty DB in 8.2-CVS-tip and
then run the given query, it does what I'd expect (return a zero-row
result).  If the same case fails for you, that suggests we fixed it.

If increasing join_collapse_limit to 9 or more makes the problem go away
for you, that would be additional evidence that what you are seeing is
the same problem already diagnosed.  That might be a usable workaround
for you until 8.2.7 comes out...

            regards, tom lane

Tom Lane wrote:

>
> If increasing join_collapse_limit to 9 or more makes the problem go away
> for you, that would be additional evidence that what you are seeing is
> the same problem already diagnosed.  That might be a usable workaround
> for you until 8.2.7 comes out...
>

Thank Tom. That worked around it.

I always knew I hated arbitrary limits. I set it to 1 ("# 1 disables
collapsing of explicit JOINS") and all seems OKAY. That was 3 hours of
my life (experimenting, researching, communicating) under the bridge.
Anyway, back in business.

Ken