Thread: 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

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

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

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

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/

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

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

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>

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