Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOSNU-0005IO-90 for pgsql-admin@arkaria.postgresql.org; Sun, 18 Nov 2018 19:05:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gOSNS-0005B6-BV for pgsql-admin@arkaria.postgresql.org; Sun, 18 Nov 2018 19:05:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOSNS-0005Ak-0C for pgsql-admin@lists.postgresql.org; Sun, 18 Nov 2018 19:05:02 +0000 Received: from mx1.mailbox.org ([2001:67c:2050:104:0:1:25:1]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOSNO-0005qV-6b for pgsql-admin@lists.postgresql.org; Sun, 18 Nov 2018 19:05:00 +0000 Received: from smtp2.mailbox.org (unknown [IPv6:2001:67c:2050:105:465:1:2:0]) (using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits)) (No client certificate requested) by mx1.mailbox.org (Postfix) with ESMTPS id 9C4944A381; Sun, 18 Nov 2018 20:04:51 +0100 (CET) X-Virus-Scanned: amavisd-new at heinlein-support.de Received: from smtp2.mailbox.org ([80.241.60.241]) by spamfilter02.heinlein-hosting.de (spamfilter02.heinlein-hosting.de [80.241.56.116]) (amavisd-new, port 10030) with ESMTP id dKTtXZIQ06b7; Sun, 18 Nov 2018 20:04:50 +0100 (CET) Message-ID: <8981328ca8d9ad247ecd203c9f9f206fb63b9e30.camel@cybertec.at> Subject: Re: unclear behavior xmax/xmin From: Laurenz Albe To: Mariel Cherkassky , pgsql-admin@lists.postgresql.org Date: Sun, 18 Nov 2018 20:04:49 +0100 In-Reply-To: References: Organization: Cybertec Content-Type: text/plain; charset="UTF-8" Mime-Version: 1.0 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Mariel Cherkassky wrote: > - I started transaction A and deleted one row but I didnt commit. > Afterwards I rolledback the transaction but the xmax of the row was set > to the txid of transaction A. I opened a new session and run VACUUM on the table. > However, that record wasnt deleted but the xmax was still set to > transaction`s A txid. Is that a bug ? the vacuum process shouldnt delete > that row because it has an old xmax (there arent any younger transactions). > In addition, I'm still capable of seeing the record (because of the rollback..) > but my txid is bigger then the xmax of the record. ? Is that a bug ? I wrote about that a whil ago, see https://www.cybertec-postgresql.com/en/whats-in-an-xmax/ > -When I run an update basically its just like running an insert and delete > (the xmax of the old row is changed to the transaction`s txid and the new row > contains xmin same as txid of the current transaction and xmax is set to 0). > However, when I query the table (didnt commit yet) I dont see the old record. > Is there a way to see it ? Why is that ? I thought that I should see the table > with a non zero value in the xmax col. In the transaction that ran the UPDATE, you will of course see the updated row. Everything else would be inconsistent. You have to start a second database session if you want to see the previous row version. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com