Strange behavior of insert CTE with trigger

Поиск

Список

Период

Сортировка

От	Anil Menon
Тема	Strange behavior of insert CTE with trigger
Дата	1 апреля 2015 г. 07:28:11
Msg-id	CAHzbRKf3fXdOeway0yQ5+XJz3vObe_T6C=TYMdh6tFw33jUxcA@mail.gmail.com обсуждение исходный текст
Ответы	Re: Strange behavior of insert CTE with trigger ("David G. Johnston" <david.g.johnston@gmail.com>)
Список	pgsql-general

Дерево обсуждения

Hi,

I am trying to wrap my head around a strange problem I am having. I have double checked the documentation but I could not find anything on this.

I am attaching a simplified version of my problem. I my TEST 4 I expect 1 row but I get nothing. The test is

with I(id) as (
insert into abc(colData) values (2) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id

Here the Insert causes an trigger to be executed. The trigger inserts a record into the table abc_Excp_log. I combine the returned serial key of the insert with the table abc_Excp_log.

However I get no rows returned from the select statement- looks the insert to abc_Excp_log is executed *after* the select statement or some sort of race condition is executed.

Is this documented anywhere and is the expected behavior? Documented anywhere? The CTE part of the PG doc does not say anything on this.

Currently using ver PG 9.3 on Windows 8.1

Thanks in advance,

create table abc (
colPK serial not null primary key,
colData int not null
);

create table abc_ins_log(
colPK int not null primary key,
starttime timestamp with time zone,
endtime timestamp with time zone
);

create table abc_excp_log(
colPK int not null primary key,
msgtxt text,
msg_context text,
msg_hint text,
msg_sqlstate text,
starttime timestamp with time zone,
endtime timestamp with time zone
);

create or replace function InsertABC( vColData int) returns boolean as
$$
begin
   if vcoldata <=5 then
    raise exception 'Column data value is less than or equal to 5';
    return false; --I know I know
   --else
    --do big business process
   end if;
   return true;
end;
$$ language plpgsql;

create or replace function ABCInsertLog() returns trigger as
$$
declare
vstarttime timestamp with time zone;
verrmsg1 text;
verrmsg2 text;
verrmsg3 text;
verrmsg4 text;
begin
   select now() into vstarttime;
   perform InsertABC(NEW.colData);
   insert into abc_ins_log(colPK, starttime, endtime)
   values(NEW.colPK, vstarttime, now());
   return NEW;
   exception when others then
   GET STACKED DIAGNOSTICS verrmsg1 = MESSAGE_TEXT, verrmsg2 = PG_EXCEPTION_CONTEXT,verrmsg3=PG_EXCEPTION_HINT, verrmsg4=RETURNED_SQLSTATE;
    insert into abc_excp_log(colPK, msgtxt, msg_context, msg_hint, msg_sqlstate, starttime, endtime)
    values(new.colPK, verrmsg1, verrmsg2, verrmsg3, verrmsg4,vstarttime, now());
   return NEW;
end;
$$ language plpgsql;

CREATE TRIGGER abc_tx_tr
AFTER INSERT
ON abc
FOR EACH ROW
EXECUTE PROCEDURE ABCInsertLog ();

--SANITY Check

select * from abc; --nothing
select * From abc_ins_log; --nothing
select * from abc_excp_log; --nothing

--TEST 1 : check if working: test case no errors
insert into abc(colData) values (10); --Query returned successfully: one row affected, 63 ms execution time.

select * from abc; --1 row
select * From abc_ins_log; --1 row
select * from abc_excp_log; --nothing

--TEST 2 : cause exception
insert into abc(colData) values (3); --Query returned successfully: one row affected, 42 ms execution time.
select * from abc; --2 rows
select * From abc_ins_log; --1 row
select * from abc_excp_log; --1 row
--2;"Column data value is less than or equal to 5";"SQL statement "SELECT InsertABC(NEW.colData)"
--PL/pgSQL function abcinsertlog() line 10 at PERFORM";"";"P0001";"2015-03-28 06:42:56.187+08";"2015-03-28 06:42:56.187+08"

--TEST 3: test with CTE : test success case
with I(id) as (
insert into abc(colData) values (10) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id
--expected values : nothing
-- got : nothing
select * from abc; --3 rows
select * From abc_ins_log; --2 rows
select * from abc_excp_log; --1 row

--TEST 4 : test with CTE : test failure case
with I(id) as (
insert into abc(colData) values (2) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id
--expected values : 1 row
-- got : nothing
select * from abc; --4 rows
select * From abc_ins_log; --2 rows
select * from abc_excp_log; --2 rows

--TEST 5 : to test "normal" inserts with CTE
with I(id) as (
insert into abc(colData) values (2) returning colPK
)
select id
from I
--expected value : 1 row
--got : 1 row (value 5)
---

--TEST 6 : test with CTE : sanity check
with I(id) as (
insert into abc(colData) values (10) returning colPK
)
select *
from abc, abc_ins_log , I
where abc.colPK=abc_ins_log.colPK
and abc.colPK=I.id
--expected values : 1 row
-- got : nothing
select * from abc; --6 rows
select * From abc_ins_log; --3 rows
select * from abc_excp_log; --3 row

В списке pgsql-general по дате отправления:

Предыдущее

От: Timothy Garnett
Дата: 01 апреля 2015 г., 07:27:56
Сообщение: Creating a non-strict custom aggregate that initializes to the first value

Следующее

От: Rebecca Zahra
Дата: 01 апреля 2015 г., 07:28:21
Сообщение: Re: GiST indeices on range types

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Strange behavior of insert CTE with trigger

Предыдущее

Следующее