Re: [GENERAL] PostgreSQL clustering (shared disk)

On 16.08.2007 08:42, Mikko Partio wrote: I have a mission to implement a two-node active-passive PostgreSQL cluster. The databases at the cluster are rather large (hundreds of GB's) which opts me to consider a shared disk environment. I know this is not natively supported with PostgreSQL, but I h

Re: [GENERAL] PostgreSQL clustering (shared disk)

Hi, On Thu, 2007-08-16 at 09:42 +0300, Mikko Partio wrote: > The idea would be that the cluster programs with gfs (and HP ilo) > would make sure that only one postmaster at a time would be able to > access the shared disk, and in case the active node fails the cluster > software would shift the se

Re: [GENERAL] PostgreSQL clustering (shared disk)

Hi, On Thu, 2007-08-16 at 10:05 +0300, Devrim GÜNDÜZ wrote: > (Of course, there is still a risk of data loss -- the postmasters are > not aware of each other and they don't share each other's buffers, > etc.) Err... I was talking about uncommitted transactions, and of course this does not mean a

Re: [GENERAL] UTF-8 encoding problem

Am Donnerstag, 16. August 2007 08:40 schrieb bhyuan: > Can I ignore the error message by confiing the config file? No, there are not provisions for that. Some errors of this type used to be ignored, but that led to SQL injection-like security issues, so you don't want that. -- Peter Eisentrau

[GENERAL] INSERT before UPDATE?

Hello, I would like to ask you for an advice. There are two tables in my PostgreSQL database - main table with datas and second with translations for all languages of these records. When I try to UPDATE a record in the language table and this record doesn't exists there I need to INSERT into the

Re: [GENERAL] INSERT before UPDATE?

am Thu, dem 16.08.2007, um 10:30:01 +0200 mailte Ji?í N?mec folgendes: > Hello, > > I would like to ask you for an advice. > > There are two tables in my PostgreSQL database - main table with datas and > second with translations for all languages of these records. > > When I try to UPDATE a rec

[GENERAL] array for cstring type

there is no array for cstring type. I created it manually in pg_type table. It seems to me that it's working fine. Can this cause any problems? PHP receives data as text. Records are converted to cstring through record_out function. So I just wanted to substitute refcursors/setof records. Can this

Re: [GENERAL] array for cstring type

Am Donnerstag, 16. August 2007 11:22 schrieb Sergey Moroz: > there is no array for cstring type. I created it manually in pg_type table. > It seems to me that it's working fine. Can this cause any problems? The cstring type is not for general use. Use varchar or text. -- Peter Eisentraut http:/

Re: [GENERAL] Deadlocks caused by a foreign key constraint

On Thu, Aug 16, 2007 at 01:21:43AM -0400, Tom Lane wrote: > Decibel! <[EMAIL PROTECTED]> writes: > > But... taking a quick look at RI_FKey_check in backend/utils/adt/ > > ri_triggers.c, I don't see it checking to see if the FK has changed, > > which seems odd. I would think that if the FK fields

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Scott Marlowe" wrote: >When I go to amazon.com I only ever get three pages of results. ever. > Because they know that returning 190 pages is not that useful, as >hardly anyone is going to wander through that many pages. > >Google, you'll notice says "Results 1 - 10 of about 5,610,000 for >blacks

Re: [GENERAL] memory optimization

On Thu, Aug 16, 2007 at 09:17:37AM +0300, Sabin Coanda wrote: > >> > >> So, what is better from the postgres memory point of view: to use > >> temporary > >> objects, or to use common variables ? > > > >A temp table might take *slightly* more room than variables... > > > >> Can you suggest me othe

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: > "Scott Marlowe" wrote: > > >When I go to amazon.com I only ever get three pages of results. ever. > > Because they know that returning 190 pages is not that useful, as > >hardly anyone is going to wander through that many pages. > >

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Decibel! wrote: >On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: >> "Scott Marlowe" wrote: >> >> >When I go to amazon.com I only ever get three pages of results. ever. >> > Because they know that returning 190 pages is not that useful, as >> >hardly anyone is going to wander throug

[GENERAL] how to get id of currently executed query?

hi, i need something to distinguish two separate calls for some select. i tried to use c functions GetCurrentTransactionId() and GetCurrentCommandId(), but there is a problem: if i'll make plpgsql function, which uses GetCurrentTransactionId() and GetCurrentCommandId() - getCurrentCommandId change

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Rainer Bauer" <[EMAIL PROTECTED]> writes: > Anyway, what Phoenix is trying to say is that 2 queries are required: One to > get the total count and one to get the tuples for the current page. I reckon > it would help, if the query returning the result set could also report the > total no. of tuple

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> But if you go to eBay, they always give you an accurate count. Even if the > >> no. > >> of items found is pretty large (example: ). > > > >And I'd bet money that they're using a full text search of some kind to

[GENERAL] Performance question

Hello list, If I've got a trigger that calls a function each time there is a DELETE or UPDATE opration on a table in my system, and in this function I retrieve some boolean information from another table and based on this information, additional code will be ran or not in this function. Could the

Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)

On 16/08/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > --- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > > On 16/08/07, Rodrigo De León <[EMAIL PROTECTED]> wrote: > > > On Aug 15, 11:46 pm, [EMAIL PROTECTED] ("Phoenix Kiula") wrote: > > > > Appreciate any tips, because it would > > > > be n

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote: > Anyway, what Phoenix is trying to say is that 2 queries are required: One to > get the total count and one to get the tuples for the current page. I reckon > it would help, if the query returning the result set could also report the >

Re: [GENERAL] INSERT before UPDATE?

am Thu, dem 16.08.2007, um 14:11:07 +0200 mailte Ji??í N??mec folgendes: > > see > > http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > > Yes I see, but I'll try to describe it in more detail: > > I could write plpgsql trigger function which wi

[GENERAL] Accessing tables in other databases, through functions

Probably an optimistic question - can a user with access to two databases create a function in one to access tables in the other? Or triggers? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Accessing tables in other databases, through functions

take a look at dblink in contrib module With Regards Ashish... - Original Message - From: "Phoenix Kiula" <[EMAIL PROTECTED]> To: "Postgres General" Sent: Thursday, August 16, 2007 6:18 PM Subject: [GENERAL] Accessing tables in other databases, through functions Probably an optimist

Re: [GENERAL] [SQL] Embedded C++ with ecpg?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jean-David Beyer wrote: > Tom Lane wrote: >> Jean-David Beyer <[EMAIL PROTECTED]> writes: >>> What is the current status of (pre) compiling Embedded SQL in C++ programs? >> I just asked Michael Meskes about that (had you put a support request >> into R

Re: [GENERAL] Accessing tables in other databases, through functions

Hello look on see http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/ or http://pgfoundry.org/projects/dblink-tds/ Regards Pavel Stehule 2007/8/16, Phoenix Kiula <[EMAIL PROTECTED]>: > Probably an optimistic question - can a user with access to two > databases create a function in

Re: [GENERAL] UTF-8 encoding problem

Thanks for your replay. Maybe SQL injection-like security issues will occour, but I find that differend version of Postgresql get different result. Such as the sql set client_encoding='SJIS'; select '\xc3\xaa',* from xxx; on V7.4 @RH3 got \xc3\xaa on [EMAIL PROTECTED] got (blank) on [EMA

[GENERAL] UTF-8 encoding

On Wed, August 15, 2007 21:15, Phoenix Kiula wrote: > > Thanks. Here's my locale information: > >> locale > LANG=en_US.UTF-8 > LC_CTYPE="en_US.UTF-8" > LC_NUMERIC="en_US.UTF-8" > LC_TIME="en_US.UTF-8" > LC_COLLATE="en_US.UTF-8" > LC_MONETARY="en_US.UTF-8" > LC_MESSAGES="en_US.UTF-8" > LC_PAPER="en

Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)

Phoenix Kiula wrote: On 16/08/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: --- Phoenix Kiula <[EMAIL PROTECTED]> wrote: On 16/08/07, Rodrigo De León <[EMAIL PROTECTED]> wrote: On Aug 15, 11:46 pm, [EMAIL PROTECTED] ("Phoenix Kiula") wrote: Appreciate any tips, b

Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)

--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > UPDATE T1 > >SET T1.title = T2.title > > FROM T2 > > WHERE T1.id = T2.id > >AND T1.title IS NULL; > > Thanks much RIchard, but neither of those work. For me table t1 has > over 6 million rows, and table t2 has about 600,000. In both of

Re: [GENERAL] UTF-8 encoding problem

Am Donnerstag, 16. August 2007 15:21 schrieb bhyuan: > Maybe SQL injection-like security issues will occour, > but I find that differend version of Postgresql get different result. That just shows that some versions are more broken than others. But there was a lot of thought put into the current

Re: [GENERAL] Accessing tables in other databases, through functions

On 8/16/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Probably an optimistic question - can a user with access to two > databases create a function in one to access tables in the other? Or > triggers? If this is related data that you just need logically separated, schemas might work for you too

Re: [GENERAL] how to get id of currently executed query?

On Thu, Aug 16, 2007 at 01:30:11PM +0200, hubert depesz lubaczewski wrote: > hi, > i need something to distinguish two separate calls for some select. > > i tried to use c functions GetCurrentTransactionId() and > GetCurrentCommandId(), > but there is a problem: > if i'll make plpgsql function, wh

Re: [GENERAL] Performance question

On Thu, Aug 16, 2007 at 08:52:02AM -0300, Marcelo de Moraes Serpa wrote: > Hello list, > > If I've got a trigger that calls a function each time there is a DELETE or > UPDATE opration on a table in my system, and in this function I retrieve > some boolean information from another table and based o

Re: [GENERAL] how to get id of currently executed query?

On Thu, Aug 16, 2007 at 09:14:24AM -0500, Decibel! wrote: > Well of course, if you're running it in a separate command. If you run > the function twice from one query I'd expect both to return the same. no. if i run one query with function i get sifferend commandid's inside the function. example:

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

"D. Dante Lorenso" <[EMAIL PROTECTED]> writes: > I need logic like "atomic test and set" or pop 1 item off the queue > atomically and tell me what that item was. > > In my situation, there are a dozen or so machines polling this queue > periodically looking for work to do. As more polling is occu

Re: [GENERAL] PostgreSQL clustering (shared disk)

Devrim GÜNDÜZ <[EMAIL PROTECTED]> writes: >> What I'm pondering here is that is the cluster able to keep the >> postmasters synchronized at all times so that the database won't get >> corrupted. > > Keep all the $PGDATA in the shared disk. That would minimize data loss > (Of course, there is stil

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

On Aug 15, 2007, at 9:21 PM, D. Dante Lorenso wrote: Erik Jones wrote: On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote: Erik Jones wrote: On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote: ...to ensure that only one server is processing the queue item, so inside PGSQL, use SELECT .

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Trevor Talbot" wrote: >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> >> But if you go to eBay, they always give you an accurate count. Even if >> >> the no. >> >> of items found is pretty large (example: ). >> > >> >And I'd bet money that they're using a fu

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Gregory Stark wrote: >"Rainer Bauer" <[EMAIL PROTECTED]> writes: > >> Anyway, what Phoenix is trying to say is that 2 queries are required: One to >> get the total count and one to get the tuples for the current page. I reckon >> it would help, if the query returning the result set could also repo

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Martijn van Oosterhout wrote: >On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote: >> Anyway, what Phoenix is trying to say is that 2 queries are required: One to >> get the total count and one to get the tuples for the current page. I reckon >> it would help, if the query returning the

Re: [GENERAL] how to get id of currently executed query?

hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > so - as you can see in one query, the value changes in plpgsql. and i need > some > id that will be unchanged within one end-user-supplied query. AFAIR, the only state that's guaranteed to work like that is statement_timestamp. Of course yo

Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll

Hi Tom :) Is this: libpq-fe.h: /** $PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.134 2006/10/04 00:30:13 momjian Exp $ **/ the latest version of libpq? - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "madhtr" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 1

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 16/08/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > Gregory Stark wrote: > > >"Rainer Bauer" <[EMAIL PROTECTED]> writes: > > > >> Anyway, what Phoenix is trying to say is that 2 queries are required: One > >> to > >> get the total count and one to get the tuples for the current page. I > >> re

[GENERAL] Function with Integer array parameter

Postgres Gurus, Please suggest me what is wrong with this function. This function tries to retrieve set of rows from description table based on set of ID fields passed as array. The error, I get is : ERROR: only one AS item needed for language "plpgsql" CREATE OR

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

In response to Rainer Bauer <[EMAIL PROTECTED]>: > "Trevor Talbot" wrote: > > >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > > > >> >> But if you go to eBay, they always give you an accurate count. Even if > >> >> the no. > >> >> of items found is pretty large (example:

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Rainer Bauer <[EMAIL PROTECTED]> writes: > My point is that whatever search criterias are involved and how many items > are found eBay always returns the *accurate* number of items found. And exactly how do you know that that's true? regards, tom lane ---

Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll

madhtr wrote: > Is this: > > libpq-fe.h: > > /** > $PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.134 2006/10/04 > 00:30:13 momjian Exp $ > **/ > > the latest version of libpq? This is what we ship with 8.2.4, yes. -- Alvaro Herrerahttp://www.advogato.org/perso

Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll

ok, ty :) - Original Message - From: "Alvaro Herrera" <[EMAIL PROTECTED]> To: "madhtr" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; Sent: Thursday, August 16, 2007 11:36 Subject: Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll madhtr wrote: Is this: libpq-fe.h

[GENERAL] I get a error msg when i try to connect to the DB

Hi I have installed PostGRESql 7.4.17 and when i try to connect to the db i get the msg psql: FATAL: no pg_hba.conf entry for host "???", user "sfmdb", database "LOGDB", SSL off My pg_hba.conf entry is as below # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD # D

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Aug 16, 5:19 am, [EMAIL PROTECTED] (Decibel!) wrote: > On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: > > "Scott Marlowe" wrote: > > But if you go to eBay, they always give you an accurate count. Even if the > > no. > > of items found is pretty large (example:

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Bill Moran wrote: >> Consider this query with multiple WHERE conditions: >> >>

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Tom Lane wrote: >Rainer Bauer <[EMAIL PROTECTED]> writes: >> My point is that whatever search criterias are involved and how many items >> are found eBay always returns the *accurate* number of items found. > >And exactly how do you know that that's true? 5 years experience with developing a bro

Re: [GENERAL] Function with Integer array parameter

On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote: > Postgres Gurus, > Please suggest me what is wrong with this > function. > This function tries to retrieve set of rows from description table based > on set of ID fields passed as array. > > The err

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > My point is that whatever search criterias are involved and how many items > are found eBay always returns the *accurate* number of items found. > > Before this drifts off: > * I do know *why* count(*) is slow using Postgres. > * I *think* tha

Re: [GENERAL] I get a error msg when i try to connect to the DB

On Thu, 2007-08-16 at 21:16 +0530, Rajaram J wrote: > Hi > > I have installed PostGRESql 7.4.17 and when i try to connect to the db > i get the msg > > psql: FATAL: no pg_hba.conf entry for host "???", user "sfmdb", > database "LOGDB", SSL off > You are allowing local, non TCP/IP connctions, an

Re: [GENERAL] Function with Integer array parameter

Decibel! wrote: On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote: Postgres Gurus, Please suggest me what is wrong with this function. This function tries to retrieve set of rows from description table based on set of ID fields passed as array.

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Trevor Talbot" wrote: >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> My point is that whatever search criterias are involved and how many items >> are found eBay always returns the *accurate* number of items found. >> >> Before this drifts off: >> * I do know *why* count(*) is slow us

Re: [GENERAL] Running a stored procedure via pgagent, need an examp le

Thank you, just had time to test this. I wrote a simple sproc that adds a row to a test table. I've created a one step job db = db the sproc is in kind = sql def = SELECT proc_test01_insertrow(); set to run on each minute of every hour every day, starting today 00:00:01 The job, step, and sc

Re: [GENERAL] Function with Integer array parameter

On Aug 16, 11:06 am, [EMAIL PROTECTED] (Ranjan Kumar Baisak) wrote: > Decibel! wrote: > > On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote: > > >> Postgres Gurus, > >> Please suggest me what is wrong with this > >> function. > >> This function tries to

Re: [GENERAL] how to get id of currently executed query?

On Thu, Aug 16, 2007 at 11:20:30AM -0400, Tom Lane wrote: > AFAIR, the only state that's guaranteed to work like that is > statement_timestamp. Of course you have to worry whether your machine > is fast enough to do more than one client interaction within whatever > the clock resolution is. i'll

[GENERAL] automatic rollback?

After using PostgreSQL 8.1 and earlier versions for years, I'm suddenly encountering a vexing problem. A certain kind of update and insert is frequently reverting to its original condition after a day or two -- the inserted record is gone and the updated one is back the way it was. There seems

Re: [GENERAL] automatic rollback?

On Aug 16, 2007, at 12:27 , John Gunther wrote: After using PostgreSQL 8.1 and earlier versions for years, I'm suddenly encountering a vexing problem. A certain kind of update and insert is frequently reverting to its original condition after a day or two -- the inserted record is gone and

Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll

Ok, i figured something out anyway ... hopefully it will be helpful to the group ... The following works on my WinXP pro machine most of the time. I have confirmed that I have the latest versions of everything. /// #pragma once #include #include #include #in

[GENERAL] How to use Integer array in where IN clause parameter

Hello, Can anybody suggest me how to use Integer array inside a function in where IN clause. In my function, I have used it like WHERE d_base.id in (|array_to_string| (integer_array, ',')) However it does not work. ANy suggestion?

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > "Trevor Talbot" wrote: > > >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > > > >> My point is that whatever search criterias are involved and how many items > >> are found eBay always returns the *accurate* number of items found. > >> >

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

Douglas McNaught wrote: "D. Dante Lorenso" <[EMAIL PROTECTED]> writes: How quickly after you update the row status are you comitting (and releasing locks)? I am calling a stored proc from PHP. Since I do not begin a transaction, I assume that my call is automatically committed immediately af

Re: [GENERAL] How to use Integer array in where IN clause parameter

On Thu, 2007-08-16 at 14:36 -0400, Ranjan Kumar Baisak wrote: > Hello, > Can anybody suggest me how to use Integer array inside a > function in where IN clause. > In my function, I have used it like > WHERE d_base.id in (array_to_string (integer_array, ',')) > > However it does not wo

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Scott Marlowe" wrote: >FYI, I went to the ebay page you posted, which listed something like >98011 pages, and asked for page 96000. It searched for about a minute >and timed out with the error message > >There was a problem executing your request. Please try again. > >Tried it again, twice, abou

Re: [GENERAL] Running a stored procedure via pgagent, need an examp le

novnov wrote: > Thank you, just had time to test this. > > I wrote a simple sproc that adds a row to a test table. I've created a one > step job > > db = db the sproc is in > kind = sql > def = SELECT proc_test01_insertrow(); > > set to run on each minute of every hour every day, starting toda

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

"D. Dante Lorenso" <[EMAIL PROTECTED]> writes: > Douglas McNaught wrote: >> How quickly after you update the row status are you comitting (and >> releasing locks)? > I am calling a stored proc from PHP. Since I do not begin a > transaction, I assume that my call is automatically committed > imm

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

Tom Lane wrote: "D. Dante Lorenso" <[EMAIL PROTECTED]> writes: Douglas McNaught wrote: How quickly after you update the row status are you comitting (and releasing locks)? I am calling a stored proc from PHP. Since I do not begin a transaction, I assume that my call is automatically committ

[GENERAL] Strange inconsistency with UPDATE

I am trying to force a column to have lowercase because Postgresql is case-sensitive in queries. For the time being I've made an expression index on lower(KEY). But I would like to have just lower case data and then drop this expression index. However, I see some inconsisent behavior from Postgres

Re: [GENERAL] Strange inconsistency with UPDATE

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/16/07 21:58, Phoenix Kiula wrote: > I am trying to force a column to have lowercase because Postgresql is > case-sensitive in queries. For the time being I've made an expression > index on lower(KEY). But I would like to have just lower case data

Re: [GENERAL] Strange inconsistency with UPDATE

On Aug 16, 2007, at 21:58 , Phoenix Kiula wrote: However, I see some inconsisent behavior from Postgresql. When I issue an UPDATE command , it shows me a duplicate violation (which could be correct) -- -# update TABLE set ACOLUMN = lower(ACOLUMN); ERROR: duplicate key violates unique

[GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Hi, I know this issue has been discussed at length before, but postgresql's behaviour of forcing a rollback when any error occurs is making life very difficult for me. We use Spring's transaction proxies, which are applied to methods in web controllers. In the backend code, if a runtime excep

Re: [GENERAL] Strange inconsistency with UPDATE

"Phoenix Kiula" <[EMAIL PROTECTED]> writes: > However, I see some inconsisent behavior from Postgresql. When I issue > an UPDATE command , it shows me a duplicate violation (which could be > correct) -- > -# update TABLE set ACOLUMN = lower(ACOLUMN); > ERROR: duplicate key violates unique

Re: [GENERAL] SELECT ... FOR UPDATE performance costs? alternatives?

"D. Dante Lorenso" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> "D. Dante Lorenso" <[EMAIL PROTECTED]> writes: >>> I am calling a stored proc from PHP. Since I do not begin a >>> transaction, I assume that my call is automatically committed >>> immediately after invocation. >> >> Have you a

Re: [GENERAL] Strange inconsistency with UPDATE

> I suspect you're not showing us the exact queries you're running. For > one, you can't have a table named TABLE (without quotes) in PostgreSQL. Of course. The data is a tad private, hence the simple table and column names represented in uppercase. > Perhaps something else you changed when cha

Re: [GENERAL] Strange inconsistency with UPDATE

On 17/08/07, Tom Lane <[EMAIL PROTECTED]> wrote: > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > However, I see some inconsisent behavior from Postgresql. When I issue > > an UPDATE command , it shows me a duplicate violation (which could be > > correct) -- > > > -# update TABLE set ACOLUMN =

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

"Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> writes: > Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us > keep going. How do you catch exceptions in these other dbs? The way to do it in Postgres is with the SAVEPOINT command. > For example, if something goes wrong, I can

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Gregory Stark wrote: "Tyson Lloyd Thwaites" <[EMAIL PROTECTED]> writes: Normally if we catch the exception, other dbs (Oracle, MSSQL) will let us keep going. How do you catch exceptions in these other dbs? plain java try/catch. In other dbs, if I am in a txn, and I run 3 statemen

Re: [GENERAL] "Out of memory" errors..

> > Vacuum memory usage is tuned by the "maintenance_work_mem" parameter. I > suggest you look at > http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html and > http://www.postgresql.org/docs/8.2/static/kernel-resources.html#AEN19338. Thanks Sander, I've read so many of these pag

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

> it is all so easy with other dbs, but with postgresql > it is a nightmare... the only solution I can see is to remove the > declarative transactions in Spring and start using manual transactions > blocks around everything that could possibly go wrong... just because of > a quirk in postgresql

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

True... apologies for any offence caused. Tweaking emotion levels. done :) The core problem is that we are maintaining a fairly mature app, and the behaviour in question means we effectively can't integrate with postgresql (which is a shame - I really *really* want to... we currently ship

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

On Fri, Aug 17, 2007 at 03:18:30PM +0930, Tyson Lloyd Thwaites wrote: > It seems like something that would be fairly easy to change... I don't > know. I read someone talking about putting automatic checkpoints on > every statement that goes through the jdbc driver to get around this > issue. Per

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

The auditing is an interesting question, to which I do not have an answer. Seems impossible to do in a transaction, by definition (ie not product specific). Thoughts? We do this with MSSQL. I have never given it a second thought until now. If anything goes wrong we send an audit event, usua

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Martijn van Oosterhout wrote: On Fri, Aug 17, 2007 at 03:18:30PM +0930, Tyson Lloyd Thwaites wrote: It seems like something that would be fairly easy to change... I don't know. I read someone talking about putting automatic checkpoints on every statement that goes through the jdbc driver to

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

Our app uses system state. We scan filesystems and record file information in a database. Here is one example: - insert 250 files - update some stats (MUST work even if insert fails) - update agent last-contact time (also must work so we know it's not awol) When last insert has finished, ser