From 1dc118660b8f12d3bdec94b6f6e1068966ca62e9 Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Fri, 19 Jul 2013 03:50:20 +0900 Subject: Fix pgstattuple functions to use regclass-type as the argument. This allows us to specify the target relation with several expressions, 'relname', 'schemaname.relname' and OID in all pgstattuple functions. pgstatindex() and pg_relpages() could not accept OID as the argument so far. Per discussion on -hackers, we decided to keep two types of interfaces, with regclass-type and TEXT-type argument, for each pgstattuple function because of the backward-compatibility issue. The functions which have TEXT-type argument will be deprecated in the future release. Patch by Satoshi Nagayasu, reviewed by Rushabh Lathia and Fujii Masao. --- contrib/pgstattuple/Makefile | 2 +- contrib/pgstattuple/expected/pgstattuple.out | 84 +++++++++++++++++++++++++++ contrib/pgstattuple/pgstatindex.c | 72 +++++++++++++++++++++-- contrib/pgstattuple/pgstattuple--1.1--1.2.sql | 39 +++++++++++++ contrib/pgstattuple/pgstattuple--1.1.sql | 58 ------------------ contrib/pgstattuple/pgstattuple--1.2.sql | 79 +++++++++++++++++++++++++ contrib/pgstattuple/pgstattuple.control | 2 +- contrib/pgstattuple/sql/pgstattuple.sql | 14 +++++ 8 files changed, 284 insertions(+), 66 deletions(-) create mode 100644 contrib/pgstattuple/pgstattuple--1.1--1.2.sql delete mode 100644 contrib/pgstattuple/pgstattuple--1.1.sql create mode 100644 contrib/pgstattuple/pgstattuple--1.2.sql (limited to 'contrib') diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile index fc893d8c85..d991c3a803 100644 --- a/contrib/pgstattuple/Makefile +++ b/contrib/pgstattuple/Makefile @@ -4,7 +4,7 @@ MODULE_big = pgstattuple OBJS = pgstattuple.o pgstatindex.o EXTENSION = pgstattuple -DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql +DATA = pgstattuple--1.2.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql REGRESS = pgstattuple diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index ab28f50cb0..7aab967665 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -5,24 +5,78 @@ CREATE EXTENSION pgstattuple; -- indexes should be that. -- create table test (a int primary key, b int[]); +select * from pgstattuple('test'); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + select * from pgstattuple('test'::text); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (1 row) +select * from pgstattuple('test'::name); + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 +(1 row) + select * from pgstattuple('test'::regclass); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (1 row) +select pgstattuple(oid) from pg_class where relname = 'test'; + pgstattuple +--------------------- + (0,0,0,0,0,0,0,0,0) +(1 row) + +select pgstattuple(relname) from pg_class where relname = 'test'; + pgstattuple +--------------------- + (0,0,0,0,0,0,0,0,0) +(1 row) + select * from pgstatindex('test_pkey'); version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN (1 row) +select * from pgstatindex('test_pkey'::text); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +select * from pgstatindex('test_pkey'::name); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +select * from pgstatindex('test_pkey'::regclass); + version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation +---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- + 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN +(1 row) + +select pgstatindex(oid) from pg_class where relname = 'test_pkey'; + pgstatindex +--------------------------- + (2,0,0,0,0,0,0,0,NaN,NaN) +(1 row) + +select pgstatindex(relname) from pg_class where relname = 'test_pkey'; + pgstatindex +--------------------------- + (2,0,0,0,0,0,0,0,NaN,NaN) +(1 row) + select pg_relpages('test'); pg_relpages ------------- @@ -35,6 +89,36 @@ select pg_relpages('test_pkey'); 1 (1 row) +select pg_relpages('test_pkey'::text); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages('test_pkey'::name); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages('test_pkey'::regclass); + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages(oid) from pg_class where relname = 'test_pkey'; + pg_relpages +------------- + 1 +(1 row) + +select pg_relpages(relname) from pg_class where relname = 'test_pkey'; + pg_relpages +------------- + 1 +(1 row) + create index test_ginidx on test using gin (b); select * from pgstatginindex('test_ginidx'); version | pending_pages | pending_tuples diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c index 97f897ec1e..282d82ca82 100644 --- a/contrib/pgstattuple/pgstatindex.c +++ b/contrib/pgstattuple/pgstatindex.c @@ -39,12 +39,24 @@ #include "utils/rel.h" +/* + * Because of backward-compatibility issue, we have decided to have + * two types of interfaces, with regclass-type input arg and text-type + * input arg, for each function. + * + * Those functions which have text-type input arg will be deprecated + * in the future release. + */ extern Datum pgstatindex(PG_FUNCTION_ARGS); +extern Datum pgstatindexbyid(PG_FUNCTION_ARGS); extern Datum pg_relpages(PG_FUNCTION_ARGS); +extern Datum pg_relpagesbyid(PG_FUNCTION_ARGS); extern Datum pgstatginindex(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(pgstatindex); +PG_FUNCTION_INFO_V1(pgstatindexbyid); PG_FUNCTION_INFO_V1(pg_relpages); +PG_FUNCTION_INFO_V1(pg_relpagesbyid); PG_FUNCTION_INFO_V1(pgstatginindex); #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX) @@ -97,6 +109,8 @@ typedef struct GinIndexStat int64 pending_tuples; } GinIndexStat; +static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo); + /* ------------------------------------------------------ * pgstatindex() * @@ -109,11 +123,6 @@ pgstatindex(PG_FUNCTION_ARGS) text *relname = PG_GETARG_TEXT_P(0); Relation rel; RangeVar *relrv; - Datum result; - BlockNumber nblocks; - BlockNumber blkno; - BTIndexStat indexStat; - BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD); if (!superuser()) ereport(ERROR, @@ -123,6 +132,34 @@ pgstatindex(PG_FUNCTION_ARGS) relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); rel = relation_openrv(relrv, AccessShareLock); + PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo)); +} + +Datum +pgstatindexbyid(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + Relation rel; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("must be superuser to use pgstattuple functions")))); + + rel = relation_open(relid, AccessShareLock); + + PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo)); +} + +static Datum +pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo) +{ + Datum result; + BlockNumber nblocks; + BlockNumber blkno; + BTIndexStat indexStat; + BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD); + if (!IS_INDEX(rel) || !IS_BTREE(rel)) elog(ERROR, "relation \"%s\" is not a btree index", RelationGetRelationName(rel)); @@ -274,7 +311,7 @@ pgstatindex(PG_FUNCTION_ARGS) result = HeapTupleGetDatum(tuple); } - PG_RETURN_DATUM(result); + return result; } /* -------------------------------------------------------- @@ -311,6 +348,29 @@ pg_relpages(PG_FUNCTION_ARGS) PG_RETURN_INT64(relpages); } +Datum +pg_relpagesbyid(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 relpages; + Relation rel; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("must be superuser to use pgstattuple functions")))); + + rel = relation_open(relid, AccessShareLock); + + /* note: this will work OK on non-local temp tables */ + + relpages = RelationGetNumberOfBlocks(rel); + + relation_close(rel, AccessShareLock); + + PG_RETURN_INT64(relpages); +} + /* ------------------------------------------------------ * pgstatginindex() * diff --git a/contrib/pgstattuple/pgstattuple--1.1--1.2.sql b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql new file mode 100644 index 0000000000..2783a63fb1 --- /dev/null +++ b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql @@ -0,0 +1,39 @@ +/* contrib/pgstattuple/pgstattuple--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit + +ALTER EXTENSION pgstattuple DROP FUNCTION pgstattuple(oid); +DROP FUNCTION pgstattuple(oid); + +CREATE FUNCTION pgstattuple(IN reloid regclass, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8) -- free space in % +AS 'MODULE_PATHNAME', 'pgstattuplebyid' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstatindex(IN relname regclass, + OUT version INT, + OUT tree_level INT, + OUT index_size BIGINT, + OUT root_block_no BIGINT, + OUT internal_pages BIGINT, + OUT leaf_pages BIGINT, + OUT empty_pages BIGINT, + OUT deleted_pages BIGINT, + OUT avg_leaf_density FLOAT8, + OUT leaf_fragmentation FLOAT8) +AS 'MODULE_PATHNAME', 'pgstatindexbyid' +LANGUAGE C STRICT; + +CREATE FUNCTION pg_relpages(IN relname regclass) +RETURNS BIGINT +AS 'MODULE_PATHNAME', 'pg_relpagesbyid' +LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.1.sql b/contrib/pgstattuple/pgstattuple--1.1.sql deleted file mode 100644 index b21fbf83e4..0000000000 --- a/contrib/pgstattuple/pgstattuple--1.1.sql +++ /dev/null @@ -1,58 +0,0 @@ -/* contrib/pgstattuple/pgstattuple--1.1.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit - -CREATE FUNCTION pgstattuple(IN relname text, - OUT table_len BIGINT, -- physical table length in bytes - OUT tuple_count BIGINT, -- number of live tuples - OUT tuple_len BIGINT, -- total tuples length in bytes - OUT tuple_percent FLOAT8, -- live tuples in % - OUT dead_tuple_count BIGINT, -- number of dead tuples - OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes - OUT dead_tuple_percent FLOAT8, -- dead tuples in % - OUT free_space BIGINT, -- free space in bytes - OUT free_percent FLOAT8) -- free space in % -AS 'MODULE_PATHNAME', 'pgstattuple' -LANGUAGE C STRICT; - -CREATE FUNCTION pgstattuple(IN reloid oid, - OUT table_len BIGINT, -- physical table length in bytes - OUT tuple_count BIGINT, -- number of live tuples - OUT tuple_len BIGINT, -- total tuples length in bytes - OUT tuple_percent FLOAT8, -- live tuples in % - OUT dead_tuple_count BIGINT, -- number of dead tuples - OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes - OUT dead_tuple_percent FLOAT8, -- dead tuples in % - OUT free_space BIGINT, -- free space in bytes - OUT free_percent FLOAT8) -- free space in % -AS 'MODULE_PATHNAME', 'pgstattuplebyid' -LANGUAGE C STRICT; - -CREATE FUNCTION pgstatindex(IN relname text, - OUT version INT, - OUT tree_level INT, - OUT index_size BIGINT, - OUT root_block_no BIGINT, - OUT internal_pages BIGINT, - OUT leaf_pages BIGINT, - OUT empty_pages BIGINT, - OUT deleted_pages BIGINT, - OUT avg_leaf_density FLOAT8, - OUT leaf_fragmentation FLOAT8) -AS 'MODULE_PATHNAME', 'pgstatindex' -LANGUAGE C STRICT; - -CREATE FUNCTION pg_relpages(IN relname text) -RETURNS BIGINT -AS 'MODULE_PATHNAME', 'pg_relpages' -LANGUAGE C STRICT; - -/* New stuff in 1.1 begins here */ - -CREATE FUNCTION pgstatginindex(IN relname regclass, - OUT version INT4, - OUT pending_pages INT4, - OUT pending_tuples BIGINT) -AS 'MODULE_PATHNAME', 'pgstatginindex' -LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.2.sql b/contrib/pgstattuple/pgstattuple--1.2.sql new file mode 100644 index 0000000000..e5fa2f58da --- /dev/null +++ b/contrib/pgstattuple/pgstattuple--1.2.sql @@ -0,0 +1,79 @@ +/* contrib/pgstattuple/pgstattuple--1.2.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit + +CREATE FUNCTION pgstattuple(IN relname text, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8) -- free space in % +AS 'MODULE_PATHNAME', 'pgstattuple' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstatindex(IN relname text, + OUT version INT, + OUT tree_level INT, + OUT index_size BIGINT, + OUT root_block_no BIGINT, + OUT internal_pages BIGINT, + OUT leaf_pages BIGINT, + OUT empty_pages BIGINT, + OUT deleted_pages BIGINT, + OUT avg_leaf_density FLOAT8, + OUT leaf_fragmentation FLOAT8) +AS 'MODULE_PATHNAME', 'pgstatindex' +LANGUAGE C STRICT; + +CREATE FUNCTION pg_relpages(IN relname text) +RETURNS BIGINT +AS 'MODULE_PATHNAME', 'pg_relpages' +LANGUAGE C STRICT; + +/* New stuff in 1.1 begins here */ + +CREATE FUNCTION pgstatginindex(IN relname regclass, + OUT version INT4, + OUT pending_pages INT4, + OUT pending_tuples BIGINT) +AS 'MODULE_PATHNAME', 'pgstatginindex' +LANGUAGE C STRICT; + +/* New stuff in 1.2 begins here */ + +CREATE FUNCTION pgstattuple(IN reloid regclass, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8) -- free space in % +AS 'MODULE_PATHNAME', 'pgstattuplebyid' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstatindex(IN relname regclass, + OUT version INT, + OUT tree_level INT, + OUT index_size BIGINT, + OUT root_block_no BIGINT, + OUT internal_pages BIGINT, + OUT leaf_pages BIGINT, + OUT empty_pages BIGINT, + OUT deleted_pages BIGINT, + OUT avg_leaf_density FLOAT8, + OUT leaf_fragmentation FLOAT8) +AS 'MODULE_PATHNAME', 'pgstatindexbyid' +LANGUAGE C STRICT; + +CREATE FUNCTION pg_relpages(IN relname regclass) +RETURNS BIGINT +AS 'MODULE_PATHNAME', 'pg_relpagesbyid' +LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control index fcfd36f1b1..a7cf47fd92 100644 --- a/contrib/pgstattuple/pgstattuple.control +++ b/contrib/pgstattuple/pgstattuple.control @@ -1,5 +1,5 @@ # pgstattuple extension comment = 'show tuple-level statistics' -default_version = '1.1' +default_version = '1.2' module_pathname = '$libdir/pgstattuple' relocatable = true diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql index 8cb350d6ee..0e0ad0e15d 100644 --- a/contrib/pgstattuple/sql/pgstattuple.sql +++ b/contrib/pgstattuple/sql/pgstattuple.sql @@ -8,13 +8,27 @@ CREATE EXTENSION pgstattuple; create table test (a int primary key, b int[]); +select * from pgstattuple('test'); select * from pgstattuple('test'::text); +select * from pgstattuple('test'::name); select * from pgstattuple('test'::regclass); +select pgstattuple(oid) from pg_class where relname = 'test'; +select pgstattuple(relname) from pg_class where relname = 'test'; select * from pgstatindex('test_pkey'); +select * from pgstatindex('test_pkey'::text); +select * from pgstatindex('test_pkey'::name); +select * from pgstatindex('test_pkey'::regclass); +select pgstatindex(oid) from pg_class where relname = 'test_pkey'; +select pgstatindex(relname) from pg_class where relname = 'test_pkey'; select pg_relpages('test'); select pg_relpages('test_pkey'); +select pg_relpages('test_pkey'::text); +select pg_relpages('test_pkey'::name); +select pg_relpages('test_pkey'::regclass); +select pg_relpages(oid) from pg_class where relname = 'test_pkey'; +select pg_relpages(relname) from pg_class where relname = 'test_pkey'; create index test_ginidx on test using gin (b); -- cgit v1.2.1