[HACKERS] Where to post a new PostgreSQL utility?
Hi everyone, Have gotten a new PostgreSQL utility together called pg_autotune that load tests using Tatsuo's pgbench code over multiple-iterations, attempting to determine decent buffer settings for a specified client load. It's more a framework for adding stuff to later, but for now it just works (albeit time consuming). Where do I post it (here or PATCHES?) because if the code is rugged enough then it might be useful in contrib? :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Where to post a new PostgreSQL utility?
On 20 Sep 2002 at 16:33, Justin Clift wrote: Hi everyone, Have gotten a new PostgreSQL utility together called pg_autotune that load tests using Tatsuo's pgbench code over multiple-iterations, attempting to determine decent buffer settings for a specified client load. It's more a framework for adding stuff to later, but for now it just works (albeit time consuming). Where do I post it (here or PATCHES?) because if the code is rugged enough then it might be useful in contrib? or at http://gborg.postgresql.org, Bye Shridhar -- The Consultant's Curse: When the customer has beaten upon you long enough, give him what he asks for, instead of what he needs. This is very strong medicine, and is normally only required once. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Would you be able to update the MySQL manual?
Hi Monty, Would you be able to update the MySQL manual? In 1.9.2.3 of the MySQL manual (http://www.mysql.com/doc/en/MySQL-PostgreSQL_benchmarks.html) it mentions : The only Open Source benchmark that we know of that can be used to benchmark MySQL Server and PostgreSQL (and other databases) is our own. It would be great if you could update the manual to reflect the Open Source Database Benchmark (OSDB) as an option as well, as it's received a decent amount of development from many database coders, is very cross platform, it does multi-user testing (more real world suitable) and it's based on the ANSI AS3AP database testing standard. The main site for the Open Source Database Benchmark is: http://osdb.sf.net :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Current CVS is broken
% uname -a FreeBSD xor 4.6-STABLE FreeBSD 4.6-STABLE #2: Tue Jun 18 20:48:48 MSD 2002 teodor@xor:/usr/src/sys/compile/XOR i386 ... gmake[3]: `/spool/home/teodor/pgsql/src/backend/commands' gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o aggregatecmds.o aggregatecmds.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o analyze.o analyze.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o async.o async.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o cluster.o cluster.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o comment.o comment.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o conversioncmds.o conversioncmds.c gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o copy.o copy.c copy.c: In function `CopyFrom': copy.c:1130: warning: passing arg 1 of `coerce_type_constraints' from incompatible pointer type copy.c:1130: warning: passing arg 2 of `coerce_type_constraints' makes integer from pointer without a cast copy.c:1130: too many arguments to function `coerce_type_constraints' -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] SCSI Error
Hiya Lists Somebody could help me? I am with an error when the Postgresql makes Insert, Delete or Update kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938856 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938800 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938864 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938872 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938808 Version: postgresql-7.2.1-5 postgresql-devel-7.2.1-5 postgresql-libs-7.2.1-5 postgresql-server-7.2.1-5 Server: processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 11 model name : Intel(R) Pentium(R) III CPU family 1266MHz stepping: 1 cpu MHz : 1258.309 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse bogomips: 2510.02 Kernel: Linux version 2.4.7-10custom (gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)) #9 Mon Sep 16 17:50:13 BRT 2002 Mem Info total:used:free: shared: buffers: cached: Mem: 789540864 782589952 6950912 81764352 34406400 583122944 Swap: 802873344 2957312 799916032 MemTotal: 771036 kB MemFree: 6788 kB MemShared: 79848 kB Buffers: 33600 kB Cached: 566568 kB SwapCached: 2888 kB Active: 14476 kB Inact_dirty:664852 kB Inact_clean: 3576 kB Inact_target: 136 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 771036 kB LowFree: 6788 kB SwapTotal: 784056 kB SwapFree: 781168 kB NrSwapPages:195292 pages pci Bus 2, device 9, function 0: SCSI storage controller: Adaptec 7899P (rev 1). IRQ 10. Master Capable. Latency=96. Min Gnt=40.Max Lat=25. I/O at 0x2100 [0x21ff]. Non-prefetchable 64 bit memory at 0xedfff000 [0xedff]. Swap FilenameTypeSizeUsedPriority /dev/sda6 partition 784056 2888-1 Tkanksfull -- Ricardo Fogliati 4 Linux/Vesper ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SCSI Error
On Fri, 20 Sep 2002, Ricardo Fogliati wrote: Hiya Lists Somebody could help me? I am with an error when the Postgresql makes Insert, Delete or Update kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938856 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938800 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938864 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938872 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938808 Version: postgresql-7.2.1-5 [deleted]... Kernel: Linux version 2.4.7-10custom (gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)) #9 Mon Sep 16 17:50:13 BRT 2002 Not sure what you're asking for. That's a hardware error. Back up immediately, if you haven't already got decent backups, and fix the disk/controller. Could possibly be a filesystem error but even if so it's still casting doubt on the hardware. On the other hand I do believe I saw a message recently saying that some of the 2.4 series kernels had file system bugs. I don't know which, someone else might be able to expand. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Where to post a new PostgreSQL utility?
gborg On Fri, 20 Sep 2002, Justin Clift wrote: Hi everyone, Have gotten a new PostgreSQL utility together called pg_autotune that load tests using Tatsuo's pgbench code over multiple-iterations, attempting to determine decent buffer settings for a specified client load. It's more a framework for adding stuff to later, but for now it just works (albeit time consuming). Where do I post it (here or PATCHES?) because if the code is rugged enough then it might be useful in contrib? :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Current CVS is broken
Teodor Sigaev [EMAIL PROTECTED] writes: gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o copy.o copy.c copy.c: In function `CopyFrom': copy.c:1130: warning: passing arg 1 of `coerce_type_constraints' from incompatible pointer type copy.c:1130: warning: passing arg 2 of `coerce_type_constraints' makes integer from pointer without a cast copy.c:1130: too many arguments to function `coerce_type_constraints' Looks like Rod's domain-constraints-in-COPY patch was stale after my recent casting changes. Will work on it ... (Bruce, you really oughta do some minimal testing on patches before committing 'em.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SCSI Error
On Fri, Sep 20, 2002 at 01:32:52PM +0100, Nigel J. Andrews wrote: the hardware. On the other hand I do believe I saw a message recently saying that some of the 2.4 series kernels had file system bugs. I recall problems, offhand, with 2.4.5, 2.4.10, 2.4.11 (which was so broken that you couldn't recover), and 2.4.15. I seem to recall a report on 2.4.12, also. There's a page at http://www.atnf.csiro.au/people/rgooch/linux/docs/kernel-newsflash.html that provides some summaries of known problems, and filesystem corruption doesn't show up for all the ones I mentioned, so maybe my memory is faulty (alas, I didn't get the ECC option). A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(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] Having no luck with getting pgbench to run multiple
Well, you'll probably want to pass in a valid timeval structure if you
don't want it to block.
Basically, that snippet tells select on the list of sockets, looking for
sockets that have data to be read while waiting forever. That means it
will block until something appears on one of the sockets your
monitoring.
Greg
On Thu, 2002-09-19 at 13:43, Justin Clift wrote:
Hi everyone,
Am trying my hand at a bit of C code again. Specifically am trying to
get Tatsuo's pgbench code to loop around more than once, but it keeps
on hanging forever at this line:
if ((nsocks = select(maxsock + 1, input_mask, (fd_set *) NULL,
(fd_set *) NULL, (struct timeval *) NULL)) 0)
{
etc
Running this on a FreeBSD 4.6.2 system with PostgreSQL 7.2.2 and gcc
2.95.3. Looking around the Net seems to say that hangs like this are
caused by the select blocking, but that's not helping me any with
knowing what to do.
Does anyone have an idea of what I can do, or maybe have a few minutes
to look at my code and point out the problem?
:-)
Regards and best wishes,
Justin Clift
--
My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there.
- Indira Gandhi
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Having no luck with getting pgbench to run multipletimes
Hi Greg, That's cool. Played with it for a while longer, then found out that the order that it was being called in didn't work very well as the select() was executed after all the required sockets had been closed/ended. So, it just meant a re-ordering of things, and it's now working alright. Am just fine tuning this util, and it's looking to be pretty nifty. It automatically tunes local or remote PostgreSQL databases (currently it's limited to the shared_buffers, sort_mem, and vacuum_mem variables). But it's a start. :) Regards and best wishes, Justin Clift Greg Copeland wrote: Well, you'll probably want to pass in a valid timeval structure if you don't want it to block. Basically, that snippet tells select on the list of sockets, looking for sockets that have data to be read while waiting forever. That means it will block until something appears on one of the sockets your monitoring. Greg -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Improving speed of copy
Hi all, While testing for large databases, I am trying to load 12.5M rows of data from a text file and it takes lot longer than mysql even with copy. Mysql takes 221 sec. v/s 1121 sec. for postgres. For postgresql, that is around 11.5K rows per second. Each tuple has 23 fields with fixed length of around 100 bytes I wrote a programs which does inserts in batches but none of thme reaches performance of copy. I tried 1K/5K/10K/100K rows in a transaction but it can not cross 2.5K rows/sec. The machine is 800MHz, P-III/512MB/IDE disk. Postmaster is started with 30K buffers i.e. around 235MB buffers. Kernel caching paramaters are defaults. Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead mentioned in FAQ, that should come to around 1.7GB, counting for 40% increase in size. Vacuum was run on database. Any further help? Especially if batch inserts could be speed up, that would be great.. Bye Shridhar -- Alone, adj.:In bad company. -- Ambrose Bierce, The Devil's Dictionary ---(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] SCSI Error
Ensure you don't have termination issues. Make sure your SCSI interface is configured correctly for your SCSI environment, especially on matters of termination. Make sure you have enough power to your drive and if possible, make sure your drives are hung off of distinct power segments coming from your power supply. Of course, make sure you have enough power being supplied from your power supply to power all of your drives and *equipment* (cards, CPU, fans, etc). Last, be sure your cables are in good working order and that they are as far away from the power supply and cables as is possible. SCSI is notorious for suffering from RF interference especially on long cables. If all else fails, you may try swapping cables to see if this helps. If at all possible, immediately fsck (don't make it perform any repairs) and backup! The fsck will let you know if you should reasonably expect to have a meaningful backup. Requesting fsck to repair in this situation could actually result in more/worse (unrecoverable) corruption. If the fsck does report potential FS damage, accept the fact that some of your data (and resulting backup) may be corrupt, perhaps even unrecoverably so. I'm using a 7880 here and have never had notable issues save only for bugs in the Linux SCSI drivers. Greg On Fri, 2002-09-20 at 07:08, Ricardo Fogliati wrote: Hiya Lists Somebody could help me? I am with an error when the Postgresql makes Insert, Delete or Update kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938856 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938800 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938864 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938872 kernel: SCSI disk error : host 2 channel 0 id 0 lun 0 return code = 7 kernel: I/O error: dev 08:08, sector 47938808 Version: postgresql-7.2.1-5 postgresql-devel-7.2.1-5 postgresql-libs-7.2.1-5 postgresql-server-7.2.1-5 Server: processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 11 model name : Intel(R) Pentium(R) III CPU family 1266MHz stepping: 1 cpu MHz : 1258.309 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 mmx fxsr sse bogomips: 2510.02 Kernel: Linux version 2.4.7-10custom (gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98)) #9 Mon Sep 16 17:50:13 BRT 2002 Mem Info total:used:free: shared: buffers: cached: Mem: 789540864 782589952 6950912 81764352 34406400 583122944 Swap: 802873344 2957312 799916032 MemTotal: 771036 kB MemFree: 6788 kB MemShared: 79848 kB Buffers: 33600 kB Cached: 566568 kB SwapCached: 2888 kB Active: 14476 kB Inact_dirty:664852 kB Inact_clean: 3576 kB Inact_target: 136 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 771036 kB LowFree: 6788 kB SwapTotal: 784056 kB SwapFree: 781168 kB NrSwapPages:195292 pages pci Bus 2, device 9, function 0: SCSI storage controller: Adaptec 7899P (rev 1). IRQ 10. Master Capable. Latency=96. Min Gnt=40.Max Lat=25. I/O at 0x2100 [0x21ff]. Non-prefetchable 64 bit memory at 0xedfff000 [0xedff]. Swap FilenameTypeSizeUsedPriority /dev/sda6 partition 784056 2888-1 Tkanksfull -- Ricardo Fogliati 4 Linux/Vesper ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Improving speed of copy
Are you using copy within a transaction? I don't know how to explain the size difference tho. I have never seen an overhead difference that large. What type of MySQL tables were you using and what version? Have you tried this with Oracle or similar commercial database? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Shridhar Daithankar Sent: Friday, September 20, 2002 9:52 AM To: [EMAIL PROTECTED] Subject: [HACKERS] Improving speed of copy Hi all, While testing for large databases, I am trying to load 12.5M rows of data from a text file and it takes lot longer than mysql even with copy. Mysql takes 221 sec. v/s 1121 sec. for postgres. For postgresql, that is around 11.5K rows per second. Each tuple has 23 fields with fixed length of around 100 bytes I wrote a programs which does inserts in batches but none of thme reaches performance of copy. I tried 1K/5K/10K/100K rows in a transaction but it can not cross 2.5K rows/sec. The machine is 800MHz, P-III/512MB/IDE disk. Postmaster is started with 30K buffers i.e. around 235MB buffers. Kernel caching paramaters are defaults. Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead mentioned in FAQ, that should come to around 1.7GB, counting for 40% increase in size. Vacuum was run on database. Any further help? Especially if batch inserts could be speed up, that would be great.. Bye Shridhar -- Alone, adj.:In bad company. -- Ambrose Bierce, The Devil's Dictionary ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving speed of copy
On 20 Sep 2002 at 21:22, Shridhar Daithankar wrote: Mysql takes 221 sec. v/s 1121 sec. for postgres. For postgresql, that is around 11.5K rows per second. Each tuple has 23 fields with fixed length of around 100 bytes I wrote a programs which does inserts in batches but none of thme reaches performance of copy. I tried 1K/5K/10K/100K rows in a transaction but it can not cross 2.5K rows/sec. 1121 sec. was time with postgres default of 64 buffers. With 30K buffers it has degraded to 1393 sec. One more issue is time taken for composite index creation. It's 4341 sec as opposed to 436 sec for mysql. These are three non-unique character fields where the combination itself is not unique as well. Will doing a R-Tree index would be a better choice? In select test where approx. 15 rows where reported with query on index field, mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues eclipse the result.. TIA once again.. Bye Shridhar -- revolutionary, adj.:Repackaged. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Novell releasing PostgreSQL for NetWare.
http://developer.novell.com/connections/091902.html I'm somehwat surprized no one else has mentioned this, as it's on Slashdot... -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Improving speed of copy
Also, did you disable fsync? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonah H. Harris Sent: Friday, September 20, 2002 10:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] Improving speed of copy Are you using copy within a transaction? I don't know how to explain the size difference tho. I have never seen an overhead difference that large. What type of MySQL tables were you using and what version? Have you tried this with Oracle or similar commercial database? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Shridhar Daithankar Sent: Friday, September 20, 2002 9:52 AM To: [EMAIL PROTECTED] Subject: [HACKERS] Improving speed of copy Hi all, While testing for large databases, I am trying to load 12.5M rows of data from a text file and it takes lot longer than mysql even with copy. Mysql takes 221 sec. v/s 1121 sec. for postgres. For postgresql, that is around 11.5K rows per second. Each tuple has 23 fields with fixed length of around 100 bytes I wrote a programs which does inserts in batches but none of thme reaches performance of copy. I tried 1K/5K/10K/100K rows in a transaction but it can not cross 2.5K rows/sec. The machine is 800MHz, P-III/512MB/IDE disk. Postmaster is started with 30K buffers i.e. around 235MB buffers. Kernel caching paramaters are defaults. Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead mentioned in FAQ, that should come to around 1.7GB, counting for 40% increase in size. Vacuum was run on database. Any further help? Especially if batch inserts could be speed up, that would be great.. Bye Shridhar -- Alone, adj.:In bad company. -- Ambrose Bierce, The Devil's Dictionary ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improving speed of copy
On Fri, 2002-09-20 at 08:52, Shridhar Daithankar wrote: Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead mentioned in FAQ, that should come to around 1.7GB, counting for 40% increase in size. Vacuum was run on database. How did you calculate the size of database? If you used du make sure you do it in the data/base directory as to not include the WAL files. -- Best Regards, Mike Benoit NetNation Communication Inc. Systems Engineer Tel: 604-684-6892 or 888-983-6600 --- Disclaimer: Opinions expressed here are my own and not necessarily those of my employer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improving speed of copy
On Fri, 20 Sep 2002, Shridhar Daithankar wrote: In select test where approx. 15 rows where reported with query on index field, mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues eclipse the result.. I don't know about anyone else but I find this aspect strange. That's 1 second (approx.) per row retrieved. That is pretty dire for an index scan. The data/index must be very non unique. -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Memory Errors...
On 20 Sep 2002, Greg Copeland wrote: I'll try to have a look-see by the end of the weekend. Any code that can reproduce it or is it ANY code that uses SPI? Greg On Fri, 2002-09-20 at 11:39, Peter Eisentraut wrote: Tom Lane writes: On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. There's a note in the PL/Python documentation that it's leaking memory if SPI plans are used. Maybe that's related and someone could take a look at it. I've added the call to free the tuptable just as in the pltcl patch I submited earlier (which I can't remember if I've seen in the list so I may well resend). However, the comments in the code imply there might be another leak with prepared plans. I'm looking into that so I won't be sending this patch just yet. -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Kris Jurka wrote: On Thu, 19 Sep 2002, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? Yes, it does, but I don't understand why. The query is entirely against pg_catalog tables which have had all of three tables added to them. How can the new ANALYZE stats be significantly different than what came from the ANALYZED template1. Kris Jurka Looking at the differences in statistics before and after the ANALYZE the only differences are in correlation. This comes from initdb around line 1046... $PGPATH/postgres $PGSQL_OPT template1 /dev/null EOF ANALYZE; VACUUM FULL FREEZE; EOF Could this be done better in the one step VACUUM FULL FREEZE ANALYZE or ANALYZING after the VACUUM FULL? Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Improving speed of copy
Nigel J. Andrews wrote: On Fri, 20 Sep 2002, Shridhar Daithankar wrote: In select test where approx. 15 rows where reported with query on index field, mysql took 14 sec. and psotgresql took 17.5 sec. Not bad but other issues eclipse the result.. I don't know about anyone else but I find this aspect strange. That's 1 second (approx.) per row retrieved. That is pretty dire for an index scan. The data/index must be very non unique. Yeah, I'd agree that is strange. Can we see EXPLAIN ANALYZE for that query. Also, in one of your ealier posts you mentioned a slowdown after raising shared buffers from the default 64 to 3. You might have driven the machine into swapping. Maybe try something more like 1 - 15000. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] to_char(FM9.9) bug fix
Karel Zak writes: test=# select to_char(0,'FM9.9'); to_char - 0. (1 row) test=# select to_char(1,'FM9.9'); to_char - 1. (1 row) I find this highly bizzare. The FM modifier means to omit unnecessary trailing stuff. There is no reasonable business or scientific custom to leave a trailing point after a number. Or perhaps a more pragmatic question is, how would I print a number without the trailing point? -- Peter Eisentraut [EMAIL PROTECTED] ---(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] Optimizer generates bad plans.
Kris Jurka [EMAIL PROTECTED] writes: Looking at the differences in statistics before and after the ANALYZE the only differences are in correlation. This comes from initdb around line 1046... $PGPATH/postgres $PGSQL_OPT template1 /dev/null EOF ANALYZE; VACUUM FULL FREEZE; EOF Could this be done better in the one step VACUUM FULL FREEZE ANALYZE or ANALYZING after the VACUUM FULL? Hm. We can't do it like that, because that would leave the pg_statistic rows unfrozen. I suppose we could do VACUUM FULL; ANALYZE; VACUUM FREEZE; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Lyris looking to help fix PostgresSQL crashing problems
Hello -- I'm the lead programmer of Lyris ListManager, an email list server that run on PostgreSQL, Oracle, and MS/SQL. About 20% of our client base of 4000 runs on PostgresSQL -- it's very popular with our clients -- much more than Oracle is (about 3%). Unfortunately we have about a dozen clients who have stability problems with PostgresSQL. This week a major television network cancelled their order with us due to their PostgresSQL stability issues, which is what prompted me to write this email and get involved with the PostgresSQL community. It seems that with larger database sizes (500,000 rows and larger) and high stress, the server daemon has a tendency to core. We've also had cases where a single connection doing a million inserts into a table will cause the daemon to core. We've seen problems with both 7.1 and 7.2.x, with built-on-the-machine and with RPMs. We've also had big stability problems with Solaris 8/Sparc, and don't ship on that platform because of that. What I'd like to do is help solve these problems in the core distribution, so that PostrgesSQL can indeed be able to handle the large databases and high transaction loads that Microsoft SQL can. My company has hired open source people before to help fix bugs or add features to open source projects, most notable from the Tcl community, as we use Tcl quite a bit (we have two programmers from the Tcl Core team working here). This works out well for the Tcl community, as we fund the development of the project, as well as pay someone to work on something they want to work on anyhow. So... what I'm looking for are recommendations on a PostgresSQL guru who could help nail the stability/load issues, and make sure that the fixes make their way back into the PostgresSQL core. What I'd prefer is to get a regular contributor to this list, so that this person could investigate our problems, and then get the community's help in solving them. Thanks! -john ---(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] Lyris looking to help fix PostgresSQL crashing problems
John Buckman [EMAIL PROTECTED] writes: It seems that with larger database sizes (500,000 rows and larger) and high stress, the server daemon has a tendency to core. We'd love to see some stack traces ... 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] Memory Errors...
Ok, below is the original email I sent, which I can not remember seeing come
across the patches list. Please do read the assumptions since they might throw
up problems with what I have done.
I have attached the pltcl patch again, just in case. For the sake of clarity
let's say this patch superscedes the previous one.
I have also attached a patch addressing the similar memory leak problem in
plpython. This includes a slight adjustment of the tests in the source
directory. The patch also includes a cosmetic change to remove a compiler
warning although I think the change makes the code look worse though.
Once again, please read my text below and also take a quick look at the comment
I've added in the plpython patch since it may well show that that
particular change is complete rubbish.
BTW, by my reckoning the memory leak would occur with prepared plans and
without. If that is not the case then I've been barking up the wrong tree.
Of further note, I have not tested for the memory leak in plpython but the
build passes the normal and big checks. However, I have tried testing using the
test.sh script in src/pl/plpython. This seems to be generating errors where
before there were warnings. Can anyone comment on the correctness of this?
Reversing my changes doesn't really help matters so I presume it is something
else that is causing the different behaviour.
--
Nigel J. Andrews
On Fri, 20 Sep 2002, Nigel J. Andrews wrote:
On Thu, 19 Sep 2002, Tom Lane wrote:
Ian Harding [EMAIL PROTECTED] writes:
It is pltcl [not plpgsql]
Ah. I don't think we've done much of any work on plugging leaks in
pltcl :-(.
It hurts when I do this:
drop function memleak();
create function memleak() returns int as '
for {set counter 1} {$counter 10} {incr counter} {
set sql select ''foo''
spi_exec $sql
}
' language 'pltcl';
select memleak();
Yeah, I see very quick memory exhaustion also :-(. Looks like the
spi_exec call is the culprit, but I'm not sure exactly why ...
anyone have time to look at this?
Attached is a patch that frees the SPI_tuptable in all post SPI_exec
non-elog paths in both pltcl_SPI_exec() and pltcl_SPI_execp().
The fault as triggered by the above code has been fixed by this patch but
please read my assumptions below to ensure they are correct.
I have assumed that Tom's comment about this only being required in non-elog
paths is correct, which seems a reasonable assumption to me.
I have also assumed, rather than verified, that freeing the tuptable does
indeed free the tuples as well. Tests with the above function show that the
process does not increase it's memory footprint during it's operation, although
if my assumption here is wrong this could be a feature of selecting
insignificantly sized tuples.
I have not worried about other uses of SPI_exec for selects in pltcl.c on the
basis that those are not under the control of the function writer and the
normal function management will release the storage.
Index: src/pl/plpython/feature.expected
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/feature.expected,v
retrieving revision 1.4
diff -c -r1.4 feature.expected
*** src/pl/plpython/feature.expected2002/03/06 18:50:31 1.4
--- src/pl/plpython/feature.expected2002/09/20 22:12:36
***
*** 29,35
(1 row)
SELECT import_fail();
! WARNING: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
--- 29,35
(1 row)
SELECT import_fail();
! NOTICE: ('import socket failed -- untrusted dynamic module: _socket',)
import_fail
failed as expected
Index: src/pl/plpython/plpython.c
===
RCS file: /projects/cvsroot/pgsql-server/src/pl/plpython/plpython.c,v
retrieving revision 1.22
diff -c -r1.22 plpython.c
*** src/pl/plpython/plpython.c 2002/09/04 22:51:23 1.22
--- src/pl/plpython/plpython.c 2002/09/20 22:12:40
***
*** 408,414
--- 408,416
else
PLy_restart_in_progress += 1;
if (proc)
+ {
Py_DECREF(proc-me);
+ }
RERAISE_EXC();
}
***
*** 1841,1847
--- 1843,1856
*
* FIXME -- leaks saved plan on object destruction. can this be
* avoided?
+* I think so. A function prepares and then execp's a statement.
+* When we come to deallocate the 'statement' object we obviously
+* no long need the plan. Even if we did, without the object
+* we're never going to be able to use it again.
+
[HACKERS] Hosed PostGreSQL Installation
As a result of some disk errors on another drive, an admin in our group brought down the server hosting our pgsql databases with a kill -KILL after having gone to runlevel 1 and finding the postmaster process still running. No surprise, our installation was hosed in the process. After talking on #postgresql with klamath for about an hour or so to work through the issue (many thanks!), it was suggested that I send the info to this list. Currently, PostGreSQL will no longer start, and gives this error. bash-2.05$ /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start postmaster successfully started bash-2.05$ DEBUG: database system shutdown was interrupted at 2002-09-19 22:59:54 EDT DEBUG: open(logfile 0 seg 0) failed: No such file or directory DEBUG: Invalid primary checkPoint record DEBUG: open(logfile 0 seg 0) failed: No such file or directory DEBUG: Invalid secondary checkPoint record FATAL 2: Unable to locate a valid CheckPoint record /usr/bin/postmaster: Startup proc 11735 exited with status 512 - abort Our setup is vanilla Red Hat 7.2, having pretty much all of the postgresql-*-7.1.3-2 packages installed. Klamath asked if I had disabled fsync in postgresql.conf, and the only non-default (read: non-commented) setting in the file is: `tcpip_socket = true` Klamath suggested that I run pg_controldata: bash-2.05$ ./pg_controldata pg_control version number:71 Catalog version number: 200101061 Database state: SHUTDOWNING pg_control last modified: Thu Sep 19 22:59:54 2002 Current log file id: 0 Next log file segment:1 Latest checkpoint location: 0/1739A0 Prior checkpoint location:0/1718F0 Latest checkpoint's REDO location:0/1739A0 Latest checkpoint's UNDO location:0/0 Latest checkpoint's StartUpID:21 Latest checkpoint's NextXID: 615 Latest checkpoint's NextOID: 18720 Time of latest checkpoint:Thu Sep 19 22:49:42 2002 Database block size: 8192 Blocks per segment of large relation: 131072 LC_COLLATE: en_US LC_CTYPE: en_US If I look into the pg_xlog directory, I see this: sh-2.05$ cd pg_xlog/ bash-2.05$ ls -l total 32808 -rw---1 postgres postgres 16777216 Sep 20 23:13 0002 -rw---1 postgres postgres 16777216 Sep 19 22:09 0002007E There is one caveat. The installation resides on a partition of its own: /dev/hda317259308 6531140 9851424 40% /var/lib/pgsql/data fdisk did not report errors for this partition at boot time after the forced shutdown, however. This installation serves a university research project, and although most of the code / schemas are in development (and should be in cvs by rights), I can't confirm that all projects have indeed done that. So any advice, ideas or suggestions on how the data and / or schemas can be recovered would be greatly appreciated. Many thanks! -- pete P.S.: I've been using pgsql for about four years now, and it played a big role during my grad work. In fact, the availability of pgsql was one of the reasons why I was able to complete and graduate. Many thanks for such a great database! -- Pete St. Onge Research Associate, Computational Biologist, UNIX Admin Banting and Best Institute of Medical Research Program in Bioinformatics and Proteomics University of Toronto http://www.utoronto.ca/emililab/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane dijo: I think we could make all these cases work if we replaced attisinherited with *two* columns, a boolean attislocal(ly defined) and a count of (direct) inheritances. DROP ONLY would have the effect of decrementing the count and setting attislocal to true in each direct child; recursive DROP would decrement the count and then drop if count is 0 *and* attislocal is not set. At the start of a recursion, we'd allow DROP only if count is 0 (and, presumably, attislocal is true, else the column would not be there...). The cases you presented are really tricky. I'll work today on the attislocal and attinhcount patch; I hope to have it ready later today for review and inclusion before beta2. -- Alvaro Herrera (alvherre[a]atentus.com) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, Gee, Officer Krupke) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] to_char(FM9.9) bug fix
Peter Eisentraut [EMAIL PROTECTED] writes: Karel Zak writes: test=# select to_char(0,'FM9.9'); to_char - 0. (1 row) test=# select to_char(1,'FM9.9'); to_char - 1. (1 row) I find this highly bizzare. No doubt, but it's what Oracle does (see tests posted to the lists by several people) and to_char exists to duplicate Oracle behavior. This is hardly the silliest aspect of to_char's definition, IMHO ... 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
[HACKERS] Conversion Questions
Is there ever a need to have more than one conversion for a given
combination of encodings? And if I have more than one combination
registered, which one is used by the implicit server/client conversion?
Also, if my server encoding is A and my client encoding is B, and I do
SELECT convert('some string' using a_to_c); -- not B
or even
SELECT convert('some string' using e_to_f);
this would surely lead to bogus results? What's the use of all this?
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Win32 rename()/unlink() questions
Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: Yes! Indeed that does work. Thinking back, I think that may still fail on Win95 (using MoveFile). Once in the past I had to work on (un)installers for Win* and I vaguely remember Win95 being more strict than Win98 but that may just have been with moving the executable you're currently running. Well, here's the test: foo.txt contains This is FOO! bar.txt contains This is BAR! Process 1 opens foo.txt Process 2 opens foo.txt Process 1 sleeps 7.5 seconds Process 2 sleeps 15 seconds Process 1 uses MoveFile() to rename foo.txt to foo2.txt Process 1 uses MoveFile() to rename bar.txt to foo.txt Process 1 uses DeleteFile() to remove foo2.txt Process 2 awakens and displays This is FOO! On the filesystem, we then have: foo.txt containing This is BAR! The good news is that this works fine under NT 4 using just MoveFile(). The bad news is that it requires the files be opened using CreateFile() with the FILE_SHARE_DELETE flag set. The C library which ships with Visual C++ 6 ultimately calls CreateFile() via fopen() but with no opportunity through the standard C library routines to use the FILE_SHARE_DELETE flag. And the FILE_SHARE_DELETE flag cannot be used under Windows 95/98 (Bad Parameter). Which means, on those platforms, there still doesn't appear to be a solution. Under NT/XP/2K, AllocateFile() will have to modified to call CreateFile() instead of fopen(). I'm not sure about ME, but I suspect it behaves similarly to 95/98. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
On Thu, 19 Sep 2002, Thomas Lockhart wrote: Actually, a core member did implement this just a few weeks ago. The same crew arguing this time rejected the changes and removed them from the 7.3 feature set. The change to make a PG_XLOG environment variable was rejected. Is that really the change you were talking about? So some folks have their heels dug in, and the vocal ones are not really interested in understanding the issues which this feature is addressing. I was one of the vocal objectors, and I certainly understand the issues very well. Perhaps we should be saying the vocal supporters of the environment variable don't understand the issues. None of the objectors I saw have any problem with enabling Windows NT to have the log file somewhere else. In fact, I'm very strongly in support of this. But I object to doing it in a way that makes the system more fragile and susceptable to not starting properly, or even damage, when there's a simple and obvious way of doing it right: put this in the database configuration file rather than in an environment variable. Why you object to that, and insist it must be an environment variable instead (if that is indeed what you're doing), I'm not sure cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Hannu Krosing [EMAIL PROTECTED] writes: I still think that this should be fixed in 7.3, but the inhcount attribute should show all tables where the column is defined, not just inherited. The default, no-inheritance case should set the column to 1. Well, no, because then a locally defined column is indistinguishable from a singly-inherited column, breaking the cases that the original attisinherited patch was supposed to fix. It doesn't fix the ONLY problem, either. Consider create table p1 (f1 int); create table p2 (f1 int); create table c () inherits(p1, p2); --c.f1 now has definition-count 2 drop ONLY column p1.f1; --c.f1 now has count 1? drop column p2.f1; --c.f1 removed because count went to 0? It might look like we could fix this by defining DROP ONLY as not touching the child-table definition-counts at all; then a DROP ONLY effectively makes a child column look like it's locally defined instead of inherited. But that trick only works once. Consider: create table p1 (f1 int); create table p2 (f1 int); create table c () inherits(p1, p2); --c.f1 now has definition-count 2 drop ONLY column p1.f1; --c.f1 still has count 2? drop ONLY column p2.f1; --c.f1 still has count 2? drop column c.f1 --fails because count1, so there is now no way to delete c.f1 I think we could make all these cases work if we replaced attisinherited with *two* columns, a boolean attislocal(ly defined) and a count of (direct) inheritances. DROP ONLY would have the effect of decrementing the count and setting attislocal to true in each direct child; recursive DROP would decrement the count and then drop if count is 0 *and* attislocal is not set. At the start of a recursion, we'd allow DROP only if count is 0 (and, presumably, attislocal is true, else the column would not be there...). Question is, is fixing these cases worth this much trouble? I think the two-column solution is actually free in terms of storage space in pg_attribute, because of alignment considerations. But it's still a large reworking of the existing patch, and we have other fish to fry by Sunday. In any case I am inclined to reject the patch as-it-stands, because it fixes one problem at the cost of introducing new ones. 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] Where to post a new PostgreSQL utility?
-Original Message- From: Marc G. Fournier [mailto:[EMAIL PROTECTED]] Sent: 20 September 2002 14:55 To: Justin Clift Cc: PostgreSQL Hackers Mailing List Subject: Re: [HACKERS] Where to post a new PostgreSQL utility? gborg Just because I'm curious, is *all* new stuff going to Gborg, and is the existing /contrib going to migrated there? Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Conversion Questions
Is there ever a need to have more than one conversion for a given
combination of encodings?
Sure. For example, several Unicode and SJIS mappings exist depending
on vendors or standards. M$ has its own, Apple has another one...
If a user want to employ Apple's map, he could define his own implicit
conversion.
And if I have more than one combination
registered, which one is used by the implicit server/client conversion?
That depends on current name space. You could find such an example in
the conversion regression test. Note that you cannot define more than
one implicit conversion for a schema/server encoding/client encoding
combination.
Also, if my server encoding is A and my client encoding is B, and I do
SELECT convert('some string' using a_to_c); -- not B
or even
SELECT convert('some string' using e_to_f);
this would surely lead to bogus results?
Yes. Choosing right conversion is callers responsibilty.
What's the use of all this?
One example. A user wants to apply lower() to Unicode database.
select convert(lower(convert('X' using utf_8_to_iso_8859_1)) using
iso_8859_1_to_utf_8);
--
Tatsuo Ishii
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Where to post a new PostgreSQL utility?
Dave Page writes: Just because I'm curious, is *all* new stuff going to Gborg, and is the existing /contrib going to migrated there? I'm curious too... If that is to happen then the profile of gborg would need to be massively increased. Currenly the only real link on the 'net to gborg (by searching through http://www.google.com/search?q=link:gborg.postgresql.org) is from the User Lounge (link Related projects) on the PostgreSQL site. I'd have thought a link on the main left-hand list would be more apt. And gbord needs a search facility if people are going to be able to find anything... Lee. ---(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] Postgres 7.2.2 Segment Error
Christopher Kings-Lynne [EMAIL PROTECTED] writes: The sessions table holds normal site session data, like a uid, username, some other stuff, etc. However entries older than two hours or so get deleted. We VACUUM everynight, so why is the on-disk relation growing so huge? FSM not big enough, perhaps? Try doing a vacuum full, then looking to see how big the table is (in physical blocks) after one day's normal usage. You need at least enough FSM space for that many blocks ... unless you want to vacuum it more often. However, I cannot repeat the error now. If you can't reproduce the error then I'm pretty well convinced that there is no problem in the stored data itself. This was either a hardware glitch or a software bug causing a memory stomp on the top byte of an item pointer retrieved from the index. Although I can't rule out the latter, I find it unlikely given that we don't have similar reports from other people. You may as well do the VACUUM FULL --- I doubt we can learn anything from examining the table. 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] Where to post a new PostgreSQL utility?
-Original Message- From: Lee Kindness [mailto:[EMAIL PROTECTED]] Sent: 20 September 2002 15:19 To: Dave Page Cc: Marc G. Fournier; Justin Clift; Lee Kindness; PostgreSQL Hackers Mailing List Subject: Re: [HACKERS] Where to post a new PostgreSQL utility? Dave Page writes: Just because I'm curious, is *all* new stuff going to Gborg, and is the existing /contrib going to migrated there? I'm curious too... If that is to happen then the profile of gborg would need to be massively increased. Currenly the only real link on the 'net to gborg (by searching through http://www.google.com/search?q=link:gborg.postgresql.org) is from the User Lounge (link Related projects) on the PostgreSQL site. I'd have thought a link on the main left-hand list would be more apt. That's being worked on in a roundabout kind of way... And gbord needs a search facility if people are going to be able to find anything... Yes, I think your're right. I will suggest it to the relevant people. Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Stephan Szabo [EMAIL PROTECTED] writes: ... let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) That is the hard part. Mike's description omitted one crucial step: 6. The old foo goes away when the last open file handle for it is closed. I doubt there is any practical way for Postgres to cause that to happen if the OS itself does not have any support for it. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Bruce Momjian wrote: I don't think we are not going to be supporting Win9X so there isn't an issue there. We will be supporting Win2000/NT/XP. I don't understand FILE_SHARE_DELETE. I read the description at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createfile.asp but I don't understand it: FILE_SHARE_DELETE - Windows NT/2000/XP: Subsequent open operations on the object will succeed only if delete access is requested. I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 rename()/unlink() questions
On Fri, 20 Sep 2002, Mike Mascari wrote: Bruce Momjian wrote: I don't think we are not going to be supporting Win9X so there isn't an issue there. We will be supporting Win2000/NT/XP. I don't understand FILE_SHARE_DELETE. I read the description at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createfile.asp but I don't understand it: FILE_SHARE_DELETE - Windows NT/2000/XP: Subsequent open operations on the object will succeed only if delete access is requested. I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. The question is, what happens if two people have the file open and one goes and tries to delete it? Can the other still read from it? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Current CVS is broken
Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o copy.o copy.c copy.c: In function `CopyFrom': copy.c:1130: warning: passing arg 1 of `coerce_type_constraints' from incompatible pointer type copy.c:1130: warning: passing arg 2 of `coerce_type_constraints' makes integer from pointer without a cast copy.c:1130: too many arguments to function `coerce_type_constraints' Looks like Rod's domain-constraints-in-COPY patch was stale after my recent casting changes. Will work on it ... (Bruce, you really oughta do some minimal testing on patches before committing 'em.) Sorry, forgot this time. I do normally test. -- 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 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] Win32 rename()/unlink() questionst
It is good that moving the file out of the way works, but it doesn't completely solve the problem. What we have now with Unix rename is ideal: 1) old opens continue seeing the old contents 2) new opens see the new contents 3) the file always exists under the fixed name We have that with MoveFileEx(), but we have to loop over the routine until is succeeds. If we move the old file out of the way, we loose the ability to know the file always exists and then we have to loop over open() until is succeeds. I think we may be best just looping on MoveFileEx() until is succeeds. We do the pg_pwd writes while holding an exclusive lock on pg_shadow so that will guarantee that no one else will slip an old version of the file in after we have written it. However, it also prevents pg_shadow access while we are doing the looping. Yuck. --- Mike Mascari wrote: Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Does a sequence like Process 1 opens foo Process 2 opens foo Process 1 creates bar Process 1 renames foo to something - where something is generated to not overlap an existing file Process 1 renames bar to foo Process 2 continues reading let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) Yes! Indeed that does work. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Bruce Momjian wrote: I don't think we are not going to be supporting Win9X so there isn't an issue there. We will be supporting Win2000/NT/XP. I don't understand FILE_SHARE_DELETE. I read the description at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createfile.asp but I don't understand it: FILE_SHARE_DELETE - Windows NT/2000/XP: Subsequent open operations on the object will succeed only if delete access is requested. I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. I don't understand what that gets us. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Current CVS is broken
Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: gcc -g -O -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o copy.o copy.c copy.c: In function `CopyFrom': copy.c:1130: warning: passing arg 1 of `coerce_type_constraints' from incompatible pointer type copy.c:1130: warning: passing arg 2 of `coerce_type_constraints' makes integer from pointer without a cast copy.c:1130: too many arguments to function `coerce_type_constraints' Looks like Rod's domain-constraints-in-COPY patch was stale after my recent casting changes. Will work on it ... (Bruce, you really oughta do some minimal testing on patches before committing 'em.) OK, patch attached. Tom, what is the proper third parameter in COPY, COERCE_DONTCARE? -- 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 Index: src/backend/commands/copy.c === RCS file: /cvsroot/pgsql-server/src/backend/commands/copy.c,v retrieving revision 1.172 diff -c -c -r1.172 copy.c *** src/backend/commands/copy.c 20 Sep 2002 03:52:50 - 1.172 --- src/backend/commands/copy.c 20 Sep 2002 15:28:42 - *** *** 1126,1133 false); /* not coerced */ /* Process constraints */ ! node = coerce_type_constraints(pstate, (Node *) con, ! attr[m]-atttypid, true); values[m] = ExecEvalExpr(node, econtext, isNull, NULL); --- 1126,1133 false); /* not coerced */ /* Process constraints */ ! node = coerce_type_constraints((Node *) con, attr[m]-atttypid, ! COERCE_DONTCARE); values[m] = ExecEvalExpr(node, econtext, isNull, NULL); ---(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] Win32 rename()/unlink() questions
Mike Mascari wrote: instead of fopen(). I'm not sure about ME, but I suspect it behaves similarly to 95/98. I just checked with Katie and the good news (tm) is that the Win32 port we did here at PeerDirect doesn't support 95/98 and ME anyway. It does support NT4, 2000 and XP. So don't bother. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Current CVS is broken
Bruce Momjian [EMAIL PROTECTED] writes: OK, patch attached. Tom, what is the proper third parameter in COPY, COERCE_DONTCARE? It would be COERCE_IMPLICIT_CAST. But I don't like the patch as it stands anyway, because it is repeating a ton of catalog lookups for every input row. I have more extensive changes in mind ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Current CVS is broken
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, patch attached. Tom, what is the proper third parameter in COPY, COERCE_DONTCARE? It would be COERCE_IMPLICIT_CAST. But I don't like the patch as it stands anyway, because it is repeating a ton of catalog lookups for every input row. I have more extensive changes in mind ... OK, I changed it to COERCE_IMPLICIT_CAST. The patch did fix a COPY failure for NULL's and DOMAIN so I didn't remove the patch. Feel free to wack it around. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 rename()/unlink() questionst
Bruce Momjian [EMAIL PROTECTED] writes: I think we may be best just looping on MoveFileEx() until is succeeds. We do the pg_pwd writes while holding an exclusive lock on pg_shadow so that will guarantee that no one else will slip an old version of the file in after we have written it. However, it also prevents pg_shadow access while we are doing the looping. Yuck. Surely you're not evaluating this on the assumption that the pg_shadow triggers are the only places that use rename() ? I see other places in pgstat and relcache that expect rename() to work per Unix spec. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: I think that's a rather poor description. I think it just means that if the file is opened once via CreateFile() with FILE_SHARE_DELETE, then any subsequent CreateFile() calls will fail unless they too have FILE_SHARE_DELETE. In other words, if one of us can delete this file while its open, any of us can. The question is, what happens if two people have the file open and one goes and tries to delete it? Can the other still read from it? Yes. I just tested it and it worked. I'll test Bruce's scenario as well: foo contains: FOO bar contains: BAR 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 calls MoveFile(foo, foo2); 4. Process 3 opens foo - Successful? 5. Process 1 calls MoveFile(bar, foo); 6. Process 4 opens foo - Successful? 7. Process 1 calls DeleteFile(foo2); 8. Process 1, 2, 3, 4 all read from their respective handles. I think the thing to worry about is a race condition between the two MoveFile() attempts. A very ugly hack would be to loop in a CreateFile() in an attempt to open foo, giving up if the error is not a NOT EXISTS error code. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questionst
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think we may be best just looping on MoveFileEx() until is succeeds. We do the pg_pwd writes while holding an exclusive lock on pg_shadow so that will guarantee that no one else will slip an old version of the file in after we have written it. However, it also prevents pg_shadow access while we are doing the looping. Yuck. Surely you're not evaluating this on the assumption that the pg_shadow triggers are the only places that use rename() ? I see other places in pgstat and relcache that expect rename() to work per Unix spec. Yes, I know there are others but I think we will need _a_ rename that works 100% and then replace that in all Win32 rename cases. Given what I have seen, I think a single rename with a loop that uses MoveFileEx() may be our best bet. It is localized, doesn't affect the open() code, and should work well. The only downside is that under heavy read activity the loop will loop around a few times but I just don't see another solution. I was initially concerned that the loop in rename could let old renames update the file overwriting newer contents but I realize now that rename() itself has the same issue (an old rename could hit in the code after a newer rename) so in all cases we must already have the proper locking in place. -- 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 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] regression test failure in CVS HEAD
It seems the 'numeric' and 'int8' tests are failing in CVS HEAD. The culprit seems to be the recent to_char() change made by Karel, but I haven't verified that. The diff follows. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC *** ./expected/int8.out Fri Jan 26 17:50:26 2001 --- ./results/int8.out Fri Sep 20 12:37:25 2002 *** *** 245,256 SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! +--- ! | 456 ! | 4567890123456789 ! | 123 ! | 4567890123456789 ! | -4567890123456789 (5 rows) SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; --- 245,256 SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! + ! | 456. ! | 4567890123456789. ! | 123. ! | 4567890123456789. ! | -4567890123456789. (5 rows) SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; == *** ./expected/numeric.out Fri Apr 7 15:17:42 2000 --- ./results/numeric.out Fri Sep 20 12:37:36 2002 *** *** 785,792 | +7799461.4119 | +16397.038491 | +93901.57763026 !| -83028485 !| +74881 | -24926804.04504742 (10 rows) --- 785,792 | +7799461.4119 | +16397.038491 | +93901.57763026 !| -83028485. !| +74881. | -24926804.04504742 (10 rows) *** *** 800,807 | 7799461.4119 | 16397.038491 | 93901.57763026 !| 83028485 !| 74881 | 24926804.04504742 (10 rows) --- 800,807 | 7799461.4119 | 16397.038491 | 93901.57763026 !| 83028485. !| 74881. | 24926804.04504742 (10 rows) *** *** 860,867 | 07799461.4119 | 00016397.038491 | 00093901.57763026 ! | -83028485 ! | 00074881 | -24926804.04504742 (10 rows) --- 860,867 | 07799461.4119 | 00016397.038491 | 00093901.57763026 ! | -83028485. ! | 00074881. | -24926804.04504742 (10 rows) *** *** 950,957 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485 ! | 74881 | -24926804.04504742 (10 rows) --- 950,957 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485. ! | 74881. | -24926804.04504742 (10 rows) *** *** 980,987 | + 7 7 9 9 4 6 1 . 4 1 1 9 | + 1 6 3 9 7 . 0 3 8 4 9 1 | + 9 3 9 0 1 . 5 7 7 6 3 0 2 6 ! | -8 3 0 2 8 4 8 5 ! | + 7 4 8 8 1 | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 (10 rows) --- 980,987 | + 7 7 9 9 4 6 1 . 4 1 1 9 | + 1 6 3 9 7 . 0 3 8 4 9 1 | + 9 3 9 0 1 . 5 7 7 6 3 0 2 6 ! | -8 3 0 2 8 4 8 5 . ! | + 7 4 8 8 1 . | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 (10 rows) *** *** 1025,1032 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485 ! | 74881 | -24926804.04504742 (10 rows) --- 1025,1032 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485. ! | 74881. | -24926804.04504742 (10 rows) == ---(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] [GENERAL] Memory Errors...
Tom Lane writes: On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. There's a note in the PL/Python documentation that it's leaking memory if SPI plans are used. Maybe that's related and someone could take a look at it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 rename()/unlink() questions
I wrote: Stephan Szabo wrote: The question is, what happens if two people have the file open and one goes and tries to delete it? Can the other still read from it? Yes. I just tested it and it worked. I'll test Bruce's scenario as well: foo contains: FOO bar contains: BAR 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 calls MoveFile(foo, foo2); 4. Process 3 opens foo - Successful? 5. Process 1 calls MoveFile(bar, foo); 6. Process 4 opens foo - Successful? 7. Process 1 calls DeleteFile(foo2); 8. Process 1, 2, 3, 4 all read from their respective handles. Process 1: FOO Process 2: FOO Process 3: Error - File does not exist Process 4: BAR Its interesting in that it allows for Unix-style rename() and unlink() behavior, but with a race condition. Without Stephan's two MoveFile() trick and the FILE_SHARE_DELETE flag, however, the result would be Access Denied. Are the places in the backend that use rename() and unlink() renaming and unlinking files that are only opened for a brief moment by other backends? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] regression test failure in CVS HEAD
Tom has fixed it. Sorry I didn't test earlier. --- Neil Conway wrote: It seems the 'numeric' and 'int8' tests are failing in CVS HEAD. The culprit seems to be the recent to_char() change made by Karel, but I haven't verified that. The diff follows. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC *** ./expected/int8.out Fri Jan 26 17:50:26 2001 --- ./results/int8.outFri Sep 20 12:37:25 2002 *** *** 245,256 SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! +--- ! | 456 ! | 4567890123456789 ! | 123 ! | 4567890123456789 ! | -4567890123456789 (5 rows) SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; --- 245,256 SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! + ! | 456. ! | 4567890123456789. ! | 123. ! | 4567890123456789. ! | -4567890123456789. (5 rows) SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; == *** ./expected/numeric.outFri Apr 7 15:17:42 2000 --- ./results/numeric.out Fri Sep 20 12:37:36 2002 *** *** 785,792 | +7799461.4119 | +16397.038491 | +93901.57763026 !| -83028485 !| +74881 | -24926804.04504742 (10 rows) --- 785,792 | +7799461.4119 | +16397.038491 | +93901.57763026 !| -83028485. !| +74881. | -24926804.04504742 (10 rows) *** *** 800,807 | 7799461.4119 | 16397.038491 | 93901.57763026 !| 83028485 !| 74881 | 24926804.04504742 (10 rows) --- 800,807 | 7799461.4119 | 16397.038491 | 93901.57763026 !| 83028485. !| 74881. | 24926804.04504742 (10 rows) *** *** 860,867 | 07799461.4119 | 00016397.038491 | 00093901.57763026 ! | -83028485 ! | 00074881 | -24926804.04504742 (10 rows) --- 860,867 | 07799461.4119 | 00016397.038491 | 00093901.57763026 ! | -83028485. ! | 00074881. | -24926804.04504742 (10 rows) *** *** 950,957 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485 ! | 74881 | -24926804.04504742 (10 rows) --- 950,957 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485. ! | 74881. | -24926804.04504742 (10 rows) *** *** 980,987 | + 7 7 9 9 4 6 1 . 4 1 1 9 | + 1 6 3 9 7 . 0 3 8 4 9 1 | + 9 3 9 0 1 . 5 7 7 6 3 0 2 6 ! | -8 3 0 2 8 4 8 5 ! | + 7 4 8 8 1 | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 (10 rows) --- 980,987 | + 7 7 9 9 4 6 1 . 4 1 1 9 | + 1 6 3 9 7 . 0 3 8 4 9 1 | + 9 3 9 0 1 . 5 7 7 6 3 0 2 6 ! | -8 3 0 2 8 4 8 5 . ! | + 7 4 8 8 1 . | -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 (10 rows) *** *** 1025,1032 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485 ! | 74881 | -24926804.04504742 (10 rows) --- 1025,1032 | 7799461.4119 | 16397.038491 | 93901.57763026 ! | -83028485. ! | 74881. | -24926804.04504742 (10 rows) == ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Its interesting in that it allows for Unix-style rename() and unlink() behavior, but with a race condition. Without Stephan's two MoveFile() trick and the FILE_SHARE_DELETE flag, however, the result would be Access Denied. Are the places in the backend that use rename() and unlink() renaming and unlinking files that are only opened for a brief moment by other backends? Yes, those files are only opened for a brief moment. They are not held open. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: foo contains: FOO bar contains: BAR 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 calls MoveFile(foo, foo2); 4. Process 3 opens foo - Successful? 5. Process 1 calls MoveFile(bar, foo); 6. Process 4 opens foo - Successful? 7. Process 1 calls DeleteFile(foo2); 8. Process 1, 2, 3, 4 all read from their respective handles. Process 1: FOO Process 2: FOO Process 3: Error - File does not exist Process 4: BAR Its interesting in that it allows for Unix-style rename() and unlink() behavior, but with a race condition. Without Stephan's two MoveFile() trick and the FILE_SHARE_DELETE flag, however, the result would be Access Denied. Are the places in the backend that use rename() and unlink() renaming and unlinking files that are only opened for a brief moment by other backends? I think we are better off looping over MoveFileEx(MOVEFILE_REPLACE_EXISTING) until the file isn't opened by anyone. That localizes the changes to rename only and not out to all the opens. The open failure loops when the file isn't there seem much worse. I am a little concerned about starving the rename when there is a lot of activity but I don't see a better solution. -- 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 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] [GENERAL] Memory Errors...
I'll try to have a look-see by the end of the weekend. Any code that can reproduce it or is it ANY code that uses SPI? Greg On Fri, 2002-09-20 at 11:39, Peter Eisentraut wrote: Tom Lane writes: On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. There's a note in the PL/Python documentation that it's leaking memory if SPI plans are used. Maybe that's related and someone could take a look at it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster signature.asc Description: This is a digitally signed message part
Re: [HACKERS] PGXLOG variable worthwhile?
Thomas Lockhart wrote: ... Why you object to that, and insist it must be an environment variable instead (if that is indeed what you're doing), I'm not sure Well, what I was hoping for, but no longer expect, is that features (store xlog in another area) can be implemented and applied without rejection by the new gatekeepers. It is a feature that we do not have now, and could have implemented for 7.3. No need to rehash the points which were not understood in the discussion. I have no fundamental objection to extending and replacing implementation features as positive contributions to development. I do have trouble with folks rejecting features without understanding the issues, and sorry, there was a strong thread of why would anyone want to put storage on another device to the discussion. I believe the discussion was Why not use symlinks? I think we have addressed that issue with the GUC variable solution. Certainly we all recognize the value of moving storage to another drive. It is mentioned in the SGML docs and other places. In fact, I tried to open a dialog with you on this issue several times, but when I got no reply, I had to remove PGXLOG. If we had continued discussion, we might have come up with the GUC compromise. There has been a fundamental shift in the quality and civility of discussions over issues over the last couple of years, and I was naively hoping that we could work through that on this topic. Not happening, and not likely too. My impression is that things have been getting better in the past six months. There is more open discussion, and more voting, meaning one group isn't making all the decisions. I have worked to limit the sway of any new gatekeepers. People are encouraged to vote, and we normally accept that outcome. I think gatekeepers should sway only in the force of their arguments. Do you feel this was not followed on the PGXLOG case, or is the concept in error? I certainly have been frustrated when my features were not accepted, but I have to accept the vote of the group. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] timestamp parse error
Hello! i'm using PostgreSQL 7.2.1 and got strange parse errors.. could somebody tell me what's wrong with this timestamp query example? PostgreSQL said: ERROR: parser: parse error at or near date Your query: select timestamp(date '1998-02-24', time '23:07') example is from PostgreSQL help and certainly worked in previous versions of pgsql.. but in 7.2.1 it does not. had anything changed and not been updated in pgsql manuals or is it a bug? thanx for any help Tomas Lehuta ---(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] [GENERAL] timestamp parse error
On Fri, 20 Sep 2002, Tomas Lehuta wrote: Hello! i'm using PostgreSQL 7.2.1 and got strange parse errors.. could somebody tell me what's wrong with this timestamp query example? PostgreSQL said: ERROR: parser: parse error at or near date Your query: select timestamp(date '1998-02-24', time '23:07') example is from PostgreSQL help and certainly worked in previous versions of pgsql.. but in 7.2.1 it does not. had anything changed and not been updated in pgsql manuals or is it a bug? Presumably it's a manual example that didn't get changed. Timestamp(...) is now a specifier for the type with a given precision. You can use timestamp(date '1998-02-24', time '23:07') or datetime math (probably something like date '1998-02-24' + time '23:07' and possibly a cast) ---(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] [GENERAL] timestamp parse error
Tomas Lehuta [EMAIL PROTECTED] writes: could somebody tell me what's wrong with this timestamp query example? select timestamp(date '1998-02-24', time '23:07') PostgreSQL said: ERROR: parser: parse error at or near date example is from PostgreSQL help From where exactly? I don't see any such example in current sources. Although you could make this work by double-quoting the name timestamp (which is a reserved word now, per SQL spec), I'd recommend sidestepping the problem by using the equivalent + operator instead: regression=# select timestamp(date '1998-02-24', time '23:07'); timestamp - 1998-02-24 23:07:00 (1 row) regression=# select date '1998-02-24' + time '23:07'; ?column? - 1998-02-24 23:07:00 (1 row) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
