PostgreSQL

EnterpriseDB

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [GENERAL] Probably been asked a hundred times before.

Lincoln Yeoh wrote:
> At 10:30 PM 6/24/2008, David Siebert wrote:
>> Which disto is best for running a Postgres server?

Just to add one more slightly different philosophy.

For servers I manage, I run the most conservative
and slow changing distros that only update security
releases (Debian Stable, RHEL are good choices; no
doubt Solaris would be too; Ubuntu updates too
frequently for my tastes). For the components less
core to our business (ssh, munin, etc) we trust the
distro provider to provide security updates and to do
the very minimum of other changes that might have
compatibility issues.

For the components that are more core to our
business, though, we get the source from the projects
themselves (like postgresql.org) and compile from
source. This gives us the advantages of being
totally in control of when updates occur, and of
having developers be able to attach debuggers if
need be.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [BUGS] BUG #4266: regress test: could not dump unrecognized node type: 925

"Clemens A. Fischer" <clefis@gmx.de> writes:
> here is a part of the output of the regression test:
> WARNING: could not dump unrecognized node type: 925
> DEBUG: parse tree:
> DETAIL: {QUERY :commandType 5 :querySource 0 :canSetTag
> true :utilityStmt {CREATESTMT

Oh, you've got debug_print_parse turned on. I'm not especially excited
about trying to make the regression tests pass under random nondefault
settings --- we could expend large amounts of effort that way, to very
little purpose.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] TODO Item: Allow pg_hba.conf to specify host names along with IP addresses

On Wednesday 25 June 2008 17:52, Dickson S. Guedes wrote:
> Thanks for all yours suggestions, use cases and opinion about this
> thread, I saw that there are more things to consider than I was
> thinking and this make me consider that it is a hard work to do for
> now.

Huh? You should get started on it ... it will just take longer than you
thought.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Latest on CITEXT 2.0

"David E. Wheeler" <david@kineticode.com> writes:
> So, are your certain about this?

See Turkish --- in that locale i and I are not an upper/lower pair,
instead they pair with some non-ASCII letters. There are likely
other cases but that's the counterexample I remember.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PERFORM] Federated Postgresql architecture ?

On Thu, Jun 26, 2008 at 4:33 PM, kevin kempter
<kevin@kevinkempterllc.com> wrote:
> Anyone have any experiences to share per setting up a federated architecture
> with PostgreSQL ? I wonder if the dblink contrib works well in a federated
> scenario, specifically in the setup of the federated views which equate to a
> select * from the same table on each federated server ?

Because Postgres currently lacks the ability to push down predicates
to individual nodes over a database link, you have to spend a good
amount of time writing PL set-returning functions capable of adding
appropriate WHERE clauses to queries sent over the link. There are
other things you can do, but it's mostly hackery at this point in
time. IIRC, David Fetter is trying to get some of the required
predicate information exposed for use in DBI-Link.

Not to self-plug, but if you require it, EnterpriseDB includes
Oracle-style database links (SELECT col FROM table@node) which support
predicate push-down.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

For the record, I just compiled libpq.dll successfully with pg8.2.3
and MSVC 2003...so something is causing troubles in pg8.3.3 with this
compiler version. Hiroshi let me know where to place that test code,
thanks.

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/

On 26-Jun-08, at 3:12 PM, Jeff McKenna wrote:

> Hi Hiroshi. What exact file am I to place your test in? win32.h on
> line#290 ?
>
>
> ---
> Jeff McKenna
> FOSS4G Consulting and Training Services
> http://www.gatewaygeomatics.com/
>
>
>
>
>
> On 26-Jun-08, at 12:42 PM, Hiroshi Saito wrote:
>
>> Hi.
>>
>> Um, Please try this.
>>
>> #include<windows.h>
>> extern void __cdecl _dosmaperr( unsigned long oserrno );
>> int main(int argc, char *argv[])
>> {
>> _dosmaperr(GetLastError());
>> return(0);
>> }
>>
>> Can errorless compile be performed?
>> Regards,
>> Hiroshi Saito
>>
>> ----- Original Message ----- From: "Jeff McKenna" <jmckenna@gatewaygeomatics.com
>> >
>> To: "pgsql-hackers" <pgsql-hackers@postgresql.org>
>> Sent: Thursday, June 26, 2008 10:04 PM
>> Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3
>>
>>
>>> Thanks Hiroshi. Unfortunately libpq.dll does not compile with
>>> MSVC 2003. Has anyone successfully compiled libpq for 8.3.3 (or
>>> CVS head) with MSVC 2003??
>>> ---
>>> Jeff McKenna
>>> FOSS4G Consulting and Training Services
>>> http://www.gatewaygeomatics.com/
>>> On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:
>>>> Hi.
>>>>
>>>> It is strange...
>>>> Problem is not reproduced although I use VC2005.
>>>>
>>>> The result of my nmake -f win32.mak is this. ...
>>>> Microsoft (R) Manifest Tool version 5.2.3790.2014
>>>> Copyright (c) Microsoft Corporation 2005.
>>>> All rights reserved.
>>>> cd ..\..
>>>> echo All Win32 parts have been built!
>>>> All Win32 parts have been built!
>>>>
>>>> C:\MinGW\home\HIROSHI\postgresql-8.3.3\src>
>>>>
>>>> It may be necessary to investigate the reference relation of
>>>> VC2003.
>>>> Does someone notice some?
>>>> Regards,
>>>> Hiroshi Saito
>>>> ----- Original Message ----- From: "Jeff McKenna" <jmckenna@gatewaygeomatics.com
>>>> >
>>>>
>>>>
>>>>> Hello,
>>>>> I am trying to compile libpq.dll with MSVC 2003 on windows,
>>>>> using postgresql-8.3.3, but I get the following compile error:
>>>>> Creating library .\Release\libpqdll.lib and object .\Release
>>>>> \libpqdll.exp
>>>>> libpq.lib(dirmod.obj) : error LNK2019: unresolved external
>>>>> symbol __dosmaperr r
>>>>> ferenced in function _pgwin32_safestat
>>>>> libpq.lib(dirent.obj) : error LNK2001: unresolved external
>>>>> symbol __dosmaperr
>>>>> .\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
>>>>> NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
>>>>> Stop.
>>>>> NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual
>>>>> Studio .NET 2003
>>>>> VC7\BIN\nmake.exe"' : return code '0x2'
>>>>> Stop.
>>>>> Does anyone have any ideas how to solve this??
>>>>> thanks.
>>>>> -jeff
>>>>> ---
>>>>> Jeff McKenna
>>>>> FOSS4G Consulting and Training Services
>>>>> http://www.gatewaygeomatics.com/
>>>>> --
>>>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>>
>>>> --
>>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[PERFORM] Federated Postgresql architecture ?

Hi List;

Anyone have any experiences to share per setting up a federated
architecture with PostgreSQL ? I wonder if the dblink contrib works
well in a federated scenario, specifically in the setup of the
federated views which equate to a select * from the same table on each
federated server ?

Thanks in advance...

/Kevin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[GENERAL] Undocumented Postgres error: "failed to fetch old tuple for AFTER trigger"

I'm running a very large series of commands - mainly DDL but some DML as well - in a large transaction. I get the following error, which doesn't seem to be documented:

"ERROR: failed to fetch old tuple for AFTER trigger
: COMMIT"

There are no triggers that I'm aware of. I've gotten this error when running the transaction on two different instances of the database. I also did a VACUUM FULL, which didn't help.

Breaking down the commands into a series of about 7 smaller transactions works fine. Each transaction runs totally fine by itself. But, when I run them all in one giant transaction, I get errors.

I'm running Postgres 8.2.1 on Windows XP. If anyone can help, I'd be very greatful.

Re[2]: [BUGS] BUG #4267: initdb fails

No, only "Program Files".
And I tried to install pgsql 8.2 (I have installed it sooner on that machine before OS reinstall) - the same error.
May be problem in filesystem permissions?

Vadim

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [PERFORM] Hardware vs Software Raid

On Thu, 26 Jun 2008, Peter T. Breuer wrote:

> "Also sprach Merlin Moncure:"
>> The linux software raid algorithms are highly optimized, and run on a
>
> I can confidently tell you that that's balderdash both as a Linux author
> and as a software RAID linux author (check the attributions in the
> kernel source, or look up something like "Raiding the Noosphere" on
> google).
>
>> presumably (much faster) cpu than what the controller supports.
>> However, there is still some extra oomph you can get out of letting
>> the raid controller do what the software raid can't...namely delay
>> sync for a time.
>
> There are several design problems left in software raid in the linux kernel.
> One of them is the need for extra memory to dispatch requests with and
> as (i.e. buffer heads and buffers, both). bhs should be OK since the
> small cache per device won't be exceeded while the raid driver itself
> serialises requests, which is essentially the case (it does not do any
> buffering, queuing, whatever .. and tries hard to avoid doing so). The
> need for extra buffers for the data is a problem. On different
> platforms different aspects of that problem are important (would you
> believe that on ARM mere copying takes so much cpu time that one wants
> to avoid it at all costs, whereas on intel it's a forgettable trivium).
>
> I also wouldn't aboslutely swear that request ordering is maintained
> under ordinary circumstances.

which flavor of linux raid are you talking about (the two main families I
am aware of are the md and dm ones)

David Lang

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [SQL] ANSI Standard

- Try SQL 2003 standards....92 is way old
- You'll find that even the big boys like Oracle, DB2 etc will diverge from SQL standards if they make more $$ thier way...let alone toys like MySQL and MS-SQL

Cheers
Medi

On Thu, Jun 26, 2008 at 12:19 PM, Steve Midgley <public@misuse.org> wrote:

At 02:20 AM 6/25/2008, pgsql-sql-owner@postgresql.org wrote:

Date: Tue, 24 Jun 2008 17:33:11 +0300
From: "Pascal Tufenkji" <ptufenkji@usj.edu.lb>
To: <pgsql-sql@postgresql.org>
Subject: ANSI Standard
Message-ID: <000601c8d607$3acda550$150fa8c0@interne.usj.edu.lb>

Hi,

How do I know if a function (or a certain sql syntax) in Postgres is a SQL
ANSI Standard, hence it works on all databases such as MySQL, SQL Server,
Oracle.

In general, I find that the Pg docs pretty clear state what is ANSI standard and what isn't within Pg. You can also view the ANSI-92 standard here:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

In terms of making sure you're cross platform compatible, I'd say you have to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle X, MS SQL X, ext) which you will test against and explicitly support. You will find that no matter how tightly you attempt to build your platform against ANSI-92 (or any other std) if you do not regularly test against a set of platforms, your solution will converge on supporting only the platforms you do regular test against.

I hope that helps,

Steve

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [HACKERS] Latest on CITEXT 2.0

David,

> When is it due, July 1? If so, yes, it should be. I could use a close
> review by someone who knows this shit a whole lot better than I do.

Well, that's what the commitfest is for. Go ahead and add yourself once you
post the new patch.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [GENERAL] Partial Index Too Literal?

use this

explain analyze select * from result where active = 't';

--- On Thu, 6/26/08, Phillip Mills <pmills@systemcore.ca> wrote:

From: Phillip Mills <pmills@systemcore.ca>
Subject: [GENERAL] Partial Index Too Literal?
To: pgsql-general@postgresql.org
Date: Thursday, June 26, 2008, 7:24 PM

Under somewhat unusual circumstances, rows in one of our tables have an 'active' flag with a true value. We check for these relatively often since they represent cases that need special handling. We've found through testing that having a partial index on that field works well. What seems odd to me, however, is that the index gets used only if the query is a textual match for how the index was specified.

That is, using an index defined as 'where active = true':
dev=# explain analyze select * from result where active = true;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) (actual time=7.868..7.868 rows=0 loops=1)
   Filter: active
   -> Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 width=0) (actual time=4.138..4.138 rows=16625 loops=1)
         Index Cond: (active = true)
Total runtime: 7.918 ms
(5 rows)

dev=# explain analyze select * from result where active is true;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual time=55.631..55.631 rows=0 loops=1)
   Filter: (active IS TRUE)
Total runtime: 55.668 ms
(3 rows)

This is version 8.2.6. Is there something I'm missing that could make these queries ever produce different results?

[pgsql-es-ayuda] dar de baja

Hola comunidad

Como me doy de baja de la lista amigos???

Gracias por toda la ayuda este tiempo

saludos

Atte.
----------------------------------------------------
Johnny Verdugo V.
Productor / Desarrollador Informático
Vortex Webzine / Dmy Medios

Celular: (56)-(9)- 84130666
Msn: jverdugo@vortex.cl
Skype: johnny_vortex
Google Talk: Jverdugo

www.vortex.cl
www.dmy.cl

Re: [PERFORM] Hardware vs Software Raid

On Thu, Jun 26, 2008 at 1:03 AM, Peter T. Breuer <ptb@inv.it.uc3m.es> wrote:
> "Also sprach Merlin Moncure:"
>> write back: raid controller can lie to host o/s. when o/s asks
>
> This is not what the linux software raid controller does, then. It
> does not queue requests internally at all, nor ack requests that have
> not already been acked by the components (modulo the fact that one can
> deliberately choose to have a slow component not be sync by allowing
> "write-behind" on it, in which case the "controller" will ack the
> incoming request after one of the compionents has been serviced,
> without waiting for both).
>
>> integrity and performance. 'write back' caching provides insane burst
>> IOPS (because you are writing to controller cache) and somewhat
>> improved sustained IOPS because the controller is reorganizing writes
>> on the fly in (hopefully) optimal fashion.
>
> This is what is provided by Linux file system and (ordinary) block
> device driver subsystem. It is deliberately eschewed by the soft raid
> driver, because any caching will already have been done above and below
> the driver, either in the FS or in the components.
>
>> > However the lack of extra buffering is really deliberate (double
>> > buffering is a horrible thing in many ways, not least because of the
>>
>> <snip>
>> completely unconvincing.
>
> But true. Therefore the problem in attaining conviction must be at your
> end. Double buffering just doubles the resources dedicated to a single
> request, without doing anything for it! It doubles the frequency with
> which one runs out of resources, it doubles the frequency of the burst
> limit being reached. It's deadly (deadlockly :) in the situation where

Only if those resources are drawn from the same pool. You are
oversimplifying a calculation that has many variables such as cost.
CPUs for example are introducing more cache levels (l1, l2, l3), etc.
Also, the different levels of cache have different capabilities.
Only the hardware controller cache is (optionally) allowed to delay
acknowledgment of a sync. In postgresql terms, we get roughly the
same effect with the computers entire working memory with fsync
disabled...so that we are trusting, rightly or wrongly, that all
writes will eventually make it to disk. In this case, the raid
controller cache is redundant and marginally useful.

> the receiving component device also needs resources in order to service
> the request, such as when the transport is network tcp (and I have my
> suspicions about scsi too).
>
>> the overhead of various cache layers is
>> completely minute compared to a full fault to disk that requires a
>> seek which is several orders of magnitude slower.
>
> That's aboslutely true when by "overhead" you mean "computation cycles"
> and absolutely false when by overhead you mean "memory resources", as I
> do. Double buffering is a killer.

Double buffering is most certainly _not_ a killer (or at least, _the_
killer) in practical terms. Most database systems that do any amount
of writing (that is, interesting databases) are bound by the ability
to randomly read and write to the storage medium, and only that.

This is why raid controllers come with a relatively small amount of
cache...there are diminishing returns from reorganizing writes. This
is also why up and coming storage technologies (like flash) are so
interesting. Disk drives have made only marginal improvements in
speed since the early 80's.

>> The linux software raid algorithms are highly optimized, and run on a
>
> I can confidently tell you that that's balderdash both as a Linux author

I'm just saying here that there is little/no cpu overhead for using
software raid on modern hardware.

> believe that on ARM mere copying takes so much cpu time that one wants
> to avoid it at all costs, whereas on intel it's a forgettable trivium).

This is a database list. The main area of interest is in dealing with
server class hardware.

merlin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [pgsql-es-ayuda] Obtener el más cercano

Jaime Casanova escribió:
> > SELECT * FROM t ORDER BY abs(x - x0) LIMIT 1;
>
> no entiendo como resuelve tu problema este SELECT, podrias dar un
> ejemplo poniendo valores?

La idea es que si tienes los valores de x = {1,12,30,102,340,323} y
buscas con x0 = 104 te entregue el registro con x = 102.

> > Pero me tinca que podría estar calculando la función abs para todos los
> > registros de la tabla, lo que no aprovecharía el índice B-Tree en la
> > columna x.
>
> creas un indice funcional
> create index idx1 on tabla (abs(x));

No sabía que existían los índices funcionales, pero de todos modos la
solución que me dio Alvaro Herrera es lo que quería.

Saludos

--
Daniel Hernández
http://www.scio.cl
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [GENERAL] Partial Index Too Literal?

On Thu, Jun 26, 2008 at 03:24:41PM -0400, Phillip Mills wrote:
> dev=# explain analyze select * from result where active = true;
> dev=# explain analyze select * from result where active is true;

> This is version 8.2.6. Is there something I'm missing that could make these
> queries ever produce different results?

As usual the counter example is when there a NULL where the two
expressions arn't equal. That this doesn't affect the result of the
query is I suppose a deficiency of the expression comparitor...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [HACKERS] Latest on CITEXT 2.0

On Jun 26, 2008, at 12:03, Josh Berkus wrote:

> Will this be ready for the July CommitFest?

When is it due, July 1? If so, yes, it should be. I could use a close
review by someone who knows this shit a whole lot better than I do.

Thanks,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] Obtener el más cercano

Alvaro Herrera escribió:
> Usa un UNION, por ej.
>
> select * from (
> (select * from t where x < 0.4 order by x desc limit 1)
> union all
> (select * from t where x > 0.4 order by x limit 1)
> ) qqch
> order by abs(x - 0.4);
>
> O sea tomas el mas cercano hacia arriba y hacia abajo, y luego escoges
> uno de entre ellos.
>
> Nota: obviamente en este caso no es realmente necesario el UNION ALL
> puesto que los conjuntos son disjuntos, pero el plan es ligeramente
> mejor.

Gracias, pensé que necesitaba algo especial, esa solución es simple.

Saludos

--
Daniel Hernández
http://www.scio.cl
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

[NOVICE] Sources of information about sizing of hardwares to run PostgreSQL

Hi all,

I need to specify servers and storage to run PostgreSQL. Does anyone
know any source of information (articles, presentations, books, etc.)
which describes methods of hardware sizing for running a large
PostgreSLQ installation?

Thank you in advance.

Sergio.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [PATCHES] Fix pg_ctl restart bug

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Alvaro Herrera wrote:
>
> > > Why do we need someone to complain? We know the bug is there. Has the
> > > code changed a lot in that area?
> >
> > Do we have the policy of backpatching every fix? I thought it was only
> > the major bugs we fixed in back branches. If someone wants to backpatch
> > it, feel free to do so.
>
> I think the policy is "we fix the bugs in supported releases". If you
> start making exceptions, it becomes needlessly complex.
>
> I've always assumed that I'm supposed to backpatch the bugs I fix in
> HEAD, however far is reasonable.

I thought we only backatched major bugs to prevent possible instability
when fixing minor bugs.

--
Bruce Momjian <bruce@momjian.us>

EnterpriseDB

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

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

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

[GENERAL] Partial Index Too Literal?

Under somewhat unusual circumstances, rows in one of our tables have an 'active' flag with a true value. We check for these relatively often since they represent cases that need special handling. We've found through testing that having a partial index on that field works well. What seems odd to me, however, is that the index gets used only if the query is a textual match for how the index was specified.

That is, using an index defined as 'where active = true':
dev=# explain analyze select * from result where active = true;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) (actual time=7.868..7.868 rows=0 loops=1)
   Filter: active
   -> Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 width=0) (actual time=4.138..4.138 rows=16625 loops=1)
         Index Cond: (active = true)
Total runtime: 7.918 ms
(5 rows)

dev=# explain analyze select * from result where active is true;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual time=55.631..55.631 rows=0 loops=1)
   Filter: (active IS TRUE)
Total runtime: 55.668 ms
(3 rows)

This is version 8.2.6. Is there something I'm missing that could make these queries ever produce different results?

Re: [PATCHES] Fix pg_ctl restart bug

Bruce Momjian wrote:
> Alvaro Herrera wrote:

> > Why do we need someone to complain? We know the bug is there. Has the
> > code changed a lot in that area?
>
> Do we have the policy of backpatching every fix? I thought it was only
> the major bugs we fixed in back branches. If someone wants to backpatch
> it, feel free to do so.

I think the policy is "we fix the bugs in supported releases". If you
start making exceptions, it becomes needlessly complex.

I've always assumed that I'm supposed to backpatch the bugs I fix in
HEAD, however far is reasonable.

--
Alvaro Herrera

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

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [SQL] ANSI Standard

At 02:20 AM 6/25/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Tue, 24 Jun 2008 17:33:11 +0300
>From: "Pascal Tufenkji" <ptufenkji@usj.edu.lb>
>To: <pgsql-sql@postgresql.org>
>Subject: ANSI Standard
>Message-ID: <000601c8d607$3acda550$150fa8c0@interne.usj.edu.lb>
>
>Hi,
>
>
>
>How do I know if a function (or a certain sql syntax) in Postgres is a
>SQL
>ANSI Standard, hence it works on all databases such as MySQL, SQL
>Server,
>Oracle.

In general, I find that the Pg docs pretty clear state what is ANSI
standard and what isn't within Pg. You can also view the ANSI-92
standard here:

In terms of making sure you're cross platform compatible, I'd say you
have to designate a series of platforms (e.g. MySQL 5, Pg 8.3, Oracle
X, MS SQL X, ext) which you will test against and explicitly support.
You will find that no matter how tightly you attempt to build your
platform against ANSI-92 (or any other std) if you do not regularly
test against a set of platforms, your solution will converge on
supporting only the platforms you do regular test against.

I hope that helps,

Steve

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[COMMITTERS] pgsql: Add MERGE TODO URL: >

Log Message:
-----------
Add MERGE TODO URL:

http://archives.postgresql.org/pgsql-hackers/2008-04/msg01890.php

Modified Files:
--------------
pgsql/doc:
TODO (r1.2475 -> r1.2476)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO?r1=1.2475&r2=1.2476)
pgsql/doc/src/FAQ:
TODO.html (r1.981 -> r1.982)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html?r1=1.981&r2=1.982)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

Hi Hiroshi. What exact file am I to place your test in? win32.h on
line#290 ?

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/

On 26-Jun-08, at 12:42 PM, Hiroshi Saito wrote:

> Hi.
>
> Um, Please try this.
>
> #include<windows.h>
> extern void __cdecl _dosmaperr( unsigned long oserrno );
> int main(int argc, char *argv[])
> {
> _dosmaperr(GetLastError());
> return(0);
> }
>
> Can errorless compile be performed?
> Regards,
> Hiroshi Saito
>
> ----- Original Message ----- From: "Jeff McKenna" <jmckenna@gatewaygeomatics.com
> >
> To: "pgsql-hackers" <pgsql-hackers@postgresql.org>
> Sent: Thursday, June 26, 2008 10:04 PM
> Subject: Re: [HACKERS] MSVC 2003 compile error with pg8.3.3
>
>
>> Thanks Hiroshi. Unfortunately libpq.dll does not compile with
>> MSVC 2003. Has anyone successfully compiled libpq for 8.3.3 (or
>> CVS head) with MSVC 2003??
>> ---
>> Jeff McKenna
>> FOSS4G Consulting and Training Services
>> http://www.gatewaygeomatics.com/
>> On 24-Jun-08, at 8:31 PM, Hiroshi Saito wrote:
>>> Hi.
>>>
>>> It is strange...
>>> Problem is not reproduced although I use VC2005.
>>>
>>> The result of my nmake -f win32.mak is this. ...
>>> Microsoft (R) Manifest Tool version 5.2.3790.2014
>>> Copyright (c) Microsoft Corporation 2005.
>>> All rights reserved.
>>> cd ..\..
>>> echo All Win32 parts have been built!
>>> All Win32 parts have been built!
>>>
>>> C:\MinGW\home\HIROSHI\postgresql-8.3.3\src>
>>>
>>> It may be necessary to investigate the reference relation of VC2003.
>>> Does someone notice some?
>>> Regards,
>>> Hiroshi Saito
>>> ----- Original Message ----- From: "Jeff McKenna" <jmckenna@gatewaygeomatics.com
>>> >
>>>
>>>
>>>> Hello,
>>>> I am trying to compile libpq.dll with MSVC 2003 on windows,
>>>> using postgresql-8.3.3, but I get the following compile error:
>>>> Creating library .\Release\libpqdll.lib and object .\Release
>>>> \libpqdll.exp
>>>> libpq.lib(dirmod.obj) : error LNK2019: unresolved external
>>>> symbol __dosmaperr r
>>>> ferenced in function _pgwin32_safestat
>>>> libpq.lib(dirent.obj) : error LNK2001: unresolved external
>>>> symbol __dosmaperr
>>>> .\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
>>>> NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
>>>> Stop.
>>>> NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual
>>>> Studio .NET 2003
>>>> VC7\BIN\nmake.exe"' : return code '0x2'
>>>> Stop.
>>>> Does anyone have any ideas how to solve this??
>>>> thanks.
>>>> -jeff
>>>> ---
>>>> Jeff McKenna
>>>> FOSS4G Consulting and Training Services
>>>> http://www.gatewaygeomatics.com/
>>>> --
>>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] Fix pg_ctl restart bug

Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.551
diff -c -c -r1.551 postmaster.c
*** src/backend/postmaster/postmaster.c 11 Jan 2008 00:54:09 -0000 1.551
--- src/backend/postmaster/postmaster.c 26 Jun 2008 19:11:37 -0000
***************
*** 4163,4169 ****

fprintf(fp, "%s", fullprogname);
for (i = 1; i < argc; i++)
! fprintf(fp, " %s%s%s", SYSTEMQUOTE, argv[i], SYSTEMQUOTE);
fputs("\n", fp);

if (fclose(fp))
--- 4163,4169 ----

fprintf(fp, "%s", fullprogname);
for (i = 1; i < argc; i++)
! fprintf(fp, " \"%s\"", argv[i]);
fputs("\n", fp);

if (fclose(fp))
Index: src/bin/pg_ctl/pg_ctl.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_ctl/pg_ctl.c,v
retrieving revision 1.92.2.3
diff -c -c -r1.92.2.3 pg_ctl.c
*** src/bin/pg_ctl/pg_ctl.c 29 Feb 2008 23:31:42 -0000 1.92.2.3
--- src/bin/pg_ctl/pg_ctl.c 26 Jun 2008 19:11:37 -0000
***************
*** 613,627 ****
{
char *arg1;

! arg1 = strchr(optline, *SYSTEMQUOTE);
! if (arg1 == NULL || arg1 == optline)
! post_opts = "";
! else
{
! *(arg1 - 1) = '\0'; /* this should be a space */
! post_opts = arg1;
}
! if (postgres_path != NULL)
postgres_path = optline;
}
else
--- 613,629 ----
{
char *arg1;

! /*
! * Are we at the first option, as defined by space and
! * double-quote?
! */
! if ((arg1 = strstr(optline, " \"")) != NULL ||
! (arg1 = strstr(optline, " -")) != NULL)
{
! *arg1 = '\0'; /* terminate so we get only program name */
! post_opts = arg1 + 1; /* point past whitespace */
}
! if (postgres_path == NULL)
postgres_path = optline;
}
else
Bruce Momjian wrote:
> I am attaching a minimal patch that will fix the bug in back branches.
> Keep in mind that a patched pg_ctl will not be able to restart a backend
> that was not patched.

I think this patch will work for unpatched backends as well. I am still
uncertain if it should be backpatched.

--
Bruce Momjian <bruce@momjian.us>

EnterpriseDB

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

Re: [HACKERS] Latest on CITEXT 2.0

David,

> Thanks. I've just completely refactored things to look more like the
> approach taken by varlena.c, both in terms of when stuff gets freed
> and in terms of coding style. It's more verbose, but I feel much more
> comfortable with memory management now that I'm following a known
> implementation more closely. :-)

Will this be ready for the July CommitFest?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] Fix pg_ctl restart bug

Bruce Momjian wrote:
> > > > > "", meaning zero-length string. I should have seen the bug when I
> > > > > applied the contributed patch in 2004.
> > > >
> > > > So, shouldn't this fix be back-patched?
> > >
> > > Well, no one has actually complained about the breakage, and it has been
> > > a few years. Also there is always the risk of a new bug being
> > > introduced, so I am unsure.
> >
> > Why do we need someone to complain? We know the bug is there. Has the
> > code changed a lot in that area?
>
> Do we have the policy of backpatching every fix? I thought it was only
> the major bugs we fixed in back branches. If someone wants to backpatch
> it, feel free to do so.

OK, I started looking at what it would take to backpatch this and found
another bug I have fixed in CVS HEAD. What back branchs (8.0-8.3.X) are
doing is pretty odd. On non-Win32 systems, it is looking for the null
byte, then putting a null byte before it, and passing a NULL back as the
options and binary location. The test:

if (postgres_path != NULL)
postgres_path = optline;

is backwards, which means that if in 8.3.X you start the server with any
arguments, like:

/usr/var/local/postgres/bin/postgres -i -o -d5

and you use pg_ctl to specify the binary location:

pg_ctl -p /u/pg/bin/postmaster restart

the server actually fails to restart because it chops off the last byte
(a bug) and the test above is wrong (another bug), and it thinks the
binary name is the full string, in quotes:

/usr/var/local/postgres/bin/postgres -i -o -d

and you get this error from pg_ctl:

sh: /usr/var/local/postgres/bin/postgres -i -o -d: not found

This is more than just ignoring the documentation, it is a failure.

I am attaching a minimal patch that will fix the bug in back branches.
Keep in mind that a patched pg_ctl will not be able to restart a backend
that was not patched.

--
Bruce Momjian <bruce@momjian.us>

EnterpriseDB

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

Re: [ADMIN] Extended security/restriction to any role with login access

hello...

you can restric acces from all the databases in your cluster. When you use pgadmin3 this show all the databases but if you dont have access to the databases you cant see the struct of this.

check waht user use pgadmin3 for connect to databases

create groups and add privileges to the group later add the users to the group

--- On Thu, 6/26/08, Carol Walter <walterc@indiana.edu> wrote:

From: Carol Walter <walterc@indiana.edu>
Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: "Domingo Alvarez Duarte" <mingodad@gmail.com>
Cc: pgsql-admin@postgresql.org
Date: Thursday, June 26, 2008, 5:34 PM

Hello, Domingo,

My question is why do your  users need access to pgadmin3?
I have not used pgadmin3; we use phpPgAdmin.  I can restrict access  
to that by putting it behind .htaccess.  That is only users with a  
user name in .htacess can run phpPgAdmin.  In the case of pgadmin3,  
shouldn't you be able to restrict access to it by setting privs at  
the operating system level?  With phpPgAdmin, I can also restrict it  
so a user can only see the databases the s/he owns.  Postgres owns my  
databases so I can't do it this way, but it could be done.

Carol

On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:

> Hello !
>
> I'm trying to use postgresql in an application that by design will  
> give access to users to a subset of the database.
>
> For example for customers access to products_view (wich will only  
> show public offers), orders (only their own orders).
>
> I'll provide an application as  user interface for the data.
>
> For that I'll give for each of then a role in the database that  
> will belong to a group role customers_group.
>
> The customers_group only has access to the views/functions that  
> I'll specify.
>
> Till here no problem postgresql do that pretty well.
>
> My concern is once I give login access to any user, even without  
> grant him/her any access to any database, he/she can using an  
> application like pgadmin3 view all databases/roles/functions/table- 
> definitions on my server. And that was not my intention.
>
> Removing all from public doesn't work : revoke all on schema public  
> from public;
>
> What I think would be the server behavior when I create a role with  
> login access an say that I only grant access to one view like this:
>
> create role oneuser login;
> grant select  on somedatabase.someview to oneuser;
>
> In that case when the user login the only thing he/she sees is the  
> view database.someview, even when they use pgadmin3 to connect.
>
> Actually he/she can see with pgadmin3 : all databases, all roles  
> and it's right access, all tables on every database (no access to  
> data), all functions, all triggers, all table definitions.
>
> The above isn't the intention to a user with a restrict view of the  
> database.
>
> Can I achieve it actually, if not how hard could be to implement  
> that in the official release ?
>
> Thanks in advance for any feedback/ideas !


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Warm standby server

On Thu, 2008-06-26 at 13:01 -0500, Scott Whitney wrote:

> A 2nd question: Is it possible to have 2 standby servers with a single
> master duplicating to standby1 (at my coloc), and standby2 (at my office)?
> Assume no auto-failover.

Yes, that works too.

--
Simon Riggs

PostgreSQL Training, Services and Support

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [HACKERS] get_relation_stats_hook()

On Thu, 2008-06-26 at 12:50 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> >> Surely you didn't mean ALL calls. Please be more specific about what
> >> you're proposing.
>
> > The statistics relation STATRELATT is accessed in a few places in the
> > planner. Since it is in the syscache it is accessed directly from there.
> > I would like to add hooks so that stats data can come from somewhere
> > else other than the syscache for tables, just as we can already do with
> > get_relation_stats_hook().
>
> Well, defining the hooks as replacing STATRELATT lookups seems the wrong
> level of abstraction. What if you want to insert stats that can't be
> represented by the current pg_statistic definition? Even if there's no
> functional limitation, cons'ing up a dummy pg_statistic tuple seems the
> hard way to do it --- we didn't define get_relation_info_hook as working
> by supplying made-up catalog rows. Furthermore, many of the interesting
> cases that someone might want to hook into are code paths that don't
> even try to look up a pg_statistic row because they know there won't be
> one, such as two out of the three cases in examine_variable().

The reason for doing it this way is I'm interested in using stats
literally copied from other servers. So the pg_statistic tuples will be
available for us directly. I'm building a tool to allow people to export
their production environment to a test system, so that SQL developers
can experiment with query tuning and optimizer developers can recreate
problems.

> I think you need to move up a level, and perhaps refactor some of the
> existing code to make it easier to inject made-up stats.

Both sound like good ideas. I wasn't really after ultimate flexibility,
but perhaps I should be.

--
Simon Riggs

PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] Warm standby server

I was thinking along the same lines. However, having 2 standby servers is a
must for us. If my colocation center gets razed to the ground, I need to
have a standby server elsewhere. Having one at the colocation center is for
soft/hardware disasters on any primary. Having one at my office is for
facility disasters at the colocation center.

I envision it working like this:

The archive command pseudocode will be:

rsync <mywalfile> someone@abox

The standby slave servers will then continually rsync that directory while
in recovery mode.

Reading the docs, it looks as if this should work.

It's also my understanding that the archive command happens asynchronously,
so it shouldn't affect the overall performance of the primary. Regardless,
my proposed solution should handle such a scenario.

-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 1:21 PM
To: Scott Whitney
Cc: Simon Riggs; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server

As WAL writes the journals all the way down to the disk (ie write thru and
not write behind) before ack-ing toward the next step in a DB operation,
increasing the number of mirrors (one production, one on-site, one off-site,
I count 3 plexes here) will prolong each operation, with the following
exponentially increasing write latencies

production DB writes are at the rate of SCSI, SATA or system bus (30 MBps)
on-site DB writes are at the rate of LAN (10 MBps)
off-site DB writes are at the rate of WAN (200 KBps)

Then if a three-way WAL writes is considered completed after the last WAN
write, then you have effectively lowered your performance to 200 KBytes per
sec writes. Now the gain. If the building gets destroyed, my data is
protected. Ok. what kind of business are we running in that building? ....
what is the rate of writes to database vs probability of building coming
down vs value of data from 2 hours ago vs 10 seconds ago.

Thanks
Medi

On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney <swhitney@journyx.com>
wrote:

Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be
discussing
those very merits.

A 2nd question: Is it possible to have 2 standby servers with a
single
master duplicating to standby1 (at my coloc), and standby2 (at my
office)?
Assume no auto-failover.

-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server

I am not so sure of this arrangement's mertis

From HA (High Availability) point of view, the host/server is a
single point
of failure which will bring your entire infrastructure down if any
of the
server hardware components fail.

From Performance point of view, you have increased the load on your
server
by 3 folds as all instances would be using your I/O bandwidth to
write to
secondary storage

Given $300 to $400 price of headless servers these days, its much
economical
to split the workload on three boxes

Cheers
Medi

On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com>
wrote:

On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> I've got 3 different database servers (db01, db02 and
db03).
>
> I would like to have a WAL standby server that replays logs
for
all 3 in
> case one goes down, so I can promote that particular
server.
>
> Can I do this by installing 3 separate postmasters on this
machine?
> Obviously, if 2 went down at the same time, I'd have to do
some
magic to
> bring up another machine, but I'm not sure that's a
concern.

Yes, that will work.

--
Simon Riggs

http://www.postgresql.org/mailpref/pgsql-admin

PostgreSQL Training, Services and Support

--
Sent via pgsql-admin mailing list
(pgsql-admin@postgresql.org)
To make changes to your subscription:

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[COMMITTERS] pgsql: Fix pg_ctl bug where detection of binary location from

Log Message:
-----------
Fix pg_ctl bug where detection of binary location from postmaster.opts
wasn't working.

Modified Files:
--------------
pgsql/src/bin/pg_ctl:
pg_ctl.c (r1.102 -> r1.103)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_ctl/pg_ctl.c?r1=1.102&r2=1.103)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [NOVICE] Upgrade Ubuntu on machine with Postgres 8.3

Lee Hachadoorian wrote:
> BTW, where do I find the Debian-specific pg_createcluster,
> pg_dropcluster, etc....
>
If you (in all likelihood) installed postgresql-common you should have them:
http://packages.ubuntu.com/gutsy/all/postgresql-common/filelist

Cheers,
Steve

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [ADMIN] Warm standby server

While I am not an expert on WAL, but again I question the merits of such sophisticated HA configuration. Of course there are use cases for such configs, but I am only advocating best price performance kind of mentality

As WAL writes the journals all the way down to the disk (ie write thru and not write behind) before ack-ing toward the next step in a DB operation, increasing the number of mirrors (one production, one on-site, one off-site, I count 3 plexes here) will prolong each operation, with the following exponentially increasing write latencies

production DB writes are at the rate of SCSI, SATA or system bus (30 MBps)
on-site DB writes are at the rate of LAN (10 MBps)
off-site DB writes are at the rate of WAN (200 KBps)

Then if a three-way WAL writes is considered completed after the last WAN write, then you have effectively lowered your performance to 200 KBytes per sec writes. Now the gain. If the building gets destroyed, my data is protected. Ok. what kind of business are we running in that building? .... what is the rate of writes to database vs probability of building coming down vs value of data from 2 hours ago vs 10 seconds ago.

Thanks
Medi

On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney <swhitney@journyx.com> wrote:

Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be discussing
those very merits.

A 2nd question: Is it possible to have 2 standby servers with a single
master duplicating to standby1 (at my coloc), and standby2 (at my office)?
Assume no auto-failover.

-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server

I am not so sure of this arrangement's mertis

From HA (High Availability) point of view, the host/server is a single point
of failure which will bring your entire infrastructure down if any of the
server hardware components fail.

From Performance point of view, you have increased the load on your server
by 3 folds as all instances would be using your I/O bandwidth to write to
secondary storage

Given $300 to $400 price of headless servers these days, its much economical
to split the workload on three boxes

Cheers
Medi

On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> I've got 3 different database servers (db01, db02 and db03).
>
> I would like to have a WAL standby server that replays logs for
all 3 in
> case one goes down, so I can promote that particular server.
>
> Can I do this by installing 3 separate postmasters on this
machine?
> Obviously, if 2 went down at the same time, I'd have to do some
magic to
> bring up another machine, but I'm not sure that's a concern.

Yes, that will work.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [GENERAL] new RETURNING clause and Pg.pm

b == bmetcalf@cash.us.nortel.com writes:

b> t == tgl@sss.pgh.pa.us writes:

b> t> "Brandon Metcalf" <bmetcalf@nortel.com> writes:
b> t> > I just upgraded to 8.3.3 and taking advantage of the RETURNING clause
b> t> > which is really cool. I've found that with Pg.pm $r->resultStatus
b> t> > returns the integer "2" when the RETURNING clause is used on an
b> t> > insert.

b> t> > Of course, without using RETURNING the status is the constant
b> t> > PGRES_COMMAND_OK.

b> t> Sounds to me like a bug in Pg.pm --- it's probably not expecting
b> t> a result to come back from an INSERT. You oughta nag its author
b> t> about that.

b> I'll look through the Pg.pm code and see what I can find.

My mistake. The constant that gets returned is PGRES_TUPLES_OK which
is what I would expect. This corresponds to 2.

--
Brandon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [HACKERS] Planner creating ineffective plans on LEFT OUTER joins

On Thu, 2008-06-26 at 12:57 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Thu, 2008-06-26 at 12:36 -0400, Robert Haas wrote:
> >> It's my understanding that the philosophy of the PGDG in the past has
> >> been to avoid putting any kind of hints into the system, focusing
> >> rather an improving the planning of queries.
>
> > It's not a specific hint, its a general goal setting.
>
> Right. There are definitely places where we've made engineering
> judgements to not attempt a particular type of optimization because it'd
> be too expensive compared to the typical payoff. Simon's idea has some
> merit for providing a framework to deal with that type of situation.
> However, just adding a GUC variable isn't going to make anything happen
> --- we'd need some concrete plans about what we'd do with it.

Well, I'm convinced the egg came first.

So I figure to put the framework in place and then start reviewing
things to see if they can be categorised. Plus I want new optimizer
features to be considered in the light of the new framework. This also
allows us a way of handling optimizer performance bugs. We just
reclassify certain cases as being costs-more solutions, rather than
stripping the code out entirely.

--
Simon Riggs

PostgreSQL Training, Services and Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [ADMIN] Warm standby server

Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be discussing
those very merits.

A 2nd question: Is it possible to have 2 standby servers with a single
master duplicating to standby1 (at my coloc), and standby2 (at my office)?
Assume no auto-failover.

-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server

I am not so sure of this arrangement's mertis

From HA (High Availability) point of view, the host/server is a single point
of failure which will bring your entire infrastructure down if any of the
server hardware components fail.

From Performance point of view, you have increased the load on your server
by 3 folds as all instances would be using your I/O bandwidth to write to
secondary storage

Given $300 to $400 price of headless servers these days, its much economical
to split the workload on three boxes

Cheers
Medi

On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> I've got 3 different database servers (db01, db02 and db03).
>
> I would like to have a WAL standby server that replays logs for
all 3 in
> case one goes down, so I can promote that particular server.
>
> Can I do this by installing 3 separate postmasters on this
machine?
> Obviously, if 2 went down at the same time, I'd have to do some
magic to
> bring up another machine, but I'm not sure that's a concern.

Yes, that will work.

--
Simon Riggs