Обсуждение: 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
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
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. >
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.
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. >
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
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.
Flavio Gurgel
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
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.
My mistake, it's pg_prepared_xacts the correct name.
regards
walter
pg_prepared_xacts is emptyFlavio Gurgel
regards
walter
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. >
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. >
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 ?" >
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 ?" >
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. >
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
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. >
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
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 >
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 updatedConcurrent 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
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?
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 ? >
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
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?
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
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
>
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
(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
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;Concurrent transactions can update or delete one or more of those rows
FEHLER: tuple concurrently updated
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
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