agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Mark Wong <markwkm@gmail.com>
To: Chao Li <li.evan.chao@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: updates for handling optional argument in system functions
Date: Thu, 29 Jan 2026 16:15:18 -0800
Message-ID: <aXv4FjR1RTr386D1@o> (raw)
In-Reply-To: <0604F7D7-EABE-47CA-A58A-231CBD0DA659@gmail.com>
References: <aTiwu9o8vBkLreAL@ltdrgnflg2>
<aW_GMVYZqGnebYLE@o>
<0604F7D7-EABE-47CA-A58A-231CBD0DA659@gmail.com>
On Wed, Jan 21, 2026 at 04:45:35PM +0800, Chao Li wrote:
>
>
> > On Jan 21, 2026, at 02:15, Mark Wong <markwkm@gmail.com> 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 <markwkm@gmail.com>
EDB https://enterprisedb.com
Attachments:
[text/x-diff] v3-0001-Track-retired-system-procedures-in-pg_retired.dat.patch (3.0K, ../aXv4FjR1RTr386D1@o/2-v3-0001-Track-retired-system-procedures-in-pg_retired.dat.patch)
download | inline diff:
From 51b5d084bb6065a5d908a4e71fd4dcedf0d2f993 Mon Sep 17 00:00:00 2001
From: Mark Wong <mark.wong@enterprisedb.com>
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
[text/x-diff] v3-0002-Handle-pg_get_ruledef-default-args-in-system_func.patch (3.5K, ../aXv4FjR1RTr386D1@o/3-v3-0002-Handle-pg_get_ruledef-default-args-in-system_func.patch)
download | inline diff:
From b1173574478c61d6001da5c6caf8b6052dde20ad Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
[text/x-diff] v3-0003-Handle-pg_get_viewdef-default-args-in-system_func.patch (5.0K, ../aXv4FjR1RTr386D1@o/4-v3-0003-Handle-pg_get_viewdef-default-args-in-system_func.patch)
download | inline diff:
From ced739f304da4dc36f5b57120bf5cd9adb55ffc2 Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
[text/x-diff] v3-0004-Handle-pg_get_indexdef-default-args-in-system_fun.patch (3.8K, ../aXv4FjR1RTr386D1@o/5-v3-0004-Handle-pg_get_indexdef-default-args-in-system_fun.patch)
download | inline diff:
From 9387c816f2b19f6317106b7e1151262b4506c259 Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
[text/x-diff] v3-0005-Handle-pg_get_constraintdef-default-args-in-syste.patch (3.8K, ../aXv4FjR1RTr386D1@o/6-v3-0005-Handle-pg_get_constraintdef-default-args-in-syste.patch)
download | inline diff:
From 7f4b48ef95c61bb3a82e54a8d1981c82879f91f8 Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
[text/x-diff] v3-0006-Handle-pg_get_expr-default-args-in-system_functio.patch (4.4K, ../aXv4FjR1RTr386D1@o/7-v3-0006-Handle-pg_get_expr-default-args-in-system_functio.patch)
download | inline diff:
From 334e0e4d4834800c824304d67e7a88fdd6c43b94 Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
[text/x-diff] v3-0007-Handle-pg_get_triggerdef-default-args-in-system_f.patch (3.5K, ../aXv4FjR1RTr386D1@o/8-v3-0007-Handle-pg_get_triggerdef-default-args-in-system_f.patch)
download | inline diff:
From 0b96cf0a17fec1be6cb0afd40b1bd03d7cadc4a6 Mon Sep 17 00:00:00 2001
From: Mark Wong <markwkm@gmail.com>
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
view thread (31+ messages) latest in thread
Message-ID: <aXv4FjR1RTr386D1@o>
Permalink: ../aXv4FjR1RTr386D1@o/
Also on: postgresql.org/message-id/aXv4FjR1RTr386D1@o
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: markwkm@gmail.com, li.evan.chao@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: updates for handling optional argument in system functions
In-Reply-To: <aXv4FjR1RTr386D1@o>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox