I am getting the following error on one query in Postgres:

select * from sampletable where sample_id = 1234;

Error is :

ERROR: could not access status of transaction <some-id> 
Detail: CLOG segment 42 does not exist: No such file or directory. 

But when I am trying to run other queries on the same table, it works fine. Eg.

select * from sampletable where sample_id = 4321;

So, its happening for a particular value of a sample_id only.

What could be the cause of it and how can I fix it? Any suggestions.

  • What version of PostgreSQL? Have you tried restarting the database? – Evan Carroll Jan 18 at 18:11
  • Its 9.6.8 version of PostgreSQL – hatellla Jan 18 at 18:13
  • You have some sort of corruption, but it is not entirely clear what caused it. Hardware failures? Running with fsync=off? Software bugs? Has the system ever been upgraded using pg_upgrade, and if so what was the original version? See wiki.postgresql.org/wiki/Corruption – jjanes Jan 18 at 18:24

CLOG is the PostgreSQL Commit Log Manager (CLog). It's telling you here that the record of transactions is fuxed. This may have been caused from any kind of corruption. I would back up the database before attempting anything. There is one suggestion the lists,

There is a commit log corruption in your database. You need to make the files with right size (256K of zeroes). A suitable dd from /dev/zero will accomplish this on modern Unixen (ie, anything that has /dev/zero). Filling of those files with zeroes nothing but "all those old transactions are aborted".

The safest way to resolve the issue by using the below command:-

dd if=/dev/zero of=Transaction_missing_file_of pg_clog bs=256K count=1

(To make the uncommitted record as they haven't been committed).

This is followed up with the amusing reply,

Just for the record, playing with pg_clog files like this is an insanely unsafe thing to do. Notably it will make all aborted transactions in that range become committed, leading to all sorts of fun. This is a desperate measures only action and not one that looks immediately related to the problem.

I would try backing up, upgrading to PostgreSQL 11, running VACUUM FULL, and restarting before I messed around with this in 9.6. But I would give it a shot if that failed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.