From: | Joe Van Dyk <joe(at)tanga(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: plpgsql plan caching allowing invalid data to enter table? |
Date: | 2013-07-09 23:12:01 |
Message-ID: | CACfv+pLSzFaPvGPt-H92yVUCgvs4E+77tv6BBwNHq_61hZFS9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs 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(at)tanga(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Van Dyk | 2013-07-09 23:21:20 | Fwd: plpgsql plan caching allowing invalid data to enter table? |
Previous Message | Joe Van Dyk | 2013-07-09 23:05:27 | plpgsql plan caching allowing invalid data to enter table? |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Van Dyk | 2013-07-09 23:21:20 | Fwd: plpgsql plan caching allowing invalid data to enter table? |
Previous Message | Joe Van Dyk | 2013-07-09 23:05:27 | plpgsql plan caching allowing invalid data to enter table? |