Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXoRl-00Cmhd-Uo for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 20:23:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXoRi-00DCPS-BT for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 20:23:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXoRi-00DCPJ-1m for pgsql-hackers@lists.postgresql.org; Wed, 07 Feb 2024 20:23:02 +0000 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rXoRb-005b4w-4d for pgsql-hackers@postgresql.org; Wed, 07 Feb 2024 20:23:00 +0000 Received: by mail-wm1-x330.google.com with SMTP id 5b1f17b1804b1-4102d50ce70so1429015e9.3 for ; Wed, 07 Feb 2024 12:22:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1707337373; x=1707942173; darn=postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to; bh=44O5+Ya08tGMlOjzFNZvgmgVvdjfyea93wpuYYHY9b8=; b=HUUshXNNNpfSFCJV/9Jsg8q8o0uLdIWNke7KtiBV4lDclkzvRSs6dchcQClUiZ6NKR +fyIBcK+NQ9Lc7k7HOz5djkDMG2tbzbWZ441QFD7TsfttGOoPUN5uSIbQ0CvQTkWHRVQ JpPdl/uDGMtFYuiqwlLEpR36aPUebyX1Hd81patrmUvxFlr13aP2cMinGNPAMuDUQVUj aZ30NjD2HTiMGiHzQfRIzg4RNtVnTH7OIB6hkHLBBHSnlvbWhVZv2Pq7IFbmanKlBQ3b v2RAy0/vK8I0aZScgjOVvzspgS0Ju+ruBR0AF0xTN/iBtyhsSa8Y99wPALTR2Ip904PY w20A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707337373; x=1707942173; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=44O5+Ya08tGMlOjzFNZvgmgVvdjfyea93wpuYYHY9b8=; b=DCpXZNZ+DBJyjaU5awXAk2zRtyjYGUO+zGuTCIC04KGahc1KHjSrRbnmQ8USeqmfyr Oz/tr+ufPPnRKUFoL82KCpvDACDpU+xgCGnEee8Afcbl7S2OVnJWumRicsxCiSfVhAWI VOjZ6o74x6bh9bIsbf2vFK6sEEv2ODcj/V3rl79FyW4flF5rtVWwrEvfNSpE+Ur7SIKQ thZ3KYun9tMaeXc4Y1uVvpD/v8+daBXwopEaS//M62reJSow7RgVHjBiVrWvHz74gAmS C76OeOwlqkAqXVhIkKvxdDlJXl14/OmtIVYiu9KdV/kwVIRPY6yWBgbfnsAny8oP35ys XzUQ== X-Forwarded-Encrypted: i=1; AJvYcCWhxydmu70tGfgCn0G++m4v6qgFJFLtRL3qtrbz5EVvXij4wbPETM+a6cCtB/02X5DIH/sRxL2yqMTdkT9sQzPsyg+1Yj8ydzF6siey X-Gm-Message-State: AOJu0Yx59PHbWa4DvnJ1efRmOrNfk2rxrmOZ58mefuBcn3bKddLi9Amc xdcy4eOwL3xnQ2CaA4owUMKFkth0P1uzParkVlR4103eBJy54su7d8UPrnLhwRw= X-Google-Smtp-Source: AGHT+IGXYqd5OGrMZ4TM3xb84/TWmSttw6ABJHh0BEceMg2xGOKXhZMRD5sH4hY/J73Ss6qoIH0WTA== X-Received: by 2002:a05:600c:1c9a:b0:40e:b447:ae7d with SMTP id k26-20020a05600c1c9a00b0040eb447ae7dmr5298643wms.33.1707337373576; Wed, 07 Feb 2024 12:22:53 -0800 (PST) X-Forwarded-Encrypted: i=1; AJvYcCU04j4dUbM5Rb9ttiOu1D7CNiZpR7VwwvIJwvOnPN6UFSip19+Xrn2Csy04pwG1tSBEMx45fNYd128nRttWk36Zho8aZi5sGOUnlnT2 Received: from localhost.localdomain ([46.151.204.202]) by smtp.gmail.com with ESMTPSA id t9-20020a05600c450900b0040fa661ee82sm6331285wmo.44.2024.02.07.12.22.53 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 07 Feb 2024 12:22:53 -0800 (PST) Message-ID: <8f31307e0de000a103a9713e19db5ec07354ffb3.camel@cybertec.at> Subject: Re: Question about behavior of deletes with REPLICA IDENTITY NOTHING From: Laurenz Albe To: James Coleman , pgsql-hackers Date: Wed, 07 Feb 2024 21:22:52 +0100 In-Reply-To: References: Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.3 (3.50.3-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2024-02-07 at 15:12 -0500, James Coleman wrote: > We recently noticed some behavior that seems reasonable but also > surprised our engineers based on the docs. >=20 > If we have this setup: > create table items(i int); > insert into items(i) values (1); > create publication test_pub for all tables; >=20 > Then when we: > delete from items where i =3D 1; >=20 > we get: > ERROR: cannot delete from table "items" because it does not have a > replica identity and publishes deletes > HINT: To enable deleting from the table, set REPLICA IDENTITY using > ALTER TABLE. >=20 > Fair enough. But if we do this: > alter table items replica identity nothing; >=20 > because the docs [1] say that NOTHING means "Records no information > about the old row." We still get the same error when we try the DELETE > again. Well, "REPLICA IDENTITY NOTHING" is the same as "has no replica identity". So is "REPLICA IDENTITY DEFAULT" if there is no primary key, or "REPLICA IDENTITY USING INDEX ..." if the index is dropped. See "pg_class": the column "relreplident" is not nullable. Yours, Laurenz Albe