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 1isZ2e-0001Qk-DG for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jan 2020 21:20:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1isZ2d-00017E-4z for pgsql-admin@arkaria.postgresql.org; Fri, 17 Jan 2020 21:20:31 +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 1isZ2c-00016Q-Nx for pgsql-admin@lists.postgresql.org; Fri, 17 Jan 2020 21:20:30 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1isZ2Y-0003vE-QV for pgsql-admin@lists.postgresql.org; Fri, 17 Jan 2020 21:20:29 +0000 Received: by mail-ot1-x32a.google.com with SMTP id h9so23723413otj.11 for ; Fri, 17 Jan 2020 13:20:26 -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=CT9Ff+DYSKs6MjBQ++7iZIk+63sOtBmUk5uvPFV8OiE=; b=C6HvMuOgWCzh5Gdemwbo2/Dsr/UtgTrUON6ot5uvWvnY1jdV6On/fUb/evzzhGggLt hHC6Os89oES7adti2uT2HJ6ygTiQNCbbiPUs53alVJDMiAjaAuHIUNm0NBbGWs87aulh FkwXwrYxO98uoR6ev4+s4YTgY1jw7KUwf5KUot0R1Gpiw9PIsZqnrGS+vtZ3XgNcKnNZ uN3XfRQedWUWPY3jx85oP6tsyF4WDQL2c99x63UO6ytZZqLImI763qeSRx9Jw9xkeXH8 ET27b5j9p/9ye3noVCeNXO7M671rcTtk/cSo/gQHwBWR9Df8uK7Wd0MoqFIKYVsV9jNK fcnQ== 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=CT9Ff+DYSKs6MjBQ++7iZIk+63sOtBmUk5uvPFV8OiE=; b=heIgC7wEsOFiI9mAVpqhahdOEn4wvzexYHkmkfborMDHJNMpK6opnQw43gFlL16DuK vpN12CBJABwuri7K3WbWTeVmehSrA7sGKL433q7/jJMXVjfFvqPlxISrN/1M2D4P8KoV Yd+GM7Vw6P5Atz1ku8kelNWiOx1KmvJdhwIFD2wqC1jHUr/RGAHzgxMF4GkY0QP0okA+ a3iLb6GmS9cOAmz953pmrhegYJVEU0+lLbjNQG7CZVf6wnqTFrzAKf0bw9+J3IV68V65 dEOf8jIfEAEjlKrwKcEDs05Ww+nV+oAFaItlSh4vdh4yDUw1ptTOdZIUeB9MrvVq0U9F uV0w== X-Gm-Message-State: APjAAAWAhdl7Neo7vIAG7V+gBw914kLIEgrTQb/HejHsB+EdlfOJHJTk 6gGWcAkh4mMeqij1Kv9opeM5QmLkPN0= X-Google-Smtp-Source: APXvYqyi3XNzkmby5+mgbWsJYbgj8ONhGx1Mu/yWQO1n7x4jLlkVNYlm98wKmkdbQTAIFnohjej/mw== X-Received: by 2002:a05:6830:10d5:: with SMTP id z21mr7920255oto.30.1579296025931; Fri, 17 Jan 2020 13:20:25 -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 k13sm8161969oig.24.2020.01.17.13.20.25 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 17 Jan 2020 13:20:25 -0800 (PST) Subject: Re: delete then insert To: pgsql-admin@lists.postgresql.org References: <4e1f53ad-6793-c714-46bc-bc7d16b836b1@gmail.com> From: Ron Message-ID: <6557b021-04e9-4c1f-4661-8f063809211c@gmail.com> Date: Fri, 17 Jan 2020 15:20:25 -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="------------C354FB14F501D8EC3ADAAA5E" 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. --------------C354FB14F501D8EC3ADAAA5E Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 1/17/20 2:59 PM, Ken Benson wrote: > > *From:* Ron > *Sent:* Friday, January 17, 2020 12:12 PM > *To:* pgsql-admin@lists.postgresql.org > *Subject:* Re: delete then insert > > 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. > > */So – the answer is – NO – the dead tuple won’t be used until is has been > reclaimed by vacuum – and that it doesn’t because it would be a bad thing, > right?/* > I don't know what happens if not other transaction is holding that "slot". -- Angular momentum makes the world go 'round. --------------C354FB14F501D8EC3ADAAA5E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 1/17/20 2:59 PM, Ken Benson wrote:

From: Ron <ronljohnsonjr@gmail.com>
Sent: Friday, January 17, 2020 12:12 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: delete then insert

 

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.

So – the answer is – NO – the dead tuple won’t be used until is has been reclaimed by vacuum – and that it doesn’t because it would be a bad thing, right?


I don't know what happens if not other transaction is holding that "slot".

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