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 1q48NS-0005IU-Fk for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 23:03:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q48NP-0006bm-QC for pgsql-general@arkaria.postgresql.org; Tue, 30 May 2023 23:03:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q48NP-0006bc-FZ for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 23:03:39 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q48NI-002n8E-IZ for pgsql-general@lists.postgresql.org; Tue, 30 May 2023 23:03:38 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4QW7H11w8kz9sQD; Wed, 31 May 2023 01:03:25 +0200 (CEST) Date: Wed, 31 May 2023 01:03:24 +0200 (CEST) From: Erik Wienhold To: Lian Jiang Cc: pgsql-general@lists.postgresql.org Message-ID: <694730690.528476.1685487804385@office.mailbox.org> In-Reply-To: References: <1154091521.527097.1685482957284@office.mailbox.org> Subject: Re: event trigger should provide more details MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4QW7H11w8kz9sQD List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 31/05/2023 00:28 CEST Lian Jiang wrote: > > The info useful for me is command_tag, object_type, object_identity. > classid, objid is not useful since object_identity is more explicit. > objsubid is not useful because I don't need comment > (https://www.postgresql.org/message-id/Pine.LNX.4.33.0212091822050.15095-100000@leary.csoft.net) > information for schema change. You need objsubid to identify the column in pg_attribute to get its type, not just to get the comment from pg_description as the linked thread says. > Besides table name, I still need: > * which columns are added and their types. > * which columns have type change, the old and new types. > * which columns are dropped. > > Will command field provide this info? I don't have an example and decoding it > needs C code (https://www.postgresql.org/message-id/20190712222343.GA26924%40alvherre.pgsql). > If I cannot get such info from pg_event_trigger_ddl_commands, I may need to > maintain schema snapshots myself and diff the old and new snapshots upon an > alter table/view event. Which way should I go? Thanks a lot. Right off the bat, I would combine it with a ddl_command_start event trigger to record the necessary info (current columns and their types) in a temp table. Query this table in the ddl_command_end event trigger to figure out which columns have changes. This can be done entirely in plpgsql without using the command column. -- Erik