Re: Row pattern recognition

Hi Henson,

> Your case looks like the same shape. It currently deparses to
>
> SELECT
> FROM t1,
> t2
> WHERE t1.id = t2.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> AFTER MATCH SKIP PAST LAST ROW
> INITIAL
> PATTERN (a)
> DEFINE
> a AS val > 0);
>
> and that text does not reparse, exactly as you say
> (ERROR: column reference "val" is ambiguous). But with one column
> alias list on t2,
>
> t2 t2(id, val_1)
>
> the same text creates the view, a second deparse gives back the same
> text, and DEFINE resolves to t1.val. So what the deparser is missing
> here is a single token.

I played with following SQL to check if the technique using subquery
to resolve an ambiguity of column names in DEFINE clause explained in
ISO/IEC 19075-5 section 6.5 "Row pattern variables and other range
variables".

First create an ambiguous column "val" in DEFINE clause.

CREATE TABLE t1 (id int, val int);
CREATE TABLE
CREATE TABLE t2 (id int, val int);
CREATE TABLE
INSERT INTO t1 VALUES(1,1),(2,2);
INSERT 0 2
INSERT INTO t2 VALUES(1,-1),(2,-2);
INSERT 0 2
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
);
psql:ambiguous.sql:13: ERROR: column reference "val" is ambiguous
LINE 5: DEFINE A AS val > 0
^
Next, use the subquery workaround:

SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
WHERE tt.id1 = ttt.id
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN(A)
DEFINE A AS val < 0
);
id1 | val1 | id | val | count
-----+------+----+-----+-------
1 | 1 | 1 | -1 | 1
2 | 2 | 2 | -2 | 1
(2 rows)

Seems work. Now, create a view from the query.

CREATE VIEW v1 AS
SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
WHERE tt.id1 = ttt.id
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN(A)
DEFINE A AS val < 0
);
CREATE VIEW
SELECT * FROM v1;
id1 | val1 | id | val | count
-----+------+----+-----+-------
1 | 1 | 1 | -1 | 1
2 | 2 | 2 | -2 | 1
(2 rows)

Again, it works. Let's check the view definition.

SELECT pg_get_viewdef('v1'::regclass, true);
pg_get_viewdef
------------------------------------------------------------------
SELECT tt.id1, +
tt.val1, +
ttt.id, +
ttt.val, +
count(*) OVER w AS count +
FROM ( SELECT t1.id AS id1, +
t1.val AS val1 +
FROM t1) tt, +
t2 ttt +
WHERE tt.id1 = ttt.id +
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
AFTER MATCH SKIP PAST LAST ROW +
INITIAL +
PATTERN (a) +
DEFINE +
a AS ttt.val < 0 );
(1 row)

Not good. The DEFINE clause uses a range variable declared in the FROM
clause (ttt.val), which is not valid. Of course if we dump/restore
this, restore will fail.

So, even if we do not use ALTER TABLE ADD COLULN/RENAME COLUMN, we
have a problem with views using RPR. I have not checked how hard to
fix this yet. If it's hard, probably we should add this as a
limitation of RPR to the document.

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