Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fd2eh-0007G5-RF for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Jul 2018 00:06:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fd2ef-0004JS-KK for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Jul 2018 00:06:49 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fd2eN-0002mX-HB for pgsql-hackers@lists.postgresql.org; Wed, 11 Jul 2018 00:06:31 +0000 Received: from mail-qt0-x234.google.com ([2607:f8b0:400d:c0d::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fd2eJ-0002Ek-Q5 for pgsql-hackers@postgresql.org; Wed, 11 Jul 2018 00:06:29 +0000 Received: by mail-qt0-x234.google.com with SMTP id y5-v6so19963843qti.12 for ; Tue, 10 Jul 2018 17:06:27 -0700 (PDT) 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:user-agent; bh=vW86LO4azGvcK2mbbjoEU0B29ir4ikj8FuOv/luIFlQ=; b=xpWnm+Tckieu/op57OXNhiSZ2/JlQqHJxQC4CRCN8K4D1sXpzsAdBJM4b2iKjXdhZS zPrb6PFJTedgRzSTQ8jfs+VTP3yb5GYH4QYEMoA8zBQSMPw5brjQFTWVV98Xxv01pOha PV+zSWcx/NVA2GFRpmZ1/32GkRtiz9PctQp3Rr8ifMMY0cK+FhktBp4J1nZc6hyb28z2 baJiii+dcHEcrArjLPWnMYXMOltMvYZgqx0SfXkZaCSlDMmiqg76xG7OgUL0ZoWitejj zDUtMOY7BUx8Fb0gZL1ec3yKUfeXwLZhjW/ZIAJdm7UkXFfWSOiRbV9ArNBRlFemPt0p FfgA== 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:user-agent; bh=vW86LO4azGvcK2mbbjoEU0B29ir4ikj8FuOv/luIFlQ=; b=TeFmgBOVOqWF0WNaOuEuYb+hu3lu39rnOsqYzb3U+n8uqC3Xidav0mz8OP40d9pxU9 NnnmqEwHVb5cb/cJcm+poW2P5nvdPvAJkfPzbTUbFpXbK681f5j+SK27NJG0Ih/uAdy3 7D8kcvX+od3nEyE8PDKo6OH3axk1EjF8BVqOIbVMp1gZdtMAGCJBe1hIr22Dy/Rsbf4I uGJpIWogyqCULtD3eJgBAgZONz1gHSqik7B2DSmHVPSF/sycyeApSK7Iy2F/GZKh3e4j +MhbC80wEs8nIBwR4pwM4KtPoRdZLhcTnGzH3h3bPOutdvmCft+TvqxBrda3w1tNYcVT f0Jw== X-Gm-Message-State: APt69E0Zld5MGF7GSOZI6JJOK3jCo66KcDUUy0j8DTfs70Vc9srjqmKr 8ZF0iJQyLGQj+x1MNseffpBI5UU/iejAN7sQIDUbn9/laukYbOmb1VDPrckvoSQHEUe/jMMntIL Mm+OaSrpKs+K62oYMvs7hb3PDj2CRP31pc3xNGmIwJDwA55jRFaMtzmhmlgVlpjHhVg1JTVZqoX mV9tA= X-Google-Smtp-Source: AAOMgpcvQoGbQ+EyjktuDlJ0eZ8WgCnpXrtSK8vcm7CxxzWd5ouyftSAJPPHImVjVjRKw005Fi5iew== X-Received: by 2002:ac8:2eaf:: with SMTP id h44-v6mr20793281qta.398.1531267586724; Tue, 10 Jul 2018 17:06:26 -0700 (PDT) Received: from alvin.alvh.no-ip.org ([190.121.29.3]) by smtp.gmail.com with ESMTPSA id x7-v6sm5950918qtc.66.2018.07.10.17.06.26 for (version=TLS1_2 cipher=ECDHE-RSA-CHACHA20-POLY1305 bits=256/256); Tue, 10 Jul 2018 17:06:26 -0700 (PDT) Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000) id 6FAD2D85; Tue, 10 Jul 2018 20:06:24 -0400 (-04) Date: Tue, 10 Jul 2018 20:06:24 -0400 From: Alvaro Herrera To: Pg Hackers Subject: TRUNCATE tables referenced by FKs on partitioned tables Message-ID: <20180711000624.zmeizicibxeehhsg@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk $subject is broken: create table prim (a int primary key); create table partfk (a int references prim) partition by range (a); create table partfk1 partition of partfk for values from (0) to (100); create table partfk2 partition of partfk for values from (100) to (200); You can't truncate prim on its own. This is expected. alvherre=# truncate table prim, partfk; ERROR: cannot truncate a table referenced in a foreign key constraint DETALLE: Table "partfk" references "prim". SUGERENCIA: Truncate table "partfk" at the same time, or use TRUNCATE ... CASCADE. However, you can't do it even if you try to include partfk in the mix: alvherre=# truncate table prim, partfk; ERROR: cannot truncate a table referenced in a foreign key constraint DETALLE: Table "partfk" references "prim". SUGERENCIA: Truncate table "partfk" at the same time, or use TRUNCATE ... CASCADE. Trying to list all the partitions individually is pointless: alvherre=# truncate table prim, partfk, partfk1, partfk2; ERROR: cannot truncate a table referenced in a foreign key constraint DETALLE: Table "partfk" references "prim". SUGERENCIA: Truncate table "partfk" at the same time, or use TRUNCATE ... CASCADE. CASCADE is also useless: alvherre=# truncate table prim cascade; NOTICE: truncate cascades to table "partfk" NOTICE: truncate cascades to table "partfk1" NOTICE: truncate cascades to table "partfk2" ERROR: cannot truncate a table referenced in a foreign key constraint DETALLE: Table "partfk" references "prim". SUGERENCIA: Truncate table "partfk" at the same time, or use TRUNCATE ... CASCADE. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services