pgpool2.git / summary / log / commit / refs
commit c47ac1679e37b82da0690feb0c17d7cb645e788a
Author: Tatsuo Ishii <ishii@postgresql.org>
Date: Wed Jul 04 22:57:28 2018 +0000
Add role, replication_delay and last_status_change columns to pgpool_adm's pcp_node_info.
Now the function is sync with show pool_nodes SQL and pcp_node_info
command. Due to parameter change, I also add upgrade scripts so that
users can migrate from 1.0 to 1.1 using ALTER extension pgpool_adm.
doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml | 31 ++++++++-
doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml | 25 ++++++-
src/sql/pgpool_adm/Makefile | 27 +++++++
src/sql/pgpool_adm/pgpool_adm--1.0--1.1.sql | 12 ++++
src/sql/pgpool_adm/pgpool_adm--1.1.sql | 85 +++++++++++++++++++++++
src/sql/pgpool_adm/pgpool_adm.c | 28 ++++++--
src/sql/pgpool_adm/pgpool_adm.control | 2 +-
src/sql/pgpool_adm/pgpool_adm.h | 2 +-
src/sql/pgpool_adm/pgpool_adm.sql.in | 2 +-
9 files changed, 201 insertions(+), 13 deletions(-)
diff --git a/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml b/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
index 1873c991c..5867c8c87 100644
--- a/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
+++ b/doc.ja/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
@@ -36,6 +36,9 @@ Pgpool-II documentation
<paramdef>text <parameter>password</parameter></paramdef>
<paramdef>out <parameter>status text</parameter></paramdef>
<paramdef>out <parameter>weight float4</parameter></paramdef>
+ <paramdef>out <parameter>role text</parameter></paramdef>
+ <paramdef>out <parameter>replication_delay bigint</parameter></paramdef>
+ <paramdef>out <parameter>last_status_change timestamp</parameter></paramdef>
</funcprototype>
<funcprototype>
@@ -44,6 +47,9 @@ Pgpool-II documentation
<paramdef>text <parameter>pcp_server</parameter></paramdef>
<paramdef>out <parameter>status text</parameter></paramdef>
<paramdef>out <parameter>weight float4</parameter></paramdef>
+ <paramdef>out <parameter>role text</parameter></paramdef>
+ <paramdef>out <parameter>replication_delay bigint</parameter></paramdef>
+ <paramdef>out <parameter>last_status_change timestamp</parameter></paramdef>
</funcprototype>
</funcsynopsis>
@@ -125,13 +131,32 @@ pcpサーバの外部サーバ名
使用例です。
<programlisting>
test=# SELECT * FROM pcp_node_info(0,'',11001,'t-ishii','t-ishii');
- host | port | status | weight
-------+-------+-------------------+--------
- /tmp | 11002 | Connection in use | 0
+ host | port | status | weight | role | replication_delay | last_status_change
+------+-------+-------------------+--------+---------+-------------------+---------------------
+ /tmp | 11002 | Connection in use | 0 | Primary | 0 | 2018-07-04 17:34:39
(1 row)
</programlisting>
</para>
+ <note>
+ <para>
+<!--
+ <parameter>role</parameter>, <parameter>replication_delay</parameter>, <parameter>last_status_change</parameter>
+ out parameters are new
+ from <productname>Pgpool-II</productname> 4.0. If you have
+ already installed pre-4.0 pgpool_adm extension, you can upgrade
+ to the new one by using ALTER EXTENSION SQL command.
+ <programlisting>
+ ALTER EXTENSION pgpool_adm UPDATE;
+ </programlisting>
+-->
+<parameter>role</parameter>, <parameter>replication_delay</parameter>, <parameter>last_status_change</parameter>出力パラメータは<productname>Pgpool-II</productname> 4.0で追加されました。
+すでに4.0以前のpgpool_adm拡張をインストールされている場合は、ALTER EXTENSION SQLコマンドを使って更新が可能です。
+ <programlisting>
+ ALTER EXTENSION pgpool_adm UPDATE;
+ </programlisting>
+ </para>
+ </note>
</refsect1>
</refentry>
diff --git a/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml b/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
index 4b267339c..2939b3ed7 100644
--- a/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
+++ b/doc/src/sgml/ref/pgpool_adm_pcp_node_info.sgml
@@ -32,6 +32,9 @@ Pgpool-II documentation
<paramdef>text <parameter>password</parameter></paramdef>
<paramdef>out <parameter>status text</parameter></paramdef>
<paramdef>out <parameter>weight float4</parameter></paramdef>
+ <paramdef>out <parameter>role text</parameter></paramdef>
+ <paramdef>out <parameter>replication_delay bigint</parameter></paramdef>
+ <paramdef>out <parameter>last_status_change timestamp</parameter></paramdef>
</funcprototype>
<funcprototype>
@@ -40,6 +43,9 @@ Pgpool-II documentation
<paramdef>text <parameter>pcp_server</parameter></paramdef>
<paramdef>out <parameter>status text</parameter></paramdef>
<paramdef>out <parameter>weight float4</parameter></paramdef>
+ <paramdef>out <parameter>role text</parameter></paramdef>
+ <paramdef>out <parameter>replication_delay bigint</parameter></paramdef>
+ <paramdef>out <parameter>last_status_change timestamp</parameter></paramdef>
</funcprototype>
</funcsynopsis>
@@ -94,13 +100,26 @@ Pgpool-II documentation
Here is an example output:
<programlisting>
test=# SELECT * FROM pcp_node_info(0,'',11001,'t-ishii','t-ishii');
- host | port | status | weight
-------+-------+-------------------+--------
- /tmp | 11002 | Connection in use | 0
+ host | port | status | weight | role | replication_delay | last_status_change
+------+-------+-------------------+--------+---------+-------------------+---------------------
+ /tmp | 11002 | Connection in use | 0 | Primary | 0 | 2018-07-04 17:34:39
(1 row)
</programlisting>
</para>
+ <note>
+ <para>
+ <parameter>role</parameter>, <parameter>replication_delay</parameter>, <parameter>last_status_change</parameter>
+ out parameters are new
+ from <productname>Pgpool-II</productname> 4.0. If you have
+ already installed pre-4.0 pgpool_adm extension, you can upgrade
+ to the new one by using ALTER EXTENSION SQL command.
+ <programlisting>
+ ALTER EXTENSION pgpool_adm UPDATE;
+ </programlisting>
+ </para>
+ </note>
+
</refsect1>
</refentry>
diff --git a/src/sql/pgpool_adm/Makefile b/src/sql/pgpool_adm/Makefile
new file mode 100644
index 000000000..5037ac7be
--- /dev/null
+++ b/src/sql/pgpool_adm/Makefile
@@ -0,0 +1,27 @@
+# contrib/pgpool_adm/Makefile
+
+MODULE_big = pgpool_adm
+OBJS = pgpool_adm.o
+PG_CPPFLAGS = -I$(libpq_srcdir) -I../../include/pcp
+
+EXTENSION = pgpool_adm
+DATA = pgpool_adm--1.0.sql pgpool_adm--1.1.sql pgpool_adm--1.0--1.1.sql
+SHLIB_LINK = -L../../libs/pcp/.libs -lpcp -Wl,--as-needed -Wl,-rpath,'${prefix}/lib',--enable-new-dtags
+# if you are using PostgreSQL 8.0 or later,
+# using pg_config is recommended.
+# if you are not, comment out following line and...
+USE_PGXS = true
+# set top_builddir to the PostgreSQL build source tree top.
+# (for example /usr/local/src/postgresql-8.4)
+top_builddir = ../..
+
+ifdef USE_PGXS
+PG_CONFIG ?= pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pgpool_adm
+#top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/sql/pgpool_adm/pgpool_adm--1.0--1.1.sql b/src/sql/pgpool_adm/pgpool_adm--1.0--1.1.sql
new file mode 100644
index 000000000..817f9e42b
--- /dev/null
+++ b/src/sql/pgpool_adm/pgpool_adm--1.0--1.1.sql
@@ -0,0 +1,12 @@
+/* contrib/pgpool_adm/pgpool_adm--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgpool_adm UPDATE TO '1.1'" to load this file. \quit
+
+ALTER EXTENSION pgpool_adm DROP FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4);
+DROP FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4);
+
+CREATE FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT last_status_change timestamp)
+RETURNS record
+AS 'MODULE_PATHNAME', '_pcp_node_info'
+LANGUAGE C VOLATILE STRICT;
diff --git a/src/sql/pgpool_adm/pgpool_adm--1.1.sql b/src/sql/pgpool_adm/pgpool_adm--1.1.sql
new file mode 100644
index 000000000..eb2166f28
--- /dev/null
+++ b/src/sql/pgpool_adm/pgpool_adm--1.1.sql
@@ -0,0 +1,85 @@
+/* contrib/pgpool_adm/pgpool_adm--1.1.sql */
+
+/* ***********************************************
+ * Administrative functions for pgPool
+ * *********************************************** */
+
+/**
+ * input parameters: node_id, host, port, username, password
+ */
+CREATE FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT last_status_change timestamp)
+RETURNS record
+AS 'MODULE_PATHNAME', '_pcp_node_info'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: node_id, server_name
+ */
+CREATE FUNCTION pcp_node_info(integer, text, OUT host text, OUT port integer, OUT status text, OUT weight float4)
+RETURNS record
+AS 'MODULE_PATHNAME', '_pcp_node_info'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: host, port, username, password
+ */
+CREATE FUNCTION pcp_pool_status(text, integer, text, text, OUT item text, OUT value text, OUT description text)
+RETURNS record
+AS 'MODULE_PATHNAME', '_pcp_pool_status'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: server_name
+ */
+CREATE FUNCTION pcp_pool_status(text, OUT item text, OUT value text, OUT description text)
+RETURNS record
+AS 'MODULE_PATHNAME', '_pcp_pool_status'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: host, port, username, password
+ */
+CREATE FUNCTION pcp_node_count(text, integer, text, text, OUT node_count integer)
+RETURNS integer
+AS 'MODULE_PATHNAME', '_pcp_node_count'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: server_name
+ */
+CREATE FUNCTION pcp_node_count(text, OUT node_count integer)
+RETURNS integer
+AS 'MODULE_PATHNAME', '_pcp_node_count'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: node_id, host, port, username, password
+ */
+CREATE FUNCTION pcp_attach_node(integer, text, integer, text, text, OUT node_attached boolean)
+RETURNS boolean
+AS 'MODULE_PATHNAME', '_pcp_attach_node'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: node_id, server_name
+ */
+CREATE FUNCTION pcp_attach_node(integer, text, OUT node_attached boolean)
+RETURNS boolean
+AS 'MODULE_PATHNAME', '_pcp_attach_node'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: node_id, gracefully, host, port, username, password
+ */
+CREATE FUNCTION pcp_detach_node(integer, boolean, text, integer, text, text, OUT node_detached boolean)
+RETURNS boolean
+AS 'MODULE_PATHNAME', '_pcp_detach_node'
+LANGUAGE C VOLATILE STRICT;
+
+/**
+ * input parameters: node_id, gracefully, server_name
+ */
+CREATE FUNCTION pcp_detach_node(integer, boolean, text, OUT node_detached boolean)
+RETURNS boolean
+AS 'MODULE_PATHNAME', '_pcp_detach_node'
+LANGUAGE C VOLATILE STRICT;
diff --git a/src/sql/pgpool_adm/pgpool_adm.c b/src/sql/pgpool_adm/pgpool_adm.c
index 8f7dc6ab0..f27aa170d 100644
--- a/src/sql/pgpool_adm/pgpool_adm.c
+++ b/src/sql/pgpool_adm/pgpool_adm.c
@@ -3,7 +3,7 @@
* pgpool_adm.c
*
*
- * Copyright (c) 2002-2015, PostgreSQL Global Development Group
+ * Copyright (c) 2002-2018, PostgreSQL Global Development Group
*
* Author: Jehan-Guillaume (ioguix) de Rorthais <jgdr@dalibo.com>
*
@@ -17,6 +17,7 @@
#include "utils/builtins.h"
#include "foreign/foreign.h"
#include "nodes/pg_list.h"
+#include "utils/timestamp.h"
/*
* PostgreSQL 9.3 or later requires htup_details.h to get the definition of
@@ -123,10 +124,12 @@ _pcp_node_info(PG_FUNCTION_ARGS)
PCPResultInfo* pcpResInfo;
BackendInfo * backend_info = NULL;
- Datum values[4]; /* values to build the returned tuple from */
- bool nulls[] = {false, false, false, false};
+ Datum values[7]; /* values to build the returned tuple from */
+ bool nulls[] = {false, false, false, false, false, false, false};
TupleDesc tupledesc;
HeapTuple tuple;
+ struct tm tm;
+ char datebuf[20];
if (nodeID < 0 || nodeID >= MAX_NUM_BACKENDS)
ereport(ERROR, (0, errmsg("NodeID is out of range.")));
@@ -163,11 +166,14 @@ _pcp_node_info(PG_FUNCTION_ARGS)
/**
* Construct a tuple descriptor for the result rows.
**/
- tupledesc = CreateTemplateTupleDesc(4, false);
+ tupledesc = CreateTemplateTupleDesc(7, false);
TupleDescInitEntry(tupledesc, (AttrNumber) 1, "hostname", TEXTOID, -1, 0);
TupleDescInitEntry(tupledesc, (AttrNumber) 2, "port", INT4OID, -1, 0);
TupleDescInitEntry(tupledesc, (AttrNumber) 3, "status", TEXTOID, -1, 0);
TupleDescInitEntry(tupledesc, (AttrNumber) 4, "weight", FLOAT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 5, "role", TEXTOID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 6, "replication_delay", INT8OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 7, "last_status_change", TIMESTAMPOID, -1, 0);
tupledesc = BlessTupleDesc(tupledesc);
backend_info = (BackendInfo *) pcp_get_binary_data(pcpResInfo,0);
@@ -196,6 +202,20 @@ _pcp_node_info(PG_FUNCTION_ARGS)
values[3] = Float8GetDatum(backend_info->backend_weight/RAND_MAX);
nulls[3] = false;
+ nulls[4] = false;
+ values[4] = backend_info->role == ROLE_PRIMARY?CStringGetTextDatum("Primary"):CStringGetTextDatum("Standby");
+
+ nulls[5] = false;
+ values[5] = Int64GetDatum(backend_info->standby_delay);
+
+ nulls[6] = false;
+ localtime_r(&backend_info->status_changed_time, &tm);
+ strftime(datebuf, sizeof(datebuf), "%F %T", &tm);
+ values[6] = DatumGetTimestamp(DirectFunctionCall3(timestamp_in,
+ CStringGetDatum(datebuf),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1)));
+
pcp_disconnect(pcpConnInfo);
pcp_free_connection(pcpConnInfo);
diff --git a/src/sql/pgpool_adm/pgpool_adm.control b/src/sql/pgpool_adm/pgpool_adm.control
index 108b0c05b..48ab73cf9 100644
--- a/src/sql/pgpool_adm/pgpool_adm.control
+++ b/src/sql/pgpool_adm/pgpool_adm.control
@@ -1,5 +1,5 @@
# pcp extension
comment = 'Administrative functions for pgPool'
-default_version = '1.0'
+default_version = '1.1'
module_pathname = '$libdir/pgpool_adm'
relocatable = true
diff --git a/src/sql/pgpool_adm/pgpool_adm.h b/src/sql/pgpool_adm/pgpool_adm.h
index b77d546e7..efc1e0cb3 100644
--- a/src/sql/pgpool_adm/pgpool_adm.h
+++ b/src/sql/pgpool_adm/pgpool_adm.h
@@ -3,7 +3,7 @@
* pgpool_adm.h
*
*
- * Copyright (c) 2002-2011, PostgreSQL Global Development Group
+ * Copyright (c) 2002-2018, PostgreSQL Global Development Group
*
* Author: Jehan-Guillaume (ioguix) de Rorthais <jgdr@dalibo.com>
*
diff --git a/src/sql/pgpool_adm/pgpool_adm.sql.in b/src/sql/pgpool_adm/pgpool_adm.sql.in
index 98366669f..2a8f8612e 100644
--- a/src/sql/pgpool_adm/pgpool_adm.sql.in
+++ b/src/sql/pgpool_adm/pgpool_adm.sql.in
@@ -7,7 +7,7 @@
/**
* input parameters: node_id, host, port, username, password
*/
-CREATE FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4)
+CREATE FUNCTION pcp_node_info(integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight float4, OUT role text, OUT replication_delay bigint, OUT last_status_change TIMESTAMP)
RETURNS record
AS 'MODULE_PATHNAME', '_pcp_node_info'
LANGUAGE C VOLATILE STRICT;
[parent: ec7c84ee88a7]