pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18812: Conditional rule: inconsistent check for statement
3+ messages / 3 participants
[nested] [flat]
* BUG #18812: Conditional rule: inconsistent check for statement
@ 2025-02-14 21:00 PG Bug reporting form <noreply@postgresql.org>
2025-02-14 22:55 ` Re: BUG #18812: Conditional rule: inconsistent check for statement Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-02-14 21:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: drtr0jan@yandex.ru
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18812: Conditional rule: inconsistent check for statement
2025-02-14 21:00 BUG #18812: Conditional rule: inconsistent check for statement PG Bug reporting form <noreply@postgresql.org>
@ 2025-02-14 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-17 10:58 ` Re: BUG #18812: Conditional rule: inconsistent check for statement Boris P. Korzun <drtr0jan@yandex.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-02-14 22:55 UTC (permalink / raw)
To: drtr0jan@yandex.ru; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18812: Conditional rule: inconsistent check for statement
2025-02-14 21:00 BUG #18812: Conditional rule: inconsistent check for statement PG Bug reporting form <noreply@postgresql.org>
2025-02-14 22:55 ` Re: BUG #18812: Conditional rule: inconsistent check for statement Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-02-17 10:58 ` Boris P. Korzun <drtr0jan@yandex.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Boris P. Korzun @ 2025-02-17 10:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-02-17 10:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-14 21:00 BUG #18812: Conditional rule: inconsistent check for statement PG Bug reporting form <noreply@postgresql.org>
2025-02-14 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-17 10:58 ` Boris P. Korzun <drtr0jan@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox