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

RETURNING and DO INSTEAD ... Intentional or not?

From
Josh Berkus
Date:
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


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

From
"Joshua D. Drake"
Date:
-----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-----


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

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


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

From
Josh Berkus
Date:
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


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

From
Richard Huxton
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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


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

From
Tom Lane
Date:
Richard Huxton <dev@archonet.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


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

From
Josh Berkus
Date:
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


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

From
Josh Berkus
Date:
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


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

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


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

From
Josh Berkus
Date:
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



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

From
"Joshua D. Drake"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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-----


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

From
Bruce Momjian
Date:
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@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


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

From
"Joshua D. Drake"
Date:
-----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-----


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

From
"Pavel Stehule"
Date:
> 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