Re: COPY .. FREEZE, (apparently) not working on 9.6

On Wed, Apr 26, 2017 at 1:40 AM, Danilo Olivares <danilo(at)evicertia(dot)com>
wrote:

> Hello,
>
> While testing "COPY/ FREEZE" feature, I've found a weird issue, by which I
> am unable to actually freeze the rows/tables when using psql 9.6. Or, at
> least, so it looks when inspecting 'xmin'..
>
> Here is the actual (failing) output from running a simple COPY/FREEZE
> snippet against postgres 9.6.2 (x86_64) on CentOS:
>
> # /usr/pgsql-9.6/bin/psql -U postgres -h localhost
> postgres=# BEGIN;
> BEGIN
> postgres=# CREATE TABLE da (text text);
> CREATE TABLE
> postgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);
> sample
> \.
> COPY 1
> postgres=# COMMIT;
> COMMIT
> postgres=# select xmin,xmax from da;
> xmin | xmax
> —----+------
> 1679 | 0
> (1 row)
>
>
> However, this same snipped when run against 9.3.16, actually works, and
> reports the expected xmin=2 value:
>
>
> # /usr/pgsql-9.3/bin/psql -U postgres -h localhost
> postgres=# BEGIN;
> BEGIN
> postgres=# CREATE TABLE da (text text);
> CREATE TABLE
> postgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);
> sample
> \.
> COPY 1
> postgres=# COMMIT;
> COMMIT
> postgres=# select xmin,xmax from da;
> xmin | xmax
> —----+------
> 2 | 0
> (1 row)
>
>
>
> Both databases are just fresh installed, have no other users/sessions
> concurrently, and have the same (default/basic) configuration.
>
> Also, quite a similar issue happens when issuing a "VACUUM FREEZE da"
> after COPY (w/o freeze): on 9.3 rows are frozen (xmin=2), while on 9.6 it
> just like if VACUUM FREEZE is simply ignored.. :?
>

Hi.

As of 9.4 (I think), freezing no longer updates xmin but rather sets a bit
in the infomask. Therefore, testing for 2 is not a valid way to see if a
row is frozen.
--

Vik Fearing +33 6 46 75 15
36http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et
Support