agora inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH 4/4] demote: add various tests related to demote and promote actions
10+ messages / 2 participants
[nested] [flat]

* [PATCH 4/4] demote: add various tests related to demote and promote actions
@ 2020-07-10 00:00 Jehan-Guillaume de Rorthais <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2020-07-10 00:00 UTC (permalink / raw)

* demote/promote with a standby replicating from the node
* make sure 2PC survive a demote/promote cycle
* commit 2PC and check the result
* swap roles between primary and standby
* make sure wal sender enters cascade mode
* commit a 2PC on the new primary
* confirm behavior of backends during smart/fast demote
---
 src/test/perl/PostgresNode.pm             |  25 ++
 src/test/recovery/t/021_promote-demote.pl | 287 ++++++++++++++++++++++
 2 files changed, 312 insertions(+)
 create mode 100644 src/test/recovery/t/021_promote-demote.pl

diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index 8c1b77376f..4488365ffc 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -906,6 +906,31 @@ sub promote
 
 =pod
 
+=item $node->demote()
+
+Wrapper for pg_ctl demote
+
+=cut
+
+sub demote
+{
+	my ($self, $mode) = @_;
+	my $port    = $self->port;
+	my $pgdata  = $self->data_dir;
+	my $logfile = $self->logfile;
+	my $name    = $self->name;
+
+	$mode = 'fast' unless defined $mode;
+
+	print "### Demoting node \"$name\" using mode $mode\n";
+
+	TestLib::system_or_bail('pg_ctl', '-D', $pgdata, '-l', $logfile,
+		'-m', $mode, 'demote');
+	return;
+}
+
+=pod
+
 =item $node->logrotate()
 
 Wrapper for pg_ctl logrotate
diff --git a/src/test/recovery/t/021_promote-demote.pl b/src/test/recovery/t/021_promote-demote.pl
new file mode 100644
index 0000000000..245acfb211
--- /dev/null
+++ b/src/test/recovery/t/021_promote-demote.pl
@@ -0,0 +1,287 @@
+# Test demote/promote actions in various scenarios using three
+# nodes alpha, beta and gamma. We check proper actions results,
+# correct data replication and cascade across multiple
+# demote/promote, manual switchover, smart and fast demote.
+
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 24;
+
+$ENV{PGDATABASE} = 'postgres';
+
+# Initialize node alpha
+my $node_alpha = get_new_node('alpha');
+$node_alpha->init(allows_streaming => 1);
+$node_alpha->append_conf(
+	'postgresql.conf', qq(
+	max_prepared_transactions = 10
+));
+
+# Take backup
+my $backup_name = 'alpha_backup';
+$node_alpha->start;
+$node_alpha->backup($backup_name);
+
+# Create node beta from backup
+my $node_beta = get_new_node('beta');
+$node_beta->init_from_backup($node_alpha, $backup_name);
+$node_beta->enable_streaming($node_alpha);
+$node_beta->start;
+
+# Create node gamma from backup
+my $node_gamma = get_new_node('gamma');
+$node_gamma->init_from_backup($node_alpha, $backup_name);
+$node_gamma->enable_streaming($node_alpha);
+$node_gamma->start;
+
+# Create some 2PC on alpha for future tests
+$node_alpha->safe_psql('postgres', q{
+CREATE TABLE ins AS SELECT 1 AS i;
+BEGIN;
+CREATE TABLE new AS SELECT generate_series(1,5) AS i;
+PREPARE TRANSACTION 'pxact1';
+BEGIN;
+INSERT INTO ins VALUES (2);
+PREPARE TRANSACTION 'pxact2';
+});
+
+# create an in idle in xact session
+my ($sess1_in, $sess1_out, $sess1_err) = ('', '', '');
+my $sess1 = IPC::Run::start(
+	[
+		'psql', '-X', '-qAt', '-v', 'ON_ERROR_STOP=1', '-f', '-', '-d',
+		$node_alpha->connstr('postgres')
+	],
+	'<', \$sess1_in,
+	'>', \$sess1_out,
+	'2>', \$sess1_err);
+
+$sess1_in = q{
+BEGIN;
+CREATE TABLE public.test_aborted (i int);
+SELECT pg_backend_pid();
+};
+$sess1->pump until $sess1_out =~ qr/[[:digit:]]+[\r\n]$/m;
+my $sess1_pid = $sess1_out;
+chomp $sess1_pid;
+
+# create an in idle session
+my ($sess2_in, $sess2_out, $sess2_err) = ('', '', '');
+my $sess2 = IPC::Run::start(
+	[
+		'psql', '-X', '-qAt', '-v', 'ON_ERROR_STOP=1', '-f', '-', '-d',
+		$node_alpha->connstr('postgres')
+	],
+	'<', \$sess2_in,
+	'>', \$sess2_out,
+	'2>', \$sess2_err);
+$sess2_in = q{
+SELECT pg_backend_pid();
+};
+$sess2->pump until $sess2_out =~ qr/\d+\s*$/m;
+my $sess2_pid = $sess2_out;
+chomp $sess2_pid;
+
+$sess2_in = q{
+SELECT pg_is_in_recovery();
+};
+$sess2->pump until $sess2_out =~ qr/(t|f)\s*$/m;
+
+# idle session is not in recovery
+is( $1, 'f', 'idle session is not in recovery' );
+
+# Fast demote alpha.
+# Secondaries beta and gamma should keep streaming from it as cascaded standbys.
+# Idle in xact session should be terminate, idle session should stay alive.
+$node_alpha->demote('fast');
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	't', 'node alpha demoted to standby' );
+
+is( $node_alpha->safe_psql(
+		'postgres',
+		'SELECT array_agg(application_name ORDER BY application_name ASC) FROM pg_stat_replication'),
+	'{beta,gamma}', 'standbys keep replicating with alpha after demote' );
+
+# the idle in xact session should not survive the demote
+is( $node_alpha->safe_psql(
+		'postgres',
+		qq{SELECT count(*)
+		   FROM pg_catalog.pg_stat_activity
+		   WHERE pid = $sess1_pid}),
+	'0', 'previous idle in transaction session should be terminated' );
+
+# table "test_aborted" has been rollbacked
+is( $node_alpha->safe_psql(
+		'postgres',
+		q{SELECT count(*) FROM pg_catalog.pg_class
+		  WHERE relname='test_aborted'
+		    AND relnamespace = (SELECT oid FROM pg_namespace
+		                        WHERE nspname='public')}),
+	'0', 'the tansaction bas been aborted during fast demote' );
+
+# the idle session should survive the demote
+is( $node_alpha->safe_psql(
+		'postgres',
+		qq{SELECT count(*)
+		   FROM pg_catalog.pg_stat_activity
+		   WHERE pid = $sess2_pid}),
+	'1', "the idle session should survive the demote: $sess2_pid" );
+
+# the idle session should report in recovery
+$sess2_out = '';
+$sess2_in = q{
+SELECT pg_is_in_recovery();
+};
+$sess2->pump until $sess2_out =~ qr/(t|f)\s*$/m;
+
+# idle session is not in recovery
+is( $1, 't', 'the idle session reports in recovery' );
+
+# close both sessions
+$sess1_out = $sess2_out = $sess1_in = $sess2_in = '';
+$sess1->finish;
+$sess2->finish;
+
+# Promote alpha back in production.
+$node_alpha->promote;
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	'f', "node alpha promoted" );
+
+# Check all 2PC xact have been restored
+is( $node_alpha->safe_psql(
+		'postgres',
+		"SELECT string_agg(gid, ',' order by gid asc) FROM pg_prepared_xacts"),
+	'pxact1,pxact2', "prepared transactions 'pxact1' and 'pxact2' exists" );
+
+# Commit one 2PC and check it on alpha and beta
+$node_alpha->safe_psql( 'postgres', "commit prepared 'pxact1'");
+
+is( $node_alpha->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' commited" );
+
+$node_alpha->wait_for_catchup($node_beta);
+$node_alpha->wait_for_catchup($node_gamma);
+
+is( $node_beta->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' replicated to beta" );
+
+is( $node_gamma->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' replicated to gamma" );
+
+# create another idle in xact session
+$sess1_in = q{
+BEGIN;
+CREATE TABLE public.test_succeed (i int);
+SELECT pg_backend_pid();
+};
+$sess1->pump until $sess1_out =~ qr/\d+\s*$/m;
+$sess1_pid = $sess1_out;
+chomp $sess1_pid;
+
+# swap roles between alpha and beta
+
+# Demote alpha in smart mode.
+# Don't wait for demote to complete here so we can use sess1
+# to keep doing some more write activity before commit and demote.
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_demote(false, false)'),
+	't', "demote signal sent to node alpha" );
+
+# wait for the demote to begin and wait for active xact.
+my $fh;
+while (1) {
+	my $status;
+	open my $fh, '<', $node_alpha->data_dir . '/postmaster.pid';
+	$status = $_ while <$fh>;
+	close $fh;
+	chomp($status);
+	last if $status eq 'demoting';
+	sleep 1;
+}
+
+# make sure the demote waits for running xacts
+sleep 2;
+
+# test no new session possible during demote
+$sess2_in = q{
+SELECT 1;
+};
+$sess2->start;
+$sess2->finish;
+ok( $sess2_err =~ /FATAL:  the database system is demoting\s$/, 'session rejected during demote process');
+
+# add some write activity on demote-blocking session sess1
+$sess1_out = '';
+$sess1_in = q{
+INSERT INTO public.test_succeed VALUES (1) RETURNING i;
+COMMIT;
+};
+$sess1->pump until $sess1_out =~ qr/\d+\s*$/m;
+$sess1->finish;
+
+chomp($sess1_out);
+is($sess1_out, '1', 'session in active xact able to write the smart demote signal');
+
+$node_alpha->poll_query_until('postgres', 'SELECT pg_is_in_recovery()', 't');
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	't', "node alpha demoted" );
+
+# fetch the last REDO location from alpha and chek beta received everyting
+my ($stdout, $stderr) = run_command([ 'pg_controldata', $node_alpha->data_dir ]);
+$stdout =~ m{REDO location:\s+([0-9A-F]+/[0-9A-F]+)$}mg;
+my $redo_loc = $1;
+
+is( $node_beta->safe_psql(
+		'postgres',
+		"SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), '$redo_loc') > 0 "),
+	't', "node beta received the demote checkpoint from alpha" );
+
+# promote beta and check it
+$node_beta->promote;
+is( $node_beta->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	'f', "node beta promoted" );
+
+# Setup alpha to replicate from beta
+$node_alpha->enable_streaming($node_beta);
+$node_alpha->reload;
+
+# check alpha is replicating from it
+$node_beta->wait_for_catchup($node_alpha);
+
+is( $node_beta->safe_psql(
+		'postgres', 'SELECT application_name FROM pg_stat_replication'),
+	$node_alpha->name, 'alpha is replicating from beta' );
+
+# check gamma is still replicating from from alpha
+$node_alpha->wait_for_catchup($node_gamma, 'write', $node_alpha->lsn('receive'));
+
+is( $node_alpha->safe_psql(
+		'postgres', 'SELECT application_name FROM pg_stat_replication'),
+	$node_gamma->name, 'gamma is replicating from beta' );
+
+# make sure the second 2PC is still available on beta
+is( $node_beta->safe_psql(
+		'postgres', 'SELECT gid FROM pg_prepared_xacts'),
+	'pxact2', "prepared transactions pxact2' exists" );
+
+# commit the second 2PC and check its result on alpha and beta nodes
+$node_beta->safe_psql( 'postgres', "commit prepared 'pxact2'");
+
+is( $node_beta->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' commited" );
+
+$node_beta->wait_for_catchup($node_alpha);
+is( $node_alpha->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' streamed to alpha" );
+
+# check the 2PC has been cascaded to gamma
+$node_alpha->wait_for_catchup($node_gamma, 'write', $node_alpha->lsn('receive'));
+is( $node_gamma->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' streamed to gamma" );
-- 
2.20.1


--MP_/Mp45B_GpB5m14pibp/TRwlo--





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with \dA++ and \dn++ (for which the single-plus
commands have historically not done any slow operations).

Also change to show the size only with \db++ and \l++ (for which it's
useful to show the ACL without also doing any slow operations).

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose (??)

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 20 +++++++++++-------
 src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------
 src/bin/psql/describe.h |  8 +++----
 3 files changed, 50 insertions(+), 24 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index fae5940b54e..14c772c18f3 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -370,6 +370,7 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
+			 strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 ||
 			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
@@ -757,6 +758,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -764,7 +766,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -789,7 +794,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -815,7 +820,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -869,7 +874,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1952,14 +1957,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f67bf0b8925..1d18f7c6cd9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -138,12 +138,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -175,6 +175,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -212,7 +217,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -232,12 +237,18 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		appendPQExpBuffer(&buf,
+						  ",\n  spcoptions AS \"%s\"",
+						  gettext_noop("Options"));
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
-						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
-						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -908,7 +919,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -959,20 +970,24 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("ICU Rules"));
 	appendPQExpBufferStr(&buf, "  ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 						  "       ELSE 'No Access'\n"
-						  "  END as \"%s\""
+						  "  END as \"%s\"",
+						  gettext_noop("Size"));
+
+	if (verbose > 0)
+		appendPQExpBuffer(&buf,
 						  ",\n  t.spcname as \"%s\""
 						  ",\n  pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
-						  gettext_noop("Size"),
 						  gettext_noop("Tablespace"),
 						  gettext_noop("Description"));
+
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_database d\n");
-	if (verbose)
+	if (verbose > 0)
 		appendPQExpBufferStr(&buf,
 							 "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 
@@ -5023,7 +5038,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -5045,6 +5060,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 273f974538e..10271ba1393 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.42.0


--+z08/w6uVZ2u/a/7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with \dA++ and \dn++ (for which the single-plus
commands have historically not done any slow operations).

Also change to show the size only with \db++ and \l++ (for which it's
useful to show the ACL without also doing any slow operations).

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose (??)

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 20 +++++++++++-------
 src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------
 src/bin/psql/describe.h |  8 +++----
 3 files changed, 50 insertions(+), 24 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 1300869d797..667beb1b16e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -369,6 +369,7 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
+			 strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 ||
 			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
@@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1945,14 +1950,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 45f6a86b872..3be7a5b8dc8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		appendPQExpBuffer(&buf,
+						  ",\n  spcoptions AS \"%s\"",
+						  gettext_noop("Options"));
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
-						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
-						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -914,7 +925,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("ICU Rules"));
 	appendPQExpBufferStr(&buf, "  ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 						  "       ELSE 'No Access'\n"
-						  "  END as \"%s\""
+						  "  END as \"%s\"",
+						  gettext_noop("Size"));
+
+	if (verbose > 0)
+		appendPQExpBuffer(&buf,
 						  ",\n  t.spcname as \"%s\""
 						  ",\n  pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
-						  gettext_noop("Size"),
 						  gettext_noop("Tablespace"),
 						  gettext_noop("Description"));
+
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_database d\n");
-	if (verbose)
+	if (verbose > 0)
 		appendPQExpBufferStr(&buf,
 							 "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 
@@ -5031,7 +5046,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -5053,6 +5068,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 24c0884a347..f11579a7bbc 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -65,7 +65,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -90,7 +90,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.34.1


--X119r+cMzLJwahiZ
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with \dA++ and \dn++ (for which the single-plus
commands have historically not done any slow operations).

Also change to show the size only with \db++ and \l++ (for which it's
useful to show the ACL without also doing any slow operations).

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 20 +++++++++++-------
 src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------
 src/bin/psql/describe.h |  8 +++----
 3 files changed, 50 insertions(+), 24 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 955397ee9dc..875b659a26d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -369,6 +369,7 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
+			 strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 ||
 			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
@@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1943,14 +1948,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 99e28f607e8..7de604a894d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		appendPQExpBuffer(&buf,
+						  ",\n  spcoptions AS \"%s\"",
+						  gettext_noop("Options"));
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
-						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
-						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -914,7 +925,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("ICU Rules"));
 	appendPQExpBufferStr(&buf, "  ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 						  "       ELSE 'No Access'\n"
-						  "  END as \"%s\""
+						  "  END as \"%s\"",
+						  gettext_noop("Size"));
+
+	if (verbose > 0)
+		appendPQExpBuffer(&buf,
 						  ",\n  t.spcname as \"%s\""
 						  ",\n  pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
-						  gettext_noop("Size"),
 						  gettext_noop("Tablespace"),
 						  gettext_noop("Description"));
+
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_database d\n");
-	if (verbose)
+	if (verbose > 0)
 		appendPQExpBufferStr(&buf,
 							 "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 
@@ -4973,7 +4988,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4995,6 +5010,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 554fe867255..d2fd8a72a36 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.34.1


--OZ1/qgOAlc2dTU8W
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with ++ in \dn, \dA, \db and (for consistency) \l

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 22 ++++++++++++++--------
 src/bin/psql/describe.c | 30 ++++++++++++++++++++++--------
 src/bin/psql/describe.h |  8 ++++----
 3 files changed, 40 insertions(+), 20 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index de6a3a71f8a..40956cc32dc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -368,7 +368,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 ||
+			 strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -753,6 +754,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -760,7 +762,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -785,7 +790,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -811,7 +816,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -865,7 +870,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1942,14 +1947,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index df166365e81..57666d8c5b8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -234,12 +239,16 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
 						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -919,7 +928,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -952,7 +961,7 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("Locale Provider"));
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
@@ -4954,7 +4963,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4976,6 +4985,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index bd051e09cbb..84df35f4623 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.25.1


--H1spWtNR+x+ondvy
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH v8 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with ++ in \dn, \dA, \db and (for consistency) \l

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 22 ++++++++++++++--------
 src/bin/psql/describe.c | 30 ++++++++++++++++++++++--------
 src/bin/psql/describe.h |  8 ++++----
 3 files changed, 40 insertions(+), 20 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a81bd3307b4..99ee47f436a 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -366,7 +366,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 ||
+			 strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -717,6 +718,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -724,7 +726,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -749,7 +754,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -775,7 +780,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -829,7 +834,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1904,14 +1909,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 327a69487bb..677dd64cda0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -234,12 +239,16 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
 						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -919,7 +928,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -952,7 +961,7 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("Locale Provider"));
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
@@ -4949,7 +4958,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4971,6 +4980,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 7872c71f58d..4d889c71368 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.17.1


--cKDw3XFoqocuprIa
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v8-0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with ++ in \dn, \dA, \db and (for consistency) \l

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.
---
 src/bin/psql/command.c  | 22 ++++++++++++++--------
 src/bin/psql/describe.c | 30 ++++++++++++++++++++++--------
 src/bin/psql/describe.h |  8 ++++----
 3 files changed, 40 insertions(+), 20 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b51d28780b1..cb65283547c 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -366,7 +366,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 ||
+			 strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -718,6 +719,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -725,7 +727,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -750,7 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -777,7 +782,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -831,7 +836,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1911,14 +1916,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		if (pattern)
 			free(pattern);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1a5d924a23f..67163e834bc 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -136,12 +136,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -173,6 +173,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -208,7 +213,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -228,12 +233,16 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
 						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -899,7 +908,7 @@ describeOperators(const char *oper_pattern,
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -932,7 +941,7 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("Locale Provider"));
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
@@ -4881,7 +4890,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4903,6 +4912,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 160000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 7872c71f58d..4d889c71368 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.17.1


--HcAYCG3uE/tztfnV
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH v4 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with ++ in \dn, \dA, \db and (for consistency) \l

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.
---
 src/bin/psql/command.c  | 22 ++++++++++++++--------
 src/bin/psql/describe.c | 30 ++++++++++++++++++++++--------
 src/bin/psql/describe.h |  8 ++++----
 3 files changed, 40 insertions(+), 20 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ccd7b48108..c9b0a01f9d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -362,7 +362,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 ||
+			 strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -707,6 +708,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -714,7 +716,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -739,7 +744,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -766,7 +771,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				success = listConversions(pattern, show_verbose, show_system);
@@ -813,7 +818,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1856,14 +1861,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		if (pattern)
 			free(pattern);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c28788e84f..c818e657da 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -128,12 +128,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -165,6 +165,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 150000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -198,7 +203,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -218,12 +223,16 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
 						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -877,7 +886,7 @@ describeOperators(const char *oper_pattern,
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -898,7 +907,7 @@ listAllDbs(const char *pattern, bool verbose)
 					  gettext_noop("Ctype"));
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
@@ -4657,7 +4666,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4679,6 +4688,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 150000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 71b320f1fc..edeea38580 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -83,7 +83,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.17.0


--2fEWJT3hVM9yyfvd
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with \dA++ and \dn++ (for which the single-plus
commands have historically not done any slow operations).

Also change to show the size only with \db++ and \l++ (for which it's
useful to show the ACL without also doing any slow operations).

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose (??)

The idea for plusplus commands were previously discussed here.
https://www.postgresql.org/message-id/20190506163359.GA29291%40alvherre.pgsql
---
 src/bin/psql/command.c  | 20 +++++++++++-------
 src/bin/psql/describe.c | 46 +++++++++++++++++++++++++++++------------
 src/bin/psql/describe.h |  8 +++----
 3 files changed, 50 insertions(+), 24 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 511debbe814..b695e09017d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -369,6 +369,7 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
+			 strcmp(cmd, "l++") == 0 || strcmp(cmd, "list++") == 0 ||
 			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
@@ -754,6 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -761,7 +763,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -786,7 +791,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -812,7 +817,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				if (strncmp(cmd, "dconfig", 7) == 0)
@@ -866,7 +871,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1943,14 +1948,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		free(pattern);
 	}
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 9325a46b8fd..f7f737e8b59 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -139,12 +139,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -176,6 +176,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -214,7 +219,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -234,12 +239,18 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		appendPQExpBuffer(&buf,
+						  ",\n  spcoptions AS \"%s\"",
+						  gettext_noop("Options"));
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
-						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
-						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -914,7 +925,7 @@ error_return:
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -961,20 +972,24 @@ listAllDbs(const char *pattern, bool verbose)
 						  gettext_noop("ICU Rules"));
 	appendPQExpBufferStr(&buf, "  ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "       THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
 						  "       ELSE 'No Access'\n"
-						  "  END as \"%s\""
+						  "  END as \"%s\"",
+						  gettext_noop("Size"));
+
+	if (verbose > 0)
+		appendPQExpBuffer(&buf,
 						  ",\n  t.spcname as \"%s\""
 						  ",\n  pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
-						  gettext_noop("Size"),
 						  gettext_noop("Tablespace"),
 						  gettext_noop("Description"));
+
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_database d\n");
-	if (verbose)
+	if (verbose > 0)
 		appendPQExpBufferStr(&buf,
 							 "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 
@@ -4970,7 +4985,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4992,6 +5007,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 170000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 554fe867255..d2fd8a72a36 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -87,7 +87,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.34.1


--mDjj41GS8UJ6l/Dp
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+
@ 2021-12-18 20:58 Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 10+ messages in thread

From: Justin Pryzby @ 2021-12-18 20:58 UTC (permalink / raw)

show the size only with ++ in \dn, \dA, \db and (for consistency) \l

\dt+ and \dP+ are not changed, since showing the table sizes seems to be their
primary purpose.
---
 src/bin/psql/command.c  | 22 ++++++++++++++--------
 src/bin/psql/describe.c | 30 ++++++++++++++++++++++--------
 src/bin/psql/describe.h |  8 ++++----
 3 files changed, 40 insertions(+), 20 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index f5904748553..cfae8fd6d12 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -366,7 +366,8 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "l++") == 0 ||
+			 strcmp(cmd, "list+") == 0 || strcmp(cmd, "list++") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -718,6 +719,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
+		int			verbose = 0;
 		bool		show_verbose,
 					show_system;
 
@@ -725,7 +727,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
+
+		show_verbose = (bool) (verbose != 0);
 		show_system = strchr(cmd, 'S') ? true : false;
 
 		switch (cmd[1])
@@ -750,7 +755,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					{
 						case '\0':
 						case '+':
-							success = describeAccessMethods(pattern, show_verbose);
+							success = describeAccessMethods(pattern, verbose);
 							break;
 						case 'c':
 							success = listOperatorClasses(pattern, pattern2, show_verbose);
@@ -777,7 +782,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = describeAggregates(pattern, show_verbose, show_system);
 				break;
 			case 'b':
-				success = describeTablespaces(pattern, show_verbose);
+				success = describeTablespaces(pattern, verbose);
 				break;
 			case 'c':
 				success = listConversions(pattern, show_verbose, show_system);
@@ -824,7 +829,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				success = listLanguages(pattern, show_verbose, show_system);
 				break;
 			case 'n':
-				success = listSchemas(pattern, show_verbose, show_system);
+				success = listSchemas(pattern, verbose, show_system);
 				break;
 			case 'o':
 				success = exec_command_dfo(scan_state, cmd, pattern,
@@ -1904,14 +1909,15 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	if (active_branch)
 	{
 		char	   *pattern;
-		bool		show_verbose;
+		int			verbose = 0;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
-		show_verbose = strchr(cmd, '+') ? true : false;
+		for (const char *t = cmd; *t != '\0'; ++t)
+			verbose += *t == '+' ? 1 : 0;
 
-		success = listAllDbs(pattern, show_verbose);
+		success = listAllDbs(pattern, verbose);
 
 		if (pattern)
 			free(pattern);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8587b19160d..de62822674e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -128,12 +128,12 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
  * Takes an optional regexp to select particular access methods
  */
 bool
-describeAccessMethods(const char *pattern, bool verbose)
+describeAccessMethods(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	static const bool translate_columns[] = {false, true, false, false};
+	static const bool translate_columns[] = {false, true, false, false, false};
 
 	if (pset.sversion < 90600)
 	{
@@ -165,6 +165,11 @@ describeAccessMethods(const char *pattern, bool verbose)
 						  "  pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
 						  gettext_noop("Handler"),
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 150000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_am_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
@@ -198,7 +203,7 @@ describeAccessMethods(const char *pattern, bool verbose)
  * Takes an optional regexp to select particular tablespaces
  */
 bool
-describeTablespaces(const char *pattern, bool verbose)
+describeTablespaces(const char *pattern, int verbose)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -218,12 +223,16 @@ describeTablespaces(const char *pattern, bool verbose)
 	{
 		appendPQExpBufferStr(&buf, ",\n  ");
 		printACLColumn(&buf, "spcacl");
+
+		if (verbose > 1)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
+							  gettext_noop("Size"));
+
 		appendPQExpBuffer(&buf,
 						  ",\n  spcoptions AS \"%s\""
-						  ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
 						  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 						  gettext_noop("Options"),
-						  gettext_noop("Size"),
 						  gettext_noop("Description"));
 	}
 
@@ -877,7 +886,7 @@ describeOperators(const char *oper_pattern,
  * for \l, \list, and -l switch
  */
 bool
-listAllDbs(const char *pattern, bool verbose)
+listAllDbs(const char *pattern, int verbose)
 {
 	PGresult   *res;
 	PQExpBufferData buf;
@@ -898,7 +907,7 @@ listAllDbs(const char *pattern, bool verbose)
 					  gettext_noop("Ctype"));
 	appendPQExpBufferStr(&buf, "       ");
 	printACLColumn(&buf, "d.datacl");
-	if (verbose)
+	if (verbose > 1)
 		appendPQExpBuffer(&buf,
 						  ",\n       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
 						  "            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
@@ -4665,7 +4674,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
  * Describes schemas (namespaces)
  */
 bool
-listSchemas(const char *pattern, bool verbose, bool showSystem)
+listSchemas(const char *pattern, int verbose, bool showSystem)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -4687,6 +4696,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		if (verbose > 1 && pset.sversion >= 150000)
+			appendPQExpBuffer(&buf,
+							  ",\n  pg_catalog.pg_size_pretty(pg_namespace_size(n.oid)) AS \"%s\"",
+							  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index fd6079679c6..5a1b97805df 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,10 +13,10 @@
 extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
 
 /* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool describeAccessMethods(const char *pattern, int verbose);
 
 /* \db */
-extern bool describeTablespaces(const char *pattern, bool verbose);
+extern bool describeTablespaces(const char *pattern, int verbose);
 
 /* \df, \dfa, \dfn, \dft, \dfw, etc. */
 extern bool describeFunctions(const char *functypes, const char *func_pattern,
@@ -62,7 +62,7 @@ extern bool listTSDictionaries(const char *pattern, bool verbose);
 extern bool listTSTemplates(const char *pattern, bool verbose);
 
 /* \l */
-extern bool listAllDbs(const char *pattern, bool verbose);
+extern bool listAllDbs(const char *pattern, int verbose);
 
 /* \dt, \di, \ds, \dS, etc. */
 extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
@@ -83,7 +83,7 @@ extern bool listCasts(const char *pattern, bool verbose);
 extern bool listCollations(const char *pattern, bool verbose, bool showSystem);
 
 /* \dn */
-extern bool listSchemas(const char *pattern, bool verbose, bool showSystem);
+extern bool listSchemas(const char *pattern, int verbose, bool showSystem);
 
 /* \dew */
 extern bool listForeignDataWrappers(const char *pattern, bool verbose);
-- 
2.17.1


--XLWMkxR+mZNQ4WTO
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0003-f-convert-the-other-verbose-to-int-too.patch"



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

end of thread, other threads:[~2021-12-18 20:58 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-10 00:00 [PATCH 4/4] demote: add various tests related to demote and promote actions Jehan-Guillaume de Rorthais <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH v8 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH v4 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>
2021-12-18 20:58 [PATCH 2/4] psql: add convenience commands: \dA+ and \dn+ Justin Pryzby <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox