Re: Row pattern recognition

> CREATE TABLE t1 (id int, val int);
> CREATE TABLE t2 (id int);
> CREATE VIEW v1 AS
> SELECT FROM t1, t2 WHERE t1.id = t2.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN
> (A) DEFINE A AS val > 0 );
>
> ALTER TABLE t2 add column val int;
>
> The preceding ALTER TABLE ADD COLUMN succeeded,
> After that, copying the view v1's SELECT definition and re-running the
> SELECT statement will cause "ERROR: column reference "val" is
> ambiguous".
>
> To make the preceding ALTER TABLE ADD COLUMN fail, we must recursively
> find all directly and indirectly associated views that contain an RPR
> query,
> and that view's RPR DEFINE clause already references this new column name.
> Making this bulletproof doesn't seem easy. We also need to consider
> ALTER TABLE RENAME COLUMN.
>
> The implication of the above is that pg_dump | pg_restore will fail to
> restore view v1.
> This seems unsolvable; also see standard 6.5: Row pattern variables
> and other range variables

Yeah, the standard does not have an idea how to dump/restore this
case.

> So I added a warning in doc/src/sgml/ref/select.sgml.
>
> Because
> ERROR: cannot use CURRENT ROW as frame end with row pattern recognition
>
> { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] [
> row_pattern_common_syntax ]
> is not supported. we can remove row_pattern_common_syntax from here.
>
> The attached doc diff is based on
> https://github.com/assam258-5892/postgres/commits/RPR

Thanks. Looks basically good to me. BTW, Why do you have this? This
seems not related to the topic you are discussing on.

@@ -1207,7 +1207,6 @@ DEFINE <replaceable class="parameter">definition_variable_name</replaceable> AS
<para>
Note that the maximum number of unique pattern variables
used in the <literal>PATTERN</literal> clause is 240.
- If this limit is exceeded, an error will be raised.
Additionally, the maximum nesting depth of pattern groups
(parentheses) is 254 levels.
However, pattern optimizations such as flattening nested sequences

Also, the line length in this paragraph is too long.
+ <para>
+ This also affects <application>pg_dump</application> and <application>pg_restore</application>.
+ A view can be created while a <literal>DEFINE</literal> referenced column name
+ is still unique, but if a later schema change (such as adding or renaming a
+ column) introduces a duplicate of that name, the view's definition keeps working while
+ its dumped definition no longer valid, restoring it fails with a
+ <quote>column reference is ambiguous</quote> error.
+ </para>

Please make each line shorter (the maximum number of columns in each
row is 78. See "J.5. Documentation Authoring" of "Appendix
J. Documentation"). An example is below.

<para>
This also affects <application>pg_dump</application>
and <application>pg_restore</application>. A view can be created while
a <literal>DEFINE</literal> referenced column name is still unique, but
if a later schema change (such as adding or renaming a column) introduces
a duplicate of that name, the view's definition keeps working while its
dumped definition no longer valid, restoring it fails with a
<quote>column reference is ambiguous</quote> error.
</para>

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