On Mon, Nov 19, 2018 at 6:36 PM Daniel Westermann
<daniel.westermann@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