BUG #14168: ALTER TABLE SET LOGGED failing

From: ganesh(dot)kannan(at)weatheranalytics(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14168: ALTER TABLE SET LOGGED failing
Date: 2016-06-01 04:57:41
Message-ID: 20160601045741.30388.7312@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14168
Logged by: Ganesh Kannan
Email address: ganesh(dot)kannan(at)weatheranalytics(dot)com
PostgreSQL version: 9.5.3
Operating system: Red Hat Enterprise 7.2
Description:

We have a large number of unlogged tables in our database and I am trying to
migrate them over to regular tables. I decided to use 9.5 feature of SET
LOGGED as a quicker way to do this job. However we are running in to space
related issue , where it is not clear why this is happening, and getting
some cryptic lack of space errors. All tables were created using " create
unlogged table ..." command.
e.g.
psql $ alter table schema.test_y1999 set logged ;

psql:mvTS2.sql:6: ERROR: could not extend file
"pg_tblspc/16404/PG_9.5_201510051/16407/61740.1": wrote only 4096 of 8192
bytes at block 175411
HINT: Check free disk space.
Time: 335539.031 ms

However, there is enough free space available on the volumes to accommodate
several of the tables, but ALTER TABLE set logged command consistently
fails, df -h shows this:

>> df -h | grep data5
/dev/mapper/vg_pgdata5-lv_pgdata5 9.8T 9.5T 363G 97% /pg_land_data5

(table size is ~24GB, free space available ~363G)

>> I even validated that this is not a space issue by running the
following:

alter table schema.test_y1999 set tablespace ts_data5 ;
>> succesfuly completes.

(tablespace ts_data5 is mounted on /pg_data5)
As you can see " SET TABLESPACE" behavior is very different than " ... SET
LOGGED" which fails each time.

Can someone help troubleshooting this issue? I am not sure what " ...SET
LOGGED" is checking.

Any help or pointers would be greatly appreciated.

~Ganesh Kannan

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message yancya 2016-06-01 06:59:21 BUG #14169: Incorrect merge join result in 9.5
Previous Message Daniel Migowski 2016-06-01 04:46:50 Re: BUG #14162: No statistics for functions used as aggregates