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

>>>

>>>On Mon, 30 May 2016 17:35:34 +0200 (CEST), Daniel Westermann

>>>(daniel.westermann@dbi-services.com) wrote about "[GENERAL] Deleting a
>>>table file does not raise an error when the table is touched
>>>afterwards, why?" (in
>>><184509399.5590018.1464622534207.JavaMail.zimbra@dbi-services.com>):
>>>
>>>[snip]
>>>> Then I delete the file:
>>>>
>>>> postgres@pg_essentials_p1:/u02/pgdata/PG1/base/16422/ [PG1] rm
>>>> 32809
>>>
>>>Actually, you are not deleting the file. You are asking the filesystem
>>>driver to delete it when it has stopped being used. The directory
>>>entry is removed immediately though, so that no other process can open i
>>>t
>>>
>>>> When doing the count(*) on the table again:
>>>>
>>>> (postgres@[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?
>>>
>>>No, the file still exists because a PG back-end still has it open.
>>>
>>>> Is this intended and safe?
>>>
>>>It is standard UNIX behaviour. It is not safe because you are not
>>>supposed to do things that way.
>>>- --
>>>Regards,
>>>
>>>Dave  [RLU #314465]

Thanks all for your answers. Maybe I should have provided more background information: We had an internal workshop today and one of the topics was backup/restore. One of the questions was what will happen if (for any reason) a file gets deleted so we tested it. I am aware that this is not the common use case. But still I want to understand why PostgreSQL works the way described. From the answers I understand this:

- the file is not really deleted because PostgreSQL is still using it => correct?

- if the above is correct why does PostgreSQL only write a partial file back to disk/wal? For me this still seems dangerous as potentially nobody will notice it

- PostgreSQL assumes that someone with write access to the files knows what she/he is doing. ok, but still, in the real world cases like this happen (for whatever reason)

Simon's answer:

- It's a very good thing that we remain flying even with multiple bullet holes in the wings.

Really? It depends on how you look at that, doesn't it? I'd prefer to get an error in this case, maybe I am wrong but I prefer to be noticed if a file is missing instead of getting results

Tom's answer:

- Well, yes, but it would impose huge amounts of overhead in order to raise an error a bit sooner for a stupid user action.  The ideal thing would be to prevent users from breaking their database in the first place --- but there's not much we can do in that direction beyond setting the directory permissions.

Ok, makes sense. But "a bit sooner" to what? The count(*) just returns a result. From a user perspective I have no idea that the result is wrong

Thanks again

Daniel