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 1sKGcm-009B70-Tr for pgsql-bugs@arkaria.postgresql.org; Thu, 20 Jun 2024 12:10:45 +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 1sKGck-0068iR-Jn for pgsql-bugs@arkaria.postgresql.org; Thu, 20 Jun 2024 12:10:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sKGck-0068iD-6d for pgsql-bugs@lists.postgresql.org; Thu, 20 Jun 2024 12:10:43 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sKGci-002dzs-HB for pgsql-bugs@lists.postgresql.org; Thu, 20 Jun 2024 12:10:42 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-a6f177b78dcso85239266b.1 for ; Thu, 20 Jun 2024 05:10:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1718885439; x=1719490239; darn=lists.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=oeUIE1mOcAWu7H7Q2tnQoVYFRXxjmxYxQmZu5vtd524=; b=LX/IQ+VP6V+WSfrxTqHdPdHZBeUNINiPug7yjWjUYwKukgKDGnCm0Jcja7N8riZqgo LV6T+P8Ud0WzKqID7EwZke5UmtKui4pKMQv7s+QwQ4sTmT92AZBBQqOyOFPTTj7SSyuD YgyBr64x0wAbNBisd78Z0aca0uL9VfsUxlRDO4UKNiO5uwFJsD9H5sAS3ZwuT5mCH/C0 2df9iXVWopVjlvRdmCSWGKbbssVyhUxcEuXRISnXhDeSDKa9i53kWU94/HDqv9jzOfYQ r2BcBZI4yeviPFDydN+TcGAkGI5fSXbxmwS6sdN1ZMmRn4FqRCSVhLHYas5l6KyrvkxO xz3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718885439; x=1719490239; 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=oeUIE1mOcAWu7H7Q2tnQoVYFRXxjmxYxQmZu5vtd524=; b=QUUiV90Bykg7qom4Pjxq/JjdG5mErEPgGh+PWiuaPhm23wMnj3ULJtx4pdO57GiwHt UcBiPgsRIsw2coFvtggCM51tYF/L8qGbBblpO6tany7kvGYlNyNcipgYMvewYU0MEfft cosaj3ZEjtgYQJlURnxyDznoWBPgAgTE3xYFv34BMES1HcH8dhVCQuKlRD0u0ksPOJ9i QYIB60lGAiRfwZlqYIJrsw6a2fItoIcSUqu8Pe7Yv6nO+KOMh2bFajnmKb8ZcFcjjllv Sr3SCVesg24Kn3q+J6kXks2pzk3Xw0vywWWtK8zIj6VZ5CjTy6gytG6a1a2Ulgi+IJc7 RqEQ== X-Forwarded-Encrypted: i=1; AJvYcCXHHDiu0CB2gMQJ6kDraYlpqC8mmjtqhro6KWdigKmjNsw6/41iadJdbuVnvGZcLRM671qzwc0qxc8HWna8fBAToiUi0n29nSWi+99sOYzA X-Gm-Message-State: AOJu0Yy+MSRkxjGUeNjbWQsGS+BeBGXFne35UBWvQhCpGVdQIqT9qvsF DHw7Of0Tx9LY1S+NYRYJPxnd3RoHY25xnLF8fHbOidZt072Me3hyXrxkI+m6XtU= X-Google-Smtp-Source: AGHT+IFIKlI3VYqye+QxF0XkUkjPAKbSjzAljI2y6KsOrXxr3Gi5g0eixOcU5th8nntitpNcIoP5+g== X-Received: by 2002:a17:907:a4c5:b0:a6f:b5ff:a6fd with SMTP id a640c23a62f3a-a6fb5ffab3cmr308653866b.12.1718885439038; Thu, 20 Jun 2024 05:10:39 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:6c86:8c2b:1cb1:2f13:98e0]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a6f56da329asm766270366b.37.2024.06.20.05.10.38 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 20 Jun 2024 05:10:38 -0700 (PDT) Message-ID: <412ed2e042c5cb552acfa45b01201083dcaae446.camel@cybertec.at> Subject: Re: BUG #18516: Foreign key data integrity is not validated when reenabled the trigger on tables From: Laurenz Albe To: mujjamil995@gmail.com, pgsql-bugs@lists.postgresql.org Date: Thu, 20 Jun 2024 14:10:37 +0200 In-Reply-To: <18516-a7e2ae00e4ccf5ca@postgresql.org> References: <18516-a7e2ae00e4ccf5ca@postgresql.org> 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.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2024-06-20 at 09:10 +0000, PG Bug reporting form wrote: > I come up with a weird issue in the PostgreSQL 15.6 version. To update th= e > data in the tables I generally disable all triggers on the table using th= e > superuser privilege and enable the after the data copy to avoid the check= ing > the parent and child tables sequence. During the enable process FK > constraint should check the data integrity between the child and parent > tables. but somehow this is skipped and trigger enabled without any issue > and later during the data restoration this issue is identified. You wish that enabling the triggers would check the foreign key constraint, but it doesn't. All it does is prevent new foreign key violations from happening. If you disable the system triggers that implement the foreign key, all bets are off, and you have to make sure that only correct data are loaded. This potential for data corruption is why only superusers can disable system triggers. Leave the foreign key triggers enabled while you load data. Yours, Laurenz Albe