pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Lebedev <olebedev@waterford.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Nested JOINs - upgrade to 7.1.2
Date: Fri, 17 Aug 2001 12:19:52 -0600
Message-ID: <3B7D6048.530CCD96@waterford.org> (raw)
References: <web-103647@davinci.ethosmedia.com>
	<25972.997999951@sss.pgh.pa.us>

I think Tom was right and the problem with nested joins is caused by the
outdated installation of my PostgreSQL.
So, I am trying to upgrade to 7.1.2 and when I use:
pg_dumpall -o > file.bac
I get an error saying:
dumpRules(): SELECT failed for table setmedias. Explanation from backend:
'ERROR: cache lookup of attribute 5 in relation 23945 failed"

As I understand setmedias table corresponds to relation 23945, but I dropped
it a long time ago. How should remove this relation pointer?
thanks,

Oleg

Tom Lane wrote:

> "Josh Berkus" <josh@agliodbs.com> writes:
> > Second, you can't alias a JOINed set of tables;
>
> Actually you can, according to my reading of SQL92:
>
>          <table reference> ::=
>                 <table name> [ [ AS ] <correlation name>
>                     [ <left paren> <derived column list> <right paren> ] ]
>               | <derived table> [ AS ] <correlation name>
>                     [ <left paren> <derived column list> <right paren> ]
>               | <joined table>
>
>          <derived table> ::= <table subquery>
>
>          <table subquery> ::= <subquery>
>
>          <subquery> ::= <left paren> <query expression> <right paren>
>
>          <query expression> ::=
>                 <non-join query expression>
>               | <joined table>
>
>          <joined table> ::=
>                 <cross join>
>               | <qualified join>
>               | <left paren> <joined table> <right paren>
>
>          <qualified join> ::=
>               <table reference> [ NATURAL ] [ <join type> ] JOIN
>                 <table reference> [ <join specification> ]
>
> So a parenthesized <qualified join> is a <derived table> and may
> be followed by [ AS ] <correlation name>.
>
> 7.1 gets this right.  7.0's very preliminary implementation of JOIN
> syntax did not.  (It had a number of other bugs too, IIRC.)
>
> The spec's grammar is horribly badly written: it's ambiguous whether the
> parentheses should be parsed as part of a second-level <joined table>
> which would imply that a correlation name would NOT be accepted.
> It took a fair amount of work to derive a grammar that was unambiguous
> and still accepted everything...
>
>                         regards, tom lane




Message-ID: <3B7D6048.530CCD96@waterford.org>
Permalink:  ../3B7D6048.530CCD96@waterford.org/
Also on:    postgresql.org/message-id/3B7D6048.530CCD96@waterford.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: olebedev@waterford.org, tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: Nested JOINs - upgrade to 7.1.2
  In-Reply-To: <3B7D6048.530CCD96@waterford.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox