MIME-Version: 1.0
From: Kwangwon Seo <anchovyseo@gmail.com>
Date: Fri, 10 Jul 2026 18:28:23 +0900
Message-ID: 
 <CAHJxwBWx_v=aWp7ZrGRFw2r_7MJdxYX2um3ZOmxTg4c_tL1qLA@mail.gmail.com>
Subject: [PATCH] Fix quotation logic for unreserved keywords in window
 specifications
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000006b8c7a06563e5df9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHJxwBWx_v%3DaWp7ZrGRFw2r_7MJdxYX2um3ZOmxTg4c_tL1qLA%40mail.gmail.com>
Precedence: bulk

--0000000000006b8c7a06563e5df9
Content-Type: multipart/alternative; boundary="0000000000006b8c7906563e5df7"

--0000000000006b8c7906563e5df7
Content-Type: text/plain; charset="UTF-8"

Hi hackers,

I was testing views with pg_dump/pg_restore and found a case where an
unreserved keyword is not quoted correctly.

example:

-- Just a base table
CREATE TABLE t (v int);
INSERT INTO t VALUES (1), (2);

-- Create a view
CREATE VIEW v AS
SELECT count(*) OVER w2 FROM t
WINDOW "rows" AS (PARTITION BY v), w2 AS ("rows" ORDER BY v);

But the problem is, here, that quotation is gone.

-- Problem: Quotation is disappared
SELECT pg_get_viewdef('v');
                       pg_get_viewdef
-------------------------------------------------------------
  SELECT count(*) OVER w2 AS count                          +
    FROM t                                                  +
   WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);
(1 row)

-- If you use this for recovering the view above, You'll face this error.
 SELECT count(*) OVER w2 AS count
        FROM t
        WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);
ERROR:  syntax error at or near "ORDER"
LINE 3:  WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);

This breaks pg_dump/restore: the view is lost.
As a result, pg_dump/pg_restore cannot restore the view successfully.


Fortunately, I found this comment in the gram.y:

It says:

 * If we see PARTITION, RANGE, ROWS or GROUPS as the first token after the
'('
 * of a window_specification, we want the assumption to be that there is
 * no existing_window_name; but those keywords are unreserved and so could
 * be ColIds.

I think that assumption can be broken when inheritance is used
across multiple window definitions.
I mean this: w2 AS ("rows" ORDER BY v)


Some comments for patch file:

The attached patch quotes the window name when it is a keyword
in get_rule_windowspec().
Output for ordinary window names is unchanged. The window definition itself
is a ColId so no change is needed there; only the refname is quoted.

Maybe this is a known thing on the team's side, but I think, at least,
should not break
compatibility with current tools. That is the motivation of this patch...

I'd appreciate any feedback or suggestions...!!


Best regards...

--0000000000006b8c7906563e5df7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div></div><div></div><div>Hi hackers,<br></div><div><br><=
/div><div>I was testing views with pg_dump/pg_restore and found a case wher=
e an unreserved keyword is not quoted correctly.<br><br>example:</div><div>=
<br></div><div>-- Just a base table</div><div>CREATE TABLE t (v int);</div>=
<div>INSERT INTO t VALUES (1), (2);</div><div><br></div><div>-- Create a vi=
ew</div><div>CREATE VIEW v AS<br>SELECT count(*) OVER w2 FROM t<br>WINDOW &=
quot;rows&quot; AS (PARTITION BY v), w2 AS (&quot;rows&quot; ORDER BY v);</=
div><div><br></div><div></div><div>But the problem is, here, that quotation=
 is gone.</div><div></div><div></div><div><br></div><div>-- Problem: Quotat=
ion is disappared</div><div>SELECT pg_get_viewdef(&#39;v&#39;);</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0pg_get_viewdef =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<br>-------------------------------------=
------------------------<br>=C2=A0 SELECT count(*) OVER w2 AS count =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0+<br>=C2=A0 =C2=A0 FROM t =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+<br>=C2=A0 =
=C2=A0WINDOW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);<br>(1 row)<=
/div><div><br></div><div>-- If you use this for recovering the view above, =
You&#39;ll face this error.</div><div>=C2=A0SELECT count(*) OVER w2 AS coun=
t<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM t<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WIND=
OW rows AS (PARTITION BY v), w2 AS (rows ORDER BY v);<br>ERROR: =C2=A0synta=
x error at or near &quot;ORDER&quot;<br>LINE 3: =C2=A0WINDOW rows AS (PARTI=
TION BY v), w2 AS (rows ORDER BY v);</div><div></div><div><br></div><div><d=
iv></div><div>This breaks pg_dump/restore: the view is lost.</div><div>As a=
 result, pg_dump/pg_restore cannot restore the view successfully.<br></div>=
<br></div><div><br></div><div><br></div><div></div><div>Fortunately,=C2=A0I=
 found this comment in the gram.y:</div><div></div><div><br></div><div>It s=
ays:</div><div></div><div><br></div><div>=C2=A0* If we see PARTITION, RANGE=
, ROWS or GROUPS as the first token after the &#39;(&#39;<br>=C2=A0* of a w=
indow_specification, we want the assumption to be that there is<br>=C2=A0* =
no existing_window_name; but those keywords are unreserved and so could<br>=
=C2=A0* be ColIds.</div><div><br></div><div></div><div>I think that assumpt=
ion can be broken when inheritance is=C2=A0used across=C2=A0multiple window=
 definitions.</div><div>I mean this: w2 AS (&quot;rows&quot; ORDER BY v)</d=
iv><div><br></div><div><br></div><div>Some comments for patch file:</div><d=
iv></div><div><br></div><div><div></div><div></div></div><div>The attached =
patch quotes the window name when it is a keyword in=C2=A0get_rule_windowsp=
ec().</div><div>Output for ordinary window names is unchanged. The window d=
efinition itself is a ColId=C2=A0so no change is needed there; only the=C2=
=A0refname=C2=A0is quoted.</div><div><br></div><div></div><div><div>Maybe t=
his is a known thing on the team&#39;s side, but I think, at least, should =
not break=C2=A0</div><div>compatibility with current tools.=C2=A0That is th=
e motivation of this patch...</div><div></div><div></div><div><br></div>I&#=
39;d appreciate any feedback or suggestions...!!</div><div><br></div><div><=
br>Best regards...</div></div>

--0000000000006b8c7906563e5df7--

--0000000000006b8c7a06563e5df9
Content-Type: text/x-patch; charset="US-ASCII";
	name="v1-0001-Fix-quotation-logic-for-unreserved-keywords-in-wi.patch"
Content-Disposition: attachment;
	filename="v1-0001-Fix-quotation-logic-for-unreserved-keywords-in-wi.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mreq8c8b0>
X-Attachment-Id: f_mreq8c8b0

RnJvbSA3MmMwZGQwZWE3ODE1NWRhYTM0ODg0NWZjZDc0YjkxMDJhNDA0MDU2IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBLd2FuZ3dvbiBTZW8gPGFuY2hvdnlzZW9AZ21haWwuY29tPgpE
YXRlOiBGcmksIDEwIEp1bCAyMDI2IDE2OjI2OjMzICswOTAwClN1YmplY3Q6IFtQQVRDSCB2MV0g
Rml4IHF1b3RhdGlvbiBsb2dpYyBmb3IgdW5yZXNlcnZlZCBrZXl3b3JkcyBpbiB3aW5kb3cKIHNw
ZWNpZmljYXRpb25zCgotLS0KIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMuYyAgICB8
IDMwICsrKysrKysrKysrKysrKysrKysrKysrKysrKy0KIHNyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0
ZWQvd2luZG93Lm91dCB8IDEyICsrKysrKysrKysrCiBzcmMvdGVzdC9yZWdyZXNzL3NxbC93aW5k
b3cuc3FsICAgICAgfCAgNyArKysrKysrCiAzIGZpbGVzIGNoYW5nZWQsIDQ4IGluc2VydGlvbnMo
KyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC91dGlscy9hZHQvcnVs
ZXV0aWxzLmMgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvcnVsZXV0aWxzLmMKaW5kZXggODE5NjMx
NzgxYzAuLjYwODRiNDhmNDM1IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9hZHQvcnVs
ZXV0aWxzLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3J1bGV1dGlscy5jCkBAIC00NTAs
NiArNDUwLDcgQEAgc3RhdGljIHZvaWQgZ2V0X3J1bGVfb3JkZXJieShMaXN0ICpvcmRlckxpc3Qs
IExpc3QgKnRhcmdldExpc3QsCiBzdGF0aWMgdm9pZCBnZXRfcnVsZV93aW5kb3djbGF1c2UoUXVl
cnkgKnF1ZXJ5LCBkZXBhcnNlX2NvbnRleHQgKmNvbnRleHQpOwogc3RhdGljIHZvaWQgZ2V0X3J1
bGVfd2luZG93c3BlYyhXaW5kb3dDbGF1c2UgKndjLCBMaXN0ICp0YXJnZXRMaXN0LAogCQkJCQkJ
CQlkZXBhcnNlX2NvbnRleHQgKmNvbnRleHQpOworc3RhdGljIHZvaWQgYXBwZW5kV2luZG93UmVm
TmFtZShTdHJpbmdJbmZvIGJ1ZiwgY29uc3QgY2hhciAqcmVmbmFtZSk7CiBzdGF0aWMgdm9pZCBn
ZXRfd2luZG93X2ZyYW1lX29wdGlvbnMoaW50IGZyYW1lT3B0aW9ucywKIAkJCQkJCQkJCSBOb2Rl
ICpzdGFydE9mZnNldCwgTm9kZSAqZW5kT2Zmc2V0LAogCQkJCQkJCQkJIGRlcGFyc2VfY29udGV4
dCAqY29udGV4dCk7CkBAIC03MTU5LDcgKzcxNjAsNyBAQCBnZXRfcnVsZV93aW5kb3dzcGVjKFdp
bmRvd0NsYXVzZSAqd2MsIExpc3QgKnRhcmdldExpc3QsCiAJYXBwZW5kU3RyaW5nSW5mb0NoYXIo
YnVmLCAnKCcpOwogCWlmICh3Yy0+cmVmbmFtZSkKIAl7Ci0JCWFwcGVuZFN0cmluZ0luZm9TdHJp
bmcoYnVmLCBxdW90ZV9pZGVudGlmaWVyKHdjLT5yZWZuYW1lKSk7CisJCWFwcGVuZFdpbmRvd1Jl
Zk5hbWUoYnVmLCB3Yy0+cmVmbmFtZSk7CiAJCW5lZWRzcGFjZSA9IHRydWU7CiAJfQogCS8qIHBh
cnRpdGlvbiBjbGF1c2VzIGFyZSBhbHdheXMgaW5oZXJpdGVkLCBzbyBvbmx5IHByaW50IGlmIG5v
IHJlZm5hbWUgKi8KQEAgLTcyMDEsNiArNzIwMiwzMyBAQCBnZXRfcnVsZV93aW5kb3dzcGVjKFdp
bmRvd0NsYXVzZSAqd2MsIExpc3QgKnRhcmdldExpc3QsCiAJYXBwZW5kU3RyaW5nSW5mb0NoYXIo
YnVmLCAnKScpOwogfQogCisvKgorICogRW1pdCB0aGUgbmFtZSBvZiB0aGUgd2luZG93IGRlZmlu
aXRpb24uCisgKgorICogUEFSVElUSU9OLCBSQU5HRSwgUk9XUywgYW5kIEdST1VQUyBoYXZlIHRo
ZSBzYW1lIHByZWNlZGVuY2UgYXMgSURFTlQKKyAqIGF0IHRoZSBzdGFydCBvZiBhIHdpbmRvdyBz
cGVjaWZpY2F0aW9uLCBwcmV2ZW50aW5nIHRoZW0gZnJvbSBiZWluZworICogcmVjb2duaXplZCBh
cyBhbiBleGlzdGluZ193aW5kb3dfbmFtZSAoc2VlIG9wdF9leGlzdGluZ193aW5kb3dfbmFtZQor
ICogaW4gZ3JhbS55KS4gU2luY2UgdGhlc2UgYXJlIHVucmVzZXJ2ZWQga2V5d29yZHMsIHF1b3Rl
X2lkZW50aWZpZXIoKQorICogZG9lcyBub3QgcXVvdGUgdGhlbSwgY2F1c2luZyB0aGUgZ2VuZXJh
dGVkIFNRTCB0byBmYWlsIHdoZW4gcmVwYXJzZWQuCisgKiBUaGVyZWZvcmUsIHF1b3RlIGFueSBr
ZXl3b3JkIGhlcmUgcmF0aGVyIHRoYW4gbWFpbnRhaW5pbmcgYSBsaXN0LgorICovCitzdGF0aWMg
dm9pZAorYXBwZW5kV2luZG93UmVmTmFtZShTdHJpbmdJbmZvIGJ1ZiwgY29uc3QgY2hhciAqcmVm
bmFtZSkKK3sKKwljb25zdCBjaGFyICpxdW90ZWQgPSBxdW90ZV9pZGVudGlmaWVyKHJlZm5hbWUp
OworCisJaWYgKHF1b3RlZCA9PSByZWZuYW1lICYmCisJCVNjYW5LZXl3b3JkTG9va3VwKHJlZm5h
bWUsICZTY2FuS2V5d29yZHMpID49IDApCisJeworCQkvKiBxdW90ZV9pZGVudGlmaWVyKCkgbGVm
dCBpdCBiYXJlLCBzbyBpdCBuZWVkcyBubyBlc2NhcGluZyAqLworCQlhcHBlbmRTdHJpbmdJbmZv
Q2hhcihidWYsICciJyk7CisJCWFwcGVuZFN0cmluZ0luZm9TdHJpbmcoYnVmLCByZWZuYW1lKTsK
KwkJYXBwZW5kU3RyaW5nSW5mb0NoYXIoYnVmLCAnIicpOworCX0KKwllbHNlCisJCWFwcGVuZFN0
cmluZ0luZm9TdHJpbmcoYnVmLCBxdW90ZWQpOworfQorCiAvKgogICogQXBwZW5kIHRoZSBkZXNj
cmlwdGlvbiBvZiBhIHdpbmRvdydzIGZyYW1pbmcgb3B0aW9ucyB0byBjb250ZXh0LT5idWYKICAq
LwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC93aW5kb3cub3V0IGIvc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC93aW5kb3cub3V0CmluZGV4IGMwYmRlMWM1ZWVjLi41MDgw
YjQxNWUwYiAxMDA2NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC93aW5kb3cub3V0
CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvd2luZG93Lm91dApAQCAtMTM2MSw2ICsx
MzYxLDE4IEBAIFNFTEVDVCBwZ19nZXRfdmlld2RlZigndl93aW5kb3cnKTsKICAgICBGUk9NIGdl
bmVyYXRlX3Nlcmllcyhub3coKSwgKG5vdygpICsgJ0AgMTAwIGRheXMnOjppbnRlcnZhbCksICdA
IDEgaG91cic6OmludGVydmFsKSBpKGkpOwogKDEgcm93KQogCistLSBBIHdpbmRvdyBuYW1lIHRo
YXQgaXMgYW4gdW5yZXNlcnZlZCBrZXl3b3JkIGNhbm5vdCBiZSBhbiBleGlzdGluZ193aW5kb3df
bmFtZQorQ1JFQVRFIFRFTVAgVklFVyB2Ml93aW5kb3dfdW5yZXNlcnZlZF9rdyBBUworCVNFTEVD
VCBjb3VudCgqKSBPVkVSIHcyIEZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIDEpIHModikKKyAgV0lO
RE9XICJyb3dzIiBBUyAoUEFSVElUSU9OIEJZIHYpLCB3MiBBUyAoInJvd3MiIE9SREVSIEJZIHYp
OworU0VMRUNUIHBnX2dldF92aWV3ZGVmKCd2Ml93aW5kb3dfdW5yZXNlcnZlZF9rdycpOworICAg
ICAgICAgICAgICAgICAgICAgICAgcGdfZ2V0X3ZpZXdkZWYgICAgICAgICAgICAgICAgICAgICAg
ICAgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0KKyAgU0VMRUNUIGNvdW50KCopIE9WRVIgdzIgQVMgY291bnQgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgKworICAgIEZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIDEpIHModikg
ICAgICAgICAgICAgICAgICAgICAgICAgICArCisgICBXSU5ET1cgcm93cyBBUyAoUEFSVElUSU9O
IEJZIHYpLCB3MiBBUyAoInJvd3MiIE9SREVSIEJZIHYpOworKDEgcm93KQorCiAtLSB0ZXN0IG92
ZXJmbG93IGZyYW1lIHNwZWNpZmljYXRpb25zCiBTRUxFQ1Qgc3VtKHVuaXF1ZTEpIG92ZXIgKHJv
d3MgYmV0d2VlbiBjdXJyZW50IHJvdyBhbmQgOTIyMzM3MjAzNjg1NDc3NTgwNyBmb2xsb3dpbmcg
ZXhjbHVkZSBjdXJyZW50IHJvdyksCiAJdW5pcXVlMSwgZm91cgpkaWZmIC0tZ2l0IGEvc3JjL3Rl
c3QvcmVncmVzcy9zcWwvd2luZG93LnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3dpbmRvdy5z
cWwKaW5kZXggOGU2ZjkyZDk0YzcuLjM5NmI5NWQwZDM5IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9y
ZWdyZXNzL3NxbC93aW5kb3cuc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3dpbmRvdy5z
cWwKQEAgLTMzMCw2ICszMzAsMTMgQEAgQ1JFQVRFIFRFTVAgVklFVyB2X3dpbmRvdyBBUwogCiBT
RUxFQ1QgcGdfZ2V0X3ZpZXdkZWYoJ3Zfd2luZG93Jyk7CiAKKy0tIEEgd2luZG93IG5hbWUgdGhh
dCBpcyBhbiB1bnJlc2VydmVkIGtleXdvcmQgY2Fubm90IGJlIGFuIGV4aXN0aW5nX3dpbmRvd19u
YW1lCitDUkVBVEUgVEVNUCBWSUVXIHYyX3dpbmRvd191bnJlc2VydmVkX2t3IEFTCisJU0VMRUNU
IGNvdW50KCopIE9WRVIgdzIgRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMSkgcyh2KQorICBXSU5E
T1cgInJvd3MiIEFTIChQQVJUSVRJT04gQlkgdiksIHcyIEFTICgicm93cyIgT1JERVIgQlkgdik7
CisKK1NFTEVDVCBwZ19nZXRfdmlld2RlZigndjJfd2luZG93X3VucmVzZXJ2ZWRfa3cnKTsKKwog
LS0gdGVzdCBvdmVyZmxvdyBmcmFtZSBzcGVjaWZpY2F0aW9ucwogU0VMRUNUIHN1bSh1bmlxdWUx
KSBvdmVyIChyb3dzIGJldHdlZW4gY3VycmVudCByb3cgYW5kIDkyMjMzNzIwMzY4NTQ3NzU4MDcg
Zm9sbG93aW5nIGV4Y2x1ZGUgY3VycmVudCByb3cpLAogCXVuaXF1ZTEsIGZvdXIKLS0gCjIuNTIu
MAoK
--0000000000006b8c7a06563e5df9--