Problem creating index

On 08/26/2013 04:27 PM, Torello Querci wrote:

Create index statement that I use is:

CREATE INDEX dati_impianto_id_tipo_dato_id_data_misurazione_idx
ON dati
USING btree
(impianto_id , tipo_dato_id , data_misurazione DESC);

What are the data types of these columns?

--
Florian Weimer / Red Hat Product Security Team

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/8/26 Florian Weimer <fweimer@redhat.com>

On 08/26/2013 04:27 PM, Torello Querci wrote:

Create index statement that I use is:

CREATE INDEX dati_impianto_id_tipo_dato_id_**data_misurazione_idx
ON dati
USING btree
(impianto_id , tipo_dato_id , data_misurazione DESC);

What are the data types of these columns?

impianto_id -> integer
tipo_dato_id -> integer
data_misurazione -> date

The index was present in the database before I drop it since one about year
and half.

On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote:

ERROR: unexpected end of tape

Really strange, if I get it right something went wrong while sorting tuples.
Is it possible to test with an incremented work_mem value?

Luca

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/8/26 Luca Ferrari <fluca1978@infinito.it>

On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote:

ERROR: unexpected end of tape

Really strange, if I get it right something went wrong while sorting
tuples.
Is it possible to test with an incremented work_mem value?

Actually I use the default work_set value (1MB).

Of course it is possible increase work_set value to any values.
Now I'll try with 10MB (shared_buffers is set to 412MB).
If you think that 10MB is a very low value for a table with million of
records I can try to increase work_set value to higher value.

Best Regards, Torello

Torello Querci <tquerci@gmail.com> writes:

2013/8/26 Luca Ferrari <fluca1978@infinito.it>

Is it possible to test with an incremented work_mem value?

Actually I use the default work_set value (1MB).

maintenance_work_mem is what would be used for CREATE INDEX.

FWIW, though, the combination of this weird error and the fact that you
had a corrupt index to begin with makes me suspicious that there's some
low-level problem. You might be well advised to do some memory testing
on that machine, for example.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Torello Querci escribi�:

2013/8/26 Luca Ferrari <fluca1978@infinito.it>

On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote:

ERROR: unexpected end of tape

Really strange, if I get it right something went wrong while sorting
tuples.
Is it possible to test with an incremented work_mem value?

Actually I use the default work_set value (1MB).

Of course it is possible increase work_set value to any values.
Now I'll try with 10MB (shared_buffers is set to 412MB).
If you think that 10MB is a very low value for a table with million of
records I can try to increase work_set value to higher value.

Note that index creation uses maintenance_work_mem to limit memory used,
not work_mem.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/8/26 Tom Lane <tgl@sss.pgh.pa.us>

Torello Querci <tquerci@gmail.com> writes:

2013/8/26 Luca Ferrari <fluca1978@infinito.it>

Is it possible to test with an incremented work_mem value?

Actually I use the default work_set value (1MB).

maintenance_work_mem is what would be used for CREATE INDEX.

Ok .... thanks

FWIW, though, the combination of this weird error and the fact that you
had a corrupt index to begin with makes me suspicious that there's some
low-level problem. You might be well advised to do some memory testing
on that machine, for example.

I check for ecc memory but unfortunally the machine use non ecc memory.

This machine is installed on a remote site so I should to try to use e
memory tester in normal linux shell, so I can't use memtest at boot level.

In this moment I get this error while executing the restore of the big
table in a different database on the same machine:

psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert unexpected
message
psql:dump_ess_2013_08_26.sql:271177424: connection to server was lost

I was connected with psql -h localhost.

Any other suggestion?

Best Regards

Ok,

now create index is finished using maintenance_work_mem=100MB.

Thanks to all.

I suppose that an error message more clear can help.

Best Regards, Torello

2013/8/26 Torello Querci <tquerci@gmail.com>

On 08/26/2013 06:37 PM, Torello Querci wrote:

In this moment I get this error while executing the restore of the big
table in a different database on the same machine:

psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert
unexpected message
psql:dump_ess_2013_08_26.sql:271177424: connection to server was lost

Hello

This error has probably nothing to do with your index problem.

How big is the restore file you are restoring? More than 512MB?

We had a similar error in our RHEL6 servers and the problem was that the
server could not SSL renegotiate. Some systems has SSL libraries that
are incapable of SSL renegotiation as a safeguard against (old) bugs in
the protocol.

If you are doing a restore of a database/table bigger than 512MB and
your system can not do SSL renegotiation, you have to change the
parameter ssl_renegotiation_limit to 0 in your postgresql.conf and
restart postgreSQL.

regards,
--
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com> wrote:

Ok,

now create index is finished using maintenance_work_mem=100MB.

Thanks to all.

I suppose that an error message more clear can help.

Unfortunately, since no one knows what the real problem is, we can't
make the message more clear. Something that is never supposed to
happen has happened.

One thing you could do is set log_error_verbosity to verbose.

It seems like the most likely cause is flaky hardware, either memory
or hard-drive. In which case, your database is in serious danger of
irrecoverable corruption.

Is it reproducible that if you lower the maintenance_work_mem you get
the error again, and if you raise it the error does not occur?

Cheers,

Jeff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes,

the table is bigger than 512MB.
Thank got your tips.

Best Regard, Torello

2013/8/26 Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>

2013/8/26 Jeff Janes <jeff.janes@gmail.com>

On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com>
wrote:

Ok,

now create index is finished using maintenance_work_mem=100MB.

Thanks to all.

I suppose that an error message more clear can help.

Unfortunately, since no one knows what the real problem is, we can't
make the message more clear. Something that is never supposed to
happen has happened.

One thing you could do is set log_error_verbosity to verbose.

It seems like the most likely cause is flaky hardware, either memory
or hard-drive. In which case, your database is in serious danger of
irrecoverable corruption.

Is it reproducible that if you lower the maintenance_work_mem you get
the error again, and if you raise it the error does not occur?

I'll try to restore the database on the same hw but different DB using

differente maintenance_work_mem end verbosity and I'll posted the result
here, if can help to improve the error message.

Cheers, Torello

Interesting .....

while trying to restore the database on the same machine as different
database I get this error message:

ERROR: date/time field value out of range: "20016009:50:37.927936"

Since I get this data from a database dump obtained with "pg_dump" on the
same hardware I suppose that can to be two possibility:

- postgresql bug somewhere
- hardware problem that caused data corruption

Since the dump file is 11G is not so easy to handle ....
I think that this is not related with create index problem since this field
is not used by this index and increase maintenance memory had worked.

I'll fix it and go ahead in maintenance_work_mem test for index creating.

Best Regards

2013/8/27 Torello Querci <tquerci@gmail.com>

Same version of DB for dump & restore? If not, was the dump done via the pg_dump from the newer version. If not, please do that.

--
Dan Langille
http://langille.org/

On Aug 28, 2013, at 2:56 AM, Torello Querci <tquerci@gmail.com> wrote:

2013/8/28 Dan Langille <dan.langille@gmail.com>

Same version of DB for dump & restore? If not, was the dump done via the
pg_dump from the newer version. If not, please do that.

I'm using the same version. I make this test on the same machine.
Moreover I try to remove this line using pgadmin and I get the same error
(this field is part of primary key).
To remove this line I need to not use primary key but give a where
condition that return only this tuple.

Again, is very strange that this data is in the database .... moreover this
data came from a import procedure and this data is not present in the
source import file.
Really, I think that I get some kind of data corruption

Best Regards

On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci <tquerci@gmail.com> wrote:

Again, is very strange that this data is in the database .... moreover this
data came from a import procedure and this data is not present in the source
import file.
Really, I think that I get some kind of data corruption

I'm sure you got some kind of data corruption because the date is
invalid and it was in a primary key (if I get it right).

Luca

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/8/28 Luca Ferrari <fluca1978@infinito.it>

On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci <tquerci@gmail.com> wrote:

Again, is very strange that this data is in the database .... moreover

this

data came from a import procedure and this data is not present in the

source

import file.
Really, I think that I get some kind of data corruption

I'm sure you got some kind of data corruption because the date is
invalid and it was in a primary key (if I get it right).

You get it right.

At this point I think that a full server check is needed ....