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

[wambacher]

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=100 
osm_id=701634735
HINWEIS:  wno_recover_pol: 2019-10-11 21:56:41.457124+02 rows=200 
osm_id=513908287
HINWEIS:  wno_recover_pol: 2019-10-11 22:03:58.663295+02 rows=300 
osm_id=36807165
HINWEIS:  wno_recover_pol: 2019-10-11 22:09:40.59184+02 rows=400 
osm_id=-9334086
HINWEIS:  wno_recover_pol: 2019-10-11 22:25:24.717055+02 rows=500 
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=600 
osm_id=303611045
HINWEIS:  wno_recover_pol: 2019-10-11 22:42:19.535747+02 rows=700 
osm_id=439078923


...

until now "only" 3 damaged records :)

regards

walter

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

[wambacher]

Hi Adrin,

Am 11.10.19 um 21:42 schrieb Adrian Klaver:

On 10/11/19 10:28 AM, wambac...@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

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

[wambacher]

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


Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 
OSM Software Watchlist 

Re: logging "raise" to file

[wambacher]

Thank Georg,

regards
walter

> the red part writes your "raise notice" to your log
>
> psql (+your connection string) -f /path/to/file.sql *>
> /path/to/log/xxx.log 2>&1*
>
> -- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 
OSM Software Watchlist

logging "raise" to file

[wambacher]

Hi,

is there a way to log output from "raise ..." to a local file? \o file
does not work (for me).

regards
walter

-- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 
OSM Software Watchlist

Re: update table with suppress_redundant_updates_trigger()

[wambacher]

Problem "solved".

"found" will be true of false depending on the trigger action.

update done -> found = true, update not done -> found=false.

But: The trigger sometimes allows updates where no data has been
changed! That is another problem to be solved :(

Regards

walter

Am 25.02.19 um 19:42 schrieb wambac...@posteo.de:
>
> Hi,
>
> i'm doing a lot of updates in my database, but most of them are not
> necessary at all (sorry, just detected it)
>
> Therefore i installed a trigger to minimize real updates.
>
> create trigger suppress_redundant_updates_boundaries
>    before update on boundaries
>    for each row execute procedure suppress_redundant_updates_trigger();
>
> Is there a way to get the count of the real table updates?
>
> Using pl/pgsql i'm updating boundaries  with
>
>   update boundaries
>  set id = bT2.id,
>  country    = bT2.country,
>  type   = 'admin',   
>  value  = bT2.value,
> ...
>     ,qm = bT2.qm
>     ,lwqm   =
> st_area(geography(coalesce(xlandarea,rT.way)))
>    where id = bT2.id;
>
>    if (found) then
>   if (debug > 0) then raise notice 'real db update
> of % done 2', bT2.id; end if;
>   updatedDB := updatedDB + 1;
>    end if;
>
> i get a "wrong" result, because "found" is always true, even when the
> records are identical (hope so) and an update should be suppressed by
> the trigger.
>
> Question: will "found" be set when update has been blocked by the
> trigger - or does that not matter?
>
> if "found" is always true: what else can i do?
>
> regards
>
> walter
>
> -- 
> My projects:
>
> Admin Boundaries of the World 
> Missing Boundaries
> 
> Emergency Map 
> Postal Code Map (Germany only) 
> Fools (QA for zipcodes in Germany) 
> Postcode Boundaries of Germany
> 
-- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 

update table with suppress_redundant_updates_trigger()

[wambacher]

Hi,

i'm doing a lot of updates in my database, but most of them are not
necessary at all (sorry, just detected it)

Therefore i installed a trigger to minimize real updates.

create trigger suppress_redundant_updates_boundaries
   before update on boundaries
   for each row execute procedure suppress_redundant_updates_trigger();

Is there a way to get the count of the real table updates?

Using pl/pgsql i'm updating boundaries  with

  update boundaries
 set id = bT2.id,
 country    = bT2.country,
 type   = 'admin',   
 value  = bT2.value,
...
    ,qm = bT2.qm
    ,lwqm   =
st_area(geography(coalesce(xlandarea,rT.way)))
   where id = bT2.id;

   if (found) then
  if (debug > 0) then raise notice 'real db update
of % done 2', bT2.id; end if;
  updatedDB := updatedDB + 1;
   end if;

i get a "wrong" result, because "found" is always true, even when the
records are identical (hope so) and an update should be suppressed by
the trigger.

Question: will "found" be set when update has been blocked by the
trigger - or does that not matter?

if "found" is always true: what else can i do?

regards

walter

-- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 

Re: changing my mail address

[wambacher]

thanks, got it.

walter


Am 17.03.2018 um 16:51 schrieb Stephen Frost:

Greetings,

* wambac...@posteo.de (wambac...@posteo.de) wrote:

how can i change my mail adress for the postgresql mailing lists? adding my
new address worked, but how do i get rid of the old one?

You'll need to change it on postgresql.org:

https://www.postgresql.org/account/

Once you've done that, log out of all PG sites (possibly by deleteing
cookies which you may have from them) and then log into postgresql.org
first and then go to lists.postgresql.org and the update should be
passed through.

Thanks!

Stephen


--
[url=http://t1p.de/epnc]Emergency Map[/url], 
[url=http://t1p.de/t7xy]PLZ-Karte[/url], [url=http://t1p.de/d6hv]Fools[/url], 
[url=http://t1p.de/6ptd]Boundaries Map[/url], [url=http://t1p.de/huxb]Postcode 
Boundaries[/url], [url=http://t1p.de/mita]Missing Boundaries[/url], 
[url=http://t1p.de/0llz]@wambacher1[/url]

changing my mail address

[wambacher]

Hi,

how can i change my mail adress for the postgresql mailing lists? adding 
my new address worked, but how do i get rid of the old one?


regards

walter