Re: Implementing Incremental View Maintenance

2020-02-18 Thread nuko yokohama 
Hi.

SELECT statements with a TABLESAMPLE clause should be rejected.

Currently, CREATE INCREMENTAL MATERIALIZED VIEW allows SELECT statements
with the TABLESAMPLE clause.
However, the result of this SELECT statement is undefined and should be
rejected when specified in CREATE INCREMENTAL MATERIALIZED VIEW.
(similar to handling non-immutable functions)
Regard.

2020年2月8日(土) 11:15 nuko yokohama :

> Hi.
>
> UNION query problem.(server crash)
>
> When creating an INCREMENTAL MATERIALIZED VIEW,
> the server process crashes if you specify a query with a UNION.
>
> (commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)
>
> execute log.
>
> ```
> [ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
> DROP TABLE IF EXISTS table_x CASCADE;
> psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
> DROP TABLE
> DROP TABLE IF EXISTS table_y CASCADE;
> DROP TABLE
> CREATE TABLE table_x (id int, data numeric);
> CREATE TABLE
> CREATE TABLE table_y (id int, data numeric);
> CREATE TABLE
> INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
> INSERT 0 3
> INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
> INSERT 0 3
> SELECT * FROM table_x;
>  id |data
> +
>   1 |  0.950724735058774
>   2 | 0.0222670808201144
>   3 |  0.391258547114841
> (3 rows)
>
> SELECT * FROM table_y;
>  id |data
> +
>   1 |  0.991717347778337
>   2 | 0.0528458947672874
>   3 |  0.965044982911163
> (3 rows)
>
> CREATE VIEW xy_union_v AS
> SELECT 'table_x' AS name, * FROM table_x
> UNION
> SELECT 'table_y' AS name, * FROM table_y
> ;
> CREATE VIEW
> TABLE xy_union_v;
>   name   | id |data
> -++
>  table_y |  2 | 0.0528458947672874
>  table_x |  2 | 0.0222670808201144
>  table_y |  3 |  0.965044982911163
>  table_x |  1 |  0.950724735058774
>  table_x |  3 |  0.391258547114841
>  table_y |  1 |  0.991717347778337
> (6 rows)
>
> CREATE INCREMENTAL MATERIALIZED VIEW xy_imv AS
> SELECT 'table_x' AS name, * FROM table_x
> UNION
> SELECT 'table_y' AS name, * FROM table_y
> ;
> psql:union_query_crash.sql:28: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> psql:union_query_crash.sql:28: fatal: connection to server was lost
> ```
> UNION query problem.(server crash)
>
> When creating an INCREMENTAL MATERIALIZED VIEW,
> the server process crashes if you specify a query with a UNION.
>
> (commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)
>
> execute log.
>
> ```
> [ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
> DROP TABLE IF EXISTS table_x CASCADE;
> psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
> DROP TABLE
> DROP TABLE IF EXISTS table_y CASCADE;
> DROP TABLE
> CREATE TABLE table_x (id int, data numeric);
> CREATE TABLE
> CREATE TABLE table_y (id int, data numeric);
> CREATE TABLE
> INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
> INSERT 0 3
> INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
> INSERT 0 3
> SELECT * FROM table_x;
>  id |data
> +
>   1 |  0.950724735058774
>   2 | 0.0222670808201144
>   3 |  0.391258547114841
> (3 rows)
>
> SELECT * FROM table_y;
>  id |data
> +
>   1 |  0.991717347778337
>   2 | 0.0528458947672874
>   3 |  0.965044982911163
> (3 rows)
>
> CREATE VIEW xy_union_v AS
> SELECT 'table_x' AS name, * FROM table_x
> UNION
> SELECT 'table_y' AS name, * FROM table_y
> ;
> CREATE VIEW
> TABLE xy_union_v;
>   name   | id |data
> -++
>  table_y |  2 | 0.0528458947672874
>  table_x |  2 | 0.0222670808201144
>  table_y |  3 |  0.965044982911163
>  table_x |  1 |  0.950724735058774
>  table_x |  3 |  0.391258547114841
>  table_y |  1 |  0.991717347778337
> (6 rows)
>
> CREATE INCREMENTAL MATERIALIZED VIEW xy_imv AS
> SELECT 'table_x' AS name, * FROM table_x
> UNION
> SELECT 'table_y' AS name, * FROM table_y
> ;
> psql:union_query_crash.sql:28: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> psql:union_query_crash.sql:28: fatal: connection to server was lost
> ```
>
> 2018年12月27日(木) 21:57 Yugo Nagata :
>
>> Hi,
>>
>> I would like to implement Increm

Re: Implementing Incremental View Maintenance

2020-02-09 Thread nuko yokohama 
Hi.

I understod that UNION is unsupported.

I also refer to the implementation of "./src/backend/commands/createas.c"
check_ivm_restriction_walker () to see if there are any other queries that
may be problematic.

2020年2月10日(月) 10:38 Yugo NAGATA :

> On Sat, 8 Feb 2020 11:15:45 +0900
> nuko yokohama  wrote:
>
> > Hi.
> >
> > UNION query problem.(server crash)
> >
> > When creating an INCREMENTAL MATERIALIZED VIEW,
> > the server process crashes if you specify a query with a UNION.
>
> Thank you for your report. As you noticed set operations including
> UNION is concurrently unsupported, although this is not checked at
> definition time and not documented either. Now we are thoroughly
> investigating unsupported queries, and will add checks and
> documentations for them.
>
> Regards,
> Yugo Nagata
>
> >
> > (commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)
> >
> > execute log.
> >
> > ```
> > [ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
> > DROP TABLE IF EXISTS table_x CASCADE;
> > psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
> > DROP TABLE
> > DROP TABLE IF EXISTS table_y CASCADE;
> > DROP TABLE
> > CREATE TABLE table_x (id int, data numeric);
> > CREATE TABLE
> > CREATE TABLE table_y (id int, data numeric);
> > CREATE TABLE
> > INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
> > INSERT 0 3
> > INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
> > INSERT 0 3
> > SELECT * FROM table_x;
> >  id |data
> > +
> >   1 |  0.950724735058774
> >   2 | 0.0222670808201144
> >   3 |  0.391258547114841
> > (3 rows)
> >
> > SELECT * FROM table_y;
> >  id |data
> > +
> >   1 |  0.991717347778337
> >   2 | 0.0528458947672874
> >   3 |  0.965044982911163
> > (3 rows)
> >
> > CREATE VIEW xy_union_v AS
> > SELECT 'table_x' AS name, * FROM table_x
> > UNION
> > SELECT 'table_y' AS name, * FROM table_y
> > ;
> > CREATE VIEW
> > TABLE xy_union_v;
> >   name   | id |data
> > -++
> >  table_y |  2 | 0.0528458947672874
> >  table_x |  2 | 0.0222670808201144
> >  table_y |  3 |  0.965044982911163
> >  table_x |  1 |  0.950724735058774
> >  table_x |  3 |  0.391258547114841
> >  table_y |  1 |  0.991717347778337
> > (6 rows)
> >
> > CREATE INCREMENTAL MATERIALIZED VIEW xy_imv AS
> > SELECT 'table_x' AS name, * FROM table_x
> > UNION
> > SELECT 'table_y' AS name, * FROM table_y
> > ;
> > psql:union_query_crash.sql:28: server closed the connection unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
> > psql:union_query_crash.sql:28: fatal: connection to server was lost
> > ```
> > UNION query problem.(server crash)
> >
> > When creating an INCREMENTAL MATERIALIZED VIEW,
> > the server process crashes if you specify a query with a UNION.
> >
> > (commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)
> >
> > execute log.
> >
> > ```
> > [ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
> > DROP TABLE IF EXISTS table_x CASCADE;
> > psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
> > DROP TABLE
> > DROP TABLE IF EXISTS table_y CASCADE;
> > DROP TABLE
> > CREATE TABLE table_x (id int, data numeric);
> > CREATE TABLE
> > CREATE TABLE table_y (id int, data numeric);
> > CREATE TABLE
> > INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
> > INSERT 0 3
> > INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
> > INSERT 0 3
> > SELECT * FROM table_x;
> >  id |data
> > +
> >   1 |  0.950724735058774
> >   2 | 0.0222670808201144
> >   3 |  0.391258547114841
> > (3 rows)
> >
> > SELECT * FROM table_y;
> >  id |data
> > +
> >   1 |  0.991717347778337
> >   2 | 0.0528458947672874
> >   3 |  0.965044982911163
> > (3 rows)
> >
> > CREATE VIEW xy_union_v AS
> > SELECT 'table_x' AS name, * FROM table_x
> > UNION
> > SELECT 'table_y' AS name, * FROM table_y
> > ;
> > CREATE VIEW
> > TABLE xy_union_v;
> >   name   | id |data
>

Re: Implementing Incremental View Maintenance

2020-02-07 Thread nuko yokohama 
Hi.

UNION query problem.(server crash)

When creating an INCREMENTAL MATERIALIZED VIEW,
the server process crashes if you specify a query with a UNION.

(commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)

execute log.

```
[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
DROP TABLE IF EXISTS table_x CASCADE;
psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
DROP TABLE
DROP TABLE IF EXISTS table_y CASCADE;
DROP TABLE
CREATE TABLE table_x (id int, data numeric);
CREATE TABLE
CREATE TABLE table_y (id int, data numeric);
CREATE TABLE
INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
INSERT 0 3
INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
INSERT 0 3
SELECT * FROM table_x;
 id |data
+
  1 |  0.950724735058774
  2 | 0.0222670808201144
  3 |  0.391258547114841
(3 rows)

SELECT * FROM table_y;
 id |data
+
  1 |  0.991717347778337
  2 | 0.0528458947672874
  3 |  0.965044982911163
(3 rows)

CREATE VIEW xy_union_v AS
SELECT 'table_x' AS name, * FROM table_x
UNION
SELECT 'table_y' AS name, * FROM table_y
;
CREATE VIEW
TABLE xy_union_v;
  name   | id |data
-++
 table_y |  2 | 0.0528458947672874
 table_x |  2 | 0.0222670808201144
 table_y |  3 |  0.965044982911163
 table_x |  1 |  0.950724735058774
 table_x |  3 |  0.391258547114841
 table_y |  1 |  0.991717347778337
(6 rows)

CREATE INCREMENTAL MATERIALIZED VIEW xy_imv AS
SELECT 'table_x' AS name, * FROM table_x
UNION
SELECT 'table_y' AS name, * FROM table_y
;
psql:union_query_crash.sql:28: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:union_query_crash.sql:28: fatal: connection to server was lost
```
UNION query problem.(server crash)

When creating an INCREMENTAL MATERIALIZED VIEW,
the server process crashes if you specify a query with a UNION.

(commit id = 23151be7be8d8f8f9c35c2d0e4e5353aedf2b31e)

execute log.

```
[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f union_query_crash.sql
DROP TABLE IF EXISTS table_x CASCADE;
psql:union_query_crash.sql:6: NOTICE:  drop cascades to view xy_union_v
DROP TABLE
DROP TABLE IF EXISTS table_y CASCADE;
DROP TABLE
CREATE TABLE table_x (id int, data numeric);
CREATE TABLE
CREATE TABLE table_y (id int, data numeric);
CREATE TABLE
INSERT INTO table_x VALUES (generate_series(1, 3), random()::numeric);
INSERT 0 3
INSERT INTO table_y VALUES (generate_series(1, 3), random()::numeric);
INSERT 0 3
SELECT * FROM table_x;
 id |data
+
  1 |  0.950724735058774
  2 | 0.0222670808201144
  3 |  0.391258547114841
(3 rows)

SELECT * FROM table_y;
 id |data
+
  1 |  0.991717347778337
  2 | 0.0528458947672874
  3 |  0.965044982911163
(3 rows)

CREATE VIEW xy_union_v AS
SELECT 'table_x' AS name, * FROM table_x
UNION
SELECT 'table_y' AS name, * FROM table_y
;
CREATE VIEW
TABLE xy_union_v;
  name   | id |data
-++
 table_y |  2 | 0.0528458947672874
 table_x |  2 | 0.0222670808201144
 table_y |  3 |  0.965044982911163
 table_x |  1 |  0.950724735058774
 table_x |  3 |  0.391258547114841
 table_y |  1 |  0.991717347778337
(6 rows)

CREATE INCREMENTAL MATERIALIZED VIEW xy_imv AS
SELECT 'table_x' AS name, * FROM table_x
UNION
SELECT 'table_y' AS name, * FROM table_y
;
psql:union_query_crash.sql:28: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:union_query_crash.sql:28: fatal: connection to server was lost
```

2018年12月27日(木) 21:57 Yugo Nagata :

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, W

Re: Implementing Incremental View Maintenance

2020-02-04 Thread nuko yokohama 
"ROW LEVEL SECURITY" and INCREMENTAL MATERIALIZED VIEW.

Hi.

If ROW LEVEL SECURITY is set for the source table after creating the
INCREMENTAL MATELIALIZED VIEW, the search results by that are not reflected.
After setting ROW LEVEL SECURITY (similar to normal MATERIALIZED VIEW), you
need to execute REFRESH MATERILALIZED VIEW and reflect the result.
(Not limited to this, but in general cases where search results change by
means other than DML)

I propose to add this note to the document (rules.sgml).

execute log.

```
[ec2-user@ip-10-0-1-10 rls]$ psql testdb -e -f rls.sql
CREATE USER user_a;
CREATE ROLE
CREATE TABLE test (id int, data text);
CREATE TABLE
GRANT ALL ON TABLE test TO user_a;
GRANT
GRANT ALL ON SCHEMA public  TO user_a;
GRANT
SET ROLE user_a;
SET
INSERT INTO test VALUES (1,'A'),(2,'B'),(3,'C');
INSERT 0 3
SELECT * FROM test;
 id | data
+--
  1 | A
  2 | B
  3 | C
(3 rows)

CREATE VIEW test_v AS SELECT * FROM test;
CREATE VIEW
CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM test;
SELECT 3
CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test;
SELECT 3
SELECT * FROM test_v;
 id | data
+--
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_mv;
 id | data
+--
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_imv;
 id | data
+--
  3 | C
  1 | A
  2 | B
(3 rows)

RESET ROLE;
RESET
CREATE POLICY test_AAA ON test FOR SELECT TO user_a USING (data = 'A');
CREATE POLICY
ALTER TABLE test ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
SET ROLE user_a;
SET
SELECT * FROM test_v;
 id | data
+--
  1 | A
(1 row)

SELECT * FROM test_mv;
 id | data
+--
  1 | A
  2 | B
  3 | C
(3 rows)

SELECT * FROM test_imv;
 id | data
+--
  3 | C
  1 | A
  2 | B
(3 rows)

REFRESH MATERIALIZED VIEW test_mv;
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW test_imv;
REFRESH MATERIALIZED VIEW
SELECT * FROM test_mv;
 id | data
+--
  1 | A
(1 row)

SELECT * FROM test_imv;
 id | data
+--
  1 | A
(1 row)

RESET ROLE;
RESET
REVOKE ALL ON TABLE test FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_v FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_mv FROM user_a;
REVOKE
REVOKE ALL ON TABLE test_imv FROM user_a;
REVOKE
REVOKE ALL ON SCHEMA public FROM user_a;
REVOKE
DROP TABLE test CASCADE;
psql:rls.sql:40: NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view test_v
drop cascades to materialized view test_mv
drop cascades to materialized view test_imv
DROP TABLE
DROP USER user_a;
DROP ROLE
[ec2-user@ip-10-0-1-10 rls]$

```

Regard.

2018年12月27日(木) 21:57 Yugo Nagata :

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, WITH OIDS will
> be removed since PG12, so OIDs are no longer available. Besides this, it
> would
> be hard to implement this since it needs many changes of executor nodes to
> collect base tables's OIDs during execuing a query. Also, the cost of
> maintaining
> OID map would be high.
>
> For these reasons, we started to think to implement IVM without relying on
> OIDs
> and made a bit more surveys.
>
> We also looked at Kevin Grittner's discussion [4] on incremental matview
> maintenance.  In this discussion, Kevin proposed to use counting algorithm
> [5]
> to handle projection views (using DISTNICT) properly. This algorithm need
> an
> additional system column, count_t, in materialized views and delta tables
> of
> base tables.
>
> However, the discussion about IVM is now stoped, so we would like to
> restart and
> progress this.
>
>
> Through our PoC inplementation and surveys, I think we need to think at
> least
> the followings for implementing IVM.
>
> 1. How to extract changes on base tables
>
> I think there would be at least two approaches for it.
>
>  - Using transition table in AFTER triggers
>  - Extracting changes from WAL using logical decoding
>
> In our PoC imp

Re: Implementing Incremental View Maintenance

2020-01-17 Thread nuko yokohama 
Hi.
I understand.
Even if the function name is min, there is a possibility that it is not an
aggregation operation for finding the minimum value, so it is restricted.
I understood  aggregation of user-defined types is a constraint.

Also, I agree with the error message improvements.

2020年1月17日(金) 17:12 Yugo NAGATA :

> On Thu, 16 Jan 2020 12:59:11 +0900
> nuko yokohama  wrote:
>
> > Aggregate operation of user-defined type cannot be specified
> > (commit e150d964df7e3aeb768e4bae35d15764f8abd284)
> >
> > A SELECT statement using the MIN() and MAX() functions can be executed
> on a
> > user-defined type column that implements the aggregate functions MIN ()
> and
> > MAX ().
> > However, if the same SELECT statement is specified in the AS clause of
> > CREATE INCREMENTAL MATERIALIZED VIEW, the following error will occur.
> >
> > ```
> > SELECT MIN(data) data_min, MAX(data) data_max FROM foo;
> >  data_min | data_max
> > --+--
> >  1/3  | 2/3
> > (1 row)
> >
> > CREATE INCREMENTAL MATERIALIZED VIEW foo_min_imv AS SELECT MIN(data)
> > data_min FROM foo;
> > psql:extension-agg.sql:14: ERROR:  aggregate function min is not
> supported
> > CREATE INCREMENTAL MATERIALIZED VIEW foo_max_imv AS SELECT MAX(data)
> > data_max FROM foo;
> > psql:extension-agg.sql:15: ERROR:  aggregate function max is not
> supported
> > ```
> >
> > Does query including user-defined type aggregate operation not supported
> by
> > INCREMENTAL MATERIALIZED VIEW?
>
> The current implementation supports only built-in aggregate functions, so
> user-defined aggregates are not supported, although it is allowed before.
> This is because we can not know how user-defined aggregates behave and if
> it can work safely with IVM. Min/Max on your fraction type may work well,
> but it is possible that some user-defined aggregate functions named min
> or max behave in totally different way than we expected.
>
> In future, maybe it is possible support user-defined aggregates are
> supported
> by extending pg_aggregate and adding support functions for IVM, but there
> is
> not still a concrete plan for now.
>
> BTW, the following error message doesn't look good because built-in min is
> supported, so I will improve it.
>
>  ERROR:  aggregate function min is not supported
>
> Regards,
> Yugo Nagata
>
> >
> > An execution example is shown below.
> >
> > ```
> > [ec2-user@ip-10-0-1-10 ivm]$ cat extension-agg.sql
> > --
> > -- pg_fraction: https://github.com/nuko-yokohama/pg_fraction
> > --
> > DROP EXTENSION IF EXISTS pg_fraction CASCADE;
> > DROP TABLE IF EXISTS foo CASCADE;
> >
> > CREATE EXTENSION IF NOT EXISTS pg_fraction;
> > \dx
> > \dT+ fraction
> >
> > CREATE TABLE foo (id int, data fraction);
> > INSERT INTO foo (id, data) VALUES (1,'2/3'),(2,'1/3'),(3,'1/2');
> > SELECT MIN(data) data_min, MAX(data) data_max FROM foo;
> > CREATE INCREMENTAL MATERIALIZED VIEW foo_min_imv AS SELECT MIN(data)
> > data_min FROM foo;
> > CREATE INCREMENTAL MATERIALIZED VIEW foo_max_imv AS SELECT MAX(data)
> > data_max FROM foo;
> >
> > SELECT MIN(id) id_min, MAX(id) id_max FROM foo;
> > CREATE INCREMENTAL MATERIALIZED VIEW foo_id_imv AS SELECT MIN(id) id_min,
> > MAX(id) id_max FROM foo;
> > ```
> >
> > Best regards.
> >
> > 2018年12月27日(木) 21:57 Yugo Nagata :
> >
> > > Hi,
> > >
> > > I would like to implement Incremental View Maintenance (IVM) on
> > > PostgreSQL.
> > > IVM is a technique to maintain materialized views which computes and
> > > applies
> > > only the incremental changes to the materialized views rather than
> > > recomputate the contents as the current REFRESH command does.
> > >
> > > I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> > > [1].
> > > Our implementation uses row OIDs to compute deltas for materialized
> > > views.
> > > The basic idea is that if we have information about which rows in base
> > > tables
> > > are contributing to generate a certain row in a matview then we can
> > > identify
> > > the affected rows when a base table is updated. This is based on an
> idea of
> > > Dr. Masunaga [2] who is a member of our group and inspired from
> ID-based
> > > approach[3].
> > >
> > > In our implementation, the mapping of the row OIDs of the materialized
> view
> > > and the base tables are stored in "OID map"

Re: Implementing Incremental View Maintenance

2020-01-16 Thread nuko yokohama 
Error occurs when updating user-defined type columns.

Create an INCREMENTAL MATERIALIZED VIEW by specifying a query that includes
user-defined type columns.
After the view is created, an error occurs when inserting into the view
source table (including the user-defined type column).
```
ERROR:  operator does not exist
```

An execution example is shown below.

```
[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -a -f extension-insert.sql
--
-- pg_fraction: https://github.com/nuko-yokohama/pg_fraction
--
DROP EXTENSION IF EXISTS pg_fraction CASCADE;
psql:extension-insert.sql:4: NOTICE:  drop cascades to column data of table
foo
DROP EXTENSION
DROP TABLE IF EXISTS foo CASCADE;
DROP TABLE
CREATE EXTENSION IF NOT EXISTS pg_fraction;
CREATE EXTENSION
\dx
   List of installed extensions
Name | Version |   Schema   | Description
-+-++--
 pg_fraction | 1.0 | public | fraction data type
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

\dT+ fraction
List of data types
 Schema |   Name   | Internal name | Size | Elements |  Owner   | Access
privileges | Description
+--+---+--+--+--+---+-
 public | fraction | fraction  | 16   |  | postgres |
|
(1 row)

CREATE TABLE foo (id int, data fraction);
CREATE TABLE
INSERT INTO foo (id, data) VALUES (1,'2/3'),(2,'1/3'),(3,'1/2');
INSERT 0 3
SELECT id, data FROM foo WHERE data >= '1/2';
 id | data
+--
  1 | 2/3
  3 | 1/2
(2 rows)

CREATE INCREMENTAL MATERIALIZED VIEW foo_imv AS SELECT id, data FROM foo
WHERE data >= '1/2';
SELECT 2
TABLE foo_imv;
 id | data
+--
  1 | 2/3
  3 | 1/2
(2 rows)

INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6'); -- error
psql:extension-insert.sql:17: ERROR:  operator does not exist: fraction
pg_catalog.= fraction
LINE 1: ...(mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(p...
 ^
HINT:  No operator matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  WITH updt AS (UPDATE public.foo_imv AS mv SET __ivm_count__ =
mv.__ivm_count__ OPERATOR(pg_catalog.+) diff.__ivm_count__  FROM
pg_temp_3.pg_temp_73900 AS diff WHERE (mv.id OPERATOR(pg_catalog.=) diff.id
OR (mv.id IS NULL AND diff.id IS NULL)) AND (mv.data OPERATOR(pg_catalog.=)
diff.data OR (mv.data IS NULL AND diff.data IS NULL)) RETURNING mv.id,
mv.data) INSERT INTO public.foo_imv SELECT * FROM pg_temp_3.pg_temp_73900
AS diff WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE (mv.id
OPERATOR(pg_catalog.=) diff.id OR (mv.id IS NULL AND diff.id IS NULL)) AND
(mv.data OPERATOR(pg_catalog.=) diff.data OR (mv.data IS NULL AND diff.data
IS NULL)));
TABLE foo;
 id | data
+--
  1 | 2/3
  2 | 1/3
  3 | 1/2
(3 rows)

TABLE foo_imv;
 id | data
+--
  1 | 2/3
  3 | 1/2
(2 rows)

DROP MATERIALIZED VIEW foo_imv;
DROP MATERIALIZED VIEW
INSERT INTO foo (id, data) VALUES (4,'2/3'),(5,'2/5'),(6,'3/6');
INSERT 0 3
TABLE foo;
 id | data
+--
  1 | 2/3
  2 | 1/3
  3 | 1/2
  4 | 2/3
  5 | 2/5
  6 | 1/2
(6 rows)

```

Best regards.

2018年12月27日(木) 21:57 Yugo Nagata :

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, WITH OIDS will
> be removed since PG12, so OIDs are no longer available. Besides this, it
> would
> be hard to implement this since it needs many changes of executor nodes to
> collect base tables's OIDs during execuing a que

Re: Implementing Incremental View Maintenance

2020-01-15 Thread nuko yokohama 
Aggregate operation of user-defined type cannot be specified
(commit e150d964df7e3aeb768e4bae35d15764f8abd284)

A SELECT statement using the MIN() and MAX() functions can be executed on a
user-defined type column that implements the aggregate functions MIN () and
MAX ().
However, if the same SELECT statement is specified in the AS clause of
CREATE INCREMENTAL MATERIALIZED VIEW, the following error will occur.

```
SELECT MIN(data) data_min, MAX(data) data_max FROM foo;
 data_min | data_max
--+--
 1/3  | 2/3
(1 row)

CREATE INCREMENTAL MATERIALIZED VIEW foo_min_imv AS SELECT MIN(data)
data_min FROM foo;
psql:extension-agg.sql:14: ERROR:  aggregate function min is not supported
CREATE INCREMENTAL MATERIALIZED VIEW foo_max_imv AS SELECT MAX(data)
data_max FROM foo;
psql:extension-agg.sql:15: ERROR:  aggregate function max is not supported
```

Does query including user-defined type aggregate operation not supported by
INCREMENTAL MATERIALIZED VIEW?

An execution example is shown below.

```
[ec2-user@ip-10-0-1-10 ivm]$ cat extension-agg.sql
--
-- pg_fraction: https://github.com/nuko-yokohama/pg_fraction
--
DROP EXTENSION IF EXISTS pg_fraction CASCADE;
DROP TABLE IF EXISTS foo CASCADE;

CREATE EXTENSION IF NOT EXISTS pg_fraction;
\dx
\dT+ fraction

CREATE TABLE foo (id int, data fraction);
INSERT INTO foo (id, data) VALUES (1,'2/3'),(2,'1/3'),(3,'1/2');
SELECT MIN(data) data_min, MAX(data) data_max FROM foo;
CREATE INCREMENTAL MATERIALIZED VIEW foo_min_imv AS SELECT MIN(data)
data_min FROM foo;
CREATE INCREMENTAL MATERIALIZED VIEW foo_max_imv AS SELECT MAX(data)
data_max FROM foo;

SELECT MIN(id) id_min, MAX(id) id_max FROM foo;
CREATE INCREMENTAL MATERIALIZED VIEW foo_id_imv AS SELECT MIN(id) id_min,
MAX(id) id_max FROM foo;
```

Best regards.

2018年12月27日(木) 21:57 Yugo Nagata :

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, WITH OIDS will
> be removed since PG12, so OIDs are no longer available. Besides this, it
> would
> be hard to implement this since it needs many changes of executor nodes to
> collect base tables's OIDs during execuing a query. Also, the cost of
> maintaining
> OID map would be high.
>
> For these reasons, we started to think to implement IVM without relying on
> OIDs
> and made a bit more surveys.
>
> We also looked at Kevin Grittner's discussion [4] on incremental matview
> maintenance.  In this discussion, Kevin proposed to use counting algorithm
> [5]
> to handle projection views (using DISTNICT) properly. This algorithm need
> an
> additional system column, count_t, in materialized views and delta tables
> of
> base tables.
>
> However, the discussion about IVM is now stoped, so we would like to
> restart and
> progress this.
>
>
> Through our PoC inplementation and surveys, I think we need to think at
> least
> the followings for implementing IVM.
>
> 1. How to extract changes on base tables
>
> I think there would be at least two approaches for it.
>
>  - Using transition table in AFTER triggers
>  - Extracting changes from WAL using logical decoding
>
> In our PoC implementation, we used AFTER trigger and transition tables,
> but using
> logical decoding might be better from the point of performance of base
> table
> modification.
>
> If we can represent a change of UPDATE on a base table as query-like
> rather than
> OLD and NEW, it may be possible to update the materialized view directly
> instead
> of performing delete & insert.
>
>
> 2. How to compute the delta to be applied to materialized views
>
> Essenti

Re: Implementing Incremental View Maintenance

2020-01-10 Thread nuko yokohama 
LIMIT clause without ORDER BY should be prohibited when creating
incremental materialized views.

In SQL, the result of a LIMIT clause without ORDER BY is undefined.
If the LIMIT clause is allowed when creating an incremental materialized
view, incorrect results will be obtained when the view is updated after
updating the source table.

```
[ec2-user@ip-10-0-1-10 ivm]$ psql --version
psql (PostgreSQL) 13devel-ivm-3bf6953688153fa72dd48478a77e37cf3111a1ee
[ec2-user@ip-10-0-1-10 ivm]$ psql testdb -e -f limit-problem.sql
DROP TABLE IF EXISTS test CASCADE;
psql:limit-problem.sql:1: NOTICE:  drop cascades to materialized view
test_imv
DROP TABLE
CREATE TABLE test (id int primary key, data text);
CREATE TABLE
INSERT INTO test VALUES (generate_series(1, 10), 'foo');
INSERT 0 10
CREATE INCREMENTAL MATERIALIZED VIEW test_imv AS SELECT * FROM test LIMIT 1;
SELECT 1
   Materialized view "public.test_imv"
Column |  Type   | Collation | Nullable | Default | Storage  |
Stats target | Description
---+-+---+--+-+--+--+-
 id| integer |   |  | | plain|
 |
 data  | text|   |  | | extended |
 |
 __ivm_count__ | bigint  |   |  | | plain|
 |
View definition:
 SELECT test.id,
test.data
   FROM test
 LIMIT 1;
Access method: heap
Incremental view maintenance: yes

SELECT * FROM test LIMIT 1;
 id | data
+--
  1 | foo
(1 row)

TABLE test_imv;
 id | data
+--
  1 | foo
(1 row)

UPDATE test SET data = 'bar' WHERE id = 1;
UPDATE 1
SELECT * FROM test LIMIT 1;
 id | data
+--
  2 | foo
(1 row)

TABLE test_imv;
 id | data
+--
  1 | bar
(1 row)

DELETE FROM test WHERE id = 1;
DELETE 1
SELECT * FROM test LIMIT 1;
 id | data
+--
  2 | foo
(1 row)

TABLE test_imv;
 id | data
+--
(0 rows)
```

ORDER BY clause is not allowed when executing CREATE INCREMENTAL
MATELIARIZED VIEW.
We propose not to allow LIMIT clauses as well.


2018年12月27日(木) 21:57 Yugo Nagata :

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on
> PostgreSQL.
> IVM is a technique to maintain materialized views which computes and
> applies
> only the incremental changes to the materialized views rather than
> recomputate the contents as the current REFRESH command does.
>
> I had a presentation on our PoC implementation of IVM at PGConf.eu 2018
> [1].
> Our implementation uses row OIDs to compute deltas for materialized
> views.
> The basic idea is that if we have information about which rows in base
> tables
> are contributing to generate a certain row in a matview then we can
> identify
> the affected rows when a base table is updated. This is based on an idea of
> Dr. Masunaga [2] who is a member of our group and inspired from ID-based
> approach[3].
>
> In our implementation, the mapping of the row OIDs of the materialized view
> and the base tables are stored in "OID map". When a base relation is
> modified,
> AFTER trigger is executed and the delta is recorded in delta tables using
> the transition table feature. The accual udpate of the matview is triggerd
> by REFRESH command with INCREMENTALLY option.
>
> However, we realize problems of our implementation. First, WITH OIDS will
> be removed since PG12, so OIDs are no longer available. Besides this, it
> would
> be hard to implement this since it needs many changes of executor nodes to
> collect base tables's OIDs during execuing a query. Also, the cost of
> maintaining
> OID map would be high.
>
> For these reasons, we started to think to implement IVM without relying on
> OIDs
> and made a bit more surveys.
>
> We also looked at Kevin Grittner's discussion [4] on incremental matview
> maintenance.  In this discussion, Kevin proposed to use counting algorithm
> [5]
> to handle projection views (using DISTNICT) properly. This algorithm need
> an
> additional system column, count_t, in materialized views and delta tables
> of
> base tables.
>
> However, the discussion about IVM is now stoped, so we would like to
> restart and
> progress this.
>
>
> Through our PoC inplementation and surveys, I think we need to think at
> least
> the followings for implementing IVM.
>
> 1. How to extract changes on base tables
>
> I think there would be at least two approaches for it.
>
>  - Using transition table in AFTER triggers
>  - Extracting changes from WAL using logical decoding
>
> In our PoC implementation, we used AFTER trigger and transition tables,
> but using
> logical decoding might be better from the point of performance of base
> table
> modification.
>
> If we can represent a change of UPDATE on a base table as query-like
> rather than
> OLD and NEW, it may be possible to update the materialized view directly
> instead
> of performing delete & insert.
>
>
> 2. How to compute the delta to be applied to materialized vie

Re: Implementing Incremental View Maintenance

2019-12-22 Thread nuko yokohama 
SELECT statement that is not IMMUTABLE must not be specified when creating
a view.

An expression SELECT statement that is not IMMUTABLE must not be specified
when creating a view.

In the current implementation, a SELECT statement containing an expression
that is not IMMUTABLE can be specified when creating a view.
If an incremental materialized view is created from a SELECT statement that
contains an expression that is not IMMUTABLE, applying the SELECT statement
to the view returns incorrect results.
To prevent this, we propose that the same error occur when a non-IMMUTABLE
expression is specified in the "CREATE INDEX" statement.

The following is an inappropriate example.

CREATE TABLE base (id int primary key, data text, ts timestamp);
CREATE TABLE
CREATE VIEW base_v AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
CREATE VIEW
CREATE MATERIALIZED VIEW base_mv AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
SELECT 0
CREATE INCREMENTAL MATERIALIZED VIEW base_imv AS SELECT * FROM base
  WHERE ts >= (now() - '3 second'::interval);
SELECT 0
  View "public.base_v"
 Column |Type | Collation | Nullable | Default |
Storage  | Description
+-+---+--+-+--+-
 id | integer |   |  | |
plain|
 data   | text|   |  | |
extended |
 ts | timestamp without time zone |   |  | |
plain|
View definition:
 SELECT base.id,
base.data,
base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);

  Materialized view "public.base_mv"
 Column |Type | Collation | Nullable | Default |
Storage  | Stats target | Description
+-+---+--+-+--+--+-
 id | integer |   |  | |
plain|  |
 data   | text|   |  | |
extended |  |
 ts | timestamp without time zone |   |  | |
plain|  |
View definition:
 SELECT base.id,
base.data,
base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);
Access method: heap

 Materialized view "public.base_imv"
Column |Type | Collation | Nullable |
Default | Storage  | Stats target | Description
---+-+---+--+-+--+--+-
 id| integer |   |  |
  | plain|  |
 data  | text|   |  |
  | extended |  |
 ts| timestamp without time zone |   |  |
  | plain|  |
 __ivm_count__ | bigint  |   |  |
  | plain|  |
View definition:
 SELECT base.id,
base.data,
base.ts
   FROM base
  WHERE base.ts >= (now() - '00:00:03'::interval);
Access method: heap
Incremental view maintenance: yes

INSERT INTO base VALUES (generate_series(1,3), 'dummy', clock_timestamp());
INSERT 0 3
SELECT * FROM base_v ORDER BY id;
 id | data  | ts
+---+
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT * FROM base_mv ORDER BY id;
 id | data | ts
+--+
(0 rows)

REFRESH MATERIALIZED VIEW base_mv;
REFRESH MATERIALIZED VIEW
SELECT * FROM base_mv ORDER BY id;
 id | data  | ts
+---+
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT * FROM base_imv ORDER BY id;
 id | data  | ts
+---+
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

SELECT pg_sleep(3);
 pg_sleep
--

(1 row)

INSERT INTO base VALUES (generate_series(4,6), 'dummy', clock_timestamp());
INSERT 0 3
SELECT * FROM base_v ORDER BY id;
 id | data  | ts
+---+
  4 | dummy | 2019-12-22 11:38:29.381414
  5 | dummy | 2019-12-22 11:38:29.381441
  6 | dummy | 2019-12-22 11:38:29.381444
(3 rows)

SELECT * FROM base_mv ORDER BY id;
 id | data  | ts
+---+
  1 | dummy | 2019-12-22 11:38:26.367481
  2 | dummy | 2019-12-22 11:38:26.367599
  3 | dummy | 2019-12-22 11:38:26.367606
(3 rows)

REFRESH MATERIALIZED VIEW base_mv;
REFRESH MATERIALIZED VIEW
SELECT * FROM bas

Re: Implementing Incremental View Maintenance

2019-12-04 Thread nuko yokohama 
2019年12月3日(火) 14:42 Yugo Nagata :

> On Mon, 2 Dec 2019 13:48:40 -0300
> Alvaro Herrera  wrote:
>
> > On 2019-Dec-02, Yugo Nagata wrote:
> >
> > > On Mon, 02 Dec 2019 10:36:36 +0900 (JST)
> > > Tatsuo Ishii  wrote:
> > >
> > > > >> One thing pending in this development line is how to catalogue
> aggregate
> > > > >> functions that can be used in incrementally-maintainable views.
> > > > >> I saw a brief mention somewhere that the devels knew it needed to
> be
> > > > >> done, but I don't see in the thread that they got around to doing
> it.
> > > > >> Did you guys have any thoughts on how it can be represented in
> catalogs?
> > > > >> It seems sine-qua-non ...
> >
> > > > > In the first option, we support only built-in aggregates which we
> know able
> > > > > to handle correctly. Supported aggregates can be identified using
> their OIDs.
> > > > > User-defined aggregates are not supported. I think this is the
> simplest and
> > > > > easiest way.
> > > >
> > > > I think this is enough for the first cut of IVM. So +1.
> > >
> > > If there is no objection, I will add the check of aggregate functions
> > > by this way. Thanks.
> >
> > The way I imagine things is that there's (one or more) new column in
> > pg_aggregate that links to the operator(s) (or function(s)?) that
> > support incremental update of the MV for that aggregate function.  Is
> > that what you're proposing?
>
> The way I am proposing above is using OID to check if a aggregate can be
> used in IVM. This allows only a part of built-in aggreagete functions.
>
> This way you mentioned was proposed as one of options as following.
>
> On Fri, 29 Nov 2019 17:33:28 +0900
> Yugo Nagata  wrote:
> > Third, we can add a new attribute to pg_aggregate which shows if each
> > aggregate can be used in IVM. We don't need to use names or OIDs list of
> > supported aggregates although we need modification of the system
> catalogue.
> >
> > Regarding pg_aggregate, now we have aggcombinefn attribute for supporting
> > partial aggregation. Maybe we could use combine functions to calculate
> new
> > aggregate values in IVM when tuples are inserted into a table. However,
> in
> > the context of IVM, we also need other function used when tuples are
> deleted
> > from a table, so we can not use partial aggregation for IVM in the
> current
> > implementation. This might be another option to implement "inverse
> combine
> > function"(?) for IVM, but I am not sure it worth.
>
> If we add "inverse combine function" in pg_aggregate that takes two results
> of aggregating over tuples in a view and tuples in a delta, and produces a
> result of aggregating over tuples in the view after tuples in the delta are
> deleted from this, it would allow to calculate new aggregate values in IVM
> using aggcombinefn together when the aggregate function provides both
> functions.
>
> Another idea is to use support functions for moving-aggregate mode which
> are
> already provided in pg_aggregate. However, in this case, we have to apply
> tuples in the delta to the view one by one instead of applying after
> aggregating tuples in the delta.
>
> In both case, we can not use these support functions in SQL via SPI because
> the type of some aggregates is internal. We have to alter the current
> apply_delta implementation if we adopt a way using these support functions.
> Instead, we also can add support functions for IVM independent to partial
> aggregate or moving-aggregate. Maybe this is also one of options.
>
>
> Regards,
> Yugo Nagata
>
> --
> Yugo Nagata 
>
>
>

Re: Implementing Incremental View Maintenance

2019-12-04 Thread nuko yokohama 
Hi.

I found the problem after running "ALTER MATERIALIZED VIEW ... RENAME TO".
If a view created with "CREATE INCREMENT MATERIALIZED VIEW" is renamed,
subsequent INSERT operations to the base table will fail.

Error message.
```
ERROR:  could not open relation with OID 0
```

Execution log.
```
[ec2-user@ip-10-0-1-10 ivm]$ psql -U postgres test -e -f
~/test/ivm/alter_rename_bug.sql
DROP TABLE IF EXISTS table_x CASCADE;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:1: NOTICE:  drop cascades
to materialized view group_imv
DROP TABLE
CREATE TABLE table_x AS
   SELECT generate_series(1, 1) AS id,
   ROUND(random()::numeric * 100, 2) AS data,
   CASE (random() * 5)::integer
 WHEN 4 THEN 'group-a'
 WHEN 3 THEN 'group-b'
 ELSE 'group-c'
   END AS part_key
;
SELECT 1
   Table "public.table_x"
  Column  |  Type   | Collation | Nullable | Default
--+-+---+--+-
 id   | integer |   |  |
 data | numeric |   |  |
 part_key | text|   |  |

DROP MATERIALIZED VIEW IF EXISTS group_imv;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:15: NOTICE:  materialized
view "group_imv" does not exist, skipping
DROP MATERIALIZED VIEW
CREATE INCREMENTAL MATERIALIZED VIEW group_imv AS
SELECT part_key, COUNT(*), MAX(data), MIN(data), SUM(data), AVG(data)
FROM table_x
GROUP BY part_key;
SELECT 3
 List of relations
 Schema |   Name|   Type|  Owner
+---+---+--
 public | group_imv | materialized view | postgres
 public | table_x   | table | postgres
(2 rows)

 Materialized view "public.group_imv"
  Column   |  Type   | Collation | Nullable | Default
---+-+---+--+-
 part_key  | text|   |  |
 count | bigint  |   |  |
 max   | numeric |   |  |
 min   | numeric |   |  |
 sum   | numeric |   |  |
 avg   | numeric |   |  |
 __ivm_count_max__ | bigint  |   |  |
 __ivm_count_min__ | bigint  |   |  |
 __ivm_count_sum__ | bigint  |   |  |
 __ivm_count_avg__ | bigint  |   |  |
 __ivm_sum_avg__   | numeric |   |  |
 __ivm_count__ | bigint  |   |  |

SELECT * FROM group_imv ORDER BY part_key;
 part_key | count |  max  | min  |sum| avg
--+---+---+--+---+-
 group-a  |  1966 | 99.85 | 0.05 |  98634.93 | 50.1703611393692777
 group-b  |  2021 | 99.99 | 0.17 | 102614.02 | 50.7738842157347848
 group-c  |  6013 | 99.99 | 0.02 | 300968.43 | 50.0529569266589057
(3 rows)

ALTER MATERIALIZED VIEW group_imv RENAME TO group_imv2;
ALTER MATERIALIZED VIEW
 List of relations
 Schema |Name|   Type|  Owner
++---+--
 public | group_imv2 | materialized view | postgres
 public | table_x| table | postgres
(2 rows)

Materialized view "public.group_imv2"
  Column   |  Type   | Collation | Nullable | Default
---+-+---+--+-
 part_key  | text|   |  |
 count | bigint  |   |  |
 max   | numeric |   |  |
 min   | numeric |   |  |
 sum   | numeric |   |  |
 avg   | numeric |   |  |
 __ivm_count_max__ | bigint  |   |  |
 __ivm_count_min__ | bigint  |   |  |
 __ivm_count_sum__ | bigint  |   |  |
 __ivm_count_avg__ | bigint  |   |  |
 __ivm_sum_avg__   | numeric |   |  |
 __ivm_count__ | bigint  |   |  |

SET client_min_messages = debug5;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:30: DEBUG:
 CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
SET
INSERT INTO table_x VALUES (1001, ROUND(random()::numeric * 100, 2),
'gruop_d');
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:
 StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS,
xid/subid/cid: 0/1/0
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:  relation
"public.group_imv" does not exist
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: DEBUG:  relation
"public.group_imv" does not exist
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:33: ERROR:  could not
open relation with OID 0
RESET client_min_messages;
psql:/home/ec2-user/test/ivm/alter_rename_bug.sql:34: DEBUG:
 StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS,
xid/subid/cid: 0/1/0
RESET
SELECT * FROM group_imv2 ORDER

To Suggest a "DROP INCREMENTAL MATERIALIZED VIEW" in psql, but the syntax error when you run.

2019-11-27 Thread nuko yokohama 
Hi.

I'm using the "Incremental Materialized View Maintenance" patch and have
reported the following issues.
(https://commitfest.postgresql.org/25/2138/)

To Suggest a "DROP INCREMENTAL MATERIALIZED VIEW" in psql, but the syntax
error when you run.
("DROP MATERIALIZED VIEW" command can drop Incremental Materialozed view
normally.)


ramendb=# CREATE INCREMENTAL MATERIALIZED VIEW pref_count AS SELECT pref,
COUNT(pref) FROM shops GROUP BY pref;
SELECT 48
ramendb=# \d pref_count
  Materialized view "public.pref_count"
Column |  Type  | Collation | Nullable | Default
---++---+--+-
 pref  | text   |   |  |
 count | bigint |   |  |
 __ivm_count__ | bigint |   |  |

ramendb=# DROP IN
INCREMENTAL MATERIALIZED VIEW  INDEX
ramendb=# DROP INCREMENTAL MATERIALIZED VIEW pref_count;
2019-11-27 11:51:03.916 UTC [9759] ERROR:  syntax error at or near
"INCREMENTAL" at character 6
2019-11-27 11:51:03.916 UTC [9759] STATEMENT:  DROP INCREMENTAL
MATERIALIZED VIEW pref_count;
ERROR:  syntax error at or near "INCREMENTAL"
LINE 1: DROP INCREMENTAL MATERIALIZED VIEW pref_count;
 ^
ramendb=# DROP MATERIALIZED VIEW pref_count ;
DROP MATERIALIZED VIEW
ramendb=#


Regard.