Re: [HACKERS] Service not starting: Error 1053
Tom Lane wrote: Frank Featherlight dirtyd...@gmail.com writes: while reading your thread two things come to mind, I have installed: Registry Mechanic ( http://www.pctools.com/registry-mechanic ) Tune-Up Utilities ( http://www.tune-up.com/products/tuneup-utilities ) Any of these two might cause the problem aswell in your opinion? Damifino, I'm not a Windows person. But I'd suggest methodically removing each and every bit of non-default software you've got, to see if you can find one that causes the failure. We know that the failure does not occur on stock Windows or with most popular add-ons, so unusual add-ons deserve a close look. I wonder if it would help to reserve the address space for the shared memory block earlier. We could pass the address and size of the shared memory block as extra arguments to the backend, and reserve it before doing anything else. There's even a function called VirtualAllocEx, that postmaster could call right after CreateProcess to reserve the address space on behalf of the child process. Of course, none of this helps if the culprit is a DLL or a 3rd party program that allocates the adress space immediately at CreateProcess. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Backend assertion failure on \d
Running the following against HEAD and REL8_3_6: create table foo (a varchar(500)); create view bar as select case foo.a when '1' then 'foo' else 'bar' end as fa from foo; \d bar Causes as assertion in the backend: TRAP: FailedAssertion(!(Node*)(((list_head(((OpExpr *) w)-args))-data.ptr_value)))-type) == T_CaseTestExpr) || Node*)(((list_head(((OpExpr *) w)-args))-data.ptr_value)))-type) == T_Const)), File: ruleutils.c, Line: 4587 ) LOG: server process (PID 28408) was terminated by signal 6: Aborted Attached is a simple patch that appears to fix this problem, not sure if it suffices in general. Alan *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *** get_rule_expr(Node *node, deparse_contex *** 4582,4592 Node *rhs; Assert(IsA(linitial(((OpExpr *) w)-args), CaseTestExpr) || IsA(linitial(((OpExpr *) w)-args), ! Const)); rhs = (Node *) lsecond(((OpExpr *) w)-args); get_rule_expr(rhs, context, false); } else if (IsA(w, CaseTestExpr)) appendStringInfo(buf, TRUE); --- 4582,4594 Node *rhs; Assert(IsA(linitial(((OpExpr *) w)-args), CaseTestExpr) || IsA(linitial(((OpExpr *) w)-args), ! Const) || ! IsA(linitial(((OpExpr *) w)-args), ! RelabelType)); rhs = (Node *) lsecond(((OpExpr *) w)-args); get_rule_expr(rhs, context, false); } else if (IsA(w, CaseTestExpr)) appendStringInfo(buf, TRUE); -- 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] Service not starting: Error 1053
On Wed, Feb 25, 2009 at 4:51 AM, Tom Lane t...@sss.pgh.pa.us wrote: Frank Featherlight dirtyd...@gmail.com writes: while reading your thread two things come to mind, I have installed: Registry Mechanic ( http://www.pctools.com/registry-mechanic ) Tune-Up Utilities ( http://www.tune-up.com/products/tuneup-utilities ) Any of these two might cause the problem aswell in your opinion? Damifino, I'm not a Windows person. But I'd suggest methodically removing each and every bit of non-default software you've got, to see if you can find one that causes the failure. We know that the failure does not occur on stock Windows or with most popular add-ons, so unusual add-ons deserve a close look. I'm not a Tom Lane, but I do have a Windows background, and my advice would be to steer clear of such tools as a general rule. Too many of them do nothing useful, and there have been some such tools that are actually trojans of some kind or other. I make no comments on the specific ones you've mentioned though. Your description of the system sounds like it's an OEM installation of Windows. Does it have any OEM versions of Norton/Symantec/McAffee/Panda/nod32 anti virus, anti spyware or firewall packages installed (or have there ever been?). It would also be potentially useful to see full details of your system. Click Start - Run and then type msinfo32 and then click OK. In the app that runs, select File - Export to save the system details to a text file. You should check the file to make sure there's nothing in there you don't want to be public, and the zip it up and mail it to the list (CC me incase the file is still too big for the list). I think you'll also need to rename it to report.zi_ or similar, as I think the lists will reject .zip files. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] psql \d commands and information_schema
Attached is a patch that modifies psql \dX commands to treat objects in information_schema as system objects. This prevents them from showing up in \dX *.* and polluting the user objects list. This is especially annoying if user objects are in multiple schemas, and one wants to get a quick overview by running \dX *.* regards, Martin *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *** *** 23,28 --- 23,34 #include variables.h + /* Macros for exluding system objects from \d commands */ + #define NOT_SYSTEM_OBJECT n.nspname NOT IN ('pg_catalog', 'information_schema')\n + #define AND_NOT_SYSTEM_OBJECT AND NOT_SYSTEM_OBJECT + #define WHERE_NOT_SYSTEM_OBJECT WHERE NOT_SYSTEM_OBJECT + + static bool describeOneTableDetails(const char *schemaname, const char *relationname, const char *oid, *** *** 95,101 describeAggregates(const char *pattern, bool verbose, bool showSystem) gettext_noop(Description)); if (!showSystem) ! appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, --- 101,107 gettext_noop(Description)); if (!showSystem) ! appendPQExpBuffer(buf, AND_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, *** *** 282,288 describeFunctions(const char *pattern, bool verbose, bool showSystem) AND NOT p.proisagg\n); if (!showSystem) ! appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, --- 288,294 AND NOT p.proisagg\n); if (!showSystem) ! appendPQExpBuffer(buf, AND_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, *** *** 373,379 describeTypes(const char *pattern, bool verbose, bool showSystem) appendPQExpBuffer(buf, AND t.typname !~ '^_'\n); if (!showSystem) ! appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n); /* Match name pattern against either internal or external name */ processSQLNamePattern(pset.db, buf, pattern, true, false, --- 379,385 appendPQExpBuffer(buf, AND t.typname !~ '^_'\n); if (!showSystem) ! appendPQExpBuffer(buf, AND_NOT_SYSTEM_OBJECT); /* Match name pattern against either internal or external name */ processSQLNamePattern(pset.db, buf, pattern, true, false, *** *** 428,434 describeOperators(const char *pattern, bool showSystem) gettext_noop(Description)); if (!showSystem) ! appendPQExpBuffer(buf, WHERE n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, !showSystem, true, n.nspname, o.oprname, NULL, --- 434,440 gettext_noop(Description)); if (!showSystem) ! appendPQExpBuffer(buf, WHERE_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, !showSystem, true, n.nspname, o.oprname, NULL, *** *** 632,638 objectDescription(const char *pattern, bool showSystem) gettext_noop(aggregate)); if (!showSystem) ! appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, --- 638,644 gettext_noop(aggregate)); if (!showSystem) ! appendPQExpBuffer(buf, AND_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, *** *** 655,661 objectDescription(const char *pattern, bool showSystem) gettext_noop(function)); if (!showSystem) ! appendPQExpBuffer(buf, AND n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, --- 661,667 gettext_noop(function)); if (!showSystem) ! appendPQExpBuffer(buf, AND_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, true, false, n.nspname, p.proname, NULL, *** *** 673,679 objectDescription(const char *pattern, bool showSystem) gettext_noop(operator)); if (!showSystem) ! appendPQExpBuffer(buf, WHERE n.nspname 'pg_catalog'\n); processSQLNamePattern(pset.db, buf, pattern, !showSystem, false, n.nspname, o.oprname, NULL, --- 679,685 gettext_noop(operator)); if (!showSystem) ! appendPQExpBuffer(buf, WHERE_NOT_SYSTEM_OBJECT); processSQLNamePattern(pset.db, buf, pattern, !showSystem, false, n.nspname, o.oprname, NULL, *** *** 691,697 objectDescription(const char *pattern, bool showSystem) gettext_noop(data type));
Re: [HACKERS] Service not starting: Error 1053
Heikki Linnakangas wrote: Tom Lane wrote: Frank Featherlight dirtyd...@gmail.com writes: while reading your thread two things come to mind, I have installed: Registry Mechanic ( http://www.pctools.com/registry-mechanic ) Tune-Up Utilities ( http://www.tune-up.com/products/tuneup-utilities ) Any of these two might cause the problem aswell in your opinion? Damifino, I'm not a Windows person. But I'd suggest methodically removing each and every bit of non-default software you've got, to see if you can find one that causes the failure. We know that the failure does not occur on stock Windows or with most popular add-ons, so unusual add-ons deserve a close look. I wonder if it would help to reserve the address space for the shared memory block earlier. We could pass the address and size of the shared memory block as extra arguments to the backend, and reserve it before doing anything else. There's even a function called VirtualAllocEx, that postmaster could call right after CreateProcess to reserve the address space on behalf of the child process. Of course, none of this helps if the culprit is a DLL or a 3rd party program that allocates the adress space immediately at CreateProcess. AFAIK all the cases where we *have* identified the culprit (which has been antivirus or firewall), this is exactly what it was doing... //Magnus -- 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] Service not starting: Error 1053
Magnus Hagander wrote: Heikki Linnakangas wrote: Of course, none of this helps if the culprit is a DLL or a 3rd party program that allocates the adress space immediately at CreateProcess. AFAIK all the cases where we *have* identified the culprit (which has been antivirus or firewall), this is exactly what it was doing... Would it be possible to build a tool that runs through a series of permission-checks, tries to grab some shared-memory, write to files in the appropriate folders etc. and then shows the name of any process interfering? Half the problem is that whenever someone has Windows-related difficulties there's no standard tools we can use to diagnose. -- Richard Huxton Archonet Ltd -- 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] Service not starting: Error 1053
On Wed, Feb 25, 2009 at 12:41 PM, Frank Featherlight dirtyd...@gmail.com wrote: I have attached the sysinfo, please don't abuse it in any way possible, I trust you guys with that. :-) Thanks! As far as I can remember, no OEM versions of anti-virus were installed. Like I said before, did have these installed (removed them already): Norton Anti-Virus Kaspersky Anti-Hacker (temporarily installed Kaspersky now, but will remove before trying to install postgresql again when I get a good tip that might help) Yes - the sysinfo shows a bunch of Kaspersky miniport drivers installed (assuming I'm reading the German text correctly). Try uninstalling it completely (check to make sure msinfo32 agrees it's all gone after a reboot) and then try a reinstall of PG. Still have installed: Registry Mechanic ( http://www.pctools.com/registry-mechanic ) Tune-Up Utilities ( http://www.tune-up.com/products/tuneup-utilities ) Afaics, registry mechanic isn't running, so shouldn't be a problem. Tune-up utilities appears to have a bunch of services, so is worth trying without. I also notice you have daemontools installed. I know earlier versions worked with PG, but I haven't tried in quite a while. Might be worth trying removing that too. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Service not starting: Error 1053
Frank Featherlight wrote: It's Microsoft Windows XP Home Edition Version 2002 with Service Pack 3. XP-HE is at best a very poor platform for postgres. You might have more success on XP-Pro. I am not clear if this is what is causing your problems, however. 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] Service not starting: Error 1053
Richard Huxton wrote: Magnus Hagander wrote: Heikki Linnakangas wrote: Of course, none of this helps if the culprit is a DLL or a 3rd party program that allocates the adress space immediately at CreateProcess. AFAIK all the cases where we *have* identified the culprit (which has been antivirus or firewall), this is exactly what it was doing... Would it be possible to build a tool that runs through a series of permission-checks, tries to grab some shared-memory, write to files in the appropriate folders etc. and then shows the name of any process interfering? Half the problem is that whenever someone has Windows-related difficulties there's no standard tools we can use to diagnose. Well, we have one already - it's called PostgreSQL :) The thing is we're doing some fairly complex things as we start up. And since we don't know exactly what the problem is, we don't know what to look for. We'd have to run the whole thing over again... And still not be able to point out *what* is the problem, since we don't know... What we could do is some small tool that looks for filter drivers and throws out a warning about it. But I'm not sure if that will make things better - it'll warn on known things like antivirus, but if people don't read the documentation/FAQ/lists about that, will they download a separate tool and run it? //Magnus -- 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] Backend assertion failure on \d
Alan Li wrote: Running the following against HEAD and REL8_3_6: Same problem exists in 8.2 and 8.1 as well. The code in ruleutils.c is similar in 8.0 as well, except that the Assertion isn't there. create table foo (a varchar(500)); create view bar as select case foo.a when '1' then 'foo' else 'bar' end as fa from foo; \d bar Causes as assertion in the backend: TRAP: FailedAssertion(!(Node*)(((list_head(((OpExpr *) w)-args))-data.ptr_value)))-type) == T_CaseTestExpr) || Node*)(((list_head(((OpExpr *) w)-args))-data.ptr_value)))-type) == T_Const)), File: ruleutils.c, Line: 4587 ) LOG: server process (PID 28408) was terminated by signal 6: Aborted Attached is a simple patch that appears to fix this problem, not sure if it suffices in general. Looks good to me, at least. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Service not starting: Error 1053
On Wed, Feb 25, 2009 at 1:43 PM, Magnus Hagander mag...@hagander.net wrote: The thing is we're doing some fairly complex things as we start up. And since we don't know exactly what the problem is, we don't know what to look for. We'd have to run the whole thing over again... And still not be able to point out *what* is the problem, since we don't know... In the case of something already having mapped part of our address space what would be useful would be the equivalent of dumping out the contents of /proc/self/maps in Linux. -- 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] Service not starting: Error 1053
Frank Featherlight wrote: 1) Uninstalled the following programs+program files folder: File Shredder Holdem Manager (this is the program I need postgresql for) mIRC Proxifier This one sounds like a potential culprit. GetDataBack for FAT and NTFS This could be, but probably shouldn't. Registry Mechanic TuneUp Utilities SimpLite Daemon Tools Kaspersky (was not installed during the time of the errors so can't be to blame) PostgresQL Could be one of those, but the ones above sounds more likely. Now, the fact that this is Windows makes it actually possible that things will work even if you reinstall *all* of them :-) But it would be interesting to know if there is a specific one that breaks it. //Magnus -- 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] Backend assertion failure on \d
Alan Li alan...@gmail.com writes: Running the following against HEAD and REL8_3_6: create table foo (a varchar(500)); create view bar as select case foo.a when '1' then 'foo' else 'bar' end as fa from foo; \d bar Causes as assertion in the backend: Thanks for the report. Looks like I forgot to consider the possibility that type coercion nodes would get inserted atop the CaseTestExpr. You can break the other paths here too if you try things like case foo when true then ... where foo is of a domain over boolean. Will fix. 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] MSVC buildfarm members are all unhappy
... and the build logs don't show any particular reason for it. What is wrong, and why isn't the buildfarm script capturing a useful error message? 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] MSVC buildfarm members are all unhappy
Tom Lane wrote: ... and the build logs don't show any particular reason for it. What is wrong, and why isn't the buildfarm script capturing a useful error message? It's the format change in the makefile for foreign stuff. The line: Could not match in foreign makefile I don't know why it ends up where it does - when I run the build in my VM, it ends up as the last line which makes it stand out properly. I'll take a look at fixing the actual issue. //Magnus -- 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] MSVC buildfarm members are all unhappy
On Wed, Feb 25, 2009 at 4:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... and the build logs don't show any particular reason for it. What is wrong, and why isn't the buildfarm script capturing a useful error message? Looks like this: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/foreign/Makefile?rev=1.2 broke Mkvcbuild.pm. The attached patch just comments out the offending code in Mkvcbuild.pm - I assume Peter will be putting the FDW stuff back in 8.5. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com fdw.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] MSVC buildfarm members are all unhappy
Dave Page wrote: On Wed, Feb 25, 2009 at 4:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... and the build logs don't show any particular reason for it. What is wrong, and why isn't the buildfarm script capturing a useful error message? Looks like this: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/foreign/Makefile?rev=1.2 broke Mkvcbuild.pm. The attached patch just comments out the offending code in Mkvcbuild.pm - I assume Peter will be putting the FDW stuff back in 8.5. Since we removed it from the general Makefiles, I suggest we actually remove it from the Mkvcbuild.pm file as well. it's still there in the history - just like the general Makefiles. Thoughts? //Magnus -- 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] MSVC buildfarm members are all unhappy
Magnus Hagander mag...@hagander.net writes: Since we removed it from the general Makefiles, I suggest we actually remove it from the Mkvcbuild.pm file as well. it's still there in the history - just like the general Makefiles. +1. Comments are not a substitute for having CVS history ... 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] MSVC buildfarm members are all unhappy
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Since we removed it from the general Makefiles, I suggest we actually remove it from the Mkvcbuild.pm file as well. it's still there in the history - just like the general Makefiles. +1. Comments are not a substitute for having CVS history ... I've applied a version that does this. Hopefully that'll bring the buildfarm back into green. //Magnus -- 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] Database corruption help
On Fri, Feb 13, 2009 at 9:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: The only other corruption mechanism I can think of is that pg_clog might contain commit bits for some logically inconsistent set of transaction numbers, due to some pages of pg_clog having made it to disk and others not. That could result in some of the intermediate tuples in the chain not being seen as dead --- but that's not what we see here either. Or can it be otherwise where some transactions which in fact committed, are marked as aborted because of clog corruption ? In that case, some of the intermediate tuples in the HOT chain may get removed (because we handle aborted heap-only tuples separately) and break the HOT chain. I am also looking at the pruning logic to see if I can spot something unusual. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.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] regression test crashes at tsearch
pg_mb2wchar_with_len() converts server encoded strings to pg_wchar strings. But pg_wchar is typedef'd as unsigned int which is not the same as wchar_t at least on Windows (unsigned short). Oops. The problem is here. TParserInit allocates twice less memory than needed. And it happens if sizeof(wchar_t) sizeof(pg_wchar) and C-locale for non-Windows box. Also for Windows, encoding should be non-utf. So, all p_is* functions are broken in this case because they work with wrong data. . I modified it corresponding to the change in char2wchar() so that wchar2char(char2wchar(x)) becomes x. Though I'm not sure if it is mbstowcs/wcstombs doesn't work with C-locale in other OSes too, so that's not needed. If there's an effective function like pg_wchar2mb_with_len() which converts wchar_t strings to server encoded strings, we had better simply call it for char2wchar(). I don't see a way to produce correct result of char2wchar with C-locale and sizeof(wchar_t) = 2. In summary, I suggest to remove support of C-locale from char2wchar function and tsearch's parser should directly use pg_mb2wchar_with_len() in case of C-locale and multibyte encoding. In all other places char2wchar is called only for non-C locale. Please, test attached patch. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ clocale.patch.gz Description: Unix tar archive -- 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] V4 of PITR performance improvement for 8.4
Hi, My reply to Gregory's comment didn't have any objections. I believe, as I posted to Wiki page, latest posted patch is okay and waiting for review. 2009/2/24 Robert Haas robertmh...@gmail.com: On Sun, Jan 25, 2009 at 7:15 AM, Gregory Stark st...@enterprisedb.com wrote: Koichi Suzuki koichi@gmail.com writes: Please find enclosed 2nd patch of pg_readahead which include a patch to bufer manager to skip prefetch of pages already in shared buffer. I'm a bit confused by this comment. PrefetchBuffer already checks if the page is in shared buffers. What is tricky to avoid is prefetching the same page twice -- since the first prefetch doesn't actually put it in shared buffers there's no way to avoid prefetching it again unless you keep some kind of hash of recently prefetched buffers. For the index scan case I'm debating about whether to add such a cache directly to PrefetchBuffer -- in which case it would remember if some other scan prefetched the same buffer -- or to keep it in the index scan code. Has this issue been resolved? Does this patch need more review? Because if so, I'm guessing it needs to happen RSN. ...Robert -- -- Koichi Suzuki -- 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] Synchronous replication Hot standby patches
On Tue, Feb 24, 2009 at 5:58 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2009-02-24 at 16:52 -0500, Robert Haas wrote: I didn't think I had proposed any such thing, although maybe I'm just not remembering. I'm pretty confused as to what the current thread is all about. http://archives.postgresql.org/pgsql-hackers/2009-01/msg00978.php I still don't see where I suggested removing anything. What Heikki suggested, and I agreed with, was adding something: integrated base backup. I don't think anyone who argued in favour of removal of existing system was aware that we'd lose anything as a result. I think everybody supports the easier-if-possible sentiment that Heikki was expressing; I just don't want to let that be seen as agreement to remove, by default, at a later time. I'm still totally unclear as to what you think anyone might, at some point in the future, propose to remove. I think the more relevant question right now is whether the work Fujii Masao is planning to do for 8.5 is reponsive to the following comment from Heikki: # IMHO, the synchronous replication isn't in such good shape, I'm afraid. I've said # this before, but I'm not happy with the built from spare parts nature of it. You # shouldn't have to configure an archive, file-based log shipping using rsync or # whatever, and pg_standby. All that is in addition to the direct connection between # master and slave. The slave really should be able to just connect to the master, and # download all the WAL it needs directly. That's a huge usability issue if left as is, # but requires very large architectural changes to fix. ...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] Hot standby, running xacts, subtransactions
When we take the snapshot of running transactions in the master, in GetRunningTransactionData(), it only includes top-level xids and those subxids that are in the subxid caches. Overflowed subxids are not included. Isn't that a problem? When the standby initializes the recovery procs using the running xacts information, pg_subtrans doesn't isn't set for the overflowed xids, because that information is not included in the WAL record. If you're lucky, the information is there already, but we don't generally guarantee pg_subtrans to survive crash or restart. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Synchronous replication Hot standby patches
Robert Haas wrote: I think the more relevant question right now is whether the work Fujii Masao is planning to do for 8.5 is reponsive to the following comment from Heikki: # IMHO, the synchronous replication isn't in such good shape, I'm afraid. I've said # this before, but I'm not happy with the built from spare parts nature of it. You # shouldn't have to configure an archive, file-based log shipping using rsync or # whatever, and pg_standby. All that is in addition to the direct connection between # master and slave. The slave really should be able to just connect to the master, and # download all the WAL it needs directly. That's a huge usability issue if left as is, # but requires very large architectural changes to fix. I believe so, see second bullet point in: http://archives.postgresql.org/message-id/3f0b79eb0902240751t13231593g17fbef70664d4...@mail.gmail.com -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Synchronous replication Hot standby patches
On Wed, 2009-02-25 at 22:45 +0200, Heikki Linnakangas wrote: Robert Haas wrote: I think the more relevant question right now is whether the work Fujii Masao is planning to do for 8.5 is reponsive to the following comment from Heikki: # IMHO, the synchronous replication isn't in such good shape, I'm afraid. I've said # this before, but I'm not happy with the built from spare parts nature of it. You # shouldn't have to configure an archive, file-based log shipping using rsync or # whatever, and pg_standby. All that is in addition to the direct connection between # master and slave. The slave really should be able to just connect to the master, and # download all the WAL it needs directly. That's a huge usability issue if left as is, # but requires very large architectural changes to fix. I believe so, see second bullet point in: http://archives.postgresql.org/message-id/3f0b79eb0902240751t13231593g17fbef70664d4...@mail.gmail.com That is exactly what I am against. Note the words get rid of. This prevents parallel data transfer, use of split mirrors and various other techniques. It sounds neater, but it implies removal of useful features. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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, running xacts, subtransactions
On Wed, 2009-02-25 at 22:39 +0200, Heikki Linnakangas wrote: When we take the snapshot of running transactions in the master, in GetRunningTransactionData(), it only includes top-level xids and those subxids that are in the subxid caches. Overflowed subxids are not included. Isn't that a problem? When the standby initializes the recovery procs using the running xacts information, pg_subtrans doesn't isn't set for the overflowed xids, because that information is not included in the WAL record. If you're lucky, the information is there already, but we don't generally guarantee pg_subtrans to survive crash or restart. That is exactly the reason why we don't treat an overflowed snapshot as a valid starting point. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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, recovery procs
On Tue, 2009-02-24 at 23:41 +, Simon Riggs wrote: On Tue, 2009-02-24 at 22:29 +0200, Heikki Linnakangas wrote: overwrites subxids array, and will resurrect any already aborted subtransaction. Isn't XLByteLT(proc-lsn, lsn) always true, because 'lsn' is the lsn of the WAL record we're redoing, so there can't be any procs with an LSN higher than that? I'm wondering whether we need those circumstances at all. The main role of ProcArrayUpdateRecoveryTransactions() is two-fold * initialise snapshot when there isn't one * reduce possibility of FATAL errors that don't write abort records Neither of those needs us to update the subxid cache, so we'd be better off avoiding that altogether in the common case. So we should be able to ignore the lsn and race conditions altogether. We still have a race condition for the initial snapshot, so your concern still holds. Thanks for highlighting it. I'm in the middle of rewriting ProcArrayUpdateRecoveryTransactions() to avoid errors caused by these race conditions. The LSN flag was an attempt to do that, but was insufficient and has now been removed. I'll discuss it more when I've got it working. Seems like we need working code now rather than lengthy debates. I see a solution and almost have it done. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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, running xacts, subtransactions
Simon Riggs wrote: On Wed, 2009-02-25 at 22:39 +0200, Heikki Linnakangas wrote: When we take the snapshot of running transactions in the master, in GetRunningTransactionData(), it only includes top-level xids and those subxids that are in the subxid caches. Overflowed subxids are not included. Isn't that a problem? When the standby initializes the recovery procs using the running xacts information, pg_subtrans doesn't isn't set for the overflowed xids, because that information is not included in the WAL record. If you're lucky, the information is there already, but we don't generally guarantee pg_subtrans to survive crash or restart. That is exactly the reason why we don't treat an overflowed snapshot as a valid starting point. We don't? I don't see anything stopping it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Synchronous replication Hot standby patches
Simon Riggs wrote: On Wed, 2009-02-25 at 22:45 +0200, Heikki Linnakangas wrote: Robert Haas wrote: I think the more relevant question right now is whether the work Fujii Masao is planning to do for 8.5 is reponsive to the following comment from Heikki: # IMHO, the synchronous replication isn't in such good shape, I'm afraid. I've said # this before, but I'm not happy with the built from spare parts nature of it. You # shouldn't have to configure an archive, file-based log shipping using rsync or # whatever, and pg_standby. All that is in addition to the direct connection between # master and slave. The slave really should be able to just connect to the master, and # download all the WAL it needs directly. That's a huge usability issue if left as is, # but requires very large architectural changes to fix. I believe so, see second bullet point in: http://archives.postgresql.org/message-id/3f0b79eb0902240751t13231593g17fbef70664d4...@mail.gmail.com That is exactly what I am against. Note the words get rid of. This prevents parallel data transfer, use of split mirrors and various other techniques. It sounds neater, but it implies removal of useful features. OK, so let's assume that we'll provide an extra facility that doesn't take anything away but which provides for close to zero config setup for the simple case. Frankly, that's what the vast majority of people want, in my experience. 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] Synchronous replication Hot standby patches
Hi, On Thu, Feb 26, 2009 at 6:03 AM, Simon Riggs si...@2ndquadrant.com wrote: That is exactly what I am against. Note the words get rid of. This prevents parallel data transfer, use of split mirrors and various other techniques. It sounds neater, but it implies removal of useful features. OK, ISTM that my description was confusing you, so I removed that statement from the TODO item on wiki. http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Todo_and_Claim Again, I'm not planning to get rid of any existing capabilities unless necessary. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Synchronous replication Hot standby patches
On Thu, 2009-02-26 at 06:15 +0900, Fujii Masao wrote: Hi, On Thu, Feb 26, 2009 at 6:03 AM, Simon Riggs si...@2ndquadrant.com wrote: That is exactly what I am against. Note the words get rid of. This prevents parallel data transfer, use of split mirrors and various other techniques. It sounds neater, but it implies removal of useful features. OK, ISTM that my description was confusing you, so I removed that statement from the TODO item on wiki. http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Todo_and_Claim Again, I'm not planning to get rid of any existing capabilities Good unless necessary. That is not a caveat I will accept, a priori. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Synchronous replication Hot standby patches
On Wed, 2009-02-25 at 16:11 -0500, Andrew Dunstan wrote: OK, so let's assume that we'll provide an extra facility that doesn't take anything away but which provides for close to zero config setup for the simple case. Frankly, that's what the vast majority of people want, in my experience. My experience also. Very much agreed. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Synchronous replication Hot standby patches
On Wed, Feb 25, 2009 at 3:45 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I believe so, see second bullet point in: http://archives.postgresql.org/message-id/3f0b79eb0902240751t13231593g17fbef70664d4...@mail.gmail.com Cool. ...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] Hot standby, running xacts, subtransactions
On Wed, 2009-02-25 at 23:08 +0200, Heikki Linnakangas wrote: That is exactly the reason why we don't treat an overflowed snapshot as a valid starting point. We don't? I don't see anything stopping it. In GetRunningTransactionData() we explicitly set latestRunningXid to InvalidTransactionId if the snapshot is overflowed. That prevents the snapshot from being used to initialise the recovery procs. I'll document that better. You raised that as an annoyance previously because it means that connection in hot standby mode may be delayed in cases of heavy, repeated use of significant numbers of subtransactions. My answer was that there is a way to avoid that but it complicates things and I'm trying my best to avoid complexity in the first release, yet still have it work (this decade :-)) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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, running xacts, subtransactions
You raised that as an annoyance previously because it means that connection in hot standby mode may be delayed in cases of heavy, repeated use of significant numbers of subtransactions. While most users still don't use explicit subtransactions at all, wouldn't this also affect users who use large numbers of stored procedures? --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] Hot standby, running xacts, subtransactions
On Wed, 2009-02-25 at 13:33 -0800, Josh Berkus wrote: You raised that as an annoyance previously because it means that connection in hot standby mode may be delayed in cases of heavy, repeated use of significant numbers of subtransactions. While most users still don't use explicit subtransactions at all, wouldn't this also affect users who use large numbers of stored procedures? If they regularly use more than 64 levels of nested EXCEPTION clauses *and* they start their base backups during heavy usage of those stored procedures, then yes. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Synchronous replication Hot standby patches
Simon Riggs wrote: On Thu, 2009-02-26 at 06:15 +0900, Fujii Masao wrote: Hi, On Thu, Feb 26, 2009 at 6:03 AM, Simon Riggs si...@2ndquadrant.com wrote: That is exactly what I am against. Note the words get rid of. This prevents parallel data transfer, use of split mirrors and various other techniques. It sounds neater, but it implies removal of useful features. OK, ISTM that my description was confusing you, so I removed that statement from the TODO item on wiki. http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Todo_and_Claim Again, I'm not planning to get rid of any existing capabilities Good unless necessary. That is not a caveat I will accept, a priori. What does accept mean above? Are you the sole acceptor for this feature? That is surprising to me. You can say you would vote against it but your wording above seems overly controlling. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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: [COMMITTERS] pgsql: Start background writer during archive recovery.
Hi, I have two questions about the stats collector during recovery. 1) Why does the stats collector need to wait for consistent recovery mode? The activity statistics which bgwriter may send before reaching the mode should be ignored? 2) Why doesn't ServerLoop() try to restart the stats collector when it's not in progress? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] effective_cache_size less than shared_buffers
Should we log a warning at startup when effective_cache_size is less than shared_buffers? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Have \d show child tables that inherit from the specified parent
Hello, Last week, i took some time to check if i was still able to write some basic C code. So i looked into the TODO list and picked some trivial items. This one is very basic, it just shows the child tables of a specific table when you type \d tablename in psql : # create table mother(id SERIAL); # create table daughter() inherits(mother); # create table daughter2() inherits(mother); # create schema plop; # create table plop.daughter3() inherits(mother); # \d mother Table public.mother Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) Child tables: daughter, daughter2, plop.daughter3 I understand that it may not be the best moment to submit such a trivial patch, as most of you are working hard on more important topics. If you think i should submit it later, just let me know. BTW, this is the first patch i submit so any comment is more than welcome. Regards, -- damien clochard dalibo.com | dalibo.org ? logfile ? psql Index: describe.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.199 diff -c -r1.199 describe.c *** describe.c 11 Feb 2009 19:12:04 - 1.199 --- describe.c 25 Feb 2009 22:53:50 - *** *** 1668,1673 --- 1668,1699 } PQclear(result); + /* print child tables */ + printfPQExpBuffer(buf, SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY i.inhseqno;, oid); + + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + for (i = 0; i tuples; i++) + { + const char *s = _(Child tables); + + if (i == 0) + printfPQExpBuffer(buf, %s: %s, s, PQgetvalue(result, i, 0)); + else + printfPQExpBuffer(buf, %*s %s, (int) strlen(s), , PQgetvalue(result, i, 0)); + if (i tuples - 1) + appendPQExpBuffer(buf, ,); + + printTableAddFooter(cont, buf.data); + } + PQclear(result); + + + if (verbose) { const char *s = _(Has OIDs); -- 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] effective_cache_size less than shared_buffers
On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote: Should we log a warning at startup when effective_cache_size is less than shared_buffers? I would say no. Although I could see an argument for the default effective_cache_size always being the same size as shared_buffers. Joshua D. Drake -Kevin -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Have \d show child tables that inherit from the specified parent
On Thu, Feb 26, 2009 at 12:25:08AM +0100, damien clochard wrote: Hello, Last week, i took some time to check if i was still able to write some basic C code. So i looked into the TODO list and picked some trivial items. This one is very basic, it just shows the child tables of a specific table when you type \d tablename in psql : # create table mother(id SERIAL); # create table daughter() inherits(mother); # create table daughter2() inherits(mother); # create schema plop; # create table plop.daughter3() inherits(mother); # \d mother Table public.mother Column | Type | Modifiers +-+- id | integer | not null default nextval('mother_id_seq'::regclass) Child tables: daughter, daughter2, plop.daughter3 I understand that it may not be the best moment to submit such a trivial patch, as most of you are working hard on more important topics. If you think i should submit it later, just let me know. BTW, this is the first patch i submit so any comment is more than welcome. Should the patch (and the feature) use WITH RECURSIVE in order to get the entire tree? Cheers, David (yes, I know it's predictable from me, but WITH RECURSIVE really is a neat new toy ;) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] Have \d show child tables that inherit from the specified parent
David Fetter da...@fetter.org writes: Should the patch (and the feature) use WITH RECURSIVE in order to get the entire tree? See the note at the top of that file that all queries are expected to work with server versions back to 7.4. 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] effective_cache_size less than shared_buffers
Joshua D. Drake wrote: On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote: Should we log a warning at startup when effective_cache_size is less than shared_buffers? I would say no. Although I could see an argument for the default effective_cache_size always being the same size as shared_buffers. That's certainly not what we've meant historically by ECS. Generally it's been the size of shared_buffers *and* the FS cache. If it were just the size of shared_buffers, then we wouldn't need a 2nd setting, would we? --Josh -- 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] effective_cache_size less than shared_buffers
On Wed, 2009-02-25 at 17:04 -0800, Josh Berkus wrote: Joshua D. Drake wrote: On Wed, 2009-02-25 at 17:21 -0600, Kevin Grittner wrote: Should we log a warning at startup when effective_cache_size is less than shared_buffers? I would say no. Although I could see an argument for the default effective_cache_size always being the same size as shared_buffers. That's certainly not what we've meant historically by ECS. Generally it's been the size of shared_buffers *and* the FS cache. If it were just the size of shared_buffers, then we wouldn't need a 2nd setting, would we? We can't determine the size of the FS cache. We can determine the size of the shared_buffers. The idea here is to eliminate one of those by default PostgreSQL is slow issues. Since we are already using X amount of shared_buffers we know we have at least X amount of cache. Sincerely, Joshua D. Drake --Josh -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets
On Wed, Feb 25, 2009 at 12:38 AM, Lawrence, Ramon ramon.lawre...@ubc.ca wrote: -Original Message- From: Robert Haas Sadly, there seem to be a number of cases in the Z7 database where the optimization makes things significantly worse (specifically, queries 2, 3, and 7, but especially query 3). Have you investigated what is going on there? I had thought that we had sufficient safeguards in place to prevent this optimization from kicking in in cases where it doesn't help, but it seems not. There will certainly be real-world databases that are more like Z7 than Z1. I agree that there should be no noticeable performance difference when the optimization is not used (single batch case or no skew). I think the patch achieves this. The optimization is not used in those cases, but we will review to see if it is the code that by-passes the optimization that is causing a difference. Yeah we need to understand what's going on there. The query #3 timing difference is primarily due to a flaw in the experimental setup. For some reason, query #3 got executed before #4 with the optimization on, and executed after #4 with the optimization off. This skewed the results for all runs (due to buffering issues), but is especially noticeable for Z7. Note how query #4 is always faster for the optimization on version even though the optimization is not actually used for those queries (because they were one batch). I expect that if you run query #3 on Z7 in isolation then the results should be basically identical. I have attached the SQL script that Joshua sent me. The raw data I have posted at: http://people.ok.ubc.ca/rlawrenc/test.output I don't think we're really doing this the right way. EXPLAIN ANALYZE has a measurable effect on the results, and we probably ought to stop the database and drop the VM caches after each query. Are the Z1-Z7 datasets on line someplace? I might be able to rig up a script here. ...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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Tue, Jan 27, 2009 at 11:23 AM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Tue, Jan 27, 2009 at 3:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: I already pointed out some pretty serious problems with the updatable views patch. Are you claiming they are trivial to fix? Not at all. I think the deferral of that particular patch is the correct thing to do because there are confirmed, real problems with it that are not realistic to fix in an appropriate timeframe for the release. The primary case that I'm objecting to is HS which you've been saying will take 10 - 12 months to complete having by your own admission not looked at the code or followed the discussion particularly closely. Well, perhaps I'm being pessimistic, or perhaps you're being optimistic. What is undeniable fact is that HS will not be committable this week, which will make it three months since feature freeze. As for when it *will* be committable --- Heikki is saying two weeks if no new problems crop up, but given the rate at which new problems have been found so far, what are the odds of that? We've seen this movie before. Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). ...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] Updates of SE-PostgreSQL 8.4devel patches (r1627)
The series of SE-PostgreSQL patches for v8.4 were updated: [1/5] http://sepgsql.googlecode.com/files/sepgsql-core-8.4devel-r1627.patch [2/5] http://sepgsql.googlecode.com/files/sepgsql-utils-8.4devel-r1627.patch [3/5] http://sepgsql.googlecode.com/files/sepgsql-policy-8.4devel-r1627.patch [4/5] http://sepgsql.googlecode.com/files/sepgsql-docs-8.4devel-r1627.patch [5/5] http://sepgsql.googlecode.com/files/sepgsql-tests-8.4devel-r1627.patch - List of updates: * It is rebased to the latest CVS HEAD. - Adding a new funcction into pg_proc.h made a patch confliction. - Changing usage messages at initdb made a patch confliction. * sepgsqlCheckDatabaseInstallModule() is removed from commands/foreigncmds.c because CreateFdwStmt-library has gone in the recent changes. Rest of parts are unchanged, so don't consider this updates needs to review whole of patches again, please. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] xpath processing brain dead
Andrew Gierth was just pointing out to me how badly broken our XPath processing is. For fear of passing an ill formed fragment of xml to the processor, we strip the xml declaration if any and surround what's left with 'x and '/x' and prepend '/x' to the supposed xpath. This is just horrible. It will break for every xpath expression that doesn't begin with a '/' and probably for many that do. This whole thing is a mess, and I suspect the only fix for now is to undo all the mangling of both the xml and the xpath expression. If the programmer passes an ill formed piece of xml to the processor that is their lookout, but I think we should ensure that we give back correct results on well formed input. The only good piece of news is that the xpath procedures in contrib/xml2 don't apparently suffer these faults. 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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Wed, 2009-02-25 at 22:56 -0500, Robert Haas wrote: Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). I agree. It is time to move on. Joshua D. Drake ...Robert -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Joshua D. Drake wrote: On Wed, 2009-02-25 at 22:56 -0500, Robert Haas wrote: Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). I agree. It is time to move on. Huh? What features are you saying about to be moved on? Is it the updatable-view feature? or rest of all pending ones? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] xpath processing brain dead
Andrew Dunstan and...@dunslane.net writes: For fear of passing an ill formed fragment of xml to the processor, we strip the xml declaration if any and surround what's left with 'x and '/x' and prepend '/x' to the supposed xpath. This is just horrible. I seem to recall having complained about that at the time, but I didn't (and don't) know enough about xpath to do any better. This whole thing is a mess, and I suspect the only fix for now is to undo all the mangling of both the xml and the xpath expression. I don't think we should change the behavior if it's just to arrive at another less-than-desirable behavior. Whacking semantics around afresh with each release does not endear us to users. If we know how to fix it right, great; but if we can't then we should keep compatibility with 8.3 until we can. 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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Thu, 2009-02-26 at 14:17 +0900, KaiGai Kohei wrote: Joshua D. Drake wrote: On Wed, 2009-02-25 at 22:56 -0500, Robert Haas wrote: Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). I agree. It is time to move on. Huh? What features are you saying about to be moved on? Is it the updatable-view feature? or rest of all pending ones? Tom originally stated (as I recall, no flames please) that we would wait for 2 weeks for the hot standby stuff. It has now been four. That is what I and I believe Robert Haas are talking about. Sincerely, Joshua D. Drake Thanks, -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Joshua D. Drake wrote: On Thu, 2009-02-26 at 14:17 +0900, KaiGai Kohei wrote: Joshua D. Drake wrote: On Wed, 2009-02-25 at 22:56 -0500, Robert Haas wrote: Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). I agree. It is time to move on. Huh? What features are you saying about to be moved on? Is it the updatable-view feature? or rest of all pending ones? Tom originally stated (as I recall, no flames please) that we would wait for 2 weeks for the hot standby stuff. It has now been four. That is what I and I believe Robert Haas are talking about. Thanks, it helps me clear. P.S, I would like folks not to forget SE-PostgreSQL. -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Thu, 2009-02-26 at 14:43 +0900, KaiGai Kohei wrote: Joshua D. Drake wrote: Tom originally stated (as I recall, no flames please) that we would wait for 2 weeks for the hot standby stuff. It has now been four. That is what I and I believe Robert Haas are talking about. Thanks, it helps me clear. P.S, I would like folks not to forget SE-PostgreSQL. I assure you that I have not. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Robert Haas wrote: On Tue, Jan 27, 2009 at 11:23 AM, Tom Lane t...@sss.pgh.pa.us wrote: As for when it *will* be committable --- Heikki is saying two weeks if no new problems crop up, but given the rate at which new problems have been found so far, what are the odds of that? We've seen this movie before. Since it's going to take us two weeks to clean up the other loose ends anyway, there's no harm in letting Simon and Heikki try to complete the patch by then. But I'll happily lay a side bet with you about what the situation will be two weeks from now. I think Tom wins this bet, since it's now been four weeks. In fact, he was being optimistic: the loose ends aren't cleaned up either (based on a review of the wiki, we're about half way there: six patches have been committed in the intervening time and seven remain in the queue). Agreed. Simon has finished the pending items he had four weeks ago, but the code clearly isn't ready for commit yet as new issues are cropping up. And I think the way subtransactions are handled, which has been a difficult part of the patch all along, still needs more thinking. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Heikki, Agreed. Simon has finished the pending items he had four weeks ago, but the code clearly isn't ready for commit yet as new issues are cropping up. And I think the way subtransactions are handled, which has been a difficult part of the patch all along, still needs more thinking. Are there issues other than subtransactions? --Josh -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1627)
On Wed, Feb 25, 2009 at 11:04 PM, KaiGai Kohei kai...@ak.jp.nec.com wrote: The series of SE-PostgreSQL patches for v8.4 were updated: [1/5] http://sepgsql.googlecode.com/files/sepgsql-core-8.4devel-r1627.patch [2/5] http://sepgsql.googlecode.com/files/sepgsql-utils-8.4devel-r1627.patch [3/5] http://sepgsql.googlecode.com/files/sepgsql-policy-8.4devel-r1627.patch [4/5] http://sepgsql.googlecode.com/files/sepgsql-docs-8.4devel-r1627.patch [5/5] http://sepgsql.googlecode.com/files/sepgsql-tests-8.4devel-r1627.patch - List of updates: * It is rebased to the latest CVS HEAD. actually i see fails when trying to apply sepgsql-core-8.4devel-r1627.patch to head (in pg_proc.h)... Hunk #4 FAILED at 113. 1 out of 4 hunks FAILED -- saving rejects to file src/include/catalog/pg_proc.h.rej -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1627)
Jaime Casanova wrote: On Wed, Feb 25, 2009 at 11:04 PM, KaiGai Kohei kai...@ak.jp.nec.com wrote: The series of SE-PostgreSQL patches for v8.4 were updated: [1/5] http://sepgsql.googlecode.com/files/sepgsql-core-8.4devel-r1627.patch [2/5] http://sepgsql.googlecode.com/files/sepgsql-utils-8.4devel-r1627.patch [3/5] http://sepgsql.googlecode.com/files/sepgsql-policy-8.4devel-r1627.patch [4/5] http://sepgsql.googlecode.com/files/sepgsql-docs-8.4devel-r1627.patch [5/5] http://sepgsql.googlecode.com/files/sepgsql-tests-8.4devel-r1627.patch - List of updates: * It is rebased to the latest CVS HEAD. actually i see fails when trying to apply sepgsql-core-8.4devel-r1627.patch to head (in pg_proc.h)... Hunk #4 FAILED at 113. 1 out of 4 hunks FAILED -- saving rejects to file src/include/catalog/pg_proc.h.rej I'll check it soon, please wait for a while. Thanks for your interesting! -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Updates of SE-PostgreSQL 8.4devel patches (r1627)
KaiGai Kohei wrote: Jaime Casanova wrote: - List of updates: * It is rebased to the latest CVS HEAD. actually i see fails when trying to apply sepgsql-core-8.4devel-r1627.patch to head (in pg_proc.h)... Hunk #4 FAILED at 113. 1 out of 4 hunks FAILED -- saving rejects to file src/include/catalog/pg_proc.h.rej I'll check it soon, please wait for a while. I could not reproduce this patch confliction you reported. Could you confirm whether the base tree is updated to the latest one, or not? The previous revision (r1608) conflicts to this commit, so it is necessary the base tree to be updated in this two days. http://git.postgresql.org/?p=postgresql.git;a=commitdiff;h=a3c594d20e6e713c2bed8e0ba416ab34cdec8ecf#patch24 [kai...@masu tmp]$ cvs -z3 -d :pserver:anon...@anoncvs.postgresql.org:/projects/cvsroot \ export -r HEAD -d pgsql.cvs pgsql [kai...@masu tmp]$ wget http://sepgsql.googlecode.com/files/sepgsql-core-8.4devel-r1627.patch [kai...@masu tmp]$ cd pgsql.cvs/ [kai...@masu pgsql.cvs]$ cat ../sepgsql-core-8.4devel-r1627.patch | patch -p1 patching file configure : patching file src/include/catalog/pg_database.h patching file src/include/catalog/pg_proc.h (*) patching file src/include/catalog/pg_proc_fn.h : patching file src/include/utils/syscache.h [kai...@masu pgsql.cvs]$ Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Updates of SE-PostgreSQL 8.4devel patches (r1627)
On Thu, Feb 26, 2009 at 1:46 AM, KaiGai Kohei kai...@ak.jp.nec.com wrote: KaiGai Kohei wrote: Jaime Casanova wrote: - List of updates: * It is rebased to the latest CVS HEAD. actually i see fails when trying to apply sepgsql-core-8.4devel-r1627.patch to head (in pg_proc.h)... Hunk #4 FAILED at 113. 1 out of 4 hunks FAILED -- saving rejects to file src/include/catalog/pg_proc.h.rej I'll check it soon, please wait for a while. I could not reproduce this patch confliction you reported. Could you confirm whether the base tree is updated to the latest one, or not? The previous revision (r1608) conflicts to this commit, so it is necessary the base tree to be updated in this two days. ah! please forget it... it's to late here... i updated my local repo but doesn't execute the cvs update -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
On Wed, 2009-02-25 at 22:11 -0800, Josh Berkus wrote: Heikki, Agreed. Simon has finished the pending items he had four weeks ago, but the code clearly isn't ready for commit yet as new issues are cropping up. And I think the way subtransactions are handled, which has been a difficult part of the patch all along, still needs more thinking. Are there issues other than subtransactions? I think his reply states that. The long and short is, what Tom was concerned about is true and Heikki has confirmed it. This patch as nice as it would be to have, isn't ready for prime time. It is time to push this patch to 8.5, close up the rest of whatever is left with other patches and move to Beta. Sincerely, Joshua D. Drake --Josh -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 8.4 release planning (was Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules)
Josh Berkus wrote: Agreed. Simon has finished the pending items he had four weeks ago, but the code clearly isn't ready for commit yet as new issues are cropping up. And I think the way subtransactions are handled, which has been a difficult part of the patch all along, still needs more thinking. Are there issues other than subtransactions? Subtransactions, and transaction tracking in general. I haven't looked at the other parts in detail yet. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers