got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

Started by Nonameover 6 years ago7 messagesgeneral

wambacher@posteo.de

over 6 years ago

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

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

over 6 years ago

In reply to: Noname (#1)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Noname (#1)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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:
/messages/by-id/16036-28184c90d952fb7f@postgresql.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

Noname

wambacher@posteo.de

over 6 years ago

In reply to: Adrian Klaver (#2)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Noname (#4)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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

Noname

wambacher@posteo.de

over 6 years ago

In reply to: Adrian Klaver (#5)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Noname (#6)

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

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