![]() ()]() 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. Let's assume we have two tables: customer and payment, and in this scenario we want to update a value in the payment table where customer fk in the payment table is equal to customer id and the store_id is 2, setting 33 to staff_id.I recently covered the internals of a row in PostgreSQL, but that was just the storage piece. ![]() This is because PostgreSQL uses the ansi-86 joins syntax in update, MySQL uses the ansi-92 syntax. In short words PostgreSQL is a relational object database management system (DBMS) developed as an open source project, If you want know deeply I recommends to click here.įirst of all the biggest spoiler possible and the sad reality, we can't do this ☹īut calm down, we are programmers and for us everything has a way □īefore we approach the possibilities, let's understand why this happens, since in MySQL we can easily perform this operation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |