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

[Joe Van Dyk <joe@xxxxxxxxx>]

As you can see, I have data in my_table that violates the check constraint.

# select * from my_table;

 name

──────

 test

(1 row)

# \d+ my_table

                        Table "public.my_table"

 Column │   Type    │ Modifiers │ Storage  │ Stats target │ Description

────────┼───────────┼───────────┼──────────┼──────────────┼─────────────

 name   │ my_domain │           │ extended │              │

Has OIDs: no

# \dD my_domain

                         List of domains

 Schema │   Name    │ Type │ Modifier │           Check

────────┼───────────┼──────┼──────────┼───────────────────────────

 public │ my_domain │ text │          │ CHECK (length(VALUE) > 5)

(1 row)

On Tue, Jul 9, 2013 at 4:05 PM, Joe Van Dyk <joe@xxxxxxxxx> 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.