Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > What kinda processor are you running? >From dmesg: Timecounter "i8254" frequency 1193182 Hz CPU: Intel Celeron (631.29-MHz 686-class CPU) Origin = "GenuineIntel" Id = 0x686 Stepping = 6 Features=0x383f9ff -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Neil Conway wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > On Fri, 28 Nov 2003, Neil Conway wrote: > >> $ uname -a > >> FreeBSD home.samurai.com 4.9-RELEASE [...] > > > > what is kern.timecounter.method set to? > > $ sysctl kern.timecounter.method > kern.timecounter.method: 0 > > FYI, I ran the test program for another 30 minutes (an hour in total) > without any output. Great, I love consistent results :) Some have 5.x that do it, some don't ... 4.x does it on all my machines, but not yours ... What kinda processor are you running? Intel or AMD? Mine are all Intel ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > On Fri, 28 Nov 2003, Neil Conway wrote: >> $ uname -a >> FreeBSD home.samurai.com 4.9-RELEASE [...] > > what is kern.timecounter.method set to? $ sysctl kern.timecounter.method kern.timecounter.method: 0 FYI, I ran the test program for another 30 minutes (an hour in total) without any output. -Neil ---(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] cvs head configure broken for --with-krb5 on RH9
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: As of this change: http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php I can't seem to configure --with-krb5. I believe the idea is that instead of --with-krb5=/usr/kerberos you now need --with-krb5 --with-includes=/usr/kerberos/include --with-libs=/usr/kerberos/lib or some variant like that. It'd be good for the docs to be more explicit about this change. Ah, that did the trick. Thanks! Joe ---(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] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Neil Conway wrote: > $ uname -a > FreeBSD home.samurai.com 4.9-RELEASE FreeBSD 4.9-RELEASE #0: Thu Nov 6 21:25:41 EST > 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SAMURAI i386 > > This machine produces no output after running the test app for about > 30 minutes. what is kern.timecounter.method set to? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cvs head configure broken for --with-krb5 on RH9
Joe Conway <[EMAIL PROTECTED]> writes: > As of this change: > http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php > I can't seem to configure --with-krb5. I believe the idea is that instead of --with-krb5=/usr/kerberos you now need --with-krb5 --with-includes=/usr/kerberos/include --with-libs=/usr/kerberos/lib or some variant like that. It'd be good for the docs to be more explicit about this change. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: >> 15 minutes of error is acceptable? > Apparently *shrug* That was just the first answer back though, so we'll > see what happens ... BTW, it seems from this thread that the BIND guys have seen even more bogus behavior than this from gettimeofday() on BSD. Their attitude is clearly "BSD is broken": http://www.isc.org/ml-archives/bind-users/2001/01/msg01246.html regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > Just got my first response on this, and, in fact, this is 'acceptable > > behaviour' to a certain extent ... there is a kernel tuneable called > > kern.timecounter.method, that is either 1 or 0: > > 15 minutes of error is acceptable? Apparently *shrug* That was just the first answer back though, so we'll see what happens ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] cvs head configure broken for --with-krb5 on RH9
As of this change: http://archives.postgresql.org/pgsql-committers/2003-11/msg00278.php I can't seem to configure --with-krb5. The configure script I've used (derived from what the the RH9 RPM spec file does) for quite some time now is: ./configure \ --host=i386-redhat-linux \ --build=i386-redhat-linux \ --target=i386-redhat-linux-gnu \ --program-prefix= \ --prefix=/usr \ --exec-prefix=/usr \ --bindir=/usr/bin \ --sbindir=/usr/sbin \ --sysconfdir=/etc \ --datadir=/usr/share \ --includedir=/usr/include \ --libdir=/usr/lib \ --libexecdir=/usr/libexec \ --localstatedir=/var \ --sharedstatedir=/usr/com \ --mandir=/usr/share/man \ --disable-rpath \ --with-perl \ --with-tcl \ --with-tclconfig=/usr/lib \ --without-tk \ --with-python \ --enable-nls \ --enable-debug \ --enable-cassert \ --enable-depend \ --sysconfdir=/etc/sysconfig/pgsql \ --datadir=/usr/share/pgsql \ --with-docdir=/usr/share/doc \ --with-openssl \ --with-pam \ --enable-integer-datetimes \ --with-krb5=/usr/kerberos With the above I get: [...] checking whether to build Java/JDBC tools... no checking whether to build with Kerberos 4 support... no checking whether to build with Kerberos 5 support... configure: error: no argument expected for --with-krb5 option If I follow the advice of the error message, and change that last line to just "--with-krb5", I get this: [...] checking for inflate in -lz... yes checking for library containing com_err... -lcom_err checking for library containing krb5_encrypt... no configure: error: could not find function 'krb5_encrypt' required for Kerberos 5 What should I be doing differently? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Marc G. Fournier wrote: > On Fri, 28 Nov 2003, Tom Lane wrote: > > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > I'm suspecting that the issue is amplified by load on the server itself > > > > That would fit right in with my idea that the failure occurs when the > > process loses its timeslice partway through gettimeofday(). Heavier > > load would presumably make it more likely to lose the CPU. > > I'm posting to the freebsd lists about it, see if they have anything to > say ... Just got my first response on this, and, in fact, this is 'acceptable behaviour' to a certain extent ... there is a kernel tuneable called kern.timecounter.method, that is either 1 or 0: 0sacrifice precision in favor of faster code execution 1return the more precise time (the same as calling microtime() or nanotime()). The intent of the getmicrotime() and getnanotime() functions is to enforce the user's preference for timer accuracy versus execution time. Now, reading the man page, there seems to be a 'microtime' vs 'getmicrotime' function ... these might be preferable to using gettimeofday on FreeBSD? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] gettimeofday() goes backwards on FreeBSD 4.9
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Just got my first response on this, and, in fact, this is 'acceptable > behaviour' to a certain extent ... there is a kernel tuneable called > kern.timecounter.method, that is either 1 or 0: 15 minutes of error is acceptable? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > > $ time ./a.out 2>&1 | tee a.txt > > out of order tv_sec: 1070066197 273140, prev 1070066195 721010 > > out of order tv_usec: 1070066197 273140, prev 1070066195 721010 > > out of order tv_sec: 1070067322 116061, prev 1070067320 440490 > > out of order tv_usec: 1070067322 116061, prev 1070067320 440490 > > out of order tv_sec: 1070067833 514969, prev 1070067831 755019 > > out of order tv_usec: 1070067833 514969, prev 1070067831 755019 > > ^C > > AFAICT the above is a legal trace, indicating only that the test program > sometimes lost control for more than a second at a time. The "revised" > version of the test will not complain about this. Doh! I was looking for time going backwards so that's what I saw, even though I already new the same complaint would be made if time skipped forward a second or so. > One variable I didn't think to ask about is whether you are running > NTP. In my experience an ntp daemon that has achieved lock will never > step the clock back by even 1 usec (it's supposed to use much more > subtle methods than that to manage the clock ;-)) but maybe under > unstable conditions such things could happen. The machines I have > tested here all run NTP. Yes, all NTPed. The FreeBSD system, the one that doesn't skip back *smacks forehead*, is the local time server but is generally lightly loaded. Not to say that there's not going to be the odd job it has to run which could make the system busy and time time away from this test. It's just difficult to say that every so often there's some task that makes the system unresponsive for a couple of seconds, which is what this is saying. Anyway, I like your suggestion as the timeslicing being a possible cause. Seems a little strange though, presumably all the numbers going into the timeofday calc are gathered by the one system call at a place past where the thread could have had it's time sliced away and before the next. However, I have no idea how the BSD kernel operates so can't do more than assume and guess. -- Nigel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > I'm suspecting that the issue is amplified by load on the server itself > > That would fit right in with my idea that the failure occurs when the > process loses its timeslice partway through gettimeofday(). Heavier > load would presumably make it more likely to lose the CPU. I'm posting to the freebsd lists about it, see if they have anything to say ... > > BTW, I can't get top(1) to work on either pgsql74 or svr1, any idea why? top is one of those things that do not work within a VM ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > I'm suspecting that the issue is amplified by load on the server itself That would fit right in with my idea that the failure occurs when the process loses its timeslice partway through gettimeofday(). Heavier load would presumably make it more likely to lose the CPU. BTW, I can't get top(1) to work on either pgsql74 or svr1, any idea why? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > Could it be a hardware problem on Marc's box? Or specific to some other > aspect of that installation (Marc, is pgsql74.hub.org multi-CPU, for > example?) All the servers are SMP ... pgsql74 is on a Dual-Xeon ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > Ah, I have made a mistake. It's only a 2.2.18 kernal. Dual SMP P-III, perhaps > that's the issue there. Hm. I seem to recall there were still lots of SMP bugs in the 2.2.* kernels. > And on the FreeBSD system I've got this: > $ time ./a.out 2>&1 | tee a.txt > out of order tv_sec: 1070066197 273140, prev 1070066195 721010 > out of order tv_usec: 1070066197 273140, prev 1070066195 721010 > out of order tv_sec: 1070067322 116061, prev 1070067320 440490 > out of order tv_usec: 1070067322 116061, prev 1070067320 440490 > out of order tv_sec: 1070067833 514969, prev 1070067831 755019 > out of order tv_usec: 1070067833 514969, prev 1070067831 755019 > ^C AFAICT the above is a legal trace, indicating only that the test program sometimes lost control for more than a second at a time. The "revised" version of the test will not complain about this. Time going backwards by even one microsecond, however, is generally considered Bad News, unless you're actively manipulating the system clock setting. One variable I didn't think to ask about is whether you are running NTP. In my experience an ntp daemon that has achieved lock will never step the clock back by even 1 usec (it's supposed to use much more subtle methods than that to manage the clock ;-)) but maybe under unstable conditions such things could happen. The machines I have tested here all run NTP. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Darcy Buskermolen wrote: > I've been running this code on a pair of FreeBSD (i386) boxen, for some time > now, one of which is a 4.8-STABLE, the other is a 5.2-BETA. > > In 10 minutes of execution the 4.8 box has only shown the following: > > out of order tv_sec: 1070063393 115226, prev 1070063391 381087 > out of order tv_usec: 1070063393 115226, prev 1070063391 381087 I'm suspecting that the issue is amplified by load on the server itself ... > the 5.2 box, hasn't outputed anything. I'm goignt o run this test on > some other boxen I have and see what/ifany results are happening. Similar results here with a 5.2 box ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On Fri, 28 Nov 2003, Tom Lane wrote: > >> "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they > > are all just a microsecond. > >> > >> What do you mean by "just a microsecond"? > > > I mean it's always a "out of order tv_usec..." line and the difference is > > 1us. That is a.out gives: > > > out of order tv_usec: 1070065862 374978, prev 1070065862 374979 > > out of order tv_usec: 1070065867 814300, prev 1070065867 814301 > > Fascinating. I'd call that a bug too, but evidently one with a > different mechanism than the BSD issue we are chasing. > > FWIW, I have not seen any failures in a fair amount of runtime on > a 2.4.18 (Red Hat 8.0) kernel here, running on a Dell P4. What is > the hardware platform you're using? Ah, I have made a mistake. It's only a 2.2.18 kernal. Dual SMP P-III, perhaps that's the issue there. And on the FreeBSD system I've got this: $ time ./a.out 2>&1 | tee a.txt out of order tv_sec: 1070066197 273140, prev 1070066195 721010 out of order tv_usec: 1070066197 273140, prev 1070066195 721010 out of order tv_sec: 1070067322 116061, prev 1070067320 440490 out of order tv_usec: 1070067322 116061, prev 1070067320 440490 out of order tv_sec: 1070067833 514969, prev 1070067831 755019 out of order tv_usec: 1070067833 514969, prev 1070067831 755019 ^C real38m53.026s user6m13.953s sys 32m6.589s So not very often there. -- Nigel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
Tom Lane <[EMAIL PROTECTED]> writes: > Whoa. Try the following test program. I tried this on two uniprocessor x86 machines: (1) $ uname -a Linux tokyo 2.6.0-test10 #3 Mon Nov 24 13:43:54 EST 2003 i686 GNU/Linux This machine produces this output, after running the test app for about 30 minutes: out of order tv_sec: 1070067959 929846, prev 1070067957 813066 out of order tv_usec: 1070068017 80145, prev 1070068017 80384 out of order tv_usec: 1070068017 104138, prev 1070068017 104368 (2) $ uname -a FreeBSD home.samurai.com 4.9-RELEASE FreeBSD 4.9-RELEASE #0: Thu Nov 6 21:25:41 EST 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SAMURAI i386 This machine produces no output after running the test app for about 30 minutes. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
"Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > On Fri, 28 Nov 2003, Tom Lane wrote: >> "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they > are all just a microsecond. >> >> What do you mean by "just a microsecond"? > I mean it's always a "out of order tv_usec..." line and the difference is > 1us. That is a.out gives: > out of order tv_usec: 1070065862 374978, prev 1070065862 374979 > out of order tv_usec: 1070065867 814300, prev 1070065867 814301 Fascinating. I'd call that a bug too, but evidently one with a different mechanism than the BSD issue we are chasing. FWIW, I have not seen any failures in a fair amount of runtime on a 2.4.18 (Red Hat 8.0) kernel here, running on a Dell P4. What is the hardware platform you're using? 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] gettimeofday() goes backwards on FreeBSD 4.9
Darcy Buskermolen <[EMAIL PROTECTED]> writes: > After 30+ minutes I'm now starting to see the same problems on my 5.2 testbed > as well. I'm going to fire this test up on my FreeBSD 3.x and 2.x boxen and > see if' it's there too. I can confirm that this not a SMP issue as that it's > happeing on both UP and SMP boxen for me. Good, that's one variable eliminated. Looking at my own data, I notice that when the error happens, the elapsed time shown between the immediately preceding and following okay-looking timestamps is always significant (at least 100 msec and often a second or more). That is kind of a lot for a tight loop containing one simple kernel call, no? I am suspicious that the failure occurs when gettimeofday() is called just as the process is losing control of the CPU (due to using up its timeslice or whatever). When control eventually returns, the process gets a reading that is neither pre-loss-of-CPU nor post-regain-of-CPU, but some unholy combination that nets out as a time about 15 min in the past. Just a theory, but it fits some of the available facts. Can anyone think of a significant interpretation for the number 695 seconds? That's got to be an important clue ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > "Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > > On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they > > are all just a microsecond. > > What do you mean by "just a microsecond"? I mean it's always a "out of order tv_usec..." line and the difference is 1us. That is a.out gives: out of order tv_usec: 1070065862 374978, prev 1070065862 374979 out of order tv_usec: 1070065867 814300, prev 1070065867 814301 out of order tv_usec: 1070065868 794176, prev 1070065868 794177 out of order tv_usec: 1070065871 553831, prev 1070065871 553832 etc. > > Attached is a tightened-up test program that will only complain if the > value of gettimeofday goes backward (at all) or forward by more than > 10 seconds (adjustable as MAX_SKIP). This should be suitable to run on > moderately loaded machines where the test program might occasionally not > get dispatched for a few seconds. I don't think that would show any for me. I've only got two skips showing from a longer run on my FreeBSD 3.3 and those differ in the seconds counter by only 2. Time between the occurances is about 1100s. -- Nigel ---(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] gettimeofday() goes backwards on FreeBSD 4.9
"Nigel J. Andrews" <[EMAIL PROTECTED]> writes:
> On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they
> are all just a microsecond.
What do you mean by "just a microsecond"?
Attached is a tightened-up test program that will only complain if the
value of gettimeofday goes backward (at all) or forward by more than
10 seconds (adjustable as MAX_SKIP). This should be suitable to run on
moderately loaded machines where the test program might occasionally not
get dispatched for a few seconds.
I still see no failures on my own (non-BSD) machines, fairly frequent
failures on pgsql74.hub.org:
> ./a.out
out of order tv_sec: 1070066031 262048, prev 1070066726 688240
out of order tv_sec: 1070066726 785019, prev 1070066031 262048
out of order tv_sec: 1070066062 62622, prev 1070066757 588814
out of order tv_sec: 1070066757 771848, prev 1070066062 62622
out of order tv_sec: 1070066093 262974, prev 1070066788 689167
out of order tv_sec: 1070066788 777486, prev 1070066093 262974
out of order tv_sec: 1070066114 113410, prev 1070066809 589602
out of order tv_sec: 1070066809 724663, prev 1070066114 113410
out of order tv_sec: 1070066145 113899, prev 1070066840 590097
out of order tv_sec: 1070066840 726558, prev 1070066145 113899
out of order tv_sec: 1070066155 263911, prev 1070066850 690103
out of order tv_sec: 1070066850 781343, prev 1070066155 263911
out of order tv_sec: 1070066176 164307, prev 1070066871 590505
out of order tv_sec: 1070066871 643350, prev 1070066176 164307
out of order tv_sec: 1070066217 264846, prev 1070066912 691039
out of order tv_sec: 1070066912 775989, prev 1070066217 264846
out of order tv_sec: 1070066248 65394, prev 1070066943 591592
out of order tv_sec: 1070066943 773822, prev 1070066248 65394
^C
and rarer failures on cvs.postgresql.org:
> ./a.out
out of order tv_sec: 1070066099 389427, prev 1070066794 865617
out of order tv_sec: 1070066795 17855, prev 1070066099 389427
out of order tv_sec: 1070066252 541729, prev 1070066947 967921
out of order tv_sec: 1070066948 38715, prev 1070066252 541729
out of order tv_sec: 1070066371 393525, prev 1070067066 869715
out of order tv_sec: 1070067068 24754, prev 1070066371 393525
^C
It seems consistent that the error is 695 seconds and change
when it happens, and that the very next read gives a correct
(or at least plausible) value again.
regards, tom lane
#include
#include
#define MAX_SKIP 10
int
main()
{
struct timeval prevtime;
struct timeval curtime;
gettimeofday(&prevtime, NULL);
for (;;)
{
gettimeofday(&curtime, NULL);
if (curtime.tv_usec < 0 || curtime.tv_usec >= 100)
fprintf(stderr, "bogus tv_usec: %ld %ld, prev %ld %ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
else if (curtime.tv_sec < prevtime.tv_sec ||
curtime.tv_sec > prevtime.tv_sec + MAX_SKIP)
fprintf(stderr, "out of order tv_sec: %ld %ld, prev %ld %ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
else if (curtime.tv_usec < prevtime.tv_usec &&
curtime.tv_sec == prevtime.tv_sec)
fprintf(stderr, "out of order tv_usec: %ld %ld, prev %ld
%ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
prevtime = curtime;
}
return 0;
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On November 28, 2003 04:19 pm, Tom Lane wrote: > I said: > > The failure is definitely quite repeatable on pgsql74.hub.org. I don't > > see it on svr1.postgresql.org, though, which seems to be running almost > > the same kernel. > > After looking more closely, I take that back: svr1 is failing too, > After 30+ minutes I'm now starting to see the same problems on my 5.2 testbed as well. I'm going to fire this test up on my FreeBSD 3.x and 2.x boxen and see if' it's there too. I can confirm that this not a SMP issue as that it's happeing on both UP and SMP boxen for me. > though not as often: > > uname -a > > FreeBSD svr1.postgresql.org 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #4: Sat > Sep 20 14:41:58 ADT 2003 > [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel i386 > > > ./a.out > > out of order tv_sec: 1070064182 410497, prev 1070064877 836691 > <--- out of order tv_usec: 1070064182 410497, prev 1070064877 > 836691 > out of order tv_sec: 1070064877 920579, prev 1070064182 410497 > out of order tv_sec: 1070064901 126624, prev 1070064899 897160 > out of order tv_usec: 1070064901 126624, prev 1070064899 897160 > out of order tv_sec: 1070064907 306286, prev 1070064905 481423 > out of order tv_usec: 1070064907 306286, prev 1070064905 481423 > out of order tv_sec: 1070064218 861047, prev 1070064914 337241 > <--- out of order tv_sec: 1070064914 570717, prev 1070064218 861047 > out of order tv_usec: 1070064914 570717, prev 1070064218 861047 > out of order tv_sec: 1070064241 411391, prev 1070064936 837585 > <--- out of order tv_usec: 1070064241 411391, prev 1070064936 > 837585 > out of order tv_sec: 1070064937 497925, prev 1070064241 411391 > out of order tv_sec: 1070064251 811548, prev 1070064947 337739 > <--- out of order tv_sec: 1070064947 508364, prev 1070064251 811548 > out of order tv_usec: 1070064947 508364, prev 1070064251 811548 > > Maybe it's a 4.9-PRERELEASE bug? > > regards, tom lane -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(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] gettimeofday() goes backwards on FreeBSD 4.9
On Fri, 28 Nov 2003, Tom Lane wrote: > Darcy Buskermolen <[EMAIL PROTECTED]> writes: > > On November 28, 2003 12:33 pm, Tom Lane wrote: > >> Whoa. Try the following test program. Then send it in to your friendly > >> local BSD hackers > > > I've been running this code on a pair of FreeBSD (i386) boxen, for some time > > now, one of which is a 4.8-STABLE, the other is a 5.2-BETA. > > Could it be a hardware problem on Marc's box? Or specific to some other > aspect of that installation (Marc, is pgsql74.hub.org multi-CPU, for > example?) > > The failure is definitely quite repeatable on pgsql74.hub.org. I don't > see it on svr1.postgresql.org, though, which seems to be running almost > the same kernel. On an Intel Linux 2.4.18 I get them quite often, 25 in 1'45", but they are all just a microsecond. On an Intel FreeBSD 3.3 I had one just after starting the program that was 2 secs behind. Then I stopped to restart with a leading under time and I've not seen any since. (4'50" it was running) .529 .2 user/sys time = 0.53 on the Linux system user/sys time = 0.2 on the FreeBSD one Make of that what you will. -- Nigel Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
On November 28, 2003 12:33 pm, Tom Lane wrote: > I said: > > I'm wondering if there's an actual bug in gettimeofday() in this > > platform, such that once in a while it returns a value that's off > > a minute or so ... > > Whoa. Try the following test program. Then send it in to your friendly > local BSD hackers > > > uname -a > > FreeBSD pgsql74.hub.org 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #3: Sat Sep > 20 14:16:48 ADT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel > i386 > > > ./a.out 8<-- snip Further to my last email, after 30 minutes of running I was able to get simular behavior off the 5.2 test system. -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
I said: > The failure is definitely quite repeatable on pgsql74.hub.org. I don't > see it on svr1.postgresql.org, though, which seems to be running almost > the same kernel. After looking more closely, I take that back: svr1 is failing too, though not as often: > uname -a FreeBSD svr1.postgresql.org 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #4: Sat Sep 20 14:41:58 ADT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel i386 > ./a.out out of order tv_sec: 1070064182 410497, prev 1070064877 836691 <--- out of order tv_usec: 1070064182 410497, prev 1070064877 836691 out of order tv_sec: 1070064877 920579, prev 1070064182 410497 out of order tv_sec: 1070064901 126624, prev 1070064899 897160 out of order tv_usec: 1070064901 126624, prev 1070064899 897160 out of order tv_sec: 1070064907 306286, prev 1070064905 481423 out of order tv_usec: 1070064907 306286, prev 1070064905 481423 out of order tv_sec: 1070064218 861047, prev 1070064914 337241 <--- out of order tv_sec: 1070064914 570717, prev 1070064218 861047 out of order tv_usec: 1070064914 570717, prev 1070064218 861047 out of order tv_sec: 1070064241 411391, prev 1070064936 837585 <--- out of order tv_usec: 1070064241 411391, prev 1070064936 837585 out of order tv_sec: 1070064937 497925, prev 1070064241 411391 out of order tv_sec: 1070064251 811548, prev 1070064947 337739 <--- out of order tv_sec: 1070064947 508364, prev 1070064251 811548 out of order tv_usec: 1070064947 508364, prev 1070064251 811548 Maybe it's a 4.9-PRERELEASE bug? 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
Re: [HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
Darcy Buskermolen <[EMAIL PROTECTED]> writes: > On November 28, 2003 12:33 pm, Tom Lane wrote: >> Whoa. Try the following test program. Then send it in to your friendly >> local BSD hackers > I've been running this code on a pair of FreeBSD (i386) boxen, for some time > now, one of which is a 4.8-STABLE, the other is a 5.2-BETA. Could it be a hardware problem on Marc's box? Or specific to some other aspect of that installation (Marc, is pgsql74.hub.org multi-CPU, for example?) The failure is definitely quite repeatable on pgsql74.hub.org. I don't see it on svr1.postgresql.org, though, which seems to be running almost the same kernel. 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] gettimeofday() goes backwards on FreeBSD 4.9
On November 28, 2003 12:33 pm, Tom Lane wrote:
> I said:
> > I'm wondering if there's an actual bug in gettimeofday() in this
> > platform, such that once in a while it returns a value that's off
> > a minute or so ...
>
> Whoa. Try the following test program. Then send it in to your friendly
> local BSD hackers
>
> > uname -a
>
> FreeBSD pgsql74.hub.org 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #3: Sat Sep
> 20 14:16:48 ADT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel
> i386
>
> > ./a.out
>
> out of order tv_sec: 1070050646 939970, prev 1070051342 516165
> out of order tv_sec: 1070051342 789127, prev 1070050646 939970
> out of order tv_usec: 1070051342 789127, prev 1070050646 939970
> out of order tv_sec: 1070050654 690255, prev 1070051350 216448
> out of order tv_sec: 1070051350 402412, prev 1070050654 690255
> out of order tv_usec: 1070051350 402412, prev 1070050654 690255
> out of order tv_sec: 1070050672 990359, prev 1070051368 416546
> out of order tv_sec: 1070051368 501648, prev 1070050672 990359
> out of order tv_usec: 1070051368 501648, prev 1070050672 990359
> out of order tv_sec: 1070050672 990363, prev 1070051368 516557
> out of order tv_sec: 1070051368 700227, prev 1070050672 990363
> out of order tv_usec: 1070051368 700227, prev 1070050672 990363
> out of order tv_sec: 1070050675 740524, prev 1070051371 216718
> out of order tv_sec: 1070051371 346521, prev 1070050675 740524
> out of order tv_usec: 1070051371 346521, prev 1070050675 740524
> out of order tv_sec: 1070050676 440415, prev 1070051371 916605
> out of order tv_usec: 1070050676 440415, prev 1070051371 916605
> out of order tv_sec: 1070051372 238348, prev 1070050676 440415
> out of order tv_usec: 1070051372 238348, prev 1070050676 440415
> ^C
>
I've been running this code on a pair of FreeBSD (i386) boxen, for some time
now, one of which is a 4.8-STABLE, the other is a 5.2-BETA.
In 10 minutes of execution the 4.8 box has only shown the following:
out of order tv_sec: 1070063393 115226, prev 1070063391 381087
out of order tv_usec: 1070063393 115226, prev 1070063391 381087
the 5.2 box, hasn't outputed anything. I'm goignt o run this test on some
other boxen I have and see what/ifany results are happening.
>
> regards, tom lane
>
>
> #include
> #include
>
> int
> main()
> {
> struct timeval prevtime;
> struct timeval curtime;
>
> gettimeofday(&prevtime, NULL);
>
> for (;;)
> {
> gettimeofday(&curtime, NULL);
>
> if (curtime.tv_usec < 0 || curtime.tv_usec >= 100)
> fprintf(stderr, "bogus tv_usec: %ld %ld, prev %ld %ld\n",
> (long int) curtime.tv_sec,
> (long int) curtime.tv_usec,
> (long int) prevtime.tv_sec,
> (long int) prevtime.tv_usec);
>
> if (curtime.tv_sec < prevtime.tv_sec ||
> curtime.tv_sec > prevtime.tv_sec + 1)
> fprintf(stderr, "out of order tv_sec: %ld %ld, prev %ld %ld\n",
> (long int) curtime.tv_sec,
> (long int) curtime.tv_usec,
> (long int) prevtime.tv_sec,
> (long int) prevtime.tv_usec);
>
> if (curtime.tv_usec < prevtime.tv_usec &&
> curtime.tv_sec != prevtime.tv_sec + 1)
> fprintf(stderr, "out of order tv_usec: %ld %ld, prev %ld
> %ld\n",
> (long int) curtime.tv_sec,
> (long int) curtime.tv_usec,
> (long int) prevtime.tv_sec,
> (long int) prevtime.tv_usec);
>
> prevtime = curtime;
> }
>
> return 0;
> }
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com
---(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] Date bug in PG
Gavin Sherry <[EMAIL PROTECTED]> writes: > What to do? Well, as far as I can tell, there are no work arounds This was discussed a few months ago and set aside because no one had a really decent solution at the time. The behavior is not really all that different from the discontinuities that occur around a daylight-saving transition, but people are used to those because (a) they happen every year, and (b) the bizarreness only lasts an hour and doesn't (with most DST rules) affect local midnight. The discontinuities in apparent local time at the ends of the 32-bit-time_t interval are larger and harder to miss, especially for those of you half a world away from Greenwich. The best thing I have been able to think of is to eliminate these discontinuities by changing our existing definition that says "all times outside the time_t interval (1901 to 2038 at present) are taken as GMT". We could instead define times before the interval as having the same local time offset as prevailed at the start of the interval, and likewise times after the interval have the latest time offset we can determine within the interval. Then there is no DST-like discontinuity in local time at either end of the interval. This might be too big a change in behavior though. Also, if there's anyone whose local timezone database starts in DST mode, it might seem odd for all times before 1901 to look like DST rather than local standard time. Thoughts? Note that this isn't directly connected to the idea of eliminating our dependence on the standard libc timezone routines. If we rolled our own, we'd still need to define what the behavior is outside the range of dates for which we have timezone database entries. But I suspect we'd settle on something more nearly like the above than like the existing behavior... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] background writer, WAL and snapshot backups
On Wed, 25 Nov 2003, Paul Tuckfield wrote: > I really like the idea of taking a snapshot backup with postgres, using > either volume manager or hardware splits in a disk array to get a > physical backup. Does anyone else see any value in snapshotting. It would, I image, essentially work like this: A snapshot command is issued, the background writer CHECKPOINTS, the background writer/buffer manager writes out all new data to a different disk partition, any reads also need to look at the data in the temporary partition, and while this is happening, the main data directories are being copied some where else. Once it is finished, everything is tied up again. It would be quite fast and would make restores rather painless, but it wouldn't be trivial to implement :-) (could you even handle b-tree or would you have to seqscan any new data?). Just a thought, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Date bug in PG
On Thu, 27 Nov 2003, Mike Macaskill wrote:
> Linux 2.4.20, PG 7.3.2 (this is the box where we wish to run our
> application)
> HP-UX 11.11, PG 7.3.2, 7.3.4, 7.4 (this is just a test box where PG
> installs take less time)
>
> On all of these configurations the the date '1901/12/14' apparently does not
> exist. For example,
> using PG 7.4 on HP-UX 11.11:
>
> shower 40: /adamdb/postgres/pg_admin/pgsql_7.4/bin/psql -d db74 -U tcz
> Password:
> Welcome to psql 7.4, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
>\h for help with SQL commands
>\? for help on internal slash commands
>\g or terminate with semicolon to execute query
>\q to quit
>
> db74=> select cast(timestamptz '1901/12/14' as date);
> date
>
> 1901-12-13
> (1 row)
Oh no. I can reproduce this problem using the Australian east coast
timezones. The problem is caused by the casting of the date to a timestamp
with time zone.
What is happening is this: 1901-12-13 with Australian timezone gives us
this in DetermineLocalTimeZone():
$4 = -2147472000
(gdb) print *tx
$5 = {tm_sec = 0, tm_min = 0, tm_hour = 10, tm_mday = 14, tm_mon = 11,
tm_year = 1, tm_wday = 6, tm_yday = 347, tm_isdst = 0, tm_gmtoff = 36000,
tm_zone = 0x82fbb90 "EST"}
The gmt offset is 36000 seconds = +10 hours. Which is correct.
We then compute the offset ourselves (presumably not every platform has
tm_gmtoff). This comes out as a negative.
We then subtract this from $4 in case we're right on a timezone boundary.
In theory, this shouldn't affect dates, since the timezone change is not
going to be +/-24, but the code is used for timestamps which may include
hours, minutes, seconds, etc. Once we subtract 36000 from $4, we're
screwed, since: $4 < - 2^31.
This means we have the following:
$11 = 2147459296
(gdb) print *tx
$12 = {tm_sec = 16, tm_min = 28, tm_hour = 7, tm_mday = 19, tm_mon = 0,
tm_year = 138, tm_wday = 2, tm_yday = 18, tm_isdst = 1, tm_gmtoff = 39600,
tm_zone = 0x82fbb90 "EST"}
Which is obviously wrong, since 1901 != 2138 (see tm_year + 1900).
What to do? Well, as far as I can tell, there are no work arounds (do you
really need to cast the date to a timestamp with timezone, then to a
date?).
As for fixing the code, DetermineLocalTimeZone will presumably need to be
made to support the range of dates which timestamps support.
Gavin
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] statistics about tamp tables ...
On Wed, Nov 26, 2003 at 05:34:28PM +0100, Hans-Jürgen Schönig wrote: > The reason why I came up with this posting is slightly different: Assume > a JDBC application which works with PostgreSQL + some other database. If > you want to use both databases without PostgreSQL being unnecessarily > slow an implicit mechanism would be better. Because otherwise you will > have an SQL command in there which is off standard - putting a switch > into the application seems to be a fairly ugly solution. That's why you delegate the job to something else, like pg_autovacuum or cron ... -- Alvaro Herrera () "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Change in behaviour of ORDER BY clause in PG7.3
Ollie Clark writes: > Since upgrading to 7.3 I've noticed that the ORDER BY clause seems to > ignore leading spaces/punctuation for eg we have a simple table with 6 > rows containing: You need to set the right locale. You appear to expect the "C" locale, so you need to tell initdb about it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] about explain analyze
did you happen to take a look at the other set of queries I sent you, that were showing 39k and 41k explain results? do they show pretty much the same 'lag' in gettimeofday()? On Fri, 28 Nov 2003, Tom Lane wrote: > > ... if its just me mis-reading the numbers, let me > > know ... it just "feels" off > > Here's what I see: > > > time psql -c "explain analyze SELECT count(rec_id) FROM url" 186_archives > QUERY PLAN > > Aggregate (cost=11177.19..11177.19 rows=1 width=4) (actual time=3539.180..3539.184 > rows=1 loops=1) >-> Seq Scan on url (cost=0.00..10741.55 rows=174255 width=4) (actual > time=0.111..2292.234 rows=215552 loops=1) > Total runtime: 3539.922 ms > (3 rows) > > 0.006u 0.000s 0:03.57 0.0% 0+0k 0+0io 0pf+0w > > So there doesn't seem to be any discrepancy between what EXPLAIN says > and what time(1) says. It is true that the overhead of EXPLAIN ANALYZE > looks a bit high: > > > time psql -c "SELECT count(rec_id) FROM url" 186_archives > count > > 215169 > (1 row) > > 0.000u 0.004s 0:00.77 0.0% 0+0k 0+0io 0pf+0w > > If I'm doing the arithmetic correctly this makes the measurement > overhead about 13 microseconds per row. Presumably that's almost > entirely spent on the two gettimeofday() calls, so they are costing > upwards of 6 microseconds apiece, which seems like a lot for a modern > processor. Might be worth griping to the BSD kernel folk... > > Still though I think we could be proud that we've gotten the price of > a seqscan down to the point where a couple of gettimeofday() calls per > row are dominating the runtime. > > regards, tom lane > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] background writer, WAL and snapshot backups
Paul Tuckfield wrote: > I really like the idea of taking a snapshot backup with postgres, using > either volume manager or hardware splits in a disk array to get a > physical backup. > > In other, lesser database systems :) the system is structured to prevent > problems arising from "split block" writes, meaning that though the > database is writing an 8k block at the instant of the split, the > intervening drivers may have written a partial block. two ways to > avoid this problem, as I understand, are: > > 1.) freeze all block writes briefly (excluding WAL perhaps). > Q:) I saw a patch going out to do writes in a background process. is > this only to help write blocks before absolutely necessary, or is it to > do *all* writes? if so, this would make adding freeze logic simple > > 2.) enable logging of entire blocks to the WAL, so recovery can > overwrite any partial blocks caught writing at the instant of split. > Q.) Does the WAL write tuple-level changes or block-level changes. We write entire 8k pages to WAL on first modify so we can recover from partial 8k writes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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/faqs/FAQ.html
Re: [HACKERS] about explain analyze
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > did you happen to take a look at the other set of queries I sent you, that > were showing 39k and 41k explain results? You mean this one? > time psql -c "explain analyze select max(intag) from ndict3" 186_archives QUERY PLAN Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual time=48953.823..48953.827 rows=1 loops=1) -> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual time=4.903..-666785.605 rows=3516680 loops=1) Total runtime: 48982.514 ms (3 rows) 0.000u 0.005s 0:49.06 0.0% 0+0k 0+0io 0pf+0w > time psql -c "select max(intag) from ndict3" 186_archives max 2147418368 (1 row) 0.000u 0.005s 0:03.06 0.0% 0+0k 0+0io 0pf+0w This looks like 46 seconds of overhead for 3516680 rows, or still right about 13 microseconds per row, so that's consistent. The negative "actual time" measurement for the Seq Scan row seems pretty broken though :-(. Now that I recall, didn't you complain of something similar with a beta? 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
Re: [HACKERS] about explain analyze
Tom, I am afraid that I don't understand. My first example: wow=# explain analyze select max(click.accesses) from click; QUERY PLAN - Aggregate (cost=1103.70..1103.70 rows=1 width=8) (actual time=289.391..289.393 rows=1 loops=1) -> Seq Scan on click (cost=0.00..971.36 rows=52936 width=8) (actual time=0.013..133.943 rows=52936 loops=1) Total runtime: 289.498 ms (3 rows) Time: 290,695 ms select max() returns only one row... If you mean rows in Seq Scan than gettimeofday was called (52936+1)*2. Huge value :( Tom Lane wrote: Teodor Sigaev <[EMAIL PROTECTED]> writes: How many times is gettimeofday called? Twice per plan node visit, if you are doing EXPLAIN ANALYZE. (The number of "visits" is one more than the number of rows returned.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(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] about explain analyze
On Fri, 28 Nov 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > did you happen to take a look at the other set of queries I sent you, that > > were showing 39k and 41k explain results? > > You mean this one? Yup ... > > time psql -c "explain analyze select max(intag) from ndict3" 186_archives > QUERY PLAN > > Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual > time=48953.823..48953.827 rows=1 loops=1) >-> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual > time=4.903..-666785.605 rows=3516680 loops=1) > Total runtime: 48982.514 ms > (3 rows) > > 0.000u 0.005s 0:49.06 0.0% 0+0k 0+0io 0pf+0w > > time psql -c "select max(intag) from ndict3" 186_archives > max > > 2147418368 > (1 row) > > 0.000u 0.005s 0:03.06 0.0% 0+0k 0+0io 0pf+0w > > This looks like 46 seconds of overhead for 3516680 rows, or still right > about 13 microseconds per row, so that's consistent. The negative > "actual time" measurement for the Seq Scan row seems pretty broken > though :-(. > > Now that I recall, didn't you complain of something similar with a beta? Yup ... and I bet its not reproducible yet again, is it? :) That would make for twice though, with v7.4, that I've come up with - results :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] about explain analyze
Teodor Sigaev <[EMAIL PROTECTED]> writes: > Tom, I am afraid that I don't understand. My first example: > wow=# explain analyze select max(click.accesses) from click; > QUERY PLAN > - > Aggregate (cost=1103.70..1103.70 rows=1 width=8) (actual > time=289.391..289.393 rows=1 loops=1) > -> Seq Scan on click (cost=0.00..971.36 rows=52936 width=8) (actual > time=0.013..133.943 rows=52936 loops=1) > Total runtime: 289.498 ms > (3 rows) > Time: 290,695 ms > select max() returns only one row... If you mean rows in Seq Scan than > gettimeofday was called (52936+1)*2. Huge value :( Right, that many times at the Seq Scan plan node, and (1+1)*2 more times at the Aggregate node. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] about explain analyze
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > On Fri, 28 Nov 2003, Tom Lane wrote: >> Now that I recall, didn't you complain of something similar with a beta? > Yup ... and I bet its not reproducible yet again, is it? :) That would > make for twice though, with v7.4, that I've come up with - results :) Well, it's not reproducibly negative, but it seems reproducibly wrong: Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual time=49641.603..49641.611 rows=1 loops=1) -> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual time=34.854..724754.474 rows=3570252 loops=1) Total runtime: 49688.524 ms Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual time=36625.013..36625.018 rows=1 loops=1) -> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual time=0.128..-676113.173 rows=3572298 loops=1) Total runtime: 36625.779 ms Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual time=41380.881..41380.885 rows=1 loops=1) -> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual time=0.091..718200.092 rows=3575264 loops=1) Total runtime: 41381.504 ms (3 rows) I'm wondering if there's an actual bug in gettimeofday() in this platform, such that once in a while it returns a value that's off a minute or so ... 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
Re: [HACKERS] $Id$ -> $PostgreSQL$ Change
On Thu, 27 Nov 2003, Rod Taylor wrote: > On Thu, 2003-11-27 at 00:50, Marc G. Fournier wrote: > > Based on discussions on -hackers, and baring any objections betwen now and > > then, I'm going to go through all files in CVS and change: > > > > $Id$ -> $PostgreSQL$ > > > > I will do this the evening of Friday, November 29th ... > > I presume you will be committing the change to the CVSROOT/options file > somewhat before hand? k, there was no options file already, so I just added it containing the one line ... And tested in on GNUMakefile.in, and appears okay: # # $PostgreSQL: pgsql-server/GNUmakefile.in,v 1.36 2003/11/28 20:32:09 pgsql Exp $ # Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] gettimeofday() goes backwards on FreeBSD 4.9
I said:
> I'm wondering if there's an actual bug in gettimeofday() in this
> platform, such that once in a while it returns a value that's off
> a minute or so ...
Whoa. Try the following test program. Then send it in to your friendly
local BSD hackers
> uname -a
FreeBSD pgsql74.hub.org 4.9-PRERELEASE FreeBSD 4.9-PRERELEASE #3: Sat Sep 20 14:16:48
ADT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/kernel i386
> ./a.out
out of order tv_sec: 1070050646 939970, prev 1070051342 516165
out of order tv_sec: 1070051342 789127, prev 1070050646 939970
out of order tv_usec: 1070051342 789127, prev 1070050646 939970
out of order tv_sec: 1070050654 690255, prev 1070051350 216448
out of order tv_sec: 1070051350 402412, prev 1070050654 690255
out of order tv_usec: 1070051350 402412, prev 1070050654 690255
out of order tv_sec: 1070050672 990359, prev 1070051368 416546
out of order tv_sec: 1070051368 501648, prev 1070050672 990359
out of order tv_usec: 1070051368 501648, prev 1070050672 990359
out of order tv_sec: 1070050672 990363, prev 1070051368 516557
out of order tv_sec: 1070051368 700227, prev 1070050672 990363
out of order tv_usec: 1070051368 700227, prev 1070050672 990363
out of order tv_sec: 1070050675 740524, prev 1070051371 216718
out of order tv_sec: 1070051371 346521, prev 1070050675 740524
out of order tv_usec: 1070051371 346521, prev 1070050675 740524
out of order tv_sec: 1070050676 440415, prev 1070051371 916605
out of order tv_usec: 1070050676 440415, prev 1070051371 916605
out of order tv_sec: 1070051372 238348, prev 1070050676 440415
out of order tv_usec: 1070051372 238348, prev 1070050676 440415
^C
>
regards, tom lane
#include
#include
int
main()
{
struct timeval prevtime;
struct timeval curtime;
gettimeofday(&prevtime, NULL);
for (;;)
{
gettimeofday(&curtime, NULL);
if (curtime.tv_usec < 0 || curtime.tv_usec >= 100)
fprintf(stderr, "bogus tv_usec: %ld %ld, prev %ld %ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
if (curtime.tv_sec < prevtime.tv_sec ||
curtime.tv_sec > prevtime.tv_sec + 1)
fprintf(stderr, "out of order tv_sec: %ld %ld, prev %ld %ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
if (curtime.tv_usec < prevtime.tv_usec &&
curtime.tv_sec != prevtime.tv_sec + 1)
fprintf(stderr, "out of order tv_usec: %ld %ld, prev %ld
%ld\n",
(long int) curtime.tv_sec,
(long int) curtime.tv_usec,
(long int) prevtime.tv_sec,
(long int) prevtime.tv_usec);
prevtime = curtime;
}
return 0;
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] about explain analyze
None that I've heard of ... Sean? Tom, any way of writing a quite test C program for this? Something to 'simulate' the same thing, but without having to build the whole postmaster? On Fri, 28 Nov 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > On Fri, 28 Nov 2003, Tom Lane wrote: > >> Now that I recall, didn't you complain of something similar with a beta? > > > Yup ... and I bet its not reproducible yet again, is it? :) That would > > make for twice though, with v7.4, that I've come up with - results :) > > Well, it's not reproducibly negative, but it seems reproducibly wrong: > > Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual > time=49641.603..49641.611 rows=1 loops=1) >-> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual > time=34.854..724754.474 rows=3570252 loops=1) > Total runtime: 49688.524 ms > > Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual > time=36625.013..36625.018 rows=1 loops=1) >-> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual > time=0.128..-676113.173 rows=3572298 loops=1) > Total runtime: 36625.779 ms > > Aggregate (cost=40168.96..40168.96 rows=1 width=4) (actual > time=41380.881..41380.885 rows=1 loops=1) >-> Seq Scan on ndict3 (cost=0.00..34560.57 rows=2243357 width=4) (actual > time=0.091..718200.092 rows=3575264 loops=1) > Total runtime: 41381.504 ms > (3 rows) > > I'm wondering if there's an actual bug in gettimeofday() in this > platform, such that once in a while it returns a value that's off > a minute or so ... > > regards, tom lane > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] $Id$ -> $PostgreSQL$ Change
> k, there was no options file already, so I just added it containing the > one line ... > > And tested in on GNUMakefile.in, and appears okay: > > # > # $PostgreSQL: pgsql-server/GNUmakefile.in,v 1.36 2003/11/28 20:32:09 pgsql Exp $ > # Looks good. Once you are comfortable with the changes, you will need to add another line to CVSROOT/options to disable processing of the $Id$ and $Header$ tags should one accidentally be added. tagexpand=iPostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] $Id$ -> $PostgreSQL$ Change
On Fri, 28 Nov 2003, Rod Taylor wrote: > > k, there was no options file already, so I just added it containing the > > one line ... > > > > And tested in on GNUMakefile.in, and appears okay: > > > > # > > # $PostgreSQL: pgsql-server/GNUmakefile.in,v 1.36 2003/11/28 20:32:09 pgsql Exp $ > > # > > Looks good. Once you are comfortable with the changes, you will need to > add another line to CVSROOT/options to disable processing of the $Id$ > and $Header$ tags should one accidentally be added. > > tagexpand=iPostgreSQL 'K, where do you find docs on the options file? there is nothing on it, that I can find, in 'info cvs'? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] $Id$ -> $PostgreSQL$ Change
> > Looks good. Once you are comfortable with the changes, you will need to > > add another line to CVSROOT/options to disable processing of the $Id$ > > and $Header$ tags should one accidentally be added. > > > > tagexpand=iPostgreSQL > > 'K, where do you find docs on the options file? there is nothing on it, > that I can find, in 'info cvs'? Excellent question. I don't know the answer to that. I followed the commit logs for the FreeBSD camp to find out what to do. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Change in behaviour of ORDER BY clause in PG7.3
Since upgrading to 7.3 I've noticed that the ORDER BY clause seems to ignore leading spaces/punctuation for eg we have a simple table with 6 rows containing: 'a' 'b' 'c' 'd' ' bill' '-bill' SELECT * FROM table ORDER BY 1; gives: a b bill -bill c d which is different from previous versions and different to MySQL and SQL Server. Is this a bug, or can you turn this behaviour off? It's breaking a lot of our applications! -- Ollie Clark-[EMAIL PROTECTED]-[EMAIL PROTECTED] http://www.ollieclark.com/acronyms.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PANIC: rename from /data/pg_xlog/0000002200000009
> I get the feeling that what we will see is the destination
> filename already present and the source not, which would suggest
> that two backends tried to do the rename concurrently.
Tom,
I just noticed that the rename panic errors like this one:
PANIC: rename from /data/pg_xlog/0003001F to
/data/pg_xlog/0003002C (initialization of log file 3, segment 44)
failed: No such file or directory
come shortly AFTER the following messages
LOG: recycled transaction log file 0003001B
LOG: recycled transaction log file 0003001C
LOG: recycled transaction log file 0003001D
LOG: recycled transaction log file 0003001E
LOG: removing transaction log file 0003001F
LOG: removing transaction log file 00030020
LOG: removing transaction log file 00030021
LOG: removing transaction log file 00030022
So, you can see that 0003001F file was previously deleted by the
logic in MoveOfflineLogs() function.
Now what I can see is that MoveOfflineLogs() does not seem to be
synchronized between backends.
The xlog directory reading loop is not synchronized itself and the caller
code is not synchronized either:
CreateCheckPoint(bool shutdown, bool force)
...
LWLockRelease(ControlFileLock);
/*
* We are now done with critical updates; no need for system panic if
* we have trouble while fooling with offline log segments.
*/
END_CRIT_SECTION();
/*
* Delete offline log files (those no longer needed even for previous
* checkpoint).
*/
if (_logId || _logSeg)
{
PrevLogSeg(_logId, _logSeg);
MoveOfflineLogs(_logId, _logSeg, recptr);
}
...
So is it possible that due to the lack of synchronization, two backends call
MoveOfflineLogs() simultaneously?
For example, first backend has unlinked the log segment file and then the
second one tries to rename the same file because it was returned by
readdir() function before it got deleted by the first beckend.
However, scenario seems to be hard to materialize since it must happen in a
very short timeframe.
The "remove" and "rename" log messages look separated in time.
Also, we have a suspicion that the problem happens even with only one client
connected to postgres.
Thanks
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] statistics about tamp tables ...
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: Recently I have come across a simple issue which made me think about it. When we create a tmp table (SELECT INTO, CREATE TABLE AS) the planner won't know anything about its content after creating it. Run ANALYZE on the temp table, if you intend to use it enough to justify gathering stats about it. VACUUM is more work than needed. regards, tom lane Of course, VACUUM is on overkill (there is no use to shrink something minimal ;) ). The reason why I came up with this posting is slightly different: Assume a JDBC application which works with PostgreSQL + some other database. If you want to use both databases without PostgreSQL being unnecessarily slow an implicit mechanism would be better. Because otherwise you will have an SQL command in there which is off standard - putting a switch into the application seems to be a fairly ugly solution. regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Copyright (C) 1996-2002
>> Today I've d-loaded PostgreSQL 7.3.4. >> I've seen in >> $PGSQLD/doc/html/index.html >> it still says >> Copyright (C) 1996-2002 >> shouldn't it be 2003? > > We only update the copyright notices when we are preparing a major > release. (Bruce just did it a week or two back for 7.4, for example.) > Updating for minor releases would create a lot of churn in the stable > CVS branches, for little purpose. I'm curious, has anyone consulted with a lawyer on this? (We wouldn't want SCO to sue PostgreSQL for this! =D Heheh.) -- Randolf Richardson - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(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] PostgreSQL on Novell Netware 6.5.
>> > They stopped at 7.2.4 because "they're finishing some usefull APIs, >> > which'll make the port much more "easy"." >> >> Will this involve using a Linux kernel ;) > > :) No, a NW kernel with a POSIX library. This'll be great, because > :you'll can run powerfull opensource software with an enterprise-class > NOS. Another option... Well, the "pthreads" seem to be working just fine. For all my work that most people would do in C/C++, I do in assembler, and the POSIX support in NetWare now is providing some very nice APIs in NetWare 6.0 and 6.5 that simply weren't available on NetWare 4.x and earlier (NetWare 5.x has some of this, and Novell has been working hard to move in this direction). > Clustering support (32 x 32), stable & consistent file system (Novell's > Storage System, forget the old Tradition File System (very mature & > stable, but it doesn't fit the current storage necessity)), security & > stability (our servers is counting 700+ days). I hosted over 500 internet sites (HTTP (Apache), DNS, SMTP/POP3, FTP, NNTP, Oracle, various Java services, PERL, etc.) on NetWare 5 across two different /24 address spaces, and the server (yes, all of this was done with a single Pentium III server with 512 MBs of RAM) was fast and reliable with minimal maintenance. The new file system (NSS) cuts down the boot time since volume mounts always complete in less than 1 second as opposed to the "Traditional File System" (TFS) which required approximately 5 to 10 minutes to mount. NSS is a journaling system, which I've not been able to break yet by simulating power outages during busy times. When the next release of PostgreSQL is ready for NetWare, I'm going to try to break it in this way as well (by making it very busy with many simultaneous write, index, and vacuum operations before "pulling the plug"). So far I've found that Oracle 8i on NetWare was quite good at handling this and the worst that ever happened (at least in my testing) was that there was a cut-off point where newly written records were lost. I'm not pleased with Oracle's decision to discontinue support for NetWare, and PostgreSQL fits that void very well (except for not having table spaces, but I can live with that), and so I anxiously await the new version of PostgreSQL so that I can proceed with some rather large web- based projects that have been in the planning and research stages for approximately 3 years now. Unfortunately I simply can't use MySQL because they don't have views, and there are some data integrity problems (e.g., numbers getting truncated silently instead of an error being returned, etc.). > It's a valuable addition to the Novell community, but it's also an good > addition to the PostgreSQL community, but of course, when everything is > 100% done. ;) I agree wholeheartedly that both the Novell and PostgreSQL user and developer communities will benefit greatly from this. From a marketing perspective, both organizations will also benefit by gaining additional exposure. -- Randolf Richardson - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Date bug in PG
Title: Message Hi We are attempting to setup a database application on a Linux box, but we are having problems loading data with particular dates. My colleague, Arnold Mavromatis has already made postings to the pgsql-bugs list, but we are still no closer to having the bug fixed, so I thought I'd email you guys in the hope we might get the ball rolling on this issue. We have tested the following configurations: Linux 2.4.20, PG 7.3.2 (this is the box where we wish to run our application) HP-UX 11.11, PG 7.3.2, 7.3.4, 7.4 (this is just a test box where PG installs take less time) On all of these configurations the the date '1901/12/14' apparently does not exist. For example, using PG 7.4 on HP-UX 11.11: shower 40: /adamdb/postgres/pg_admin/pgsql_7.4/bin/psql -d db74 -U tczPassword: Welcome to psql 7.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit db74=> select cast(timestamptz '1901/12/14' as date); date 1901-12-13(1 row) db74=> select cast(timestamptz '1901/12/13' as date); date 1901-12-13(1 row) db74=> select cast(timestamptz '1901/12/15' as date); date 1901-12-15(1 row) db74=> \qshower 41: I can give you other examples, but these have already been posted to the pgsql-bugs list by Arnold. We understand that this problem is related to the timezone and only affects users in our part of the world (Melbourne, Australia). Is this correct? If you could shed any light on this and give us some indication of whether or not this big can be fixed, and/or when it can be fixed it would be of great interest to us. Thanks, Mike Macaskill Mike Macaskill NCC Computing Support Tel 9669 4265 Fax 9669 4760
Re: [HACKERS] Materialized views proposal
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 November 2003 09:19, Hannu Krosing wrote: > > First, You could start by implementing materialized views manually, > using tables and triggers, to get the feel of what should be generated. > > Next, still working from frontend, try to make some code (probably in > some RAD/scripring language) which does the transform from CREATE VIEW > (or whatever) syntax (or manually constructed syntax tree) into create > table + create trigger statements. > > If you have the above working well, talk about moving this to backend. > Thanks for the suggestion. I will definitely do that. > What is needed is good algorithms. Writing C code is secondary to that. > > Similar problem has kept us from implementing updatable views for quite > some time. > You are definitely correct. - -- Jonathan M. Gardner Web Developer, Amazon.com [EMAIL PROTECTED] - (206) 266-2906 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/xPfOBFeYcclU5Q0RAhgfAKDAYmm67Jc5n7WwyUDtn5IJOhhrXwCfcRud EAR9U63FEqlHbGrdzKxGmdw= =a6RK -END PGP SIGNATURE- ---(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] [PERFORM] More detail on settings for pgavd?
On Fri, 21 Nov 2003, Matthew T. O'Connor wrote: > >> Do you know of an easy way to get a > >>count of the total pages used by a whole cluster? > > > >Select sum(relpages) from pg_class. You might want to exclude indexes from this calculation. Some large read only tables might have indexes larger than the tables themselves. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] mechanism used to store images in Postgres
We're looking at storing the equivalent of very large rasters images in postgres. Our understanding is that postgres stores large objects as little pieces in rows of a table. As our images would be 30MB-4GB in size, this would be a lot of rows. Has anybody used a different mechanism to store their data that offers higher performance? Thank you, Eric. ---(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] Handy user/group hack
Here's a slightly condensed version - do SQL functions have an advantage in that they can be inlined? Or have I misunderstood? CREATE OR REPLACE FUNCTION user_in_group(name,name) RETURNS boolean STRICT AS ' SELECT EXISTS(SELECT u.* FROM pg_catalog.pg_user u INNER JOIN pg_catalog.pg_group g ON (u.usesysid = ANY(g.grolist)) WHERE u.usename = $1 AND g.groname = $2); ' LANGUAGE 'SQL'; "David Fetter" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Kind people, > > Here's something I came up with for finding whether a PostgreSQL 7.4 > user is in a group. > > Cheers, > D > > CREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS ' > DECLARE > the_user ALIAS FOR $1; > the_group ALIAS FOR $2; > dummy text; -- SELECT INTO dummy because PERFORM always returns true. > -- Is this a bug? > BEGIN > SELECT INTO dummy u.usename > FROM > pg_user u > , pg_group g > WHERE > u.usename = the_user > AND g.groname = the_group > AND u.usesysid = ANY (g.grolist); > > IF FOUND > THEN > RETURN true; > ELSE > RETURN false; > END IF; > END; > ' LANGUAGE 'plpgsql' STRICT IMMUTABLE; > -- > David Fetter [EMAIL PROTECTED] http://fetter.org/ > phone: +1 510 893 6100cell: +1 415 235 3778 > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Anyone working on pg_dump dependency ordering?
[EMAIL PROTECTED] (Christopher Kings-Lynne) wrote in message news:<[EMAIL PROTECTED]>... > Lastly, I presume it's possible to create a system of circular > dependencies (eg create or replace view), which really cannot be solved > without a system of 'shells', similar to that needed to dump types and > their i/o functions. > > Views seem to be by far the nastiest object. They can be dependent on > almost everything in the database. Hello Group, It might be an idea to keep track of all data-definition changes during the lifetime of a database. Keep all the SQL-definition-commands in a seperate systemtable somewhere. Then, when the schema is dumped you'd have the choice of dumping: - the most recent schema with database-determined ordering (as is the case in the current situation) - "replay" the "recorded" datadefinition of the past, so you know the data definition is executed in a "sound" sequence. Of course, data itself would be extracted seperate of the definitions and there would also be the need to remove constrains while the data is being loaded. This last thing could be done by parsing the "recorded" data-definition-commands or by first blindly running the "recorded" commands, then removing (or disabling) any constraints that are there and finally replacing (or re-enabling) the constraints. Negative side of this idea is obviously that you need to make changes to a lot of internals of the postgresql database. AND you need to make perfectly sure that the state of the database is always consistent with the state of the recorded SQL. Good Luck, Arian. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER COLUMN/logical column position
We had this discussion. information_schema doesn't deliver enough info needed for admin tools. It should. This is the sole reason for existance of it. If it is not enough, then it should be updated. It can't. ANSI says only objects owned by the user are shown. Admins might be quite unhappy about that... pg_catalog views don't help either, just look at pg_tables. It doesn't even have the oid, how should a table be identified uniquely? The system views are unusable for hardcore admin purposes, until they include *. So I'd rather use the tables directly. Usually, nobody should care about the column ordering, but for those unfortunate guys that rely on a specific SELECT * ordering the list of columns displayed in admin tools must show that ordering; this is what current admin tools expect from attnum. No SQL user would ever care about internal storage details/pointers/counters, so any admin tool would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum END This won't work anyway if table is missing column attpos . Sorry to be not precise enough, this was meant as meta code. Of course the query must be built version dependent. Still there were several predictions of all admin tools breaking as a result of gaps. wasn' me. Can you name one PG version change from A.N to A.M where old admin tools have not needed any changes ? Older tools usually continue to work, they just don't know new features. Maybe some esotheric features break, so few people notice. attnum interpretation of pgAdmin3 is already wrong - it claims it to be Position even when some previous columns are dropped. So you can have a table which has 1 column with "Position" 3 ;) attnum isn't interpreted at all in pgAdmin3, only used for ordering. It can't be used as key to a column any more, if altering a column would create a new attnum with old name (and old attpos). In this sense, the key is attrelid/attpos, because only these don't change for an ALTER COLUMN statement. Imagine a sql update to a non-pk column would change the pk of the row, so you'd have to reread the row by its content to obtain the new pk value. This wouldn't make you happy, right? Same here. IMHO, The only behaviour visible to common user we should worry about is SELECT * , and a special column for solving this is _the_ easiest way to do it. Surely this is the easiest way. But it has the biggest impact on clients too. Not all clients - just admin tools. And we dont have that many admin tools. And IMNSHO admin tools should move to using information_schema as much as possible. And if information_schema is inadequate then fix it instead of bypassing it. It is unfixable. Being ANSI-conformant, it prevents the admin seeing everything. I think keeping know-how about retrieving postgresql structure inside of an application instead of making it readily available in information_schema is either a) an egoistic attemt of shutting out competition b) laziness or c) both It is also bad design, as it ties ones tool to backend structure too tightly. Backend structure will keep changing and the last thing we want to hold it back is some frontend tool which thinks it knows better how to organize data in backend. What if some completely new storage is added to postgreSQL (ancient Postgres versions had built-in support for several storages). Should all frontend tools (including ?DBC drivers) need updating or just information_schema ? Again, I'm not against using information_schema. I tried to use it (for non-admin tool purposes!) and failed badly. pgAdmin3 is a tool for *all* PostgreSQL features, not just for some common ANSI stuff. I'm just imagining what would happen to pgAdmin3. The column number would have to display attpos (this is what the user is interested in to see the ordering), No they are interested in position as you mentioned above, they didn't want to see attnum (i.e 1,2,3,5,6,8 in your example) before either. I think it is a bug that pgAdmin3 shows attnum instead the real position. It shows what's in the db, to identify the column. I can't imagine why I should show a column number, programmers should address by column name and nothing else. Sorry, I must have missed it ;( could you give a link to archived copy. About a week ago, when the message you answered on was posted. Took a week now to appear... But I think that nobody objected, but nobody didn't volunteer to do the work either ;) At least that was the impression i got from an answer to my similar question on growing varchars and dropping isnull's without forcing column copies and constraint checks. Yeah, interesting. For my observations, these trivial changes make 90-95 % of daily column change work, that's why I implemented it in pgAdmin3 (targeting the system tables directly...), so it's worth the effort handling them separately. I might add it some time to the backend (as an additional code p
Re: [HACKERS] ALTER COLUMN/logical column position
Andreas Pflug kirjutas N, 20.11.2003 kell 16:10: > Hannu Krosing wrote: > > > > > > >You are just shifting the interface problems to a place needing way more > >changes in the backend. There will be some problems either way. > > > > > Not quite. Certainly, basing internal storage on attstoragenum is more > work in the backend, but less (precisely: zero) work on an unknown > number of frontend tools and apps. With stress on "unknown number" ;) > >also, tools needing knowledge should start using information schema as > >much as they can, making internal reshufflings less of a problem. > > > > > We had this discussion. information_schema doesn't deliver enough info > needed for admin tools. It should. This is the sole reason for existance of it. If it is not enough, then it should be updated. Updating information_schema would also make developers of other admin tools happy. Remember - competition is good ;) Compatibility with old verions of admin tools wont happen anyway, so we should not let that lock backend into bad development decisions. Look what happened to DOS-WIN16-WIN32. > >>This way, the "user interface" doesn't change, and all those > >>"SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the > >>expected result. > >> > >> > > > >Depending on what you expect ;) > > > > > Usually, nobody should care about the column ordering, but for those > unfortunate guys that rely on a specific SELECT * ordering the list of > columns displayed in admin tools must show that ordering; this is what > current admin tools expect from attnum. No SQL user would ever care > about internal storage details/pointers/counters, so any admin tool > would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum > END This won't work anyway if table is missing column attpos . You have to have different queries for different versions. Add it is preferential to keep these different queries in information_schema of corresponding databases not all in frontend tool. You still need different queries for old databases which did not support schemas. > (and the unique key to pg_attribute, as seen from the tool, changes > from refoid/attnum to refoid/attindex too). the key needs no change, just the ORDER BY clause. > >If you expect the above to give you all active columns as orderd as they > >are stored, then it does not give you what you expect. > > > >Btw, most of these concerns (and more) were already iterated when DROP > >column was done causing gaps in attnum. There were a lot of doomsday > >profecies, but in the end it went quite smoothly. > > > I don't bother about missing attnum values, even 1,2,3,5,6,8 is nicely > ordered. Still there were several predictions of all admin tools breaking as a result of gaps. > > The tools needing > >internal knowledge about storage (meaning any tool doing select .. from > >pg_...) have always needed some upgrades for new verions. > > > > > Yes, but changes to pg_... should retain the usual meanings as much as > possible, so older tools continue to work. Can you name one PG version change from A.N to A.M where old admin tools have not needed any changes ? > The discussed change is > problematic because old tools *seem* to work ok, but their attnum > interpretation would be wrong. attnum interpretation of pgAdmin3 is already wrong - it claims it to be Position even when some previous columns are dropped. So you can have a table which has 1 column with "Position" 3 ;) > >IMHO, The only behaviour visible to common user we should worry about is > >SELECT * , and a special column for solving this is _the_ easiest way to > >do it. > > > Surely this is the easiest way. But it has the biggest impact on clients > too. Not all clients - just admin tools. And we dont have that many admin tools. And IMNSHO admin tools should move to using information_schema as much as possible. And if information_schema is inadequate then fix it instead of bypassing it. I think keeping know-how about retrieving postgresql structure inside of an application instead of making it readily available in information_schema is either a) an egoistic attemt of shutting out competition b) laziness or c) both It is also bad design, as it ties ones tool to backend structure too tightly. Backend structure will keep changing and the last thing we want to hold it back is some frontend tool which thinks it knows better how to organize data in backend. What if some completely new storage is added to postgreSQL (ancient Postgres versions had built-in support for several storages). Should all frontend tools (including ?DBC drivers) need updating or just information_schema ? > I'm just imagining what would happen to pgAdmin3. The column number > would have to display attpos (this is what the user is interested in to > see the ordering), No they are interested in position as you mentioned above, they didn't want to see attnum (i.e 1,2,3,5,6,8 in your example) bef
Re: [HACKERS] Build farm
Just a thought. You could also run the regression test automatically after a successful build? "Andrew Dunstan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > > Jean-Michel POURE wrote: > > >Le Vendredi 21 Novembre 2003 19:47, Tom Lane a écrit : > > > > > >>I think the main value of a build farm is that we'd get nearly immediate > >>feedback about the majority of simple porting problems. Your previous > >>arguments that it wouldn't smoke everything out are certainly valid --- > >>but we wouldn't abandon the regression tests just because they don't > >>find everything. Immediate feedback is good because a patch can be > >>fixed while it's still fresh in the author's mind. > >> > >> > > > >Dear friends, > > > >We have a small build farm for pgAdmin covering Win32, FreeBSD and most GNU/ > >Linux systems. See http://www.pgadmin.org/pgadmin3/download.php#snapshots > > > >The advantage are immediate feedback and correction of problems. Also, in a > >release cycle, developers and translators are quite motivated to see their > >work published fast. > > > >Of course, it is always hard to "mesure" the real impact of a build farm. My > >opinion it that it is quite positive, as it helps tighten the links between > >people, which is free software is mostly about. > > > > > > > > Right. But I think we have been talking about using the build farm to do > test builds rather than to provide snapshots. I'd be very wary of > providing arbitrary snapshots of postgres, whereas I'd be prepared to > try a snapshot of pgadmin3 under certain circumstances. (Also, building > your own snapshot of postgres is somewhat easier than building your own > snapshot of pgadmin3). > > cheers > > andrew > > > ---(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 > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ObjectWeb/Clustered JDBC
Dave, I know that the backend does - it is an essential feature. Clustered JDBC parses the statement sent to it in order to find out what to do with it. I have played around a little (mostly interactive shell). You will find out that Clustered JDBC will complain in this case because it doesn't know what to do with it. If you are a tool support load balancing and this kind of stuff DECLARE CURSOR can be painful to implement - especially across multiple transactions. Is is a very weak point of the current beta version. Regards, Hans Dave Cramer wrote: Hans, I don't understand the statement about missing "DECLARE CURSOR" ? The backend supports it? Dave On Sun, 2003-11-23 at 12:12, Hans-Jürgen Schönig wrote: Peter Eisentraut wrote: I was at the ObjectWeb Conference today; ObjectWeb (http://www.objectweb.org) being a consortium that has amassed quite an impressive array of open-source, Java-based middleware under their umbrella, including for instance our old friend Enhydra. And they regularly kept mentioning PostgreSQL in their presentations. To those that are interested in distributed transactions/two-phase commit, I recommend taking a look at Clustered JDBC (http://c-jdbc.objectweb.org/). While this is not exactly the same thing, it looks to be a pretty neat solution for a similar class of applications. In particular, it provides redundancy, load balancing, caching, and even database independence. It is indeed a nice solution but it is far from ready yet. Especially the disaster recovery mechanism and things such as adding new masters need some more work. What I really miss is "DECLARE CURSOR". Maybe it will be in there some day :). However, we have done some real testing with sync replication (4 x pg, 1 x oracle). It performed surprisingly well (the JDBC part, not the Oracle one ;) ). Maybe this will be something really useful within the next few months. Cheers, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] background writer, WAL and snapshot backups
I really like the idea of taking a snapshot backup with postgres, using either volume manager or hardware splits in a disk array to get a physical backup. In other, lesser database systems :) the system is structured to prevent problems arising from "split block" writes, meaning that though the database is writing an 8k block at the instant of the split, the intervening drivers may have written a partial block. two ways to avoid this problem, as I understand, are: 1.) freeze all block writes briefly (excluding WAL perhaps). Q:) I saw a patch going out to do writes in a background process. is this only to help write blocks before absolutely necessary, or is it to do *all* writes? if so, this would make adding freeze logic simple 2.) enable logging of entire blocks to the WAL, so recovery can overwrite any partial blocks caught writing at the instant of split. Q.) Does the WAL write tuple-level changes or block-level changes. I've been gradually browsing the code.(WAL at least) I'm a professional DBA but amateur C programmer, I'm in over my head trying to sort out myself. ---(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] ObjectWeb/Clustered JDBC
Peter Eisentraut wrote: Hans-Jürgen Schönig writes: Especially the disaster recovery mechanism and things such as adding new masters need some more work. Yes, someone is working on automatic recovery (which would extend to adding new masters by starting recovery from zero). In fact, they're just across town from you (together.at). Guess who has taught them PostgreSQL ;). Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] about explain analyze
> ... if its just me mis-reading the numbers, let me > know ... it just "feels" off Here's what I see: > time psql -c "explain analyze SELECT count(rec_id) FROM url" 186_archives QUERY PLAN Aggregate (cost=11177.19..11177.19 rows=1 width=4) (actual time=3539.180..3539.184 rows=1 loops=1) -> Seq Scan on url (cost=0.00..10741.55 rows=174255 width=4) (actual time=0.111..2292.234 rows=215552 loops=1) Total runtime: 3539.922 ms (3 rows) 0.006u 0.000s 0:03.57 0.0% 0+0k 0+0io 0pf+0w So there doesn't seem to be any discrepancy between what EXPLAIN says and what time(1) says. It is true that the overhead of EXPLAIN ANALYZE looks a bit high: > time psql -c "SELECT count(rec_id) FROM url" 186_archives count 215169 (1 row) 0.000u 0.004s 0:00.77 0.0% 0+0k 0+0io 0pf+0w If I'm doing the arithmetic correctly this makes the measurement overhead about 13 microseconds per row. Presumably that's almost entirely spent on the two gettimeofday() calls, so they are costing upwards of 6 microseconds apiece, which seems like a lot for a modern processor. Might be worth griping to the BSD kernel folk... Still though I think we could be proud that we've gotten the price of a seqscan down to the point where a couple of gettimeofday() calls per row are dominating the runtime. 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] Functions with COPY
Stephen Frost <[EMAIL PROTECTED]> writes: > * Tom Lane ([EMAIL PROTECTED]) wrote: >> You can do any amount of processing you want in an INSERT statement, >> though. > Certainly, but for bulk loads that requires more pre-processing work for > the user and I believe results in more work for the server too (it > certainly takes longer...). Have you batched multiple INSERTs into a transaction? Also consider using a prepared statement to eliminate parse/plan overhead. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] about explain analyze
Teodor Sigaev <[EMAIL PROTECTED]> writes: > How many times is gettimeofday called? Twice per plan node visit, if you are doing EXPLAIN ANALYZE. (The number of "visits" is one more than the number of rows returned.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] about explain analyze
How many times is gettimeofday called? wow=# explain analyze select 1; QUERY PLAN Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.010 rows=1 loops=1) Total runtime: 0.047 ms (2 rows) Several thousands? Tom Lane wrote: Teodor Sigaev <[EMAIL PROTECTED]> writes: Explain analyze takes 3 times more time for execution. Why? Measurement overhead. It would seem your platform has a particularly slow version of gettimeofday() though ... I've never noticed such a large discrepancy myself. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Functions with COPY
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > No, I'm interested, as I discussed in my message[1], in the ability to > > use functions in a copy statement to allow me to specify the conversion > > from text to the appropriate data type. > > COPY is not intended to be that flexible; it's intended to be fast. I wouldn't expect much of a speed difference between to_date() and cast(text as date). Is there some reason I'm not seeing to expect it to be much slower? My guess was that supporting this wouldn't involve that much code change either but I'm probably wrong. > You can do any amount of processing you want in an INSERT statement, > though. Certainly, but for bulk loads that requires more pre-processing work for the user and I believe results in more work for the server too (it certainly takes longer...). Stephen signature.asc Description: Digital signature
Re: [HACKERS] about explain analyze
On Fri, 28 Nov 2003, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > Actually, I'm noticing similar "problems" with v7.4 ... "EXPLAIN ANALYZE" > > seems to be showing some *very* high ms for execution time, but if you run > > the actual query, it doesn't seem to take even 1/10th the time reported > > ... > > Example? I don't see anything out of line here. pgsql74# /usr/local/pgsql/bin/psql -U pgsql -c "SELECT count(rec_id) FROM url" 186_archives count 209872 (1 row) pgsql74# /usr/local/pgsql/bin/psql -U pgsql -c "EXPLAIN ANALYZE SELECT max(rec_id) FROM url" 186_archives QUERY PLAN Aggregate (cost=11177.19..11177.19 rows=1 width=4) (actual time=2400.579..2400.585 rows=1 loops=1) -> Seq Scan on url (cost=0.00..10741.55 rows=174255 width=4) (actual time=0.056..1387.803 rows=209872 loops=1) Total runtime: 2407.095 ms (3 rows) pgsql74# time /usr/local/pgsql/bin/psql -U pgsql -c "SELECT max(rec_id) FROM url" 186_archives max 690582 (1 row) 0.000u 0.004s 0:00.32 0.0% 0+0k 0+0io 0pf+0w Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Functions with COPY
Stephen Frost <[EMAIL PROTECTED]> writes:
> No, I'm interested, as I discussed in my message[1], in the ability to
> use functions in a copy statement to allow me to specify the conversion
> from text to the appropriate data type.
COPY is not intended to be that flexible; it's intended to be fast.
You can do any amount of processing you want in an INSERT statement,
though.
INSERT INTO mytable VALUES (mydatefunc('2001/01/03'), ... );
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] -fpic vs. -fPIC
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I've tried building PostgreSQL with -fpic on Sparc and saw no problems. > So I suggest that we change back to -fpic until we get detailed evidence. Okay with me. It never struck me that we'd really seen adequate evidence that -fPIC was needed. Makefile.solaris is the only other place where gcc -fPIC is selected; should it be changed also? regards, tom lane ---(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] Functions with COPY
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > I guess my example was lacking, I'm sure there are cases where the > > text->date casting will end up being wrong or some date style won't be > > accepted. If the above was 'January 10th, 2003, October 1st, 2003, > > January 1st, 2003', for example. Thinking back I think that might have > > been the situation I was thinking about (conflicting mdy and dmy) and > > would have made more sense as an example. > > Then what are you after, some magically prescient input mode that will > guess the correct interpretation? No, I'm interested, as I discussed in my message[1], in the ability to use functions in a copy statement to allow me to specify the conversion from text to the appropriate data type. Right now Postgres is using casting which can end up being wrong. That's not a fault or something that can be fixed, the casting logic itself is fine but it's not always appropriate to apply the same casting to all fields of a given type. It would be nice to be able to choose what function is used and to pass arguments to it. This is a feature request and I'm not suggesting a change in host the text->date casting is done. From a programmatical standpoint I see things like this: Right now: text -> date : cast(text as date) text -> numeric : cast(text as numeric) I want to be able to pop that out and put my own function in place of it, like so: text -> date : to_date(text,'-Mon-DD') text -> numeric : to_numeric(text,'99V99') My other message had an example syntax to do this. I don't know if that'd be the appropriate syntax or not but I thought it illustrated what I was interested in. Thanks, Stephen [1] http://archives.postgresql.org/pgsql-hackers/2003-11/msg01456.php signature.asc Description: Digital signature
Re: [HACKERS] Functions with COPY
Stephen Frost <[EMAIL PROTECTED]> writes: > I guess my example was lacking, I'm sure there are cases where the > text->date casting will end up being wrong or some date style won't be > accepted. If the above was 'January 10th, 2003, October 1st, 2003, > January 1st, 2003', for example. Thinking back I think that might have > been the situation I was thinking about (conflicting mdy and dmy) and > would have made more sense as an example. Then what are you after, some magically prescient input mode that will guess the correct interpretation? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] about explain analyze
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Actually, I'm noticing similar "problems" with v7.4 ... "EXPLAIN ANALYZE" > seems to be showing some *very* high ms for execution time, but if you run > the actual query, it doesn't seem to take even 1/10th the time reported > ... Example? I don't see anything out of line here. 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
Re: [HACKERS] Functions with COPY
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > Consider the following input data: > > 1234,24.50,10-Jan-2003,10/1/03,10-01-2003,hiall > > > The interpretation for the numbers is: > > 1234 =3D 12.34, 24.50 =3D 24.50 > > The interpretation for the dates is: > > January 10th, 2003, October 1st, 2003, October 1st, 2003 > > > I don't believe it's possible, currently, to correctly import this > > data with copy. I'm not sure the date fields would even be accepted > > as date fields. > Nonsense. [...] > I think you'd have to do some preprocessing on the numeric inputs if you > wanted implied decimal points inserted like that, but the dates look fine. I guess my example was lacking, I'm sure there are cases where the text->date casting will end up being wrong or some date style won't be accepted. If the above was 'January 10th, 2003, October 1st, 2003, January 1st, 2003', for example. Thinking back I think that might have been the situation I was thinking about (conflicting mdy and dmy) and would have made more sense as an example. Stephen signature.asc Description: Digital signature
Re: [HACKERS] about explain analyze
On Fri, 28 Nov 2003, Tom Lane wrote: > Teodor Sigaev <[EMAIL PROTECTED]> writes: > > Explain analyze takes 3 times more time for execution. Why? > > Measurement overhead. It would seem your platform has a particularly > slow version of gettimeofday() though ... I've never noticed such a > large discrepancy myself. Actually, I'm noticing similar "problems" with v7.4 ... "EXPLAIN ANALYZE" seems to be showing some *very* high ms for execution time, but if you run the actual query, it doesn't seem to take even 1/10th the time reported ... This is on a FreeBSD 4.x box ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Encoding problem with 7.4
"E.Rodichev" <[EMAIL PROTECTED]> writes: > /e:2>createdb test > test | er | SQL_ASCII <- Incorrect! > (3 rows) > Let's note than the last line is in fact completely incorrect. What's incorrect about it? You didn't ask for any other encoding than SQL_ASCII. You can set the default encoding at initdb time, IIRC, but you didn't. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Functions with COPY
Stephen Frost <[EMAIL PROTECTED]> writes: > Consider the following input data: > 1234,24.50,10-Jan-2003,10/1/03,10-01-2003,hiall > The interpretation for the numbers is: > 1234 =3D 12.34, 24.50 =3D 24.50 > The interpretation for the dates is: > January 10th, 2003, October 1st, 2003, October 1st, 2003 > I don't believe it's possible, currently, to correctly import this > data with copy. I'm not sure the date fields would even be accepted > as date fields. Nonsense. regression=# set datestyle to mdy; SET regression=# select '10-Jan-2003'::date; date 2003-01-10 (1 row) regression=# select '10/1/03'::date; date 2003-10-01 (1 row) regression=# select '10-01-2003'::date; date 2003-10-01 (1 row) I think you'd have to do some preprocessing on the numeric inputs if you wanted implied decimal points inserted like that, but the dates look fine. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] about explain analyze
Teodor Sigaev <[EMAIL PROTECTED]> writes: > Explain analyze takes 3 times more time for execution. Why? Measurement overhead. It would seem your platform has a particularly slow version of gettimeofday() though ... I've never noticed such a large discrepancy myself. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timestamp convert function
Karel Zak <[EMAIL PROTECTED]> writes: > On Thu, Nov 27, 2003 at 09:49:22AM +0100, Nhan NGO DINH wrote: >> This output has been obtained from a PostgreSQL 7.3.4 > I think it's fixed in 7.4. Yes. The patch will also be in 7.3.5 when we release that (probably in the next week or so). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_restore and create FK without verification check
Oli Sennhauser wrote: People might be more interested in debating this topic with you if we hadn't discussed it at length just a couple months back. There wasn't consensus then that we had to offer an escape hatch, and you've not offered any argument that wasn't made before. I'm simply presenting a problem for which I currently do not see any solution (it's very important for us to be able to restore db within a reasonable amount of time). If there's no solution and none is planned, then we cannot use pgsql, can we? You're simply presenting a problem that isn't there in the first place. If you really feel the need to shoot yourself in the foot, use separate schema and data dumps and do the latter with "-X disable-triggers". And now will you please put it to rest? If this is not a prio 1 problem, what are then the prio one problems??? Did you read my mail or only that last sentence? You are a developer, right? Did you ever manage a big database in production? What shoul I tell to my customers when they want to have a not that big database (100 GB) in PostgreSQL: "I am sorry, but we are not able to do performant backups, I recommend you to choos ORACLE instead???". Is it this we/you recommend? Among many other things I am a developer too, and I have managed customer databases up to 1.2 TB. But I wonder what you are. You should tell your customers that they have to dump their databases as pg_dump -d swisscheese >swisscheese.schema.dump pg_dump -a -X disable-triggers swisscheese >swisscheese.data.dump This is what I recommended in my previous mail. Is that an unacceptable solution for your customers or what is the problem? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/SQL packages
On Fri, Nov 28, 2003 at 02:35:11PM +0100, Peter Eisentraut wrote: > Karel Zak writes: > > > in our TODO is the item: "Add PL/PgSQL packages". > > I think the interesting part are the package-global variables. The > name hierarchy seems completely redundant with schemas. Agree, but there is more advantage than package-global variables only. The Oracle PACKAGE: - package is interface definition only and the real functions are defined in PACKAGE BODY, it allows you change internal logic, but interface is still same. - package-global cursors - package-global variables - ?? Maybe PACKAGE can be in PostgreSQL implemented as special a little extended schema (like sequence are special table) and we needn't think about something new. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/SQL packages
Karel Zak writes: > in our TODO is the item: "Add PL/PgSQL packages". I think the interesting part are the package-global variables. The name hierarchy seems completely redundant with schemas. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] PL/SQL packages
Hi, in our TODO is the item: "Add PL/PgSQL packages". I thought about it and for example Oracle's CREATE PACKAGE / PACKAGE BODY seems really interesting and modular. IMHO it's interesting item in PostgreSQL TODO, but I think there a is small collision between the schemas and possible package contents referencing: SELECT xyz.funcname(); ^^^ Is it some function in schema 'xyz' or some function in package 'xyz' in the current schema? Or is needful check both alternatives? Do you have any experience with it? Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] -fpic vs. -fPIC
Late in the 7.4 release we've added a patch that changed -fpic to -fPIC for Linux Sparc. We wanted to investigate that issue further later on. I've tried building PostgreSQL with -fpic on Sparc and saw no problems. So I suggest that we change back to -fpic until we get detailed evidence. Any other suggestions on how to deal with this? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_restore and create FK without verification check
People might be more interested in debating this topic with you if we hadn't discussed it at length just a couple months back. There wasn't consensus then that we had to offer an escape hatch, and you've not offered any argument that wasn't made before. I'm simply presenting a problem for which I currently do not see any solution (it's very important for us to be able to restore db within a reasonable amount of time). If there's no solution and none is planned, then we cannot use pgsql, can we? You're simply presenting a problem that isn't there in the first place. If you really feel the need to shoot yourself in the foot, use separate schema and data dumps and do the latter with "-X disable-triggers". And now will you please put it to rest? If this is not a prio 1 problem, what are then the prio one problems??? You are a developer, right? Did you ever manage a big database in production? What shoul I tell to my customers when they want to have a not that big database (100 GB) in PostgreSQL: "I am sorry, but we are not able to do performant backups, I recommend you to choos ORACLE instead???". Is it this we/you recommend? Regards Oli --- Oli Sennhauser Database-Engineer (Oracle & PostgreSQL) Rebenweg 6 CH - 8610 Uster / Switzerland Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14 e-Mail [EMAIL PROTECTED] Website http://mypage.bluewin.ch/shinguz/PostgreSQL/ Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import smime.p7s Description: S/MIME Cryptographic Signature
