agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feed[PATCH 3/3] split try_partitionwise_join
23+ messages / 10 participants
[nested] [flat]
* [PATCH 3/3] split try_partitionwise_join
@ 2020-03-26 00:15 Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 23+ messages in thread
From: Tomas Vondra @ 2020-03-26 00:15 UTC (permalink / raw)
---
src/backend/optimizer/path/joinrels.c | 146 ++++++++++++++------------
1 file changed, 78 insertions(+), 68 deletions(-)
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 0b082fc915..314b0267c3 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1333,71 +1333,12 @@ restriction_is_constant_false(List *restrictlist,
return false;
}
-/*
- * Assess whether join between given two partitioned relations can be broken
- * down into joins between matching partitions; a technique called
- * "partitionwise join"
- *
- * Partitionwise join is possible when a. Joining relations have same
- * partitioning scheme b. There exists an equi-join between the partition keys
- * of the two relations.
- *
- * Partitionwise join is planned as follows (details: optimizer/README.)
- *
- * 1. Create the RelOptInfos for joins between matching partitions i.e
- * child-joins and add paths to them.
- *
- * 2. Construct Append or MergeAppend paths across the set of child joins.
- * This second phase is implemented by generate_partitionwise_join_paths().
- *
- * The RelOptInfo, SpecialJoinInfo and restrictlist for each child join are
- * obtained by translating the respective parent join structures.
- */
static void
-try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
- RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
- List *parent_restrictlist)
+compute_partition_bounds(PlannerInfo *root, RelOptInfo *rel1,
+ RelOptInfo *rel2, RelOptInfo *joinrel,
+ SpecialJoinInfo *parent_sjinfo,
+ List **parts1, List **parts2)
{
- bool rel1_is_simple = IS_SIMPLE_REL(rel1);
- bool rel2_is_simple = IS_SIMPLE_REL(rel2);
- List *parts1 = NIL;
- List *parts2 = NIL;
- ListCell *lcr1 = NULL;
- ListCell *lcr2 = NULL;
- int cnt_parts;
-
- /* Guard against stack overflow due to overly deep partition hierarchy. */
- check_stack_depth();
-
- /* Nothing to do, if the join relation is not partitioned. */
- if (joinrel->part_scheme == NULL || joinrel->nparts == 0)
- return;
-
- /* The join relation should have consider_partitionwise_join set. */
- Assert(joinrel->consider_partitionwise_join);
-
- /*
- * We can not perform partitionwise join if either of the joining relations
- * is not partitioned.
- */
- if (!IS_PARTITIONED_REL(rel1) || !IS_PARTITIONED_REL(rel2))
- return;
-
- Assert(REL_HAS_ALL_PART_PROPS(rel1) && REL_HAS_ALL_PART_PROPS(rel2));
-
- /* The joining relations should have consider_partitionwise_join set. */
- Assert(rel1->consider_partitionwise_join &&
- rel2->consider_partitionwise_join);
-
- /*
- * The partition scheme of the join relation should match that of the
- * joining relations.
- */
- Assert(joinrel->part_scheme == rel1->part_scheme &&
- joinrel->part_scheme == rel2->part_scheme);
-
- Assert(!(joinrel->merged && (joinrel->nparts <= 0)));
-
/*
* If we don't have the partition bounds for the join rel yet, try to
* compute those along with pairs of partitions to be joined.
@@ -1440,13 +1381,13 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
part_scheme->partcollation,
rel1, rel2,
parent_sjinfo->jointype,
- &parts1, &parts2);
+ parts1, parts2);
if (boundinfo == NULL)
{
joinrel->nparts = 0;
return;
}
- nparts = list_length(parts1);
+ nparts = list_length(*parts1);
joinrel->merged = true;
}
@@ -1472,11 +1413,80 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
if (joinrel->merged)
{
get_matching_part_pairs(root, joinrel, rel1, rel2,
- &parts1, &parts2);
- Assert(list_length(parts1) == joinrel->nparts);
- Assert(list_length(parts2) == joinrel->nparts);
+ parts1, parts2);
+ Assert(list_length(*parts1) == joinrel->nparts);
+ Assert(list_length(*parts2) == joinrel->nparts);
}
}
+}
+
+/*
+ * Assess whether join between given two partitioned relations can be broken
+ * down into joins between matching partitions; a technique called
+ * "partitionwise join"
+ *
+ * Partitionwise join is possible when a. Joining relations have same
+ * partitioning scheme b. There exists an equi-join between the partition keys
+ * of the two relations.
+ *
+ * Partitionwise join is planned as follows (details: optimizer/README.)
+ *
+ * 1. Create the RelOptInfos for joins between matching partitions i.e
+ * child-joins and add paths to them.
+ *
+ * 2. Construct Append or MergeAppend paths across the set of child joins.
+ * This second phase is implemented by generate_partitionwise_join_paths().
+ *
+ * The RelOptInfo, SpecialJoinInfo and restrictlist for each child join are
+ * obtained by translating the respective parent join structures.
+ */
+static void
+try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
+ RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
+ List *parent_restrictlist)
+{
+ bool rel1_is_simple = IS_SIMPLE_REL(rel1);
+ bool rel2_is_simple = IS_SIMPLE_REL(rel2);
+ List *parts1 = NIL;
+ List *parts2 = NIL;
+ ListCell *lcr1 = NULL;
+ ListCell *lcr2 = NULL;
+ int cnt_parts;
+
+ /* Guard against stack overflow due to overly deep partition hierarchy. */
+ check_stack_depth();
+
+ /* Nothing to do, if the join relation is not partitioned. */
+ if (joinrel->part_scheme == NULL || joinrel->nparts == 0)
+ return;
+
+ /* The join relation should have consider_partitionwise_join set. */
+ Assert(joinrel->consider_partitionwise_join);
+
+ /*
+ * We can not perform partitionwise join if either of the joining relations
+ * is not partitioned.
+ */
+ if (!IS_PARTITIONED_REL(rel1) || !IS_PARTITIONED_REL(rel2))
+ return;
+
+ Assert(REL_HAS_ALL_PART_PROPS(rel1) && REL_HAS_ALL_PART_PROPS(rel2));
+
+ /* The joining relations should have consider_partitionwise_join set. */
+ Assert(rel1->consider_partitionwise_join &&
+ rel2->consider_partitionwise_join);
+
+ /*
+ * The partition scheme of the join relation should match that of the
+ * joining relations.
+ */
+ Assert(joinrel->part_scheme == rel1->part_scheme &&
+ joinrel->part_scheme == rel2->part_scheme);
+
+ Assert(!(joinrel->merged && (joinrel->nparts <= 0)));
+
+ compute_partition_bounds(root, rel1, rel2, joinrel, parent_sjinfo,
+ &parts1, &parts2);
if (joinrel->merged)
{
--
2.21.1
--abghhlrw3s6ezwra--
^ permalink raw reply [nested|flat] 23+ messages in thread
* Add \pset options for boolean value display
@ 2025-03-21 03:24 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2025-03-21 03:24 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi!
Please accept this patch (though it's not finished, just functional)!
It's \pset null for boolean values
Printing tables of 't' and 'f' makes for painful-to-read output.
This provides an easy win for psql users, giving them the option to do
better. I would like all of our documentation examples eventually to be
done with "\pset display_true true" and "\pset display_false false"
configured. Getting it into v18 so docs being written now, like my NULL
patch, can make use of it, would make my year.
I was initially going to go with the following to mirror null even more
closely.
\pset { true | false } value
And still like that option, though having the same word repeated as the
expected value and name hurts it a bit.
This next one was also considered but the word "print" already seemed a bit
too entwined with \pset format related stuff.
\pset { print_true | print_false } value
David J.
Attachments:
[text/x-patch] v0-0001-Add-pset-options-for-boolean-value-display.patch (4.3K, ../../CAKFQuwYts3vnfQ5AoKhEaKMTNMfJ443MW2kFswKwzn7fiofkrw@mail.gmail.com/3-v0-0001-Add-pset-options-for-boolean-value-display.patch)
download | inline diff:
From 4d306939c008ecf7e8ae0036a46218b059a069c7 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Thu, 20 Mar 2025 20:03:01 -0700
Subject: [PATCH] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable
but visually atrocious. Teach psql to detect these two values
and print whatever it deems is appropriate. For now, in the
interest of backward compatability, that defaults to 't' and 'f'.
However, now the user can impose their own standards by using the
newly introduced display_true and display_false pset settings.
---
src/bin/psql/command.c | 43 ++++++++++++++++++++++++++++++++++++
src/fe_utils/print.c | 6 +++++
src/include/fe_utils/print.h | 2 ++
3 files changed, 51 insertions(+)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index bbe337780f..29f212e54f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2688,6 +2688,7 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
"border", "columns", "csv_fieldsep", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
+ "display_true", "display_false",
"recordsep", "recordsep_zero",
"tableattr", "title", "tuples_only",
"unicode_border_linestyle",
@@ -5193,6 +5194,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
+ /* false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5411,6 +5432,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->nullPrint ? popt->nullPrint : "");
}
+ /* show boolean true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
+ /* show boolean false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
/* show locale-aware numeric output */
else if (strcmp(param, "numericlocale") == 0)
{
@@ -5656,6 +5691,14 @@ pset_value_string(const char *param, printQueryOpt *popt)
return pset_quoted_string(popt->nullPrint
? popt->nullPrint
: "");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint
+ ? popt->truePrint
+ : "t");
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint
+ ? popt->falsePrint
+ : "f");
else if (strcmp(param, "numericlocale") == 0)
return pstrdup(pset_bool_string(popt->topt.numericLocale));
else if (strcmp(param, "pager") == 0)
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 5e5e54e1b7..2156616c9d 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3582,6 +3582,12 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ {
+ cell = (PQgetvalue(result, r, c)[0] == 't')
+ ? (opt->truePrint ? opt->truePrint : "t")
+ : (opt->falsePrint ? opt->falsePrint : "f");
+ }
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a3..2378e26acb 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
--
2.34.1
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-06-24 22:18 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 3 replies; 23+ messages in thread
From: David G. Johnston @ 2025-06-24 22:18 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> It's \pset null for boolean values
>
v1, Ready aside from bike-shedding the name.
David J.
Attachments:
[text/x-patch] v1-0001-Add-pset-options-for-boolean-value-display.patch (8.2K, ../../CAKFQuwZaeRT3Nco9sPd-9ZwEuStUZLU+AKH9+iRf4978T59kRg@mail.gmail.com/3-v1-0001-Add-pset-options-for-boolean-value-display.patch)
download | inline diff:
From c897e53577d433c374e51619405a5c2b8bd4151c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 18 Jun 2025 12:20:43 -0700
Subject: [PATCH] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable
but visually atrocious. Teach psql to detect these two values
and print whatever it deems is appropriate. For now, in the
interest of backward compatability, that defaults to 't' and 'f'.
However, now the user can impose their own standards by using the
newly introduced display_true and display_false pset settings.
---
doc/src/sgml/ref/psql-ref.sgml | 24 ++++++++++++++++
src/bin/psql/command.c | 45 +++++++++++++++++++++++++++++-
src/fe_utils/print.c | 6 ++++
src/include/fe_utils/print.h | 2 ++
src/test/regress/expected/psql.out | 32 +++++++++++++++++++++
src/test/regress/sql/psql.sql | 16 +++++++++++
6 files changed, 124 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 570ef21d1fc..03d4e429d8a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3287,6 +3287,30 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-pset-display_true">
+ <term><literal>display_true</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a true value.
+ The default is to print <literal>t</literal> as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset print_true 'true'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="app-psql-meta-command-pset-display_false">
+ <term><literal>display_false</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a false value.
+ The default is to print <literal>f</literal> as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset print_false 'false'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-pset-null">
<term><literal>null</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 83e84a77841..c6afa982a59 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2688,7 +2688,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
int i;
static const char *const my_list[] = {
- "border", "columns", "csv_fieldsep", "expanded", "fieldsep",
+ "border", "columns", "csv_fieldsep",
+ "display_true", "display_false", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
"recordsep", "recordsep_zero",
@@ -5198,6 +5199,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
+ /* false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5416,6 +5437,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->nullPrint ? popt->nullPrint : "");
}
+ /* show boolean true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
+ /* show boolean false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
/* show locale-aware numeric output */
else if (strcmp(param, "numericlocale") == 0)
{
@@ -5661,6 +5696,14 @@ pset_value_string(const char *param, printQueryOpt *popt)
return pset_quoted_string(popt->nullPrint
? popt->nullPrint
: "");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint
+ ? popt->truePrint
+ : "t");
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint
+ ? popt->falsePrint
+ : "f");
else if (strcmp(param, "numericlocale") == 0)
return pstrdup(pset_bool_string(popt->topt.numericLocale));
else if (strcmp(param, "pager") == 0)
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 4af0f32f2fc..7829460f7d3 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3582,6 +3582,12 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ {
+ cell = (PQgetvalue(result, r, c)[0] == 't')
+ ? (opt->truePrint ? opt->truePrint : "t")
+ : (opt->falsePrint ? opt->falsePrint : "f");
+ }
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a31..2378e26acb6 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..a276d79bf46 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -445,6 +445,8 @@ environment value
border 1
columns 0
csv_fieldsep ','
+display_true 't'
+display_false 'f'
expanded off
fieldsep '|'
fieldsep_zero off
@@ -464,6 +466,36 @@ unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width full
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+ n | t | f
+---+---+---
+ | t | f
+(1 row)
+
+deallocate q;
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1a8a83462f0..c1784a691fe 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -219,6 +219,22 @@ select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
-- show all pset options
\pset
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+deallocate q;
+
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
--
2.34.1
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-06-24 22:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 2 replies; 23+ messages in thread
From: Tom Lane @ 2025-06-24 22:30 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> It's \pset null for boolean values
> v1, Ready aside from bike-shedding the name.
Do we really want this? It's the sort of thing that has a strong
potential to break anything that reads psql output --- and I'd
urge you to think that human consumers of psql output may well
be the minority. There's an awful lot of scripts out there.
I concede that \pset null hasn't had a huge amount of pushback,
but that doesn't mean that making boolean output unpredictable
will be cost-free. And the costs won't be paid by you (or me),
but by people who didn't ask for it.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-06-24 22:43 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: David G. Johnston @ 2025-06-24 22:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Jun 24, 2025 at 3:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
> > david.g.johnston@gmail.com> wrote:
> >> It's \pset null for boolean values
>
> > v1, Ready aside from bike-shedding the name.
>
> Do we really want this? It's the sort of thing that has a strong
> potential to break anything that reads psql output --- and I'd
> urge you to think that human consumers of psql output may well
> be the minority. There's an awful lot of scripts out there.
>
> I concede that \pset null hasn't had a huge amount of pushback,
> but that doesn't mean that making boolean output unpredictable
> will be cost-free. And the costs won't be paid by you (or me),
> but by people who didn't ask for it.
>
>
If we didn't use psql to produce all of our examples I'd be a bit more
accepting of this position. Yes, users of it need to do so responsibly.
But we have tons of pretty-presentation-oriented options in psql so, yes, I
do believe this is well within its charter.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-06-25 18:03 Daniel Verite <daniel@manitou-mail.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 23+ messages in thread
From: Daniel Verite @ 2025-06-25 18:03 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
David G. Johnston wrote:
> > It's \pset null for boolean values
> >
>
> v1, Ready aside from bike-shedding the name.
An annoying weakness of this approach is that it cannot detect
booleans inside arrays or composite types or COPY output,
meaning that the translation of t/f is incomplete.
Also it reminds of a previous discussion (see [1]) where pretty much
the same idea was proposed (and eventually rejected at the time).
[1] https://postgr.es/m/56308F56.8060908%40joh.to
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-06-25 18:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Daniel Verite <daniel@manitou-mail.org>
0 siblings, 0 replies; 23+ messages in thread
From: David G. Johnston @ 2025-06-25 18:21 UTC (permalink / raw)
To: Daniel Verite <daniel@manitou-mail.org>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Jun 25, 2025 at 11:03 AM Daniel Verite <daniel@manitou-mail.org>
wrote:
> David G. Johnston wrote:
>
> > > It's \pset null for boolean values
> > >
> >
> > v1, Ready aside from bike-shedding the name.
>
> An annoying weakness of this approach is that it cannot detect
> booleans inside arrays or composite types
Arrays are probably doable. The low volume of composite literal outputs is
not worth worrying about.
> or COPY output,
> meaning that the translation of t/f is incomplete.
>
pset doesn't affect COPY output ever so this doesn't seem problematic.
> Also it reminds of a previous discussion (see [1]) where pretty much
> the same idea was proposed (and eventually rejected at the time).
>
>
> [1] https://postgr.es/m/56308F56.8060908%40joh.to
>
>
Ok, so yes, I really want this hack in psql. It fits with pset formats and
affects our \d and other table-producing meta-commands. Plus I'd like to
use it for documentation examples.
Maybe that's enough to change some decade-old opinions. Mine's apparently
changed since then.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-07-05 12:41 Vik Fearing <vik@postgresfriends.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Vik Fearing @ 2025-07-05 12:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 25/06/2025 00:30, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>> It's \pset null for boolean values
> Do we really want this?
Yes, many of us do.
> It's the sort of thing that has a strong
> potential to break anything that reads psql output --- and I'd
> urge you to think that human consumers of psql output may well
> be the minority. There's an awful lot of scripts out there.
You mean scripts that don't use --no-psqlrc? Those scripts are already
bug ridden.
--
Vik Fearing
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-20 14:24 Álvaro Herrera <alvherre@kurilemu.de>
parent: David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 1 reply; 23+ messages in thread
From: Álvaro Herrera @ 2025-10-20 14:24 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 2025-Jun-24, David G. Johnston wrote:
> v1, Ready aside from bike-shedding the name.
Here's v2 after some kibitzing. What do you think?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Attachments:
[text/x-diff] v2-0001-Add-pset-options-for-boolean-value-display.patch (8.3K, ../../202510201419.mpror4maizbg@alvherre.pgsql/2-v2-0001-Add-pset-options-for-boolean-value-display.patch)
download | inline diff:
From ab14c69835836ff70c7193ff016e683ec8de9608 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Mon, 20 Oct 2025 12:29:34 +0200
Subject: [PATCH v2] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable but
visually atrocious. Teach psql to detect these two values and print
whatever it deems is appropriate. In the interest of backward
compatability, that defaults to 't' and 'f'. However, now the user can
impose their own standards by using the newly introduced display_true
and display_false pset settings.
Author: David G. Johnston <David.G.Johnston@gmail.com>
Discussion: https://postgr.es/m/CAKFQuwYts3vnfQ5AoKhEaKMTNMfJ443MW2kFswKwzn7fiofkrw@mail.gmail.com
---
doc/src/sgml/ref/psql-ref.sgml | 24 +++++++++++++++++
src/bin/psql/command.c | 43 +++++++++++++++++++++++++++++-
src/fe_utils/print.c | 4 +++
src/include/fe_utils/print.h | 2 ++
src/test/regress/expected/psql.out | 32 ++++++++++++++++++++++
src/test/regress/sql/psql.sql | 16 +++++++++++
6 files changed, 120 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1a339600bc4..06f1e08d87a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3099,6 +3099,30 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-pset-display-false">
+ <term><literal>display_false</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a false value.
+ The default is to print <literal>f</literal>, as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset display_false 'false'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="app-psql-meta-command-pset-display-true">
+ <term><literal>display_true</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a true value.
+ The default is to print <literal>t</literal>, as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset display_true 'true'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-pset-expanded">
<term><literal>expanded</literal> (or <literal>x</literal>)</term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index cc602087db2..f7454daf6ed 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2709,7 +2709,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
int i;
static const char *const my_list[] = {
- "border", "columns", "csv_fieldsep", "expanded", "fieldsep",
+ "border", "columns", "csv_fieldsep",
+ "display_false", "display_true", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
"recordsep", "recordsep_zero",
@@ -5300,6 +5301,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* 'false' display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
+ /* 'true' display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5474,6 +5495,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->topt.csvFieldSep);
}
+ /* show boolean 'false' display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
+ /* show boolean 'true' display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
/* show field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5743,6 +5778,12 @@ pset_value_string(const char *param, printQueryOpt *popt)
return psprintf("%d", popt->topt.columns);
else if (strcmp(param, "csv_fieldsep") == 0)
return pset_quoted_string(popt->topt.csvFieldSep);
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint ?
+ popt->falsePrint : "f");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint ?
+ popt->truePrint : "t");
else if (strcmp(param, "expanded") == 0)
return pstrdup(popt->topt.expanded == 2
? "auto"
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 73847d3d6b3..4d97ad2ddeb 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3775,6 +3775,10 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ cell = (PQgetvalue(result, r, c)[0] == 't' ?
+ (opt->truePrint ? opt->truePrint : "t") :
+ (opt->falsePrint ? opt->falsePrint : "f"));
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a31..6a6fc7e132c 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index fa8984ffe0d..c8f3932edf0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -445,6 +445,8 @@ environment value
border 1
columns 0
csv_fieldsep ','
+display_false 'f'
+display_true 't'
expanded off
fieldsep '|'
fieldsep_zero off
@@ -464,6 +466,36 @@ unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width full
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+ n | t | f
+---+---+---
+ | t | f
+(1 row)
+
+deallocate q;
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index f064e4f5456..dcdbd4fc020 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -219,6 +219,22 @@ select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
-- show all pset options
\pset
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+deallocate q;
+
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
--
2.47.3
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-20 20:51 David G. Johnston <david.g.johnston@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 2 replies; 23+ messages in thread
From: David G. Johnston @ 2025-10-20 20:51 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Monday, October 20, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
> On 2025-Jun-24, David G. Johnston wrote:
>
> > v1, Ready aside from bike-shedding the name.
>
> Here's v2 after some kibitzing. What do you think?
>
Thank you. Seems good from a quick read. I’m regretting the choice of the
display_ prefix; is there any technical limitation or other opposition to
using just true and false?
\pset true ‘true’
\pset false ‘false’
To keep in line with:
\pset null ‘(null)’
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-20 21:08 Álvaro Herrera <alvherre@kurilemu.de>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 2 replies; 23+ messages in thread
From: Álvaro Herrera @ 2025-10-20 21:08 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 2025-Oct-20, David G. Johnston wrote:
> Thank you. Seems good from a quick read. I’m regretting the choice of the
> display_ prefix; is there any technical limitation or other opposition to
> using just true and false?
>
> \pset true ‘true’
> \pset false ‘false’
>
> To keep in line with:
>
> \pset null ‘(null)’
Uhm. I don't know. No technical limitation AFAICS. It looks a bit
weird to me, because those names are so generic; but also I cannot
really object to them. That said, such a last-minute bikeshed comment
seems like a perfect way to kill your patch.
I'll gladly take a vote.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 01:38 Chao Li <li.evan.chao@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Chao Li @ 2025-10-21 01:38 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
> On Oct 21, 2025, at 04:51, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Monday, October 20, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
> On 2025-Jun-24, David G. Johnston wrote:
>
> > v1, Ready aside from bike-shedding the name.
>
> Here's v2 after some kibitzing. What do you think?
>
> Thank you. Seems good from a quick read. I’m regretting the choice of the display_ prefix; is there any technical limitation or other opposition to using just true and false?
>
> \pset true ‘true’
> \pset false ‘false’
>
> To keep in line with:
>
> \pset null ‘(null)’
>
+1. Especially, when I see the newly added test case:
```
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
```
Looks inconsistant. If we decided to use “display_xx” then we should have renamed “null” to “display_null”.
The other thing I am thinking is that, with this patch, users are allowed to display arbitrary strings for true/false, if a user mistakenly set display_true to f and display_false to t, which will load to misunderstanding.
```
evantest=# \pset display_true f
Boolean true display is "f".
evantest=# \pset display_false t
Boolean false display is "t".
evantest=# select true as t, false as f;
t | f
---+---
f | t
(1 row)
```
Can we perform a basic sanity check to prevent this kind of error-prone behavior? The consideration applies to the “null” option, but since “null” lacks a clear opposite string representation (unlike “true”/“t" and “false”/“f”), it’s fine to skip the check for it.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 02:29 David G. Johnston <david.g.johnston@gmail.com>
parent: Chao Li <li.evan.chao@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: David G. Johnston @ 2025-10-21 02:29 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Monday, October 20, 2025, Chao Li <li.evan.chao@gmail.com> wrote:
> The other thing I am thinking is that, with this patch, users are allowed
> to display arbitrary strings for true/false, if a user mistakenly set
> display_true to f and display_false to t, which will load to
> misunderstanding.
>
Sympathetic to the concern but opposed to taking on such responsibility.
They could probably modify their own query to do that if they really wanted
to fool someone and I’m having trouble accepting this happening by
accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
checkmark/X?
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 02:48 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2025-10-21 02:48 UTC (permalink / raw)
To: Chao Li <li.evan.chao@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Monday, October 20, 2025, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Monday, October 20, 2025, Chao Li <li.evan.chao@gmail.com> wrote:
>
>> The other thing I am thinking is that, with this patch, users are allowed
>> to display arbitrary strings for true/false, if a user mistakenly set
>> display_true to f and display_false to t, which will load to
>> misunderstanding.
>>
>
> Sympathetic to the concern but opposed to taking on such responsibility.
> They could probably modify their own query to do that if they really wanted
> to fool someone and I’m having trouble accepting this happening by
> accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
> checkmark/X?
>
>
Actually, preventing t/f makes sense to me. Prevents a “hacker” from
messing with the default outputs in a hard-to-identify manner. Any other
value would point to pset being used.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 02:51 Chao Li <li.evan.chao@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Chao Li @ 2025-10-21 02:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
> On Oct 21, 2025, at 10:29, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> They could probably modify their own query to do that if they really wanted to fool someone and I’m having trouble accepting this happening by accident.
If they modify queries, the result can visibly correlate to the query, for example:
```
evantest=# select CASE WHEN TRUE THEN 'f' END as t;
t
---
f
(1 row)
```
There is no confusion. But if a user did some test by setting “display_true = f” previous and forget about it, there is a no any indication in current SQL statement but unexpected results might be shown.
> Do we test for yes/no; oui/non (i.e., foreign language choices); checkmark/X?
>
When I said “basic sanity check”, I only meant something like “display_true” cannot be “false” and “f”.
I won’t argue more. It’s also reasonable to let users take own responsibilities to stay away from wrong behavior.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 03:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Tom Lane @ 2025-10-21 03:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chao Li <li.evan.chao@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, October 20, 2025, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>> Sympathetic to the concern but opposed to taking on such responsibility.
>> They could probably modify their own query to do that if they really wanted
>> to fool someone and I’m having trouble accepting this happening by
>> accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
>> checkmark/X?
> Actually, preventing t/f makes sense to me. Prevents a “hacker” from
> messing with the default outputs in a hard-to-identify manner. Any other
> value would point to pset being used.
-1. Yeah, you could reject "\pset true 'f'", but what about
not-obviously-different values such as 'f ', or f with a non-breaking
space, or f with a tab, or yadda yadda yadda?
I went on record as opposed to this entire idea back at the start of
this thread, precisely because I was worried that it could lead to
confusion. And I remain of the opinion that it's not a great idea.
But if we're going to do it, let's not bother with any fig-leaf
proposals that pretend to partially guard against confusion.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-10-21 11:26 Pavel Stehule <pavel.stehule@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 0 replies; 23+ messages in thread
From: Pavel Stehule @ 2025-10-21 11:26 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
út 21. 10. 2025 v 9:38 odesílatel Álvaro Herrera <alvherre@kurilemu.de>
napsal:
> On 2025-Oct-20, David G. Johnston wrote:
>
> > Thank you. Seems good from a quick read. I’m regretting the choice of
> the
> > display_ prefix; is there any technical limitation or other opposition to
> > using just true and false?
> >
> > \pset true ‘true’
> > \pset false ‘false’
> >
> > To keep in line with:
> >
> > \pset null ‘(null)’
>
> Uhm. I don't know. No technical limitation AFAICS. It looks a bit
> weird to me, because those names are so generic; but also I cannot
> really object to them. That said, such a last-minute bikeshed comment
> seems like a perfect way to kill your patch.
> I'll gladly take a vote.
>
I think so this is little bit different case
In this context I see three "safe" variants like
short: t, f
long: true, false
localized: nepravda, pravda (if this is available)
localized short is probably very messy - like 'n' and 'p' for Czech
language and never be used
In the Czech environment we mostly don't translate boolean constants in
computer science.
Regards
Pavel
Null is different - there is not known any formal symbol for null.
> --
> Álvaro Herrera Breisgau, Deutschland —
> https://www.EnterpriseDB.com/
> "On the other flipper, one wrong move and we're Fatal Exceptions"
> (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)
>
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-11-03 16:44 Álvaro Herrera <alvherre@kurilemu.de>
parent: Álvaro Herrera <alvherre@kurilemu.de>
1 sibling, 1 reply; 23+ messages in thread
From: Álvaro Herrera @ 2025-11-03 16:44 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 2025-Oct-21, Álvaro Herrera wrote:
> On 2025-Oct-20, David G. Johnston wrote:
> > Thank you. Seems good from a quick read. I’m regretting the choice of the
> > display_ prefix; is there any technical limitation or other opposition to
> > using just true and false?
> >
> > \pset true ‘true’
> > \pset false ‘false’
> Uhm. I don't know. [...] I'll gladly take a vote.
I got zero votes and lots of digression, so I have pushed with your
original choice of "display_true" and "display_false". The "true" and
"false" variable names sound too generic and I think they're more likely
to cause confusion. I think "null" is not a great name either, but it's
been there since forever so I'm not going to propose changing it.
It's always been the case that for machine-readable output, --no-psqlrc
should be used, and a majority of interesting scripts I've seen do that
already, so I don't expect lots of breakage. (Also, such a script is
easy to fix if anyone runs into trouble.)
I have added this to my stock .psqlrc as dogfooding experiment:
select :VERSION_NUM >= 190000 as bool_display \gset
\if :bool_display
\pset display_true YES
\pset display_false no
\unset bool_display
\endif
This means I get support for it when connecting to older servers with
new psql, and if I use the old psql, things behave normally with no
extra noise.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2025-11-03 17:16 David G. Johnston <david.g.johnston@gmail.com>
parent: Álvaro Herrera <alvherre@kurilemu.de>
0 siblings, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2025-11-03 17:16 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Monday, November 3, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
> On 2025-Oct-21, Álvaro Herrera wrote:
> > On 2025-Oct-20, David G. Johnston wrote:
> > > Thank you. Seems good from a quick read. I’m regretting the choice
> of the
> > > display_ prefix; is there any technical limitation or other opposition
> to
> > > using just true and false?
> > >
> > > \pset true ‘true’
> > > \pset false ‘false’
>
> > Uhm. I don't know. [...] I'll gladly take a vote.
>
> I got zero votes and lots of digression, so I have pushed with your
> original choice of "display_true" and "display_false". The "true" and
> "false" variable names sound too generic and I think they're more likely
> to cause confusion. I think "null" is not a great name either, but it's
> been there since forever so I'm not going to propose changing it.
Thank you.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2026-04-21 06:10 a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: a.kozhemyakin @ 2026-04-21 06:10 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Hi Hackers,
The following script, when built with addresssanitizer, fails with a
DoubleFree error. reproduce on master (d3bba0415435)
psql postgres <<EOF
\pset display_false 'f'
SELECT 1 as one, 2 as two \g (display_false=csv csv_fieldsep='\t')
\pset display_false 'f'
EOF
Boolean false display is "f".
one | two
-----+-----
1 | 2
(1 строка)
=================================================================
==2263488==ERROR: AddressSanitizer: attempting double-free on
0x774e419e15d0 in thread T0:
#0 0x64c15cb777ab in free.part.0
(/pgpro/builds/master/inst_asan/bin/psql+0x23e7ab) (BuildId:
235e8c12978fc34235af5b00bd4a2feaab9cb794)
#1 0x64c15cbedfbd in do_pset
/pgpro/postgres/src/bin/psql/command.c:5310
#2 0x64c15cbef308 in exec_command_pset
/pgpro/postgres/src/bin/psql/command.c:2737
#3 0x64c15cbf10d3 in exec_command
/pgpro/postgres/src/bin/psql/command.c:431
#4 0x64c15cbf1579 in HandleSlashCmds
/pgpro/postgres/src/bin/psql/command.c:258
#5 0x64c15cc25ecd in MainLoop
/pgpro/postgres/src/bin/psql/mainloop.c:496
#6 0x64c15cbec791 in process_file
/pgpro/postgres/src/bin/psql/command.c:4977
#7 0x64c15cc48cda in main /pgpro/postgres/src/bin/psql/startup.c:424
#8 0x7b2e4322a574 in __libc_start_call_main
../sysdeps/nptl/libc_start_call_main.h:58
#9 0x7b2e4322a627 in __libc_start_main_impl ../csu/libc-start.c:360
#10 0x64c15ca91b84 in _start
(/pgpro/builds/master/inst_asan/bin/psql+0x158b84) (BuildId:
235e8c12978fc34235af5b00bd4a2feaab9cb794)
0x774e419e15d0 is located 0 bytes inside of 2-byte region
[0x774e419e15d0,0x774e419e15d2)
freed by thread T0 here:
#0 0x64c15cb777ab in free.part.0
(/pgpro/builds/master/inst_asan/bin/psql+0x23e7ab) (BuildId:
235e8c12978fc34235af5b00bd4a2feaab9cb794)
#1 0x64c15cbedfbd in do_pset
/pgpro/postgres/src/bin/psql/command.c:5310
#2 0x783e419e2980 (<unknown module>)
previously allocated by thread T0 here:
#0 0x64c15cb725c8 in strdup
(/pgpro/builds/master/inst_asan/bin/psql+0x2395c8) (BuildId:
235e8c12978fc34235af5b00bd4a2feaab9cb794)
#1 0x64c15cc92bd5 in pg_strdup
/pgpro/postgres/src/common/fe_memutils.c:95
SUMMARY: AddressSanitizer: double-free
(/pgpro/builds/master/inst_asan/bin/psql+0x23e7ab) (BuildId:
235e8c12978fc34235af5b00bd4a2feaab9cb794) in free.part.0
==2263488==ABORTING
645cb44c5490f70da4dca57b8ecca6562fb883a7 is the first bad commit
gcc --version
gcc (Ubuntu 15.2.0-4ubuntu4) 15.2.0
building CPPFLAGS="-Og -fsanitize=address -fsanitize=undefined
-fno-sanitize-recover=all -fno-sanitize=nonnull-attribute
-fstack-protector" LDFLAGS='-fsanitize=address -fsanitize=undefined
-static-libasan' \
./configure --prefix /pgpro/builds/master_simple && make world-bin -s
-j$(nproc) && make install-world-bin -s
04.11.2025 00:16, David G. Johnston пишет:
> On Monday, November 3, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2025-Oct-21, Álvaro Herrera wrote:
> > On 2025-Oct-20, David G. Johnston wrote:
> > > Thank you. Seems good from a quick read. I’m regretting the
> choice of the
> > > display_ prefix; is there any technical limitation or other
> opposition to
> > > using just true and false?
> > >
> > > \pset true ‘true’
> > > \pset false ‘false’
>
> > Uhm. I don't know. [...] I'll gladly take a vote.
>
> I got zero votes and lots of digression, so I have pushed with your
> original choice of "display_true" and "display_false". The "true" and
> "false" variable names sound too generic and I think they're more
> likely
> to cause confusion. I think "null" is not a great name either,
> but it's
> been there since forever so I'm not going to propose changing it.
>
>
> Thank you.
>
> David J.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2026-04-21 06:30 David G. Johnston <david.g.johnston@gmail.com>
parent: a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
0 siblings, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2026-04-21 06:30 UTC (permalink / raw)
To: a.kozhemyakin <a.kozhemyakin@postgrespro.ru>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Monday, April 20, 2026, a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
wrote:
>
> The following script, when built with addresssanitizer, fails with a
> DoubleFree error. reproduce on master (d3bba0415435)
>
Thanks for the report. To be honest, I’m not sure where to start debugging
it - but I plan to give it a go. I’m curious if it fails for “\pset null
‘N’” “\g (null=csv)” too and I just got bit by inheriting its layout.
Don’t see why these new ones would be different.
David J.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2026-04-21 17:43 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: David G. Johnston @ 2026-04-21 17:43 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@kurilemu.de>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
On Mon, Apr 20, 2026 at 11:30 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Monday, April 20, 2026, a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
> wrote:
>>
>> The following script, when built with addresssanitizer, fails with a
>> DoubleFree error. reproduce on master (d3bba0415435)
>>
>
> Thanks for the report. To be honest, I’m not sure where to start
> debugging it - but I plan to give it a go. I’m curious if it fails for
> “\pset null ‘N’” “\g (null=csv)” too and I just got bit by inheriting its
> layout. Don’t see why these new ones would be different.
>
>
Had issues getting a server to run meson+asan but did get psql to do so and
confirmed.
I just didn't find all of the patterns.
savePsetInfo and restorePsetInfo need explicit knowledge of these options
as well to clean up the popt struct.
Attached.
David J.
Attachments:
[text/x-patch] save-restore-pset-info-fix.diff (814B, ../../CAKFQuwaYEq5sQsWZZZn-u3Fx4UQL2TFMVbG8HnxrL-=KFM2AjA@mail.gmail.com/3-save-restore-pset-info-fix.diff)
download | inline diff:
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 493400f9090..01b8f11aadd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -5680,6 +5680,10 @@ savePsetInfo(const printQueryOpt *popt)
save->topt.tableAttr = pg_strdup(popt->topt.tableAttr);
if (popt->nullPrint)
save->nullPrint = pg_strdup(popt->nullPrint);
+ if (popt->truePrint)
+ save->truePrint = pg_strdup(popt->truePrint);
+ if (popt->falsePrint)
+ save->falsePrint = pg_strdup(popt->falsePrint);
if (popt->title)
save->title = pg_strdup(popt->title);
@@ -5707,6 +5711,8 @@ restorePsetInfo(printQueryOpt *popt, printQueryOpt *save)
free(popt->topt.recordSep.separator);
free(popt->topt.tableAttr);
free(popt->nullPrint);
+ free(popt->truePrint);
+ free(popt->falsePrint);
free(popt->title);
/*
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Add \pset options for boolean value display
@ 2026-05-12 22:28 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Bruce Momjian @ 2026-05-12 22:28 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>; a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
On Tue, Apr 21, 2026 at 10:43:02AM -0700, David G. Johnston wrote:
> On Mon, Apr 20, 2026 at 11:30 PM David G. Johnston <david.g.johnston@gmail.com>
> Had issues getting a server to run meson+asan but did get psql to do so and
> confirmed.
>
> I just didn't find all of the patterns.
>
> savePsetInfo and restorePsetInfo need explicit knowledge of these options as
> well to clean up the popt struct.
>
> Attached.
Patch applied. Let me know if you need any other fixes. Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2026-05-12 22:28 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-26 00:15 [PATCH 3/3] split try_partitionwise_join Tomas Vondra <tv@fuzzy.cz>
2025-03-21 03:24 Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-06-24 22:18 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-06-24 22:30 ` Re: Add \pset options for boolean value display Tom Lane <tgl@sss.pgh.pa.us>
2025-06-24 22:43 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-07-05 12:41 ` Re: Add \pset options for boolean value display Vik Fearing <vik@postgresfriends.org>
2025-06-25 18:03 ` Re: Add \pset options for boolean value display Daniel Verite <daniel@manitou-mail.org>
2025-06-25 18:21 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-10-20 14:24 ` Re: Add \pset options for boolean value display Álvaro Herrera <alvherre@kurilemu.de>
2025-10-20 20:51 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-10-20 21:08 ` Re: Add \pset options for boolean value display Álvaro Herrera <alvherre@kurilemu.de>
2025-10-21 11:26 ` Re: Add \pset options for boolean value display Pavel Stehule <pavel.stehule@gmail.com>
2025-11-03 16:44 ` Re: Add \pset options for boolean value display Álvaro Herrera <alvherre@kurilemu.de>
2025-11-03 17:16 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2026-04-21 06:10 ` Re: Add \pset options for boolean value display a.kozhemyakin <a.kozhemyakin@postgrespro.ru>
2026-04-21 06:30 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2026-04-21 17:43 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2026-05-12 22:28 ` Re: Add \pset options for boolean value display Bruce Momjian <bruce@momjian.us>
2025-10-21 01:38 ` Re: Add \pset options for boolean value display Chao Li <li.evan.chao@gmail.com>
2025-10-21 02:29 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-10-21 02:48 ` Re: Add \pset options for boolean value display David G. Johnston <david.g.johnston@gmail.com>
2025-10-21 03:37 ` Re: Add \pset options for boolean value display Tom Lane <tgl@sss.pgh.pa.us>
2025-10-21 02:51 ` Re: Add \pset options for boolean value display Chao Li <li.evan.chao@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox