Обсуждение: tuple concurrently updated

Поиск
Список
Период
Сортировка

tuple concurrently updated

От
wambacher@posteo.de
Дата:
Hi,

getting this error:

DELETE FROM planet_osm_line WHERE osm_id = -166570;
FEHLER:  tuple concurrently updated

postgresql 10.1

any idea what i can do? of course i did a restart first.

regards

walter



Re: tuple concurrently updated

От
066ce286@free.fr
Дата:
You may have others transactions opened modifying this line of table planet_osm_line I presume.

----- Mail original -----
De: wambacher@posteo.de
À: pgsql-admin@lists.postgresql.org
Envoyé: Mardi 28 Août 2018 12:36:03
Objet: tuple concurrently updated

Hi,

getting this error:

DELETE FROM planet_osm_line WHERE osm_id = -166570;
FEHLER:  tuple concurrently updated

postgresql 10.1

any idea what i can do? of course i did a restart first.

regards

walter




Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago and the db "recovered".


Am 28.08.2018 um 13:00 schrieb 066ce286@free.fr:
> You may have others transactions opened modifying this line of table planet_osm_line I presume.
>



Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago.


Am 28.08.2018 um 13:00 schrieb 066ce286@free.fr:
> You may have others transactions opened modifying this line of table planet_osm_line I presume.



Re: tuple concurrently updated

От
066ce286@free.fr
Дата:
Maybe it'll be wise to dump/restore ?

----- Mail original -----
De: wambacher@posteo.de
À: 066ce286@free.fr
Cc: pgsql-admin@lists.postgresql.org
Envoyé: Mardi 28 Août 2018 13:08:59
Objet: Re: tuple concurrently updated

Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago and the db "recovered".


Am 28.08.2018 um 13:00 schrieb 066ce286@free.fr:
> You may have others transactions opened modifying this line of table planet_osm_line I presume.
>



Re: tuple concurrently updated

От
Flavio Henrique Araque Gurgel
Дата:


Em ter, 28 de ago de 2018 às 13:15, <wambacher@posteo.de> escreveu:
Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago.

Look if you have prepared transactions on the pg_prepared_transactions view.
If you make use of JDBC it's possible that you use it without knowing and it survives Postgres restarts.
If you have any, drop them.

Flavio Gurgel

Re: tuple concurrently updated

От
Flavio Henrique Araque Gurgel
Дата:


Em ter, 28 de ago de 2018 às 13:18, Flavio Henrique Araque Gurgel <fhagur@gmail.com> escreveu:
Em ter, 28 de ago de 2018 às 13:15, <wambacher@posteo.de> escreveu:
Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago.

Look if you have prepared transactions on the pg_prepared_transactions view.
If you make use of JDBC it's possible that you use it without knowing and it survives Postgres restarts.
If you have any, drop them.


My mistake, it's pg_prepared_xacts the correct name.
Flavio Gurgel

Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:

 pg_prepared_transactions does not exist, or i don't know how to list that

Am 28.08.2018 um 13:18 schrieb Flavio Henrique Araque Gurgel:


Em ter, 28 de ago de 2018 às 13:15, <wambacher@posteo.de> escreveu:
Hi,

there is only one process updating my database. and that is exactly the
job which is getting the error.

all other jobs are read/only and i even stoppend those.

but there has been a crash some hours ago.

Look if you have prepared transactions on the pg_prepared_transactions view.
If you make use of JDBC it's possible that you use it without knowing and it survives Postgres restarts.
If you have any, drop them.

Flavio Gurgel

Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:

Am 28.08.2018 um 13:15 schrieb 066ce286@free.fr:
> Maybe it'll be wise to dump/restore ?
>
yes, that may be one option. pg_dump is running right now.


Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
My mistake, it's pg_prepared_xacts the correct name.
Flavio Gurgel
pg_prepared_xacts is empty

regards
walter

Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
pg_dump not completed:

pg_dump: Ausgabe des Inhalts der Tabelle »planet_osm_line«
fehlgeschlagen: PQgetResult() fehlgeschlagen.
pg_dump: Fehlermeldung vom Server: FEHLER:  unexpected chunk number 0
(expected 1) for toast value 1261719035 in pg_toast_1340113

anything else i can do?

regards
walter
Am 28.08.2018 um 13:25 schrieb wambacher@posteo.de:
>
> Am 28.08.2018 um 13:15 schrieb 066ce286@free.fr:
>> Maybe it'll be wise to dump/restore ?
>>
> yes, that may be one option. pg_dump is running right now.
>


Re: tuple concurrently updated

От
066ce286@free.fr
Дата:
BTW you have data corruption.

So forget your problem about concurrent updates ; that's not the clue.

Your new question should be how to recover corrupted table.

Sorry, I've no skill for that problem other that "how ran your backups/archivelogs ?"



----- Mail original -----
De: wambacher@posteo.de
À: pgsql-admin@lists.postgresql.org
Envoyé: Mardi 28 Août 2018 15:04:14
Objet: Re: tuple concurrently updated

pg_dump not completed:

pg_dump: Ausgabe des Inhalts der Tabelle »planet_osm_line«
fehlgeschlagen: PQgetResult() fehlgeschlagen.
pg_dump: Fehlermeldung vom Server: FEHLER:  unexpected chunk number 0
(expected 1) for toast value 1261719035 in pg_toast_1340113

anything else i can do?

regards
walter
Am 28.08.2018 um 13:25 schrieb wambacher@posteo.de:
>
> Am 28.08.2018 um 13:15 schrieb 066ce286@free.fr:
>> Maybe it'll be wise to dump/restore ?
>>
> yes, that may be one option. pg_dump is running right now.
>



Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
because it's an OpenStreetMap Full planet database, which is huge (~ 2
TB), i don't have many backups. the last is from 20180608

ok, i can restore that and reload the incremential updates or reload
full data starting from raw data. both will need some days.

if i could get rid of the bad data-record, it's much easier to recover that.

Running a vacuum or a vacuum full, is my last try.

Regards
walter


Am 28.08.2018 um 15:10 schrieb 066ce286@free.fr:
> BTW you have data corruption.
>
> So forget your problem about concurrent updates ; that's not the clue.
>
> Your new question should be how to recover corrupted table.
>
> Sorry, I've no skill for that problem other that "how ran your backups/archivelogs ?"
>




Re: tuple concurrently updated

От
066ce286@free.fr
Дата:
I don't think a vacuum can handle what a pg_dump can't. But I'm maybe wrong.

----- Mail original -----
De: wambacher@posteo.de
À: pgsql-admin@lists.postgresql.org
Envoyé: Mardi 28 Août 2018 15:19:28
Objet: Re: tuple concurrently updated

because it's an OpenStreetMap Full planet database, which is huge (~ 2
TB), i don't have many backups. the last is from 20180608

ok, i can restore that and reload the incremential updates or reload
full data starting from raw data. both will need some days.

if i could get rid of the bad data-record, it's much easier to recover that.

Running a vacuum or a vacuum full, is my last try.

Regards
walter


Am 28.08.2018 um 15:10 schrieb 066ce286@free.fr:
> BTW you have data corruption.
>
> So forget your problem about concurrent updates ; that's not the clue.
>
> Your new question should be how to recover corrupted table.
>
> Sorry, I've no skill for that problem other that "how ran your backups/archivelogs ?"
>





Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
so do i :( but i will try it.

in parall the preperations for a full import are running. e.g the
download will need 2 hours.

walter


Am 28.08.2018 um 15:34 schrieb 066ce286@free.fr:
> I don't think a vacuum can handle what a pg_dump can't. But I'm maybe wrong.
>



Re: tuple concurrently updated

От
pavan95
Дата:
Hi Walter,

Why can't you go with a postgres service restart in order to get rid of this
error?

>DELETE FROM planet_osm_line WHERE osm_id = -166570; 
>FEHLER:  tuple concurrently updated 

Concurrent transactions can update or delete one or more of those rows
before DELETE can lock the rows (at least with the default isolation level
READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR
UPDATE (or other options)

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: tuple concurrently updated

От
066ce286@free.fr
Дата:
AAMOF, what are your fsync/wal_sync_method parameters, and what is your storage hardware ?

----- Mail original -----
De: wambacher@posteo.de
À: pgsql-admin@lists.postgresql.org
Envoyé: Mardi 28 Août 2018 15:38:37
Objet: Re: tuple concurrently updated

so do i :( but i will try it.

in parall the preperations for a full import are running. e.g the
download will need 2 hours.

walter


Am 28.08.2018 um 15:34 schrieb 066ce286@free.fr:
> I don't think a vacuum can handle what a pg_dump can't. But I'm maybe wrong.
>




Re: tuple concurrently updated

От
Alvaro Herrera
Дата:
On 2018-Aug-28, wambacher@posteo.de wrote:

> getting this error:
> 
> DELETE FROM planet_osm_line WHERE osm_id = -166570;
> FEHLER:  tuple concurrently updated
> 
> postgresql 10.1
> 
> any idea what i can do? of course i did a restart first.

What is the definition of the table?  It sounds like there's some GIS
datatype -- Right?  Are there any triggers or some other fun fact about
this delete?  What is the isolation level in effect when the delete
runs?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: tuple concurrently updated

От
Walter Nordmann
Дата:
updates are done using a program, that is without my control: osm2pgsql.
this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.

regards

walter



Am 28.08.2018 um 15:45 schrieb pavan95:
> Hi Walter,
>
> Why can't you go with a postgres service restart in order to get rid of this
> error?
>
>> DELETE FROM planet_osm_line WHERE osm_id = -166570; 
>> FEHLER:  tuple concurrently updated 
> Concurrent transactions can update or delete one or more of those rows
> before DELETE can lock the rows (at least with the default isolation level
> READ COMMITTED). This would result in your error message.
>
> To defend against this race condition, lock the rows in the SELECT with FOR
> UPDATE (or other options)
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>



Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
updates are done using a program, that is without my control: osm2pgsql.

this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.

regards

walter
Am 28.08.2018 um 15:45 schrieb pavan95:
Hi Walter,

Why can't you go with a postgres service restart in order to get rid of this
error?

DELETE FROM planet_osm_line WHERE osm_id = -166570; 
FEHLER:  tuple concurrently updated 
Concurrent transactions can update or delete one or more of those rows
before DELETE can lock the rows (at least with the default isolation level
READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR
UPDATE (or other options)

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
updates are done using a program, that is without my control: osm2pgsql.

this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.

regards

walter

Am 28.08.2018 um 15:51 schrieb Alvaro Herrera:
On 2018-Aug-28, wambacher@posteo.de wrote:

getting this error:

DELETE FROM planet_osm_line WHERE osm_id = -166570;
FEHLER:  tuple concurrently updated

postgresql 10.1

any idea what i can do? of course i did a restart first.
What is the definition of the table?  It sounds like there's some GIS
datatype -- Right?  Are there any triggers or some other fun fact about
this delete?  What is the isolation level in effect when the delete
runs?


Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:
see postgresql.auto.conf:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
autovacuum = 'true'
shared_buffers = '16GB'
max_stack_depth = '4MB'
autovacuum_max_workers = '4'
work_mem = '1GB'
listen_addresses = '*'
max_connections = '300'
max_worker_processes = '8'
max_parallel_workers_per_gather = '2'
max_wal_size = '4GB'
max_wal_senders = '0'
max_locks_per_transaction = '128'
ssl = 'on'
log_min_messages = 'error'
log_connections = 'off'
log_hostname = 'off'
maintenance_work_mem = '4GB'
log_min_duration_statement = '30000'
checkpoint_completion_target = '0.85'
track_io_timing = 'on'
effective_cache_size = '2GB'
log_checkpoints = 'on'
wal_level = 'minimal'
wal_compression = 'on'
checkpoint_timeout = '5min'

and the storage hardware is on hdd


Am 28.08.2018 um 15:46 schrieb 066ce286@free.fr:
> AAMOF, what are your fsync/wal_sync_method parameters, and what is your storage hardware ?
>



Re: tuple concurrently updated

От
Alvaro Herrera
Дата:
On 2018-Aug-28, wambacher@posteo.de wrote:

> updates are done using a program, that is without my control: osm2pgsql.
> 
> this program does everthing which is necessary including locking. and
> it't running for years on many, many openstreetmap servers.
> 
> because there was a crash before, it must be a data corruption problem,
> not programming error.

It's curious that you reply to my email and not respond to a single of
the questions I posed, and instead answered questions I didn't ask.
Perhaps you thought I asked them because I was bored?

> > What is the definition of the table?  It sounds like there's some GIS
> > datatype -- Right?  Are there any triggers or some other fun fact about
> > this delete?  What is the isolation level in effect when the delete
> > runs?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:

no, i respect your questions, but i can't answer them. because i don't know in detail what osm2pgsql is doing.

planet_osm_line it postgis

see:

                                     Tabelle »public.planet_osm_line«
             Spalte              |            Typ            | Sortierfolge | NULL erlaubt? | Vorgabewert
---------------------------------+---------------------------+--------------+---------------+-------------
 osm_id                          | bigint                    |              |               |
 access                          | text                      |              |               |
 addr:housename                  | text                      |              |               |
 addr:housenumber                | text                      |              |               |
 addr:interpolation              | text                      |              |               |
 admin_level                     | text                      |              |               |
 aerialway                       | text                      |              |               |
 aeroway                         | text                      |              |               |
 amenity                         | text                      |              |               |
 area                            | text                      |              |               |
 barrier                         | text                      |              |               |
 bicycle                         | text                      |              |               |
 brand                           | text                      |              |               |
 bridge                          | text                      |              |               |
 boundary                        | text                      |              |               |
 building                        | text                      |              |               |
 construction                    | text                      |              |               |
 covered                         | text                      |              |               |
 culvert                         | text                      |              |               |
 cutting                         | text                      |              |               |
 denomination                    | text                      |              |               |
 disused                         | text                      |              |               |
 embankment                      | text                      |              |               |
 foot                            | text                      |              |               |
 generator:source                | text                      |              |               |
 harbour                         | text                      |              |               |
 highway                         | text                      |              |               |
 historic                        | text                      |              |               |
 horse                           | text                      |              |               |
 intermittent                    | text                      |              |               |
 junction                        | text                      |              |               |
 landuse                         | text                      |              |               |
 layer                           | text                      |              |               |
 leisure                         | text                      |              |               |
 lock                            | text                      |              |               |
 man_made                        | text                      |              |               |
 military                        | text                      |              |               |
 motorcar                        | text                      |              |               |
 name                            | text                      |              |               |
 natural                         | text                      |              |               |
 office                          | text                      |              |               |
 oneway                          | text                      |              |               |
 operator                        | text                      |              |               |
 place                           | text                      |              |               |
 population                      | text                      |              |               |
 power                           | text                      |              |               |
 power_source                    | text                      |              |               |
 public_transport                | text                      |              |               |
 railway                         | text                      |              |               |
 ref                             | text                      |              |               |
 religion                        | text                      |              |               |
 route                           | text                      |              |               |
 service                         | text                      |              |               |
 shop                            | text                      |              |               |
 sport                           | text                      |              |               |
 surface                         | text                      |              |               |
 toll                            | text                      |              |               |
 tourism                         | text                      |              |               |
 tower:type                      | text                      |              |               |
 tracktype                       | text                      |              |               |
 tunnel                          | text                      |              |               |
 water                           | text                      |              |               |
 waterway                        | text                      |              |               |
 wetland                         | text                      |              |               |
 width                           | text                      |              |               |
 wood                            | text                      |              |               |
 z_order                         | integer                   |              |               |
 way_area                        | real                      |              |               |
 addr:postcode                   | text                      |              |               |
 de:amtlicher_gemeindeschluessel | text                      |              |               |
 de:regionalschluessel           | text                      |              |               |
 ISO3166-1:alpha3                | text                      |              |               |
 maxspeed                        | text                      |              |               |
 maxspeed:forward                | text                      |              |               |
 maxspeed:backward               | text                      |              |               |
 name:de                         | text                      |              |               |
 note                            | text                      |              |               |
 postal_code                     | text                      |              |               |
 tags                            | hstore                    |              |               |
 way                             | geometry(LineString,4326) |              |               |
Indexe:
    "idx_planet_osm_line_highway" btree (highway)
    "planet_osm_line_index" gist (way), Tablespace »planet3_is1«
    "planet_osm_line_pkey" btree (osm_id), Tablespace »planet3_is1«
    "planet_osm_line_tags_index" gin (tags), Tablespace »planet3_is1«
Tablespace: »planet3_ts1«
Am 28.08.2018 um 16:33 schrieb Alvaro Herrera:
On 2018-Aug-28, wambacher@posteo.de wrote:

updates are done using a program, that is without my control: osm2pgsql.

this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.
It's curious that you reply to my email and not respond to a single of
the questions I posed, and instead answered questions I didn't ask.
Perhaps you thought I asked them because I was bored?

What is the definition of the table?  It sounds like there's some GIS
datatype -- Right?  Are there any triggers or some other fun fact about
this delete?  What is the isolation level in effect when the delete
runs?

Re: tuple concurrently updated

От
Alvaro Herrera
Дата:
On 2018-Aug-28, wambacher@posteo.de wrote:

> no, i respect your questions, but i can't answer them. because i don't
> know in detail what osm2pgsql is doing.
> 
> planet_osm_line it postgis

> >              Spalte              |            Typ            |
> > Sortierfolge | NULL erlaubt? | Vorgabewert
> > ---------------------------------+---------------------------+--------------+---------------+-------------

> >  way                             | geometry(LineString,4326)
> > |              |               |
> > Indexe:
> >     "idx_planet_osm_line_highway" btree (highway)
> >     "planet_osm_line_index" gist (way), Tablespace »planet3_is1«
> >     "planet_osm_line_pkey" btree (osm_id), Tablespace »planet3_is1«
> >     "planet_osm_line_tags_index" gin (tags), Tablespace »planet3_is1«
> > Tablespace: »planet3_ts1«

I bet that something is happening to the special Postgis catalog table
for the geometry type when you run this update.  Maybe, say, if you have
two such updates in a transaction, things break.  The innards of
geometry_in are, um, fascinating (though I can't tell for sure whether
it tries to modify anything).

Also, I wouldn't put my hands to the fire for the gist support for it.

I further bet you'd have more luck asking a Postgis mailing list rather
than pgsql-admin.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: tuple concurrently updated

От
John P
Дата:
Please take me off this list

On Tue, Aug 28, 2018, 11:38 AM Walter Nordmann <walter.nordmann@posteo.de> wrote:
updates are done using a program, that is without my control: osm2pgsql.
this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.

regards

walter



Am 28.08.2018 um 15:45 schrieb pavan95:
> Hi Walter,
>
> Why can't you go with a postgres service restart in order to get rid of this
> error?
>
>> DELETE FROM planet_osm_line WHERE osm_id = -166570;
>> FEHLER:  tuple concurrently updated
> Concurrent transactions can update or delete one or more of those rows
> before DELETE can lock the rows (at least with the default isolation level
> READ COMMITTED). This would result in your error message.
>
> To defend against this race condition, lock the rows in the SELECT with FOR
> UPDATE (or other options)
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>


Re: tuple concurrently updated

От
Evan Bauer
Дата:
Walter,

I agree with your diagnosis of a database corruption, the error message from your attempt to dump essentially provides it:
pg_dump: Fehlermeldung vom Server: FEHLER:  unexpected chunk number 0
(expected 1) for toast value 1261719035 in pg_toast_1340113


With all of the TEXT columns in the table, a corrupted TOAST pointer or (more likely) TOAST table page looks to be the culprit.

I agree that a rebuild seems to be the best (though lengthy) approach.

Your post here was my first introduction to Open Street Map — a fascinating project — thanks.

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 09:58, Walter Nordmann <walter.nordmann@posteo.de> wrote:

updates are done using a program, that is without my control: osm2pgsql.
this program does everthing which is necessary including locking. and
it't running for years on many, many openstreetmap servers.

because there was a crash before, it must be a data corruption problem,
not programming error.

regards

walter



Am 28.08.2018 um 15:45 schrieb pavan95:
Hi Walter,

Why can't you go with a postgres service restart in order to get rid of this
error?

DELETE FROM planet_osm_line WHERE osm_id = -166570;
FEHLER:  tuple concurrently updated
Concurrent transactions can update or delete one or more of those rows
before DELETE can lock the rows (at least with the default isolation level
READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR
UPDATE (or other options)

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html




Re: tuple concurrently updated

От
wambacher@posteo.de
Дата:

got it - vacuum analyze on that table solved the problem. think, there was one index corrupt and not the data.

thanks to all

walter

Am 28.08.2018 um 12:36 schrieb wambacher@posteo.de:
Hi,

getting this error:

DELETE FROM planet_osm_line WHERE osm_id = -166570;
FEHLER:  tuple concurrently updated

postgresql 10.1

any idea what i can do? of course i did a restart first.

regards

walter