transaction lost when delete clog file after normal shutdown

From: 章晨曦(at)易景科技 <zhangchenxi(at)halodbtech(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: transaction lost when delete clog file after normal shutdown
Date: 2024-12-23 04:16:37
Message-ID: tencent_71E752E36B9AB86C6F093005@qq.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi there,

I notice a little strange things of clog behaviours.&nbsp;

When I create a test table, say, a_test, the table only contains a INT type column.

postgres=# CREATE TABLE a_test (n INT);

CREATE TABLE

and then insert one tuple:

postgres=# INSERT INTO a_test VALUES (1);

INSERT 0 1

And then quit, shutdown the database.
[jet(at)halodev-jet-01 pg_xact]$ pg_ctl stopwaiting for server to shut down....2024-12-23 12:05:46.568 CST [1340130] LOG: &nbsp;received fast shutdown request
2024-12-23 12:05:46.577 CST [1340130] LOG: &nbsp;aborting any active transactions
2024-12-23 12:05:46.579 CST [1340130] LOG: &nbsp;background worker "logical replication launcher" (PID 1340136) exited with exit code 1
2024-12-23 12:05:46.579 CST [1340131] LOG: &nbsp;shutting down
2024-12-23 12:05:46.583 CST [1340131] LOG: &nbsp;checkpoint starting: shutdown immediate
2024-12-23 12:05:46.604 CST [1340131] LOG: &nbsp;checkpoint complete: wrote 21 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.011 s, total=0.025 s; sync files=17, longest=0.005 s, average=0.001 s; distance=88 kB, estimate=88 kB; lsn=0/1554658, redo lsn=0/1554658
2024-12-23 12:05:46.607 CST [1340130] LOG: &nbsp;database system is shut down
&nbsp;done

server stopped

After database stopped, change to the pg_xact directory, and delete the clog file, and then create a dummy clog file.
[jet(at)halodev-jet-01 pg_xact]$ ls -ltrtotal 8
-rw-rw-r--. 1 jet jet 8192 Dec 23 12:05 0000
[jet(at)halodev-jet-01 pg_xact]$ rm -f 0000
[jet(at)halodev-jet-01 pg_xact]$ dd if=/dev/zero of=./0000 bs=1024 count=8
8+0 records in
8+0 records out

8192 bytes (8.2 kB, 8.0 KiB) copied, 8.5894e-05 s, 95.4 MB/s

And then start the database.
[jet(at)halodev-jet-01 pg_xact]$ pg_ctl startwaiting for server to start....2024-12-23 12:06:01.935 CST [1340148] LOG: &nbsp;starting PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
2024-12-23 12:06:01.936 CST [1340148] LOG: &nbsp;listening on IPv6 address "::1", port 5432
2024-12-23 12:06:01.936 CST [1340148] LOG: &nbsp;listening on IPv4 address "127.0.0.1", port 5432
2024-12-23 12:06:01.943 CST [1340148] LOG: &nbsp;listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-12-23 12:06:01.950 CST [1340151] LOG: &nbsp;database system was shut down at 2024-12-23 12:05:46 CST
2024-12-23 12:06:01.956 CST [1340148] LOG: &nbsp;database system is ready to accept connections
&nbsp;done

server started

The database started normally. But the INSERT transaction was lost:
[jet(at)halodev-jet-01 pg_xact]$ psqlpsql (16.6)
Type "help" for help.

postgres=# SELECT * FROM a_test;
&nbsp;n
---
(0 rows)

postgres=#

BUT! If I do the same steps above, and the only difference is after INSERT,&nbsp; I just do a simple query SELECT * FROM a_test; the INSERT transaction still exists. How could this happen? What's the difference after SELECT?
[jet(at)halodev-jet-01 pg_xact]$ psqlpsql (16.6)
Type "help" for help.

postgres=# INSERT INTO a_test VALUES (1);
INSERT 0 1
postgres=# SELECT * FROM a_test;
&nbsp;n
---
&nbsp;1
(1 row)

postgres=# \q
[jet(at)halodev-jet-01 pg_xact]$ pg_ctl stop
waiting for server to shut down....2024-12-23 12:13:36.577 CST [1340148] LOG: &nbsp;received fast shutdown request
2024-12-23 12:13:36.583 CST [1340148] LOG: &nbsp;aborting any active transactions
2024-12-23 12:13:36.584 CST [1340148] LOG: &nbsp;background worker "logical replication launcher" (PID 1340154) exited with exit code 1
2024-12-23 12:13:36.585 CST [1340149] LOG: &nbsp;shutting down
2024-12-23 12:13:36.587 CST [1340149] LOG: &nbsp;checkpoint starting: shutdown immediate
2024-12-23 12:13:36.601 CST [1340149] LOG: &nbsp;checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.006 s, total=0.017 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=0/15548E8, redo lsn=0/15548E8
2024-12-23 12:13:36.603 CST [1340148] LOG: &nbsp;database system is shut down
&nbsp;done
server stopped
[jet(at)halodev-jet-01 pg_xact]$ rm -f 0000
[jet(at)halodev-jet-01 pg_xact]$ dd if=/dev/zero of=./0000 bs=1024 count=8
8+0 records in
8+0 records out
8192 bytes (8.2 kB, 8.0 KiB) copied, 8.9862e-05 s, 91.2 MB/s
[jet(at)halodev-jet-01 pg_xact]$ pg_ctl start
waiting for server to start....2024-12-23 12:13:48.430 CST [1340205] LOG: &nbsp;starting PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
2024-12-23 12:13:48.430 CST [1340205] LOG: &nbsp;listening on IPv6 address "::1", port 5432
2024-12-23 12:13:48.430 CST [1340205] LOG: &nbsp;listening on IPv4 address "127.0.0.1", port 5432
2024-12-23 12:13:48.437 CST [1340205] LOG: &nbsp;listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-12-23 12:13:48.445 CST [1340208] LOG: &nbsp;database system was shut down at 2024-12-23 12:13:36 CST
2024-12-23 12:13:48.451 CST [1340205] LOG: &nbsp;database system is ready to accept connections
&nbsp;done
server started
[jet(at)halodev-jet-01 pg_xact]$ psql
psql (16.6)
Type "help" for help.

postgres=# SELECT * FROM a_test;
&nbsp;n
---
&nbsp;1
(1 row)

Jet

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2024-12-23 04:56:24 Re: attndims, typndims still not enforced, but make the value within a sane threshold
Previous Message Tom Lane 2024-12-23 04:15:31 Re: Fix a wrong comment in load_file()