Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted - Mailing list pgsql-general

From p.buongiovanni@net-international.com
Subject Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted
Date
Msg-id OF67F31E92.892E4968-ONC125773D.0026BE21-C125773D.0027D132@netspa.it
Whole thread Raw
List pgsql-general

Thank you very much for your answer below.

Just to keep you in the picture, the first problem has been solved with a FULL VACUUMING of the database.

With reference to the sequence, I experience this problem when I operate with pgAdmin III. It seems that the sequence START value is replaced every time I refresh a database object, i.e. the schema containing the mentioned sequence.
If I open a session with the Query tool and try to update the sequence with SETVAL function the returned value is correct. When I return back to pgAdmin III and look at the sequence object I see that the START value is different from the return value I obtained from the SETVAL function. This is a nonsense.
I trust now the problem is clearer than yesterday.

Thank you very much in advance for your feedback.

Kind regards

Piergiorgio Buongiovanni




Robert Haas <robertmhaas@gmail.com>

09/06/2010 05.04

Per
Greg Stark <gsstark@mit.edu>
CC
Piergiorgio Buongiovanni <piergiorgio.buongiovanni@netspa.it>, pgsql-bugs@postgresql.org
Oggetto
Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted





On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni
> <piergiorgio.buongiovanni@netspa.it> wrote:
>> I reused the previous command to re-set the sequence value to the right one,
>> but I see that the START value is now 59100. I reused the previous command
>> another time and the START value is now 30440.
>>
>> I think this is a bug. I have a lot of problems with this sequence.
>
> Sequences wouldn't directly affect retrieval times. But one way you
> could get both of these symptoms is by having an application which
> inserts many rows but aborts and rolls back the inserts without
> committing. Perhaps a large copy which is interrupted. That would fill
> the table with garbage dead records which could slow down retrieval
> depending on the access method and also increase the sequence value.

If this is what happened, CLUSTER on the table might be enough to fix
the problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


   
-- Disclaimer --
This message contains information which may be confidential. Unless you are the addressee (or authorized to receive for the addressee), 
you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received the message
in error, please contact the sender by e-mail and delete the message. Many  thanks.
Il presente messaggio contiene informazioni di carattere riservato. Qualora non foste il destinatario (o autorizzato dallo stesso al ricevimento)
non usate, copiate o rivelate il presente messaggio o le informazioni contenute. Se avete ricevuto il messaggio per errore, Vi preghiamo di
cancellarlo e avvisare il mittente tramite e-mail. Grazie.

pgsql-general by date:

Previous
From: Brian Modra
Date:
Subject: Re: Cognitive dissonance
Next
From: Torsten Zühlsdorff
Date:
Subject: Re: Cognitive dissonance