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 1nrwo8-0004cx-IV for pgsql-admin@arkaria.postgresql.org; Fri, 20 May 2022 07:12:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nrwo6-0005Ae-2S for pgsql-admin@arkaria.postgresql.org; Fri, 20 May 2022 07:12:18 +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 1nrwo5-0005AV-OH for pgsql-admin@lists.postgresql.org; Fri, 20 May 2022 07:12:17 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nrwo3-0002BJ-4E for pgsql-admin@postgresql.org; Fri, 20 May 2022 07:12:17 +0000 Received: by mail-ej1-x62b.google.com with SMTP id n13so12040443ejv.1 for ; Fri, 20 May 2022 00:12:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=message-id:subject:from:to:date:in-reply-to:references:user-agent :mime-version:content-transfer-encoding; bh=GDdelpvs1ceLXOWdLSk/LvNdz5ENOpkWr0jt0G/3Kqc=; b=K98iE49aepHMjlw/aSkCRIe32i0dAJU8x7L5kl6VeKAyYvyXTDdADKtfNfAf6IzZ7B 8ZNEL6wTUnxGZHeb6WIY9wmvL+1xeZr3BZ7POHXD/fR6E4AYaS/iBujM3yCxaERXdjvb kq7AEzFdLYHv7r7VUc553OUbVfDFwZkCyJydNUNV+fC12dUpP0fnubLsjsKttlhofbbp LbGGRFpu4QJbvYB0Drbgo/78HGYLqmTMYZlE9xJtf/dCQnimFtULLLKJKZpaWPtIX8Cn SbtNqP1X5fPE/HzZGbvneg0WsRhn7J02XUQ1+en5UHhDbS+Din94Ri0/UAdiJpcPr2pL ljVQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:subject:from:to:date:in-reply-to :references:user-agent:mime-version:content-transfer-encoding; bh=GDdelpvs1ceLXOWdLSk/LvNdz5ENOpkWr0jt0G/3Kqc=; b=b7mEs1gTAnoA6KSDVwTYKXabAA43KXZ5FjsXi3cN6nu5SopMfOrN+3fSxw4B+m1Iqn KQHin58Pb/NFGY4x4d4gbj2qC1XxgE4jRQPNvT39yO1Iu47t0Z5B7pJQjUO/1RmlmUKw XtszAEZeVvKoOEkUfDQ6i/v39tTC1Cx3m3bLg0MLV/+3Vi13NMucXoR4AyoCzAOB6jc3 O+3UkCRmf6QkMJBD99vKzLl80BlUdXicTq1g5JFhRjnIVd44nJJz3zCC26Goel+ahy3P q0zMM+m3pCzDJiWFkW/lls7NeQAkMmgoGYW8IauwbIhBaWVmQ+ZocbqpPffjpLL72RtI SEYA== X-Gm-Message-State: AOAM533Vehs8y6H+fxZwOu0s7XjWIr+Dt6Q6qsQ39lbJyZfY8dRc9rgQ 6FlGqDicyg+lSYdk6+xUGesiXg0nB4kn5A== X-Google-Smtp-Source: ABdhPJxBAIEUTtSqwBM9/UzSgYMfpvvfqBf8VesiYRq90lKJb5C0LADx8D517uI/al45qDi5bsp0mA== X-Received: by 2002:a17:906:9b96:b0:6f5:cdd:9bf2 with SMTP id dd22-20020a1709069b9600b006f50cdd9bf2mr7611030ejc.364.1653030733549; Fri, 20 May 2022 00:12:13 -0700 (PDT) Received: from [10.0.0.15] (62-47-149-89.adsl.highway.telekom.at. [62.47.149.89]) by smtp.gmail.com with ESMTPSA id em20-20020a170907289400b006f3ef214e66sm2824111ejc.204.2022.05.20.00.12.12 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 20 May 2022 00:12:13 -0700 (PDT) Message-ID: <344acccba555f6a080e5937d7c6bb7af482d8f75.camel@cybertec.at> Subject: Re: Locks under the hood on re-mat and dropping triggers From: Laurenz Albe To: Wells Oliver , pgsql-admin Date: Fri, 20 May 2022 09:12:12 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) MIME-Version: 1.0 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2022-05-19 at 14:37 -0700, Wells Oliver wrote: > Dropping triggers from some table yields a lock while a concurrent refresh of a materialized > view in another schema entirely is running-- why is this? That is because dropping a trigger requires a (brief) ACCESS EXCLUSIVE lock on the table, which conflicts with all concurrent access to the table. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com