The following two files specify the behaviour of the MERGE statement and
how it will work in the world of PostgreSQL. In places, this supercedes
my recent commentary on MERGE, particularly with regard to triggers.

Neither of these files is intended for CVS.

The HTML file was generated from SGML source, though the latter is not
included here for clarity.

The test file shows how I expect a successful test run to look when a
regression test is executed with a working version of final MERGE patch
applied. It has behavioural comments in it also, to make it slightly
more readable.

If anybody has any questions, ask them now please, before I begin
detailed implementation.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Simon,

would you support RETURNING clause?

Regards
Pavel Stehule

On 21/04/2008, Simon Riggs <simon@2ndquadrant.com> wrote:
> The following two files specify the behaviour of the MERGE statement and
> how it will work in the world of PostgreSQL. In places, this supercedes
> my recent commentary on MERGE, particularly with regard to triggers.
>
> Neither of these files is intended for CVS.
>
> The HTML file was generated from SGML source, though the latter is not
> included here for clarity.
>
> The test file shows how I expect a successful test run to look when a
> regression test is executed with a working version of final MERGE patch
> applied. It has behavioural comments in it also, to make it slightly
> more readable.
>
> If anybody has any questions, ask them now please, before I begin
> detailed implementation.
>
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, 2008-04-21 at 22:18 +0200, Pavel Stehule wrote:

> would you support RETURNING clause?

I wouldn't rule it out completely, but not in the first implementation
because
- its more work
- its not in the SQL Standard
- neither Oracle nor DB2 support it either, so its only going to provide
incompatibility
- there are some wrinkles with MERGE that means I don't want to
over-complicate it because it looks to me like it will change in future
versions of the standard
- not sure what the use case for that will be

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Apr 21, 2008, at 4:08 PM, Simon Riggs wrote:

> The following two files specify the behaviour of the MERGE statement
> and
> how it will work in the world of PostgreSQL. In places, this
> supercedes
> my recent commentary on MERGE, particularly with regard to triggers.
>
> Neither of these files is intended for CVS.
>
> The HTML file was generated from SGML source, though the latter is not
> included here for clarity.
>
> The test file shows how I expect a successful test run to look when a
> regression test is executed with a working version of final MERGE
> patch
> applied. It has behavioural comments in it also, to make it slightly
> more readable.
>
> If anybody has any questions, ask them now please, before I begin
> detailed implementation.


"MERGE will not invoke Rules." Does this imply that MERGE cannot be
used on views or that the resulting INSERTs or UPDATEs do not work on
views?

Cheers,
M

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, 2008-04-21 at 16:38 -0400, A.M. wrote:

> "MERGE will not invoke Rules." Does this imply that MERGE cannot be
> used on views or that the resulting INSERTs or UPDATEs do not work on
> views?

Yes, that's right. Just like COPY. That seems fine to me because you're
likely to be doing a MERGE immediately after a COPY anyway, so the
restriction just continues.

Rules for Insert, Update and Delete are almost identical to the way
MERGE works anyway, so there's no particular loss of functionality. That
was why I co-opted the ability to DO NOTHING in a WHEN clause from the
way PostgreSQL Rules work.

I'm not taking any explicit decisions to exclude them permanently. I do
think its possible that we could support them and possibly very cheaply,
but I wouldn't make any promises initially.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2008-04-21 at 16:38 -0400, A.M. wrote:
>> "MERGE will not invoke Rules." Does this imply that MERGE cannot be
>> used on views or that the resulting INSERTs or UPDATEs do not work on
>> views?

> Yes, that's right. Just like COPY.

I find this to be pretty ugly. COPY is a special case because
(a) it is a utility statement not a plannable one, and (b) its only
reason to exist is to transfer data as fast as possible, so bypassing
rules isn't an unreasonable restriction. MERGE has neither of those
properties, and thus arguing that it can or should be like COPY is an
entirely unconvincing proposition. (In fact, I don't even want to think
about what kind of crock you're going to need in order to get it through
the planner without also going through the rewriter.)

Please think a bit harder.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, 2008-04-21 at 20:28 -0400, Tom Lane wrote:

> In fact, I don't even want to think
> about what kind of crock you're going to need in order to get it through
> the planner without also going through the rewriter.

Hmmm, I hadn't thought I might be adding work rather than avoiding it.

I'll give it a go.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, 2008-04-21 at 20:28 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Mon, 2008-04-21 at 16:38 -0400, A.M. wrote:
> >> "MERGE will not invoke Rules." Does this imply that MERGE cannot be
> >> used on views or that the resulting INSERTs or UPDATEs do not work on
> >> views?
>
> > Yes, that's right. Just like COPY.
>
> I find this to be pretty ugly. COPY is a special case because
> (a) it is a utility statement not a plannable one, and (b) its only
> reason to exist is to transfer data as fast as possible, so bypassing
> rules isn't an unreasonable restriction. MERGE has neither of those
> properties, and thus arguing that it can or should be like COPY is an
> entirely unconvincing proposition.

Unrelated to rule processing, you did read the bit about MERGE and race
conditions? ISTM that MERGE as it stands isn't very useful for anything
other than large data loads since its going to cause problems if used
concurrently.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs wrote:

> Unrelated to rule processing, you did read the bit about MERGE and race
> conditions? ISTM that MERGE as it stands isn't very useful for anything
> other than large data loads since its going to cause problems if used
> concurrently.

But that's how the committee designed it, yes?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Simon Riggs" <simon@2ndquadrant.com> writes:

> Unrelated to rule processing, you did read the bit about MERGE and race
> conditions? ISTM that MERGE as it stands isn't very useful for anything
> other than large data loads since its going to cause problems if used
> concurrently.

If there are race conditions what advantage does it offer over writing plpgsql
or client code to do it?

I thought the whole advantage of having a built-in command is that it could do
the kind of locking our unique constraints do to avoid race conditions.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers