W. A. Sanchez wrote:
> Hi! We have a java program accessing a postgresql 7.1.3 db
> through a connection pool. this morning when i checked the
> db, a number of recent records were missing. however, when i
> did a search on the pgdata/base files, the recent records
> were still there and also in the pg_xlog file but i can't see
> them using psql. Is there a way to fix this like rebuilding the
> pg_xlog perhaps?
>
> Thanks.
>
>
The reason you can find the data but not see the row is that PostgreSQL uses a nonoverwriteing storage manager. This means that when some one updates a row the original row isn't overwriten, but rather that a new row is inserted. There are various fields to tell who can see what row, as if the update where part of a transaction, later parts of the transaction could see the updated row, but other traansactions would see the original until the first row committed (see the manual about MVCC). This is also the case with deletes. Therefore if someone has deleted your row, the data would still be there until you vacuumed. pg_xlog is the WAL (write ahead log) files and shouldn't be fiddled with. These ensure that even if the db where to suffer powerdown any commited transactions would be committed and there couldn't be a problem with a partly written row in a table.
I also heard of a problem with non superusers running vacuum, but I think it just affects 7.2.1 & 7.2.2. What could happen there is that the bits that tell wether a transaction is commited or not could be prematurley removed, therefore leading to a row being thought to not be committed.
hmmm... I do seem to have gone on a bit sorry
hth,
- Stuart