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 1rn57v-00BUcE-Rn for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 23:13:44 +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 1rn57u-009Bnh-BN for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 23:13:42 +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 1rn57t-009BnW-9d for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 23:13:42 +0000 Received: from wfhigh7-smtp.messagingengine.com ([64.147.123.158]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rn57l-005ZTO-Nr for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 23:13:40 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailfhigh.west.internal (Postfix) with ESMTP id 622C418000D8; Wed, 20 Mar 2024 19:13:33 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Wed, 20 Mar 2024 19:13:33 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1710976412; x=1711062812; bh=sy34iCdEDv1YRWEXHe7KITqLwcRVssm5ACrDDYmN2Ro=; b= malxjqsYhgGbIwccYk7RE5GVTVlRnwNEo6sI6DEAaRiYky2cwvJX6CLMl7NbY6/H 6ZInvKpNwRKePhQXRmWGmIS/UcJpiP0AZepVUwnyFFHrJWAP66W0gO31u1ID6xUl riTIWVIfFkwQ8jMsD0LzBPnvcYux43KugmtK9G9oUkvEeXlHCH3bJPbW0BWW4soF AnaL3hAJAm4+kSyqZ8IvRqA6HhEvL0YHwidpiynfmzdQ0nmehe8fUt4F2Ua2w86Q /cLbdNyKyMKD9ZM7aD4FCg0jZW3KFlD2QmAeazbZZWTcen9/IiFP8U+4SWxBpMFW gI+OAQDIkWXjsC+0HVFmSA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1710976412; x= 1711062812; bh=sy34iCdEDv1YRWEXHe7KITqLwcRVssm5ACrDDYmN2Ro=; b=X whA3R72cB/Pr8G300x+1YyiZYcY0oTt2KpIUsAyoggnj0tphc2MOS/kQiZX1d9aX OB4nVhcdIMkICldtab3Ps3DVMNp7vVyLbyDoDWsLoekWPmKHc6h0ROzfrFB44Kpn 4c+V/hT3KianYFYFmrgVBVntS5U1QtDljebL2fVohw3DYo0/8noFLaKFjFtDN5SO 1Evy7nvHdkIN/qAflfpdsFEM/zjMDpFjEsobqNKoo5iPvvWNFagYA4EOr269hGkL HQ5ukWLseTNvo88BPBWmsB2CD50cZcfpfiPBijDbU49iJsuAOotMrcQD/gIG4Te9 Z3U0PXWGB7+Mq4oSAenxA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrleehgddtjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepheffheffueejuedvfeeugeeuudfgudekteelffelkedt ieelffdtudeitdehfeeunecuffhomhgrihhnpehophgvnhhvihhsthgrshdrnhgvthenuc evlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgr nhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 20 Mar 2024 19:13:32 -0400 (EDT) Message-ID: <481ed49d-7d18-4fdb-9a62-df8598728e72@aklaver.com> Date: Wed, 20 Mar 2024 16:13:31 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function Content-Language: en-US To: Jeff Ross , pgsql-general@lists.postgresql.org References: <6960da61-8033-4067-84d1-d9257ce94870@openvistas.net> <81c4556d-b599-4512-b6a2-7255f2a39089@aklaver.com> From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/20/24 15:52, Jeff Ross wrote: > On 3/20/24 16:25, Adrian Klaver wrote: > >> On 3/20/24 15:18, Jeff Ross wrote: >>> Greetings! >>> >>> I built a trigger fired process that copies an "order" from our >>> production database to our dev database.  An order, in this case, is >>> an initial row from a table and all of the rows in all of the tables >>> in that database/schema that are needed to satisfy all of the foreign >>> key constraints for the original insert. Through a web page, one of >>> our folks can select a schema and an order id to copy.  That >>> information is then inserted into a table.  A trigger attached to >>> that table takes care of copying the necessary rows using a function >>> that uses both plython3u and psycopg2.  I can supply the source code >>> if that will help. >> >> I think that will help, especially the interaction between psycopg2 >> and plpython3u. >> > As requested: > > https://openvistas.net/copy_orders_to_dev.html 1) I have not gone through this thoroughly enough to figure out what is going on. 2) Things I have noticed, may not be relevant. a) from psycopg2 import sql Never used. b) #prod_database_connection.set_session(autocommit=True) #dev_database_connection.set_session(autocommit=True) Why are they commented out? c) prod_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" dev_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" What version of PgBouncer? How is it setup? d) Why predefine all those cursors()? e) Why is database global? > > Jeff > > > -- Adrian Klaver adrian.klaver@aklaver.com