[GENERAL] The Best Postgresql Load Balancing Solution

[Najib Abi Fadel]

Hi,i was searching for a load balancing solution for postgres, I found some ready to use software like PGCluster, Slony, pgpool and others.It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load balance.I would also like to know if someone has used Red Hat Cluster and GFS for implementing postgresql Load balancing solution. I have alreay used that for implementing a mail load balancing solution with 2 sendmail servers running on 2 different machines but sharing the same storage.Thanks for any help.Najib Abi FadelSystem AdministratorSaint-Joseph University 
	

	
		Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Ron Johnson]

On 09/14/06 21:32, AgentM wrote:
> 
> On Sep 14, 2006, at 21:25 , Bruce Momjian wrote:
> 
>>
>> I am not going to ask how you got to 135MPH.
> 
> Obviously he was running MySQL under the hood.

No wonder he spun out and crashed into a telephone poll...

-- 
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Rich Shepard]

On Thu, 14 Sep 2006, AgentM wrote:


Obviously he was running MySQL under the hood.


  Ta-da! We have a winner!

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PostgreSQL slammed by PHP creator

[AgentM]

On Sep 14, 2006, at 21:25 , Bruce Momjian wrote:



I am not going to ask how you got to 135MPH.


Obviously he was running MySQL under the hood.

-M

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

bank :)

Are we talking about this one?
http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda.shtml


Note that this was a very long time ago :)

I hope it wasn't on 1966 ...


I am not going to ask how you got to 135MPH.


Oh that is easy, you just press on the gas and watch the car beside you 
disappear :). Of course that is when you notice you are running out of 
highway and quickly approaching downtown portland. Then you hit the 
brakes hard trying to make the corner, hit gravel, have the rear brakes 
lock, spin, and hit a telephone pole.



Joshua D. Drake







--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Geoffrey]

Joshua D. Drake wrote:


I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have 
beat the other car too.


' (a tear for your 66 cuda...)


Hell I cried more then a tear. The cop felt so bad for me, he didn't 
even ticket me... All white, red interior... man I loved that car. It 
was the first car that *I* bought with *my* money and I *owned* it. No 
bank :)


Note that this was a very long time ago :)


We are showing our age...

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

Alvaro Herrera wrote:

Joshua D. Drake wrote:

I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.

' (a tear for your 66 cuda...)
Hell I cried more then a tear. The cop felt so bad for me, he didn't 
even ticket me... All white, red interior... man I loved that car. It 
was the first car that *I* bought with *my* money and I *owned* it. No 
bank :)


Are we talking about this one?
http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda.shtml


No that is the child of mine... and actually the one linked above is 
worth a great deal of money :) No mine was this one:


http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda-history.shtml

Scroll down to 1966. Mine was slightly modified.




Note that this was a very long time ago :)


I hope it wasn't on 1966 ...



Heh no. I wasn't even born in 1966.

Joshua D. Drake


--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Geoffrey]

Bruce Momjian wrote:

Alvaro Herrera wrote:

Joshua D. Drake wrote:

I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.

' (a tear for your 66 cuda...)
Hell I cried more then a tear. The cop felt so bad for me, he didn't 
even ticket me... All white, red interior... man I loved that car. It 
was the first car that *I* bought with *my* money and I *owned* it. No 
bank :)

Are we talking about this one?
http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda.shtml


Note that this was a very long time ago :)

I hope it wasn't on 1966 ...


I am not going to ask how you got to 135MPH.


I don't think you'd have any problem doing 135mph in a '66 Cuda.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Bruce Momjian]

Alvaro Herrera wrote:
> Joshua D. Drake wrote:
> > >>
> > >>I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
> > >>If it hadn't been for the gravel around that corner, I would have beat 
> > >>the other car too.
> > >
> > >' (a tear for your 66 cuda...)
> > 
> > Hell I cried more then a tear. The cop felt so bad for me, he didn't 
> > even ticket me... All white, red interior... man I loved that car. It 
> > was the first car that *I* bought with *my* money and I *owned* it. No 
> > bank :)
> 
> Are we talking about this one?
> http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda.shtml
> 
> > Note that this was a very long time ago :)
> 
> I hope it wasn't on 1966 ...

I am not going to ask how you got to 135MPH.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Alvaro Herrera]

Joshua D. Drake wrote:
> >>
> >>I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
> >>If it hadn't been for the gravel around that corner, I would have beat 
> >>the other car too.
> >
> >' (a tear for your 66 cuda...)
> 
> Hell I cried more then a tear. The cop felt so bad for me, he didn't 
> even ticket me... All white, red interior... man I loved that car. It 
> was the first car that *I* bought with *my* money and I *owned* it. No 
> bank :)

Are we talking about this one?
http://www.musclecarclub.com/musclecars/plymouth-cuda/plymouth-cuda.shtml

> Note that this was a very long time ago :)

I hope it wasn't on 1966 ...

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.


' (a tear for your 66 cuda...)


Hell I cried more then a tear. The cop felt so bad for me, he didn't 
even ticket me... All white, red interior... man I loved that car. It 
was the first car that *I* bought with *my* money and I *owned* it. No 
bank :)


Note that this was a very long time ago :)

Joshua D. Drake




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Geoffrey]

Joshua D. Drake wrote:

[EMAIL PROTECTED] wrote:

[EMAIL PROTECTED] wrote on 15/09/2006 04:40:04 a.m.:

I wasn't able to find anything the "article" worth discussing. If 
you give up A, C, I, and D, of course you get better performance- just 

like
you can get better performance from a wheel-less Yugo if you slide 
it down a luge track.
I don't think a Yugo would give you better performance no matter what 
you do ;)


I found in my youth that a mini slides better on it roof than on it's 
wheels.


I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.


' (a tear for your 66 cuda...)

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Initializing Datums for use with SPI_execute_plan

[Andrew - Supernews]

On 2006-09-14, "Jack Orenstein" <[EMAIL PROTECTED]> wrote:
> I don't think I explained myself clearly. I have a C string (char*,
> terminating zero) and a byte array (char*, possibly containing zeros,
> and I know the length). I want to obtain Datums wrapping these values
> that will be used to bind varchar and bytea columns, and I'm trying to
> find out how to generate these Datums.

One way:

bytea *my_bytea = (bytea *) palloc(byte_array_len + VARHDRSZ);
memcpy(VARDATA(my_bytea), byte_array, byte_array_len);
VARATT_SIZEP(my_bytea) = byte_array_len + VARHDRSZ;

values[0] = DirectFunctionCall1(textin,CStringGetDatum(yourstring));
values[1] = PointerGetDatum(my_bytea);

SPI_execute_plan...

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Scott Marlowe]

On Thu, 2006-09-14 at 11:40, Joshua D. Drake wrote:
> > I wasn't able to find anything the "article" worth discussing. If you 
> > give up A, C, I, and D, of course you get better performance- just like 
> > you can get better performance from a wheel-less Yugo if you slide it 
> > down a luge track.
> 
> I don't think a Yugo would give you better performance no matter what 
> you do ;)
> 
> > 

http://www.youtube.com/watch?v=mCkOpfLiRcA

mini on a ski jump.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

Brandon Aiken wrote:

And yet this man is smart enough not to run MySQL on Windows.  Methinks
this says something


Heh..

Joshua D. Drake



--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D. Drake
Sent: Thursday, September 14, 2006 5:16 PM
To: [EMAIL PROTECTED]
Cc: PostgreSQL General ML
Subject: Re: [GENERAL] PostgreSQL slammed by PHP creator

[EMAIL PROTECTED] wrote:

[EMAIL PROTECTED] wrote on 15/09/2006 04:40:04 a.m.:


I wasn't able to find anything the "article" worth discussing. If
you 
give up A, C, I, and D, of course you get better performance- just 
like 

you can get better performance from a wheel-less Yugo if you slide
it 

down a luge track.

I don't think a Yugo would give you better performance no matter what



you do ;)
I found in my youth that a mini slides better on it roof than on it's 
wheels.


I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.


Joshua D. Drake





--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Initializing Datums for use with SPI_execute_plan

[Jack Orenstein]

On 9/13/06, Martijn van Oosterhout  wrote:

On Tue, Sep 12, 2006 at 09:57:33PM -0400, Jack Orenstein wrote:
> ...
> int, bigint: From looking at postgres.h, I realize that Datum is an
> unsigned long. I'm guessing that I should just be able to assign Datums
> carrying ints or bigints, e.g. using Int32GetDatum to cast the int to
> a Datum. Is that correct?

Yes, need to use the *GetDatum functions.

> varchar: I have a zero-terminated string that I need to turn into a
> Datum. Is CStringGetDatum the right thing to use?

CStringGetDatum will get you something of the right format for cstring,
if you want varchar, you need to have an object of "VarChar*" first.

> bytea: I have an unsigned char* (not zero-terminated). Can I use
> PointerGetDatum?

Similarly you should use "bytea*" here. Then you can use
PointerGetDatum.


I don't think I explained myself clearly. I have a C string (char*,
terminating zero) and a byte array (char*, possibly containing zeros,
and I know the length). I want to obtain Datums wrapping these values
that will be used to bind varchar and bytea columns, and I'm trying to
find out how to generate these Datums.

This doesn't seem right:

   void* plan = SPI_prepare(...);
   char* string = ...;
   char* byte_array = ...;
   int byte_array_length = ...;
   Datum* values = (Datum*) palloc(sizeof(Datum) * 2);
   values[0] = CStringGetDatum(string);
   values[1] = PointerGetDatum(byte_array);
   SPI_execute_plan(plan, values, ...);

because the code does not communicate byte_array_length to
SPI_execute_plan.

I suspect I'm missing something important, because I don't know how to
get a bytea* from byte_array and byte_array_length.

Jack Orenstein

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Feature request (was psql: absolutes and toggles)

[Steve Crawford]

I would like the ability to absolutely set parameters/settings in psql
so that our psql scripts could generate predictable output absent a
known or controllable initial state. Original discussion at bottom of
message.

One alternate and easier approach I've thought of is to simply add
something akin to a \factory-reset meta-command which would return all
settings to the state they would be in immediately after starting psql
with the --no-psqlrc option. This would at least provide one solution to
the problem and might be a handy meta-command even if absolute settings
were added.

If a "factory reset" meta-command were added I think that \o should be
exempted as it is already an absolute setting that can be predictably
used in scripts and, where output redirection isn't specified in the
script, we shouldn't interfere with the ability to save the output of a
script or scripts as the user desires.

Cheers,
Steve

Peter Eisentraut wrote:
> Steve Crawford wrote:
>> We create psql scripts that can be used at various times by various
>> users. I have been unable to find how to absolutely set various
>>options (timing, expanded, etc.) rather than toggle them.

>> The --no-psqlrc option provides a partial workaround - as long as
>> the user remembers to include it and as long as they are only
>> running the one script. But if they forget or if they are already
>> running a session there is no telling what settings have been
>> toggled by previously run scripts or the users themselves.

>> So...have I overlooked an interactive psql option that will let me
>> reset all options to "factory-defaults" or a method of specifying an
>> absolute setting to the various options?
> 
> Probably not.
> 
>> If not, do psql users out there feel this is worth a feature request?
> 
> I think so.
> 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Brandon Aiken]

And yet this man is smart enough not to run MySQL on Windows.  Methinks
this says something

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joshua D. Drake
Sent: Thursday, September 14, 2006 5:16 PM
To: [EMAIL PROTECTED]
Cc: PostgreSQL General ML
Subject: Re: [GENERAL] PostgreSQL slammed by PHP creator

[EMAIL PROTECTED] wrote:
> [EMAIL PROTECTED] wrote on 15/09/2006 04:40:04 a.m.:
> 
>>> I wasn't able to find anything the "article" worth discussing. If
you 
>>> give up A, C, I, and D, of course you get better performance- just 
> like 
>>> you can get better performance from a wheel-less Yugo if you slide
it 
>>> down a luge track.
>> I don't think a Yugo would give you better performance no matter what

>> you do ;)
> 
> I found in my youth that a mini slides better on it roof than on it's 
> wheels.

I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.

Joshua D. Drake


-- 

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
  http://www.commandprompt.com/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

[EMAIL PROTECTED] wrote:

[EMAIL PROTECTED] wrote on 15/09/2006 04:40:04 a.m.:

I wasn't able to find anything the "article" worth discussing. If you 
give up A, C, I, and D, of course you get better performance- just 
like 
you can get better performance from a wheel-less Yugo if you slide it 
down a luge track.
I don't think a Yugo would give you better performance no matter what 
you do ;)


I found in my youth that a mini slides better on it roof than on it's 
wheels.


I can top that :). I hit a telephone pole at 135MPH in a 66 Cuda :)
If it hadn't been for the gravel around that corner, I would have beat 
the other car too.


Joshua D. Drake


--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Is it possible to have multiple names for a column?

[Bernhard Weisshuhn]

On Thu, Sep 14, 2006 at 12:18:05PM -0700, [EMAIL PROTECTED] wrote:

> We have a need to rename some columns, but since we can't update both
> the database and the programs instantly, we'd like to temporarily
> assign both names to the same column while the updates are in
> progress.  Something like this would be super nifty :-)
> 
> ALTER TABLE howdy_doody ADD NAME xyzzy TO COLUMN plugh;

If the clients only read the column, you could just add the new
column and create triggers that copy the value from the other column on
any modification.
Later you drop the old column and the triggers.

Maybe an approch based on rules might work? Dunno, haven't used them yet.

regards,
  bkw

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Sun Java Studio Creator and PostgreSQL

[Poul Møller Hansen]

I have some troubles getting Sun Java Studio Creator & Sun Java 
Application Server to work with

PostgreSQL.

Trying different syntaxes for the SQL statement (schema or no schema) in 
the session bean

I get either the error: "No columns in table: pmh.tablename" or
"Cannot change transaction isolation level in the middle of a transaction"
when trying to delete, insert or update rows. Selecting is ok.

The application works fine when using an Apache Derby database, so the 
problem is related to PostgreSQL


I can see several postings on other mailing lists with the same problem, 
but haven's found any solution.


Do anyone of you know what I can do ?


Thanks,
Poul



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Is it possible to have multiple names for a column?

[Scott Marlowe]

On Thu, 2006-09-14 at 14:18, [EMAIL PROTECTED] wrote:
> We have a need to rename some columns, but since we can't update both
> the database and the programs instantly, we'd like to temporarily
> assign both names to the same column while the updates are in
> progress.  Something like this would be super nifty :-)
> 
> ALTER TABLE howdy_doody ADD NAME xyzzy TO COLUMN plugh;
> 
> I am pretty certain no such SQL command exists.  But is it possible to
> do something sneaky to the internal tables so that two names point to
> the same columnand everything just works?  Everything meaning updates,
> inserts, etc, not further ALTER TABLE and so on.

You could create a view, make the two outside names reference the one
inside name, and create a trigger to update based on which one if given
data and throw an error if you try to update both columns at the same
time.

Sounds like a lot of work though.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Is it possible to have multiple names for a column?

[Tom Lane]

[EMAIL PROTECTED] writes:
> I am pretty certain no such SQL command exists.  But is it possible to
> do something sneaky to the internal tables so that two names point to
> the same columnand everything just works?

No ... at least not for usefully large values of "work".

You might be able to do something involving a view that renames the
column in question, with as-yet-unconverted clients made to reference
the view.  Offhand I'm not convinced that's easier than just fixing the
clients though ...

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Is it possible to have multiple names for a column?

[felix]

We have a need to rename some columns, but since we can't update both
the database and the programs instantly, we'd like to temporarily
assign both names to the same column while the updates are in
progress.  Something like this would be super nifty :-)

ALTER TABLE howdy_doody ADD NAME xyzzy TO COLUMN plugh;

I am pretty certain no such SQL command exists.  But is it possible to
do something sneaky to the internal tables so that two names point to
the same columnand everything just works?  Everything meaning updates,
inserts, etc, not further ALTER TABLE and so on.

-- 
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
 Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED]
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Stephen Frost]

* Arturo Perez ([EMAIL PROTECTED]) wrote:
> Any response to this:
> http://www.internetnews.com/dev-news/article.php/3631831

Turn fsync off and try again.  Don't expect your data to stay consistant
tho.

Enjoy,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] initdb: invalid locale name "sv_SE.ISO-8859-1"

[Ragnar Österlund]

Thanks for the advice, I tried to install the "language-packs" that
ubuntu supports. I can no initialize a se_SV.utf8 cluster, but it
seems there is no support form latin1.

Best whishes

/Ragnar

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Majordomo drops multi-line Subject:

[Stefan Kaltenbrunner]

Michelle Konzack wrote:
> Hello Marc,
> 
> Am 2006-09-09 12:50:36, schrieb Marc G. Fournier:
> 
>> And I missed this one from Joshua ... but, we aren't running Majordomo 
>> from GreatCircle, we are running Majordomo2 (http://www.mj2.org) which is 
>> very much being actively support ...
> 
> I have seen...
> 
>> Majordomo2 support a nomail option as well ...
> 
> I know, since some Mailinglist I am on are migrated to Majordomo2.
> Afaik does Mailman not support the "nomail" Option.
> 
> Same for ezmlm, sympha, and smartlist.

fwiw: "sympa" - and that one has a nomail option for ages ...


Stefan

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Client Connections

[Joshua D. Drake]

Curtis Scheer wrote:

Is there a setting to limit the number of database connections per IP
address or client?


No but you can limit per database as a whole.

Joshua D. Drake



 


Thanks,

 


Curtis





--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Majordomo drops multi-line Subject:

[Michelle Konzack]

Hello Marc,

Am 2006-09-09 12:50:36, schrieb Marc G. Fournier:

> And I missed this one from Joshua ... but, we aren't running Majordomo 
> from GreatCircle, we are running Majordomo2 (http://www.mj2.org) which is 
> very much being actively support ...

I have seen...

> Majordomo2 support a nomail option as well ...

I know, since some Mailinglist I am on are migrated to Majordomo2.
Afaik does Mailman not support the "nomail" Option.

Same for ezmlm, sympha, and smartlist.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] OK, this is a Test für a Subject containing one non ASCII character ans some more like éáóèàòôç [WAS: Re: [GENERAL] This is a Reply to the message "Re: Majordomo drops multi-line Subject

[Michelle Konzack]

Hi Alvaro,

This is a second test with some non US-ACII characters...

Since I am in France, I have never seen broken Subject lines.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


Am 2006-09-09 12:30:25, schrieb Alvaro Herrera:

> I don't know what the conditions are, but mutt frequently produces
> multiline Subjects on my messages, even when I'm answering mail whose
> Subject was not multiline.  Maybe the usage of non-ASCII chars has
> something to do with it.  If you search the archives of pgsql-es-ayuda
> you can find plenty of examples of this, where threads have changing
> subjects -- some of the mails from me have the truncated subject on the
> archives, while other mails on the same thread have the non-truncated
> subject.
> 
- END OF REPLIED MESSAGE -



-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

I wasn't able to find anything the "article" worth discussing. If you 
give up A, C, I, and D, of course you get better performance- just like 
you can get better performance from a wheel-less Yugo if you slide it 
down a luge track.


I don't think a Yugo would give you better performance no matter what 
you do ;)




-M

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

Alvaro Herrera wrote:

Joshua D. Drake wrote:

However, he does carry some umpf in certain circles. Perhaps we should 
prove him wrong?


I take it you mean "oomph"?


Yeah probably :)



Main Entry: oomph
Pronunciation: 'um(p)f
Function: noun
Etymology: imitative of a sound made under exertion
1 : personal charm or magnetism : GLAMOUR
2 : SEX APPEAL
3 : PUNCH, VITALITY




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Alvaro Herrera]

Joshua D. Drake wrote:

> However, he does carry some umpf in certain circles. Perhaps we should 
> prove him wrong?

I take it you mean "oomph"?

Main Entry: oomph
Pronunciation: 'um(p)f
Function: noun
Etymology: imitative of a sound made under exertion
1 : personal charm or magnetism : GLAMOUR
2 : SEX APPEAL
3 : PUNCH, VITALITY

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Joshua D. Drake]

Arturo Perez wrote:

Hi all,

Any response to this:
http://www.internetnews.com/dev-news/article.php/3631831

From the FA:

One performance enhancement that Lerdorf suggested based on code analysis was to use MySQL instead of PostgreSQL for the database. 

"If you can fit your problem into what MySQL can handle it's very fast," Lerdorf said. "You can gain quite a bit of performance." 

For the items that MySQL doesn't handle as well as PostgreSQL, Lerdorf noted that some features can be emulated in PHP itself, and you still end up with a net performance boost. 



Considering the quality piece of software that PHP is, I don't hold much 
weight to his comment.


However, he does carry some umpf in certain circles. Perhaps we should 
prove him wrong?


Joshua D. Drake




-arturo





--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Berend Tober]

AgentM wrote:



On Sep 14, 2006, at 11:27 , Arturo Perez wrote:



One performance enhancement that Lerdorf suggested based on code  
analysis was to use MySQL instead of PostgreSQL for the database.


...If you  give up A, C, I, and D, of course you get better 
performance- just  like you can get better performance from a 
wheel-less Yugo if you  slide it down a luge track.



ROTFL. That has got to go down as a classic quotation.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Client Connections

[Michael Fuhr]

On Thu, Sep 14, 2006 at 10:59:09AM -0500, Curtis Scheer wrote:
> Is there a setting to limit the number of database connections per IP
> address or client?

8.1 has per-database and per-user connection limits; see the
CONNECTION LIMIT option of {CREATE,ALTER} {DATABASE,ROLE}.

http://www.postgresql.org/docs/8.1/interactive/sql-createdatabase.html
http://www.postgresql.org/docs/8.1/interactive/sql-alterdatabase.html
http://www.postgresql.org/docs/8.1/interactive/sql-createrole.html
http://www.postgresql.org/docs/8.1/interactive/sql-alterrole.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] PostgreSQL slammed by PHP creator

[Bernhard Weisshuhn]

Arturo Perez wrote:


Any response to this:
http://www.internetnews.com/dev-news/article.php/3631831


Oh please! Can we skip this one?
Maybe on the advocacy groups, not on [GENERAL], pleze!

thanks,
bkw

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] PostgreSQL slammed by PHP creator

[AgentM]

On Sep 14, 2006, at 11:27 , Arturo Perez wrote:


Hi all,

Any response to this:
http://www.internetnews.com/dev-news/article.php/3631831

From the FA:

One performance enhancement that Lerdorf suggested based on code  
analysis was to use MySQL instead of PostgreSQL for the database.


"If you can fit your problem into what MySQL can handle it's very  
fast," Lerdorf said. "You can gain quite a bit of performance."


For the items that MySQL doesn't handle as well as PostgreSQL,  
Lerdorf noted that some features can be emulated in PHP itself, and  
you still end up with a net performance boost.


I wasn't able to find anything the "article" worth discussing. If you  
give up A, C, I, and D, of course you get better performance- just  
like you can get better performance from a wheel-less Yugo if you  
slide it down a luge track.


-M

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Client Connections

[Curtis Scheer]

Is there a setting to limit the number of database
connections per IP address or client?

 

Thanks,

 

Curtis

[GENERAL] pgAdmin III v1.6 Beta 1 Released

[Dave Page]

I'm pleased to announce the first beta version of pgAdmin III v1.6.0 is
available for download in Source, Windows, Slackware and Mac OSX formats
from:

http://www.pgadmin.org/download/

This is a beta version and may contain bugs - please test at your own
risk and report any bugs found to [EMAIL PROTECTED]

There are a huge number of improvements in pgAdmin 1.6 - for a complete
list, please see the changelog at:

http://www.pgadmin.org/development/changelog.php

Please note that translation work is still ongoing, therefore beta
releases may not include all the languages that pgAdmin eventually ships
with. If you are able to assist with the translation effort, please see
the status and HOWTO pages linked from:

http://www.pgadmin.org/translation/

Don't forget to let me know that you are working on a translation before
you start!!

Regards Dave
-- 
Dave Page
pgAdmin Project Lead

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Superuser lost access to particular database

[Tom Lane]

Francisco Reyes <[EMAIL PROTECTED]> writes:
> Going to try the FreeBSD lists to see why and when a process would not 
> show up in ktrace. Specially one using 90% cpu.

That's not hard: it's in a loop that doesn't include any kernel calls.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] When to reload and when to restart?

[Tom Lane]

Francisco Reyes <[EMAIL PROTECTED]> writes:
> Is there a list anywyere of what config changes take effect after a reload 
> and which after a restart?

These settings require a postmaster restart, nothing else does:
select name from pg_settings where context = 'postmaster';

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Running PostgreSQL in a Xen DomU?

[Ben]

My database needs are probably less than yours, but I've had zero  
issues running postgres inside a domU. I'm not using SAN, but instead  
DRBD to replicate data at the block level between dom0s. I haven't  
tried migrating the domU from one machine to another without  
rebooting it, but the shutdown/move/startup process works quite well  
for me.


I had some concerns that xen's virtual disk i/o speed wouldn't be  
fast enough... but my database performance has never been bad enough  
that I had to actually see how fast my domU can do i/o.


On Sep 14, 2006, at 5:04 AM, Thomas Harold wrote:

We're slowly trying to move away from hardware dependence for our  
servers and it looks like Xen is the path.  Our primary goal is  
that if a particular server fails, we can simply migrate the guest  
OSs to another Xen node and the users will not experience any  
downtime.  Plus it should allow us to consolidate a few servers.


Our database needs aren't that great at the moment, less then 50  
concurrent users even at the worst of times.  But we might have  
multi-gigabyte databases that will be queried in bursts and then  
left alone for a few days.  A single CPU is generally fast enough  
for us.


So our current thinking is:

- Place PG in a DomU that can be moved from host-to-host as needed

- Backend storage would be over a SAN (iSCSI, 9k jumbo frames,  
dedicated  NIC or bonded NICs for the SAN, dedicated switch or VLAN  
for the SAN)


- SAN unit itself would be DRBD'd to a 2nd SAN storage unit.   
That's down the road a bit once we build out the first few Xen nodes.


What I'm not sure of:

- Maybe it's better to run PGSQL in Dom0, on 2 different Xen units  
that are beefed up, with a few lighter DomU guest OSs running in  
the background.  Some sort of heartbeat software that allows the 2  
Xen units to grab the PGSQL's IP address as needed.


- Performance of PGSQL in a DomU.  What are the gotchas?  Do we  
need to export PCI NICs to the PGSQL DomU?


(I have a lot of freedom to experiment.  As long as services are up  
and running and things are stable...  We're going from individual  
machine with DAS to a more clustered/virtual environment with SAN.)


---(end of  
broadcast)---

TIP 4: Have you searched our list archives?

  http://archives.postgresql.org



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] PostgreSQL slammed by PHP creator

[Arturo Perez]

Title: PostgreSQL slammed by PHP creator






Hi all,


Any response to this:

http://www.internetnews.com/dev-news/article.php/3631831


From the FA:


One performance enhancement that Lerdorf suggested based on code analysis was to use MySQL instead of PostgreSQL for the database. 

"If you can fit your problem into what MySQL can handle it's very fast," Lerdorf said. "You can gain quite a bit of performance." 

For the items that MySQL doesn't handle as well as PostgreSQL, Lerdorf noted that some features can be emulated in PHP itself, and you still end up with a net performance boost. 

-arturo

[GENERAL] Optimising a query requiring seqscans=0

[Russ Brown]

Hi,

We recently upgraded our trac backend from sqlite to postgres, and I
decided to have a little fun and write some reports that delve into
trac's subversion cache, and got stuck with a query optimisation
problem.

Table revision contains 2800+ rows
Table node_change contains 37+.

rev is a 'TEXT' field on both containing numerical data (not my choice,
trac does it like this to support VCS backends with non-numerical
revision identifiers).

I've got stuck with this query:

   SELECT author, COUNT(DISTINCT r.rev)
 FROM revision AS r
LEFT JOIN node_change AS nc
   ON r.rev=nc.rev
WHERE r.time >= EXTRACT(epoch FROM (NOW() - interval '30
days'))::integer
 GROUP BY r.author;

Currently it shows the number of commits per author in the last 30 days.
The join to node_change is superfluous for this purpose but was added
because I intended to add new columns which draw on this table, such as
the number of files added, deleted etc.

I never got that far however due to the planner problem:

 GroupAggregate  (cost=56755.41..56852.28 rows=2 width=17) (actual
time=4836.433..4897.458 rows=25 loops=1)
   ->  Sort  (cost=56755.41..56787.69 rows=12913 width=17) (actual
time=4836.233..4851.968 rows=22419 loops=1)
 Sort Key: r.author
 ->  Merge Left Join  (cost=53886.10..55873.68 rows=12913
width=17) (actual time=4600.733..4641.749 rows=22419 loops=1)
   Merge Cond: ("outer".rev = "inner".rev)
   ->  Sort  (cost=93.78..96.24 rows=982 width=17) (actual
time=7.050..7.383 rows=1088 loops=1)
 Sort Key: r.rev
 ->  Index Scan using revision_time_idx on revision
r  (cost=0.01..44.98 rows=982 width=17) (actual time=0.191..4.014
rows=1088 loops=1)
   Index Cond: ("time" >=
(date_part('epoch'::text, (now() - '30 days'::interval)))::integer)
   ->  Sort  (cost=53792.32..54719.09 rows=370707 width=8)
(actual time=4203.665..4443.748 rows=346238 loops=1)
 Sort Key: nc.rev
 ->  Seq Scan on node_change nc
(cost=0.00..12852.07 rows=370707 width=8) (actual time=0.054..663.719
rows=370707 loops=1)
 Total runtime: 4911.430 ms

If I disable sequential scans I get the following explain:

 GroupAggregate  (cost=221145.13..221242.01 rows=2 width=17) (actual
time=286.348..348.268 rows=25 loops=1)
   ->  Sort  (cost=221145.13..221177.42 rows=12913 width=17) (actual
time=286.183..302.239 rows=22419 loops=1)
 Sort Key: r.author
 ->  Nested Loop Left Join  (cost=0.01..220263.40 rows=12913
width=17) (actual time=0.339..86.626 rows=22419 loops=1)
   ->  Index Scan using revision_time_idx on revision r
(cost=0.01..44.98 rows=982 width=17) (actual time=0.194..4.056 rows=1088
loops=1)
 Index Cond: ("time" >= (date_part('epoch'::text,
(now() - '30 days'::interval)))::integer)
   ->  Index Scan using node_change_rev_idx on node_change
nc  (cost=0.00..223.18 rows=86 width=8) (actual time=0.009..0.058
rows=21 loops=1088)
 Index Cond: ("outer".rev = nc.rev)
 Total runtime: 350.103 ms

Statistics are set to 20, and I have ANALYZEd both tables.

The report itself isn't important, but I'm using this as an exercise in
PostgreSQL query optimisation and planner tuning, so any help/hints
would be appreciated.

Thanks.

-- 

Russ


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Superuser lost access to particular database

[Francisco Reyes]

Tom Lane writes:


Nothing happens when I try to run the query.


So pg_dump seems off the hook.  Can you run the query, attach to the
backend with gdb, and see what it's doing?


Tried to set level debug5 to see if any extra output appeared in log. 
Nothing.


Going to read up on gdb.. tried ktrace, but strangely enough got no 
output.. Tried several other processes to make sure I had the right 
syntax.. and every other process I tried produced lots of data with ktrace. 
Going to try the FreeBSD lists to see why and when a process would not 
show up in ktrace. Specially one using 90% cpu.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] When to reload and when to restart?

[Francisco Reyes]

Is there a list anywyere of what config changes take effect after a reload 
and which after a restart?


Reading over a number of settings to optimize performance, but would like to 
know what settings require restart.. other than trying to use show to see if 
the change took effect.


Can all variables be seen from "show all" in psql?

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Running PostgreSQL in a Xen DomU?

[Joshua D. Drake]

What I'm not sure of:

- Maybe it's better to run PGSQL in Dom0, on 2 different Xen units that 
are beefed up, with a few lighter DomU guest OSs running in the 
background.  Some sort of heartbeat software that allows the 2 Xen units 
to grab the PGSQL's IP address as needed.


PostgreSQL performs very, very well on Xen even in DomU. It is one of 
the things that lends to the Xen credibility because they use us in 
their benchmarks.





- Performance of PGSQL in a DomU.  What are the gotchas?  Do we need to 
export PCI NICs to the PGSQL DomU?


You will take about a 5% hit over Dom0.

Sincerely,

Joshua D. Drake



(I have a lot of freedom to experiment.  As long as services are up and 
running and things are stable...  We're going from individual machine 
with DAS to a more clustered/virtual environment with SAN.)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Question about timestamp with time zone

[Tom Lane]

"Jan van der Weijde" <[EMAIL PROTECTED]> writes:
> When I insert a time stamp value '1903-08-07 00:00:00+02' into a table
> and next select it again using psql I get '1903-08-06 22:19:32+00:19'.
> I'm located in The Netherlands and before 1940 there was a so called
> Amsterdam Time that is UTC + 20. So that more or less explains the
> +00:19 that is returned.

Well, actually, what I see in the zic database is

# Amsterdam Mean Time was +00:19:32.13 exactly, but the .13 is omitted
# below because the current format requires GMTOFF to be an integer.
# Zone  NAMEGMTOFF  RULES   FORMAT  [UNTIL]
Zone Europe/Amsterdam   0:19:32 -   LMT 1835
0:19:32 Neth%s  1937 Jul  1
0:20NethNE%sT   1940 May 16 0:00 # Dutch Time
1:00C-Eur   CE%sT   1945 Apr  2 2:00
1:00NethCE%sT   1977
1:00EU  CE%sT

So converting midnight GMT+2 to the reported result seems right.  I
think the problem is that the timestamp output function isn't expecting
there to be any residual seconds in the zone GMT offset, and so doesn't
bother to display it.  As you say, the correct display would be
'1903-08-06 22:19:32+00:19:32'.

If we fix this we'd also have to fix timestamp_in to be willing to
accept such strings, too.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Question about timestamp with time zone

[Jan van der Weijde]

Hi 
All,
 
I have an issue with 
timestamp with time zone I don't understand.
 
When I insert a time 
stamp value '1903-08-07 00:00:00+02' into a table and next select it again using 
psql I get '1903-08-06 22:19:32+00:19'.
I'm located in The 
Netherlands and before 1940 there was a so called Amsterdam Time that is UTC + 
20. So that more or less explains the +00:19 that is 
returned.
However what I don't 
understand is that there is also a 'second-correction' of 32 in the 
timestamp value that is not returned in the time zone. 
I guess the returned 
values should have been '1903-08-06 22:19:32+00:19:32'. But because the time 
zone specification does not allow seconds I would 
expect '1903-08-06 
22:19:00+00:19'
 
Can anyone explain 
this?
Thanks for any 
help!
 
Regards,
Jan
 

Re: [GENERAL] Inherited tables vs UNION ALL views

[Tom Lane]

Florian Weimer <[EMAIL PROTECTED]> writes:
> I'm going to create a (manually) partioned table and wonder whether I
> should use inherited tables or an explicitly created view using UNION
> ALL.  Constraint exclusion is not important for this application
> (major updates will directly target the individual tables).

IIRC, these should be planned equivalently in 8.2, but they go through
different planner paths in 8.1 and before --- you'll want to check the
specific queries you have in mind and see whether they get planned as
you want.  C-E definitely only works on the inheritance case in 8.1.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] remote duplicate rows

[Berend Tober]

A. Kretschmer wrote:


am  Wed, dem 13.09.2006, um 15:46:58 -0700 mailte Junkone folgendes:
 


hI
i have a bad situation that i did not have primary key. so i have a
table like this
colname1colname2
1 apple
1 apple
2  orange
2   orange

It is a very large table. how do i remove the duplctes quickly annd
without much change.
   



begin;
alter table foo rename to tmp;
create table foo as select distinct * from tmp;
commit;
 


A couple potential problems here. First, you forgot to drop table tmp. But 
maybe that is good thing because although the OP hasn't told us anything else 
useful about the situation, and he has clearly contrived a simplistic facsimile 
of his real problem, to be useful the table most likely either has foreign key 
references, and/or is the primary key for other table foreign keys. You're 
suggestion will break whatever application this data base supports because all 
the foreign keys will point to table tmp rather than foo afterwards. Similarly, 
there is the problem of any indexes on the table that would be lost. But I 
suppose one can make the point that your suggestion is a great solution, given 
the contrived example and insufficient problem understanding presented by the 
OP -- I really think he needs more help than he realizes.

Regards,
Berend Tober



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] remote duplicate rows

[Andrews, Chris]

Dunno about quickly, but I usually do something like this (before slapping 
myself in the face for getting into that state):

CREATE TABLE tn_backup AS SELECT DISTINCT * FROM tn;
TRUNCATE TABLE tn;
INSERT INTO tn VALUES SELECT * from tn_backup;

(Where "tn" is the table name)

May not be the best way, but keeps indexes and stuff on the original table if 
you don't want to set them all up again. Me lazy?

That said, if you've got foriegn keys pointing at it, the truncate ain't going 
to work.

Or if you have your data exported as a tab or csv, the use sort | uniq on it and
shove it back in...



-Original Message-
From: Junkone [mailto:[EMAIL PROTECTED]
Sent: 13 September 2006 23:47
To: pgsql-general@postgresql.org
Subject: [GENERAL] remote duplicate rows


hI
i have a bad situation that i did not have primary key. so i have a
table like this
colname1colname2
1 apple
1 apple
2  orange
2   orange

It is a very large table. how do i remove the duplctes quickly annd
without much change.

Regards

Seede


-
The information contained in this email is confidential and is
intended for the recipient only. If you have received it in error,
please notify us immediately by reply email and then delete it from
your system. Please do not copy it or use it for any purposes, or
disclose its contents to any other person or store or copy this
information in any medium. The views contained in this email are
those of the author and not necessarily those of Lorien plc.


 

Thank you for your co-operation.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Inherited tables vs UNION ALL views

[Florian Weimer]

I'm going to create a (manually) partioned table and wonder whether I
should use inherited tables or an explicitly created view using UNION
ALL.  Constraint exclusion is not important for this application
(major updates will directly target the individual tables).

After these considerations, is there still a difference between the
two approaches?

-- 
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH   http://www.bfk.de/
Durlacher Allee 47tel: +49-721-96201-1
D-76131 Karlsruhe fax: +49-721-96201-99

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Running PostgreSQL in a Xen DomU?

[Thomas Harold]

We're slowly trying to move away from hardware dependence for our 
servers and it looks like Xen is the path.  Our primary goal is that if 
a particular server fails, we can simply migrate the guest OSs to 
another Xen node and the users will not experience any downtime.  Plus 
it should allow us to consolidate a few servers.


Our database needs aren't that great at the moment, less then 50 
concurrent users even at the worst of times.  But we might have 
multi-gigabyte databases that will be queried in bursts and then left 
alone for a few days.  A single CPU is generally fast enough for us.


So our current thinking is:

- Place PG in a DomU that can be moved from host-to-host as needed

- Backend storage would be over a SAN (iSCSI, 9k jumbo frames, dedicated 
 NIC or bonded NICs for the SAN, dedicated switch or VLAN for the SAN)


- SAN unit itself would be DRBD'd to a 2nd SAN storage unit.  That's 
down the road a bit once we build out the first few Xen nodes.


What I'm not sure of:

- Maybe it's better to run PGSQL in Dom0, on 2 different Xen units that 
are beefed up, with a few lighter DomU guest OSs running in the 
background.  Some sort of heartbeat software that allows the 2 Xen units 
to grab the PGSQL's IP address as needed.


- Performance of PGSQL in a DomU.  What are the gotchas?  Do we need to 
export PCI NICs to the PGSQL DomU?


(I have a lot of freedom to experiment.  As long as services are up and 
running and things are stable...  We're going from individual machine 
with DAS to a more clustered/virtual environment with SAN.)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] remote duplicate rows

[John Sidney-Woollett]

If you have a primary key value (or OID?) then you can delete the 
duplicates in situ using something like (untested)


-- should work if never more than 1 duplicate row for colname1, colname2

delete from table where pk_value in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) > 1
)

-- if you can have multiple duplicate rows for colname1, colname2
-- then you need something like

delete from table where pk_value not in (
select min(pk_value)
from table
group by colname1, colname2
having count(*) = 1
)

Hope that helps.

John

A. Kretschmer wrote:

am  Wed, dem 13.09.2006, um 15:46:58 -0700 mailte Junkone folgendes:

hI
i have a bad situation that i did not have primary key. so i have a
table like this
colname1colname2
1 apple
1 apple
2  orange
2   orange

It is a very large table. how do i remove the duplctes quickly annd
without much change.


begin;
alter table foo rename to tmp;
create table foo as select distinct * from tmp;
commit;

You should create a primary key now to avoid duplicated entries...


HTH, Andreas


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] select unique items in db

[Stijn Vanroye]

a schreef:

this doesnt work

SELECT DISTINCT on link *, rank(ts_vec, to_tsquery('default', $qtxt))
FROM feed_entry WHERE ts_vec @@ to_tsquery('default', $qtxt) ORDER
BY rank(ts_vec, to_tsquery('default', $qtxt)) DESC LIMIT 5 OFFSET 0

can you tell me how to get the DISTINCT elements in LINK
thanks


Just place your distinct fields between ().

example syntax:
SELECT DISTINCT ON (field1, field2, fieldn) field1, field4
in your case:
SELECT DISTINCT ON (link) *, rank[...]

Do remember that if you select different fields than the ones you use in 
the distinct, the results may not always be what you want.


e.g.:

suppose you have this data loaded in the a table:

id  field1  field2
--  --  --
1   foo fooble
2   bar barble
3   foo wobble
4   woo wibble
5   foo bobble


if you "select distinct on (field1) *"  which of the records with foo in 
field1 is going to be returned 1, 3 or 5? I believe this doesn't 
nescescarily has to be the same value each time.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings