Re: [GENERAL] Reload only specific databases from pg_dumpall

[Guido Neitzer]

On 06.02.2008, at 19:41, Andrej Ricnik-Bay wrote:


And a more generic version :}


Thanks for that!

I had solved my problem as I saw that actually importing all database  
from the dump file was faster than compressing the dump file (on a 4  
core Woodcrest). So it wasn't that big of a deal that I thought it  
would be.


Nevertheless good to have that.

cug

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

  http://archives.postgresql.org/

Re: [GENERAL] Reload only specific databases from pg_dumpall

[Guido Neitzer]

On 04.02.2008, at 18:00, Gurjeet Singh wrote:

I understand it all needs a little bit of 'vi' wizardry, (or  
whichever editor you are using). Also, as with all open-source  
suggestions, do not rely on this procedure until you understand what  
and how it does what it does.


Sure, the only problem is, I'm talking about a lrge file. Maybe I  
just import the whole bunch and drop some of the databases after that.  
Seems to be less hazzle, but takes probably an hour or two longer, so  
I'll take a service window next night ...


Thanks for the hints.

cug

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

Re: [GENERAL] Reload only specific databases from pg_dumpall

[Guido Neitzer]

On 04.02.2008, at 10:41, Gurjeet Singh wrote:

What was the output format option used (-f option) ? Was it the  
plain-text (SQL) or custom format?


I cannot see a -f option on pg_dumpall. This is the command:

pg_dumpall > `date "+%y-%m-%d"`.sql

I just want to use an older file from a dump to restore a server, but  
I don't want to reload all databases (because that will take way  
longer).


cug

---(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] Reload only specific databases from pg_dumpall

[Guido Neitzer]

Hi.

Is it possible to reload only specific databases from a file created  
by pg_dumpall?


Thanks,
cug

---(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] Experiences with extensibility

[Guido Neitzer]

On 09.01.2008, at 13:51, Martin wrote:


I've been working with FrontBase a lot lately and I wouldn't say
anything about it qualifies as "incredibly easy" and reliable it
is not.


We had never ever any reliability issues with FrontBase as long as  
didn't try to insert garbage. It really doesn't like that.



Performance of FrontBase is just plain terrible. One of
our reports takes 9 minutes on FrontBase and 10 seconds on
Postgres.


As I said: depends on what you are doing.

Which version did you use where you got that terrible performance? The  
latest one? There was a bug in there query planner in an old version  
that totally killed some queries.


There is also the or-query problem (not sure whether that one is still  
in there): if your report does something like "select bar from foo  
where a = 1 or b = 1;" it didn't use indexes on a or b which is  
terrible. If you have that, use separate selects combined with a union  
if you can.


In my cases it was never as fast as PostgreSQL as soon as there are  
more than two tables involved. But except a couple of cases where I  
had to use PostgreSQL for performance reasons, I was okay with it. But  
performance is not everything.


PostgreSQL is for most cases the better product, but for some cases,  
FrontBase is. Depends on what you're doing how much which side has ...



Then there's the documentation issue...


PostgreSQL documentation is better, but what is your issue exactly?  
The main problem I have with the FrontBase documentation is that it  
mostly gives no useful examples.


As this is off-topic here: we can transfer that either to the  
FrontBase list or to private mail if you like.


cug

--
http://www.event-s.net


---(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] Experiences with extensibility

[Guido Neitzer]

On 09.01.2008, at 13:51, Martin wrote:


I've been working with FrontBase a lot lately and I wouldn't say
anything about it qualifies as "incredibly easy" and reliable it
is not.


We had never ever any reliability issues with FrontBase as long as  
didn't try to insert garbage. It really doesn't like that.



Performance of FrontBase is just plain terrible. One of
our reports takes 9 minutes on FrontBase and 10 seconds on
Postgres.


As I said: depends on what you are doing.

Which version did you use where you got that terrible performance? The  
latest one? There was a bug in there query planner in an old version  
that totally killed some queries.


There is also the or-query problem (not sure whether that one is still  
in there): if your report does something like "select bar from foo  
where a = 1 or b = 1;" it didn't use indexes on a or b which is  
terrible. If you have that, use separate selects combined with a union  
if you can.


In my cases it was never as fast as PostgreSQL as soon as there are  
more than two tables involved. But except a couple of cases where I  
had to use PostgreSQL for performance reasons, I was okay with it. But  
performance is not everything.


PostgreSQL is for most cases the better product, but for some cases,  
FrontBase is. Depends on what you're doing how much which side has ...



Then there's the documentation issue...


PostgreSQL documentation is better, but what is your issue exactly?  
The main problem I have with the FrontBase documentation is that it  
mostly gives no useful examples.


As this is off-topic here: we can transfer that either to the  
FrontBase list or to private mail if you like.


cug

--
http://www.event-s.net


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

Re: [GENERAL] Experiences with extensibility

[Guido Neitzer]

On 09.01.2008, at 09:05, Andrew Sullivan wrote:


Easy multi-master clustering with just two machines.


To my knowledge, _nobody_ actually offers that.


As I said: FrontBase is offering that.

cug

--
http://www.event-s.net


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

Re: [GENERAL] Experiences with extensibility

[Guido Neitzer]

On 09.01.2008, at 00:14, Ow Mun Heng wrote:


Like, I have a situation where I need multi-master just for
availability. Two small servers are good enough for that. But
unfortunately with PostgreSQL the whole setup is a major pain in  
the ...




Isn't that the reason they hire DB admins and not the run of the mill
guy.


Isn't that more the situation where it is preferred to have a working  
fail-over with as less money and work as possible?


There is just no way I (personally) can afford hiring someone to set  
that up as I'm talking about something that hasn't brought a dollar  
yet and will probably not for the next time ... and it is my own  
project, but there is still some need for a reliable service to come  
to a point where I can maybe hire someone.


cug

--
http://www.event-s.net


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

Re: [GENERAL] Experiences with extensibility

[Guido Neitzer]

On 09.01.2008, at 00:08, Joshua D. Drake wrote:


Did you even bother to read the page?


Actually I tried but typed it in the browser and it resolved directly  
to continuent.com (which I have as a bookmark) and I wasn't aware of  
the Sequoia stuff anymore and combined Contiuent with uni/cluster  
directly in my mind. That was the main problem here.


After reading again a bit now, I haven't seen mentioned how many nodes  
are required to have a minimum more or less safe fail-over setup.


Great! I was just trying to show you that there was a JDBC layer  
available for multi-mastering with PostgreSQL.


When I find some time, I might dig a bit deeper in the Sequoia stuff  
again. We will see. Thanks for that hint.


cug

--
http://www.event-s.net


---(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] Experiences with extensibility

[Guido Neitzer]

On 08.01.2008, at 23:40, Joshua D. Drake wrote:


There are OS level things you can do here.


They are normally not really easier and, more important, I don't have  
them on my deployment environment.



http://www.continuent.org/HomePage


When I'm talking about two cheap machines you recommend a solution  
where I need four machines (Or can I use the uni/cluster machines also  
as db nodes?) and licenses for a couple of thousands bucks? Sorry, no  
option.


And, I have my option ...

cug

--
http://www.event-s.net


---(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] Experiences with extensibility

[Guido Neitzer]

On 08.01.2008, at 23:20, Joshua D. Drake wrote:


That isn't really an extensibility argument.


I was thinking about that too - for me, it still is just an  
outstanding issue with PostgreSQL. It is incredibly scalable on one  
machine but it totally sucks when you want more, but not much more.


Like, I have a situation where I need multi-master just for  
availability. Two small servers are good enough for that. But  
unfortunately with PostgreSQL the whole setup is a major pain in the ...


At least not in my mind. Further I don't know of anyone that can  
"easily" do it. You either suffer the possibility of catastrophic  
data loss (dolphins) or you suffer guaranteed bank account drainage  
(Oracle), or you suffer the willingness of Monopolies (MSSQL).


FrontBase. It has an incredibly easy to configure replication and  
multi master clustering support, is very reliable and can also handle  
really big databases. The only problem is that the query planner is  
not as good as PostgreSQL's so you might end up with much worse  
performance. Depends a bit on the complexity of the database and how  
"special" your queries are.


But if you need something easy to setup, multi-master with just two  
machines, easy fail-over (done in the JDBC driver) without your  
application even noticing it - try it. It's free, but not open source.  
And it's a good product. I use it for some stuff and PostgreSQL for  
other projects. Just depends on the requirements.


cug

--
http://www.event-s.net


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

Re: [GENERAL] Experiences with extensibility

[Guido Neitzer]

On 08.01.2008, at 17:36, Joshua D. Drake wrote:


2.  What types of extensibility (possibly already available in
other DBMSs) are currently missing in PostgreSQL?


None that I am aware of.


Easy multi-master clustering with just two machines.

cug

--
http://www.event-s.net


---(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] Postgres High Availablity Solution needed for hot-standby and load balancing

[Guido Neitzer]

On 01.12.2007, at 09:35, Ragnar Heil wrote:


WHich solution are you using now, Guido?


For the one installation we needed multi-master, we have FrontBase  
running.


PostgreSQL multi-master just wasn't "right" for us. Too much hardware  
needed to get real redundancy, the setup is too complex and didn't  
provide what we needed: multi-master replication with two machines for  
real redundancy not really performance (which is good enough with one  
simple, cheap server), load-balancing is done in the apps (we have  
instances connecting to one or the other server), automatic failover  
in case of one server goes down. If we want to bring it back up, it's  
a matter of some really simple commands and it synchronizes itself  
with the master, no copying of files, no complex scripting, no complex  
procedures at all.


Performance of one PostgreSQL server would be faster compared to that  
setup, at least for the load we have, as FrontBase seems to be getting  
slower with inserting in large tables with complex indexes. PostgreSQL  
index creation is way faster. There are also other annoying things we  
have seen, but overall the solution works fine.


cug

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

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Guido Neitzer]

On 30.11.2007, at 12:50, Shane Ambler wrote:

I project where the "latest news" page shows the newest entry from  
March 2005 and the install talks only about PostgreSQL 8.0 isn't  
really inspiring confidence ...


Although they aren't the fastest with releases, they really aren't  
as bad as that.


Nicely said ... ;-) Yeah, I was looking at the wrong site. Maybe there  
should be a redirect to the new page to avoid that.


I have last looked at pgcluster back in 2005 when I was trying to find  
an affordable multi master solution an the setup sounded so horrific  
that we spent about 10k EUR to get a different solution that is  
actually working fine now. Setting this up on just two machines was  
about 10 minutes work ...


I hope the setup has changed to the better.

cug

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

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Guido Neitzer]

On 30.11.2007, at 04:48, Wolfgang Keller wrote:


LSI drivers are not available for MacOS X on PowerMacs? Ouch.


The problem is that they suck as they can't to channel bundling for  
higher trough-put to a single disk array.


[not your comment, but referred there]

and Mac OS X, PostgreSQL has enough
performance issues that I feel using those plaforms can only be
justified if platform compatibility is more important than
performance to you.


Actually - In our test if just used with a similar load as pgbench  
(e.g. typical web applications) Mac OS X  10.4.7 performed better then  
Yellow Dog Linux (I was testing with G5 hardware) on the same hardware  
as soon as more than about 90 concurrent clients were simulated.


But okay, don't trust statistics you didn't make up yourself ...

cug

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

Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing

[Guido Neitzer]

On 30.11.2007, at 02:34, Usama Dar wrote:


Have you looked at pgCluster


I project where the "latest news" page shows the newest entry from  
March 2005 and the install talks only about PostgreSQL 8.0 isn't  
really inspiring confidence ...


Continuent is very active, but it limits the servers to Linux, it  
seems it doesn't work on BSD or Mac OS X. At least, these are not  
listed on the product page for uni/cluster.


The lack of an integrated multi master clustering solution in  
PostgreSQL is the only real downside I can see. For me it is better to  
have something that is well integrated, functional and supported but  
only fits the needs for about 80% of the people in need for a multi  
master than having nothing and always pointing to very old or poor or  
commercial commercial solutions.


But that is just my personal view on that. I know that a multi master  
cluster is a very complex feature, but you can't always throw more  
(the existing solutions need AFAIK at least four servers to be  
redundant) or bigger hardware or expensive solutions if you only need  
load balancing but your app needs to be able to write to whatever  
server it is connected.


In that respect, I really like the solution in FrontBase, where you  
can do multi master with two servers, schema synchronization is  
included, you can connect or disconnect servers from the cluster at  
any time, it is fully transparent for the application, you just add  
more addresses to the JDBC connection string. This might not fit the  
needs for a couple of users, but it fits for the vast majority.


Personally I can live without a multi master solution for PG at the  
moment as I just use a different product if I need it and live with  
the downsides of said product (cost for Oracle and similar, less  
configuration options and lower performance with FrontBase, other  
problems with other DMBS).


cug


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

  http://archives.postgresql.org/

Re: [GENERAL] Porting from FrontBase

[Guido Neitzer]

On 29.11.2007, at 12:30, Martin wrote:


I am looking at porting a web application from FrontBase to
Postgresql. If anybody has some tips on how to deal with
this, I'd appreciate hearing him. Thanks.


If it is WebObjects, you can contact me off-list. I've done that for  
some of our apps. It's not really hard.


cug

--
http://www.event-s.net


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

  http://archives.postgresql.org/

Re: [GENERAL] Database Select Slow

[Guido Neitzer]

On 10.08.2007, at 06:58, .ep wrote:


Hi, what if I need to do a count with a WHERE condition? E.g.,

SELECT count(*) from customers where cust_id = 'georgebush' and
created_on > current_date - interval '1 week' ;

Can I get the info about this from somewhere in the pg system tables
as well? Queries like these are very common in most applications, so
I'm hoping I can avoid the sequential scans!


If you have a qualified count(*) it goes to the index first, than  
checks whether the rows are live for your transaction. The problem is  
only the unqualified count with


select count(*) from table_name;

without any qualification. Or, of course, if your qualifier is not  
selective enough and you get a couple of millions rows back from a  
slow IO system ...


I try to do counts only if I know that the selectivity is good enough  
not to kill the performance. Or I use "pleas wait" pages in the my  
application to tell the user, that his request is being processed and  
not hung.


cug

---(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 Replication

[Guido Neitzer]

On 07.07.2007, at 06:16, Gabriele wrote:


Let's have a server which feed data to multiple slaves, usually using
direct online connections. Now, we may want to allow those client to
sync the data to a local replica, work offline and then resync the
data back to the server. Which is the easiest way to approach this
problem?


I don't know anything for PostgreSQL doing that.

FrontBase [1] might have something like that if I understood Geert  
correctly. But I'm not sure about that. Maybe you ask them directly.  
There DBMS is free but not open source though.


cug

[1] http://www.frontbase.com

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

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

Re: [GENERAL] OS X Kernel settings

[Guido Neitzer]

On 05.04.2007, at 09:09, Scott Ribe wrote:


Yes. You can now edit /etc/sysctl.conf and nothing else is required. I
learned this ~10.4.8, so I don't know when it actually happened.


Okay, that's good. They had the wrong order of commands before, so  
that the values in /etc/rc were used and not the values from the  
external file. That was a bug in the script.


cug

---(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] OS X Kernel settings

[Guido Neitzer]

On 04.04.2007, at 16:55, Jamie Deppeler wrote:


Is this a safe setting for g5 1gig ram Os 10.4.9

sysctl -w kern.sysv.shmmax=167772160
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=32
sysctl -w kern.sysv.shmseg=8
sysctl -w kern.sysv.shmall=65536


Why shouldn't it be safe?

cug

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

Re: [GENERAL] OS X Kernel settings

[Guido Neitzer]

On 03.04.2007, at 23:00, Jamie Deppeler wrote:

Is it possible to set shared memory settings on the fly in OS X  
like you can in Linux e.g. sysctl -w kernel.shmmax=134217728


If this wasn't changed recently - no you can't.

If you use /etc/sysctl.conf make sure it is loaded in /etc/rc before  
the values are set in there. I'm not sure whether Apple has fixed the  
bug in the startup script, where the external file was referred AFTER  
the values where set. And when they are set, they are final ...


cug



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

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

Re: [GENERAL] Multi DB performance test

[Guido Neitzer]

On 12.03.2007, at 22:46, Tom Lane wrote:


Most of us stopped reading about the place where they explained
that (a) they did zero tuning on each database, and (b) they were
comparing myisam to transactional engines.


What I don't like about things like that, is that some morons throw  
something together they call a "performance test", add some colorful  
graphs to it and it will be quoted for the next ten years, "decision  
makers" will base their recommendations on it and a lot of followers  
will just believe.


Whether or not this applies to the referenced test - judge yourself.

cug

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

Re: [GENERAL] php professional

[Guido Neitzer]

On 22.02.2007, at 16:03, Ted Byers wrote:

One of my problems with database development is how to construct  
analogously strong test cases in order to prove the code correct.


With tests you can't prove that your code is correct. You can only  
show that your code works with the test cases. There are ways of  
proving that code is correct, but I don't know anybody using that on  
"real world code".


If you are using Java as a client you can use DbUnit for unit testing:

http://www.dbunit.org/

This can or cannot show that your latest changes didn't break stuff  
that used to work ... ;-)


cug

---(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] How would you handle updating an item and related stuff all at once?

[Guido Neitzer]

On 22.02.2007, at 15:56, Jim Nasby wrote:

and "surrogate key fields should be named 'id'" (I *much* prefer  
the form "object_id", ie: user_id, used *everywhere*, including the  
user table (in that example)).
Fortunately, with rails extensibility it shouldn't be hard to  
change those default behaviors (in fact there's probably a patch  
somewhere for the first case...)


It's really not hard to use the integrated mechanism for that:

class MyTable < ActiveRecord::Base
set_primary_key "mytable_id"
end

There are nevertheless usage glitches as far as I know. But it's no  
real problem.


cug



---(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] Database performance comparison paper.

[Guido Neitzer]

Am 19.02.2007 um 17:49 schrieb Jan Wieck:

Oh, this one wasn't about raw speed of trivial single table  
statements like all the others?


No, it wasn't. They also tested the insert performance of a system  
without foreign keys and without transactions (MySQL MyISAM)  against  
systems with foreign key handling and transactions.


It would be more or less the same, if you compare copy against insert  
performance on PostgreSQL and state that insert should be as fast as  
copy without saying why.


Btw: these guys claim to be database consultants.

cug

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

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

Re: [GENERAL] Database performance comparison paper.

[Guido Neitzer]

Am 15.02.2007 um 13:05 schrieb Alexander Elgert:

Nice, but it would be interesting which storage engine was used for  
mysql - ok, default is MyIsam.


They used MyISAM as it is described late in the paper.

cug

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

  http://archives.postgresql.org/

Re: [GENERAL] Database performance comparison paper.

[Guido Neitzer]

Am 15.02.2007 um 11:21 schrieb Marc Evans:


http://us.devloop.org.uk/


These *peeep* [deleted] compared MySQL with MyISAM to ACID  
compliant databases. So why not compare an F-15 to 747? What? Apples  
and Oranges? So what? You can compare anything you want, right? Only  
the result matters.


So, my hint to these guys is: learn about the principles of databases  
(at least read: ), then about the  
principles of optimizing databases, then about the principles of  
testing (don't compare products or setups that do completely  
different things) and then do you homework again.


Go home.

cug

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

Re: [GENERAL] Hardware

[Guido Neitzer]

On 06.02.2007, at 08:59, Walter Vaughan wrote:

Is this still true in regards to Xeon's? I was looking at a server  
with Quad Core Xeon 2 5335 @ 2.0GHz.


No, it's not true anymore. See

http://tweakers.net/reviews/657/1

for an interesting comparison.

cug

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

Re: [GENERAL] replication choices

[Guido Neitzer]

On 31.01.2007, at 14:53, Lenorovitz, Joel wrote:

I do not know of any product, Slony included, that has built in  
support
for a situation such as this, so I suspect all of the details will  
have

to be handled in a custom fashion.


It is not relevant for you as your are using PostgreSQL (for good  
reasons I believe - I use it too, but not exclusively), but from what  
I have heard, the guys at FrontBase  [1] have developed a solution  
for such scenarios based on their own database engine. If you are  
especially looking for such a product and if you are not bound to a  
special db engine, you might want to talk to them.


The information about that is, as far as I know, not on their  
website, so you might ask the customer support for that.


To make that clear: I have nothing to do with FrontBase and it  
clearly has disadvantages and advantages compared to PostgreSQL. I'm  
just a user, as I am for PostgreSQL. I use both of them (more often  
PostgreSQL) and I always try to use the one that fits the situation  
best. That's all and that's why I'm giving this information.


cug

[1] http://www.frontbase.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] Any Plans for cross database queries on the same server?

[Guido Neitzer]

Am 30.01.2007 um 12:11 schrieb Tony Caduto:

Why?  Seems to me if it was discussed that much it must be a very  
sought after feature.

How come it's not on the TO Do list for the future at least?
Is it because of some limitation of the core engine or something?


http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

I guess, nobody has a real interest on that because, if you really  
need that, there are work arounds ... E.g. I do a lot of cross  
database queries all the day with my apps. It's just handled by the  
app server ...


cug

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

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

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

[Guido Neitzer]

On 23.08.2006, at 16:51 Uhr, Alvaro Herrera wrote:


Guido Neitzer wrote:


8<8<8<8<8<8<
2.2.1. Unstructured Header Field Bodies


   Some field bodies in this standard are defined simply as
   "unstructured" (which is specified below as any US-ASCII  
characters,

   except for CR and LF) with no further restrictions.  These are
   referred to as unstructured field bodies.  Semantically,
unstructured
   field bodies are simply to be treated as a single line of  
characters

   with no further processing (except for header "folding" and
   "unfolding" as described in section 2.2.3).

8<8<8<8<8<8<


So see what "folding" means.  Section 2.2.3 says

2.2.3. Long Header Fields


   Each header field is logically a single line of characters  
comprising

   the field name, the colon, and the field body.  For convenience
   however, and to deal with the 998/78 character limitations per  
line,
   the field body portion of a header field can be split into a  
multiple

   line representation; this is called "folding".  The general rule is
   that wherever this standard allows for folding white space (not
   simply WSP characters), a CRLF may be inserted before any WSP.  For
   example, the header field:


Interesting. Haven't seen that. Thanks for the hint. Not really  
intuitive, but okay.


cug



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

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

[Guido Neitzer]

On 23.08.2006, at 16:31 Uhr, Alvaro Herrera wrote:


Can you have multi-line subject lines?  I didn't think that was
possible.


Yes.  This is the header of a mail you sent to -patches:


Aha? Subject is an "unstructured header field" and according to RFC  
2822 [1]:


8<8<8<8<8<8<
2.2.1. Unstructured Header Field Bodies


   Some field bodies in this standard are defined simply as
   "unstructured" (which is specified below as any US-ASCII characters,
   except for CR and LF) with no further restrictions.  These are
   referred to as unstructured field bodies.  Semantically,  
unstructured

   field bodies are simply to be treated as a single line of characters
   with no further processing (except for header "folding" and
   "unfolding" as described in section 2.2.3).

8<8<8<8<8<8<

So they don't contain line feeds or carriage returns and so the can't  
be multi-line. If a mail client sends multi line subjects it does  
something against the RFC and I assume with that, it does something  
wrong.


This is the theory in RFC 2822 as far as I understand it.

cug

[1] http://tools.ietf.org/html/rfc2822

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

Re: [GENERAL] pgsql vs mysql

[Guido Neitzer]

On 11.07.2006, at 21:11 Uhr, Alvaro Herrera wrote:


I had the problem a few months ago, where my app server plugin and
the jdbc driver used prepared statements for selecting stuff from the
database. Most of the time, indexes weren't used at all, so
PostgreSQL performance was the worst I've ever seen in this  
environment.


I'm pretty excited about this idea of yours on how to fix this  
problem.

Does it involve the histogram at all?


There is no idea. It's only not using prepared statements right now.  
You can force this in the jdbc driver (using protocolVersion=2 in the  
connection url) or you can send different stuff from the application.  
There is nothing where the DB itself does anything.


Nothing special.

cug


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

  http://archives.postgresql.org

Re: [GENERAL] pgsql vs mysql

[Guido Neitzer]

On 11.07.2006, at 19:36 Uhr, Merlin Moncure wrote:


As to preparing
statements, I agree in principle although I don't know if that is a
good argument not to make the non-paramaterized interface more
powerful.


It is not, as prepared statements have the problem that they are only  
optimized once and very generically and without actual knowledge of  
the parameter content, this is just useless.


I had the problem a few months ago, where my app server plugin and  
the jdbc driver used prepared statements for selecting stuff from the  
database. Most of the time, indexes weren't used at all, so  
PostgreSQL performance was the worst I've ever seen in this environment.


There are fixes for that, but it should be made easier ...

cug

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

Re: [GENERAL] Copy content from dbs

[Guido Neitzer]

On 14.06.2006, at 14:12 Uhr, A. Kretschmer wrote:

Is there an easy way to copy the content including the table   
structure,

indexes and so on from one db to another?


Create the new db with the old db as template.


Thanks, but as far as I can see, I have to disconnect all clients  
from the production db to do this and this wasn't an option.


I forgot to mention that. The pg_dump way is fine for me.

cug

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

Re: [GENERAL] Copy content from dbs

[Guido Neitzer]

On 14.06.2006, at 14:02 Uhr, Thomas Kellerer wrote:


pg_dump: http://www.postgresql.org/docs/8.1/static/app-pgdump.html


Found it. Thanks. I was just blind and thought, pg_dump can only re- 
create the db with the same name.


Thanks,
Guido

---(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] Copy content from dbs

[Guido Neitzer]

Hi.

Is there an easy way to copy the content including the table  
structure, indexes and so on from one db to another?


Let's say I have a production db called db_production and want to  
create a development db called db_dev with exactly the same content  
on the same machine - just with the different name?


cug

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

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

Re: [GENERAL] What is your favorite front end for user interaction

[Guido Neitzer]

On 09.05.2006, at 16:52 Uhr, Reid Thompson wrote:


*WebObjects Distribution License
$699 per copy*

WebObjects, the premier web application server used by hundreds of  
corporations, is now available for redistribution by web  
application developers just like you.


Upon signature by Apple, the agreement, together with payment for  
your initial request of licenses, allows you to resell WebObjects  
license keys, and redistribute the WebObjects deployment runtime  
and adaptors as part of your web application. The WebObjects  
runtime includes a powerful object-relational engine for extracting  
and managing data from virtually any database, without writing a  
single line of SQL. Its HTML component model makes it a breeze to  
assemble dynamic, fully customizable web pages. There’s even  
support for rich Java clients and Web services.


This is for WO 5.2.4. WO 5.3.1 (current release) is free for  
development on Mac OS X and free for deployment on any chosen platform.


cug



---(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] What is your favorite front end for user interaction to postgresql databases?

[Guido Neitzer]

On 09.05.2006, at 16:31 Uhr, Steve Atkins wrote:


Is that actually true? My understanding was that under the most recent
license changes it was not possible to deploy it to any platform other
than XServe.


Wrong. You are allowed to deploy on any platform you like, but only  
Mac OS X Server is officially supported by Apple.


Please note that this is for 5.3.1 - there was a bad license  
formulation in 5.3 which actually did not allow the deployment. For  
5.3.1 this was corrected. The issue came up when WebObjects was  
released free as part of the Xcode tools and a new license was  
necessary which wasn't well written.



Technically possible, sure, but a violation of the license.


Nope. Cliff Tuel of Apple clarified this on the WO mailing lists.

cug

---(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] What is your favorite front end for user interaction to postgresql databases?

[Guido Neitzer]

On 09.05.2006, at 0:33 Uhr, Karen Hill wrote:


What is your favorite front end for end users to interact with your
postgresql db?  Is it java, .net, web apache + php, MS-Access, ruby on
rails?  Why is it your favorite?  Which would you recommend for end
users on multiple OSes?


You mean what technology to use to build business logic?



It just depends on your needs. I prefer to use Apple WebObjects for  
building web frontends to the db.


Why? Because it is extremely powerful, free if you are able to  
develop on Mac OS X (it comes with the developer tools) and even if  
you have to buy a couple of Powerbooks and Xserves it may be cheaper  
then only the software license for one of the other big Java  
application server technologies. You can deploy it to every Java  
plattform because it's just a Java application, but it's build on and  
for Mac OS X. It completely frees you from thinking in tables, joins,  
selects and other sql stuff, well, at least nearly completely. I can  
switch my whole application including a couple of hundred MB database  
content in half a day (mostly the time to transfer the data) - I have  
done this two months ago from another dbms to PostgreSQL.


It's just as "cool" as Ruby on Rails right now, not as fat and slow  
in development as most J2EE servers, you can deploy everywhere, I can  
develop on Mac OS X (yes, that's a reason for me, because it speeds  
up my work by a good percentage), it is extremely fast for  
development once you have the concepts and a good set of your own  
components in stock which you are used to. It's pure Java, so I can  
easily include third party tools like Lucene or others. It does one  
connection per application instance which keeps concurrent db tasks  
low. Also it has more than one working approach for multi language /  
internationalized applications.


And, most important, it has a tool, which enables me to do things  
alone in days, other teams do in weeks with other J2EE tools:  
DirectToWeb - a rule and template driven dev plattform. You write the  
db schema, lots of "rules" how list, inspect, edit and search pages  
should look like and some basic html for wrappers and you can create  
an administration application faster as with everything I've seen so  
far. Including Ruby on Rails, PHP and other Java tools. But, to make  
this clear: the starting point to use it is VERY high. I had  a  
couple of years doing WebObjects development when I started using D2W  
and it was good to have this background.


Also you can build WebServices, rich Java client apps, can use Ajax  
for rich web applications and I have a tool that proved it's  
scalability, reliability and ease of use in a real high volume  
environment: the iTunes Music Store.


For more information, send me an email and/or go to www.apple.com/webobjects>. (I hope the url is correct, I'm not online  
right now - sitting in the train to work  ;-))


Ah, and if you download it for testing: go to one of the WO mailing  
lists and ask for some good tips for starters. It will help.




Also, what do you think of having the database management system do  
all

work (business logic etc) with the front end as a user interface vs.
having N-tier with an application server handling business logic, and
the db just accepting data.


I prefer the second approach because it frees me from one specific  
database. I can use Oracle, PostgreSQL, MySQL, FrontBase, OpenBase,  
Sybase, MSSQL without changing one line of code (if I have avoided  
using custom SQL, which I mostly do) by just switching one framework.  
Okay, right, there are always some problems you have to solve, but  
they are nothing compared to what a PHP developer has to do to use  
different dbms as the one he develops on.



This comes from having to re-invent the wheel every time a
new user app needs to  be created.


You do something wrong if you don't build libraries of your basic and  
generic work over the time. This has nothing to do with the tool you  
use or the underlying dbms - it's just bad coding style if you have  
to re-invent the wheel for every app.


For me and our customers it's more important to be able to switch the  
dbms than the application server.


But, as with everything: YMMV.

Ah, and for curiosity I'm just evaluating Ruby on Rails: it has  
interesting ideas and places itself somewhere between the fat J2EE  
app servers and the scripting languages PHP and Perl. It is more  
closely placed to WebObjects then I have thought on the first look,  
but is a bit easier to learn and not the big 800 pound gorilla of web  
development, with hooks, tool, templates, ideas and concepts for  
nearly every case.


But in my opinion it lacks a couple of things, two of the most  
important for me are complete abstraction from the db (you may reach  
this with easy schemas and generic sql but not enough) and a working  
internationalization approach. It is lightweight and it would be  
interesting to build 

Re: [GENERAL] Updating database structure

[Guido Neitzer]

On 23.03.2006, at 9:50 Uhr, [EMAIL PROTECTED] wrote:

I currently use phpPgAdmin to make changes to the database, so it  
would
be very handy if Postgres could add a change made to a lable  
somewhere,

after which I gather all the rows with changes and put them in a SQL
query.


I mostly use the command line tools or a graphical tool to make my  
own sql calls for every schema change I make on the development  
database.


Then all these changes come to a "script" in my application (it's not  
actually a script but similar). The database has a version tag in one  
table. The application has a bunch of these "scripts" to convert from  
one version to another.  It knows the sequence in how they have to be  
applied by looking at the version numbers.


So, let's say, the production database is on version 1.1 and you have  
made several test versions connecting to a test server, the test/dev  
db is on version 1.4 and now I want to put my new application online.  
I simply shut down all running instances of my app, replace the  
application binaries, start the application again (one single  
instance for now), the app checks the DB version, sees that changes  
have to be made and applies all scripts necessary for converting the  
db from version 1.1 to 1.4 automatically. When this is done, I start  
the other instances and everything is fine.


For the kids: Don't do this at home without a fresh backup and  
without a lot of testing of this process!


cug



--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Difference between "add column" and "add column" with default

[Guido Neitzer]

On 20.03.2006, at 11:41 Uhr, Martijn van Oosterhout wrote:


What is the technical difference between adding a column to a table
and then apply a "set value = ..." to all columns and adding a column
with a default value = ...?


What version are you using:

# alter table a add column b int4 default 0;
ERROR:  adding columns with defaults is not implemented


DB=# show server_version;
server_version

8.1.3
(1 row)

# alter table a add column b int4 default 0;

works just fine.


The latter doesn't work in a single step. The former does indeed
duplicate all the rows.


It works here.


The latter only affects newly inserted rows, changing the default does
not affect any existing rows. If it does, please provide examples.


Nope it doesn't. If I add the column with a default constraint, all  
rows have the default value.


Example:

DB=# create table test (id int4, a int4);
CREATE TABLE
DB=# insert into test values (1, 1);
INSERT 0 1
DB=# insert into test values (2, 2);
INSERT 0 1
DB=# insert into test values (3, 3);
INSERT 0 1
DB=# select * from test;
id | a
+---
  1 | 1
  2 | 2
  3 | 3
(3 rows)

DB=# alter table test add column b int4 default 0;
ALTER TABLE
DB=# select * from test;
id | a | b
+---+---
  1 | 1 | 0
  2 | 2 | 0
  3 | 3 | 0
(3 rows)

DB=# alter table test add column c int4 default 17;
ALTER TABLE
DB=# select * from test;
id | a | b | c
+---+---+
  1 | 1 | 0 | 17
  2 | 2 | 0 | 17
  3 | 3 | 0 | 17
(3 rows)


cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Difference between "add column" and "add column" with default

[Guido Neitzer]

Hi.

What is the technical difference between adding a column to a table  
and then apply a "set value = ..." to all columns and adding a column  
with a default value = ...?


I have seen that the first duplicates all rows, I had to vacuum and  
reindex the whole table. Okay so far, I have expected this. But this  
wasn't necessary with the second option, nevertheless, fetching some  
rows showed, that the value of the new column was my default value.


So, I'm curious: what happens (not) here?

Thx for the explanation.

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Question about index usage

[Guido Neitzer]

On 07.03.2006, at 16:04 Uhr, Tom Lane wrote:


Because IN means "=", which is a member of the index opclass for the
second index but not the first.

Why do you care?  Should be about the same result either way.


Only because I haven't set up the second index because I wasn't aware  
of this fact.


cug


--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Logging seq scans

[Guido Neitzer]

On 07.03.2006, at 12:11 Uhr, A. Kretschmer wrote:


Yes, of cource. You can define

log_min_duration_statement = 100

to log all queries taking longer 100 ms.


Thanks.

cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Question about index usage

[Guido Neitzer]

On 07.03.2006, at 12:09 Uhr, chris smith wrote:


Try without the quotes:

select id from dga_dienstleister where plz in (45257, 45259);


Same result, second index is used.


What is the table structure for dga_dienstleister ?


For the relevant column:

plz| character varying(256)  |  
not null


Thanks,
cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Question about index usage

[Guido Neitzer]

Hi.

Is there a reason why this query:

select id from dga_dienstleister where plz in ('45257', '45259');

doesn't use this index:

"dga_dienstleister_plz_index" btree (plz varchar_pattern_ops)

but uses this index:

"dga_dienstleister_plz_index2" btree (plz)

I had the first index setup for queries with "plz like '4525%'" but I  
never tested the "in" query until I saw in the logs that these  
queries where slow compared to the rest. Query plans at the end.


cug


DGADB=# explain analyse select id from dga_dienstleister where plz  
like  
'45257';   Q 
UERY PLAN
 

Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21  
width=8) (actual time=13.489..14.211 rows=16 loops=1)

   Filter: ((plz)::text ~~ '45257'::text)
   ->  Bitmap Index Scan on dga_dienstleister_plz_index   
(cost=0.00..2.07 rows=21 width=0) (actual time=13.323..13.323 rows=16  
loops=1)

 Index Cond: ((plz)::text ~=~ '45257'::character varying)
Total runtime: 14.328 ms
(5 rows)


DGADB=# explain analyse select id from dga_dienstleister where plz =  
'45257';
  QUERY  
PLAN
 
---
Bitmap Heap Scan on dga_dienstleister  (cost=2.07..82.41 rows=21  
width=8) (actual time=0.486..0.663 rows=16 loops=1)

   Recheck Cond: ((plz)::text = '45257'::text)
   ->  Bitmap Index Scan on dga_dienstleister_plz_index2   
(cost=0.00..2.07 rows=21 width=0) (actual time=0.424..0.424 rows=16  
loops=1)

 Index Cond: ((plz)::text = '45257'::text)
Total runtime: 0.826 ms
(5 rows)



smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Logging seq scans

[Guido Neitzer]

Hi.

Is there a way to set up logging in a way that I can see queries  
which trigger seq scans? Or to log queries "taking longer than xx ms"?


Background is, that it is nearly impossible to tell, which queries  
are used in my applications as they are mostly generated by the  
frameworks. Yesterday I found, that one of the more often used  
queries has not used an existing index and I had to use another index  
for it. Nobody complained about the performance but nevertheless the  
query took about 1000ms and therefore slowed down the rest of the  
server ...


cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] a web framework for postgresql?

[Guido Neitzer]

On 01.03.2006, at 19:39 Uhr, falcon wrote:


Any one know of such a framework?

(I'm asking this in pgsql because such a framework will have to be
fairly closely linked to a database...and I mainly use pgsql).


Hmm. No. I don't think you can have this combined with what is often  
called "business logic". A lot of stuff is described in the db model,  
but that's only one thing.


What about process flows, permissions, login handling, session  
handling, design, performance optimization and so on?


I use WebObjects (http://www.apple.com/webobjects) as my main  
development environment and I found that every application is unique  
in a good percentage of its features.


For "administration" application the included DirectToWeb-Framwork in  
combination with a powerful community based framework  
("ProjectWonder") is a VERY powerful tool when it comes to rapid  
development. It saves me days and weeks of work - I just couldn't do  
my job anymore without it and developing applications in a "standard  
way". But it has a very steep learning curve and is sometimes hard to  
handle and often frustration - until you learn how not to fight the  
tool.


Frontend applications like online shops, portals and so on are then  
developed in a more conventional way with components and handwritten  
xhtml/css.


If you have a Mac for development it's worth a look. Deployment can  
go anywhere (in theory - we have only used Linux and Mac OS X Server  
so far) as long as a Java runtime is installed.


cug

--
PharmaLine, Essen, GERMANY
Software and Database Development




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] verifying database integrity - fsck for pg?

[Guido Neitzer]

On 31.01.2006, at 17:38 Uhr, Rich Doughty wrote:

I'm not too fussed about a row-by-row comparison between the source  
and the
copy. It's rather a case of a tool to check the datafiles'  
integrity (such

as fsck, myisamchk, svnadmin verify etc).

If the fact that pg_dumpall returned successfully, then i would  
hope that

all the data is present and correct.


As I moved my content from another DBMS to PostgreSQL that was no  
option for us. We had to check table by table and row by row.


cug

--
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] verifying database integrity - fsck for pg?

[Guido Neitzer]

On 31.01.2006, at 16:00 Uhr, Rich Doughty wrote:


i had considered pg_dump. i was hoping there was a utility similar
to fsck that could check for corruption. i'd like to verify now that
the data is ok, rather than coming across errors in 6 months time.


I have done this using JDBC (and the WebObjects frameworks) a few  
months ago. It wasn't a big problem as I could crawl over the defined  
tables (entities) in my database definition file (EOModel) and then  
compare row by row. It was slow like hell as it had to go over every  
row and over nearly every column, but it wasn't really complicate to  
write.


Unfortunately I don't have the code for that anymore as I have  
deleted the project by accident (I have made this with one of my test  
projects).


But as far as I see, you can do the same with every tool that may  
connect to two datasources, can iterate over a result set (rows and  
columns) and can compare contents.


Perhaps you have to do some tricks to tell the script which tables or  
columns should be equal and which are allowed to differ, but as far  
as I can see, it shouldn't be that hard. At all, it took me about a  
day to verify the db contents.


cug

--
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Indexes on character type columns

[Guido Neitzer]

On 22.12.2005, at 11:27 Uhr, Martijn van Oosterhout wrote:


For btrees at least, yes. Around a third of a page or about 2713 bytes
by default. For bigger things you probably want tsearch anyway.


Thanks.

cug

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Indexes on character type columns

[Guido Neitzer]

Hi.

Is there a limitation of the length of a char or varchar(x) column  
for indexing?


cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] sequences w/o holes

[Guido Neitzer]

On 19.12.2005, at 12:14 Uhr, Marian Naghen wrote:


Can anyones suggest some readings about implementing
sequences w/o holes ?


Question: why?

I doubt that this is possible without heavy locking, which kills your  
performance.


cug


--
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Improving Availability

[Guido Neitzer]

On 15.12.2005, at 9:10 Uhr, Brendan Duddridge wrote:


What about Sequoia? Is that better or worse than pgpool?


That looks interesting, I haven't seen it yet. Has anyone experiences  
to share?


cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Improving Availability

[Guido Neitzer]

Hi Scott, hi Richard,

On 14.12.2005, at 17:30 Uhr, Scott Marlowe wrote:


This setup I'm talking about would have pgpool on each db server.

If you meant pgpool running on both application servers, that would  
work
fine with slony in the background and pgpool in load balancing  
mode, or

with pgpool doing the replication.


Okay, just that I get this right (have to write a business paper on  
that and they will take me by the word ...):


Setup would be:

Machine 1:
- web server
- application server connecting to "localhost --> pgpool"
- PostgreSQL installed and accessed only via pgpool
- pgpool installed and knowing of machine 1 and machine 2  
(replication mode)


Machine 2:
- web server
- application server connecting to "localhost --> pgpool"
- PostgreSQL installed and accessed only via pgpool
- pgpool installed and knowing of machine 1 and machine 2  
(replication mode)


If one machine fails, the replication is cut off, and pgpool works  
with the other machine. Okay so far.


The applications only know the connection to the local pgpool, so  
they are fault tolerant as far as pgpool accepts requests.


If one machine fails, the service is not down because as far as all  
the services on the remaining machines are working properly. To get  
everything back, we will have to shut down all apps and all  
databases, sync the db data directories from the working machine to  
the machine that has failed, start the dbs, start pgpool, start the  
applications.


Everything correct?

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Improving Availability

[Guido Neitzer]

On 14.12.2005, at 16:46 Uhr, Richard Huxton wrote:

I'd be tempted to look at pgpool sitting between your application  
and the database, and then replication from the one machine to the  
other.


Right, I thought the same, BUT: I have two application servers (one  
on each machine) connecting to a network URL via JDBC. When pgpool is  
involved, I assume it only runs on one server, right?


Or can I have pgpool running on both servers, each knowing about the  
two servers and the applications just connect to "localhost"?


cug

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Improving Availability

[Guido Neitzer]

Hi.

I have two server boxes (Apple Xserve G5). WebServers and  
applications are installed on both machines, the load is distributed  
by a loadbalancer to both machines.


How can I make a setup in a way that the database is also tolerant  
against failure? What I actually need is a mechanism that keeps both  
machines synchronized.


What is the way to go for this?

Thanks.

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Slow COUNT

[Guido Neitzer]

On 02.12.2005, at 20:02 Uhr, Jaime Casanova wrote:


so the way to do it is create a trigger that record in a table the
number of rows...


As there are SO MANY questions about the "count(*)" issue, I wonder  
whether it makes sense to add a mechanism which does exactly the  
method mentioned above in a default PostgreSQL installation (perhaps  
switched of by default for other performance impacts)?!


cug

--
PharmaLine, Essen, GERMANY
Software and Database Development





smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Ordering and unicode

[Guido Neitzer]

On 11.11.2005, at 9:33 Uhr, Guido Neitzer wrote:

Yes, as far as I know there is no other way of changing the locale  
settings. Hopefully you are on Linux! If you deploy on Mac OS X or  
*BSD it won't work even with a change.


I have to correct me: Hopefully you are not on Mac OS X. On Mac OS X,  
locale support is not yet available for UTF-8, so you will not get  
correct ordering. This is from a statement of an Apple engineer --  
they have this on the to do list.


As the locale support in Mac OS X comes directly from BSD I assume,  
it's not better there but I have tested this only on one BSD  
plattform and looked through the provided locale files (in the given  
cvs directories) for others where it seems similar.


This is not a problem of PostgreSQL, only a problem for PostgreSQL  
when running on the "wrong" plattform.


cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Ordering and unicode

[Guido Neitzer]

On 10.11.2005, at 21:31 Uhr, Michael Schuerig wrote:

My current conjecture is that I'd have to re-initialize the cluster  
with

a utf-8 collation. Is this correct? Right now I don't have access to
the machine and can't check this.


Yes, as far as I know there is no other way of changing the locale  
settings. Hopefully you are on Linux! If you deploy on Mac OS X or  
*BSD it won't work even with a change.


cug




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] PostgreSQL, UTF-8 and Mac OS X

[Guido Neitzer]

On 07.11.2005, at 14:07 Uhr, Martijn van Oosterhout wrote:


We had this question earlier this week. Mac OS X uses the locales from
FreeBSD, and neither support UTF-8 collation at all. You'll see  
exactly

the same results from other UNIX utilities.


I think I was the one who asked.

I worked on my locale problem on the weekend and was able to build a  
LC_COLLATE file, that actually works with ISO locales, but not with  
UTF-8 (50% progress ... ;-)).


When you test the UNIX utility "sort" on Mac OS X, you should be  
aware, that the pre-installed version on Mac OS X ignores locales at  
all ... :-( I had to install the gnu coreutils to get a sort that  
works with locales, and this also fails on UTF-8 but works with ISO  
encoding/collate - same as PG does.


Now I'm not sure, whether my own LC_COLLATE file is not appropriate  
for UTF-8 (why not?) or whether Mac OS X locale does not support  
UTF-8 at all as you state.



Sometime in the near future (hopefully) PostgreSQL will provide locale
support independant of the underlying operating system, but for now
you're stuck.


Will be cool to have locale support directly in PostgreSQL.

So, just a quick question regarding a switch: is there a problem with  
using ISO8859-15 for now, and do a switch later with dumping the data  
and import it to a newer version which should then use UTF-8? Do I  
need to do some conversion or how does this work?


Thanks for your help!
cug

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] PostgreSQL, UTF-8 and Mac OS X

[Guido Neitzer]

Hi.

I have a problem with PostgreSQL and UTF-8 on my Mac OS X Powerbook.


- System is Mac OS X Client 10.4.3, PostgreSQL 8.1beta3

- initdb was called with -E UTF-8 --locale=de_DE.UTF-8

I have successfully build a LC_COLLATE file for ISO8859-15, and  
ordering works there if I do the initdb with ISO8859-1 but I want to  
use UTF-8 for some reasons.


I have linked the LC_COLLATE for de_DE.UTF-8 to the same LC_COLLATE  
file that works fine with ISO8859-1.


"show all;" shows that the encoding ist UTF-8 now, the LC_... are  
"de_DE.UTF-8". Okay, this is fine.


But it doesn't work. The LC_COLLATE file works, if I set encoding and  
locale to ISO... but not, if I set the values to be UTF-8 (don't know  
how often I have called initdb in the last days ...).



It seems to me, that the locale "de_DE.UTF-8" just isn't working at  
all (at least for ordering results) in the combination PG -- Mac OS X.


Some hints what I can try to find out more?

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] PostgreSQL, Mac OS X and locales

[Guido Neitzer]

On 02.11.2005, at 11:47 Uhr, Martijn van Oosterhout wrote:


Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply
point to the ASCII versions which doesn't exactly work very well.


Right.

Only another FreeBSD system, these files are not portable. However,  
the

source files for these are plain text so you can edit them. I beleive
the source is in the C library. Find the source files, edit them and
recompile. Then just copy the LC_COLLATE file over.


I have to look how to do this. I have build locales a very long time  
ago, but currently I'm looking where to start ... perhaps with the  
Darwin sources. We will see.



You might also want to look at some of the other predefined locale
collate orders, maybe someone has put some effert in getting an order
right for another language.


I tried a lot of them and the all didn't work for me.

cug

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] PostgreSQL, Mac OS X and locales

[Guido Neitzer]

On 01.11.2005, at 23:24 Uhr, Martijn van Oosterhout wrote:

PostgreSQL should get the same results as the command-line sort for  
the

same values of LC_COLLATE. However, the value is fixed at initdb time
so maybe that's what's confusing you.


Yes, it gets the same results as sort.

I have tried several encoding/locale settings with initdb.

However, MacOS X inherited FreeBSDs locale setup, which is fairly  
lame.

They've made many improvements though so your problem should be
fixable.

BTW, you didn't actually say what locale you where using...


I mostly use UTF-8, so I have called initdb with "-E UTF-8 -- 
locale=de_DE.UTF-8".


The LC_COLLATE for this locale is a link pointing to "../la_LN.US- 
ASCII/LC_COLLATE". This is why I don't think they paid much attention  
to the correct sort order of umlauts.


But I have also tried ISO-5589-, ISO-8859-15 and others. No change.  
May I take an LC_COLLATE file from another system and use this?


cug

smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] PostgreSQL, Mac OS X and locales

[Guido Neitzer]

Hi.

I have a problem with the use of PostgreSQL on Mac OS X (10.4.x) and  
selects with "order by". When there are german umlauts in the column  
which is used to order, the sorted result is wrong.


As an example, I get:

a
b
ä

where it should be:

a
ä
b

Had someone here the same problem and solved it? I think the locales,  
that come with Mac OS X are broken for this, as it seems they don't  
provide the correct LC_COLLATE sort order.


I tried to figure out, how to get a correct LC_COLLATE file but  
wasn't successful so far. Someone else?


I'm using the following configuration:

- Mac OS X (Server) 10.4.x and 10.3.x
- PostgreSQL 8.0.3
- initdb with encoding set to UTF-8 or ISO-Latin-1 (same results)

Some hints? Thank you!

cug

smime.p7s
Description: S/MIME cryptographic signature