Re: [HACKERS] Avoiding bad prepared-statement plans.
Craig Ringer cr...@postnewspapers.com.au writes: 1) People preparing statements to save on parse+plan time; and 2) People preparing statements to get convenenient param placement. I suspect that most of (1) also want (2), but many of (2) don't care much about (1) and are just preparing statements for sql-injection safety (param placement), because they've been told to by someone, because their library does it for them, etc. So: Would it be easier to handle control of replan vs no-replan at PREPARE time? Or would that have very much the same protocol/pl change issues? http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-MAIN PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. So I think what you're talking about is already in there. -- dim -- 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] Anyone know if Alvaro is OK?
I'm happy that you and your family are fine, Alvaro. Same from my wife, too. On Sat, 2010-02-27 at 19:45 -0800, Alvaro Herrera wrote: Hi. We're out of town right now, and it seems I can't get to my home machine (probably just a loose cable). Our building was shaken badly enough that we'll have a lot of work to do to make it usable again. Our earthquake was 8.3 or 8.8 depending on who you ask, and whatever it really was, it was strong enough to tear down a bunch of buildings. Not on my zone though, fortunately for us. I have several friends on the worst area though :-( (I have to note that buildings here are built to resist this kind of thing, so the fact that some went down means that it was really strong) Re: the more frequent earthquakes, yeah I was thinking the same today. An actual scientific study would be more useful than idle speculation though ... -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Anyone know if Alvaro is OK?
It must be that someone has dropped a small amount of cheese into a lactose-intolerant volcano god... Marc G. Fournier írta: Is there a higher then normal amount of earthquakes happening recently? haiti, japan just had one for 6.9, there was apparently one in illinos a few weeks back, one on the Russia/China/N.Korean border and now Chile? Hrmmm ... On Sat, 27 Feb 2010, Bruce Momjian wrote: Josh Berkus wrote: There was a huge earthquake in Chile this morning ... Alvaro, you OK? Yes, I talked to Alvaro via IM about 2 hours ago. He was already online. His apartment building was shaken up but undamaged and his family is fine too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dedebugging and a functions that just don't work on debian flavour
I wrote a function that turns tsvectors into tsqueries. I can't see any evident error and actually the function return the expected results but just for a strange set of queries. Otherwise it causes tsqueryout to exit with errors when the result is returned. It seems that tsqueryout is running beyond what it should and most likely it could be some dirty memory/allocation issue. (ERROR: unrecognized operator type: or ERROR: stack depth limit exceeded) The queries that succede and the one that fail belong to a pretty curious set: SUCCEDE: select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items limit 2; -- q1 select itemid, tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items; -- q2 select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items where itemid=10 or itemid=15 or itemid=27; -- q3 select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items order by random() limit 3; -- q4 FAIL: select tsvector_to_tsquery(ft1idx, '|', 'ABCD', 22::smallint) from catalog_items limit 3; -- q5 If I compile my function with -O0 all queries succeed. Then I created a table create table tsvectors(i serial, tsv tsvector); in the same DB of catalog_items and filled it with some data. Length of tsvector is similar, just catalog_items contains more fields and records. All queries above succeeded. I then compiled from scratch postgresql with default configure options, loaded the whole DB containing catalog_items and no errors. Then I looked into configure options used by debian, copied nearly all with the exception of tcl stuff and rpath, compiled once more... tested my function and no error. I've no idea what to do next other than asking if someone can give a look to the code and check my comprehension of what a tsquery should be in memory. http://www.webthatworks.it/d1/files/ts_utilities.tar.bz2 thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, Feb 28, 2010 at 6:07 AM, Greg Smith g...@2ndquadrant.com wrote: Not forced to--have the option of. There are obviously workloads where you wouldn't want this. At the same time, I think there are some pretty common ones people are going to expect HS+SR to work on transparently where this would obviously be the preferred trade-off to make, were it available as one of the options. The test case I put together shows an intentionally pathological but not completely unrealistic example of such a workload. Well if we're forced to eventually have both then it kind of takes the wind out of Tom's arguments. We had better get both features working so it becomes only a question of which is worth doing first and which can be held off. Since there aren't any actual bugs in evidence for the current setup and we already have it that's a pretty easy decision. What I am sure of is that a SR-based xmin passing approach is simpler, easier to explain, more robust for some common workloads, and less likely to give surprised wow, I didn't think *that* would cancel my standby query reports from the field Really? I think we get lots of suprised wows from the field from the idea that a long-running read-only query can cause your database to bloat. I think the only reason that's obvious to us is that we've been grappling with that problem for so long. And since I never like to bet against Tom's gut feel, having it around as a plan B in case he's right about an overwhelming round of bug reports piling up against the max_standby_delay etc. logic doesn't hurt either. Agreed. Though I think it'll be bad in that case even if we have a plan B. It'll mean no file-based log shipping replicas and no guarantee that what you run on the standby can't affect the master -- which is a pretty nice guarantee. It'll also mean it'll be much more fragile against network interruptions. -- greg -- 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] A thought on Index Organized Tables
On Sun, Feb 28, 2010 at 6:02 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: So just with a addition of 8 bytes per tuple, we can have the snapshot stored with the index. Can someone please comment on this? The transaction information on tuples take 18 bytes plus several info bits. It's possible just storing a subset of that would be useful but it's unclear. And I think it would complicate the code if it had to sometimes fetch the heap tuple to get the rest and sometimes doesn't. I think you have to take up a simpler project as a first project. This is a major overhaul of transaction information and it depends on understanding how a lot of different areas work -- all of which are very complex tricky areas to understand. -- greg -- 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] Avoiding bad prepared-statement plans.
On Fri, Feb 26, 2010 at 4:01 AM, Robert Haas robertmh...@gmail.com wrote: It's not going to be easier to implement. Yeah, it would be easy to provide a global switch via a GUC setting, but that's not going to be helpful, because this is the sort of thing that really needs to be managed per-query. Almost any nontrivial application is going to have some queries that really need the custom plan and many that don't. If people just turn the GUC on we might as well throw away the plan caching mechanism altogether. I agree. A GUC is a really bad idea. I'm not sure I see why. GUCs can be turned on and off per-query. Even if you have protocol-level support a GUC could be more convenient in some cases. Specifically it would be useful for testing explain plans on queries that you expect to be planned without parameters. Currently you have to rewrite the query using PREPARE QUERY which is far from intuitive for users and even once you know how to do it prevents you from just copying and pasting queries. But putting support for a per-query level of control into the protocol (and then every client library) as well as every PL is going to be painful to implement, and even more painful to use. I suppose I should have learned by now not to argue with you over technical points, but I don't see why this should be painful. I mean, it'll be a lot of work and it'll in the end touch a lot of different parts of the code, but work != pain, and I don't see any reason why the problem can't be attacked incrementally. I'm also deeply unconvinced that any other solution will be as satisfactory. I'm not sure we should be so conservative about adding features to the protocol. Sure it'll take time to filter through to all the drivers and the better ones will support it before the less well maintained ones. But that's just a question of time and nobody will be too surprised by that. I think we should have a general purpose options field for all our messages. We can define an enum of options keys and pass an integer value for each option. In some cases they'll actually be boolean and other cases they'll be tunables but it seems simpler to make it so the server can parse all the options out of the message even if it doesn't understand them all. And that means proxies can pass them on without understanding what they all mean. We discussed a while back marking all latency-critical queries so the backend knows that any buffers touched by that query should be marked as more important to stay in cache. If we got more complex priority handling we would be able to pass an option for the time budget for a query or its nice level. -- greg -- 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] [GENERAL] trouble with to_char('L')
Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Where are we on this issue? Oops I forgot it completely. I have a little improved version and would post it tonight. Ah, very good. Thanks. Attached is an improved version. I spent many hours on this patch and am attaching an updated version. I have restructured the code and added many comments, but this is the main one: * Ideally, the server encoding and locale settings would * always match. Unfortunately, WIN32 does not support UTF-8 * values for setlocale(), even though PostgreSQL runs fine with * a UTF-8 encoding on Windows: * * http://msdn.microsoft.com/en-us/library/x99tb11d.aspx * * Therefore, we must set LC_CTYPE to match LC_NUMERIC and * LC_MONETARY, call localeconv(), and use mbstowcs() to * convert the locale-aware string, e.g. Euro symbol, which * is not in UTF-8 to the server encoding. I need someone with WIN32 experience to review and test this patch. I don't understand why cache_locale_time() works on Windows. It sets the LC_CTYPE but does not do any encoding coversion. Do month and day-of-week names not work either, or do they work and the encoding conversion for numeric/money, e.g. Euro, it not necessary? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] A thought on Index Organized Tables
Gokulakannan Somasundaram gokul...@gmail.com writes: a) We are already going from table to index to do unique checks. This is the same thing, which we will do to go and update the snapshot in the indexes. No, it is not the same thing. Updating index snapshots requires being able to *re-find* a previously made index entry for the current row. And it has to be done 100% reliably. The worst that happens if an index entry is not found when it should be during a uniqueness check is that the uniqueness constraint is not enforced properly; which is bad but it doesn't lead to internally-inconsistent data structures. b) The way, it should work would be to have a check on whether the operator is broken / function is volatile and put the onus on the user to make sure that they are updated correctly. Pretending the problem doesn't exist doesn't make it go away ... 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] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, Feb 28, 2010 at 2:54 PM, Greg Stark gsst...@mit.edu wrote: Really? I think we get lots of suprised wows from the field from the idea that a long-running read-only query can cause your database to bloat. I think the only reason that's obvious to us is that we've been grappling with that problem for so long. It seems to me that the scenario that you are looking at is one where people run different queries with and without HS, i.e. that they will run longer read-only queries than now once they have HS. I don't think that is the case. If it isn't you cannot really speak of a master bloat. Instead, I assume that most people who will grab 9.0 and use HS+SR do already have a database with a certain query profile. Now with HS+SR they will try to put the most costly and longest read-only queries to the standby but in the end will run the same number of queries with the same overall complexity. Now let's take a look at both scenarios from the administrators' point of view: 1) With the current implementation they will see better performance on the master and more aggressive vacuum (!), since they have less long-running queries now on the master and autovacuum can kick in and clean up with less delay than before. On the other hand their queries on the standby might fail and they will start thinking that this HS+SR feature is not as convincing as they thought it was... Next step for them is to take the documentation and study it for a few days to learn all about vacuum, different delays, transaction ids and age parameters and experiment a few weeks until no more queries fail - for a while... But they can never be sure... In the end they might also modify the parameters in the wrong direction or overshoot because of lack of time to experiment and lose another important property without noticing (like being as close as possible to the master). 2) On the other hand if we could ship 9.0 with the xmin-propagation feature, people would still see a better performance and have a hot standby system but this time without query cancellations. Again: the read-only queries that will be processed by the HS in the future are being processed by the master today anyway, so why should it get worse? The first impression will be that it just works nicely out of the box, is easy to set up and has no negative effect (query cancellation) that has not already shown up before (vacuum lag). I guess that most people will just run fine with this setup and never get to know about the internals. Of course we should still offer an expert mode where you can turn all kinds of knobs and where you can avoid the vacuum dependency but it would be nice if this could be the expert mode only. Tuning this is highly installation specific and you need to have a deep understanding of how PostgreSQL and HS work internally and what you actually want to achieve... Agreed. Though I think it'll be bad in that case even if we have a plan B. It'll mean no file-based log shipping replicas and no guarantee that what you run on the standby can't affect the master -- which is a pretty nice guarantee. It'll also mean it'll be much more fragile against network interruptions. Regarding the network interruptions... in reality if you have network interruptions of several minutes between your primary and your standby, you have worse problems anyway... If the standby does not renew its xmin for n seconds, log a message and just go on... Joachim -- 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] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Tue, Feb 23, 2010 at 3:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
The plan I was thinking of was to pass a flag indicating if it's a
directory to fsync_fname() and open it RD_ONLY if it's a directory and
RDRW if it's a file. Then ignore any error returns (or at least EBADF
and EINVAL) iff the flag indicating it was a directory was true.
Works for me, but let's first try just ignoring EBADF, which is the only
value we saw in the recent buildfarm failures. If we got past the fd0
test then EBADF could only indicate a rdonly failure, whereas it's less
clear what EINVAL might cover.
So I'm thinking of something like this.
Ignore ESDIR when opening a directory (and return immediately)
and ignore EBADF when trying to fsync a directory.
--
greg
*** a/src/port/copydir.c
--- b/src/port/copydir.c
***
*** 37,43
static void copy_file(char *fromfile, char *tofile);
! static void fsync_fname(char *fname);
/*
--- 37,43
static void copy_file(char *fromfile, char *tofile);
! static void fsync_fname(char *fname, bool isdir);
/*
***
*** 121,132 copydir(char *fromdir, char *todir, bool recurse)
errmsg(could not stat file \%s\: %m, tofile)));
if (S_ISREG(fst.st_mode))
! fsync_fname(tofile);
}
FreeDir(xldir);
- #ifdef NOTYET
-
/*
* It's important to fsync the destination directory itself as individual
* file fsyncs don't guarantee that the directory entry for the file is
--- 121,130
errmsg(could not stat file \%s\: %m, tofile)));
if (S_ISREG(fst.st_mode))
! fsync_fname(tofile, false);
}
FreeDir(xldir);
/*
* It's important to fsync the destination directory itself as individual
* file fsyncs don't guarantee that the directory entry for the file is
***
*** 135,142 copydir(char *fromdir, char *todir, bool recurse)
*
* However we can't do this just yet, it has portability issues.
*/
! fsync_fname(todir);
! #endif
}
/*
--- 133,139
*
* However we can't do this just yet, it has portability issues.
*/
! fsync_fname(todir, true);
}
/*
***
*** 216,235 copy_file(char *fromfile, char *tofile)
/*
* fsync a file
*/
static void
! fsync_fname(char *fname)
{
! int fd = BasicOpenFile(fname,
! O_RDWR | PG_BINARY,
! S_IRUSR | S_IWUSR);
!
! if (fd 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open file \%s\: %m, fname)));
! if (pg_fsync(fd) != 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not fsync file \%s\: %m, fname)));
--- 213,256
/*
* fsync a file
+ *
+ * Try to fsync directories but ignore errors that indicate the OS
+ * just doesn't allow/require fsyncing directories.
*/
static void
! fsync_fname(char *fname, bool isdir)
{
! int fd;
! int returncode;;
!
! if (!isdir)
! fd = BasicOpenFile(fname,
! O_RDWR | PG_BINARY,
! S_IRUSR | S_IWUSR);
! else
! fd = BasicOpenFile(fname,
! O_RDONLY | PG_BINARY,
! S_IRUSR | S_IWUSR);
!
! /* Some OSs don't allow us to open directories at all */
! if (fd 0 isdir errno == EISDIR)
! return;
!
! else if (fd 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not open file \%s\: %m, fname)));
! returncode = pg_fsync(fd);
!
! /* Some OSs don't allow us to fsync directories */
! if (returncode != 0 isdir errno == EBADF)
! {
! close(fd);
! return;
! }
!
! if (returncode != 0)
ereport(ERROR,
(errcode_for_file_access(),
errmsg(could not fsync file \%s\: %m, fname)));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Simple fix for contrib/xml2
I'm beginning to think nobody is going to step up and fix contrib/xml2, so I had a look at it myself. It strikes me that there is a pretty simple fix for it: just get rid of the attempt to manage libxml memory via palloc, and don't do anything else. The implication of this will be that if an error is thrown out of one of the libxml2 functions, we'll leak whatever memory libxml2 was using for the current document (for the life of the session). While this isn't great, it sure beats crashing; and it seems like it might be about the appropriate level of effort for a contrib module that's slated for destruction anyhow. It looks to me like the only really probable elog cause within those functions is out-of-memory, and even that wouldn't be very probable in normal use. So sticking in PG_TRY/PG_CATCH logic would be a significant increment in effort for only very marginal returns. Comments, objections? 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] [COMMITTERS] Re: pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Greg Stark st...@mit.edu writes: So I'm thinking of something like this. Ignore ESDIR when opening a directory (and return immediately) and ignore EBADF when trying to fsync a directory. Seems reasonable, but get rid of the comment However we can't do this just yet, it has portability issues; and you've got a double semicolon in one place. It might also be worth commenting the BasicOpenFile calls along the lines of Many OSs don't let us open directories RDWR, while some reject fsync on files opened RDONLY, so we need two cases. 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] Re: Hot Standby query cancellation and Streaming Replication integration
All, First, from the nature of the arguments, we need to eventually have both versions of SR: delay-based and xmin-pub. And it would be fantastic if Greg Smith and Tom Lane could work on xmin-pub to see if we can get it ready as well. I also think, based on the discussion and Greg's test case, that we could do two things which would make the shortcomings of delay-based SR a vastly better experience for users: 1) Automated retry of cancelled queries on the slave. I have no idea how hard this would be to implement, but it makes the difference between writing lots of exception-handling code for slave connections (unacceptable) to just slow response times on the slave (acceptable). 2) A more usable vacuum_defer_cleanup_age. If it was feasible for a user to configure the master to not vacuum records less than, say, 5 minutes dead, then that would again offer the choice to the user of slightly degraded performance on the master (acceptable) vs. lots of query cancel (unacceptable). I'm going to test Greg's case with vacuum_cleanup_age used fairly liberally to see if this approach has merit. Why do I say that lots of query cancel is unacceptable? For the simple reason that one cannot run the same application code against an HS+SR cluster with lots of query cancel as one runs against a standalone database. And if that's true, then the main advantage of HS+SR over Slony and Londiste is gone. MySQL took great pains to make sure that you could run the same code against replicated MySQL as standalone, and that was based on having a fairly intimate relationship with their users (at the time, anyway). Another thing to keep in mind in these discussions is the inexpensiveness of servers today. This means that, if slaves have poor performance, that's OK; one can always spin up more slaves. But if each slave imposes a large burden on the master, then that limits your scalability. --Josh Berkus -- 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] Simple fix for contrib/xml2
Tom Lane wrote: I'm beginning to think nobody is going to step up and fix contrib/xml2, so I had a look at it myself. It strikes me that there is a pretty simple fix for it: just get rid of the attempt to manage libxml memory via palloc, and don't do anything else. The implication of this will be that if an error is thrown out of one of the libxml2 functions, we'll leak whatever memory libxml2 was using for the current document (for the life of the session). While this isn't great, it sure beats crashing; and it seems like it might be about the appropriate level of effort for a contrib module that's slated for destruction anyhow. It looks to me like the only really probable elog cause within those functions is out-of-memory, and even that wouldn't be very probable in normal use. So sticking in PG_TRY/PG_CATCH logic would be a significant increment in effort for only very marginal returns. Comments, objections? FYI, I created a web page of all the open xml items: http://momjian.us/cgi-bin/pgsql/mbox?xml -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Anyone know if Alvaro is OK?
Jaime Casanova wrote: At Saturday, 02/27/2010 on 4:21 pm Marc G. Fournier scra...@hub.org wrote: On Sat, Feb 27, 2010 at 10:45 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: Is there a higher then normal amount of earthquakes happening recently? Re: the more frequent earthquakes, yeah I was thinking the same today. An actual scientific study would be more useful than idle speculation though This is a technical list so i won't insist on this but those of you that wanna give a try can read Matthew 24:3, 7, 8 and Luke 21:11 I find these links useful: http://earthquake.usgs.gov/earthquakes/eqinthenews/2010/ http://earthquake.usgs.gov/earthquakes/eqinthenews/2009/ ... I note an 8.1 in Samoa in Sep 2009 no 8.x's in 2008 an 8.5 in Sumatra Sep 12 2007 an 8.0 in Peru, Aug 2007 an 8.1 in Solomon Islands Apr 2007 an 8.1 in Kuril Islands Jan 13 2007 an 8.3 in Kuril Islands Nov 2006 an 8.7 in Sumatra, March 2005 an 8.1 in Macquarie Island Dec 2004 an 8.3 in Hokkaido Japan, Sep 2003 So yeah, if we're counting 8.8+'s this year's worse than usual; but 2005's 8.7's close. But if we're counting anything over 8.0, 2007's up there as well. -- 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] Simple fix for contrib/xml2
Bruce Momjian br...@momjian.us writes: FYI, I created a web page of all the open xml items: http://momjian.us/cgi-bin/pgsql/mbox?xml Oh, thanks, I was just about to go trawling through the archives to verify all the reported failure cases really do get fixed this way. I don't think you got em all though --- one I had on my list before was bug #5079 ... 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] contrib/xml2 pfree bug
Andrew Dunstan and...@dunslane.net writes: Didn't we just clean up a mess in our XML handling to do with memory handlers? It looks like contrib/xml2 might have similar problems. BTW, I couldn't duplicate this because I don't know what cb_ob_invoice_xml(1,1) refers to. Can you provide a self-contained example? 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] Re: Hot Standby query cancellation and Streaming Replication integration
Joachim Wieland wrote: Instead, I assume that most people who will grab 9.0 and use HS+SR do already have a database with a certain query profile. Now with HS+SR they will try to put the most costly and longest read-only queries to the standby but in the end will run the same number of queries with the same overall complexity. This is a nice summary of the primary use-case I am trying to optimize usability for, because I know for a fact there's a stack of pent-up requests for exactly this form of improvement from existing warm standby users. And your subsequent discussion of how administrators will react in each of the possible configurations here matches my own concerns. I would highly recommend anyone who feels this is not a critical feature to fix carefully read Joachim's message from an advocacy perspective, that's a better user-oriented prediction than mine of exactly how this is going to play out in the field post-release. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] full text search index scan query plan changed in 8.4.2?
Hi, hackers:
I am testing the index used by full text search recently.
I have install 8.3.9 and 8.4.2 separately.
In 8.3.9, the query plan is like:
postgres=# explain SELECT s.name as source , t.name as target FROM element as
s, element as t WHERE to_tsvector('testcfg',s.name) @@
to_tsquery('testcfg',replace(t.name,':','|'));
QUERY PLAN
Nested Loop (cost=0.01..259.92 rows=491 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Index Scan using element_ftsidx_test on element s (cost=0.01..0.33 rows=1
width=9)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))
(4 rows)
I have index: element_ftsidx_test gin (to_tsvector('testcfg'::regconfig,
name::text))
The same index and query in 8.4.2:
postgres=# explain SELECT s.name as source , t.name as target FROM element as
s, element as t WHERE to_tsvector('testcfg',s.name) @@
to_tsquery('testcfg',replace(t.name,':','|')) ;
QUERY PLAN
--
Nested Loop (cost=0.32..3123.51 rows=2457 width=18)
- Seq Scan on element t (cost=0.00..13.01 rows=701 width=9)
- Bitmap Heap Scan on element s (cost=0.32..4.36 rows=4 width=9)
Recheck Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text,
'|'::text)))
- Bitmap Index Scan on element_ftsidx_test (cost=0.00..0.32 rows=4
width=0)
Index Cond: (to_tsvector('testcfg'::regconfig, (s.name)::text) @@
to_tsquery('testcfg'::regconfig, replace((t.name)::text, ':'::text, '|'::text)))
(6 rows)
Why the query plans are different and why? Thanks!
Xu Fei
--
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] Re: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: First, from the nature of the arguments, we need to eventually have both versions of SR: delay-based and xmin-pub. And it would be fantastic if Greg Smith and Tom Lane could work on xmin-pub to see if we can get it ready as well. As I see it, the main technical obstacle here is that a subset of a feature already on the SR roadmap needs to get built earlier than expected to pull this off. I don't know about Tom, but I have no expectation it's possible for me to get up to speed on that code fast enough to contribute anything there. I expect the thing I'd be most productive at as far as moving the release forward is to continue testing this pair of features looking for rough edges, which is what I have planned for the next month. I'm not even close to finished with generating test cases specifically probing for bad behavior suspected after a look the implementation details--this is just what I came up with in my first week of that. Count me in for more testing, but out for significant development here. It's not what I've got my time allocated for because it's not where I think I'll be most productive. 2) A more usable vacuum_defer_cleanup_age. If it was feasible for a user to configure the master to not vacuum records less than, say, 5 minutes dead, then that would again offer the choice to the user of slightly degraded performance on the master (acceptable) vs. lots of query cancel (unacceptable). I'm going to test Greg's case with vacuum_cleanup_age used fairly liberally to see if this approach has merit. I've been down that road and it leads quickly to the following question: how can I tell how old in time-based units an xid is? If there were an easy answer to that question, vacuum_defer_cleanup_age would already be set in time units. It's the obvious UI to want, it's just not obvious how to build it internally. Maybe I missed something, but my guess is that vacuum_defer_cleanup_age is already as good as it's going to get. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus j...@agliodbs.com writes: 2) A more usable vacuum_defer_cleanup_age. If it was feasible for a user to configure the master to not vacuum records less than, say, 5 minutes dead, then that would again offer the choice to the user of slightly degraded performance on the master (acceptable) vs. lots of query cancel (unacceptable). I'm going to test Greg's case with vacuum_cleanup_age used fairly liberally to see if this approach has merit. I think that to associate any time based interval notion with the XID flow, you need a ticker. We already took the txid and txid_snapshot types and functions from Skytools, which took them from Slony. Maybe we could consider borrowing pgqd, the C version of the ticker, for being able to specify in human time how long a dead transaction is allowed to remain in the heap? http://github.com/markokr/skytools-dev/tree/master/sql/ticker/ Regards, -- dim -- 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] contrib/xml2 pfree bug
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
Didn't we just clean up a mess in our XML handling to do with memory
handlers? It looks like contrib/xml2 might have similar problems.
BTW, I couldn't duplicate this because I don't know what
cb_ob_invoice_xml(1,1) refers to. Can you provide a self-contained
example?
Almost any XML will do for the first param. e.g.:
select xslt_process( query_to_xml('select x from generate_series(1,5) as
x',true,false,'')::text,
$$xsl:stylesheet version=1.0
xmlns:xsl=http://www.w3.org/1999/XSL/Transform;
xsl:output method=xml indent=yes /
xsl:template match=*
xsl:copy
xsl:copy-of select=@* /
xsl:apply-templates /
/xsl:copy
/xsl:template
xsl:template match=comment()|processing-instruction()
xsl:copy /
/xsl:template
/xsl:stylesheet
$$::text);
cheers
andrew
--
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] contrib/xml2 pfree bug
Andrew Dunstan wrote:
Almost any XML will do for the first param. e.g.:
It looks like you need to make sure the XML library is called first to
induce the crash, so before doing what's below, do:
select query_to_xml('select 1 as x',true,false,''):
select xslt_process( query_to_xml('select x from generate_series(1,5)
as x',true,false,'')::text,
$$xsl:stylesheet version=1.0
xmlns:xsl=http://www.w3.org/1999/XSL/Transform;
xsl:output method=xml indent=yes /
xsl:template match=*
xsl:copy
xsl:copy-of select=@* /
xsl:apply-templates /
/xsl:copy
/xsl:template
xsl:template match=comment()|processing-instruction()
xsl:copy /
/xsl:template
/xsl:stylesheet
$$::text);
cheers
andrew
--
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] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, Feb 28, 2010 at 8:47 PM, Josh Berkus j...@agliodbs.com wrote: 1) Automated retry of cancelled queries on the slave. I have no idea how hard this would be to implement, but it makes the difference between writing lots of exception-handling code for slave connections (unacceptable) to just slow response times on the slave (acceptable). We're not only canceling queries, we are effectively canceling transactions. It seems quite impossible to repeat all queries from a transaction that has started in the past. One query might be or include the result of a previous query and as the data we see now has changed since then, the client might now want to execute a different query when it gets a different result out of a previous query... And even if it was possible, how often would you retry? You still have no guarantee that your query succeeds the second time. I'd claim that if a query failed once, chances are even higher that it fails again than that it succeeds the second time. Moreover if you continue to repeat the query and if queries come in at a certain rate, you need to process more and more queries on the slave which will not really help other queries to finish in time nor will it be beneficial for the throughput of the system as a whole... I fully agree with what you say about user expectations: We need to assume that many programs are not prepared for failures of simple read-only queries because in the past they have always worked... Another thing to keep in mind in these discussions is the inexpensiveness of servers today. This means that, if slaves have poor performance, that's OK; one can always spin up more slaves. But if each slave imposes a large burden on the master, then that limits your scalability. The burden of the xmin-publication feature is not the number of slaves, it's just the longest running queries on whatever slave they are. So your argument applies to both cases... To minimize the burden on the master, get additional slaves so that you can run your most expensive queries in a shorter time :-) Joachim -- 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] remove contrib/xml2
I believe I have fixed all the reported crashes in contrib/xml2.
However there is still this issue pointed out by Robert:
CREATE TABLE xpath_test (id integer NOT NULL, t xml);
INSERT INTO xpath_test VALUES (1, 'rowlistrow a=1/row a=2
b=oops//rowlist');
SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row/@a|/rowlist/row/@b', 'true') as t(id int4, a text, b text);
which yields an answer that is, at least, extremely surprising, if not
flat-out wrong:
id | a | b
+---+--
1 | 1 | oops
1 | 2 |
(2 rows)
the point being that it seems like oops should be associated with 2
not 1. The reason for that behavior is that xpath_table runs through
the XPATH_NODESET results generated by the various XPaths and dumps the
k'th one of each into the k'th output row generated for the current
input row. If there is any way to synchronize which node in each array
goes with each node in each other array, it's not apparent to me, but
I don't know libxml's API at all. Perhaps there is some other call we
should be using to evaluate all the XPaths in parallel?
(The code is also unbelievably inefficient, recompiling each XPath
expression once per output row (!); but it doesn't seem worth fixing
that right away given that we might have to throw away the logic
entirely in order to fix this bug.)
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] Hot Standby query cancellation and Streaming Replication integration
On Fri, 2010-02-26 at 16:44 -0500, Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Fri, Feb 26, 2010 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's *definitely* not going to be enough information in the WAL stream coming from a master that doesn't think it has HS slaves. We can't afford to record all that extra stuff in installations for which it's just useless overhead. BTW, has anyone made any attempt to measure the performance hit that the patch in its current form is creating via added WAL entries? What extra entries? Locks, just for starters. I haven't read enough of the code yet to know what else Simon added. In the past it's not been necessary to record any transient information in WAL, but now we'll have to. There is room for technical confusion here, so I'll just add some info. There was/is potential for performance hit because of the volume of additional WAL *and* the processing overhead from that additional WAL. As Heikki points out these turn out to be minimal, though this has been by careful design. There is also potential for a performance hit because incoming cleanup records may conflict with currently executing queries. If we knew for certain that the master was not sending any cleanup records that would effect current standby queries we could avoid that overhead altogether. That's a good reason for integrating a solution. AccessExclusiveLock lock records are nothing at all to do with that. They exist simply to prevent obvious correctness issues such as somebody reading a file while it is being deleted. -- Simon Riggs www.2ndQuadrant.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] Hot Standby query cancellation and Streaming Replication integration
On Fri, 2010-02-26 at 03:33 -0500, Greg Smith wrote: I really hope this discussion can say focused on if and how it's possible to improve this area, with the goal being to deliver a product everyone can be proud of with the full feature set that makes this next release a killer one. The features that have managed to all get into this release already are fantastic, everyone who contributed should be proud of that progress, and it's encouraging that the alpha4 date was nailed. It would be easy to descend into finger-pointing for why exactly this particular problem is only getting more visibility now, or into schedule-oriented commentary suggesting it must be ignored because it's too late to do anything about it. I hope everyone appreciates wandering that way will not help make PostgreSQL 9.0 a better release. This issue is so easy to encounter, and looks so bad when it happens, that I feel it could easily lead to an embarrassing situation for the community if something isn't done about it before release. Thanks Greg. It's a great relief for me to hear someone else say this and to watch a discussion about this important issue unfold. -- Simon Riggs www.2ndQuadrant.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] Re: Hot Standby query cancellation and Streaming Replication integration
Greg, Joachim, As I see it, the main technical obstacle here is that a subset of a feature already on the SR roadmap needs to get built earlier than expected to pull this off. I don't know about Tom, but I have no expectation it's possible for me to get up to speed on that code fast enough to contribute anything there. I expect the thing I'd be most productive at as far as moving the release forward is to continue testing this pair of features looking for rough edges, which is what I have planned for the next month. That's OK with me. I thought you were saying that xmin-pub was going to be easier than expected. Per my other e-mails, I think that we should be shooting for good enough, on time for 9.0., rather than perfect. We can't ever get to perfect if we don't release software. Quite frankly, simply telling people that long-running queries on the slave tend not to be effective, wait for 9.1 is a possibility. If you consider the limitations and silent failures associated with MySQL replication, let alone the issues with other Postgres solutions or the replication of some of the nosql databases, no long-running queries is a positively straightforwards restriction. HS+SR is still a tremendous improvement over the options available previously. We never thought it was going to work for everyone everywhere, and shouldn't let our project's OCD tendencies run away from us. I've been down that road and it leads quickly to the following question: how can I tell how old in time-based units an xid is? If there were an easy answer to that question, vacuum_defer_cleanup_age would already be set in time units. It's the obvious UI to want, it's just not obvious how to build it internally. Maybe I missed something, but my guess is that vacuum_defer_cleanup_age is already as good as it's going to get. Well, we could throw this on the user if we could get them some information on how to calculate that number. For example, some way for them to calculate the number of XIDs per minute via a query, and then set vacuum_defer_cleanup_age appropriately on the master. Sure, it's clunky, but we've already warned people that 9.0 will be clunky and hard to administer. And it's no worse than setting FSM_pages used to be. However, first we need to test that setting vacuum_defer_cleanup_age actually benefits query cancel issues. We're not only canceling queries, we are effectively canceling transactions. It seems quite impossible to repeat all queries from a transaction that has started in the past. One query might be or include the result of a previous query and as the data we see now has changed since then, the client might now want to execute a different query when it gets a different result out of a previous query... Sure, except that I don't expect people to be using explicit transactions as much on the slaves, since they are read-only anyway and can't even create temp tables. So having the retry not retry if there is an explicit transaction would be an OK option. And even if it was possible, how often would you retry? You still have no guarantee that your query succeeds the second time. I'd claim that if a query failed once, chances are even higher that it fails again than that it succeeds the second time. Moreover if you continue to repeat the query and if queries come in at a certain rate, you need to process more and more queries on the slave which will not really help other queries to finish in time nor will it be beneficial for the throughput of the system as a whole... Well, we'd need to have a limited number of retries, which means a GUC in recovery.conf: query_cancel_retry = # This might default to, say, 2. However, I'd still like to hear from someone with the requisite technical knowledge whether capturing and retrying the current query in a query cancel is even possible. --Josh Berkus -- 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] remove contrib/xml2
Tom Lane wrote:
I believe I have fixed all the reported crashes in contrib/xml2.
Yay! Well done! That at least removes any possibly urgency about
removing the module.
However there is still this issue pointed out by Robert:
CREATE TABLE xpath_test (id integer NOT NULL, t xml);
INSERT INTO xpath_test VALUES (1, 'rowlistrow a=1/row a=2
b=oops//rowlist');
SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row/@a|/rowlist/row/@b', 'true') as t(id int4, a text, b text);
which yields an answer that is, at least, extremely surprising, if not
flat-out wrong:
id | a | b
+---+--
1 | 1 | oops
1 | 2 |
(2 rows)
the point being that it seems like oops should be associated with 2
not 1. The reason for that behavior is that xpath_table runs through
the XPATH_NODESET results generated by the various XPaths and dumps the
k'th one of each into the k'th output row generated for the current
input row. If there is any way to synchronize which node in each array
goes with each node in each other array, it's not apparent to me, but
I don't know libxml's API at all. Perhaps there is some other call we
should be using to evaluate all the XPaths in parallel?
(The code is also unbelievably inefficient, recompiling each XPath
expression once per output row (!); but it doesn't seem worth fixing
that right away given that we might have to throw away the logic
entirely in order to fix this bug.)
Damn that's ugly.
ISTM the missing piece is really in our API. We need to be able to
specify a nodeset to iterate over, and then for each node take the first
value produced by each xpath expression. So the example above would look
something like:
SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row', '@a|@b', 'true') as t(id int4, a text, b text);
Maybe we could approximate that with the current API by factoring out
the common root of the xpath expressions, but that's likely to be
extremely fragile and error prone, and we've already got bad experience
of trying to be too cute with xpath expressions.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] contrib/xml2 vs core xml in 8.3
So I started to work on back-patching the no-pallocs change in xml2. It seems to work fine in 8.4, but it crashes in 8.3. The reason is that we never back-patched the 8.4 change to make utils/adt/xml.c not change the libxml2 memory allocation functions. That means that if you try to intermix core xml operations with contrib/xml2 operations, it fails because libxml2 is still trying to use the core's substituted memory alloc functions, and the contrib module isn't doing what's needful to make those actually work. This may explain the previous observations that 8.3's contrib/xml2 didn't crash in as many cases as 8.4's does. If core and contrib code both replace the allocation hooks, they're somewhat independent of each other. When only one does, big trouble is what you've got. It seems like the most rational response to this is to go ahead and back-patch the 8.4 changes, specifically this patch http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php into 8.3 so that we can also fix xml2. I was afraid to do that back in May when the patch was committed, but by now we have enough field testing to suggest that 8.4 is no worse than 8.3 as far as the core xml operations go. Comments? 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] contrib/xml2 vs core xml in 8.3
On Sun, Feb 28, 2010 at 06:57:33PM -0500, Tom Lane wrote: So I started to work on back-patching the no-pallocs change in xml2. It seems to work fine in 8.4, but it crashes in 8.3. The reason is that we never back-patched the 8.4 change to make utils/adt/xml.c not change the libxml2 memory allocation functions. That means that if you try to intermix core xml operations with contrib/xml2 operations, it fails because libxml2 is still trying to use the core's substituted memory alloc functions, and the contrib module isn't doing what's needful to make those actually work. This may explain the previous observations that 8.3's contrib/xml2 didn't crash in as many cases as 8.4's does. If core and contrib code both replace the allocation hooks, they're somewhat independent of each other. When only one does, big trouble is what you've got. It seems like the most rational response to this is to go ahead and back-patch the 8.4 changes, specifically this patch http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php into 8.3 so that we can also fix xml2. I was afraid to do that back in May when the patch was committed, but by now we have enough field testing to suggest that 8.4 is no worse than 8.3 as far as the core xml operations go. Comments? +1 for back-patching. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] contrib/xml2 vs core xml, more issues
Some other things struck me on further code-reading. These are not problems if core xml functions and contrib/xml2 functions are used independently, but they would be issues if one is called within the other --- and both modules have functions that invoke arbitrary SQL inside their execution, so that's quite possible. 1. contrib/xml2 calls xmlCleanupParser() all over the place. This cuts any outer usage of the libxml parser off at the knees. I think we should just remove these calls, and hope that Perl and other possible users of libxml2 are not dumb enough to call it either. The libxml2 documentation deprecates using this function at all, except possibly just before exit() if you want to avoid valgrind memory leak warnings. 2. contrib/xml2 calls xmlSetGenericErrorFunc with its own error handler, possibly replacing the core's as the active handler, or vice versa. This wouldn't be fatal except that xml2's error handler will remain active after it returns, and that handler is very wobbly for contexts other than the one it's designed to be called in --- in particular it supposes that its static errbuf string is still valid, even though that string is allocated in a short-lived memory context. It's not too hard to envision code paths in which this results in a core dump. What I'm inclined to do to fix #2 is export the core's xml_init() and xml_ereport() functions so that contrib/xml2 can make use of the core's much-more-robust libxml error handler. This fixes the problem as far as those two modules are concerned. If Perl or some other third-party module also calls xmlSetGenericErrorFunc, what would happen is that potentially our error handler would eat libxml messages intended for the other or vice versa. As long as the error handlers are robust and won't crash when called in unexpected contexts, the worst possible consequence is not seeing some useful libxml detail about an error, so I think this is tolerable. (The more I learn about it, the less impressed I am with the design of libxml2's API, but I suppose we have no control over that.) Comments? 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
[HACKERS] Could we do pgindent on just utils/adt/xml.c in the 8.3 branch?
I just committed a backpatch of http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php to 8.3. As in that patch, I did not try to manually reindent the code that's now wrapped in PG_TRY blocks. It might be a good idea to run pgindent on just that one file so that it's spacing-consistent with the later branches. This will ease any future backpatching in that file. 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] Avoiding bad prepared-statement plans.
On Sat, Feb 27, 2010 at 11:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Feb 26, 2010 at 7:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Wouldn't it be better if it just did the right thing automatically? The sort of heuristic I'm envisioning would essentially do replan every time for some number of executions, and give up only if it noticed that it wasn't getting anything better than the generic plan. So you'd have a fixed maximum overhead per session when the custom plan was useless, and the Right Thing when it wasn't. Which is likely useless for my use case. [ shrug... ] You'd better explain exactly why, if you want me to take that objection seriously. Hmm... on further thought, maybe it *would* work in that case. I'm still not convinced this is going to be generally satisfactory. It seems like it depends a great deal on how many times the function figures to be called per session and in what percentage of those cases a non-generic plan figures to be better. The appeal of a user-controllable knob is that I am pretty sure from experience that I can set it correctly, but hey... ...Robert -- 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] Avoiding bad prepared-statement plans.
On Sun, Feb 28, 2010 at 2:52 AM, Mark Mielke m...@mark.mielke.cc wrote: On 02/27/2010 11:20 PM, Craig Ringer wrote: Essentially, you have: 1) People preparing statements to save on parse+plan time; and 2) People preparing statements to get convenenient param placement. I suspect that most of (1) also want (2), but many of (2) don't care much about (1) and are just preparing statements for sql-injection safety (param placement), because they've been told to by someone, because their library does it for them, etc. So: Would it be easier to handle control of replan vs no-replan at PREPARE time? Or would that have very much the same protocol/pl change issues? I think if SQL hints were sufficient, that clients would only need to remove the prepared statement and re-create it whenever required. It should do the right thing automatically. I'm convinced that means generic plans are always wrong, and that some combination of performing fixed operations in PREPARE and variable operations in EXECUTE, combined with a plan caching against the prepared statement with criteria to determine whether or not the parameters match the assumptions made when creating one of the cached plans. Tom says extracting the fixed part of the planning out to PREPARE would be difficult or less valuable than I think. And the multi-plan caching with criteria seems to have been brought up and not commented on much by several people. So, it doesn't look like I will get this unless I learn how to implement it myself - which is probably not feasible at this time. :-) Not getting this, I think I'd be happy if PREPARE/EXECUTE can *easily* detect the worst cases (i.e. not slower in the general case), and generic plan plus custom plan plus custom execution is still significantly faster than generic plan plus generic execution. Adding SQL to indicate whether it should be re-planned or not is completely unappealing. If I could change the code, today, I'd just turn off or choose not to use PREPARE/EXECUTE. Today, PREPARE/EXECUTE seems like it should always be considered slower unless one can prove it is actually faster in a specific case, which is the exact opposite of what people expect. I don't really understand most of what you're saying here, but there's definitely some truth to your last sentence. This has easily got to be one of the top ten questions on -performance. ...Robert -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: Well, we could throw this on the user if we could get them some information on how to calculate that number. For example, some way for them to calculate the number of XIDs per minute via a query, and then set vacuum_defer_cleanup_age appropriately on the master. Sure, it's clunky, but we've already warned people that 9.0 will be clunky and hard to administer. And it's no worse than setting FSM_pages used to be. However, first we need to test that setting vacuum_defer_cleanup_age actually benefits query cancel issues. Proving that setting works as expected is already on my test case grid, seems fine in my limited testing so far. I've started looking into ways to monitor XID churn in a way for setting it better. I'll take care of providing all that in my next test case update. My intent here is to take the ideas outlined in my Hot Standby Tradeoffs blog post and turn that into a new documentation section making it more clear where the problem steps are, regardless of what else happens here. And I need some concrete example of XID burn rate measurement to finish that job. The main problem with setting vacuum_defer_cleanup_age high isn't showing it works, it's a pretty simple bit of code. It's when you recognize that it penalizes all cleanup all the time, whether or not the standby is actually executing a long-running query or not, that you note the second level of pain in increasing it. Returning to the idea of how is this different from a site already in production?, it may very well be the case that a site that sets vacuum_defer_cleanup_age high enough to support off-peak batch reporting cannot tolerate how that will impact vacuums during their peak time of day. The XID export implementation sidesteps that issue by only making the vacuum delay increase when queries that require it are running, turning this back into a standard what's the best time of day to run my big reports? issue that people understand how to cope with already. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, Feb 28, 2010 at 5:38 PM, Josh Berkus j...@agliodbs.com wrote: Greg, Joachim, As I see it, the main technical obstacle here is that a subset of a feature already on the SR roadmap needs to get built earlier than expected to pull this off. I don't know about Tom, but I have no expectation it's possible for me to get up to speed on that code fast enough to contribute anything there. I expect the thing I'd be most productive at as far as moving the release forward is to continue testing this pair of features looking for rough edges, which is what I have planned for the next month. That's OK with me. I thought you were saying that xmin-pub was going to be easier than expected. Per my other e-mails, I think that we should be shooting for good enough, on time for 9.0., rather than perfect. We can't ever get to perfect if we don't release software. I agree. It seems to me that the right long term fix for the problem of query cancellations on the slave is going to be to give the slave the ability to save multiple versions of relation pages where necessary so that older snapshots can continue to be used even after the conflicting WAL has been applied. However, I'm pretty sure that's going to be a very difficult project which is unlikely to be coded by anyone any time soon, let alone merged. Until it does, we're going to force people to pick from a fairly unappealing menu of options: postpone WAL replay for long periods of time, cancel queries (perhaps even seemingly unrelated to what changed on the master), bloat the master. All of those options are seriously unpleasant. I think, though, that we have to think of this as being like the Windows port, or maybe even more significant than that, as an architectural change. I think it is going to take several releases for this feature to be well-understood and stable and have all the options we'd like it to have. It wouldn't surprise me if we get to 10.0 before we really have truly seamless replication. I don't expect Slony or Londiste or any of the other solutions that are out there now to get kicked to the curb by PG 9.0. Still, a journey of a thousand miles begins with the first step. Simon and many others have put a great deal of time and energy into getting us to the point where we are now, and if we let the fact that we haven't reached our ultimate goal keep us from putting what we have out there in front of our customers, I think we're going to regret that. I think the thing to do is to reposition our PR around these features. We should maybe even go so far as to call them beta or experimental. We shouldn't tell people - this is going to be totally awesome. We should tell people - this is a big improvement, and it's still got some pretty significant limitations, but it's good stuff and it's going in a good direction. Overhyping what we have today is not going to be good for the project, and I'm frankly quite afraid that nothing we can possibly code between now and the release is going to measure up to what people are hoping for. We need to set our own expectations, and those of our customers, at a level at which they can be met. Quite frankly, simply telling people that long-running queries on the slave tend not to be effective, wait for 9.1 is a possibility. Yep. HS+SR is still a tremendous improvement over the options available previously. We never thought it was going to work for everyone everywhere, and shouldn't let our project's OCD tendencies run away from us. Yep. However, I'd still like to hear from someone with the requisite technical knowledge whether capturing and retrying the current query in a query cancel is even possible. I'm not sure who you want to hear from here, but I think that's a dead end. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Could we do pgindent on just utils/adt/xml.c in the 8.3 branch?
Tom Lane wrote: I just committed a backpatch of http://archives.postgresql.org/pgsql-committers/2009-05/msg00229.php to 8.3. As in that patch, I did not try to manually reindent the code that's now wrapped in PG_TRY blocks. It might be a good idea to run pgindent on just that one file so that it's spacing-consistent with the later branches. This will ease any future backpatching in that file. Sure, I can easily do that, but I am not sure how to generate a typedef list for that release, except to use the old typedef collection method for that file. Is that OK? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Lock Wait Statistics (next commitfest)
On Sun, Feb 28, 2010 at 1:06 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: Robert Haas wrote: This might be my fault, so I apologize for killing your enthusiasm. I think when I get wrapped up in a CommitFest (and especially during the second half) I get wound up in determining whether or not things are going to get applied and tend to give short shrift to thinks that seem like they won't. My bad. Generally speaking, I am always in favor of adding things to the CommitFest, but I guess the one exception I would make is if there are outstanding comments already given that haven't been addressed yet. In that case it seems a little unfair to ask people to review it further unless there are very specific questions you need answered. I think you were good about communicating that the patch wasn't ready to be applied yet, but I also think that it's to be expected that you'll get less feedback while it's in that state. Yeah, makes sense, altho perhaps there needs to be a way to get incremental progress reviewed? I think it's possible to get that, but there's a certain way you need to ask. As a general rule, anything that is of the form here's my code, can you take a look gets less attention - with the possible except of a patch from a committer who is planning to commit it if no one writes back. And even then it often doesn't get looked at. Code dumps are just no fun. Now if you write something like here's my patch... I can't quite finish it because of X and I'm not sure whether the best solution is Y or Z, those tend to get answered a lot more often, at least IME. Reading a patch and trying to understand what it's doing and why it's doing it and whether it's really the best solution is a fairly time-consuming effort; giving the reader some context makes that a lot easier, and so people are more likely to help you if you do it, again IME. ...Robert -- 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] Could we do pgindent on just utils/adt/xml.c in the 8.3 branch?
Bruce Momjian br...@momjian.us writes: Sure, I can easily do that, but I am not sure how to generate a typedef list for that release, except to use the old typedef collection method for that file. Is that OK? One of the things I noticed while patching was that the 8.3 typedef list was missing xmlChar and a few other typedefs that are used a lot in that file :-(. So please don't use the same method you used in 8.3. I think it would be close enough to use the current (just added to CVS) typedef list. Or use the one you used for 8.4 if you have it handy. There is not a lot of difference between the 8.3 and current code in that file, so either of those are probably going to give you the same results. 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] dedebugging and a functions that just don't work on debian flavour
On Sun, Feb 28, 2010 at 8:43 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've no idea what to do next other than asking if someone can give a look to the code and check my comprehension of what a tsquery should be in memory. Have you tried valgrind? ...Robert -- 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] remove contrib/xml2
Andrew Dunstan and...@dunslane.net writes:
Tom Lane wrote:
... The reason for that behavior is that xpath_table runs through
the XPATH_NODESET results generated by the various XPaths and dumps the
k'th one of each into the k'th output row generated for the current
input row.
Damn that's ugly.
Yup :-(
ISTM the missing piece is really in our API. We need to be able to
specify a nodeset to iterate over, and then for each node take the first
value produced by each xpath expression. So the example above would look
something like:
SELECT * FROM xpath_table('id', 't', 'xpath_test',
'/rowlist/row', '@a|@b', 'true') as t(id int4, a text, b text);
Hm. It seems like that still leaves you open to the possibility of
out-of-sync results. If you consider the current behavior as what
you'd get with an empty root nodeset spec, then restricting it to
produce only the first output row doesn't help at all -- it would still
associate 1 with oops. In general if the nodeset spec doesn't
select a unique subnode then you're at risk of bogus answers.
Maybe that could be defined as user error but it sure seems like it
would be error-prone to use.
Maybe we could approximate that with the current API by factoring out
the common root of the xpath expressions, but that's likely to be
extremely fragile and error prone, and we've already got bad experience
of trying to be too cute with xpath expressions.
Agreed, we do not want to be doing textual manipulations of XPaths,
which is what burnt us before. But does libxml2 offer any more
abstract path representation we could work on?
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] psql with Function Type in \df
On Thu, Feb 25, 2010 at 07:20:58PM -0500, Bruce Momjian wrote: Did we ever get tab completion support for these backslash commands? Nope :/ Not sure if I'll be able to get to it this week, either. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] A thought on Index Organized Tables
No, it is not the same thing. Updating index snapshots requires being able to *re-find* a previously made index entry for the current row. And it has to be done 100% reliably. The worst that happens if an index entry is not found when it should be during a uniqueness check is that the uniqueness constraint is not enforced properly; which is bad but it doesn't lead to internally-inconsistent data structures. Hmmm... OK Fine... I am leaving this proposal once and for all. Pretending the problem doesn't exist doesn't make it go away ... Because this is how it is done in other databases Ref: .http://www.akadia.com/services/ora_function_based_index_2.html Thanks, Gokul.
Re: [HACKERS] A thought on Index Organized Tables
The transaction information on tuples take 18 bytes plus several info bits. It's possible just storing a subset of that would be useful but it's unclear. And I think it would complicate the code if it had to sometimes fetch the heap tuple to get the rest and sometimes doesn't. Visibility map had a similar proposal and it got accepted. Fine... I think, if you guys are going to stress so hard, then there might be some issues, which i am not foreseeing right now. I think you have to take up a simpler project as a first project. This is a major overhaul of transaction information and it depends on understanding how a lot of different areas work -- all of which are very complex tricky areas to understand. Yep.. i would start by just joining in someone's project to help them out. Thanks, Gokul.
