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 1oBJDg-0004qM-BP for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 16:58:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oBJDd-00027S-B4 for pgsql-admin@arkaria.postgresql.org; Tue, 12 Jul 2022 16:58:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oBJDc-00027F-Vc for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 16:58:41 +0000 Received: from mail-qt1-x834.google.com ([2607:f8b0:4864:20::834]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oBJDZ-0002yh-S2 for pgsql-admin@lists.postgresql.org; Tue, 12 Jul 2022 16:58:40 +0000 Received: by mail-qt1-x834.google.com with SMTP id g6so9860447qtu.2 for ; Tue, 12 Jul 2022 09:58:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:from:mime-version:date:subject:message-id :to; bh=nndel4ChPPJM8MVEywKJCPLrR4aF55GkDROzCFiRXkY=; b=anTQwIa6/vqNOuunurWc7StlruBuHdvTnAcmk3jXqxVH8JgCr6JkXA3cbwnufpQESs l30bpoqmeD8eXhmPLxD45/eg/eWm6TqRVMxq4f7KFJ5XPitW4NN0kGkhH6CltOz5gPfm NVkFX+U9JgYXb5JJuPcmUQcozK8zuE9un2vdhCPVB6OUVj+M5G4wVEAk35IJ9Pk1JxCC sx3PQDx3bSBsaPj7Dr94+sfRxe63/koMp5xtHtOXVkGmiclBxaksnbThSkCVNnbq2c4W kKrYMZ117ySml652xOrCpIjeLnPdwWqQPngNPrFaxMCS2AmLEdnMM1mIJCrRce1+L8Nd srLQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:content-transfer-encoding:from:mime-version:date :subject:message-id:to; bh=nndel4ChPPJM8MVEywKJCPLrR4aF55GkDROzCFiRXkY=; b=nRY8ohYvDIjmUDdjfq1we6bJBfbJBd6pCz0D692f2nq6s6jeYrNxzCcPWTmB043Y6p zOwMMnzy0WcUm+BtCX1hTElA/RN9jIM0lc5jhv10T75cItL9kVJKYZx6TMGtdNy+AGTN jcFYctsyeXlZdpEGs+iocDl73O9u46meKx+QjZznQVcv9bTnuQ7pfr2LSPMqEJFm5Y3q YXbvUVfxAUn56qNz4JfKVhrhayEOWTCmoJlXwmPXi/VneJOpTypjK03xuAznAIseM7kv PVUBLgUJgyGp4wvp8M/sezxVJUQ3jDmWIc73btztg57FvBgOxfOO+eD6oaH8obAt1Ga4 nViQ== X-Gm-Message-State: AJIora+fGrgxfQaDOMWRB40zWomp4C1DuXnr8GvDx/5YOnjtNR7gkQnR reIkw5jC411adNOEhiBzH8iNkcIsYUA= X-Google-Smtp-Source: AGRyM1tUXN+Ef2Tr0ioV9RulFTLQeeh6oYSch4dgtfQwtf27vUCq+3GvH0stbaZaRA8V1t54UuwIgg== X-Received: by 2002:a05:622a:1892:b0:31e:b892:1efc with SMTP id v18-20020a05622a189200b0031eb8921efcmr7141435qtc.512.1657645115489; Tue, 12 Jul 2022 09:58:35 -0700 (PDT) Received: from smtpclient.apple ([72.47.90.47]) by smtp.gmail.com with ESMTPSA id bz8-20020a05622a1e8800b0031b18d29864sm7966686qtb.64.2022.07.12.09.58.34 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 12 Jul 2022 09:58:35 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable From: John Scalia Mime-Version: 1.0 (1.0) Date: Tue, 12 Jul 2022 12:58:34 -0400 Subject: Automating removal of orphaned stored procedure calls Message-Id: <60D4E7CB-BA42-4203-A5EF-9E5FA4D7CAE3@gmail.com> To: pgsql-admin@lists.postgresql.org X-Mailer: iPad Mail (19F77) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk One of our newer applications is regularly leaving some stored procedures in= a partially completed state. It=E2=80=99s not one of my applications, and t= he DBA responsible for it hasn=E2=80=99t said why this happens, but he is tr= ying to automate removing these. Currently, it=E2=80=99s a manual process wh= ere they log into the db as the application owner which owns these and remov= e them manually. For whatever reason he can=E2=80=99t use the application ow= ner=E2=80=99s I=E2=80=99d to run this process. I suggested adding the applic= ation owner as a group for the actual user he is going to use, and once that= happened, using a SET ROLE to become that user, but it did not apparently w= ork. Anybody have any good ideas here to make this work? Sent from my iPad=