On Mon, 2020-04-13 at 21:11 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/wal-async-commit.html
> Description:
>
> In this page:
> https://www.postgresql.org/docs/current/wal-async-commit.html
>
> In this paragraph:
> Certain utility commands, for instance DROP TABLE, are forced to commit
> synchronously regardless of the setting of synchronous_commit. This is to
> ensure consistency between the server's file system and the logical state of
> the database. The commands supporting two-phase commit, such as PREPARE
> TRANSACTION, are also always synchronous.
>
> > This is to ensure consistency between the server's file system and the
> logical state of the database.
>
> Here I don't understand how inconsistency is introduced, if DROP TABLE or
> PREPARE transactions is not logged.
> Considering drop table is like any other transaction, the user is informed
> that the table is dropped, and the system crashed, it didn't actually
> perform the action of dropping table nor was it logged in WAL, then how it
> would cause inconsistency.
> Isn't it similar to losing any other last transaction, if not logged.
Operations *are* logged even if "synchronous_commit" is "off".
The difference is that the log is not flushed out to disk at commit time.
Imagine you run DROP TABLE, and the transaction commits without flushing
WAL. The file that belongs to the table is removed during the commit.
Now if there is a crash, it could happen that the transaction didn't
happen (because the WAL wasn't persisted yet), but the file is already
gone. This would be data corruption.
Yours,
Laurenz Albe