Hello,
The following bug has been logged on the website:
Bug reference: 16072
Logged by: qiangwei zhu
Email address: zhuqiangwei010@hotmail.com
PostgreSQL version: 10.5
Operating system: "PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled
Description:
my test table struct is:
create table test(
f1 int
)
test table data is:
f1
---------------
1
1
1
1
1
I open two query analyzer,
trans A:
begin TRANSACTION;
delete from test;
INSERT into test(f1)
values(1)
trans B:
begin TRANSACTION;
delete from test;
then commit transA, last commit transB
why in test table has 1 rows at last?
sqlserver 、oracle、mysql database，use same test solution, there was no data
in test table at last.
I suppose you're in the isolation level "read commited" as it's the default. With this level, the database snapshot is made when the query starts its execution. When the DELETE started its execution on transaction B, it didn't know about the value inserted during transaction A as the latter wasn't commited. The DELETE was waiting for a lock on the rows to delete, but the database snapshot already happened. So, when it finally got the locks, it only tried to delete rows existing before the beginning of transaction A, hence without the latest inserted row.
Doesn't look like a bug to me. Seems to be working as intended.
--