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 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.
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
From a4d331f2be74ad4e0c9a30f3984c2988aa541c3d Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
