Re: [HACKERS] slow SP with temporary tables, PLPGSQL problems
Create the temp table only once per connection (you can use ON COMMIT DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't need to use EXECUTE. I am sorry, first time I didn't understand. Now I did some test and its good adivice. Sometimes I have problem understand so I can use temp. tables on an level of connection and its not neccesery push temp tables on SP level. If I don't change structure of temp tables (and its unpropably on produstion database) I haven't problems. I have still one question. What is possible so I can CREATE OR REPLACE FONCTION foo() .. BEGIN CREATE TEMP TABLE xxx(... INSERT INTO xxx VALUES (... It's works, but in time of compilation SP temp table xxx doesn't exists. It's mean so Id in cmd INSERT is little bit dynamic? I rewrite my SP and I have one notice: In documentation is note, so TRUNCATE TABLE is faster then DELETE FROM. It's not true for small temp tables. On my computer TRUNCATE needs 100ms and DELETE 8ms. It's general or any exception? Thank You Pavel Stehule ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] creating the same table in 2 different sessions
Hi, Maybe I found the following bug or 'not ideal behaviour' of postgres(version 7.4.7 and 8.0 /linux): first start asession 1 begin; create table a0(a bigint); than login for a second session begin create table a0(a bigint) postgres block nows in session 2 when session 1 is commited the following error appears in session 2 duplicate key violates unique constraint pg_class_relname_nsp_index So i think postgres first inserts into pg_class, but blocks in session 2 because of the unique the unique index on (relname,relnamespace). I just wonder if it's safer to check if the table is being created in an other session, 'block' until the session is commited block before starting any insert or other action? Or when the error 'duplicate key violates unique constraint pg_class_relname_nsp_index' hapens during creating of a table ,raise the error 'duplicate key violates unique constraint pg_class_relname_nsp_index, maybe table allready exists' ? Regards, Jeroen ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] creating the same table in 2 different sessions
Jeroen van Iddekinge [EMAIL PROTECTED] writes: begin; create table a0(a bigint); than login for a second session begin create table a0(a bigint) postgres block nows in session 2 when session 1 is commited the following error appears in session 2 duplicate key violates unique constraint pg_class_relname_nsp_index It's always worked like that; certainly as far back as 7.0, which is the oldest version I have alive to test. The friendly a0 already exists test falls through because a0 doesn't exist (at least it's not committed at the time). The unique index mechanism is the last-ditch fallback that prevents the race condition from actually creating a problem. So: no bug, it's operating as designed. I agree that the error message isn't as pretty as one might wish, but I don't think it's worth the effort it would take to produce something else. (The solution you suggest doesn't fix it, it only makes the window narrower.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] strerror_r int and char* return type mixup on FC2
Hi, I noticed that there where strange symbols in the error message when I can't connect to a database. This happends in PHP and pgsql and in the 7.4.x and 8.0 version of postgesql in pqStrError there is a 'if def' for 'char*' and 'int' return type strerror_r. I'm have FC2 linux. This version has a 'int' return type strerror_r and not char* I think the build system doesn't detect the return type of strerror_r correctly and is not using the STRERROR_R_INT but the 'char*' part. This happends in 7.4.x(wich is the offical FC2 release) and mine own build 8.0. The libc version is the default 2.3.3 Is this problem known? According config.log i have build 8.0 with the following swiches: ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-thre ads=posix --disable-checking --disable-libunwind-exceptions --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Regards Jeroen ---(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
Re: [HACKERS] Query optimizer 8.0.1 (and 8.0)
Probably off-topic, but I think it's worth to see what astronomers are doing with their very big spatial databases. For example, we are working with more than 500,000,000 rows catalog and we use some special transformation of coordinates to integer numbers with preserving objects closeness. I hope we could show postgresql is good enough to be used in astronomy for very big catalogs. Currently, MS SQL is in use. See http://www.sdss.jhu.edu/htm/ for details. We use another technique. Oleg On Wed, 9 Feb 2005 [EMAIL PROTECTED] wrote: I wrote a message caled One Big trend vs multiple smaller trends in table statistics that, I think, explains what we've been seeing. [EMAIL PROTECTED] wrote: In this case, the behavior observed could be changed by altering the sample size for a table. I submit that an arbitrary fixed sample size is not a good base for the analyzer, but that the sample size should be based on the size of the table or some calculation of its deviation. Mark, Do you have any evidence that the Sample Size had anything to do with the performance problem you're seeing? Sample size is only a bandaid for the issue, however, more samples always provide more information. I also do a lot with the complete Census/TIGER database. Every problem I have with the optimizer comes down to the fact that the data is loaded (and ordered on disk) by State/County FIPS codes, and then queried by zip-code or by city name. Like this: Alabama36101 [hundreds of pages with zip's in 36***] Alaska 99686 [hundreds of pages with zip's in 9] Arizona85701 [hundreds of pages with zip's in 855**] Note that the zip codes are *NOT* sequential. Again, read One Big Trend... and let me know what you think. I think it describes exactly the problem that we see. For now, the solution that works for me is to seriously up the value of targrows in analyze.c. It makes it take longer, and while the stats are not correct because they are not designed to detect these sorts of patterns, a larger sample allows them to be less wrong enough to give a better hint to the planner. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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
Re: [HACKERS] strerror_r int and char* return type mixup on FC2
Jeroen van Iddekinge wrote: Hi, I noticed that there where strange symbols in the error message when I can't connect to a database. This happends in PHP and pgsql and in the 7.4.x and 8.0 version of postgesql in pqStrError there is a 'if def' for 'char*' and 'int' return type strerror_r. I'm have FC2 linux. This version has a 'int' return type strerror_r and not char* I think the build system doesn't detect the return type of strerror_r correctly and is not using the STRERROR_R_INT but the 'char*' part. This happends in 7.4.x(wich is the offical FC2 release) and mine own build 8.0. The libc version is the default 2.3.3 Is this problem known? According config.log i have build 8.0 with the following swiches: ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-thre ads=posix --disable-checking --disable-libunwind-exceptions --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Uh, I don't think that is a PostgreSQL config build. I don't recognize many of those flags but I know you can pass flags it doesn't understand so maybe it is. The problem you are seeing is generated by the client libpq library, not by the server. Are you testing against an 8.0 server or client? You need to use an 8.0 client libpq library. You are correct the 7.4.X libpq client didn't handle the case you describe. If you are using an 8.0 client library, check the three STRERROR defines in src/include/pg_config.h after you run configure. Particularly your STRERROR_R_INT should be defined and not undefined. Please report back. Thanks. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] strerror_r int and char* return type mixup on FC2
Thanks for the reply, I have 7.4.7 and 8.0.0 both installed , so I din't noticed that i was using 7.4.7 client. 8.0.0 works fine Thanks Jeroen Jeroen van Iddekinge wrote: Hi, I noticed that there where strange symbols in the error message when I can't connect to a database. This happends in PHP and pgsql and in the 7.4.x and 8.0 version of postgesql in pqStrError there is a 'if def' for 'char*' and 'int' return type strerror_r. I'm have FC2 linux. This version has a 'int' return type strerror_r and not char* I think the build system doesn't detect the return type of strerror_r correctly and is not using the STRERROR_R_INT but the 'char*' part. This happends in 7.4.x(wich is the offical FC2 release) and mine own build 8.0. The libc version is the default 2.3.3 Is this problem known? According config.log i have build 8.0 with the following swiches: ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-thre ads=posix --disable-checking --disable-libunwind-exceptions --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Uh, I don't think that is a PostgreSQL config build. I don't recognize many of those flags but I know you can pass flags it doesn't understand so maybe it is. The problem you are seeing is generated by the client libpq library, not by the server. Are you testing against an 8.0 server or client? You need to use an 8.0 client libpq library. You are correct the 7.4.X libpq client didn't handle the case you describe. If you are using an 8.0 client library, check the three STRERROR defines in src/include/pg_config.h after you run configure. Particularly your STRERROR_R_INT should be defined and not undefined. Please report back. Thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] strerror_r int and char* return type mixup on FC2
Jeroen van Iddekinge wrote: Thanks for the reply, I have 7.4.7 and 8.0.0 both installed , so I din't noticed that i was using 7.4.7 client. 8.0.0 works fine OK, thanks for the report. --- Thanks Jeroen Jeroen van Iddekinge wrote: Hi, I noticed that there where strange symbols in the error message when I can't connect to a database. This happends in PHP and pgsql and in the 7.4.x and 8.0 version of postgesql in pqStrError there is a 'if def' for 'char*' and 'int' return type strerror_r. I'm have FC2 linux. This version has a 'int' return type strerror_r and not char* I think the build system doesn't detect the return type of strerror_r correctly and is not using the STRERROR_R_INT but the 'char*' part. This happends in 7.4.x(wich is the offical FC2 release) and mine own build 8.0. The libc version is the default 2.3.3 Is this problem known? According config.log i have build 8.0 with the following swiches: ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-thre ads=posix --disable-checking --disable-libunwind-exceptions --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Uh, I don't think that is a PostgreSQL config build. I don't recognize many of those flags but I know you can pass flags it doesn't understand so maybe it is. The problem you are seeing is generated by the client libpq library, not by the server. Are you testing against an 8.0 server or client? You need to use an 8.0 client libpq library. You are correct the 7.4.X libpq client didn't handle the case you describe. If you are using an 8.0 client library, check the three STRERROR defines in src/include/pg_config.h after you run configure. Particularly your STRERROR_R_INT should be defined and not undefined. Please report back. Thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Query optimizer 8.0.1 (and 8.0)
What's the purpose of doing this transformation? Is it just a means to sub-divide the dataset? It's very possible that PostGIS would do just as good a job, without using HTM. Granted, GIS is designed more for working in LAT/LONG, but I suspect it should work just as well in whatever coordinate system astronomers use. Something else to consider is that it would be relatively easy to imlpement an HTM type in postgresql, which would result in substantial space savings. You need 3 bits for level 0, each additional level requires 2 bits. This will be much smaller than storing the HTM in a varchar, and also smaller than using a bit to indicate N vs S and an int (or using sign to indicate N/S with an int). On Sun, Feb 13, 2005 at 08:14:58PM +0300, Oleg Bartunov wrote: Probably off-topic, but I think it's worth to see what astronomers are doing with their very big spatial databases. For example, we are working with more than 500,000,000 rows catalog and we use some special transformation of coordinates to integer numbers with preserving objects closeness. I hope we could show postgresql is good enough to be used in astronomy for very big catalogs. Currently, MS SQL is in use. See http://www.sdss.jhu.edu/htm/ for details. We use another technique. Oleg On Wed, 9 Feb 2005 [EMAIL PROTECTED] wrote: I wrote a message caled One Big trend vs multiple smaller trends in table statistics that, I think, explains what we've been seeing. [EMAIL PROTECTED] wrote: In this case, the behavior observed could be changed by altering the sample size for a table. I submit that an arbitrary fixed sample size is not a good base for the analyzer, but that the sample size should be based on the size of the table or some calculation of its deviation. Mark, Do you have any evidence that the Sample Size had anything to do with the performance problem you're seeing? Sample size is only a bandaid for the issue, however, more samples always provide more information. I also do a lot with the complete Census/TIGER database. Every problem I have with the optimizer comes down to the fact that the data is loaded (and ordered on disk) by State/County FIPS codes, and then queried by zip-code or by city name. Like this: Alabama36101 [hundreds of pages with zip's in 36***] Alaska 99686 [hundreds of pages with zip's in 9] Arizona85701 [hundreds of pages with zip's in 855**] Note that the zip codes are *NOT* sequential. Again, read One Big Trend... and let me know what you think. I think it describes exactly the problem that we see. For now, the solution that works for me is to seriously up the value of targrows in analyze.c. It makes it take longer, and while the stats are not correct because they are not designed to detect these sorts of patterns, a larger sample allows them to be less wrong enough to give a better hint to the planner. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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 -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Design notes for BufMgrLock rewrite
I'm working on an experimental patch to break up the BufMgrLock along the lines we discussed a few days ago --- in particular, using a clock sweep algorithm instead of LRU lists for the buffer replacement strategy. I started by writing up some design notes, which are attached for review in case anyone has better ideas. One thing I realized quickly is that there is no natural way in a clock algorithm to discourage VACUUM from blowing out the cache. I came up with a slightly ugly idea that's described below. Can anyone do better? regards, tom lane Buffer manager's internal locking - Before PostgreSQL 8.1, all operations of the shared buffer manager itself were protected by a single system-wide lock, the BufMgrLock, which unsurprisingly proved to be a source of contention. The new locking scheme avoids grabbing system-wide exclusive locks in common code paths. It works like this: * There is a system-wide LWLock, the BufMappingLock, that notionally protects the mapping from buffer tags (page identifiers) to buffers. (Physically, it can be thought of as protecting the hash table maintained by buf_table.c.) To look up whether a buffer exists for a tag, it is sufficient to obtain share lock on the BufMappingLock. Note that one must pin the found buffer, if any, before releasing the BufMappingLock. To alter the page assignment of any buffer, one must hold exclusive lock on the BufMappingLock. This lock must be held across adjusting the buffer's header fields and changing the buf_table hash table. The only common operation that needs exclusive lock is reading in a page that was not in shared buffers already, which will require at least a kernel call and usually a wait for I/O, so it will be slow anyway. * A separate system-wide LWLock, the BufFreelistLock, provides mutual exclusion for operations that access the buffer free list or select buffers for replacement. This is always taken in exclusive mode since there are no read-only operations on those data structures. The buffer management policy is designed so that BufFreelistLock need not be taken except in paths that will require I/O, and thus will be slow anyway. (Details appear below.) It is never necessary to hold the BufMappingLock and the BufFreelistLock at the same time. * Each buffer header contains a spinlock that must be taken when examining or changing fields of that buffer header. This allows operations such as ReleaseBuffer to make local state changes without taking any system-wide lock. We use a spinlock, not an LWLock, since there are no cases where the lock needs to be held for more than a few instructions. Note that a buffer header's spinlock does not control access to the data held within the buffer. Each buffer header also contains an LWLock, the buffer context lock, that *does* represent the right to access the data in the buffer. It is used per the rules above. There is yet another set of per-buffer LWLocks, the io_in_progress locks, that are used to wait for I/O on a buffer to complete. The process doing a read or write takes exclusive lock for the duration, and processes that need to wait for completion try to take shared locks (which they release immediately upon obtaining). XXX on systems where an LWLock represents nontrivial resources, it's fairly annoying to need so many locks. Possibly we could use per-backend LWLocks instead (a buffer header would then contain a field to show which backend is doing its I/O). Buffer replacement strategy --- There is a free list of buffers that are prime candidates for replacement. In particular, buffers that are completely free (contain no valid page) are always in this list. We may also throw buffers into this list if we consider their pages unlikely to be needed soon. The list is singly-linked using fields in the buffer headers; we maintain head and tail pointers in global variables. (Note: although the list links are in the buffer headers, they are considered to be protected by the BufFreelistLock, not the buffer-header spinlocks.) To choose a victim buffer to recycle when there are no free buffers available, we use a simple clock-sweep algorithm, which avoids the need to take system-wide locks during common operations. It works like this: Each buffer header contains a recently used flag bit, which is set true whenever the buffer is unpinned. (Setting this bit requires only the buffer header spinlock, which would have to be taken anyway to decrement the buffer reference count, so it's nearly free.) The clock hand is a buffer index, NextVictimBuffer, that moves circularly through all the available buffers. NextVictimBuffer is protected by the BufFreelistLock. The algorithm for a process that needs to obtain a victim buffer is: 1. Obtain BufFreelistLock. 2. If buffer free list is nonempty, remove its head buffer. If the buffer is pinned or has its recently used bit set, it cannot
Re: [HACKERS] Goals for 8.1
Nicolai Tufar wrote: On Sun, 23 Jan 2005 21:43:17 -0800, Benjamin Arai [EMAIL PROTECTED] wrote: What are the goals for 8.1? Fix %n$ format string argument placement in platforms that do not support it, like HP-UX, Win32 Also add NetBSD and BSD/OS to that. It turns out %1$ isn't part of the C99 standard but was introduced by the Single Unix Specification (SUS). I am posting a patch now on this. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Design notes for BufMgrLock rewrite
Tom Lane wrote: I'm working on an experimental patch to break up the BufMgrLock along the lines we discussed a few days ago --- in particular, using a clock sweep algorithm instead of LRU lists for the buffer replacement strategy. I started by writing up some design notes, which are attached for review in case anyone has better ideas. One thing I realized quickly is that there is no natural way in a clock algorithm to discourage VACUUM from blowing out the cache. I came up with a slightly ugly idea that's described below. Can anyone do better? Uh, is the clock algorithm also sequential-scan proof? Is that something that needs to be done too? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Design notes for BufMgrLock rewrite
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: One thing I realized quickly is that there is no natural way in a clock algorithm to discourage VACUUM from blowing out the cache. I came up with a slightly ugly idea that's described below. Can anyone do better? Uh, is the clock algorithm also sequential-scan proof? Is that something that needs to be done too? If you can think of a way. I don't see any way to make the algorithm itself scan-proof, but if we modified the bufmgr API to tell ReadBuffer (or better ReleaseBuffer) that a request came from a seqscan, we could do the same thing as for VACUUM. Whether that's good enough isn't clear --- for one thing it would kick up the contention for the BufFreelistLock, and for another it might mean *too* short a lifetime for blocks fetched by seqscan. regards, tom lane ---(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
Re: [HACKERS] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Nicolai Tufar wrote:
Hello all,
I would like to submit my changes to src/port/snprintf.c to
enable %n$ format placeholder replacement in snprintf() and
vsnprintf(). Additionally I implemented a trivial printf().
I also attach a diff for configure.in to include snprintf.o
in pgport but I am sure it is not the right thing to do.
Could someone give a hint on where I need to place such a
definition.
Please review my patch. as Tom Lane pointed out there
are 150 messages in the following files that do not print
properly:
It took me a while to understand this but I get it now. This is the
best explanation I have seen, from Linux 2.6:
One can also specify explicitly which argument is taken, at each place
where an argument is required, by writing '%m$' instead of '%' and '*m$'
instead of '*', where the decimal integer m denotes the position in the
argument list of the desired argument, indexed starting from 1. Thus,
printf(%*d, width, num);
and
printf(%2$*1$d, width, num);
are equivalent. The second style allows repeated references to the
same argument. The C99 standard does not include the style using '$',
which comes from the Single Unix Specification. If the style using '$'
is used, it must be used throughout for all conversions taking an
argument and all width and precision arguments, but it may be mixed with
'%%' formats which do not consume an argument. There may be no gaps in
the numbers of arguments specified using '$'; for example, if
arguments 1 and 3 are specified, argument 2 must also be specified
somewhere in the format string.
I can see why this would be useful for translations because it uncouples
the order of the printf arguments from the printf string. However, I
have learned that Win32, HP-UX, NetBSD 2.0, and BSD/OS do not support
this. This is probably because it is not in C99 but in SUS (see above).
Anyway, this is too large to put into 8.0, but I am attaching a patch
for 8.1 that has the proper configure tests to check if the C library
supports this behavior. If it does not, the build will use our
port/snprintf.c.
One problem with that is that our snprintf.c is not thread-safe. Seems
the increases use of it will require us to fix this soon. I have added
to TODO:
* Make src/port/snprintf.c thread-safe
One change to the original port is that there was a define of a union
with no name:
+ union{
+ void* value;
+ long_long numvalue;
+ double fvalue;
+ int charvalue;
+ };
As far as I know a union with no name is illegal. I just removed the
union { and the closing brace.
--
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
Index: configure
===
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.425
diff -c -c -r1.425 configure
*** configure 18 Jan 2005 05:23:35 - 1.425
--- configure 13 Feb 2005 23:50:46 -
***
*** 12162,12167
--- 12162,12224
done
+ echo $as_me:$LINENO: checking printf supports argument control 5
+ echo $ECHO_N checking printf supports argument control... $ECHO_C 6
+ if test ${pgac_cv_printf_arg_control+set} = set; then
+ echo $ECHO_N (cached) $ECHO_C 6
+ else
+ if test $cross_compiling = yes; then
+ pgac_cv_printf_arg_control=cross
+ else
+ cat conftest.$ac_ext _ACEOF
+ #line $LINENO configure
+ #include confdefs.h
+ #include stdio.h
+
+ int does_printf_have_arg_control()
+ {
+ char buf[100];
+
+ /* can it swap arguments? */
+ snprintf(buf, 100, %2$d|%1$d, 3, 4);
+ if (strcmp(buf, 4|3) != 0)
+ return 0;
+ return 1;
+ }
+ main() {
+ exit(! does_printf_have_arg_control());
+ }
+ _ACEOF
+ rm -f conftest$ac_exeext
+ if { (eval echo $as_me:$LINENO: \$ac_link\) 5
+ (eval $ac_link) 25
+ ac_status=$?
+ echo $as_me:$LINENO: \$? = $ac_status 5
+ (exit $ac_status); } { ac_try='./conftest$ac_exeext'
+ { (eval echo $as_me:$LINENO: \$ac_try\) 5
+ (eval $ac_try) 25
+ ac_status=$?
+ echo $as_me:$LINENO: \$? = $ac_status 5
+ (exit $ac_status); }; }; then
+ pgac_cv_printf_arg_control=yes
+ else
+ echo $as_me: program exited with status $ac_status 5
+ echo $as_me: failed program was: 5
+ cat conftest.$ac_ext 5
+ ( exit $ac_status )
+ pgac_cv_printf_arg_control=no
+ fi
+ rm -f core core.* *.core conftest$ac_exeext conftest.$ac_objext
conftest.$ac_ext
+ fi
+
+ fi
+ echo $as_me:$LINENO: result: $pgac_cv_printf_arg_control 5
+ echo ${ECHO_T}$pgac_cv_printf_arg_control
Re: [HACKERS] Design notes for BufMgrLock rewrite
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: One thing I realized quickly is that there is no natural way in a clock algorithm to discourage VACUUM from blowing out the cache. I came up with a slightly ugly idea that's described below. Can anyone do better? Uh, is the clock algorithm also sequential-scan proof? Is that something that needs to be done too? If you can think of a way. I don't see any way to make the algorithm itself scan-proof, but if we modified the bufmgr API to tell ReadBuffer (or better ReleaseBuffer) that a request came from a seqscan, we could do the same thing as for VACUUM. Whether that's good enough isn't clear --- for one thing it would kick up the contention for the BufFreelistLock, and for another it might mean *too* short a lifetime for blocks fetched by seqscan. If I remember correctly, the ARC system was sequential-scan resistant _only_ because it split the cache into two parts and let the sequential scan wipe one cache while the other was for frequently accessed information and was more immune, and the overhead for a frequent cache requires too much locking. One interesting aspect is that we now have only three buffer access patterns, index pages, random heap lookups via index or ctid, and heap scans. It would be interesting to control the behavior of each one. For example, if the sequential scan is larger than the buffer cache, is there any reason to cache any of it? Should non-sequential scan pages be kept longer? Can we record on the buffer page how the page was initially or most recently accessed? The problem with the kernel cache is that is doesn't know the applicaiton access pattern, but we do so it seems we can use that information to improve the algorithm. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Dealing with network-dead clients
I'm currently trying to find a clean way to deal with network-dead clients that are in a transaction and holding locks etc. The normal client closes socket case works fine. The scenario I'm worried about is when the client machine falls off the network entirely for some reason (ethernet problem, kernel panic, machine catches fire..). From what I can see, if the connection is idle at that point, the server won't notice this until TCP-level SO_KEEPALIVE kicks in, which by default takes over 2 hours on an idle connection. I'm looking for something more like a 30-60 second turnaround if the client is holding locks. The options I can see are: 1) tweak TCP keepalive intervals down to a low value, system-wide 2) use (nonportable) setsockopt calls to tweak TCP keepalive settings on a per-socket basis. 3) implement an idle timeout on the server so that open transactions that are idle for longer than some period are automatically aborted. (1) is very ugly because it is system-wide. (2) is not portable. Also I'm not sure how well extremely low keepalive settings behave. (3) seems like a proper solution. I've searched the archives a bit and transaction timeouts have been suggested before, but there seems to be some resistance to them. I was thinking along the lines of a SIGALRM-driven timeout that starts at the top of the query-processing loop when in a transaction and is cancelled when client traffic is received. I'm not sure exactly what should happen when the timeout occurs, though. Should it kill the entire connection, or just roll back the current transaction? If the connection stays alive, the fun part seems to be in avoiding confusing the client about the current transaction state. Any suggestions on what I should do here? -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Query optimizer 8.0.1 (and 8.0)
Just my 2 cents. I am not a super statistics guy but besides increasing the sample size and assumming things on the distribution, I understand you want to get more info on what distribution the data represents. usualy the problem with these things is that the data needs to be sorted on the index key and also it could take a while, at least for the one time you want to find out what is the distribution. Example: for comulative distributions you need to first sort the data (I am talking scalars but probably other keys can work) and run it sequentially thru a KS(Kolmogorov smirnov) test. (there are other tests but this is good for general cases) The test can be against all kind of comulative distributions like normals,etc... You then get a feel of how close is the data to the selected distribution with a parameter that can be rejected at 0.01, 0.05, 0.1, etc... Anyway, it can be done, however I am not sure how much better is it over just plain histograms with random() and uniform dist. Or what happens if you just increase the sample size and be done with it. Again, I am talking about the general/common cases. Regards, tzahi. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, February 09, 2005 3:46 PM To: Ron Mayer Cc: Mark Kirkwood; Tom Lane; Ron Mayer; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Query optimizer 8.0.1 (and 8.0) I wrote a message caled One Big trend vs multiple smaller trends in table statistics that, I think, explains what we've been seeing. [EMAIL PROTECTED] wrote: In this case, the behavior observed could be changed by altering the sample size for a table. I submit that an arbitrary fixed sample size is not a good base for the analyzer, but that the sample size should be based on the size of the table or some calculation of its deviation. Mark, Do you have any evidence that the Sample Size had anything to do with the performance problem you're seeing? Sample size is only a bandaid for the issue, however, more samples always provide more information. I also do a lot with the complete Census/TIGER database. Every problem I have with the optimizer comes down to the fact that the data is loaded (and ordered on disk) by State/County FIPS codes, and then queried by zip-code or by city name. Like this: Alabama36101 [hundreds of pages with zip's in 36***] Alaska 99686 [hundreds of pages with zip's in 9] Arizona85701 [hundreds of pages with zip's in 855**] Note that the zip codes are *NOT* sequential. Again, read One Big Trend... and let me know what you think. I think it describes exactly the problem that we see. For now, the solution that works for me is to seriously up the value of targrows in analyze.c. It makes it take longer, and while the stats are not correct because they are not designed to detect these sorts of patterns, a larger sample allows them to be less wrong enough to give a better hint to the planner. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Design notes for BufMgrLock rewrite
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: One thing I realized quickly is that there is no natural way in a clock algorithm to discourage VACUUM from blowing out the cache. I came up with a slightly ugly idea that's described below. Can anyone do better? Uh, is the clock algorithm also sequential-scan proof? Is that something that needs to be done too? I think the normal strategy is to make it *always* work the way you made VACUUM work. That is, it should always enter newly loaded pages with the recently used flag false. It doesn't necessarily mean they get purged immediately on the next flush, any other buffer that hasn't been accessed since it was loaded is also a candidate, but if nothing else accesses it before the clock hand gets to it then it a candidate. The only thing that scares me about this is that running a vacuum or sequential scan could have too much of an effect on non-sequential accesses like index scans if it forced the hand around so fast that the index scan didn't have a chance to reuse pages. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] getting oid of function
Hello all Is it possible to get the oid of a function on the basis of its name?. The scenario which i am currently facing is that i have the function name, now i want search the pg_proc system catalog on the basis of the function name and retrieve its Oid. Another confusion which i am facing is that, I am not sure whether Oid of a function is entered in pg_proc system catalog or not. Because i am not able to identify any relevant field. thank you ---(end of broadcast)--- TIP 8: explain analyze is your friend
