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

On Mon, May 30, 2016 at 11:35 AM, Daniel Westermann <
daniel(dot)westermann(at)dbi-services(dot)com> wrote:

> 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
>
>
I have not heard of pg_essentials, but obviously it is external to the
PostgreSQL server.
PostgreSQL cannot tell is someone is intentionally messing with the file
system. You have removed only the first file node with rm 32809.
*First off, you should never do that.* *If you want to drop the table, then
do DROP TABLE t5;*
*That will drop all the file nodes for that table.*
*You may as well ask "If I shoot myself in the head, why don't I feel any
pain?"*.
*You could also do rm -r *.* if you really want to screw the pooch.* *The
O/S won't complain, but you will be very sorry!*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.