Re: [HACKERS] Cannot cancel the change of a tablespace
Le 30/06/2010 06:53, Guillaume Lelarge a écrit : Le 30/06/2010 05:25, Tom Lane a écrit : Robert Haas robertmh...@gmail.com writes: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. I tend to think we should fix it for 9.0, but could be talked out of it if someone has a compelling argument to make. Er, maybe I lost count, but I thought you were the one objecting to the patch. You're right. Robert questioned the use of CHECK_FOR_INTERRUPTS() in code available in the src/port directory. I don't see what issue could result with this. He also said that whatever would be commited should be back-patched. I can still add it for the next commit fest, I just don't want this patch to get lost. Though I won't be able to do this before getting back from work. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). https://commitfest.postgresql.org/action/patch_view?id=331 -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for 9.1: WAL streaming from WAL buffers
On Wed, Jun 30, 2010 at 12:37 PM, Robert Haas robertmh...@gmail.com wrote: One thought that occurred to me is that if the master and standby were more tightly coupled, you could recover after a crash by making the one with the further-advanced WAL position the master, and the other one the standby. That would get around this problem, though at the cost of considerable additional complexity. But then if one of the servers comes up and can't talk to the other, you need some mechanism for preventing split-brain syndrome. Users should be free to build infrastructure to allow that. But we can't just switch ourselves -- we don't know what other pieces of their systems need to be updated when the master changes. We also need to stop thinking in terms of one master and one slave. They could have dozens of slaves and in case of failover would want to pick the slave with the most recent WAL position. The way I picture that happening they're monitoring all their slaves in some monitoring tool and use that data to pick the new master. Some external tool picks the new master and tells that host, all the other slaves, and all the rest of the their infrastructure where to find the new master and does whatever is necessary to restart or reload configurations. The question I think is what interfaces do we need in Postgres to make this easy. The monitoring tool needs a way to find the current WAL position from the slaves even when the master is down. That means potentially needing to start up the slaves in read-only mode with no master at all. It also means making it easy for an external tool to switch a node from slave to primary and change a slave's master. And it also means a slave should be able to change master and pick up where it left off easily. I'm not sure what the recommended interfaces for these operations would be currently for an external tool. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] bitmap indexes - performance
Using as a starting point the old bitmap patch in: http://archives.postgresql.org/message-id/20081101000154.go27...@fune I re-applied and re-worked the patch to see what kind of improvements over btrees bitmaps actually provided. Using a 20M rows table of 10/100/1000 random values, I've found that: 1) bulk index creation time is roughly 6 times better 2) index size is 6-15 times smaller (depending on column cardinality) 3) there's almost no difference in query times (but I have to make more tests) 4) I can't say anything about the insertion performance, but I guess bitmap will perform way worse than btree Are these improvements (index creation time, index size) worth enough to keep on working on this? I mean: given that bitmaps don't give any benefits in query times, but only benefits related to disk size and bulk index creation times, and will have horrible performance for insertions/deletions: would this job be worthed? In case it is: I will try to clean up the patch and post it... As a side note: I guess that most of the bitmap indexes performance improvements in the SELECT area are already implemented in postgres in the bitmapand/or and bitmap scan stuff? I couldn't find any docs that say that bitmap indexes are faster for selects, unless of course they are ANDed/ORed together (which is something postgres already does for regular btree indexes) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
Leonardo F wrote: Using as a starting point the old bitmap patch in: http://archives.postgresql.org/message-id/20081101000154.go27...@fune I re-applied and re-worked the patch to see what kind of improvements over btrees bitmaps actually provided. Using a 20M rows table of 10/100/1000 random values, I've found that: 1) bulk index creation time is roughly 6 times better 2) index size is 6-15 times smaller (depending on column cardinality) 3) there's almost no difference in query times (but I have to make more tests) 4) I can't say anything about the insertion performance, but I guess bitmap will perform way worse than btree Are these improvements (index creation time, index size) worth enough to keep on working on this? I mean: given that bitmaps don't give any benefits in query times, but only benefits related to disk size and bulk index creation times, and will have horrible performance for insertions/deletions: would this job be worthed? In case it is: I will try to clean up the patch and post it... As a side note: I guess that most of the bitmap indexes performance improvements in the SELECT area are already implemented in postgres in the bitmapand/or and bitmap scan stuff? I couldn't find any docs that say that bitmap indexes are faster for selects, unless of course they are ANDed/ORed together (which is something postgres already does for regular btree indexes) Great report, thanks. The other big problem with on-disk bitmap indexes is removing expired values via vacuum. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
On Thu, Jul 1, 2010 at 5:30 AM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). I'd been avoiding working on this because Simon had said he was going to commit it, but I can pick it up. I've committed and back-patched (to 8.0, as 7.4 does not have tablespaces) the fix for ALTER TABLE .. SET TABLESPACE. I'll take a look at the rest of it as well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] superfluous copydir() prototype in pg_upgrade.h
While looking at Guillaume Lelarge's patch to add CHECK_FOR_INTERRUPTS() within copydir(), I noticed that we seem to have a $SUBJECT. Trivial patch attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company remove-stray-copydir.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superfluous copydir() prototype in pg_upgrade.h
Robert Haas robertmh...@gmail.com writes: While looking at Guillaume Lelarge's patch to add CHECK_FOR_INTERRUPTS() within copydir(), I noticed that we seem to have a $SUBJECT. Trivial patch attached. The rmtree() declaration below it shouldn't be there either ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superfluous copydir() prototype in pg_upgrade.h
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: While looking at Guillaume Lelarge's patch to add CHECK_FOR_INTERRUPTS() within copydir(), I noticed that we seem to have a $SUBJECT. Trivial patch attached. The rmtree() declaration below it shouldn't be there either ... When pg_upgrade was not in /contrib, I was concerned about including port.h, but at this point, I think it is fine. Both removed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
On Thu, Jul 1, 2010 at 9:23 AM, Leonardo F m_li...@yahoo.it wrote: Using as a starting point the old bitmap patch in: http://archives.postgresql.org/message-id/20081101000154.go27...@fune I re-applied and re-worked the patch to see what kind of improvements over btrees bitmaps actually provided. Using a 20M rows table of 10/100/1000 random values, I've found that: 1) bulk index creation time is roughly 6 times better 2) index size is 6-15 times smaller (depending on column cardinality) 3) there's almost no difference in query times (but I have to make more tests) 4) I can't say anything about the insertion performance, but I guess bitmap will perform way worse than btree Are these improvements (index creation time, index size) worth enough to keep on working on this? I mean: given that bitmaps don't give any benefits in query times, but only benefits related to disk size and bulk index creation times, and will have horrible performance for insertions/deletions: would this job be worthed? In case it is: I will try to clean up the patch and post it... As a side note: I guess that most of the bitmap indexes performance improvements in the SELECT area are already implemented in postgres in the bitmapand/or and bitmap scan stuff? I couldn't find any docs that say that bitmap indexes are faster for selects, unless of course they are ANDed/ORed together (which is something postgres already does for regular btree indexes) Hmm... no performance improvement? That's not encouraging. The index being smaller ought to by itself provide some performance improvement if, say, the smaller index can fit in cache and the larger one can't. With a 6-15x size difference, that's presumably not an implausible scenario. But I guess the real point is to be able to AND and OR bitmap indices on multiple columns. Not sure if this implementation supports that or not (I haven't read the patch) and how the performance compares to doing Bitmap Heap Scan - BitmapAnd - Bitmap Index Scan with btree indices. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
Robert Haas robertmh...@gmail.com writes: Hmm... no performance improvement? That's not encouraging. The index being smaller ought to by itself provide some performance improvement if, say, the smaller index can fit in cache and the larger one can't. With a 6-15x size difference, that's presumably not an implausible scenario. But I guess the real point is to be able to AND and OR bitmap indices on multiple columns. Not sure if this implementation supports that or not (I haven't read the patch) and how the performance compares to doing Bitmap Heap Scan - BitmapAnd - Bitmap Index Scan with btree indices. In principle a bitmap index scan should be significantly faster if the index can return the bitmap more or less natively rather than having to construct it. My recollection though is that a significant amount of work is needed to make that happen, and that there is no existing patch that tackled the problem. So I'm not sure that this report should be taken as indicating that there's no chance of a SELECT performance improvement. What it does say is that we have to do that work if we want to make bitmap indexes useful. In particular, I recall some discussions about developing a streaming API whereby an index AM could return a bitmap page-by-page or so, rather than having to construct the whole thing in-memory before anything could happen. This would be a huge win for AND/OR cases, and even for a simple indexscan it would eliminate the existing startup cost penalty for a bitmap scan. Streaming like this would also eliminate the problem of having to lossify large bitmaps in order to not overrun memory. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
On Thu, Jul 1, 2010 at 11:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: In particular, I recall some discussions about developing a streaming API whereby an index AM could return a bitmap page-by-page or so, rather than having to construct the whole thing in-memory before anything could happen. This would be a huge win for AND/OR cases, and even for a simple indexscan it would eliminate the existing startup cost penalty for a bitmap scan. Streaming like this would also eliminate the problem of having to lossify large bitmaps in order to not overrun memory. Now that would be cool. The existing startup penalty for a bitmap scan is the pits. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
On Thu, Jul 1, 2010 at 10:18 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 1, 2010 at 5:30 AM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). I'd been avoiding working on this because Simon had said he was going to commit it, but I can pick it up. I've committed and back-patched (to 8.0, as 7.4 does not have tablespaces) the fix for ALTER TABLE .. SET TABLESPACE. I'll take a look at the rest of it as well. It looks like we have two reasonable choices here: - We could backpatch this only to 8.4, where ALTER DATABASE .. SET TABLESPACE was introduced. - Or, since this also makes it easier to interrupt CREATE DATABASE new TEMPLATE = some_big_database, we could back-patch it all the way to 8.1, which is the first release where we use copydir() rather than invoking cp -r (except on Windows, where copydir() has always been used, but releases 8.2 aren't supported on Windows anyway). Since I can't remember anyone complaining about difficulty interrupting CREATE DATABASE, I'm inclined to go back only to 8.4, and will do that a bit later. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] reassign owned to change the ownership for op class and family
Currently REASSIGN OWNED complains unexpected classid for operator
class and family.
For example,
create two users, user1 and user2
under user1:
create type oxetype as enum ('oxe1');
create operator class oxeops
default for type oxetype using btree as
function 1 array_lower(anyarray,integer);
and then observe unexpected classid error:
reassign owned by user1 to user2
So I propose a patch that goes against head, and it would be great to
get it backported to at least 8.3 branch
Comments?
Asko Tiidumaa
---
Skype, Sr DBA
reassign_owned_opclass_opfamily.diff
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
Le 01/07/2010 17:54, Robert Haas a écrit : On Thu, Jul 1, 2010 at 10:18 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 1, 2010 at 5:30 AM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). I'd been avoiding working on this because Simon had said he was going to commit it, but I can pick it up. I've committed and back-patched (to 8.0, as 7.4 does not have tablespaces) the fix for ALTER TABLE .. SET TABLESPACE. I'll take a look at the rest of it as well. It looks like we have two reasonable choices here: - We could backpatch this only to 8.4, where ALTER DATABASE .. SET TABLESPACE was introduced. - Or, since this also makes it easier to interrupt CREATE DATABASE new TEMPLATE = some_big_database, we could back-patch it all the way to 8.1, which is the first release where we use copydir() rather than invoking cp -r (except on Windows, where copydir() has always been used, but releases 8.2 aren't supported on Windows anyway). Since I can't remember anyone complaining about difficulty interrupting CREATE DATABASE, I'm inclined to go back only to 8.4, and will do that a bit later. I agree that a backpatch to 8.4 seems enough. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] log files and permissions
With logging_collector enabled, all the postgres log files are created with mode 0600. This makes life complicated if users other than postgres need to be able to examine the log files as well. Common example of this is when the database runs under postgres user and DBA-s have named accounts. In order to examine the log files the DBA then has to go through extra steps to sudo to postgres or equivalent. Another example would be a monitoring script that runs as an unprivileged user but needs to tail the log files. It'd be convenient if the log files would have group read access. Then we could make all the DBA or monitoring users members of the postgres group and they'd have direct access to the logs. However, as the group read is not likely a universally correct setting, the creation mode needs to be configurable. Attached is a patch that adds a GUC log_file_mode which allows to specify the creation mode for the log files. Presently it lacks documentation, which I'll add if the idea is generally acceptable. PS. I have no idea how all of this would work on Windows, maybe it's not event relevant there? regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
Martin Pihlak wrote:
Attached is a patch that adds a GUC log_file_mode which allows to specify
the creation mode for the log files. Presently it lacks documentation, which
I'll add if the idea is generally acceptable.
Now it really is attached.
regards,
Martin
*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***
*** 914,916 show_role(void)
--- 914,947
return endptr + 1;
}
+
+
+ /*
+ * LOG_FILE_MODE
+ */
+
+ extern int Log_file_mode; /* in guc.c */
+
+ /*
+ * assign_log_file_mode: GUC assign_hook for log_file_mode
+ */
+ const char *
+ assign_log_file_mode(const char *value, bool doit, GucSource source)
+ {
+ int file_mode;
+
+ /* Parse the octal mode, complain if invalid */
+ if (sscanf(value, %o, file_mode) != 1 || file_mode 0777)
+ {
+ ereport(GUC_complaint_elevel(source),
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(invalid value for parameter \log_file_mode\)));
+ return NULL;
+ }
+
+ if (doit)
+ Log_file_mode = file_mode;
+
+ return value;
+ }
+
*** a/src/backend/postmaster/syslogger.c
--- b/src/backend/postmaster/syslogger.c
***
*** 73,78 int Log_RotationSize = 10 * 1024;
--- 73,79
char *Log_directory = NULL;
char *Log_filename = NULL;
bool Log_truncate_on_rotation = false;
+ int Log_file_mode = 0600;
/*
* Globally visible state (used by elog.c)
***
*** 135,140 static void syslogger_parseArgs(int argc, char *argv[]);
--- 136,142
static void process_pipe_input(char *logbuffer, int *bytes_in_logbuffer);
static void flush_pipe_input(char *logbuffer, int *bytes_in_logbuffer);
static void open_csvlogfile(void);
+ static FILE *logfile_open(const char *filename, const char *mode, bool die_on_error);
#ifdef WIN32
static unsigned int __stdcall pipeThread(void *arg);
***
*** 516,530 SysLogger_Start(void)
*/
filename = logfile_getname(time(NULL), NULL);
! syslogFile = fopen(filename, a);
!
! if (!syslogFile)
! ereport(FATAL,
! (errcode_for_file_access(),
! (errmsg(could not create log file \%s\: %m,
! filename;
!
! setvbuf(syslogFile, NULL, LBF_MODE, 0);
pfree(filename);
--- 518,524
*/
filename = logfile_getname(time(NULL), NULL);
! syslogFile = logfile_open(filename, a, true);
pfree(filename);
***
*** 1004,1018 open_csvlogfile(void)
filename = logfile_getname(time(NULL), .csv);
! fh = fopen(filename, a);
!
! if (!fh)
! ereport(FATAL,
! (errcode_for_file_access(),
! (errmsg(could not create log file \%s\: %m,
! filename;
!
! setvbuf(fh, NULL, LBF_MODE, 0);
#ifdef WIN32
_setmode(_fileno(fh), _O_TEXT); /* use CRLF line endings on Windows */
--- 998,1004
filename = logfile_getname(time(NULL), .csv);
! fh = logfile_open(filename, a, true);
#ifdef WIN32
_setmode(_fileno(fh), _O_TEXT); /* use CRLF line endings on Windows */
***
*** 1025,1030 open_csvlogfile(void)
--- 1011,1040
}
/*
+ * Open the logfile, set permissions and buffering options.
+ */
+ static FILE *
+ logfile_open(const char *filename, const char *mode, bool die_on_error)
+ {
+ FILE *fh;
+
+ fh = fopen(filename, mode);
+
+ if (fh)
+ {
+ setvbuf(fh, NULL, LBF_MODE, 0);
+ fchmod(fileno(fh), Log_file_mode);
+ }
+ else
+ ereport(die_on_error ? FATAL : LOG,
+ (errcode_for_file_access(),
+ (errmsg(could not create log file \%s\: %m,
+ filename;
+
+ return fh;
+ }
+
+ /*
* perform logfile rotation
*/
static void
***
*** 1070,1088 logfile_rotate(bool time_based_rotation, int size_rotation_for)
if (Log_truncate_on_rotation time_based_rotation
last_file_name != NULL
strcmp(filename, last_file_name) != 0)
! fh = fopen(filename, w);
else
! fh = fopen(filename, a);
if (!fh)
{
int saveerrno = errno;
- ereport(LOG,
- (errcode_for_file_access(),
- errmsg(could not open new log file \%s\: %m,
- filename)));
-
/*
* ENFILE/EMFILE are not too surprising on a busy system; just
* keep using the old file till we manage to get a new one.
--- 1080,1093
if (Log_truncate_on_rotation time_based_rotation
last_file_name != NULL
strcmp(filename, last_file_name) != 0)
! fh = logfile_open(filename, w, false);
else
! fh = logfile_open(filename, a, false);
if (!fh)
{
int saveerrno = errno;
/*
* ENFILE/EMFILE are not too surprising on a busy system; just
* keep using the old file till we manage to get a new one.
***
*** 1128,1146 logfile_rotate(bool time_based_rotation, int size_rotation_for)
if (Log_truncate_on_rotation time_based_rotation
last_csv_file_name != NULL
strcmp(csvfilename, last_csv_file_name) != 0)
! fh = fopen(csvfilename, w);
Re: [HACKERS] Issue: Deprecation of the XML2 module 'xml_is_well_formed' function
Quoting Mike Fowler m...@mlfowler.com: Should the IS DOCUMENT predicate support this? At the moment you get the following: template1=# SELECT 'townstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns' IS DOCUMENT; ?column? -- t (1 row) template1=# SELECT 'townstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns' IS DOCUMENT; ERROR: invalid XML content LINE 1: SELECT 'townstownBidford-on-Avon/towntownCwmbran/to... ^ DETAIL: Entity: line 1: parser error : expected '' ownstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns ^ Entity: line 1: parser error : chunk is not well balanced ownstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns ^ I would've hoped the second would've returned 'f' rather than failing. I've had a glance at the XML/SQL standard and I don't see anything in the detail of the predicate (8.2) that would specifically prohibit us from changing this behavior, unless the common rule 'Parsing a string as an XML value' (10.16) must always be in force. I'm no standard expert, but IMHO this would be an acceptable change to improve usability. What do others think? Right, I've answered my own question whilst sitting in the open source coding session at CHAR(10). Yes, IS DOCUMENT should return false for a non-well formed document, and indeed is coded to do such. However, the conversion to the xml type which happens before the underlying xml_is_document function is even called fails and exceptions out. I'll work on a patch to resolve this behavior such that IS DOCUMENT will give you the missing 'xml_is_well_formed' function. Regards, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
In principle a bitmap index scan should be significantly faster if the index can return the bitmap more or less natively rather than having to construct it. The problem I'm seeing is that even on a 20M rows table, doing a select * from t where c1=10 and c2=1 where c1 and c2 are low cardinality columns, leads to a *very* fast bitmap index scan, even with btree indexes (200ms per index on my PC). The rest of the time is spent in actually retrieving heap rows; and of course no index type is going to help with that. Now: if an index search on such a big table takes so little time, what kind of improvement are we trying to get? The btree indexes on c1 and c2 are about 340MB eaxh: maybe I'm experiencing some caching weirdness? Or it's normal that an index search on such a big table is that fast (again, not counting the heap scan step, which will be required no matter the index type)? I'll try to re-test it... In particular, I recall some discussions about developing a streaming API whereby an index AM could return a bitmap page-by-page or so, rather than having to construct the whole thing in-memory before anything could happen. This would be a huge win for AND/OR cases, and even for a simple indexscan it would eliminate the existing startup cost penalty for a bitmap scan. Streaming like this would also eliminate the problem of having to lossify large bitmaps in order to not overrun memory. One of the improvements I was going to try was to avoid calling tid_set_bit (or whatever is the function, I don't remember now) for every row, and call something like tid_set_bits_in_page where a whole page was passed in: this would remove a lot of the hash_* calls that are made in each and every tid_set_bit call (now that's something btree can't do, but bitmap indexes can do easily). But I stopped before implementing it, because, as I said, I don't think the improvement would still be worth it (even calling tid_set_bit 1/20th of the needed times didn't help that much; we're still talking about going from 200ms to 180ms on a query that takes seconds to execute). But I'm going to give more tested numbers... Talking about bitmap indexes I don't think we should mention memory... I mean: bitmap indexes are supposed to be used on huge tables, and I don't think that 100MB (which holds a lot of rows in a tbm...) to spare as work_mem would be a big problem... As for the startup cost: again, I wouldn't see that as a big improvement, as we're talking mostly OLAP scenarios, where most likely there will be some other blocking operator (group by, sort, sub select etc) that will remove any improvements in startup time... To sum up: IMHO nor improvements in memory usage nor in startup time would be good reasons to switch to bitmap indexes... but bulk index creation time (10 minutes to index what it takes 60 minutes with btree... and maybe more if tables are bigger) and (maybe) index disk space might be... but I'm not 100% convinced... I'm trying to find more docs that explain the improvements of bitmap indexes in other products... but most of what I've found talks about bitmapAND/OR which is something that is very cool, but that postgres already does even with btree indexes... or index creation time/size, which are, for the moment, the only things that I'm pretty confident the patch would actually provide. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
Martin Pihlak martin.pih...@gmail.com writes: It'd be convenient if the log files would have group read access. Then we could make all the DBA or monitoring users members of the postgres group and they'd have direct access to the logs. However, as the group read is not likely a universally correct setting, the creation mode needs to be configurable. It doesn't appear to me that this helps unless you are willing to make the containing director(ies) group-readable/executable as well, which is something we've resisted doing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
Tom Lane t...@sss.pgh.pa.us wrote: Martin Pihlak martin.pih...@gmail.com writes: It'd be convenient if the log files would have group read access. Then we could make all the DBA or monitoring users members of the postgres group and they'd have direct access to the logs. However, as the group read is not likely a universally correct setting, the creation mode needs to be configurable. It doesn't appear to me that this helps unless you are willing to make the containing director(ies) group-readable/executable as well, which is something we've resisted doing. I just tried creating a symbolic link to the pg_log directory and flagging the existing logs within it to 640. As a member of the group I was able to list and view the contents of log files through the symbolic link, even though I didn't have any authority to the PostgreSQL data directory. That seems potentially useful to me. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
Tom Lane wrote: It doesn't appear to me that this helps unless you are willing to make the containing director(ies) group-readable/executable as well, which is something we've resisted doing. The log can be moved outside of data directory by setting log_directory to an absolute path. Then the permissions for the log directory can be arbitrary as the postmaster is only strict about permissions on data directory. regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
* Tom Lane (t...@sss.pgh.pa.us) wrote: Martin Pihlak martin.pih...@gmail.com writes: It'd be convenient if the log files would have group read access. Then we could make all the DBA or monitoring users members of the postgres group and they'd have direct access to the logs. However, as the group read is not likely a universally correct setting, the creation mode needs to be configurable. It doesn't appear to me that this helps unless you are willing to make the containing director(ies) group-readable/executable as well, which is something we've resisted doing. Perhaps we should have a umask-like GUC instead of this? In the end, I agree with and completely understand the OP's complaint. I havn't run into this issue much since, on Debian systems, we use logrotate to move log files around and use the copy/truncate method there, so permissions end up being preserved once an admin has decided to change them. Might be something to consider, but, really, we should give the admin some flexibility here, even if the default is the same as current behaviour. I'll refrain from bringing up the fact that we're concerned about log files having group permissions by default, but we ship with trust in pg_hba.conf... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] log files and permissions
Martin Pihlak martin.pih...@gmail.com writes: Tom Lane wrote: It doesn't appear to me that this helps unless you are willing to make the containing director(ies) group-readable/executable as well, which is something we've resisted doing. The log can be moved outside of data directory by setting log_directory to an absolute path. Oh, of course. We'd need to mention that in the documentation for the log-file-permission GUC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
On 07/01/2010 12:56 PM, Kevin Grittner wrote: I just tried creating a symbolic link to the pg_log directory and flagging the existing logs within it to 640. As a member of the group I was able to list and view the contents of log files through the symbolic link, even though I didn't have any authority to the PostgreSQL data directory. That seems potentially useful to me. Symlinks are exactly equivalent to using the target of the link. Your permissions are probably already arranged so that you (as a group member) can access the files. Fedora's initscript seems to deliberately revoke group permissions from PGDATA and pg_log so I'm guessing that at some point some things were created with some group permissions. That said, as Martin mentions one can easily place the log directory outside of the data directory and set appropriate directory permissions. -- m. tharp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log files and permissions
On Thu, Jul 1, 2010 at 12:19 PM, Michael Tharp g...@partiallystapled.com wrote: That said, as Martin mentions one can easily place the log directory outside of the data directory and set appropriate directory permissions. If I can offer my $0.02, I recently solved such a problem on SuSE Linux with apache logs. I used the ACL support on ext3 to give a specific group read-only access: cd /var/log # Add an ACL for the 'www' user setfacl -m u:www:r-x apache2 setfacl -m u:www:r-- apache2/* # Modify the default ACL so that new files get 'r' for user setfacl -d -m u:www:r-- apache2 Just pointing out that this problem is solvable on systems that support ACLs w/o patching postgres. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
On Thu, Jul 1, 2010 at 12:11 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Le 01/07/2010 17:54, Robert Haas a écrit : On Thu, Jul 1, 2010 at 10:18 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 1, 2010 at 5:30 AM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). I'd been avoiding working on this because Simon had said he was going to commit it, but I can pick it up. I've committed and back-patched (to 8.0, as 7.4 does not have tablespaces) the fix for ALTER TABLE .. SET TABLESPACE. I'll take a look at the rest of it as well. It looks like we have two reasonable choices here: - We could backpatch this only to 8.4, where ALTER DATABASE .. SET TABLESPACE was introduced. - Or, since this also makes it easier to interrupt CREATE DATABASE new TEMPLATE = some_big_database, we could back-patch it all the way to 8.1, which is the first release where we use copydir() rather than invoking cp -r (except on Windows, where copydir() has always been used, but releases 8.2 aren't supported on Windows anyway). Since I can't remember anyone complaining about difficulty interrupting CREATE DATABASE, I'm inclined to go back only to 8.4, and will do that a bit later. I agree that a backpatch to 8.4 seems enough. Done. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] actualised plpgsql tutorial
Hello I actualised plpgsql tutorial on address http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Design_of_triggers_in_PL.2FpgSQL I hope so this document can be useful for people who start with plpgsql. Sorry - my English is good for reading mailing list and coding in C lang. Can some native english speaker look on this document and correct some errors and mistakes? Thank you Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cannot cancel the change of a tablespace
Le 01/07/2010 22:13, Robert Haas a écrit : On Thu, Jul 1, 2010 at 12:11 PM, Guillaume Lelarge guilla...@lelarge.info wrote: Le 01/07/2010 17:54, Robert Haas a écrit : On Thu, Jul 1, 2010 at 10:18 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 1, 2010 at 5:30 AM, Guillaume Lelarge guilla...@lelarge.info wrote: On Tue, Jun 29, 2010 at 9:42 PM, Bruce Momjian br...@momjian.us wrote: So this is not something we want fixed for 9.0, as indicated by Simon? I don't see the patch on the commit-fest page yet. Finally, I added it to the next commit fest. Robert can work on it before if he wants to (or has the time). I'd been avoiding working on this because Simon had said he was going to commit it, but I can pick it up. I've committed and back-patched (to 8.0, as 7.4 does not have tablespaces) the fix for ALTER TABLE .. SET TABLESPACE. I'll take a look at the rest of it as well. It looks like we have two reasonable choices here: - We could backpatch this only to 8.4, where ALTER DATABASE .. SET TABLESPACE was introduced. - Or, since this also makes it easier to interrupt CREATE DATABASE new TEMPLATE = some_big_database, we could back-patch it all the way to 8.1, which is the first release where we use copydir() rather than invoking cp -r (except on Windows, where copydir() has always been used, but releases 8.2 aren't supported on Windows anyway). Since I can't remember anyone complaining about difficulty interrupting CREATE DATABASE, I'm inclined to go back only to 8.4, and will do that a bit later. I agree that a backpatch to 8.4 seems enough. Done. Thanks, Robert. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue: Deprecation of the XML2 module 'xml_is_well_formed' function
On Thu, Jul 1, 2010 at 12:25 PM, Mike Fowler m...@mlfowler.com wrote: Quoting Mike Fowler m...@mlfowler.com: Should the IS DOCUMENT predicate support this? At the moment you get the following: template1=# SELECT 'townstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns' IS DOCUMENT; ?column? -- t (1 row) template1=# SELECT 'townstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns' IS DOCUMENT; ERROR: invalid XML content LINE 1: SELECT 'townstownBidford-on-Avon/towntownCwmbran/to... ^ DETAIL: Entity: line 1: parser error : expected '' ownstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns ^ Entity: line 1: parser error : chunk is not well balanced ownstownBidford-on-Avon/towntownCwmbran/towntownBristol/town/towns ^ I would've hoped the second would've returned 'f' rather than failing. I've had a glance at the XML/SQL standard and I don't see anything in the detail of the predicate (8.2) that would specifically prohibit us from changing this behavior, unless the common rule 'Parsing a string as an XML value' (10.16) must always be in force. I'm no standard expert, but IMHO this would be an acceptable change to improve usability. What do others think? Right, I've answered my own question whilst sitting in the open source coding session at CHAR(10). Yes, IS DOCUMENT should return false for a non-well formed document, and indeed is coded to do such. However, the conversion to the xml type which happens before the underlying xml_is_document function is even called fails and exceptions out. I'll work on a patch to resolve this behavior such that IS DOCUMENT will give you the missing 'xml_is_well_formed' function. I think the point if IS DOCUMENT is to distinguish a document: foosome stuffbar/baz//foo from a document fragment: bar/baz/ A document is allowed only one toplevel tag. It'd be nice, I think, to have a function that tells you whether something is legal XML without throwing an error if it isn't, but I suspect that should be a separate function, rather than trying to jam it into IS DOCUMENT. http://developer.postgresql.org/pgdocs/postgres/functions-xml.html#AEN15187 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hello
hello: I have good news for you. Last week ,I have Order china New Apple iPad Wi-Fi 32GB this website: www.Toradeo.com I have received the product! I believe you will find what you want there and have an good experience on shopping from them. Regards! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bitmap indexes - performance
Leonardo F wrote: I'm trying to find more docs that explain the improvements of bitmap indexes in other products... but most of what I've found talks about bitmapAND/OR which is something that is very cool, but that postgres already does even with btree indexes... or index creation time/size, which are, for the moment, the only things that I'm pretty confident the patch would actually provide. I think a real limitation of on-disk bitmap indexes is that they are only feable for low cardinality columns, while btree handles all column types. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0beta2 - server crash when using HS + SR
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The attached patch shows the hint only during recovery. BTW, it would be easier and more consistent with the rest of the code to look at InRecovery, instead of messing around with the function signature. And the usual way to emit a hint conditionally is (InRecovery ? errhint(...) : 0) rather than duplicate a lot of surrounding code. Thanks for the hints. I was thinking there was a way to use ? : for the hint, but couldn't find an example. I see examples now. Updated patch attached. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
Fujii Masao wrote: On Thu, Jun 10, 2010 at 5:06 AM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. ?Of course, they might not have been thinking of the delay for writes. If there were an easy way to make the trigger file cancel all running queries, apply remaining logs and come up, then I'd vote for that for 9.0. ?I think it's the more desired behavior by most users. ?However, I'm opposed to any complex solutions which might delay 9.0 release. My feeling about it is that if you want fast failover you should not have your failover target server configured as hot standby at all, let alone hot standby with a long max_standby_delay. ?Such a slave could be very far behind on applying WAL when the crunch comes, and no amount of query killing will save you from that. ?Put your long-running standby queries on a different slave instead. We should consider whether we can improve the situation in 9.1, but it is not a must-fix for 9.0; especially when the correct behavior isn't immediately obvious. OK. Let's revisit in 9.1. I attached the proposal patch for 9.1. The patch treats max_standby_delay as zero (i.e., cancels all the conflicting queries immediately), ever since the trigger file is created. So we can cause a recovery to end without waiting for any lock held by queries, and minimize the failover time. OTOH, queries which don't conflict with a recovery survive the failover. Should this be added to the first 9.1 commitfest? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
Bruce Momjian br...@momjian.us writes: Fujii Masao wrote: On Thu, Jun 10, 2010 at 5:06 AM, Tom Lane t...@sss.pgh.pa.us wrote: My feeling about it is that if you want fast failover you should not have your failover target server configured as hot standby at all, let alone hot standby with a long max_standby_delay. Such a slave could be very far behind on applying WAL when the crunch comes, and no amount of query killing will save you from that. Put your long-running standby queries on a different slave instead. We should consider whether we can improve the situation in 9.1, but it is not a must-fix for 9.0; especially when the correct behavior isn't immediately obvious. OK. Let's revisit in 9.1. I attached the proposal patch for 9.1. The patch treats max_standby_delay as zero (i.e., cancels all the conflicting queries immediately), ever since the trigger file is created. So we can cause a recovery to end without waiting for any lock held by queries, and minimize the failover time. OTOH, queries which don't conflict with a recovery survive the failover. Should this be added to the first 9.1 commitfest? Not sure ... it seems like proof of concept for a pretty dubious concept. If you want a slave to be ready for fast failover then you should not be letting it get far behind the master in the first place. I think there's some missing piece here, but I'm not quite sure what to propose. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
