Thread: 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@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
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@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
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
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@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
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@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-----
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. +
-----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