Birgit, I suspect as you alluded to that you are a victim of the dreaded cached plan and your OID issue is because the new table doesn't have the same OID as the old table. 8.4 is supposed to be smart enough to invalidate plans in these situations, thought maybe not.
One possible work around is instead of creating and dropping the table, why don't you just TRUNCATE the table and reset the sequence So something like TRUNCATE TABLE birgit.test_diff_dlm07_tmp; ALTER SEQUENCE birgit.test_diff_dlm07_tmp.gid RESTART WITH 1; You could also use CREATE TEMP TABLE instead of CREATE TABLE. I suspect temp table oids may not be cached. Leo -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Birgit Laggner Sent: Thursday, December 03, 2009 12:26 PM To: PostGIS Users Discussion Subject: [postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX Dear list, I have written a pl/pgsql function (see below) for st_difference which in short should sequentially scan a geometric table (a) if there are intersections with geometric table (b) and if there are, it writes the intersecting polygons of table (b) into an extra table and then executes the st_difference for the actual polygon of table (a) and all polygons of table (b ) written in the extra table as a sequence always using the product of the last difference as the input (instead of the table (a) polygon) of the next difference. I hope everybody understands my way of thinking ;-) My problem is now, that at polygon 451 of table (a), the function stops with the following error message: ERROR: could not open relation with OID 25736 SQL Status:XX000 Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE Strange is, that the function did run successfully for more than 100 difference-loops. In an older PostGres version (8.1...), I have had a similar problem, but then always in the 2nd loop, because of the cashing-problem of the query planner. This are the PostGIS/PostgreSQL versions I am using: PostGIS: 8.4.1-2.1 PostgreSQL: 1.4.0-10.1 Here, the last few message rows of the running function, perhaps this helps with understanding the problem (sorry because it's partly in German, I hope it doesn't matter): NOTICE: Beginn Difference für dlm07-Polygon 450 NOTICE: Anzahl Intersection-Polygone: 1 NOTICE: CREATE TABLE erstellt implizit eine Sequenz »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte »test_diff_dlm07_tmp.gid« CONTEXT: SQL-Anweisung »create table birgit.test_diff_dlm07_tmp (gid serial, inv07_id integer, the_geom geometry);« PL/pgSQL function "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung NOTICE: recordset_object2a: (309108,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC87519141564121 B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E943870 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D792 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142 690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A4170493851304156 41D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163BDF058E9 3B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015 233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE 6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A 41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD983366 4156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F746505974415641125009 96C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB3C4A418B 99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C8F914156 41579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1) NOTICE: recordset_object1: (450,0103000020EB7A00000100000022000000CC72F1149A3C4A41000000002B415641295C8 F229A3C4A4185EB51582B4156413E0AD7A3983C4A41000000002B415641351D7C68833C4A410 00000002B415641F6285C2F7E3C4A410000001031415641B81E856B913C4A41A4703D6A35415 6413E0AD723AD3C4A41AE47E17A3B415641B81E850BAE3C4A4114AE47E13E415641E17A144EA F3C4A417B14AEE74341564152B81EA5B03C4A410AD7A3B04741564114AE4721BB3C4A41EC51B 82E474156417B14AE67CC3C4A41AE47E17A46415641B81E852BD83C4A411F85EB01464156416 6666646D73C4A4185EB51B8454156413E0AD783D63C4A41666666664541564114AE4761D53C4 A41B81E85DB44415641295C8F02D53C4A418FC2F548444156413E0AD703D53C4A41666666C64 34156419A999979D53C4A41AE47E1CA424156410AD7A330D63C4A41713D0AA741415641A4703 D8AD73C4A41B81E859B40415641A4703DEAD93C4A41E17A14CE3E415641713D0A17DB3C4A41B 81E85DB3D415641CDCCCC8CDC3C4A41F6285CAF3C415641E17A148EDF3C4A41AE47E10A3B415 6419A999959E13C4A410AD7A3103A4156413E0AD7E3E23C4A4148E17AC438415641A4703D0AE 43C4A41AE47E13A38415641A4703DCAEB3C4A4114AE47213841564100000060EC3C4A413E0AD 7D33241564114AE47E1E03C4A41D7A370AD324156419A999979E33C4A417B14AE772E4156418 82F554CE43C4A41000000002B415641CC72F1149A3C4A41000000002B415641) NOTICE: recordset_object2a: (309108,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC87519141564121 B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E943870 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D792 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142 690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A4170493851304156 41D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163BDF058E9 3B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015 233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE 6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A 41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD983366 4156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F746505974415641125009 96C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB3C4A418B 99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C8F914156 41579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1) NOTICE: Intersection-Polygon 1 verarbeitet. NOTICE: Difference-Polygon dlm07 450 ist fertig. NOTICE: Beginn Difference für dlm07-Polygon 451 NOTICE: Anzahl Intersection-Polygone: 1 NOTICE: CREATE TABLE erstellt implizit eine Sequenz »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte »test_diff_dlm07_tmp.gid« CONTEXT: SQL-Anweisung »create table birgit.test_diff_dlm07_tmp (gid serial, inv07_id integer, the_geom geometry);« PL/pgSQL function "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung NOTICE: recordset_object2a: (242405,0103000020EB7A00000100000093000000A5D6BC7F113B4A41DA9C414B8C44564150 FAAEDD133B4A419F49202D8B4456412FF0D7391B3B4A41168EC19286445641E5C111791C3B4A 412DEE6FD88D44564193716BA71F3B4A41527950589B4456413706B5B4203B4A411CA7E1639F 4456417F264CED223B4A410BDF111DA544564195B45007273B4A410C80CF17AE445641233940 05293B4A41D9F53FC8B34456417B6AB9082D3B4A415F147142B4445641B5FC2FAC303B4A4129 35E853B4445641C323EFF1373B4A41C18C5DAAB34456412A8D25813F3B4A41C33BF4E5B24456 41022A8ED84C3B4A4118E6FF5DB2445641F237A3BB5D3B4A414C7E863FB244564191C9735265 3B4A410E9B8A5CB1445641529A25D6633B4A41C47E2DB7AD445641B5D7BDEA5E3B4A4160BF3D D7A4445641064DA2335A3B4A41B397823D9B445641468081F5693B4A412AAF71059B44564174 5B60647B3B4A4188667A919A4456411E7579487A3B4A41BA466E559944564131F2D4F87E3B4A 41C9563A27994456414A570C2C893B4A41CC1B91F797445641FAD2C8BCA33B4A419EEF661595 44564194F74682CE3B4A41C8A7A28090445641F677AA51E53B4A41C3AC804A8E4456419D16B3 33023C4A4145B4DB368B4456412CD4AE17083C4A4172F829968A4456418E660AAB223C4A4142 4103B9874456414F5B3AF4233C4A4100517DEF92445641F472B68E233C4A41C56C4F98934456 4102FCF859223C4A415E932D9A95445641EFCC195F113C4A41DB4F155BA64456415457E1A40E 3C4A4151BCC460A9445641115545C6083C4A41DC02CB19B14456418064136EF33B4A413F76BC D1AC445641DB6389DCF13B4A41BFE689EFB344564126807354EB3B4A4183EC2AACBD445641C5 F5579DFA3B4A4121A624F3BF445641D717C6EBF73B4A41ABBB2195C744564161A21A04F33B4A 41FB77D15FD5445641D0D839DB053C4A41B7831098D84456410A907C33073C4A414244DACED0 44564148A334A9163C4A41547CEC36D1445641BF7B60D5363C4A41BCF33468D1445641F68B9D FE393C4A411283312ADA445641B45CC31A4C3C4A41F6A929D1D744564185C3D2CE563C4A4146 BC02D9D64456410E266F8E503C4A41256BD10AC544564178A04D1C4E3C4A416E50306BBD4456 417ED898E54C3C4A41A3F1EA24B6445641C11890CB4D3C4A4185584DDFAE445641100E678C50 3C4A41447D8CC1A8445641C8EA7258533C4A41D025F919A4445641F45E60645B3C4A41F11D11 5B9B445641769EB3136B3C4A41DD06E5858D44564175B35E1A703C4A41CBE002D8874456418E E0827A743C4A41A399728D80445641C2DE7203773C4A4116C1F4C579445641BF3B192A783C4A 41250884A571445641B465A72D773C4A41317A276E5C4456414FFAB66E6C3C4A41EE3D4D3554 445641D8E3F8AF613C4A41CC142A074A445641C4DD74D9493C4A41E18223504A44564149E72F 02203C4A416083AE4C4B445641BB020316163C4A417AD8A6804D44564115A108B7023C4A4133 B9970352445641667D32D4FA3B4A413B9DE3AB52445641206FE7D0EF3B4A410F12217D524456 416EAA545EE83B4A41E4AAF88E51445641C242D969DD3B4A41BC9873F84F44564159773A65CF 3B4A410D0595C04B445641B4F85B10CF3B4A4187C6CA374A445641883CC8C0C73B4A419A306D 07464456419595327EBB3B4A41A5F9E0843F445641A4113DFEA83B4A4120D2EB1E3644564157 6BBF8A9D3B4A41334EA22D30445641F52DD12E9C3B4A411BEEF45232445641A4E92DF3973B4A 413F90FFD13444564143331E7A943B4A41F761391136445641A69D4CB1903B4A414DC4100837 445641267AE7258C3B4A41A46AA98B37445641AFD254247C3B4A41E986946E384456417541D6 286D3B4A416D1F52DB3944564158B4E7076B3B4A412A3AF20F3A4456419F2AFEC4623B4A4145 0828DC3A44564109C6A7D5613B4A416D1F52DB39445641E9BFB312583B4A413FC197913B4456 4137F356324D3B4A4145E438BF3E445641A69714F4243B4A41FF1C9DC249445641F8194AC213 3B4A4129F0221B4F445641FA42FB2D133B4A41A2CCF75B54445641616D3114153B4A419CB61B 4C58445641BF8B0E1D183B4A418E3130FA5B4456415BD1C57B1F3B4A41AE6C11205E445641EB C43854353B4A4112AAE30063445641F4DE50CD423B4A41B562C8B96C4456417E0F5D25413B4A 41BA301B6C6D445641CBDFD71D343B4A41E66F8A9663445641805C16E81C3B4A41F872CAAB5E 445641433BF7F3173B4A41BAB368095D4456413F29FF2F143B4A41D805CA755A445641C55526 D1103B4A4158A7F41B554456415BF92622113B4A41885AA7994F4456416402759FF53A4A4116 623BE750445641F6FFBDB0F23A4A415EB061555044564131A5403BE93A4A418CC63CFA4C4456 412FFBA344E43A4A418D36F0724C4456415DB86386B33A4A41BFA5FE494D445641A5FD702CAD 3A4A417D8DBFBC4D445641A2967FC9A33A4A41E508260650445641FAC39BD16C3A4A419ABBC1 1F5F445641E2C7B9E9683A4A413EFFA1685F44564170F2B498613A4A41F872CAAB5E44564192 C7B713423A4A41D76C65915B445641F5A23B242A3A4A41F4F96F2D5A445641DAD3B5921B3A4A 4168628F5858445641AF0DF4EC133A4A41F038A8CC56445641807AF58D0D3A4A41F80F4C3D56 445641641AB90A073A4A412046A47557445641764EC9D5013A4A417192BBD758445641C1CD4D 37FE394A418E16E7775B445641E07DDC1AFA394A418AE5C1E55E445641783329A1F1394A411E EC95816444564171FF88C2E8394A41BB6F1AAA694456411A500F6DD9394A414D459052724456 4160B99CAEE8394A41D4C6E9F3744456411E15301DF4394A41E6F51C60794456416CD799DEF7 394A41A3D453B37A4456411D105594103A4A41CC5FFA667E44564136C98BE8183A4A4147B127 89804456414A49D33C213A4A41B381F8C8814456414633A250283A4A41AA794B418244564122 3F70313C3A4A415F233FEA82445641B5899402483A4A410904F70B8344564117DDEFEC5C3A4A 41E541706483445641E6247DF6713A4A417E1B589683445641A219CD66933A4A41372C3C3784 44564146FD76AECD3A4A41F5171BC28644564129A670D9D23A4A41BB8934F98744564136C565 B1D63A4A4104824C3A89445641265EE8E6E03A4A41B0E45ABC8D44564141F89B25EA3A4A416D 9A8BC792445641E1B6AC97EC3A4A41EE52A9E19444564164801FAAFA3A4A414E05B8289A4456 41A5D6BC7F113B4A41DA9C414B8C445641,1) NOTICE: recordset_object1: (451,0103000020EB7A00000100000007000000F6285CEF5D3C4A413E0AD7134A445641D7A37 0DD733C4A411F85EB0159445641C2F5283C7C3C4A413E0AD72359445641EC51B8FE7A3C4A41D 7A370FD47445641EC51B8FE733C4A413E0AD76348445641713D0A57663C4A418FC2F54849445 641F6285CEF5D3C4A413E0AD7134A445641) ERROR: could not open relation with OID 25736 CONTEXT: PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE If anybody has suggestions, I would be very happy. If you need more information or the two tables in question, please tell me. Many thanks, Birgit. CREATE OR REPLACE FUNCTION _laggner_b_pgdifference_a() RETURNS void AS $BODY$ DECLARE counter integer; recordset_object1 RECORD; recordset_object2 RECORD; recordset_object2a RECORD; recordset_object3 RECORD; i integer; n integer; j integer; m integer; BEGIN --4. Difference a (dlm07): counter := 0; i := 0; n := count(dlm07_id) from birgit.ni_dlm07_clip2; FOR i in 1..n LOOP --LOOP 1 RAISE NOTICE 'Beginn Difference für dlm07-Polygon % ', i; SELECT dlm07_id, the_geom INTO recordset_object1 from birgit.ni_dlm07_clip2 where dlm07_id=i; SELECT b.inv07_id as inv07_id, b.the_geom as the_geom INTO recordset_object2 from birgit.ni_dlm07_clip2 a, birgit.ni_inv07_clip2 b where a.dlm07_id=i and st_relate(a.the_geom, b.the_geom, '2********'); m := count(recordset_object2.inv07_id); RAISE NOTICE 'Anzahl Intersection-Polygone: % ', m; IF m > 0 THEN execute 'create table birgit.test_diff_dlm07_tmp (gid serial, inv07_id integer, the_geom geometry);'; insert into birgit.test_diff_dlm07_tmp (inv07_id, the_geom) select recordset_object2.inv07_id, recordset_object2.the_geom; SELECT a.inv07_id as inv07_id, a.the_geom as the_geom, a.gid as gid INTO recordset_object2a FROM birgit.test_diff_dlm07_tmp a; RAISE NOTICE 'recordset_object2a: %', recordset_object2a; execute 'drop table birgit.test_diff_dlm07_tmp;'; j := 0; FOR j in 1..m LOOP --LOOP 2 RAISE NOTICE 'recordset_object1: %', recordset_object1; RAISE NOTICE 'recordset_object2a: %', recordset_object2a; SELECT recordset_object1.dlm07_id as dlm07_id, st_difference(recordset_object1.the_geom, recordset_object2a.the_geom) as the_geom INTO recordset_object3 WHERE recordset_object2a.gid=j; SELECT recordset_object3.dlm07_id as dlm07_id, recordset_object3.the_geom as the_geom INTO recordset_object1; RAISE NOTICE 'Intersection-Polygon % verarbeitet. ', j; END LOOP; --END LOOP 2 IF st_isempty(recordset_object1.the_geom)='f' then INSERT INTO birgit.test_diff_dlm07 (dlm07_id, inv07_id, the_geom) VALUES ( recordset_object1.dlm07_id, NULL, recordset_object1.the_geom); END IF; RAISE NOTICE 'Difference-Polygon dlm07 % ist fertig. ', i ; ELSE RAISE NOTICE 'Kein Difference berechnet. '; END IF; counter := counter + 1; END LOOP; --END LOOP 1 END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION _laggner_b_pgdifference_a() OWNER TO postgres; _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
