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,
AK
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
