Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m6aHg-0001Jz-90 for pgsql-hackers@arkaria.postgresql.org; Thu, 22 Jul 2021 15:06:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m6aHe-0006IM-TR for pgsql-hackers@arkaria.postgresql.org; Thu, 22 Jul 2021 15:06:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m6aHe-0006IE-Gg for pgsql-hackers@lists.postgresql.org; Thu, 22 Jul 2021 15:06:46 +0000 Received: from mxout2.routing.net ([2a03:2900:1:a::b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m6aHV-0000AF-V9 for pgsql-hackers@lists.postgresql.org; Thu, 22 Jul 2021 15:06:45 +0000 Received: from mxbox3.masterlogin.de (unknown [192.168.10.78]) by mxout2.routing.net (Postfix) with ESMTP id 56A825FC28; Thu, 22 Jul 2021 15:06:33 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mailerdienst.de; s=20200217; t=1626966393; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=USukATh/OL7PNArBN+fi9sZnQ7zMHh3NX5I4TrotSCs=; b=hwU8eRpGgvPAiko7iurqtcABF+eCPtgo8c6OMk/i9O6aZ7iJeV63cYwWTHIN+sEQUorOdU LDPG9Ry7wkkIQiVaAL6loU1blxrfG8828IcCLrmsQmPxTaXl/RSDqE7xsjRjF/7pXWq9j7 6nZLf9vEF1u6Mt0A+LfSKtCOUkXjfEI= Received: from remote.index.de (p578a9a34.dip0.t-ipconnect.de [87.138.154.52]) by mxbox3.masterlogin.de (Postfix) with ESMTPSA id D9FC236019F; Thu, 22 Jul 2021 15:06:32 +0000 (UTC) Received: from acedia.index-gruppe.local (192.168.3.121) by acedia.index-gruppe.local (192.168.3.121) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2176.14; Thu, 22 Jul 2021 17:06:32 +0200 Received: from acedia.index-gruppe.local ([fe80::d96c:8551:78c8:d947]) by acedia.index-gruppe.local ([fe80::d96c:8551:78c8:d947%13]) with mapi id 15.01.2176.014; Thu, 22 Jul 2021 17:06:32 +0200 From: Arne Roland To: Alvaro Herrera CC: vignesh C , Zhihong Yu , Pg Hackers Subject: Re: Rename of triggers for partitioned tables Thread-Topic: Rename of triggers for partitioned tables Thread-Index: AQHWxEk1+rora/wutkWbcRQhC03q0KnbGQ2sgAAECBOATmSTgIBhAeKAgBDG6bOAACCNT4AAKOcAgAAj1kP///tHgIAEsSJngAHhzjCAhX9QJP//43kAgAAjD3T///1iAABXUKhwA1bKhQAABHSL9QDdrxEAAARjdxL///exAIABN1SAgAG0zACAASTOjA== Date: Thu, 22 Jul 2021 15:06:32 +0000 Message-ID: <724b9f97a96c470aab3bfee502366b7c@index.de> References: <202107202013.xgao3c2m6nht@alvherre.pgsql>,<202107212216.kj4i5us2i33k@alvherre.pgsql> In-Reply-To: <202107212216.kj4i5us2i33k@alvherre.pgsql> Accept-Language: en-US, de-DE Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.255.1.33] Content-Type: multipart/alternative; boundary="_000_724b9f97a96c470aab3bfee502366b7cindexde_" MIME-Version: 1.0 X-Mail-ID: f3b4153f-97af-4a16-88af-9ddea261d3e5 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_724b9f97a96c470aab3bfee502366b7cindexde_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, looking at the patch, I realized the renametrig_partition could use an ind= ex leading with tgparentid, without the need to traverse the child tables. = Since we still need to lock them, there is likely no practical performance = gain. But I am surprised there is no unique index on (tgparentid, tgrelid),= which sounds like a decent sanity check to have anyways. ________________________________ From: Alvaro Herrera Sent: Thursday, July 22, 2021 00:16 To: Arne Roland Cc: vignesh C; Zhihong Yu; Pg Hackers Subject: Re: Rename of triggers for partitioned tables ... now, thinking about how does pg_dump deal with this, I think this thread is all wrong, or at least mostly wrong. We cannot have triggers in partitions with different names from the triggers in the parents. pg_dump would only dump the trigger in the parent and totally ignore the ones in children if they have different names. That makes things simpler. The reasoning behind the ONLY variant was not to= break things that worked in the past. If it never worked in the past, ther= e is no reason to offer backwards compatibility. 1. if we detect that we're running in a trigger which has tgparentid set, we have to raise an error, 2. if we are running on a trigger in a partitioned table, then ONLY must not be specified. I think that would make the whole idea of allowing ONLY obsolete altogether= anyways. I'd vote to simply scratch that syntax. I will work on updating y= our version 9 of this patch accordingly. I think we should do something about pg_upgrade. The inconsistency is obvio= usly broken. I'd like it to make sure every partitioned trigger is named co= rrectly, simply taking the name of the parent. Simplifying the state our da= tabase can have, might help us to avoid future headaches. Regards Arne --_000_724b9f97a96c470aab3bfee502366b7cindexde_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable


Hi,

looking at the patch, I realized the renametrig_partition could  use an index leading with tgpa= rentid, without the need to traverse the child tables. Since we still need = to lock them, there is likely no practical performance gain.= But I am surprised there is no unique index on (tgparentid, tgrelid), which sounds like a decent sanity check to have anyw= ays.

From: Alvaro Herrera <= alvherre@alvh.no-ip.org>
Sent: Thursday, July 22, 2021 00:16
To: Arne Roland
Cc: vignesh C; Zhihong Yu; Pg Hackers
Subject: Re: Rename of triggers for partitioned tables
 
... now, thinking about how does pg_dump deal with= this, I think this
thread is all wrong, or at least mostly wrong.  We cannot have trigger= s
in partitions with different names from the triggers in the parents.
pg_dump would only dump the trigger in the parent and totally ignore the ones in children if they have different names.

That makes things s= impler. The reasoning behind the ONLY variant was not to break things that = worked in the past. If it never worked in the past, there is no reason to o= ffer backwards compatibility.

1.= if we detect that we're running in a trigger which has tgparentid
set, we have to raise an error,
2. if we are running on a trigger in a partitioned table, then ONLY must not be specified.

I think that would make the whole idea of allowing ONLY obsol= ete altogether anyways. I'd vote to simply scratch that syntax. I will work= on updating your version 9 of this patch accordingly.

I think we should do something about pg_upgrade<= /span>. The inconsistency is obviously broke= n. I'd like it to make sure every partitioned trigger is named correctly, simply taking the name of the parent. Simplifying the state our database can= have, might help us to avoid future headaches.

Regards
Arne

--_000_724b9f97a96c470aab3bfee502366b7cindexde_--