Обсуждение: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed:FEHLER: tuple concurrently updated
got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed:FEHLER: tuple concurrently updated
От
wambacher@posteo.de
Дата:
Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated
any idea what i can do?
regards
walter
On 10/11/19 10:28 AM, wambacher@posteo.de wrote: > Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE > osm_id = -390840 failed: FEHLER: tuple concurrently updated > > any idea what i can do? More information would be helpful: 1) Postgres version? 2) What is logged just before the crash? 3) By crash do you mean the Postgres server shuts down? 4) Does this happen every time you delete -390840? 5) Is -390840 really the value? If so what is the definition for the osm_id column? > > regards > > walter > > -- > My projects: > > Admin Boundaries of the World <https://wambachers-osm.website/boundaries> > Missing Boundaries > <https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries> > Emergency Map <https://wambachers-osm.website/emergency> > Postal Code Map (Germany only) <https://wambachers-osm.website/plz> > Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools> > Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries> > OSM Software Watchlist > <https://wambachers-osm.website/index.php/osm-software-watchlist> -- Adrian Klaver adrian.klaver@aklaver.com
On 10/11/19 10:28 AM, wambacher@posteo.de wrote: > Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE > osm_id = -390840 failed: FEHLER: tuple concurrently updated > > any idea what i can do? Additional question: 6) Are there triggers on planet_osm_line? In particular a BEFORE UPDATE, on the theory it is related to this: https://www.postgresql.org/message-id/16036-28184c90d952fb7f%40postgresql.org > > regards > > walter > > -- > My projects: > > Admin Boundaries of the World <https://wambachers-osm.website/boundaries> > Missing Boundaries > <https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries> > Emergency Map <https://wambachers-osm.website/emergency> > Postal Code Map (Germany only) <https://wambachers-osm.website/plz> > Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools> > Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries> > OSM Software Watchlist > <https://wambachers-osm.website/index.php/osm-software-watchlist> -- Adrian Klaver adrian.klaver@aklaver.com
Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840failed: FEHLER: tuple concurrently updated
От
wambacher@posteo.de
Дата:
Hi Adrin, Am 11.10.19 um 21:42 schrieb Adrian Klaver: > On 10/11/19 10:28 AM, wambacher@posteo.de wrote: >> Hi, after a crash i get this errpor: DELETE FROM planet_osm_line >> WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated >> >> any idea what i can do? > > More information would be helpful: > > 1) Postgres version? 10.1 > > 2) What is logged just before the crash? will have to check this later. > > 3) By crash do you mean the Postgres server shuts down? no, system hang and i had to do a power reset (nothing else helped) > > 4) Does this happen every time you delete -390840? yes > > 5) Is -390840 really the value? yes > If so what is the definition for the osm_id column? bigint, index "planet_osm_line_pkey" btree (osm_id), Tablespace »planet3_is1 and there is no trigger on this table. i wrote a pg/plsql function reading the table line by line using an exception condition and writing all records to a new table. program is running and some records have been ignored. it is quite easy to re-add the missing records - hope so. regards walter
On 10/11/19 1:56 PM, wambacher@posteo.de wrote: > Hi Adrin, > > Am 11.10.19 um 21:42 schrieb Adrian Klaver: >> On 10/11/19 10:28 AM, wambacher@posteo.de wrote: >>> Hi, after a crash i get this errpor: DELETE FROM planet_osm_line >>> WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated >>> >>> any idea what i can do? >> >> More information would be helpful: >> >> 1) Postgres version? > 10.1 >> >> 2) What is logged just before the crash? > will have to check this later. >> >> 3) By crash do you mean the Postgres server shuts down? > no, system hang and i had to do a power reset (nothing else helped) By system do you mean just Postgres or the computer as a whole? >> >> 4) Does this happen every time you delete -390840? > yes >> >> 5) Is -390840 really the value? > yes >> If so what is the definition for the osm_id column? > > bigint, > > index "planet_osm_line_pkey" btree (osm_id), Tablespace »planet3_is1 Have you tried a REINDEX? > > and there is no trigger on this table. > > i wrote a pg/plsql function reading the table line by line using an > exception condition and writing all records to a new table. program is > running and some records have been ignored. it is quite easy to re-add > the missing records - hope so. > > regards > > walter > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840failed: FEHLER: tuple concurrently updated
От
wambacher@posteo.de
Дата:
Hi Adrian, no, system hang and i had to do a power reset (nothing else helped) > > By system do you mean just Postgres or the computer as a whole? The whole system was hanging (Ubuntu). very strange: did a "lshw" not being root. no idea what was going on. But we don't have to discuss that here. May be disk io was hanging too. > > Have you tried a REINDEX? > not yet because i don't want to change any bit in this table right now. at least i'll wait until my recovery program finished (about ~ 50% done) toast seems to be invalid too. here some lines of the log: planet3=# truncate pol_recover;select wno_recover_pol(); TRUNCATE TABLE HINWEIS: wno_recover_pol: 2019-10-11 21:52:18.066759+02 rows=0 osm_id=633182165 HINWEIS: wno_recover_pol: 2019-10-11 21:53:59.233604+02 rows=1000000 osm_id=701634735 HINWEIS: wno_recover_pol: 2019-10-11 21:56:41.457124+02 rows=2000000 osm_id=513908287 HINWEIS: wno_recover_pol: 2019-10-11 22:03:58.663295+02 rows=3000000 osm_id=36807165 HINWEIS: wno_recover_pol: 2019-10-11 22:09:40.59184+02 rows=4000000 osm_id=-9334086 HINWEIS: wno_recover_pol: 2019-10-11 22:25:24.717055+02 rows=5000000 osm_id=-2242787 HINWEIS: wno_recover_pol: Exception -9493166 unexpected chunk number 0 (expected 1) for toast value 3243289288 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -9493166 unexpected chunk number 0 (expected 1) for toast value 3243289288 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -61549 unexpected chunk number 0 (expected 1) for toast value 3243289264 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 (expected 1) for toast value 3243289204 in pg_toast_1340113 HINWEIS: wno_recover_pol: 2019-10-11 22:40:42.696463+02 rows=6000000 osm_id=303611045 HINWEIS: wno_recover_pol: 2019-10-11 22:42:19.535747+02 rows=7000000 osm_id=439078923 ... until now "only" 3 damaged records :) regards walter
On 10/11/19 3:42 PM, wambacher@posteo.de wrote: > Hi Adrian, > > no, system hang and i had to do a power reset (nothing else helped) >> >> By system do you mean just Postgres or the computer as a whole? > > The whole system was hanging (Ubuntu). very strange: did a "lshw" not > being root. no idea what was going on. But we don't have to discuss that > here. May be disk io was hanging too. > >> >> Have you tried a REINDEX? >> > not yet because i don't want to change any bit in this table right now. > at least i'll wait until my recovery program finished (about ~ 50% done) > > toast seems to be invalid too. here some lines of the log: Any hardware 'events' recently? > > planet3=# truncate pol_recover;select wno_recover_pol(); > TRUNCATE TABLE > HINWEIS: wno_recover_pol: 2019-10-11 21:52:18.066759+02 rows=0 > osm_id=633182165 > HINWEIS: wno_recover_pol: 2019-10-11 21:53:59.233604+02 rows=1000000 > osm_id=701634735 > HINWEIS: wno_recover_pol: 2019-10-11 21:56:41.457124+02 rows=2000000 > osm_id=513908287 > HINWEIS: wno_recover_pol: 2019-10-11 22:03:58.663295+02 rows=3000000 > osm_id=36807165 > HINWEIS: wno_recover_pol: 2019-10-11 22:09:40.59184+02 rows=4000000 > osm_id=-9334086 > HINWEIS: wno_recover_pol: 2019-10-11 22:25:24.717055+02 rows=5000000 > osm_id=-2242787 > HINWEIS: wno_recover_pol: Exception -9493166 unexpected chunk number 0 > (expected 1) for toast value 3243289288 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -9493166 unexpected chunk number 0 > (expected 1) for toast value 3243289288 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -61549 unexpected chunk number 0 > (expected 1) for toast value 3243289264 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: Exception -240861 unexpected chunk number 0 > (expected 1) for toast value 3243289204 in pg_toast_1340113 > HINWEIS: wno_recover_pol: 2019-10-11 22:40:42.696463+02 rows=6000000 > osm_id=303611045 > HINWEIS: wno_recover_pol: 2019-10-11 22:42:19.535747+02 rows=7000000 > osm_id=439078923 > > ... > > until now "only" 3 damaged records :) > > regards > > walter > > > > -- Adrian Klaver adrian.klaver@aklaver.com