Re: [PATCHES] TODO item -- Improve psql's handling of multi-line
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > On Wed, 7 Dec 2005, Andrew Dunstan wrote: >> A zero byte is probably a pretty bad choice. Some other low valued byte >> (e.g. \x01 ) would probably work better. > Currently I replace '\n' with the '\x01' as Andrew suggested. Won't this get confused by some of the Far Eastern encodings we support? The zero-byte approach is at least proof against that. But what we need to ask is whether we can expect readline to cope with either. The patch *looks* pretty ugly: random insertions of blank space, general failure to conform to the project's code layout conventions, etc. (Some of this would get cleaned up by pgindent, but I'm not sure how much.) Also I get the impression that the patch enforces a lot of history maintenance overhead even in the non-USE_READLINE case, which is surely useless. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] TODO item -- Improve psql's handling of multi-line
On Wed, 7 Dec 2005, Andrew Dunstan wrote: > Sergey E. Koposov wrote: > > >I also wanted this feature for a long time. > >And I agree that it is rather simple to just remove the \n when writing to > >the > >readline history file, but I don't think that's what everybody wants. I > >think that people want to preserve the original formatting... > > > >So in that case the solution can be just putting some symbol instead of \n > >in the history file, and during the loading of that file replace it back > >(that symbol can be zero byte for example). But I'm not sure that people > >will like that solution too. > > > A zero byte is probably a pretty bad choice. Some other low valued byte > (e.g. \x01 ) would probably work better. > > And I agree that format preservation is highly desirable. If actually the idea with just replacing '\n' in the history with other byte is not rejected I'm sending the new version of the psql patch including also the saving of the multiline queries in the history (even after exiting the psql). Currently I replace '\n' with the '\x01' as Andrew suggested. Regards, Sergey * Sergey E. Koposov Max-Planck Institut for Astronomy Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] Index: help.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v retrieving revision 1.106 diff -c -r1.106 help.c *** help.c 15 Oct 2005 02:49:40 - 1.106 --- help.c 8 Dec 2005 04:04:17 - *** *** 7,12 --- 7,13 */ #include "postgres_fe.h" #include "common.h" + #include "pqexpbuffer.h" #include "input.h" #include "print.h" #include "help.h" Index: input.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/input.c,v retrieving revision 1.46 diff -c -r1.46 input.c *** input.c 15 Oct 2005 02:49:40 - 1.46 --- input.c 8 Dec 2005 04:04:17 - *** *** 7,14 */ #include "postgres_fe.h" - #include "input.h" #include "pqexpbuffer.h" #include "settings.h" #include "tab-complete.h" #include "common.h" --- 7,14 */ #include "postgres_fe.h" #include "pqexpbuffer.h" + #include "input.h" #include "settings.h" #include "tab-complete.h" #include "common.h" *** *** 90,107 #ifdef USE_READLINE char *s; - static char *prev_hist = NULL; - if (useReadline) /* On some platforms, readline is declared as readline(char *) */ s = readline((char *) prompt); else s = gets_basic(prompt); ! if (useHistory && s && s[0]) { - enum histcontrol HC; HC = GetHistControlConfig(); if (((HC & hctl_ignorespace) && s[0] == ' ') || --- 90,146 #ifdef USE_READLINE char *s; if (useReadline) /* On some platforms, readline is declared as readline(char *) */ s = readline((char *) prompt); else s = gets_basic(prompt); ! return s; ! #else ! return gets_basic(prompt); ! #endif ! } ! ! /* Put the line in the history buffer and also add the trailing \n */ ! void pgadd_history(char *s, PQExpBuffer history_buf) ! { ! #ifdef USE_READLINE ! ! int slen; ! if (useReadline && useHistory && s && s[0]) ! { ! slen = strlen(s); ! if (s[slen-1]=='\n') ! { ! appendPQExpBufferStr(history_buf, s); ! } ! else ! { ! appendPQExpBufferStr(history_buf, s); ! appendPQExpBufferChar(history_buf, '\n'); ! } ! } ! #endif ! } ! ! ! /* Feed the contents of the history buffer to readline */ ! void pgflush_history(PQExpBuffer history_buf) ! { ! #ifdef USE_READLINE ! char *s; ! static char *prev_hist; ! int slen, i; ! ! if (useReadline && useHistory ) { + enum histcontrol HC; + + s = history_buf->data; + prev_hist = NULL; + HC = GetHistControlConfig(); if (((HC & hctl_ignorespace) && s[0] == ' ') || *** *** 112,130 else { free(prev_hist); prev_hist = pg_strdup(s); add_history(s); } } - return s; - #else - return gets_basic(prompt); #endif } /* * gets_fromFile * --- 151,184 else { free(prev_hist); + slen = strlen(s); + /* Trim the trailing \n
Re: [PATCHES] TODO item -- Improve psql's handling of multi-line
Sergey E. Koposov wrote: I also wanted this feature for a long time. And I agree that it is rather simple to just remove the \n when writing to the readline history file, but I don't think that's what everybody wants. I think that people want to preserve the original formatting... So in that case the solution can be just putting some symbol instead of \n in the history file, and during the loading of that file replace it back (that symbol can be zero byte for example). But I'm not sure that people will like that solution too. A zero byte is probably a pretty bad choice. Some other low valued byte (e.g. \x01 ) would probably work better. And I agree that format preservation is highly desirable. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO item -- Improve psql's handling of multi-line queries
[ Previous version removed.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Sergey E. Koposov wrote: > On Wed, 7 Dec 2005, Andreas Seltenreich wrote: > > > Sergey E. Koposov schrob: > > > > > I submit the new version of my patch (against the CVS tip), correcting > > > the > > > problem with \edit (pointed by Andreas). So now everything works fine. > > > > I think there's a pgflush_history() call missing somewhere, since the > > buffer isn't flushed on a control-c. The fresh query is appended to > > the aborted one in the history: > > > Wouldn't it be more elegant to use the facilities in libpq's > > pqexpbuffer.h for the history buffer instead of passing the > > buffer-length around as a separate function argument and doing custom > > string operations? > > > Thank you, Andreas. I corrected the bug and also switched to pqexpbuffer > instead of my own work with the strings. > > The new patch is attached. > > > > > The multi-line history entries are not preserved between psql > > invocations. Bash does solve this by folding multi-line commands into > > a single line where possible. But I have to admit, this could be sold > > as another TODO item :-) > > > > I also wanted this feature for a long time. > And I agree that it is rather simple to just remove the \n when writing to the > readline history file, but I don't think that's what everybody wants. I > think that people want to preserve the original formatting... > > So in that case the solution can be just putting some symbol instead of \n > in the history file, and during the loading of that file replace it back > (that symbol can be zero byte for example). But I'm not sure that people > will like that solution too. > > > Regards, > Sergey > > * > Sergey E. Koposov > Max-Planck Institut for Astronomy > Web: http://lnfm1.sai.msu.ru/~math > E-mail: [EMAIL PROTECTED] Content-Description: [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] TODO item -- Improve psql's handling of multi-line
On Wed, 7 Dec 2005, Andreas Seltenreich wrote: > Sergey E. Koposov schrob: > > > I submit the new version of my patch (against the CVS tip), correcting the > > problem with \edit (pointed by Andreas). So now everything works fine. > > I think there's a pgflush_history() call missing somewhere, since the > buffer isn't flushed on a control-c. The fresh query is appended to > the aborted one in the history: > Wouldn't it be more elegant to use the facilities in libpq's > pqexpbuffer.h for the history buffer instead of passing the > buffer-length around as a separate function argument and doing custom > string operations? Thank you, Andreas. I corrected the bug and also switched to pqexpbuffer instead of my own work with the strings. The new patch is attached. > > The multi-line history entries are not preserved between psql > invocations. Bash does solve this by folding multi-line commands into > a single line where possible. But I have to admit, this could be sold > as another TODO item :-) > I also wanted this feature for a long time. And I agree that it is rather simple to just remove the \n when writing to the readline history file, but I don't think that's what everybody wants. I think that people want to preserve the original formatting... So in that case the solution can be just putting some symbol instead of \n in the history file, and during the loading of that file replace it back (that symbol can be zero byte for example). But I'm not sure that people will like that solution too. Regards, Sergey * Sergey E. Koposov Max-Planck Institut for Astronomy Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] Index: help.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v retrieving revision 1.106 diff -c -r1.106 help.c *** help.c 15 Oct 2005 02:49:40 - 1.106 --- help.c 8 Dec 2005 01:40:14 - *** *** 7,12 --- 7,13 */ #include "postgres_fe.h" #include "common.h" + #include "pqexpbuffer.h" #include "input.h" #include "print.h" #include "help.h" Index: input.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/input.c,v retrieving revision 1.46 diff -c -r1.46 input.c *** input.c 15 Oct 2005 02:49:40 - 1.46 --- input.c 8 Dec 2005 01:40:14 - *** *** 7,14 */ #include "postgres_fe.h" - #include "input.h" #include "pqexpbuffer.h" #include "settings.h" #include "tab-complete.h" #include "common.h" --- 7,14 */ #include "postgres_fe.h" #include "pqexpbuffer.h" + #include "input.h" #include "settings.h" #include "tab-complete.h" #include "common.h" *** *** 90,107 #ifdef USE_READLINE char *s; - static char *prev_hist = NULL; - if (useReadline) /* On some platforms, readline is declared as readline(char *) */ s = readline((char *) prompt); else s = gets_basic(prompt); ! if (useHistory && s && s[0]) { - enum histcontrol HC; HC = GetHistControlConfig(); if (((HC & hctl_ignorespace) && s[0] == ' ') || --- 90,146 #ifdef USE_READLINE char *s; if (useReadline) /* On some platforms, readline is declared as readline(char *) */ s = readline((char *) prompt); else s = gets_basic(prompt); ! return s; ! #else ! return gets_basic(prompt); ! #endif ! } ! ! /* Put the line in the history buffer and also add the trailing \n */ ! void pgadd_history(char *s, PQExpBuffer history_buf) ! { ! #ifdef USE_READLINE ! ! int slen; ! if (useReadline && useHistory && s && s[0]) ! { ! slen = strlen(s); ! if (s[slen-1]=='\n') ! { ! appendPQExpBufferStr(history_buf, s); ! } ! else ! { ! appendPQExpBufferStr(history_buf, s); ! appendPQExpBufferChar(history_buf, '\n'); ! } ! } ! #endif ! } ! ! ! /* Feed the contents of the history buffer to readline */ ! void pgflush_history(PQExpBuffer history_buf) ! { ! #ifdef USE_READLINE ! char *s; ! static char *prev_hist; ! int slen, i; ! ! if (useReadline && useHistory ) { + enum histcontrol HC; + + s = history_buf->data; + prev_hist = NULL; + HC = GetHistControlConfig(); if (((HC & hctl_ignorespace) && s[0] == ' ') || *** *** 112,130 else { free(prev_h
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 17:40 +, Simon Riggs wrote: > On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: > > Simon Riggs <[EMAIL PROTECTED]> writes: > > > We discussed an optimization of VACUUM here > > > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php > > > that would allow VACUUM to complete faster by avoiding scanning the > > > indexes when no rows were removed from the heap by the VACUUM. > > > > Unfortunately I can't read that message right now because archives > > isn't responding, but this seems like a pretty bad idea to me. > > You still have to do the vacuum cleanup pass (at least in the btree > > case, and the only reason gist doesn't need it is it's not yet up > > to speed) so there's no real savings. > > There are real savings; this is not a theoretical patch. > > One pass of an index is faster than two, always. Test results on a 1.2GB table, 10^6 rows and 3 indexes: w/o optimization87s with optimization 56s Timings taken with primed cache, to allow reasonable comparison without confusing the issue with hint bit updates etc. Performance gain is dependant upon: 1. size of index 2. logical/physical ordering of index pages These tests performed immediately after load, which is best case, but also the main case for which I seek to optimize. postgres=# select pg_relation_size('vactest'); pg_relation_size -- 1204707328 vacuum verbose vactest; psql:vacnout1.sql:3: INFO: vacuuming "public.vactest" psql:vacnout1.sql:3: INFO: index "vactest_idx1" now contains 1000 row versions in 21899 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.25s/0.03u sec elapsed 5.81 sec. psql:vacnout1.sql:3: INFO: index "vactest_idx2" now contains 1000 row versions in 21899 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.26s/0.04u sec elapsed 5.78 sec. psql:vacnout1.sql:3: INFO: index "vactest_idx3" now contains 1000 row versions in 21899 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.23s/0.05u sec elapsed 5.69 sec. psql:vacnout1.sql:3: INFO: "vactest": found 0 removable, 1000 nonremovable row versions in 147059 pages DETAIL: 0 dead row versions cannot be removed yet. There were 2 unused item pointers. 0 pages are entirely empty. CPU 2.59s/0.58u sec elapsed 56.02 sec. psql:vacnout1.sql:3: INFO: vacuuming "pg_toast.pg_toast_16415" psql:vacnout1.sql:3: INFO: index "pg_toast_16415_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. psql:vacnout1.sql:3: INFO: "pg_toast_16415": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM update vactest set col1a =1 where col1a = 1 and col1b = 1 and col1c = 1; UPDATE 1 vacuum verbose vactest; psql:vacnout1.sql:6: INFO: vacuuming "public.vactest" psql:vacnout1.sql:6: INFO: index "vactest_idx1" now contains 1000 row versions in 21899 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.94s/0.45u sec elapsed 15.29 sec. psql:vacnout1.sql:6: INFO: index "vactest_idx2" now contains 1000 row versions in 21899 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.03s/0.40u sec elapsed 16.80 sec. psql:vacnout1.sql:6: INFO: index "vactest_idx3" now contains 1000 row versions in 21899 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.94s/0.49u sec elapsed 15.84 sec. psql:vacnout1.sql:6: INFO: "vactest": removed 1 row versions in 1 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.02 sec. psql:vacnout1.sql:6: INFO: "vactest": found 1 removable, 1000 nonremovable row versions in 147059 pages DETAIL: 0 dead row versions cannot be removed yet. There were 1 unused item pointers. 0 pages are entirely empty. CPU 4.99s/1.85u sec elapsed 87.20 sec. psql:vacnout1.sql:6: INFO: vacuuming "pg_toast.pg_toast_16415" psql:vacnout1.sql:6: INFO: index "pg_toast_16415_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. psql:vacnout1.sql:6: INFO: "pg_toast_16415": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Other details available. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can
Re: [PATCHES] TODO-Item: Rename of constraints
On Mon, 2005-12-05 at 10:24 +0100, Joachim Wieland wrote: > I propose the appended patch for the following TODO-items: > > o %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME > o Have ALTER INDEX update the name of a constraint using that index > o Add ALTER TABLE RENAME CONSTRAINT, update index name also > Patch looks very interesting. I've read the additional docs you supply and agree with the thought processes therein. Not looked at detailed coding. Passes make check for me. My compiler complains: pg_constraint.c: In function ‘RenameConstraint’: pg_constraint.c:726: warning: ISO C90 forbids mixed declarations and code This probably allows you to rename an inherited constraint to another name. Not sure if that is a problem, but it probably ought to throw an error, but I'm not sure who would care. I'll test some more to see if my work on inherited constraints conflicts in any way. Patch applies (with some fuzz) alongside my inherited constraints patch. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Inherited Constraints
Following patch implements record of whether a constraint is inherited or not, and prevents dropping of inherited constraints. What it doesn't do: It doesn't yet prevent dropping the parent constraint, which is wrong, clearly, but what to do about it? 1. make dropping a constraint drop all constraints dependent upon it (without any explicit cascade) 2. add a new clause to ALTER TABLE DROP CONSTRAINT CASCADE I prefer (1), since it is SQL Standard compliant, easier to remember and automatic de-inheritance is the natural opposite of the automatic inheritance process. Current patch passes make check on cvstip, applies cleanly. Further patch will utilise this new knowledge to reduce the number of tests made during constraint_exclusion. Best Regards, Simon Riggs Index: src/backend/access/common/tupdesc.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/common/tupdesc.c,v retrieving revision 1.113 diff -c -r1.113 tupdesc.c *** src/backend/access/common/tupdesc.c 22 Nov 2005 18:17:05 - 1.113 --- src/backend/access/common/tupdesc.c 7 Dec 2005 20:15:04 - *** *** 194,199 --- 194,200 cpy->check[i].ccname = pstrdup(constr->check[i].ccname); if (constr->check[i].ccbin) cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin); + cpy->check[i].ccinh = constr->check[i].ccinh; } } *** *** 362,368 for (j = 0; j < n; check2++, j++) { if (strcmp(check1->ccname, check2->ccname) == 0 && ! strcmp(check1->ccbin, check2->ccbin) == 0) break; } if (j >= n) --- 363,370 for (j = 0; j < n; check2++, j++) { if (strcmp(check1->ccname, check2->ccname) == 0 && ! strcmp(check1->ccbin, check2->ccbin) == 0 && ! check1->ccinh == check2->ccinh) break; } if (j >= n) Index: src/backend/catalog/heap.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/heap.c,v retrieving revision 1.293 diff -c -r1.293 heap.c *** src/backend/catalog/heap.c 22 Nov 2005 18:17:08 - 1.293 --- src/backend/catalog/heap.c 7 Dec 2005 20:15:06 - *** *** 72,78 Oid new_rel_oid, char new_rel_kind); static void RelationRemoveInheritance(Oid relid); ! static void StoreRelCheck(Relation rel, char *ccname, char *ccbin); static void StoreConstraints(Relation rel, TupleDesc tupdesc); static void SetRelationNumChecks(Relation rel, int numchecks); --- 72,78 Oid new_rel_oid, char new_rel_kind); static void RelationRemoveInheritance(Oid relid); ! static void StoreRelCheck(Relation rel, char *ccname, char *ccbin, bool ccinh); static void StoreConstraints(Relation rel, TupleDesc tupdesc); static void SetRelationNumChecks(Relation rel, int numchecks); *** *** 1305,1311 * in the pg_class entry for the relation. */ static void ! StoreRelCheck(Relation rel, char *ccname, char *ccbin) { Node *expr; char *ccsrc; --- 1305,1311 * in the pg_class entry for the relation. */ static void ! StoreRelCheck(Relation rel, char *ccname, char *ccbin, bool ccinh) { Node *expr; char *ccsrc; *** *** 1366,1371 --- 1366,1372 CONSTRAINT_CHECK, /* Constraint Type */ false, /* Is Deferrable */ false, /* Is Deferred */ + ccinh,/* Is Inherited */ RelationGetRelid(rel), /* relation */ attNos, /* attrs in the constraint */ keycount, /* # attrs in the constraint */ *** *** 1414,1420 for (i = 0; i < constr->num_check; i++) StoreRelCheck(rel, constr->check[i].ccname, ! constr->check[i].ccbin); if (constr->num_check > 0) SetRelationNumChecks(rel, constr->num_check); --- 1415,1422 for (i = 0; i < constr->num_check; i++) StoreRelCheck(rel, constr->check[i].ccname, ! constr->check[i].ccbin, ! constr->check[i].ccinh); if (constr->num_check > 0) SetRelationNumChecks(rel, constr->num_check); *** *** 1619,1625 /* * OK, store it. */ ! StoreRelCheck(rel, ccname, nodeToString(expr)); numchecks++; --- 1621,1627 /* * OK, store it. */ ! StoreRelCheck(rel, ccname, nodeToString(expr), false); numchecks++; *** *** 1782,1788 */ int RemoveRelConstraints(Relation rel, const char *constrName, ! DropBehavior behavior) { int ndeleted = 0; Relation conrel; --- 1784,1790 */ int RemoveRelConstraints(Relation rel, const char *constrName, ! DropBehavior behavior, bool isChild) { int ndeleted = 0; Relation conrel; *** *** 1813,1818 --- 1815,1828 { ObjectAddress conobj; + /* + *
Re: [PATCHES] Patch to allow contrib/pgbench files to have blank lines
On 11/23/05, David Fetter <[EMAIL PROTECTED]> wrote: > Having blank lines in -f scripts was causing silent failures. This > fixes it, for some value of "fixes." If it's OK, please apply to 8.1 > CURRENT and CVS TIP :) > if (strncmp(buf, "\n", 1) != 0) { > commands = process_commands(buf); > if (commands == NULL) > { > fclose(fd); > return false; > } > } else { > lineno++; Above line will cause a skip in the my_commands array, leaving a garbage pointer. Removing it will fix things. > continue; > } > > my_commands[lineno] = commands; -- Nicolas Barbier http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TODO item -- Improve psql's handling of multi-line queries
Sergey E. Koposov schrob: > I submit the new version of my patch (against the CVS tip), correcting the > problem with \edit (pointed by Andreas). So now everything works fine. I think there's a pgflush_history() call missing somewhere, since the buffer isn't flushed on a control-c. The fresh query is appended to the aborted one in the history: --8<---cut here---start->8--- nnpg=# select nnpg-# something_i_would_rather_not_submit_yet nnpg-# -- nnpg=# select 1; ?column? -- 1 (1 row) nnpg=# -- will now yield the following history entry nnpg=# select something_i_would_rather_not_submit_yet select 1; --8<---cut here---end--->8--- Some more comments: Wouldn't it be more elegant to use the facilities in libpq's pqexpbuffer.h for the history buffer instead of passing the buffer-length around as a separate function argument and doing custom string operations? The multi-line history entries are not preserved between psql invocations. Bash does solve this by folding multi-line commands into a single line where possible. But I have to admit, this could be sold as another TODO item :-) regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > We discussed an optimization of VACUUM here > > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php > > that would allow VACUUM to complete faster by avoiding scanning the > > indexes when no rows were removed from the heap by the VACUUM. > > Unfortunately I can't read that message right now because archives > isn't responding, but this seems like a pretty bad idea to me. > You still have to do the vacuum cleanup pass (at least in the btree > case, and the only reason gist doesn't need it is it's not yet up > to speed) so there's no real savings. There are real savings; this is not a theoretical patch. One pass of an index is faster than two, always. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Skipping VACUUM of indexes when no work required
On Wed, 2005-12-07 at 09:55 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > We discussed an optimization of VACUUM here > > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php > > that would allow VACUUM to complete faster by avoiding scanning the > > indexes when no rows were removed from the heap by the VACUUM. resolved. > > Unfortunately I can't read that message right now because archives > isn't responding, but this seems like a pretty bad idea to me. > You still have to do the vacuum cleanup pass (at least in the btree > case, and the only reason gist doesn't need it is it's not yet up > to speed) so there's no real savings. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl, ODBCng - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Another example moved to SQL
Simon Riggs <[EMAIL PROTECTED]> writes: > Code comment moved to use an SQL example; hangover from pre-1995 > comments. Done. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Minor spelling correction on cvstip
Simon Riggs <[EMAIL PROTECTED]> writes: > ! MUST_HAVE_OIDS, /* WITH OIDS explicitly > specified */ > ! MUST_NOT_HAVE_OIDS, /* WITHOUT OIDS explicitly > specified */ Done. Glimpse found the same mistake in foreign_key.sql, too. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Skipping VACUUM of indexes when no work required
Simon Riggs <[EMAIL PROTECTED]> writes: > We discussed an optimization of VACUUM here > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php > that would allow VACUUM to complete faster by avoiding scanning the > indexes when no rows were removed from the heap by the VACUUM. Unfortunately I can't read that message right now because archives isn't responding, but this seems like a pretty bad idea to me. You still have to do the vacuum cleanup pass (at least in the btree case, and the only reason gist doesn't need it is it's not yet up to speed) so there's no real savings. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Another example moved to SQL
Code comment moved to use an SQL example; hangover from pre-1995 comments. Best Regards, Simon Riggs Index: src/backend/executor/execProcnode.c === RCS file: /projects/cvsroot/pgsql/src/backend/executor/execProcnode.c,v retrieving revision 1.51 diff -c -r1.51 execProcnode.c *** src/backend/executor/execProcnode.c 15 Oct 2005 02:49:16 - 1.51 --- src/backend/executor/execProcnode.c 7 Dec 2005 12:18:26 - *** *** 32,38 * suppose we want the age of the manager of the shoe department and * the number of employees in that department. so we have the query: * ! *retrieve (DEPT.no_emps, EMP.age) *where EMP.name = DEPT.mgr and * DEPT.name = "shoe" * --- 32,38 * suppose we want the age of the manager of the shoe department and * the number of employees in that department. so we have the query: * ! *select DEPT.no_emps, EMP.age *where EMP.name = DEPT.mgr and * DEPT.name = "shoe" * ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] Minor spelling correction on cvstip
Best Regards, Simon Riggs Index: src/include/nodes/parsenodes.h === RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v retrieving revision 1.297 diff -c -r1.297 parsenodes.h *** src/include/nodes/parsenodes.h 28 Nov 2005 04:35:32 - 1.297 --- src/include/nodes/parsenodes.h 7 Dec 2005 12:08:27 - *** *** 660,667 typedef enum ContainsOids { ! MUST_HAVE_OIDS,/* WITH OIDS explicitely specified */ ! MUST_NOT_HAVE_OIDS, /* WITHOUT OIDS explicitely specified */ DEFAULT_OIDS/* neither specified; use the default, which * is the value of the default_with_oids GUC * var */ --- 660,667 typedef enum ContainsOids { ! MUST_HAVE_OIDS,/* WITH OIDS explicitly specified */ ! MUST_NOT_HAVE_OIDS, /* WITHOUT OIDS explicitly specified */ DEFAULT_OIDS/* neither specified; use the default, which * is the value of the default_with_oids GUC * var */ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Skipping VACUUM of indexes when no work required
We discussed an optimization of VACUUM here http://archives.postgresql.org/pgsql-hackers/2005-09/msg00046.php that would allow VACUUM to complete faster by avoiding scanning the indexes when no rows were removed from the heap by the VACUUM. Patch applies cleanly on cvstip; make check passes. Tests shows clear performance gain when no rows removed by VACUUM. Not as useful as may once have been, but certainly no loss either, whatever happens in the future with VACUUM. Best Regards, Simon Riggs Index: src/backend/access/gist/gistvacuum.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/gist/gistvacuum.c,v retrieving revision 1.11 diff -c -r1.11 gistvacuum.c *** src/backend/access/gist/gistvacuum.c 22 Nov 2005 18:17:05 - 1.11 --- src/backend/access/gist/gistvacuum.c 7 Dec 2005 11:52:43 - *** *** 125,131 if (chldtuple.ituplen > 1) { /* ! * child was splitted, so we need mark completion * insert(split) */ int j; --- 125,131 if (chldtuple.ituplen > 1) { /* ! * child was split, so we need mark completion * insert(split) */ int j; *** *** 329,337 } /* ! * For usial vacuum just update FSM, for full vacuum * reforms parent tuples if some of childs was deleted or changed, ! * update invalid tuples (they can exsist from last crash recovery only), * tries to get smaller index */ --- 329,337 } /* ! * For usual vacuum just update FSM, for full vacuum * reforms parent tuples if some of childs was deleted or changed, ! * update invalid tuples (they can exist from last crash recovery only), * tries to get smaller index */ *** *** 505,514 *ptr; bool needLock; ! stack = (GistBDItem *) palloc0(sizeof(GistBDItem)); ! ! stack->blkno = GIST_ROOT_BLKNO; ! needFullVacuum = false; while (stack) { --- 505,519 *ptr; bool needLock; ! if (callback_state) ! { ! stack = (GistBDItem *) palloc0(sizeof(GistBDItem)); ! ! stack->blkno = GIST_ROOT_BLKNO; ! needFullVacuum = false; ! } ! else ! stack = NULL; while (stack) { Index: src/backend/access/hash/hash.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/hash/hash.c,v retrieving revision 1.82 diff -c -r1.82 hash.c *** src/backend/access/hash/hash.c 6 Nov 2005 19:29:00 - 1.82 --- src/backend/access/hash/hash.c 7 Dec 2005 11:52:43 - *** *** 504,509 --- 504,520 tuples_removed = 0; num_index_tuples = 0; + /* return statistics */ + num_pages = RelationGetNumberOfBlocks(rel); + + result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult)); + result->num_pages = num_pages; + + if (!callback_state) + { + PG_RETURN_POINTER(result); + } + /* * Read the metapage to fetch original bucket and tuple counts. Also, we * keep a copy of the last-seen metapage so that we can use its *** *** 652,662 _hash_wrtbuf(rel, metabuf); - /* return statistics */ - num_pages = RelationGetNumberOfBlocks(rel); - - result = (IndexBulkDeleteResult *) palloc0(sizeof(IndexBulkDeleteResult)); - result->num_pages = num_pages; result->num_index_tuples = num_index_tuples; result->tuples_removed = tuples_removed; --- 663,668 Index: src/backend/access/index/indexam.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/index/indexam.c,v retrieving revision 1.87 diff -c -r1.87 indexam.c *** src/backend/access/index/indexam.c 3 Dec 2005 05:51:00 - 1.87 --- src/backend/access/index/indexam.c 7 Dec 2005 11:52:44 - *** *** 685,690 --- 685,695 * callback routine tells whether a given main-heap tuple is * to be deleted * + * passing NULL callback_state can be interpreted by the + * index access method as meaning that the index does not need + * to be scanned in logical sequence to remove rows for this call + * index_vacuum_cleanup is always required after this, however. + * * return value is an optional palloc'd struct of statistics * */ Index: src/backend/access/nbtree/nbtree.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/nbtree/nbtree.c,v retrieving revision 1.134 diff -c -r1.134 nbtree.c *** src/backend/access/nbtree/nbtree.c 22 Nov 2005 18:17:06 - 1.134 --- src/backend/access/nbtree/nbtree.c 7 Dec 2005 11:52:45 - *** *** 587,592 --- 587,593 int ndeletable; Buffer buf; BlockNumber num_pages; + boolscanindex = true; tuples_removed = 0; num_index_tup