Re: [PATCHES] Patch for UUID datatype (beta)
Gevik Babakhani wrote: - new_guid() function is supported. This function is based on V4 random uuid value. It generated 16 random bytes with uuid 'variant' and 'version'. It is not guaranteed to produce unique values Isn't guaranteed uniqueness the very attribute that's expected? AFAIK there's a commonly accepted algorithm providing this. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [Patch] - Fix for bug #2558, InitDB failed to run
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I am more than somewhat perplexed as to why the NUL device should be a security risk ... what are they thinking?? Frankly, I don't believe it; even Microsoft can't be that stupid. And I can't find any suggestion that they've done this in a google search. I think the OP is misdiagnosing his problem. An older message suggests that a service pack induced this problem, per MS. I just tried it as non-admin on a W2K3 machine with recent hotfixes, and the command dir nul _did_ work for me. Though neglected, it still sounds like a virus scanner issue to me. Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [Patch] - Fix for bug #2558, InitDB failed to run
Bruce Momjian wrote: Andreas Pflug wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I am more than somewhat perplexed as to why the NUL device should be a security risk ... what are they thinking?? Frankly, I don't believe it; even Microsoft can't be that stupid. And I can't find any suggestion that they've done this in a google search. I think the OP is misdiagnosing his problem. An older message suggests that a service pack induced this problem, per MS. I just tried it as non-admin on a W2K3 machine with recent hotfixes, and the command dir nul _did_ work for me. Though neglected, it still sounds like a virus scanner issue to me. Yes, it seems we will need more information on this. We need someone at a win32 command prompt to show us a nul failure. OTOH, what issues might arise if the output is redirected to a legal tmp file? Regards, Andreas ---(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] Patch for - Allow server logs to be remotely read
Alvaro Herrera wrote: Bruce Momjian wrote: Uh, I just added /contrib/adminpack a few weeks ago to CVS, which does this, and more. Sorry I forgot to mark the TODO item as completed. Huh, how do you read files with adminpack? try select * from pg_logdir_ls() as (filetime timestamp, filename text) and read the file you need. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Patch for - Allow server logs to be remotely read
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: I wonder if we should take pg_read_file (and the rest of genfile.c) back out of the backend and stick them into contrib/adminpack. I thought about that but what we have in the backend now is read-only which basically could be done using COPY, so I don't see any security value to moving them out. They are super-user only just like COPY. The you-can-do-it-with-COPY argument doesn't apply to pg_ls_dir, nor to pg_stat_file, and I find it unconvincing even for pg_read_file. COPY isn't at all friendly for trying to read binary files, for instance. pg_file_read returns text which isn't binary-friendly either. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Binary COPY for psql
The attached patch enables psql to copy binary data in and out.
Regards,
Andreas
Index: src/bin/psql/copy.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.60
diff -u -r1.60 copy.c
--- src/bin/psql/copy.c 5 Mar 2006 15:58:51 - 1.60
+++ src/bin/psql/copy.c 25 May 2006 15:17:58 -
@@ -284,9 +284,10 @@
fetch_next = true;
- /* someday allow BINARY here */
if (pg_strcasecmp(token, oids) == 0)
result-oids = true;
+ else if (pg_strcasecmp(token, binary) == 0)
+ result-binary = true;
else if (pg_strcasecmp(token, csv) == 0)
result-csv_mode = true;
else if (pg_strcasecmp(token, header) == 0)
@@ -442,8 +443,6 @@
initPQExpBuffer(query);
printfPQExpBuffer(query, COPY );
- if (options-binary)
- appendPQExpBuffer(query, BINARY );
appendPQExpBuffer(query, %s , options-table);
@@ -480,6 +479,9 @@
appendPQExpBuffer(query, WITH NULL AS '%s',
options-null);
}
+ if (options-binary)
+ appendPQExpBuffer(query, BINARY);
+
if (options-csv_mode)
appendPQExpBuffer(query, CSV);
@@ -622,7 +624,7 @@
if (buf)
{
- fputs(buf, copystream);
+ fwrite(buf, 1, ret, copystream);
PQfreemem(buf);
}
}
@@ -686,6 +688,21 @@
else
prompt = NULL;
+ if (!prompt)
+ {
+ int buflen;
+
+ while ((buflen = fread(buf, 1, COPYBUFSIZ, copystream)) 0)
+ {
+ if (PQputCopyData(conn, buf, buflen) = 0)
+ {
+ OK = false;
+ copydone = true;
+ break;
+ }
+ }
+ }
+ else
while (!copydone)
{ /* for each
input line ... */
if (prompt)
---(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] CREATE SYNONYM ...
Jonah H. Harris wrote: On 3/13/06, *Peter Eisentraut* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Does any SQL-like database system other than Oracle have this feature? I know that SQL Server, DB2, SAP DB/MAX DB, and Mimer have it. Introduced in MSSQL2005: http://msdn2.microsoft.com/en-us/library/ms177544.aspx Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] CREATE SYNONYM ...
Neil Conway wrote: I'm still unconvinced that this feature would be sufficiently useful to justify the maintenance burden, in addition to the added complexity: even if it is implemented in a way that imposes minimal *runtime* overhead, new features add complexity: introducing a bunch of new DDL commands and a new concept (synonyms) makes the system more difficult for users to understand. Synonyms appear to me a little like domains. I like them to abstract from proprietary data types. Similar, leightweight synonyms (pg_class entries) allow some abstraction if needed, without using rules. I don't think that synonyms are more difficult to understand than domains. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Numeric 508 datatype
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: However, I don't think we can promise never to change the ondisk representation of data, nor the page layout. Sometimes an inplace upgrade just won't work, ISTM. We have talked about batching on-disk changes so that they'd only occur once every few release cycles. But until we have a pg_upgrade, there is no reason to adopt such a policy. IMHO such a policy is a _prerequisite_ for somebody to come up implementing pg_upgrade. Why spend time on pg_upgrade if there's no policy to support it? Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Numeric 508 datatype
Alvaro Herrera wrote: Is anybody working or considering to work on pg_upgrade, or is all this hypothetical? Our past history has seen lots of people offering to work on pg_upgrade, and none has produced a working version. Is it fair or useful to impose restrictions on development just because it's remotely possible that somebody is going to be motivated enough to consider producing it? Depends on the impact the restriction imposes. If stability/scalability/functionality or so is affected, this sounds not tolerable. If it's about not saving two bytes that have been spoiled for ages before, or keeping a backward compatibility type, it appears feasible to me. Changing on-disk structures at the start of the 8.2 dev cycle is a guarantee that nobody will implement pg_upgrade for 8.2. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Numeric 508 datatype
Simon Riggs wrote: Now we're into 8.2devel mode, its time to submit the previously discussed patch that: - reduces Numeric storage format by 2 bytes This makes the often discussed binary upgrade impossible, so I wonder if two bytes savings are worth the trouble. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Numeric 508 datatype
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Simon Riggs wrote: Now we're into 8.2devel mode, its time to submit the previously discussed patch that: - reduces Numeric storage format by 2 bytes This makes the often discussed binary upgrade impossible, so I wonder if two bytes savings are worth the trouble. Unless someone actually steps forward and produces a working pg_upgrade in the 8.2 timeframe, this objection is moot. Hm, so if this patch is applied now, and in 5 months or so somebody implements pg_upgrade, this numeric storage patch would be rolled back? OTOH, an upgrade mechanism that's compatible for future 8.3+ versions only seems not too attractive. A solution might be to keep the current numeric implementation under a different name (deprecatednumeric or so), for backward compatibility (this should apply to future storage format changes as well). Regards, Andreas ---(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] [HACKERS] For review: Server instrumentation patch
Tom Lane wrote: I removed the separate pg_file_length() function, as it doesn't have any significant notational advantage anymore; you can do Please note that there are pg_file_length functions in use for 8.0 on probably 95 % of win32 installations, so you're breaking backwards compatibility. Regards, Andreas ---(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] [HACKERS] For review: Server instrumentation patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: I removed the separate pg_file_length() function, as it doesn't have any significant notational advantage anymore; you can do Please note that there are pg_file_length functions in use for 8.0 on probably 95 % of win32 installations, so you're breaking backwards compatibility. What backwards compatibility? Bruce already renamed several of these functions. You're right. These arbitrary renames brake nearly all of the existing code. Great. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote: True, but that is more for the application. I don't imagine a user looking at that from psql would have a problem. However, you asked for a query that looks like pg_ls_logdir() and here it is: SELECT pg_ls_dir FROM( SELECT pg_ls_dir(t1.setting) FROM(SELECT setting FROM pg_settings WHERE NAME = 'log_directory') AS t1 ) AS t2, (SELECT setting FROM pg_settings WHERE NAME = 'log_filename') AS t3 WHERE t2.pg_ls_dir LIKE regexp_replace(t3.setting, '%.*', '') || '%'; The one thing it doesn't do, as you mentioned, is check for valid dates, but it is certainly more flexible than embedding something in the backend. The interesting part of pg_logdir_ls is the filetime, to enable SELECT pg_file_unlink(filename) FROM pg_logdir_ls() WHERE filetime now() - '30 days'::interval Regards, Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Bruce Momjian wrote: Well, if they mix log files and non-log files in the same directory, we would have to filter based on the log_filename directive in the application, or use LIKE in a query. .. which is what pg_logdir_ls does. And it's robust against filenames that don't have valid dates too; imagine postgresql-2005-01-01_crash1.log. The proposed version of pg_logdir_ls could not be called robust in any way at all, considering that it fails as soon as you modify the log_filename pattern. This is caused by the exposure of log_filename, I never proposed to do that for good reasons. Any try to interpret it and read files back will break finally when log_filename is changed at runtime, i.e. it's a 'break me' option by design. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote:
Also, do we have a way to return columns from a system-installed
function? I really don't like that pg_stat_file() to returns a record
rather than named columns. How do I even access the individual record
values?
As in pg_settings:
SELECT length, mtime FROM pg_file_stat('postgresql.conf') AS st(length
int4, ctime timestamp, atime timestamp, mtime timestamp, isdir bool)
Regards,
Andreas
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote: BTW, it surprised me that one of the functions (don't remember which one) expected the log files to be named in a very specific fashion. So there's no flexibility for changing the log_prefix. Probably it's not so bad, but strange anyway. Is this for security reasons? The logger subprocess patch originally didn't allow changing the the logfile name pattern, to make sure it can be interpreted safely at a later time. There's simply no way to mark the file with a timestamp without the risk of it being arbitrarily modified by file commands, thus screwing up the order of logfiles. Later, there was the request to alternatively append a timestamp instead of a date pattern, to use apache logging tools that will probably access the logfiles directly anyway. This ended up in the log_filename GUC variable. Righ, pg_logdir_ls() was the function. My feeling is that the application has access to the log_directory and log_filename values and can better and move flexibly filter pg_ls_dir() on the client end than we can do on the server. It just seemed like something that we better done outside the server. Outside the server means pure SQL, if you don't want to drop psql as client. So how would your query to display all all available _logfiles_ look like? You'd need to check for a valid date, besides interpreting pg_strfime's patterns. Doesn't sound exactly like fun, but I'm keen to see how your equivalent to SELECT *, pg_file_length(filename) AS len FROM pg_logdir_ls looks like. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: So how would your query to display all all available _logfiles_ look like? I think it's perfectly reasonable to assume that all the files in the log directory are logfiles --- more so than assuming that the admin hasn't exercised his option to change the log filename pattern, anyway. I also don't have a problem with using the file mod times to sort them. ... until you copy the database cluster. See discussion from last year. Regards, Andreas ---(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] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote: I don't assume people using psql will care about the current log files --- Hm. Probably because you think these users will have direct file access? Which in turn means they can edit *.conf directly too and don't need an interface for that either. it would be something done in C or another application language. Aren't the file names already ordered based on their file names, given the default pattern, postgresql-%Y-%m-%d_%H%M%S.log? The issue is _filtering_, not ordering. Since the log directory might be directed to a different location, non-pgsql logfiles might be there too. You'd probably won't expect to retrieve these files over a pgsql connection. Regards, Andreas ---(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] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote: I don't see how listing the log files relates to editing the confuration files. Both are remote administration. While we've seen the discussion that one aspect (config file editing) should be performed in psql, you assume the other aspect (viewing the logfile) to be not interesting. Your argumentation doesn't seem consequent to me. it would be something done in C or another application language. Aren't the file names already ordered based on their file names, given the default pattern, postgresql-%Y-%m-%d_%H%M%S.log? The issue is _filtering_, not ordering. Since the log directory might be directed to a different location, non-pgsql logfiles might be there too. You'd probably won't expect to retrieve these files over a pgsql connection. Well, if they mix log files and non-log files in the same directory, we would have to filter based on the log_filename directive in the application, or use LIKE in a query. .. which is what pg_logdir_ls does. And it's robust against filenames that don't have valid dates too; imagine postgresql-2005-01-01_crash1.log. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Bruce Momjian wrote:
Dave Page wrote:
The only part I didn't like about the patch is the stat display:
test= select pg_file_stat('postgresql.conf');
pg_file_stat
-
(12287,2005-08-11 00:06:30,2005-08-11 00:06:43,2005-08-11
00:06:30,f)
(1 row)
Shouldn't this return multiple labeled columns rather than an array?
pg_show_all_settings output is equally unreadable, designed not to be
used directly.
Regards,
Andreas
---(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] [HACKERS] For review: Server instrumentation patch
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 01 August 2005 03:26 To: Dave Page Cc: PostgreSQL-patches Subject: Re: [HACKERS] For review: Server instrumentation patch Dave Page wrote: [Resent as the list seems to have rejected yesterdays attempt] As per Bruce's request, here's a copy of Andreas' server instrumentation patch for review. I've separated out the dbsize stuff and pg_terminate_backend is also not included. This version was generated against CVS today. As far as I can tell from review of comments made back to pre-8.0, all security and other concerns raised have been addressed. Here is a modified version of your patch that adds functions to do configuration file reload, and log file rotation. OK, thanks. Are there any objections to adding pg_dir_ls() and pg_file_read() which will allow us to look at the log directory, and the logfiles themselves? pg_dir_ls isn't necessary for reading the logfiles; pg_logdir_ls will do this. Regards, Andreas ---(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] [HACKERS] For review: Server instrumentation patch
Dave Page wrote: pg_dir_ls isn't necessary for reading the logfiles; pg_logdir_ls will do this. Err, yes, sorry - that was a thinko. The list isn't complete. pgadmin uses these three functions for logfile tracking: - pg_logdir_ls to list logfiles - pg_file_length to check for changes of the current logfile - pg_file_read to retrieve a logfile Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
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). From a user's view, a trigger implementing RI isn't a trigger but an implementation detail he shouldn't need to care about. So for std triggers, owner check should be sufficient, requiring superuser for RI triggers only. This impacts EN/DISABLE TRIGGER ALL too. To touch RI triggers as well, an additional keyword is needed. Regards, Andreas ---(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: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: Andreas Pflug wrote: Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 02 July 2005 21:30 To: Bruce Momjian Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development Subject: Re: [PATCHES] Dbsize backend integration Is a new version of this patch coming? Yup, attached. Per our earlier conversation, pg_dbfile_size() now returns the size of a table or index, and pg_relation_size() returns the total size of a relation and all associated indexes and toast tables etc. pg_relation_size's name is quite unfortunate, since the 8.0 contrib function does something different. And pg_dbfile_size sounds misleading, suggesting it takes a filename or relfilenode as parameter. Hmm. I don't see how we can call it pg_table_size because people think of tables and indexes, while relation has a more inclusive suggestion. We could, taking the same logic as GRANT which uses the keyword TABLE for sequences and Indexes too, but it's certainly not favourable. As far as pg_dbfile_size, do you have any other idea for a name? To me, it returns the size of the 'db file' associated with the heap/index/toast. How about pg_relation_size(oid, bool) with the second optional parameter to count all additional objects too (the 'total' flag). Regards, Andreas ---(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 backend integration
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 02 July 2005 21:30 To: Bruce Momjian Cc: Dave Page; PostgreSQL-patches; PostgreSQL-development Subject: Re: [PATCHES] Dbsize backend integration Is a new version of this patch coming? Yup, attached. Per our earlier conversation, pg_dbfile_size() now returns the size of a table or index, and pg_relation_size() returns the total size of a relation and all associated indexes and toast tables etc. pg_relation_size's name is quite unfortunate, since the 8.0 contrib function does something different. And pg_dbfile_size sounds misleading, suggesting it takes a filename or relfilenode as parameter. Regards, Andreas ---(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] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
Satoshi Nagayasu wrote: 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. It would be convenient if all triggers could be disabled with a single command. More precise: option 1: All triggers except for RI triggers (EN/DISABLE TRIGGER ALL) option 2: really all triggers including RI triggers (superuser only) Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: Yea, but then we have toast and we would need another name. I suggested pg_storage_size() because it relates to a storage unit (index, toast, etc), and not a real object or relation. I'm not really happy that all functions change their names (more versioning handling in pgadmin), but pg_storage_size is certainly the most precise name. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] default database creation with initdb
As per discussion on -hackers the attached patch creates the 'default'
database at initdb time as a default target for initial connections to
keep template1 free from connections and available as template source.
I consider this DB a system object, so it's created before
make_template0 sets the last_system_oid (wondering why template0 isn't
considered a system db too)
Regards,
Andreas
Index: src/bin/initdb/initdb.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -u -r1.83 initdb.c
--- src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 - 1.83
+++ src/bin/initdb/initdb.c 18 Jun 2005 08:37:16 -
@@ -177,6 +177,7 @@
static void set_info_version(void);
static void setup_schema(void);
static void vacuum_db(void);
+static void make_default(void);
static void make_template0(void);
static void trapsig(int signum);
static void check_ok(void);
@@ -1828,6 +1829,38 @@
}
/*
+ * copy template1 to pg_system
+ */
+static void
+make_default(void)
+{
+ PG_CMD_DECL;
+ char **line;
+ static char *pg_system_setup[] = {
+ CREATE DATABASE \default\;\n,
+ REVOKE CREATE,TEMPORARY ON DATABASE \default\ FROM
public;\n,
+ NULL
+ };
+
+ fputs(_(copying template1 to default ... ), stdout);
+ fflush(stdout);
+
+ snprintf(cmd, sizeof(cmd),
+\%s\ %s template1 %s,
+backend_exec, backend_options,
+DEVNULL);
+
+ PG_CMD_OPEN;
+
+ for (line = pg_system_setup; *line; line++)
+ PG_CMD_PUTS(*line);
+
+ PG_CMD_CLOSE;
+
+ check_ok();
+}
+
+/*
* copy template1 to template0
*/
static void
@@ -2606,6 +2639,8 @@
vacuum_db();
+ make_default();
+
make_template0();
if (authwarning != NULL)
---(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] default database creation with initdb
Magnus Hagander wrote:
Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)
:-)
Regards,
Andreas
Index: src/bin/initdb/initdb.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -u -r1.83 initdb.c
--- src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 - 1.83
+++ src/bin/initdb/initdb.c 18 Jun 2005 08:54:07 -
@@ -177,6 +177,7 @@
static void set_info_version(void);
static void setup_schema(void);
static void vacuum_db(void);
+static void make_default(void);
static void make_template0(void);
static void trapsig(int signum);
static void check_ok(void);
@@ -1828,6 +1829,38 @@
}
/*
+ * copy template1 to default
+ */
+static void
+make_default(void)
+{
+ PG_CMD_DECL;
+ char **line;
+ static char *default_setup[] = {
+ CREATE DATABASE \default\;\n,
+ REVOKE CREATE,TEMPORARY ON DATABASE \default\ FROM
public;\n,
+ NULL
+ };
+
+ fputs(_(copying template1 to default ... ), stdout);
+ fflush(stdout);
+
+ snprintf(cmd, sizeof(cmd),
+\%s\ %s template1 %s,
+backend_exec, backend_options,
+DEVNULL);
+
+ PG_CMD_OPEN;
+
+ for (line = default_setup; *line; line++)
+ PG_CMD_PUTS(*line);
+
+ PG_CMD_CLOSE;
+
+ check_ok();
+}
+
+/*
* copy template1 to template0
*/
static void
@@ -2606,6 +2639,8 @@
vacuum_db();
+ make_default();
+
make_template0();
if (authwarning != NULL)
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PATCHES] Server instrumentation
= PG_GETARG_OID(0);
PG_RETURN_INT64(calculate_database_size(dbOid));
}
/* Calculate relation size given tablespace and relation OIDs */
static int64
calculate_relation_size(Oid tblspcOid, Oid relnodeOid)
{
int64 totalsize=0;
unsigned int segcount=0;
char dirpath[MAXPGPATH];
char pathname[MAXPGPATH];
if (!tblspcOid)
tblspcOid = MyDatabaseTableSpace;
if (tblspcOid == DEFAULTTABLESPACE_OID)
snprintf(dirpath, MAXPGPATH, %s/base/%u, DataDir, MyDatabaseId);
else if (tblspcOid == GLOBALTABLESPACE_OID)
snprintf(dirpath, MAXPGPATH, %s/global, DataDir);
else
snprintf(dirpath, MAXPGPATH, %s/pg_tblspc/%u/%u,
DataDir, tblspcOid, MyDatabaseId);
for (segcount = 0 ;; segcount++)
{
struct stat fst;
if (segcount == 0)
snprintf(pathname, MAXPGPATH, %s/%u,
dirpath, relnodeOid);
else
snprintf(pathname, MAXPGPATH, %s/%u.%u,
dirpath, relnodeOid, segcount);
if (stat(pathname, fst) 0)
{
if (errno == ENOENT)
break;
else
ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not stat \%s\: %m, pathname)));
}
totalsize += fst.st_size;
}
return totalsize;
}
/*
* calculate size of relation
*/
Datum
pg_relation_size(PG_FUNCTION_ARGS)
{
Oid relOid=PG_GETARG_OID(0);
HeapTuple tuple;
Form_pg_class pg_class;
Oid relnodeOid;
Oid tblspcOid;
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
0, 0, 0);
if (!HeapTupleIsValid(tuple))
ereport(ERROR,
(ERRCODE_UNDEFINED_TABLE,
errmsg(relation with OID %u does not exist, relOid)));
pg_class = (Form_pg_class) GETSTRUCT(tuple);
relnodeOid = pg_class-relfilenode;
tblspcOid = pg_class-reltablespace;
ReleaseSysCache(tuple);
PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
}
/*
* formatting with size units
*/
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
int64 size=PG_GETARG_INT64(0);
char *result=palloc(50+VARHDRSZ);
int64 limit = 10*1024;
int64 mult=1;
if (size limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT bytes,
size);
else
{
mult *= 1024;
if (size limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT kB,
(size+mult/2) / mult);
else
{
mult *= 1024;
if (size limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT MB,
(size+mult/2) / mult);
else
{
mult *= 1024;
if (size limit*mult)
snprintf(VARDATA(result), 50, INT64_FORMAT GB,
(size+mult/2) / mult);
else
{
mult *= 1024;
snprintf(VARDATA(result), 50, INT64_FORMAT TB,
(size+mult/2) / mult);
}
}
}
}
VARATT_SIZEP(result) = strlen(VARDATA(result)) + VARHDRSZ;
PG_RETURN_TEXT_P(result);
}
/*-
*
* genfile.c
*
*
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* Author: Andreas Pflug [EMAIL PROTECTED]
*
* IDENTIFICATION
* $PostgreSQL: $
*
*-
*/
#include postgres.h
#include sys/file.h
#include sys/stat.h
#include unistd.h
#include dirent.h
#include utils/builtins.h
#include miscadmin.h
#include storage/fd.h
#include catalog/pg_type.h
#include funcapi.h
#ifdef WIN32
/* we don't want *nix emulating retry stuff here, but the native behaviour */
#ifdef rename
#undef rename
#endif
#ifdef unlink
#undef unlink
#endif
#endif
extern char *Log_directory;
typedef struct
{
char *location;
DIR *dirdesc;
} directory_fctx;
/*---
* some helper functions
*/
/*
* Return an absolute path. Argument may be absolute or
* relative to the DataDir.
*/
static char *absClusterPath(text *arg, bool logAllowed)
{
char *filename;
int len=VARSIZE(arg) - VARHDRSZ;
int dlen = strlen(DataDir);
filename = palloc(len+1);
memcpy(filename, VARDATA(arg), len);
filename[len] = 0;
if (strstr(filename, ..) != NULL)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg(No .. allowed in filenames;
if (is_absolute_path(filename))
{
if (logAllowed !strncmp(filename, Log_directory, strlen(Log_directory)))
return filename;
if (strncmp(filename, DataDir, dlen))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg(Absolute path not allowed;
return filename;
}
else
{
char *absname = palloc(dlen+len+2);
sprintf(absname, %s/%s, DataDir, filename);
pfree(filename);
return absname;
}
}
/*
* check for superuser, bark if not.
*/
static void
requireSuperuser(void)
{
if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg(only superuser may access generic file functions;
}
/*
* generic file handling functions
*/
Datum pg_file_stat(PG_FUNCTION_ARGS)
{
AttInMetadata *attinmeta = NULL;
char * filename = absClusterPath(PG_GETARG_TEXT_P(0), true);
struct stat fst;
int64
Re: [PATCHES] Server instrumentation
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: pg_terminate_backend() exposing kill -SIGTERM backendpid to the client The objections to this have not changed since last year; in fact they are stronger because we have at least one report of actual trouble with retail SIGTERMs. I'm not arguing about that, still SIGTERMing a single backend is widely used. pg_file_stat() pg_file_length() pg_file_read() pg_file_write() pg_file_unlink() pg_file_rename() pg_dir_ls() I really have serious doubts about the value of this. You're insisting on this exceptionally weak argument for a year now. We all know that you personally do everything from the cmd line, but there *is* a requirement to have additional ways of access to config files. I do have wide positive feedback on this, see the discussion a month back (and some more private mails). And besides, they are already widely in use by pgadmin from the win32 pgsql distro when displaying logfiles, which is installed by default. Regards, Andreas ---(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] Server instrumentation: pg_terminate_backend, pg_reload_conf
Bruce Momjian wrote: Andreas Pflug wrote: Bruce Momjian wrote: Andreas Pflug wrote: This patch reenables pg_terminate_backend, allowing (superuser only, of course) to terminate a backend. As taken from the discussion some weeks earlier, SIGTERM seems to be used quite widely, without a report of misbehavior so while the code path is officially not too well tested, in practice it's working ok and helpful. I thought we had a discussion that the places we accept SIGTERM might be places that can exit if the postmaster is shutting down, but might not be places we can exit if the postmaster continues running, e.g. holding locks. Have you checked all the places we honor SIGTERM to check that we are safe to exit? I know Tom had concerns about that. My patch is purely to enable a supervisor to issue a SIGTERM using a pgsql client, instead of doing it from a server command line. It's not meant to fix the underlying problems. We don't support sending SIGTERM from the server command line to individual backends, so why add support for it in SQL? I don't want to slip into discussion whether it's good to SIGTERM a backend or not, it is in use. So drop it if you don't like clients to have the same facilities as console users. BTW, I got a lot of other instrumentation stuff pending, which I originally wanted to post one by one to allow individual discussion but I'm running out of time for feature freeze. Apparently I'll have to post all at once. Regards, Andreas ---(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
[PATCHES] dbsize backend integration
As a start for a bunch of instrumentation functions that should be included in the backend as discussed previously, here are the dbsize functions. The dbsize.c file should go to the usual place, src/backend/utils/adt. Regards, Andreas ? GNUmakefile ? config.log ? config.status ? dbsize-backend.patch ? runcfg ? contrib/admin ? contrib/postgis-1.0.0-rc3 ? doc/src/sgml-8.0 ? doc/src/sgml/admin.html ? doc/src/sgml/app-clusterdb.html ? doc/src/sgml/app-createdb.html ? doc/src/sgml/app-createlang.html ? doc/src/sgml/app-createuser.html ? doc/src/sgml/app-dropdb.html ? doc/src/sgml/app-droplang.html ? doc/src/sgml/app-dropuser.html ? doc/src/sgml/app-ecpg.html ? doc/src/sgml/app-initdb.html ? doc/src/sgml/app-ipcclean.html ? doc/src/sgml/app-pg-ctl.html ? doc/src/sgml/app-pg-dumpall.html ? doc/src/sgml/app-pgconfig.html ? doc/src/sgml/app-pgcontroldata.html ? doc/src/sgml/app-pgdump.html ? doc/src/sgml/app-pgresetxlog.html ? doc/src/sgml/app-pgrestore.html ? doc/src/sgml/app-postgres.html ? doc/src/sgml/app-postmaster.html ? doc/src/sgml/app-psql.html ? doc/src/sgml/app-vacuumdb.html ? doc/src/sgml/applevel-consistency.html ? doc/src/sgml/arrays.html ? doc/src/sgml/auth-methods.html ? doc/src/sgml/backup-file.html ? doc/src/sgml/backup-online.html ? doc/src/sgml/backup.html ? doc/src/sgml/bki-commands.html ? doc/src/sgml/bki-example.html ? doc/src/sgml/bki-structure.html ? doc/src/sgml/bki.html ? doc/src/sgml/bookindex.sgml ? doc/src/sgml/bug-reporting.html ? doc/src/sgml/catalog-pg-aggregate.html ? doc/src/sgml/catalog-pg-am.html ? doc/src/sgml/catalog-pg-amop.html ? doc/src/sgml/catalog-pg-amproc.html ? doc/src/sgml/catalog-pg-attrdef.html ? doc/src/sgml/catalog-pg-attribute.html ? doc/src/sgml/catalog-pg-cast.html ? doc/src/sgml/catalog-pg-class.html ? doc/src/sgml/catalog-pg-constraint.html ? doc/src/sgml/catalog-pg-conversion.html ? doc/src/sgml/catalog-pg-database.html ? doc/src/sgml/catalog-pg-depend.html ? doc/src/sgml/catalog-pg-description.html ? doc/src/sgml/catalog-pg-group.html ? doc/src/sgml/catalog-pg-index.html ? doc/src/sgml/catalog-pg-inherits.html ? doc/src/sgml/catalog-pg-language.html ? doc/src/sgml/catalog-pg-largeobject.html ? doc/src/sgml/catalog-pg-listener.html ? doc/src/sgml/catalog-pg-namespace.html ? doc/src/sgml/catalog-pg-opclass.html ? doc/src/sgml/catalog-pg-operator.html ? doc/src/sgml/catalog-pg-proc.html ? doc/src/sgml/catalog-pg-rewrite.html ? doc/src/sgml/catalog-pg-shadow.html ? doc/src/sgml/catalog-pg-statistic.html ? doc/src/sgml/catalog-pg-tablespace.html ? doc/src/sgml/catalog-pg-trigger.html ? doc/src/sgml/catalog-pg-type.html ? doc/src/sgml/catalogs.html ? doc/src/sgml/charset.html ? doc/src/sgml/client-authentication-problems.html ? doc/src/sgml/client-authentication.html ? doc/src/sgml/client-interfaces.html ? doc/src/sgml/connect-estab.html ? doc/src/sgml/creating-cluster.html ? doc/src/sgml/datatype-binary.html ? doc/src/sgml/datatype-bit.html ? doc/src/sgml/datatype-boolean.html ? doc/src/sgml/datatype-character.html ? doc/src/sgml/datatype-datetime.html ? doc/src/sgml/datatype-geometric.html ? doc/src/sgml/datatype-money.html ? doc/src/sgml/datatype-net-types.html ? doc/src/sgml/datatype-oid.html ? doc/src/sgml/datatype-pseudo.html ? doc/src/sgml/datatype.html ? doc/src/sgml/datetime-appendix.html ? doc/src/sgml/datetime-keywords.html ? doc/src/sgml/datetime-units-history.html ? doc/src/sgml/ddl-alter.html ? doc/src/sgml/ddl-constraints.html ? doc/src/sgml/ddl-default.html ? doc/src/sgml/ddl-depend.html ? doc/src/sgml/ddl-inherit.html ? doc/src/sgml/ddl-others.html ? doc/src/sgml/ddl-priv.html ? doc/src/sgml/ddl-schemas.html ? doc/src/sgml/ddl-system-columns.html ? doc/src/sgml/ddl.html ? doc/src/sgml/disk-full.html ? doc/src/sgml/diskusage.html ? doc/src/sgml/dml-delete.html ? doc/src/sgml/dml-update.html ? doc/src/sgml/dml.html ? doc/src/sgml/ecpg-commands.html ? doc/src/sgml/ecpg-connect.html ? doc/src/sgml/ecpg-descriptors.html ? doc/src/sgml/ecpg-develop.html ? doc/src/sgml/ecpg-disconnect.html ? doc/src/sgml/ecpg-dynamic.html ? doc/src/sgml/ecpg-errors.html ? doc/src/sgml/ecpg-include.html ? doc/src/sgml/ecpg-library.html ? doc/src/sgml/ecpg-process.html ? doc/src/sgml/ecpg-set-connection.html ? doc/src/sgml/ecpg-variables.html ? doc/src/sgml/ecpg.html ? doc/src/sgml/encryption-options.html ? doc/src/sgml/errcodes-appendix.html ? doc/src/sgml/error-message-reporting.html ? doc/src/sgml/error-style-guide.html ? doc/src/sgml/examples.html ? doc/src/sgml/executor.html ? doc/src/sgml/explicit-joins.html ? doc/src/sgml/explicit-locking.html ? doc/src/sgml/extend-type-system.html ? doc/src/sgml/extend.html ? doc/src/sgml/extensibility.html ? doc/src/sgml/features-supported.sgml ? doc/src/sgml/features-unsupported.sgml ? doc/src/sgml/features.html ? doc/src/sgml/functions-admin.html ? doc/src/sgml/functions-aggregate.html ? doc/src/sgml/functions-array.html ? doc/src/sgml/functions-binarystring.html ? doc/src/sgml/functions-bitstring.html ?
[PATCHES] Server instrumentation: pg_terminate_backend, pg_reload_conf
This patch reenables pg_terminate_backend, allowing (superuser only, of
course) to terminate a backend. As taken from the discussion some weeks
earlier, SIGTERM seems to be used quite widely, without a report of
misbehaviour so while the code path is officially not too well tested,
in practice it's working ok and helpful.
pg_reload_conf is a client-side issued SIGHUP, shouldn't provoke too
much problems.
Regards,
Andreas
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.250
diff -u -r1.250 func.sgml
--- doc/src/sgml/func.sgml 23 May 2005 01:50:01 - 1.250
+++ doc/src/sgml/func.sgml 1 Jun 2005 20:49:09 -
@@ -8860,6 +8860,12 @@
indexterm zone=functions-admin
primarypg_cancel_backend/primary
/indexterm
+ indexterm zone=functions-admin
+primarypg_terminate_backend/primary
+ /indexterm
+ indexterm zone=functions-admin
+primarypg_reload_conf/primary
+ /indexterm
indexterm zone=functions-admin
primarysignal/primary
@@ -8889,17 +8895,46 @@
entrytypeint/type/entry
entryCancel a backend's current query/entry
/row
+ row
+ entry
+literalfunctionpg_terminate_backend/function(parameterpid/parameter)/literal
+/entry
+ entrytypeint/type/entry
+ entryTerminate a backend process/entry
+ /row
+ row
+ entry
+literalfunctionpg_reload_conf/function(parameter/parameter)/literal
+/entry
+ entrytypeint/type/entry
+ entryTriggers the server processes to reload configuration files/entry
+ /row
/tbody
/tgroup
/table
para
-This function returns 1 if successful, 0 if not successful.
+These functions return 1 if successful, 0 if not successful.
The process ID (literalpid/literal) of an active backend can be found
from the structfieldprocpid/structfield column in the
structnamepg_stat_activity/structname view, or by listing the commandpostgres/command
processes on the server with applicationps/.
/para
+ para
+Terminating a backend with functionpg_terminate_backend/
+should be used only as a last resort, i.e. if the backend process
+doesn't react to functionpg_cancel_backend/ any more and can't
+be controlled otherwise. Since the exact state of the
+backend at the moment of termination isn't precisely known, some
+locked resources might remain in the server's shared memory
+structure, effectively blocking other backends. In this case,
+you'd have to stop and restart the postmaster.
+ /para
+ para
+functionpg_reload_conf/ sends a SIGHUP event to the
+postmaster, and thus triggers a reload of the configuration files
+in all backend processes.
+ /para
indexterm zone=functions-admin
primarypg_start_backup/primary
@@ -8970,6 +9005,83 @@
For details about proper usage of these functions, see
xref linkend=backup-online.
/para
Index: src/backend/utils/adt/misc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/misc.c,v
retrieving revision 1.43
diff -u -r1.43 misc.c
--- src/backend/utils/adt/misc.c 19 May 2005 21:35:47 - 1.43
+++ src/backend/utils/adt/misc.c 1 Jun 2005 20:49:13 -
@@ -101,22 +101,40 @@
return 1;
}
+
Datum
pg_cancel_backend(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGINT));
}
-#ifdef NOT_USED
-
-/* Disabled in 8.0 due to reliability concerns; FIXME someday */
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM));
}
-#endif
+
+
+Datum
+pg_reload_conf(PG_FUNCTION_ARGS)
+{
+ if (!superuser())
+ ereport(ERROR,
+(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg(only superuser can signal the postmaster;
+
+ if (kill(PostmasterPid, SIGHUP))
+ {
+ ereport(WARNING,
+(errmsg(failed to send signal to postmaster: %m)));
+
+ PG_RETURN_INT32(0);
+ }
+
+ PG_RETURN_INT32(1);
+}
+
/* Function to find out which databases make use of a tablespace */
Index: src/include/catalog/pg_proc.h
===
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.363
diff -u -r1.363 pg_proc.h
--- src/include/catalog/pg_proc.h 20 May 2005 01:29:55 - 1.363
+++ src/include/catalog/pg_proc.h 1 Jun 2005 20:49:31 -
@@ -3016,12 +3016,16 @@
DESCR(is conversion visible in search path?);
+DATA(insert OID = 2168 ( pg_terminate_backend PGNSP PGUID 12 f f t f v 1 23 23 _null_ _null_ _null_ pg_terminate_backend - _null_ ));
+DESCR(Terminate a server process);
DATA(insert OID = 2171 ( pg_cancel_backend PGNSP PGUID 12 f f t f v 1 23 23 _null_ _null_ _null_ pg_cancel_backend - _null_ ));
DESCR(Cancel a server process' current query);
[PATCHES] was: BUG #1466: syslogger issues
Magnus Hagander wrote:
There is special code in the send_message_to_server_log
function to make
sure it's written directly to the file.
If the logger is complaining, it's quite possibly because it's
unable to
write to its file. Now that you mention it, doesn't this code go into
infinite recursion if write_syslogger_file_binary() tries to ereport?
Yes, apparently.
Actually, elog.c code should look like this:
if ((Log_destination LOG_DESTINATION_STDERR) ...)
{
if (am_syslogger)
write_syslogger_file(buf.data, buf.len);
else
fwrite(buf.data, 1, buf.len, stderr);
}
This avoids unnecessary pipe traffic (which might fail too) and gettext
translation.
Next, the elog call in write_syslogger_file_binary will almost certainly
loop, so it should call write_stderr then (since eventlog is usually
fixed-size with cyclic writing, even in out-of-disk-space conditions
something might get logged).
3rd, I've been proposing to have redirect_stderr=true on by default at
least on win32 earlier, I still think this is reasonable.
Regards,
Andresa
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] dbsize patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: 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' Your suggestion would be more compelling if the example were correct ;-). Consider more than one index on the same table. Hopefully SUM() will do the job. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] dbsize patch
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' Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PATCHES] pg_get_viewdef returns one paren too much
[EMAIL PROTECTED] noticed a problem with pg_get_viewdef
in prettyprint mode.
create table gnrcitm (gnrcitmid int);
create table gnrcitmothrref (idntfyrefid int, gnrcitmid int);
create table other_ref(idntfyrefid int, catnmeclssid text, actvle text);
CREATE OR REPLACE VIEW test_view AS
SELECT or0.actvle AS treename
FROM gnrcitm g
LEFT JOIN (gnrcitmothrref g0
JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid::text = 'Tree Name'::text) or0
ON g.gnrcitmid = or0.gnrcitmid;
pg_get_viewdef(viewoid, true) will return
CREATE OR REPLACE VIEW test_view AS
SELECT or0.actvle AS treename
FROM gnrcitm g
LEFT JOIN
( --
(gnrcitmothrref g0
JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid = 'Tree Name'::text) or0
) --
ON g.gnrcitmid = or0.gnrcitmid;
The attached patch corrects this, without affecting the following:
CREATE OR REPLACE VIEW test_view2 AS
SELECT r0.actvle AS treename
FROM gnrcitm g
LEFT JOIN (gnrcitmothrref g0
JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid::text = 'Tree Name'::text)
ON g.gnrcitmid = g0.gnrcitmid
Regards,
Andreas
Index: ruleutils.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.185
diff -u -r1.185 ruleutils.c
--- ruleutils.c 5 Nov 2004 19:16:11 - 1.185
+++ ruleutils.c 10 Dec 2004 13:41:17 -
@@ -3876,7 +3876,8 @@
bool need_paren_on_right;
need_paren_on_right = PRETTY_PAREN(context)
- !IsA(j-rarg, RangeTblRef);
+ !IsA(j-rarg, RangeTblRef)
+ !(IsA(j-rarg, JoinExpr) ((JoinExpr*)j-rarg)-alias != NULL);
if (!PRETTY_PAREN(context) || j-alias != NULL)
appendStringInfoChar(buf, '(');
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PATCHES] htmlhelp generation
The attached Makefile patch together with stylesheet-hh.xsl allows make htmlhelp. stylesheet-hh.xsl is derived from stylesheet.xsl, after some advise from PeterE. The result isn't perfect, but quite usable. Regards, Andreas Index: Makefile === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/Makefile,v retrieving revision 1.72 diff -c -r1.72 Makefile *** Makefile 9 Mar 2004 20:10:10 - 1.72 --- Makefile 22 Nov 2004 17:31:29 - *** *** 215,220 --- 215,223 testxml: stylesheet.xsl postgres.xml $(XSLTPROC) $(XSLTPROCFLAGS) --stringparam pg.version '$(VERSION)' $^ + htmlhelp: stylesheet-hh.xsl postgres.xml + $(XSLTPROC) $(XSLTPROCFLAGS) --stringparam pg.version '$(VERSION)' $^ + ## ## Check ?xml version='1.0'? xsl:stylesheet xmlns:xsl=http://www.w3.org/1999/XSL/Transform; version='1.0' xmlns=http://www.w3.org/TR/xhtml1/transitional; exclude-result-prefixes=#default xsl:import href=http://docbook.sourceforge.net/release/xsl/current/htmlhelp/htmlhelp.xsl/ !-- Parameters -- xsl:param name=htmlhelp.use.hhk select='1'/ xsl:param name=pg.fast select='0'/ !-- xsl:param name=draft.mode xsl:choose xsl:when test=contains($pg.version, 'devel')yes/xsl:when xsl:otherwiseno/xsl:otherwise /xsl:choose /xsl:param -- xsl:param name=show.comments xsl:choose xsl:when test=contains($pg.version, 'devel')1/xsl:when xsl:otherwise0/xsl:otherwise /xsl:choose /xsl:param xsl:param name=callout.graphics select='0'/xsl:param xsl:param name=toc.section.depth2/xsl:param xsl:param name=linenumbering.extension select='0'/xsl:param xsl:param name=generate.index select=1 - $pg.fast/xsl:param xsl:param name=preface.autolabel select=1 - $pg.fast/xsl:param xsl:param name=section.autolabel select=1 - $pg.fast/xsl:param xsl:param name=section.label.includes.component.label select=1 - $pg.fast/xsl:param xsl:param name=html.stylesheet select='stylesheet.css'/xsl:param xsl:param name=use.id.as.filename select='1'/xsl:param xsl:param name=make.valid.html select=1/xsl:param xsl:param name=generate.id.attributes select=1/xsl:param xsl:param name=generate.legalnotice.link select=1/xsl:param xsl:param name=refentry.xref.manvolnum select=0/ xsl:param name=link.mailto.url[EMAIL PROTECTED]/xsl:param xsl:param name=formal.procedures select=0/xsl:param xsl:param name=punct.honorific select=''/xsl:param xsl:param name=chunker.output.indent select='yes'/ xsl:param name=chunk.quietly select=1/xsl:param !-- Change display of some elements -- xsl:template match=command xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=productname xsl:call-template name=inline.charseq/ /xsl:template xsl:template match=structfield xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=structname xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=symbol xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=systemitem xsl:call-template name=inline.charseq/ /xsl:template xsl:template match=token xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=type xsl:call-template name=inline.monoseq/ /xsl:template xsl:template match=programlisting/emphasis xsl:call-template name=inline.boldseq/ /xsl:template !-- Special support for Tcl synopses -- xsl:template match=[EMAIL PROTECTED]'tcl'] ?xsl:call-template name=inline.charseq/? /xsl:template !-- Format multiple terms in varlistentry vertically, instead of comma-separated. -- xsl:template match=varlistentry/term[position()!=last()] span class=term xsl:call-template name=anchor/ xsl:apply-templates/ /spanbr/ /xsl:template /xsl:stylesheet ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] New Translation
Peter Eisentraut wrote: Tom Lane wrote: Nonetheless, it would also be good to have some consistency between the core PG server and related projects. I tend to agree that we should honor pgadmin's precedent here; it's not a strong argument but the argument for fa over fa_IR seems even weaker. fa_IR would uselessly restrict the range of users that would be able to use this translation. Translations are primarily targeted for a language, not a country. If wxWidgets is broken, that's not our problem. Please note that wxWidgets also depends on available locales. On Debian, locale -a returns fa_IR only, and guessing that from fa would be quite painful. OTOH, usual locale algorithms would strip the country fa_IR if not found. Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] New Translation
[EMAIL PROTECTED] wrote: hello, we are starting a new language translation in postgresql. I send the pg_controldata message translation in Farsi language. I sended it to the Peter Eisentraut with the name fa_IR.po. He said that Is there any use of fa outside of IR? Else I would just call the translation files fa.po. there are some countries that uses this language. I see that there is a farsi translation in pgadmin and you named it fa_IR. This isn't comparable. In pgadmin, we're bound to the canonical names defined in wxWidgets, which is fa_IR (and there's no other Farsi version). Thus it seems reasonable to use .fa only if you have a choice. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [pgsql-hackers-win32] VC++ psql build broken
Bruce Momjian wrote: How does your Win32 system rename prototype differ from what is in port.h? What is the need of *any* special file handling functions for client tools? Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [pgsql-hackers-win32] VC++ psql build broken
Bruce Momjian wrote: Andreas Pflug wrote: Bruce Momjian wrote: How does your Win32 system rename prototype differ from what is in port.h? What is the need of *any* special file handling functions for client tools? We could avoid it but it does give us Unix semantics so it seemed good to keep it if we could. As you can see it creates much pain, without any use. The functions are for backend concurrent/shared access purposes only. Regards, Andreas ---(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] [pgsql-hackers-win32] Contrib modules on Win32
Dave Page wrote:
cube
seg
patch attached. Compiles, but not tested.
miscutil
Needs review; includes some deprecated stuff (backend_pid)
pg_logger
deprecated; use redirect_stderr (BTW, is it default on win32 now?)
pgcrypto
misses -lws2_32. According to README, it needs some tuning concerning
random() before deploying.
Regards,
Andreas
Index: cube.c
===
RCS file: /projects/cvsroot/pgsql-server/contrib/cube/cube.c,v
retrieving revision 1.16
diff -u -r1.16 cube.c
--- cube.c 29 Aug 2004 05:06:34 - 1.16
+++ cube.c 8 Sep 2004 17:45:32 -
@@ -15,8 +15,12 @@
#include cubedata.h
+#ifndef max
#define max(a,b) ((a) (b) ? (a) : (b))
+#endif
+#ifndef min
#define min(a,b) ((a) = (b) ? (a) : (b))
+#endif
#define abs(a) ((a) (0) ? (-a) : (a))
extern int cube_yyparse();
Index: cubeparse.y
===
RCS file: /projects/cvsroot/pgsql-server/contrib/cube/cubeparse.y,v
retrieving revision 1.11
diff -u -r1.11 cubeparse.y
--- cubeparse.y 2 Sep 2004 20:53:42 - 1.11
+++ cubeparse.y 8 Sep 2004 17:45:43 -
@@ -28,7 +28,7 @@
%}
/* BISON Declarations */
-%token FLOAT O_PAREN C_PAREN O_BRACKET C_BRACKET COMMA
+%token CUBEFLOAT O_PAREN C_PAREN O_BRACKET C_BRACKET COMMA
%start box
/* Grammar follows */
@@ -128,13 +128,13 @@
;
list:
- FLOAT {
+ CUBEFLOAT {
/* alloc enough space to be sure whole list will fit */
$$ = palloc(scanbuflen + 1);
strcpy($$, $1);
}
|
- list COMMA FLOAT {
+ list COMMA CUBEFLOAT {
$$ = $1;
strcat($$, ,);
strcat($$, $3);
Index: cubescan.l
===
RCS file: /projects/cvsroot/pgsql-server/contrib/cube/cubescan.l,v
retrieving revision 1.8
diff -u -r1.8 cubescan.l
--- cubescan.l 24 Feb 2004 22:06:32 - 1.8
+++ cubescan.l 8 Sep 2004 17:45:50 -
@@ -39,7 +39,7 @@
%%
-{float} yylval = yytext; return FLOAT;
+{float} yylval = yytext; return CUBEFLOAT;
\[ yylval = (; return O_BRACKET;
\] yylval = ); return C_BRACKET;
\( yylval = (; return O_PAREN;
Index: seg.c
===
RCS file: /projects/cvsroot/pgsql-server/contrib/seg/seg.c,v
retrieving revision 1.10
diff -u -r1.10 seg.c
--- seg.c 29 Aug 2004 05:06:38 - 1.10
+++ seg.c 8 Sep 2004 17:58:00 -
@@ -14,8 +14,12 @@
#include segdata.h
+#ifndef max
#define max(a,b) ((a) (b) ? (a) : (b))
+#endif
+#ifndef min
#define min(a,b) ((a) = (b) ? (a) : (b))
+#endif
#define abs(a) ((a) (0) ? (-a) : (a))
/*
@@ -946,7 +950,7 @@
if (exp == 0)
{
/* use the supplied mantyssa with sign */
- strcpy((char *) index(result, 'e'), );
+ strcpy((char *) strchr(result, 'e'), );
}
else
{
Index: segscan.l
===
RCS file: /projects/cvsroot/pgsql-server/contrib/seg/segscan.l,v
retrieving revision 1.7
diff -u -r1.7 segscan.l
--- segscan.l 24 Feb 2004 22:06:32 - 1.7
+++ segscan.l 8 Sep 2004 17:58:20 -
@@ -41,7 +41,7 @@
{range} yylval.text = yytext; return RANGE;
{plumin} yylval.text = yytext; return PLUMIN;
-{float} yylval.text = yytext; return FLOAT;
+{float} yylval.text = yytext; return SEGFLOAT;
\ yylval.text = ; return EXTENSION;
\ yylval.text = ; return EXTENSION;
\~ yylval.text = ~; return EXTENSION;
Index: segparse.y
===
RCS file: /projects/cvsroot/pgsql-server/contrib/seg/segparse.y,v
retrieving revision 1.12
diff -u -r1.12 segparse.y
--- segparse.y 2 Sep 2004 20:53:42 - 1.12
+++ segparse.y 8 Sep 2004 17:59:28 -
@@ -38,7 +38,7 @@
} bnd;
char * text;
}
-%token text FLOAT
+%token text SEGFLOAT
%token text RANGE
%token text PLUMIN
%token text EXTENSION
@@ -105,13 +105,13 @@
;
boundary:
- FLOAT {
+ SEGFLOAT {
$$.ext = '\0';
$$.sigd = significant_digits($1);
$$.val = seg_atof($1);
}
|
- EXTENSION FLOAT {
+ EXTENSION SEGFLOAT {
$$.ext = $1[0];
$$.sigd = significant_digits($2);
$$.val = seg_atof($2);
@@ -119,7 +119,7 @@
;
deviation:
- FLOAT {
+ SEGFLOAT {
$$.ext = '\0';
$$.sigd = significant_digits($1);
$$.val = seg_atof($1);
* CVS exited normally with code 1 *
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] dbsize contrib
Bruce Momjian wrote:
Patch applied. Thanks.
Can I get some documentation in the README for all the new
functionality.
Here it is.
Regards,
Andreas
Index: README.dbsize
===
RCS file: /projects/cvsroot/pgsql-server/contrib/dbsize/README.dbsize,v
retrieving revision 1.2
diff -u -r1.2 README.dbsize
--- README.dbsize 23 Jun 2002 20:09:23 - 1.2
+++ README.dbsize 2 Sep 2004 08:28:10 -
@@ -1,15 +1,51 @@
-This module contains two functions that report the size of a given
-database or relation. E.g.,
+This module contains several functions that report the size of a given
+database object.
+
+int8 database_size(name)
+int8 relation_size(text)
+int8 pg_database_size(oid)
+int8 pg_tablespace_size(oid)
+int8 pg_relation_size(oid)
+text pg_size_pretty(int8)
+
+These functions come in two flavours. The old style takes the name of the
+object, and supports databases and tables. These where the only functions
+supported for PostgreSQL up to 7.4.x.
SELECT database_size('template1');
SELECT relation_size('pg_class');
-These functions report the actual file system space. Thus, users can
-avoid digging through the details of the database directories.
+Please note that for relation_size() only the pure table file usage is
+computed, not the space used by indexes and toast tables.
+
+Starting with PostgreSQL 8.0, additional functions taking the oid of the
+object where added.
+
+SELECT pg_database_size(1); -- template1 database
+SELECT pg_tablespace_size(1663); -- pg_default tablespace
+SELECT pg_relation_size(1259); -- pg_class table size
+
+pg_relation_size will report the size of for table, index and toast table
+OIDs, but won't add them automatically. To obtain the total size of a table
+including all helper files you'd have to do something like
+
+SELECT pg_relation_size(cl.oid) AS tablesize,
+ CASE WHEN reltoastrelid=0 THEN 0
+ELSE pg_relation_size(reltoastrelid) END AS toastsize,
+ SUM(pg_relation_size(indexrelid)) AS indexsize,
+ pg_size_pretty(pg_relation_size(cl.oid)
++ pg_relation_size(reltoastrelid)
++ SUM(pg_relation_size(indexrelid))::int8) AS totalsize
+ FROM pg_class cl
+ JOIN pg_index ON cl.oid=indrelid
+ WHERE relname = 'pg_rewrite'
+ GROUP BY 1,2
+
+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, TB.
+It is also contained in this module.
+
Copy this directory to contrib/dbsize in your PostgreSQL source tree.
Then just run make; make install. Finally, load the functions into any
database using dbsize.sql.
-
-When computing the size of a table, it does not include TOAST or index
-disk space.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: I don't have the time now to review the impact, but this might make interpreting the log filename difficult or impossible, effectively corrupting pg_logdir_ls. So if you want to use that, you use a format that it can cope with. you is the backend, which should be able to interpret what it wrote. I don't see a problem. Yes, you don't see a problem if the logfile can't be displayed on the client, I know that. My primary intention for contributing *any* logfile related stuff was to make it available through admin interfaces, and this goal seems to get obstructed in any possible way. Anybody volunteering to fix the pg_logdir_ls code at http://cvs.pgadmin.org/cgi-bin/viewcvs.cgi/pgadmin-tools/support/misc.c?rev=HEAD which should have been in the backend right from the start? What about misbehaving size rotation if the filename isn't unique? And what's a non human readable name.epoch pattern default good for? Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] dbsize contrib
Gavin Sherry wrote: The attached patch contributes: - database_size(name) - relation_size(text) I sent in a dbsize patch to make these functions tablespace aware... AFAIR your patch was applied, but it misses tables in non-default tablespaces. Regards, Andreas ---(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()
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: I can see the value of not needing any cron daemon to remove old logs. No other logs on your system to purge? The DBA isn't necessarily also root. Interesting this argument comes from you.. :-) Tasks like purging old log files is certainly not a job that needs to be implemented in the backend; instead, an external database maintenance agent should do that. Such an agent (pgadmin TODO list working title: pgAgent, there was a lengthy discussion Scheduled jobs starting 2004-05-12), allowing scheduled sql scripts, would delete old log files using select pg_file_unlink(filename) from pg_logdir_ls where filetime now() - '8 days'::interval *if* this functionality isn't corrupted by arbitrary selectable file name formatting. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
Bruce Momjian wrote: Are we going to change this before beta2? I have not seen a final patch yet. Can we have pg_logdir_ls in the backend first so any related changes to the log filename are reflected in both places? Otherwise displaying the logfile on the client continues to be a moving target. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] dbsize contrib
The current implementation of dbsize doesn't handle tables in
tablespaces correctly, and is quite restricted on objects covered (only
tables and databases, but not tablespaces and indexes).
The attached patch contributes:
- database_size(name)
- relation_size(text)
These are the well-known functions, tablespace-aware.
- pg_tablespace_size(oid)
- pg_database_size(oid)
- pg_relation_size(oid)
Tablespace-aware implementations, used by the upper functions.
pg_relation_size will report sizes of indexes as well.
- pg_size_pretty(bigint)
Formatting of sizes, to display '146MB' instead of '152885668'
Regards,
Andreas
? dbsize.diff
? dbsize.sql
Index: dbsize.c
===
RCS file: /projects/cvsroot/pgsql-server/contrib/dbsize/dbsize.c,v
retrieving revision 1.12
diff -u -r1.12 dbsize.c
--- dbsize.c 29 Aug 2004 05:06:35 - 1.12
+++ dbsize.c 29 Aug 2004 10:12:11 -
@@ -1,157 +1,285 @@
+/*
+ * dbsize.c
+ * object size functions
+ *
+ * Copyright (c) 2004, PostgreSQL Global Development Group
+ *
+ * Author: Andreas Pflug [EMAIL PROTECTED]
+ *
+ * IDENTIFICATION
+ * $PostgreSQL: $
+ *
+ */
+
+
#include postgres.h
#include sys/types.h
#include sys/stat.h
-#include unistd.h
#include access/heapam.h
-#include catalog/catalog.h
-#include catalog/catname.h
+#include storage/fd.h
+#include utils/syscache.h
+#include utils/builtins.h
#include catalog/namespace.h
#include catalog/pg_tablespace.h
#include commands/dbcommands.h
-#include fmgr.h
-#include storage/fd.h
-#include utils/builtins.h
+#include miscadmin.h
-static int64
- get_tablespace_size(Oid dbid, Oid spcid, bool baddirOK);
+extern DLLIMPORT char *DataDir;
-static char *
-psnprintf(size_t len, const char *fmt,...)
-{
- va_list ap;
- char *buf;
+Datum pg_tablespace_size(PG_FUNCTION_ARGS);
+Datum pg_database_size(PG_FUNCTION_ARGS);
+Datum pg_relation_size(PG_FUNCTION_ARGS);
+Datum pg_size_pretty(PG_FUNCTION_ARGS);
- buf = palloc(len);
+Datum database_size(PG_FUNCTION_ARGS);
+Datum relation_size(PG_FUNCTION_ARGS);
- va_start(ap, fmt);
- vsnprintf(buf, len, fmt, ap);
- va_end(ap);
+PG_FUNCTION_INFO_V1(pg_tablespace_size);
+PG_FUNCTION_INFO_V1(pg_database_size);
+PG_FUNCTION_INFO_V1(pg_relation_size);
+PG_FUNCTION_INFO_V1(pg_size_pretty);
- return buf;
-}
+PG_FUNCTION_INFO_V1(database_size);
+PG_FUNCTION_INFO_V1(relation_size);
-/*
- * SQL function: database_size(name) returns bigint
- */
+static int64
+db_dir_size(char *path)
+{
+int64 dirsize=0;
+struct dirent *direntry;
+ DIR *dirdesc;
+ char filename[MAXPGPATH];
-PG_FUNCTION_INFO_V1(database_size);
+ dirdesc=AllocateDir(path);
-Datum database_size(PG_FUNCTION_ARGS);
+ if (!dirdesc)
+ return 0;
-Datum
-database_size(PG_FUNCTION_ARGS)
-{
- Name dbname = PG_GETARG_NAME(0);
+ while ((direntry = readdir(dirdesc)) != 0)
+ {
+ struct stat fst;
- Oid dbid;
- int64 totalsize;
+ if (!strcmp(direntry-d_name, .) || !strcmp(direntry-d_name, ..))
+ continue;
-#ifdef SYMLINK
- Relation dbrel;
- HeapScanDesc scan;
- HeapTuple tuple;
-#endif
+ snprintf(filename, MAXPGPATH, %s/%s, path, direntry-d_name);
- dbid = get_database_oid(NameStr(*dbname));
- if (!OidIsValid(dbid))
- ereport(ERROR,
-(errcode(ERRCODE_UNDEFINED_DATABASE),
- errmsg(database \%s\ does not exist, NameStr(*dbname;
+ if (stat(filename, fst) 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg(could not stat \%s\: %m, filename)));
+ dirsize += fst.st_size;
+ }
+
+ FreeDir(dirdesc);
+ return dirsize;
+}
-#ifdef SYMLINK
- dbrel = heap_openr(TableSpaceRelationName, AccessShareLock);
- scan = heap_beginscan(dbrel, SnapshotNow, 0, (ScanKey) NULL);
+static int64
+calculate_database_size(Oid dbOid)
+{
+ int64 totalsize=0;
+ DIR *dirdesc;
+struct dirent *direntry;
+ char pathname[MAXPGPATH];
+
+ snprintf(pathname, MAXPGPATH, %s/global/%u, DataDir, (unsigned)dbOid);
+ totalsize += db_dir_size(pathname);
+ snprintf(pathname, MAXPGPATH, %s/base/%u, DataDir, (unsigned)dbOid);
+ totalsize += db_dir_size(pathname);
+
+ snprintf(pathname, MAXPGPATH, %s/pg_tblspc, DataDir);
+ dirdesc = AllocateDir(pathname);
- totalsize = 0;
+ if (!dirdesc)
+ ereport(ERROR,
+(errcode_for_file_access(),
+ errmsg(could not open tablespace directory: %m)));
- while ((tuple = heap_getnext(scan, ForwardScanDirection)))
+ while ((direntry = readdir(dirdesc)) != 0)
{
- Oid spcid = HeapTupleGetOid(tuple);
+ if (!strcmp(direntry-d_name, .) || !strcmp(direntry-d_name, ..))
+ continue;
- if (spcid != GLOBALTABLESPACE_OID)
- totalsize += get_tablespace_size(dbid, spcid, true);
+ snprintf(pathname, MAXPGPATH, %s/pg_tblspc/%s/%u, DataDir, direntry-d_name, (unsigned)dbOid);
+ totalsize += db_dir_size(pathname);
}
- heap_endscan(scan);
- heap_close(dbrel, AccessShareLock);
-#else
- /* Same as always */
- totalsize = get_tablespace_size(dbid, DEFAULTTABLESPACE_OID, false
Re: [PATCHES] Win32 bug fix
Maksim Likharev wrote: Compiled and tried, seems much better, no more WAIT_FAIL errors. but I have some questions: 1. Is there a any possibility to assign server log file ( option -l,--log ) when PG runs as a service, I wasn't able to do that, dump everything into stderror or eventlog just not convenient. Use redirect_stderr. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] log_filename_prefix -- log_filename + strftime()
Jan Wieck wrote: but allows to setup a configuration that automatically overwrites files in a rotating manner, if the DBA so desires. ... which can't work because it will overwrite the logfile on server start, and thus will overwrite the very latest logfile when performing multiple restarts. We had discussions how to identify a logfile's start time, and agreed that the file's creation/modification time can *not* be used for that. That's why the name has a fixed and well-known timestamp format. Regards, Andreas ---(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()
Tom Lane wrote: at logger startup or size-based rotation, the rule would be to append. which then has a problem when you startup the postmaster after 10 hours of downtime ... hmmm. Doesn't seem like a big problem --- at worst that logfile will get to be double the size it normally would. ... continuing log entries with a time gap... Note that this scheme effectively disables size-based rotation anyway, unless you use one of the hacks we talked about like using a %H:%M pattern when you intend hourly rotation. Please no colons in filenames (win32!) Regards, Andreas ---(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()
Tom Lane wrote: It's definitely creeping featurism ... but I can see the value of not needing any cron daemon to remove old logs. No other logs on your system to purge? 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 :-(. Same can happen after logger process restart. After all, I wonder what an apache style logfile name is good for. The pgsql logfiles don't contain access log data that are analyzed by webalizer or stuff like that. I don't see the point having any formatting option at all. Regards, Andreas ---(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()
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. I don't have the time now to review the impact, but this might make interpreting the log filename difficult or impossible, effectively corrupting pg_logdir_ls. I don't object against adjusting the timestamp format in a reasonable way, but it should stay fixed; same about PID. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Compiling libpq with VC6
Bruce Momjian wrote: The problem with this approach is that it has us using the non-reliable libc rename/unlink rather than our own in libpq. Not really. The backend will still use the reliable pg_ functions. Frontends continue to use the libc functions, which are totally sufficient in non-concurrent access situations. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Compiling libpq with VC6
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Andreas Pflug wrote: Not really. The backend will still use the reliable pg_ functions. Frontends continue to use the libc functions, which are totally sufficient in non-concurrent access situations. I would like to keep full Unix semantics for these functions even in libpq, if possible. libpq doesn't use either rename or unlink AFAIK. However, if we make the patch be something involving #ifdef FRONTEND, then it will affect other code that might want to use those. One thing that hasn't been real clear to me is why this only affects fe-lobj.c, when we have many other files that include io.h after including postgres.h. Quite simple: mingw includes do *not* conflict. Only a minority of tools is object to VC6 compilation, ultimately only libpq is needed. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Compiling libpq with VC6
Tom Lane wrote: Hm. Given that we now support a native Windows port, do we care about building libpq with VC6 anymore? Yes please! I just tried: I renamed libpq.a to libpq.lib. pgAdmin links with that, but will crash. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [pgsql-hackers-win32] libpq build problem with io.h
Bruce, I posted the attached patch 4 days ago, with the comment The attached patch will redefine unlink and rename only if FRONTEND is not defined.. I still believe this a good way to fix it. Tom Lane wrote: To put that in a more positive light: we like to think that our code is Posix-compliant and runs in a Posix-compliant environment. We're not thrilled about introducing non-Posix-isms for the convenience of one platform ... especially if there's no easy way to enforce that the nonstandard coding convention be used. Back on track: if rename() does exist under Windows then my idea is unreliable. Any other thoughts? How about #including io.h in port.h (for Windows only of course) before we #define these things? Probably won't work, because pgrename and rename do not have the same definition/linkage. Regards, Andreas Index: port.h === RCS file: /projects/cvsroot/pgsql-server/src/include/port.h,v retrieving revision 1.52 diff -u -r1.52 port.h --- port.h 12 Aug 2004 18:32:43 - 1.52 +++ port.h 13 Aug 2004 15:58:19 - @@ -141,7 +141,7 @@ extern int pclose_check(FILE *stream); -#if defined(WIN32) || defined(__CYGWIN__) +#if (defined(WIN32) || defined(__CYGWIN__)) !defined(FRONTEND) /* * Win32 doesn't have reliable rename/unlink during concurrent access, * and we need special code to do symlinks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Win32 Event log
Dave Page wrote: Dave Page [EMAIL PROTECTED] writes: The attached patch directs FATAL and PANIC elog's to the event log as well as their normal destination. I don't think this is a good idea. In the first place, FATAL errors are not necessarily serious or out-of-the-ordinary --- an example is that all authorization errors are FATAL. OK, I could live with just panics. Logging auth failures will be interesting for admins too. This could indicate an ongoing attack. I would keep FATAL. In the second place, the proposed patch deliberately subverts what the DBA has set as the logging output parameters. I dislike software that knows better than I do what I want and is willing to ignore what I told it to do on those grounds. Logging like this is fairly normal on Windows. Applications may maintain their own (often verbose) logfiles, however more serious errors get directed to the event log as well. This allows automated monitoring of servers to be achieved for example. It must be stressed that win32 eventlog behaves very different from linux syslog. And what the DBA wants to know, is not necessarily what the sys admin (domain admin) wants to know. Frankly, i doubt that plain eventlog logging will be used widely in the presence of redirect_stderr (and tools to read them); the behaviour is too non-windowish. One possible solution would be to use our own event log which is possible in 2K+, (but not NT). This is very uncommon, even for MS software. AFAICS only system software (intrinsic to win32) does so. I wonder how many eventlog monitoring programs already know about that possibility... A patch that would be more in the spirit of Postgres is to allow different min_log_level values for the different possible log destinations (stderr, syslog, eventlog). However that looks a lot like a new feature to me, so maybe it will have to wait for 8.1. Yes, that would work, though as you say it's a new feature. No doubt, the best solution. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] libpq problem
Andreas Pflug wrote:
Some recent change in libpq seems to interfere with gtk.
After I tested a new pgadmin3 version on linuy yesterday, I found that
the GUI is hanging after PQconnectdb was called. After the call, the db
connection is fully functional, but the GUI mouse will show waiting
and the program doesn't react to mouse clicks any more; screen updates
are not performed either.
When I replace the 8.0 libpq.so* version with an older saved version
(7.4.3 from debian installation) it works ok.
OK, I found out. Seems I didn't run make distclean for a longer time, so
I didn't realize earlier.
The reason is the sigpipe handling code. If the app (in this case: some
gtk internals) already installed a SIGPIPE handler, the thread_in_send
key is not created. pthread_setspecific calls will thus use an invalid
key, which screws up gtk.
The attached patch will implement two features:
1) unconditionally create thread_in_send
2) Always register our own SIGPIPE handler, chain to a previously
registered handler when the signal is thrown while not sending.
Regards,
Andreas
Index: fe-secure.c
===
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.45
diff -u -r1.45 fe-secure.c
--- fe-secure.c 12 Jul 2004 14:23:28 - 1.45
+++ fe-secure.c 11 Aug 2004 12:49:35 -
@@ -153,6 +153,7 @@
#ifdef ENABLE_THREAD_SAFETY
static void sigpipe_handler_ignore_send(int signo);
pthread_key_t thread_in_send;
+static pqsigfunc pipehandler;
#endif
/* */
@@ -1190,23 +1191,14 @@
void
check_sigpipe_handler(void)
{
- pqsigfunc pipehandler;
-
/*
* If the app hasn't set a SIGPIPE handler, define our own
* that ignores SIGPIPE on libpq send() and does SIG_DFL
* for other SIGPIPE cases.
*/
+ pthread_key_create(thread_in_send, NULL);
pipehandler = pqsignalinquire(SIGPIPE);
- if (pipehandler == SIG_DFL) /* not set by application */
- {
- /*
- * Create key first because the signal handler might be called
- * right after being installed.
- */
- pthread_key_create(thread_in_send, NULL);
- pqsignal(SIGPIPE, sigpipe_handler_ignore_send);
- }
+ pqsignal(SIGPIPE, sigpipe_handler_ignore_send);
}
/*
@@ -1221,7 +1213,12 @@
* that caused the signal.
*/
if (!PQinSend())
- exit(128 + SIGPIPE); /* typical return value for SIG_DFL */
+ {
+ if (pipehandler == SIG_DFL) /* not set by application */
+ exit(128 + SIGPIPE); /* typical return value for SIG_DFL */
+ else
+ (*pipehandler)(signo); /* call original handler */
+ }
}
#endif
#endif
---(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] Win32 tablespace
[EMAIL PROTECTED] wrote: (create/drop tablespace works too) I can *not* confirm this; after configure; make clean; make; make install I got tablespace not supported. pg_config.h lacks HAVE_SYMLINK=1. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Win32 tablespace
Dave Page wrote: This is obviously win2k+ only though - There's a chance it might work on NT4 with =SP4, because AFAIR the updated NTFS driver already understands the W2K format. until now we've tried to support NT4 as well, although it seems that we can't get initdb to work as we'd like in the installer on that platform. Should we officially support 2K+ only? Since NT4 isn't supported by M$ for quite some time now, it seems reasonable to mark it as does run with issues, but not officially supported. The installer would have to skip initdb, and display a lengthy read-carefully-readme how to continue manually. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] logger subprocess including win32
Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] writes:
Attached the patch, an orgy in #ifdefs, decorated with various indents
and crlf line ends (glad we have pgindent).
I spent a fair amount of time fooling with this, trying to extract
something that I trusted enough to apply at this late date, but got
stuck on one point. Exiting when the postmaster dies is *not* good
enough; we want the logger to stick around until the last process
upstream of the logger pipe is gone. In the Unix case we can detect
this by watching for EOF on the pipe,
I saw strange errnos coming from that pipe, i.e. EMFILE. I'm not sure if
EOF is really reliable.
but I don't know how to do the equivalent in this threaded scheme
you've devised for Windows.
if (realStdErr !0 NULL)
{
...
}
#ifdef WIN32
CloseHandle(writePipe);
#else
close(syslogPipe[1]);
#endif
You probably found out yourself.
In pipeThread:
if (!ReadFile(...))
{
DWORD error = GetLastError();
if (error == ERROR_HANDLE_EOF)
exit(0);
/* errno is not set */
ereport(COMERROR,
errmsg(could not read from system logger pipe: %d, error)))}
}
(Why is the separate thread needed, again?)
On unnamed pipes, WaitForSingleObject does not work (it always reports
signaled, so the blocking ReadFile won't allow for
sighup/IsPostmasterRunning; select is for sockets only).
Regards,
Andreas
---(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] logger subprocess including win32
Tom Lane wrote:
if (!ReadFile(...))
{
DWORD error = GetLastError();
if (error == ERROR_HANDLE_EOF)
exit(0);
Got it. And there's no reason that the pipe thread can't do exit(0)
for itself?
Not really. All threads are equivalent.
BTW, should there be a last NOTICE syslogger shutting down?
Regards,
Andreas
---(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
[PATCHES] logger subprocess including win32
and eventlog, depending on
-# platform.
+#log_destination = 'stderr' # Valid values are combinations of stderr, file,
+# syslog and eventlog, depending on platform.
+#log_directory = 'pg_log' # subdirectory where logfiles are written
+# if 'file' log_destination is used.
+# May be specified absolute or relative to PGDATA
+#log_filename_prefix = 'postgresql_' # prefix for logfile names
+#log_rotation_age = 1440# Automatic rotation of logfiles will happen if
+# specified age in minutes is reached. 0 to disable.
+#log_rotation_size = 10240 # Automatic rotation of logfiles will happen if
+# specified size in kb is reached. 0 to disable.
+
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
Index: include/storage/pmsignal.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/storage/pmsignal.h,v
retrieving revision 1.9
diff -u -r1.9 pmsignal.h
--- include/storage/pmsignal.h 19 Jul 2004 02:47:15 - 1.9
+++ include/storage/pmsignal.h 4 Aug 2004 18:40:21 -
@@ -25,7 +25,7 @@
PMSIGNAL_PASSWORD_CHANGE, /* pg_pwd file has changed */
PMSIGNAL_WAKEN_CHILDREN, /* send a SIGUSR1 signal to all backends */
PMSIGNAL_WAKEN_ARCHIVER, /* send a NOTIFY signal to xlog archiver */
-
+ PMSIGNAL_ROTATE_LOGFILE, /* send SIGUSR1 to syslogger to rotate logfile */
NUM_PMSIGNALS/* Must be last value of enum! */
} PMSignalReason;
Index: elog.h===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/elog.h,v
retrieving revision 1.72
diff -u -r1.72 elog.h
--- elog.h 31 Jul 2004 23:04:55 - 1.72
+++ elog.h 4 Aug 2004 18:45:49 -
@@ -278,6 +278,7 @@
#define LOG_DESTINATION_STDERR 1
#define LOG_DESTINATION_SYSLOG 2
#define LOG_DESTINATION_EVENTLOG 4
+#define LOG_DESTINATION_FILE 8
/* Other exported functions */
extern void DebugFileOpen(void);
/*-
*
* syslogger.c
*
* The system logger (syslogger) is new in Postgres 7.5. It catches all
* stderr output from backends, the postmaster and subprocesses by
* redirecting to a pipe, and writes it to a logfile and stderr if
* configured.
* It's possible to have size and age limits for the logfile configured
* in postgresql.conf. If these limits are reached or passed, the
* current logfile is closed and a new one is created (rotated).
* The logfiles are stored in a subdirectory (configurable in
* postgresql.conf), using an internal naming scheme that mangles
* creation time and current postmaster pid.
*
* Author: Andreas Pflug [EMAIL PROTECTED]
*
* Copyright (c) 2004, PostgreSQL Global Development Group
*
*
* IDENTIFICATION
* $PostgreSQL: $
*
*-
*/
#include postgres.h
#include signal.h
#include time.h
#include unistd.h
#include libpq/pqsignal.h
#include miscadmin.h
#include postmaster/postmaster.h
#include storage/pmsignal.h
#include storage/pg_shmem.h
#include storage/ipc.h
#include postmaster/syslogger.h
#include utils/ps_status.h
#include utils/guc.h
/*
* GUC parameters
*/
int Log_RotationAge = 24*60;
int Log_RotationSize = 10*1024;
char * Log_directory = pg_log;
char * Log_filename_prefix = postgresql-;
extern pid_t SysLoggerPID;
/*
* Flags set by interrupt handlers for later service in the main loop.
*/
static volatile sig_atomic_t got_SIGHUP = false;
static volatile sig_atomic_t rotation_requested = false;
#define MAXRETRIES 3
static pg_time_t last_rotation_time = 0;
static char currentLogDir[MAXPGPATH];
static void sigHupHandler(SIGNAL_ARGS);
static void rotationHandler(SIGNAL_ARGS);
void writeLogfile(char *buffer, int count);
#ifdef EXEC_BACKEND
static pid_t syslogger_forkexec(void);
#endif
static char* logfile_getname(pg_time_t timestamp);
static bool logfile_rotate(void);
FILE *realStdErr = NULL;
volatile FILE *syslogFile = NULL;
#ifdef WIN32
static unsigned int __stdcall pipeThread(void *arg);
static HANDLE readPipe=0, writePipe=0;
static HANDLE threadHandle=0;
static CRITICAL_SECTION sysfileSection;
#else
static int syslogPipe[2] = {0, 0};
#endif
void
writeLogfile(char *buffer, int count)
{
int rc;
#ifdef WIN32
EnterCriticalSection(sysfileSection);
rc = fwrite(buffer, 1, count, (FILE*)syslogFile);
LeaveCriticalSection(sysfileSection);
#else
rc = fwrite(buffer, 1, count, (FILE*)syslogFile);
#endif
if (rc 1)
{
ereport(COMMERROR,
(errcode_for_file_access(),
errmsg(fwrite to logfile failed in system logger: %m)));
exit(1);
}
if (Log_destination LOG_DESTINATION_STDERR)
{
if (realStdErr
Re: [PATCHES] Admin functions contrib
Bruce Momjian wrote: Do people want the server file logging/rotating patch applied if it is Unix-only? Right now the patch is ifdef'ed so Win32 use of it is disabled. Andreas is asking. Please commit ASAP. Is I stated several times, I'll do the win32 as soon as I get a chance to. It's not a logger file issue, it's a *win32 stderr* problem. If you believe that a feature must support all systems, tablespace must be removed as well. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Admin functions contrib
Peter Eisentraut wrote: Bruce Momjian wrote: Do people want the server file logging/rotating patch applied if it is Unix-only? Right now the patch is ifdef'ed so Win32 use of it is disabled. How is logging typically handled on Windows? It is done using the eventlog service (which is supported as replacement for syslog now) or in case of MSSQL as file logging. MSSQL in more detail: In eventlog only the most important MSSQL messages will appear (start/stop, PANIC), while the rest is done in logfiles (retrievable over client tools, rotatable using sp_cycle_errorlog) and may grow much larger than eventlog (which will receive messages from all apps/services). This handling is so throughout M$ apps, like webserver etc: fatals to eventlog, the rest to files. Regards, Andreas ---(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] Admin functions contrib
Tom Lane wrote: Do people want the server file logging/rotating patch applied if it is Unix-only? Right now the patch is ifdef'ed so Win32 use of it is disabled. I'm slightly worried that we might be painting ourselves into a corner, ie implementing functionality that will never work on Windows. Personally, of course, I won't care if it never works on Windows. But I suspect there are some out there who do care ;-). It might be better to wait till we're sure there's a reasonable implementation path for Windows. Actually, I believe the implementation I did first (having all processes append to the logfile themselves) would have worked for win32 too. As long as you don't impose linux-centric limitations on win32 implementations, there certainly *are* solutions to the problem. A very reasonable way would be to have the win32_signal_waiter thread not only wait for the child terminating, but also checking the pipe. This is certainly *the* recommended win32 way. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Admin functions contrib
Dave Page wrote: As Bruce has seen, this is some pretty nice functionality that Andreas has added to pga3, and is one of the few areas that we lag behind SQL Server etc. in on the management front. If you're curious what Bruce has seen, it was this: http://www.pse-consulting.de/pgadmin3/pgadmin3-serverlog.png The size functions will offer this: http://www.pse-consulting.de/pgadmin3/pgadmin3-tblspc-stat.png Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Admin functions contrib
Bruce Momjian wrote: I talked to Tom about this today. First, I want to apologize for running you around in circles in this. I don't think we are giving it the attention it needs because of our schedule. I also think the functionality is drifting into the new features territory and this is also part of the delay you are seeing. I think you did a great thing by breaking the patch into two parts: one for logging, and the other for log reading and other stuff. The logging part is already in the patch queue. As for the function below, I first think the security issue brough up about them wasn't a valid concern because as I stated someone could just load the plperl server-side language and do anything to the OS. In fact this might be the best solution for you. Instead of trying to code read/write/rename/unlink and other functions into the backend as hardcoded, why not just have pgadmin load plperlu and as the super-user you have access to that functionality, and much more, especially with the new plperl in 7.5. In fact, your goal of modifying the postgresql.conf file is much more natural in perl than in the API you supplied, and probably more reliable. So, I suggest we get the logging code into the backend, and you can code anything you want pgadmin to do in plperlu, and Win32 supports plperlu too. The big advantage is that you can improve the plperlu functions with every release of pgadmin. I do not agree on this. Administrative tools should require as few additional backend packages as possible. What you're proposing is simply a nightmare. Actually, IMHO all functions should be *backend* code, not contrib code, even less arbitrary loadable language functions. Certainly an external package relying on a loadable language is quite the opposite, generating lots of support issues. It won't generate trust if pgadmin documentation advises install untrusted plperl to maintain your machine. Additionally, several of the functions are by no means new, but replacements, did you notice pg_xxx_size? I posted this stuff as contrib module to keep it off the feature freeze issue. If it still can't go there, it must stay an external module which will be distributed as pgadmin add-on. Reimplementing it as plperlu is crap. Regards, Andreas ---(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] Admin functions contrib
Bruce Momjian wrote: Basically I think we are converging on an answer that we can't do any of this for 7.5. If it's not going into the distribution as contrib or core, I'll package that as additional admin pack. I'm quite sure I can convince the win32 installer packager guys to include that as default-on option as soon as I'm able to prove them how it's working. The scope has gone way beyond what we had at feature freeze, and we can't even get it to work on Win32, ??? The functions will work for win32, are you talking about logging? The win32 log issue isn't a serverlog rotation issue, as I stated also the current stderr output is affected! I'd clearly see that as a fix, whilst it might be more than 10 lines of code. I'll try to fix that tomorrow. The very log_destination=file and rotation code will be more or less the same as for ***x. I have added this to the TODO list: * Allow server logs to be read using SQL commands * Allow server configuration parameters to be modified remotetly This is desirable in any case. But until 7.6 will be around, 1-1.5 years and ???,000 installations will happen. Since the functionality is available now or will be available VSN (certainly before 7.5 release), there's no good reason suppress it. Regards, Andreas ---(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] logger subprocess
Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. Do not apply. I'm investigating issues under win32. Main issue: pgpipe doesn't create a pipe, but sockets. win32 doesn't like to redirect stderr into sockets... There's also an issue about file handles not being inherited. The file handles are there with _spawnl, but not CreateProcess (despite bInheritHandles=true). Still hunting. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] logger subprocess
This is the known patch, with following changes:
- realStdErr handed over for EXEC_BACKEND, but still not tested
- Sometimes EMFILE is received in the logger's process queue, when a
backend ended after a SSL connection was interrupted. This is ignored
now (previously it forced an exit(1) and restart of the subprocess)
- log_destination needs to be PGC_POSTMASTER, because the logger process
creation depends on that.
- no functions included
Regards,
Andreas
Index: src/backend/postmaster/Makefile
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/Makefile,v
retrieving revision 1.18
diff -u -r1.18 Makefile
--- src/backend/postmaster/Makefile 21 Jul 2004 20:34:46 - 1.18
+++ src/backend/postmaster/Makefile 27 Jul 2004 10:33:30 -
@@ -12,7 +12,7 @@
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
-OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o
+OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o syslogger.o
all: SUBSYS.o
Index: src/backend/postmaster/postmaster.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.416
diff -u -r1.416 postmaster.c
--- src/backend/postmaster/postmaster.c 27 Jul 2004 01:46:03 - 1.416
+++ src/backend/postmaster/postmaster.c 27 Jul 2004 10:33:36 -
@@ -118,7 +118,7 @@
#include utils/ps_status.h
#include bootstrap/bootstrap.h
#include pgstat.h
-
+#include postmaster/syslogger.h
/*
* List of active backends (or child processes anyway; we don't actually
@@ -201,6 +201,7 @@
BgWriterPID = 0,
PgArchPID = 0,
PgStatPID = 0;
+pid_t SysLoggerPID = 0;
/* Startup/shutdown state */
#define NoShutdown 0
@@ -852,6 +853,12 @@
#endif
/*
+ * start logging to file
+ */
+
+SysLoggerPID = SysLogger_Start();
+
+ /*
* Reset whereToSendOutput from Debug (its starting state) to None.
* This stops ereport from sending log messages to stderr unless
* Log_destination permits. We don't do this until the postmaster
@@ -1231,6 +1238,11 @@
StartupPID == 0 !FatalError Shutdown == NoShutdown)
PgStatPID = pgstat_start();
+ /* If we have lost the system logger, try to start a new one */
+ if (SysLoggerPID == 0
+ StartupPID == 0 !FatalError Shutdown == NoShutdown)
+ SysLoggerPID = SysLogger_Start();
+
/*
* Touch the socket and lock file at least every ten minutes, to ensure
* that they are not removed by overzealous /tmp-cleaning tasks.
@@ -1771,6 +1783,9 @@
kill(BgWriterPID, SIGHUP);
if (PgArchPID != 0)
kill(PgArchPID, SIGHUP);
+ if (SysLoggerPID != 0)
+ kill(SysLoggerPID, SIGHUP);
+
/* PgStatPID does not currently need SIGHUP */
load_hba();
load_ident();
@@ -1836,7 +1851,6 @@
if (PgStatPID != 0)
kill(PgStatPID, SIGQUIT);
break;
-
case SIGINT:
/*
* Fast Shutdown:
@@ -1903,6 +1917,7 @@
kill(PgStatPID, SIGQUIT);
if (DLGetHead(BackendList))
SignalChildren(SIGQUIT);
+
ExitPostmaster(0);
break;
}
@@ -2065,6 +2080,15 @@
continue;
}
+ /* was it the system logger, try to start a new one */
+ if (SysLoggerPID != 0 pid == SysLoggerPID)
+ {
+ if (exitstatus != 0)
+LogChildExit(LOG, gettext(system logger process),
+ pid, exitstatus);
+ SysLoggerPID = SysLogger_Start();
+ continue;
+ }
/*
* Else do standard backend child cleanup.
*/
@@ -2968,6 +2992,16 @@
PgstatCollectorMain(argc, argv);
proc_exit(0);
}
+ if (strcmp(argv[1], -forklog) == 0)
+ {
+ /* Close the postmaster's sockets */
+ ClosePostmasterPorts();
+
+ /* Do not want to attach to shared memory */
+
+ SysLoggerMain(argc, argv);
+ proc_exit(0);
+ }
return 1; /* shouldn't get here */
}
@@ -3024,7 +3058,6 @@
if (Shutdown = SmartShutdown)
SignalChildren(SIGUSR1);
}
-
if (PgArchPID != 0 Shutdown == NoShutdown)
{
if (CheckPostmasterSignal(PMSIGNAL_WAKEN_ARCHIVER))
@@ -3036,6 +3069,10 @@
kill(PgArchPID, SIGUSR1);
}
}
+ if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) SysLoggerPID != 0)
+ {
+ kill(SysLoggerPID, SIGUSR1);
+ }
PG_SETMASK(UnBlockSig);
Index: src/backend/utils/error/elog.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/error/elog.c,v
retrieving revision 1.142
diff -u -r1.142 elog.c
--- src/backend/utils/error/elog.c 24 Jun 2004 21:03:13 - 1.142
+++ src/backend/utils/error/elog.c 27 Jul 2004 10:33:39 -
@@ -84,6 +84,10 @@
static void write_eventlog(int level, const char *line);
#endif
+/* in syslogger.c */
+extern FILE *syslogFile;
+extern FILE *realStdErr;
+extern pid_t SysLoggerPID;
/*
* ErrorData holds the data accumulated during any one ereport() cycle.
* Any non-NULL pointers must point to palloc'd data in ErrorContext.
@@ -1451,10 +1455,31 @@
[PATCHES] Admin functions contrib
These files add administrative functions to pgsql 7.5. All are used by
pgAdmin3 or will be used in the near future if available. This is meant
as contrib module, whilst IMHO all these functions should be integrated
into the backend.
Included are functions to
log file access
These where previously posted together with the logger subprocess patch
and might get committed to the backend code.
misc.
send SIGHUP to postmaster
generic file access functions
These are more restrictive than the previously posted: Write access is
necessary for files to rename and unlink, and paths are restricted to
PGDATA and the logdir.
size functions
These are 7.5 replacements for dbsize.
Regards,
Andreas
subdir = contrib/admin
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
MODULE_big = admin
DATA_built = admin.sql
DOCS = README.admin
OBJS = size.o genfile.o misc.o
include $(top_srcdir)/contrib/contrib-global.mk
/* **
* Administrative functions
* * */
/* database object size functions (admin.c) */
CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION pg_database_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_database_size'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_relation_size'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
AS 'MODULE_PATHNAME', 'pg_size_pretty'
LANGUAGE C STABLE STRICT;
/* generic file access functions (genfile.c) */
CREATE FUNCTION pg_file_stat(text) RETURNS record
AS 'MODULE_PATHNAME', 'pg_file_stat'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION pg_file_length(text) RETURNS bigint
AS 'SELECT len FROM pg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m
timestamp, i bool)'
LANGUAGE SQL VOLATILE STRICT;
CREATE FUNCTION pg_file_read(text, bigint, bigint) RETURNS text
AS 'MODULE_PATHNAME', 'pg_file_read'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION pg_file_write(text, text, bool) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_file_write'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION pg_file_rename(text, text, text) RETURNS bool
AS 'MODULE_PATHNAME', 'pg_file_rename'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION pg_file_unlink(text) RETURNS bool
AS 'MODULE_PATHNAME', 'pg_file_unlink'
LANGUAGE C VOLATILE STRICT;
CREATE FUNCTION pg_file_rename(text, text) RETURNS bool
AS 'SELECT pg_file_rename($1, $2, NULL); '
LANGUAGE SQL VOLATILE STRICT;
CREATE FUNCTION pg_dir_ls(text, bool) RETURNS setof text
AS 'MODULE_PATHNAME', 'pg_dir_ls'
LANGUAGE C VOLATILE STRICT;
/* Miscellaneous functions (misc.c) */
CREATE FUNCTION pg_reload_conf() RETURNS int4
AS 'MODULE_PATHNAME', 'pg_reload_conf'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION pg_logfile_rotate() RETURNS bool
AS 'MODULE_PATHNAME', 'pg_logfile_rotate'
LANGUAGE C STABLE STRICT;
CREATE FUNCTION pg_logdir_ls() RETURNS setof record
AS 'MODULE_PATHNAME', 'pg_logdir_ls'
LANGUAGE C VOLATILE STRICT;
CREATE VIEW pg_logdir_ls AS
SELECT *
FROM pg_logdir_ls() AS A
(filetime timestamp, pid int4, filename text);
/*-
*
* genfile.c
*
*
* Copyright (c) 2004, PostgreSQL Global Development Group
*
* Author: Andreas Pflug [EMAIL PROTECTED]
*
* IDENTIFICATION
* $PostgreSQL: $
*
*-
*/
#include postgres.h
#include sys/file.h
#include unistd.h
#include dirent.h
#include miscadmin.h
#include storage/fd.h
#include catalog/pg_type.h
#include funcapi.h
Datum pg_file_stat(PG_FUNCTION_ARGS);
Datum pg_file_read(PG_FUNCTION_ARGS);
Datum pg_file_write(PG_FUNCTION_ARGS);
Datum pg_file_rename(PG_FUNCTION_ARGS);
Datum pg_file_unlink(PG_FUNCTION_ARGS);
Datum pg_dir_ls(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_file_stat);
PG_FUNCTION_INFO_V1(pg_file_read);
PG_FUNCTION_INFO_V1(pg_file_write);
PG_FUNCTION_INFO_V1(pg_file_rename);
PG_FUNCTION_INFO_V1(pg_file_unlink);
PG_FUNCTION_INFO_V1(pg_dir_ls);
extern char *Log_directory;
typedef struct
{
char *location;
DIR *dirdesc;
} directory_fctx;
/*---
* some helper functions
*/
/*
* Return an absolute path. Argument may be absolute or
* relative to the DataDir.
*/
static char *absClusterPath(text *arg, bool logAllowed)
{
char *filename;
int len=VARSIZE(arg) - VARHDRSZ;
int dlen = strlen(DataDir);
filename = palloc(len+1);
memcpy(filename, VARDATA(arg), len);
filename[len] = 0;
if (strstr(filename, ..) != NULL)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg(No .. allowed in filenames;
if (is_absolute_path(filename))
{
if (logAllowed !strncmp(filename
Re: [PATCHES] [HACKERS] Function to kill backend
Tom Lane wrote: If you don't mind plastering a use at your own risk sign on it, then go for it. killing a backend is obviously much more at your own risk than a descent function. Taken from your mail, I understand that a killed backend might leave some loose ends, eg. open locks, which would degrade the cluster's performance. Still, it should not corrupt the shared mem, just leave it as if the backend's still alive and sleeping, right? You'd kill a backend only if your complete cluster is suffering from it, and you hope to keep it running by just shooting that process. If the cluster still has that uncleaned locks or so, you're unlucky and need to shutdown the cluster. Maybe we should supply a restricted version of pg_terminate_backend that's callable from admin interfaces only so we can make sure that the user was warned what he's doing before the termination is executed, something like that: ticket := select pg_admin_ticket(); /* calculate well-known stuff on ticket and issue before it times out */ select pg_terminate_backend(ticket_hash); Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] logfile subprocess and Fancy File Functions
Bruce Momjian wrote:
Are we done? Seems pg_file_stat() works fine. Do we need other
adjustments?
Here are the documentation changes.
Regards,
Andreas
Index: catalogs.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.89
diff -u -r2.89 catalogs.sgml
--- catalogs.sgml 4 Jul 2004 23:34:23 - 2.89
+++ catalogs.sgml 23 Jul 2004 12:16:47 -
@@ -3855,6 +3855,11 @@
/row
row
+ entrylink linkend=view-pg-logdir-lsstructnamepg_logdir_ls/structname/link/entry
+ entrylog files in log directory/entry
+ /row
+
+ row
entrylink linkend=view-pg-rulesstructnamepg_rules/structname/link/entry
entryrules/entry
/row
@@ -3943,6 +3948,50 @@
/table
/sect1
+ sect1 id=view-pg-logdir-ls
+ titlestructnamepg_logdir_ls/structname/title
+
+ indexterm zone=view-pg-logdir-ls
+ primarypg_logdir_ls/primary
+ /indexterm
+
+ para
+ The view structnamepg_logdir_ls/structname provides access to
+log files stored in the log directory.
+ /para
+
+ table
+ titlestructnamepg_logdir_ls/ Columns/title
+
+ tgroup cols=3
+thead
+ row
+ entryName/entry
+ entryType/entry
+ entryDescription/entry
+ /row
+/thead
+tbody
+ row
+ entrystructfieldfiletime/structfield/entry
+ entrytypetimestamp/type/entry
+ entrytimestamp of log file creation/entry
+ /row
+ row
+ entrystructfieldpid/structfield/entry
+ entrytypeint4/type/entry
+ entryprocess id of postmaster that created the logfile/entry
+ /row
+ row
+ entrystructfieldfilename/structfield/entry
+ entrytypetext/type/entry
+ entryfull pathname of log file/entry
+ /row
+/tbody
+ /tgroup
+ /table
+
+ /sect1
sect1 id=view-pg-locks
titlestructnamepg_locks/structname/title
Index: func.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.214
diff -u -r1.214 func.sgml
--- func.sgml 12 Jul 2004 20:23:47 - 1.214
+++ func.sgml 23 Jul 2004 12:17:06 -
@@ -2658,8 +2658,10 @@
function fails and returns null. To indicate the part of the
pattern that should be returned on success, the pattern must contain
two occurrences of the escape character followed by a double quote
- (literal/). The text matching the portion of the pattern
+ (literal/). The text matching the portion of the pattern
between these markers is returned.
+ !-- This comment is to stop misbehaving sgml highlighting from
+ previous double qoutes --
/para
para
@@ -7455,6 +7457,41 @@
/para
indexterm zone=functions-misc
+ primarypg_logdir_ls/primary
+ /indexterm
+ indexterm zone=functions-misc
+ primarypg_logfile_rotate/primary
+ /indexterm
+ para
+The functions shown in xref linkend=functions-misc-logfile
+ deal with the server log file if configured with log_destination
+ quotefile/quote.
+ /para
+
+ table id=functions-misc-logfile
+titleServer Logfile Functions/title
+tgroup cols=3
+ thead
+ rowentryName/entry entryReturn Type/entry entryDescription/entry/row
+ /thead
+
+ tbody
+ row
+ entryliteralfunctionpg_logfile_rotate/function()/literal/entry
+ entrytypebool/type/entry
+ entryrotates the server log file/entry
+ /row
+ /tbody
+/tgroup
+/table
+para
+ functionpg_logfile_rotate/function will force the logger
+ process to rotate log files. If logging to file was not enabled
+ ('file' in literallog_destination/ configuration
+ parameter), false will be returned.
+/para
+
+ indexterm zone=functions-misc
primarypg_cancel_backend/primary
/indexterm
@@ -7463,6 +7500,10 @@
/indexterm
indexterm zone=functions-misc
+primarypg_reload_config/primary
+ /indexterm
+
+ indexterm zone=functions-misc
primarysignal/primary
secondary sortas=backendbackend processes/secondary
/indexterm
@@ -7497,6 +7538,13 @@
entrytypeint/type/entry
entryTerminate a backend process/entry
/row
+ row
+ entry
+ literalfunctionpg_reload_config/function()/literal
+ /entry
+ entrytypeint/type/entry
+ entryReload configuration from postgresql.conf/entry
+ /row
/tbody
/tgroup
/table
@@ -7508,6 +7556,196 @@
structnamepg_stat_activity/structname view, or by listing the postgres
processes on the server.
/para
+ para
+ literalfunctionpg_reload_config/function/literal will send
+a literalSIGHUP/ signal to all backends, forcing them to
+reload their configuration from literalpostgresql.conf/.
+ /para
+
+ indexterm zone=functions-misc
+primarypg_file_stat/primary
+ /indexterm
+ indexterm zone=functions-misc
+primarypg_file_length/primary
+ /indexterm
+ indexterm
Re: [PATCHES] logfile subprocess and Fancy File Functions
Bruce Momjian wrote:
Here is what you can do:
SELECT filename,
(SELECT file_len FROM pg_file_stat(filename)),
(SELECT file_ctime FROM pg_file_stat(filename)),
(SELECT file_mtime FROM pg_file_stat(filename)),
(SELECT file_atime FROM pg_file_stat(filename))
FROM pg_dir_ls('/etc') AS d (filename text...)
WHERE filename like 's%'
Not really satisfying (pg_file_stat is volatile) but subselects give the
desired result.
Are we done? Seems pg_file_stat() works fine. Do we need other
adjustments?
The only single spot where performance could be improved is in
pg_file_stat, where attinmeta is created again and again; this may be
cached in a static memory context instead.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] logfile subprocess and Fancy File Functions
Bruce Momjian wrote:
Andreas Pflug wrote:
OK, new idea. Forget about modifying pg_dir_ls(). Instead add
pg_file_stat the returns the file size, times. You can then easily use
that for file size and times. Also, if you want, add an is_dir boolean
so people can write functions that walk the directory tree.
I now replaced pg_logfile_length, instead pg_logfile_stat(text) will
return a record (len int8, ctime timestamp, atime timestamp, mtime
timestamp, isdir bool).
For convenience, I'd like to have the function
CREATE FUNCTION pg_file_length(text) RETURNS int8
AS
$BODY$
SELECT len
FROM pg_file_stat($1) AS stat
(len int8, ctime timestamp,
atime timestamp, mtime timestamp, isdir bool)
$BODY$ LANGUAGE SQL STRICT;
Where is the right place to put it?
Also, I wonder how to join pg_file_stat and pg_dir_ls to get a ls -l
like listing. Apparently I can't do that, unless I don't code pg_dir_ls
as returning records too, right?
I noticed we had a big logging discussion during 7.4 beta about logging
and log rotation. This patch is clearly superior to the ideas we had at
that time.
Currently, the discussion circles around file functions, not logging. If
you think that part is clean, how about committing it separately so it
can be tested/used (no problem if pg_logfile_rotate() isn't available
right from the start). I'll supply docs RSN.
Regards,
Andreas
Index: src/backend/catalog/system_views.sql
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/catalog/system_views.sql,v
retrieving revision 1.6
diff -u -r1.6 system_views.sql
--- src/backend/catalog/system_views.sql 26 Apr 2004 15:24:41 - 1.6
+++ src/backend/catalog/system_views.sql 21 Jul 2004 09:49:22 -
@@ -273,3 +273,8 @@
DO INSTEAD NOTHING;
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+
+CREATE VIEW pg_logdir_ls AS
+ SELECT *
+ FROM pg_logdir_ls() AS A
+ (filetime timestamp, pid int4, filename text);
Index: src/backend/postmaster/Makefile
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/Makefile,v
retrieving revision 1.16
diff -u -r1.16 Makefile
--- src/backend/postmaster/Makefile 19 Jul 2004 02:47:08 - 1.16
+++ src/backend/postmaster/Makefile 21 Jul 2004 09:49:23 -
@@ -12,7 +12,7 @@
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
-OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o
+OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o syslogger.o
all: SUBSYS.o
Index: src/backend/postmaster/postmaster.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.412
diff -u -r1.412 postmaster.c
--- src/backend/postmaster/postmaster.c 19 Jul 2004 02:47:08 - 1.412
+++ src/backend/postmaster/postmaster.c 21 Jul 2004 09:49:29 -
@@ -118,7 +118,7 @@
#include utils/ps_status.h
#include bootstrap/bootstrap.h
#include pgstat.h
-
+#include postmaster/syslogger.h
/*
* List of active backends (or child processes anyway; we don't actually
@@ -201,6 +201,7 @@
BgWriterPID = 0,
PgArchPID = 0,
PgStatPID = 0;
+pid_t SysLoggerPID = 0;
/* Startup/shutdown state */
#define NoShutdown 0
@@ -852,6 +853,12 @@
#endif
/*
+ * start logging to file
+ */
+
+SysLoggerPID = SysLogger_Start();
+
+ /*
* Reset whereToSendOutput from Debug (its starting state) to None.
* This prevents ereport from sending log messages to stderr unless
* the syslog/stderr switch permits. We don't do this until the
@@ -1230,6 +1237,11 @@
StartupPID == 0 !FatalError Shutdown == NoShutdown)
PgStatPID = pgstat_start();
+ /* If we have lost the system logger, try to start a new one */
+ if (SysLoggerPID == 0
+ StartupPID == 0 !FatalError Shutdown == NoShutdown)
+ SysLoggerPID = SysLogger_Start();
+
/*
* Touch the socket and lock file at least every ten minutes, to ensure
* that they are not removed by overzealous /tmp-cleaning tasks.
@@ -1770,6 +1782,9 @@
kill(BgWriterPID, SIGHUP);
if (PgArchPID != 0)
kill(PgArchPID, SIGHUP);
+ if (SysLoggerPID != 0)
+ kill(SysLoggerPID, SIGHUP);
+
/* PgStatPID does not currently need SIGHUP */
load_hba();
load_ident();
@@ -1835,7 +1850,6 @@
if (PgStatPID != 0)
kill(PgStatPID, SIGQUIT);
break;
-
case SIGINT:
/*
* Fast Shutdown:
@@ -1902,6 +1916,7 @@
kill(PgStatPID, SIGQUIT);
if (DLGetHead(BackendList))
SignalChildren(SIGQUIT);
+
ExitPostmaster(0);
break;
}
@@ -2059,6 +2074,15 @@
continue;
}
+ /* was it the system logger, try to start a new one */
+ if (SysLoggerPID != 0 pid == SysLoggerPID)
+ {
+ if (exitstatus != 0)
+LogChildExit(LOG, gettext(system logger process),
+ pid, exitstatus);
+ SysLoggerPID = SysLogger_Start();
+ continue;
+ }
/*
* Else do
Re: [PATCHES] logfile subprocess and Fancy File Functions
Bruce Momjian wrote: Andreas Pflug wrote: Very nice. You did a nice trick of reading the log filenames into a timestamp field: count = sscanf(de-d_name, %04d-%02d-%02d_%02d%02d%02d_%05d.log, yea$ You only process files that match that pattern for pg_logfiles_ls() (perhaps this should be pg_logdir_ls for consistency). Yup. And you can then process the timestamp field in queries. Good idea. What happens if a filename matches the above pattern but isn't a valid timestamp? Does the function fail? Right now, BuildTupleFromCString will fail for invalid timestamps. I'm going to change that to pgsql's internal function (strptime seems a bad idea though). My only question is whether we need to allow a custom prefix for the log filenames so they can be distinguished from other file names in a user-supplied log directory, like /var/log, or would they always go into a separate directory under there. I think a prefix would be nice. How should the prefix be named? pgsql_ ? Of course this needs docs but I assume you are waiting to see it applied first. Not necessarily, but I'd like names etc. fixed before. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] logfile subprocess and Fancy File Functions
Bruce Momjian wrote:
Peter Eisentraut wrote:
Bruce Momjian wrote:
Peter Eisentraut wrote:
Andreas Pflug wrote:
How should the prefix be named? pgsql_ ?
Make the file names configurable.
He has code to interpret the file names as timestamps that can be
used in queries. If we allowed full user control over the file name,
he couldn't do that.
I can't see this working. As you know, there are constantly people who
want to install and configure PostgreSQL in the weirdest ways. If we
tell everybody, you log files must be named like this, it will start
all over again.
Maybe it would be better if the time stamps of the files are used as
time stamps in queries.
Imagine an older logfile was edited with lets say emacs, which will
rename the old and create a new file. Or after log_directory was
changed, the files from the old location are copied to the new location.
This would garble the log_dir_ls output badly.
The logfilename currently also includes the postmaster's pid, there's no
file metadata that could take this information safely.
Apparently it's best to invent a log_file_prefix = 'pgsql_' guc variable.
In fact one idea would be to add new stat() columns for
creation/mod/access file times to the directory listing command.
Actually, a preliminary version of pg_dir_ls did also return some stat
data. I removed this, in favor of functions like pg_file_length.
SELECT fn, pg_file_length(fn)
FROM pg_dir_ls('/etc', true) AS fn
WHERE fn like '/etc/p%'
I certainly could supply a record-returning pg_dir_ls
(fn text, fullfn text, len int8, ctime timestamp, atime timestamp, mtime
timestamp)
Regards,
Andreas
---(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] logfile subprocess and Fancy File Functions
database oids in a tablespace);
+DATA(insert OID = 2557( pg_file_length PGNSP PGUID 12 f f t f v 1 20 25 _null_ pg_file_length - _null_ ));
+DESCR(length of generic file);
+DATA(insert OID = 2558( pg_file_read PGNSP PGUID 12 f f t f v 3 25 25 20 20 _null_ pg_file_read - _null_ ));
+DESCR(read contents of generic file);
+DATA(insert OID = 2559( pg_file_write PGNSP PGUID 12 f f t f v 3 20 25 25 16 _null_ pg_file_write - _null_ ));
+DESCR(write generic file);
+DATA(insert OID = 2560( pg_file_renamePGNSP PGUID 12 f f t f v 2 16 25 25 _null_ pg_file_rename - _null_ ));
+DESCR(rename generic file);
+DATA(insert OID = 2561( pg_file_renamePGNSP PGUID 12 f f t f v 33 16 25 25 25 _null_ pg_file_rename - _null_ ));
+DESCR(rename generic file);
+DATA(insert OID = 2562( pg_file_unlink PGNSP PGUID 12 f f t f v 1 16 25 _null_ pg_file_unlink - _null_ ));
+DESCR(remove generic file);
+DATA(insert OID = 2563( pg_dir_ls PGNSP PGUID 12 f f t t v 2 25 25 16 _null_ pg_dir_ls - _null_ ));
+DESCR(list generic directory);
+
+DATA(insert OID = 2564( pg_logfile_rotate PGNSP PGUID 12 f f t f v 0 16 _null_ pg_logfile_rotate - _null_ ));
+DESCR(rotate log file);
+DATA(insert OID = 2565( pg_logfiles_ls PGNSP PGUID 12 f f t t v 0 2249 _null_ pg_logfiles_ls - _null_ ));
+DESCR(list all available log files);
/*
* Symbolic values for provolatile column: these indicate whether the result
Index: src/include/storage/pmsignal.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/storage/pmsignal.h,v
retrieving revision 1.8
diff -u -r1.8 pmsignal.h
--- src/include/storage/pmsignal.h 29 May 2004 22:48:23 - 1.8
+++ src/include/storage/pmsignal.h 18 Jul 2004 22:14:33 -
@@ -24,6 +24,7 @@
{
PMSIGNAL_PASSWORD_CHANGE, /* pg_pwd file has changed */
PMSIGNAL_WAKEN_CHILDREN, /* send a SIGUSR1 signal to all backends */
+ PMSIGNAL_ROTATE_LOGFILE, /* send SIGUSR1 to syslogger to rotate logfile */
NUM_PMSIGNALS/* Must be last value of enum! */
} PMSignalReason;
Index: src/include/utils/builtins.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.246
diff -u -r1.246 builtins.h
--- src/include/utils/builtins.h 12 Jul 2004 20:23:59 - 1.246
+++ src/include/utils/builtins.h 18 Jul 2004 22:14:35 -
@@ -362,8 +362,20 @@
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
+extern Datum pg_logfile_rotate(PG_FUNCTION_ARGS);
+extern Datum pg_logfiles_ls(PG_FUNCTION_ARGS);
+
+extern Datum pg_file_length(PG_FUNCTION_ARGS);
+extern Datum pg_file_read(PG_FUNCTION_ARGS);
+extern Datum pg_file_write(PG_FUNCTION_ARGS);
+extern Datum pg_file_rename(PG_FUNCTION_ARGS);
+extern Datum pg_file_unlink(PG_FUNCTION_ARGS);
+
+extern Datum pg_dir_ls(PG_FUNCTION_ARGS);
+
/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
extern Datum oidnotin(PG_FUNCTION_ARGS);
Index: src/include/utils/elog.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/elog.h,v
retrieving revision 1.70
diff -u -r1.70 elog.h
--- src/include/utils/elog.h 6 Jul 2004 19:51:59 - 1.70
+++ src/include/utils/elog.h 18 Jul 2004 22:14:36 -
@@ -185,10 +185,10 @@
#define LOG_DESTINATION_STDERR 1
#define LOG_DESTINATION_SYSLOG 2
#define LOG_DESTINATION_EVENTLOG 4
+#define LOG_DESTINATION_FILE 8
/* Other exported functions */
extern void DebugFileOpen(void);
-
/*
* Write errors to stderr (or by equal means when stderr is
* not available). Used before ereport/elog can be used
/*-
*
* syslogger.c
*
* The system logger (syslogger) is new in Postgres 7.5. It catches all
* stderr output from backends, the postmaster and subprocesses by
* redirecting to a pipe, and writes it to a logfile and stderr if
* configured.
* It's possible to have size and age limits for the logfile configured
* in postgresql.conf. If these limits are reached or passed, the
* current logfile is closed and a new one is created (rotated).
* The logfiles are stored in a subdirectory (configurable in
* postgresql.conf), using an internal naming scheme that mangles
* creation time and current postmaster pid.
*
* Author: Andreas Pflug [EMAIL PROTECTED]
*
* Copyright (c) 2004, PostgreSQL Global Development Group
*
*
* IDENTIFICATION
* $PostgreSQL: $
*
*-
*/
#include postgres.h
#include signal.h
#include time.h
#include unistd.h
#include libpq/pqsignal.h
[PATCHES] logfile subprocess and Fancy File Functions
The attached patch and additional src/backend/postmaster/syslogger.c
implements the logfile subprocess as discussed.
TODO:
- documentation
- win32 code (forkexec) is included, but not tested (no build env)
Functions (all are superuser only):
int4 pg_reload_conf()
Sends SIGHUP to postmaster
bool pg_logfile_rotate()
initiates logfile rotation, same does SIGUSR1 to the syslogger
subprocess; returns true if logging is enabled
setof record pg_logfiles_ls()
lists all available logfiles, should we have a view as well?
CREATE VIEW pg_logfiles AS
SELECT ts, pid, fn
FROM pg_logfiles_ls()
AS pgls(ts timestamp, pid int4, fn text)
int8 pg_file_length(filename_text)
returns length of file, or -1 if non existent (no ERROR)
text pg_file_read(filename_text, startpos_int6, length_int8)
reads file
int8 pg_file_write(filename_text, data_text, append_bool)
writes file. creates or appends
to create, file must not exist, to append, file may exist.
bool pg_file_rename(filename_old_text, filenamenew_text)
rename file
bool pg_file_unlink(filename_text)
unlinks file. returns true/false if done (no ERROR)
bool pg_file_rename(filename_old_text,
filename_new_text, filename_archive_text)
chain rename: new-archive, old-archive, example:
It should be quite safe to do
pg_file_write('postgresql.conf.tmp',
'.some stuff...', false);
pg_file_unlink('postgresql.conf.bak');
pg_file_rename('postgresql.conf.tmp',
'postgresql.conf', 'postgresql.conf.bak');
pg_reload_conf();
Regards,
Andreas
/*-
*
* syslogger.c
*
* The system logger (syslogger) is new in Postgres 7.5. It catches all
* stderr output from backends, the postmaster and subprocesses by
* redirecting to a pipe, and writes it to a logfile and stderr if
* configured.
* It's possible to have size and age limits for the logfile configured
* in postgresql.conf. If these limits are reached or passed, the
* current logfile is closed and a new one is created (rotated).
* The logfiles are stored in a subdirectory (configurable in
* postgresql.conf), using an internal naming scheme that mangles
* creation time and current postmaster pid.
*
* Author: Andreas Pflug [EMAIL PROTECTED]
*
* Copyright (c) 2004, PostgreSQL Global Development Group
*
*
* IDENTIFICATION
* $PostgreSQL: $
*
*-
*/
#include postgres.h
#include signal.h
#include time.h
#include unistd.h
#include libpq/pqsignal.h
#include miscadmin.h
#include postmaster/postmaster.h
#include storage/pmsignal.h
#include storage/pg_shmem.h
#include storage/ipc.h
#include postmaster/syslogger.h
#include utils/ps_status.h
#include utils/guc.h
/*
* GUC parameters
*/
int Log_RotationAge = 24*60;
int Log_RotationSize = 10*1024;
char * Log_directory = pg_log;
/*
* Flags set by interrupt handlers for later service in the main loop.
*/
static volatile sig_atomic_t got_SIGHUP = false;
static volatile sig_atomic_t rotation_requested = false;
static pg_time_t last_rotation_time = 0;
static void sigHupHandler(SIGNAL_ARGS);
static void rotationHandler(SIGNAL_ARGS);
#ifdef EXEC_BACKEND
static pid_t syslogger_forkexec();
#endif
static char* logfile_getname(pg_time_t timestamp);
static bool logfile_rotate(void);
FILE *realStdErr = NULL;
FILE *syslogFile = NULL;
int syslogPipe[2] = {0, 0};
/*
* Main entry point for syslogger process
* argc/argv parameters are valid only in EXEC_BACKEND case.
*/
void
SysLoggerMain(int argc, char *argv[])
{
IsUnderPostmaster = true;
MyProcPid = getpid();
init_ps_display(system logger process, , );
set_ps_display();
#ifdef EXEC_BACKEND
Assert(argc == 6);
argv += 3;
StrNCpy(postgres_exec_path, argv++, MAXPGPATH);
syslogPipe[0] = atoi(argv++);
syslogPipe[1] = atoi(argv);
#endif
/*
* Properly accept or ignore signals the postmaster might send us
*
* Note: we ignore all termination signals, and wait for the postmaster
* to die to catch as much pipe output as possible.
*/
pqsignal(SIGHUP, sigHupHandler); /* set flag to read config file */
pqsignal(SIGINT, SIG_IGN);
pqsignal(SIGTERM, SIG_IGN);
pqsignal(SIGQUIT, SIG_IGN);
pqsignal(SIGALRM, SIG_IGN);
pqsignal(SIGPIPE, SIG_IGN);
pqsignal(SIGUSR1, rotationHandler); /* request log rotation */
pqsignal(SIGUSR2, SIG_IGN);
/*
* Reset some signals that are accepted by postmaster but not here
*/
pqsignal(SIGCHLD, SIG_DFL);
pqsignal(SIGTTIN, SIG_DFL);
pqsignal(SIGTTOU, SIG_DFL);
pqsignal(SIGCONT, SIG_DFL);
pqsignal(SIGWINCH, SIG_DFL);
PG_SETMASK(UnBlockSig);
/*
* if we restarted, our stderr is redirected.
* Direct it back to system stderr.
*/
if (realStdErr != NULL)
{
if (dup2(fileno(realStdErr), fileno(stderr)) 0)
{
char *errstr = strerror(errno);
/*
* Now we have a real problem: we can't redirect to stderr,
* and can't ereport it correctly (it would go into our queue
Re: [PATCHES] serverlog rotation/functions
Tom Lane wrote: That struck me as not only useless but the deliberately hard way to do it. To use that in the real world, you'd have to set up a cron job to trigger the rotation, Still on my radar... which means a lot of infrastructure and privilege; whereas ISTM the point of this feature was to avoid both. ... I was thinking about putting this into the pg_autovacuum process. The log capture process should just do its own rotation on a pre-configured time-interval basis, and/or maximum-file-size basis. Yup. I see zero value added in having it respond to external signals. I see 0 value. I like to truncate my logfile before doing some complicated stuff, to have a handy file for debugging purposes. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] serverlog rotation/functions
Bruce Momjian wrote: Also there are no documenttion changes. Here are the missing docs, freshly created against cvs. Regards, Andreas Index: func.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.214 diff -u -r1.214 func.sgml --- func.sgml 12 Jul 2004 20:23:47 - 1.214 +++ func.sgml 14 Jul 2004 19:08:16 - @@ -7455,6 +7455,80 @@ /para indexterm zone=functions-misc + primarypg_logfile_get/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_length/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_name/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_rotate/primary + /indexterm + para +The functions shown in xref linkend=functions-misc-logfile + deal with the server log file if configured with log_destination + quotefile/quote. + /para + + table id=functions-misc-logfile +titleServer Logfile Functions/title +tgroup cols=3 + thead + rowentryName/entry entryReturn Type/entry entryDescription/entry/row + /thead + + tbody + row + entryliteralfunctionpg_logfile_get/function(parametersize_int4/parameter, + parameteroffset_int4/parameter,parameterfilename_text/parameter)/literal/entry + entrytypecstring/type/entry + entryget a part of the current server log file/entry + /row + row + entryliteralfunctionpg_logfile_length/function(paramaterfilename_text/parameter)/literal/entry + entrytypeint4/type/entry + entryreturn the current length of the server log file/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the new log file + name/entry + /row + row + entryliteralfunctionpg_logfile_name/function()/literal/entry + entrytypecstring/type/entry + entryreturns the current server log file name/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the previous log file + name/entry + /row + /tbody +/tgroup +/table +para +The functionpg_logfile_get/function function will return the + contents of the current server log file, limited by the size + parameter. If size is NULL, a server internal limit (currently + 5) is applied. The position parameter specifies the + starting position of the server log chunk to be returned. A + positive number or 0 will be counted from the start of the file, + a negative number from the end; if NULL, -size is assumed + (i.e. the tail of the log file). +/para +para +Both functionpg_logfile_get/function and + functionpg_logfile_length/function have a filename + parameter which may specify the logfile to examine or the + current logfile if NULL. +/para + + indexterm zone=functions-misc primarypg_cancel_backend/primary /indexterm Index: runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.269 diff -u -r1.269 runtime.sgml --- runtime.sgml 11 Jul 2004 00:18:40 - 1.269 +++ runtime.sgml 14 Jul 2004 19:08:26 - @@ -1769,9 +1769,9 @@ listitem para productnamePostgreSQL/productname supports several methods - for loggning, including systemitemstderr/systemitem and - systemitemsyslog/systemitem. On Windows, - systemitemeventlog/systemitem is also supported. Set this + for logging, including systemitemstderr/systemitem, + systemitemfile/systemitem and systemitemsyslog/systemitem. + On Windows, systemitemeventlog/systemitem is also supported. Set this option to a list of desired log destinations separated by a comma. The default is to log to systemitemstderr/systemitem only. This option must be set at server start. @@ -1779,6 +1779,17 @@ /listitem /varlistentry + varlistentry id=guc-syslog-facility xreflabel=log_filename + termvarnamelog_filename/varname (typestring/type)/term + listitem +para + This option sets the target filename for the log destination + quotefile/quote option. It may be specified as absolute + path or relative to the applicationcluster directory/application. +/para + /listitem + /varlistentry + varlistentry id=guc-syslog-facility xreflabel=syslog_facility termvarnamesyslog_facility/varname (typestring/type)/term listitem ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] serverlog rotation/functions
Bruce Momjian wrote: How is this patch supposed to work? Do people need to modify postgresql.conf and then sighup the postmaster? It seems more logical for the super-user to call a server-side function. I assume calling pg_logfile_rotate() to be the standard way. calling pg_logfile_rotate will increment the internal logfile timestamp, so each backend's next write to the logfile will lead to a reopen. On the other hand, if nothing is to be logged, nothing happens in the backends. You have pg_logfile_rotate(), but that doesn't send a sighup to the postmaster so all the backends will reread the global log file name. As long as there's no SIGHUP, the logfile name template will not change, so each backend can calculate the logfile's name from the timestamp. In case a SIGHUP *is* issued, the template might have changed, so despite an unchanged timestamp the filename to create might be different. Additionally, SIGHUP will force all backends to check for current logfile name, and close/reopen if their internal timestamp isn't up-to-date with the common timestamp. Also, what mechanism is there to prevent backends from reading the log filename _while_ it is being modified? I don't understand your concern. There's no place where the name is stored, only the GUC log_filename which is actually the template, and the timestamp (probably accessed atomically by the processor). Also there are no documenttion changes. Hm, seems I missed this in this posting; the previous had it. I'll repost it. However, looking at the issue of backends all reloading their postgresql.conf files at different times and sending output to different files, We might have a fraction of a second in practice, when a SIGHUP was issued to reread postgresql.conf, with a log_filename change, and a backend still writing its log to the old log because GUC reread is deferred for queries that started before SIGHUP. I don't really see a problem with that. I wonder if it would be best to create a log process and have each backend connect to that. That way, all the logging happens in one process. sigh All I wanted was displaying the serverlog /sigh While this might be ultimately the best solution (we even might find a way to catch stderr without interrupting further stderr piping), currently this doesn't seem to be the right moment. We'd have several inter process issues (and more with win32), which probably need some discussion. OTOH, if the current implementation is replaced by a log process later, the api interface probably would stay the same. Regards, Andreas ---(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] serverlog rotation/functions
Tom Lane wrote: That was something that bothered me too. I think in the patch as given, the GUC parameter determining the logfile name would have to be PGC_POSTMASTER, ie, you could not change it on the fly because the backends wouldn't all switch together. In my original posting it was PGC_POSTMASTER, I changed it recently after I added rotation handling in ProcessConfigFile. If you think this is critical, we can revert it. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] pg_autovacuum integration attempt #2
Peter Eisentraut wrote: Bruce Momjian wrote: I have added this patch plus your later comments to the patch queue. The autovacuum process still uses libpq to send its queries, which is not the idea behind backend integration. Can we consider this a non-fatal bug that has to be solved soon after the patch is applied? Regards, Andreas ---(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] serverlog rotation/functions
Updated version.
Only timestamp of fresh logfile in shared mem, with sanity checks.
On SIGHUP, timestamp is checked if rotation was issued, as well as
changed log_filename setting from postgresql.conf.
Regards,
Andreas
Index: src/backend/postmaster/postmaster.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.405
diff -u -r1.405 postmaster.c
--- src/backend/postmaster/postmaster.c 24 Jun 2004 21:02:55 - 1.405
+++ src/backend/postmaster/postmaster.c 6 Jul 2004 22:12:22 -
@@ -729,6 +729,11 @@
reset_shared(PostPortNumber);
/*
+* Opens alternate log file
+*/
+ LogFileInit();
+
+ /*
* Estimate number of openable files. This must happen after setting
* up semaphores, because on some platforms semaphores count as open
* files.
Index: src/backend/utils/adt/misc.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.35
diff -u -r1.35 misc.c
--- src/backend/utils/adt/misc.c2 Jul 2004 18:59:22 - 1.35
+++ src/backend/utils/adt/misc.c6 Jul 2004 22:12:34 -
@@ -202,3 +202,137 @@
FreeDir(fctx-dirdesc);
SRF_RETURN_DONE(funcctx);
}
+
+
+extern FILE *logfile; // in elog.c
+#define MAXLOGFILECHUNK 5
+
+static char *absClusterPath(text *arg)
+{
+ char *filename;
+
+ if (is_absolute_path(VARDATA(arg)))
+ filename=VARDATA(arg);
+ else
+ {
+ filename = palloc(strlen(DataDir)+VARSIZE(arg)+2);
+ sprintf(filename, %s/%s, DataDir, VARDATA(arg));
+ }
+ return filename;
+}
+
+
+Datum pg_logfile_get(PG_FUNCTION_ARGS)
+{
+ size_t size=MAXLOGFILECHUNK;
+ char *buf=0;
+ size_t nbytes;
+ FILE *f;
+
+ if (!PG_ARGISNULL(0))
+ size = PG_GETARG_INT32(0);
+ if (size MAXLOGFILECHUNK)
+ {
+ size = MAXLOGFILECHUNK;
+ ereport(WARNING,
+ (errcode(ERRCODE_OUT_OF_MEMORY),
+errmsg(Maximum size is %d., size)));
+ }
+
+ if (PG_ARGISNULL(2))
+ f = logfile;
+ else
+ {
+/* explicitely named logfile */
+ char *filename = absClusterPath(PG_GETARG_TEXT_P(2));
+ f = fopen(filename, r);
+ if (!f)
+ {
+ ereport(WARNING,
+ (errcode_for_file_access(),
+errmsg(file not found %s, filename)));
+ PG_RETURN_NULL();
+ }
+ }
+
+ if (f)
+ {
+
+ if (PG_ARGISNULL(1))
+ fseek(f, -size, SEEK_END);
+ else
+ {
+ long pos = PG_GETARG_INT32(1);
+ if (pos = 0)
+ fseek(f, pos, SEEK_SET);
+ else
+ fseek(f, pos, SEEK_END);
+ }
+ buf = palloc(size+1);
+ nbytes = fread(buf, 1, size, f);
+ buf[nbytes] = 0;
+
+ fseek(f, 0, SEEK_END);
+
+ if (!PG_ARGISNULL(2))
+ fclose(f);
+ }
+
+ if (buf)
+ PG_RETURN_CSTRING(buf);
+ else
+ PG_RETURN_NULL();
+}
+
+
+Datum pg_logfile_length(PG_FUNCTION_ARGS)
+{
+ if (PG_ARGISNULL(0))
+ {
+ if (logfile)
+ {
+ fflush(logfile);
+ PG_RETURN_INT32(ftell(logfile));
+ }
+ }
+ else
+ {
+ struct stat fst;
+ fst.st_size=0;
+ stat(absClusterPath(PG_GETARG_TEXT_P(0)), fst);
+
+ PG_RETURN_INT32(fst.st_size);
+ }
+ PG_RETURN_INT32(0);
+}
+
+
+Datum pg_logfile_name(PG_FUNCTION_ARGS)
+{
+ char *filename=LogFileName();
+ if (filename)
+ {
+ if (strncmp(filename, DataDir, strlen(DataDir)))
+ PG_RETURN_CSTRING(filename);
+ else
+ PG_RETURN_CSTRING(filename+strlen(DataDir)+1);
+ }
+ PG_RETURN_NULL();
+}
+
+
+Datum pg_logfile_rotate(PG_FUNCTION_ARGS)
+{
+ char *renamedFile = LogFileRotate();
+
+ if (renamedFile)
+ {
+ if (strncmp(renamedFile, DataDir, strlen(DataDir)))
+ PG_RETURN_CSTRING(renamedFile);
+ else
+ PG_RETURN_CSTRING(renamedFile+strlen(DataDir)+1);
+ }
+ else
+ PG_RETURN_NULL();
+}
+
Index: src/backend/utils/error/elog.c
===
RCS file:
Re: [PATCHES] pg_tablespace_databases
Joe Conway wrote: Attached is the patch I plan to apply. There are a couple of changes from what was posted. 1) You must have meant tablespace instead of namespace here: + row + entryliteralfunctionpg_tablespace_databases/function(parameternamespace_oid/parameter)/literal/entry + entrytypesetof oid/type/entry Of course. I just call everything namespace :-) 2) This allocation size was a bit ambigous and I think based on a once longer tablespace directory name: +fctx-location = (char*)palloc(strlen(DataDir)+16+10+1); This size calculation originated (copy/paste) from commands/tablespace.c, should be clarified there too (and pg_tblspc is hardcoded in strings, could be extracted to a macro definition). Regards, Andreas ---(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] patch queue reminder
Fabien COELHO wrote: Dear patchers, I have two minor patches that are being submitted but which do not appear yet in the official patch queue on the web site: http://momjian.postgresql.org/cgi-bin/pgpatches That site is maintained by Bruce, who is out to Armenia until next week, with virtually no internet access. In the meanwhile, Tom is trying to take Bruce's part as far as his spare time allowes, but he probably won't maintain the pending-patches site, so please be patient. I'd expect that patches stuck in pgsql-patches are still supposed to be in-time for feature freeze. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] serverlog rotation/functions
The attached patch includes serverlog rotation with minimal shared memory usage as discussed and functions to access it. Regards, Andreas Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.211 diff -u -r1.211 func.sgml --- doc/src/sgml/func.sgml 25 Jun 2004 17:20:21 - 1.211 +++ doc/src/sgml/func.sgml 28 Jun 2004 10:35:09 - @@ -7430,6 +7430,80 @@ /para indexterm zone=functions-misc + primarypg_logfile_get/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_length/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_name/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_rotate/primary + /indexterm + para +The functions shown in xref linkend=functions-misc-logfile + deal with the server log file if configured with log_destination + quotefile/quote. + /para + + table id=functions-misc-logfile +titleServer Logfile Functions/title +tgroup cols=3 + thead + rowentryName/entry entryReturn Type/entry entryDescription/entry/row + /thead + + tbody + row + entryliteralfunctionpg_logfile_get/function(parametersize_int4/parameter, + parameteroffset_int4/parameter,parameterfilename_text/parameter)/literal/entry + entrytypecstring/type/entry + entryget a part of the current server log file/entry + /row + row + entryliteralfunctionpg_logfile_length/function(paramaterfilename_text/parameter)/literal/entry + entrytypeint4/type/entry + entryreturn the current length of the server log file/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the new log file + name/entry + /row + row + entryliteralfunctionpg_logfile_name/function()/literal/entry + entrytypecstring/type/entry + entryreturns the current server log file name/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the previous log file + name/entry + /row + /tbody +/tgroup +/table +para +The functionpg_logfile_get/function function will return the + contents of the current server log file, limited by the size + parameter. If size is NULL, a server internal limit (currently + 5) is applied. The position parameter specifies the + starting position of the server log chunk to be returned. A + positive number or 0 will be counted from the start of the file, + a negative number from the end; if NULL, -size is assumed + (i.e. the tail of the log file). +/para +para +Both functionpg_logfile_get/function and + functionpg_logfile_length/function have a filename + parameter which may specify the logfile to examine or the + current logfile if NULL. +/para + + indexterm zone=functions-misc primarypg_cancel_backend/primary /indexterm Index: doc/src/sgml/runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.268 diff -u -r1.268 runtime.sgml --- doc/src/sgml/runtime.sgml 27 Jun 2004 22:58:19 - 1.268 +++ doc/src/sgml/runtime.sgml 28 Jun 2004 10:35:19 - @@ -1721,14 +1721,25 @@ listitem para productnamePostgreSQL/productname supports several methods -for loggning, including systemitemstderr/systemitem and -systemitemsyslog/systemitem. On Windows, -systemitemeventlog/systemitem is also supported. Set this +for logging, including systemitemstderr/systemitem, +systemitemfile/systemitem and systemitemsyslog/systemitem. + On Windows, systemitemeventlog/systemitem is also supported. Set this option to a list of desired log destinations separated by a comma. The default is to log to systemitemstderr/systemitem only. This option must be set at server start. /para /listitem + /varlistentry + + varlistentry id=guc-syslog-facility xreflabel=log_filename + termvarnamelog_filename/varname (typestring/type)/term + listitem +para + This option sets the target filename for the log destination + quotefile/quote option. It may be specified as absolute + path or relative to the applicationcluster directory/application. +/para + /listitem /varlistentry varlistentry id=guc-syslog-facility xreflabel=syslog_facility Index: src/backend/postmaster/postmaster.c
Re: [PATCHES] pg_tablespace_databases
Andreas Pflug wrote:
From an idea of Bruce, the attached patch implements the function
pg_tablespace_databases(oid) RETURNS SETOF oid
which delivers as set of database oids having objects in the selected
tablespace, enabling an admin to examine only the databases affecting
the tablespace for objects instead of scanning all of them.
It might be easier to review if I attach the file...
Regards,
Andreas
Index: src/backend/utils/adt/misc.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.34
diff -u -r1.34 misc.c
--- src/backend/utils/adt/misc.c2 Jun 2004 21:29:29 - 1.34
+++ src/backend/utils/adt/misc.c28 Jun 2004 11:16:05 -
@@ -16,11 +16,16 @@
#include sys/file.h
#include signal.h
+#include dirent.h
#include commands/dbcommands.h
#include miscadmin.h
#include storage/sinval.h
+#include storage/fd.h
#include utils/builtins.h
+#include funcapi.h
+#include catalog/pg_type.h
+#include catalog/pg_tablespace.h
/*
@@ -102,4 +107,92 @@
pg_cancel_backend(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT));
+}
+
+
+typedef struct
+{
+ char *location;
+ DIR *dirdesc;
+} ts_db_fctx;
+
+Datum pg_tablespace_databases(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ struct dirent *de;
+ ts_db_fctx *fctx;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tablespaceOid=PG_GETARG_OID(0);
+
+ funcctx=SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
+
+ fctx = palloc(sizeof(ts_db_fctx));
+
+ fctx-location = (char*)palloc(strlen(DataDir)+16+10+1);
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ {
+ fctx-dirdesc = NULL;
+ ereport(NOTICE,
+ (errcode(ERRCODE_WARNING),
+errmsg(global tablespace never has
databases.)));
+ }
+ else
+ {
+ if (tablespaceOid == DEFAULTTABLESPACE_OID)
+ sprintf(fctx-location, %s/base, DataDir);
+ else
+ sprintf(fctx-location, %s/pg_tblspc/%u, DataDir,
tablespaceOid);
+
+ fctx-dirdesc = AllocateDir(fctx-location);
+
+ if (!fctx-dirdesc) /* not a tablespace */
+ ereport(NOTICE,
+ (errcode(ERRCODE_WARNING),
+errmsg(%d is no tablespace oid.,
tablespaceOid)));
+ }
+ funcctx-user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx=SRF_PERCALL_SETUP();
+ fctx = (ts_db_fctx*)funcctx-user_fctx;
+
+ if (!fctx-dirdesc) /* not a tablespace */
+ SRF_RETURN_DONE(funcctx);
+
+ while ((de = readdir(fctx-dirdesc)) != NULL)
+ {
+ char *subdir;
+ DIR *dirdesc;
+
+ Oid datOid = atol(de-d_name);
+ if (!datOid)
+ continue;
+
+ subdir = palloc(strlen(fctx-location) + 1 + strlen(de-d_name) +1 );
+ sprintf(subdir, %s/%s, fctx-location, de-d_name);
+ dirdesc = AllocateDir(subdir);
+ if (dirdesc)
+ {
+ while ((de = readdir(dirdesc)) != 0)
+ {
+ if (strcmp(de-d_name, .) strcmp(de-d_name,
..))
+ break;
+ }
+ pfree(subdir);
+ FreeDir(dirdesc);
+
+ if (!de) /* database subdir is empty; don't report
tablespace as used */
+ continue;
+ }
+
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(datOid));
+ }
+
+ FreeDir(fctx-dirdesc);
+ SRF_RETURN_DONE(funcctx);
}
Index: src/include/catalog/pg_proc.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.339
diff -u -r1.339 pg_proc.h
--- src/include/catalog/pg_proc.h 25 Jun 2004 17:20:28 - 1.339
+++ src/include/catalog/pg_proc.h 28 Jun 2004 11:16:32 -
@@ -3595,6 +3595,9 @@
DATA(insert OID = 2243 ( bit_or
PGNSP PGUID 12 t f f f i 1 1560 1560 _null_ aggregate_dummy - _null_));
DESCR(bitwise-or bit aggregate);
+DATA(insert OID = 2554( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26
26 _null_ pg_tablespace_databases - _null_
Re: [PATCHES] Compiling libpq with VisualC
Tom wrote: It's there to declare struct timeval, and I'm fairly certain that diking it out of the header would break things on some platforms. Where does Windows define struct timeval? struct timeval is defined in winsock.h under vc6. I'm checking for _MSC_VER now. Agreed. We define FRONTEND when compiling libpq. Please test for that and send another patch. elog.h is included in postgres.h, which is included in many src/port/*.c. Many of them are pretty straight, not requiring any backend specific stuff, so the attached patch will change postgres.h to c.h for most of them. Regards, Andreas Index: include/libpq/libpq-be.h === RCS file: /projects/cvsroot/pgsql-server/src/include/libpq/libpq-be.h,v retrieving revision 1.45 diff -u -r1.45 libpq-be.h --- include/libpq/libpq-be.h21 May 2004 05:08:04 - 1.45 +++ include/libpq/libpq-be.h20 Jun 2004 09:19:54 - @@ -18,7 +18,12 @@ #ifndef LIBPQ_BE_H #define LIBPQ_BE_H +#if _MSC_VER 0 +/* struct timeval is declared in winsock.h */ +#else #include sys/time.h +#endif + #ifdef USE_SSL #include openssl/ssl.h Index: port/getopt.c === RCS file: /projects/cvsroot/pgsql-server/src/port/getopt.c,v retrieving revision 1.5 diff -u -r1.5 getopt.c --- port/getopt.c 4 Aug 2003 00:43:33 - 1.5 +++ port/getopt.c 20 Jun 2004 09:19:55 - @@ -32,7 +32,7 @@ * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF */ -#include postgres.h +#include c.h #if defined(LIBC_SCCS) !defined(lint) Index: port/getrusage.c === RCS file: /projects/cvsroot/pgsql-server/src/port/getrusage.c,v retrieving revision 1.4 diff -u -r1.4 getrusage.c --- port/getrusage.c29 Nov 2003 19:52:13 - 1.4 +++ port/getrusage.c20 Jun 2004 09:19:55 - @@ -16,7 +16,7 @@ #include stdio.h #include errno.h -#include postgres.h +#include c.h #include rusagestub.h /* This code works on: Index: port/gettimeofday.c === RCS file: /projects/cvsroot/pgsql-server/src/port/gettimeofday.c,v retrieving revision 1.4 diff -u -r1.4 gettimeofday.c --- port/gettimeofday.c 21 May 2004 05:08:05 - 1.4 +++ port/gettimeofday.c 20 Jun 2004 09:19:55 - @@ -23,7 +23,7 @@ * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. */ -#include postgres.h +#include c.h #include sys/time.h Index: port/kill.c === RCS file: /projects/cvsroot/pgsql-server/src/port/kill.c,v retrieving revision 1.1 diff -u -r1.1 kill.c --- port/kill.c 27 May 2004 13:08:57 - 1.1 +++ port/kill.c 20 Jun 2004 09:19:55 - @@ -14,7 +14,7 @@ *- */ -#include postgres.h +#include c.h #ifdef WIN32 /* signal sending */ Index: port/noblock.c === RCS file: /projects/cvsroot/pgsql-server/src/port/noblock.c,v retrieving revision 1.1 diff -u -r1.1 noblock.c --- port/noblock.c 10 Mar 2004 21:12:49 - 1.1 +++ port/noblock.c 20 Jun 2004 09:19:55 - @@ -12,7 +12,7 @@ *- */ -#include postgres.h +#include c.h #include sys/types.h #include fcntl.h Index: port/pgsleep.c === RCS file: /projects/cvsroot/pgsql-server/src/port/pgsleep.c,v retrieving revision 1.3 diff -u -r1.3 pgsleep.c --- port/pgsleep.c 12 Apr 2004 16:19:18 - 1.3 +++ port/pgsleep.c 20 Jun 2004 09:19:56 - @@ -10,7 +10,7 @@ * *- */ -#include postgres.h +#include c.h #include unistd.h #include sys/time.h Index: port/pgstrcasecmp.c === RCS file: /projects/cvsroot/pgsql-server/src/port/pgstrcasecmp.c,v retrieving revision 1.1 diff -u -r1.1 pgstrcasecmp.c --- port/pgstrcasecmp.c 7 May 2004 00:24:59 - 1.1 +++ port/pgstrcasecmp.c 20 Jun 2004 09:19:56 - @@ -20,7 +20,7 @@ * *- */ -#include postgres.h +#include c.h #include ctype.h Index: port/pipe.c === RCS file: /projects/cvsroot/pgsql-server/src/port/pipe.c,v retrieving revision 1.5 diff -u -r1.5 pipe.c --- port/pipe.c 11 Jun 2004 03:48:35 - 1.5 +++ port/pipe.c 20 Jun 2004 09:19:56 - @@ -15,7 +15,7 @@ *- */ -#include postgres.h +#include c.h #ifdef WIN32 int Index: port/sprompt.c
Re: [PATCHES] Tablespace patch review
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: As for the authentication-is-expensive issue, what of it? You *should* have to authenticate yourself in order to look inside another person's database. The sort of cross-database inspection being proposed here would be a big security hole in many people's view. Accessing pg_class et al using the current sysuseid with acl checking should be ok and satisfy security demands, no? No. If the other user has you locked out from connecting to his database at all, he's probably not going to feel that he should have to disable your access to individual objects inside it. Well he's using my tablespace, so I'd like to know at least the object name. This has some connections to the discussions we periodically have about preventing Joe User from looking at the system catalogs. If we make any changes in this area at all, I would expect them to be in the direction of narrowing access, not widening it to include being able to see other databases' catalogs. Superuser/tablespace owner isn't quite Joe User, I believe. Actually, there seem quite some other cross database/shared table issues (schema default tablespace, dropping user who owns objects) which make it desirable to have superuser readonly access to pg_catalog tables. Maybe a todo for 7.6... Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] on behalf of Bruce Momjian Sent: Sat 6/19/2004 1:05 AM To: Andreas Pflug Cc: Tom Lane; Gavin Sherry; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review We can build a gui on top of the command-line tool, no? No, we can't. Don't forget, everything we do in pgAdmin is via libpq. Yeah, gui on top of cmd line is nasty and a pain in regarding portability. It's not exactly challenging to implement it directly either, that's what I'll do in absence of a serverside solution. I'll redirect all pgadmin user speed complaints about this to Toms personal mailbox ;-) Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Tablespace patch review
Bruce Momjian wrote: I don't see why an admin tool can't connect to each database and get a listing of what is in each tablespace. I don't think connecting to 100 databases to get that information will be slow. Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Tablespace patch review
Dave Page wrote: -Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: Sat 6/19/2004 6:40 PM To: Bruce Momjian Cc: Dave Page; Tom Lane; PostgreSQL-patches Subject: Re: [PATCHES] Tablespace patch review Well, whatever you call slow or not slow. I checked it; connecting 10 databases, retrieving tablespace dependencies (pg_class union pg_schema) and closing takes about one second over an ssl connection, 0.2 seconds with non-ssl. This was a trusted connection, can't check what will happen with md5, krb or so. Don't suppose you happened to try it on Win32 did you? This was from a win32 workstation (pgadmin3) to a Linux server. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
