Re: RETURNING and DO INSTEAD ... Intentional or not?

All,

A Hibernate developer pointed out the following odd behavior to me in 8.2.1:

create table test ( test1 text );
create table test2 ( test_col text );
create rule test_insert as on insert to test do instead insert into test2
values ( NEW.test1 ) RETURNING test2.test_col;

postgres=# insert into test values ( 'joe' );
INSERT 0 1

... no RETURNING. In fact, there doesn't seem to be any way to capture the
RETURNING output if you have a DO INSTEAD rule on an insert. Is this
intentional, or a bug?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh Berkus wrote:
> All,
>
> A Hibernate developer pointed out the following odd behavior to me in 8.2.1:
>
> create table test ( test1 text );
> create table test2 ( test_col text );
> create rule test_insert as on insert to test do instead insert into test2
> values ( NEW.test1 ) RETURNING test2.test_col;
>
> postgres=# insert into test values ( 'joe' );
> INSERT 0 1
>
> ... no RETURNING. In fact, there doesn't seem to be any way to capture the
> RETURNING output if you have a DO INSTEAD rule on an insert. Is this
> intentional, or a bug?

I think this is a side effect of rules. I seem to remember that creating
update view with rules is broken in a similar fashion.

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG6BlhATb/zqfZUUQRAntqAJ9rmCeX7t/23i5NIW1PpWIi8HGm/ACgkaMg
k+VQip5jZolm+Xs7BsiZwkw=
=hdNt
-----END PGP SIGNATURE-----

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> A Hibernate developer pointed out the following odd behavior to me in 8.2.1:

> create table test ( test1 text );
> create table test2 ( test_col text );
> create rule test_insert as on insert to test do instead insert into test2
> values ( NEW.test1 ) RETURNING test2.test_col;

> postgres=# insert into test values ( 'joe' );
> INSERT 0 1

> ... no RETURNING.

It would surely be quite broken for an INSERT that has *not* got a
returning clause to spit data at you, don't you think?

What the RETURNING clause in the rule does is let you define the data
that should be returned if the rewritten INSERT had a returning clause
to start with.

regards, tom lane

Tom,

> What the RETURNING clause in the rule does is let you define the data
> that should be returned if the rewritten INSERT had a returning clause
> to start with.

Hmmm. Aha, that works:

postgres=# insert into test values ( 'mary' ) returning test1;
test1
-------
mary

So, this should probably be documented to avoid confusion like mine. Will
write something up ...

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> A Hibernate developer pointed out the following odd behavior to me in 8.2.1:
>
>> create table test ( test1 text );
>> create table test2 ( test_col text );
>> create rule test_insert as on insert to test do instead insert into test2
>> values ( NEW.test1 ) RETURNING test2.test_col;
>
>> postgres=# insert into test values ( 'joe' );
>> INSERT 0 1
>
>> ... no RETURNING.
>
> It would surely be quite broken for an INSERT that has *not* got a
> returning clause to spit data at you, don't you think?
>
> What the RETURNING clause in the rule does is let you define the data
> that should be returned if the rewritten INSERT had a returning clause
> to start with.

Sorry - haven't got a CSV download here, or I'd check myself. Does this
just allow an INSERT...RETURNING inside the rule, or could it be
something like:
CREATE RULE ... AS ON INSERT ... DO INSTEAD SELECT f(NEW.test1);

--
Richard Huxton
Archonet Ltd

Richard Huxton <dev(at)archonet(dot)com> writes:
> Tom Lane wrote:
>> What the RETURNING clause in the rule does is let you define the data
>> that should be returned if the rewritten INSERT had a returning clause
>> to start with.

> Sorry - haven't got a CSV download here, or I'd check myself. Does this
> just allow an INSERT...RETURNING inside the rule, or could it be
> something like:
> CREATE RULE ... AS ON INSERT ... DO INSTEAD SELECT f(NEW.test1);

Well, that's what you do if you want to deliberately break the normal
behavior of INSERT, ie, have it fire back data unconditionally.
What the rule definition of RETURNING is intended for is to let you
write rules that support an updatable view that does the right things,
ie INSERT and INSERT RETURNING on the view both do what you'd expect
them to do if the view were a plain table.

Josh, this *is* documented; see the CREATE RULE reference page for full
details, and there's at least passing references here:
http://developer.postgresql.org/pgdocs/postgres/rules-update.html#RULES-UPDATE-VIEWS

regards, tom lane

Tom,

> Josh, this *is* documented; see the CREATE RULE reference page for full
> details, and there's at least passing references here:
> http://developer.postgresql.org/pgdocs/postgres/rules-update.html#RULES-UPD
>ATE-VIEWS

Yeah, it's just hard to find since it's buried in an offhand example in a
subsection which is 5 pages long, and the necessity to match up columns and
data types in order is not clearly explained. I've submitted what I believe
are improvements.

I'll note that we currently prevent adding RETURNING to a *conditional* DO
INSTEAD rule. This means that if we have a conditional DO INSTEAD rule which
inserts into a different table than the final unconditional rule, we'll be
RETURNING wrong or empty values. Mind you, that's a pretty extreme corner
case.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

All,

> I'll note that we currently prevent adding RETURNING to a *conditional* DO
> INSTEAD rule. This means that if we have a conditional DO INSTEAD rule
> which inserts into a different table than the final unconditional rule,
> we'll be RETURNING wrong or empty values. Mind you, that's a pretty
> extreme corner case.

FYI, after some tinkering around, I've found that RETURNING is 100%
incompatible with any table which has conditional DO INSTEAD rules; there's
just no way to make it work and return any intelligible data. This would be
a completely corner case, except that people use conditional DO INSTEAD rules
heavily with partitioning (and yes, real users are complaining).

I don't see this as super-urgent to fix for 8.3, but can we put it up as a
TODO?

-- Make it possible to use RETURNING together with conditional DO INSTEAD
rules, such as for partitioning setups.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> FYI, after some tinkering around, I've found that RETURNING is 100%
> incompatible with any table which has conditional DO INSTEAD rules; there's
> just no way to make it work and return any intelligible data. This would be
> a completely corner case, except that people use conditional DO INSTEAD rules
> heavily with partitioning (and yes, real users are complaining).

Those would be real users who are not on any PG mailing list? Cause I
have not seen any complaints, much less any proposals for a solution...

regards, tom lane

Tom,

> Those would be real users who are not on any PG mailing list? Cause I
> have not seen any complaints, much less any proposals for a solution...

On the Hibernate user mailing list.

--Josh

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> FYI, after some tinkering around, I've found that RETURNING is 100%
>> incompatible with any table which has conditional DO INSTEAD rules; there's
>> just no way to make it work and return any intelligible data. This would be
>> a completely corner case, except that people use conditional DO INSTEAD rules
>> heavily with partitioning (and yes, real users are complaining).
>
> Those would be real users who are not on any PG mailing list? Cause I
> have not seen any complaints, much less any proposals for a solution...

The majority of our users do not sit on a postgresql mailing list. They
sit on php-db, hibernate, perl-dbd etc...

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG6rUGATb/zqfZUUQRAib4AJ9I+/PvPCOutHJVZYdOgsDyt2WveACcDbyw
V/jt2lJquaQ3O4azJumfYUs=
=GhO1
-----END PGP SIGNATURE-----

Josh Berkus wrote:
> All,
>
> > I'll note that we currently prevent adding RETURNING to a *conditional* DO
> > INSTEAD rule. This means that if we have a conditional DO INSTEAD rule
> > which inserts into a different table than the final unconditional rule,
> > we'll be RETURNING wrong or empty values. Mind you, that's a pretty
> > extreme corner case.
>
> FYI, after some tinkering around, I've found that RETURNING is 100%
> incompatible with any table which has conditional DO INSTEAD rules; there's
> just no way to make it work and return any intelligible data. This would be
> a completely corner case, except that people use conditional DO INSTEAD rules
> heavily with partitioning (and yes, real users are complaining).
>
> I don't see this as super-urgent to fix for 8.3, but can we put it up as a
> TODO?
>
> -- Make it possible to use RETURNING together with conditional DO INSTEAD
> rules, such as for partitioning setups.

Added to TODO:

* Make it possible to use RETURNING together with conditional DO INSTEAD
rules, such as for partitioning setups

http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Momjian wrote:
> Josh Berkus wrote:
>> All,

>> -- Make it possible to use RETURNING together with conditional DO INSTEAD
>> rules, such as for partitioning setups.
>
> Added to TODO:
>
> * Make it possible to use RETURNING together with conditional DO INSTEAD
> rules, such as for partitioning setups
>
> http://archives.postgresql.org/pgsql-hackers/2007-09/msg00577.php
>

Would it make sense to expand this to something like:

Make it possible for rules to return affected tuples?

I come to this because if you use a rule to create an updateable view,
you never know how many rows the view actually updated.

Joshua D. Drake

>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG6tocATb/zqfZUUQRAnlyAJ48kiUurDxxlnVINhY0NyhnvqFBTgCcD3kk
lcIG2DupUSPnscg+vUhhMC0=
=GAHe
-----END PGP SIGNATURE-----

> Would it make sense to expand this to something like:
>
> Make it possible for rules to return affected tuples?
>
> I come to this because if you use a rule to create an updateable view,
> you never know how many rows the view actually updated.
>

Updatable views can be (maybe) implemented with updatable cursors.

http://archives.postgresql.org/pgsql-hackers/2007-06/msg00335.php

Regards
Pavel Stehule