[postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX
Kevin Neufeld
kneufeld at refractions.net
Thu Dec 3 11:33:21 PST 2009
Yeah, I agree. What I've done to get around the caching problem that seems to work is to define all table names as
variables at the top of the function. All the sql statements used throughout the function then reference a variable
instead of an actual table. The planner can't cache the query plan since the query is adhoc ... no OID referencing problem.
-- Kevin
Paragon Corporation wrote:
> 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list