BUG #5492: Query performs slowly and sequence corrupted

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.

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-general@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

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 <gsstark@mit.edu>
07/06/2010 23.33

Per
Piergiorgio Buongiovanni <piergiorgio.buongiovanni@netspa.it>
CC
pgsql-bugs@postgresql.org
Oggetto
Re: BUG #5492: Query performs slowly and sequence corrupted

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.

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-general@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.

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

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.