Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Joshua D. Drake
On Thu, 16 Apr 2009 21:05:41 -0700 (PDT), kabindra kabin...@ttwin.com wrote: Hello I am using pgplus 8.3 in linux(centos) and trying to backup the database from windows machine using pgadmin 1.8.4 but i get an error while backing up. It says version mismatched and use -i option if I want to

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread kabindra
I think this is a bug in the version. If linux and windows pg_dump are not the same and what can be I do to backup the database. Thanks Joshua D. Drake wrote: On Thu, 16 Apr 2009 21:05:41 -0700 (PDT), kabindra kabin...@ttwin.com wrote: Hello I am using pgplus 8.3 in linux(centos) and

Re: [GENERAL] triggers

2009-04-17 Thread Yuriy Rusinov
Hello Gustavo ! Gente, buenos dias. Como consulto lo triggers desde psql? Unfortunately I don't know Spanish. As far as I understand your question was about triggers in postgresql. Trigger is the function calls automatically on operations insert-delete-update. -- Best regards, Sincerely

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Quan Zongliang
I think this is a bug in the version. If linux and windows pg_dump are not the same and what can be I do to backup the database. Hi, In next version(1.10), pgAdmin add a new option to ignore version mismatch check. Beta2 is here: http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0-beta2/

Re: [GENERAL] (P)SQL for a sum with constraints

2009-04-17 Thread Shug Boabby
Life sure would be easier if that were the case Michal, but no... that is not the case here. The sum is not a simple sum, it is a sum of all elements having a lower or equal A and the same C. This is a cumulative sum as pointed out by others. 2009/4/15 Michal Politowski mpol...@meep.pl: On Wed,

Re: [GENERAL] (P)SQL for a sum with constraints

2009-04-17 Thread Shug Boabby
Oh... and also, A, B, C are in the same table. 2009/4/17 Shug Boabby shug.boa...@gmail.com: Life sure would be easier if that were the case Michal, but no... that is not the case here. The sum is not a simple sum, it is a sum of all elements having a lower or equal A and the same C. This is a

Re: [GENERAL] Performance of full outer join in 8.3

2009-04-17 Thread Grzegorz Jaśkiewicz
On Thu, Apr 16, 2009 at 9:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: I've applied a patch for this.  It will be in 8.3.8, or if you're in a hurry you can grab it from our CVS server or here: http://archives.postgresql.org/message-id/20090416204228.57931754...@cvs.postgresql.org just out of

Re: [GENERAL] Performance of full outer join in 8.3

2009-04-17 Thread Grzegorz Jaśkiewicz
On Fri, Apr 17, 2009 at 9:22 AM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: just out of curiosity - when was it introduced, ie - which version was the first affected ? We're still on 8.3.5 here. (I had no idea release-notes have date), it got in by 8.3.4 (changed right after 8.3.3 was

Re: [GENERAL] select distinct, index not used

2009-04-17 Thread Thomas Guettler
Thank you Tom. The cron job for vacuum+analyze was not installed on the host. (I had this idea some seconds after posting) After vacuum+analyze the performance is good. I am happy. Nevertheless, on a different host with nearly the same data, a index scan is used. foo_hostone_foo=# explain

[GENERAL] Parallel postgres client

2009-04-17 Thread Igor Katson
Is there any kind of a parallel psql client, to control a cluster of databases, without the need to write a plproxy function for each request? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Chris . Ellis
What are folks doing to protect sensitive data in their databases? We're running on the assumption that the _really_ sensitive data is too sensitive for us to just trust the front-end programs that connect to it. The decision coming down from on-high is that we need to encrypt certain

Re: [GENERAL] (P)SQL for a sum with constraints

2009-04-17 Thread Shug Boabby
Michal... I must apologise, your suggestion worked a treat!!! I never realised it was possible to do a join on a table to itself before! 2009/4/15 Michal Politowski mpol...@meep.pl: On Wed, 15 Apr 2009 15:32:42 +0100, Shug Boabby wrote: I simplified my problem a little too much and now I'm

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Bill Moran
In response to chris.el...@shropshire.gov.uk: What are folks doing to protect sensitive data in their databases? We're running on the assumption that the _really_ sensitive data is too sensitive for us to just trust the front-end programs that connect to it. The decision coming

Re: [GENERAL] Performance of full outer join in 8.3

2009-04-17 Thread Christian Schröder
Tom Lane wrote: I've applied a patch for this. It will be in 8.3.8, or if you're in a hurry you can grab it from our CVS server or here: Thanks a lot for your effort and the quick response! Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT

Re: [GENERAL] Full text search strategy for names

2009-04-17 Thread Rick Schumeyer
You can get extra (undesirable) results, depending on the name. For example, if you are searching for the last name of Ricks, you will also find all authors whose first name is Rick. I also noticed that the directions for indexing multiple columns don't seem to be quite right. In section

Re: [GENERAL] Full text search strategy for names

2009-04-17 Thread Grzegorz Jaśkiewicz
so it looks to me, like you are trying to use wrong tool for the job. Why not just normalize names to #3 ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problem with invalid byte sequence and log_min_error_statement

2009-04-17 Thread Janning Vygen
On Wednesday 15 April 2009 19:21:03 you wrote: Janning Vygen vy...@kicktipp.de writes: Now i see that the errors occur _exactly_ every 4000 seconds (1 hour, 6 minutes and 40 seconds). I have no clue as i only have one cronjob at night concerning postgresql. I have no autovacuum running

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Chris . Ellis
Take the performance hit, If people on high want the data encrypted, then they have to suffer the performance penalty, however bad. As reasonable as that sounds, I don't think it's true. We've already brainstormed a dozen ways to work around the performance issue (creative hashing,

[GENERAL] Common table expressions and threaded comments

2009-04-17 Thread Cultural Sublimation
Greetings, I need to implement the classical problem of threaded comments in a Postgresql database. I know that the upcoming 8.4 release includes Common Table Expressions, so I have to ask: is this feature the mother-of-all-solutions to the threaded comments problem, or is it still worth

Re: [GENERAL] Common table expressions and threaded comments

2009-04-17 Thread Grzegorz Jaśkiewicz
On Fri, Apr 17, 2009 at 1:40 PM, Cultural Sublimation cultural_sublimat...@yahoo.com wrote: Greetings, I need to implement the classical problem of threaded comments in a Postgresql database.  I know that the upcoming 8.4 release includes Common Table Expressions, so I have to ask: is this

Re: [GENERAL] postgres cascade weird behaviour

2009-04-17 Thread Adrian Klaver
On Thursday 16 April 2009 6:50:46 pm Irwan Hendra wrote: Hi I have table A with pk that is being referenced by 5 other tables as foreign key I have put all those tables reference to be ON DELETE CASCADE However for some reason when I tried to delete one of the row in table A, out of 5

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Sam Mason
On Thu, Apr 16, 2009 at 05:06:13PM -0400, Bill Moran wrote: I disagree. We're already addressing the issues of security on the application level through extensive testing, data validation out the wazoo (to prevent SQL Injection and other application breaches). All our servers are in highly

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Bill Moran
In response to Sam Mason s...@samason.me.uk: On Thu, Apr 16, 2009 at 05:06:13PM -0400, Bill Moran wrote: I disagree. We're already addressing the issues of security on the application level through extensive testing, data validation out the wazoo (to prevent SQL Injection and other

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Sam Mason
On Fri, Apr 17, 2009 at 09:52:30AM -0400, Bill Moran wrote: In response to Sam Mason s...@samason.me.uk: For example; you say that you don't trust the application, yet the user must trust the application as they're entering their secret into it. How does the user ascertain that the

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Tom Lane
Quan Zongliang quanzongli...@gmail.com writes: In next version(1.10), pgAdmin add a new option to ignore version mismatch check. This is a really bad idea, as it opens you up to the possibility of silently wrong dumps. regards, tom lane -- Sent via pgsql-general

Re: [GENERAL] Common table expressions and threaded comments

2009-04-17 Thread Tom Lane
Cultural Sublimation cultural_sublimat...@yahoo.com writes: I need to implement the classical problem of threaded comments in a Postgresql database. I know that the upcoming 8.4 release includes Common Table Expressions, so I have to ask: is this feature the mother-of-all-solutions to the

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Bill Moran
In response to Sam Mason s...@samason.me.uk: As far as the trust factor, you've blurred the lines a bit. My job is to ensure that the user doesn't know or care about the lines between application and database, but trusts the system as a whole. However, I need to clearly define those

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Dave Page
On Fri, Apr 17, 2009 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: Quan Zongliang quanzongli...@gmail.com writes: In next version(1.10), pgAdmin add a new option to ignore version mismatch check. This is a really bad idea, as it opens you up to the possibility of silently wrong dumps.

Re: [GENERAL] Removing Constraints Efficiently

2009-04-17 Thread Tom Lane
Jeff Cook j...@deserettechnology.com writes: I'm attempting to remove constraints (primary/foreign keys) ahead of a massive import. Once the data has been imported, I would like to regenerate the constraints I removed. This process is recommended in PostgreSQL's documentation and incidentally

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Tom Lane
Dave Page dp...@pgadmin.org writes: On Fri, Apr 17, 2009 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: Quan Zongliang quanzongli...@gmail.com writes: In next version(1.10), pgAdmin add a new option to ignore version mismatch check. This is a really bad idea, as it opens you up to the

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Will Harrower
Bill Moran wrote: John R Pierce pie...@hogranch.com wrote: Eric Soroos wrote: an index on the encrypted SSN field would do this just fine. if authorized person needs to find the record with a specific SSN, they encrypt that SSN and then look up the ciphertext in the database...

Re: [GENERAL] Looking for advice on database encryption

2009-04-17 Thread Sam Mason
On Fri, Apr 17, 2009 at 10:33:15AM -0400, Bill Moran wrote: The goal is not to trust any one part of the system. As a result, we can protect the data across multiple security failures. As far as I know this isn't a good way to go about designing secure systems. You're better off defining a set

Re: [GENERAL] pgadmin 1.8.4 gives error while backing up

2009-04-17 Thread Dave Page
On Fri, Apr 17, 2009 at 4:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: As of 8.4, pg_dump ignores --ignore-version altogether, because of the way it's been rampantly abused.  There was never any intention that that should be a flag to use routinely; it was meant to get you out of emergency

[GENERAL] Set search_path for session

2009-04-17 Thread Mary Sipple
We have a script wrapper for psql which reads a configuration file and constructs a psql command to connect to the configured instance of postgres. We would like to include a search_path setting in the configuraration file and have the wrapper use that to set the search_path for the psql session.

Re: [GENERAL] Trigger error

2009-04-17 Thread sub_woofer
Hi Thank you both for your responses and apologies for the late reply! Tom you were correct! :) I split the If statements into 2 for the old and new tests and it all worked! :) Thanks once again for all your help! :) t. Tom Lane-2 wrote: sub_woofer tqzeli...@yahoo.co.uk writes: IF

Re: [GENERAL] Does anyone use postgreSQL(windows version)+Hibernate with middlegen?

2009-04-17 Thread rolandpeng
I have found the correct combinations to solve this problem. Let me brief what I have done,and the way is simple. 1.download latest middlegen from CVS (:pserver:anonym...@middlegen.cvs.sourceforge.net:/cvsroot/middlegen) 2.build the source with ant,then I have middlegen2.2-dev bundles jar

Re: [GENERAL] Set search_path for session

2009-04-17 Thread Scott Mead
On Fri, Apr 17, 2009 at 10:47 AM, Mary Sipple msip...@iii.com wrote: We have a script wrapper for psql which reads a configuration file and constructs a psql command to connect to the configured instance of postgres. We would like to include a search_path setting in the configuraration file

Re: [GENERAL] Set search_path for session

2009-04-17 Thread Scott Mead
On Fri, Apr 17, 2009 at 10:47 AM, Mary Sipple msip...@iii.com wrote: We have a script wrapper for psql which reads a configuration file and constructs a psql command to connect to the configured instance of postgres. We would like to include a search_path setting in the configuraration file

Re: [GENERAL] Set search_path for session

2009-04-17 Thread Alvaro Herrera
Mary Sipple wrote: We have a script wrapper for psql which reads a configuration file and constructs a psql command to connect to the configured instance of postgres. We would like to include a search_path setting in the configuraration file and have the wrapper use that to set the search_path

[GENERAL] number of relations reported by vacuumdb -av

2009-04-17 Thread Irene Barg
Hi, I have a PostgreSQL installation with 8 databases (counting postgres, template0, and template1). I run 'vacuumdb -z' daily on 3 of the largest user databases. The vacuumdb logs show the 'max_fsm_pages' need to be increased with almost each vacuum. So I did a 'vacuumdb -av' on all the

[GENERAL] Migration/Upgrade Problems

2009-04-17 Thread stevefoss
I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal success. I tried pg_dumpall and got the tables but lost most of the data. I also tried pg_dump but keep getting FATAL 1: Database whatever does not exist in the system catalog errors. I also tried using the latest

Re: [GENERAL] Migration/Upgrade Problems

2009-04-17 Thread David Fetter
On Fri, Apr 17, 2009 at 09:19:31AM -0700, stevefoss wrote: I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal success. I tried pg_dumpall and got the tables but lost most of the data. I also tried pg_dump but keep getting FATAL 1: Database whatever does not exist

Re: [GENERAL] Migration/Upgrade Problems

2009-04-17 Thread Joshua D. Drake
On Fri, 17 Apr 2009 09:19:31 -0700 (PDT), stevefoss sfos...@autotraninc.com wrote: I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal success. I tried pg_dumpall and got the tables but lost most of the data. I also tried pg_dump but keep getting FATAL 1: Database

[GENERAL] Unable to archive WAL files

2009-04-17 Thread Nikola
I am running PostgreSQL 8.2.4 on Windows 2003 Server. I have a setup where WAL files are archived to another drive on the same machine. The command looks like this: archive_command = 'COPY %p F:/recovery/%f' This works well, but I often get the following error in the log: [2009-04-17 12:07:34

Re: [GENERAL] Migration/Upgrade Problems

2009-04-17 Thread Alvaro Herrera
stevefoss wrote: I am trying to upgrade my postgresql data from 7.0.3 to 8.3.7 with marginal success. I tried pg_dumpall and got the tables but lost most of the data. I also tried pg_dump but keep getting FATAL 1: Database whatever does not exist in the system catalog errors. No

Re: [GENERAL] Set search_path for session

2009-04-17 Thread Scott Marlowe
On Fri, Apr 17, 2009 at 8:47 AM, Mary Sipple msip...@iii.com wrote: We have a script wrapper for psql which reads a configuration file and constructs a psql command to connect to the configured instance of postgres. We would like to include a search_path setting in the configuraration file and

Re: [GENERAL] Migration/Upgrade Problems

2009-04-17 Thread Tom Lane
Joshua D. Drake j...@commandprompt.com writes: Your library problems are likely because you just copied the binary? I assume you didn't actually install postgresql. That's what it looked like to me --- you can't just copy an executable that's been built on a far newer platform. However, you

Re: [GENERAL] Common table expressions and threaded comments

2009-04-17 Thread Grzegorz Jaśkiewicz
I can only say for my own little experience. Features like that, allow users to exchange less (none?) data between app and pg. That's the main advantage of CTE. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] High memory usage with savepoints encoding differences

2009-04-17 Thread Dylan Adams
I'm running into a situation where postmaster is consuming significantly more memory than I would expect. This only seems to happen when I combine savepoints with differences between client and database encoding. I originally discovered this while running some Java code which uses JDBC (the

Re: [GENERAL] High memory usage with savepoints encoding differences

2009-04-17 Thread Tom Lane
Dylan Adams dad...@bybaxter.com writes: I'm running into a situation where postmaster is consuming significantly more memory than I would expect. This only seems to happen when I combine savepoints with differences between client and database encoding. I originally discovered this while

Re: [GENERAL] High memory usage with savepoints encoding differences

2009-04-17 Thread Dylan Adams
Tom Lane writes: I think this example is pretty artificial. The fundamental reason memory is increasing is that each subtransaction can require some state storage. In the example the per-subtransaction CurTransactionContexts are not getting used for anything except encoding conversion on