Re: plpgsql plan caching allowing invalid data to enter table?

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.

My guess this has more to do with MVCC. Session 1 and 2 are looking at
different snapshots of the database and acting accordingly.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com