[PATCHES] Better psql tab-completion support for schemas and tables
Full support for all schema and table name combinations when getting a list of attributes. All of the following will now work: select * from information_schema.columns where tab select * from foo where tab select * from user where tab select * from foo where tab select * from Uppercase.lower where tab select * from gtsm.com.foo.Bar where tab select * from GTSM.com.foo where tab Also applies to other places that get lists of columns: insert into, alter table, create index, etc. -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200710211212 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 Index: tab-complete.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v retrieving revision 1.167 diff -r1.167 tab-complete.c 55a56 #include stringutils.h 127,130c128,132 static const char *completion_charp; /* to pass a string */ static const char *const * completion_charpp; /* to pass a list of strings */ static const char *completion_info_charp; /* to pass a second string */ static const SchemaQuery *completion_squery; /* to pass a SchemaQuery */ --- static const char *completion_charp;/* to pass a string */ static const char *const * completion_charpp; /* to pass a list of strings */ static const char *completion_info_charp; /* to pass a second string */ static const char *completion_info_charp2; /* to pass a third string */ static const SchemaQuery *completion_squery;/* to pass a SchemaQuery */ 148,149c150,164 #define COMPLETE_WITH_ATTR(table, addon) \ do {completion_charp = Query_for_list_of_attributes addon; completion_info_charp = table; matches = completion_matches(text, complete_from_query); } while(0) --- #define COMPLETE_WITH_ATTR(relation, addon) \ do {\ completion_schema = strtokx(relation, \t\n\r, ., \, 0, false, false, pset.encoding); \ strtokx(NULL, \t\n\r, ., \, 0, false, false, pset.encoding); \ completion_table = strtokx(NULL, \t\n\r, ., \, 0, false, false, pset.encoding); \ if (NULL == completion_table) { \ completion_charp = Query_for_list_of_attributes addon; \ completion_info_charp = relation;\ } \ else {\ completion_charp = Query_for_list_of_attributes_with_schema addon; \ completion_info_charp = completion_table; \ completion_info_charp2 = completion_schema; \ } \ matches = completion_matches(text, complete_from_relation_query); } while(0) 315c330 * completion_info_charp. --- * completion_info_charp. A third %s is replaced by completion_info_charp2. 328c343,344 AND pg_catalog.quote_ident(relname)='%s' \ --- AND (pg_catalog.quote_ident(relname)='%s' \ OR '\' || pg_catalog.quote_ident(relname) || '\'='%s') \ 330a347,359 #define Query_for_list_of_attributes_with_schema \ SELECT pg_catalog.quote_ident(attname) \ FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n \ WHERE c.oid = a.attrelid \ AND n.oid = c.relnamespace \ AND a.attnum 0 \ AND NOT a.attisdropped \ AND substring(pg_catalog.quote_ident(attname),1,%d)='%s' \ AND (pg_catalog.quote_ident(relname)='%s' \ OR '\' || pg_catalog.quote_ident(relname) || '\' ='%s') \ AND (pg_catalog.quote_ident(nspname)='%s' \ OR '\' || pg_catalog.quote_ident(nspname) || '\' ='%s') 499a529 static char *complete_from_relation_query(const char *text, int state); 552a583,585 /* We may want to separate a word into a table and schema */ char *completion_schema, *completion_table; 585a619,621 completion_info_charp2 = NULL; completion_schema = NULL; completion_table = NULL; 588c624 * Scan the input line before our current position for the last four --- * Scan the input line before our current position for the last five 2202c2238 /* The following two functions are wrappers for _complete_from_query */ --- /* The following three functions are wrappers for _complete_from_query */ 2215a2252,2256 static char * complete_from_relation_query(const char *text, int state) { return _complete_from_query(2, text, state); } 2219c2260 The query can be one of two kinds: --- The query can be one of three kinds: 2224a2266,2269 - A simple query as above, but with two or four additional %s in it, which are replaced by completion_info_charp (first two), and by completion_info_charp2 for the second two if needed. or: 2251a2297 char *e_info_charp2; 2275a2322,2333 if (completion_info_charp2) { size_t charp_len; charp_len = strlen(completion_info_charp2); e_info_charp2 = pg_malloc(charp_len * 2 + 1); PQescapeString(e_info_charp2, completion_info_charp2, charp_len); } else e_info_charp2 = NULL; 2278c2336 if (is_schema_query) --- if (1 == is_schema_query) 2366a2425,2430 else if (2 ==
Re: [PATCHES] Better psql tab-completion support for schemas and tables
Greg Sabino Mullane wrote: Full support for all schema and table name combinations when getting a list of attributes. All of the following will now work: diff -c please ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Better psql tab-completion support for schemas and tables
(oops, this time with -c, thanks Alvaro) Full support for all schema and table name combinations when getting a list of attributes. All of the following will now work: select * from information_schema.columns where tab select * from foo where tab select * from user where tab select * from foo where tab select * from Uppercase.lower where tab select * from gtsm.com.foo.Bar where tab select * from GTSM.com.foo where tab Also applies to other places that get lists of columns: insert into, alter table, create index, etc. -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200710211532 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 Index: tab-complete.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v retrieving revision 1.167 diff -c -r1.167 tab-complete.c *** tab-complete.c 14 Sep 2007 04:25:24 - 1.167 --- tab-complete.c 21 Oct 2007 19:30:48 - *** *** 53,58 --- 53,59 #include pqexpbuffer.h #include common.h #include settings.h + #include stringutils.h #ifdef HAVE_RL_FILENAME_COMPLETION_FUNCTION #define filename_completion_function rl_filename_completion_function *** *** 124,133 * Communication variables set by COMPLETE_WITH_FOO macros and then used by * the completion callback functions. Ugly but there is no better way. */ ! static const char *completion_charp; /* to pass a string */ ! static const char *const * completion_charpp; /* to pass a list of strings */ ! static const char *completion_info_charp; /* to pass a second string */ ! static const SchemaQuery *completion_squery; /* to pass a SchemaQuery */ /* A couple of macros to ease typing. You can use these to complete the given string with --- 125,135 * Communication variables set by COMPLETE_WITH_FOO macros and then used by * the completion callback functions. Ugly but there is no better way. */ ! static const char *completion_charp;/* to pass a string */ ! static const char *const * completion_charpp; /* to pass a list of strings */ ! static const char *completion_info_charp; /* to pass a second string */ ! static const char *completion_info_charp2; /* to pass a third string */ ! static const SchemaQuery *completion_squery;/* to pass a SchemaQuery */ /* A couple of macros to ease typing. You can use these to complete the given string with *** *** 145,152 do { completion_charpp = list; matches = completion_matches(text, complete_from_list); } while(0) #define COMPLETE_WITH_CONST(string) \ do { completion_charp = string; matches = completion_matches(text, complete_from_const); } while(0) ! #define COMPLETE_WITH_ATTR(table, addon) \ ! do {completion_charp = Query_for_list_of_attributes addon; completion_info_charp = table; matches = completion_matches(text, complete_from_query); } while(0) /* * Assembly instructions for schema queries --- 147,167 do { completion_charpp = list; matches = completion_matches(text, complete_from_list); } while(0) #define COMPLETE_WITH_CONST(string) \ do { completion_charp = string; matches = completion_matches(text, complete_from_const); } while(0) ! #define COMPLETE_WITH_ATTR(relation, addon) \ ! do {\ ! completion_schema = strtokx(relation, \t\n\r, ., \, 0, false, false, pset.encoding); \ ! strtokx(NULL, \t\n\r, ., \, 0, false, false, pset.encoding); \ ! completion_table = strtokx(NULL, \t\n\r, ., \, 0, false, false, pset.encoding); \ ! if (NULL == completion_table) { \ ! completion_charp = Query_for_list_of_attributes addon; \ ! completion_info_charp = relation;\ ! } \ ! else {\ ! completion_charp = Query_for_list_of_attributes_with_schema addon; \ ! completion_info_charp = completion_table; \ ! completion_info_charp2 = completion_schema; \ ! } \ ! matches = completion_matches(text, complete_from_relation_query); } while(0) /* * Assembly instructions for schema queries *** *** 312,318 * become a SQL literal string). %d will be replaced by the length of the * string (in unescaped form). A second %s, if present, will be replaced * by a suitably-escaped version of the string provided in ! * completion_info_charp. * * Beware that the allowed sequences of %s and %d are determined by * _complete_from_query(). --- 327,333 * become a SQL literal string). %d will be replaced by the length of the * string (in unescaped form). A second %s, if present, will be replaced * by a suitably-escaped version of the string provided in ! * completion_info_charp. A third %s is replaced by completion_info_charp2. * * Beware that the allowed sequences of %s and %d are determined by * _complete_from_query(). *** *** 325,333 AND a.attnum 0 \ AND
[PATCHES] Hash Index Build Patch v2
This revised version of our patch uses the function estimate_rel_size() from plancat.c to estimate the number of tuples in the parent relation. This method is an alternative to scanning the parent relation to estimate the number of tuples, as we did in the first version of the patch. -Tom #include stdio.h #include stdlib.h extern int main(int argc, char **argv) { char *p; long i, tups, seed; if (argc 3) { printf(Too few args. tuples seed\n); return 0; } tups = strtol(argv[1],p,0); seed = strtol(argv[2], p, 0); srand(seed); for (i=0; i tups; i++) { printf(%d\n, rand()); } return 0; } *** ./backend/access/hash/hash.c.orig 2007-09-23 19:01:09.0 -0700 --- ./backend/access/hash/hash.c2007-10-21 12:07:48.455594000 -0700 *** *** 22,33 #include access/hash.h #include catalog/index.h #include commands/vacuum.h /* Working state for hashbuild and its callback */ typedef struct { ! double indtuples; } HashBuildState; static void hashbuildCallback(Relation index, --- 22,36 #include access/hash.h #include catalog/index.h #include commands/vacuum.h + #include optimizer/plancat.h /* Working state for hashbuild and its callback */ typedef struct { ! double indtuples; /* The current number of index tuples */ ! RelationheapRel; /* The index covers this heap relation */ ! HSpool *spool;/* Used to sort the index tuples before insertion into the index */ } HashBuildState; static void hashbuildCallback(Relation index, *** *** 40,46 --- 43,80 /* *hashbuild() -- build a new hash index. + * + * + * The algorithm: + *(1) Initialize the build state + *(2) Retrieve estimate of tuple count from estimate_rel_size(); + *(3) Transform the heap file tuples into index tuples (itups), + *while inserting them into a spool. If the spool overflows + *memory, sort it into runs and spill it to disk + *(4) Finish sorting the spool + *(5) Pre-initialize all the buckets of the final index + *(6) Insert itups from the spool into the index + * + * Sorting the tuples before inserting them into the index is a classical + * bulk-load technique, also used in the BTree code. The sort is done in + * hash value order. + * Pre-allocating the buckets minimizes the number of overflow pages. + * The reason for step (2) is that in order to sort, in step (3), one must + * know the hash value, which depends on the number of buckets, which in turn + * depends on the number of itups = the number of rows in the heap file. + * Steps (3),(4) and (6) parallel similar steps in the BTree code. + * + * Here is an alternative algorithm: + *(1') Same as (1) + *(2') Scan the heap file, counting the number of rows, forming index + * tuples and inserting them into a spool (the spool is not presorted). + *(3') Sort the spool + *(4') same as (5) + *(5') same as (6) + *Although this algorithm would be somewhat faster, we prefer the existing + * algorithm because it reuses existing BTree code. */ + Datum hashbuild(PG_FUNCTION_ARGS) { *** *** 50,55 --- 84,94 IndexBuildResult *result; double reltuples; HashBuildState buildstate; + double tuples; + BlockNumber pages; + HashMetaPagemetap; + Buffer metabuf; + uint32 num_bkt; /* Estimates number of buckets in the final index */ /* * We expect to be called exactly once for any index relation. If that's *** *** 59,81 elog(ERROR, index \%s\ already contains data, RelationGetRelationName(index)); ! /* initialize the hash index metadata page */ ! _hash_metapinit(index); ! ! /* build the index */ buildstate.indtuples = 0; ! /* do the heap scan */ ! reltuples = IndexBuildHeapScan(heap, index, indexInfo, ! hashbuildCallback, (void *) buildstate); - /* -* Return statistics -*/ - result = (IndexBuildResult *) palloc(sizeof(IndexBuildResult)); ! result-heap_tuples = reltuples; ! result-index_tuples = buildstate.indtuples; PG_RETURN_POINTER(result); } --- 98,158 elog(ERROR, index \%s\ already contains data, RelationGetRelationName(index)); ! /* initialize the build state */ buildstate.indtuples = 0; + buildstate.heapRel = heap; + buildstate.spool = h_spoolinit(index); !/* ! * Retrieve an estimate of the number of rows ! * ! */ ! tuples=0; !
[PATCHES] pgstattuple locking fix
Here is a trivial fix of locking issue in pgstattuple(). It was locking buffers around PageGetHeapFreeSpace() in the heap scan loop, but not in the scanning of the tail. I think we need locks in the tail, too. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgstattuple_lock.patch Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] pgstattuple locking fix
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Here is a trivial fix of locking issue in pgstattuple(). Hmm, is this really a bug, and if so how far back does it go? I'm thinking that having a pin on the buffer should be enough to call PageGetHeapFreeSpace. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings