Re: [GENERAL] Query performance question on a large table

2004-01-07 Thread Sean Shanny
Tom, Sort of piggybacking on this thread but why the suggestion to drop the use of DISTINCT in 7.4? We use DISTINCT all over the place to eliminate duplicates in sub select statements. Running 7.4.0 currently on FreeBSD5.1 Dell 2650 4GB RAM 5 disk SCSI array hardware RAID 0 Example: explain

Re: [GENERAL] Query performance question on a large table

2004-01-07 Thread Tom Lane
Sean Shanny [EMAIL PROTECTED] writes: Sort of piggybacking on this thread but why the suggestion to drop the use of DISTINCT in 7.4? Because the 7.4 planner can decide for itself whether DISTINCT'ifying the sub-select output is the best way to proceed or not. There is more than one good way

Re: [GENERAL] problems with transaction blocks

2004-01-07 Thread Chris Travers
Transactions are atomic. What you are asking to do violates the whole concept of a transaction. You can, however, do these inserts outside of the transaction block. Best Wishes, Chris Travers - Original Message - From: Chris Ochs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday,

Re: [GENERAL] Query performance question on a large table

2004-01-07 Thread Egyd Csaba
Hi Tom, thank you, I'll upgrade as soon as I can. Anyway I've already planned to do so for a while. I'll keep in mind your remarks concerning the DISTINCT clause too. Bye and Best Regards, -- Csaba -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane

[GENERAL] Reparse PL/pgSQL Function?

2004-01-07 Thread John Sidney-Woollett
Is it possible to (automatically) force the reparse of a stored PL/pgSQL function following the deletion and recreation of an object that was referenced within the function (for example a table or another function)? The need to re-create (create or replace ...) the function is a pain as you have

Re: [GENERAL] Loading a dumped databse from cd

2004-01-07 Thread Richard Huxton
On Wednesday 07 January 2004 04:38, Paul Mc Gee wrote: hi everybody i have postgresql installed on my red hat linux machine and i want to load up a dumped postgresql database which i have on cdrom. does anyone know how i could do this? You probably want to read up on pg_restore (and perhaps

[GENERAL] deadlocks - sharelocks on transactions

2004-01-07 Thread Tim McAuley
Hi, I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock? ERROR: deadlock detected DETAIL: Process

Re: [GENERAL] problems with transaction blocks

2004-01-07 Thread scott.marlowe
Another good way to handle this is to put a trigger on the table that diverts inserts that would fail to a holding table. While this will slow down the inserts, it will allow you to insert large lists of dubious quality and worry about the bad rows later. My preference is to fix the data

Re: [GENERAL] Reparse PL/pgSQL Function?

2004-01-07 Thread scott.marlowe
On Wed, 7 Jan 2004, John Sidney-Woollett wrote: Is it possible to (automatically) force the reparse of a stored PL/pgSQL function following the deletion and recreation of an object that was referenced within the function (for example a table or another function)? Would CREATE OR REPLACE

Re: [GENERAL] problems with transaction blocks

2004-01-07 Thread Bruno Wolff III
On Tue, Jan 06, 2004 at 16:52:12 -0800, Chris Ochs [EMAIL PROTECTED] wrote: I want to do a series of inserts within a single transaction block, but with postgresql if one insert fails, the whole block is aborted. Is there any way to get around this behavior so that postgresql won't abort the

Re: [GENERAL] problems with transaction blocks

2004-01-07 Thread Rodrigo Malara
Another way is break the transaction. Instead of consisting of many inserts, each insert is a transaction itself. Do a Begin and a Commit (or rollback) circling the insert statement. HTH Rodrigo Malara Em Qua, 2004-01-07 às 14:41, scott.marlowe escreveu: Another good way to handle this is to put

Re: [GENERAL] deadlocks - sharelocks on transactions

2004-01-07 Thread Stephan Szabo
On Wed, 7 Jan 2004, Tim McAuley wrote: Hi, I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock?

Re: [GENERAL] Any way to have CREATEUSER privs without having all privs?

2004-01-07 Thread Bruno Wolff III
On Sat, Dec 06, 2003 at 22:33:00 -0800, Ezra Epstein [EMAIL PROTECTED] wrote: Thank you Bruno for the informative reply. I'm not sure how ident solves this. I would like the session to run as the actual user (via set session authorization) so that that user's actual privs are enforced.

Re: [GENERAL] deadlocks - sharelocks on transactions

2004-01-07 Thread Tom Lane
Tim McAuley [EMAIL PROTECTED] writes: I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock?

Re: [GENERAL] Query performance question on a large table

2004-01-07 Thread Bruno Wolff III
On Wed, Jan 07, 2004 at 02:31:22 -0500, Tom Lane [EMAIL PROTECTED] wrote: I just a couple days ago added some logic to CVS tip to notice that the sub-select has a DISTINCT clause, and not add unnecessary unique-ifying processing on top of it. So in 7.5, writing a DISTINCT clause will