![]() INSERT INTO data VALUES (2, 'new') RETURNING xmin Ĭausing trouble to facilitate transaction ID wraparound INSERT INTO data VALUES (1, 'hello') RETURNING xmin ĭELETE FROM data WHERE id = 1 RETURNING xmax ![]() It is very important that we don’t SELECT from the table at this stage, since that would set hint bits, which would spoil the effect.ĬREATE TABLE data (id integer PRIMARY KEY, x text) For detailed information about xmax, you can read my article on that topic. Similarly, I’ll return xmax for all deleting statements. I’ll return xmin for each row I create, which stores the transaction ID of the creating transaction. Now we can create some data (that we will destroy later). That prepared transaction will later be used to stir trouble. I will use a free, non-standard port and allow a prepared transaction. Since I will cause data corruption, I’ll create a new cluster that I can discard afterwards: But don’t panic: usually, you won’t even notice when your transaction IDs wrap around. Still, these protections can disrupt operation, so it is good to be aware of the problem. As you will see, I have to use evil tricks to overcome those protections. There are strong protections in PostgreSQL to prevent that. Is data loss from transaction ID wraparound a real danger? ![]() If you are curious, come along on this trip! We’ll do some scary things that you shouldn’t do to your databases at home. Many people know horror stories about anti-wraparound autovacuum tanking performance or databases that stop working, but who has ever seen actual data loss? I decided to face the beast ( having developed a fondness for breaking things), and I learned something along the way. ![]() But for most people it is an abstract concept, a bogeyman lurking in the dark around the corner. The concept has been well explained in Hans’ article, so I won’t repeat all that here. Most people are aware of transaction ID wraparound. Corruption pg_resetwal pg_surgery postgresql prepared transaction single-user mode wraparound ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |