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 1isXyb-0007WO-44 for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jan 2020 20:12: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 1isXyZ-0002nq-0m for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jan 2020 20:12:15 +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 1isXyY-0002n6-HT for pgsql-admin@lists.postgresql.org; Fri, 17 Jan 2020 20:12:14 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1isXyW-0003PS-4Y for pgsql-admin@lists.postgresql.org; Fri, 17 Jan 2020 20:12:13 +0000 Received: by mail-oi1-x231.google.com with SMTP id c77so23294474oib.7 for ; Fri, 17 Jan 2020 12:12:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=+hamjeA5cMjEnTGqsXmLRvyYUXDClL8utVL93fOqvPM=; b=is/9Hm03cgxcymGkPQ89J6oX/dvk7euHYowhUTVVWIDepYXgL2HthQ49lh4DnQT97x P7pxZhIO6fMteVm+FW1WBG+F030Jm5kD2/RQkDFmLGOhAMaxhCtK7aSUUemN4fw2scVe +ZIZk3Yu3FLL/BIL0qoHpQk5Pj1hyo/S7ajXzShDCk49x55Iqd1b/+3WuClztS5EeS7D 3sRNpAUXQcfAOEeaxQR94Qfx6Dw6bvl0zYma3FVnkTys2vdo29TIA/HZavPb2awrpEa4 bkh8p6ewsdJTM5gxrkCUQIgN7cYeT/hQQRAm9G01eSTawUlDUA4yFsybM5/lYyg1jl07 nlvg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=+hamjeA5cMjEnTGqsXmLRvyYUXDClL8utVL93fOqvPM=; b=IxFUVIloJR/hepnmRVXxAZ7OZ6tduL4bQNqawq4ctIJDQUMOC06voWDdjbpCDhaRVN 4v0IrlwsjFIXlthA0sgUHkC/jmFH6SvGrFKMqScHwlgcH0oGSkdNJHKpyaE0utdSxiq8 YVVnT8xVMPXXJpOCeHx9PTt7KXkiugV07MeDI8BFqufhwZWppFnXrmRYbRPdQfRqFhAe IAmG9xgEW2WeRUoDX20JTktL5jPTOjw8EYjwlu1/42SZuoyuoPEdMAoBhOk6aPSAWV1m wyGQpKe9jtBxL2SUOuC0tqwg9TdTpuqwuC+B9/mdl9alXTr7fwMrzXFvXbM6oCPzRymZ 6jyA== X-Gm-Message-State: APjAAAXrveM6JUWLYU9uba7i19dUwyoFwmDqJVWuKVcUb7sxbLXz5LYt LJ5DNz43JWLrQxDN/8Je4pEVC3F75SQ= X-Google-Smtp-Source: APXvYqzgyNv3O6xIEs2BdulQu+XfRpsyz4mXdGvnsthH+bzilrtotcD/oWuifFNLbAxl6MOKDExx9A== X-Received: by 2002:a05:6808:6d6:: with SMTP id m22mr4551950oih.138.1579291931345; Fri, 17 Jan 2020 12:12:11 -0800 (PST) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id k26sm8100611oiw.34.2020.01.17.12.12.10 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 17 Jan 2020 12:12:11 -0800 (PST) Subject: Re: delete then insert To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <4e1f53ad-6793-c714-46bc-bc7d16b836b1@gmail.com> Date: Fri, 17 Jan 2020 14:12:10 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.2.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------C5D6D98F2B7CB9DB8FCFFBB5" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------C5D6D98F2B7CB9DB8FCFFBB5 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/17/20 2:03 PM, Ken Benson wrote: > > So – I THINK I know – that when a row is deleted from a table – the row is > not actually removed from the table – but, merely marked as deleted, thus > becoming a dead tuple. > AUTOVACUUM – takes care of the process of removing these dead tuples. > > My question. If – an insert occurs to that same table before autovacuum > comes along, does that inserted record use a dead tuple? Or – does the > insert result in an actual added row? > If there's an open transaction which is still looking at the now-deleted record, then over-writing that area of the file would be a Bad Thing. -- Angular momentum makes the world go 'round. --------------C5D6D98F2B7CB9DB8FCFFBB5 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/17/20 2:03 PM, Ken Benson wrote:

 

So – I THINK I know – that when a row is deleted from a table – the row is not actually removed from the table – but, merely marked as deleted, thus becoming a dead tuple.
AUTOVACUUM – takes care of the process of removing these dead tuples.

 

My question. If – an insert occurs to that same table before autovacuum comes along, does that inserted record use a dead tuple? Or – does the insert result in an actual added row?


If there's an open transaction which is still looking at the now-deleted record, then over-writing that area of the file would be a Bad Thing.


--
Angular momentum makes the world go 'round.
--------------C5D6D98F2B7CB9DB8FCFFBB5--