Re: [PATCHES] [GENERAL] dropping role w/dependent objects
On Tuesday 01 May 2007 9:34 pm, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: [ enlarge MAX_REPORTED_DEPS to 2000 ] I was about to apply this, but stopped to reflect that it is probably not such a hot idea. My concern is that enormously long error message detail fields are likely to break client software, particularly GUI clients. A poor (e.g., truncated) display isn't unlikely, and a crash not entirely out of the question. Moreover, who's to say that 2000 is enough lines to cover all cases? And if it's not, aren't you faced with an even bigger problem? Perhaps a better solution is to keep MAX_REPORTED_DEPS where it is, and arrange that when it's exceeded, the *entire* list of dependencies gets reported to the postmaster log; we can expect that that will work. We still send the same just-the-count message to the client. We could add a hint suggesting to look in the postmaster log for the details. This would require some refactoring of checkSharedDependencies's API, I suppose, but doesn't seem especially difficult. Fair enough. Something, anything, in the server log would suffice to identify the problem specifics which are now hidden. Unfortunately, I won't get to it anytime soon. Ed ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [GENERAL] dropping role w/dependent objects
On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote: Perhaps this could be added to the TODO list? I won't get to it anytime soon. Yes. What should the TODO text be? See if the attached patch is acceptable. If not, perhaps the TODO text should be: Enable end user to identify dependent objects when the following error is encountered: ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 227 objects in this database Index: ./src/backend/catalog/pg_shdepend.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_shdepend.c,v retrieving revision 1.17 diff -C1 -r1.17 pg_shdepend.c *** ./src/backend/catalog/pg_shdepend.c 3 Mar 2007 19:32:54 - 1.17 --- ./src/backend/catalog/pg_shdepend.c 5 Apr 2007 00:05:56 - *** *** 484,488 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. */ ! #define MAX_REPORTED_DEPS 100 --- 484,497 * We try to limit the number of reported dependencies to something sane, ! * both for the user's sake and to avoid blowing out memory. But since ! * this is the only way for an end user to easily identify the dependent ! * objects, make the limit pretty big. Generously assuming each object ! * description is 64 chars long, and assuming we add some commentary of ! * up to 15 chars in storeObjectDescription(), that's ~80 chars per ! * object. If we allow 2000, that's 160Kb, which is reasonable. If the ! * installer gets wild and uses 128 character names, that's still only ! * 320Kb. These sorts of high numbers of dependencies are reached quite ! * easily when a sizeable schema of hundreds of tables has specific grants ! * on each relation. */ ! #define MAX_REPORTED_DEPS 2000 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] query planner rewrite
The attached patch overhauls the query planner to store all query plans and cache all query results in a Microsoft Excel spreadsheet via ODBC. Index: postgres.c === RCS file: /projects/cvsroot/pgsql/src/backend/tcop/postgres.c,v retrieving revision 1.442 diff -C1 -r1.442 postgres.c *** postgres.c 22 Feb 2005 04:37:17 - 1.442 --- postgres.c 1 Apr 2005 16:25:54 - *** *** 4,5 --- 4,6 * POSTGRES C Backend Interface + * Happy April Fools' Day! * ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] dbsize patch
On Thursday February 3 2005 9:23, Ed L. wrote:
Neil, do you have a verdict on this patch?
On Friday January 28 2005 10:30, Ed L. wrote:
If the C code for the prior dbsize patch is not acceptable
for whatever reason, here's a SQL-based patch to replace it.
I submitted a dbsize patch on Jan 25, revised it twice per
concerns raised by Michael Paesold and Neil Conway (indexes
instead of indices) and Andreas Pflug and Tom Lane (implement
in SQL instead of C) and resubmitted Jan 28. I've not received
any further communication regarding the patch. Please advise if
there are concerns. I've attached the patch again, slightly
cleaned up, in case it has fallen through the cracks.
Ed
Index: README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- README.dbsize 6 Feb 2005 15:06:19 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the on-disk size of a
! given database object in bytes:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
***
*** 12,14
! The first two functions:
--- 14,20
! setof record relation_size_components(text)
!
! The first four functions take the name of the object (possibly
! schema-qualified for the latter three) and returns the size of the
! on-disk files in bytes.
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 22,27
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
! These functions take object OIDs:
***
*** 24,49
Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
!
! SELECT *,
! pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
! FROM
! (SELECT pg_relation_size(cl.oid) AS tablesize,
! COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
! FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size(reltoastrelid)
! END AS toastsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
! WHERE ct.oid = cl.reltoastrelid))
! END AS toastindexsize
! FROM pg_class cl
! WHERE relname = 'foo') ss;
!
! This sample query utilizes the helper function pg_size_pretty(int8),
! which formats the number of bytes into a convenient string using KB, MB,
! GB. It is also contained in this module.
--- 31,113
+ The indexes_size() function returns the total size of the indices for a
+ relation, including any toasted indices.
+
+ The aggregate_relation_size() function returns the total size of the relation,
+ all its indices, and any toasted data.
+
Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any related indexes or toast tables are not
! counted. To obtain the total size of a table including all indices and
! toasted data, use aggregate_relation_size().
!
! The last function, relation_size_components(), returns a set of rows
! showing the sizes of the component relations constituting the input
! relation.
!
! Examples
!
!
! I've loaded the following table with a little less than 3 MB of data for
! illustration:
!
! create table fat ( id serial, data varchar );
! create index fat_uidx on fat (id);
! create index fat_idx on fat (data);
!
! You can retrieve a rowset containing constituent sizes as follows:
!
! # SELECT relation_size_components('fat');
! relation_size_components
!
! (2088960,65536,2891776,fat,r,59383,59383)
! (32768,704512,737280,pg_toast_59383,t,59386,59386)
! (0,32768,32768,pg_toast_59383_index,i,59388,59388)
! (0,2039808,2039808,fat_idx,i,59389,59389)
! (0,49152,49152,fat_uidx,i,59911,59911)
! (5 rows)
!
! To see a more readable output of the rowset:
!
! SELECT *
! FROM relation_size_components('fat') AS (idxsize BIGINT,
! datasize BIGINT,
! totalsize BIGINT
Re: [PATCHES] dbsize patch
Neil, do you have a verdict on this patch? On Friday January 28 2005 10:30, Ed L. wrote: If the C code for the prior dbsize patch is not acceptable for whatever reason, here's a SQL-based patch to replace it. It's not a drop-in for 7.3/7.4 as I'd hoped, only an 8.1 patch. I believe it is functionally equivalent to the C patch, but simpler, shorter, and probably a tad slower. I also removed the README section on how to aggregate since it was incomplete/incorrect (it didn't count toasted indices) and added a SQL function that itemizes the size for a relation's table and index data (helpful to us in identifying bloat, measuring performance, capacity estimation, etc). Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] dbsize patch
On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
Perhaps you could rename indices_size to indexes_size.
Attached patch identical except for s/indices/indexes/g.
Attached is the same patch as context diff. (prior send from unregistered
email address)
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 27 Jan 2005 08:49:25 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 aggregate_relation_size(text)
+ int8 indexes_size(text)
***
*** 12,14
! The first two functions:
--- 14,16
! The first four functions:
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 18,24
SELECT relation_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+ SELECT indexes_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
***
*** 24,29
! Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
--- 28,65
! The function relation_size() returns the size of a relation including the
! size of any toast tables and toast indexes. It does not include the
! size of dependent indexes.
!
! The function aggregate_relation_size() returns the size of a relation
! including the size of any toast tables, toast indexes, and dependent
! indexes.
!
! The function indexes_size() returns the size of all user-defined indexes
! for the given relation. It does not include the size of the relation
! data nor does it include the size of any relation toast data.
!
! Here's an example with a table called 'fat' that illustrates
! the differences between relation_size and aggregate_relation_size:
!
! select indexes_size(n.nspname||'.'||c.relname) as idx,
!relation_size(n.nspname||'.'||c.relname) as rel,
!aggregate_relation_size(n.nspname||'.'||c.relname) as total,
!c.relname, c.relkind as kind, c.oid, c.relfilenode as node
! from pg_class c, pg_namespace n
! where c.relnamespace = n.oid
! and (c.relname like 'fat%' or c.relname like 'pg_toast%')
! order by total, c.relname
!
! (snipped)
!idx | rel | total | relname| kind | oid | node
! -+-+-+--+--+---+---
!0 | 32768 | 32768 | pg_toast_59383_index | i| 59388 | 59388
!32768 | 704512 | 737280 | pg_toast_59383 | t| 59386 | 59386
!0 | 1818624 | 1818624 | fat_idx | i| 59389 | 59389
! 1818624 | 761856 | 2580480 | fat | r| 59383 | 59383
!
! Please note that pg_relation_size reports only the size of the selected
! relation itself; any subsidiary indexes or toast tables are not counted.
! To obtain the total size of a table including all helper files you'd
! have to do something like:
***
*** 45,46
--- 81,84
+ Alternatively, just use the aggregate_relation_size() function.
+
This sample query utilizes the helper function pg_size_pretty(int8),
***
*** 51
--- 89,95
into any database using dbsize.sql.
+
+ Wishlist:
+ - include size of serial sequence objects
+ - make pg_* functions include toast, indexes, and sequences;
+ - maybe other dependent objects as well? triggers, procs, etc
+
Index: contrib/dbsize/dbsize.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 - 1.16
--- contrib/dbsize/dbsize.c 27 Jan 2005 08:49:26 -
***
*** 24,25
--- 24,26
#include utils/syscache.h
+ #include utils/relcache.h
***
*** 36,37
--- 37,40
Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum indexes_size(PG_FUNCTION_ARGS);
***
*** 44,45
--- 47,50
PG_FUNCTION_INFO_V1(relation_size);
+
Re: [PATCHES] dbsize patch
On Thursday January 27 2005 6:59, Andreas Pflug wrote: Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Ed ---(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] dbsize patch
On Thursday January 27 2005 2:12, Ed L. wrote: Well, it seems quite a bit more complicated than that to me, but I'm going to rework the patch so it drops into 7.3 as well and resubmit shortly. Too much trouble for now. Neil, if the latest patch is acceptable or useful for others as-is, great, please apply. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] dbsize patch
The attached dbsize patch:
+ makes relation_size(relname) include toast tables;
+ adds aggregate_relation_size(relname) to count table data and indices;
+ adds indices_size(relname) to report the size of indices for a
relation;
I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu,
compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).
Ed
Index: contrib/dbsize/README.dbsize
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize 28 Sep 2004 19:35:43 - 1.4
--- contrib/dbsize/README.dbsize 25 Jan 2005 23:38:16 -
***
*** 1,3
! This module contains several functions that report the size of a given
! database object:
--- 1,3
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
***
*** 5,6
--- 5,8
int8 relation_size(text)
+ int8 aggregate_relation_size(text)
+ int8 indices_size(text)
***
*** 12,14
! The first two functions:
--- 14,16
! The first four functions:
***
*** 16,20
SELECT relation_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
--- 18,24
SELECT relation_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+ SELECT indices_size('pg_class');
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
***
*** 24,29
! Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted. To obtain the total size of a table including all helper files
! you'd have to do something like:
--- 28,65
! The function relation_size() returns the size of a relation including the
! size of any toast tables and toast indices. It does not include the
! size of dependent indices.
!
! The function aggregate_relation_size() returns the size of a relation
! including the size of any toast tables, toast indices, and dependent
! indices.
!
! The function indices_size() returns the size of all user-defined indices
! for the given relation. It does not include the size of the relation
! data nor does it include the size of any relation toast data.
!
! Here's an example with a table called 'fat' that illustrates
! the differences between relation_size and aggregate_relation_size:
!
! select indices_size(n.nspname||'.'||c.relname) as idx,
!relation_size(n.nspname||'.'||c.relname) as rel,
!aggregate_relation_size(n.nspname||'.'||c.relname) as total,
!c.relname, c.relkind as kind, c.oid, c.relfilenode as node
! from pg_class c, pg_namespace n
! where c.relnamespace = n.oid
! and (c.relname like 'fat%' or c.relname like 'pg_toast%')
! order by total, c.relname
!
! (snipped)
!idx | rel | total | relname| kind | oid | node
! -+-+-+--+--+---+---
!0 | 32768 | 32768 | pg_toast_59383_index | i| 59388 | 59388
!32768 | 704512 | 737280 | pg_toast_59383 | t| 59386 | 59386
!0 | 1818624 | 1818624 | fat_idx | i| 59389 | 59389
! 1818624 | 761856 | 2580480 | fat | r| 59383 | 59383
!
! Please note that pg_relation_size reports only the size of the selected
! relation itself; any subsidiary indexes or toast tables are not counted.
! To obtain the total size of a table including all helper files you'd
! have to do something like:
***
*** 45,46
--- 81,84
+ Alternatively, just use the aggregate_relation_size() function.
+
This sample query utilizes the helper function pg_size_pretty(int8),
***
*** 51
--- 89,95
into any database using dbsize.sql.
+
+ Wishlist:
+ - include size of serial sequence objects
+ - make pg_* functions include toast, indices, and sequences;
+ - maybe other dependent objects as well? triggers, procs, etc
+
Index: contrib/dbsize/dbsize.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 - 1.16
--- contrib/dbsize/dbsize.c 25 Jan 2005 23:38:17 -
***
*** 24,25
--- 24,26
#include utils/syscache.h
+ #include utils/relcache.h
***
*** 36,37
--- 37,40
Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum
Re: [PATCHES] HP-UX PA-RISC/Itanium 64-bit Patch and HP-UX 11.23 Patch
Shinji Teragaito [EMAIL PROTECTED] writes: I made a patch to let PostgreSQL work in the LP64 data model on HP-UX PA-RISC and HP-UX Itanium platform. I see Shinji's patch changed the library suffix from .sl to .so for ia64. Is that is necessary? If so, why? Thanks, Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] 8.0.0beta3 duration logging patch
On Tuesday September 28 2004 7:59, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Your issue brings up that the boolean API doesn't really work well, and in fact highlights the fact that printing the duration as an independent capability really made no sense at all. Perhaps your approach is the proper solution --- to link them together. I thought we had fixed things so that log_duration would print the statement if it hadn't already been logged for other reasons. Did that fix get broken again? I guess so. If you set log_min_statement_duration = 0, you get duration: %ld.%03ld ms statement: %s regardless of your log_duration or log_statement settings. But log_duration does not heed log_statement, thus no way to quiet durations in sync with log_statement setting. In beta3, the logic is... if ( log_duration = true || (log_min_statement_duration = 0 || (log_min_statement_duration 0 duration log_min_statement_duration))) Going back to the issue of usefulness of queryless durations, I guess I can imagine that if someone wanted to measure average duration similar to a speedometer, they might want to log only durations, not queries, just to know how hot the DB is running. I have a 7.3 perl script to do just that. Maybe a better patch would be to make log_duration have the same options as log_statement (none, mod, ddl, all)? That would preserve the previous functionality and enable the more common usage as well. I would leave log_min_statement_duration alone since I can see where it would be useful to be able to visually distinguish between durations printed because they exceeded log_min_statement_duration. For example, logging all data-changing queries (mod) and also any overly slow SELECTs. Ed ---(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
[PATCHES] 8.0.0beta3 duration logging patch
The attached patch forces queryless duration log statements to be turned off
in step with the log_statement directive. Without this patch, and with
log_statement = 'mod' and log_duration = true, there is no way to silence
SELECT query duration logging when quieting the logging of SELECT queries.
Note this patch changes the semantics of log_duration from logging the
duration of every completed statement to every completed statement that
satisfies log_statement directive. I argue this semantic change is
justified given 1) the docs themselves recommend turning log_statement
sufficiently up to be able to make this mapping, and 2) I can see it being
quite common that folks only want to log queries (and durations) that
change the database, while I fail to see the usefulness of queryless
durations (and I'm trying to scratch my own itch with a small effort).
It's possible someone else feels strongly about their queryless durations
for reasons I cannot imagine. If so, then another more conservative
approach may be in order.
Note also this patch is independent of queries and durations logged due to
the log_min_duration_statement directive. If, for example, log_statement =
'all', log_min_duration_statement = 1 (ms), and a SELECT query takes longer
than 1ms, it's duration will be logged twice, with the 2nd log entry
including the statement with the duration.
Ed
Index: doc/src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.284
diff -C1 -r1.284 runtime.sgml
*** doc/src/sgml/runtime.sgml 26 Sep 2004 22:51:49 - 1.284
--- doc/src/sgml/runtime.sgml 28 Sep 2004 02:19:16 -
***
*** 2305,2313
para
! Causes the duration of every completed statement to be logged.
! To use this option, it is recommended that you also enable
! varnamelog_statement/ and if not using applicationsyslog/
! log the PID using varnamelog_line_prefix/ so that you
! can link the statement to the duration using the process
! ID. The default is off. Only superusers can turn off this
! option if it is enabled by the administrator.
/para
--- 2305,2314
para
! Causes the duration of every completed statement which satisfies
! varnamelog_statement/ directive to be logged.
! When using this option, if you are not using applicationsyslog/,
! it is recommended that you log the PID or session ID using
! varnamelog_line_prefix/ or log the session ID so that you can
! link the statement to the duration using the process ID or session
! ID. The default is off. Only superusers can turn off this option
! if it is enabled by the administrator.
/para
Index: src/backend/tcop/postgres.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/postgres.c,v
retrieving revision 1.433
diff -C1 -r1.433 postgres.c
*** src/backend/tcop/postgres.c 26 Sep 2004 00:26:25 - 1.433
--- src/backend/tcop/postgres.c 28 Sep 2004 02:19:19 -
***
*** 83,84
--- 83,87
+ /* flag noting if the statement satisfies log_statement directive */
+ bool loggable_statement;
+
/* GUC variable for maximum stack depth (measured in kilobytes) */
***
*** 465,469
--- 468,476
+ loggable_statement = false;
if (log_statement == LOGSTMT_ALL)
+ {
ereport(LOG,
(errmsg(statement: %s, query_string)));
+ loggable_statement = true;
+ }
***
*** 503,504
--- 510,512
(errmsg(statement: %s, query_string)));
+ loggable_statement = true;
break;
***
*** 514,515
--- 522,524
(errmsg(statement: %s, query_string)));
+ loggable_statement = true;
break;
***
*** 1005,1007
! if (save_log_duration)
ereport(LOG,
--- 1014,1023
! /*
! * If log_duration = true, don't log duration unless statement
! * statement also satifies log_statement directive. Otherwise,
! * the duration statements are devoid of context without their
! * query having been logged. Note the statement still may be
! * below due to log_min_duration_statement directive.
! */
! if (loggable_statement save_log_duration)
ereport(LOG,
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Monday August 30 2004 11:07, Ed L. wrote:
On Monday August 30 2004 10:56, Tom Lane wrote:
Ed L. [EMAIL PROTECTED] writes:
Attached is a revised patch:
Applied with minor revisions.
I did not add UTC offset logic nor logic to shift to top of the
hour/day for rotation periods of 60/1440 minutes, but would like to
add that shortly if time permits.
I did the latter but not the former -- ie, rotation target times are
rounded off, but rounded with respect to GMT not local time. I didn't
see an obviously correct behavior of round-to-local-time across DST
transitions ...
This patch rotates logs on local time boundaries instead of UTC boundaries,
e.g., midnight local for daily rotation instead of midnight UTC. It does
so by parsing the %z result from strftime().
Correct me if I'm mistaken, but I *think* the correct behavior across DST
transitions may be an orthogonal issue. Consider the case if one is
truncating logs on rotation and rotating hourly. UTC vs local is
irrelevant. If local time shifts backward from 02:00 to 01:00, our UTC
offset will move in the negative direction. If 1) our policy were to
truncate on rotation, and 2) we were rotating hourly or more frequently,
and 3) our filename would be identical the 2nd time through that clock hour
(i.e., it did not contain the epoch or UTC offset), this could cause a log
file rotation into the same filename we just had open, thereby erasing an
hour of log data. Apache's rotatelogs apparently has the same issue
without a solution, and warns of it in the code. I am arguing for
inclusion of this patch because 1) the utility of local time boundary
rotations exceeds the risk for us, and because 2) the risk can be mitigated
by a comment in the documentation and maybe postgresql.conf, and because 3)
I think the issue already exists and this doesn't make it worse.
Ed
Index: syslogger.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/syslogger.c,v
retrieving revision 1.8
diff -C1 -r1.8 syslogger.c
*** syslogger.c 31 Aug 2004 04:53:44 - 1.8
--- syslogger.c 20 Sep 2004 22:26:01 -
***
*** 849,850
--- 849,882
+
+ /*
+ * Determine our offset from GMT so we can rotate on localtime boundaries.
+ */
+ pg_time_t
+ utc_offset (pg_time_t now) {
+ int offset = 1.0, count;
+ charmsg[8], hrs[3], min[3];
+
+ /*
+ * We expect a strftime(%z) result of the form [+-]HHMM according to
+ * RFC822-conformant dates, where HH:MM is the unsigned UTC offset.
+ * If we don't get it, just return zero offset, and let the logs
+ * rotate on UTC time boundaries.
+ */
+
+ count = strftime(msg, 6, %z, localtime(now));
+ if ( count != 5 ) {
+ return 0;
+ }
+
+ if ( msg[0] == '-' )
+ offset = -1.0;
+ strncpy(hrs, msg[1], 2);
+ offset = offset * atoi(hrs) * 3600;
+ strncpy(min, msg[3], 2);
+ offset += atoi(min) * 60;
+
+ return offset;
+ }
+
+
/*
***
*** 865,868
* multiple of the log rotation interval. Multiple can be interpreted
! * fairly loosely --- in particular, for intervals larger than an hour,
! * it might be interesting to align to local time instead of GMT.
*/
--- 897,909
* multiple of the log rotation interval. Multiple can be interpreted
! * fairly loosely and we attempt to align with local time.
! *
! * In cases of Daylight Savings Time, if local time shifts backward
! * from 02:00 to 01:00, our UTC offset will move in the negative
! * direction. This could cause a logfile rotation into the same
! * filename we just had open, thereby erasing an hour of log data
! * if 1) our policy were to truncate, 2) we were rotating hourly
! * or more frequently, and 3) our filename did not contain the
! * epoch or UTC offset. Apache's rotatelogs apparently has the
! * same issue. Naming files with the epoch (%s) and/or UTC
! * offset (%z) avoids that problem.
*/
***
*** 870,873
now = time(NULL);
now -= now % rotinterval;
! now += rotinterval;
next_rotation_time = now;
--- 911,915
now = time(NULL);
+ now += (offset = utc_offset(now));
now -= now % rotinterval;
! now += rotinterval - offset;
next_rotation_time = now;
---(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] log_filename_prefix -- log_filename + strftime()
On Monday September 20 2004 4:43, Ed L. wrote: This patch rotates logs on local time boundaries instead of UTC boundaries, e.g., midnight local for daily rotation instead of midnight UTC. It does so by parsing the %z result from strftime(). ... I am arguing for inclusion of this patch because 1) the utility of local time boundary rotations exceeds the risk for us, and because 2) the risk can be mitigated by a comment in the documentation and maybe postgresql.conf, and because 3) I think the issue already exists and this doesn't make it worse. And I'd add that working with UTC-oriented rotations in 8.0.0 beta code has already proved annoying and needlessly confusing, thus the patch. Daily log rotations occur at 18:00 here. Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Monday September 20 2004 4:57, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: Consider the case if one is truncating logs on rotation and rotating hourly. UTC vs local is irrelevant. If local time shifts backward from 02:00 to 01:00, our UTC offset will move in the negative direction. If 1) our policy were to truncate on rotation, and 2) we were rotating hourly or more frequently, and 3) our filename would be identical the 2nd time through that clock hour (i.e., it did not contain the epoch or UTC offset), this could cause a log file rotation into the same filename we just had open, thereby erasing an hour of log data. Apache's rotatelogs apparently has the same issue without a solution, and warns of it in the code. Hmm. Maybe we should remember the previous filename, and only truncate when the new one is different (plus all the other conditions); else append. Sounds good. If you accept that the DST gotcha already exists and this patch is independent of it, would you consider applying this patch regardless? I'd be happy to submit an addition for your idea as my time permits. Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Monday September 20 2004 6:02, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: That might work for you, but it's not portable. Do you consider pg_tm.tm_gmtoff reliable and portable from pg_localtime(now)? Yeah, in fact I was just adapting the patch to use that. I have a 5-line check-last-filename patch, but it's so small you probably just want to add it yourself? Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] log rotatoin doc updates
This patch updates log rotation documentation: + Removed false statement that log_filename can only be changed on restart (it is reloadable via sighup); + Added a couple of examples; + Cleaned up a few smgl tags; Index: runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.280 diff -C1 -r1.280 runtime.sgml *** runtime.sgml 31 Aug 2004 04:53:43 - 1.280 --- runtime.sgml 17 Sep 2004 21:14:56 - *** *** 1931,1945 para ! When varnameredirect_stderr/ is enabled, this option sets the file names of the created log files. The value ! is treated as a systemitemstrftime/ pattern, ! so literal%/-escapes can be used to specify time-varying file names. ! If no literal%/-escapes are present, productnamePostgreSQL/productname will append the epoch of the new log file's open time. For example, ! if varnamelog_filename/ were literalserver_log/, then the ! chosen file name would be literalserver_log.1093827753/ ! for a log starting at Sun Aug 29 19:02:33 2004 MST. ! This option can only be set at server start or in the ! filenamepostgresql.conf/filename configuration file. /para --- 1931,1959 para ! When varnameredirect_stderr/varname is enabled, this option sets the file names of the created log files. The value ! is treated as a systemitemstrftime/systemitem pattern, ! so literal%/literal-escapes can be used to specify time-varying file names. ! If no literal%/literal-escapes are present, productnamePostgreSQL/productname will append the epoch of the new log file's open time. For example, ! if varnamelog_filename/varname were literalserver_log/literal, then the ! chosen file name would be literalserver_log.1093827753/literal ! for a log starting Sun Aug 29 19:02:33 2004 MST. ! /para ! para ! Example: To keep 7 days of logs, one log file per day named ! literalserver_log.Mon/literal, literalserver_log.Tue/literal, ! etc, and automatically overwrite last week's log with this week's log, ! set varnamelog_filename/varname to literalserver_log.%a/literal, ! varnamelog_truncate_on_rotation/varname to literaltrue/literal, and ! varnamelog_rotation_age/varname to literal1440/literal. ! /para ! para ! Example: To keep 24 hours of logs, one log file per hour, but ! also rotate sooner if the log file size exceeds 1GB, set ! varnamelog_filename/varname to literalserver_log.%H%M/literal, ! varnamelog_truncate_on_rotation/varname to literaltrue/literal, ! varnamelog_rotation_age/varname to literal60/literal, and ! varnamelog_rotation_size/varname to literal100/literal. /para *** *** 1952,1954 para ! When varnameredirect_stderr/ is enabled, this option determines the maximum lifetime of an individual log file. --- 1966,1968 para ! When varnameredirect_stderr/varname is enabled, this option determines the maximum lifetime of an individual log file. *** *** 1967,1969 para ! When varnameredirect_stderr/ is enabled, this option determines the maximum size of an individual log file. --- 1981,1983 para ! When varnameredirect_stderr/varname is enabled, this option determines the maximum size of an individual log file. *** *** 1982,1984 para ! When varnameredirect_stderr/ is enabled, this option will cause productnamePostgreSQL/productname to truncate (overwrite), --- 1996,1998 para ! When varnameredirect_stderr/varname is enabled, this option will cause productnamePostgreSQL/productname to truncate (overwrite), *** *** 1989,1991 all cases. For example, using this option in combination with ! a varnamelog_filename/ like literalpostgresql-%H.log/ would result in generating twenty-four hourly log files and then --- 2003,2005 all cases. For example, using this option in combination with ! a varnamelog_filename/varname like literalpostgresql-%H.log/literal would result in generating twenty-four hourly log files and then ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] log rotatoin doc updates
On Friday September 17 2004 4:44, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: This patch updates log rotation documentation: Applied, except for + Removed false statement that log_filename can only be changed on restart (it is reloadable via sighup); The statement was correct as given so I didn't remove it. (If you think it should mention that SIGHUP works, there are several dozen other uses of the identical wording to describe SIGHUP variables ...) This option can only be set at server start or in the filenamepostgresql.conf/filename configuration file. My misunderstanding. I misread the above to mean SIGHUP did not work, as opposed to postgresql.conf variables for which SIGHUP *will* reload them. I guess the word only refers to not being able to use SET? Ed ---(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] log_filename_prefix -- log_filename + strftime()
Should the epoch snprintf format of the int64 pg_time_t timestamp be %lld instead of %d? Ed ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Tuesday August 31 2004 8:45, Ed L. wrote: Should the epoch snprintf format of the int64 pg_time_t timestamp be %lld instead of %d? Ah, I see you handled it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Monday August 30 2004 10:56, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: Attached is a revised patch: Applied with minor revisions. I did not add UTC offset logic nor logic to shift to top of the hour/day for rotation periods of 60/1440 minutes, but would like to add that shortly if time permits. I did the latter but not the former -- ie, rotation target times are rounded off, but rounded with respect to GMT not local time. I didn't see an obviously correct behavior of round-to-local-time across DST transitions ... One idea for handling the round-to-localtime issue from the other end of the problem: optionally rotate logs upon an *interpolated* filename change. Then, 'server_log.%a' would cause a rotation when strftime() thinks it's midnight local, 'server_log.%H' would rotate at the top of the hour, etc. Possibly a half-baked idea. I also noticed pg_tm.tm_gmtoff is apparently not set, at least not for my local (US MT). Ed ---(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
[PATCHES] log_filename_prefix -- log_filename + strftime()
Attached is a patch which replaces the 'log_filename_prefix' configuration
directive with a similar 'log_filename' directive. It differs from the
former in the following ways:
+ allows embedded strftime() escapes ala Apache's rotatelogs;
+ eliminates hard-coded embedding of the postmaster pid;
+ makes the current hard-coded timestamp configurable;
+ changes the default log filename to exclude the PID;
This patch enables us to continue using our existing log-handling utilities
and filenaming conventions which we now use with Apache's rotatelogs.
Index: doc/src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.279
diff -C1 -r1.279 runtime.sgml
*** doc/src/sgml/runtime.sgml 24 Aug 2004 00:06:50 - 1.279
--- doc/src/sgml/runtime.sgml 27 Aug 2004 17:37:09 -
***
*** 1927,1930
! varlistentry id=guc-log-filename-prefix xreflabel=log_filename_prefix
! termvarnamelog_filename_prefix/varname (typestring/type)/term
listitem
--- 1927,1930
! varlistentry id=guc-log-filename xreflabel=log_filename
! termvarnamelog_filename/varname (typestring/type)/term
listitem
***
*** 1932,1936
When varnameredirect_stderr/ is enabled, this option
! sets the prefix of the file names of the created log files.
! The postmaster PID and the current time are appended to this
! prefix to form an exact log file name.
This option can only be set at server start or in the
--- 1932,1935
When varnameredirect_stderr/ is enabled, this option
! sets the name of the created log files. Any embedded
! strftime escapes sequences are interpolated per strftime().
This option can only be set at server start or in the
Index: src/backend/postmaster/syslogger.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/syslogger.c,v
retrieving revision 1.5
diff -C1 -r1.5 syslogger.c
*** src/backend/postmaster/syslogger.c 9 Aug 2004 20:28:48 - 1.5
--- src/backend/postmaster/syslogger.c 27 Aug 2004 17:37:11 -
***
*** 64,66
char * Log_directory = pg_log;
! char * Log_filename_prefix = postgresql-;
--- 64,66
char * Log_directory = pg_log;
! char * Log_filename = postgresql-%Y-%m-%d_%H%M%S.log;
***
*** 122,123
--- 122,124
char currentLogDir[MAXPGPATH];
+ char currentLogFilename[MAXPGPATH];
***
*** 219,222
last_rotation_time = time(NULL);
! /* remember active logfile directory */
strncpy(currentLogDir, Log_directory, MAXPGPATH);
--- 220,224
last_rotation_time = time(NULL);
! /* remember active logfile directory and filename */
strncpy(currentLogDir, Log_directory, MAXPGPATH);
+ strncpy(currentLogFilename, Log_filename, MAXPGPATH);
***
*** 240,247
/*
! * Check if the log directory changed in postgresql.conf. If so,
! * force rotation to make sure we're writing the logfiles in the
! * right place.
! *
! * XXX is it worth responding similarly to a change of
! * Log_filename_prefix?
*/
--- 242,246
/*
! * Check if the log directory or filename prefix changed in
! * postgresql.conf. If so, force rotation to make sure we're
! * writing the logfiles in the right place.
*/
***
*** 252,253
--- 251,257
}
+ if (strncmp(Log_filename, currentLogFilename, MAXPGPATH) != 0)
+ {
+ strncpy(currentLogFilename, Log_filename, MAXPGPATH);
+ rotation_requested = true;
+ }
}
***
*** 791,796
char *filename;
! char stamptext[128];
!
! pg_strftime(stamptext, sizeof(stamptext), %Y-%m-%d_%H%M%S,
! pg_localtime(timestamp));
--- 795,797
char *filename;
! int len;
***
*** 799,807
if (is_absolute_path(Log_directory))
! snprintf(filename, MAXPGPATH, %s/%s%05u_%s.log,
! Log_directory, Log_filename_prefix,
! (unsigned int) PostmasterPid, stamptext);
else
! snprintf(filename, MAXPGPATH, %s/%s/%s%05u_%s.log,
! DataDir, Log_directory, Log_filename_prefix,
! (unsigned int) PostmasterPid, stamptext);
--- 800,815
if (is_absolute_path(Log_directory))
! snprintf(filename, MAXPGPATH, %s/, Log_directory);
else
! snprintf(filename, MAXPGPATH, %s/%s/, DataDir, Log_directory);
!
! len = strnlen(filename, MAXPGPATH);
!
! /* use strftime() if there are embedded % escape sequences */
! if ( strstr(Log_filename, %) != NULL ) {
! struct pg_tm *now;
! now = pg_gmtime(timestamp);
! pg_strftime((char*) (filename + len), MAXPGPATH - len, Log_filename, now);
! }
! else
! snprintf((char*) (filename +
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
The patch is intended for 8.0.0 or later, and was generated and tested with the cvs trunk as of 26-Aug-2004. On Friday August 27 2004 11:50, Ed L. wrote: Attached is a patch which replaces the 'log_filename_prefix' configuration directive with a similar 'log_filename' directive. It differs from the former in the following ways: + allows embedded strftime() escapes ala Apache's rotatelogs; + eliminates hard-coded embedding of the postmaster pid; + makes the current hard-coded timestamp configurable; + changes the default log filename to exclude the PID; This patch enables us to continue using our existing log-handling utilities and filenaming conventions which we now use with Apache's rotatelogs. ---(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] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 12:08, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: Attached is a patch which replaces the 'log_filename_prefix' configuration directive with a similar 'log_filename' directive. + changes the default log filename to exclude the PID; This would be better stated as makes it impossible to use the PID in the file name. While I'm prepared to grant that it may not be necessary to do so in many scenarios, I'm not very happy with arbitrarily removing the ability ... especially without giving any justification. Yes, should have said more on that item. First, I didn't see how to easily make it configurable in combination with strftime() without doing more work, and it didn't appear to be worth the effort. By its addition, hard-coding the PID into the filename deviates from what I would argue is the de facto standard of Apache's rotatelogs and forces a naming convention where none existed before. That creates work for us as we have a considerable infrastructure setup to deal with logs; I suspect that may be the case with others. I looked, but did not find, justification for why it was introduced; I would assume it was added to allow for multiple postmasters sharing the same log directory. I had difficulty fathoming the usefulness of this being hard-coded, as it seems one could compensate easily through the configurable 'log_filename' if one chose to share a log directory among postmasters. Not by including the PID, but by some other postmaster-unique naming approach. Given its a new 'feature', I'm hoping it can be altered to return the freedom of filenaming to the administrator. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 12:41, Tom Lane wrote: BTW, as long as we are taking Apache as the de facto standard --- does the default of postgresql-%Y-%m-%d_%H%M%S.log actually make sense, or would something different be closer to the common practice with Apache? Apache defaults to access_log.N where N is the epoch of the logfile start time. Ed ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 12:51, Ed L. wrote: On Friday August 27 2004 12:41, Tom Lane wrote: BTW, as long as we are taking Apache as the de facto standard --- does the default of postgresql-%Y-%m-%d_%H%M%S.log actually make sense, or would something different be closer to the common practice with Apache? Apache defaults to access_log.N where N is the epoch of the logfile start time. I should say, Apache rotatelogs takes a configurable filename and then appends .N where N is the logfile start time epoch. In one case, its access_log.N, in another its error_log.N. Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 1:03, Tom Lane wrote:
Ed L. [EMAIL PROTECTED] writes:
On Friday August 27 2004 12:41, Tom Lane wrote:
BTW, as long as we are taking Apache as the de facto standard --- does
the default of postgresql-%Y-%m-%d_%H%M%S.log actually make sense,
or would something different be closer to the common practice with
Apache?
I should say, Apache rotatelogs takes a configurable filename and then
appends .N where N is the logfile start time epoch. In one case, its
access_log.N, in another its error_log.N.
Hmm ... there isn't any way to emulate that with strftime escapes,
unless I missed the right one.
If you supply an escape, Apache will override that default epoch. So I
could see setting the default to server_log or postgresql_log or
whatever, and making the default (with no escapes supplied) be the epoch.
That would be easy tweak, and be much closer to Apache style.
Ed
Apache 1.3.31:
if (use_strftime) {
struct tm *tm_now;
tm_now = gmtime(tLogStart);
strftime(buf2, sizeof(buf2), szLogRoot, tm_now);
}
else {
sprintf(buf2, %s.%010d, szLogRoot, (int) tLogStart);
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 1:39, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Ah, so we keep the existing format but drop the pid, and just make it changable by the user, and we rename it. Doesn't sound as drastic as it first did. Yeah, the only change in default behavior would be to drop the PID part of the log filename, which doesn't seem too bad, since people aren't yet depending on that. regards, tom lane OK, if I read you correctly... Default remains postgresql-%Y-%m-%d_%H%M%S.log (Apache style: access_log.%s) If log_filename = 'xxx', rotate with strftime() to 'xxx-%Y-%m-%d_%H%M%S' (Apache style: xxx.%s) If log_filename = 'xxx.%a', rotate with strftime() to 'xxx.%a' (Apache style: xxx.%a) Not a big fan of the verbose 32-character default name, 'server_log.%s' would be my pick, but easy enough to override it. Ed ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 2:15, Tom Lane wrote:
Ed L. [EMAIL PROTECTED] writes:
If log_filename = 'xxx', rotate with strftime() to
'xxx-%Y-%m-%d_%H%M%S'
No, I was thinking that if no %'s in the log_filename, then use xxx.EPOCH
to provide Apache compatibility.
OK, that works for me.
One addition I'd like to include with the revised patch: a boolean
postgresql.conf option ('log_truncate_on_rotation', default false) to
truncate any existing log file by the same name. Default behavior here and
with Apache is to always append, but it's a useful feature for us because
it largely eliminates the issue of logs filling up the disk. You don't
want the log clobbered on restarts, so the idea is to only truncate during
time/size-based rotation, not on the initial open. Thoughts?
Ed
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 1:15, Tom Lane wrote: Ed L. [EMAIL PROTECTED] writes: On Friday August 27 2004 1:03, Tom Lane wrote: Hmm ... there isn't any way to emulate that with strftime escapes, unless I missed the right one. If you supply an escape, Apache will override that default epoch. So I could see setting the default to server_log or postgresql_log or whatever, and making the default (with no escapes supplied) be the epoch. That would be easy tweak, and be much closer to Apache style. Yeah, and it would also prevent a risk I now see with your initial patch: if no %, it'll write the same filename each time, which is almost certainly not desired. Works for me. I think this turns out to be no big deal either way here as it is for Apache either way. Consider if I set my rotation time to 1 hour and my log_filename = 'server_log.%a' (server_log.Fri). Then each of the first 22 rotations for the day will simply reopen and append to the same file. IIRC, Apache's rotatelogs works the same way. In both cases, you just have to be careful to coordinate your filename and rotation time/size limits to get the desired effect. Ed ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 3:49, Tom Lane wrote: A potential problem is what about size-driven rotation? If the hourly output exceeds log_rotation_size then you'd truncate and rewrite the current file, which is just exactly not what you want :-(. You could say that truncation occurs only at time-driven, not size-driven rotations, but that would effectively amount to saying that size-driven rotation is disabled, which I don't think I like ... One other thing I've been thinking of suggesting is that the next-rotation-target-time be rounded to an exact multiple of log_rotation_age. So for example if you set log_rotation_age = 60 minutes then rotations will happen at the top of the hour no matter when the postmaster was started. The simplistic approach of doing this on the time_t value would mean that, say, age = 24*60 would give you rotations occurring at GMT midnight not local midnight, which isn't perfect but I'd say good enough. Again though, the interaction with size-driven rotation might need more thought. Apache's rotatelogs works this way, and includes a UTC offset option, to allow rotations at local midnight. Possibly you could fix the first issue if you did all this to the code and then used, say, log_filename postgresql_%H:%M.log with 60-minute rotation. You'd normally get only logfiles named after the top of the hour, but in an hour with unusually heavy log output you might get some additional files with intermediate %M values. Course that puts you back to needing a cron daemon to clean those up ... Not that elegant, but pretty reasonable, I think. In the normal case of logfiles under the maximum size, everything is cleaned up. If you bloat, you have some clean-up to do, but easy enough with a cron job. We have been operating ~40 clusters this way for a couple years now with a modified Apache rotatelogs (w/truncate option) and a cron to clean-up too-old logfiles. It has pretty much eliminated our disk-full crises from DB logs. Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
On Friday August 27 2004 4:34, Ed L. wrote: One other thing I've been thinking of suggesting is that the next-rotation-target-time be rounded to an exact multiple of log_rotation_age. So for example if you set log_rotation_age = 60 minutes then rotations will happen at the top of the hour no matter when the postmaster was started. The simplistic approach of doing this on the time_t value would mean that, say, age = 24*60 would give you rotations occurring at GMT midnight not local midnight, which isn't perfect but I'd say good enough. Again though, the interaction with size-driven rotation might need more thought. Apache's rotatelogs works this way, and includes a UTC offset option, to allow rotations at local midnight. I see struct pg_tm has tm_gmtoff, but it seems to be zero on my MST7MDT 2.4 kernel linux box here. Is there a standard way of retrieving the offset within the PG source code? Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] log_line_prefix additions
This patch against 8.0.0beta1 source adds log_line_prefix options for
millisecond timestamps (%m), remote host (%h), and remote port (%P). The
milliseconds are useful for QPS measurements, and the remote port is
worthless to us as part of %r.
*** src/backend/utils/error/elog.c.orig 2004-08-25 12:37:26.0 -0600
--- src/backend/utils/error/elog.c 2004-08-25 15:05:34.0 -0600
***
*** 1347,1348
--- 1347,1370
break;
+ case 'm':
+ {
+ time_t stamp_time;
+ char strfbuf[128];
+ struct timeval tv;
+ struct timezone tz = {0, 0};
+
+ gettimeofday(tv, tz);
+ stamp_time = tv.tv_sec;
+
+ /* leave room for milliseconds... */
+ strftime(strfbuf, sizeof(strfbuf),
+ %Y-%m-%d %H:%M:%S %Z,
+ localtime(stamp_time));
+
+ /* 'paste' milliseconds into place... */
+ sprintf(strfbuf+19, .%03d,
+ (int)(tv.tv_usec/1000));
+
+ appendStringInfoString(buf, strfbuf);
+ }
+ break;
case 't':
***
*** 1394,1395
--- 1416,1430
}
+ case 'h':
+ if (MyProcPort)
+ {
+ appendStringInfo(buf, %s, MyProcPort-remote_host);
+ }
+ break;
+ case 'P':
+ if (MyProcPort)
+ {
+ if (strlen(MyProcPort-remote_port) 0)
+ appendStringInfo(buf, %s,
+ MyProcPort-remote_port);
+ }
break;
*** ./doc/src/sgml/runtime.sgml.orig 2004-08-25 15:11:39.0 -0600
--- ./doc/src/sgml/runtime.sgml 2004-08-25 15:13:29.0 -0600
***
*** 2304,2305
--- 2304,2315
row
+ entryliteral%h/literal/entry
+ entryRemote Hostname or IP address/entry
+ entryYes/entry
+ /row
+ row
+ entryliteral%P/literal/entry
+ entryRemote Port/entry
+ entryYes/entry
+ /row
+ row
entryliteral%p/literal/entry
***
*** 2314,2315
--- 2324,2330
row
+ entryliteral%m/literal/entry
+ entryTimestamp with milliseconds/entry
+ entryNo/entry
+ /row
+ row
entryliteral%i/literal/entry
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] log_line_prefix additions
Attached also is a patch to comments in sample postgresql.conf file.
Subject: [PATCHES] log_line_prefix additions
Date: Wednesday August 25 2004 3:26
From: Ed L. [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
This patch against 8.0.0beta1 source adds log_line_prefix options for
millisecond timestamps (%m), remote host (%h), and remote port (%P). The
milliseconds are useful for QPS measurements, and the remote port is
worthless to us as part of %r.
---
*** src/backend/utils/error/elog.c.orig 2004-08-25 12:37:26.0 -0600
--- src/backend/utils/error/elog.c 2004-08-25 15:05:34.0 -0600
***
*** 1347,1348
--- 1347,1370
break;
+ case 'm':
+ {
+ time_t stamp_time;
+ char strfbuf[128];
+ struct timeval tv;
+ struct timezone tz = {0, 0};
+
+ gettimeofday(tv, tz);
+ stamp_time = tv.tv_sec;
+
+ /* leave room for milliseconds... */
+ strftime(strfbuf, sizeof(strfbuf),
+ %Y-%m-%d %H:%M:%S %Z,
+ localtime(stamp_time));
+
+ /* 'paste' milliseconds into place... */
+ sprintf(strfbuf+19, .%03d,
+ (int)(tv.tv_usec/1000));
+
+ appendStringInfoString(buf, strfbuf);
+ }
+ break;
case 't':
***
*** 1394,1395
--- 1416,1430
}
+ case 'h':
+ if (MyProcPort)
+ {
+ appendStringInfo(buf, %s, MyProcPort-remote_host);
+ }
+ break;
+ case 'P':
+ if (MyProcPort)
+ {
+ if (strlen(MyProcPort-remote_port) 0)
+ appendStringInfo(buf, %s,
+ MyProcPort-remote_port);
+ }
break;
*** ./doc/src/sgml/runtime.sgml.orig 2004-08-25 15:11:39.0 -0600
--- ./doc/src/sgml/runtime.sgml 2004-08-25 15:13:29.0 -0600
***
*** 2304,2305
--- 2304,2315
row
+ entryliteral%h/literal/entry
+ entryRemote Hostname or IP address/entry
+ entryYes/entry
+ /row
+ row
+ entryliteral%P/literal/entry
+ entryRemote Port/entry
+ entryYes/entry
+ /row
+ row
entryliteral%p/literal/entry
***
*** 2314,2315
--- 2324,2330
row
+ entryliteral%m/literal/entry
+ entryTimestamp with milliseconds/entry
+ entryNo/entry
+ /row
+ row
entryliteral%i/literal/entry
*** src/backend/utils/misc/postgresql.conf.sample.orig 2004-08-25 16:01:19.0 -0600
--- src/backend/utils/misc/postgresql.conf.sample 2004-08-25 16:02:04.0 -0600
***
*** 225,227
--- 225,229
# %r=remote host and port
+ # %h=remote host %P=port
# %p=PID %t=timestamp %i=command tag
+ # %m=timestamp with milliseconds
# %c=session id %l=session line number
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] log_line_prefix additions
On Wednesday August 25 2004 4:25, Andrew Dunstan wrote: From: Ed L. [EMAIL PROTECTED] To: [EMAIL PROTECTED] This patch against 8.0.0beta1 source adds log_line_prefix options for millisecond timestamps (%m), remote host (%h), and remote port (%P). The milliseconds are useful for QPS measurements, and the remote port is worthless to us as part of %r. [snip] I don't understand what you mean by the remote port being worthless to us as part of %r. Please explain. Worthless to us as part of %r means we don't use the remote port, and would rather not have it in our logs. With %h, we can log what we want (hostname/IP), and others can still get both ip and port if they like with %h(%P) or %r. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
