[PATCHES] trace_checkpoint parameter patch
Hi all,
Here is a brand new patch to log a checkpointing load information
to tune the bgwriter parameter.
When you enable trace_checkpoint parameter and process
the checkpoint, the bgwriter logs the number of
flushed buffer pages and the elapsed time.
---
[EMAIL PROTECTED]:~/pgsql-snapshot% ./bin/psql -c 'checkpoint' pgbench
LOG: CheckPoint: 1522/3072 buffer(s) flushed. CPU 0.03s/0.00u sec elapsed 1.57
sec
CHECKPOINT
[EMAIL PROTECTED]:~/pgsql-snapshot%
---
I think this information is useful to configure
the bgwriter parameter to reduce the performance impact
of the checkpointing.
Any suggestion or comments?
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -rc postgresql-snapshot.orig/src/backend/access/transam/xlog.c
postgresql-snapshot/src/backend/access/transam/xlog.c
*** postgresql-snapshot.orig/src/backend/access/transam/xlog.c Fri Jun 1
00:13:01 2007
--- postgresql-snapshot/src/backend/access/transam/xlog.c Tue Jun 12
18:37:47 2007
***
*** 48,53
--- 48,54
#include storage/spin.h
#include utils/builtins.h
#include utils/pg_locale.h
+ #include utils/pg_rusage.h
***
*** 66,76
char *XLOG_sync_method = NULL;
const charXLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
bool fullPageWrites = true;
-
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
#endif
/*
* XLOGfileslop is used in the code as the allowed fuzz in the number of
* preallocated XLOG segments --- we try to have at least XLOGfiles advance
--- 67,78
char *XLOG_sync_method = NULL;
const charXLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
bool fullPageWrites = true;
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
#endif
+ bool trace_checkpoint = false;
+
/*
* XLOGfileslop is used in the code as the allowed fuzz in the number of
* preallocated XLOG segments --- we try to have at least XLOGfiles advance
***
*** 5699,5708
static void
CheckPointGuts(XLogRecPtr checkPointRedo)
{
CheckPointCLOG();
CheckPointSUBTRANS();
CheckPointMultiXact();
! FlushBufferPool(); /* performs all required fsyncs
*/
/* We deliberately delay 2PC checkpointing as long as possible */
CheckPointTwoPhase(checkPointRedo);
}
--- 5701,5722
static void
CheckPointGuts(XLogRecPtr checkPointRedo)
{
+ PGRUsage ru0;
+ int flushed = 0;
+
CheckPointCLOG();
CheckPointSUBTRANS();
CheckPointMultiXact();
!
! if (trace_checkpoint)
! pg_rusage_init(ru0);
!
! flushed = FlushBufferPool();/* performs all
required fsyncs */
!
! if (trace_checkpoint)
! elog(LOG, CheckPoint: %d/%d buffer(s) flushed. %s,
!flushed, NBuffers, pg_rusage_show(ru0));
!
/* We deliberately delay 2PC checkpointing as long as possible */
CheckPointTwoPhase(checkPointRedo);
}
diff -rc postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.c
postgresql-snapshot/src/backend/storage/buffer/bufmgr.c
*** postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.cThu May
31 05:11:58 2007
--- postgresql-snapshot/src/backend/storage/buffer/bufmgr.c Tue Jun 12
17:51:25 2007
***
*** 1001,1012
*
* This is called at checkpoint time to write out all dirty shared buffers.
*/
! void
BufferSync(void)
{
int buf_id;
int num_to_scan;
int absorb_counter;
/*
* Find out where to start the circular scan.
--- 1001,1013
*
* This is called at checkpoint time to write out all dirty shared buffers.
*/
! int
BufferSync(void)
{
int buf_id;
int num_to_scan;
int absorb_counter;
+ int flushed;
/*
* Find out where to start the circular scan.
***
*** 1019,1024
--- 1020,1026
/*
* Loop over all buffers.
*/
+ flushed = 0;
num_to_scan = NBuffers;
absorb_counter = WRITES_PER_ABSORB;
while (num_to_scan-- 0)
***
*** 1038,1047
--- 1040,1053
AbsorbFsyncRequests();
absorb_counter = WRITES_PER_ABSORB;
}
+
+ flushed++;
}
if (++buf_id = NBuffers)
buf_id = 0;
}
+
+ return flushed;
}
/*
***
*** 1340,1350
* Local relations do not participate in checkpoints, so they don't need to be
* flushed.
*/
! void
[PATCHES] trace_checkpoint parameter patch
Hi all,
Here is a brand new patch to log a checkpointing load information
to tune the bgwriter parameter.
When you enable trace_checkpoint parameter and process
the checkpoint, the bgwriter logs the number of
flushed buffer pages and the elapsed time.
---
[EMAIL PROTECTED]:~/pgsql-snapshot% ./bin/psql -c 'checkpoint' pgbench
LOG: CheckPoint: 1522/3072 buffer(s) flushed. CPU 0.03s/0.00u sec elapsed 1.57
sec
CHECKPOINT
[EMAIL PROTECTED]:~/pgsql-snapshot%
---
I think this information is useful to configure
the bgwriter parameter to reduce the performance impact
of the checkpointing.
Any suggestion or comments?
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -rc postgresql-snapshot.orig/src/backend/access/transam/xlog.c
postgresql-snapshot/src/backend/access/transam/xlog.c
*** postgresql-snapshot.orig/src/backend/access/transam/xlog.c Fri Jun 1
00:13:01 2007
--- postgresql-snapshot/src/backend/access/transam/xlog.c Tue Jun 12
18:37:47 2007
***
*** 48,53
--- 48,54
#include storage/spin.h
#include utils/builtins.h
#include utils/pg_locale.h
+ #include utils/pg_rusage.h
***
*** 66,76
char *XLOG_sync_method = NULL;
const charXLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
bool fullPageWrites = true;
-
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
#endif
/*
* XLOGfileslop is used in the code as the allowed fuzz in the number of
* preallocated XLOG segments --- we try to have at least XLOGfiles advance
--- 67,78
char *XLOG_sync_method = NULL;
const charXLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
bool fullPageWrites = true;
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
#endif
+ bool trace_checkpoint = false;
+
/*
* XLOGfileslop is used in the code as the allowed fuzz in the number of
* preallocated XLOG segments --- we try to have at least XLOGfiles advance
***
*** 5699,5708
static void
CheckPointGuts(XLogRecPtr checkPointRedo)
{
CheckPointCLOG();
CheckPointSUBTRANS();
CheckPointMultiXact();
! FlushBufferPool(); /* performs all required fsyncs
*/
/* We deliberately delay 2PC checkpointing as long as possible */
CheckPointTwoPhase(checkPointRedo);
}
--- 5701,5722
static void
CheckPointGuts(XLogRecPtr checkPointRedo)
{
+ PGRUsage ru0;
+ int flushed = 0;
+
CheckPointCLOG();
CheckPointSUBTRANS();
CheckPointMultiXact();
!
! if (trace_checkpoint)
! pg_rusage_init(ru0);
!
! flushed = FlushBufferPool();/* performs all
required fsyncs */
!
! if (trace_checkpoint)
! elog(LOG, CheckPoint: %d/%d buffer(s) flushed. %s,
!flushed, NBuffers, pg_rusage_show(ru0));
!
/* We deliberately delay 2PC checkpointing as long as possible */
CheckPointTwoPhase(checkPointRedo);
}
diff -rc postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.c
postgresql-snapshot/src/backend/storage/buffer/bufmgr.c
*** postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.cThu May
31 05:11:58 2007
--- postgresql-snapshot/src/backend/storage/buffer/bufmgr.c Tue Jun 12
17:51:25 2007
***
*** 1001,1012
*
* This is called at checkpoint time to write out all dirty shared buffers.
*/
! void
BufferSync(void)
{
int buf_id;
int num_to_scan;
int absorb_counter;
/*
* Find out where to start the circular scan.
--- 1001,1013
*
* This is called at checkpoint time to write out all dirty shared buffers.
*/
! int
BufferSync(void)
{
int buf_id;
int num_to_scan;
int absorb_counter;
+ int flushed;
/*
* Find out where to start the circular scan.
***
*** 1019,1024
--- 1020,1026
/*
* Loop over all buffers.
*/
+ flushed = 0;
num_to_scan = NBuffers;
absorb_counter = WRITES_PER_ABSORB;
while (num_to_scan-- 0)
***
*** 1038,1047
--- 1040,1053
AbsorbFsyncRequests();
absorb_counter = WRITES_PER_ABSORB;
}
+
+ flushed++;
}
if (++buf_id = NBuffers)
buf_id = 0;
}
+
+ return flushed;
}
/*
***
*** 1340,1350
* Local relations do not participate in checkpoints, so they don't need to be
* flushed.
*/
! void
Re: [PATCHES] trace_checkpoint parameter patch
2007/6/13, Alvaro Herrera [EMAIL PROTECTED]: Well, more I/O numbers would be more interesting than CPU stats ... Well, I think so too, and I attempted to print block in / out using getrusage(), but I couldn't get them because they were always zero (on my linux). I still can't understand the reason. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] trace_checkpoint parameter patch
Greg, Thanks for comments. Greg Smith wrote: The idea of using pg_rusage_init is a new one though; I hadn't thought the CPU usage info was interesting enough to figure out how to collect it. The way the patch mentioned above works it would be hard to squeeze it in the line usefully for formatting reasons. trace_sort option uses the pg_rusage_init(), so my trace_checkpoint also uses it. I don't know what's wrong, but the I/O here is pretty simple: the checkpoint wrote some amount of data that you can compute the size of easily within the code knowing the block size. That's already done in the patch under review. Cool. If you're interested in this area, you should check out the pg_stat_bgwriter feature already in the 8.3 CVS, look through the pgsql-hackers archives for the discussion this week on the topic Controlling Load Distributed Checkpoints, and check out the Automatic adjustment of bgwriter_lru_maxpages patch whose latest version is at http://archives.postgresql.org/pgsql-patches/2007-05/msg00142.php Thanks for the information. I missed that thread. But I'm not so much interested in huge modification on the checkpoint now. I need just some information on checkpointing to tune my config by my hand. -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-50-5546-2496 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] pgstattuple extension for indexes
Thanks Bruce, Here are updated Japanese README, and uninstall_pgstattuple.sql. Bruce Momjian wrote: Patch applied. Thanks. I updated the README documentation for the new functions, attached. I could not update the Japanese version of the README. --- Satoshi Nagayasu wrote: Bruce, Attached patch has been cleaned up, and modified to be able to work with CVS HEAD. Thanks. Satoshi Nagayasu wrote: Alvaro, Alvaro Herrera wrote: Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function 'GetBTPageStatistics': /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' undeclared (first use in this function) While you're at it, please consider removing C++ style comments and unused code. Formatting is way off as well, but I guess that is easily fixed with pgindent. Thanks for comments. I'm going to fix my patch from now. Regarding the pg_relpages function, why do you think it's necessary? (It returns the true number of blocks of a given relation). It may belong into core given a reasonable use case, but otherwise it doesn't seem to belong into pgstatindex (or pgstattuple for that matter). I wanted to sample some pages from the table/index, and get their statistics to know table/index conditions. I know pgstattuple() reports table statistics, however, pgstattuple() generates heavy CPU and I/O load. When we need to sample some pages from table/index, we need to know true number of blocks. I have another function, called pgstatpage(), to get information inside a single block/page statistics of the table. pg_relpages() will be used with this. Sorry for not mentioned in previous post about pgstatpage(), but I've remembered about it just now. Many memories in my brain have already `paged-out` (too busy in last few months), and some of them got `out-of-memory`. :^) Thanks. -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-3-3523-8122 diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile --- pgstattuple.orig/Makefile2006-02-27 21:54:40.0 +0900 +++ pgstattuple/Makefile 2006-08-14 09:28:58.0 +0900 @@ -6,7 +6,7 @@ # #- -SRCS= pgstattuple.c +SRCS= pgstattuple.c pgstatindex.c MODULE_big = pgstattuple OBJS= $(SRCS:.c=.o) diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c --- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900 +++ pgstattuple/pgstatindex.c2006-08-14 11:24:23.0 +0900 @@ -0,0 +1,706 @@ +/* + * pgstatindex + * + * Copyright (c) 2006 Satoshi Nagayasu [EMAIL PROTECTED] + * + * Permission to use, copy, modify, and distribute this software and + * its documentation for any purpose, without fee, and without a + * written agreement is hereby granted, provided that the above + * copyright notice and this paragraph and the following two + * paragraphs appear in all copies. + * + * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, + * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING + * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS + * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED + * OF THE POSSIBILITY OF SUCH DAMAGE. + * + * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR + * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN AS + * IS BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, + * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + */ + +#include postgres.h + +#include fmgr.h +#include funcapi.h +#include access/heapam.h +#include access/itup.h +#include access/nbtree.h +#include access/transam.h +#include catalog/namespace.h +#include catalog/pg_type.h +#include utils/builtins.h +#include utils/inval.h + +PG_FUNCTION_INFO_V1(pgstatindex); +PG_FUNCTION_INFO_V1(bt_metap); +PG_FUNCTION_INFO_V1(bt_page_items); +PG_FUNCTION_INFO_V1(bt_page_stats); +PG_FUNCTION_INFO_V1(pg_relpages); + +extern Datum pgstatindex(PG_FUNCTION_ARGS); +extern Datum bt_metap(PG_FUNCTION_ARGS); +extern Datum bt_page_items(PG_FUNCTION_ARGS); +extern Datum bt_page_stats(PG_FUNCTION_ARGS); +extern Datum pg_relpages(PG_FUNCTION_ARGS); + +#define PGSTATINDEX_TYPE public.pgstatindex_type +#define PGSTATINDEX_NCOLUMNS 10 + +#define BTMETAP_TYPE public.bt_metap_type +#define BTMETAP_NCOLUMNS 6 + +#define BTPAGEITEMS_TYPE public.bt_page_items_type +#define BTPAGEITEMS_NCOLUMNS 6 + +#define BTPAGESTATS_TYPE public.bt_page_stats_type +#define BTPAGESTATS_NCOLUMNS 11 + + +#define IS_INDEX(r) ((r)-rd_rel-relkind == 'i') +#define IS_BTREE(r) ((r
Re: [PATCHES] pgstattuple extension for indexes
Tom Lane wrote: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I../../src/include -D_GNU_SOURCE -c -o pgstatindex.o pgstatindex.c pgstatindex.c: In function 'bt_page_items': pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 has type 'long unsigned int' pgstatindex.c:564: warning: format '%d' expects type 'int', but argument 4 has type 'long unsigned int' I guess my '%d' should be '%zd', right? -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-3-3523-8122 *** pgstatindex.c 2006-09-03 02:05:29.0 +0900 --- pgstatindex.c.new 2006-09-04 08:22:42.0 +0900 *** *** 561,567 values[j] = palloc(32); snprintf(values[j++], 32, (%u,%u), blkno, itup-t_tid.ip_posid); values[j] = palloc(32); ! snprintf(values[j++], 32, %d, IndexTupleSize(itup)); values[j] = palloc(32); snprintf(values[j++], 32, %c, IndexTupleHasNulls(itup) ? 't' : 'f'); values[j] = palloc(32); --- 561,567 values[j] = palloc(32); snprintf(values[j++], 32, (%u,%u), blkno, itup-t_tid.ip_posid); values[j] = palloc(32); ! snprintf(values[j++], 32, %zd, IndexTupleSize(itup)); values[j] = palloc(32); snprintf(values[j++], 32, %c, IndexTupleHasNulls(itup) ? 't' : 'f'); values[j] = palloc(32); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Bruce, Bruce Momjian wrote: BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update your patch for CVS HEAD. Thanks. I've posted CVS HEAD workable version on Aug.14. Please check it out. Thanks. -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
Sorry, I'll write README (and uninstall.sql?) by tomorrow. Bruce Momjian wrote: Satoshi Nagayasu wrote: Bruce, Bruce Momjian wrote: BTIem is no longer in CVS HEAD, though it was in 8.1.X. Please update your patch for CVS HEAD. Thanks. I've posted CVS HEAD workable version on Aug.14. Please check it out. Thanks. OK, I found it, but it has no updates to README.pgstattuple to describe the new functionality. Should I write it? -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pgstattuple extension for indexes
Bruce,
Attached patch has been cleaned up,
and modified to be able to work with CVS HEAD.
Thanks.
Satoshi Nagayasu wrote:
Alvaro,
Alvaro Herrera wrote:
Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD:
/pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function
'GetBTPageStatistics':
/pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem'
undeclared (first use in this function)
While you're at it, please consider removing C++ style comments and
unused code.
Formatting is way off as well, but I guess that is easily fixed with
pgindent.
Thanks for comments. I'm going to fix my patch from now.
Regarding the pg_relpages function, why do you think it's necessary?
(It returns the true number of blocks of a given relation). It may
belong into core given a reasonable use case, but otherwise it doesn't
seem to belong into pgstatindex (or pgstattuple for that matter).
I wanted to sample some pages from the table/index, and get their statistics
to know table/index conditions. I know pgstattuple() reports table
statistics, however, pgstattuple() generates heavy CPU and I/O load.
When we need to sample some pages from table/index, we need to know
true number of blocks.
I have another function, called pgstatpage(), to get information inside
a single block/page statistics of the table. pg_relpages() will be used
with this.
Sorry for not mentioned in previous post about pgstatpage(),
but I've remembered about it just now.
Many memories in my brain have already `paged-out` (too busy in last few
months),
and some of them got `out-of-memory`. :^)
Thanks.
--
NAGAYASU Satoshi [EMAIL PROTECTED]
Phone: +81-3-3523-8122
diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile
--- pgstattuple.orig/Makefile 2006-02-27 21:54:40.0 +0900
+++ pgstattuple/Makefile2006-08-14 09:28:58.0 +0900
@@ -6,7 +6,7 @@
#
#-
-SRCS = pgstattuple.c
+SRCS = pgstattuple.c pgstatindex.c
MODULE_big = pgstattuple
OBJS = $(SRCS:.c=.o)
diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c
--- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900
+++ pgstattuple/pgstatindex.c 2006-08-14 11:24:23.0 +0900
@@ -0,0 +1,706 @@
+/*
+ * pgstatindex
+ *
+ * Copyright (c) 2006 Satoshi Nagayasu [EMAIL PROTECTED]
+ *
+ * Permission to use, copy, modify, and distribute this software and
+ * its documentation for any purpose, without fee, and without a
+ * written agreement is hereby granted, provided that the above
+ * copyright notice and this paragraph and the following two
+ * paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
+ * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
+ * OF THE POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
+ * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN AS
+ * IS BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
+ * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ */
+
+#include postgres.h
+
+#include fmgr.h
+#include funcapi.h
+#include access/heapam.h
+#include access/itup.h
+#include access/nbtree.h
+#include access/transam.h
+#include catalog/namespace.h
+#include catalog/pg_type.h
+#include utils/builtins.h
+#include utils/inval.h
+
+PG_FUNCTION_INFO_V1(pgstatindex);
+PG_FUNCTION_INFO_V1(bt_metap);
+PG_FUNCTION_INFO_V1(bt_page_items);
+PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(pg_relpages);
+
+extern Datum pgstatindex(PG_FUNCTION_ARGS);
+extern Datum bt_metap(PG_FUNCTION_ARGS);
+extern Datum bt_page_items(PG_FUNCTION_ARGS);
+extern Datum bt_page_stats(PG_FUNCTION_ARGS);
+extern Datum pg_relpages(PG_FUNCTION_ARGS);
+
+#define PGSTATINDEX_TYPE public.pgstatindex_type
+#define PGSTATINDEX_NCOLUMNS 10
+
+#define BTMETAP_TYPE public.bt_metap_type
+#define BTMETAP_NCOLUMNS 6
+
+#define BTPAGEITEMS_TYPE public.bt_page_items_type
+#define BTPAGEITEMS_NCOLUMNS 6
+
+#define BTPAGESTATS_TYPE public.bt_page_stats_type
+#define BTPAGESTATS_NCOLUMNS 11
+
+
+#define IS_INDEX(r) ((r)-rd_rel-relkind == 'i')
+#define IS_BTREE(r) ((r)-rd_rel-relam == BTREE_AM_OID)
+
+#define CHECK_PAGE_OFFSET_RANGE(page, offset) { \
+ if ( !(FirstOffsetNumber=(offset) \
+
(offset)=PageGetMaxOffsetNumber(page)) ) \
+elog(ERROR, Page offset number out of range.); }
+
+#define CHECK_RELATION_BLOCK_RANGE(rel, blkno) { \
+ if ( (blkno)0 RelationGetNumberOfBlocks((rel))=(blkno
Re: [PATCHES] pgstattuple extension for indexes
Alvaro, Alvaro Herrera wrote: Huh, I bet it works with 8.1.4, but it doesn't work on CVS HEAD: /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c: In function 'GetBTPageStatistics': /pgsql/source/00orig/contrib/pgstattuple/pgstatindex.c:182: error: 'BTItem' undeclared (first use in this function) While you're at it, please consider removing C++ style comments and unused code. Formatting is way off as well, but I guess that is easily fixed with pgindent. Thanks for comments. I'm going to fix my patch from now. Regarding the pg_relpages function, why do you think it's necessary? (It returns the true number of blocks of a given relation). It may belong into core given a reasonable use case, but otherwise it doesn't seem to belong into pgstatindex (or pgstattuple for that matter). I wanted to sample some pages from the table/index, and get their statistics to know table/index conditions. I know pgstattuple() reports table statistics, however, pgstattuple() generates heavy CPU and I/O load. When we need to sample some pages from table/index, we need to know true number of blocks. I have another function, called pgstatpage(), to get information inside a single block/page statistics of the table. pg_relpages() will be used with this. Sorry for not mentioned in previous post about pgstatpage(), but I've remembered about it just now. Many memories in my brain have already `paged-out` (too busy in last few months), and some of them got `out-of-memory`. :^) Thanks. -- NAGAYASU Satoshi [EMAIL PROTECTED] Phone: +81-3-3523-8122 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pgstattuple extension for indexes
Hi all,
Here is a patch to add pgstatindex functions to the pgstattuple module,
which can work with 8.1.4. Please review and try it. Thanks.
Satoshi Nagayasu wrote:
Bruce,
I'll fix it in this week. Please wait a few days.
Thanks.
Bruce Momjian wrote:
nagayasu-san,
This looks good, but we would like the code added to
/contrib/pgstattuple, rather than it being its own /contrib module. Can
you make that adjustment? Thanks.
---
satoshi nagayasu wrote:
Hi folks,
As I said on -PATCHES, I've been working on an utility to get
a b-tree index information. I'm happy to introduce
my new functions to you.
pgstattuple module provides a `pgstatindex()`, and other small
functions, which allow you to get b-tree internal information.
I believe this module will be helpful to know b-tree index deeply.
So please try it, send comment to me, and have fun.
Thanks,
--
NAGAYASU Satoshi [EMAIL PROTECTED]
-
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
-[ RECORD 1 ]--+
version| 2
tree_level | 1
index_size | 3588096
root_block_no | 3
internal_pages | 0
leaf_pages | 437
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 59.5
leaf_fragmentation | 49.89
-
[ application/x-gzip is not supported, skipping... ]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
NAGAYASU Satoshi [EMAIL PROTECTED]
Phone: +81-3-3523-8122
diff -ruN pgstattuple.orig/Makefile pgstattuple/Makefile
--- pgstattuple.orig/Makefile 2006-08-10 19:22:47.0 +0900
+++ pgstattuple/Makefile2006-08-10 19:24:05.0 +0900
@@ -6,7 +6,7 @@
#
#-
-SRCS = pgstattuple.c
+SRCS = pgstattuple.c pgstatindex.c
MODULE_big = pgstattuple
OBJS = $(SRCS:.c=.o)
diff -ruN pgstattuple.orig/pgstatindex.c pgstattuple/pgstatindex.c
--- pgstattuple.orig/pgstatindex.c 1970-01-01 09:00:00.0 +0900
+++ pgstattuple/pgstatindex.c 2006-08-11 17:51:26.0 +0900
@@ -0,0 +1,714 @@
+/*
+ * pgstatindex
+ *
+ * Copyright (c) 2006 Satoshi Nagayasu [EMAIL PROTECTED]
+ *
+ * Permission to use, copy, modify, and distribute this software and
+ * its documentation for any purpose, without fee, and without a
+ * written agreement is hereby granted, provided that the above
+ * copyright notice and this paragraph and the following two
+ * paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
+ * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
+ * OF THE POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
+ * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN AS
+ * IS BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
+ * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ */
+
+#include postgres.h
+
+#include fmgr.h
+#include funcapi.h
+#include access/heapam.h
+#include access/itup.h
+#include access/nbtree.h
+#include access/transam.h
+#include catalog/namespace.h
+#include catalog/pg_type.h
+#include utils/builtins.h
+#include utils/inval.h
+
+PG_FUNCTION_INFO_V1(pgstatindex);
+PG_FUNCTION_INFO_V1(bt_metap);
+PG_FUNCTION_INFO_V1(bt_page_items);
+PG_FUNCTION_INFO_V1(bt_page_stats);
+PG_FUNCTION_INFO_V1(pg_relpages);
+
+extern Datum pgstatindex(PG_FUNCTION_ARGS);
+extern Datum bt_metap(PG_FUNCTION_ARGS);
+extern Datum bt_page_items(PG_FUNCTION_ARGS);
+extern Datum bt_page_stats(PG_FUNCTION_ARGS);
+extern Datum pg_relpages(PG_FUNCTION_ARGS);
+
+#define PGSTATINDEX_TYPE public.pgstatindex_type
+#define PGSTATINDEX_NCOLUMNS 10
+
+#define BTMETAP_TYPE public.bt_metap_type
+#define BTMETAP_NCOLUMNS 6
+
+#define BTPAGEITEMS_TYPE public.bt_page_items_type
+#define BTPAGEITEMS_NCOLUMNS 6
+
+#define BTPAGESTATS_TYPE public.bt_page_stats_type
+#define BTPAGESTATS_NCOLUMNS 12
+
+
+#define IS_INDEX(r) ((r)-rd_rel-relkind == 'i')
+#define IS_BTREE(r) ((r)-rd_rel-relam == BTREE_AM_OID)
+
+#define CHECK_PAGE_OFFSET_RANGE(page, offset) { \
+if ( !(FirstOffsetNumber=(offset) \
+(offset)=PageGetMaxOffsetNumber(page)) ) \
+ elog(ERROR, Page offset number out of range.); }
+
+#define CHECK_RELATION_BLOCK_RANGE(rel, blkno) { \
+if ( (blkno)0 RelationGetNumberOfBlocks((rel))=(blkno) ) \
+ elog
Re: [PATCHES] pgstattuple extension for indexes
Bruce,
I'll fix it in this week. Please wait a few days.
Thanks.
Bruce Momjian wrote:
nagayasu-san,
This looks good, but we would like the code added to
/contrib/pgstattuple, rather than it being its own /contrib module. Can
you make that adjustment? Thanks.
---
satoshi nagayasu wrote:
Hi folks,
As I said on -PATCHES, I've been working on an utility to get
a b-tree index information. I'm happy to introduce
my new functions to you.
pgstattuple module provides a `pgstatindex()`, and other small
functions, which allow you to get b-tree internal information.
I believe this module will be helpful to know b-tree index deeply.
So please try it, send comment to me, and have fun.
Thanks,
--
NAGAYASU Satoshi [EMAIL PROTECTED]
-
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
-[ RECORD 1 ]--+
version| 2
tree_level | 1
index_size | 3588096
root_block_no | 3
internal_pages | 0
leaf_pages | 437
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 59.5
leaf_fragmentation | 49.89
-
[ application/x-gzip is not supported, skipping... ]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
NAGAYASU Satoshi [EMAIL PROTECTED]
Phone: +81-3-3523-8122
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple extension for indexes
Hi folks,
As I said on -PATCHES, I've been working on an utility to get
a b-tree index information. I'm happy to introduce
my new functions to you.
pgstattuple module provides a `pgstatindex()`, and other small
functions, which allow you to get b-tree internal information.
I believe this module will be helpful to know b-tree index deeply.
So please try it, send comment to me, and have fun.
Thanks,
--
NAGAYASU Satoshi [EMAIL PROTECTED]
-
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
-[ RECORD 1 ]--+
version| 2
tree_level | 1
index_size | 3588096
root_block_no | 3
internal_pages | 0
leaf_pages | 437
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 59.5
leaf_fragmentation | 49.89
-
pgstatindex.tar.gz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[PATCHES] monitoring sort activities
Hi folks, I've created a patch to get following TODO item. * %Add ability to monitor the use of temporary sort files This patch provides one system view (pg_stat_sorts) and one utility function (pg_stat_get_prev_sort_size). snaga=# SELECT * from pg_stat_sorts; heap_all | index_all | datum_all | heap_tape | datum_tape | max_size --+---+---+---++-- 4 | 6 | 0 | 4 | 0 | 2640 (1 row) snaga=# select aid from accounts where aid % 2 = 1 order by filler desc limit 10; [...snip...] (10 rows) snaga=# SELECT pg_stat_get_prev_sort_size(); pg_stat_get_prev_sort_size 1320 (1 row) Yeah, I don't forget previous discussion with Tom. We also have to monitor temp files, but I think these view and function will benefit DBAs on performance tuning. Any comments or suggestion? -- NAGAYASU Satoshi [EMAIL PROTECTED] diff -rc postgresql-8.1beta2.orig/src/backend/catalog/system_views.sql postgresql-8.1beta2/src/backend/catalog/system_views.sql *** postgresql-8.1beta2.orig/src/backend/catalog/system_views.sql 2005-09-24 11:29:38.0 +0900 --- postgresql-8.1beta2/src/backend/catalog/system_views.sql2005-09-24 14:27:18.0 +0900 *** *** 320,325 --- 320,335 WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND pg_stat_get_backend_userid(S.backendid) = U.oid; + + CREATE VIEW pg_stat_sorts AS + SELECT pg_stat_get_sort_heap_all() as heap_all, + pg_stat_get_sort_index_all() as index_all, + pg_stat_get_sort_datum_all() as datum_all, + pg_stat_get_sort_heap_tape() as heap_tape, + pg_stat_get_sort_datum_tape() as datum_tape, + pg_stat_get_max_sort_size() as max_size; + + CREATE VIEW pg_stat_database AS SELECT D.oid AS datid, diff -rc postgresql-8.1beta2.orig/src/backend/postmaster/pgstat.c postgresql-8.1beta2/src/backend/postmaster/pgstat.c *** postgresql-8.1beta2.orig/src/backend/postmaster/pgstat.c2005-09-24 11:29:39.0 +0900 --- postgresql-8.1beta2/src/backend/postmaster/pgstat.c 2005-09-24 14:42:09.0 +0900 *** *** 120,125 --- 120,132 static bool pgStatRunningInCollector = FALSE; + static long pgStatSortHeapAll = 0; + static long pgStatSortIndexAll = 0; + static long pgStatSortDatumAll = 0; + static long pgStatSortHeapTape = 0; + static long pgStatSortDatumTape = 0; + static Size pgStatSortMaxUsedSize = 0; + /* * Place where backends store per-table info to be sent to the collector. * We store shared relations separately from non-shared ones, to be able to *** *** 823,828 --- 830,843 return; } + elog(DEBUG1, report: Sort statistics: [dbentry=%d], MyDatabaseId); + elog(DEBUG1, report: Sort statistics: heap(all)=%ld, index(all)=%ld, datum(all)=%ld, +pgStatSortHeapAll,pgStatSortIndexAll,pgStatSortDatumAll); + elog(DEBUG1, report: Sort statistics: heap(tape)=%ld, datum(tape)=%ld, +pgStatSortHeapTape,pgStatSortDatumTape); + elog(DEBUG1, report: Sort statistics: MaxSortSize=%ld, +pgStatSortMaxUsedSize); + /* * For each message buffer used during the last query set the header * fields and send it out. *** *** 839,847 --- 854,877 tsmsg-m_xact_commit = pgStatXactCommit; tsmsg-m_xact_rollback = pgStatXactRollback; + pgStatXactCommit = 0; pgStatXactRollback = 0; + tsmsg-m_sort_heap_all = pgStatSortHeapAll; + tsmsg-m_sort_index_all = pgStatSortIndexAll; + tsmsg-m_sort_datum_all = pgStatSortDatumAll; + tsmsg-m_sort_heap_tape = pgStatSortHeapTape; + tsmsg-m_sort_datum_tape = pgStatSortDatumTape; + tsmsg-m_sort_max_used_size = pgStatSortMaxUsedSize; + + pgStatSortHeapAll = 0; + pgStatSortIndexAll = 0; + pgStatSortDatumAll = 0; + pgStatSortHeapTape = 0; + pgStatSortDatumTape = 0; + pgStatSortMaxUsedSize = 0; + pgstat_setheader(tsmsg-m_hdr, PGSTAT_MTYPE_TABSTAT); tsmsg-m_databaseid = MyDatabaseId; pgstat_send(tsmsg, len); *** *** 2218,2223 --- 2248,2260 result-destroy = 0; result-last_autovac_time = 0; + result-n_sort_heap_all = 0; + result-n_sort_index_all = 0; + result-n_sort_datum_all = 0; + result-n_sort_heap_tape = 0; + result-n_sort_datum_tape = 0; + result-sort_max_used_size = 0; + memset(hash_ctl, 0, sizeof(hash_ctl));
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
The message format for elog() report is cleaned up.
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -cr pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0
+0900
--- pgsql/src/backend/commands/tablecmds.c 2005-08-08 13:46:44.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c2005-08-16 11:23:47.0
+0900
***
*** 3063,3065
--- 3063,3174
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ *Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+ int nkeys;
+ int changed;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ nkeys = 2;
+ changed = 0;
+ if ( strcmp(tgname, *)==0 )
+ {
+ if ( !superuser() )
+ ereport(ERROR,
+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg(ENABLE/DISABLE TRIGGER ALL is
allowed superuser only.)));
+
+ nkeys = 1;
+ }
+
+ ScanKeyInit(keys[0],
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(keys[1],
+ Anum_pg_trigger_tgname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(tgname));
+
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
This isn't really a gain in localizability because it assumes that there are only singular and plural forms. I do agree that plugging words like enabled or disabled into a string is not good style. Please read the message style guidelines at http://developer.postgresql.org/docs/postgres/error-style-guide.html particularly the section about writing localization-friendly messages http://developer.postgresql.org/docs/postgres/nls-programmer.html#NLS-GUIDELINES I did not know about this. I'll check my code for this style. Thanks. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Here is new patch with pg_dump modification.
Bruce Momjian wrote:
I am waiting for pg_dump support for this patch.
---
Satoshi Nagayasu wrote:
Bruce Momjian wrote:
I am not sure what to do with this patch. It is missing dump
capability, there is no clause to disable all triggers on a table, and
it uses a table owner check when a super user check is required (because
of referential integrity).
Satoshi, are you still working on it? If not does someone else want to
complete it? I realized you just started on it but the deadline is
soon.
I've already implemented 'ENABLE/DISABLE TRIGGER ALL',
and the superuser check has been added. Please check it.
And, I'm going to have a business trip to Sydney this weekend,
so I'll complete pg_dump stuffs while my flight.
Thank you.
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -cr pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28
14:08:54.0 +0900
--- pgsql/src/backend/commands/tablecmds.c2005-07-01 15:50:27.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c 2005-07-04 10:40:27.0
+0900
***
*** 3063,3065
--- 3063,3158
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+ int nkeys;
+ int changed;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ nkeys = 2;
+ changed = 0;
+ if ( strcmp(tgname, *)==0 )
+ {
+ if ( !superuser() )
+ elog(ERROR, ENABLE/DISABLE TRIGGER ALL is allowed
superuser only
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Alvaro Herrera wrote: There are a few elog() calls that should really be ereport(). Also this message I've fixed to call ereport() on permission error. + elog(NOTICE, %d trigger(s) on %s %s., + changed, + NameStr(rel-rd_rel-relname), + enable ? enabled : disabled); should really be two messages (Maybe even four: disabled-plural, disabled-singular, enabled-plural, enabled-singular) What does really be two messages mean? There's a SQL typo here: + appendPQExpBuffer(query, ALTER TABLE %s DIABLE TRIGGER %s;\n, Fixed. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] A couple of p.tches for PostgreSQL 64bit support
Hi guys, BTW, I found the work_mem is still limited to 2GB. If we support 64bit shared memory, we also need to support 64bit work_mem. Thanks. -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Bruce Momjian wrote:
I am not sure what to do with this patch. It is missing dump
capability, there is no clause to disable all triggers on a table, and
it uses a table owner check when a super user check is required (because
of referential integrity).
Satoshi, are you still working on it? If not does someone else want to
complete it? I realized you just started on it but the deadline is
soon.
I've already implemented 'ENABLE/DISABLE TRIGGER ALL',
and the superuser check has been added. Please check it.
And, I'm going to have a business trip to Sydney this weekend,
so I'll complete pg_dump stuffs while my flight.
Thank you.
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -cr pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0
+0900
--- pgsql/src/backend/commands/tablecmds.c 2005-07-01 15:50:27.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c2005-07-04 10:40:27.0
+0900
***
*** 3063,3065
--- 3063,3158
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ *Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+ int nkeys;
+ int changed;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ nkeys = 2;
+ changed = 0;
+ if ( strcmp(tgname, *)==0 )
+ {
+ if ( !superuser() )
+ elog(ERROR, ENABLE/DISABLE TRIGGER ALL is allowed
superuser only.);
+
+ nkeys = 1;
+ }
+
+ ScanKeyInit(keys[0],
+ Anum_pg_trigger_tgrelid,
[PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Hi all,
Here is a first patch to allow these commands.
ALTER TABLE table ENABLE TRIGGER trigname
ALTER TABLE table DISABLE TRIGGER trigname
Bruce said to allow them only super-user,
but currently this patch allows also the table owner.
If we need to restrict these operations,
we have to add more user checks.
From: Bruce Momjian pgman@candle.pha.pa.us
Date: 2005/06/29 20:49
Subject: Re: [HACKERS] Open items
To: Satoshi Nagayasu [EMAIL PROTECTED]
Cc: PostgreSQL-development pgsql-hackers@postgresql.org
Satoshi Nagayasu wrote:
How about enable/disable triggers?
From TODO:
Allow triggers to be disabled.
http://momjian.postgresql.org/cgi-bin/pgtodo?trigger
I think this is good for COPY performance improvement.
Now I have user functions to enable/disable triggers, not DDL.
It modifies system tables.
But I can rewrite this as a DDL. (ALTER TABLE?)
Yea, it is a TODO item, and should be pretty straight-forward to code,
so sure, go ahead.
It has to be something that is super-user-only.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -ru pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
--- pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0
+0900
+++ pgsql/src/backend/commands/tablecmds.c 2005-07-01 15:50:27.0
+0900
@@ -236,6 +236,8 @@
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
@@ -1993,6 +1995,11 @@
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
@@ -2155,6 +2162,12 @@
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
@@ -5465,6 +5478,15 @@
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+static void
+ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+{
+ EnableDisableTrigger(rel, trigname, enable);
+}
+
+/*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -ru pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
--- pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
+++ pgsql/src/backend/commands/trigger.c2005-07-01 15:53:45.0
+0900
@@ -3063,3 +3063,74 @@
afterTriggerAddEvent(new_event);
}
}
+
+/* --
+ * EnableDisableTrigger()
+ *
+ * Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+void
+EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+{
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
The table owner can drop and create triggers - so why shouldn't they be able to enable and disable them? For convenience or easy operation. I believe the user doesn't like to create same triggers again and again. Christopher Kings-Lynne wrote: ALTER TABLE table ENABLE TRIGGER trigname ALTER TABLE table DISABLE TRIGGER trigname Bruce said to allow them only super-user, but currently this patch allows also the table owner. The table owner can drop and create triggers - so why shouldn't they be able to enable and disable them? Chris -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
I said why _shouldn't_. I was agreeing with you. Oops. Sorry. I don't know why only super-user shold be able to enable/disable tirggers. I believe the table owner want to enable/disable triggers, because I always need it. Loading huge data set into the table with triggers(or fk) is very painful. Christopher Kings-Lynne wrote: Satoshi Nagayasu wrote: The table owner can drop and create triggers - so why shouldn't they be able to enable and disable them? For convenience or easy operation. I believe the user doesn't like to create same triggers again and again. I said why _shouldn't_. I was agreeing with you. Chris -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Thanks for comments, Neil.
Some are fixed.
Neil Conway wrote:
Also, you should probably skip the simple_heap_update() if the user
tries to disable an already-disabled trigger, to avoid pointless MVCC
bloat (and same for enabling an already-enabled trigger, of course).
Do we need some log/warning message for the user in these cases?
If someone pg_dump's a table with a disabled trigger, should the dump
enable or disable the trigger? I'd be inclined to say pg_dump should
preserve the state of the database it is dumping, and so the trigger
should be disabled. In that case I believe pg_dump needs to be updated.
I think so too.
The patch needs to update the documentation and add regression tests.
psql tab completion might also be worth adding.
I'll dive into pg_dump and psql this weekend...
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -cr pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0
+0900
--- pgsql/src/backend/commands/tablecmds.c 2005-07-01 15:50:27.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c2005-07-01 17:21:44.0
+0900
***
*** 3063,3065
--- 3063,3132
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ *Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ ScanKeyInit(keys[0],
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
There is one more fix...
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ SnapshotNow, 1,
keys);
'1' was incorrect, must be '2'.
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ SnapshotNow, 2,
keys);
--
NAGAYASU Satoshi [EMAIL PROTECTED]
diff -cr pgsql.orig/src/backend/commands/tablecmds.c
pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28 14:08:54.0
+0900
--- pgsql/src/backend/commands/tablecmds.c 2005-07-01 15:50:27.0
+0900
***
*** 236,241
--- 236,243
Oid newOwnerId);
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
+ bool
enable);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
char *tablespacename);
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
***
*** 1993,1998
--- 1995,2005
}
pass = AT_PASS_DROP;
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATSimplePermissions(rel, false);
+ pass = AT_PASS_MISC;
+ break;
case AT_SetTableSpace: /* SET TABLESPACE */
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd-name);
***
*** 2155,2160
--- 2162,2173
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_EnableTrig: /* ENABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, true);
+ break;
+ case AT_DisableTrig:/* DISABLE TRIGGER */
+ ATExecEnableDisableTrigger(rel, cmd-name, false);
+ break;
default:/* oops */
elog(ERROR, unrecognized alter table type: %d,
(int) cmd-subtype);
***
*** 5465,5470
--- 5478,5492
}
/*
+ * ALTER TABLE ENABLE/DISABLE TRIGGER
+ */
+ static void
+ ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
+ {
+ EnableDisableTrigger(rel, trigname, enable);
+ }
+
+ /*
* ALTER TABLE SET TABLESPACE
*/
static void
diff -cr pgsql.orig/src/backend/commands/trigger.c
pgsql/src/backend/commands/trigger.c
*** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
+0900
--- pgsql/src/backend/commands/trigger.c2005-07-01 17:21:44.0
+0900
***
*** 3063,3065
--- 3063,3132
afterTriggerAddEvent(new_event);
}
}
+
+ /* --
+ * EnableDisableTrigger()
+ *
+ *Called by ALTER TABLE ENABLE/DISABLE TRIGGER
+ * to change 'tgenabled' flag in the pg_trigger.
+ * --
+ */
+ void
+ EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
+ {
+ Relation tgrel;
+ SysScanDesc tgscan;
+ ScanKeyData keys[2];
+ HeapTuple tuple;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+errmsg(permission denied: \%s\ is a system
catalog,
+ RelationGetRelationName(rel;
+
+ tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
+
+ ScanKeyInit(keys[0],
+ Anum_pg_trigger_tgrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(keys[1],
+ Anum_pg_trigger_tgname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(tgname));
+
+ tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
+ SnapshotNow, 2,
keys);
+
+ while (HeapTupleIsValid(tuple =
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Hi, Gavin Sherry wrote: Hmmm.. just thinking about it for a second. I wonder if we should also support: ALTER TABLE DISABLE TRIGGERS I found some RDBMSes are supporting 'DISABLE TRIGGER ALL TRIGGERS' command. Does anyone know about the SQL99 spec? -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] read-only database
Bruce Momjian wrote: It's come up a few times ... more than an un-overridable read-only mode anyway. Also, I should think that those who want a secure read-only mode want it enforced selectively --- for instance, assuredly read-only for some users but not others. I can hardly see any use case for the patch as proposed; it seems to have all the disadvantages of a low-level read-only mode (eg, not selective) without any of the advantages. Our company has some PostgreSQL replication systems for our customers. I need to switch the database state between read-only and writable for recovering or maintenance. As I mentioned before, I wanted to the read-only database mode. It is the per-database state. http://archives.postgresql.org/pgsql-hackers/2005-03/msg00540.php However, if it is not provided, we have to find alternative way to get our purpose. So I'm still looking for how to make the (user) database as read-only. -- NAGAYASU Satoshi [EMAIL PROTECTED] OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] read-only database
I think the read-only has two meanings for the user. First is the internal state. XID, OID or something like that. In these cases, the internal state mustn't be changed. Some users will need the read-only for internal state. Second is read-only for the user data contents. In some cases, the user want to make the user data as read-only. For this purpose, the user doesn't care XID or OID, I guess. So, we can implement them in different way. I think both are necessary. Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: It seems server_read_only is the same as default_transaction_read_only except it can't be changed. I thought the TODO item was for a low-level read-only option, suitable for trying to look at a corrupted database or run off a read-only volume. This is very far from being that --- it allows temp table creation/use, and it still eats transaction IDs so it is certainly not read-only to xlog or clog. I am not sure I see any use case for this implementation: it is read-only enough to get in your way, without being read-only enough to derive any real benefit. I am not sure I see the use case either but I developed it so everyone could look at it and decide if it is useful. When true, it is basically a unchangable default_transaction_read_only. -- NAGAYASU Satoshi [EMAIL PROTECTED] OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] read-only database
But the second is only a subset of the first, no? So why not just implement the first? Put another way, why do you think the second is necessary? Because there is "default_transaction_read_only" option and implementation. My implementation is an extension of the existing option. I wanted to make the postmaster read-only, and found "default_transaction_read_only" option, but it can be overwritten. -- NAGAYASU Satoshi [EMAIL PROTECTED] OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] read-only database
Satoshi Nagayasu wrote: I wanted to make the postmaster read-only, and found "default_transaction_read_only" option, but it can be overwritten. I mean it can be overridden by the user. I don't want that. -- NAGAYASU Satoshi [EMAIL PROTECTED] OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] read-only database
Tom Lane wrote:
I'd view this as a postmaster state that propagates to backends.
Probably you'd enable it by means of a postmaster option, and the
only way to get out of it is to shut down and restart the postmaster
without the option.
I've created a patch to make a postmaster read-only.
(attached patch can be applied to 8.0.1)
Read-only state can be enabled/disabled by the postmaster option,
or the postgresql.conf option.
If you start the postmaster with "-r" options,
the cluster will go to read-only.
% pg_ctl -o "-i -r" -D $PGDATA start
Or if you set "readonly_cluster = true" in the postgresql.conf,
the cluster will also become read-only.
Any comments?
--
NAGAYASU Satoshi [EMAIL PROTECTED]
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jpdiff -ru postgresql-8.0.1.orig/src/backend/executor/execMain.c
postgresql-8.0.1/src/backend/executor/execMain.c
--- postgresql-8.0.1.orig/src/backend/executor/execMain.c 2005-01-15
02:53:33.0 +0900
+++ postgresql-8.0.1/src/backend/executor/execMain.c2005-03-21
13:12:22.0 +0900
@@ -43,6 +43,7 @@
#include optimizer/clauses.h
#include optimizer/var.h
#include parser/parsetree.h
+#include postmaster/postmaster.h
#include utils/acl.h
#include utils/guc.h
#include utils/lsyscache.h
@@ -127,7 +128,7 @@
* If the transaction is read-only, we need to check if any writes are
* planned to non-temporary tables.
*/
- if (XactReadOnly !explainOnly)
+ if ( (XactReadOnly || ReadOnlyCluster) !explainOnly)
ExecCheckXactReadOnly(queryDesc-parsetree);
/*
diff -ru postgresql-8.0.1.orig/src/backend/postmaster/postmaster.c
postgresql-8.0.1/src/backend/postmaster/postmaster.c
--- postgresql-8.0.1.orig/src/backend/postmaster/postmaster.c 2005-01-13
01:38:17.0 +0900
+++ postgresql-8.0.1/src/backend/postmaster/postmaster.c2005-03-21
13:21:17.0 +0900
@@ -236,6 +236,8 @@
extern int optreset;
#endif
+bool ReadOnlyCluster = false;
+
/*
* postmaster.c - function prototypes
*/
@@ -440,7 +442,7 @@
opterr = 1;
- while ((opt = getopt(argc, argv,
A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:)) != -1)
+ while ((opt = getopt(argc, argv,
A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:rSs-:)) != -1)
{
switch (opt)
{
@@ -515,6 +517,9 @@
case 'p':
SetConfigOption(port, optarg, PGC_POSTMASTER,
PGC_S_ARGV);
break;
+ case 'r':
+ SetConfigOption(readonly_cluster, true,
PGC_POSTMASTER, PGC_S_ARGV);
+ break;
case 'S':
/*
diff -ru postgresql-8.0.1.orig/src/backend/tcop/utility.c
postgresql-8.0.1/src/backend/tcop/utility.c
--- postgresql-8.0.1.orig/src/backend/tcop/utility.c2005-01-25
02:46:29.0 +0900
+++ postgresql-8.0.1/src/backend/tcop/utility.c 2005-03-21 13:13:45.0
+0900
@@ -47,6 +47,7 @@
#include parser/parse_expr.h
#include parser/parse_type.h
#include postmaster/bgwriter.h
+#include postmaster/postmaster.h
#include rewrite/rewriteDefine.h
#include rewrite/rewriteRemove.h
#include storage/fd.h
@@ -265,7 +266,7 @@
static void
check_xact_readonly(Node *parsetree)
{
- if (!XactReadOnly)
+ if (!XactReadOnly !ReadOnlyCluster)
return;
/*
diff -ru postgresql-8.0.1.orig/src/backend/utils/misc/guc.c
postgresql-8.0.1/src/backend/utils/misc/guc.c
--- postgresql-8.0.1.orig/src/backend/utils/misc/guc.c 2005-01-01
14:43:08.0 +0900
+++ postgresql-8.0.1/src/backend/utils/misc/guc.c 2005-03-21
13:06:42.0 +0900
@@ -851,6 +851,15 @@
#endif
},
+ {
+ {readonly_cluster, PGC_POSTMASTER, UNGROUPED,
+ gettext_noop(Enables the postmaster read-only.),
+ NULL
+ },
+ ReadOnlyCluster,
+ false, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL
diff -ru postgresql-8.0.1.orig/src/include/postmaster/postmaster.h
postgresql-8.0.1/src/include/postmaster/postmaster.h
--- postgresql-8.0.1.orig/src/include/postmaster/postmaster.h 2005-01-01
07:03:39.0 +0900
+++ postgresql-8.0.1/src/include/postmaster/postmaster.h2005-03-21
13:03:16.0 +0900
@@ -34,6 +34,7 @@
extern HANDLE PostmasterHandle;
#endif
+extern bool ReadOnlyCluster;
extern int PostmasterMain(int argc, char *argv[]);
extern void ClosePostmasterPorts(bool am_syslogger);
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
