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

On Tue, Apr 25, 2017 at 4:40 PM, Danilo Olivares <danilo@evicertia.com> wrote:

Both databases are just fresh installed, have no other users/sessions
concurrently, and have the same (default/basic) configuration.

I think that it's just due to this:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=37484ad2aacef5ec794f4dd3d5cf814475180a78

Note that this is reflected in the user-visible documentation; that changed too.

--
Peter Geoghegan

VMware vCenter Server
https://www.vmware.com/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Apr 26, 2017 at 1:40 AM, Danilo Olivares <danilo@evicertia.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

On 2017-04-26 01:40:40 +0200, Danilo Olivares 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)

These days xmin still shows the pre-frozen value, even if a tuple is
frozen. That's for forensic purposes, so we can freeze more
aggressively. You'd have to use the pageinspect extension to verify
whether it's actually frozen.

- Andres

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

The value of infomask indicates freezed.

Thanks for your very helpful explanation!!

2017-04-26 1:48 GMT+02:00 Andres Freund <andres@anarazel.de>: