Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

On Wed, Mar 13, 2019 at 11:37 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
wrote:

>
>
> I think that since COPY FREEZE can be executed only when the table is
> created or truncated within the transaction other users cannot insert
> any rows during COPY FREEZE.
>
>
Right. But the truncating transaction can insert unfrozen rows into the
table before inserting more rows via COPY FREEZE.

postgres=# CREATE EXTENSION pageinspect ;
CREATE EXTENSION
postgres=# BEGIN;
BEGIN
postgres=# TRUNCATE testtab ;
TRUNCATE TABLE
postgres=# INSERT INTO testtab VALUES (100, 200);
INSERT 0 1
postgres=# COPY testtab FROM STDIN WITH (FREEZE);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1 2
>> 2 3
>> \.
COPY 2
postgres=# COMMIT;

postgres=# SELECT lp, to_hex(t_infomask) FROM
heap_page_items(get_raw_page('testtab', 0));
lp | to_hex
----+--------
1 | 800
2 | b00
3 | b00
(3 rows)

The first row in inserted by regular insert and it's not frozen. The next 2
are frozen. We can't mark such as page all-visible, all-frozen.

>
> I'd suggest to measure performance overhead. I can imagine one use
> case of COPY FREEZE is the loading a very large table. Since in
> addition to set visibility map bits this patch could scan a very large
> table I'm concerned that how much performance is degraded by this
> patch.
>

Ok. I will run some tests. But please note that this patch is a bug fix to
address the performance issue that is caused by having to rewrite the
entire table when all-visible bit is set on the page during first vacuum.
So while we may do some more work during COPY FREEZE, we're saving a lot of
page writes during next vacuum. Also, since the scan that we are doing in
this patch is done on a page that should be in the buffer cache, we will
pay a bit in terms of CPU cost, but not anything in terms of IO cost.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services