Deleting a table file does not raise an error when the table is touched afterwards, why?

From: Daniel Westermann <daniel(dot)westermann(at)dbi-services(dot)com>
To: Postgres General Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Deleting a table file does not raise an error when the table is touched afterwards, why?
Date: 2016-05-30 15:35:34
Message-ID: 184509399.5590018.1464622534207.JavaMail.zimbra@dbi-services.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I need to understand something: Lets assume I have a table t5 with 1'000'000 rows:

(postgres(at)[local]:5432) [sample] > select count(*) from t5;
count
---------
1000000
(1 row)

Time: 2363.834 ms
(postgres(at)[local]:5432) [sample] >

I get the file for that table:

postgres(at)pg_essentials_p1:/u02/pgdata/PG1/base/16422/ [PG1] oid2name -d sample -t t5
From database "sample":
Filenode Table Name
----------------------
32809 t5

Then I delete the file:

postgres(at)pg_essentials_p1:/u02/pgdata/PG1/base/16422/ [PG1] rm 32809

When doing the count(*) on the table again:

(postgres(at)[local]:5432) [sample] > select count(*) from t5;
count
---------
1000000
(1 row)

No issue in the log. This is probably coming from the cache, isn't it? Is this intended and safe?

Then I restart the instance and do the select again:

2016-05-30 19:25:20.633 CEST - 9 - 2777 - - @ FATAL: could not open file "base/16422/32809": No such file or directory
2016-05-30 19:25:20.633 CEST - 10 - 2777 - - @ CONTEXT: writing block 8192 of relation base/16422/32809

(postgres(at)[local]:5432) [sample] > select count(*) from t5;
count
--------
437920
(1 row)

Can someone please tell me the intention behind that? From my point of view this is dangerous. If nobody is monitoring the log (which sadly is the case in reality) nobody will notice that only parts of the table are there. Wouldn't it be much more safe to raise an error as soon as the table is touched?

PostgreSQL version:

(postgres(at)[local]:5432) [sample] > select version();
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Thanks in advance
Daniel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2016-05-30 16:05:54 Re: Deleting a table file does not raise an error when the table is touched afterwards, why?
Previous Message CN 2016-05-30 15:21:18 Re: How to find business partners from PostgreSQL communities?