[DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back

Started by Henson Choiabout 1 month ago5 messageshackers
Jump to latest
#1Henson Choi
assam258@gmail.com

Hi hackers,

A function in FROM, or in ROWS FROM(), must be written in
function-call syntax. ruleutils.c knows this and puts anything else
there inside CAST(... AS type), but it takes AT TIME ZONE, AT LOCAL,
OVERLAPS and IS NORMALIZED for function calls. They are printed in
parentheses instead, which the grammar does not accept there, so a
view using one cannot be dumped and restored. The two attached
patches add tests and fix it.

The problem
-----------

create view v as
select * from cast(now() at time zone 'UTC' as timestamp) as t;

before: SELECT t FROM (now() AT TIME ZONE 'UTC'::text) t(t);
ERROR: syntax error at or near "TIME"
after: SELECT t FROM CAST((now() AT TIME ZONE 'UTC'::text)
AS timestamp without time zone) t(t);

In ROWS FROM() the same expression fails with a syntax error at
"(". AT LOCAL (17 and later), (a, b) OVERLAPS (c, d) and
x IS NORMALIZED behave the same way. Pretty and non-pretty output
are both affected, so pg_dump output cannot be restored. The other
SQL-syntax functions, such as EXTRACT, SUBSTRING, TRIM and
COLLATION FOR, print in a form the grammar accepts there.

The fix
-------

Only a function in FROM and in ROWS FROM() must be in function-call
syntax (func_expr_windowless), and both go through
get_rule_expr_funccall(). Index, statistics and partition keys
accept function-call syntax too, but they also accept a
parenthesized expression, so the current output reads back there.
Other places, such as the select list, are fine.

looks_like_function() serves both. The callers for keys only decide
whether to add parentheses, so simply rejecting these four forms
there would give key expressions a second pair:
RANGE (((ts AT TIME ZONE 'UTC'::text))). Printing them always as a
CAST in get_func_sql_syntax() would fix FROM too, but would change
all the output that was fine: view bodies, key expressions, EXPLAIN
and so on.

So 0002 gives looks_like_function() an in_from argument and rejects
these four forms only when it is true. get_rule_expr_funccall()
passes true, and the callers for keys pass false. A function in
FROM then gets the dummy CAST() that is already there for such
cases, and no other output changes.

This puts a list of the function OIDs for these four forms in
looks_like_function(). A function added to them must be added to
the list too, so the comments on both sides point to each other.

Testing
-------

0001 adds the four forms to the existing tt20v view in create_view.
0001 has the output before the fix, and 0002 updates it. I checked
that views and ROWS FROM recreated from the fixed output have the
same definitions as the originals.

Back branches
-------------

Since 14, these functions are printed in SQL syntax, and
looks_like_function() treats every SQL-syntax function as a function
call; before that they were printed as plain function calls. So I
think this should be fixed back to 14. Since the only output that
changes is the function in FROM, which was broken, the same patch can
go into the back branches. AT LOCAL exists only in 17 and later, so
14-16 need its cases and its test column dropped.

This came out of the same AI-assisted audit of ruleutils.c as my
earlier [DEPARSE] reports.

Best regards,
Henson

Attachments:

t254301_1
v1-0001-Add-tests-for-AT-TIME-ZONE-and-similar.patchapplication/octet-stream; name=v1-0001-Add-tests-for-AT-TIME-ZONE-and-similar.patchDownload+34-16
v1-0002-Fix-deparsing-of-AT-TIME-ZONE-and-similar.patchapplication/octet-stream; name=v1-0002-Fix-deparsing-of-AT-TIME-ZONE-and-similar.patchDownload+91-34
#2Prashant Chinnam
crprashant@hotmail.com
In reply to: Henson Choi (#1)
Re: [DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested

Hi Henson,

Thanks for the patch. I have read through the report and the code, and tested it hands-on (the build and test runs were done with AI assistance). I agree with the analysis: the four forms are not function calls in the grammar, and FROM is the only place that insists on function-call syntax, so teaching looks_like_function() about the context rather than changing what get_func_sql_syntax() prints everywhere - is the right layer.

On the code: I checked the OID list in looks_like_function() against get_func_sql_syntax() case by case. The 23 funcids are exactly the ones printed there as a parenthesized expression; everything else (EXTRACT, TRIM, SUBSTRING, POSITION, OVERLAY, COLLATION FOR, and so on) prints in KEYWORD(...) form and is correctly left returning true. The cross-pointing comments on both sides should help keep the two lists in sync. I also confirmed the only other callers are the index, statistics and partition key deparsers, which pass false, and that index/partition/statistics key output is byte-for-byte unchanged with the patch - no extra parentheses appear there.

What I verified, on current master with v1 applied in order:

The bug reproduces on unpatched master in all the reported forms: views using AT TIME ZONE, AT LOCAL, OVERLAPS or IS NORMALIZED in FROM print back in parentheses and fail to recreate with a syntax error (the report's example fails at "TIME"; the ROWS FROM form fails at "("), and a pg_dump containing them cannot be restored.
With the patch, every one of these views recreates from its pg_get_viewdef() output -- pretty and non-pretty -- with an identical definition, and a dump/restore of the same database completes with no errors.
Objects created before the fix are repaired by the patched deparser alone: reading a database created on unpatched master under the patched server prints the CAST form, and a fresh dump of it restores cleanly.
I also tried a few forms the new tests do not cover. A timetz AT TIME ZONE whose result is used as timetz (identity cast) is broken on master and fixed by the patch, so the F_TIMEZONE_*_TIMETZ entries in the list are load-bearing; time-pair and interval-endpoint OVERLAPS behave the same way, as does a nested AT TIME ZONE. Two related forms were never broken, for the record: IS NOT NORMALIZED (the NOT sits on top, so it already got the dummy CAST) and AT TIME ZONE under a real, type-changing cast.
A BEGIN ATOMIC SQL function whose body uses AT TIME ZONE in FROM also fails to read back on master and is fixed; its definition now prints the CAST form.
installcheck-world is fully green for me on the patched build: core regression 239/239, isolation 134/134, contrib 206/206 tests plus contrib isolation 20/20, test modules 71/71 plus module isolation 24/24, including the injection_points isolation specs 18/18. (The only failures I saw along the way were in my own installcheck setup - test_decoding needed max_prepared_transactions=10 on the server; with that set it passes 20/20 and 16/16.)

One suggestion: the tests added to tt20v cover the timestamp and timestamptz forms, but not the timetz form of AT TIME ZONE, which is broken in the same way and fixed by the same list. Adding that one case would pin the timetz entries of the list against regressions.

I also agree with backpatching to 14, with the AT LOCAL cases dropped for 14–16 as you describe, since the only output that changes is the FROM case that was broken.

--Prashant

The new status of this patch is: Waiting on Author

#3Henson Choi
assam258@gmail.com
In reply to: Henson Choi (#1)
Re: [DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back

Hi Prashant,

One suggestion: the tests added to tt20v cover the timestamp and
timestamptz forms, but not the timetz form of AT TIME ZONE, which
is broken in the same way and fixed by the same list.

Thank you for the careful review and testing. As you suggest, I
will send a revised patch (v2) to the thread with the tests
extended by the timetz form of AT TIME ZONE in tt20v.

So that you can also exchange reviews by mail, here is how. A
review in the CommitFest app is fine, but replying by mail lets the
discussion continue right in the thread.

1. Log in to https://lists.postgresql.org/ with the same
community account you use for the CommitFest app and subscribe
to pgsql-hackers. The list has a lot of traffic, so in Gmail I
made a label: list mail skips the inbox and goes straight to
that label, and only mail with my address in To or Cc stays in
the inbox. An AI agent will also work out such a filter in
detail if you ask.

2. To reply to a message sent before you subscribed, open the
patch page in the CommitFest app
(e.g. https://commitfest.postgresql.org/patch/7491/) and click
the thread title or the "Latest at" date under Emails to go to
that message in the mail archive. While logged in, click
"Resend email"; it is sent to your inbox again. "Reply all" to
that mail keeps the thread together.

3. When replying, put the person the message is meant for in To, if
there is one, and the people who took part in the thread, along
with pgsql-hackers, in Cc. Write in plain text and, by convention,
break lines yourself so that none goes past 80 columns (around 72
is common). The convention for replies is inline replies: keep
only the sentences you answer as quotes, delete the rest, and
write your answer right below each quote (no top-posting).

One more thing: the AI agent you already use can explain this
PostgreSQL contribution process, such as mailing-list conventions
and how the CommitFest works, quite well.

Regards,
Henson

#4Henson Choi
assam258@gmail.com
In reply to: Henson Choi (#3)
Re: [DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back

Hi Prashant,

One suggestion: the tests added to tt20v cover the timestamp and
timestamptz forms, but not the timetz form of AT TIME ZONE, which
is broken in the same way and fixed by the same list. Adding that
one case would pin the timetz entries of the list against
regressions.

Done in v2, attached. tt20v now also has

cast(current_time at time zone 'UTC' as timetz) as atzt

which goes through F_TIMEZONE_TEXT_TIMETZ.

While at it, I added one more column for ROWS FROM. The commit
message says the fix covers a function alone in FROM or in ROWS
FROM, but v1 only tested the first. ROWS FROM prints each of its
functions through the same get_rule_expr_funccall(), so it now gets
the CAST back the same way:

rows from(cast('foo' is normalized as boolean), now()) as rf(rfa, rfb)

before:
ROWS FROM(('foo'::text IS NORMALIZED), now()) rf(rfa, rfb)
after:
ROWS FROM(CAST(('foo'::text IS NORMALIZED) AS boolean), now()) rf(rfa,
rfb)

It has two functions because ROWS FROM() is printed only then; a
single function is printed alone.

The code in 0002 is unchanged from v1. 0001 has the two new
columns, with the output before the fix, and 0002 updates their
output along with the others.

Regards,
Henson

Attachments:

t254301_3
v2-0001-Add-tests-for-AT-TIME-ZONE-and-similar-as-FROM-cl.patchapplication/octet-stream; name=v2-0001-Add-tests-for-AT-TIME-ZONE-and-similar-as-FROM-cl.patchDownload+43-16
v2-0002-Fix-deparsing-of-AT-TIME-ZONE-and-similar-as-FROM.patchapplication/octet-stream; name=v2-0002-Fix-deparsing-of-AT-TIME-ZONE-and-similar-as-FROM.patchDownload+96-39
#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Henson Choi (#4)
Re: [DEPARSE] AT TIME ZONE and similar as a function in FROM do not read back

Henson Choi <assam258@gmail.com> writes:

The code in 0002 is unchanged from v1. 0001 has the two new
columns, with the output before the fix, and 0002 updates their
output along with the others.

Pushed with some editorialization. I did not see the need to
complicate the API spec for looks_like_function(): it's defined
to say whether the construct will parse as func_expr_windowless,
and that's what it should do. I don't think the cases where
this results in extra parentheses are common enough to be a
big deal, especially since there seem to be none in our
regression tests.

regards, tom lane