Thread: two bugs
The first:
zhxp@THINKPAD-E14:~/repositories/postgres/postgres$ psql
psql (12.14)
Type "help" for help.
postgres=# create table trigtest(i serial primary key);
CREATE TABLE
postgres=# create table trigtestfk(i int references trigtest(i) on delete cascade);
CREATE TABLE
postgres=# insert into trigtest default values;
INSERT 0 1
postgres=# insert into trigtest default values;
INSERT 0 1
postgres=# insert into trigtest default values;
INSERT 0 1
postgres=# select * from trigtest;
i
---
1
2
3
(3 rows)
postgres=# insert into trigtestfk values (1);
INSERT 0 1
postgres=# alter table trigtest disable trigger all;
ALTER TABLE
postgres=# delete from trigtest where i = 1;
DELETE 1
postgres=# select * from trigtest;
i
---
2
3
(2 rows)
postgres=# select * from trigtestfk;
i
---
1
(1 row)
postgres=# \q
zhxp@THINKPAD-E14:~/release/pg$ bin/pg_dump > trigtets.sql
zhxp@THINKPAD-E14:~/release/pg$ bin/createdb trigtest
zhxp@THINKPAD-E14:~/release/pg$ psql trigtest
psql (12.14)
Type "help" for help.
trigtest=# \i /home/zhxp/release/pg/trigtets.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 2
COPY 1
setval
--------
4
(1 row)
ALTER TABLE
psql:/home/zhxp/release/pg/trigtets.sql:113: ERROR: insert or update on table "trigtestfk" violates foreign key constraint "trigtestfk_i_fkey"
DETAIL: Key (i)=(1) is not present in table "trigtest".
trigtest=#
and the second:
zhxp@THINKPAD-E14:~/release/pg$ psql
psql (12.14)
Type "help" for help.
postgres=# create table t1(i int);
CREATE TABLE
postgres=# insert into t1 values (1);
INSERT 0 1
postgres=# create materialized view mv_t1 as select * from t1;
SELECT 1
postgres=# create unique index on mv_t1(i);
CREATE INDEX
postgres=# insert into t1 values (1);
INSERT 0 1
postgres=# select * from t1;
i
---
1
1
(2 rows)
postgres=# select * from mv_t1;
i
---
1
(1 row)
postgres=# \q
zhxp@THINKPAD-E14:~/release/pg$ bin/pg_dump > mvtest.sql
zhxp@THINKPAD-E14:~/release/pg$ bin/createdb mvtest
zhxp@THINKPAD-E14:~/release/pg$ psql mvtest
psql (12.14)
Type "help" for help.
mvtest=# \i /home/zhxp/release/pg/mvtest.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE MATERIALIZED VIEW
ALTER TABLE
COPY 2
CREATE INDEX
psql:/home/zhxp/release/pg/mvtest.sql:67: ERROR: could not create unique index "mv_t1_i_idx"
DETAIL: Key (i)=(1) is duplicated.
mvtest=#
I don't think any of this a bug, it's the expected behavior. It would be better if you also shared some reasoning why you think these are bugs. 1) DISABLE TRIGGER ALL disables all triggers, including those for referential integrity. So it's not surprising some of the data violates the FK on restore. 2) Similarly for the materialized view - you create a unique index on the MV at a point when there's no duplicates. But then you insert data into the source tables. If you tried to refresh the MV at this point, it'd fail - which is expected. And restore first loads all the data and then builds the materialized view. We don't have the capability to dump/restore the MV data directly, we have to run the query. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company