[pgpool-hackers: 3268] Re: [pgpool-committers: 5093] pgpool: Import PostgreSQL 11 beta3 parser.
Bo Peng
pengbo at sraoss.co.jp
Fri Mar 15 18:22:54 JST 2019
Hi,
On Thu, 28 Feb 2019 20:54:01 +0900
Yugo Nagata <nagata at sraoss.co.jp> wrote:
> 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.
Thank you for checking my patch.
Yes. I forgot to add support for EXCLUDE claus.
I fixed that and attached this patch.
Because default is "EXCLUDE NO OTHERS", so query below
---
insert into result select now(), sum(i) over (
order by i rows between 1 preceding and 1 following EXCLUDE NO OTHERS) from test_tbl returning result;
---
is parsed to
---
LOG: statement: INSERT INTO "result" SELECT "pg_catalog"."timestamptz"(
'2019-03-15 18:16:25.197509 +09'::text),"sum"("i") OVER (
ORDER BY "i" ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM "test_tbl" RETURNING "result"
---
I think it should be ok.
> 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>
>
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
-------------- next part --------------
A non-text attachment was scrubbed...
Name: frame_groups_v2.patch
Type: application/octet-stream
Size: 1054 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20190315/58794d26/attachment.obj>
More information about the pgpool-hackers
mailing list