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
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
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, Михаил Кечинов
>
Михаил Кечинов 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
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
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
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
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 "
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);
>
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
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;
>
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
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
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
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
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
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
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
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
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
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
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
- "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'
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
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
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
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
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
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
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
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
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 -
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
>
>
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
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
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
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
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
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
"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
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,
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
---
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
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'
63 matches
Mail list logo