Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix cross-version upgrade test failure

  2. Fix deparsing FETCH FIRST <expr> ROWS WITH TIES

  1. BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    PG Bug reporting form <noreply@postgresql.org> — 2025-05-15T04:04:23Z

    The following bug has been logged on the website:

Bug reference:      18929
Logged by:          yonghao lee
Email address:      707330139@qq.com
PostgreSQL version: 17.5
Operating system:   linux
Description:        

Hello PostgreSQL team,
I found an error in the view definition SQL given when looking at the view
description using \d+. Here are the detailed steps for this problem.
executed query:
postgres=#  CREATE TABLE onek (thousand int4);
CREATE TABLE
postgres=#  CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek
WHERE thousand < 995  ORDER BY thousand FETCH FIRST (NULL::bigint +
1::bigint) ROWS WITH TIES;
CREATE VIEW
postgres=#  \d+ limit_thousand_v_3
                      View "public.limit_thousand_v_3"
  Column  |  Type   | Collation | Nullable | Default | Storage | Description
----------+---------+-----------+----------+---------+---------+-------------
 thousand | integer |           |          |         | plain   |
View definition:
 SELECT thousand
   FROM onek
  WHERE thousand < 995
  ORDER BY thousand
 FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
postgres=# SELECT thousand
postgres-#    FROM onek
postgres-#   WHERE thousand < 995
postgres-#   ORDER BY thousand
postgres-#  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
ERROR:  syntax error at or near "::"
LINE 5:  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
                         ^
I think that executing the view definition query will be successful.
I'm happy to provide more info if I can/ if needed.
Thanks,
    yonghao lee,
    Highgo software.

  2. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Heikki Linnakangas <hlinnaka@iki.fi> — 2025-05-15T14:41:42Z

    On 15/05/2025 07:04, PG Bug reporting form wrote:
> postgres=#  \d+ limit_thousand_v_3
>                        View "public.limit_thousand_v_3"
>    Column  |  Type   | Collation | Nullable | Default | Storage | Description
> ----------+---------+-----------+----------+---------+---------+-------------
>   thousand | integer |           |          |         | plain   |
> View definition:
>   SELECT thousand
>     FROM onek
>    WHERE thousand < 995
>    ORDER BY thousand
>   FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
> postgres=# SELECT thousand
> postgres-#    FROM onek
> postgres-#   WHERE thousand < 995
> postgres-#   ORDER BY thousand
> postgres-#  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
> ERROR:  syntax error at or near "::"
> LINE 5:  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>                           ^

Agreed that's a bug. Attached is a simple patch to add parens to all 
deparsed FETCH FIRST n ROWS WITH TIES expressions.

It would be nice to skip the parens for simple literals, but 
unfortunately it's non-trivial to distinguish Consts that are printed as 
simple literals from more complicated cases that are printed as 
typecasts, like '123'::bigint. XMLEXISTS is another expression type that 
takes a 'c_expr' in the the grammar, and it also just always adds the 
parens.

I'll commit this fix, unless someone comes up with something more 
sophisticated.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  3. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Heikki Linnakangas <hlinnaka@iki.fi> — 2025-05-19T15:52:50Z

    On 15/05/2025 17:41, Heikki Linnakangas wrote:
> On 15/05/2025 07:04, PG Bug reporting form wrote:
>> postgres=#  \d+ limit_thousand_v_3
>>                        View "public.limit_thousand_v_3"
>>    Column  |  Type   | Collation | Nullable | Default | Storage | 
>> Description
>> ----------+---------+-----------+----------+---------+--------- 
>> +-------------
>>   thousand | integer |           |          |         | plain   |
>> View definition:
>>   SELECT thousand
>>     FROM onek
>>    WHERE thousand < 995
>>    ORDER BY thousand
>>   FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>> postgres=# SELECT thousand
>> postgres-#    FROM onek
>> postgres-#   WHERE thousand < 995
>> postgres-#   ORDER BY thousand
>> postgres-#  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>> ERROR:  syntax error at or near "::"
>> LINE 5:  FETCH FIRST NULL::bigint + 1::bigint ROWS WITH TIES;
>>                           ^
> 
> Agreed that's a bug. Attached is a simple patch to add parens to all 
> deparsed FETCH FIRST n ROWS WITH TIES expressions.
> 
> It would be nice to skip the parens for simple literals, but 
> unfortunately it's non-trivial to distinguish Consts that are printed as 
> simple literals from more complicated cases that are printed as 
> typecasts, like '123'::bigint. XMLEXISTS is another expression type that 
> takes a 'c_expr' in the the grammar, and it also just always adds the 
> parens.
> 
> I'll commit this fix, unless someone comes up with something more 
> sophisticated.

Committed, thanks for the report.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  4. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-20T04:31:26Z

    Heikki Linnakangas <hlinnaka@iki.fi> writes:
> Committed, thanks for the report.

In the no-good-deed-goes-unpunished department: it appears from
buildfarm reports that we need to do something about output
mismatches in cross-version-upgrade tests due to this commit.
I didn't yet look more closely.

			regards, tom lane

  5. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Michael Paquier <michael@paquier.xyz> — 2025-05-20T05:02:21Z

    On Tue, May 20, 2025 at 12:31:26AM -0400, Tom Lane wrote:
> In the no-good-deed-goes-unpunished department: it appears from
> buildfarm reports that we need to do something about output
> mismatches in cross-version-upgrade tests due to this commit.
> I didn't yet look more closely.

Yes, the buildfarm is unhappy.

It seems to me that this needs an update of @_unused_view_qualifiers
in AdjustUpgrade.pm to update public.limit_thousand_v_5's qual, like
in the attached.  This is needed in v16 and newer branches.
--
Michael

  6. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Heikki Linnakangas <hlinnaka@iki.fi> — 2025-05-20T07:45:07Z

    On 20/05/2025 08:02, Michael Paquier wrote:
> On Tue, May 20, 2025 at 12:31:26AM -0400, Tom Lane wrote:
>> In the no-good-deed-goes-unpunished department: it appears from
>> buildfarm reports that we need to do something about output
>> mismatches in cross-version-upgrade tests due to this commit.
>> I didn't yet look more closely.
> 
> Yes, the buildfarm is unhappy.
> 
> It seems to me that this needs an update of @_unused_view_qualifiers
> in AdjustUpgrade.pm to update public.limit_thousand_v_5's qual, like
> in the attached.  This is needed in v16 and newer branches.

Committed the same fix.  Sorry for not crediting you in the commit 
message, I reached the same conclusion and committed before I saw this 
discussion. Thanks for looking!

-- 
Heikki Linnakangas
Neon (https://neon.tech)

  7. Re: BUG #18929: After the view is created, executed query against the view definition, reported syntax error.

    Michael Paquier <michael@paquier.xyz> — 2025-05-20T08:44:51Z

    On Tue, May 20, 2025 at 10:45:07AM +0300, Heikki Linnakangas wrote:
> Committed the same fix.  Sorry for not crediting you in the commit message,
> I reached the same conclusion and committed before I saw this discussion.
> Thanks for looking!

No problem.  Thanks for committing the fix.

> Neon (https://neon.tech)

Congrats on this part.
--
Michael