Tab completion for GRANT MAINTAIN

2023-04-18 Thread Ken Kato 

Hi hackers,

I found that GRANT MAINTAIN is not tab-completed with ON, so here is a 
patch.


Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5825b2a195..bd04244969 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3902,7 +3902,7 @@ psql_completion(const char *text, int start, int end)
 	else if (TailMatches("GRANT|REVOKE", MatchAny) ||
 			 TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny))
 	{
-		if (TailMatches("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|ALL"))
+		if (TailMatches("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|MAINTAIN|ALL"))
 			COMPLETE_WITH("ON");
 		else if (TailMatches("GRANT", MatchAny))
 			COMPLETE_WITH("TO");

Re: Add last_vacuum_index_scans in pg_stat_all_tables

2022-10-03 Thread Ken Kato 

The problem is that you're not closing the 


Thank you for the reviews and comments.
I closed the  so that the problem should be fixed now.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 342b20ebeb..14cb496fdc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4562,6 +4562,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
daemon
   
  
+
+ 
+  
+   last_vacuum_index_scans bigint
+  
+  
+   Number of splitted index scans performed during the the last vacuum
+   on this table
+  
+ 
 

   
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index dfbe37472f..c5b4405f4b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -629,7 +629,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 		 rel->rd_rel->relisshared,
 		 Max(vacrel->new_live_tuples, 0),
 		 vacrel->recently_dead_tuples +
-		 vacrel->missed_dead_tuples);
+		 vacrel->missed_dead_tuples,
+		 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f7ec79e0..f854576b20 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -675,7 +675,9 @@ CREATE VIEW pg_stat_all_tables AS
 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
 pg_stat_get_analyze_count(C.oid) AS analyze_count,
-pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
+pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
 FROM pg_class C LEFT JOIN
  pg_index I ON C.oid = I.indrelid
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-	 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+	 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index eadd8464ff..573f761f6b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 68bb032d3e..9cadf4df7a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5334,6 +5334,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_last_vacuum_index_scans' },
 { oid => '1936', descr => 'statistics: currently active backend IDs',
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4',
diff --git a/s

Re: Add last_vacuum_index_scans in pg_stat_all_tables

2022-09-15 Thread Ken Kato 

Regression is failing on all platforms; please correct that and
resubmit the patch.


Hi,

Thank you for the review!
I fixed it and resubmitting the patch.

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..d58dbc36bc 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4562,6 +4562,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
daemon
   
  
+
+ 
+  
+   last_vacuum_index_scans bigint
+  
+  
+   Number of splitted index scans performed during the the last vacuum
+   on this table
+  
+ 
 

   
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index dfbe37472f..c5b4405f4b 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -629,7 +629,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 		 rel->rd_rel->relisshared,
 		 Max(vacrel->new_live_tuples, 0),
 		 vacrel->recently_dead_tuples +
-		 vacrel->missed_dead_tuples);
+		 vacrel->missed_dead_tuples,
+		 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f7ec79e0..f854576b20 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -675,7 +675,9 @@ CREATE VIEW pg_stat_all_tables AS
 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
 pg_stat_get_analyze_count(C.oid) AS analyze_count,
-pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
+pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
 FROM pg_class C LEFT JOIN
  pg_index I ON C.oid = I.indrelid
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-	 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+	 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index be15b4b2e5..d5fd34ee0e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a07e737a33..36c6c53e65 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5331,6 +5331,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_last_vacuum_index_scans' },
 { oid => '1936', descr => 'statistics: currently active backend IDs',
   proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't',
   provolatile => 's', proparallel => 'r', prorettype => 'int4'

Re: pg_stat_wal: tracking the compression effect

2022-08-29 Thread Ken Kato 

On 2022-08-27 16:48, Bharath Rupireddy wrote:

On Fri, Aug 26, 2022 at 8:39 AM Kyotaro Horiguchi
 wrote:


At Fri, 26 Aug 2022 11:55:27 +0900 (JST), Kyotaro Horiguchi 
 wrote in

> At Thu, 25 Aug 2022 16:04:50 +0900, Ken Kato  wrote 
in
> > Accumulating the values, which indicates how much space is saved by
> > each compression (size before compression - size after compression),
> > and keep track of how many times compression has happened. So that one
> > can know how much space is saved on average.
>
> Honestly, I don't think its useful much.
> How about adding them to pg_waldump and pg_walinspect instead?
>
> # It further widens the output of pg_waldump, though..

Sorry, that was apparently too short.

I know you already see that in per-record output of pg_waldump, but
maybe we need the summary of saved bytes in "pg_waldump -b -z" output
and the corresponding output of pg_walinspect.


+1 for adding compression stats such as type and saved bytes to
pg_waldump and pg_walinspect given that the WAL records already have
the saved bytes info. Collecting them in the server via pg_stat_wal
will require some extra effort, for instance, every WAL record insert
requires that code to be executed. When users want to analyze the
compression efforts they can either use pg_walinspect or pg_waldump
and change the compression type if required.


Thank you for all the comments!

I will go with adding the compression stats in pg_waldump and 
pg_walinspect.


Regards,
--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

pg_stat_wal: tracking the compression effect

2022-08-25 Thread Ken Kato 



Hi hackers,

We can specify compression method (for example, lz4, zstd), but it is 
hard to know the effect of compression depending on the method. There is 
already a way to know the compression effect using pg_waldump. However, 
having these statistics in the view makes it more accessible. I am 
proposing to add statistics, which keeps track of compression effect in 
pg_stat_ wal view.


The design I am thinking is below:

compression_saved | compression_times
--+---
38741 |6


Accumulating the values, which indicates how much space is saved by each 
compression (size before compression - size after compression), and keep 
track of how many times compression has happened. So that one can know 
how much space is saved on average.


What do you think?

Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Re: Add last_vacuum_index_scans in pg_stat_all_tables

2022-07-15 Thread Ken Kato 

On 2022-07-09 03:18, Peter Geoghegan wrote:
On Fri, Jul 8, 2022 at 10:47 AM Alvaro Herrera 
 wrote:

Saving some sort of history would be much more useful, but of course a
lot more work.


Thank you for the comments!
Yes, having some sort of history would be ideal in this case.
However, I am not sure how to implement those features at this moment, 
so I will take some time to consider.


At the same time, I think having this metrics exposed in the 
pg_stat_all_tables comes in handy when tuning the 
maintenance_work_mem/autovacuume_work_mem even though it shows the value 
of only last vacuum/autovacuum.


Regards,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Add last_vacuum_index_scans in pg_stat_all_tables

2022-07-04 Thread Ken Kato 


Hi hackers,

I think having number of index scans of the last vacuum in 
pg_stat_all_tables can be helpful. This value shows how efficiently 
vacuums have performed and can be an indicator to increase 
maintenance_work_mem.


It was proposed previously[1], but it was not accepted due to the 
limitation of stats collector. Statistics are now stored in shared 
memory, so we got more rooms to store statistics. I think this 
statistics is still valuable for some people, so I am proposing this 
again.


Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

[1] 
https://www.postgresql.org/message-id/20171010.192616.108347483.horiguchi.kyotaro%40lab.ntt.co.jpdiff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4549c2560e..bd7bfa7d9d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4557,6 +4557,16 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
daemon
   
  
+
+ 
+  
+   last_vacuum_index_scans bigint
+  
+  
+   Number of splitted index scans performed during the the last vacuum
+   on this table
+  
+ 
 

   
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index b802ed247e..bf47d6837d 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -625,7 +625,8 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 		 rel->rd_rel->relisshared,
 		 Max(vacrel->new_live_tuples, 0),
 		 vacrel->recently_dead_tuples +
-		 vacrel->missed_dead_tuples);
+		 vacrel->missed_dead_tuples,
+		 vacrel->num_index_scans);
 	pgstat_progress_end_command();
 
 	if (instrument)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fedaed533b..475551d9b3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -676,7 +676,9 @@ CREATE VIEW pg_stat_all_tables AS
 pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
 pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
 pg_stat_get_analyze_count(C.oid) AS analyze_count,
-pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
+pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+pg_stat_get_last_vacuum_index_scans(C.oid) AS last_vacuum_index_scans
+
 FROM pg_class C LEFT JOIN
  pg_index I ON C.oid = I.indrelid
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index a846d9ffb6..ffc9daf944 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -208,8 +208,8 @@ pgstat_drop_relation(Relation rel)
  * Report that the table was just vacuumed.
  */
 void
-pgstat_report_vacuum(Oid tableoid, bool shared,
-	 PgStat_Counter livetuples, PgStat_Counter deadtuples)
+pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter livetuples,
+	 PgStat_Counter deadtuples, PgStat_Counter num_index_scans)
 {
 	PgStat_EntryRef *entry_ref;
 	PgStatShared_Relation *shtabentry;
@@ -232,6 +232,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
 
 	tabentry->n_live_tuples = livetuples;
 	tabentry->n_dead_tuples = deadtuples;
+	tabentry->n_index_scans = num_index_scans;
 
 	/*
 	 * It is quite possible that a non-aggressive VACUUM ended up skipping
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 893690dad5..57d1de52e6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -179,6 +179,20 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(result);
 }
 
+Datum
+pg_stat_get_last_vacuum_index_scans(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatTabEntry *tabentry;
+
+	if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+		result = 0;
+	else
+		result = tabentry->n_index_scans;
+
+	PG_RETURN_INT64(result);
+}
 
 Datum
 pg_stat_get_mod_since_analyze(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a77b293723..d061cd2d8d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5331,6 +5331,10 @@
   proname => 'pg_stat_get_autoanalyze_count', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_autoanalyze_count' },
+{ oid => '3813', descr => 'statistics: last vacuum index scans for a table',
+  proname => 'pg_stat_get_last_vacuum_index_scans', provolatile => 's',
+  proparallel => 'r', pro

Re: [PATCH] Add min() and max() aggregate functions for xid8

2022-02-08 Thread Ken Kato 

On 2022-02-08 23:16, Fujii Masao wrote:

If you want to avoid the line longer than 80 columns, you should break
it into two or more rather than remove the test code, I think. What to
test is more important than formatting.

Also the following descriptions about formatting would be helpful.

---
https://www.postgresql.org/docs/devel/source-format.html

Limit line lengths so that the code is readable in an 80-column window.
(This doesn't mean that you must never go past 80 columns. For 
instance,

breaking a long error message string in arbitrary places just to keep
the code within 80 columns is probably not a net gain in readability.)
---

Therefore I'm ok with the patch that I posted upthread. Also I'm ok if
you will break that longer line into two and post new patch. Or if the
value '010' is really useless for the test purpose, I'm also ok if you
remove it. Thought?


Thank you for the explanation!

Even though the line is over 80 characters, it makes more sense to put 
in one line and it enhances readability IMO.
Also, '010' is good to have since it is the only octal value in the 
test.


Therefore, I think min_max_aggregates_for_xid8_v4.patch is the best one 
to go.


Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Re: [PATCH] Add min() and max() aggregate functions for xid8

2022-02-08 Thread Ken Kato 

On 2022-02-08 15:34, Fujii Masao wrote:

Thanks for updating the patch! It basically looks good to me. I
applied the following small changes to the patch. Updated version of
the patch attached. Could you review this version?


Thank you for the patch!

It looks good to me!

I'm not sure how strict coding conventions are, but the following line 
is over 80 characters.
+insert into xid8_t1 values ('0'), ('010'), ('42'), 
('0x'), ('-1');
Therefore, I made a patch which removed ('010') just to fit in 80 
characters.



Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8754f2f89b..1b064b4feb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19973,7 +19973,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
@@ -19992,7 +19992,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
index 9b4ceaea47..e4b4952a28 100644
--- a/src/backend/utils/adt/xid.c
+++ b/src/backend/utils/adt/xid.c
@@ -286,6 +286,30 @@ xid8cmp(PG_FUNCTION_ARGS)
 		PG_RETURN_INT32(-1);
 }
 
+Datum
+xid8_larger(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	if (FullTransactionIdFollows(fxid1, fxid2))
+		PG_RETURN_FULLTRANSACTIONID(fxid1);
+	else
+		PG_RETURN_FULLTRANSACTIONID(fxid2);
+}
+
+Datum
+xid8_smaller(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	if (FullTransactionIdPrecedes(fxid1, fxid2))
+		PG_RETURN_FULLTRANSACTIONID(fxid1);
+	else
+		PG_RETURN_FULLTRANSACTIONID(fxid2);
+}
+
 /*
  *	 COMMAND IDENTIFIER ROUTINES			 *
  */
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 137f6eef69..2843f4b415 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -149,6 +149,9 @@
 { aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
   aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'max(xid8)', aggtransfn => 'xid8_larger',
+  aggcombinefn => 'xid8_larger', aggsortop => '>(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # min
 { aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -214,6 +217,9 @@
 { aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
   aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'min(xid8)', aggtransfn => 'xid8_smaller',
+  aggcombinefn => 'xid8_smaller', aggsortop => '<(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # count
 { aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7024dbe10a..62f36daa98 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -203,6 +203,12 @@
 { oid => '5071', descr => 'convert xid8 to xid',
   proname => 'xid', prorettype => 'xid', proargtypes => 'xid8',
   prosrc => 'xid8toxid' },
+{ oid => '5097', descr => 'larger of two',
+  proname => 'xid8_larger', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_larger' },
+{ oid => '5098', descr => 'smaller of two',
+  proname => 'xid8_smaller', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_smaller' },
 { oid => '69',
   proname => 'cideq', proleakproof => 't', prorettype => 'bool',
   proargtypes => 'cid cid', prosrc => 'cideq'

Re: [PATCH] Add min() and max() aggregate functions for xid8

2022-02-07 Thread Ken Kato 


Thank you for the comments!


if (FullTransactionIdFollows(fxid1, fxid2))
PG_RETURN_FULLTRANSACTIONID(fxid1);
else
PG_RETURN_FULLTRANSACTIONID(fxid2);



Isn't it better to use '0x'::xid8 instead of
'18446744073709551615'::xid8, to more easily understand that this test
uses maximum number allowed as xid8?


I updated these two parts as you suggested.



In addition to those two xid8 values, IMO it's better to insert also
the xid8 value neither minimum nor maximum xid8 ones, for example,
'42'::xid8.


I added '010'::xid8, '42'::xid8, and '-1'::xid8
in addition to '0'::xid8 and '0x'::xid8
just to have more varieties.


Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8754f2f89b..1b064b4feb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19973,7 +19973,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
@@ -19992,7 +19992,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
index 9b4ceaea47..9f7e1816b0 100644
--- a/src/backend/utils/adt/xid.c
+++ b/src/backend/utils/adt/xid.c
@@ -286,6 +286,30 @@ xid8cmp(PG_FUNCTION_ARGS)
 		PG_RETURN_INT32(-1);
 }
 
+Datum
+xid8_larger(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	if (FullTransactionIdFollowsOrEquals(fxid1, fxid2))
+		PG_RETURN_FULLTRANSACTIONID(fxid1);
+	else
+		PG_RETURN_FULLTRANSACTIONID(fxid2);
+}
+
+Datum
+xid8_smaller(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	if (FullTransactionIdPrecedesOrEquals(fxid1, fxid2))
+		PG_RETURN_FULLTRANSACTIONID(fxid1);
+	else
+		PG_RETURN_FULLTRANSACTIONID(fxid2);
+}
+
 /*
  *	 COMMAND IDENTIFIER ROUTINES			 *
  */
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 137f6eef69..2843f4b415 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -149,6 +149,9 @@
 { aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
   aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'max(xid8)', aggtransfn => 'xid8_larger',
+  aggcombinefn => 'xid8_larger', aggsortop => '>(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # min
 { aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -214,6 +217,9 @@
 { aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
   aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'min(xid8)', aggtransfn => 'xid8_smaller',
+  aggcombinefn => 'xid8_smaller', aggsortop => '<(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # count
 { aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7024dbe10a..62f36daa98 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -203,6 +203,12 @@
 { oid => '5071', descr => 'convert xid8 to xid',
   proname => 'xid', prorettype => 'xid', proargtypes => 'xid8',
   prosrc => 'xid8toxid' },
+{ oid => '5097', descr => 'larger of two',
+  proname => 'xid8_larger', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_larger' },
+{ oid => '5098', descr => 'smaller of two',
+  proname => 'xid8_smaller', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_smaller' },
 { oid => '69',

Re: [PATCH] Add min() and max() aggregate functions for xid8

2022-02-04 Thread Ken Kato 

+   PG_RETURN_FULLTRANSACTIONID((U64FromFullTransactionId(fxid1) >
U64FromFullTransactionId(fxid2)) ? fxid1 : fxid2);

Shouldn't we use FullTransactionIdFollows() to compare those two fxid
values here, instead?

+   PG_RETURN_FULLTRANSACTIONID((U64FromFullTransactionId(fxid1) <
U64FromFullTransactionId(fxid2)) ? fxid1 : fxid2);

Shouldn't we use FullTransactionIdPrecedes() to compare those two fxid
values here, instead?

Could you add the regression tests for those min() and max() functions 
for xid8?


Thank you for the comments.
I sent my old version of patch by mistake.
This is the updated one.

Best wishes

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8754f2f89b..1b064b4feb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19973,7 +19973,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
@@ -19992,7 +19992,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
index 9b4ceaea47..32009f81ae 100644
--- a/src/backend/utils/adt/xid.c
+++ b/src/backend/utils/adt/xid.c
@@ -286,6 +286,24 @@ xid8cmp(PG_FUNCTION_ARGS)
 		PG_RETURN_INT32(-1);
 }
 
+Datum
+xid8_larger(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	PG_RETURN_FULLTRANSACTIONID((FullTransactionIdFollowsOrEquals(fxid1, fxid2)) ? fxid1 : fxid2);
+}
+
+Datum
+xid8_smaller(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	PG_RETURN_FULLTRANSACTIONID((FullTransactionIdPrecedesOrEquals(fxid1, fxid2)) ? fxid1 : fxid2);
+}
+
 /*
  *	 COMMAND IDENTIFIER ROUTINES			 *
  */
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 137f6eef69..2843f4b415 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -149,6 +149,9 @@
 { aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
   aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'max(xid8)', aggtransfn => 'xid8_larger',
+  aggcombinefn => 'xid8_larger', aggsortop => '>(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # min
 { aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -214,6 +217,9 @@
 { aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
   aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'min(xid8)', aggtransfn => 'xid8_smaller',
+  aggcombinefn => 'xid8_smaller', aggsortop => '<(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # count
 { aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7024dbe10a..62f36daa98 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -203,6 +203,12 @@
 { oid => '5071', descr => 'convert xid8 to xid',
   proname => 'xid', prorettype => 'xid', proargtypes => 'xid8',
   prosrc => 'xid8toxid' },
+{ oid => '5097', descr => 'larger of two',
+  proname => 'xid8_larger', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_larger' },
+{ oid => '5098', descr => 'smaller of two',
+  proname => 'xid8_smaller', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_smaller' },
 { oid => '69',
   proname => 'cideq', proleakproof => 't', prorettype => 'bool',
   proargtypes => 'cid cid', prosrc => 'cideq' },
@@ -6564,6 +6570,9 @@
 { oid => 

[PATCH] Add min() and max() aggregate functions for xid8

2022-02-02 Thread Ken Kato 

Hi hackers,

Unlike xid, xid8 increases monotonically and cannot be reused.
This trait makes it possible to support min() and max() aggregate 
functions for xid8.

I thought they would be useful for monitoring.

So I made a patch for this.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 8754f2f89b..1b064b4feb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19973,7 +19973,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
@@ -19992,7 +19992,7 @@ SELECT NULLIF(value, '(none)') ...
 values.  Available for any numeric, string, date/time, or enum type,
 as well as inet, interval,
 money, oid, pg_lsn,
-tid,
+tid, xid8,
 and arrays of any of these types.

Yes
diff --git a/src/backend/utils/adt/xid.c b/src/backend/utils/adt/xid.c
index 9b4ceaea47..6e24697782 100644
--- a/src/backend/utils/adt/xid.c
+++ b/src/backend/utils/adt/xid.c
@@ -286,6 +286,24 @@ xid8cmp(PG_FUNCTION_ARGS)
 		PG_RETURN_INT32(-1);
 }
 
+Datum
+xid8_larger(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	PG_RETURN_FULLTRANSACTIONID((U64FromFullTransactionId(fxid1) > U64FromFullTransactionId(fxid2)) ? fxid1 : fxid2);
+}
+
+Datum
+xid8_smaller(PG_FUNCTION_ARGS)
+{
+	FullTransactionId fxid1 = PG_GETARG_FULLTRANSACTIONID(0);
+	FullTransactionId fxid2 = PG_GETARG_FULLTRANSACTIONID(1);
+
+	PG_RETURN_FULLTRANSACTIONID((U64FromFullTransactionId(fxid1) < U64FromFullTransactionId(fxid2)) ? fxid1 : fxid2);
+}
+
 /*
  *	 COMMAND IDENTIFIER ROUTINES			 *
  */
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 137f6eef69..2843f4b415 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -149,6 +149,9 @@
 { aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
   aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'max(xid8)', aggtransfn => 'xid8_larger',
+  aggcombinefn => 'xid8_larger', aggsortop => '>(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # min
 { aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
@@ -214,6 +217,9 @@
 { aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
   aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
   aggtranstype => 'pg_lsn' },
+{ aggfnoid => 'min(xid8)', aggtransfn => 'xid8_smaller',
+  aggcombinefn => 'xid8_smaller', aggsortop => '<(xid8,xid8)',
+  aggtranstype => 'xid8' },
 
 # count
 { aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7024dbe10a..62f36daa98 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -203,6 +203,12 @@
 { oid => '5071', descr => 'convert xid8 to xid',
   proname => 'xid', prorettype => 'xid', proargtypes => 'xid8',
   prosrc => 'xid8toxid' },
+{ oid => '5097', descr => 'larger of two',
+  proname => 'xid8_larger', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_larger' },
+{ oid => '5098', descr => 'smaller of two',
+  proname => 'xid8_smaller', prorettype => 'xid8', proargtypes => 'xid8 xid8',
+  prosrc => 'xid8_smaller' },
 { oid => '69',
   proname => 'cideq', proleakproof => 't', prorettype => 'bool',
   proargtypes => 'cid cid', prosrc => 'cideq' },
@@ -6564,6 +6570,9 @@
 { oid => '4189', descr => 'maximum value of all pg_lsn input values',
   proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
   proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
+{ oid => '5099', descr => 

[PATCH] DROP tab completion

2021-11-29 Thread Ken Kato 

Hi hackers,

This time, I went through DROP tab completions
and noticed some tab completions missing for the following commands:
-DROP MATERIALIZED VIEW, DROP OWNED BY, DROP POLICY: missing 
[CASCADE|RESTRICT] at the end

-DROP TRANSFORM: no completions after TRANSFORM

I made a patch for this.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 630026da2f..2f412ca3db 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3322,12 +3322,16 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("VIEW");
 	else if (Matches("DROP", "MATERIALIZED", "VIEW"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+	else if (Matches("DROP", "MATERIALIZED", "VIEW", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/* DROP OWNED BY */
 	else if (Matches("DROP", "OWNED"))
 		COMPLETE_WITH("BY");
 	else if (Matches("DROP", "OWNED", "BY"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_roles);
+	else if (Matches("DROP", "OWNED", "BY", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/* DROP TEXT SEARCH */
 	else if (Matches("DROP", "TEXT", "SEARCH"))
@@ -3368,6 +3372,8 @@ psql_completion(const char *text, int start, int end)
 		completion_info_charp = prev2_wd;
 		COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_policy);
 	}
+	else if (Matches("DROP", "POLICY", MatchAny, "ON", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
 
 	/* DROP RULE */
 	else if (Matches("DROP", "RULE", MatchAny))
@@ -3380,6 +3386,21 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("DROP", "RULE", MatchAny, "ON", MatchAny))
 		COMPLETE_WITH("CASCADE", "RESTRICT");
 
+	/* DROP TRANSFORM */
+	else if (Matches("DROP", "TRANSFORM"))
+		COMPLETE_WITH("FOR");
+	else if (Matches("DROP", "TRANSFORM", "FOR"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+	else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny))
+		COMPLETE_WITH("LANGUAGE");
+	else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny, "LANGUAGE"))
+	{
+		completion_info_charp = prev2_wd;
+		COMPLETE_WITH_QUERY(Query_for_list_of_languages);
+	}
+	else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny, "LANGUAGE", MatchAny))
+		COMPLETE_WITH("CASCADE", "RESTRICT");
+
 /* EXECUTE */
 	else if (Matches("EXECUTE"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);

Re: [PATCH] ALTER tab completion

2021-11-26 Thread Ken Kato 

Hi,

Thank you for the comments!

I made following updates:


-ALTER DEFAULT PRIVILEGES: missing FOR USER


FOR ROLE is an equivalent.  That does not seem mandatory to me.

I deleted the completion for "FOR USER".


-ALTER VIEW: no completion after ALTER COLUMN column_name

+   /* ALTER VIEW xxx ALTER yyy */
+   else if (Matches("ALTER", "VIEW", MatchAny, "ALTER", MatchAny))
+   COMPLETE_WITH("SET DEFAULT", "DROP DEFAULT");
It may be cleaner to group this one with "ALTER VIEW xxx ALTER yyy"
two blocks above.

I put them back to back so that it looks cleaner.


-ALTER SEQUENCE: missing AS

+   /* ALTER SEQUENCE  AS */
+   else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS"))
+   COMPLETE_WITH("smallint", "integer", "bigint");
Re-quoting Horiguchi-san, that should be COMPLETE_WITH_CS() to keep
these completions in lower case.

That's what it's for.
I used COMPLETE_WITH_CS instead of COMPLETE_WITH.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index fa2e19593c..4cc32ac0ba 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1088,7 +1088,7 @@ static const pgsql_thing_t words_after_create[] = {
 	{"TEMPORARY", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},	/* for CREATE TEMPORARY
 		 * TABLE ... */
 	{"TEXT SEARCH", NULL, NULL, NULL},
-	{"TRANSFORM", NULL, NULL, NULL},
+	{"TRANSFORM", NULL, NULL, NULL, THING_NO_ALTER},
 	{"TRIGGER", "SELECT pg_catalog.quote_ident(tgname) FROM pg_catalog.pg_trigger WHERE substring(pg_catalog.quote_ident(tgname),1,%d)='%s' AND NOT tgisinternal"},
 	{"TYPE", NULL, NULL, &Query_for_list_of_datatypes},
 	{"UNIQUE", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},	/* for CREATE UNIQUE
@@ -1754,7 +1754,10 @@ psql_completion(const char *text, int start, int end)
 
 	/* ALTER FOREIGN DATA WRAPPER  */
 	else if (Matches("ALTER", "FOREIGN", "DATA", "WRAPPER", MatchAny))
-		COMPLETE_WITH("HANDLER", "VALIDATOR", "OPTIONS", "OWNER TO", "RENAME TO");
+		COMPLETE_WITH("HANDLER", "VALIDATOR", "NO",
+	  "OPTIONS", "OWNER TO", "RENAME TO");
+	else if (Matches("ALTER", "FOREIGN", "DATA", "WRAPPER", MatchAny, "NO"))
+		COMPLETE_WITH("HANDLER", "VALIDATOR");
 
 	/* ALTER FOREIGN TABLE  */
 	else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny))
@@ -1907,9 +1910,12 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("DEFAULT", "NOT NULL", "SCHEMA");
 	/* ALTER SEQUENCE  */
 	else if (Matches("ALTER", "SEQUENCE", MatchAny))
-		COMPLETE_WITH("INCREMENT", "MINVALUE", "MAXVALUE", "RESTART", "NO",
-	  "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY", "OWNER TO",
-	  "RENAME TO");
+		COMPLETE_WITH("AS", "INCREMENT", "MINVALUE", "MAXVALUE", "RESTART",
+	  "NO", "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY",
+	  "OWNER TO", "RENAME TO");
+	/* ALTER SEQUENCE  AS */
+	else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS"))
+		COMPLETE_WITH_CS("smallint", "integer", "bigint");
 	/* ALTER SEQUENCE  NO */
 	else if (Matches("ALTER", "SEQUENCE", MatchAny, "NO"))
 		COMPLETE_WITH("MINVALUE", "MAXVALUE", "CYCLE");
@@ -1935,6 +1941,12 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH_ATTR(prev2_wd, " UNION SELECT 'COLUMN' UNION SELECT 'TO'");
 	else if (Matches("ALTER", "VIEW", MatchAny, "ALTER|RENAME", "COLUMN"))
 		COMPLETE_WITH_ATTR(prev3_wd, "");
+	/* ALTER VIEW xxx ALTER yyy */
+	else if (Matches("ALTER", "VIEW", MatchAny, "ALTER", MatchAny))
+		COMPLETE_WITH("SET DEFAULT", "DROP DEFAULT");
+	/* ALTER VIEW xxx ALTER COLUMN yyy */
+	else if (Matches("ALTER", "VIEW", MatchAny, "ALTER", "COLUMN", MatchAny))
+		COMPLETE_WITH("SET DEFAULT", "DROP DEFAULT");
 	/* ALTER VIEW xxx RENAME yyy */
 	else if (Matches("ALTER", "VIEW", MatchAny, "RENAME", MatchAnyExcept("TO")))
 		COMPLETE_WITH("TO");

[PATCH] ALTER tab completion

2021-11-25 Thread Ken Kato 

Hi hackers,

I noticed that there are some tab completions missing for the following 
commands:

-ALTER DEFAULT PRIVILEGES: missing FOR USER
-ALTER FOREIGN DATA WRAPPER: missing NO HANDLER, NO VALIDATOR
-ALTER SEQUENCE: missing AS
-ALTER VIEW: no completion after ALTER COLUMN column_name
-ALTER TRANSFORM: no doc for ALTER TRANSFORM, so I excluded TRANSFORM 
from ALTER tab completion


I made a patch for this, so please have a look.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4f724e4428..60bbd18ade 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1088,7 +1088,7 @@ static const pgsql_thing_t words_after_create[] = {
 	{"TEMPORARY", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},	/* for CREATE TEMPORARY
 		 * TABLE ... */
 	{"TEXT SEARCH", NULL, NULL, NULL},
-	{"TRANSFORM", NULL, NULL, NULL},
+	{"TRANSFORM", NULL, NULL, NULL, THING_NO_ALTER},
 	{"TRIGGER", "SELECT pg_catalog.quote_ident(tgname) FROM pg_catalog.pg_trigger WHERE substring(pg_catalog.quote_ident(tgname),1,%d)='%s' AND NOT tgisinternal"},
 	{"TYPE", NULL, NULL, &Query_for_list_of_datatypes},
 	{"UNIQUE", NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},	/* for CREATE UNIQUE
@@ -1754,7 +1754,10 @@ psql_completion(const char *text, int start, int end)
 
 	/* ALTER FOREIGN DATA WRAPPER  */
 	else if (Matches("ALTER", "FOREIGN", "DATA", "WRAPPER", MatchAny))
-		COMPLETE_WITH("HANDLER", "VALIDATOR", "OPTIONS", "OWNER TO", "RENAME TO");
+		COMPLETE_WITH("HANDLER", "VALIDATOR", "NO",
+	  "OPTIONS", "OWNER TO", "RENAME TO");
+	else if (Matches("ALTER", "FOREIGN", "DATA", "WRAPPER", MatchAny, "NO"))
+		COMPLETE_WITH("HANDLER", "VALIDATOR");
 
 	/* ALTER FOREIGN TABLE  */
 	else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny))
@@ -1856,10 +1859,10 @@ psql_completion(const char *text, int start, int end)
 
 	/* ALTER DEFAULT PRIVILEGES */
 	else if (Matches("ALTER", "DEFAULT", "PRIVILEGES"))
-		COMPLETE_WITH("FOR ROLE", "IN SCHEMA");
+		COMPLETE_WITH("FOR ROLE", "FOR USER", "IN SCHEMA");
 	/* ALTER DEFAULT PRIVILEGES FOR */
 	else if (Matches("ALTER", "DEFAULT", "PRIVILEGES", "FOR"))
-		COMPLETE_WITH("ROLE");
+		COMPLETE_WITH("ROLE", "USER");
 	/* ALTER DEFAULT PRIVILEGES IN */
 	else if (Matches("ALTER", "DEFAULT", "PRIVILEGES", "IN"))
 		COMPLETE_WITH("SCHEMA");
@@ -1907,9 +1910,12 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("DEFAULT", "NOT NULL", "SCHEMA");
 	/* ALTER SEQUENCE  */
 	else if (Matches("ALTER", "SEQUENCE", MatchAny))
-		COMPLETE_WITH("INCREMENT", "MINVALUE", "MAXVALUE", "RESTART", "NO",
-	  "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY", "OWNER TO",
-	  "RENAME TO");
+		COMPLETE_WITH("AS", "INCREMENT", "MINVALUE", "MAXVALUE", "RESTART",
+	  "NO", "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY",
+	  "OWNER TO", "RENAME TO");
+	/* ALTER SEQUENCE  AS */
+	else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS"))
+		COMPLETE_WITH("smallint", "integer", "bigint");
 	/* ALTER SEQUENCE  NO */
 	else if (Matches("ALTER", "SEQUENCE", MatchAny, "NO"))
 		COMPLETE_WITH("MINVALUE", "MAXVALUE", "CYCLE");
@@ -1938,9 +1944,15 @@ psql_completion(const char *text, int start, int end)
 	/* ALTER VIEW xxx RENAME yyy */
 	else if (Matches("ALTER", "VIEW", MatchAny, "RENAME", MatchAnyExcept("TO")))
 		COMPLETE_WITH("TO");
+	/* ALTER VIEW xxx ALTER yyy */
+	else if (Matches("ALTER", "VIEW", MatchAny, "ALTER", MatchAny))
+		COMPLETE_WITH("SET DEFAULT", "DROP DEFAULT");
 	/* ALTER VIEW xxx RENAME COLUMN yyy */
 	else if (Matches("ALTER", "VIEW", MatchAny, "RENAME", "COLUMN", MatchAnyExcept("TO")))
 		COMPLETE_WITH("TO");
+	/* ALTER VIEw xxx ALTER COLUMN yyy */
+	else if (Matches("ALTER", "VIEW", MatchAny, "ALTER", "COLUMN", MatchAny))
+		COMPLETE_WITH("SET DEFAULT", "DROP DEFAULT");
 
 	/* ALTER MATERIALIZED VIEW  */
 	else if (Matches("ALTER", "MATERIALIZED", "VIEW", MatchAny))

Re: CREATE tab completion

2021-11-18 Thread Ken Kato 

I have fixed (or discarded) that, and the parts for sequences, domains
and transforms remained.  That looked like good enough on its own, so
applied those parts of the patch.
--
Michael

Thank you very much!

I assume Michael has committed the modified version of the patch.
Therefore, I changed the status to"committed" in Commitfest 2022-01.
https://commitfest.postgresql.org/36/3418/

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

Re: CREATE tab completion

2021-11-17 Thread Ken Kato 

+   else if (Matches("CREATE", "TRANSFORM", "FOR", MatchAny)
+   COMPLETE_WITH("LANGUAGE")
+   else if (Matches("CREATE", "TRANSFORM", "FOR", MatchAny, 
"LANGUAGE")


Those three lines are wrong, for two different reasons and three
mistakes.  You may want to compile your code before sending it :)

+   COMPLETE_WITH_QUERY(Query_for_list_of_schemas
+   " UNION SELECT
'AUTORIZATION'");
Incorrect completion here, s/AUTORIZATION/AUTHORIZATION/.


Thank you for the comments.

I am sorry for the compile error and a typo. I will make sure to compile 
before sending it and double check typos.




"CREATE [TEMP|TEMPORARY] SEQUENCE name AS" could be completed with the
supported types.  There are three of them.


For this part, I did the following:
+   else if (TailMatches("CREATE", "SEQUENCE", MatchAny, "AS") ||
+			 TailMatches("CREATE", "TEMP|TEMPORARY", "SEQUENCE", MatchAny, 
"AS"))

+   COMPLETE_WITH("smallint", "integer", "bigint");

Am I doing this right? or Are there better ways to do it?


Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4f724e4428..efa7bb1f5f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2572,6 +2572,26 @@ psql_completion(const char *text, int start, int end)
 			COMPLETE_WITH("true", "false");
 	}
 
+	/* CREATE CONVERSION */
+	else if (Matches("CREATE", "CONVERSION", MatchAny) ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny))
+		COMPLETE_WITH("FOR");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR") ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny, "FOR"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny) ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny, "FOR", MatchAny))
+		COMPLETE_WITH("TO");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO") ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny, "FOR", MatchAny, "TO"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny) ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny))
+		COMPLETE_WITH("FROM");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny, "FROM") ||
+			 Matches("CREATE", "DEFAULT", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny, "FROM"))
+		COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+
 	/* CREATE DATABASE */
 	else if (Matches("CREATE", "DATABASE", MatchAny))
 		COMPLETE_WITH("OWNER", "TEMPLATE", "ENCODING", "TABLESPACE",
@@ -2582,6 +2602,17 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("CREATE", "DATABASE", MatchAny, "TEMPLATE"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_template_databases);
 
+	/* CREATE DOMAIN */
+	else if (Matches("CREATE", "DOMAIN", MatchAny))
+		COMPLETE_WITH("AS");
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "AS"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
+		COMPLETE_WITH("COLLATE", "DEFAULT", "CONSTRAINT",
+	  "NOT NULL", "NULL", "CHECK (");
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations, NULL);
+
 	/* CREATE EXTENSION */
 	/* Complete with available extensions rather than installed ones. */
 	else if (Matches("CREATE", "EXTENSION"))
@@ -2661,6 +2692,14 @@ psql_completion(const char *text, int start, int end)
 			 !TailMatches("FOR", MatchAny, MatchAny, MatchAny))
 		COMPLETE_WITH("(");
 
+	/* CREATE LANGUAGE */
+	else if (Matches("C

CREATE tab completion

2021-11-16 Thread Ken Kato 

Hi hackers,

I noticed that there are some tab completions missing for the following 
commands:

-CREATE CONVERSION : missing FOR, TO, FROM
-CREATE DOMAIN : missing after AS
-CREATE LANGUAGE : missing after HANDLER
-CREATE SCHEMA : missing AUTHORIZATION, IF NOT EXISTS
-CREATE SEQUENCE : missing AS
-CREATE TRANSFORM : missing after FOR

I made a patch for this, so please have a look.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4f724e4428..0216b50946 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2572,6 +2572,20 @@ psql_completion(const char *text, int start, int end)
 			COMPLETE_WITH("true", "false");
 	}
 
+	/* CREATE CONVERSION */
+	else if (Matches("CREATE", "CONVERSION", MatchAny))
+		COMPLETE_WITH("FOR");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny))
+		COMPLETE_WITH("TO");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_encodings);
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny))
+		COMPLETE_WITH("FROM");
+	else if (Matches("CREATE", "CONVERSION", MatchAny, "FOR", MatchAny, "TO", MatchAny, "FROM"))
+		COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions, NULL);
+
 	/* CREATE DATABASE */
 	else if (Matches("CREATE", "DATABASE", MatchAny))
 		COMPLETE_WITH("OWNER", "TEMPLATE", "ENCODING", "TABLESPACE",
@@ -2582,6 +2596,17 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("CREATE", "DATABASE", MatchAny, "TEMPLATE"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_template_databases);
 
+	/* CREATE DOMAIN */
+	else if (Matches("CREATE", "DOMAIN", MatchAny))
+		COMPLETE_WITH("AS");
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "AS"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "AS", MatchAny))
+		COMPLETE_WITH("COLLATE", "DEFAULT", "CONSTRAINT",
+	  "NOT NULL", "NULL", "CHECK (");
+	else if (Matches("CREATE", "DOMAIN", MatchAny, "COLLATE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_collations, NULL);
+
 	/* CREATE EXTENSION */
 	/* Complete with available extensions rather than installed ones. */
 	else if (Matches("CREATE", "EXTENSION"))
@@ -2661,6 +2686,12 @@ psql_completion(const char *text, int start, int end)
 			 !TailMatches("FOR", MatchAny, MatchAny, MatchAny))
 		COMPLETE_WITH("(");
 
+	/* CREATE LANGUAGE */
+	else if (Matches("CREATE", "LANGUAGE", MatchAny))
+		COMPLETE_WITH("HANDLER");
+	else if (Matches("CREATE", "LANGUAGE", MatchAny, "HANDLER", MatchAny))
+		COMPLETE_WITH("INLINE", "VALIDATOR");
+
 	/* CREATE OR REPLACE */
 	else if (Matches("CREATE", "OR"))
 		COMPLETE_WITH("REPLACE");
@@ -2802,11 +2833,23 @@ psql_completion(const char *text, int start, int end)
 	else if (TailMatches("AS", "ON", "SELECT|UPDATE|INSERT|DELETE", "TO"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
 
+/* CREATE SCHEMA */
+	else if (Matches("CREATE", "SCHEMA"))
+		COMPLETE_WITH("AUTHORIZATION", "IF NOT EXISTS");
+	else if (Matches("CREATE", "SCHEMA", "IF", "NOT", "EXISTS"))
+	{
+		completion_info_charp = prev2_wd;
+		COMPLETE_WITH_QUERY(Query_for_list_of_schemas
+			" UNION SELECT 'AUTORIZATION'");
+	}
+	else if (Matches("CREATE", "SCHEMA") && TailMatches("AUTHORIZATION"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_roles);
+
 /* CREATE SEQUENCE --- is allowed inside CREATE SCHEMA, so use TailMatches */
 	else if (TailMatches("CREATE", "SEQUENCE", MatchAny) ||
 			 TailMatches("CREATE", "TEMP|TEMPORARY", "SEQUENCE", MatchAny))
-		COMPLETE_WITH("INCREMENT BY", "MINVALUE", "MAXVALUE", "NO", "CACHE",
-	  "CYCLE", "OWNED BY", "START WITH");
+		COMPLETE_WITH("AS&qu

[PATCH] pg_stat_statements Configuration Parameters Documentation

2021-11-07 Thread Ken Kato 

Hi,

Configuration parameters for pg_stat_statements were not in the index,
so I added them just like auto_explain configuration parameters.

Best wishes,

--
Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index 70b28a92c1..bc9d5bdbe3 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -634,6 +634,9 @@

 
  pg_stat_statements.max (integer)
+ 
+  pg_stat_statements.max configuration parameter
+ 
 
 
 
@@ -654,6 +657,9 @@

 
  pg_stat_statements.track (enum)
+ 
+  pg_stat_statements.track configuration parameter
+ 
 
 
 
@@ -673,6 +679,9 @@

 
  pg_stat_statements.track_utility (boolean)
+ 
+  pg_stat_statements.track_utility configuration parameter
+ 
 
 
 
@@ -690,6 +699,9 @@

 
  pg_stat_statements.track_planning (boolean)
+ 
+  pg_stat_statements.track_planning configuration parameter
+ 
 
 
 
@@ -709,6 +721,9 @@

 
  pg_stat_statements.save (boolean)
+ 
+  pg_stat_statements.save configuration parameter
+

Re: [PATCH] Added TRANSFORM FOR for COMMENT tab completion

2021-11-04 Thread Ken Kato 

Hi,

I found unnecessary line deletion in my previous patch, so I made a 
minor update for that.


--
Best wishes,

Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ecae9df8ed..b9af1df319 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2399,22 +2399,19 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COMMENT"))
 		COMPLETE_WITH("ON");
 	else if (Matches("COMMENT", "ON"))
-		COMPLETE_WITH("ACCESS METHOD", "CAST", "COLLATION", "CONVERSION",
-	  "DATABASE", "EVENT TRIGGER", "EXTENSION",
-	  "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "SERVER",
-	  "INDEX", "LANGUAGE", "POLICY", "PUBLICATION", "RULE",
-	  "SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION",
-	  "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW",
-	  "COLUMN", "AGGREGATE", "FUNCTION",
-	  "PROCEDURE", "ROUTINE",
-	  "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN",
-	  "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE");
+		COMPLETE_WITH("ACCESS METHOD", "AGGREGATE", "CAST", "COLLATION",
+	  "COLUMN","CONSTRAINT", "CONVERSION", "DATABASE",
+	  "DOMAIN","EXTENSION", "EVENT TRIGGER",
+	  "FOREIGN DATA WRAPPER","FOREIGN TABLE",
+	  "FUNCTION", "INDEX", "LANGUAGE","LARGE OBJECT",
+	  "MATERIALIZED VIEW", "OPERATOR","POLICY",
+	  "PROCEDURE", "PROCEDURAL LANGUAGE", "PUBLICATION","ROLE",
+	  "ROUTINE", "RULE", "SCHEMA", "SEQUENCE","SERVER",
+	  "STATISTICS", "SUBSCRIPTION", "TABLE",
+	  "TABLESPACE", "TEXT SEARCH", "TRANSFORM FOR",
+	  "TRIGGER", "TYPE", "VIEW");
 	else if (Matches("COMMENT", "ON", "ACCESS", "METHOD"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
-	else if (Matches("COMMENT", "ON", "FOREIGN"))
-		COMPLETE_WITH("DATA WRAPPER", "TABLE");
-	else if (Matches("COMMENT", "ON", "TEXT", "SEARCH"))
-		COMPLETE_WITH("CONFIGURATION", "DICTIONARY", "PARSER", "TEMPLATE");
 	else if (Matches("COMMENT", "ON", "CONSTRAINT"))
 		COMPLETE_WITH_QUERY(Query_for_all_table_constraints);
 	else if (Matches("COMMENT", "ON", "CONSTRAINT", MatchAny))
@@ -2422,15 +2419,67 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COMMENT", "ON", "CONSTRAINT", MatchAny, "ON"))
 	{
 		completion_info_charp = prev2_wd;
-		COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_constraint);
+		COMPLETE_WITH_QUERY(Query_for_list_of_tables_for_constraint
+			" UNION SELECT 'DOMAIN'");
 	}
-	else if (Matches("COMMENT", "ON", "MATERIALIZED", "VIEW"))
-		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+	else if (Matches("COMMENT", "ON", "CONSTRAINT", MatchAny, "ON", "DOMAIN"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_domains, NULL);
 	else if (Matches("COMMENT", "ON", "EVENT", "TRIGGER"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+	else if (Matches("COMMENT", "ON", "FOREIGN"))
+		COMPLETE_WITH("DATA WRAPPER", "TABLE");
+	else if (Matches("COMMENT", "ON", "FOREIGN", "TABLE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
+	else if (Matches("COMMENT", "ON", "MATERIALIZED", "VIEW"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews, NULL);
+	else if (Matches("COMMENT", "ON", "POLICY"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_policies);
+	else if (Matches("COMMENT", "ON", "POLICY", MatchAny))
+		COMPLETE_WITH("ON");
+	else if (Matches("COMMENT", "ON", "POLICY", MatchAny, "ON"))
+	{
+		completion

Re: [PATCH] Added TRANSFORM FOR for COMMENT tab completion

2021-11-04 Thread Ken Kato 

+   else if (Matches("COMMENT", "ON", "PROCEDURAL"))
+   COMPLETE_WITH("LANGUAGE");
+   else if (Matches("COMMENT", "ON", "PROCEDURAL", "LANGUAGE"))
+   COMPLETE_WITH_QUERY(Query_for_list_of_languages);
I don't think that there is much point in being this picky either 
with

the usage of PROCEDURAL, as we already complete a similar and simpler
grammar with LANGUAGE.  I would just remove this part of the patch.
In my opinion, it is written in the documentation, so tab-completion 
of "PROCEDURAL"is good.
How about a completion with "LANGUAGE" and "PROCEDURAL LANGUAGE", like 
"PASSWORD" and "ENCRYPTED PASSWORD" in CREATE ROLE?


I kept LANGUAGE and PROCEDURAL LANGUAGE just like PASSWORD and ENCRYPTED 
PASSWORD.




+   else if (Matches("COMMENT", "ON", "OPERATOR"))
+   COMPLETE_WITH("CLASS", "FAMILY");
Isn't this one wrong?  Operators can have comments, and we'd miss
them.  This is mentioned upthread, but it seems to me that we'd 
better

drop this part of the patch if the operator naming part cannot be
solved easily.

As you said, it may be misleading.
I agree to drop it.


Hearing all the opinions given, I decided not to support OPERATOR CLASS 
or FAMILY in COMMENT.
Therefore, I drooped Query_for_list_of_operator_class_index_methods as 
well.



+static const SchemaQuery Query_for_list_of_text_search_configurations 
= {


We already have Query_for_list_of_ts_configurations in tab-complete.c.
Do we really need both queries? Or we can drop either of them?


Thank you for pointing out!
I didn't notice that there already exists 
Query_for_list_of_ts_configurations,

so I changed TEXT SEARCH completion with using Query_for_list_of_ts_XXX.

I made the changes to the points above and updated the patch.

--
Best wishes,

Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ecae9df8ed..ee63a54470 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -494,7 +494,6 @@ static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.result = "pg_catalog.quote_ident(c.relname)",
 };
 
-
 /* All relations */
 static const SchemaQuery Query_for_list_of_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -2399,22 +2398,19 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COMMENT"))
 		COMPLETE_WITH("ON");
 	else if (Matches("COMMENT", "ON"))
-		COMPLETE_WITH("ACCESS METHOD", "CAST", "COLLATION", "CONVERSION",
-	  "DATABASE", "EVENT TRIGGER", "EXTENSION",
-	  "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "SERVER",
-	  "INDEX", "LANGUAGE", "POLICY", "PUBLICATION", "RULE",
-	  "SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION",
-	  "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW",
-	  "COLUMN", "AGGREGATE", "FUNCTION",
-	  "PROCEDURE", "ROUTINE",
-	  "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN",
-	  "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE");
+		COMPLETE_WITH("ACCESS METHOD", "AGGREGATE", "CAST", "COLLATION",
+	  "COLUMN","CONSTRAINT", "CONVERSION", "DATABASE",
+	  "DOMAIN","EXTENSION", "EVENT TRIGGER",
+	  "FOREIGN DATA WRAPPER","FOREIGN TABLE",
+	  "FUNCTION", "INDEX", "LANGUAGE","LARGE OBJECT",
+	  "MATERIALIZED VIEW", "OPERATOR","POLICY",
+	  "PROCEDURE", "PROCEDURAL LANGUAGE", "PUBLICATION","ROLE",
+	  "ROUTINE", "RULE", "SCHEMA", "SEQUENCE","SERVER",
+	  "STATISTICS", "SUBSCRIPTION", "TABLE",
+	  "TABLESPACE", "TEXT SEARCH", "TRANSFORM FOR",
+	  "TRIGGER", "TYPE", "VIEW");
 	else if (Matches("COMMENT", "ON", "ACCESS", "METHOD"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
-	else if (Matches("COMMENT", "ON", "FOREIGN"))
-		COMPLETE_WITH("DATA WRAPPER", "TABLE");
-	else if (Matches("COMMENT", "ON", "TEXT", "

Re: [PATCH] Added TRANSFORM FOR for COMMENT tab completion

2021-10-15 Thread Ken Kato 

2021-10-15 13:29 に Shinya Kato さんは書きました:

On 2021-10-14 14:30, katouknl wrote:
It is very good, but it seems to me that there are some 
tab-completion

missing in COMMENT command.
For example,
- CONSTRAINT ... ON DOMAIN
- OPERATOR CLASS
- OPERATOR FAMILY
- POLICY ... ON
- [PROCEDURAL]
- RULE ... ON
- TRIGGER ... ON

I think these tab-comletion also can be improved and it's a good
timing for that.


Thank you for the comments!

I fixed where you pointed out.

Thank you for the update!
I tried "COMMENT ON OPERATOR ...", and an operator seemed to be
complemented with double quotation marks.
However, it caused the COMMENT command to fail.
---
postgres=# COMMENT ON OPERATOR "+" (integer, integer) IS 'test_fail';
ERROR:  syntax error at or near "("
LINE 1: COMMENT ON OPERATOR "+" (integer, integer) IS 'test_fail';
postgres=# COMMENT ON OPERATOR + (integer, integer) IS 'test_success';
COMMENT
---

So, I think as with \do command, you do not need to complete the 
operators.

Do you think?

Thank you for the further comments!

I fixed so that it doesn't complete the operators anymore.
It only completes with CLASS and FAMILY.

Also, I updated TEXT SEARCH.
It completes object names for each one of CONFIGURATION, DICTIONARY, 
PARSER, and TEMPLATE.


--
Best wishes,

Ken Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONdiff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5cd5838668..62f494630b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -494,7 +494,6 @@ static const SchemaQuery Query_for_list_of_partitioned_indexes = {
 	.result = "pg_catalog.quote_ident(c.relname)",
 };
 
-
 /* All relations */
 static const SchemaQuery Query_for_list_of_relations = {
 	.catname = "pg_catalog.pg_class c",
@@ -513,11 +512,46 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
 	.result = "pg_catalog.quote_ident(c.relname)",
 };
 
+static const SchemaQuery Query_for_list_of_operator_classes = {
+	.catname = "pg_catalog.pg_opclass o",
+	.viscondition = "pg_catalog.pg_opclass_is_visible(o.oid)",
+	.namespace = "o.opcnamespace",
+	.result = "pg_catalog.quote_ident(o.opcname)",
+};
+
 static const SchemaQuery Query_for_list_of_operator_families = {
-	.catname = "pg_catalog.pg_opfamily c",
-	.viscondition = "pg_catalog.pg_opfamily_is_visible(c.oid)",
-	.namespace = "c.opfnamespace",
-	.result = "pg_catalog.quote_ident(c.opfname)",
+	.catname = "pg_catalog.pg_opfamily o",
+	.viscondition = "pg_catalog.pg_opfamily_is_visible(o.oid)",
+	.namespace = "o.opfnamespace",
+	.result = "pg_catalog.quote_ident(o.opfname)",
+};
+
+static const SchemaQuery Query_for_list_of_text_search_configurations = {
+	.catname = "pg_catalog.pg_ts_config t",
+	.viscondition = "pg_catalog.pg_ts_config_is_visible(t.oid)",
+	.namespace = "t.cfgnamespace",
+	.result = "pg_catalog.quote_ident(t.cfgname)",
+};
+
+static const SchemaQuery Query_for_list_of_text_search_dictionaries = {
+	.catname = "pg_catalog.pg_ts_dict t",
+	.viscondition = "pg_catalog.pg_ts_dict_is_visible(t.oid)",
+	.namespace = "t.dictnamespace",
+	.result = "pg_catalog.quote_ident(t.dictname)",
+};
+
+static const SchemaQuery Query_for_list_of_text_search_parsers = {
+	.catname = "pg_catalog.pg_ts_parser t",
+	.viscondition = "pg_catalog.pg_ts_parser_is_visible(t.oid)",
+	.namespace = "t.prsnamespace",
+	.result = "pg_catalog.quote_ident(t.prsname)",
+};
+
+static const SchemaQuery Query_for_list_of_text_search_templates = {
+	.catname = "pg_catalog.pg_ts_template t",
+	.viscondition = "pg_catalog.pg_ts_template_is_visible(t.oid)",
+	.namespace = "t.tmplnamespace",
+	.result = "pg_catalog.quote_ident(t.tmplname)",
 };
 
 /* Relations supporting INSERT, UPDATE or DELETE */
@@ -628,7 +662,6 @@ static const SchemaQuery Query_for_list_of_collations = {
 	.result = "pg_catalog.quote_ident(c.collname)",
 };
 
-
 /*
  * Queries to get lists of names of various kinds of things, possibly
  * restricted to names matching a partially entered name.  In these queries,
@@ -767,6 +800,22 @@ static const SchemaQuery Query_for_list_of_collations = {
 " UNION ALL SELECT 'CURRENT_USER'"\
 " UNION ALL SELECT 'SESSION_USER'"
 
+#define Query_for_list_of_operator_class_index_methods \
+"SELECT pg_catalog.quote_ident(amname)"\
+"  FROM pg_catalog.pg_am"\
+" WHERE (%d = pg_catalog.length('%s'))"\
+"   AND  oid IN "\
+"   (SELECT opcmethod FROM pg_catalog.pg_opclass "\
+" WHERE pg_catalog.qu