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 1ijQcr-0005AH-7H for pgsql-bugs@arkaria.postgresql.org; Mon, 23 Dec 2019 16:32:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ijQco-0001fh-UU for pgsql-bugs@arkaria.postgresql.org; Mon, 23 Dec 2019 16:32:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ijQco-0001fa-KD for pgsql-bugs@lists.postgresql.org; Mon, 23 Dec 2019 16:32:06 +0000 Received: from mail-yw1-xc41.google.com ([2607:f8b0:4864:20::c41]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ijQcl-00013W-IW for pgsql-bugs@lists.postgresql.org; Mon, 23 Dec 2019 16:32:05 +0000 Received: by mail-yw1-xc41.google.com with SMTP id i126so7270576ywe.7 for ; Mon, 23 Dec 2019 08:32:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:content-transfer-encoding:date:from:to:cc:subject :in-reply-to:references:message-id:user-agent; bh=09ZoKhrS9TxJ+/ngsSzhRBe66pNYafUoTyY5zRwvU/M=; b=piGUi4vuYb0KlO3GylEu2iXaS2esIi2+fmDb2N2fNw4n6ceVptBcyK0Jan6MmVZUI3 D31Zuo51kF9MTtYWlpwbGH0C2dMV2PYdarTLOzm6nvu/d5BJ7M23GaDUQj43O3uRcdT6 DZ9wSUUKrgM4lfACGYvLLHx6rjv+0RkDx7oE6BsYFkskwwFSGLz/DVGUlP4JYtelmNbZ k/KS4/zkNZsDC8IhWCMb7bHLrNKoYQu3nHzbFuPW8cZ29wwnFwHLB2bdu0AFUwpwHpSK fUdjQ8fV+EW67AntEWZg/UYwAJBlICh7bkKPVjDyR8lUwopRReZWpLQSzUjQSOXTs1ff z3sw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:content-transfer-encoding:date:from :to:cc:subject:in-reply-to:references:message-id:user-agent; bh=09ZoKhrS9TxJ+/ngsSzhRBe66pNYafUoTyY5zRwvU/M=; b=m+weoAuK1d+egdZlaw/TXB+WtHWKB4V8DEW5HKV24N1Io97PXkLLjHZpGOI2eU3Hbo pzl8KhU9hVcyckd+F9qzl+QwDs3N3We8M2+wJzyrFbIVNRgt1s/pBnnxJSrMfL07tfte Nr6Ay94/HL8PL06+0ohfNuX/Az2tTlgAjB3RWesanegSbdDVsZxUWZ0mT5ibhduM0jgE pvd5hL9JSa8Kes8ue+7a9Xi7uv84u+GFD7CNHzw77nEQNbYMXcXe6YnZruoeryVDqTyK xEsgDWaFjw+rbCILTx0xeZNSsKn+vuZUxYL867Mis8L7tAYcXKcLgZ6Dr1rZNer4IKaP y79A== X-Gm-Message-State: APjAAAUcS4eb3DoLm0Wl7eCVfOt9KU7I3X/9u6yLgT9E/v9D78f8oCBf Ehe06+WQnVlQ0CP+mdyr6TE= X-Google-Smtp-Source: APXvYqzfLw4xI4ir6Ukv/cFUtYDASK6qwZ05QlA2geW3p8rHWTV6fHRsGcqfeMVgosRAsXsGFTgCwg== X-Received: by 2002:a81:6785:: with SMTP id b127mr23755564ywc.56.1577118721648; Mon, 23 Dec 2019 08:32:01 -0800 (PST) Received: from mail-cube (c-73-74-24-80.hsd1.il.comcast.net. [73.74.24.80]) by smtp.gmail.com with ESMTPSA id h193sm7935434ywc.88.2019.12.23.08.32.00 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 23 Dec 2019 08:32:00 -0800 (PST) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed Content-Transfer-Encoding: 7bit Date: Mon, 23 Dec 2019 10:31:59 -0600 From: Andrei Pozolotin To: Alvaro Herrera Cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16177: pg_event_trigger_ddl_commands() returns empty set for ddl_command_start and "drop table" In-Reply-To: <20191223143638.GA16273@alvherre.pgsql> References: <20191223143638.GA16273@alvherre.pgsql> Message-ID: <511b78e6435d03faa20d605a51aa3578@gmail.com> X-Sender: andrei.pozolotin@gmail.com User-Agent: Roundcube Webmail/1.3.10 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Alvaro, hi: 1. thank you for the idea, I have just tried that 2. I could not make it work, since "sql_drop" event happens **after** the "drop table" has already being attempted and failed due to table oid dependencies 3. you may find it curious that I was in fact trying to improve on the following trigger function: https://github.com/2ndQuadrant/pglogical#automatic-assignment-of-replication-sets-for-new-tables to make it work for "drop table" as well 4. my current workaround is to move trigger logic for "create table"/"drop table" outside of postgre into the app layer, essentially doing via execute() intercept: * replicate_ddl_command("create table; replication_set_add_table();") * replicate_ddl_command("replication_set_remove_table(); drop table;") 5. nonetheless, can you suggest any other way to make "event_trigger" work for this scenario: https://github.com/2ndQuadrant/pglogical#automatic-assignment-of-replication-sets-for-new-tables ? Thanks, Andrei. On 2019-12-23 08:36, Alvaro Herrera wrote: > On 2019-Dec-22, PG Bug reporting form wrote: > >> 1. use case is to delete rows with oid references to the table before >> "drop >> table" > > I think you could achieve that with an event trigger on event sql_drop. > IIRC that event trigger is run for the objects before the drops are > actually executed. > > ddl_command_start is pretty useless, because it doesn't have much info > abot the command being run; I'm sure that > pg_event_trigger_ddl_commands() would always return empty there. > The function would return non-empty only during ddl_command_end, but > that's no useful to you because the drops will already have run.