comportamiento transaccional WITH

Hola Lista

Estaba revisando porque unas sentencias se ejecutaban y otras no con WITH y
prepare este script para que ustedes me ayuden a entender:

test=# create table padre(id int primary key, texto text);
CREATE TABLE
test=# create table hijo(id int primary key, texto text, padre_id int
references padre(id));
CREATE TABLE
test=# begin transaction;
BEGIN
test=# with base as(
test(# insert into padre(id,texto)
test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato)
returning id,texto
test(# ),hijos as (
test(# insert into hijo(id, texto, padre_id)
test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from base
as a
test(# )
test-# select * from
test-# padre as a
test-# join hijo as b on a.id=b.padre_id;
id | texto | id | texto | padre_id
----+-------+----+-------+----------
(0 rows)

no sale nada pero si en la misma transacción ejecuto nuevamente el ultimo
select:

test=# select * from
test-# padre as a
test-# join hijo as b on a.id=b.padre_id;
id | texto | id | texto | padre_id
-----+--------+------+--------------------------+----------
33 | ! | -33 | el char de 33es: ! | 33
34 | " | -34 | el char de 34es: " | 34
35 | # | -35 | el char de 35es: # | 35
36 | $ | -36 | el char de 36es: $ | 36
37 | % | -37 | el char de 37es: % | 37
38 | & | -38 | el char de 38es: & | 38
39 | ' | -39 | el char de 39es: ' | 39
40 | ( | -40 | el char de 40es: ( | 40
41 | ) | -41 | el char de 41es: ) | 41
42 | * | -42 | el char de 42es: * | 42
43 | + | -43 | el char de 43es: + | 43
44 | , | -44 | el char de 44es: , | 44
45 | - | -45 | el char de 45es: - | 45
46 | . | -46 | el char de 46es: . | 46
47 | / | -47 | el char de 47es: / | 47
48 | 0 | -48 | el char de 48es: 0 | 48
49 | 1 | -49 | el char de 49es: 1 | 49
50 | 2 | -50 | el char de 50es: 2 | 50
51 | 3 | -51 | el char de 51es: 3 | 51
52 | 4 | -52 | el char de 52es: 4 | 52
53 | 5 | -53 | el char de 53es: 5 | 53
54 | 6 | -54 | el char de 54es: 6 | 54
55 | 7 | -55 | el char de 55es: 7 | 55
56 | 8 | -56 | el char de 56es: 8 | 56
test=# rollback;
ROLLBACK

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

test=# begin transaction;
BEGIN
test=# with base as(
test(# insert into padre(id,texto)
test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato)
returning id,texto
test(# ),hijos as (
test(# insert into hijo(id, texto, padre_id)
test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from
padre as a ---no inserta
test(# )
test-# select * from
test-# padre as a
test-# join hijo as b on a.id=b.padre_id;
id | texto | id | texto | padre_id
----+-------+----+-------+----------
(0 rows)

No inserto en el hijo.

test=# select * from
test-# padre as a
test-# join hijo as b on a.id=b.padre_id;
id | texto | id | texto | padre_id
----+-------+----+-------+----------
(0 rows)

test=# select * from padre;
id | texto
-----+--------
33 | !
34 | "
35 | #
36 | $
37 | %
38 | &
39 | '
40 | (
41 | )
42 | *
43 | +
44 | ,
45 | -
46 | .
47 | /
48 | 0
49 | 1
50 | 2
51 | 3
52 | 4
53 | 5
54 | 6
55 | 7
56 | 8
test=# select * from hijo;
id | texto | padre_id
----+-------+----------
(0 rows)

test=# rollback;
ROLLBACK

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

test=# begin transaction;
BEGIN
test=# insert into padre(id,texto)
test-# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato);
INSERT 0 223
test=#
test=# with elimina as (
test(# delete from padre where id%2=0 returning id,texto
test(# ),hijos as (
test(# insert into hijo(id, texto, padre_id)
test(# select -a.id,'el char de ' || a.id || 'es: ' || a.texto,a.id from
padre as a returning id, texto, padre_id
test(# )
test-# select * from
test-# hijos as b;
ERROR: insert or update on table "hijo" violates foreign key constraint
"hijo_padre_id_fkey"
DETAIL: Key (padre_id)=(34) is not present in table "padre".

ok aunque en hijos emplee la tabla padre.

test=#
test=# rollback;
ROLLBACK

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

test=# begin transaction;
BEGIN
test=# with base as(
test(# insert into padre(id,texto)
test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato)
returning id,texto
test(# ),hijos as (
test(# insert into hijo(id, texto, padre_id)
test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from
padre as a ---no inserta
test(# )
test-# select * from
test-# padre as a
test-# join hijo as b on a.id=b.padre_id;
id | texto | id | texto | padre_id
----+-------+----+-------+----------
(0 rows)

aquí no ve los registros de la tabla padre que se habían insertado en
base.....

test=# select * from hijo;
id | texto | padre_id
----+-------+----------
(0 rows)

test=# select * from padre;
id | texto
-----+--------
33 | !
34 | "
35 | #
36 | $
37 | %
38 | &
39 | '
40 | (
41 | )
42 | *
43 | +
44 | ,
45 | -
46 | .
47 | /
48 | 0
49 | 1
50 | 2
51 | 3
52 | 4
53 | 5
54 | 6
55 | 7
56 | 8
test=#

test=# rollback;
ROLLBACK
--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet