Re: [GENERAL] [BUGS] BUG #5492: Query performs slowly and sequence corrupted
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 robertmh...@gmail.com 09/06/2010 05.04 Per Greg Stark gsst...@mit.edu CC Piergiorgio Buongiovanni piergiorgio.buongiova...@netspa.it, pgsql-b...@postgresql.org Oggetto Re: [BUGS] BUG #5492: Query performs slowly and sequence corrupted On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark gsst...@mit.edu wrote: On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni piergiorgio.buongiova...@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.
Re: [GENERAL] BUG #5492: Sequence corruption
Hi Greg,
first of all thank you for your answer below.
The first problem of the query performing slowly has been solved with a
FULL VACUUMING of the database.
Now the problem of the sequence remains.
This e-mail is sent to pgsql-general@postgresql.org e-mail address too, as
per your suggestion.
WIth reference to the bad sequence, if I use pgAdmin III and go to
subject_isid_seq object of the business schema I see the following:
-- Sequence: business.subject_isid_seq
-- DROP SEQUENCE business.subject_isid_seq;
CREATE SEQUENCE business.subject_isid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 47948992
CACHE 10;
ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;
My problem is that the START value is higher than the MAX value (100) used
in the table. I tried to change the START value with SETVAL function many
times without success.
With the above situation if I now try to change the value esecuting the
following code:
SELECT SETVAL('business.subject_isid_seq', MAX(iSId)) FROM
business.Subject;
I obtain the return value 100, as expected.
If I now use pgAdmin III to look at the sequence data I see the following
situation:
CREATE SEQUENCE business.subject_isid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1810
CACHE 10;
ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;
Every time I try to change the value to 100 I see the value is different.
When I execute the SETVAL function I am connected as netdw_owner user,
that's the business schema owner. So I believe it's not a problem of
object ownership.
I have other sequences in my database but only this one has this problem.
Could anybody help me in finding a solution?
Thank you very much in advance.
Piergiorgio Buongiovanni
Greg Stark gsst...@mit.edu
07/06/2010 23.33
Per
Piergiorgio Buongiovanni piergiorgio.buongiova...@netspa.it
CC
pgsql-b...@postgresql.org
Oggetto
Re: BUG #5492: Query performs slowly and sequence corrupted
On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni
piergiorgio.buongiova...@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.
You'll need to provide a lot more data before people would be able to
help you. I suggest you post the results of explain analyze select
... as well as some details of what previous operations you've done
on this database to pgsql-gene...@postgresql.org. pgsql-bugs is not
really appropriate unless you're reporting a specific problem with
postgresql in general, not an operational problem with your database.
--
greg
-- 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.
