Vacuum Full does not release the disk size space after delete from table

2018-11-02 Thread Haozhou Wang 
Hi hackers,

We meet a corner case that related to the behavior of Vacuum Full.

There are two SQL scripts

SQL1:
-
-- Test vacuum full

create schema s2;

set search_path to s2;

create table a (i int);

create table b (i int);

insert into a select generate_series(1,5);

delete from a where i > 10;

-- Disk space should release after vacuum full

vacuum full a;

drop table a, b;

reset search_path;

drop schema s2;
-

SQL2:
--
create schema s1;

set search_path to s1;

create table c(i int);

insert into c select generate_series(1,5);

select pg_sleep(5);

reset search_path;

drop schema s1;
---

If we run both sql scripts on same database in parallel, the "VACUUM FULL
a;" will not release the disk space.

But if we run then on same database in sequence, "VACUUM FULL a" can
release the disk space.

We use the PG master branch and the autovacuum has set to off.

Could someone please help us to figure out the root cause of this issue?

Thanks a lot!

-- 
Regards,
Haozhou

Vacuum Full does not release the disk size space after delete from table

2018-11-02 Thread Haozhou Wang 
Hi hackers,

We meet a corner case that related to the behavior of Vacuum Full.

There are two SQL scripts

SQL1:
-
-- Test vacuum full

create schema s2;

set search_path to s2;

create table a (i int);

create table b (i int);

insert into a select generate_series(1,5);

delete from a where i > 10;

-- Disk space should release after vacuum full

vacuum full a;

drop table a, b;

reset search_path;

drop schema s2;
-

SQL2:
--
create schema s1;

set search_path to s1;

create table c(i int);

insert into c select generate_series(1,5);

select pg_sleep(5);

reset search_path;

drop schema s1;
---

If we run both sql scripts on same database in parallel, the "VACUUM FULL
a;" will not release the disk space.

But if we run then on same database in sequence, "VACUUM FULL a" can
release the disk space.

We use the PG master branch and the autovacuum has set to off.

Could someone please help us to figure out the root cause of this issue?

Thanks a lot!

-- 
Regards,
Haozhou

Re: Vacuum Full does not release the disk size space after delete from table

2018-11-02 Thread Tom Lane 
Haozhou Wang  writes:
> We meet a corner case that related to the behavior of Vacuum Full.
> ...
> If we run both sql scripts on same database in parallel, the "VACUUM FULL
> a;" will not release the disk space.

I think what's happening is that the delete in script 1 happens after the
"pg_sleep" in script 2 starts.  Then the pg_sleep has an open snapshot
that could potentially see the deleted rows, so they can't be removed yet.

You could check this theory by changing the vacuum to use VERBOSE, and
seeing what it says about rows that can't be removed yet.

regards, tom lane

Re: Vacuum Full does not release the disk size space after delete from table

2018-11-04 Thread Haozhou Wang 
Thank Tom!
We will check it.

On Fri, Nov 2, 2018 at 10:35 PM Tom Lane  wrote:

> Haozhou Wang  writes:
> > We meet a corner case that related to the behavior of Vacuum Full.
> > ...
> > If we run both sql scripts on same database in parallel, the "VACUUM FULL
> > a;" will not release the disk space.
>
> I think what's happening is that the delete in script 1 happens after the
> "pg_sleep" in script 2 starts.  Then the pg_sleep has an open snapshot
> that could potentially see the deleted rows, so they can't be removed yet.
>
> You could check this theory by changing the vacuum to use VERBOSE, and
> seeing what it says about rows that can't be removed yet.
>
> regards, tom lane
>


-- 
Regards,
Haozhou

Re: Vacuum Full does not release the disk size space after delete from table

2018-11-02 Thread Tom Lane 
Haozhou Wang  writes:
> We meet a corner case that related to the behavior of Vacuum Full.
> ...
> If we run both sql scripts on same database in parallel, the "VACUUM FULL
> a;" will not release the disk space.

I think what's happening is that the delete in script 1 happens after the
"pg_sleep" in script 2 starts.  Then the pg_sleep has an open snapshot
that could potentially see the deleted rows, so they can't be removed yet.

You could check this theory by changing the vacuum to use VERBOSE, and
seeing what it says about rows that can't be removed yet.

regards, tom lane

Re: Vacuum Full does not release the disk size space after delete from table

2018-11-04 Thread Haozhou Wang 
Thank Tom!
We will check it.

On Fri, Nov 2, 2018 at 10:35 PM Tom Lane  wrote:

> Haozhou Wang  writes:
> > We meet a corner case that related to the behavior of Vacuum Full.
> > ...
> > If we run both sql scripts on same database in parallel, the "VACUUM FULL
> > a;" will not release the disk space.
>
> I think what's happening is that the delete in script 1 happens after the
> "pg_sleep" in script 2 starts.  Then the pg_sleep has an open snapshot
> that could potentially see the deleted rows, so they can't be removed yet.
>
> You could check this theory by changing the vacuum to use VERBOSE, and
> seeing what it says about rows that can't be removed yet.
>
> regards, tom lane
>


-- 
Regards,
Haozhou