?????? bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
Hi??hackers I may have discovered another issue in the concurrency scenario of merge, and I am currently not sure if this new issue is related to the previous one. It seems that it may also be an issue with the EPQ mechanism in the merge scenario? I will provide this test case, hoping it will be helpful for you to fix related issues in the future. DROP TABLE IF EXISTS src1, tgt; CREATE TABLE src1 (a int, b text); CREATE TABLE tgt (a int, b text); INSERT INTO src1 SELECT x, 'Src1 '||x FROM generate_series(1, 3) g(x); INSERT INTO tgt SELECT x, 'Tgt '||x FROM generate_series(1, 6, 2) g(x); insert into src1 values(3,'src1 33'); If I only execute merge , I will get the following error: merge into tgt a using src1 c on a.a = c.a when matched then update set b = c.b when not matched then insert (a,b) values(c.a,c.b); -- excute fail ERROR: MERGE command cannot affect row a second time HIINT: Ensure that not more than one source row matches any one target row. But when I execute the update and merge concurrently, I will get the following result set. --session1 begin; update tgt set b = 'tgt333' where a =3; --session2 merge into tgt a using src1 c on a.a = c.a when matched then update set b = c.b when not matched then insert (a,b) values(c.a,c.b); -- excute success --session1 commit; select * from tgt; a | b ---+- 5 | Tgt 5 1 | Src1 1 2 | Src1 2 3 | Src1 3 3 | src1 33 I think even if the tuple with id:3 is udpated, merge should still be able to retrieve new tuples with id:3, andreport the same error as above? Regards, wenjiang zhang ---- ??: "jian he"
?????? bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
Thanks your reply. I understand what you mean and have tried to correct this patch. According to the previous use case, the result obtained is as follows: id | name | year | xmax | xmin | ctid +--+--+--+--+--- 1 | liuwei | 20 | 0 | 859 | (0,1) 2 | zhangbin | 30 | 866 | 866 | (0,7) 3 | fuguo | 44 | 866 | 866 | (0,8) 4 | yihe | 33 | 0 | 865 | (0,6) 4 | yihe | 33 | 0 | 866 | (0,9) (5 rows) At present, the behavior of the number of rows for ??id?? 2 and 3 appears to be normal, but there is duplicate data in the data for ??id?? 4. According to what you said, this is a normal manifestation of transaction isolation level. But there are still differences between the results and those of Oracle(no duplicate data 'id' 4). After that I have tried several scenarios in Oracle and PG: 1??session1?? insert?? session2??merge into?? duplicate data may also occur ??pg and oracle consistent??. 2??session1: update + insert ,session2?? merge into?? there will be no duplicate data in oracle ??pg has duplicate data. It looks like there is an exclusive lock between the update statement and merge statement in oracle. After submitting both update and insert, merge will proceed with locking and execution. (Of course, this is just my guess.) However, it seems that both PG and Oracle have no obvious issues, and their respective designs are reasonable. If I want to get the same results as Oracle, do I need to adjust the lock behavior of the update and merge statements? If I want to achieve the same results as Oracle, can I achieve exclusive locking by adjusting update and merge? Do you have any suggestions? Regards, wenjiang zhang ---- ??: "Dean Rasheed" https://www.postgresql.org/docs/current/transaction-iso.html Regards, Dean
?????? Why is XLOG_FPI_FOR_HINT always need backups?
Thank you for reply. You are right and the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint while data checksum is on. But I wonder whether it is necessary or not while my file system can protect the blocks of database to be torn. And I read a comment in functionMarkBufferDirtyHint: ``` /* * If we need to protect hint bit updates from torn writes, WAL-log a * full page image of the page. This full page image is only necessary * if the hint bit update is the first change to the page since the * last checkpoint. * * We don't check full_page_writes here because that logic is included * when we call XLogInsert() since the value changes dynamically. */ ``` However, the code tell me it has nothing to do with full_page_writes. I can't figure it out. -- Zhang Wenjie -- -- ??: "Japin Li" https://www.postgresql.org/docs/current/runtime-config-wal.html -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
Why is XLOG_FPI_FOR_HINT always need backups?
Hi all, When I read the source code file src/backend/access/transam/xloginsert.c, I get something confused me. In the function XLogSaveBufferForHint, the flags are always REGBUF_FORCE_IMAGE which means it is always need backups. Is it right? Why do not check the full_page_writes? -- Zhang Wenjie check_synchronous_standby_names.patch Description: Binary data
