[PATCHES] Thread-safe PREPARE in ecpg
Here is a WIP patch to make prepared statements thread-safe in ecpg. The variable prep_stmts was global but not protected by any locks. I divided it into per-connection field so that we can access prepared statements separately in each thread. I needed to change the following exported functions, but it will introduce an incompatibility issue. It might be ok for CVS HEAD, but I'd like to port the fix to back versions. Do you have any thoughts on how we can accomplish this better? From: - bool ECPGdeallocate(int, int, const char *name); - bool ECPGdeallocate_all(int, int); - char *ECPGprepared_statement(const char *name, int); To: - bool ECPGdeallocate(int, int, const char *connection_name, const char *name); - bool ECPGdeallocate_all(int, int, const char *connection_name); - char *ECPGprepared_statement(const char *connection_name, const char *name, int); (connection_name argument is added.) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ecpg_prepare.patch Description: Binary data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On Mon, 2007-09-24 at 21:26 -0500, Jaime Casanova wrote: On 9/24/07, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2007-09-22 at 23:49 -0500, Jaime Casanova wrote: On 6/19/07, Simon Riggs [EMAIL PROTECTED] wrote: related TODO items: - add a WAIT n clause in same SQL locations as NOWAIT - add a lock_wait_timeout (USERSET), default = 0 (unlimited waiting) to provide better control over lock waits. are these actual TODO items? i can't find them on the TODO list and i don't remember any discussion nor patch about this They are my proposals for TODO items to assist with application development. while i'm not at all comfortable with the idea of a GUC for this, the WAIT clause seems to be useful. just out of curiosity, why the NOWAIT patch wasn't do it that way in first place, i mean like a WAIT clause and when receiving NOWAIT transform it in WAIT 0? maybe dicussion? NOWAIT is used by Oracle. DB2 supports a lock wait timeout. What I didn't know before googling this was that SQLServer uses NOWAIT also. SQLServer also implement WAIT [n seconds] *and* a parameter called lock wait period, which is pretty spooky. Another reason to implement this is to help avoid global deadlocks in distributed transactions (e.g. two phase). SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. there's concensus in adding a WAIT clause? Just do it, but take careful note of any comments against things. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] Full page writes improvement, code update
On Tue, 2007-04-10 at 16:23 +0900, Koichi Suzuki wrote: Here're two patches for 1) lesslog_core.patch, patch for core, to set a mark to the log record to be removed in archiving, 2) lesslog_contrib.patch, patch for contrib/lesslog, pg_compresslog and pg_decompresslog, respectively, as asked. I hope they work. Koichi-san, Earlier, I offered to document the use of pg_compresslog and pg_decompresslog and would like to do that now. My understanding was that we would make these programs available on pgfoundry.org. Unfortunately, I can't find these files there, so perhaps I misunderstood. Do we have later versions of these programs that work with the changes Tom committed on 20 May? Or is the code posted here the latest version? Many thanks, -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Thread-safe PREPARE in ecpg
On Tue, Sep 25, 2007 at 03:22:13PM +0900, ITAGAKI Takahiro wrote: Here is a WIP patch to make prepared statements thread-safe in ecpg. The variable prep_stmts was global but not protected by any locks. I divided it into per-connection field so that we can access prepared statements separately in each thread. Thanks a lot. This is exactly how I was planning to implement it, but I haven't even found the time to start coding yet. :-) Could you please create a small example that we could add to the regression suite? I needed to change the following exported functions, but it will introduce an incompatibility issue. It might be ok for CVS HEAD, but I'd like to port the fix to back versions. Do you have any thoughts on how we can accomplish this better? No idea at the moment, sorry. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] 'Waiting on lock'
Simon Riggs [EMAIL PROTECTED] writes: SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. Why? What does it do that statement_timeout doesn't do better? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On Tue, 2007-09-25 at 09:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. Why? What does it do that statement_timeout doesn't do better? If the execution time is negligible, then setting statement_timeout is the same thing as setting a lock timeout. If execution time is not negligible, then you may want to tell the difference between waiting for completion against waiting forever without doing anything useful at all. It's also easier to set an all encompassing lock timeout at User level than it is to set statement_timeout on individual transactions issued by that user. Plus, if applications are written using these concepts it is easier to port them to PostgreSQL. Not planning to work on this myself, but I think it is a valid TODO. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] 'Waiting on lock'
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2007-09-25 at 09:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. Why? What does it do that statement_timeout doesn't do better? If the execution time is negligible, then setting statement_timeout is the same thing as setting a lock timeout. To make this explicit, I think the typical scenario where it would make a difference is where you're running some large job in a plpgsql function. You might be processing millions of records but want for a single step of that process to not wait for a lock. You still want to process all the records you can though. So for example if you're updating all the user profiles on your system but don't want to block on any user-profiles which are locked by active users -- especially if you use database locks for user-visible operations which users can drag out for long periods of time. (Not saying I agree with that design but there are arguments for it and people do do it) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Optimizer hook
This hook seems very strangely defined to me. Why did you not put the
hook at the point where the current geqo-vs-regular decision is made?
The reason is that I thought about gaining a control over the algorithm
used to solve individual subproblems in make_rel_from_joinlist. If we would
have a couple of algorithms to be tested we can implement a plugin
using join_order_search_hook which will compare the results of
those algorithms. Doing the same at the place where geqo/regular code is
called might make thinks a bit more difficult.
Later on, we could also implement a code trying to run some very fast
algorithm at first (for the master problem and all subproblems) to get
some estimates and decide whether it makes sense to try to find a better
plan in longer time.
Also, optimizer_hook seems nearly content-free as a name for use
in this area; I see no reason why the particular sub-section of the
planner we're discussing here has more title to that name than other
parts. Something like join_order_search_hook might be more
appropriate.
I completely agree and I have renamed the hook.
The new version V3 of the patch is attached.
Thanks for the comments.
Regards
Julius Stroffek
*** ./src/backend/optimizer/path/allpaths.c.orig Sat May 26 20:23:01 2007
--- ./src/backend/optimizer/path/allpaths.c Tue Sep 25 11:37:19 2007
***
*** 37,43
--- 37,45
bool enable_geqo = false; /* just in case GUC doesn't set it */
int geqo_threshold;
+ join_order_search_hook_type join_order_search_hook = NULL;
+
static void set_base_rel_pathlists(PlannerInfo *root);
static void set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
Index rti, RangeTblEntry *rte);
***
*** 52,60
RangeTblEntry *rte);
static void set_values_pathlist(PlannerInfo *root, RelOptInfo *rel,
RangeTblEntry *rte);
- static RelOptInfo *make_rel_from_joinlist(PlannerInfo *root, List *joinlist);
- static RelOptInfo *make_one_rel_by_joins(PlannerInfo *root, int levels_needed,
- List *initial_rels);
static bool subquery_is_pushdown_safe(Query *subquery, Query *topquery,
bool *differentTypes);
static bool recurse_pushdown_safe(Node *setOp, Query *topquery,
--- 54,59
***
*** 87,93
/*
* Generate access paths for the entire join tree.
*/
! rel = make_rel_from_joinlist(root, joinlist);
/*
* The result should join all and only the query's base rels.
--- 86,95
/*
* Generate access paths for the entire join tree.
*/
! if (join_order_search_hook)
! rel = join_order_search_hook(root, joinlist);
! else
! rel = make_rel_from_joinlist(root, joinlist);
/*
* The result should join all and only the query's base rels.
***
*** 612,618
* See comments for deconstruct_jointree() for definition of the joinlist
* data structure.
*/
! static RelOptInfo *
make_rel_from_joinlist(PlannerInfo *root, List *joinlist)
{
int levels_needed;
--- 614,620
* See comments for deconstruct_jointree() for definition of the joinlist
* data structure.
*/
! RelOptInfo *
make_rel_from_joinlist(PlannerInfo *root, List *joinlist)
{
int levels_needed;
***
*** 695,701
* Returns the final level of join relations, i.e., the relation that is
* the result of joining all the original relations together.
*/
! static RelOptInfo *
make_one_rel_by_joins(PlannerInfo *root, int levels_needed, List *initial_rels)
{
List **joinitems;
--- 697,703
* Returns the final level of join relations, i.e., the relation that is
* the result of joining all the original relations together.
*/
! RelOptInfo *
make_one_rel_by_joins(PlannerInfo *root, int levels_needed, List *initial_rels)
{
List **joinitems;
*** ./src/include/optimizer/paths.h.orig Tue May 22 03:40:33 2007
--- ./src/include/optimizer/paths.h Tue Sep 25 11:32:34 2007
***
*** 23,30
extern bool enable_geqo;
extern int geqo_threshold;
! extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
#ifdef OPTIMIZER_DEBUG
extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
#endif
--- 23,46
extern bool enable_geqo;
extern int geqo_threshold;
! /* A macro pointing to the standard joinorder search function. */
! #define standard_join_order_search make_rel_from_joinlist
+ /* Hook for plugins to get control in make_one_rel() */
+ typedef RelOptInfo * (*join_order_search_hook_type) (PlannerInfo * root,
+ List * joinlist);
+ extern PGDLLIMPORT join_order_search_hook_type join_order_search_hook;
+
+ /*
+ * Functions related to searching the space
+ * of all possible join orders.
+ */
+ extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
+ extern RelOptInfo *make_rel_from_joinlist(PlannerInfo *root,
+ List *joinlist);
+ extern RelOptInfo *make_one_rel_by_joins(PlannerInfo *root,
+
Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded
Marshall, Steve wrote: I'm glad to see the patch making its way through the process. I'm also glad you guys do comprehensive testing before accepting it, since we are only able to test in a more limited range of environments. We have applied the patch to our 8.2.4 installations and are running it in a high transaction rate system (processing lots and lots of continually changing weather data). Let me know if there is any information we could provide that would be of help in making the back-patching decision. I have re-enabled tcl builds(for -HEAD) on lionfish (mipsel) and quagga (arm) a few days ago so we should get a bit of additional coverage from boxes that definitly had problems with the tcl-threading behaviour. Stefan ---(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] Optimizer hook
Julius Stroffek [EMAIL PROTECTED] writes: This hook seems very strangely defined to me. Why did you not put the hook at the point where the current geqo-vs-regular decision is made? The reason is that I thought about gaining a control over the algorithm used to solve individual subproblems in make_rel_from_joinlist. That would be an entirely different problem, I think. If you want to replace the *entire* planner, there's already the planner_hook to do that. If you're just looking to change the join order search method, the place where GEQO hooks in is the most convenient place for that. It's not clear to me what would be usefully accomplished by putting the hook somewhere else; you can't just willy-nilly replace the code for single-relation path selection, at least not without a whole lot of changes elsewhere in the planner infrastructure. My thought was that the reason for this hook to exist was simply to provide a convenient way to replace only the join search order algorithm. I'm willing to entertain the thought of other hooks in other places for other specific purposes, but where you want to put it seems not well-matched to any problem that would be likely to be solved without replacing all of the planner. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Optimizer hook
Well, It seems that I probably do not understand something quite well or
my explanation
is not clear. There is some example code of my idea in the attachment.
There is a function jos_search which has nearly the same code as
make_rel_from_joinlist.
The example tries geqo first and then the regular algorithm. At the
first sight it seems that
it is possible to do the same with the hook placed to the current
decision point. However,
the execution flow is slightly different.
1.) example in dummy.c
---
It first uses geqo only to find the cheapest path also for all the recursive
calls in jos_search. Afterwards, the same is executed using the regular
algorithm
also for all the recursive calls in jos_search. The whole result path
for the query
will be produced only by either geqo or regular algorithm.
2.) placing the hook to the current decision point and trying both
geqoregular
at that place.
---
Parts of the result path might be found by geqo and parts of it by
regular algorithm.
The problem here is that regular algorithm will find the best plan every
time it finishes. However, the above is supposed to be used for the
algorithms
that none of them is supposed to find the best solution.
That would be an entirely different problem, I think. If you want to
replace the *entire* planner, there's already the planner_hook to do that.
Replacing the whole planner would need a much more code to be reused without
any change than in this case.
you can't just willy-nilly replace the code for
single-relation path selection, at least not without a whole lot of
changes elsewhere in the planner infrastructure.
Would the code in dummy.c work in a way that I expect and explained above?
If there is no way of how to make the code work then it makes no sense
to put the hook to the place I am proposing. It works for me, but I have
not tested that very well yet. If I would swap calls to geqo
and make_one_rel_by_joins it will not work. Therefore there might be
an issue I do not know about yet.
My thought was that the reason for this hook to exist was simply to
provide a convenient way to replace only the join search order
algorithm.
Yes, thats true. I do not have a plan to do something more for now.
Thank you
Regards
Julius Stroffek
#include postgres.h
#include fmgr.h
#include optimizer/geqo.h
#include optimizer/paths.h
#include optimizer/pathnode.h
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
typedef RelOptInfo * (*jos_function_type) (PlannerInfo *root, int levels_needed, List* initial_rels);
static
RelOptInfo *
jos_search(PlannerInfo *root, List *joinlist, jos_function_type jos_function)
{
int levels_needed;
List *initial_rels;
ListCell *jl;
/*
* Count the number of child joinlist nodes. This is the depth of the
* dynamic-programming algorithm we must employ to consider all ways of
* joining the child nodes.
*/
levels_needed = list_length(joinlist);
if (levels_needed = 0)
return NULL;/* nothing to do? */
/*
* Construct a list of rels corresponding to the child joinlist nodes.
* This may contain both base rels and rels constructed according to
* sub-joinlists.
*/
initial_rels = NIL;
foreach(jl, joinlist)
{
Node *jlnode = (Node *) lfirst(jl);
RelOptInfo *thisrel;
if (IsA(jlnode, RangeTblRef))
{
int varno = ((RangeTblRef *) jlnode)-rtindex;
thisrel = find_base_rel(root, varno);
}
else if (IsA(jlnode, List))
{
/* Recurse to handle subproblem */
thisrel = jos_search(root, (List *) jlnode, jos_function);
}
else
{
elog(ERROR, unrecognized joinlist node type: %d,
(int) nodeTag(jlnode));
thisrel = NULL; /* keep compiler quiet */
}
initial_rels = lappend(initial_rels, thisrel);
}
if (levels_needed == 1)
{
/*
* Single joinlist node, so we're done.
*/
return (RelOptInfo *) linitial(initial_rels);
}
else
{
jos_function(root, levels_needed, initial_rels);
}
}
static
RelOptInfo *
jos_dummy(PlannerInfo *root, List *joinlist)
{
RelOptInfo *dynamic_result, *geqo_result;
List *copy;
Cost dynamic_cost, geqo_cost;
copy = list_copy(joinlist);
elog(LOG, Starting a join order search \geqo\...);
geqo_result = jos_search(root, copy, geqo);
// geqo_result = jos_search(root, copy, make_one_rel_by_joins);
geqo_cost = geqo_result-cheapest_total_path-total_cost;
copy = list_copy(joinlist);
elog(LOG, Starting a join order search \dynamic\...);
dynamic_result = jos_search(root, copy, make_one_rel_by_joins);
// dynamic_result = jos_search(root, copy, geqo);
dynamic_cost = dynamic_result-cheapest_total_path-total_cost;
printf(GEQO cost: %f\n,
Re: [PATCHES] Optimizer hook
Julius Stroffek [EMAIL PROTECTED] writes: Parts of the result path might be found by geqo and parts of it by regular algorithm. Why would you care? Seems like forcing that to not happen is actively making it stupider. If there is no way of how to make the code work then it makes no sense to put the hook to the place I am proposing. It works for me, but I have not tested that very well yet. If I would swap calls to geqo and make_one_rel_by_joins it will not work. Therefore there might be an issue I do not know about yet. Well, I can see one likely problem: list_copy is a shallow copy and thus doesn't ensure that the second set of functions sees the same input data structures as the first. I know that geqo has to go through some special pushups to perform multiple invocations of the base planner, and I suspect you need that here too. Look at geqo_eval(). regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] 'Waiting on lock'
On 9/25/07, Simon Riggs [EMAIL PROTECTED] wrote: On Tue, 2007-09-25 at 09:16 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQLServer and DB2 have more need of this than PostgreSQL, but we do still need it. Why? What does it do that statement_timeout doesn't do better? If the execution time is negligible, then setting statement_timeout is the same thing as setting a lock timeout. If execution time is not negligible, then you may want to tell the difference between waiting for completion against waiting forever without doing anything useful at all. [...thinking on this a bit...] mmm... i think we can emulate WAIT number_of_seconds using the NOWAIT and a bit of logic... point for tom Plus, if applications are written using these concepts it is easier to port them to PostgreSQL. no words... point for simon... Not planning to work on this myself, but I think it is a valid TODO. i will make a try for 8.4 -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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 get through to the mailing list cleanly
Re: [PATCHES] Optimizer hook
Why would you care? Seems like forcing that to not happen is actively making it stupider. To better compare the algorithms and possibly not for final solution at the beginning. If we would implement 10 algorithms and want to pickup just 3 best ones to be used and throw 7 away. Later on, we can try to run just the one very fast algorithm and depending on the cost decide whether we would run remaining 9 or less or even none. Yes, the example in dummy.c is really stupider, but it could be done in more clever way. Well, I can see one likely problem: list_copy is a shallow copy and thus doesn't ensure that the second set of functions sees the same input data structures as the first. I know that geqo has to go through some special pushups to perform multiple invocations of the base planner, and I suspect you need that here too. Look at geqo_eval(). I'll explore that. Thanks Regards Julius Stroffek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Configure template change to use SysV Semaphors on darwin
I wrote: Awhile back, Chris Marcellino [EMAIL PROTECTED] wrote: If this is in fact the case, I have a trivial patch to conditionally enable SysV semaphores based on the OS release: I've tried this patch on my Mac laptop, and while it seems to work as advertised in terms of not eating a boatload of file descriptors, I was disturbed to find that it seems consistently a couple percent slower than the POSIX-sema code according to pgbench. I rechecked this using PG CVS HEAD and can no longer reproduce a slowdown --- in fact, the SYSV-sema code is marginally faster. It's well within the noise level of pgbench, but anyway this seems enough to allay my fears of a performance drop: posix: g42:~ tgl$ pgbench -c 10 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1000 number of transactions actually processed: 1/1 tps = 107.051937 (including connections establishing) tps = 107.207220 (excluding connections establishing) sysv: g42:~ tgl$ pgbench -c 10 -t 1000 bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 10 number of transactions per client: 1000 number of transactions actually processed: 1/1 tps = 107.565526 (including connections establishing) tps = 107.730181 (excluding connections establishing) I am not sure what changed since May, but one possibility is that I just recently boosted the sysv shmem limit on my laptop. I think the previous runs were probably taken with max_connections of 30 or so, whereas these numbers have max_connections = 100. That's relevant since there are 3x more semaphores needed. Anyway, getting rid of all the file descriptors for Posix semas seems Clearly A Good Idea, so I'll go ahead and apply this patch. Thanks for sending it in. regards, tom lane ---(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 get through to the mailing list cleanly
Re: [PATCHES] Optimizer hook
Julius Stroffek [EMAIL PROTECTED] writes: Why would you care? Seems like forcing that to not happen is actively making it stupider. To better compare the algorithms and possibly not for final solution at the beginning. If we would implement 10 algorithms and want to pickup just 3 best ones to be used and throw 7 away. Well, you could in any case force the same decision to be made in every invocation, for example by driving it off a GUC variable. The idea you have here seems to be it'll be the same choice in every sub-problem, only we won't know which one afterwards, which doesn't seem at all helpful for planner testing purposes. Yes, the example in dummy.c is really stupider, but it could be done in more clever way. I think dummy.c kinda proves my point: more than half the code is accomplishing nothing except to duplicate the behavior of make_rel_from_joinlist. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Warning is adjusted of pgbench.
Hiroshi Saito [EMAIL PROTECTED] writes: Why do you need to #undef EXEC_BACKEND, and is there a specific reason for removing the include of win32.h? I put in in order to avoid -D of the Makefile. If that matters, the problem is that somebody put the wrong stuff in the wrong include file. Backend-only things ought to go in postgres.h not c.h. In particular this is wrongly placed: /* EXEC_BACKEND defines */ #ifdef EXEC_BACKEND #define NON_EXEC_STATIC #else #define NON_EXEC_STATIC static #endif but AFAICS it doesn't affect anything that pgbench would care about. So I'm wondering *exactly* what goes wrong if you don't #undef EXEC_BACKEND in pgbench. As for the FRONTEND #define, that seems essential on Windows (and on no other platform) because port/win32.h uses it. But putting the #define into pgbench.c (and by implication into anything else we build on Windows) sure seems like a broken approach. Where else could we put it? It looks like right now that's left to the build system, which might or might not be a good idea, but if it is a good idea then pgbench must be getting missed. Perhaps instead postgres_fe.h should #define FRONTEND? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Warning is adjusted of pgbench.
Hi. From: Tom Lane [EMAIL PROTECTED] Hiroshi Saito [EMAIL PROTECTED] writes: Why do you need to #undef EXEC_BACKEND, and is there a specific reason for removing the include of win32.h? I put in in order to avoid -D of the Makefile. If that matters, the problem is that somebody put the wrong stuff in the wrong include file. Backend-only things ought to go in postgres.h not c.h. In particular this is wrongly placed: /* EXEC_BACKEND defines */ #ifdef EXEC_BACKEND #define NON_EXEC_STATIC #else #define NON_EXEC_STATIC static #endif but AFAICS it doesn't affect anything that pgbench would care about. So I'm wondering *exactly* what goes wrong if you don't #undef EXEC_BACKEND in pgbench. As for the FRONTEND #define, that seems essential on Windows (and on no other platform) because port/win32.h uses it. But putting the #define into pgbench.c (and by implication into anything else we build on Windows) sure seems like a broken approach. Where else could we put it? It looks like right now that's left to the build system, which might or might not be a good idea, but if it is a good idea then pgbench must be getting missed. Perhaps instead postgres_fe.h should #define FRONTEND? Yes, I feared that the physique of a main part broke. Then, Magnus said the same suggestion as you. It seems that it needs to be brushup. I am going to improve by the reason referred to as that FRONTEND influences nmake (libpq) again. Probably, Mugnus is operating main part.? Thanks. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Warning is adjusted of pgbench.
Hiroshi Saito [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] Perhaps instead postgres_fe.h should #define FRONTEND? Yes, I feared that the physique of a main part broke. Then, Magnus said the same suggestion as you. It seems that it needs to be brushup. I am going to improve by the reason referred to as that FRONTEND influences nmake (libpq) again. Probably, Mugnus is operating main part.? To be clear: my thought is to put #define FRONTEND 1 into postgres_fe.h and then eliminate ad-hoc definitions of it from a boatload of Makefiles. A quick grep suggests that the only place this wouldn't work too well is src/port/, but that could keep on doing what it's doing. I'm not in a good position to test this, because it will mainly matter on Windows which I don't do. Anyone else have a chance to try it? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Warning is adjusted of pgbench.
Hi. From: Tom Lane [EMAIL PROTECTED] Hiroshi Saito [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] Perhaps instead postgres_fe.h should #define FRONTEND? Yes, I feared that the physique of a main part broke. Then, Magnus said the same suggestion as you. It seems that it needs to be brushup. I am going to improve by the reason referred to as that FRONTEND influences nmake (libpq) again. Probably, Mugnus is operating main part.? To be clear: my thought is to put #define FRONTEND 1 into postgres_fe.h and then eliminate ad-hoc definitions of it from a boatload of Makefiles. A quick grep suggests that the only place this wouldn't work too well is src/port/, but that could keep on doing what it's doing. I think sufficient suggestion. I'm not in a good position to test this, because it will mainly matter on Windows which I don't do. Anyone else have a chance to try it? I want, will try it. of course, work of Magnus is not barred. Thanks! Regards, Hiroshi Saito ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Thread-safe PREPARE in ecpg
Here is a revised patch against CVS HEAD. I fixed a bug in ECPGdeallocate_all(). Michael Meskes [EMAIL PROTECTED] wrote: Could you please create a small example that we could add to the regression suite? The attached prep.pgc is an example for this fix, that repeats EXEC SQL PREPARE and EXECUTE in loops. It works with pthread and Win32 thread. I needed to change the following exported functions, but it will introduce an incompatibility issue. It might be ok for CVS HEAD, but I'd like to port the fix to back versions. Do you have any thoughts on how we can accomplish this better? No idea at the moment, sorry. For porting back to 8.2, I'm planning to keep ECPGdeallocate, ECPGdeallocate_all and ECPGprepared_statement as their original names, and add new versions with different names. If users use only thread default connection, the simplified version would work enough. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ecpg_prepare-8.3.patch Description: Binary data prep.pgc Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Hash Index Build Patch
Hello All,
We have prepared a patch (against CVS HEAD)for the TODO item:
* Hash
-During index creation, pre-sort the tuples to improve build speed
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php
Details of this patch's performance improvements can be found at
http://web.cecs.pdx.edu/~raneyt/pg/.
For example, in one 12 million tuple example, the 8.2.4 hash index
build required over 2.8 hours while this patch's build required 80
seconds. Hash build times are now comparable to BTree build times,
for example, for a 60 million tuple example the patched hash index
build time is 8.1 minutes and the BTree build time is 4.6 minutes.
We used spool functions from the BTree code to sort the index
tuples. Sorting is done on the hash value of the tuples. The hash
value depends on the number of primary bucket pages (henceforth
just bucket pages) that will be required to fit all the index
tuples. So, before sorting, the base relation is scanned to get
the total number of tuples. Then, the fill factor (either default
or user defined, as applicable) is used to get the estimate of the
number of bucket pages.
The 8.2.4 code builds the index by inserting one tuple at a time
and splitting buckets as needed. We pre-allocate the estimated
number of bucket pages all at once. This avoids bucket page splits
and thus redistribution of index tuples between the bucket page
that caused the split and the newly created bucket page.
We changed the values of low_mask, high_mask and max_bucket, used
by hashkey2bucket () while inserting index tuples to bucket pages.
They are set as follows:
Low_mask = (power of 2 value-1) less than the estimate.
High_mask = (power of 2 value-1) greater than the estimate.
Max_bucket = estimated number of bucket -1 (because bucket numbers
start from 0).
(Please note: hashsort.c is a new file and resides in
src/backend/access/hash/)
We have also attached the simple data generator we used in the
tests. Our SQL statements were as follows:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
value INTEGER
);
COPY test FROM 'data';
\timing
CREATE INDEX hash ON test USING HASH(value);
Regards,
Shreya Bhargava [EMAIL PROTECTED]
Tom Raney [EMAIL PROTECTED]
/*---
* hashsort.c
*-*/
/*
* Functions needed to support initializing and sorting tuples in the spool
* in hash.c
*/
#include postgres.h
#include access/hash.h
#include miscadmin.h
#include storage/smgr.h
#include utils/tuplesort.h
struct HSpool
{
/* State data for tuplesort.c */
Tuplesortstate *sortstate;
Relation index;
};
/* create and initialize the spool structure */
HSpool *h_spoolinit(Relation index)
{
HSpool *hspool;
int hKbytes;
hspool = (HSpool *) palloc0(sizeof(HSpool));
hspool-index = index;
/* hKbytes is the amount of memory we are going to use
* to sort the spool.
*/
hKbytes = maintenance_work_mem;
hspool-sortstate = tuplesort_begin_index(index,false,hKbytes,false);
/* Substitute the default compare call-back function
* for a specific hash index build compare function.
*/
tuplesort_set_hashindex(hspool-sortstate);
return hspool;
}
void h_spool(IndexTuple itup, HSpool *hspool)
{
tuplesort_putindextuple(hspool-sortstate, itup);
}
/*
* h_bkt_num() estimates the number of buckets
* in the final hash table.
*
*/
uint32 h_bkt_num(uint32 tuples, Relation rel)
{
int32 ffactor;
int32 data_width;
int32 item_width;
uint32 bkt_num;
/*
* Calculate the fill factor. We could get this from the meta page
* but at the point this method is called, the meta page has not been
* created.
*
*/
data_width = get_typavgwidth(RelationGetDescr(rel)-attrs[0]-atttypid,
RelationGetDescr(rel)-attrs[0]-atttypmod);
item_width = MAXALIGN(sizeof(IndexTupleData)) + MAXALIGN(data_width) +
sizeof(ItemIdData);
ffactor = RelationGetTargetPageUsage(rel, HASH_DEFAULT_FILLFACTOR) /
item_width;
if (ffactor 10)
ffactor = 10;
bkt_num = tuples / ffactor;
bkt_num = bkt_num +1;
return bkt_num;
}
/* In order to define an order for the index tuples, there must be a mask
* applied to the 32 bit hash value of the index key during the sort
* process.
* For example, if there are 4,555 buckets approximated, the mask (or
modulo)
* would be 8,191 (hex value 1FFF).
*
*/
void h_set_bkt_mask(HSpool *spool, uint32 bkts) {
uint32 bkt_pwr2, bkt_mask;
bkt_pwr2 = _hash_log2(bkts);
bkt_mask = (1(bkt_pwr2))-1;
