[GENERAL] CTE materializing sets?

2012-10-05 Thread Liam Caffrey
Hi, If I run a CTE does that materialize the resulting data in the same (or a similar) way as if I created a temp table and referred to that instead? Or does the CTE keep the set in memory? Regards Liam

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Shashank Tripathi
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver wrote: > On 10/05/2012 07:00 PM, Phoenix Kiula wrote: >> >> On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack wrote: >> >>> >>> You need to have a pgbouner directory in /var/log and have the owner >>> pgbouncer. This is easy to test try creating a

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Adrian Klaver
On 10/05/2012 07:30 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver wrote: One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html ""\*" acts as fallback database" Notice the backslash. Ok, but: (1) The exact same INI file was working so fa

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Phoenix Kiula
On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver wrote: > > One thing I see above: > http://pgbouncer.projects.postgresql.org/doc/config.html > ""\*" acts as fallback database" > > Notice the backslash. > Ok, but: (1) The exact same INI file was working so far. (2) Why do I need a fallback datab

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Adrian Klaver
On 10/05/2012 07:23 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver wrote: ...snip... What are the contents of your pgbouncer.ini file? Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment abou

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Phoenix Kiula
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver wrote: ...snip... > What are the contents of your pgbouncer.ini file? >> Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment about port numbers. This is a public list so I cha

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Adrian Klaver
On 10/05/2012 07:00 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack wrote: You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Phoenix Kiula
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack wrote: > You need to have a pgbouner directory in /var/log and have the owner > pgbouncer. This is easy to test try creating a file in /var/log as the user > pgbouncer. It should fail because pgbouncer does not have writer permissions > to /v

Re: [GENERAL] pg_dump problem

2012-10-05 Thread Leonardo Rame
- Mensaje original - Fecha: Fri, 05 Oct 2012 17:30:15 -0400 De: "Tom Lane" Para: "Leonardo M. Ramé" Asunto: Re: [GENERAL] pg_dump problem Copia: "PostgreSql-general" Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= writes: > I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 > s

Re: [GENERAL] pg_dump problem

2012-10-05 Thread Tom Lane
Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= writes: > I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 > server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server > 12.04. > When running pg_dump from the Linux server, I get: > postgres@ubuntupostgresql:~$ pg_dump -h 192.1

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Wolf Schwurack
-- OK, but this is not a showstopper here. Right? Your right - just a thought -- What settings do I need to give "/var/log" (currently root) so the pgbouncer process can write to it? Why are these special permissions needed You need to have a pgbouner directory in /var/log and have the owner pgb

[GENERAL] pg_dump problem

2012-10-05 Thread Leonardo M . Ramé
I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server 12.04. When running pg_dump from the Linux server, I get: postgres@ubuntupostgresql:~$ pg_dump -h 192.168.10.105 -U postgres ris pg_dump: SQL command failed pg_

Re: [GENERAL] Counting all results before LIMIT

2012-10-05 Thread Edson Richter
Em 05/10/2012 12:17, Mike Christensen escreveu: You could use a windowing function. Something like: SELECT x, y, z, COUNT(*) OVER() FROM Foo LIMIT 50; Good to know! I'll give a try! On Fri, Oct 5, 2012 at 8:02 AM, P Gouv > wrote: You cant. There is an article

Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer

2012-10-05 Thread Phoenix Kiula
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack wrote: > I use pgpool but some of the problem you listed are same as I had with pgpool Thanks Wolf, for the thoughts. > I would not run pgbouner in /var/run/pbbouner. Every time you reboot the > directory will get deleted. I set my parameter to an

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

2012-10-05 Thread Moshe Jacobson
On Fri, Oct 5, 2012 at 11:13 AM, Ivan Voras wrote: > > On the other hand, if the temp table can be completely ditched for a > > session variable or two, then yeah, that would be much better since > > you'd avoid the overhead of creating the table completely. > > Yes, this is what I was aiming at,

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

2012-10-05 Thread Ivan Voras
On 5 October 2012 15:55, Merlin Moncure wrote: > On Fri, Oct 5, 2012 at 3:09 AM, Ivan Voras wrote: >> I think I can make a fairly educated guess that catching exceptions >> while dealing with session variables should be much, much faster than >> creating any kind of a table :) > On the other han

Re: [GENERAL] Counting all results before LIMIT

2012-10-05 Thread Mike Christensen
You could use a windowing function. Something like: SELECT x, y, z, COUNT(*) OVER() FROM Foo LIMIT 50; On Fri, Oct 5, 2012 at 8:02 AM, P Gouv wrote: > You cant. There is an article about count performance. Generally its slow > but latest version 9.2 i think supports index for count under some

Re: [GENERAL] Counting all results before LIMIT

2012-10-05 Thread P Gouv
You cant. There is an article about count performance. Generally its slow but latest version 9.2 i think supports index for count under some condition.But 300 isnt that much that you should worry.Another modern solution is to not count results just add one more at limit to see if there is next page

[GENERAL] Add a stemmer for fts

2012-10-05 Thread P Gouv
Hello. I was trying to find a stemmer for greek but it isnt supported out of the box. Dictionaries dont help at all because they just limit the words they do nothing useful for my problem. I found several stemmers in php / java etc from lucene but i am not sure how i can use it with postgresql. I g

[GENERAL] Counting all results before LIMIT

2012-10-05 Thread Moshe Jacobson
We have a PHP web application that pulls results from the database and paginates them. We show e.g. "1-50 of 300" so the user knows how many total results there are, and which ones are currently being displayed. To achieve this, we use a query with LIMIT...OFFSET to get the displayed results, and w

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

2012-10-05 Thread Merlin Moncure
On Fri, Oct 5, 2012 at 3:09 AM, Ivan Voras wrote: > I think I can make a fairly educated guess that catching exceptions > while dealing with session variables should be much, much faster than > creating any kind of a table :) That is true, but it's not clear how using session variables keeps you

Re: [GENERAL] Trajectory of a [Pg] DBA

2012-10-05 Thread Shaun Thomas
On 10/04/2012 03:44 PM, Thalis Kalfigkopoulos wrote: Is it more common to start as a developer and change focus to DBA? In particular how does one go about starting as a Pg DBA? Is the most common case by migrating from another DBMS? You've already gotten a bunch of good responses from this th

Re: [GENERAL] Trajectory of a [Pg] DBA

2012-10-05 Thread Andrew Sullivan
On Thu, Oct 04, 2012 at 05:44:54PM -0300, Thalis Kalfigkopoulos wrote: > I see that most of the DBA job posts ask for Sr or Ssr which is > understandable given that databases are among a company’s most > valuable assets, but it is also an obvious catch-22. So I'd like to > ask the list's part- and

Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

2012-10-05 Thread Ivan Voras
On 5 October 2012 04:53, Moshe Jacobson wrote: > On Thu, Oct 4, 2012 at 6:12 AM, Ivan Voras wrote: >> >> On 01/10/2012 15:36, Moshe Jacobson wrote: >> > I am working on an audit logging trigger that gets called for every row >> > inserted, updated or deleted on any table. >> > For this, I need to

Re: [GENERAL] Moving from Java 1.5 to Java 1.6

2012-10-05 Thread Craig Ringer
On 10/04/2012 02:52 PM, Swayam Prakash Vemuri wrote: Hi We have an application which uses postgresql 7.4.5. Wow. Upgrade time on two levels: - You're running a fossilized point release. The latest 7.4.x is 7.4.30, 25 patch-levels after yours, and came out in October 2010 vs the August 2004

Re: [GENERAL] Moving from Java 1.5 to Java 1.6

2012-10-05 Thread Thomas Kellerer
John R Pierce, 05.10.2012 08:34: On 10/04/12 11:21 PM, Thomas Kellerer wrote: You are also moving to a Java version that is soon to be de-supported. Java is supported? Found the link as well: http://www.oracle.com/technetwork/java/javase/eol-135779.html Quote: "After February 2013, Oracle

Re: [GENERAL] Moving from Java 1.5 to Java 1.6

2012-10-05 Thread Thomas Kellerer
John R Pierce, 05.10.2012 08:34: You are also moving to a Java version that is soon to be de-supported. Java is supported? only if you mean the non-stop stream of updates brought on by web exploit exposures. Yes it is. In a similar way as PostgreSQL is "supported". if you're using Java as