Обсуждение: plpgsql plan caching allowing invalid data to enter table?
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?
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, 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@tanga.com> 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;begininsert 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.
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.klaver@gmail.com
On Tue, Jul 9, 2013 at 4:29 PM, Adrian Klaver <adrian.klaver@gmail.com> 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.
Joe Van Dyk <joe@tanga.com> writes:
> 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?
I think it's not really plpgsql's fault but domain_in's --- there's no
provision for flushing the latter's cached info about how to check
domain constraints. (You can't see this in simple commands because
the cache only lives as long as the statement, but I think plpgsql
is letting it get put into the function's definitional memory context,
which will pretty much survive for the whole session if you don't
redefine the function.)
We could ameliorate this case and probably improve performance as well
by keeping domain check info in the typcache rather than using ad-hoc
storage for it.
However, I think it's a mistake to imagine that there's ever going to be
a bulletproof guarantee that you can whack domain constraints around
in a live database and not have any risk of some data going unchecked.
As a couple of examples:
* suppose you do the ALTER DOMAIN, and commit it at an instant where
the plpgsql function is actively executing and has a live variable
value of that domain type. Nothing is going to make the constraint
change apply retroactively to that variable.
* suppose you don't do an ALTER DOMAIN at all, but just change the
behavior of a function that's used in a check constraint.
regards, tom lane