Ah, there is one reason not to use a mapping to CTEs to implement MERGE:
it might be faster to use a single query that is a FULL OUTER JOIN of the
source and target to drive the update/insert/delete operations.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make
On Tue, Nov 07, 2017 at 03:31:22PM -0800, Peter Geoghegan wrote:
> On Tue, Nov 7, 2017 at 3:29 PM, Nico Williams wrote:
> > On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
> >> Nico Williams wrote:
> >> >A MERGE mapped to a DML like
On Tue, Nov 7, 2017 at 3:29 PM, Nico Williams wrote:
> On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
>> Nico Williams wrote:
>> >A MERGE mapped to a DML like this:
>
> I needed to spend more time reading MERGE docs from other
On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
> Nico Williams wrote:
> >A MERGE mapped to a DML like this:
I needed to spend more time reading MERGE docs from other RDBMSes.
The best MERGE so far is MS SQL Server's, which looks like:
MERGE INTO
On 6 November 2017 at 17:35, Simon Riggs wrote:
> I read that step 3 in Approach2 is some kind of problem in MVCC
> semantics. My understanding is that SQL Standard allows us to define
> what the semantics of the statement are in relation to concurrency, so
> any semantic
Simon Riggs wrote:
In step 3 we discover that an entry exists in the index for a committed row.
Since we have a unique index we use it to locate the row we know
exists and UPDATE that.
We don't use a new MVCC snapshot, we do what EPQ does. EPQ is already
violating MVCC
On 6 November 2017 at 18:35, Peter Geoghegan wrote:
>> APPROACH2 (modified from my original proposal slightly)
>
>
> This write-up actually begins to confront the issues that I've raised.
> I'm glad to see this.
>
>> 1. Join...
>> 2. Apply results for UPDATE, if present not visible
Simon Riggs wrote:
APPROACH1
1. Join to produce results based upon snapshot at start of query
2. Apply results for INSERT, UPDATE or DELETE
Such failures are of great concern in practice because the time
between 1 and 2 could be very long for large statements, or for
On 3 November 2017 at 16:35, Peter Geoghegan wrote:
> Simon Riggs wrote:
The *only* behavioural difference I have proposed would be the *lack*
of an ERROR in (some) concurrent cases.
>>>
>>>
>>> I think that's a big difference. Error vs.
Simon Riggs wrote:
The *only* behavioural difference I have proposed would be the *lack*
of an ERROR in (some) concurrent cases.
I think that's a big difference. Error vs. non-error is a big deal by
itself;
Are you saying avoiding an ERROR is a bad or good thing?
On 3 November 2017 at 08:26, Robert Haas wrote:
> On Fri, Nov 3, 2017 at 1:05 PM, Simon Riggs wrote:
>>> Therefore, if MERGE eventually uses INSERT .. ON CONFLICT
>>> UPDATE when a relevant unique index exists and does something else,
>>> such as
* Robert Haas (robertmh...@gmail.com) wrote:
> On Fri, Nov 3, 2017 at 1:05 PM, Simon Riggs wrote:
> > We seem to have a few options for PG11
> >
> > 1. Do nothing, we reject MERGE
> >
> > 2. Implement MERGE for unique index situations only, attempting to
> > avoid errors
On 3 November 2017 at 07:46, Thomas Kellerer wrote:
> PMFJI
>
>> We seem to have a few options for PG11
>>
>> 1. Do nothing, we reject MERGE
>>
>> 2. Implement MERGE for unique index situations only, attempting to
>> avoid errors (Simon OP)
>>
>> 3. Implement MERGE, but
On Fri, Nov 3, 2017 at 1:05 PM, Simon Riggs wrote:
>> Therefore, if MERGE eventually uses INSERT .. ON CONFLICT
>> UPDATE when a relevant unique index exists and does something else,
>> such as your proposal of taking a strong lock, or Peter's proposal of
>> doing this in a
PMFJI
> We seem to have a few options for PG11
>
> 1. Do nothing, we reject MERGE
>
> 2. Implement MERGE for unique index situations only, attempting to
> avoid errors (Simon OP)
>
> 3. Implement MERGE, but without attempting to avoid concurrent ERRORs
> (Peter)
>
> 4. Implement MERGE, while
On 2 November 2017 at 22:59, Nico Williams wrote:
> On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
>> Nico Williams wrote:
>> >A MERGE mapped to a DML like this:
>>
>> This is a bad idea. An implementation like this is not at all
>>
On 2 November 2017 at 17:06, Robert Haas wrote:
> On Tue, Oct 31, 2017 at 5:14 PM, Simon Riggs wrote:
>> I've proposed a SQL Standard compliant implementation that would do
>> much more than be new syntax over what we already have.
>>
>> So these two
On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote:
> Nico Williams wrote:
> >A MERGE mapped to a DML like this:
>
> This is a bad idea. An implementation like this is not at all
> maintainable.
Assuming the DELETE issue can be addressed, why would this not
Nico Williams wrote:
A MERGE mapped to a DML like this:
WITH
updated AS (
UPDATE
SET ...
WHERE
RETURNING
)
, inserted AS (
INSERT INTO
SELECT ...
WHERE NOT IN (SELECT FROM updated) AND ..
ON
On Thu, Nov 02, 2017 at 02:05:19PM -0700, Peter Geoghegan wrote:
> Simon Riggs wrote:
> >So in your view we should make no attempt to avoid concurrent errors,
> >even when we have the capability to do so (in some cases) and doing so
> >would be perfectly compliant with the
Simon Riggs wrote:
I think people imagined you had worked out how to make MERGE run
concurrently, I certainly did, but in fact you're just saying you
don't believe it ever should.
I'm certain that they didn't think that at all. But I'll let them speak
for themselves.
On 2 November 2017 at 19:16, Peter Geoghegan wrote:
> Simon Riggs wrote:
>>
>> So if I understand you correctly, in your view MERGE should just fail
>> with an ERROR if it runs concurrently with other DML?
>
>
> That's certainly my opinion on the matter. It
On Thu, Nov 02, 2017 at 12:51:45PM -0700, Peter Geoghegan wrote:
> Nico Williams wrote:
> >If you want to ignore conflicts arising from concurrency you could
> >always add an ON CONFLICT DO NOTHING to the INSERT DML in the mapping I
> >proposed earlier. Thus a MERGE
Simon Riggs wrote:
So if I understand you correctly, in your view MERGE should just fail
with an ERROR if it runs concurrently with other DML?
That's certainly my opinion on the matter. It seems like that might be
the consensus, too.
Obviously there are things that you
Nico Williams wrote:
If you want to ignore conflicts arising from concurrency you could
always add an ON CONFLICT DO NOTHING to the INSERT DML in the mapping I
proposed earlier. Thus a MERGE CONCURRENTLY could just do that.
Is there any reason not to map MERGE as I
On Thu, Nov 02, 2017 at 06:49:18PM +, Simon Riggs wrote:
> On 1 November 2017 at 18:20, Peter Geoghegan wrote:
> > In Postgres, you can avoid duplicate violations with MERGE by using a
> > higher isolation level (these days, those are turned into a
> > serialization error at
If nothing else, anyone needing MERGE can port their MERGE statements to
a DML with DML-containing CTEs...
The generic mapping would be something like this, I think:
WITH
rows AS (SELECT FROM WHERE )
, updated AS (
UPDATE
SET ...
WHERE IN (SELECT FROM rows) /* matched */
On 1 November 2017 at 18:20, Peter Geoghegan wrote:
> In Postgres, you can avoid duplicate violations with MERGE by using a
> higher isolation level (these days, those are turned into a
> serialization error at higher isolation levels when no duplicate is
> visible to the xact's
Robert Haas wrote:
And if, in the meantime, MERGE can only handle the cases where there
is a unique index, then it can only handle the cases INSERT .. ON
CONFLICT UPDATE can cover, which makes it, as far as I can see,
syntactic sugar over what we already have. Maybe it's
On Tue, Oct 31, 2017 at 5:14 PM, Simon Riggs wrote:
> I've proposed a SQL Standard compliant implementation that would do
> much more than be new syntax over what we already have.
>
> So these two claims aren't accurate: "radical difference" and "syntax
> sugar over a
On Thu, Nov 2, 2017 at 8:28 AM, Craig Ringer wrote:
> On 1 November 2017 at 01:55, Peter Geoghegan wrote:
>> The problem here is: Iff the first statement uses ON CONFLICT
>> infrastructure, doesn't the absence of WHEN NOT MATCHED imply
>> different semantics
On 1 November 2017 at 01:55, Peter Geoghegan wrote:
> The problem here is: Iff the first statement uses ON CONFLICT
> infrastructure, doesn't the absence of WHEN NOT MATCHED imply
> different semantics for the remaining updates and deletes in the
> second version of the query?
On Wed, Nov 1, 2017 at 10:19 AM, Simon Riggs wrote:
>> The problem here is: Iff the first statement uses ON CONFLICT
>> infrastructure, doesn't the absence of WHEN NOT MATCHED imply
>> different semantics for the remaining updates and deletes in the
>> second version of the
On 31 October 2017 at 18:55, Peter Geoghegan wrote:
> On Tue, Oct 31, 2017 at 2:25 AM, Simon Riggs wrote:
>> If there are challenges ahead, its reasonable to ask for test cases
>> for that now especially if you think you know what they already are.
>> Imagine
On Tue, Oct 31, 2017 at 2:25 AM, Simon Riggs wrote:
> If there are challenges ahead, its reasonable to ask for test cases
> for that now especially if you think you know what they already are.
> Imagine we go forwards 2 months - if you dislike my patch when it
> exists you
On 31 October 2017 at 12:56, Stephen Frost wrote:
> Simon,
>
> * Simon Riggs (si...@2ndquadrant.com) wrote:
>> On 30 October 2017 at 19:55, Stephen Frost wrote:
>> > I don't think MERGE should be radically different from other database
>> > systems and
Simon,
* Simon Riggs (si...@2ndquadrant.com) wrote:
> On 30 October 2017 at 19:55, Stephen Frost wrote:
> > I don't think MERGE should be radically different from other database
> > systems and just syntax sugar over a capability we have.
>
> I've proposed a SQL Standard
On 30 October 2017 at 19:55, Stephen Frost wrote:
> * Robert Haas (robertmh...@gmail.com) wrote:
>> On Sun, Oct 29, 2017 at 1:19 AM, Simon Riggs wrote:
>> > Nothing I am proposing blocks later work.
>>
>> That's not really true. Nobody's going to be
Can I add my 2c worth, as someone without a horse in the race, as it
were, in the hope that telling me how I've got this wrong might
clarify the argument a bit (or at least you can all start shouting at
me rather than each other :) )
The point of merge is to allow you to choose to either INSERT
On 30 October 2017 at 19:17, Peter Geoghegan wrote:
> On Mon, Oct 30, 2017 at 11:07 AM, Simon Riggs wrote:
>> Please explain in detail the MERGE SQL statements that you think will
>> be problematic and why.
>
> Your proposal is totally incomplete, so I can
* Robert Haas (robertmh...@gmail.com) wrote:
> On Sun, Oct 29, 2017 at 1:19 AM, Simon Riggs wrote:
> > Nothing I am proposing blocks later work.
>
> That's not really true. Nobody's going to be happy if MERGE has one
> behavior in one set of cases and an astonishingly
On Mon, Oct 30, 2017 at 10:59:43AM -0700, Peter Geoghegan wrote:
> On Mon, Oct 30, 2017 at 6:21 AM, Simon Riggs wrote:
> > If a general purpose solution exists, please explain what it is.
>
> For the umpteenth time, a general purpose solution is one that more or
> less
On Mon, Oct 30, 2017 at 11:07 AM, Simon Riggs wrote:
> Please explain in detail the MERGE SQL statements that you think will
> be problematic and why.
Your proposal is totally incomplete, so I can only surmise its
behavior in certain cases, to make a guess at what the
On 30 October 2017 at 18:59, Peter Geoghegan wrote:
> It is most emphatically *not* just a "small matter of programming".
Please explain in detail the MERGE SQL statements that you think will
be problematic and why.
--
Simon Riggshttp://www.2ndQuadrant.com/
On Mon, Oct 30, 2017 at 6:21 AM, Simon Riggs wrote:
>> That's not really true. Nobody's going to be happy if MERGE has one
>> behavior in one set of cases and an astonishingly different behavior
>> in another set of cases. If you adopt a behavior for certain cases
>> that
On 30 October 2017 at 09:44, Robert Haas wrote:
> On Sun, Oct 29, 2017 at 1:19 AM, Simon Riggs wrote:
>> Nothing I am proposing blocks later work.
>
> That's not really true. Nobody's going to be happy if MERGE has one
> behavior in one set of cases
On 29 October 2017 at 21:25, Peter Geoghegan wrote:
> The semantics that I suggest (the SQL standard's semantics) will
> require less code, and will be far simpler. Right now, I simply don't
> understand why you're insisting on using ON CONFLICT without even
> saying why. I can
On Sun, Oct 29, 2017 at 1:19 AM, Simon Riggs wrote:
> Nothing I am proposing blocks later work.
That's not really true. Nobody's going to be happy if MERGE has one
behavior in one set of cases and an astonishingly different behavior
in another set of cases. If you adopt
On Sun, Oct 29, 2017 at 4:48 AM, Simon Riggs wrote:
> I have no objection to you writing a better version than me and if my
> work inspires you to complete that in a reasonable timescale then we
> all win.
My whole point is that the way that you seem determined to go on
On 28 October 2017 at 22:04, Peter Geoghegan wrote:
> On Sat, Oct 28, 2017 at 12:49 PM, Simon Riggs wrote:
>> Nothing I am proposing blocks later work.
>
> Actually, many things will block future work if you go down that road.
> You didn't respond to the
On Sat, Oct 28, 2017 at 12:49 PM, Simon Riggs wrote:
> Nothing I am proposing blocks later work.
Actually, many things will block future work if you go down that road.
You didn't respond to the specific points I raised, but that doesn't
mean that they're not real.
>
On Fri, Oct 27, 2017 at 3:00 PM, Serge Rielau wrote:
>> What other systems *do* have this restriction? I've never seen one that did.
>
> Not clear what you are leading up to here.
> When I did MERGE in DB2 there was also no limitation:
> "Each row in the target can only be
On 28 October 2017 at 20:39, Peter Geoghegan wrote:
> On Sat, Oct 28, 2017 at 3:10 AM, Simon Riggs wrote:
>> SQL:2011 specifically states "The extent to which an
>> SQL-implementation may disallow independent changes that are not
>> significant is
On Sat, Oct 28, 2017 at 3:10 AM, Simon Riggs wrote:
> SQL:2011 specifically states "The extent to which an
> SQL-implementation may disallow independent changes that are not
> significant is implementation-defined”, so in my reading the above
> behaviour would make us fully
On 28 October 2017 at 00:31, Michael Paquier wrote:
> On Fri, Oct 27, 2017 at 9:00 AM, Robert Haas wrote:
>> On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs wrote:
>>> I didn't say it but my intention was to just throw an
On Fri, Oct 27, 2017 at 02:13:27PM -0700, srielau wrote:
> While the standard may not require a unique index for the ON clause I have
> never seen a MERGE statement that did not have this property. So IMHO this
> is a reasonable restrictions.
I don't understand how one could have a conflict upon
On Fri, Oct 27, 2017 at 9:00 AM, Robert Haas wrote:
> On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs wrote:
>> I didn't say it but my intention was to just throw an ERROR if no
>> single unique index can be identified.
>>
>> It could be possible to
via Newton Mail
[https://cloudmagic.com/k/d/mailapp?ct=dx=9.8.79=10.12.6=email_footer_2]
On Fri, Oct 27, 2017 at 2:42 PM, Peter Geoghegan wrote:
On Fri, Oct 27, 2017 at 2:13 PM, srielau wrote:
> While the standard may not require a unique index for the ON clause
On Fri, Oct 27, 2017 at 2:13 PM, srielau wrote:
> While the standard may not require a unique index for the ON clause I have
> never seen a MERGE statement that did not have this property. So IMHO this
> is a reasonable restrictions.
The Oracle docs on MERGE say nothing about
Simon,
Nice writeup.
While the standard may not require a unique index for the ON clause I have
never seen a MERGE statement that did not have this property. So IMHO this
is a reasonable restrictions.
In fact I have only ever seen two flavors of usage:
* Single row source (most often simply a
On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs wrote:
> I didn't say it but my intention was to just throw an ERROR if no
> single unique index can be identified.
>
> It could be possible to still run MERGE in that situaton but we would
> need to take a full table lock at
On Fri, Oct 27, 2017 at 6:24 AM, Robert Haas wrote:
> I think one of the reasons why Peter Geoghegan decided to pursue
> INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL
> syntax, he felt free to mandate a non-standard SQL requirement, namely
> the
On Fri, Oct 27, 2017 at 7:41 AM, Simon Riggs wrote:
> Good points.
>
> I didn't say it but my intention was to just throw an ERROR if no
> single unique index can be identified.
You'd also throw an error when there was no "upsert compatible" join
quals, I take it?
I don't
On 27 October 2017 at 15:24, Robert Haas wrote:
> On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs wrote:
>> Questions?
>
> I think one of the reasons why Peter Geoghegan decided to pursue
> INSERT .. ON CONFLICT UPDATE was that, because it is
On Fri, Oct 27, 2017 at 10:55 AM, Simon Riggs wrote:
> Questions?
I think one of the reasons why Peter Geoghegan decided to pursue
INSERT .. ON CONFLICT UPDATE was that, because it is non-standard SQL
syntax, he felt free to mandate a non-standard SQL requirement, namely
Simon Riggs wrote:
> Earlier thoughts on how this could/could not be done were sometimes
> imprecise or inaccurate, so I have gone through the command per
> SQL:2011 spec and produced a definitive spec in the form of an SGML
> ref page. This is what I intend to deliver for PG11.
Nice work. I
Hey,
It looks quite nice. Personally I'd like to also have the returning
statement, and have the number of deleted and inserted rows as separate
numbers in the output message.
regards
Szymon Lipiński
pt., 27.10.2017, 10:56 użytkownik Simon Riggs
napisał:
> I'm working on
I'm working on re-submitting MERGE for PG11
Earlier thoughts on how this could/could not be done were sometimes
imprecise or inaccurate, so I have gone through the command per
SQL:2011 spec and produced a definitive spec in the form of an SGML
ref page. This is what I intend to deliver for PG11.
On Wed, 2008-04-16 at 22:18 -0400, Robert Treat wrote:
* USING query can be a VALUES clause if we wish to do single/few row
operations, so MERGE can be used for bulk-loading and OLTP
* There is no RETURNING option for MERGE, nor for any INSERT/UPDATE
sub-clauses
Is there a reason for
On Thu, 2008-04-17 at 07:31 +0100, Simon Riggs wrote:
* MERGE will perform a left outer join between source on left and target
on right. There must be no more than 1 row from table-ref for each row
in the table. Each row in the table can only be updated once during each
MERGE
[EMAIL PROTECTED] (Simon Riggs) writes:
Should there be a new rule option? ie. ON MERGE rules ?
Maybe, but not as part of this project.
That seems to warrant a bit of elaboration...
If we're running a MERGE, and it performs an INSERT or UPDATE of a
particular tuple in(to) a particular
I've analysed various flavours of MERGE command to understand and
propose what we should use for PostgreSQL.
The results aren't what you'd expect from a quick flick through the
standard, so lets look at my main concerns:
1. The simplest syntax is for SQL:2003. The syntax for DB2, SQL Server
and
On Wednesday 16 April 2008 14:58, Simon Riggs wrote:
I've analysed various flavours of MERGE command to understand and
propose what we should use for PostgreSQL.
The results aren't what you'd expect from a quick flick through the
standard, so lets look at my main concerns:
1. The simplest
73 matches
Mail list logo