Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipBNH-0005j0-CE for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Jan 2020 13:27:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ipBNG-0001sN-6p for pgsql-bugs@arkaria.postgresql.org; Wed, 08 Jan 2020 13:27:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipBNF-0001sG-Mf for pgsql-bugs@lists.postgresql.org; Wed, 08 Jan 2020 13:27:49 +0000 Received: from mail-wr1-x441.google.com ([2a00:1450:4864:20::441]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ipBNC-0003Qr-O9 for pgsql-bugs@lists.postgresql.org; Wed, 08 Jan 2020 13:27:48 +0000 Received: by mail-wr1-x441.google.com with SMTP id q6so3339992wro.9 for ; Wed, 08 Jan 2020 05:27:46 -0800 (PST) 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:references:mime-version :content-disposition:in-reply-to; bh=O52pMUySRhHvMZOCM6jsBNstF1n9+QLL3DLMi+5yq94=; b=TY6YJ+Hwlo3CS4x+wtCorbe9EusQ/dVkCQ6sxgDUEGw4AihCrzs4mRWRjsPLfxiCzA hmQwyqDFVj30vzjohoZS/Kk7AWbPSzSBxytwHhin/qieyDhN/92Q70hO4Rlx4A9wL2qj lPKBHSjYA+yOx2tt2QmawId21cyJv90PM0boQBr4K0oZyUHeck7xjWxNDaiD0ZFFuZcR Isra/Krmrs//HBCIAOmL8nJgp7984e0serOscIZJRGbNs1GflKKseu/ryrSdNkuM2H6q DnQ9XFuY7kvl/7eD0ZSNr6FL30fcYz5YvXy/3b7kjF4deIdRa4chP4DqXUlzkyhAS9ow ewVQ== 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:references :mime-version:content-disposition:in-reply-to; bh=O52pMUySRhHvMZOCM6jsBNstF1n9+QLL3DLMi+5yq94=; b=CoD3XaD4UCOIz078Fn4buEpqlz0y9p98o1AFRSRdaGwInKEOG3fFKNJi6KFcMWA3BI 7GCYu08hekuZza4NOiim53OJhh2DmBRin8NmMU1p2EYQncmAnhPgOFPYeN+b707iDkwS uafW0ObPYvPCh1bqkVXZwUZ04V8XSoD0fYh9Dxbo4Sk3Jlu7t4aGUoS9b6dueMwfSbED cGfHK38fWZnOYu0z8JDSt5TxHtmx+I5iuDRZhIB0l+NNKwRBqFIx2ItpkkGiZcWIBI9u BBLqE1MDWyadoH43qMelJilzoxSHB5cBAaxHbH+N/g1mBRwZFsTomGtgWGHlMgP80/XK opuQ== X-Gm-Message-State: APjAAAUmMEaIOR+5X/jYPir64IodQlSuqSShcETkmTNjCcQjIQ9jONXL ompNj/xuN9OXUGTOnwKqRM33kA== X-Google-Smtp-Source: APXvYqzb0pOCRBJb8p1E1z6aGWskL+Ng93Oe9E0uT34HkPqoCW68HaUz6djmrnHMi0NbuTw+Q5qV7A== X-Received: by 2002:adf:f28c:: with SMTP id k12mr4536541wro.360.1578490063775; Wed, 08 Jan 2020 05:27:43 -0800 (PST) Received: from localhost (static-84-42-175-93.net.upcbroadband.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id n187sm3823882wme.28.2020.01.08.05.27.42 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 08 Jan 2020 05:27:43 -0800 (PST) Date: Wed, 8 Jan 2020 14:27:41 +0100 From: Tomas Vondra To: andy.ye@gtssz.net, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16197: pg_advisory_xact_lock error Message-ID: <20200108132741.ad2gvq3mrox63uc4@development> References: <16197-95f237f0ed016e22@postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: <16197-95f237f0ed016e22@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Wed, Jan 08, 2020 at 10:38:43AM +0000, PG Bug reporting form wrote: >The following bug has been logged on the website: > >Bug reference: 16197 >Logged by: andy ye >Email address: andy.ye@gtssz.net >PostgreSQL version: 9.5.12 >Operating system: ubuntu 16.04 >Description: > >error log: >2020-01-07 09:45:21 UTC:172.21.1.126(41882):daladmin@mobime-pre:[21636]:LOG: > statement: BEGIN >2020-01-07 09:45:21 UTC:172.21.1.126(41882):daladmin@mobime-pre:[21636]:LOG: > statement: SELECT pg_advisory_xact_lock(10006, 498408); >2020-01-07 15:43:14 UTC:172.21.1.126(41882):daladmin@mobime-pre:[21636]:LOG: > duration: 21472423.193 ms > >Hello: >I found that the official documentation seems to introduce very little about >pg_advisory_xact_lock. When I check the postgresql log, I find that it can't >acquire the lock for a long time. Does this mean that the last lock was not >released or not released completely? At present I may need some help: >1. View the SQL that exists in the database pg_advisory_xact_lock and the >SQL that was manually released >2. Corresponds to the fact that I have not obtained the lock for a long >time. Can I set the lock lifetime and automatically release it for a long >time without release, that is, if the lock has not been released for a long >time last time, it will not affect the acquisition of the next lock. What >should I do? Do, since the official documentation is less, I need your >help This is the third thread you started on pgsql-bugs about the issues with advisory locks you're facing. And the other two kinda got abandoned because you have not even responded to people trying to help you. Also, this very much does not seem like a bug - at least you have not provided any data that would suggest it's not a simple pilot error, e.g. because someone else is holding the lock. So pgsql-bugs may not be the best list to discuss thisl. Please do this: 1) Stop sending messages to pgsql-bugs unless when it's about a bug (and you have some data to show it). Use pgsql-general instead. 2) Don't abandon the messages, respond to people trying to help you. 3) For this particular issue, it'd be good to know PID of the session that is waiting, ID of the lock it's waiting on, and data from pg_locks. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services