[ADMIN] Why would queries fail with 'could not stat file' after CLUSTER?

[David Schnur]

The software I develop bundles a Postgres 8.3.15 database for storage.
Several users are reporting errors like this:

ProgrammingError: could not stat file "base/16384/52212305.1": Permission
denied

I'm unable to reproduce this myself, but it's clearly a real issue. The
response to all previous reports of this that I can find was 'check your
permissions'. I had the user who reported this most recently do that, and
can't see any permissions issue. There is no AV, backup or indexing task
that should be accessing the cluster. The problem appears exclusively on
Windows, though that might be a coincidence, since most of our users are on
Windows. A directory listing a few hours after the error shows no file
with that name.

Virtually all instances of the problem happen during our weekly maintenance
task. It CLUSTERs about 10-15 tables, then runs various queries, including
selecting pg_database_size and selecting reltuples from pg_class, to assess
the database's contents. The errors happen when running these queries; the
CLUSTERs themselves always succeed. Those same queries are run
successfully at other times during the week, without the preceding CLUSTERs.

Is this a bug? How might I collect more information without shipping the
user a new build? The Postgres log, at least at notice level, doesn't
appear to show anything more useful than the error message.

Thanks,

David

[Raghavendra]

Post by David Schnur
The software I develop bundles a Postgres 8.3.15 database for storage.
ProgrammingError: could not stat file "base/16384/52212305.1": Permission
denied
I'm unable to reproduce this myself, but it's clearly a real issue. The
response to all previous reports of this that I can find was 'check your
permissions'. I had the user who reported this most recently do that, and
can't see any permissions issue. There is no AV, backup or indexing task
that should be accessing the cluster. The problem appears exclusively on
Windows, though that might be a coincidence, since most of our users are on
Windows. A directory listing a few hours after the error shows no file
with that name.

On windows, you face permission issues, I have given a try to reproduce
similar issue. I have taken out complete permission (right click on file
and take security tab and removing permissions) on the test table oid file
from $PGDATA/base/11913. So I got below error.

postgres=# select count(*) from test;
ERROR: could not open file "base/11913/16393": Permission denied

postgres=# cluster test using itest;
ERROR: could not open file "base/11913/16393": Permission denied

Virtually all instances of the problem happen during our weekly maintenance

Post by David Schnur
task. It CLUSTERs about 10-15 tables, then runs various queries, including
selecting pg_database_size and selecting reltuples from pg_class, to assess
the database's contents. The errors happen when running these queries; the
CLUSTERs themselves always succeed. Those same queries are run
successfully at other times during the week, without the preceding CLUSTERs.
CLUSTER in Postgres will create a new relfilenode for each table. I

believe, at the time of new file creation, there might be the permission
issue, which is causing this. Check below, after cluster my test table
relfilenode from 16393 to it has changed to 16401.

postgres=# cluster test using itest;
CLUSTER
postgres=# select relfilenode,relname from pg_class where relname='test';
relfilenode | relname
-------------+---------
16401 | test
(1 row)

Post by David Schnur
Is this a bug? How might I collect more information without shipping the
user a new build? The Postgres log, at least at notice level, doesn't
appear to show anything more useful than the error message.

Bug ? Am not sure... You need to wait for other respected community member
suggestions and proceed accordingly.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[Sashbeer Bhandari]

Hi
Good Morning,

I am using Postgresql DB 8.2 and my encoding is in SQL_ASCII ,. I want to
convert it in UTF8, Please help me it.


Thanks

Sashbeer

Post by David Schnur
The software I develop bundles a Postgres 8.3.15 database for storage.
ProgrammingError: could not stat file "base/16384/52212305.1": Permission
denied
I'm unable to reproduce this myself, but it's clearly a real issue. The
response to all previous reports of this that I can find was 'check your
permissions'. I had the user who reported this most recently do that, and
can't see any permissions issue. There is no AV, backup or indexing task
that should be accessing the cluster. The problem appears exclusively on
Windows, though that might be a coincidence, since most of our users are on
Windows. A directory listing a few hours after the error shows no file
with that name.
Virtually all instances of the problem happen during our weekly
maintenance task. It CLUSTERs about 10-15 tables, then runs various
queries, including selecting pg_database_size and selecting reltuples from
pg_class, to assess the database's contents. The errors happen when
running these queries; the CLUSTERs themselves always succeed. Those same
queries are run successfully at other times during the week, without the
preceding CLUSTERs.
Is this a bug? How might I collect more information without shipping the
user a new build? The Postgres log, at least at notice level, doesn't
appear to show anything more useful than the error message.
Thanks,
David

[raghu ram]

Post by Sashbeer Bhandari
Hi
Good Morning,
I am using Postgresql DB 8.2 and my encoding is in SQL_ASCII ,. I want to
convert it in UTF8, Please help me it.

Convert a database in the SQL_ASCII format into one of the UTF-8 format::

template1=# create database test with encoding='SQL_ASCII';

CREATE DATABASE

template1=# \connect test

You are now connected to database "test".

test=# create table a (x text);

CREATE TABLE

test=# insert into a values ('');

test=# select * from a;

x

--


(1 row)


*test=# update pg_database set encoding =*

*pg_catalog.pg_char_to_encoding('UTF8') where datname='test';*

UPDATE 1

test=# select * from a;

x

--


(1 row)


test=# \connect template1

You are now connected to database "template1".

template1=# \connect test

You are now connected to database "test".

test=# select * from a;

x

--


(1 row)


--Raghu

[Kevin Grittner]

Post by Sashbeer Bhandari
I am using Postgresql DB 8.2 and my encoding is in SQL_ASCII ,. I
want to convert it in UTF8, Please help me it.

This has nothing to do with the thread on which you posted it.
Please start a new thread with an appropriate subject line.

By the way, PostgreSQL version 8.2 is out of support. Perhaps you
should install 9.1 and create a database with the desired encoding,
and make that transition during your upgrade.

-Kevin