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

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Fwd: plpgsql plan caching allowing invalid data to enter table?
Date: 2013-07-09 23:21:20
Message-ID: CACfv+p+G_sgmAVymrBPKE8f3rA4X6+20FVGVH9vVq8a=knfaww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

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.

As you can see below, I have data in my_table that's violating the domain's
constraint of being longer than 5 characters.

# 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)

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Adrian Klaver 2013-07-09 23:29:06 Re: plpgsql plan caching allowing invalid data to enter table?
Previous Message Joe Van Dyk 2013-07-09 23:12:01 Re: plpgsql plan caching allowing invalid data to enter table?

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-07-09 23:29:06 Re: plpgsql plan caching allowing invalid data to enter table?
Previous Message Joe Van Dyk 2013-07-09 23:12:01 Re: plpgsql plan caching allowing invalid data to enter table?