I have run into an intermittent problem with PostgreSQL in my application.
My environment is:
Windows XP Pro
Postgresql 7.3.2 (and the 7.3 JDBC driver)
cygwin 1.3.22
ipc-daemon 1.14
The box is a dual Xeon 2.4 GHz with 2 GB RAM.
My application uses two separate databases (one postmaster) and uses them
both heavily. That is, both databases have tables that receive many updates
and inserts (on the order of 10 per second). A few of the tables have over
500,000 rows, and most of the rows are small (almost all are less than 1kB,
a handful are larger.. approaching 1MB).
After several hours of processing, I have seen this error in the postgres
log:
LOG: recycled transaction log file 0000000600000084
LOG: recycled transaction log file 0000000600000085
LOG: recycled transaction log file 0000000600000086
LOG: _mdfd_blind_getseg: couldn't open
/usr/local/pgsql/data/base/2183775/2183797: Permission denied
ERROR: cannot write block 51786 of 2183775/2183797 blind: Permission denied
LOG: _mdfd_blind_getseg: couldn't open
/usr/local/pgsql/data/base/2183775/2183792.3: Permission denied
ERROR: cannot write block 440368 of 2183775/2183792 blind: Permission
denied
LOG: recycled transaction log file 0000000600000087
LOG: recycled transaction log file 0000000600000088
LOG: recycled transaction log file 0000000600000089
As you can see, the two occurrences were back to back. After shutting
down my application and the postmaster and then restarting, I have not been
able to reproduce the error. Unfortunately, I do not think the answer is as
simple as the file permissions, the files in the data/base dirs are all 600
for the owner of postmaster. The disk is not full either -- the databases
look like they take about 3.5GB on disk (du -sk in the data/base dir), and I
still have 60GB free.
However, I do not know if this is Windows' way of reporting to
cygwin/postgres that a file locking problem occurred.
Or, perhaps, trying to say something about available file handles or file
size. It would appear that postgreSQL starts a new file (with the .x
extension) when a single file reaches 1 GB.
I apologize for not having a consistent method of reproducing the
problem... I'm working on it. :-)
I just thought I'd take a shot in the dark, and see if this error message
meant more to someone else than it does to me. The only thing I found on
the archives mentioning this error was this thread:
http://archives.postgresql.org/pgsql-cygwin/2003-02/msg00037.php
Please let me know if there is a better forum for this problem.
Thanks in advance,
Brian
