Re: Using results from INSERT ... RETURNING

On Tue, Jul 7, 2009 at 3:31 PM, Marko
Tiikkaja<marko.tiikkaja@cs.helsinki.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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing
therequest. 
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 unexpectedlyThis probably means the server terminated abnormallybefore or while processing
therequest. 
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