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 1ifNxO-0006Qe-Nw for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Dec 2019 12:52:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ifNxN-0000Sq-JM for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Dec 2019 12:52:37 +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 1ifNxN-0000Sj-BN for pgsql-bugs@lists.postgresql.org; Thu, 12 Dec 2019 12:52:37 +0000 Received: from mail-vs1-xe42.google.com ([2607:f8b0:4864:20::e42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ifNxK-0005fa-JK for pgsql-bugs@lists.postgresql.org; Thu, 12 Dec 2019 12:52:36 +0000 Received: by mail-vs1-xe42.google.com with SMTP id n27so1469746vsa.0 for ; Thu, 12 Dec 2019 04:52:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:subject:message-id:mime-version:content-disposition :content-transfer-encoding:in-reply-to:user-agent; bh=v7N5dHCaAWp0fTevclTBKMQK/MWJk5kOiQr+4njNuCw=; b=M+7r4JWvWzuVPrLAXzs4jAG/ld33BFRYDB2w1gnxbdTYyALgs/ky57XxLzr8u9ItRJ /O4RLj6Z2ak6por5gRbkINTx/VvJa3j2YYagWl+/pU7IvREycHJ7aU9wNcTrKvZbpy1Z GCT+G4/mOeCTHIiruuNB8VqBSYrg6dOGbczsZNBEGDnVCdXxgZ6w4DgDvtcUy/0giK35 EszXtqeF6wRHqeztDz/NWYqFZcZL7eaDbD962V/jAlpbCHC+iyk9tcV6ueejyHBcuiUD 9Z4HB6GFjTHU6HUaURYoOtGh5RgnwMFhjJxXQ2WhbWxTb6eKf17Vrz0UgTwtNYIxeDUT xqOA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:mime-version :content-disposition:content-transfer-encoding:in-reply-to :user-agent; bh=v7N5dHCaAWp0fTevclTBKMQK/MWJk5kOiQr+4njNuCw=; b=taJLDd43Bou79u0SMImmruV+bZ6HqfmkXCzPN4bO6ZIo0Fh2HhXyje7l0vQmtGeLYJ +hnEUnbFiprIQVFIeQBMFBBaMr8EY8nsLCG4NSGvr9Hdx03fzBGKrbMylI56ziuLrGzM fpktpiligjPSoxYXYptTyaNJ3pb0cJRe4xb1HQ3OG/FvcnCXmswzNJ08QawBUSrLlcJ2 FmUM5k1mutKQQHeSompNwjw0g081CmBYsKrqBHs/dtyDd5YyVz6v5aAWvSHtLTr38HmY 0BzL2UsqyEK1aj84Z6aha9swhebJ8ns91ey48LXC0ofjFXhc+YFgpzn6oHJGUVAvAORG GidA== X-Gm-Message-State: APjAAAUYH/RQ6JihUONqH7RoG5LlZJSHqdbQ3CLZILjuMmYNMTsvAChW gUzaYAFMR0i51GVfROLdjczOHA== X-Google-Smtp-Source: APXvYqxgXqxFl+q5eCheNisGJDyNGji0dLlmlfUA2FBJekMBFJbPA5xw8Bm744EO6T4hQeO0qFJwmw== X-Received: by 2002:a67:5e85:: with SMTP id s127mr6945101vsb.157.1576155152649; Thu, 12 Dec 2019 04:52:32 -0800 (PST) Received: from nimloth.alvh.no-ip.org ([179.56.30.174]) by smtp.gmail.com with ESMTPSA id u16sm3505330vkl.21.2019.12.12.04.52.31 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 12 Dec 2019 04:52:32 -0800 (PST) Received: by nimloth.alvh.no-ip.org (Postfix, from userid 1000) id E6E2F300832; Thu, 12 Dec 2019 09:52:29 -0300 (-03) Date: Thu, 12 Dec 2019 09:52:29 -0300 From: Alvaro Herrera To: a.roland@index.de, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16153: foreign key update should probably move dependent rows in the case of tuple rerouting Message-ID: <20191212125229.GA8225@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <16153-dd8a42806bf9cb79@postgresql.org> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2019-Dec-05, PG Bug reporting form wrote: > foreign key update should probably move dependent rows in the case of > tuple rerouting > instead of simply deleting them silently Hmm. > create table a (id serial, primary key (id)) partition by range (id); > create table b (id serial, primary key (id)) partition by range (id); > alter table b add constraint a_fk foreign key (id) references a (id) on > delete cascade; > create table a1 partition of a for values from (1) to (2); > create table a2 partition of a for values from (2) to (3); > create table b1 partition of b for values from (1) to (2); > create table b2 partition of b for values from (2) to (3); > > insert into a (id) values (1); > insert into b (id) values (1); > > update a set id=2; > > select * from b; This seems to be a side-effect of how tuple updates work across partitions. My bet is that it's seen as just a deletion, which deletes the referencing tuple because of the ON DELETE CASCADE; then the other row is inserted and nothing else needs to happen. You don't have ON UPDATE CASCADE in your example, so my expectation would be that this should raise an error, per NO ACTION. But if you were to add ON UPDATE CASCADE, then yeah the referencing row should be moved too somehow. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services