From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Bruno Wolff III <bruno@wolff.to>
To: pgsql-bugs@postgresql.org
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [BUGS] Beta 10 parser error for CREATE STATISTICS IF NOT EXISTS
Date: Wed, 21 Jun 2017 10:55:28 +0900
Message-ID: <38b7d52e-387a-0e47-7525-b8b654ca4bfb@lab.ntt.co.jp> (raw)
In-Reply-To: <73b2a51a-3cf9-a44c-4cd0-bbc611016eb4@lab.ntt.co.jp>
References: <20170621004237.GB8337@wolff.to>
<73b2a51a-3cf9-a44c-4cd0-bbc611016eb4@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:0001-Fix-the-syntax-of-IF-NOT-EXISTS-variant-of-CREATE-ST.patch (text/plain, 3.2K) download
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.yindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, bruno@wolff.to, pgsql-hackers@postgresql.org
Subject: Re: [BUGS] Beta 10 parser error for CREATE STATISTICS IF NOT EXISTS
In-Reply-To: <38b7d52e-387a-0e47-7525-b8b654ca4bfb@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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