Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

From: Andres Freund <andres(at)anarazel(dot)de>
To: Peter Geoghegan <pg(at)heroku(dot)com>
Cc: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Stephen Frost <sfrost(at)snowman(dot)net>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Bruce Momjian <bruce(at)momjian(dot)us>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Subject: Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
Date: 2015-04-23 10:54:07
Message-ID: 20150423105407.GB3055@alap3.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2015-04-22 16:40:07 -0700, Peter Geoghegan wrote:
> On Wed, Apr 22, 2015 at 3:23 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> > * We need to sort out those issues with the grammar, since that only
> > really applies to the inference specification. Maybe the WHERE clause
> > that the inference specification accepts can be broken out. No ON
> > CONFLICT UPDATE specific issues left there, AFAICT though.
>
> I pushed some code that deals with the predicate being within parenthesis:
>
> https://github.com/petergeoghegan/postgres/commit/358854645279523310f998dfc9cb3fe3e165ce1e

And the way you've used nonassoc here doesn't look correct. You're
hiding legitimate ambiguities in the grammar. UPDATE is a unreserved
keyword, so for

... ON CONFLICT '(' index_params ')' where_clause OnConflictUpdateStmt

it won't be able to discern whether an UPDATE in the WHERE clause is
part of the where_clause or OnConflictUpdate.

This is legal:
SELECT * FROM (SELECT true as update) f WHERE update;
i.e. 'update' can be the last part of a WHERE clause.

Essentially what you're trying to do with the nonassic is hiding that
UPDATE and IGNORE need to be reserved keywords with the syntax you're
proposing. We can either make them reserved or change the syntax.

One way to avoid making them reserved keywords - which would be somewhat
painful - is to add a 'DO' before the IGNORE/UPDATE. I.e. something like

ON CONFLICT opt_conflict_expr DO OnConflictUpdateStmt
| ON CONFLICT opt_conflict_expr DO IGNORE

Greetings,

Andres Freund

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2015-04-23 12:21:26 Split the 'Server Programming' chapter into two?
Previous Message Kyotaro HORIGUCHI 2015-04-23 10:20:43 Re: [BUGS] Failure to coerce unknown type to specific type