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

Поиск
Список
Период
Сортировка
От Joe Van Dyk
Тема Re: plpgsql plan caching allowing invalid data to enter table?
Дата
Msg-id CACfv+pLSzFaPvGPt-H92yVUCgvs4E+77tv6BBwNHq_61hZFS9g@mail.gmail.com
обсуждение исходный текст
Ответ на plpgsql plan caching allowing invalid data to enter table?  (Joe Van Dyk <joe@tanga.com>)
Список pgsql-general
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; 
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Joe Van Dyk
Дата:
Сообщение: plpgsql plan caching allowing invalid data to enter table?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: plpgsql plan caching allowing invalid data to enter table?