Lock and pg_stat

["Desbiens, Eric" <edesbiens@xxxxxxxxxx>]

- I have a problem with some files on a postgresql  9.0 on windows:

 

2013-05-10 12:49:08 EDT ERROR:  could not stat file "base/773074/30352481": Permission denied

2013-05-10 12:49:08 EDT STATEMENT:  SELECT pg_database_size($1) AS size;

 

I know what does it means: the statistic pooler can`t access the file.

It is a only database server without antivirus (but on a windows cluster machine)

 

- on disk, the file is shown as a 0-octet file, and there is no security tab when I try to get information.

It looks like this file has been created, opened, and not yet close (or written)

 

- when I try to get more information on the file with `oid2name` it is unable to give me information:

S:\PostgreSQL\9.0\data\base>"C:\Program Files\PostgreSQL\9.0\bin\oid2name.exe" -

U postgres -d mydb -f 30352481

Password:

From database "lxcal":

  Filenode  Table Name

----------------------

 

Certainly because the pg_stat worker can access it, so don`t have info on it?

 

I tried also:

  select * from pg_class where oid=30352481;

but didn't got anything

 

- This same file is owned by a postgresql backend thread (with `process explorer`) I see that the file is owned by a postgresql --forkbackend with pid 3520

I tried to see what the 3520 process is doing. It is in "<IDLE>"

It is not statistic worker (it is not "postgresql --backcol")

 

I thought it was maybe a file locked, so I check pg_locks with:

 

select pg_class.relname, pg_locks.virtualtransaction, pg_locks.mode, pg_locks.granted as "g",

substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start,

age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid

from pg_stat_activity,pg_locks

left outer join pg_class on

(pg_locks.relation = pg_class.oid)

where pg_locks.pid=pg_stat_activity.procpid

order by query_start;

 

my process (pid 3520) is not listed has having lock.

 

How can I debug to know what is going on?

This message may contain confidential and privileged information. If it has been sent to you in error, please reply to advise the sender of the error and then immediately delete it. If you are not the intended recipient, do not read, copy, disclose or otherwise use this message. The sender disclaims any liability for such unauthorized use. PLEASE NOTE that all incoming e-mails sent to Weatherford e-mail accounts will be archived and may be scanned by us and/or by external service providers to detect and prevent threats to our systems, investigate illegal or inappropriate behavior, and/or eliminate unsolicited promotional e-mails (spam). This process could result in deletion of a legitimate e-mail before it is read by its intended recipient at our organization. Moreover, based on the scanning results, the full text of e-mails and attachments may be made available to Weatherford security and other personnel for review and appropriate action. If you have any concerns about this process, please contact us at dataprivacy@xxxxxxxxxxxxxxx.