On 2024-02-12 19:20 +0100, Tom Lane wrote:
> I wrote:
> > It strikes me that this is basically to_regtype() with the additional
> > option to return the typmod. That leads to some questions:
>
> BTW, another way that this problem could be approached is to use
> to_regtype() as-is, with a separate function to obtain the typmod:
>
> select format_type(to_regtype('timestamp(4)'), to_regtypmod('timestamp(4)'));
>
> This is intellectually ugly, since it implies parsing the same
> typename string twice. But on the other hand it avoids the notational
> pain and runtime overhead involved in using a record-returning
> function. So I think it might be roughly a wash for performance.
> Question to think about is which way is easier to use. I don't
> have an opinion particularly; just throwing the idea out there.
Out of curiosity, I benchmarked this with the attached to_regtypmod()
patch based on David's v5 applied to a6c21887a9. The script running
pgbench and its output are included at the end.
Just calling parse_type() vs to_regtype()/to_regtypmod() is a wash for
performance as you thought. But format_type() performs better with
to_regtypmod() than with parse_type(). Accessing the record fields
returned by parse_type() adds some overhead.
to_regtypmod() is better for our use case in pgTAP which relies on
format_type() to normalize the type name. The implementation of
to_regtypmod() is also simpler than parse_type(). Usage-wise, both are
clunky IMO.
Benchmark script:
#!/usr/bin/env bash
set -eu
cat <<'SQL' > parse_type.sql
SELECT parse_type('interval second(0)');
SQL
cat <<'SQL' > parse_type_and_format.sql
SELECT format_type(p.typid, p.typmod) FROM parse_type('interval
second(0)') p;
SQL
cat <<'SQL' > to_regtypmod.sql
SELECT to_regtype('interval second(0)'), to_regtypmod('interval
second(0)');
SQL
cat <<'SQL' > to_regtypmod_and_format.sql
SELECT format_type(to_regtype('interval second(0)'),
to_regtypmod('interval second(0)'));
SQL
for f in \
parse_type.sql \
parse_type_and_format.sql \
to_regtypmod.sql \
to_regtypmod_and_format.sql
do
pgbench -n -f "$f" -T10 postgres
echo
done
pgbench output:
pgbench (17devel)
transaction type: parse_type.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 277017
number of failed transactions: 0 (0.000%)
latency average = 0.036 ms
initial connection time = 1.623 ms
tps = 27706.005513 (without initial connection time)
pgbench (17devel)
transaction type: parse_type_and_format.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 222487
number of failed transactions: 0 (0.000%)
latency average = 0.045 ms
initial connection time = 1.603 ms
tps = 22252.095670 (without initial connection time)
pgbench (17devel)
transaction type: to_regtypmod.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 276134
number of failed transactions: 0 (0.000%)
latency average = 0.036 ms
initial connection time = 1.570 ms
tps = 27617.628259 (without initial connection time)
pgbench (17devel)
transaction type: to_regtypmod_and_format.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 270820
number of failed transactions: 0 (0.000%)
latency average = 0.037 ms
initial connection time = 1.631 ms
tps = 27086.331104 (without initial connection time)
--
Erik
>From 0b60432a84d63a7fccaae0fe123a0aa2ae67493b Mon Sep 17 00:00:00 2001
From: Erik Wienhold <[email protected]>
Date: Sun, 18 Feb 2024 17:33:35 +0100
Subject: [PATCH] Add to_regtypmod() for benchmarking against parse_type()
---
src/backend/utils/adt/regproc.c | 18 ++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/regproc.out | 51 +++++++++++++++++++++++++++
src/test/regress/sql/regproc.sql | 26 ++++++++++++++
4 files changed, 98 insertions(+)
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 4fee27a139..6285dc7192 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -2067,3 +2067,21 @@ parse_type(PG_FUNCTION_ARGS)
return HeapTupleGetDatum(rettuple);
#undef PARSE_TYPE_STRING_COLS
}
+
+Datum
+to_regtypmod(PG_FUNCTION_ARGS)
+{
+ const char *type; /* the type string we want to resolve */
+ Oid typid; /* the resolved type oid */
+ int32 typmod; /* the resolved type modifier */
+
+ type = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ /*
+ * Parse type-name argument to obtain type OID and encoded typmod. We
don't
+ * need to handle parseTypeString failure, just let the error be raised.
+ */
+ (void) parseTypeString(type, &typid, &typmod, NULL);
+
+ PG_RETURN_INT32(typmod);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6cad101bca..befeaeec37 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2190,6 +2190,9 @@
proargtypes => 'text', proallargtypes => '{text,oid,int4}',
proargmodes => '{i,o,o}', proargnames => '{typname,typid,typmod}',
prosrc => 'parse_type' },
+{ oid => '8402', descr => 'covert type name to typmod',
+ proname => 'to_regtypmod', provolatile => 's', prorettype => 'int4',
+ proargtypes => 'text', prosrc => 'to_regtypmod' },
{ oid => '1084', descr => 'I/O',
proname => 'date_in', provolatile => 's', prorettype => 'date',
proargtypes => 'cstring', prosrc => 'date_in' },
diff --git a/src/test/regress/expected/regproc.out
b/src/test/regress/expected/regproc.out
index 033f6a81a5..c548b320c3 100644
--- a/src/test/regress/expected/regproc.out
+++ b/src/test/regress/expected/regproc.out
@@ -595,3 +595,54 @@ SELECT format_type(typid, typmod) FROM p;
character varying(128)
(7 rows)
+-- Test to_regtypmod
+SELECT * FROM to_regtypmod('text');
+ to_regtypmod
+--------------
+ -1
+(1 row)
+
+SELECT * FROM to_regtypmod(NULL);
+ to_regtypmod
+--------------
+
+(1 row)
+
+-- Test to_regtypmod errors
+SELECT to_regtypmod('nonesuch'); -- error expected
+ERROR: type "nonesuch" does not exist
+SELECT to_regtypmod('interval nonesuch'); -- grammar error expected
+ERROR: syntax error at or near "nonesuch"
+LINE 1: SELECT to_regtypmod('interval nonesuch');
+ ^
+CONTEXT: invalid type name "interval nonesuch"
+SELECT to_regtypmod('year(4)'); -- grammar error expected
+ERROR: type "year" does not exist
+-- Test to_regtypmod with various aliases and grammar-based types
+WITH s(s) AS (
+ SELECT * FROM unnest(ARRAY[
+ 'timestamp(4)',
+ 'interval(0)',
+ 'interval second(0)',
+ 'timestamptz',
+ 'timestamptz(6)',
+ 'varchar',
+ 'varchar(128)'
+ ])
+),
+p(typid, typmod) AS (
+ SELECT to_regtype(s), to_regtypmod(s)
+ FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
+ format_type
+--------------------------------
+ timestamp(4) without time zone
+ interval(0)
+ interval second(0)
+ timestamp with time zone
+ timestamp(6) with time zone
+ character varying
+ character varying(128)
+(7 rows)
+
diff --git a/src/test/regress/sql/regproc.sql b/src/test/regress/sql/regproc.sql
index 489463aa9e..ac19737012 100644
--- a/src/test/regress/sql/regproc.sql
+++ b/src/test/regress/sql/regproc.sql
@@ -173,3 +173,29 @@ p(typid, typmod) AS (
)
SELECT format_type(typid, typmod) FROM p;
+-- Test to_regtypmod
+SELECT * FROM to_regtypmod('text');
+SELECT * FROM to_regtypmod(NULL);
+
+-- Test to_regtypmod errors
+SELECT to_regtypmod('nonesuch'); -- error expected
+SELECT to_regtypmod('interval nonesuch'); -- grammar error expected
+SELECT to_regtypmod('year(4)'); -- grammar error expected
+
+-- Test to_regtypmod with various aliases and grammar-based types
+WITH s(s) AS (
+ SELECT * FROM unnest(ARRAY[
+ 'timestamp(4)',
+ 'interval(0)',
+ 'interval second(0)',
+ 'timestamptz',
+ 'timestamptz(6)',
+ 'varchar',
+ 'varchar(128)'
+ ])
+),
+p(typid, typmod) AS (
+ SELECT to_regtype(s), to_regtypmod(s)
+ FROM s
+)
+SELECT format_type(typid, typmod) FROM p;
--
2.43.2
