Re: [GENERAL] SSDD reliability

On 05/05/11 03:31, David Boreham wrote: On 5/4/2011 11:15 AM, Scott Ribe wrote: Sigh... Step 2: paste link in ;-) To be honest, like the article author, I'd be happy with 300+ days to failure, IF the drives

Re: Fwd: Re: [GENERAL] SSDD reliability

On Wed, May 4, 2011 at 9:34 PM, David Boreham wrote: > On 5/4/2011 9:06 PM, Scott Marlowe wrote: >> >> Most of it is.  But certain parts are fairly new, i.e. the >> controllers.  It is quite possible that all these various failing >> drives share some long term ~ 1 year degradation issue like the

Re: Fwd: Re: [GENERAL] SSDD reliability

On 5/4/2011 9:06 PM, Scott Marlowe wrote: Most of it is. But certain parts are fairly new, i.e. the controllers. It is quite possible that all these various failing drives share some long term ~ 1 year degradation issue like the 6Gb/s SAS ports on the early sandybridge Intel CPUs. If that's th

Re: Fwd: Re: [GENERAL] SSDD reliability

On Wed, May 4, 2011 at 6:31 PM, David Boreham wrote: > > this). The technology and manufacturing processes are common across many > different types of product. They either all work , or they all fail. Most of it is. But certain parts are fairly new, i.e. the controllers. It is quite possible th

Re: Fwd: Re: [GENERAL] SSDD reliability

On 5/4/2011 6:02 PM, Greg Smith wrote: On 05/04/2011 03:24 PM, David Boreham wrote: So if someone says that SSDs have "failed", I'll assume that they suffered from Flash cell wear-out unless there is compelling proof to the contrary. I've been involved in four recovery situations similar to t

Re: Fwd: Re: [GENERAL] SSDD reliability

On 05/04/2011 03:24 PM, David Boreham wrote: So if someone says that SSDs have "failed", I'll assume that they suffered from Flash cell wear-out unless there is compelling proof to the contrary. I've been involved in four recovery situations similar to the one described in that coding horror

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Tue, May 3, 2011 at 8:03 PM, Greg Smith wrote: > With a uniqueness constraint in this situation, the unexpected data--row > with a non unique MAC--will be rejected and possibly lost when the insertion > happens.  You say that's a good thing, plenty of people will say that's the > worst possibl

Re: [GENERAL] GROUP BY Wildcard Syntax Thought

"David Johnston" writes: > When specifying columns in a GROUP BY clause would it be possible to use a > wildcard to specify all columns coming from a given relation? I think the need for this will be largely superseded by the SQL-standard behavior that grouping by a primary key is sufficient (whi

Re: [GENERAL] ZEOS or PGDAC - How to lock a resource?

2011/5/4 durumdara : > Hi! > > We will porting an application to PGSQL from some table based app (BDE > like). > > The older application used a special technic of the driver: if a record > edited, some exclusive (over transaction), "forever living" lock put on it. > On exit, cancel, or post this lo

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

> Being the “first line” or the “second line” of a physical invoice is a > property for that line. Identifying its position on the invoice is only > natural. > Specifically, the position of the line on the invoice; you can't have to > invoice lines at the second line of aninvoice for example.

[GENERAL] GROUP BY Wildcard Syntax Thought

When specifying columns in a GROUP BY clause would it be possible to use a wildcard to specify all columns coming from a given relation? SELECT rosum.*, sum(ld.amount) AS ldcost, count(ld.amount) AS ldcount, rosum.rocost + sum(ld.amount) AS netbal FROM ( SELECT w.s_id, w.accountnum

Fwd: Re: [GENERAL] SSDD reliability

No problem with that, for a first step. ***BUT*** the failures in this article and many others I've read about are not in high-write db workloads, so they're not write wear, they're just crappy electronics failing. As a (lapsed) electronics design engineer, I'm suspicious of the notion that

[GENERAL] ZEOS or PGDAC - How to lock a resource?

Hi! We will porting an application to PGSQL from some table based app (BDE like). The older application used a special technic of the driver: if a record edited, some exclusive (over transaction), "forever living" lock put on it. On exit, cancel, or post this lock removed. We used this to l

Re: [GENERAL] SSDD reliability

On May 4, 2011, at 11:31 AM, David Boreham wrote: > To be honest, like the article author, I'd be happy with 300+ days to > failure, IF the drives provide an accurate predictor of impending doom. No problem with that, for a first step. ***BUT*** the failures in this article and many others I've

Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions

On Wed, May 04, 2011 at 07:03:31PM +0200, Marek Więckowski wrote: > (and this is why I was looking into this in the first place). There is a > danger that client programs will continue issuing queries while believing > that > they are in a transaction... They do expect db errors and rolled back

Re: [GENERAL] SSDD reliability

On 5/4/2011 11:15 AM, Scott Ribe wrote: Sigh... Step 2: paste link in ;-) To be honest, like the article author, I'd be happy with 300+ days to failure, IF the drives provide an accurate predictor of impend

Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions

Marek =?utf-8?q?Wi=C4=99ckowski?= writes: > But for the library which I'm using, simply exiting/aborting is not an option > (and this is why I was looking into this in the first place). There is a > danger that client programs will continue issuing queries while believing > that > they are in

Re: [GENERAL] SSDD reliability

On May 4, 2011, at 10:50 AM, Greg Smith wrote: > Your link didn't show up on this. Sigh... Step 2: paste link in ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voic

Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions

On Wednesday 04 May 2011 18:04:16 Tom Lane wrote: > Marek Wieckowski writes: > > If there is a script executed in psql there is no easy way to catch that > > psql has reconnected in the middle of it... > > As far as psql goes, it should certainly abandon executing any script > file if it loses th

Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions

Marek =?utf-8?q?Wi=C4=99ckowski?= writes: > If there is a script executed in psql there is no easy way to catch that psql > has reconnected in the middle of it... As far as psql goes, it should certainly abandon executing any script file if it loses the connection. I rather thought it did alrea

Re: [GENERAL] Rearranging simple where clauses

On Wed, 2011-05-04 at 11:49 -0400, Tom Lane wrote: > Well, you failed to show us any concrete examples of the cases you > were looking at, but no I don't think the planner necessarily likes > "all the constants on one side". Most likely the win cases are where > one side of a WHERE-condition opera

Re: [GENERAL] auto-reconnect: temp schemas, sequences, transactions

On Monday 02 May 2011 17:32:26 Tom Lane wrote: > If the client-side logic tries to re-issue these queries > after re-connecting, it would be up to that logic to be careful about > what to reissue or not. Possibly this is a question for the author > of your client library. I see. So I have two use

Re: [GENERAL] Rearranging simple where clauses

Michael Graham writes: > I did suspect that the answer would be that the difficulty out ways the > benefit. But in terms of driving the planner don't we always want to be > looking to move all the constants to one side of the expression since > the planner seems to like those? Well, you failed t

Re: [GENERAL] Rearranging simple where clauses

On Wed, 2011-05-04 at 10:49 -0400, Tom Lane wrote: > Well, it'd require a very large amount of > type-specific/operator-specific knowledge, and it's not clear what > would drive the planner towards doing useful rearrangements rather > than counterproductive ones, and the number of real-world querie

Re: [GENERAL] undead index

Jens Wilke writes: > pg_upgrade brakes with the following error: > Could not find foo.bar_idx in old cluster Hmm, is this an autogenerated index? I suspect pg_upgrade can't cope if it's been assigned a different name in the new cluster. regards, tom lane -- Sent via pg

Re: [GENERAL] postgres segfaulting on pg_restore

Chris Curvey writes: > in reverse order: no third-party, no contrib, no home-brew C.here is a > stack trace from a fresh build of 9.1 beta 1 built with enable-cassert. > Program received signal SIGSEGV, Segmentation fault. > 0x00744777 in AllocSetAlloc (context=0x13f6d08, size=16) a

[GENERAL] SSDD reliability

Yeah, on that subject, anybody else see this: <> Absolutely pathetic. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgres

Re: [GENERAL] Rearranging simple where clauses

Michael Graham writes: > I was playing around with some sql in postgres and got to wondering why > the optimiser can't figure out that rearranging some expressions can > result in massive improvements in the queue plan. For example id + 5 < > 100 compared with id < 100 - 5. > Is it simply that n

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 4, 2011 at 2:25 AM, Greg Smith wrote: > David Johnston wrote: >> >> Is there any rules-of-thumb on the performance of a PK as a function of >> key length?  I like using varchar based identifiers since I tend to query >> tables directly and writing where clauses is much easier if you ca

Re: [GENERAL] postgresql not updating the sequence

2011/5/4 Kosna Sridhar : > > actually i resolved the problem thanks for quick response Great! For history and reference of this list could you tell us how with a "Reply all"? regards -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresq

[GENERAL] Default Operator Class for datatype

Hello, I've set pl_PL.UTF-8 collation on my database after upgrade do 9.0. That means indexes for varchar column doesn't work anymore in selects using "like" or "=" operators with that columns. I know the solution - operator classes - which works very well. But that means I have to find and recrea

[GENERAL] Rearranging simple where clauses

Hi, I was playing around with some sql in postgres and got to wondering why the optimiser can't figure out that rearranging some expressions can result in massive improvements in the queue plan. For example id + 5 < 100 compared with id < 100 - 5. Is it simply that no one has go around to doing

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 4, 2011 at 7:50 AM, Misa Simic wrote: > 2011/5/4 Merlin Moncure >> >> Most of the old school accounting systems maintained an invoice line >> number. >> > Invoice Line >> >     -Invoice Number >> >     -LineNo >> >     -ItemID >> >     -qty >> >     -Price >> >> The line number starte

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 04, 2011 at 09:33:57AM -0400, David Johnston wrote: > “Hello - person born in Liverpool London, St. Whatever > hospital, Room 101 @ 13:14:57AM on the 5th of March 2001 – > how may I direct your call?” (I guess you could use the > conception date as well That will rarely be known to an

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

>>Thanks, merlin, >>>And in that case, what is "Natural" in LineNo? I would say, with adding >>>LineNo we are creating syntethic/surrogate Key (just instead of 1 surrogate >>>column - it will be Compound key with more columns...)? The >>>same is with >>>all other tables what are "parts" o

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

2011/5/4 Merlin Moncure > Most of the old school accounting systems maintained an invoice line > number. > > > Invoice Line > > -Invoice Number > > -LineNo > > -ItemID > > -qty > > -Price > > The line number started from 1 (the first line on the invoice) on > every unique invo

Re: [GENERAL] postgresql not updating the sequence

2011/5/4 kosna : > hi everyone, > > i ve created a table with id and datatype of id is serial > and i ve created a  sequence on that table > whenever i insert the new row values the id is not being incremented and it > is giving the exception entityalreadyexists. pls help me > thanks and regards, >

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

On Wed, May 4, 2011 at 7:14 AM, Misa Simic wrote: > > > 2011/4/28 Merlin Moncure >> >> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote: >> *) most tables don't have unique natural keys (let's see em) >> etc >> > > i.e for an Invoice, we have at least 2 tables (more in practice...): > Invoice H

Re: [GENERAL] postgresql not updating the sequence

On 04/05/2011 08:10, kosna wrote: hi everyone, i ve created a table with id and datatype of id is serial and i ve created a sequence on that table whenever i insert the new row values the id is not being incremented and it is giving the exception entityalreadyexists. pls help me thanks and rega

[GENERAL] undead index

Hi, pg_upgrade brakes with the following error: pg_upgrade 8.4.5 to 9.0.4: Restoring user relation files /data1/postgres/pgsql/foo/data_8.4/base/11564/2613 ^M /data1/postgres/pgsql/foo/data_8.4/base/11564/2683 Could not find foo.bar_idx in old cluster This index was dele

[GENERAL] postgresql not updating the sequence

hi everyone, i ve created a table with id and datatype of id is serial and i ve created a sequence on that table whenever i insert the new row values the id is not being incremented and it is giving the exception entityalreadyexists. pls help me thanks and regards, kosna. -- View this message i

Re: [GENERAL] postgres segfaulting on pg_restore

> > > It occurred to me that a simple explanation for a core dump there would > be if something had scribbled past the end of the preceding palloc > chunk. That would tend to clobber the "context" link of the palloc > chunk after it, which would send GetMemoryChunkSpace off into > never-never land

Re: [GENERAL] multiple group by on same table

Ciao Leonardo, I am not sure if this could apply to your case, but maybe - unless you have done it before - you could look at windowing functions (http://www.postgresql.org/docs/current/interactive/tutorial-window.html). They require PG8.4+ though. Cheers, Gabriele On Wed, 4 May 2011 11:51

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

2011/4/28 Merlin Moncure > On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote: > *) most tables don't have unique natural keys (let's see em) > etc > > i.e for an Invoice, we have at least 2 tables (more in practice...): Invoice Header -Invoice Number -Date -CustomerID -Currency

Re: [GENERAL] multiple group by on same table

On 05/04/2011 01:51 PM, Leonardo Francalanci wrote: Hi, I'm going to need to GROUP BY the same table multiple times. That is, something like: select (some aggregate functions here) from tableA group by f1, f2 select (some other aggregate functions here) from tableA group by f3, f4 etc The

[GENERAL] multiple group by on same table

Hi, I'm going to need to GROUP BY the same table multiple times. That is, something like: select (some aggregate functions here) from tableA group by f1, f2 select (some other aggregate functions here) from tableA group by f3, f4 etc The table is pretty large; can someone suggest the best way

Re: [GENERAL] Bidirectional replication

Hi Thanks to ALL, John I tried Perl built into RHEL 5.5 but i got some errors so I download activeperl 5.12 and installed it. After that when start installation I stuck with the error, FAILED! (psql:/usr/local/share/bucardo/bucardo.schema:40: ERROR: didn't get a returINSTALLATION n item from mksa

Re: [GENERAL] Question on Wal time lines

dabicho wrote: For restoring a database from wal files, if I omit a target on the recovery.conf file, can I make it so the database continues the time line instead of starting one? Or is there a tool to pick the most recent time line from a bunch of wal files? When recovery finishes, you

Re: [GENERAL] Bidirectional replication

Merlin Moncure wrote: I know some people do some cool, usable things with that stuff, but the whole concept seems awfully awkward to me. I suppose I'm a crotchety, cane shaking fundamentalist... It's possible--do you sometimes find yourself yelling at young developers, telling them to stop re

Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys

David Johnston wrote: Is there any rules-of-thumb on the performance of a PK as a function of key length? I like using varchar based identifiers since I tend to query tables directly and writing where clauses is much easier if you can avoid the joins. I'm likely better off creating views and