Re: [HACKERS] refactoring fork() and EXEC_BACKEND
Magnus Hagander wrote: This is a lot like what I was planning to work towards with the refactoring of the forkexec code I promised to do for 8.1. Cool. BTW, have we accepted that EXEC_BACKEND is the way we're going to workaround the lack of fork() on Win32 for the foreseeable future? I mean, it _works_, but it's slow, ugly, and complicates the code. If it's the only workable option for Win32 support, then fair enough -- I just don't know enough of the Win32 API to know if there's a better alternative out there (short of using threads, which is of course not really plausible). I was actually thinking of not passing these on the commandline at all, in order to avoid possible quoting issues etc (recall all the problems with the stupid commandline processing on win32). Instead moving it into a struct that is appended to the end of the backend variable file/shared memory. Sounds good to me. Finding a cleaner way to pass data to the child process than writing it out to a file would also be nice, if possible. Again, I'm not sure what options there are on Win32... That was also what I was thinking. Let me know if you want to split the load somewhere :-) Given that you're planning to work on this, I've scaled back my ambitions. I'll send a patch to -patches that just cleans up fork() and doesn't change the EXEC_BACKEND case. So fork_process() will: - flush stderr/stdout - save and restore the profiling timer if LINUX_PROFILE is defined - handle BeOS which means it should not be very invasive. Of course, there is plenty of room for improvement -- if you're interested in taking a look, please do... -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bitmap AM design
Pailloncy Jean-Gerard wrote: You should have a look to this thread http://archives.postgresql.org/pgsql-hackers/2005-02/msg00263.php Take a look at this paper about lock-free parallel hash table http://www.cs.rug.nl/~wim/mechver/hashtable/ Is this relevant? Hash indexes are on-disk data structures, so ISTM lock-free algorithms aren't really applicable. No. Sorry for the misunderstanding. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Exception ERROR Code
Hi , I am looking for the way to get the error code corresponding to the exception in plpgsql. Can any body guide me. Thanks, Asif Ali __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Exception ERROR Code
On Sat, Mar 05, 2005 at 06:03:20AM -0800, Ali Baba wrote: I am looking for the way to get the error code corresponding to the exception in plpgsql. What exception? Can you describe what you're trying to do? Are you using the EXCEPTION clause that's available in the latest release, or are you hacking PL/pgSQL itself? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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
[HACKERS] Manual vs automatic functionality
Tom recently said, when talking about allowing the user (in this case me) from passing a hash table size to create index: but that doesn't mean I want to make the user deal with it. I started thinking about this and, maybe I'm old fashioned, but I would like the ability to deal with it. So much software these days does things in an automatic fashion, and too often you are left saying stop, no do it that way, damn! tossing hands up in frustration. Come on, be honest, we all fight this. I think the holy grail of completly automatic tuning/functionality is a lofty goal and a good one to seek, but the harsh reality is that there are many times when the statistics aren't sufficient and the very broad audience to which PostgreSQL is targeted clouds various specific use cases. I have been on the end of these problems numerous times in the almost 10 years of using PostgreSQL. While I still believe that PostgreSQL is, by far, one of the best and most usable databases out there, there are times I just get frustrated. Being able to assign hints to queries may be able to allow DBAs to tune queries in tables who's characteristics are misrepresented by the statistics in ANALYZE. Being able to pass a hash table size to a hash CREATE INDEX statement, may make hash table faster. Whould a hinting syntax help this out? I don't know, but I do know that just about every non-trivial project for which I have used PostgreSQL, I have run into issues where I've had to manually alter statistics source code, or enable/disable scan types to work around situations where PG just didn't understand the nature of the problem. Also, isn't SET enable_seqscan=FALSE just another more clumsy way of issuing a hint to the planner? CREATE INDEX mytablehash ON mytable USING hash /* +HASH_SIZE(1000) */ SELECT * from table1, table2 where table1.realm = table2.realm and table1.name = 'foo' /* +USE_INDEX(tabel1.realm) +USE_HASH(table1.realm, table2.realm) */ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Manual vs automatic functionality
On Sat, Mar 05, 2005 at 11:05:32AM -0500, [EMAIL PROTECTED] wrote: Being able to assign hints to queries may be able to allow DBAs to tune queries in tables who's characteristics are misrepresented by the statistics in ANALYZE. I wouldn't mind being able to provide hints to the planner. For example, I have some set-returning functions that typically return 10-100 rows and I usually have a good idea of how many rows a particular set of inputs will generate, and sometimes I know other characteristics about those rows as well. But the planner, not knowing any better, always assumes the functions will return 1000 rows, which presumably affects the plan, sometimes for the worse. Here's an example of a problem I've had: http://archives.postgresql.org/pgsql-performance/2004-08/msg00236.php I've wondered if the ability to provide hints about the expected output from set-returning functions would be useful. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Explicit Transaction Priority
Hello.. I was wondering if it's possible to make postgresql backend to adjust his priority based by an parameter to begin.. i.e: For linux , a "BEGIN -20" to renice backend priority to -20.. This should be easy (i hope) to implement using setpriority(..) , but there are a few questions. 1) How portable is it ? 2) What implications does it have on the postgres backend ? This could be useful in some situations (like logtables , where I don't need instant inserts or updates..) Thanks Alex
Re: [HACKERS] 8.0.X and the ARC patent
On Fri, 2005-03-04 at 20:10 -0500, Greg Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: Amdahl's Law tells me that looking at the checkpoints is the next best action for tuning, since they add considerably to the average response time. Looking at the oprofile for the run as a whole is missing out the delayed transaction behaviour that occurs during checkpoints. Even aside from the effect it has on average response time. I would point out that many applications are governed by the worst case more than the average throughput. For a web server, for example (or any OLTP application in general), it doesn't matter if the database can handle x transactions/s on average. What matters is that 100% of the time the latency is below the actual rate of requests. If every 30m latency suddenly spikes up beyond that, even for only a minute, then it will fall behind in the requests. The user will effectively see a completely unresponsive web server. So I would really urge you to focus your attention on the maximum latency figure. It's at least if not *more* important than the average throughput number. Sorry Greg, clearly my English was poor. The checkpoints are the source of the peak latency on transactions, so we are in complete agreement. PS That's why I was pushing before for the idea that the server should try to spread the I/O from one checkpoint out over more or less the time interval between checkpoints. If it's been 30m since the last checkpoint then you have about 30m to do the I/O for this checkpoint. (Though I would suggest a safety factor of aiming to be finished within 50% of the time.) I don't want to fix it before I know what the issue is. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Exception ERROR Code
Hello, It's no possible now. But I prepared small patch which implemented variables sqlcode and sqlerrm for plpgsql. I can send it tomorrow. regards Pavel Stehule On Sat, 5 Mar 2005, Ali Baba wrote: Hi , I am looking for the way to get the error code corresponding to the exception in plpgsql. Can any body guide me. Thanks, Asif Ali __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0.X and the ARC patent
Simon Riggs [EMAIL PROTECTED] writes: The checkpoints are the source of the peak latency on transactions, so we are in complete agreement. I realize that. I was just supporting your conclusion but saying that latency is important in its own right, not just as a means to achieving throughput. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Explicit Transaction Priority
On Sat, Mar 05, 2005 at 07:56:16PM +0200, Alex wrote: I was wondering if it's possible to make postgresql backend to adjust his priority based by an parameter to begin.. This has come up before -- search the list archives to see past discussion. Here's a message that sums up the arguments against: http://archives.postgresql.org/pgsql-general/2004-10/msg00400.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] postgreSQL-8.0.1 compilation with icc-8.1 on Itanium-2 gives error: asm statements not supported
Just an update, the __INTEL_COMPILER is true on Itanium if icc is
being used. So, the following worked for me-
---BEGIN OLD
s_lock.h-
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __ia64__ || __ia64 */
-END OLD
s_lock.h-
---BEGIN NEW
s_lock.h-
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
/* Intel Itanium */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
#if defined(__INTEL_COMPILER)
static __inline__ int
tas(volatile slock_t *lock)
{
int ret;
ret = _InterlockedExchange(lock,1);
return ret;
}
#else/* __INTEL_COMPILER */
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __INTEL_COMPILER */
#endif /* __ia64__ || __ia64 */
-END NEW
s_lock.h-
Thanks and Regards,
On Fri, 4 Mar 2005 00:57:15 -0500, Vikram Kalsi [EMAIL PROTECTED] wrote:
Tom, Peter,
I have been able to compile and sucessfully run pgSQL after replacing
the asm statement in postgresql-8.0.1/src/include/storage/s_lock.h
with an equivalent intrinsic for the Itanium platform-
-BEGIN OLD
s_lock.h
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __ia64__ || __ia64 */
---END OLD
s_lock.h
-BEGIN NEW
s_lock.h--
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
int ret;
ret = _InterlockedExchange(lock,1);
return ret;
}
#endif /* __ia64__ || __ia64 */
---END NEW
s_lock.h--
The binary appears to be stable and the tpc-H benchmark executed
successfully against it as well. I also ran the regression test but
the following tests failed, the reasons for which I haven't
investigated yet
(http://www.cse.psu.edu/~kalsi/files/regression.diffs)-
test create_function_1... FAILED
test create_type ... FAILED
test create_table ... FAILED
test create_function_2... FAILED
test triggers ... FAILED
test create_operator ... FAILED
test create_view ... FAILED
test transactions ... FAILED
test misc ... FAILED
test select_views ... FAILED
test rules... FAILED
test plpgsql ... failed (ignored)
test copy2... FAILED
test rangefuncs ... FAILED
test conversion ... FAILED
test stats... FAILED
The _InterlockedExchange() function is defined in ia64intrin.h header file
int _InterlockedExchange(volatile int *Target, long value)
Do an exchange operation atomically. Maps to the xchg4 instruction.
More information is available at
http://www.intel.com/software/products/compilers/clin/docs/ug_cpp/lin1072.htm
Also, some other points to note, _ICC wasn't defined on my
installation when I was using icc by setting env var CC=icc. So, when
I tried to put a #if defined for using asm() for gcc and
_InterlockedExchange(), it didn't work. So, after this change gcc
compilation fails.
As of now, I am trying to test the binary further to see if it is
stable. Would you be knowing some good way to test this change?
I am not aware of the procedure of building patches but if this
resolves this issue and you would like me to make some sort of a
patch, then
Re: [HACKERS] Manual vs automatic functionality
I wouldn't mind being able to provide hints to the planner. For example, I have some set-returning functions that typically return 10-100 rows and I usually have a good idea of how many rows a particular set of inputs will generate, and sometimes I know other characteristics about those rows as well. But the planner, not knowing any better, always assumes the functions will return 1000 rows, which presumably affects the plan, sometimes for the worse. Here's an example of a problem I've had: http://archives.postgresql.org/pgsql-performance/2004-08/msg00236.php I've wondered if the ability to provide hints about the expected output from set-returning functions would be useful. Hmmm, you could jank that by making a new GUC: SET expected_srf_rows TO 152; SELECT * FROM myfunction(); Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Cost of XLogInsert CRC calculations
I was profiling a case involving UPDATEs into a table with too many indexes (brought to mind by mysql's sql-bench, about which more later) and got this rather surprising result for routines costing more than 1% of the total runtime: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls s/call s/call name 64.03 86.2086.20 133608 0.00 0.00 XLogInsert 3.50 90.91 4.71 2484787 0.00 0.00 _bt_compare 2.92 94.84 3.93 839893 0.00 0.00 hash_search 2.77 98.57 3.73 1875815 0.00 0.00 LWLockAcquire 1.89101.12 2.55 1887972 0.00 0.00 LWLockRelease 1.27102.83 1.71 125234 0.00 0.00 _bt_getroot 1.01104.19 1.36 403342 0.00 0.00 PinBuffer 1.00105.54 1.35 840002 0.00 0.00 hash_any I suppose that the bulk of the CPU cycles being attributed to XLogInsert are going into the inlined CRC calculations. Maybe we need to think twice about the cost/benefit ratio of using 64-bit CRCs to protect xlog records that are often only a few dozen bytes. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Missing coalesce
ERROR: function coalence(interval, interval) does not exist I'm guessing this is an oversight, right? Where would I go about fixing it? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing coalesce
Clearly I need to learn how to spell 'coalesce'. Nevermind. On Sun, Mar 06, 2005 at 01:09:12AM -0600, Jim C. Nasby wrote: ERROR: function coalence(interval, interval) does not exist I'm guessing this is an oversight, right? Where would I go about fixing it? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
