[postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX
Paragon Corporation
lr at pcorp.us
Thu Dec 3 11:20:44 PST 2009
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
More information about the postgis-users
mailing list