BUG #14168: ALTER TABLE SET LOGGED failing
The following bug has been logged on the website:
Bug reference: 14168
Logged by: Ganesh Kannan
Email address: ganesh.kannan@weatheranalytics.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
--
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, 01 Jun 2016 04:57:41 +0000
ganesh.kannan@weatheranalytics.com wrote:
The following bug has been logged on the website:
Bug reference: 14168
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 msHowever, 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
Sometimes the unix error ENOSPC covers more than disk space. For example
you could be out of inodes. What does df -i show?
-dg
--
David Gould 510 282 0869 daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
This is a false alarm . The free space error that I get from " alter table .. set logged" was not because of lack of space in the volumed used by the tablespace, but from the volumes used by the indexes of that table. Given the table was an "unlogged" table, indexes were also "unlogged", and it looks like " alter table ...set logged" also implicitly converts the indexes from "unlogged" to regular indexes.
After clearing enough space " alter table ...set logged" worked fine. It would help to mention the implicit index migration in the documentation.
Thanks David for the response. I did run the "df -i" command, and this is the result.
/dev/mapper/vg_data5-lv_data5 1521588112 9897 1521578215 1% /pg_data5
Thanks
Ganesh Kannan
________________________________________
From: David Gould <daveg@sonic.net>
Sent: Wednesday, June 1, 2016 3:06 AM
To: Ganesh Kannan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14168: ALTER TABLE SET LOGGED failing
On Wed, 01 Jun 2016 04:57:41 +0000
ganesh.kannan@weatheranalytics.com wrote:
The following bug has been logged on the website:
Bug reference: 14168
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 msHowever, 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_data5
Sometimes the unix error ENOSPC covers more than disk space. For example
you could be out of inodes. What does df -i show?
-dg
--
David Gould 510 282 0869 daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs