Обсуждение: 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