On Mon, Nov 19, 2018 at 6:36 PM Daniel Westermann <daniel.westerm...@dbi-services.com> wrote: > > >Yes, we need to connect to the database for performing rollback > >actions. Once the rollback for that database is over, undo apply > >worker will exit and you should be able to drop the database. > > Thank you, Amit. > Can you have a look at this one? > > create table t1 ( a text ) partition by list (a); > create table t1_1 PARTITION of t1 (a) for values in ('a'); > create table t1_2 PARTITION of t1 (a) for values in ('b'); > create table t1_3 PARTITION of t1 (a) for values in ('c'); > create table t1_4 PARTITION of t1 (a) default; > > postgres=# \d+ t1 > Table "public.t1" > Column | Type | Collation | Nullable | Default | Storage | Stats target | > Description > --------+------+-----------+----------+---------+----------+--------------+------------- > a | text | | | | extended | | > Partition key: LIST (a) > Partitions: t1_1 FOR VALUES IN ('a'), > t1_2 FOR VALUES IN ('b'), > t1_3 FOR VALUES IN ('c'), > t1_4 DEFAULT > Options: storage_engine=zheap > > > insert into t1 select 'a' from generate_series ( 1, 1000000 ); > insert into t1 select 'b' from generate_series ( 1, 1000000 ); > insert into t1 select 'c' from generate_series ( 1, 1000000 ); > > postgres=# begin; > BEGIN > postgres=# update t1 set a = 'd' where a = 'a'; > UPDATE 1000000 > postgres=# rollback; > ROLLBACK >
Here, you are doing a big rollback, so I guess it will be pushed to background unless you increase the value of 'rollback_overflow_size'. You can confirm that by checking if any undo apply worker is active and rollback finishes immediately. > postgres=# select * from t1 where a = 'd'; > postgres=# select * from t1 where a = 'd'; > postgres=# select * from t1 where a = 'd'; > > The selects at the end take seconds > I think what is happening is as rollback is still in progress, the scan needs to fetch the data from undo and it will be slow. > and a lot of checkpoints are happening. > It is because Rollbacks also write WAL and you are doing a big Rollback which will lead to re-write of the entire table. I guess if you allow rollback to complete before issuing a select, you will see better results. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com