Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Pavel Stehule : > 2010/1/14 Vincenzo Romano : >> 2010/1/14 Pavel Stehule : >>> 2010/1/14 Vincenzo Romano : 2010/1/14 Adrian Klaver : > On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: >> ... CREATE OR REPLACE FUNCTION f() RETURNS VOID LANGUAGE plpgsq

[GENERAL] vacuum issues under load?

We have recently discovered a problem with our slony-1 cluster of 8.1.19 installs. Specifically, we are unable to vacuum a table on the master node; vacuum always hangs on the same index of the same table. If we do a slony switchover and make the other node the master, then *it* will become una

[GENERAL] Calling a plpgsql function with composite type as parameter?

I'm working on an e-commerce site that calls various plpgsql functions from a Python app.  One of the things I need to do is create a shopping cart and add several items to it.  I'd like for both of these steps to be contained within the same transaction so if an error occurs adding an item to the

Re: [GENERAL] DELETE Weirdness

On Fri, 2010-01-15 at 13:55 +1300, Ravi Chemudugunta wrote: > I cannot quite understand this; Are the contents of the IN query > worked out ONCE per outer query and therefore become invalid when > DELETE comes along and changes items that were part of the set ? (for > e.g.) The command itself gets

[GENERAL] DELETE Weirdness

There are two ways of deleting things, DELETE FROM WHERE PK IN ( ...SET... ); DELETE FROM USING WHERE AND ; I am deleting from a table where the rows are inter-related (it is a tree); using the first version with IN, it does not delete all rows; there is a trigger that rearranges the rows wh

Re: [GENERAL] Memory Access Violation While Using PQexec

On 14/01/2010 4:49 PM, Yan Cheng Cheok wrote: I encounter case when I call a stored procedure for 299166 th times (intensive, i put a non-stop while true loop to call stored procedure) , the following exception will be thrown from PQexec. I am rather sure the exception are from PQexec, as ther

Re: [GENERAL] Split pg_dump dump into files and then combine it back

Dmitry Koterov writes: > Is there a tool (or a way) to parse a pg_dump'ed (structure only) dump file > into smaller files (each function in its own file, its table in its own > etc.) with ability to combine these files later into the proper dump file? > The main problem is dependencies. Sometimes

Re: [GENERAL] Avoid transaction abot if/when constraint violated

Gauthier, Dave wrote: Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and

[GENERAL] Split pg_dump dump into files and then combine it back

Hello. Is there a tool (or a way) to parse a pg_dump'ed (structure only) dump file into smaller files (each function in its own file, its table in its own etc.) with ability to combine these files later into the proper dump file? The main problem is dependencies. Sometimes functions are defined b

Re: [GENERAL] Moving database cluster

On Thu, Jan 14, 2010 at 06:21:14PM -0300, Fernando Hevia wrote: - - - > -Mensaje original- - > De: Guillaume Lelarge [mailto:guilla...@lelarge.info] - > - > Le 14/01/2010 21:40, Fernando Hevia a écrit : - > > - > > An Ubuntu install creates a postgres cluster automatically on - > > /

Re: [GENERAL] Avoid transaction abot if/when constraint violated

On Thu, Jan 14, 2010 at 3:12 PM, Gauthier, Dave wrote: > Hello ! > > > > I have a long list of records I want to insert into a table in such a way as > I can trap and report any/all constraint violations before rolling back (or > opting to commit).  Unfortunately, after I hit the first constraint

Re: [GENERAL] Avoid transaction abot if/when constraint violated

Ya, I don't mind that it eventually fails (why have constraints otherwise), but I'd like to see all the constraint violations for the set of records. I actually have something working. I'm coding in perl/DBI, and I just "rollback" after each constraint violation and keep going. Nothing from th

Re: [GENERAL] Avoid transaction abot if/when constraint violated

On Thu, 2010-01-14 at 15:12 -0700, Gauthier, Dave wrote: > Hello ! > > > > I have a long list of records I want to insert into a table in such a > way as I can trap and report any/all constraint violations before > rolling back (or opting to commit). Unfortunately, after I hit the > first cons

[GENERAL] Avoid transaction abot if/when constraint violated

Hello ! I have a long list of records I want to insert into a table in such a way as I can trap and report any/all constraint violations before rolling back (or opting to commit). Unfortunately, after I hit the first constraint violation, it aborts the transaction, and then reports "ERROR: cur

Re: [GENERAL] Moving database cluster

On Thursday 14 January 2010, Fernando Hevia elucidated thus: > > The easiest way is to shut down Pg, move the 'main' directory > > somewhere else, and then point a symlink to the new location. > > Thanks for your reply. > I had considered this first but then I wasn't sure if there would be > any pe

Re: [GENERAL] Moving database cluster

> -Mensaje original- > De: > > On Thursday 14 January 2010, Fernando Hevia elucidated thus: > > An Ubuntu install creates a postgres cluster automatically on > > /var/lib/postgresql/8.4/main Whats the best procedure for > moving this > > cluster to an other location? Should I just r

Re: [GENERAL] Moving database cluster

> -Mensaje original- > De: Guillaume Lelarge [mailto:guilla...@lelarge.info] > > Le 14/01/2010 21:40, Fernando Hevia a écrit : > > > > An Ubuntu install creates a postgres cluster automatically on > > /var/lib/postgresql/8.4/main Whats the best procedure for > moving this > > clust

Re: [GENERAL] Moving database cluster

On Thursday 14 January 2010, Fernando Hevia elucidated thus: > An Ubuntu install creates a postgres cluster automatically on > /var/lib/postgresql/8.4/main > Whats the best procedure for moving this cluster to an other > location? Should I just rerun initdb? What happens then with the > default clu

Re: [GENERAL] Moving database cluster

Le 14/01/2010 21:40, Fernando Hevia a écrit : > > An Ubuntu install creates a postgres cluster automatically on > /var/lib/postgresql/8.4/main > Whats the best procedure for moving this cluster to an other location? > Should I just rerun initdb? What happens then with the default cluster or > how

[GENERAL] Moving database cluster

An Ubuntu install creates a postgres cluster automatically on /var/lib/postgresql/8.4/main Whats the best procedure for moving this cluster to an other location? Should I just rerun initdb? What happens then with the default cluster or how could I delete it? Thanks, Fernando. -- Sent via pgsql

Re: [GENERAL] FOSDEM dinner

On Thu, Jan 14, 2010 at 09:51:34AM +0200, Dave Coventry wrote: > Yes, I'm in South Africa, which might make it problematic! You're on the right land mass, assuming you count being able to cross the Suez canal on foot ;) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 A

[GENERAL] PostreSQL PostgreSQL 8.1.18 and Tsearch2 context error

Hi guys, I'm working with full-text search on my project, however i'm receiving an "Config file error: 7 ERROR: no tsearch config CONTEXT" message but i don't know what it means. Can you help me about this error?, i will apreciate so much any suggestion about it. Thank's in advance, Rodrigo

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Vincenzo Romano : > 2010/1/14 Pavel Stehule : >> 2010/1/14 Vincenzo Romano : >>> 2010/1/14 Adrian Klaver : On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > ... >>> CREATE OR REPLACE FUNCTION f() >>> RETURNS VOID >>> LANGUAGE plpgsql >>> AS $function$ >>> DECLARE >>>

Re: [GENERAL] Configuration Optimisation

Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.55.07 Howard Cole wrote: Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Wind

Re: [GENERAL] Configuration Optimisation

On Thursday 14. January 2010 16.55.07 Howard Cole wrote: > Leif Biberg Kristensen wrote: > > On Thursday 14. January 2010 16.02.12 Andy Colson wrote: > > > > See my reply above. My 250 tps seems in line with the 700 tps on a modern > > system that the OP gets on his Windows setup. To me it seem

Re: [GENERAL] Configuration Optimisation

Leif Biberg Kristensen wrote: On Thursday 14. January 2010 16.02.12 Andy Colson wrote: See my reply above. My 250 tps seems in line with the 700 tps on a modern system that the OP gets on his Windows setup. To me it seems like something is broken on his Ubuntu setup. regards, I am hopin

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Vincenzo Romano : > 2010/1/14 Pavel Stehule : >> 2010/1/14 Vincenzo Romano : >>> 2010/1/14 Adrian Klaver : On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > ... >>> CREATE OR REPLACE FUNCTION f() >>> RETURNS VOID >>> LANGUAGE plpgsql >>> AS $function$ >>> DECLARE >>>

Re: [GENERAL] Configuration Optimisation

On Thursday 14. January 2010 16.02.12 Andy Colson wrote: > I think an important question is: are both os's really flushing all the > way to disk, or is someone lying to you? > > Assuming your workload is IO bound, I'd bet windows is write caching and > linux is not. See my reply above. My 250 t

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Pavel Stehule : > 2010/1/14 Vincenzo Romano : >> 2010/1/14 Adrian Klaver : >>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: ... >> CREATE OR REPLACE FUNCTION f() >> RETURNS VOID >> LANGUAGE plpgsql >> AS $function$ >> DECLARE >>  cmd TEXT; >> BEGIN >>  EXECUTE ' >>    S

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Vincenzo Romano : > 2010/1/14 Adrian Klaver : >> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: >> >>> > >>> > Scott, thanks for that I must have read through that section several >>> > times at least with out picking up on it. >>> > >>> > -- >>> > Adrian Klaver >>> > adr

Re: [GENERAL] Configuration Optimisation

On 1/14/2010 7:31 AM, Howard Cole wrote: Hi, I am trying to optimise a database server to give the best performance possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the basis that most people seem to be of the opinion that postgres runs better on linux than windows. To test my

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Adrian Klaver : > On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > >> > >> > Scott, thanks for that I must have read through that section several >> > times at least with out picking up on it. >> > >> > -- >> > Adrian Klaver >> > adrian.kla...@gmail.com >> >> Really? >> >

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote: > > > > Scott, thanks for that I must have read through that section several > > times at least with out picking up on it. > > > > -- > > Adrian Klaver > > adrian.kla...@gmail.com > > Really? > > That section is not in any page of the

Re: [GENERAL] Configuration Optimisation

On Thursday 14. January 2010 14.31.07 Howard Cole wrote: > Test setup: pgbench -i -s 5 > Test run: pgbench -T 120 > > You may think this is a short test, but running it for much longer does > not seem to make a significant difference. > > Now running on windows I get ~ 700 TPS, but on linux I a

[GENERAL] Configuration Optimisation

Hi, I am trying to optimise a database server to give the best performance possible, so I switched from windows 2k3 to linux (ubuntu 9.10) on the basis that most people seem to be of the opinion that postgres runs better on linux than windows. To test my optimisation of the system, I run a si

Re: [GENERAL] Setting global parameter in Postgres 8.3

On Thu, Jan 14, 2010 at 11:10 AM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to a.bhattacha...@sungard.com : > > CREATE TABLESPACE bank_master_tbsp OWNER bank LOCATION ?$drive/data/bank/ > > master_tbsp'; > > > > CREATE TABLESPACE bank_static_tbsp OWNER bank LOCATION ?

Re: [GENERAL] Setting global parameter in Postgres 8.3

In response to a.bhattacha...@sungard.com : > CREATE TABLESPACE bank_master_tbsp OWNER bank LOCATION ?$drive/data/bank/ > master_tbsp'; > > CREATE TABLESPACE bank_static_tbsp OWNER bank LOCATION ?$drive /data/bank/ > static_tbsp'; > > > > I need to replace the $drive with a absoulte location i

[GENERAL] Setting global parameter in Postgres 8.3

Hi All, Is there any way we can set the global parameter in Postgres 8.3? As I have a sql script which need to accept some parameter at run time. For example I have sql script for creating tablespaces called "create_tablespace.sql" Inside the sql script I have the following statements

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

2010/1/14 Vincenzo Romano : > The documentation says (also in v8.5) "These symbols refer to values > supplied in the USING clause". > "values"and not "variable name or reference". This leads to the useful > feature mentioned a line later in the same page. > Once you have a value replaced you can av

[GENERAL] Memory Access Violation While Using PQexec

I encounter case when I call a stored procedure for 299166 th times (intensive, i put a non-stop while true loop to call stored procedure) , the following exception will be thrown from PQexec. I am rather sure the exception are from PQexec, as there is a just before cout and just after cout wra

R: Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

The documentation says (also in v8.5) "These symbols refer to values supplied in the USING clause". "values"and not "variable name or reference". This leads to the useful feature mentioned a line later in the same page. Once you have a value replaced you can avoid the restrictions you now mention o

Re: [GENERAL] Functions - how to get the date created or updated

2010/1/14 Huda Booley (h...@careerjunction.co.za) : > Hi > > > > Is there a pg_stat table that one can query, or a script to use to determine > what date a function was created, or what date it was updated / modified? no regards Pavel Stehule > > > > Ta > > > > > > Huda Booley > DBA | CareerJunc

[GENERAL] Functions - how to get the date created or updated

Hi Is there a pg_stat table that one can query, or a script to use to determine what date a function was created, or what date it was updated / modified? Ta Huda Booley DBA | CareerJunction | Better jobs. More often. Web: www.careerjunction.co.za |Email: h...@careerjunction.co.za