Beta 10 parser error for CREATE STATISTICS IF NOT EXISTS
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS
IF NOT EXISTS. Given this is new, maybe there is a bug in the parser.
Sample output:
psql (10beta1)
Type "help" for help.
o365logs=# select version();
version
--------------------------------------------------------------------------------
----------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-11), 64-bit
(1 row)
o365logs=# CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies) ON record_type, operation FROM logs;
ERROR: syntax error at or near "NOT"
LINE 1: CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies)...
^
o365logs=# CREATE STATISTICS logs_corrtest (dependencies) ON record_type, operation FROM logs;
CREATE STATISTICS
o365logs=# DROP STATISTICS IF EXISTS logs_corrtest;
DROP STATISTICS
o365logs=#
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017/06/21 9:42, Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.Sample output:
psql (10beta1)
Type "help" for help.o365logs=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623
(Red Hat 4.8.5-11), 64-bit
(1 row)o365logs=# CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies) ON
record_type, operation FROM logs;
ERROR: syntax error at or near "NOT"
LINE 1: CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies)...
Looks like a documentation bug if the authors of the feature actually
meant to implement the following syntax:
CREATE [ IF NOT EXISTS ] STATISTICS
create if not exists statistics words_stats on a, b from words;
CREATE STATISTICS
create if not exists statistics words_stats on a, b from words;
NOTICE: statistics object "words_stats" already exists, skipping
CREATE STATISTICS
If that's really what's intended, it seems a bit inconsistent with most
other commands and with DROP STATISTICS [ IF NOT EXISTS ] itself.
Thanks,
Amit
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017/06/21 10:15, Amit Langote wrote:
On 2017/06/21 9:42, Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.Sample output:
psql (10beta1)
Type "help" for help.o365logs=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623
(Red Hat 4.8.5-11), 64-bit
(1 row)o365logs=# CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies) ON
record_type, operation FROM logs;
ERROR: syntax error at or near "NOT"
LINE 1: CREATE STATISTICS IF NOT EXISTS logs_corrtest (dependencies)...Looks like a documentation bug if the authors of the feature actually
meant to implement the following syntax:CREATE [ IF NOT EXISTS ] STATISTICS
create if not exists statistics words_stats on a, b from words;
CREATE STATISTICScreate if not exists statistics words_stats on a, b from words;
NOTICE: statistics object "words_stats" already exists, skipping
CREATE STATISTICSIf that's really what's intended, it seems a bit inconsistent with most
other commands and with DROP STATISTICS [ IF NOT EXISTS ] itself.
Here is a patch, just in case, that changes the grammar to accept the
following syntax instead of the current one:
CREATE STATISTICS [ IF NOT EXIST ] ...
Also added a test. Documentation already displays the above syntax, so no
update needed there.
Thanks,
Amit
Attachments:
0001-Fix-the-syntax-of-IF-NOT-EXISTS-variant-of-CREATE-ST.patchtext/plain; charset=UTF-8; name=0001-Fix-the-syntax-of-IF-NOT-EXISTS-variant-of-CREATE-ST.patchDownload
From a4d331f2be74ad4e0c9a30f3984c2988aa541c3d Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Wed, 21 Jun 2017 10:47:06 +0900
Subject: [PATCH] Fix the syntax of IF NOT EXISTS variant of CREATE STATISTICS
The norm seems to be CREATE <object> IF NOT EXISTS, not
CREATE IF NOT EXISTS <object>, which the original code implemented.
---
src/backend/parser/gram.y | 23 +++++++++++++++++------
src/test/regress/expected/stats_ext.out | 5 +++++
src/test/regress/sql/stats_ext.sql | 5 +++++
3 files changed, 27 insertions(+), 6 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ada95e5bc3..34e07c87a2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3846,15 +3846,26 @@ ExistingIndex: USING INDEX index_name { $$ = $3; }
*****************************************************************************/
CreateStatsStmt:
- CREATE opt_if_not_exists STATISTICS any_name
+ CREATE STATISTICS any_name
opt_name_list ON expr_list FROM from_list
{
CreateStatsStmt *n = makeNode(CreateStatsStmt);
- n->defnames = $4;
- n->stat_types = $5;
- n->exprs = $7;
- n->relations = $9;
- n->if_not_exists = $2;
+ n->defnames = $3;
+ n->stat_types = $4;
+ n->exprs = $6;
+ n->relations = $8;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE STATISTICS IF_P NOT EXISTS any_name
+ opt_name_list ON expr_list FROM from_list
+ {
+ CreateStatsStmt *n = makeNode(CreateStatsStmt);
+ n->defnames = $6;
+ n->stat_types = $7;
+ n->exprs = $9;
+ n->relations = $11;
+ n->if_not_exists = true;
$$ = (Node *)n;
}
;
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 5fd1244bcb..97551e9df6 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -34,6 +34,11 @@ ERROR: unrecognized statistic type "unrecognized"
CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
+-- Check the IF NOT EXISTS syntax
+CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
+CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
+NOTICE: statistics object "ab1_a_b_stats" already exists, skipping
+DROP STATISTICS ab1_a_b_stats;
CREATE SCHEMA regress_schema_2;
CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
-- Let's also verify the pg_get_statisticsobjdef output looks sane.
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 4c2f514b93..8ecfd9a0f9 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -23,6 +23,11 @@ CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
CREATE STATISTICS ab1_a_b_stats ON a, b FROM ab1;
DROP STATISTICS ab1_a_b_stats;
+-- Check the IF NOT EXISTS syntax
+CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
+CREATE STATISTICS IF NOT EXISTS ab1_a_b_stats ON a, b FROM ab1;
+DROP STATISTICS ab1_a_b_stats;
+
CREATE SCHEMA regress_schema_2;
CREATE STATISTICS regress_schema_2.ab1_a_b_stats ON a, b FROM ab1;
--
2.11.0
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
On 2017/06/21 9:42, Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.
Looks like a documentation bug if the authors of the feature actually
meant to implement the following syntax:
CREATE [ IF NOT EXISTS ] STATISTICS
Hm, that is what the grammar supports, but surely it's utterly
inconsistent with every other usage of IF NOT EXISTS. Even if
this was intended and the docs were not, we should fix the grammar
to match the docs not vice versa.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.
You're absolutely right, and this is a bug in the parser -- I probably
misplaced the IF NOT EXISTS clause while playing with Tomas' parser
changes. Here's the fix. I'm a bit troubled that this change doesn't
seem to affect any tests, so I'll add some before pushing.
(Now, "statistics" being plural would seem to call for CREATE STATISTICS
IF NOT EXIST, rather than EXISTS, but I'll put that thought aside on
account of it being just too weird ...)
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-fix-create-stats-grammar.patchtext/plain; charset=us-asciiDownload
From d0a11533f83b366e2cdc8537fc511f3f22ddb35a Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 20 Jun 2017 21:54:23 -0400
Subject: [PATCH] fix create stats grammar
---
src/backend/parser/gram.y | 25 ++++++++++++++++++-------
1 file changed, 18 insertions(+), 7 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ada95e5bc3..0f3998ff89 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3834,7 +3834,7 @@ ExistingIndex: USING INDEX index_name { $$ = $3; }
/*****************************************************************************
*
* QUERY :
- * CREATE STATISTICS stats_name [(stat types)]
+ * CREATE STATISTICS [IF NOT EXISTS] stats_name [(stat types)]
* ON expression-list FROM from_list
*
* Note: the expectation here is that the clauses after ON are a subset of
@@ -3846,15 +3846,26 @@ ExistingIndex: USING INDEX index_name { $$ = $3; }
*****************************************************************************/
CreateStatsStmt:
- CREATE opt_if_not_exists STATISTICS any_name
+ CREATE STATISTICS any_name
opt_name_list ON expr_list FROM from_list
{
CreateStatsStmt *n = makeNode(CreateStatsStmt);
- n->defnames = $4;
- n->stat_types = $5;
- n->exprs = $7;
- n->relations = $9;
- n->if_not_exists = $2;
+ n->defnames = $3;
+ n->stat_types = $4;
+ n->exprs = $6;
+ n->relations = $8;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE STATISTICS IF_P NOT EXISTS any_name
+ opt_name_list ON expr_list FROM from_list
+ {
+ CreateStatsStmt *n = makeNode(CreateStatsStmt);
+ n->defnames = $6;
+ n->stat_types = $7;
+ n->exprs = $9;
+ n->relations = $11;
+ n->if_not_exists = true;
$$ = (Node *)n;
}
;
--
2.11.0
On 2017/06/21 11:02, Alvaro Herrera wrote:
Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.You're absolutely right, and this is a bug in the parser -- I probably
misplaced the IF NOT EXISTS clause while playing with Tomas' parser
changes. Here's the fix. I'm a bit troubled that this change doesn't
seem to affect any tests, so I'll add some before pushing.
I posted the same patch but with a test:
/messages/by-id/38b7d52e-387a-0e47-7525-b8b654ca4bfb@lab.ntt.co.jp
Forgot to cc you or Tomas.
Thanks,
Amit
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Amit Langote wrote:
On 2017/06/21 11:02, Alvaro Herrera wrote:
Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.You're absolutely right, and this is a bug in the parser -- I probably
misplaced the IF NOT EXISTS clause while playing with Tomas' parser
changes. Here's the fix. I'm a bit troubled that this change doesn't
seem to affect any tests, so I'll add some before pushing.I posted the same patch but with a test:
/messages/by-id/38b7d52e-387a-0e47-7525-b8b654ca4bfb@lab.ntt.co.jp
Pushed.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017/06/23 2:22, Alvaro Herrera wrote:
Amit Langote wrote:
On 2017/06/21 11:02, Alvaro Herrera wrote:
Bruno Wolff III wrote:
I'm not seeing an obvious error in my attempt to use CREATE STATISTICS IF
NOT EXISTS. Given this is new, maybe there is a bug in the parser.You're absolutely right, and this is a bug in the parser -- I probably
misplaced the IF NOT EXISTS clause while playing with Tomas' parser
changes. Here's the fix. I'm a bit troubled that this change doesn't
seem to affect any tests, so I'll add some before pushing.I posted the same patch but with a test:
/messages/by-id/38b7d52e-387a-0e47-7525-b8b654ca4bfb@lab.ntt.co.jp
Pushed.
Thanks, Alvaro.
Regards,
Amit
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs