From: | Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | INSERT ... VALUES... with ORDER BY / LIMIT |
Date: | 2010-10-01 09:52:15 |
Message-ID: | AANLkTinYG0Ud9OtZQmUATgoHrM9WnZ4gdwuZQqJFv8-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
While tackling the top-level CTEs patch, I found that INSERT ...
VALUES isn't aware of ORDER BY / LIMIT.
regression=# CREATE TABLE t1(x int);
CREATE TABLE
regression=# INSERT INTO t1 VALUES (1),(2),(3) LIMIT 1;
INSERT 0 3
regression=# TABLE t1;
x
---
1
2
3
(3 rows)
regression=# TRUNCATE t1;
TRUNCATE TABLE
regression=# INSERT INTO t1 VALUES (1),(2),(3) ORDER BY 2;
INSERT 0 3
regression=# TABLE t1;
x
---
1
2
3
(3 rows)
Is it intentional, or a bug?
This behavior gives me a thought that current INSERT ... VALUES should
be refactored. Query(INSERT)->rtable has a RangeTblEntry(RTE_VALUES)
as the representation of VALUES, but to be consistent with the syntax,
rtable should have RangeTblEntry(RTE_SUBSELECT), whose rtable member
holds RangeTblEntry(RTE_VALUES) as the normal SELECT does. I see
there's some reason here why INSERT case doesn't handle it as the
normal SELECT does, and I don't see how big this change affect to
around rewriter and planner. On the other hand, if INSERT ... VALUES
doesn't care about ORDER BY / LIMIT, it should ignore WITH clause as
well. Ignoring WITH breaks the current behavior, but the use case is
quite narrow and I bet no one relies on it, so we might be able to
break it.
Regards,
--
Hitoshi Harada
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2010-10-01 10:48:25 | Re: is sync rep stalled? |
Previous Message | Fujii Masao | 2010-10-01 09:47:45 | Re: recovery.conf location |