Date: Sat, 20 Jun 2026 12:17:19 +0900 (JST)
Message-Id: <20260620.121719.209332541992434500.ishii@postgresql.org>
To: jian.universality@gmail.com
Cc: assam258@gmail.com, zsolt.parragi@percona.com, sjjang112233@gmail.com,
 vik@postgresfriends.org, er@xs4all.nl, jacob.champion@enterprisedb.com,
 david.g.johnston@gmail.com, peter@eisentraut.org, li.evan.chao@gmail.com,
 pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: 
 <CACJufxHEu+zYWdpi4gP7FbL+fjMhk8VOF7LJDU4CG=ZMYEPb-w@mail.gmail.com>
References: 
 <CACJufxH_Z5aaYEir3=GHoZu-0pKzQdScu85LgCm1C8n=oQo=4Q@mail.gmail.com>
	<CAAAe_zAe8CRN-ggNhcP7b-ALiRrHxKTXBWbNVMhdW_QoO=1c4Q@mail.gmail.com>
	<CACJufxHEu+zYWdpi4gP7FbL+fjMhk8VOF7LJDU4CG=ZMYEPb-w@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20260620.121719.209332541992434500.ishii%40postgresql.org>
Precedence: bulk

Hi Jian,

Only comments to error messages.

> CREATE TABLE stock (company TEXT, tdate DATE, price INTEGER);
> CREATE TEMP TABLE stock (company TEXT, tdate DATE, price INTEGER);
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS
> pg_temp.stock.price > 0 );
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.stock.price
>> 0 );
> SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.price > 0 );
> 
> The error messages for the above 3 SELECT queries are different.
> (pg_temp.stock.price, public.stock.price, stock.price) mean the same
> thing: column reference,
> Should we try to make the error messages consistent?

I have tested above queries to see how error messages actually look
like. These errors raised by different reasons and becomes different
looks natural. I see no consistency problem here.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS
pg_temp.stock.price > 0 );
(1)
psql:rangevar.sql:6: ERROR:  42601: qualified expression "pg_temp.stock.price" is not allowed in DEFINE clause
LINE 3: pg_temp.stock.price > 0 );
        ^
LOCATION:  transformColumnRef, parse_expr.c:966

"stock" table in the FROM clause is actually pg_temp.stock. The
expression "pg_temp.stock.price > 0" is valid in general but in a DEFINE
clause schema qualified column reference is not allowed by the
standard. So the error messages look reasonable to me.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.stock.price
> 0 );
(2)
psql:rangevar.sql:9: ERROR:  42P01: invalid reference to FROM-clause entry for table "stock"
LINE 2: ...W AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS public.sto...
                                                             ^
DETAIL:  There is an entry for table "stock", but it cannot be referenced from this part of the query.
LOCATION:  errorMissingRTE, parse_relation.c:3864

"stock" table in the FROM clause is actually pg_temp.stock. The
expression "public.stock.price > 0" is not valid because public.stock
is not in the FROM clause. The error messages look reasonable to me.

SELECT count(*) over w FROM stock WINDOW w AS ( ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.price > 0 );
(3)
psql:rangevar.sql:11: ERROR:  42601: range variable qualified expression "stock.price" is not allowed in DEFINE clause
LINE 2: ...W AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS stock.pric...
                                                             ^
LOCATION:  transformColumnRef, parse_expr.c:674

The error message precisely points out that the range variable "stock"
qualifies "stock.price", which is not allowed by the standard. I see
no problem here.

> ERROR:  range variable qualified expression "rpr_composite.items" is
> not allowed in DEFINE clause
> 
> "Range variable qualified expression" is non-standard that may confuse users.

Which part of it do you think "non-standard"?  The standard uses both
terms "Range variable" and "qualified".

> To improve clarity and consistency, let's align this with the
> established error pattern:
> 
> ERROR: invalid reference to FROM-clause entry for table "the_table"

-1. As I explained above, these 3 errors raised by the different
 reasons. "invalid reference to FROM-clause entry for table
 "the_table" is only applied to (2). So unified (1) and (3) will make
 more confusion.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp