Thread: [BUGS] COPY .. FREEZE, (apparently) not working on 9.6
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 localhostpostgres=# BEGIN;BEGINpostgres=# CREATE TABLE da (text text);CREATE TABLEpostgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);sample\.COPY 1postgres=# COMMIT;COMMITpostgres=# 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 localhostpostgres=# BEGIN;BEGINpostgres=# CREATE TABLE da (text text);CREATE TABLEpostgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);sample\.COPY 1postgres=# COMMIT;COMMITpostgres=# 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.. :?
Regards
Danilo Olivares
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:
Hi.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 localhostpostgres=# BEGIN;BEGINpostgres=# CREATE TABLE da (text text);CREATE TABLEpostgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);sample\.COPY 1postgres=# COMMIT;COMMITpostgres=# 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 localhostpostgres=# BEGIN;BEGINpostgres=# CREATE TABLE da (text text);CREATE TABLEpostgres=# COPY da FROM STDIN WITH (DELIMITER ',', FREEZE);sample\.COPY 1postgres=# COMMIT;COMMITpostgres=# 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.. :?
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 36 http://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>:
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