[postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

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,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC87519141564121B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E9438704156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D7929CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A417049385130415641D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163BDF058E93B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD9833664156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F74650597441564112500996C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB3C4A418B99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C8F91415641579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1)
NOTICE:  recordset_object1:
(450,0103000020EB7A00000100000022000000CC72F1149A3C4A41000000002B415641295C8F229A3C4A4185EB51582B4156413E0AD7A3983C4A41000000002B415641351D7C68833C4A41000000002B415641F6285C2F7E3C4A410000001031415641B81E856B913C4A41A4703D6A354156413E0AD723AD3C4A41AE47E17A3B415641B81E850BAE3C4A4114AE47E13E415641E17A144EAF3C4A417B14AEE74341564152B81EA5B03C4A410AD7A3B04741564114AE4721BB3C4A41EC51B82E474156417B14AE67CC3C4A41AE47E17A46415641B81E852BD83C4A411F85EB014641564166666646D73C4A4185EB51B8454156413E0AD783D63C4A41666666664541564114AE4761D53C4A41B81E85DB44415641295C8F02D53C4A418FC2F548444156413E0AD703D53C4A41666666C6434156419A999979D53C4A41AE47E1CA424156410AD7A330D63C4A41713D0AA741415641A4703D8AD73C4A41B81E859B40415641A4703DEAD93C4A41E17A14CE3E415641713D0A17DB3C4A41B81E85DB3D415641CDCCCC8CDC3C4A41F6285CAF3C415641E17A148EDF3C4A41AE47E10A3B4156419A999959E13C4A410AD7A3103A4156413E0AD7E3E23C4A4148E17AC438415641A4703D0AE43C4A41AE47E13A38415641A4703DCAEB3C4A4114AE47213841564100000060EC3C4A413E0AD7D33241564114AE47E1E03C4A41D7A370AD324156419A999979E33C4A417B14AE772E415641882F554CE43C4A41000000002B415641CC72F1149A3C4A41000000002B415641)
NOTICE:  recordset_object2a:
(309108,0103000020EB7A000001000000220000009B8A50B33D3D4A410CFC87519141564121B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E9438704156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D7929CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A417049385130415641D1ABC0367F3C4A41000000002B4156415FC001E8EB3B4A41000000002B41564163BDF058E93B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD9833664156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F74650597441564112500996C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB3C4A418B99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C8F91415641579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,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,0103000020EB7A00000100000093000000A5D6BC7F113B4A41DA9C414B8C44564150FAAEDD133B4A419F49202D8B4456412FF0D7391B3B4A41168EC19286445641E5C111791C3B4A412DEE6FD88D44564193716BA71F3B4A41527950589B4456413706B5B4203B4A411CA7E1639F4456417F264CED223B4A410BDF111DA544564195B45007273B4A410C80CF17AE44564123394005293B4A41D9F53FC8B34456417B6AB9082D3B4A415F147142B4445641B5FC2FAC303B4A412935E853B4445641C323EFF1373B4A41C18C5DAAB34456412A8D25813F3B4A41C33BF4E5B2445641022A8ED84C3B4A4118E6FF5DB2445641F237A3BB5D3B4A414C7E863FB244564191C97352653B4A410E9B8A5CB1445641529A25D6633B4A41C47E2DB7AD445641B5D7BDEA5E3B4A4160BF3DD7A4445641064DA2335A3B4A41B397823D9B445641468081F5693B4A412AAF71059B445641745B60647B3B4A4188667A919A4456411E7579487A3B4A41BA466E559944564131F2D4F87E3B4A41C9563A27994456414A570C2C893B4A41CC1B91F797445641FAD2C8BCA33B4A419EEF66159544564194F74682CE3B4A41C8A7A28090445641F677AA51E53B4A41C3AC804A8E4456419D16B333023C4A4145B4DB368B4456412CD4AE17083C4A4172F829968A4456418E660AAB223C4A41424103B9874456414F5B3AF4233C4A4100517DEF92445641F472B68E233C4A41C56C4F989344564102FCF859223C4A415E932D9A95445641EFCC195F113C4A41DB4F155BA64456415457E1A40E3C4A4151BCC460A9445641115545C6083C4A41DC02CB19B14456418064136EF33B4A413F76BCD1AC445641DB6389DCF13B4A41BFE689EFB344564126807354EB3B4A4183EC2AACBD445641C5F5579DFA3B4A4121A624F3BF445641D717C6EBF73B4A41ABBB2195C744564161A21A04F33B4A41FB77D15FD5445641D0D839DB053C4A41B7831098D84456410A907C33073C4A414244DACED044564148A334A9163C4A41547CEC36D1445641BF7B60D5363C4A41BCF33468D1445641F68B9DFE393C4A411283312ADA445641B45CC31A4C3C4A41F6A929D1D744564185C3D2CE563C4A4146BC02D9D64456410E266F8E503C4A41256BD10AC544564178A04D1C4E3C4A416E50306BBD4456417ED898E54C3C4A41A3F1EA24B6445641C11890CB4D3C4A4185584DDFAE445641100E678C503C4A41447D8CC1A8445641C8EA7258533C4A41D025F919A4445641F45E60645B3C4A41F11D115B9B445641769EB3136B3C4A41DD06E5858D44564175B35E1A703C4A41CBE002D8874456418EE0827A743C4A41A399728D80445641C2DE7203773C4A4116C1F4C579445641BF3B192A783C4A41250884A571445641B465A72D773C4A41317A276E5C4456414FFAB66E6C3C4A41EE3D4D3554445641D8E3F8AF613C4A41CC142A074A445641C4DD74D9493C4A41E18223504A44564149E72F02203C4A416083AE4C4B445641BB020316163C4A417AD8A6804D44564115A108B7023C4A4133B9970352445641667D32D4FA3B4A413B9DE3AB52445641206FE7D0EF3B4A410F12217D524456416EAA545EE83B4A41E4AAF88E51445641C242D969DD3B4A41BC9873F84F44564159773A65CF3B4A410D0595C04B445641B4F85B10CF3B4A4187C6CA374A445641883CC8C0C73B4A419A306D07464456419595327EBB3B4A41A5F9E0843F445641A4113DFEA83B4A4120D2EB1E36445641576BBF8A9D3B4A41334EA22D30445641F52DD12E9C3B4A411BEEF45232445641A4E92DF3973B4A413F90FFD13444564143331E7A943B4A41F761391136445641A69D4CB1903B4A414DC4100837445641267AE7258C3B4A41A46AA98B37445641AFD254247C3B4A41E986946E384456417541D6286D3B4A416D1F52DB3944564158B4E7076B3B4A412A3AF20F3A4456419F2AFEC4623B4A41450828DC3A44564109C6A7D5613B4A416D1F52DB39445641E9BFB312583B4A413FC197913B44564137F356324D3B4A4145E438BF3E445641A69714F4243B4A41FF1C9DC249445641F8194AC2133B4A4129F0221B4F445641FA42FB2D133B4A41A2CCF75B54445641616D3114153B4A419CB61B4C58445641BF8B0E1D183B4A418E3130FA5B4456415BD1C57B1F3B4A41AE6C11205E445641EBC43854353B4A4112AAE30063445641F4DE50CD423B4A41B562C8B96C4456417E0F5D25413B4A41BA301B6C6D445641CBDFD71D343B4A41E66F8A9663445641805C16E81C3B4A41F872CAAB5E445641433BF7F3173B4A41BAB368095D4456413F29FF2F143B4A41D805CA755A445641C55526D1103B4A4158A7F41B554456415BF92622113B4A41885AA7994F4456416402759FF53A4A4116623BE750445641F6FFBDB0F23A4A415EB061555044564131A5403BE93A4A418CC63CFA4C4456412FFBA344E43A4A418D36F0724C4456415DB86386B33A4A41BFA5FE494D445641A5FD702CAD3A4A417D8DBFBC4D445641A2967FC9A33A4A41E508260650445641FAC39BD16C3A4A419ABBC11F5F445641E2C7B9E9683A4A413EFFA1685F44564170F2B498613A4A41F872CAAB5E44564192C7B713423A4A41D76C65915B445641F5A23B242A3A4A41F4F96F2D5A445641DAD3B5921B3A4A4168628F5858445641AF0DF4EC133A4A41F038A8CC56445641807AF58D0D3A4A41F80F4C3D56445641641AB90A073A4A412046A47557445641764EC9D5013A4A417192BBD758445641C1CD4D37FE394A418E16E7775B445641E07DDC1AFA394A418AE5C1E55E445641783329A1F1394A411EEC95816444564171FF88C2E8394A41BB6F1AAA694456411A500F6DD9394A414D4590527244564160B99CAEE8394A41D4C6E9F3744456411E15301DF4394A41E6F51C60794456416CD799DEF7394A41A3D453B37A4456411D105594103A4A41CC5FFA667E44564136C98BE8183A4A4147B12789804456414A49D33C213A4A41B381F8C8814456414633A250283A4A41AA794B4182445641223F70313C3A4A415F233FEA82445641B5899402483A4A410904F70B8344564117DDEFEC5C3A4A41E541706483445641E6247DF6713A4A417E1B589683445641A219CD66933A4A41372C3C378444564146FD76AECD3A4A41F5171BC28644564129A670D9D23A4A41BB8934F98744564136C565B1D63A4A4104824C3A89445641265EE8E6E03A4A41B0E45ABC8D44564141F89B25EA3A4A416D9A8BC792445641E1B6AC97EC3A4A41EE52A9E19444564164801FAAFA3A4A414E05B8289A445641A5D6BC7F113B4A41DA9C414B8C445641,1)
NOTICE:  recordset_object1:
(451,0103000020EB7A00000100000007000000F6285CEF5D3C4A413E0AD7134A445641D7A370DD733C4A411F85EB0159445641C2F5283C7C3C4A413E0AD72359445641EC51B8FE7A3C4A41D7A370FD47445641EC51B8FE733C4A413E0AD76348445641713D0A57663C4A418FC2F54849445641F6285CEF5D3C4A413E0AD7134A445641)

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;