![]() Refer to htup.h for more info about the bitmap meanings. But, once I applied the bitmap deciphering technology that I call “swear repeatedly”, I was able to determine that this row was HEAP_HOT_UPDATED and contains 10 attributes. Looking at t_infomask2 on row 2 we can immediately see two things… I lied, I can’t immediately see anything apart from some large number. lp 2 is the first update to Mary’s email address. The row with lp 1 is the row that was originally on this page before we started messing around with Mary Smith’s email address. ```There are three rows listed on the page. Select * from heap_page_items(get_raw_page(‘customer’, 8)) We can see the same information much more legibly by using the `heap_page_items` function: We can even see the original row in the hex dump from page 8. If we did back in and look at the row, we’ll see that the`t_ctid` value in Mary Smith’s record at page 8, slot 2 is updated from `00 00 00 00 00 00` to `00 00 00 08 00 03`. ()]() If we update the row again, we’ll see that it moves to a new position on the page, from (8,2) to (8,3). Eventually this old row (commonly called a dead tuple) will be cleaned up by the vacuum process. This tells us that if we look on page 8 in position 2 we’ll find the newest version of the data that corresponds to Mary Smith. However, PostgreSQL has marked the row as being “old” by setting the `xmax` value as well as setting the `t_ctid` value to `00 00 00 08 00 02`. ()]() Looking at page 0 of the customer table, we can see that the original row is still present. Looking at rows during the update process is interesting, but the after effects are much more interesting. This is important because we want to look at what’s going on with the row _after_ the update is complete. We’re going to dig into this in a minute, but for now go ahead and commit that first transaction. ()]() Astute readers will have noticed that the row is on disk in two places at the same time. This shows us the range of transactions where this row is valid. We can also see that both the `xmin` and `xmax` columns now have values. You’ll see that the row is still there with all of the original data present the email address hasn’t changed. In another query window, run the previous select again. Transactions with a transaction id lower than `xmin`won’t be able to see the row. The other interesting thing to note is that the`xmin` value has changed. ()]() After running this, we can see that the customer’s row has moved off of page 0 and is now on page 8 in slot 2. SELECT ctid, xmin, xmax, * FROM customer WHERE customer_id = 1 In the current query window, run the following command: ()]() Where did her data go? Interestingly enough, it’s in two places right now because we haven’t committed the transaction. I use psql on the command prompt, but there are plenty of great tools out there.```ĭon’t commit the transaction yet! When we go to look for Mary’s data using the first `select` ordered by `ctid`, we won’t see her data anywhere. Open up a connection to PostgreSQL using your favorite interactive querying tool. Let’s see what happens when we update some of Mary’s data. We already know that Mary’s row is in page 0, position 1 because of the ctidwe retrieved in our first query. Why is Mary’s data the first row in the table but the last entry on the page? PostgreSQL starts writing data from the end of the page but writes item identifiers from the beginning of the page. Mary Smith’s data is at the end of the page. We can take a look at the row on the disk using the get_raw_page function to examine page 0 of the customer table. Now that we know who we’re going to update, we can go ahead and mess around with the data. ![]() Using that select statement, we can see that Mary Smith’s data lives on page 0 and in row 1 Updating a Row We’re going to be looking at the customer with a customer_id of 1: Mary Smith. I did this by running: SELECT ctid, * FROM customer ORDER BY ctid LIMIT 10 This gives us the primary key of a customer to mess with as well as the location of the row on disk. The first thing I did was retrieve the ctid along with the rest of the data in the row. Since the goal is to look at an existing row, update it, and then see what happens to the row, we’ll need to be able to locate the row again. The first trick was to find a customer to update. Rather than come up with a set of sample data, I figured it would be easy to work within an existing set of data. I started by using the customer table from the pagila sample database. There are a lot of complexities to data, after all, and it’s nice to know how our database is going to be affected by updates. I got more curious and decided that I would look into what happens when a row gets updated. I recently covered the internals of a row in PostgreSQL, but that was just the storage piece. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |