Thread: INSERT .... RETURNING

INSERT .... RETURNING

From
Rafal Pietrak
Date:
Hi Everybody,

Forgive my sarcasm below, but I just *adore* postgres for years, now. I
hope it's all natural with this level of emotions to be deeply hurt when
the object of attraction is (to quote HHTTG by Douglas Adams): " ....
almost, but not quite entirely unlike tea".

I've just upgraded to v8.3.4 ... since eventually it does have
INSERT ... RETURNING extention to the SQL standard.

The documentation was quite encouraging: "if INSERT contains RETURNING,
the result is similar to SELECT....".

So, first thing after upgrade I tried:
+--------------------------------
|CREATE TABLE test1 (id serial, info text);
|CREATE TEMP TABLE test2 AS INSERT INTO test1 (info) VALUES
( random()::text );
+-----------------------------------

I also tried other scenarios with "INSERT... RETURNING ..." where SELECT
could be used as subquery - all this to no avail.

At this point my sad conclusion is, that "similar to SELECT" behavior is
limited to client-side server API, not to SQL per se.

The question is: Is this a feature or a bug? (by "a bug" here I mean: an
*unintended* omission).

Or may be there is currently some way to capture the "RETURNING" into
SQL commands sequence (meaning, back into the database)? And I just
haven't discovered the proper way from the docs?? If so, someone pls
point me to those docs.

I'm rising this issue, since this really is a long awaited feature and I
was really waiting for it - particularly after I've faced some badly
entangled schemas in the past, which could easily be cut through using
"CREATE TEMP TABLE .... AS INSERT...".

Is there any way to get SQL-level functionality like this, with
"current" v8.3.4 (or similar)? Or is there any hope for such
functionality to show up in future releases (I believe, it's only a
syntax matter now,  all the internals look like already implemented)?

Thenx,

-R

Re: INSERT .... RETURNING

From
"Merlin Moncure"
Date:
On Wed, Nov 5, 2008 at 2:41 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> Hi Everybody,
>
> Forgive my sarcasm below, but I just *adore* postgres for years, now. I
> hope it's all natural with this level of emotions to be deeply hurt when
> the object of attraction is (to quote HHTTG by Douglas Adams): " ....
> almost, but not quite entirely unlike tea".
>
> I've just upgraded to v8.3.4 ... since eventually it does have
> INSERT ... RETURNING extention to the SQL standard.
>
> The documentation was quite encouraging: "if INSERT contains RETURNING,
> the result is similar to SELECT....".
>
> So, first thing after upgrade I tried:
> +--------------------------------
> |CREATE TABLE test1 (id serial, info text);
> |CREATE TEMP TABLE test2 AS INSERT INTO test1 (info) VALUES
> ( random()::text );
> +-----------------------------------
>
> I also tried other scenarios with "INSERT... RETURNING ..." where SELECT
> could be used as subquery - all this to no avail.
>
> At this point my sad conclusion is, that "similar to SELECT" behavior is
> limited to client-side server API, not to SQL per se.
>
> The question is: Is this a feature or a bug? (by "a bug" here I mean: an
> *unintended* omission).
>
> Or may be there is currently some way to capture the "RETURNING" into
> SQL commands sequence (meaning, back into the database)? And I just
> haven't discovered the proper way from the docs?? If so, someone pls
> point me to those docs.
>
> I'm rising this issue, since this really is a long awaited feature and I
> was really waiting for it - particularly after I've faced some badly
> entangled schemas in the past, which could easily be cut through using
> "CREATE TEMP TABLE .... AS INSERT...".
>
> Is there any way to get SQL-level functionality like this, with
> "current" v8.3.4 (or similar)? Or is there any hope for such
> functionality to show up in future releases (I believe, it's only a
> syntax matter now,  all the internals look like already implemented)?

This is a (very) faq.
Everyone wants it.
It's more complicated than it looks (triggers).
8.4 partially addresses this...it works in functions.

merlin

Re: INSERT .... RETURNING

From
Rafal Pietrak
Date:
Hi,

On Wed, 2008-11-05 at 10:34 -0500, Merlin Moncure wrote:
> On Wed, Nov 5, 2008 at 2:41 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> > Hi Everybody,
[...]
> > I've just upgraded to v8.3.4 ... since eventually it does have
> > INSERT ... RETURNING extention to the SQL standard.
> >
> > The documentation was quite encouraging: "if INSERT contains RETURNING,
> > the result is similar to SELECT....".
[....]
> This is a (very) faq.
> Everyone wants it.

I see. Sounds promising :)

> It's more complicated than it looks (triggers).

Could you give me pointers where I could get some more information on
this problems? (simple postgres-doc-search and google queries on the
subject didn't gave any interesting results).


-R

Re: INSERT .... RETURNING

From
"Merlin Moncure"
Date:
On Wed, Nov 5, 2008 at 11:20 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> Hi,
>
> On Wed, 2008-11-05 at 10:34 -0500, Merlin Moncure wrote:
>> On Wed, Nov 5, 2008 at 2:41 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>> > Hi Everybody,
> [...]
>> > I've just upgraded to v8.3.4 ... since eventually it does have
>> > INSERT ... RETURNING extention to the SQL standard.
>> >
>> > The documentation was quite encouraging: "if INSERT contains RETURNING,
>> > the result is similar to SELECT....".
> [....]
>> This is a (very) faq.
>> Everyone wants it.
>
> I see. Sounds promising :)
>
>> It's more complicated than it looks (triggers).
>
> Could you give me pointers where I could get some more information on
> this problems? (simple postgres-doc-search and google queries on the
> subject didn't gave any interesting results).

see:
http://markmail.org/message/yst7y3bjqbso7i33

Tom Lane wrote:
"Making [returning work as select] happen would imply a whole lot of
other changes; this issue
isn't the principal gating factor.  One of the main things I'd point to
right now, in view of this having all arisen from the question of when
triggers should fire, is where and when we'd fire BEFORE/AFTER STATEMENT
triggers for a RETURNING command embedded in a larger query.  For that
matter, the system has several not-easily-removed assumptions that a
SELECT command won't fire any triggers at all --- which would break down
if we allowed constructs like

       SELECT ... FROM (INSERT ... RETURNING ...) ...

We do currently have the ability to make plpgsql functions send
RETURNING results back to a calling query, and with this change we could
say the same of plain SQL functions --- and in both cases we'll be
depending on a tuplestore buffer to keep things sane in terms of when
triggers fire."

Re: INSERT .... RETURNING

From
Rafal Pietrak
Date:

On Wed, 2008-11-05 at 14:38 -0500, Merlin Moncure wrote:
[....]
> >
> >> It's more complicated than it looks (triggers).
> >
> > Could you give me pointers where I could get some more information on
[....]
> matter, the system has several not-easily-removed assumptions that a
> SELECT command won't fire any triggers at all --- which would break down
> if we allowed constructs like
>
>        SELECT ... FROM (INSERT ... RETURNING ...) ...
>

I can only say I wish I could be of any help here .... but database
coding is way out of my league.

The "SELECT ... FROM (INSERT ..." seam so close to "INSERT ...
RETURNING...", its hard to believe it's so far away.

One comment I'd like to make as total lamer on the subject, is that the
assumption on SELECT (that it's not firing triggers), could potentially
be resolved by a *global* or "database" configuration option - once
selected, the SQL programmers' responsibility would be: not to assume
that on SELECT at the application layer. Otherwise (for legacy
applications) it may be disabled like it happened in the past, for other
"not so standard" (or should I rather say, new and untested by wider
audience) postgress features.

But I understand, 8.4 gets us a little closer to the goal with SQL
functions able to return what SELECT cannot.

Thenx for the pointer, the reading was enlightening.

-R


Re: INSERT .... RETURNING

From
"Dennis Brakhane"
Date:
On Thu, Nov 6, 2008 at 8:49 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
> One comment I'd like to make as total lamer on the subject, is that the
> assumption on SELECT (that it's not firing triggers), could potentially
> be resolved by a *global* or "database" configuration option - once
> selected, the SQL programmers' responsibility would be: not to assume
> that on SELECT at the application layer.

I think Tom meant that PostgreSQL's code assumes that select fires no triggers.
Hence, triggers (and probably constraints) wouldn't fire if you did a
select (insert ... returning).
So your setting would basically mean "make triggers fire some of the
time, and I don't
care about data consistency either". I doubt any sane person would
activate it ;)