Re: [osmosis-dev] node_tags duplicate key with MySQL varbinary - fixed using Blackhole and trigger
Picking up my earlier question at: http://lists.openstreetmap.org/pipermail/osmosis-dev/2011-July/001092.html On 22 July 2011 08:22, Shaun McDonald sh...@shaunmcdonald.me.uk wrote: It is a problem with trailing spaces in MySQL VARCHAR: http://sql-info.de/mysql/gotchas.html#1_6 Yeah, trailing spaces and character sets have got me here, and my query about an idea to use INSERT IGNORE to fill the the node_tags table was not picked up, but I think I've got a way through now. I can define the node_tags table to use the BLACKHOLE engine, and create a before trigger to fill my own version of the table: CREATE TABLE `node_tags` ( `id` bigint(64) NOT NULL, `version` bigint(20) NOT NULL, -- Changing this to varchar(255) binary wasn't enough to fix the problem with a boutique/clothes shop somewhere in eastern Europe. `k` varchar(255) NOT NULL default '', `v` varchar(255) NOT NULL default '', -- Removed `version`, from this index as we only deal with the latest. -- Because OSM allows duplicated key names this may need to be downgraded from a PRIMARY KEY to just KEY, this gets fixed up during import. KEY `id_k` (`id`,`k`) -- CONSTRAINT `node_tags_ibfk_1` FOREIGN KEY (`id`, `version`) REFERENCES `nodes` (`id`, `version`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8; -- Create the structure of the nodeTag similar to the node_tags table but with fewer fields and a primary key on id,k.; CREATE TABLE `nodeTag` ( `id` bigint(64) NOT NULL DEFAULT '0', `k` varchar(255) NOT NULL, `v` varchar(255) NOT NULL, PRIMARY KEY (`id`,`k`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- A trigger that diverts node_tags insertions into nodeTag drop trigger if exists insert_node_tags; delimiter // create trigger insert_node_tags before insert on node_tags for each row begin insert ignore nodeTag (id, k, v) values (new.id, new.k, new.v); end;// delimiter ; I'm yet to discover how efficient this will be. ___ osmosis-dev mailing list osmosis-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/osmosis-dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi Frederik, I'm running minutely updates on south-east asia. The DB is comparably small. After I did this: On 07.08.2011 21:19, Frederik Ramm wrote: DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); It took some minutes, but then looked fine. The last night it seams import gets stuck. postgres is spending a lot of time in UPDATE. Either there is something missing or fastupdate=off is a bad idea for some systems. Before it worked not too bad. I have a cronjob doing cleanup work including a vacuum which usually completes in minutes. The last night it spent there two hours. I'm reverting the index to the default now... Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] Problems with Dominican Republic
Hello, I have detected some extrange problems with Dominican Republic country. All reverse geocoding were from Virgin Islands country (country_code rq). This error was on 05/08/2011, now all is recovered. My server is periodically downloading to do a recover: http://download.geofabrik.de/osm/central-america/haiti-and-domrep.osm.bz2 http://downloads.cloudmade.com/americas/caribbean/virgin_islands_us/virgin_islands_us.osm.bz2 On 05/08 was an error in this files ? in osm data ? How could I find bugs about data of OpenStreetMaps ? Thank you! ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] osm2pgsql intarray contrib module loaded
Hi! After starting this command: osm2pgsql/osm2pgsql -s -C 3000 -d gis -U gisuser -S default.style gis.osm I get this output from osm2pgsql: osm2pgsql SVN version 0.80.0 (32bit id space) (...) The target database has the intarray contrib module loaded. While required for earlier versions of osm2pgsql, intarray is now unnecessary and will interfere with osm2pgsql's array handling. Please use a database without intarray. Does anybody know what to do? Should I switch off the Intarray extension in postgresql? If yes, how? Markus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql intarray contrib module loaded
Hi, On 08/08/11 19:08, mar...@gmx.eu wrote: The target database has the intarray contrib module loaded. While required for earlier versions of osm2pgsql, intarray is now unnecessary and will interfere with osm2pgsql's array handling. Please use a database without intarray. Simply drop your old database and create a new one, without loading _int this time. If you would prefer to continue using the intarray version of osm2pgsql, this has been moved to the osm2pgsql-intarray directory. Bye Frederik ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql intarray contrib module loaded
On Mon, 2011-08-08 at 19:08 +0200, mar...@gmx.eu wrote: Hi! After starting this command: osm2pgsql/osm2pgsql -s -C 3000 -d gis -U gisuser -S default.style gis.osm I get this output from osm2pgsql: osm2pgsql SVN version 0.80.0 (32bit id space) (...) The target database has the intarray contrib module loaded. While required for earlier versions of osm2pgsql, intarray is now unnecessary and will interfere with osm2pgsql's array handling. Please use a database without intarray. Does anybody know what to do? Should I switch off the Intarray extension in postgresql? If yes, how? If you want to use the latest version of the code then you will have to remove the previous intarray extension or create a new database without it. The latest code uses the built-in array support instead. Your postgres install should have included an appropriate uninstall SQL script in the contrib directory and can be executed like: $ sudo -u postgres psql DBNAME /usr/share/pgsql/contrib/uninstall__int.sql Jon Markus ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql intarray contrib module loaded
Hi Frederik and Jon, thanks a lot, this worked! There was no uninstall procedure available but the database could be recreated easily. Markus Original-Nachricht Datum: Mon, 08 Aug 2011 19:30:03 +0200 Von: Frederik Ramm frede...@remote.org An: dev@openstreetmap.org Betreff: Re: [OSM-dev] osm2pgsql intarray contrib module loaded Hi, On 08/08/11 19:08, mar...@gmx.eu wrote: The target database has the intarray contrib module loaded. While required for earlier versions of osm2pgsql, intarray is now unnecessary and will interfere with osm2pgsql's array handling. Please use a database without intarray. Simply drop your old database and create a new one, without loading _int this time. If you would prefer to continue using the intarray version of osm2pgsql, this has been moved to the osm2pgsql-intarray directory. Bye Frederik ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql intarray contrib module loaded
Hi, Stephan Knauss wrote: Sorry, for me it's not fully clear. Maybe for my small DB it's fine to reimport, but for others having the whole planet: There's a misunderstanding here. The old intarray method and the now builtin PostgreSQL arrays method lead to a slightly different structure in the slim mode tables. It is *not* possible to update an intarray database with the builtin PostgreSQL arrays software (or vice versa). Even if there is a way to un-load the intarray code from your PostgreSQL instance, this will not magically re-format your slim mode tables so you will certainly have to reimport if you want to use the newer osm2pgsql version, or use the old osm2pgsql version if you don't want to reimport. The other thing is that even *if* you choose to do a full reimport, osm2pgsql will *still* complain when it finds that you have the intarray module loaded, so either you unload it or you drop and re-create the database, which is no big deal since you were re-importing anyway. The reason why I put this oops you have _int warning in is that I noticed certain adverse effects of having _int loaded and then using builtin arrays; it seemed that PostgreSQL wouldn't use the index properly in these cases. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
On 07.08.2011 21:19, Frederik Ramm wrote: To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes with DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); This did lead to huge problems on my DB as it was still using the intarray for the updates. Recovered it with the gin__int_ops on the gin index: DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes gin__int_ops) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts gin__int_ops) WITH (fastupdate=off); Documentation suggests to use ALTER INDEX instead. I did not try it as I already broke my DB using the original commands. Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, Stephan Knauss wrote: On 07.08.2011 21:19, Frederik Ramm wrote: To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes with DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); This did lead to huge problems on my DB as it was still using the intarray for the updates. I don't understand. The intarray/no intarray question is completely unrelated to the fastupdate on/off question? Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
On 09.08.2011 00:00, Frederik Ramm wrote: Stephan Knauss wrote: This did lead to huge problems on my DB as it was still using the intarray for the updates. I don't understand. The intarray/no intarray question is completely unrelated to the fastupdate on/off question? The problem was that the INDEX is different. It has the gin__int_ops included on my installation with intarray in place. USING gin (nodes gin__int_ops) I guess without it postgres was not using indexes at all and thus slowing it down that much. Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, Stephan Knauss wrote: The problem was that the INDEX is different. It has the gin__int_ops included on my installation with intarray in place. Ah, now I see. My instructions on rebuilding the index were not taking the old style into account. Sorry, I overlooked that! So, anyone else still using the old osm2pgsql (i.e. anybody who hasn't updated in the last few days and hasn't been using the osm2pgsql-64 branch before, i.e. most people) wanting to recover from the missing fastupdate=off by dropping and re-creating the index, don't forget the gin__int_ops when building the index. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
