Re: comportamiento transaccional WITH

Hola lista

Sobre este tema no hay comentarios? Lo estoy haciendo mal o interpretando
erróneamente? Escucho al menos sus críticas

El 10 ago. 2017 2:49 PM, "Hellmuth Vargas" <hivs77(at)gmail(dot)com> escribió:

>
>
> Un caso adicional
>
> 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=# with elimina as (
> test(# delete from padre where id%2=0 returning id,texto
> test(# )select * from padre; -- trae todos
> 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 padre; -- trae solo los impares
> id | texto
> -----+--------
> 33 | !
> 35 | #
> 37 | %
> 39 | '
> 41 | )
> 43 | +
> 45 | -
> 47 | /
> 49 | 1
> 51 | 3
> 53 | 5
> 55 | 7
> 57 | 9
> 59 | ;
> 61 | =
> 63 | ?
> 65 | A
> 67 | C
> 69 | E
> 71 | G
> 73 | I
> 75 | K
> 77 | M
> 79 | O
> test=# rollback;
> ROLLBACK
> test=#
>
> 2017-08-10 14:33 GMT-05:00 Hellmuth Vargas <hivs77(at)gmail(dot)com>:
>
>>
>> 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
>>
>>
>
>
> --
> Cordialmente,
>
> Ing. Hellmuth I. Vargas S.
> Esp. Telemática y Negocios por Internet
> Oracle Database 10g Administrator Certified Associate
> EnterpriseDB Certified PostgreSQL 9.3 Associate
>
>