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 1gOTc8-0001mA-B3 for pgsql-admin@arkaria.postgresql.org; Sun, 18 Nov 2018 20:24:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gOTc6-000696-2R for pgsql-admin@arkaria.postgresql.org; Sun, 18 Nov 2018 20:24:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOTc4-00068y-Fw for pgsql-admin@lists.postgresql.org; Sun, 18 Nov 2018 20:24:13 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOTby-0004o1-6H for pgsql-admin@lists.postgresql.org; Sun, 18 Nov 2018 20:24:11 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id 7EDA3B93; Sun, 18 Nov 2018 15:24:02 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Sun, 18 Nov 2018 15:24:02 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=mJqAARg6chRCSNyeBGVqPjBmQTI p1jfxZ/XEqldKDKU=; b=PBmFkbf8jrr8oYyX05+i+5t7teN9vgG0zTsVgwLi0H0 7z5c8nHHI9W1Mi5YGpTKsYufek1304MWm0Fit7Xb2KvjlUgLq641a7/lEWs/VPgM U63ErCQWGIo89oAhpMM2S4TImBsyNZ6ZyRxER+pHduOT8WucIfYX9OTuIQ/QYL5u +4urK6WP7aiWVdk2bIJ+f1p1LdNg0DSjtbj2FLwUNAeT2CbXOwRrrdCk5iclwasm XeWMC6gt0Le/YPF847sWF1st7u5Ri8j331xZLuqfow+qMHBVQG5vvC1rZ//DtPgf lrj+a5yK84DBVzphzvCytS5hfYCSuzzeEmWQej7GAog== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=mJqAAR g6chRCSNyeBGVqPjBmQTIp1jfxZ/XEqldKDKU=; b=pMgXwrU/FsN0bfInTbE0s7 5zQE2gIaxGkOhvGRYT1vBAaFLVTolnD1JN69QVQqcWxbBgZ3lIehnJ+zNwl3P1Jr XcGS/pN0ZfT7tj2ilFAmUQ1uD73pOeG8ssEbJcdSryUO4s84LjoBjy/usAtfx8ZB XKgTw4a3af6kOijXb5pYKlhmXZynCH9YioMl4FDR+iFkE6+fxbX27jHclWcHHaEu kHF4VlAfD4Hemc4homSLLHQ0aAbTj2AOlaev0/sgB9JUv2QoIQSQfXjnG8Nzs+25 56EmHXrkv49/4QsBcXc+2yd/cIskgeaOcqExAzkW6mUNuNlxP7WTvt15OyeFOYrg == X-ME-Sender: X-ME-Proxy: Received: from intern.anarazel.de (c-98-210-140-171.hsd1.ca.comcast.net [98.210.140.171]) by mail.messagingengine.com (Postfix) with ESMTPA id 7EC76E405D; Sun, 18 Nov 2018 15:24:01 -0500 (EST) Date: Sun, 18 Nov 2018 12:23:59 -0800 From: Andres Freund To: Mariel Cherkassky Cc: pgsql-admin@lists.postgresql.org Subject: Re: unclear behavior xmax/xmin Message-ID: <20181118202359.6cwmsig3jsvzxm2x@alap3.anarazel.de> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2018-11-18 19:13:28 +0200, 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 ? No. A vacuum will not necessarily immeditely process a row/page if has recently been modified. > 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 ? No, it's not. For visiblity checks it's not just the xid that counts, but also whether this xid committed. bool HeapTupleSatisfiesMVCC(HeapTuple htup, Snapshot snapshot, Buffer buffer) ... if (!(tuple->t_infomask & HEAP_XMAX_COMMITTED)) { if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple))) { if (HeapTupleHeaderGetCmax(tuple) >= snapshot->curcid) return true; /* deleted after scan started */ else return false; /* deleted before scan started */ } if (XidInMVCCSnapshot(HeapTupleHeaderGetRawXmax(tuple), snapshot)) return true; if (!TransactionIdDidCommit(HeapTupleHeaderGetRawXmax(tuple))) { /* it must have aborted or crashed */ SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, InvalidTransactionId); return true; } > -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. You can use the pageinspect extension to see such rows. We can't normally show such rows, because it'd violate transactional semantics. What are you trying to achieve? Just exploring the system? Greetings, Andres Freund