[GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Михаил Кечинов
Hello. Here is a problem, which I can't understand. One week ago our database has crashed and after restore begins some problems. One of them: When I try to delete one row from database (for example): delete from document where numdoc = 901721617 I have this error: ERROR: tuple concurrently up

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Greg Stark
On Tue, Dec 29, 2009 at 9:41 AM, Михаил Кечинов wrote: > One week ago our database has crashed and after restore begins some > problems. What version? And how was this backup taken? It sounds like it might be an inconsistent backup. -- greg -- Sent via pgsql-general mailing list (pgsql-gener

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Михаил Кечинов
Version 8.4 So, it was not database crash - HDD died. We copied data to new HDD, droped some dead indexes (when vacuuming we has errors with indexes, so we drop it and recreate new indexes), made vacuum full. That's all. 2009/12/29 Greg Stark > On Tue, Dec 29, 2009 at 9:41 AM, Михаил Кечинов >

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread John R Pierce
Михаил Кечинов wrote: Version 8.4 So, it was not database crash - HDD died. We copied data to new HDD, droped some dead indexes (when vacuuming we has errors with indexes, so we drop it and recreate new indexes), made vacuum full. That's all. where did you copy this data from if the drive die

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Greg Stark
On Tue, Dec 29, 2009 at 9:41 AM, Михаил Кечинов wrote: > When I try to delete one row from database (for example): > delete from document where numdoc = 901721617 > I have this error: > ERROR: tuple concurrently updated > SQL state: XX000 > I know, that no one deleting this row at same time. > Wha

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Михаил Кечинов
Good. Now I have error: docs=# REINDEX TABLE document; ERROR: could not create unique index "pkey_document" DETAIL: Table contains duplicated values. So, I have primary key and I have some rows with similar "numdoc", but "numdoc" is primary key and must be unique. I can't drop pkey because the

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Osvaldo Kussama
2009/12/24 Israel Brewster : > This is sort of a PostgreSQL question/sort of a general SQL question, so I > apologize if this isn't the best place to ask. At any rate, I know in > PostgreSQL you can issue a command like 'SELECT "time"(timestamp_column) > from table_name' to get the time part of a t

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Adrian Klaver
On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote: > 2009/12/24 Israel Brewster : > > This is sort of a PostgreSQL question/sort of a general SQL question, so > > I apologize if this isn't the best place to ask. At any rate, I know in > > PostgreSQL you can issue a command like 'SELECT "

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Alvaro Herrera
Osvaldo Kussama escribió: > bdteste=# SELECT "time"(CURRENT_TIMESTAMP); > time > - > 10:55:20.679684 > (1 registro) > > bdteste=# SELECT $$time$$(CURRENT_TIMESTAMP); > ERRO: erro de sintaxe em ou próximo a "(" > LINE 1: SELECT $$time$$(CURRENT_TIMESTAMP); >

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Adrian Klaver
On Monday 28 December 2009 8:58:38 am Israel Brewster wrote: > On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: > > On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: > >> This is sort of a PostgreSQL question/sort of a general SQL question, > >> so I apologize if this isn't the best

Re: [GENERAL] DELETE ERROR: tuple concurrently updated

2009-12-29 Thread Михаил Кечинов
Ok, I did it. 1. Drop primary key CASCADE (foreign keys too). 2. Reindex table. 3. Delete duplicate rows. 4. Create primary key. 5. Create foreign keys. Thanks for help. 29 декабря 2009 г. 15:24 пользователь Михаил Кечинов написал: > Good. Now I have error: > > docs=# REINDEX TABLE document; >

[GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
Hi, I need to create a trigger on a table used by our sofware, the problem is, when I issue a "create trigger" on this table, it takes forever. It doesn't matter if I use pgAdmin, or psql. The only way to do it is by disconnecting all the instances of the program from the database, execute the

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Bill Moran
In response to "Leonardo M." Ramé : > Hi, I need to create a trigger on a table used by our sofware, the > problem is, when I issue a "create trigger" on this table, it takes > forever. It doesn't matter if I use pgAdmin, or psql. > > The only way to do it is by disconnecting all the instance

[GENERAL] Comparing 2 databases

2009-12-29 Thread akp geek
Hi all - I have postgres running on 2 servers. one production and one testing. What would be the best way to compare the 2 database, so find out the differences? Can you please advice? regards

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Osvaldo Kussama
2009/12/29 Adrian Klaver : > On Tuesday 29 December 2009 5:01:03 am Osvaldo Kussama wrote: >> 2009/12/24 Israel Brewster : >> > This is sort of a PostgreSQL question/sort of a general SQL question, so >> > I apologize if this isn't the best place to ask. At any rate, I know in >> > PostgreSQL you c

[GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Michael Fork
I have an index scan on a custom function that is returning a wildly incorrect row estimate that is throwing off the rest of the query planning. The result of the function is roughly unique - there are a handful with multiple entries - but the planner is estimating 227,745 rows. I re-ran ANALY

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Pavel Stehule
Hello 2009/12/29 Michael Fork : > I have an index scan on a custom function that is returning a wildly > incorrect row estimate that is throwing off the rest of the query planning.   > The result of the function is roughly unique - there are a handful with > multiple entries - but the planner is

Re: [GENERAL] cross-database time extract?

2009-12-29 Thread Israel Brewster
On Dec 29, 2009, at 5:41 AM, Adrian Klaver wrote: On Monday 28 December 2009 8:58:38 am Israel Brewster wrote: On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: This is sort of a PostgreSQL question/sort of a general SQL questi

[GENERAL] esql vs libpq

2009-12-29 Thread svcntk
Hello, I'm doing a comparison between ESQL interfaces and libpq. For libp I use pgbench, based on TCP-C, while for ESQL have a program that also follows the transactions carried out on TCP-C. However, the result with libpq is much better, with about 700 transactions per second, whereas with ESQL

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
El mar, 29-12-2009 a las 11:20 -0500, Bill Moran escribió: > In response to "Leonardo M." Ramé : > > > Hi, I need to create a trigger on a table used by our sofware, the > > problem is, when I issue a "create trigger" on this table, it takes > > forever. It doesn't matter if I use pgAdmin, or

Re: [GENERAL] pg_dump ERROR, usename "postgres" duplicated

2009-12-29 Thread Gastón Quiroga
Thank You Tom: I'll Try to make an update, but the 2 fields are equals row by row, how could I make a difference in the "WHERE" statement? Regards Gastón Quiroga Allytech S.A. Tom Lane wrote: =?ISO-8859-1?Q?Gast=F3n?= writes: It's Postgres version 8.0.8 Well, that's pretty a

Re: [GENERAL] pg_dump ERROR, usename "postgres" duplicated

2009-12-29 Thread Alvaro Herrera
Gastón Quiroga wrote: > Thank You Tom: >I'll Try to make an update, but the 2 fields are equals row by > row, how could I make a difference in the "WHERE" statement? Use the ctid hidden system field. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Re

Re: [GENERAL] pg_dump ERROR, usename "postgres" duplicated

2009-12-29 Thread Adrian Klaver
- "Gastón Quiroga" wrote: > Thank You Tom: > I'll Try to make an update, but the 2 fields are equals row by row, > how could I make a difference in the "WHERE" statement? > > Regards > > Gastón Quiroga > Allytech S.A. > > Tom Lane wrote: > > =?ISO-8859-1?Q?Gast=F3n?= writes: > > It'

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Michael Fork
Pavel, Thanks for the suggestion but unfortunately the planner estimate was not really affected: QUERY PLAN --- Index Scan using idx_event_card_id on event (cost=0.00

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Pavel Stehule
2009/12/29 Michael Fork : > Pavel, > > Thanks for the suggestion but unfortunately the planner estimate was not > really affected: any string estimation are not exact. you can use following dirty trick: http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Using_IMMUTABLE_functions_as_hints_f

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Thomas Kellerer
Michael Fork wrote on 29.12.2009 18:08: I have an index scan on a custom function that is returning a wildly incorrect row estimate that is throwing off the rest of the query planning. The result of the function is roughly unique - there are a handful with multiple entries - but the planner is e

Re: [GENERAL] Comparing 2 databases

2009-12-29 Thread Joshua Tolley
On Tue, Dec 29, 2009 at 11:43:58AM -0500, akp geek wrote: >Hi all - > > I have postgres running on 2 servers. one production and one >testing. What would be the best way to compare the 2 database, so find out >the differences? Can you please advice? > >regards

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
El mar, 29-12-2009 a las 14:48 -0300, Leonardo M. Ramé escribió: > El mar, 29-12-2009 a las 11:20 -0500, Bill Moran escribió: > > In response to "Leonardo M." Ramé : > > > > > Hi, I need to create a trigger on a table used by our sofware, the > > > problem is, when I issue a "create trigger" o

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Merlin Moncure
2009/12/29 Leonardo M. : > El mar, 29-12-2009 a las 14:48 -0300, Leonardo M. Ramé escribió: >> El mar, 29-12-2009 a las 11:20 -0500, Bill Moran escribió: >> > In response to "Leonardo M." Ramé : >> > >> > > Hi, I need to create a trigger on a table used by our sofware, the >> > > problem is, when I

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
El mar, 29-12-2009 a las 14:18 -0500, Merlin Moncure escribió: > >> > > > > Well, I'm trying to debug the problem, and found that when I do a simple > > "select * from table" from my app, then go to pgAdmin, and do "select * > > from pg_locks", it shows many locks (23 to be exact). > > Those locks

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Merlin Moncure
2009/12/29 Leonardo M. : > El mar, 29-12-2009 a las 14:18 -0500, Merlin Moncure escribió: >> >> >> > >> > Well, I'm trying to debug the problem, and found that when I do a simple >> > "select * from table" from my app, then go to pgAdmin, and do "select * >> > from pg_locks", it shows many locks (2

Re: [GENERAL] Comparing 2 databases

2009-12-29 Thread akp geek
thanks for the repsonse. I appreciate it. are there any limitations on using this one? Means that we have to the same user on both databases and same passwords. I have used the command following way check_postgres.pl --action=same_schema -H 172. -p 1550 --db=myProdDB --dbuser=prodUser -

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
El mar, 29-12-2009 a las 15:05 -0500, Merlin Moncure escribió: > > This solves the locking problem, but what happens to transactions? the > > app is still working in transaction mode, or just applying changes after > > every Insert/Update/Delete?. > > huh...the default transaction mode _is_ read c

[GENERAL] memory usage of group by select

2009-12-29 Thread Anthony
Hi all, I'm running a group by query on a table with over a billion rows and my memory usage is seemingly growing without bounds. Eventually the mem usage exceeds my physical memory and everything starts swapping. Here is what I gather to be the relevant info: My machine has 768 megs of ram. s

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Tom Lane
Michael Fork writes: > I have an index scan on a custom function that is returning a wildly > incorrect row estimate that is throwing off the rest of the query planning. > The result of the function is roughly unique - there are a handful with > multiple entries - but the planner is estimating

Re: [GENERAL] memory usage of group by select

2009-12-29 Thread Anthony
On Tue, Dec 29, 2009 at 3:41 PM, Anthony wrote: > I'm running a group by query on a table with over a billion rows and my > memory usage is seemingly growing without bounds. Eventually the mem usage > exceeds my physical memory and everything starts swapping. > I guess I didn't ask my question.

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Merlin Moncure
2009/12/29 Leonardo M. : > El mar, 29-12-2009 a las 15:05 -0500, Merlin Moncure escribió: >> > This solves the locking problem, but what happens to transactions? the >> > app is still working in transaction mode, or just applying changes after >> > every Insert/Update/Delete?. >> >> huh...the defau

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Leonardo M.
El mar, 29-12-2009 a las 15:44 -0500, Merlin Moncure escribió: > right. IIRC the zeos library has a transaction mode that controls if > commits are explicit or invoked via the library commit method. either > way, you you need to make sure that transactions are not left > open...this can lead (as y

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Merlin Moncure
2009/12/29 Leonardo M. : > El mar, 29-12-2009 a las 15:44 -0500, Merlin Moncure escribió: >> right. IIRC the zeos library has a transaction mode that controls if >> commits are explicit or invoked via the library commit method.  either >> way, you you need to make sure that transactions are not lef

Re: [GENERAL] DDL commands take forever

2009-12-29 Thread Raymond O'Donnell
On 29/12/2009 20:59, Leonardo M. Ramé wrote: > in " in transaction". The interesting thing is the app is doing > only Selects, without opening transactions. Everything in PG happens in a transaction, whether you open one explicitly or not. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@i

Re: [GENERAL] memory usage of group by select

2009-12-29 Thread Alvaro Herrera
Anthony wrote: > On Tue, Dec 29, 2009 at 3:41 PM, Anthony wrote: > > > I'm running a group by query on a table with over a billion rows and my > > memory usage is seemingly growing without bounds. Eventually the mem usage > > exceeds my physical memory and everything starts swapping. > > > > I

Re: [GENERAL] memory usage of group by select

2009-12-29 Thread Tom Lane
Alvaro Herrera writes: > It's expecting 85k distinct groups. If that's not accurate, then > HashAggregate would use more memory than expected. See if you can make > it work by setting enable_hashagg = off. > If that works, good -- the real solution is different. Maybe you need > to ANALYZE mor

Re: [GENERAL] memory usage of group by select

2009-12-29 Thread Anthony
On Tue, Dec 29, 2009 at 4:09 PM, Alvaro Herrera wrote: > It's expecting 85k distinct groups. If that's not accurate, then > HashAggregate would use more memory than expected. Great diagnosis. There are actually about 76 million distinct groups. > See if you can make it work by setting enable

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
Problem is we already have gcc-c++ but when we try to do a ./configure for the geos3.2.0 install it gives errors saying it is looking for g+ +, is it version of gcc-c++ is the highest upgrade and should have g++ included according to tech specialist. He is actually doing the install...trying to fin

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
Problem is we already have gcc-c++ but when we try to do a ./configure for the geos3.2.0 install it gives errors saying it is looking for g+ +, is it version of gcc-c++ is the highest upgrade and should have g++ included according to tech specialist. He is actually doing the install...trying to fin

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
Problem is we already have gcc-c++ but when we try to do a ./configure for the geos3.2.0 install it gives errors saying it is looking for g+ +, is it version of gcc-c++ is the highest upgrade and should have g++ included according to tech specialist. He is actually doing the install...trying to fin

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > Nick wrote: > > 'g++' or g++ says -bash g++: command not found > > > distro is red hat > > Assuming thats RHEL5, > >     yum install gcc-c++ > > if its RHEL4 or earlier, use up2date instead.   either of these will > require a RHN subs

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 29, 1:21 pm, Nick wrote: > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > > > > Nick wrote: > > > 'g++' or g++ says -bash g++: command not found > > > > distro is red hat > > > Assuming thats RHEL5, > > >     yum install gcc-c++ > > > if its RHEL4 or earlier, use up2date

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > Nick wrote: > > 'g++' or g++ says -bash g++: command not found > > > distro is red hat > > Assuming thats RHEL5, > >     yum install gcc-c++ > > if its RHEL4 or earlier, use up2date instead.   either of these will > require a RHN subs

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 29, 1:21 pm, Nick wrote: > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > > > > Nick wrote: > > > 'g++' or g++ says -bash g++: command not found > > > > distro is red hat > > > Assuming thats RHEL5, > > >     yum install gcc-c++ > > > if its RHEL4 or earlier, use up2date

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Joshua D. Drake
On Tue, 2009-12-29 at 13:21 -0800, Nick wrote: > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > Nick wrote: > > > 'g++' or g++ says -bash g++: command not found > > > > > distro is red hat > > > > Assuming thats RHEL5, > > > > yum install gcc-c++ > > > > if its RHEL4 or earl

Re: [GENERAL] Comparing 2 databases

2009-12-29 Thread Joshua Tolley
On Tue, Dec 29, 2009 at 03:21:18PM -0500, akp geek wrote: >thanks for the repsonse. I appreciate it. are there any limitations on >using this one? Means that we have to the same user on both databases and >same passwords. > >I have used the command following way > >

Re: [GENERAL] Comparing 2 databases

2009-12-29 Thread DM
Also check this out Very interesting – it can compare data between the DBs (tables/views). Check this out – http://www.zidsoft.com/ http://www.zidsoft.com/screenshots.html Thanks Deepak On Tue, Dec 29, 2009 at 4:37 PM, Joshua Tolley wrote: > On Tue, Dec 29, 2009 at 03:21:18PM -0500, akp gee

[GENERAL] DataBase Problem

2009-12-29 Thread Premila Devi
I am having problem as : Caused by: org.springframework.transaction.TransactionSystemException: Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: JDBC rollback failed at org.springframework.orm.hibernate3.HibernateTransactionManager.doRoll

Re: [GENERAL] DataBase Problem

2009-12-29 Thread Craig Ringer
On 30/12/2009 9:49 AM, Premila Devi wrote: Caused by: _java.sql.SQLException_: Couldn't perform the operation rollback: You can't perform any operations on this connection. It has been automatically closed by Proxool for some reason (see logs). "see logs" Look at your proxool logs and see why

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 29, 4:19 pm, j...@commandprompt.com ("Joshua D. Drake") wrote: > On Tue, 2009-12-29 at 13:21 -0800, Nick wrote: > > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > > Nick wrote: > > > > 'g++' or g++ says -bash g++: command not found > > > > > distro is red hat > > > > Assu

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 29, 4:19 pm, j...@commandprompt.com ("Joshua D. Drake") wrote: > On Tue, 2009-12-29 at 13:21 -0800, Nick wrote: > > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > > Nick wrote: > > > > 'g++' or g++ says -bash g++: command not found > > > > > distro is red hat > > > > Assu

Re: [GENERAL] Installation of Postgis/postgresql

2009-12-29 Thread Nick
On Dec 29, 4:19 pm, j...@commandprompt.com ("Joshua D. Drake") wrote: > On Tue, 2009-12-29 at 13:21 -0800, Nick wrote: > > On Dec 28, 8:22 pm, pie...@hogranch.com (John R Pierce) wrote: > > > Nick wrote: > > > > 'g++' or g++ says -bash g++: command not found > > > > > distro is red hat > > > > Assu

Re: [GENERAL] Idle processes chewing up CPU?

2009-12-29 Thread Tom Lane
"Brendan Hill" writes: > I think I've confirmed the fix. Using a dirty disconnect generator, I was > able to reliably recreate the problem within about 30-60 seconds. The > symptoms were the same as before, however it occurred around SSL_write > instead of SSL_read - I assume this was due to the a

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Michael Fork
springboard_v2=# SELECT version(); version -- PostgreSQL 8.3.7 on amd64-portbld-freebsd7.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD] (1 row) Yes,

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Michael Fork
I tried the trick below and the planner estimate was roughly the same: springboard_v2=# explain select * from trail.event where type='CREDIT' and lpad(CAST('57729970' AS text), 13, '0') = ANY(parsecardidfromreferencecode(reference_code)); QUERY PLAN ---

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Tom Lane
Michael Fork writes: >> Also, what happened to the type='CREDIT' condition in your query? Is >> that a partial index? > Yes, this is partial index. I should have included the index definition > earlier: Ah. The optimizer is ignoring the index stats on the grounds that they are not representa

Re: [GENERAL] Planner Row Estimate with Function

2009-12-29 Thread Michael Fork
That solved it. Thanks! Michael - Original Message From: Tom Lane To: Michael Fork Cc: pgsql-general@postgresql.org Sent: Tue, December 29, 2009 11:19:42 PM Subject: Re: [GENERAL] Planner Row Estimate with Function Michael Fork writes: >> Also, what happened to the type='CREDIT'