Re: [HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Alfred Perlstein <[EMAIL PROTECTED]> [001128 22:55]: > * Larry Rosenman <[EMAIL PROTECTED]> [001128 20:52] wrote: > > My offer stands for you as well, if you'd like an account > > on this P-III 600E, you are welcome to one... > > I just remebered my laptop in the other room, it's a pretty recent 4.2. > > I'll give it shot. > > Yes, it's possible to forget about a computer... >http://people.freebsd.org/~alfred/images/lab.jpg > > :) I've got to go to bed now, but the offer stands. If y'all need an account, peter e's got one already, and I can make more tomorrow. Good luck, all. LER > > -- > -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] > "I have the heart of a child; I keep it in a jar on my desk." -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Tom Lane <[EMAIL PROTECTED]> [001128 23:03]: > Larry Rosenman <[EMAIL PROTECTED]> writes: > >> We've called that routine s_lock for a *long* time, so it seems > >> like there must be some factor involved that I don't see just yet... > > > Didn't your commit message say something about the TAS and NON-TAS > > paths being the same now? > > Yeah, but don't tell me you were running on a non-TAS platform... > that stuff didn't work... The configure stuff used tas/dummy.s, so I'm not sure > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
Larry Rosenman <[EMAIL PROTECTED]> writes: >> We've called that routine s_lock for a *long* time, so it seems >> like there must be some factor involved that I don't see just yet... > Didn't your commit message say something about the TAS and NON-TAS > paths being the same now? Yeah, but don't tell me you were running on a non-TAS platform... that stuff didn't work... regards, tom lane
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Tom Lane <[EMAIL PROTECTED]> [001128 22:55]: > Larry Rosenman <[EMAIL PROTECTED]> writes: > >> Here is the "Current" /usr/include/machine/lock.h: > >> ... > >> void s_lock __P((struct simplelock *)); > >> ... > > Ick. Seems like the relevant question is not so much "why did it break" > as "how did it ever manage to work"? > > I have no problem with renaming our s_lock, if that's what it takes, > but I'm curious to know why there is a problem now and not before. > We've called that routine s_lock for a *long* time, so it seems > like there must be some factor involved that I don't see just yet... Didn't your commit message say something about the TAS and NON-TAS paths being the same now? > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
Larry Rosenman <[EMAIL PROTECTED]> writes: >> Here is the "Current" /usr/include/machine/lock.h: >> ... >> void s_lock __P((struct simplelock *)); >> ... Ick. Seems like the relevant question is not so much "why did it break" as "how did it ever manage to work"? I have no problem with renaming our s_lock, if that's what it takes, but I'm curious to know why there is a problem now and not before. We've called that routine s_lock for a *long* time, so it seems like there must be some factor involved that I don't see just yet... regards, tom lane
Re: [HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Larry Rosenman <[EMAIL PROTECTED]> [001128 20:52] wrote: > My offer stands for you as well, if you'd like an account > on this P-III 600E, you are welcome to one... I just remebered my laptop in the other room, it's a pretty recent 4.2. I'll give it shot. Yes, it's possible to forget about a computer... http://people.freebsd.org/~alfred/images/lab.jpg :) -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Initdb not running on beos
* Tom Lane <[EMAIL PROTECTED]> [001128 20:48] wrote: > Adam Haberlach <[EMAIL PROTECTED]> writes: > > On Mon, Nov 27, 2000 at 04:09:46PM -0500, Tom Lane wrote: > >> Somewhere right around here is where I am going to ask why we are > >> entertaining the idea of a BeOS port in the first place... it's > >> evidently not Unix or even trying hard to be close to Unix. > > > You've asked this before. > > > How does Windows manage to work? > > Objection! Point not in evidence! > > ;-) > > Seriously, we do not pretend to run on Windows. It does seem to be > possible to run Postgres atop Cygwin's Unix emulation atop Windows. > However, that's only because of some superhuman efforts from the > Cygwin team, not because Windows is a Postgres-compatible platform. > > As far as the original question goes, I suspect that a rename() would > work just as well as the link()/unlink() combo that's in that code now. > I would have no objection to a submitted patch along that line. But the > target audience for Postgres is POSIX-compatible platforms, and I do not > think that the core group of developers should be spending much time on > hacking the code to work on platforms that can't meet the POSIX spec. > If anyone else wants to make that happen, we'll accept patches ... but > don't expect us to supply solutions, OK? Afaik the atomicity of rename() (the same as a link()/unlink() pair) is specified by POSIX. Sorry for jumping in late in the thread, but rename() sure sounds a lot better than a link()/unlink() pair, but I'm probably taking it out of context. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
My offer stands for you as well, if you'd like an account on this P-III 600E, you are welcome to one... LER * Alfred Perlstein <[EMAIL PROTECTED]> [001128 22:46]: > * Larry Rosenman <[EMAIL PROTECTED]> [001128 20:44] wrote: > > * Tom Lane <[EMAIL PROTECTED]> [001128 22:31]: > > > Larry Rosenman <[EMAIL PROTECTED]> writes: > > > > The last batch of commits break on FreeBSD 4.2-STABLE. > > > > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > > > > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > > > > > > That's odd. s_lock has been declared the same way right along in our > > > code; I didn't change it. Can you see what's changed to cause a > > > conflict where there was none before? > > > > > > regards, tom lane > > Other things that may be an issue: > > > > 1) BINUTILS 2.10.1 > > 2) OPENSSL 0.9.6 > > > > both just MFC'd into FreeBSD recently, but I believe we built until > > tonite. > > > > I can make you an account on the box if you'd like > > My signifigant other just installed a fresh copy of 4.2 last night, > unfortunetly the poor box is only a 233mhz, it'll be a while before > we build -stable on it. > > However I'm confident I can have a fix within a couple of days. > > -- > -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] > "I have the heart of a child; I keep it in a jar on my desk." -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Larry Rosenman <[EMAIL PROTECTED]> [001128 20:44] wrote: > * Tom Lane <[EMAIL PROTECTED]> [001128 22:31]: > > Larry Rosenman <[EMAIL PROTECTED]> writes: > > > The last batch of commits break on FreeBSD 4.2-STABLE. > > > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > > > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > > > > That's odd. s_lock has been declared the same way right along in our > > code; I didn't change it. Can you see what's changed to cause a > > conflict where there was none before? > > > > regards, tom lane > Other things that may be an issue: > > 1) BINUTILS 2.10.1 > 2) OPENSSL 0.9.6 > > both just MFC'd into FreeBSD recently, but I believe we built until > tonite. > > I can make you an account on the box if you'd like My signifigant other just installed a fresh copy of 4.2 last night, unfortunetly the poor box is only a 233mhz, it'll be a while before we build -stable on it. However I'm confident I can have a fix within a couple of days. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
Re: [HACKERS] Initdb not running on beos
Adam Haberlach <[EMAIL PROTECTED]> writes: > On Mon, Nov 27, 2000 at 04:09:46PM -0500, Tom Lane wrote: >> Somewhere right around here is where I am going to ask why we are >> entertaining the idea of a BeOS port in the first place... it's >> evidently not Unix or even trying hard to be close to Unix. > You've asked this before. > How does Windows manage to work? Objection! Point not in evidence! ;-) Seriously, we do not pretend to run on Windows. It does seem to be possible to run Postgres atop Cygwin's Unix emulation atop Windows. However, that's only because of some superhuman efforts from the Cygwin team, not because Windows is a Postgres-compatible platform. As far as the original question goes, I suspect that a rename() would work just as well as the link()/unlink() combo that's in that code now. I would have no objection to a submitted patch along that line. But the target audience for Postgres is POSIX-compatible platforms, and I do not think that the core group of developers should be spending much time on hacking the code to work on platforms that can't meet the POSIX spec. If anyone else wants to make that happen, we'll accept patches ... but don't expect us to supply solutions, OK? regards, tom lane
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Tom Lane <[EMAIL PROTECTED]> [001128 22:31]: > Larry Rosenman <[EMAIL PROTECTED]> writes: > > The last batch of commits break on FreeBSD 4.2-STABLE. > > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > > That's odd. s_lock has been declared the same way right along in our > code; I didn't change it. Can you see what's changed to cause a > conflict where there was none before? > > regards, tom lane Other things that may be an issue: 1) BINUTILS 2.10.1 2) OPENSSL 0.9.6 both just MFC'd into FreeBSD recently, but I believe we built until tonite. I can make you an account on the box if you'd like LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Larry Rosenman <[EMAIL PROTECTED]> [001128 22:33]: > * Tom Lane <[EMAIL PROTECTED]> [001128 22:31]: > > Larry Rosenman <[EMAIL PROTECTED]> writes: > > > The last batch of commits break on FreeBSD 4.2-STABLE. > > > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > > > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > > > > That's odd. s_lock has been declared the same way right along in our > > code; I didn't change it. Can you see what's changed to cause a > > conflict where there was none before? > This maybe Matt Dillon's recent commit to FBSD then. Either way, it's > a problem on -STABLE 4.2 of FreeBSD. Nope, I just checked, and that hadn't changed either: $ ls -l sys/i386/include/lock.h sys/sys/lock.h -rw-r--r-- 1 root wheel 4981 Oct 3 21:43 sys/i386/include/lock.h -rw-r--r-- 1 root wheel 9365 Oct 3 21:43 sys/sys/lock.h $ ls -l /usr/include/machine/lock.h -r--r--r-- 1 root wheel 4981 Oct 4 00:24 /usr/include/machine/lock.h $ > > Here is the "Current" /usr/include/machine/lock.h: > > /* > * Copyright (c) 1997, by Steve Passe > * All rights reserved. > * > * Redistribution and use in source and binary forms, with or without > * modification, are permitted provided that the following conditions > * are met: > * 1. Redistributions of source code must retain the above copyright > *notice, this list of conditions and the following disclaimer. > * 2. The name of the developer may NOT be used to endorse or promote products > *derived from this software without specific prior written permission. > * > * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND > * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE > * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE > * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE > * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL > * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS > * OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) > * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT > * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY > * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF > * SUCH DAMAGE. > * > * $FreeBSD: src/sys/i386/include/lock.h,v 1.11.2.2 2000/09/30 02:49:34 ps Exp $ > */ > > > #ifndef _MACHINE_LOCK_H_ > #define _MACHINE_LOCK_H_ > > > #ifdef LOCORE > > #ifdef SMP > > #define MPLOCKEDlock ; > > /* > * Some handy macros to allow logical organization. > */ > > #define MP_LOCK call_get_mplock > > #define MP_TRYLOCK\ > pushl $_mp_lock ; /* GIANT_LOCK */\ > call_MPtrylock ;/* try to get lock */ \ > add $4, %esp > > #define MP_RELLOCK\ > movl$_mp_lock,%edx ;/* GIANT_LOCK */\ > call_MPrellock_edx > > /* > * Protects the IO APIC and apic_imen as a critical region. > */ > #define IMASK_LOCK\ > pushl $_imen_lock ; /* address of lock */ \ > call_s_lock ; /* MP-safe */ \ > addl$4, %esp > > #define IMASK_UNLOCK \ > movl$0, _imen_lock > > #else /* SMP */ > > #define MPLOCKED/* NOP */ > > #define MP_LOCK /* NOP */ > > #endif /* SMP */ > > #else /* LOCORE */ > > #ifdef SMP > > #include /** xxx_LOCK */ > > /* > * Locks regions protected in UP kernel via cli/sti. > */ > #ifdef USE_MPINTRLOCK > #define MPINTR_LOCK() s_lock(&mpintr_lock) > #define MPINTR_UNLOCK() s_unlock(&mpintr_lock) > #else > #define MPINTR_LOCK() > #define MPINTR_UNLOCK() > #endif /* USE_MPINTRLOCK */ > > /* > * sio/cy lock. > * XXX should rc (RISCom/8) use this? > */ > #ifdef USE_COMLOCK > #define COM_LOCK()s_lock(&com_lock) > #define COM_UNLOCK() s_unlock(&com_lock) > #define COM_DISABLE_INTR() \ > { __asm __volatile("cli" : : : "memory"); COM_LOCK(); } > #define COM_ENABLE_INTR() \ > { COM_UNLOCK(); __asm __volatile("sti"); } > #else > #define COM_LOCK() > #define COM_UNLOCK() > #define COM_DISABLE_INTR()disable_intr() > #define COM_ENABLE_INTR() enable_intr() > #endif /* USE_COMLOCK */ > > /* > * Clock hardware/struct lock. > * XXX pcaudio and friends still need this lock installed. > */ > #ifdef USE_CLOCKLOCK > #define CLOCK_LOCK() s_lock(&clock_lock) > #define CLOCK_UNLOCK()s_unlock(&clock_lock) > #define CLOCK_DISABLE_INTR() \ >
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
* Tom Lane <[EMAIL PROTECTED]> [001128 22:31]: > Larry Rosenman <[EMAIL PROTECTED]> writes: > > The last batch of commits break on FreeBSD 4.2-STABLE. > > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > > That's odd. s_lock has been declared the same way right along in our > code; I didn't change it. Can you see what's changed to cause a > conflict where there was none before? This maybe Matt Dillon's recent commit to FBSD then. Either way, it's a problem on -STABLE 4.2 of FreeBSD. Here is the "Current" /usr/include/machine/lock.h: /* * Copyright (c) 1997, by Steve Passe * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * 1. Redistributions of source code must retain the above copyright *notice, this list of conditions and the following disclaimer. * 2. The name of the developer may NOT be used to endorse or promote products *derived from this software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS * OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF * SUCH DAMAGE. * * $FreeBSD: src/sys/i386/include/lock.h,v 1.11.2.2 2000/09/30 02:49:34 ps Exp $ */ #ifndef _MACHINE_LOCK_H_ #define _MACHINE_LOCK_H_ #ifdef LOCORE #ifdef SMP #define MPLOCKEDlock ; /* * Some handy macros to allow logical organization. */ #define MP_LOCK call_get_mplock #define MP_TRYLOCK \ pushl $_mp_lock ; /* GIANT_LOCK */\ call_MPtrylock ;/* try to get lock */ \ add $4, %esp #define MP_RELLOCK \ movl$_mp_lock,%edx ;/* GIANT_LOCK */\ call_MPrellock_edx /* * Protects the IO APIC and apic_imen as a critical region. */ #define IMASK_LOCK \ pushl $_imen_lock ; /* address of lock */ \ call_s_lock ; /* MP-safe */ \ addl$4, %esp #define IMASK_UNLOCK\ movl$0, _imen_lock #else /* SMP */ #define MPLOCKED/* NOP */ #define MP_LOCK /* NOP */ #endif /* SMP */ #else /* LOCORE */ #ifdef SMP #include/** xxx_LOCK */ /* * Locks regions protected in UP kernel via cli/sti. */ #ifdef USE_MPINTRLOCK #define MPINTR_LOCK() s_lock(&mpintr_lock) #define MPINTR_UNLOCK() s_unlock(&mpintr_lock) #else #define MPINTR_LOCK() #define MPINTR_UNLOCK() #endif /* USE_MPINTRLOCK */ /* * sio/cy lock. * XXX should rc (RISCom/8) use this? */ #ifdef USE_COMLOCK #define COM_LOCK() s_lock(&com_lock) #define COM_UNLOCK()s_unlock(&com_lock) #define COM_DISABLE_INTR() \ { __asm __volatile("cli" : : : "memory"); COM_LOCK(); } #define COM_ENABLE_INTR() \ { COM_UNLOCK(); __asm __volatile("sti"); } #else #define COM_LOCK() #define COM_UNLOCK() #define COM_DISABLE_INTR() disable_intr() #define COM_ENABLE_INTR() enable_intr() #endif /* USE_COMLOCK */ /* * Clock hardware/struct lock. * XXX pcaudio and friends still need this lock installed. */ #ifdef USE_CLOCKLOCK #define CLOCK_LOCK()s_lock(&clock_lock) #define CLOCK_UNLOCK() s_unlock(&clock_lock) #define CLOCK_DISABLE_INTR() \ { __asm __volatile("cli" : : : "memory"); CLOCK_LOCK(); } #define CLOCK_ENABLE_INTR() \ { CLOCK_UNLOCK(); __asm __volatile("sti"); } #else #define CLOCK_LOCK() #define CLOCK_UNLOCK() #define CLOCK_DISABLE_INTR()disable_intr() #define CLOCK_ENABLE_INTR() enable_intr() #endif /* USE_CLOCKLOCK */ #else /* SMP */ #define MPINTR_LOCK() #define MPINTR_UNLOCK() #define COM_LOCK() #define COM_UNLOCK() #define CLOCK_LOCK() #define CLOCK_UNLOCK() #endif /* SMP */ /* * Simple spin lock. * It is an error to hold one of these locks while a process is sleeping. */ struct simplelock { volatile intlock_data; }; /*
[HACKERS] Re: LOCK Fixes/Break on FreeBSD 4.2-STABLE
Larry Rosenman <[EMAIL PROTECTED]> writes: > The last batch of commits break on FreeBSD 4.2-STABLE. > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' That's odd. s_lock has been declared the same way right along in our code; I didn't change it. Can you see what's changed to cause a conflict where there was none before? regards, tom lane
Re: [HACKERS] Initdb not running on beos
On Mon, Nov 27, 2000 at 04:09:46PM -0500, Tom Lane wrote: > Cyril VELTER <[EMAIL PROTECTED]> writes: > > Unfortunatly, there is no hard link on beos :=(. link and unlink are > > there, but link always return "No such file or directory". > > Somewhere right around here is where I am going to ask why we are > entertaining the idea of a BeOS port in the first place... it's > evidently not Unix or even trying hard to be close to Unix. You've asked this before. How does Windows manage to work? -- Adam Haberlach |"California's the big burrito, Texas is the big [EMAIL PROTECTED] | taco ... and following that theme, Florida is http://www.newsnipple.com| the big tamale ... and the only tamale that '88 EX500| counts any more." -- Dan Rather
Re: [HACKERS] Problem in AlterTableAddConstraint?
Assuming the silence is agreement, does this look like the right solution (I assume looping through the index is the only way to count the segments): if (indexStruct->indisunique) { List *attrl; /* go through the fkconstraint->pk_attrs list */ foreach(attrl, fkconstraint->pk_attrs) { Ident *attr=lfirst(attrl); found = false; for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++) { int pkattno = indexStruct->indkey[i]; if (pkattno>0) { char *name = NameStr(rel_attrs[pkattno-1]->attname); if (strcmp(name, attr->name)==0) { found = true; break; } } } if (!found) + { break; + } else { + + /* Require same number of segments */ + if (i != length(fkconstraint->pk_attrs)) + { + found = false; + break; + } + } } } At 02:49 29/11/00 +1100, Philip Warner wrote: > >create table c2(f1 integer, f2 integer, unique(f1,f2)); >create table c1(f1 integer, f2 integer, >foreign key(f1) references c2(f1)); > > is allowed with current sources. > Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] beta testing version
On Wed, Nov 29, 2000 at 09:59:34AM +0800, xuyifeng wrote: > NO, I just tested how solid PgSQL is, I run a program busy inserting > record into PG table, when I suddenly pulled out power from my machine ... Nobody claims PostgreSQL is proof against power failures. > ... We use WindowsNT and MSSQL on our production server, > before we accept MSSQL, we use this method to test if MSSQL can endure > this kind of strike, it's OK, all databases are safely recovered, we > can continue our work. You got lucky. Period. MSSQL is not proof against power failures, and neither is NTFS. In particular, that the database accepted transactions afterward is far from proof that its files were not corrupted. Incompetent testers produce invalid tests. Invalid tests lead to meaningless conclusions. Incompetent testers' employers suffer from false confidence, and poor decision-making. Nathan Myers [EMAIL PROTECTED]
[Fwd: Re: [HACKERS] 8192 BLCKSZ ?]
Tom Samplonius wrote: > On Tue, 28 Nov 2000, mlw wrote: > > > Tom Samplonius wrote: > > > > > > On Mon, 27 Nov 2000, mlw wrote: > > > > > > > This is just a curiosity. > > > > > > > > Why is the default postgres block size 8192? These days, with caching > > > > file systems, high speed DMA disks, hundreds of megabytes of RAM, maybe > > > > even gigabytes. Surely, 8K is inefficient. > > > > > > I think it is a pretty wild assumption to say that 32k is more efficient > > > than 8k. Considering how blocks are used, 32k may be in fact quite a bit > > > slower than 8k blocks. > > > > I'm not so sure I agree. Perhaps I am off base here, but I did a bit of > > OS profiling a while back when I was doing a DICOM server. I > > experimented with block sizes and found that the best throughput on > > Linux and Windows NT was at 32K. The graph I created showed a steady > > increase in performance and a drop just after 32K, then steady from > > there. In Windows NT it was more pronounced than it was in Linux, but > > Linux still exhibited a similar trait. > > You are a bit off base here. The typical access pattern is random IO, > not sequentional. If you use a large block size in Postgres, Postgres > will read and write more data than necessary. Which is faster? 1000 x 8K > IOs? Or 1000 x 32K IOs I can sort of see your point, but the 8K vs 32K is not a linear relationship. The big hit is the disk I/O operation, more so than just the data size. It may be almost as efficient to write 32K as it is to write 8K. While I do not know the exact numbers, and it varies by OS and disk subsystem, I am sure that writing 32K is not even close to 4x more expensive than 8K. Think about seek times, writing anything to the disk is expensive regardless of the amount of data. Most disks today have many heads, and are RL encoded. It may only add 10us (approx. 1-2 sectors of a 64 sector drive spinning 7200 rpm) to a disk operation which takes an order of magnitude longer positioning the heads. The overhead of an additional 24K is minute compared to the cost of a disk operation. So if any measurable benefit can come from having bigger buffers, i.e. having more data available per disk operation, it will probably be faster.
Re: [HACKERS] beta testing version
NO, I just tested how solid PgSQL is, I run a program busy inserting record into PG table, when I suddenly pulled out power from my machine and restarted PG, I can not insert any record into database table, all backends are dead without any respone (not core dump), note that I am using FreeBSD 4.2, it's rock solid, it's not OS crash, it just losted power. We use WindowsNT and MSSQL on our production server, before we accept MSSQL, we use this method to test if MSSQL can endure this kind of strik, it's OK, all databases are safely recovered, we can continue our work. we are a stock exchange company, our server are storing millilion $ finance number, we don't hope there are any problems in this case, we are using UPS, but UPS is not everything, it you bet everything on UPS, you must be idiot. I know you must be an avocation of PG, but we are professional customer, corporation user, we store critical data into database, not your garbage data. Regards, XuYifeng - Original Message - From: Don Baccus <[EMAIL PROTECTED]> To: Ron Chmara <[EMAIL PROTECTED]>; Mitch Vincent <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, November 29, 2000 6:58 AM Subject: Re: [HACKERS] beta testing version > At 03:25 PM 11/28/00 -0700, Ron Chmara wrote: > >Mitch Vincent wrote: > >> > >> This is one of the not-so-stomped boxes running PostgreSQL -- I've never > >> restarted PostgreSQL on it since it was installed. > >> 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 > >> I had some index corruption problems in 6.5.3 but since 7.0.X I haven't > >> heard so much as a peep from any PostgreSQL backend. It's superbly stable on > >> all my machines.. > > > >I have a 6.5.x box at 328 days of active use. > > > >Crash "recovery" seems silly to me. :-) > > Well, not really ... but since our troll is a devoted MySQL user, it's a bit > of a red-herring anyway, at least as regards his own server. > > You know, the one he's afraid to put Postgres on, but sleeps soundly at > night knowing the mighty bullet-proof MySQL with its full transaction > semantics, archive logging and recovery from REDO logs and all that > will save him? :) > > Again ... he's a troll, not even a very entertaining one. > > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. >
Re: [HACKERS] LOCK Fixes/Break on FreeBSD 4.2-STABLE
BTW, it compiles fine on UnixWare 7.1.1 * Larry Rosenman <[EMAIL PROTECTED]> [001128 19:36]: > The last batch of commits break on FreeBSD 4.2-STABLE. > $ uname -a > FreeBSD lerbsd.lerctr.org 4.2-STABLE FreeBSD 4.2-STABLE #90: Tue Nov > 28 04:07:50 CST 2000 > [EMAIL PROTECTED]:/usr/src/sys/compile/LERBSD i386 > $ > > Configure: > > ./configure --prefix=/home/ler/pg-test --enable-syslog \ > --with-CXX --with-perl --enable-multibyte --enable-cassert \ > --with-openssl \ > --with-includes="/usr/local/include/tcl8.3 /usr/local/include/tk8.3" \ > --with-tcl \ > --with-tclconfig=/usr/local/lib/tcl8.3 \ > --with-tkconfig=/usr/local/lib/tk8.3 > > > Last 50 lines of make output: > > ranlib libplpgsql.a > /usr/libexec/elf/ld -x -shared -soname libplpgsql.so.1 -o libplpgsql.so.1 pl_parse.o >pl_handler.o pl_comp.o pl_exec.o pl_funcs.o -R/home/ler/pg-test/lib > rm -f libplpgsql.so > ln -s libplpgsql.so.1 libplpgsql.so > gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plpgsql/src' > gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plpgsql' > gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/tcl' > /bin/sh mkMakefile.tcldefs.sh '/usr/local/lib/tcl8.3/tclConfig.sh' 'Makefile.tcldefs' > gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/tcl' > gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/tcl' > cc -O -fPIC -I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 >-I../../../src/include -DHAVE_UNISTD_H=1 -DHAVE_LIMITS_H=1 -DHAVE_GETCWD=1 >-DHAVE_OPENDIR=1 -DHAVE_STRSTR=1 -DHAVE_STRTOL=1 -DHAVE_TMPNAM=1 -DHAVE_WAITPID=1 >-DNO_VALUES_H=1 -DHAVE_UNISTD_H=1 -DHAVE_SYS_PARAM_H=1 -DUSE_TERMIOS=1 >-DHAVE_SYS_TIME_H=1 -DTIME_WITH_SYS_TIME=1 -DHAVE_TM_ZONE=1 -DHAVE_TM_GMTOFF=1 >-DHAVE_ST_BLKSIZE=1 -DSTDC_HEADERS=1 -DNEED_MATHERR=1 -DHAVE_SIGNED_CHAR=1 >-DHAVE_SYS_IOCTL_H=1 -DHAVE_SYS_FILIO_H=1 -c -o pltcl.o pltcl.c > ld -Bshareable -x -o pltcl.so pltcl.o -L/usr/local/lib -ltcl83 > rm pltcl.o > gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/tcl' > gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/plperl' > plperl_installdir='/home/ler/pg-test/lib' \ > EXTRA_INCLUDES='-I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 >-I../../../src/include' \ > perl Makefile.PL > Writing Makefile for plperl > gmake -f Makefile all > gmake[4]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/plperl' > mkdir blib > mkdir blib/lib > mkdir blib/arch > mkdir blib/arch/auto > mkdir blib/arch/auto/plperl > mkdir blib/lib/auto > mkdir blib/lib/auto/plperl > cc -c -I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 -I../../../src/include >-DVERSION=\"0.10\" -DXS_VERSION=\"0.10\" -DPIC -fpic >-I/usr/libdata/perl/5.00503/mach/CORE plperl.c > In file included from plperl.c:80: > /usr/libdata/perl/5.00503/mach/CORE/perl.h:1483: warning: `DEBUG' redefined > ../../../src/include/utils/elog.h:22: warning: this is the location of the previous >definition > In file included from /usr/include/sys/lock.h:45, > from /usr/include/sys/mount.h:49, > from /usr/libdata/perl/5.00503/mach/CORE/perl.h:376, > from plperl.c:80: > /usr/include/machine/lock.h:148: conflicting types for `s_lock' > ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' > gmake[4]: *** [plperl.o] Error 1 > gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plperl' > gmake[3]: *** [all] Error 2 > gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plperl' > gmake[2]: *** [all] Error 2 > gmake[2]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl' > gmake[1]: *** [all] Error 2 > gmake[1]: Leaving directory `/home/ler/pg-dev/pgsql/src' > gmake: *** [all] Error 2 > $ ^D > > Script done on Tue Nov 28 19:32:31 2000 > BTW: this is the same configure I was using after Peter_E fixed the > TCL / --with-includes stuff. > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] > US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] LOCK Fixes/Break on FreeBSD 4.2-STABLE
The last batch of commits break on FreeBSD 4.2-STABLE. $ uname -a FreeBSD lerbsd.lerctr.org 4.2-STABLE FreeBSD 4.2-STABLE #90: Tue Nov 28 04:07:50 CST 2000 [EMAIL PROTECTED]:/usr/src/sys/compile/LERBSD i386 $ Configure: ./configure --prefix=/home/ler/pg-test --enable-syslog \ --with-CXX --with-perl --enable-multibyte --enable-cassert \ --with-openssl \ --with-includes="/usr/local/include/tcl8.3 /usr/local/include/tk8.3" \ --with-tcl \ --with-tclconfig=/usr/local/lib/tcl8.3 \ --with-tkconfig=/usr/local/lib/tk8.3 Last 50 lines of make output: ranlib libplpgsql.a /usr/libexec/elf/ld -x -shared -soname libplpgsql.so.1 -o libplpgsql.so.1 pl_parse.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o -R/home/ler/pg-test/lib rm -f libplpgsql.so ln -s libplpgsql.so.1 libplpgsql.so gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plpgsql/src' gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plpgsql' gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/tcl' /bin/sh mkMakefile.tcldefs.sh '/usr/local/lib/tcl8.3/tclConfig.sh' 'Makefile.tcldefs' gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/tcl' gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/tcl' cc -O -fPIC -I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 -I../../../src/include -DHAVE_UNISTD_H=1 -DHAVE_LIMITS_H=1 -DHAVE_GETCWD=1 -DHAVE_OPENDIR=1 -DHAVE_STRSTR=1 -DHAVE_STRTOL=1 -DHAVE_TMPNAM=1 -DHAVE_WAITPID=1 -DNO_VALUES_H=1 -DHAVE_UNISTD_H=1 -DHAVE_SYS_PARAM_H=1 -DUSE_TERMIOS=1 -DHAVE_SYS_TIME_H=1 -DTIME_WITH_SYS_TIME=1 -DHAVE_TM_ZONE=1 -DHAVE_TM_GMTOFF=1 -DHAVE_ST_BLKSIZE=1 -DSTDC_HEADERS=1 -DNEED_MATHERR=1 -DHAVE_SIGNED_CHAR=1 -DHAVE_SYS_IOCTL_H=1 -DHAVE_SYS_FILIO_H=1 -c -o pltcl.o pltcl.c ld -Bshareable -x -o pltcl.so pltcl.o -L/usr/local/lib -ltcl83 rm pltcl.o gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/tcl' gmake[3]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/plperl' plperl_installdir='/home/ler/pg-test/lib' \ EXTRA_INCLUDES='-I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 -I../../../src/include' \ perl Makefile.PL Writing Makefile for plperl gmake -f Makefile all gmake[4]: Entering directory `/home/ler/pg-dev/pgsql/src/pl/plperl' mkdir blib mkdir blib/lib mkdir blib/arch mkdir blib/arch/auto mkdir blib/arch/auto/plperl mkdir blib/lib/auto mkdir blib/lib/auto/plperl cc -c -I/usr/local/include/tcl8.3 -I/usr/local/include/tk8.3 -I../../../src/include -DVERSION=\"0.10\" -DXS_VERSION=\"0.10\" -DPIC -fpic -I/usr/libdata/perl/5.00503/mach/CORE plperl.c In file included from plperl.c:80: /usr/libdata/perl/5.00503/mach/CORE/perl.h:1483: warning: `DEBUG' redefined ../../../src/include/utils/elog.h:22: warning: this is the location of the previous definition In file included from /usr/include/sys/lock.h:45, from /usr/include/sys/mount.h:49, from /usr/libdata/perl/5.00503/mach/CORE/perl.h:376, from plperl.c:80: /usr/include/machine/lock.h:148: conflicting types for `s_lock' ../../../src/include/storage/s_lock.h:402: previous declaration of `s_lock' gmake[4]: *** [plperl.o] Error 1 gmake[4]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plperl' gmake[3]: *** [all] Error 2 gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl/plperl' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/home/ler/pg-dev/pgsql/src/pl' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/home/ler/pg-dev/pgsql/src' gmake: *** [all] Error 2 $ ^D Script done on Tue Nov 28 19:32:31 2000 BTW: this is the same configure I was using after Peter_E fixed the TCL / --with-includes stuff. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] F_SETLK is looking worse and worse...
While testing interlocking of multiple postmasters, I discovered that the HAVE_FCNTL_SETLK interlock code we have in StreamServerPort() does not work at all on HPUX 10.20. This platform has F_SETLK according to configure, but: 1. The lock is never applied to a socket, because the open() on the newly-created socket (at line 303 of pqcomm.c) fails with EOPNOTSUPP, Operation not supported. 2. If a postmaster finds a socket file in its way, it is unable to remove it despite the lack of any lock, because the open() at line 230 fails with EADDRINUSE, Address already in use. I have no idea whether the fcntl(F_SETLK) call would succeed if control did get to it, but these results don't leave me very hopeful. Between this and the already-known result that F_SETLK doesn't work on sockets in shipping Linux kernels, I'm pretty unimpressed with the usefulness of this interlock method. We talked before about flushing the F_SETLK technique and using good old interlock files containing PIDs, same method that we use for interlocking the data directory. That is, if the socket file name is /tmp/.s.PGSQL.5432, we'd create a plain file /tmp/.s.PGSQL.5432.lock containing the owning process's PID. The code would insist on getting this interlock file first, and if successful would just unconditionally remove any existing socket file before doing the bind(). I can only think of one scenario where this is worse than what we have now: if someone is running a /tmp-directory-sweeper that is bright enough not to remove socket files, it would still zap the interlock file, thus potentially allowing a second postmaster to take over the socket file. This doesn't seem like a mainstream problem though. BTW, it also seems like a good idea to reorder the postmaster's startup operations so that the data-directory lockfile is checked before trying to acquire the port lockfile, instead of after. That way, in the common scenario where you're trying to start a second postmaster in the same directory + same port, it'd fail cleanly even if /tmp/.s.PGSQL.5432.lock had disappeared. Comments? regards, tom lane
Re: [HACKERS] beta testing version
On Tue, 28 Nov 2000, Don Baccus wrote: > At 03:25 PM 11/28/00 -0700, Ron Chmara wrote: > >Mitch Vincent wrote: > >> > >> This is one of the not-so-stomped boxes running PostgreSQL -- I've never > >> restarted PostgreSQL on it since it was installed. > >> 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 > >> I had some index corruption problems in 6.5.3 but since 7.0.X I haven't > >> heard so much as a peep from any PostgreSQL backend. It's superbly stable on > >> all my machines.. > > > >I have a 6.5.x box at 328 days of active use. > > > >Crash "recovery" seems silly to me. :-) > > Well, not really ... but since our troll is a devoted MySQL user, it's a bit > of a red-herring anyway, at least as regards his own server. > > You know, the one he's afraid to put Postgres on, but sleeps soundly at > night knowing the mighty bullet-proof MySQL with its full transaction > semantics, archive logging and recovery from REDO logs and all that > will save him? :) > > Again ... he's a troll, not even a very entertaining one. Or informed?
Re: [HACKERS] beta testing version
At 03:25 PM 11/28/00 -0700, Ron Chmara wrote: >Mitch Vincent wrote: >> >> This is one of the not-so-stomped boxes running PostgreSQL -- I've never >> restarted PostgreSQL on it since it was installed. >> 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 >> I had some index corruption problems in 6.5.3 but since 7.0.X I haven't >> heard so much as a peep from any PostgreSQL backend. It's superbly stable on >> all my machines.. > >I have a 6.5.x box at 328 days of active use. > >Crash "recovery" seems silly to me. :-) Well, not really ... but since our troll is a devoted MySQL user, it's a bit of a red-herring anyway, at least as regards his own server. You know, the one he's afraid to put Postgres on, but sleeps soundly at night knowing the mighty bullet-proof MySQL with its full transaction semantics, archive logging and recovery from REDO logs and all that will save him? :) Again ... he's a troll, not even a very entertaining one. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
Mitch Vincent wrote: > > This is one of the not-so-stomped boxes running PostgreSQL -- I've never > restarted PostgreSQL on it since it was installed. > 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 > I had some index corruption problems in 6.5.3 but since 7.0.X I haven't > heard so much as a peep from any PostgreSQL backend. It's superbly stable on > all my machines.. I have a 6.5.x box at 328 days of active use. Crash "recovery" seems silly to me. :-) -Bop -- Brought to you from boop!, the dual boot Linux/Win95 Compaq Presario 1625 laptop, currently running RedHat 6.1. Your bopping may vary.
Re: [HACKERS] 8192 BLCKSZ ?
On Tue, Nov 28, 2000 at 04:24:34PM -0500, Tom Lane wrote: > Nathan Myers <[EMAIL PROTECTED]> writes: > > In the event of a power outage, the drive will stop writing in > > mid-sector. > > Really? Any competent drive firmware designer would've made sure that > can't happen. The drive has to detect power loss well before it > actually loses control of its actuators, because it's got to move > the heads to the safe landing zone. If it checks for power loss and > starts that shutdown process between sector writes, never in the middle > of one, voila: atomic writes. I used to think that way too, because that's how I would design a drive. (Anyway that would still only give you 512-byte-atomic writes, which isn't enough.) Talking to people who build them was a rude awakening. They have apparatus to yank the head off the drive and lock it away when the power starts to go down, and it will happily operate in mid-write. (It's possible that some drives are made the way Tom describes, but evidently not the commodity stuff.) The level of software-development competence, and of reliability engineering, that I've seen among disk drive firmware maintainers distresses me whenever I think about it. A disk drive is best considered as throwaway cache image of your real medium. > Of course, there's still no guarantee if you get a hardware failure > or sector write failure (recovery from the write failure might well > take longer than the drive has got). But guarding against a plain > power-failure scenario is actually simpler than doing it the wrong > way. If only the disk-drive vendors (and buyers!) thought that way... Nathan Myers [EMAIL PROTECTED]
Re: [HACKERS] 8192 BLCKSZ ?
Nathan Myers <[EMAIL PROTECTED]> writes: > In the event of a power outage, the drive will stop writing in > mid-sector. Really? Any competent drive firmware designer would've made sure that can't happen. The drive has to detect power loss well before it actually loses control of its actuators, because it's got to move the heads to the safe landing zone. If it checks for power loss and starts that shutdown process between sector writes, never in the middle of one, voila: atomic writes. Of course, there's still no guarantee if you get a hardware failure or sector write failure (recovery from the write failure might well take longer than the drive has got). But guarding against a plain power-failure scenario is actually simpler than doing it the wrong way. But, as you say, customary page sizes are bigger than a sector, so this is all moot for our purposes anyway :-( regards, tom lane
Re: [HACKERS] 8192 BLCKSZ ?
On Tue, Nov 28, 2000 at 12:38:37AM -0500, Tom Lane wrote: > "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > I don't believe it's a performance issue, I believe it's that writes to > > blocks greater than 8k cannot be guaranteed 'atomic' by the operating > > system. Hence, 32k blocks would break the transactions system. > > As Nathan remarks nearby, it's hard to tell how big a write can be > assumed atomic, unless you have considerable knowledge of your OS and > hardware. Not to harp on the subject, but even if you _do_ know a great deal about your OS and hardware, you _still_ can't assume any write is atomic. To give an idea of what is involved, consider that modern disk drives routinely re-order writes, by themselves. You think you have asked for a sequential write of 8K bytes, or 16 sectors, but the disk might write the first and last sectors first, and then the middle sectors in random order. A block of all zeroes might not be written at all, but just noted in the track metadata. Most disks have a "feature" that they report the write complete as soon as it is in the RAM cache, rather than after the sectors are on the disk. (It's a "feature" because it makes their benchmarks come out better.) It can usually be turned off, but different vendors have different ways to do it. Have you turned it off on your production drives? In the event of a power outage, the drive will stop writing in mid-sector. If you're lucky, that sector would have a bad checksum if you tried to read it. If the half-written sector happens to contain track metadata, you might have a bigger problem. The short summary is: for power outage or OS-crash recovery purposes, there is no such thing as atomicity. This is why backups and transaction logs are important. "Invest in a UPS." Use a reliable OS, and operate it in a way that doesn't stress it. Even a well-built OS will behave oddly when resources are badly stressed. (That the oddities may be documented doesn't really help much.) For performance purposes, it may be more or less efficient to group writes into 4K, 8K, or 32K chunks. That's not a matter of database atomicity, but of I/O optimization. It can only confuse people to use "atomicity" in that context. Nathan Myers [EMAIL PROTECTED]
Re: [HACKERS] 8192 BLCKSZ ?
On Tue, Nov 28, 2000 at 12:38:37AM -0500, Tom Lane wrote: > Not sure about the wild-and-wooly world of Linux filesystems... > anybody know what the allocation unit is on the popular Linux FSes? It rather depends on the filesystem. Current ext2 (the most common) systems default to 1K on small partitions and 4K otherwise. IIRC, reiserfs uses 4K blocks in a tree structure that includes tail merging which makes the question of block size tricky. Linux 2.3.x passes all file I/O through its page cache, which deals in 4K pages on most 32-bit architectures. -- Bruce Guenter <[EMAIL PROTECTED]> http://em.ca/~bruceg/ PGP signature
Re: [HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
> > To fix this you simply need to double-quote "overlaps" when it's used as a > > straight function call. See how substring does it in pg_proc.h. > Hmm. Why was this required for the substring() example? afaik all of > this should be handled (correctly) in the grammar... I see it now. Will look at it... - Thomas
Re: [HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
> To fix this you simply need to double-quote "overlaps" when it's used as a > straight function call. See how substring does it in pg_proc.h. Hmm. Why was this required for the substring() example? afaik all of this should be handled (correctly) in the grammar... - Thomas
Re: [GENERAL] Warning: Don't delete those /tmp/.PGSQL.* files
On 25 Nov 2000, at 17:35, Tom Lane wrote: > > So, I began restarting pgsql w/a line like > > > rm -f /tmp/.PGSQL.* && postmaster -i >log 2>log & > > > Which works great. Except that I *kept* using this for two weeks > > after the view problem (damn that bash up-arrow laziness!), and > > yesterday, used it to restart PostgreSQL except (oops!) it was > > already running. > > > Results: no database at all. All classes (tables/views/etc) returned > > 0 records (meaning that no tables showed up in psql's \d, since > > pg_class returned nothing.) > > Ugh. The reason that removing the socket file allowed a second > postmaster to start up is that we use an advisory lock on the socket > file as the interlock that prevents two PMs on the same port number. > Remove the socket file, poof no interlock. > > *However*, there is a second line of defense to prevent two > postmasters in the same directory, and I don't understand why that > didn't trigger. Unless you are running a version old enough to not > have it. What PG version is this, anyway? 7.1devel, from about 1 week ago. > Assuming you got past both interlocks, the second postmaster would > have reinitialized Postgres' shared memory block for that database, > which would have been a Bad Thing(tm) ... but it would not have led to > any immediate damage to your on-disk files, AFAICS. Was the database > still hosed after you stopped both postmasters and started a fresh > one? (Did you even try that?) Yes, I stopped both, rebooted machine, restarted postmaster. Rebooted machine, used just postgres, tried to vacuum, tried to dump, etc. Always the same story. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> > So, having _both_ is the best thing. > > Absolutely, that's always what I meant -- we already have views and views > can do this type of stuff at SELECT time can't they? So it's not a change, > just an addition And the precalculated and stored on disk thing can be done with triggers. Andreas
Re: [HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
Tom Lane writes: > template1=# select ('today', interval '1 day') OVERLAPS ('yesterday', interval > '18 hours'); > ERROR: parser: parse error at or near "overlaps" > > I don't understand why we're getting a parse error here ... The OVERLAPS special SQL-construct is converted into the 'select overlaps(...)' function call, which isn't allowed because OVERLAPS is a keyword. *That* is where the parse error is coming from. To fix this you simply need to double-quote "overlaps" when it's used as a straight function call. See how substring does it in pg_proc.h. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] beta testing version
This is one of the not-so-stomped boxes running PostgreSQL -- I've never restarted PostgreSQL on it since it was installed. 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 I had some index corruption problems in 6.5.3 but since 7.0.X I haven't heard so much as a peep from any PostgreSQL backend. It's superbly stable on all my machines.. Damn good work guys. -Mitch - Original Message - From: "The Hermit Hacker" <[EMAIL PROTECTED]> To: "Hannu Krosing" <[EMAIL PROTECTED]> Cc: "xuyifeng" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Don Baccus" <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 8:53 AM Subject: Re: [HACKERS] beta testing version > On Tue, 28 Nov 2000, Hannu Krosing wrote: > > > xuyifeng wrote: > > > > > > > I just noticed this conversation so I have not followed all of it, > > but you seem to have strange priorities > > > > > I just want PG can be improved quickly, for me crash recover is very urgent problem, > > > > Crash avoidance is usually much more urgent, at least on production > > servers. > > Good call, but I kinda jumped to the conclusion that since PgSQL itself > isn't that crash prone, its his OS or his hardware that was the problem :0 > > >
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
> So, having _both_ is the best thing. Absolutely, that's always what I meant -- we already have views and views can do this type of stuff at SELECT time can't they? So it's not a change, just an addition -Mitch
[HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
Tom Lane wrote: > > Thomas Lockhart <[EMAIL PROTECTED]> writes: > >> I see it does fail, but I'm at a complete loss to understand why, > >> especially given that the first case still works. The grammar looks > >> perfectly fine AFAICT. Can you explain what's wrong here? > > > Here is what I'm planning on doing (already tested, but not committed). > > I'm adding some productions to the func_name rule in gram.y to handle > > the various "stringy operators" such as LIKE and OVERLAPS. These tokens > > will also be allowed in the ColLabel rule (as several are already). > > This fixes the immediate problem, and makes LIKE handling more > > consistant with other special functions. Comments? > That all sounds fine, but it doesn't seem to fix the problem I'm looking > at, which is that the OVERLAPS production is broken in current sources: Yes it does. When you execute select (timestamp 'today', interval '1 day') OVERLAPS (timestamp 'yesterday', timestamp 'tomorrow'); This is matched up with an entry in pg_proc which declares an SQL language implementation as 'select overlaps($1, ($1+$2), $3, $4)' which is what fails. It may be better to declare this as 'select ($1, ($1+$2)) overlaps ($3, $4)' but that is not what is there now. I've just tested the latter form and it seems to work, so I'll include that in my next patchball. - Thomas
Re: [HACKERS] beta testing version
On Tue, 28 Nov 2000, Hannu Krosing wrote: > xuyifeng wrote: > > > > I just noticed this conversation so I have not followed all of it, > but you seem to have strange priorities > > > I just want PG can be improved quickly, for me crash recover is very urgent >problem, > > Crash avoidance is usually much more urgent, at least on production > servers. Good call, but I kinda jumped to the conclusion that since PgSQL itself isn't that crash prone, its his OS or his hardware that was the problem :0
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
On Tue, Nov 28, 2000 at 05:19:45PM +0100, Zeugswetter Andreas SB wrote: > > I guess it depends on what you're using it for -- disk space > > is cheap and > > abundant anymore, I can see some advantages of having it > > computed only once > > rather than X times, where X is the number of SELECTs as that > > could get > > costly on really high traffic servers.. Costly not so much for simple > > computations like that but more complex ones. > As I said in my original post, my understanding of computed fields may be in error. If they're computed at SELECT time, to avoid creating table space, then a VIEW is exacly the right solution. However, it's easy to come up with examples of complex calculations that it would be useful to cache the results of, in the table. Then, computing at INSERT/UPDATE is clearly the way to go. So, having _both_ is the best thing. Ross
Re: [HACKERS] beta testing version
On Tue, 28 Nov 2000, xuyifeng wrote: > you are complete wrong, if I don't like PG, I'll never go here or talk > anything about PG, I don't care it. I just want PG can be improved > quickly, for me crash recover is very urgent problem, otherewise PG is > forced to stay on my desktop machine, We'll dare not move it to our > Server, I always see myself as a customer, customer is always right. except when they are wrong ... ... but, as for crash recover, the plan right now is that on Thursday, Dec 1st, 7.1 goes beta ... if you are so keen on the crash recovery stuff, what I'd recommend is grab the snapshot, and work with that on your machine, get used to the features that it presents and report any bugs you find. Between beta and release, there will be bug fixes, but no features added, so it makes for a relatively safe starting point. I wouldn't use it in production (or, rather, I personally would, but it isn't something I'd recommend for the faint of heart), but it will give you a base to start from ... release will be shortly into the new year, depending on what sorts of bugs ppl report and how quickly they can be fixed ... if all goes well, Jan 1st will be release date, but, from experience, we're looking at closer to jan 15th :) > > Regards, > XuYifeng > > > - Original Message - > From: Don Baccus <[EMAIL PROTECTED]> > To: xuyifeng <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Tuesday, November 28, 2000 11:16 PM > Subject: Re: [HACKERS] beta testing version > > > > At 11:15 PM 11/28/00 +0800, xuyifeng wrote: > > >no doubt, I have touched some problems PG has, right? if PG is so good, > > >is there any necessary for the team to improve PG again? > > > > See? Troll... > > > > The guy worships MySQL, just in case folks haven't made the connection. > > > > I'm going to ignore him from now on, suggest others do the same, I'm sure > > he'll go away eventually. > > > > > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > > Nature photos, on-line guides, Pacific Northwest > > Rare Bird Alert Service and other goodies at > > http://donb.photo.net. > > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] beta testing version
On Tue, 28 Nov 2000, xuyifeng wrote: > no doubt, I have touched some problems PG has, right? if PG is so good, > is there any necessary for the team to improve PG again? There is always room for improvements for any software package ... whether it be PgSQL, Linux, FreeBSD or PHPBuilder ... as ppl learn more, understand more and come up with new techniques, things tend to get better ... > > Regards, > XuYifeng > > - Original Message - > From: Don Baccus <[EMAIL PROTECTED]> > To: xuyifeng <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Tuesday, November 28, 2000 10:37 PM > Subject: Re: [HACKERS] beta testing version > > > > At 04:17 PM 11/28/00 +0800, xuyifeng wrote: > > >Hi, > > > > > > how long is PG7.1 already in beta testing? can it be released before Christmas >day? > > > can PG7.1 will recover database from system crash? > > > > This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect >this > > due to some posts he made in regard to Tim's SourceForge/Postgres article). > > > > Since he's read Tim's article, and at least some of the follow-up posts (given that > > he's posted responses himself), he should know by now that PG 7.1 is still in a >pre-beta > > state and won't be released before Christmas day. I also posted a fairly long >answer > > to a question Tim's posted at phpbuilder.com regarding recoverability and this >guy's > > undoubtably read it, too. > > > > Have I forgotten anything, xuyifeng? > > > > > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > > Nature photos, on-line guides, Pacific Northwest > > Rare Bird Alert Service and other goodies at > > http://donb.photo.net. > > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [HACKERS] beta testing version
xuyifeng wrote: > I just noticed this conversation so I have not followed all of it, but you seem to have strange priorities > I just want PG can be improved quickly, for me crash recover is very urgent problem, Crash avoidance is usually much more urgent, at least on production servers. > otherewise PG is forced to stay on my desktop machine, We'll dare not move it to >our Server, Why do you keep crashing your server ? If your desktop crashes less often than your server you might exchange them, no? > I always see myself as a customer, customer is always right. I'd like to see myself as being always right too ;) --- Hannu
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> I guess it depends on what you're using it for -- disk space > is cheap and > abundant anymore, I can see some advantages of having it > computed only once > rather than X times, where X is the number of SELECTs as that > could get > costly on really high traffic servers.. Costly not so much for simple > computations like that but more complex ones. Once and for all forget the argument in database technology, that disk space is cheap in regard to $/Mb. That is not the question. The issue is: 1. amout of rows you can cache 2. number of rows you can read from disk per second (note that it is not pages/sec) 3. how many rows you can sort in memory In the above sence disk space is one of the most expensive things in a database system. Saving disk space where possible will gain you drastic performance advantages. Andreas
[HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
Thomas Lockhart <[EMAIL PROTECTED]> writes: >> I see it does fail, but I'm at a complete loss to understand why, >> especially given that the first case still works. The grammar looks >> perfectly fine AFAICT. Can you explain what's wrong here? > Here is what I'm planning on doing (already tested, but not committed). > I'm adding some productions to the func_name rule in gram.y to handle > the various "stringy operators" such as LIKE and OVERLAPS. These tokens > will also be allowed in the ColLabel rule (as several are already). > This fixes the immediate problem, and makes LIKE handling more > consistant with other special functions. Comments? That all sounds fine, but it doesn't seem to fix the problem I'm looking at, which is that the OVERLAPS production is broken in current sources: template1=# select ('today', 'tomorrow') OVERLAPS ('yesterday', 'now'); overlaps -- t (1 row) template1=# select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18 hours'); ERROR: parser: parse error at or near "overlaps" I don't understand why we're getting a parse error here ... regards, tom lane
[HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
> Thomas Lockhart <[EMAIL PROTECTED]> writes: > > select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18 > > hours'); > > (the second one fails). Now that I look, this breakage was introduced in > > March when "we" expunged operators allowed as identifiers (Tom Lane and > > I have blood on our hands on this one ;) See gram.y around line 5409. > I see it does fail, but I'm at a complete loss to understand why, > especially given that the first case still works. The grammar looks > perfectly fine AFAICT. Can you explain what's wrong here? Yes. There is one underlying routine implementing the OVERLAPS operator. As you might expect, it is called overlaps() in the catalog, has an entry point of overlaps_timestamp(), and takes four arguments of type timestamp. The other variants which accept an interval type for the second and/or fourth arguments are defined in pg_proc.h as SQL procedures which simply add, say, the first and second arguments to end up with four timestamp arguments. The SQL routine explicitly calls overlaps() as a function, which is currently disallowed. Here is what I'm planning on doing (already tested, but not committed). I'm adding some productions to the func_name rule in gram.y to handle the various "stringy operators" such as LIKE and OVERLAPS. These tokens will also be allowed in the ColLabel rule (as several are already). This fixes the immediate problem, and makes LIKE handling more consistant with other special functions. Comments? - Thomas
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
I guess it depends on what you're using it for -- disk space is cheap and abundant anymore, I can see some advantages of having it computed only once rather than X times, where X is the number of SELECTs as that could get costly on really high traffic servers.. Costly not so much for simple computations like that but more complex ones. Just playing the devil's advocate a bit. -Mitch - Original Message - From: "Zeugswetter Andreas SB" <[EMAIL PROTECTED]> To: "'Ross J. Reedstrom'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 7:50 AM Subject: AW: [HACKERS] Please advise features in 7.1 (SUMMARY) > > > > This is a summary of replies. > > > > > > 1. Calculated fields in table definitions . eg. > > > > > > Create table test ( > > > A Integer, > > > B integer, > > > the_sum As (A+B), > > > ); > > > > > > This functionality can be achieved through the use of views. > > > > Using a view for this isn't quite the same functionality as a computed > > field, from what I understand, since the calculation will be done at > > SELECT time, rather than INSERT/UPDATE. > > I would expect the calculated field from above example to be calculated > during select time also, no ? You don't want to waste disk space with something > you can easily compute at runtime. > > Andreas >
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> > This is a summary of replies. > > > > 1. Calculated fields in table definitions . eg. > > > > Create table test ( > > A Integer, > > B integer, > > the_sum As (A+B), > > ); > > > > This functionality can be achieved through the use of views. > > Using a view for this isn't quite the same functionality as a computed > field, from what I understand, since the calculation will be done at > SELECT time, rather than INSERT/UPDATE. I would expect the calculated field from above example to be calculated during select time also, no ? You don't want to waste disk space with something you can easily compute at runtime. Andreas
[HACKERS] Problem in AlterTableAddConstraint?
Browsing through backend/commands/command.c I noticed the following code: if (indexStruct->indisunique) { List *attrl; /* go through the fkconstraint->pk_attrs list */ foreach(attrl, fkconstraint->pk_attrs) { Ident *attr=lfirst(attrl); found = false; for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++) { int pkattno = indexStruct->indkey[i]; if (pkattno>0) { char *name = NameStr(rel_attrs[pkattno-1]->attname); if (strcmp(name, attr->name)==0) { found = true; break; } } } if (!found) break; } } which is (I think) supposed to be checking for a unique index on the FK fields in the referenced table. Unfortunately, my reading of this code suggests it is doing the following: for each column in the FK see if we can find the column in the index if not, then die next FK column The problem with this is that it needs to ensure a 1:1 match between columns for the UNIQUE constraint requirement to be satisfied...I think. To give an example, create table c2(f1 integer, f2 integer, unique(f1,f2)); create table c1(f1 integer, f2 integer, foreign key(f1) references c2(f1)); is allowed with current sources. I'd guess that adding code to ensure the column lists are the same size would fix the problem. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
On Tue, Nov 28, 2000 at 02:04:01PM +1300, John Huttley wrote: > Thanks for your help, everyone. > > This is a summary of replies. > > 1. Calculated fields in table definitions . eg. > > Create table test ( > A Integer, > B integer, > the_sum As (A+B), > ); > > This functionality can be achieved through the use of views. Using a view for this isn't quite the same functionality as a computed field, from what I understand, since the calculation will be done at SELECT time, rather than INSERT/UPDATE. This can also be done with a trigger, which, while more cumbersome to write, would be capable of doing the math at modification time. Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
AW: [HACKERS] Re: [GENERAL] is it a bug?
> lpad and rpad never truncate, they only pad. > > Perhaps they *should* truncate if the specified length is less than > the original string length. Does Oracle do that? Yes, it truncates, same as Informix. Andreas
Re: [HACKERS] beta testing version
> no doubt, I have touched some problems PG has, right? if PG is so good, > is there any necessary for the team to improve PG again? *rofl* Good call Don :) - Thomas
Re: [HACKERS] beta testing version
you are complete wrong, if I don't like PG, I'll never go here or talk anything about PG, I don't care it. I just want PG can be improved quickly, for me crash recover is very urgent problem, otherewise PG is forced to stay on my desktop machine, We'll dare not move it to our Server, I always see myself as a customer, customer is always right. Regards, XuYifeng - Original Message - From: Don Baccus <[EMAIL PROTECTED]> To: xuyifeng <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 11:16 PM Subject: Re: [HACKERS] beta testing version > At 11:15 PM 11/28/00 +0800, xuyifeng wrote: > >no doubt, I have touched some problems PG has, right? if PG is so good, > >is there any necessary for the team to improve PG again? > > See? Troll... > > The guy worships MySQL, just in case folks haven't made the connection. > > I'm going to ignore him from now on, suggest others do the same, I'm sure > he'll go away eventually. > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. >
Re: [HACKERS] beta testing version
At 11:15 PM 11/28/00 +0800, xuyifeng wrote: >no doubt, I have touched some problems PG has, right? if PG is so good, >is there any necessary for the team to improve PG again? See? Troll... The guy worships MySQL, just in case folks haven't made the connection. I'm going to ignore him from now on, suggest others do the same, I'm sure he'll go away eventually. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
no doubt, I have touched some problems PG has, right? if PG is so good, is there any necessary for the team to improve PG again? Regards, XuYifeng - Original Message - From: Don Baccus <[EMAIL PROTECTED]> To: xuyifeng <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 10:37 PM Subject: Re: [HACKERS] beta testing version > At 04:17 PM 11/28/00 +0800, xuyifeng wrote: > >Hi, > > > > how long is PG7.1 already in beta testing? can it be released before Christmas >day? > > can PG7.1 will recover database from system crash? > > This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect >this > due to some posts he made in regard to Tim's SourceForge/Postgres article). > > Since he's read Tim's article, and at least some of the follow-up posts (given that > he's posted responses himself), he should know by now that PG 7.1 is still in a >pre-beta > state and won't be released before Christmas day. I also posted a fairly long answer > to a question Tim's posted at phpbuilder.com regarding recoverability and this guy's > undoubtably read it, too. > > Have I forgotten anything, xuyifeng? > > > > - Don Baccus, Portland OR <[EMAIL PROTECTED]> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net. >
RE: [HACKERS] Constraint names using 'user namespace'?
At 02:18 PM 11/28/00 +0800, Christopher Kings-Lynne wrote: >> As for the treading-on-user-namespace issue, we already do that for all >> implicitly created indexes (see UNIQUE, PRIMARY KEY, etc). I'd prefer >> to treat named constraints consistently with that long-established >> practice until we have a better idea that can be implemented uniformly >> across that whole set of constructs. (Once we have schemas, for >> example, it might be practical to give indexes a separate namespace >> from tables, which'd help a lot.) > >Surely the best way to do it would be to make the unique and primary key >implicitly created indices totally invisible to the user. Or at least add a >'system' flag to their entries in the pg_indexes table. Create a >pg_constraint table instead that people can use to find constraints. Oracle has a "user_constraints" table. Explicitly named contraints have that name entered into the user's namespace, implicitly named constraints get stuffed into "sys" in the form "sys.cn", where "n" is drawn from some system sequence. In Oracle you NEED the user_constraints table, particularly for RI constraint errors, because their wonderful error messages just give you the RI constraint name. If you've not given it a meaningful name yourself, which typically one doesn't ("integer references some_table"), you need to do a select on the user_constraints table to see what went wrong. Keep PG's superior error messages no matter what else is done :) The above is offered as a datapoint, that's all. >To support this, dropping unique and pk constraints would no longer be >possible (and _should_ no longer be possible) with a CREATE/DROP INDEX >command, and instead would be achieved with a functional ALTER TABLE >ADD/DROP CONSTRAINT statement. This is essentially the case in Oracle, though I suspect you could dig around, find the name of the unannounced unique index, and drop it by hand if you wanted. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] Re: [GENERAL] is it a bug?
"He weiping" <[EMAIL PROTECTED]> writes: > but it seems the "lpad", "rpad" don't work, > when I type: > select lpad('laser', 4, 'a'); > in psql, the result is still=20 > 'laser', the same with 'rpad', > Is it a bug or I'm mis-understaning the lpad and/or rpad functions? lpad and rpad never truncate, they only pad. Perhaps they *should* truncate if the specified length is less than the original string length. Does Oracle do that? regards, tom lane
RE: [HACKERS] Full text Indexing -out of contrib and into main..
At 10:52 AM 11/28/00 +0100, Magnus Hagander wrote: >> > b) Check out MSSQL 7's capabilities and weep. >> >> BTW, have you studied MSSQL enough to tell me if it has a >> separate/standalone >> (as a process) fti engine or just another index type. >It is standalone - separate process, data is stored in separate files (not >in db). > >In SQL Server 7.0, you also have to manually update the index. Just updating >the values in the table does *NOT* update the index. (Can be scheduled, of >course, but not live) >In SQL Server 2000 the index can be auto-updated when rows change, but it's >not default. This is similar to Oracle's InterMedia. In practice, using auto-update on a busy, live website is impractical, though how much this is due to InterMedia's being flakey and how much due to the computational expense isn't clear (or rather IM's so flakey one can't really explore enough to see how expensive auto-update on a busy site would be). - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Question about Oracle compatibility
At 09:59 AM 11/28/00 +, Pete Forman wrote: >Mario Weilguni writes: > > This gets really bad when the actual data is coming from a > > webinterface, I've to handle 2 different queries for the case empty > > string and non-empty string. > >In their documentation both Oracle 7 and 8 state: > >Oracle currently treats a character value with a length of zero >as null. However, this may not continue to be true in future >releases, and Oracle recommends that you do not treat empty >strings the same as NULLs. Yeah, but this is harder than it sounds! NULL and '' are indistinguishable in queries, so how do you treat them differently? Has to be in the application code, I guess. - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 04:17 PM 11/28/00 +0800, xuyifeng wrote: >Hi, > > how long is PG7.1 already in beta testing? can it be released before Christmas day? > can PG7.1 will recover database from system crash? This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect this due to some posts he made in regard to Tim's SourceForge/Postgres article). Since he's read Tim's article, and at least some of the follow-up posts (given that he's posted responses himself), he should know by now that PG 7.1 is still in a pre-beta state and won't be released before Christmas day. I also posted a fairly long answer to a question Tim's posted at phpbuilder.com regarding recoverability and this guy's undoubtably read it, too. Have I forgotten anything, xuyifeng? - Don Baccus, Portland OR <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] Re: FWD: tinterval vs interval on pgsql-novice
Thomas Lockhart <[EMAIL PROTECTED]> writes: > select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18 > hours'); > (the second one fails). Now that I look, this breakage was introduced in > March when "we" expunged operators allowed as identifiers (Tom Lane and > I have blood on our hands on this one ;) See gram.y around line 5409. I see it does fail, but I'm at a complete loss to understand why, especially given that the first case still works. The grammar looks perfectly fine AFAICT. Can you explain what's wrong here? regards, tom lane
AW: [HACKERS] is it a bug?
> > ... it seems the "lpad", "rpad" don't work, > > when I type: > > select lpad('laser', 4, 'a'); > > in psql, the result is still > > 'laser', the same with 'rpad', > > Is it a bug or I'm mis-understaning the lpad and/or rpad functions? > > A simple misunderstanding. The length argument is for the *total* > length. So padding a 5 character string to a length of 4 will do > nothing. But padding to a length of 6 will add a single "a" to the > string. Seems the implementor made a mistake, since this is supposed to be oracle compat stuff it should behave like Oracle, and thus trim the string to 4 chars. Andreas
Re: [HACKERS] is it a bug?
> ... it seems the "lpad", "rpad" don't work, > when I type: > select lpad('laser', 4, 'a'); > in psql, the result is still > 'laser', the same with 'rpad', > Is it a bug or I'm mis-understaning the lpad and/or rpad functions? A simple misunderstanding. The length argument is for the *total* length. So padding a 5 character string to a length of 4 will do nothing. But padding to a length of 6 will add a single "a" to the string. - Thomas
AW: [HACKERS] Constraint names using 'user namespace'?
> > pjw=# create table pk1(f1 integer, constraint zzz primary key(f1)); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit > index 'zzz' for > > table 'pk1' > > CREATE > > pjw=# create table zzz(f1 integer); > > ERROR: Relation 'zzz' already exists > > > Is there a good reason why the automatically created items > do not have a > > 'pg_' in front of their names? > > Not a good idea. I think it should probably be pk1_zzz in this case. > > If we do either, it will break the recently submitted pg_dump > patch that > uses the index name as the constraint name. I thought that patch was > wrongheaded anyway, and would recommend reversing it... I rather think, that having index names clash with table names is the bogus part. That the index gets the specified name from the constraint clause is more or less expected behavior (Informix, Oracle ...). Andreas
[HACKERS] Patch for 7.0.3 code to read pg_options
The pg_options.sample that is included in 7.0.x cannot actually be used because of bugs in the routine that reads it. First, it reads only 4095 bytes and second it does not cope with white space within lines. The attached patch cures the problem. It seems to be relevant only to 7.0.x because the entire code has been removed from 7.1. Index: src/backend/utils/misc/trace.c === RCS file: /cvs/pgsql-deb/postgresql/src/backend/utils/misc/trace.c,v retrieving revision 1.1.1.2 diff -c -b -r1.1.1.2 trace.c *** src/backend/utils/misc/trace.c 2000/11/14 10:40:10 1.1.1.2 --- src/backend/utils/misc/trace.c 2000/11/28 07:43:13 *** *** 438,444 --- 438,446 int fd; int n; int verbose; + int incomment = 0; charbuffer[BUF_SIZE]; + charoptbuf[BUF_SIZE]; charc; char *s, *p; *** *** 455,478 #else if ((fd = open(buffer, O_RDONLY | O_BINARY)) < 0) #endif return; ! if ((n = read(fd, buffer, BUF_SIZE - 1)) > 0) { ! /* collpse buffer in place removing comments and spaces */ ! for (s = buffer, p = buffer, c = '\0'; s < (buffer + n);) { switch (*s) { case '#': while ((s < (buffer + n)) && (*s++ != '\n')); break; - case ' ': - case '\t': - case '\n': case '\r': if (c != ',') c = *p++ = ','; s++; break; default: --- 457,490 #else if ((fd = open(buffer, O_RDONLY | O_BINARY)) < 0) #endif + { + fprintf(stderr,"Couldn't open %s\n",buffer); return; + } ! p = optbuf; ! c = '\0'; ! while ((n = read(fd, buffer, BUF_SIZE - 1)) > 0) { ! if (incomment && (*buffer != '\n')) ! *buffer = '#'; ! ! /* read in options removing comments and spaces */ ! for (s = buffer; s < (buffer + n);) { switch (*s) { case '#': + incomment = 1; while ((s < (buffer + n)) && (*s++ != '\n')); break; case '\r': + case '\n': + incomment = 0; if (c != ',') c = *p++ = ','; + case ' ': + case '\t': s++; break; default: *** *** 480,494 break; } } if (c == ',') p--; *p = '\0'; verbose = pg_options[TRACE_VERBOSE]; ! parse_options(buffer, true); verbose |= pg_options[TRACE_VERBOSE]; if (verbose || postgres_signal_arg == SIGHUP) ! tprintf(TRACE_ALL, "read_pg_options: %s", buffer); ! } close(fd); } --- 492,506 break; } } + } if (c == ',') p--; *p = '\0'; verbose = pg_options[TRACE_VERBOSE]; ! parse_options(optbuf, true); verbose |= pg_options[TRACE_VERBOSE]; if (verbose || postgres_signal_arg == SIGHUP) ! tprintf(TRACE_ALL, "read_pg_options: %s", optbuf); close(fd); } -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein." Psalms 24:1
Re: [HACKERS] Question about Oracle compatibility
Mario Weilguni writes: > This gets really bad when the actual data is coming from a > webinterface, I've to handle 2 different queries for the case empty > string and non-empty string. In their documentation both Oracle 7 and 8 state: Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs. -- Pete Forman -./\.- Disclaimer: This post is originated Western Geophysical -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- the opinion of Baker Hughes or http://www.crosswinds.net/~petef -./\.- its divisions.
RE: [HACKERS] Full text Indexing -out of contrib and into main..
> > b) Check out MSSQL 7's capabilities and weep. > > BTW, have you studied MSSQL enough to tell me if it has a > separate/standalone > (as a process) fti engine or just another index type. It is standalone - separate process, data is stored in separate files (not in db). In SQL Server 7.0, you also have to manually update the index. Just updating the values in the table does *NOT* update the index. (Can be scheduled, of course, but not live) In SQL Server 2000 the index can be auto-updated when rows change, but it's not default. //Magnus
AW: [HACKERS] 8192 BLCKSZ ?
> 8k is the standard Unix file system disk transfer size. Are you sure ? I thought it was 4k on AIX and 2k on Sun. Andreas
AW: [HACKERS] 8192 BLCKSZ ?
> I don't believe it's a performance issue, I believe it's that > writes to > blocks greater than 8k cannot be guaranteed 'atomic' by the operating > system. Hence, 32k blocks would break the transactions system. (Or > something like that - am I correct?) First, 8k are not atomic eighter. Second, the page layout in PostgreSQL has been designed to not care about the atomicity of IO. This design might have been compromised for index pages recently, to optimize index performance, but data pages are perfectly safe. Andreas
Re: [HACKERS] Full text Indexing -out of contrib and into main..
john huttley wrote: > > > I believe that it is appropriate for contrib/ because it is a good demo > > of FTI-like capabilities. But nothing more, yet. For at least a couple > > of reasons: > > > > 1) It generates the "index" as a table, not a PostgreSQL index or > > index-like thing. > > > > 2) It has a hardcoded list of non-indexed words. This should come from a > > table, to allow it to be tuned to the application requirements. > > > > Comments? > > > > - Thomas > > > > In general.. > a) Considering that I was coding up the same thing with triggers and such, > things could only get better. AFAIK, the one in contrib _is_ the same thing coded up with triggers and such ;) > b) Check out MSSQL 7's capabilities and weep. BTW, have you studied MSSQL enough to tell me if it has a separate/standalone (as a process) fti engine or just another index type. I have been contemplating about implementing FTI for postgres for some time and my current plan would be to implement a out-of-process fti engine (API + sample implementation, in the spirit of PostgreSQLs extensibility) that could postpone the actual indexing but still help with queries even for not yet fully indexed stuff. Will probably need some choreography but essential for high performance. You generally don't want to wait for all index entries of an inverted index to be saved. Also the thing should be more general than the one in contrib , being able to index both fields and full records and support functional indexes. Is there a way to make PostgresQL optimiser aware of the selectivity/cost of function, so that it can do the right thing for a query like SELECT * FROM ARTICLES WHERE ADATE BETWEEN YESTERDAY AND TOMORROW AND ARTICLES.FTI_MATCHES('(CAT & DOG) ! PRESIDENT') It would be almost automatic if functions could return sets and then be used like SELECT * FROM ARTICLE WHERE ADATE BETWEEN YESTERDAY AND TOMORROW AND ARTICLE_ID = ARTICLE.FTI_MATCHING_IDS('(CAT & DOG) ! PRESIDENT') and somehow the optimiser would know that it can join on the returned ids but this is probably not the case ;) > c) It would be a start. One its in the tree, it gets used more, gets > improved.. But, it is not a _real_ full text index, just a postgresql sample application that implements a full text index using an sql database. -- Hannu
[GENERAL] is it a bug?
I'm using cvs-current, and testing those build-in function according to the docs. but it seems the "lpad", "rpad" don't work, when I type: select lpad('laser', 4, 'a'); in psql, the result is still 'laser', the same with 'rpad', Is it a bug or I'm mis-understaning the lpad and/or rpad functions? Regards Laser
RE: [HACKERS] JDBC charSet patch
It's been committed into the cvs repository. The easiest thing to do is to use CVS. I can't remember if it was posted direct to me, or to the patches list. Peter -- Peter Mount Enterprise Support Officer, Maidstone Borough Council Email: [EMAIL PROTECTED] WWW: http://www.maidstone.gov.uk All views expressed within this email are not the views of Maidstone Borough Council -Original Message- From: Dave [mailto:[EMAIL PROTECTED]] Sent: Monday, November 27, 2000 8:47 PM To: [EMAIL PROTECTED] Subject: [HACKERS] JDBC charSet patch Hi all, I heard there is a patch which can assign encoding other the database default. Can anyone tell me where to get it, or where can I get more information. Thanks Dave
[HACKERS] ~{;X84~}: [HACKERS] beta testing version
-Original Message- ~{7"<~HK~}: xuyifeng <[EMAIL PROTECTED]> ~{JU<~HK~}: [EMAIL PROTECTED] <[EMAIL PROTECTED]> ~{HUFZ~}: 2000~{Dj~}11~{TB~}28~{HU~} 16:22 ~{VwLb~}: [HACKERS] beta testing version >Hi, > > how long is PG7.1 already in beta testing? can it be released before Christmas day? you may get pre-beta version via cvs. pretty stable, I've tested. > can PG7.1 will recover database from system crash? yes, I've tested via kill use -KILL signal. but no more. > > Thanks, > >XuYifeng >
[HACKERS] beta testing version
Hi, how long is PG7.1 already in beta testing? can it be released before Christmas day? can PG7.1 will recover database from system crash? Thanks, XuYifeng