[pgpool-hackers: 3263] Re: [pgpool-committers: 5093] pgpool: Import PostgreSQL 11 beta3 parser.

[Yugo Nagata]

On Thu, 28 Feb 2019 14:24:48 +0900
Bo Peng <pengbo at sraoss.co.jp> wrote:

> Hi,
> 
> On Tue, 26 Feb 2019 18:02:59 +0900
> Yugo Nagata <nagata at sraoss.co.jp> wrote:
> 
> > Hi Peng,
> > 
> > I found the import of PG11 parser is insufficient so that the
> > native replication mode can not rewrite queries including GROUPS
> > in frame clauses.
> > https://www.postgresql.org/docs/11/sql-select.html
> 
> Thank you.
> 
> I fixed this issue. Patch is attached. 

Great!

BTW, I think codes for EXCLUDE claus is also necessary.

For example:

test=# select now(), sum(i) over (order by i rows between 1 preceding and 1 following EXCLUDE CURRENT ROW) from test_tbl;
              now              | sum 
-------------------------------+-----
 2019-02-28 20:44:48.746597+09 |   2
 2019-02-28 20:44:48.746597+09 |   4
 2019-02-28 20:44:48.746597+09 |   2
(3 rows)

test=# insert into result select now(), sum(i) over (order by i rows between 1 preceding and 1 following EXCLUDE CURRENT ROW) from test_tbl returning result;
              result              
----------------------------------
 ("2019-02-28 20:45:09.754981",3)
 ("2019-02-28 20:45:09.754981",6)
 ("2019-02-28 20:45:09.754981",5)
(3 rows)

INSERT 0 3

Note that the result of sum(i) is different. According to the log, the statement
issued was as below and the EXCLUDE clause was omitted.

INSERT INTO "result" SELECT "pg_catalog"."timestamptz"('2019-02-28　20:45:03.846093+09'::text),"sum"("i") OVER ( ORDER BY "i" ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM "test_tbl" RETURNING "result"

Regards,

> 
> > For example,
> > 
> > test=# \d test_tbl;
> >               Table "public.test_tbl"
> >  Column |  Type   | Collation | Nullable | Default 
> > --------+---------+-----------+----------+---------
> >  i      | integer |           |          | 
> > 
> > test=# \d result 
> >                          Table "public.result"
> >  Column |            Type             | Collation | Nullable | Default 
> > --------+-----------------------------+-----------+----------+---------
> >  t      | timestamp without time zone |           |          | 
> >  sum    | integer                     |           |          | 
> > 
> > test=#  select now(), sum(i) over (order by i groups between 1 preceding and 1 following) from test_tbl;
> >               now              | sum 
> > -------------------------------+-----
> >  2019-02-26 17:50:40.262925+09 |   3
> >  2019-02-26 17:50:40.262925+09 |   6
> >  2019-02-26 17:50:40.262925+09 |   5
> > (3 rows)
> > 
> > test=# insert into result select now(), sum(i) over (order by i groups between 1 preceding and 1 following) from test_tbl;
> > ERROR:  syntax error at or near "PRECEDING"
> > 
> > According to the log, this query is rewritten as following:
> > 
> > INSERT INTO "result" SELECT "pg_catalog"."timestamptz"('2019-02-26 17:51:00.749663+09'::text),
> >  "sum"("i") OVER ( ORDER BY "i" BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM "test_tbl"
> > 
> > As you see, GROUPS is omitted in the rewritten query because the codes to handle this 
> > is missing in outfuncs.c. Similarly, I guess EXCLUDE in frame clauses is not properly
> > handled, neither.
> > 
> > Regards,
> > 
> > On Tue, 28 Aug 2018 09:21:02 +0000
> > Bo Peng <pengbo at sraoss.co.jp> wrote:
> > 
> > > Import PostgreSQL 11 beta3 parser.
> > > 
> > > Branch
> > > ------
> > > master
> > > 
> > > Details
> > > -------
> > > https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=8b43ac3b956022447b41b744baf2260dca20bba1
> > > 
> > > Modified Files
> > > --------------
> > > src/include/parser/gramparse.h        |    4 +-
> > > src/include/parser/keywords.h         |    4 +-
> > > src/include/parser/kwlist.h           |   12 +-
> > > src/include/parser/makefuncs.h        |    6 +-
> > > src/include/parser/nodes.h            |   16 +-
> > > src/include/parser/parsenodes.h       |  182 ++--
> > > src/include/parser/parser.h           |    8 +-
> > > src/include/parser/pg_class.h         |   84 +-
> > > src/include/parser/pg_config_manual.h |   20 +-
> > > src/include/parser/pg_list.h          |    7 +-
> > > src/include/parser/pg_trigger.h       |   42 +-
> > > src/include/parser/pg_wchar.h         |   17 +-
> > > src/include/parser/primnodes.h        |   37 +-
> > > src/include/parser/scanner.h          |    4 +-
> > > src/include/parser/scansup.h          |    4 +-
> > > src/include/parser/stringinfo.h       |   12 +-
> > > src/include/parser/value.h            |   10 +-
> > > src/parser/copyfuncs.c                |  163 +++-
> > > src/parser/gram.h                     | 1622 +++++++++++++++++----------------
> > > src/parser/gram.y                     | 1076 +++++++++++++++-------
> > > src/parser/keywords.c                 |    4 +-
> > > src/parser/list.c                     |   66 +-
> > > src/parser/makefuncs.c                |   25 +-
> > > src/parser/nodes.c                    |    4 +-
> > > src/parser/outfuncs.c                 |  113 ++-
> > > src/parser/parser.c                   |   29 +-
> > > src/parser/scan.l                     |   18 +-
> > > src/parser/scansup.c                  |    6 +-
> > > src/parser/snprintf.c                 |   14 +-
> > > src/parser/stringinfo.c               |   25 +-
> > > src/parser/value.c                    |    4 +-
> > > src/parser/wchar.c                    |   12 +-
> > > src/protocol/pool_proto_modules.c     |    2 +-
> > > 33 files changed, 2192 insertions(+), 1460 deletions(-)
> > > 
> > 
> > 
> > -- 
> > Yugo Nagata <nagata at sraoss.co.jp>
> > 
> 
> 
> -- 
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS, Inc. Japan


-- 
Yugo Nagata <nagata at sraoss.co.jp>