On 7 May 2015 at 18:37, Andres Freund wrote:
> I don't see a problem at all, with one exception: If we want the AS to
> be optional like in a bunch of other places, we have to either promote
> VALUES to a reserved keyword, only accept unreserved keywords, or play
> precedence games. I think it'd
On Thu, May 7, 2015 at 10:37 AM, Andres Freund wrote:
> I don't see a problem at all, with one exception: If we want the AS to
> be optional like in a bunch of other places, we have to either promote
> VALUES to a reserved keyword, only accept unreserved keywords, or play
> precedence games. I thi
On 2015-05-07 16:15:18 +0300, Heikki Linnakangas wrote:
> On 05/07/2015 12:01 AM, Andres Freund wrote:
> >6. The tablename and EXCLUDED? Possibility with the ability to specify
> >an AS for INSERT INTO foo AS whatever?
>
> If we don't allow "AS whatever", and you create a table called "exclude
On 05/07/2015 12:01 AM, Andres Freund wrote:
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
If we don't allow "AS whatever", and you create a table called
"excluded", you're stuck with the ambiguity in the DO UPDATE statement
On 05/07/2015 12:01 AM, Andres Freund wrote:
How about
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
From an implementation pov that'd be simple ;)
I did this, because as you say it's simple to implement, and it resolves
the
On 6 May 2015 at 22:30, Heikki Linnakangas wrote:
> On 05/07/2015 12:01 AM, Andres Freund wrote:
>
>> On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
>>
>>> I'll see about fixing that. It's not just a matter of creating another
>>> alias
>>> for the same rel, I'm afraid: "foo.t" is suppos
On 05/07/2015 12:01 AM, Andres Freund wrote:
On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
I'll see about fixing that. It's not just a matter of creating another alias
for the same rel, I'm afraid: "foo.t" is supposed to refer to the tuple that
we attempted to insert, like it does with
On 05/07/2015 12:18 AM, Andres Freund wrote:
On 2015-05-07 00:10:22 +0300, Heikki Linnakangas wrote:
Right, that's the idea. Indexes are just an implementation detail -
I think that's a distinction just about no user out there cares about.
Unfortunately you can't create a "partial constraint
On 2015-05-07 00:10:22 +0300, Heikki Linnakangas wrote:
> Right, that's the idea. Indexes are just an implementation detail -
I think that's a distinction just about no user out there cares about.
> Unfortunately you can't create a "partial constraint" - you'll have to
> create a partial index. I
On 05/06/2015 11:05 PM, Peter Geoghegan wrote:
On Wed, May 6, 2015 at 7:53 AM, Andres Freund wrote:
In this variant, you explicitly specify the constraint by name.
I do think it's a bit sad to not be able to specify unique indexes that
aren't constraints. So I'd like to have a corresponding O
On Wed, May 6, 2015 at 2:01 PM, Andres Freund wrote:
> How about
> 6. The tablename and EXCLUDED? Possibility with the ability to specify
>an AS for INSERT INTO foo AS whatever?
>
> From an implementation pov that'd be simple ;)
That's what I wanted to do when I realized what Andres wanted to
On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
> I'll see about fixing that. It's not just a matter of creating another alias
> for the same rel, I'm afraid: "foo.t" is supposed to refer to the tuple that
> we attempted to insert, like it does without the ON CONFLICT.
I'm not sure what yo
On Wed, May 6, 2015 at 1:48 PM, Heikki Linnakangas wrote:
>> TARGET is also very descriptive, because it situationally describes
>> either the existing tuple actually present in the table, or (from a
>> RETURNING clause) the final tuple present in the table post-UPDATE.
>> We use the term "target"
Andres pointed out on IM that the TARGET alias is a bit crummy. In
particular, adding an ON CONFLICT DO UPDATE can make a RETURNING clause
invalid, because we change the alias of the target rel:
create table foo (id int4 primary key, t text);
This works:
postgres=# insert into foo (id, t) val
On 2015-05-06 13:37:07 -0700, Peter Geoghegan wrote:
> On Wed, May 6, 2015 at 1:22 PM, Andres Freund wrote:
> > That it supports exclusion constraints?
>
> But so does just naming the index. I don't think it's significant that
> exclusion operators are in pg_constraint -- you could just as easily
On Wed, May 6, 2015 at 1:22 PM, Andres Freund wrote:
> That it supports exclusion constraints?
But so does just naming the index. I don't think it's significant that
exclusion operators are in pg_constraint -- you could just as easily
name the index, since that's all you ultimately end up with an
On 2015-05-06 13:05:16 -0700, Peter Geoghegan wrote:
> On Wed, May 6, 2015 at 7:53 AM, Andres Freund wrote:
> >> In this variant, you explicitly specify the constraint by name.
> >
> > I do think it's a bit sad to not be able to specify unique indexes that
> > aren't constraints. So I'd like to ha
On Wed, May 6, 2015 at 7:53 AM, Andres Freund wrote:
>> In this variant, you explicitly specify the constraint by name.
>
> I do think it's a bit sad to not be able to specify unique indexes that
> aren't constraints. So I'd like to have a corresponding ON INDEX - which
> would be trivial.
Then w
On Tue, May 5, 2015 at 5:27 AM, Heikki Linnakangas wrote:
> To recap, there are three variants:
>
> A. INSERT ... ON CONFLICT DO NOTHING
>
> No arbiter is specified. This means that a conflict on any unique or
> exclusion constraint is not allowed (and will do nothing instead). This
> variant is o
On 2015-05-05 15:27:09 +0300, Heikki Linnakangas wrote:
> I'm a bit late to the party as I haven't paid much attention to the syntax
> before, but let me give some comments on this "arbiter index inference"
> thingie.
>
>
> To recap, there are three variants:
>
> A. INSERT ... ON CONFLICT DO NOT
On Tue, May 5, 2015 at 9:36 AM, Andres Freund wrote:
> Theoretically this changes the pictures for FDWs, right? Right now
> there's
> +
> + INSERT with an ON CONFLICT clause is not
> + supported with a unique index inference specification, since a
> + conflict arbitrating unique in
On 2015-05-04 14:16:42 -0700, Peter Geoghegan wrote:
> On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas wrote:
> > The ability to specify a constraint by name hasn't been implemented, but
> > that would read quite naturally as:
> >
> > INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint
On 05/05/2015 12:16 AM, Peter Geoghegan wrote:
On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas wrote:
The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:
INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint UPDATE ...
For th
On Wed, Apr 29, 2015 at 12:09 PM, Robert Haas wrote:
>> * Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
>> TARGET.*). Those seem fine to me as well.
>
> There seem to be a few votes for NEW and OLD. That's what I proposed
> originally, and (surprise, surprise) I still like t
On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas wrote:
> The ability to specify a constraint by name hasn't been implemented, but
> that would read quite naturally as:
>
> INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint UPDATE ...
For the record, I have made this change on Github
* Andres Freund (and...@anarazel.de) wrote:
> On the other hand it's way more convenient to specify a single
> constraint name than several columns and a predicate. I'm pretty sure
> there's situations where I a) rather live with a smaller chance of error
> during a replacement of the constraint b)
On Wed, Apr 29, 2015 at 4:09 PM, Simon Riggs wrote:
> I dislike the way that ignoring objections for a period leads them to be
> potentially discarded. I'd prefer to think that as a community we are able
> to listen to people even when they aren't continually present to reinforce
> the original ob
On 25 April 2015 at 14:05, Peter Geoghegan wrote:
> > a) Why is is 'CONFLICT"? We're talking about a uniquness violation. What
> >if we, at some later point, also want to handle other kind of
> >violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ...
>
> I think that naming un
On 2015-04-29 15:31:59 -0400, Robert Haas wrote:
> On Wed, Apr 29, 2015 at 3:13 PM, Stephen Frost wrote:
> >> I still think that constraints should never be named in the syntax.
> >
> > I guess I don't see a particular problem with that..? Perhaps I'm
> > missing something, but if there's multipl
On Wed, Apr 29, 2015 at 3:13 PM, Stephen Frost wrote:
> My general feeling is "keep it short" but I'm not particular beyond
> that. I do like the idea that we'll be able to support more options in
> the future.
Yeah. To me "ON CONFLICT" sounds like "if there's a war, then...".
So I like ON DUPL
* Robert Haas (robertmh...@gmail.com) wrote:
> On Mon, Apr 27, 2015 at 7:21 PM, Peter Geoghegan wrote:
> > On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian wrote:
> >> Agreed, and I like the DO [ UPDATE | NOTHING ] too.
> >
> > Here is what I think I need to do:
> >
> > * Don't change the ON CONFL
On Mon, Apr 27, 2015 at 7:21 PM, Peter Geoghegan wrote:
> On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian wrote:
>> Agreed, and I like the DO [ UPDATE | NOTHING ] too.
>
> Here is what I think I need to do:
>
> * Don't change the ON CONFLICT spelling.
What I proposed originally was ON DUPLICATE.
On Tue, Apr 28, 2015 at 10:36 AM, David G. Johnston
wrote:
> This example exemplifies the poorness of the proposed wording, IMO:
>
>
> [...]
> SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')'
>
> NEW.dname || '(formerly ' || OLD.dname || ')' reads perfectly well.
>
> Yes, this is
On Tue, Apr 28, 2015 at 9:58 AM, Stephen Frost wrote:
> * Peter Geoghegan (p...@heroku.com) wrote:
> > On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost
> wrote:
> > > I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
> > > the tuple being added, while OLD is clearly the existin
On Tue, Apr 28, 2015 at 7:36 AM, Petr Jelinek wrote:
> I am also very sure that every time I'll write this statement I will have to
> look into manual for the names of TARGET and EXCLUDED because they don't
> seem intuitive to me at all (especially the EXCLUDED).
According to wordcount.org, the w
* Peter Geoghegan (p...@heroku.com) wrote:
> On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost wrote:
> > I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
> > the tuple being added, while OLD is clearly the existing tuple.
>
> Yes, but EXCLUDED is neither the tuple being added,
On Tue, Apr 28, 2015 at 9:45 AM, Peter Geoghegan wrote:
> Yes, but EXCLUDED is neither the tuple being added, nor is it the new
> tuple. It's something else entirely.
I mean, nor is it the existing tuple.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.or
On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost wrote:
> I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
> the tuple being added, while OLD is clearly the existing tuple.
Yes, but EXCLUDED is neither the tuple being added, nor is it the new
tuple. It's something else entirely
* Peter Geoghegan (p...@heroku.com) wrote:
> On Tue, Apr 28, 2015 at 7:38 AM, Andres Freund wrote:
> > On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
> >> I am also very sure that every time I'll write this statement I will have
> >> to
> >> look into manual for the names of TARGET and EXCLUDE
On Tue, Apr 28, 2015 at 7:38 AM, Andres Freund wrote:
> On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
>> I am also very sure that every time I'll write this statement I will have to
>> look into manual for the names of TARGET and EXCLUDED because they don't
>> seem intuitive to me at all (espe
On 28 April 2015 at 15:57, I wrote:
> MySQL uses VALUES(columnname) to reference the intended INSERT value
> (what you might term "NEW") and the target name to reference "OLD". I
> understand that people might think the bracketed syntax isn't very pleasant
> because that looks like a function, bu
On 28 April 2015 at 15:46, Stephen Frost wrote:
> +1, NEW/OLD seem pretty natural and I'm not worried about what they look
> like in rules, and their usage in triggers matches up with what they'd
> mean here, I'd think.
>
Since I've stuck my head above the parapet once I figured I'd give m
y 2p'
On 28/04/15 16:44, Andres Freund wrote:
On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
* Andres Freund (and...@anarazel.de) wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will have to
look into manual for the names o
* Andres Freund (and...@anarazel.de) wrote:
> On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
> > * Andres Freund (and...@anarazel.de) wrote:
> > > On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
> > > > I am also very sure that every time I'll write this statement I will
> > > > have to
> >
On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
> * Andres Freund (and...@anarazel.de) wrote:
> > On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
> > > I am also very sure that every time I'll write this statement I will have
> > > to
> > > look into manual for the names of TARGET and EXCLUDE
* Andres Freund (and...@anarazel.de) wrote:
> On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
> > I am also very sure that every time I'll write this statement I will have to
> > look into manual for the names of TARGET and EXCLUDED because they don't
> > seem intuitive to me at all (especially t
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
> I am also very sure that every time I'll write this statement I will have to
> look into manual for the names of TARGET and EXCLUDED because they don't
> seem intuitive to me at all (especially the EXCLUDED).
Same here. I don't understand why 'CO
On 28/04/15 03:51, Peter Geoghegan wrote:
On Mon, Apr 27, 2015 at 4:21 PM, Peter Geoghegan wrote:
* Don't change the ON CONFLICT spelling.
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well.
* Change the syntax to put the WHERE claus
On Mon, Apr 27, 2015 at 4:21 PM, Peter Geoghegan wrote:
> * Don't change the ON CONFLICT spelling.
>
> * Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
> TARGET.*). Those seem fine to me as well.
>
> * Change the syntax to put the WHERE clause used to infer partial
> indexes o
On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian wrote:
> Agreed, and I like the DO [ UPDATE | NOTHING ] too.
Here is what I think I need to do:
* Don't change the ON CONFLICT spelling.
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well
On Mon, Apr 27, 2015 at 1:19 PM, Peter Eisentraut wrote:
> it appears that they are using quite a different syntax. The ON
> CONFLICT clause is attached to a constraint, specifying the default
> action for that constraint. The INSERT command can then override this
> default choice. I think.
We
On 4/25/15 2:05 PM, Peter Geoghegan wrote:
> Note that the syntax is quite similar to the SQLite
> syntax of the same feature, that has ON CONFLICT IGNORE (it also has
> ON CONFLICT REPLACE, but not ON CONFLICT UPDATE).
I don't know anything about SQLite's syntax, but from the online syntax
diagra
On Sun, Apr 26, 2015 at 09:34:12AM -0400, Stephen Frost wrote:
> * Heikki Linnakangas (hlinn...@iki.fi) wrote:
> > On 04/25/2015 12:01 PM, Andres Freund wrote:
> > >INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
> > >UPDATE|IGNORE
> > >
> > >My problem with the WHERE being insid
On Sun, Apr 26, 2015 at 11:43 AM, Stephen Frost wrote:
>> I think it easily could be.
>
> Ok.. Can you elaborate on that? Would it be an issue that's different
> from the same thing done as independent commands?
I think that the stuff I linked to describes my concerns exhaustively.
In any case,
* Peter Geoghegan (p...@heroku.com) wrote:
> On Sun, Apr 26, 2015 at 11:35 AM, Stephen Frost wrote:
> > Ok, that makes sense.. So is the concern that an INSERT would end up
> > getting default values while an UPDATE would preserve whatever's there?
> >
> > I don't see that as an issue.
>
> I thi
On Sun, Apr 26, 2015 at 11:35 AM, Stephen Frost wrote:
> Ok, that makes sense.. So is the concern that an INSERT would end up
> getting default values while an UPDATE would preserve whatever's there?
>
> I don't see that as an issue.
I think it easily could be.
> Are you still against having a
* Peter Geoghegan (p...@heroku.com) wrote:
> On Sun, Apr 26, 2015 at 11:08 AM, Stephen Frost wrote:
> >> I don't want to accept something that automatically merges the
> >> excluded tuple (e.g., "SET (*) = EXLCUDED.*"), for reasons outlined
> >> here: https://wiki.postgresql.org/wiki/UPSERT#VoltDB
On Sun, Apr 26, 2015 at 11:08 AM, Stephen Frost wrote:
>> I don't want to accept something that automatically merges the
>> excluded tuple (e.g., "SET (*) = EXLCUDED.*"), for reasons outlined
>> here: https://wiki.postgresql.org/wiki/UPSERT#VoltDB.27s_UPSERT
>
> Perhaps I'm missing it, but the rea
Peter,
* Peter Geoghegan (p...@heroku.com) wrote:
> On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost wrote:
> > What's important, in my view, is to keep the simple case simple and so
> > I'm not particularly wedded to any of these approaches, just trying to
> > help with other suggestions.
> >
> >
On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost wrote:
> What's important, in my view, is to keep the simple case simple and so
> I'm not particularly wedded to any of these approaches, just trying to
> help with other suggestions.
>
> INSERT INTO mytable VALUES ('key1','key2','val1','val2')
> ON C
* Heikki Linnakangas (hlinn...@iki.fi) wrote:
> On 04/25/2015 12:01 PM, Andres Freund wrote:
> >INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
> >UPDATE|IGNORE
> >
> >My problem with the WHERE being inside the parens in the above is that
> >it's
> >a) different from CREATE INDEX
On 26/04/15 12:08, Andres Freund wrote:
On April 26, 2015 11:22:01 AM GMT+02:00, Heikki Linnakangas
wrote:
On 04/25/2015 12:01 PM, Andres Freund wrote:
That's why I wanted the WHERE outside the (), which requires either
adding DO between the index inference clause, and the action, to
avoid
On April 26, 2015 11:22:01 AM GMT+02:00, Heikki Linnakangas
wrote:
>On 04/25/2015 12:01 PM, Andres Freund wrote:
>> INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
>UPDATE|IGNORE
>>
>> My problem with the WHERE being inside the parens in the above is
>that
>> it's
>> a) differen
On 04/25/2015 12:01 PM, Andres Freund wrote:
INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial]) UPDATE|IGNORE
My problem with the WHERE being inside the parens in the above is that
it's
a) different from CREATE INDEX
b) unclear whether the WHERE belongs to colb or the whole index
On Sat, Apr 25, 2015 at 12:35 PM, Peter Geoghegan wrote:
>>> > That
>>> > it has 'morphing' characteristics imo just makes it worse, rather than
>>> > better. Besides being confusing that it has different meanings, it's far
>>> > from inconceivable that somebody wants to return values from the
>>>
On Sat, Apr 25, 2015 at 12:23 PM, Andres Freund wrote:
> 95% of all users will know NEW/OLD from triggers, not rules. Where NEW
> is used in a quite comparable way.
I don't think it's comparable.
>> >> Seems pretty descriptive of the situation to me - I actually put a lot
>> >> of thought into t
On 2015-04-25 11:50:59 -0700, Peter Geoghegan wrote:
> On Sat, Apr 25, 2015 at 11:24 AM, Andres Freund wrote:
> >> > c) Right now the UPDATE can refer to pseudo relations 'TARGET' and
> >> >'EXCLUDED'. I think especially the latter doesn't fit anymore at
> >> >all. How about 'CONFLICTING'
On Sat, Apr 25, 2015 at 11:50 AM, Peter Geoghegan wrote:
> To be fair, there is one unrelated slight difference with RETURNING
> and conventional UPDATEs: You cannot return the EXCLUDED tuple (in the
> same way that you can reference the joined-FROM tuple within
> conventional UPDATEs). This is be
On Sat, Apr 25, 2015 at 11:24 AM, Andres Freund wrote:
>> > b) unclear whether the WHERE belongs to colb or the whole index
>> >expression. The equivalent for aggregates, which I bet is going to be
>> >used less often, caused a fair amount of confusing.
>>
>> I don't see those two situatio
On 2015-04-25 11:05:49 -0700, Peter Geoghegan wrote:
> On Sat, Apr 25, 2015 at 2:01 AM, Andres Freund wrote:
> > My problem with the WHERE being inside the parens in the above is that
> > it's
> > a) different from CREATE INDEX
>
> I don't think that that's an important goal.
Given that it's use
On Sat, Apr 25, 2015 at 2:01 AM, Andres Freund wrote:
> My problem with the WHERE being inside the parens in the above is that
> it's
> a) different from CREATE INDEX
I don't think that that's an important goal.
> b) unclear whether the WHERE belongs to colb or the whole index
>expression. T
I'm separating this discussion out of the thread because I think this
needs wider input.
On 2015-04-24 19:21:37 -0700, Peter Geoghegan wrote:
> I've *provisionally* pushed code that goes back to the old way,
> Andres:
> https://github.com/petergeoghegan/postgres/commit/2a5d80b27d2c5832ad26dde465
72 matches
Mail list logo
