| From: | friend(dot)have_00(at)icloud(dot)com | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Could not read block 0 in file | 
| Date: | 2022-04-08 07:01:57 | 
| Message-ID: | 5D839689-C2C9-437E-AD6B-CD744AC3F489@icloud.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi,
While we are looking for a suitable backup to recover from, I hope this community may have some other advice on forward steps in case we cannot restore.
RCA: Unexpected shutdown due to critical power failure
Current Issue: The file base/16509/17869 is zero bytes in size.
Additional Information:
Platform: Windows Server
PostGres Version: 10.16 (64-bit)
The database does start, and is otherwise functioning and working aside from a particular application feature that relies on the lookup of the values in the table that was held in the currently zero-bytes data file.
The non-functioning table (ApprovalStageDefinition) is a relatively simple table with 5 rows of static data. The contents can easily be recovered with a query such as the following for each of the 5 records:
insert into ApprovalStageDefinition values (1, 'Stage One', 'Stage One');
The error message when running this query is:
ERROR:  could not read block 0 in file "base/16509/17869": read only 0 of 8192 bytes
The file does exist on the file system, with zero bytes, as do the associated fsm and vm files.
PostGres does allow us to describe the table:
 \d ApprovalStageDefinition;
           Table "public.approvalstagedefinition"
      Column       |  Type  | Collation | Nullable | Default
-------------------+--------+-----------+----------+---------
 stageid           | bigint |           | not null |
 stagename         | citext |           | not null |
 internalstagename | citext |           | not null |
Indexes:
    "approvalstagedef_pk" PRIMARY KEY, btree (stageid)
    "approvalstagedefinition_uk1" UNIQUE CONSTRAINT, btree (stagename)
    "approvalstagedefinition_uk2" UNIQUE CONSTRAINT, btree (internalstagename)
Check constraints:
    "approvalstagedefinition_internalstagename_c" CHECK (length(internalstagename::text) <= 100)
    "approvalstagedefinition_stagename_c" CHECK (length(stagename::text) <= 100)
Referenced by:
    TABLE "approvaldetails" CONSTRAINT "approvaldetails_fk5" FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
    TABLE "currentapprovalstage" CONSTRAINT "currentapprovalst_fk1" FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
    TABLE "serviceapprovermapping" CONSTRAINT "serviceapprovermapping_fk4" FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
Desired Solution:
A way to recreate the data file based on the existing schema so that we can then insert the required records.
Challenges/Apprehensions:
I am a PostGres novice, and reluctant to try dropping the table and recreating it based on the existing schema as I don’t know what else it may break, especially with regards to foreign keys and references.
Any constructive advice would be appreciated.
Thank you
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PALAYRET Jacques | 2022-04-08 08:29:06 | PostgreSQL : error hint for LATERAL join | 
| Previous Message | Nikolay Samokhvalov | 2022-04-08 05:57:18 | Re: What have I done!?!?!? :-) |