2

I'm using a recent version of PostgreSQL on Windows and trying to understand the internal mechanisms arround WAL files.

The scenario I want to test is the following.

  1. Look at the active wal file on the file system
SELECT pg_walfile_name(pg_current_wal_lsn());

Returns "000000010000000000000007"

2. Get is last write time

ls 000000010000000000000007

Returns

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/12/2020     13:05       16777216 000000010000000000000007

3. Insert a record into a table of the database

INSERT INTO person(name)
VALUES('Christopher')

4. Chech again the last write time of the active wal

ls

Returns

    Répertoire : C:\Program Files\PostgreSQL\13\data\pg_wal

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----       27/11/2020     07:45                archive_status
-a----       10/12/2020     11:05       16777216 000000010000000000000007
-a----       06/12/2020     21:15       16777216 000000010000000000000008

The WAL hasn't been updated ! Why ?

None of the file in pg_wal has been updated !

UPDATE
Thanks to your answers, I've understand. Windows seems to be the cause !

If I check the date of the file using

ls

    Répertoire : C:\Program Files\PostgreSQL\13\data\pg_wal

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d-----       27/11/2020     07:45                archive_status
-a----       10/12/2020     11:05       16777216 000000010000000000000007
-a----       06/12/2020     21:15       16777216 000000010000000000000008

BUT the following gives the right time !

ls 000000010000000000000007

Returns

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/12/2020     13:05       16777216 000000010000000000000007

How in the world ?

Laurent GRENIER
  • 612
  • 1
  • 6
  • 13
  • 'last file in the pg_wal directory'. Last alphabetically or last by time? – jjanes Dec 10 '20 at 13:14
  • it's last by time – Laurent GRENIER Dec 10 '20 at 13:42
  • The file system may be configured not to update the timestamp on every write. Updating timestamps on every write doubles the amount of writing which needs to be done. – jjanes Dec 10 '20 at 14:16
  • @jjanes no, the file is correctly updated but the cause is the 'ls' command on windows that returns two differents result depending if you watch an entire folder or a file specifically. – Laurent GRENIER Dec 10 '20 at 14:25

1 Answers1

4

The active WAL segment will get updated. You can get its name with

SELECT pg_walfile_name(pg_current_wal_lsn());

The other WAL segments are either old or held in reserve for the future.

There is no reason for the directory modification timestamp to change if one of the files in it changes.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks for your answer. Graceful to your query I have been able to retrieve the active wal and check it specifically as I've described in my edited answer. – Laurent GRENIER Dec 10 '20 at 14:02