Re: Using results from INSERT ... RETURNING

On Tue, Jul 7, 2009 at 3:31 PM, Marko
Tiikkaja<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> Hello.
>
> Here's a patch(WIP) that implements INSERT .. RETURNING inside a CTE. Should
> apply cleanly against CVS head.
>
> The INSERT query isn't rewritten so rules and default values don't work.
> Recursive CTEs don't work either.
>

my questions first:
- what's the use case for this?
- why you need a node InsertReturning (see nodeInsertReturning.c) at all?
- if we will support this, shouldn't we supporting INSERT RETURNING
inside subqueries too?

and it crashes for triggers (example using regression's int4_tbl)

create function trg_int4_tbl() returns trigger as $$
begin
raise notice 'ejecutando';
return new;
end;
$$ language plpgsql;

create trigger trig_int4_tbl before insert on int4_tbl for each row
execute procedure trg_int4_tbl();

with
q as (insert into int4_tbl select generate_series(1, 5) returning *)
select * from q;
NOTICE: ejecutando
LOG: server process (PID 20356) was terminated by signal 11: Segmentation fault
LOG: terminating any other active server processes
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: FATAL: the
database system is in recovery mode
Failed.
!> LOG: all server processes terminated; reinitializing

and for defaults (even if i'm providing the values, actually is worse
in that case)

CREATE TABLE t(i SERIAL PRIMARY KEY);

with
t1 as (insert into t values (default), (default), (default)
returning 'INSERT', i)
select * from t1;
ERROR: unrecognized node type: 337

with
t1 as (insert into t values (1), (2), (3) returning 'INSERT', i)
select * from t1;
LOG: server process (PID 21604) was terminated by signal 11: Segmentation fault
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2009-07-18 15:29:28 ECT
LOG: database system was not properly shut down; automatic recovery in progress
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: FATAL: the
database system is in recovery mode
Failed.
!> LOG: redo starts at 0/32A0310

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157