On Tue, Jul 9, 2013 at 4:29 PM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote:
My guess this has more to do with MVCC. Session 1 and 2 are looking at different snapshots of the database and acting accordingly.On 07/09/2013 04:05 PM, Joe Van Dyk wrote:
It's looking like I can use a plpgsql function to insert data into a
table that violates a domain constraint. Is this a known problem?
Session 1:
create domain my_domain text check (length(value) > 2);
create table my_table (name my_domain);
create function f(text) returns void as $$
declare my_var my_domain := $1;
begin
insert into my_table values (my_var);
end $$ language plpgsql;
Session 2:
select f('test');
delete from my_table;
-- Keep session open!
Session 1:
alter domain my_domain drop constraint my_domain_check;
alter domain my_domain add constraint my_domain_check check
(length(value) > 5);
Session 2:
select f('test');
-- This works, but it should fail.
-- I have a constraint of more than 5 characters on the domain.
-- But I can insert a row with 4 characters.
Hm, I'd be surprised -- there's no multi-statement transactions used here. My guess is that the check constraint gets cached by the plpgsql function and there's no check of the constraint when the data is being inserted inside the function body.
In any event, I shouldn't be allowed to have data in a table that violates a check constraint.
