Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlcAa-001P91-2K for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jan 2026 00:15:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vlcAY-0024o7-1s for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jan 2026 00:15:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlcAY-0024nz-0c for pgsql-hackers@lists.postgresql.org; Fri, 30 Jan 2026 00:15:27 +0000 Received: from mail-dy1-x1331.google.com ([2607:f8b0:4864:20::1331]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vlcAW-000000006Kv-1DIJ for pgsql-hackers@lists.postgresql.org; Fri, 30 Jan 2026 00:15:26 +0000 Received: by mail-dy1-x1331.google.com with SMTP id 5a478bee46e88-2b785801c93so3986654eec.0 for ; Thu, 29 Jan 2026 16:15:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1769732122; x=1770336922; darn=lists.postgresql.org; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=zMW8r2Cey+V3irfFZdGrbVO/AMbgiwbWcxUBeXBwOBc=; b=LbYBKVKmhivkNHwrZLMwx+8QTplE6tV7fBlb1Hg9M1JvfhURDbhAcu/WdZM0wP35BL yd7A92+u8k02n4IMUzTXa4lYwaTO5wYUBsEAPBbtBDV2Pqd66OBoga3AKw9mVZfTsjoU uC+sy2sP5cd+v77fcmaYM6CTWse6qJFIBAzP89OQ0HDuBOtDkUVAzc8mxUbouxSCy1Bz /1OMJ0MjHkUTtoxINZDw6yMR84j07rNmQ/6KtnVFSFyeD2pgIC02+33zaSkLPMQPcY82 ewk5K5+ZxKjGJm4eHV2z+jmZWTIzWcofIdTLLmM26KXoITEIM8o7E8KRK86rrSlAzCNt jvLA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1769732122; x=1770336922; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=zMW8r2Cey+V3irfFZdGrbVO/AMbgiwbWcxUBeXBwOBc=; b=TUnnKnu+gJ9iVwVR64lfpI+qb2OjNUBZy9N0ePELCp6Jo/koX1lPNucYqp8rwl6pws 0cMJQVl6CNE/r8ip2RKN189abPKr3ZAgE66+FM0AoRX5VfreF7O4GMItP6lolpcYsCN2 15ZvO3fb7RhOIkUYzXqVxsjD6CnssiTeU3Kud9ZJ+C7YAEu9hfG8iKDvVmsL1rAchhhy IIPFS9dbk2yaOLTw6K4lTWrotV+HDc8cILoINIoSWfBkLvcZkg/FGOR4ELV0n0KOYJ7L 5nS8BAtSNDK2yPU3NW2YgnUIkG8oTOTIYEU68jprJPYMpDCDbRoCfOGlMy0CgnsH48BE NwAg== X-Gm-Message-State: AOJu0YzjzwQ77qYFCP3tlmOPCCzW2Sf/FYpETiuDKRpccJ8FFZ5VFrVr FzssSjvI2UIZBgl8LTz5nCS2hgBS6VJs5cLiKBKdseJCaeYi9fycSYPu X-Gm-Gg: AZuq6aIX+7TbgrSvEgU8ch+bYmmqclPwBl5uYxBlMz65xIJHiVYtZlkD4hpIwel/5HC oyznO67sSDaJtOMnlbLZ9TxiXcGaH00TUlVCenK3vVnGKOQ4EqaYytnZzgx9zXa56gTbUmSx60o XoaFUVk13key+V/mqULzESMbjOH7QQLT8P4M3n+lI2aiGWCBju48WprItfSCKj7uOX14AJMBSQw qa3eYzFY5XnNr5U5laDJifZZ+vnY4s5ZnJhF/r5sm2GO4+lNZ/bE0rEyz1fB9FoKrrkbYTkgf6+ t2Ewxp9PifGlMB9dnZP4Kr0Urff04gRum5PRKs/jnZlwji3XeCBbq00KMTDxh0SN4q5lKNt6TDm vFnVuRlWi81iP7v2uR/pVDkCIKULeuD+X+lYMqbKGomKiCQn4X/FxIVC08Zh3Ff3VGiptrFzyPc mtUK6n2+bW/KLRjL5edIk= X-Received: by 2002:a05:7300:220f:b0:2b6:aeb8:3d8c with SMTP id 5a478bee46e88-2b7c891b410mr555501eec.32.1769732121845; Thu, 29 Jan 2026 16:15:21 -0800 (PST) Received: from o (71-34-92-171.ptld.qwest.net. [71.34.92.171]) by smtp.gmail.com with ESMTPSA id 5a478bee46e88-2b7a16cf8f2sm10067892eec.7.2026.01.29.16.15.20 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 29 Jan 2026 16:15:20 -0800 (PST) Date: Thu, 29 Jan 2026 16:15:18 -0800 From: Mark Wong To: Chao Li Cc: pgsql-hackers@lists.postgresql.org Subject: Re: updates for handling optional argument in system functions Message-ID: References: <0604F7D7-EABE-47CA-A58A-231CBD0DA659@gmail.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="zd8Z8rlPOcTi77n/" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <0604F7D7-EABE-47CA-A58A-231CBD0DA659@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --zd8Z8rlPOcTi77n/ Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Wed, Jan 21, 2026 at 04:45:35PM +0800, Chao Li wrote: > > > > On Jan 21, 2026, at 02:15, Mark Wong wrote: > > > > Hi everyone, > > > > On Tue, Dec 09, 2025 at 03:28:59PM -0800, Mark Wong wrote: > >> Hi everyone, > >> > >> I noticed how it was preferred to define optional arguments with the > >> system functions in system_functions.sql instead of defining them in > >> pg_proc.dat. > >> > >> I took a gross stab at updating the ones that ended in _ext, which > >> turned out to be 7 declarations across 6 system functions, and created a > >> patch per system function, hoping it would be easier to review. > >> > >> Perhaps the most interesting thing to share is the total reduction of > >> the lines of code, although system_functions.sql only grows: > >> > >> src/backend/catalog/system_functions.sql | 49 ++++++++ > >> src/backend/utils/adt/ruleutils.c | 130 ---------------------- > >> src/include/catalog/pg_proc.dat | 36 ++---- > >> 3 files changed, 56 insertions(+), 159 deletions(-) > >> > >> > >> Is that something we want? > > > > I fixed an error caught by the address sanitizer in CI [1] and am uploading a > > new patchset. The only change is to 2 lines in > > v2-0005-Handle-pg_get_expr-default-args-in-system_functio.patch to update a > > call to pg_get_expr with the correct number of arguments in tablecmds.c. > > > > Regards, > > Mark > > > > [1] https://cirrus-ci.com/task/6109065824174080 > > Hi Mark, > > Thanks for the patch. After reviewing it, I have mixed feelings. From one side, it removes some redundant code, which is good. In the other side, I doubt if we should delete proc entries from pg_proc.c? Say, there is a view that uses a proc to be deleted, the proc OID is stored with the view, then after an upgrade, the view would be broken. From this perspective, should we retain the old proc entries and only point them to the new functions? I don't have a solution for the case of a view storing the OID, but Álvaro Herrera suggested to me to at least try preventing those OIDs from being reused. I've attached a v3 patch set that introduces src/include/catalog/pg_retired.dat to store previously used OIDs and procedure names that the scripts unused_oids and renumber_oids.pl can consume to prevent the reuse of retired OIDs. Maybe that can also be used towards finding that particular solution... Regards, Mark -- Mark Wong EDB https://enterprisedb.com --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0001-Track-retired-system-procedures-in-pg_retired.dat.patch" From 51b5d084bb6065a5d908a4e71fd4dcedf0d2f993 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Thu, 29 Jan 2026 15:47:50 -0800 Subject: [PATCH v3 1/7] Track retired system procedures in pg_retired.dat System procedures may be removed from time to time. Keep track of previously used OIDs to prevent potential conflicts from reusing OIDs after an upgrade. --- src/include/catalog/pg_retired.dat | 20 ++++++++++++++++++++ src/include/catalog/renumber_oids.pl | 9 +++++++++ src/include/catalog/unused_oids | 7 +++++++ 3 files changed, 36 insertions(+) create mode 100644 src/include/catalog/pg_retired.dat diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat new file mode 100644 index 00000000000..413b68ad1cd --- /dev/null +++ b/src/include/catalog/pg_retired.dat @@ -0,0 +1,20 @@ +#---------------------------------------------------------------------- +# +# pg_retired.dat +# Entries that have been removed from pg_proc.dat with OIDs that are +# are not to be reused. +# +# Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group +# Portions Copyright (c) 1994, Regents of the University of California +# +# src/include/catalog/pg_retired.dat +# +#---------------------------------------------------------------------- + +[ + +# What's primarily important here is knowing which OIDs are not to be reused. +# At the same time, it may be good to be reminded what procedure was associated +# with it. + +] diff --git a/src/include/catalog/renumber_oids.pl b/src/include/catalog/renumber_oids.pl index f6e25d0f1e1..7489be02a50 100755 --- a/src/include/catalog/renumber_oids.pl +++ b/src/include/catalog/renumber_oids.pl @@ -68,6 +68,15 @@ my $oids = Catalog::FindAllOidsFromHeaders(@header_files); my %oidhash; @oidhash{@$oids} = undef; +# Also include OIDs listed in pg_retired.dat to prevent new OIDs to be mapped +# to OIDs that have been retired. +my $retired_data = Catalog::ParseData('pg_retired.dat', undef, 0); +foreach my $row (@$retired_data) +{ + $oidhash{ $row->{oid} } = undef + if ref $row eq 'HASH' && defined $row->{oid}; +} + # Select new OIDs for existing OIDs in the mapped range. # We do this first so that we preserve the ordering of the mapped OIDs # (for reproducibility's sake), and so that if we fail due to running out diff --git a/src/include/catalog/unused_oids b/src/include/catalog/unused_oids index 147fba5e209..aeb485d9b5e 100755 --- a/src/include/catalog/unused_oids +++ b/src/include/catalog/unused_oids @@ -32,6 +32,13 @@ my @input_files = glob("pg_*.h"); my $oids = Catalog::FindAllOidsFromHeaders(@input_files); +# Also exclude OIDs listed in pg_retired.dat +my $retired_data = Catalog::ParseData('pg_retired.dat', undef, 0); +foreach my $row (@$retired_data) +{ + push @{$oids}, $row->{oid} if ref $row eq 'HASH' && defined $row->{oid}; +} + # Also push FirstGenbkiObjectId to serve as a terminator for the last gap. my $FirstGenbkiObjectId = Catalog::FindDefinedSymbol('access/transam.h', '..', 'FirstGenbkiObjectId'); -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0002-Handle-pg_get_ruledef-default-args-in-system_func.patch" From b1173574478c61d6001da5c6caf8b6052dde20ad Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Mon, 8 Dec 2025 15:41:07 -0800 Subject: [PATCH v3 2/7] Handle pg_get_ruledef default args in system_functions.sql Modernize pg_get_ruledef to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/utils/adt/ruleutils.c | 18 ------------------ src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 2 ++ 4 files changed, 10 insertions(+), 22 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index eb9e31ae1bf..3aad2fbf8f3 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -657,6 +657,13 @@ LANGUAGE INTERNAL STRICT VOLATILE PARALLEL UNSAFE AS 'pg_replication_origin_session_setup'; +CREATE OR REPLACE FUNCTION + pg_get_ruledef(rule oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_ruledef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 033b625f3fc..3733212fe0a 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -558,24 +558,6 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan, */ Datum pg_get_ruledef(PG_FUNCTION_ARGS) -{ - Oid ruleoid = PG_GETARG_OID(0); - int prettyFlags; - char *res; - - prettyFlags = PRETTYFLAG_INDENT; - - res = pg_get_ruledef_worker(ruleoid, prettyFlags); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - - -Datum -pg_get_ruledef_ext(PG_FUNCTION_ARGS) { Oid ruleoid = PG_GETARG_OID(0); bool pretty = PG_GETARG_BOOL(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 894b6a1b6d6..2a3362567a7 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3961,9 +3961,6 @@ proargtypes => 'oid oid', prosrc => 'oidge' }, # System-view support functions -{ oid => '1573', descr => 'source text of a rule', - proname => 'pg_get_ruledef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid', prosrc => 'pg_get_ruledef' }, { oid => '1640', descr => 'select statement of a view', proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', prorettype => 'text', proargtypes => 'text', @@ -8508,7 +8505,7 @@ # System-view support functions with pretty-print option { oid => '2504', descr => 'source text of a rule with pretty-print option', proname => 'pg_get_ruledef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid bool', prosrc => 'pg_get_ruledef_ext' }, + proargtypes => 'oid bool', prosrc => 'pg_get_ruledef' }, { oid => '2505', descr => 'select statement of a view with pretty-print option', proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index 413b68ad1cd..a3ce10c7f62 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -17,4 +17,6 @@ # At the same time, it may be good to be reminded what procedure was associated # with it. +{ oid => '1573', proname => 'pg_get_ruledef' } + ] -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0003-Handle-pg_get_viewdef-default-args-in-system_func.patch" From ced739f304da4dc36f5b57120bf5cd9adb55ffc2 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Tue, 9 Dec 2025 09:33:21 -0800 Subject: [PATCH v3 3/7] Handle pg_get_viewdef default args in system_functions.sql Modernize pg_get_viewdef to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument for both versions that use OID or view name. --- src/backend/catalog/system_functions.sql | 14 ++++++++ src/backend/utils/adt/ruleutils.c | 44 ------------------------ src/include/catalog/pg_proc.dat | 11 ++---- src/include/catalog/pg_retired.dat | 4 ++- 4 files changed, 19 insertions(+), 54 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 3aad2fbf8f3..4ddbf194fa8 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -664,6 +664,20 @@ LANGUAGE INTERNAL PARALLEL SAFE AS 'pg_get_ruledef'; +CREATE OR REPLACE FUNCTION + pg_get_viewdef(view text, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL RESTRICTED +AS 'pg_get_viewdef_name'; + +CREATE OR REPLACE FUNCTION + pg_get_viewdef(view oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL RESTRICTED +AS 'pg_get_viewdef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 3733212fe0a..29557f3c9d2 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -658,25 +658,6 @@ pg_get_ruledef_worker(Oid ruleoid, int prettyFlags) */ Datum pg_get_viewdef(PG_FUNCTION_ARGS) -{ - /* By OID */ - Oid viewoid = PG_GETARG_OID(0); - int prettyFlags; - char *res; - - prettyFlags = PRETTYFLAG_INDENT; - - res = pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - - -Datum -pg_get_viewdef_ext(PG_FUNCTION_ARGS) { /* By OID */ Oid viewoid = PG_GETARG_OID(0); @@ -716,31 +697,6 @@ pg_get_viewdef_wrap(PG_FUNCTION_ARGS) Datum pg_get_viewdef_name(PG_FUNCTION_ARGS) -{ - /* By qualified name */ - text *viewname = PG_GETARG_TEXT_PP(0); - int prettyFlags; - RangeVar *viewrel; - Oid viewoid; - char *res; - - prettyFlags = PRETTYFLAG_INDENT; - - /* Look up view name. Can't lock it - we might not have privileges. */ - viewrel = makeRangeVarFromNameList(textToQualifiedNameList(viewname)); - viewoid = RangeVarGetRelid(viewrel, NoLock, false); - - res = pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - - -Datum -pg_get_viewdef_name_ext(PG_FUNCTION_ARGS) { /* By qualified name */ text *viewname = PG_GETARG_TEXT_PP(0); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 2a3362567a7..531d0dea7db 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3961,13 +3961,6 @@ proargtypes => 'oid oid', prosrc => 'oidge' }, # System-view support functions -{ oid => '1640', descr => 'select statement of a view', - proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', - prorettype => 'text', proargtypes => 'text', - prosrc => 'pg_get_viewdef_name' }, -{ oid => '1641', descr => 'select statement of a view', - proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', - prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_viewdef' }, { oid => '1642', descr => 'role name by OID (with fallback)', proname => 'pg_get_userbyid', provolatile => 's', prorettype => 'name', proargtypes => 'oid', prosrc => 'pg_get_userbyid' }, @@ -8510,12 +8503,12 @@ descr => 'select statement of a view with pretty-print option', proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', prorettype => 'text', proargtypes => 'text bool', - prosrc => 'pg_get_viewdef_name_ext' }, + prosrc => 'pg_get_viewdef_name' }, { oid => '2506', descr => 'select statement of a view with pretty-print option', proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', prorettype => 'text', proargtypes => 'oid bool', - prosrc => 'pg_get_viewdef_ext' }, + prosrc => 'pg_get_viewdef' }, { oid => '3159', descr => 'select statement of a view with pretty-printing and specified line wrapping', proname => 'pg_get_viewdef', provolatile => 's', proparallel => 'r', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index a3ce10c7f62..d5d51ddb3e8 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -17,6 +17,8 @@ # At the same time, it may be good to be reminded what procedure was associated # with it. -{ oid => '1573', proname => 'pg_get_ruledef' } +{ oid => '1573', proname => 'pg_get_ruledef' }, +{ oid => '1640', proname => 'pg_get_viewdef' }, +{ oid => '1641', proname => 'pg_get_viewdef' } ] -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0004-Handle-pg_get_indexdef-default-args-in-system_fun.patch" From 9387c816f2b19f6317106b7e1151262b4506c259 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Tue, 9 Dec 2025 10:02:15 -0800 Subject: [PATCH v3 4/7] Handle pg_get_indexdef default args in system_functions.sql Modernize pg_get_indexdef to use CREATE OR REPLACE FUNCTION to handle the optional column and pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/utils/adt/ruleutils.c | 20 -------------------- src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 3 ++- 4 files changed, 10 insertions(+), 25 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 4ddbf194fa8..72f5fdc06f9 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -678,6 +678,13 @@ LANGUAGE INTERNAL PARALLEL RESTRICTED AS 'pg_get_viewdef'; +CREATE OR REPLACE FUNCTION + pg_get_indexdef(view oid, "column" int DEFAULT 0, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_indexdef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 29557f3c9d2..d8b8d7b4d38 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1114,26 +1114,6 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) */ Datum pg_get_indexdef(PG_FUNCTION_ARGS) -{ - Oid indexrelid = PG_GETARG_OID(0); - int prettyFlags; - char *res; - - prettyFlags = PRETTYFLAG_INDENT; - - res = pg_get_indexdef_worker(indexrelid, 0, NULL, - false, false, - false, false, - prettyFlags, true); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - -Datum -pg_get_indexdef_ext(PG_FUNCTION_ARGS) { Oid indexrelid = PG_GETARG_OID(0); int32 colno = PG_GETARG_INT32(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 531d0dea7db..fee5efca41e 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3964,9 +3964,6 @@ { oid => '1642', descr => 'role name by OID (with fallback)', proname => 'pg_get_userbyid', provolatile => 's', prorettype => 'name', proargtypes => 'oid', prosrc => 'pg_get_userbyid' }, -{ oid => '1643', descr => 'index description', - proname => 'pg_get_indexdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid', prosrc => 'pg_get_indexdef' }, { oid => '3415', descr => 'extended statistics object description', proname => 'pg_get_statisticsobjdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', @@ -8517,7 +8514,7 @@ { oid => '2507', descr => 'index description (full create statement or single expression) with pretty-print option', proname => 'pg_get_indexdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid int4 bool', prosrc => 'pg_get_indexdef_ext' }, + proargtypes => 'oid int4 bool', prosrc => 'pg_get_indexdef' }, { oid => '2508', descr => 'constraint description with pretty-print option', proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' }, diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index d5d51ddb3e8..768ce980a1d 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -19,6 +19,7 @@ { oid => '1573', proname => 'pg_get_ruledef' }, { oid => '1640', proname => 'pg_get_viewdef' }, -{ oid => '1641', proname => 'pg_get_viewdef' } +{ oid => '1641', proname => 'pg_get_viewdef' }, +{ oid => '1643', proname => 'pg_get_indexdef' } ] -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0005-Handle-pg_get_constraintdef-default-args-in-syste.patch" From 7f4b48ef95c61bb3a82e54a8d1981c82879f91f8 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Tue, 9 Dec 2025 10:59:41 -0800 Subject: [PATCH v3 5/7] Handle pg_get_constraintdef default args in system_functions.sql Modernize pg_get_constraintdef to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/utils/adt/ruleutils.c | 17 ----------------- src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 1 + 4 files changed, 9 insertions(+), 21 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 72f5fdc06f9..1824faab231 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -685,6 +685,13 @@ LANGUAGE INTERNAL PARALLEL SAFE AS 'pg_get_indexdef'; +CREATE OR REPLACE FUNCTION + pg_get_constraintdef("constraint" oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_constraintdef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d8b8d7b4d38..4f2c93f1ee2 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2061,23 +2061,6 @@ pg_get_partconstrdef_string(Oid partitionId, char *aliasname) */ Datum pg_get_constraintdef(PG_FUNCTION_ARGS) -{ - Oid constraintId = PG_GETARG_OID(0); - int prettyFlags; - char *res; - - prettyFlags = PRETTYFLAG_INDENT; - - res = pg_get_constraintdef_worker(constraintId, false, prettyFlags, true); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - -Datum -pg_get_constraintdef_ext(PG_FUNCTION_ARGS) { Oid constraintId = PG_GETARG_OID(0); bool pretty = PG_GETARG_BOOL(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index fee5efca41e..ffec11c5539 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3986,9 +3986,6 @@ { oid => '1662', descr => 'trigger description', proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_triggerdef' }, -{ oid => '1387', descr => 'constraint description', - proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid', prosrc => 'pg_get_constraintdef' }, { oid => '1716', descr => 'deparse an encoded expression', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' }, @@ -8517,7 +8514,7 @@ proargtypes => 'oid int4 bool', prosrc => 'pg_get_indexdef' }, { oid => '2508', descr => 'constraint description with pretty-print option', proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' }, + proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef' }, { oid => '2509', descr => 'deparse an encoded expression with pretty-print option', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index 768ce980a1d..90928a9cb00 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -17,6 +17,7 @@ # At the same time, it may be good to be reminded what procedure was associated # with it. +{ oid => '1387', proname => 'pg_get_constraintdef' }, { oid => '1573', proname => 'pg_get_ruledef' }, { oid => '1640', proname => 'pg_get_viewdef' }, { oid => '1641', proname => 'pg_get_viewdef' }, -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0006-Handle-pg_get_expr-default-args-in-system_functio.patch" From 334e0e4d4834800c824304d67e7a88fdd6c43b94 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Tue, 9 Dec 2025 11:17:56 -0800 Subject: [PATCH v3 6/7] Handle pg_get_expr default args in system_functions.sql Modernize pg_get_expr to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/commands/tablecmds.c | 4 ++-- src/backend/utils/adt/ruleutils.c | 17 ----------------- src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 3 ++- 5 files changed, 12 insertions(+), 24 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 1824faab231..a6f7cdf3a36 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -692,6 +692,13 @@ LANGUAGE INTERNAL PARALLEL SAFE AS 'pg_get_constraintdef'; +CREATE OR REPLACE FUNCTION + pg_get_expr(expr pg_node_tree, relation oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_expr'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f976c0e5c7e..12c4ce08437 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -17480,8 +17480,8 @@ decompile_conbin(HeapTuple contup, TupleDesc tupdesc) if (isnull) elog(ERROR, "null conbin for constraint %u", con->oid); - expr = DirectFunctionCall2(pg_get_expr, attr, - ObjectIdGetDatum(con->conrelid)); + expr = DirectFunctionCall3(pg_get_expr, attr, + ObjectIdGetDatum(con->conrelid), false); return TextDatumGetCString(expr); } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 4f2c93f1ee2..d87b361a093 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2573,23 +2573,6 @@ decompile_column_index_array(Datum column_index_array, Oid relId, */ Datum pg_get_expr(PG_FUNCTION_ARGS) -{ - text *expr = PG_GETARG_TEXT_PP(0); - Oid relid = PG_GETARG_OID(1); - text *result; - int prettyFlags; - - prettyFlags = PRETTYFLAG_INDENT; - - result = pg_get_expr_worker(expr, relid, prettyFlags); - if (result) - PG_RETURN_TEXT_P(result); - else - PG_RETURN_NULL(); -} - -Datum -pg_get_expr_ext(PG_FUNCTION_ARGS) { text *expr = PG_GETARG_TEXT_PP(0); Oid relid = PG_GETARG_OID(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index ffec11c5539..4304ab220ba 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3986,9 +3986,6 @@ { oid => '1662', descr => 'trigger description', proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_triggerdef' }, -{ oid => '1716', descr => 'deparse an encoded expression', - proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', - proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' }, { oid => '1665', descr => 'name of sequence for a serial column', proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text', proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' }, @@ -8518,7 +8515,7 @@ { oid => '2509', descr => 'deparse an encoded expression with pretty-print option', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', - proargtypes => 'pg_node_tree oid bool', prosrc => 'pg_get_expr_ext' }, + proargtypes => 'pg_node_tree oid bool', prosrc => 'pg_get_expr' }, { oid => '2510', descr => 'get the prepared statements for this session', proname => 'pg_prepared_statement', prorows => '1000', proretset => 't', provolatile => 's', proparallel => 'r', prorettype => 'record', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index 90928a9cb00..8b5f58850f6 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -21,6 +21,7 @@ { oid => '1573', proname => 'pg_get_ruledef' }, { oid => '1640', proname => 'pg_get_viewdef' }, { oid => '1641', proname => 'pg_get_viewdef' }, -{ oid => '1643', proname => 'pg_get_indexdef' } +{ oid => '1643', proname => 'pg_get_indexdef' }, +{ oid => '1716', proname => 'pg_get_expr' } ] -- 2.43.0 --zd8Z8rlPOcTi77n/ Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v3-0007-Handle-pg_get_triggerdef-default-args-in-system_f.patch" From 0b96cf0a17fec1be6cb0afd40b1bd03d7cadc4a6 Mon Sep 17 00:00:00 2001 From: Mark Wong Date: Tue, 9 Dec 2025 11:51:39 -0800 Subject: [PATCH v3 7/7] Handle pg_get_triggerdef default args in system_functions.sql Modernize pg_get_triggerdef to use CREATE OR REPLACE FUNCTION to handle the optional pretty argument. --- src/backend/catalog/system_functions.sql | 7 +++++++ src/backend/utils/adt/ruleutils.c | 14 -------------- src/include/catalog/pg_proc.dat | 5 +---- src/include/catalog/pg_retired.dat | 1 + 4 files changed, 9 insertions(+), 18 deletions(-) diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index a6f7cdf3a36..08b9b68d0cd 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -699,6 +699,13 @@ LANGUAGE INTERNAL PARALLEL SAFE AS 'pg_get_expr'; +CREATE OR REPLACE FUNCTION + pg_get_triggerdef(trigger oid, pretty bool DEFAULT false) +RETURNS TEXT +LANGUAGE INTERNAL +PARALLEL SAFE +AS 'pg_get_triggerdef'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d87b361a093..598c916ddb6 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -807,20 +807,6 @@ pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn) */ Datum pg_get_triggerdef(PG_FUNCTION_ARGS) -{ - Oid trigid = PG_GETARG_OID(0); - char *res; - - res = pg_get_triggerdef_worker(trigid, false); - - if (res == NULL) - PG_RETURN_NULL(); - - PG_RETURN_TEXT_P(string_to_text(res)); -} - -Datum -pg_get_triggerdef_ext(PG_FUNCTION_ARGS) { Oid trigid = PG_GETARG_OID(0); bool pretty = PG_GETARG_BOOL(1); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4304ab220ba..bf00983b3ff 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3983,9 +3983,6 @@ proname => 'pg_get_partition_constraintdef', provolatile => 's', prorettype => 'text', proargtypes => 'oid', prosrc => 'pg_get_partition_constraintdef' }, -{ oid => '1662', descr => 'trigger description', - proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid', prosrc => 'pg_get_triggerdef' }, { oid => '1665', descr => 'name of sequence for a serial column', proname => 'pg_get_serial_sequence', provolatile => 's', prorettype => 'text', proargtypes => 'text text', prosrc => 'pg_get_serial_sequence' }, @@ -8551,7 +8548,7 @@ prosrc => 'pg_timezone_names' }, { oid => '2730', descr => 'trigger description with pretty-print option', proname => 'pg_get_triggerdef', provolatile => 's', prorettype => 'text', - proargtypes => 'oid bool', prosrc => 'pg_get_triggerdef_ext' }, + proargtypes => 'oid bool', prosrc => 'pg_get_triggerdef' }, # asynchronous notifications { oid => '3035', diff --git a/src/include/catalog/pg_retired.dat b/src/include/catalog/pg_retired.dat index 8b5f58850f6..46764648040 100644 --- a/src/include/catalog/pg_retired.dat +++ b/src/include/catalog/pg_retired.dat @@ -22,6 +22,7 @@ { oid => '1640', proname => 'pg_get_viewdef' }, { oid => '1641', proname => 'pg_get_viewdef' }, { oid => '1643', proname => 'pg_get_indexdef' }, +{ oid => '1662', proname => 'pg_get_triggerdef' }, { oid => '1716', proname => 'pg_get_expr' } ] -- 2.43.0 --zd8Z8rlPOcTi77n/--