GSOC13 proposal - extend RETURNING syntax

On Thu, May 02, 2013 at 11:04:15AM +0200, Karol Trzcionka wrote:

Hello,
I'm student who want to participate in Google Summer of Code. I want to
implement feature which allows to get old values directly from update
statement. I mean there should be possibility to choose the value
immedietly before or after update in RETURNING statement. The syntax may
be realized as "aliases". That means: OLD keywordswould be alias to row
before update and NEW to row after update. The conclusion of syntax is:
UPDATE foo SET bar=bar+1 RETURNING OLD.bar AS old_bar, NEW.bar AS new_bar;
UPDATE foo SET ... RETURNING NEW.* will be equivalent to UPDATE foo SET
... RETURNING foo.*
It may be possible to add similar syntax to DELETE and INSERT statements
but I'm not sure if it makes a sense (OLD for DELETE will be alias to
row before delete, NEW for INSERT will be alias to row after insert and
all triggers - however what about NEW for delete and OLD for INSERT?).
Additionally NEW and OLD values will be reserved keywords (it might be
some capability problem since in new PostgreSQL it isn't reserved -
however standard says it is and in old PgSQL it was).
I'd like to hear (read) yours feedback about syntax and/or implement
issues related to this proposal.
Regards,
Karol Trzcionka

I would like to include the proposal as we've hammered it out together
on IRC and on GSoC site below.

Cheers,
David.

1. As the SQL standard mandates that OLD and NEW be reserved words, we'll re-reserve them.

2. Let's make OLD and NEW have the same meaning that INSERT/UPDATE/DELETE have when returning rows from the changed table. In particular

INSERT INTO foo (...) RETURNING NEW.*

will be equivalent to

INSERT INTO foo(...) RETURNING foo.*

Similarly for UPDATE and DELETE:

UPDATE foo SET ... RETURNING NEW.*

will be equivalent to

UPDATE foo SET ... RETURNING foo.*

and

DELETE FROM foo ... RETURNING OLD.*

will be equivalent to

DELETE FROM foo ... RETURNING foo.*

As RETURNING clauses have access to everything in the FROM/USING clause, it is important to limit the NEW/OLD rows as being only those in the table being written to in the statement.

3. Let's add an option to UPDATE so that it can RETURN OLD with the same characteristics as above, namely that it refers only to constants and columns in the updated table and not to everything available from the USING clause if included.

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

David Fetter <david@fetter.org> writes:

1. As the SQL standard mandates that OLD and NEW be reserved words, we'll re-reserve them.

As I mentioned yesterday, I'm not exactly thrilled with re-reserving
those, and especially not NEW as it is utterly unnecessary (since the
default would already be to return the NEW column).

It should in any case be possible to do this without converting them to
reserved words; rather the implementation could be that those table
aliases are made available when parsing the UPDATE RETURNING expressions.
(This is, in fact, the way that rules use these names now.) Probably it
should work something like "add these aliases if they don't already
exist in the query", so as to avoid breaking existing applications.

I don't really see a lot of value in hacking the behavior of either
INSERT RETURNING or DELETE RETURNING.

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

W dniu 02.05.2013 17:17, Tom Lane pisze:

It should in any case be possible to do this without converting them
to reserved words; rather the implementation could be that those table
aliases are made available when parsing the UPDATE RETURNING
expressions. (This is, in fact, the way that rules use these names
now.) Probably it should work something like "add these aliases if
they don't already exist in the query", so as to avoid breaking
existing applications. I don't really see a lot of value in hacking
the behavior of either INSERT RETURNING or DELETE RETURNING. regards,
tom lane

I'm not sure about it. If it is not reserved keyword how can user get
old value from table named "old". The new value is not a problem
(doesn't conflict) but what should happened in statement:
UPDATE old SET foo=foo+1 RETURNING old.foo;
If it returns old value, it'll break capability. If it returns new value
(as now), there is no possibility to get old value (and user cries
because of broken feature).
Regards,
Karol Trzcionka

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

On 2013-05-02 17:32, Karol Trzcionka wrote:

W dniu 02.05.2013 17:17, Tom Lane pisze:

It should in any case be possible to do this without converting them
to reserved words; rather the implementation could be that those table
aliases are made available when parsing the UPDATE RETURNING
expressions. (This is, in fact, the way that rules use these names
now.) Probably it should work something like "add these aliases if
they don't already exist in the query", so as to avoid breaking
existing applications. I don't really see a lot of value in hacking
the behavior of either INSERT RETURNING or DELETE RETURNING.

what should happened in statement:
UPDATE old SET foo=foo+1 RETURNING old.foo;
If it returns old value, it'll break capability. If it returns new value
(as now), there is no possibility to get old value (and user cries
because of broken feature).

In Tom's proposal that would give you the "new" value.

Personally I would maybe prefer reserving NEW/OLD, but if we go through
with Tom's idea, this should work:

UPDATE old myold SET foo=foo+1 RETURNING myold.foo, old.foo;

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

Regards,
Marko Tiikkaja

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

Marko Tiikkaja <marko@joh.to> writes:

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

That's a really good point: if you follow this approach then you're
creating fundamental conflicts for use of the feature in trigger
functions or rules, which will necessarily have conflicting uses of
those names. Yeah, we could define scoping rules such that there's
an unambiguous interpretation, but then the user is just out of luck
if he wants to reference the other definition. (This problem is
probably actually worse if you implement with reserved words rather
than aliases.)

I'm thinking it would be better to invent some other notation for access
to old-row values.

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 2013-05-02 12:23:19 -0400, Tom Lane wrote:

Marko Tiikkaja <marko@joh.to> writes:

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

That's a really good point: if you follow this approach then you're
creating fundamental conflicts for use of the feature in trigger
functions or rules, which will necessarily have conflicting uses of
those names. Yeah, we could define scoping rules such that there's
an unambiguous interpretation, but then the user is just out of luck
if he wants to reference the other definition. (This problem is
probably actually worse if you implement with reserved words rather
than aliases.)

I'm thinking it would be better to invent some other notation for access
to old-row values.

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On Thu, May 02, 2013 at 06:28:53PM +0200, Andres Freund wrote:

On 2013-05-02 12:23:19 -0400, Tom Lane wrote:

Marko Tiikkaja <marko@joh.to> writes:

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

That's a really good point: if you follow this approach then you're
creating fundamental conflicts for use of the feature in trigger
functions or rules, which will necessarily have conflicting uses of
those names. Yeah, we could define scoping rules such that there's
an unambiguous interpretation, but then the user is just out of luck
if he wants to reference the other definition. (This problem is
probably actually worse if you implement with reserved words rather
than aliases.)

I'm thinking it would be better to invent some other notation for access
to old-row values.

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

BEFORE/AFTER seems more logical to me. Yes, those words both have
meaning in, for example, a trigger definition, but they're clearly
separable by context.

Yay, bike-shedding!

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

2013/5/2 Tom Lane <tgl@sss.pgh.pa.us>

Marko Tiikkaja <marko@joh.to> writes:

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

That's a really good point: if you follow this approach then you're
creating fundamental conflicts for use of the feature in trigger
functions or rules, which will necessarily have conflicting uses of
those names. Yeah, we could define scoping rules such that there's
an unambiguous interpretation, but then the user is just out of luck
if he wants to reference the other definition. (This problem is
probably actually worse if you implement with reserved words rather
than aliases.)

I'm thinking it would be better to invent some other notation for access
to old-row values.

I am not sure, but I am thinking so NEW and OLD are used in some statements
and features ANSI SQL 2012, so probably we should to do keywords from these
words if we would to support modern ANSI SQL

Regards

Pavel

David Fetter <david@fetter.org> writes:

On Thu, May 02, 2013 at 06:28:53PM +0200, Andres Freund wrote:

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

BEFORE/AFTER seems more logical to me.

Works for me.

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

W dniu 02.05.2013 19:40, Tom Lane pisze:

BEFORE/AFTER seems more logical to me.

Works for me.

What do you think about function- or cast-like syntax. I mean:
RETURNING OLD(foo.bar)
or RETURNING OLD(foo).bar
or RETURNING (foo::OLD).bar ?
I think none of them should conflict with any other statements.
Regards,
Karol Trzcionka

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

On Thu, May 02, 2013 at 01:40:59PM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Thu, May 02, 2013 at 06:28:53PM +0200, Andres Freund wrote:

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

BEFORE/AFTER seems more logical to me.

Works for me.

regards, tom lane

Maybe we can make BEFORE and AFTER implied aliases rather than
keywords. What say?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Sent from my iPad

On 03-May-2013, at 0:07, David Fetter <david@fetter.org> wrote:

On Thu, May 02, 2013 at 01:40:59PM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Thu, May 02, 2013 at 06:28:53PM +0200, Andres Freund wrote:

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

BEFORE/AFTER seems more logical to me.

Works for me.

regards, tom lane

Maybe we can make BEFORE and AFTER implied aliases rather than
keywords. What say?

I agree.Overall,I like the concept.

Regards,

Atri

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

David Fetter <david@fetter.org> writes:

Maybe we can make BEFORE and AFTER implied aliases rather than
keywords. What say?

Well, they still have to be unreserved keywords for their use in
trigger-related commands, but as far as this feature is concerned
I think they're best off being handled as automatically-supplied
aliases. IOW the patch needn't touch gram.y at all.

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

Karol Trzcionka <karlikt@gmail.com> writes:

What do you think about function- or cast-like syntax. I mean:
RETURNING OLD(foo.bar)
or RETURNING OLD(foo).bar
or RETURNING (foo::OLD).bar ?
I think none of them should conflict with any other statements.

I think you'll find those alternatives are at least as ugly to
implement as they are to look at ...

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

W dniu 02.05.2013 20:45, Tom Lane pisze:

David Fetter <david@fetter.org> writes:

Maybe we can make BEFORE and AFTER implied aliases rather than
keywords. What say?

Well, they still have to be unreserved keywords for their use in
trigger-related commands, but as far as this feature is concerned
I think they're best off being handled as automatically-supplied
aliases. IOW the patch needn't touch gram.y at all.

Well... the syntax which wouldn't conflict with Pl/PgSQL is (draft):
INSERT INTO foo ... RETURNING AFTER.*
UPDATE foo SET ... RETURNING AFTER.*, BEFORE.*
DELETE FROM foo ... RETURNING BEFORE.*
It will not solve the problems:
1. How to access to old rows if the table is named "BEFORE"?
2. Should AFTER for DELETE and BEFORE for INSERT be allowed? If yes what
should they return? I mean: what should be result of:
INSERT INTO foo ... RETURNING BEFORE.*
and
DELETE FROM foo ... RETURNING AFTER.*
?
The best summarize of dropping NEW/OLD keywords for this proposal was
proposed while IRC meeting:
create or replace function ft1(integer) returns integer language plpgsql
as $f$ <<x>> declare r rt1; begin select 1 as a into r; update rt1 r set
a = a + 1 returning XXX into r; raise notice 'r = %', r; return null;
end; $f$;
Regards,
Karol Trzcionka

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

On 03/05/13 04:52, David Fetter wrote:

On Thu, May 02, 2013 at 06:28:53PM +0200, Andres Freund wrote:

On 2013-05-02 12:23:19 -0400, Tom Lane wrote:

Marko Tiikkaja <marko@joh.to> writes:

What I'm more interested in is: how can we make this feature work in
PL/PgSQL where OLD means something different?

That's a really good point: if you follow this approach then you're
creating fundamental conflicts for use of the feature in trigger
functions or rules, which will necessarily have conflicting uses of
those names. Yeah, we could define scoping rules such that there's
an unambiguous interpretation, but then the user is just out of luck
if he wants to reference the other definition. (This problem is
probably actually worse if you implement with reserved words rather
than aliases.)

I'm thinking it would be better to invent some other notation for access
to old-row values.

prior/after? Both are unreserved keywords atm and it seems far less
likely to have conflicts than new/old.

BEFORE/AFTER seems more logical to me. Yes, those words both have
meaning in, for example, a trigger definition, but they're clearly
separable by context.

Yay, bike-shedding!

Cheers,
David.

I prefer 'PRIOR & 'AFTER' as the both have the same length
- but perhaps that is just me! :-)

Cheers,
Gavin

W dniu 02.05.2013 23:34, Gavin Flower pisze:

I prefer 'PRIOR & 'AFTER' as the both have the same length
- but perhaps that is just me! :-)

I think it doesn't matter at now because PRIOR has the same problems as
BEFORE ;)
Regards,
Karol

Karol Trzcionka <karlikt@gmail.com> writes:

It will not solve the problems:
1. How to access to old rows if the table is named "BEFORE"?

The user can simply choose to use a different table alias, as Andres
explained upthread. If any table's active alias is "before" (or
"after"), we just don't activate the corresponding implicit alias.

2. Should AFTER for DELETE and BEFORE for INSERT be allowed? If yes what
should they return?

These cases should certainly fail. Now, IMO there's no very good reason
to alter the behavior at all for INSERT/DELETE; only UPDATE has an issue
here. But if we were going to support the extra aliases in those
commands, only the ones that actually make sense should be allowed.

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

Hello,
according to my mentor's suggestion, I send first PoC patch of
"RETURNING AFTER/BEFORE" statement. Some info:
- it is early version - more hack PoC than RC patch
- AFTER in this version works as decribed before but it returns row
after update but before post-update before (to be fixed or switch with
current "*" behaviour - I don't know yet)
- patch passes all regression tests
- the code is uncommented already, to be fixed
I'm not sure what may fail so you use it on your risk ;)
Regards,
Karol