Thread: BUG #18812: Conditional rule: inconsistent check for statement

The following bug has been logged on the website:

Bug reference:      18812
Logged by:          Boris Korzun
Email address:      drtr0jan@yandex.ru
PostgreSQL version: 17.2
Operating system:   FreeBSD 14-stable
Description:

Conditional rule checks underlying table for the types ignoring the
statement.

-----
CREATE TABLE t
(
    c varchar(10) NOT NULL
);

CREATE VIEW v AS
SELECT NULL::text AS c
FROM t;

CREATE RULE "insert" AS ON INSERT TO v
    WHERE FALSE
    DO INSTEAD
    INSERT INTO t (c)
    VALUES (new.c);

CREATE RULE "skip" AS ON INSERT TO v DO INSTEAD NOTHING;
-----

I've two rules for a view - unconditional INSTEAD (skip) and conditional
INSTEAD (always FALSE). But if I trying to insert a type mismatched data to
the view, I've got a type constraint error.

-----
INSERT INTO v (c) VALUES ('testtesttest');
-----
[22001] ERROR: value too long for type character varying(10)
-----

Why? It seems like a bug.

PG Bug reporting form <noreply@postgresql.org> writes:
> I've two rules for a view - unconditional INSTEAD (skip) and conditional
> INSTEAD (always FALSE). But if I trying to insert a type mismatched data to
> the view, I've got a type constraint error.

[ shrug... ]  The WHERE FALSE condition is evaluated later than it
would need to be to prevent this error.  If we use a value that
doesn't trigger the error:

=# explain verbose INSERT INTO v (c) VALUES ('testtest');
                      QUERY PLAN                      
------------------------------------------------------
 Insert on public.t  (cost=0.00..0.01 rows=0 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=14)
         Output: 'testtest'::character varying(10)
         One-Time Filter: false
(4 rows)

we can see that the "false" is actually applied at runtime, but the
value coercion happened during planner constant-folding.  In general
the order of application of WHERE clauses is not guaranteed, so
there's not a good argument that this outcome is wrong.

We get variants of this complaint from time to time, but few of
them present use-cases that seem compelling enough to justify the
performance costs of not doing constant-folding.

            regards, tom lane

Hi Tom,

thanks for the fastest and exhaustive answer!

On 15/02/2025 01:55, Tom Lane wrote:
> We get variants of this complaint from time to time, but few of
> them present use-cases that seem compelling enough to justify the
> performance costs of not doing constant-folding.

I think it's the right decision! But...

On 15/02/2025 01:55, Tom Lane wrote:
 > PG Bug reporting form <noreply@postgresql.org> writes:
 >> I've two rules for a view - unconditional INSTEAD (skip) and conditional
 >> INSTEAD (always FALSE). But if I trying to insert a type mismatched 
data to
 >> the view, I've got a type constraint error.
 >
 > [ shrug... ]  The WHERE FALSE condition is evaluated later than it
 > would need to be to prevent this error.  If we use a value that
 > doesn't trigger the error:
 >
 > =# explain verbose INSERT INTO v (c) VALUES ('testtest');
 >                        QUERY PLAN
 > ------------------------------------------------------
 >   Insert on public.t  (cost=0.00..0.01 rows=0 width=0)
 >     ->  Result  (cost=0.00..0.01 rows=1 width=14)
 >           Output: 'testtest'::character varying(10)
 >           One-Time Filter: false
 > (4 rows)
 >
 > we can see that the "false" is actually applied at runtime, but the
 > value coercion happened during planner constant-folding.  In general
 > the order of application of WHERE clauses is not guaranteed, so
 > there's not a good argument that this outcome is wrong.

What do you think about adding the behavior described above (undefined 
behavior, generally) to the help?

---
WBR
Boris