Fehler bei mehrmaligem Funktionsaufruf

Hallo,

ich habe gerade ein seltsames Problem. In meiner DB gibt es mehrere identische Schemas, d.h. gleiche Tabellen etc. Nun habe ich eine pl_pgsql Funktion,
die einen Eintrag in eine Tabelle macht, diese tabelle hat einen Fk-Constraint auf eine andere im gleichen Schema.
BSP :
create schema a;
create table a.company (id integer primary key , name varchar (100) );
create table a.company_objects(id serial, company_id integer references a.company(id) , obj_class varchar(100));

create schema b;
create table b.company (id integer primary key , name varchar (100) );
create table b.company_objects(id serial, company_id integer references b.company(id) , obj_class varchar(100));

create or replace function public.xxx( coid integer)
returns integer as $$ DECLARE newid integer;
BEGIN
insert into company_objects(company_id, obj_class) values (coid, ‘TEST’) returning id into newid;
return newid;
END;
$$ language plpgsql;

set search_path to a;
insert into company values (123,‘A’);

set search_path to b;
insert into company values (987,‘B’);

set search_path to a, public;
select xxx(123);

set search_path to b, public;
select xxx(987);

drop schema a cascade;
drop schema b cascade;
drop function public.xxx(integer);

============
Wenn ich das ausführe passiert das:
CREATE SCHEMA
psql:/tmp/xxx.sql:2: HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »company_pkey« für Tabelle »company«
CREATE TABLE
psql:/tmp/xxx.sql:3: HINWEIS: CREATE TABLE erstellt implizit eine Sequenz »company_objects_id_seq« für die »serial«-Spalte »company_objects.id«
CREATE TABLE
CREATE SCHEMA
psql:/tmp/xxx.sql:6: HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »company_pkey« für Tabelle »company«
CREATE TABLE
psql:/tmp/xxx.sql:7: HINWEIS: CREATE TABLE erstellt implizit eine Sequenz »company_objects_id_seq« für die »serial«-Spalte »company_objects.id«
CREATE TABLE
CREATE FUNCTION
SET
INSERT 0 1
SET
INSERT 0 1
SET
xxx

1
(1 Zeile)

SET
psql:/tmp/xxx.sql:28: FEHLER: Einfügen oder Aktualisieren in Tabelle »company_objects« verletzt Fremdschlüssel-Constraint »company_objects_company_id_fkey«
DETAIL: Schlüssel (company_id)=(987) ist nicht in Tabelle »company« vorhanden.
KONTEXT: SQL-Anweisung »insert into company_objects(company_id, obj_class) values (coid, ‘TEST’) returning id«
PL/pgSQL function xxx(integer) line 3 at SQL-Anweisung
psql:/tmp/xxx.sql:31: HINWEIS: Löschvorgang löscht ebenfalls 2 weitere Objekte
DETAIL: Löschvorgang löscht ebenfalls Tabelle a.company
Löschvorgang löscht ebenfalls Tabelle a.company_objects
DROP SCHEMA
psql:/tmp/xxx.sql:32: HINWEIS: Löschvorgang löscht ebenfalls 2 weitere Objekte
DETAIL: Löschvorgang löscht ebenfalls Tabelle company
Löschvorgang löscht ebenfalls Tabelle company_objects
DROP SCHEMA
DROP FUNCTION

=======================
Welche Erklärung gibt es dafür ?
Der erste Funktionsaufruf in der Session geht immer gut, der zweite mit geändertem search_path schlägt fehl.

Grüße Rolf
P.S. Wollte das Skript hier Anhängen, aber sql , txt sei nicht erlaubt :frowning:

Ich denke, Du hast das schon ganz gut erklärt. Zwischendrin den search_path zu ändern ist halt nicht nett. Wiederhole das noch mal, und nutze in der Funktion komplette Pfade.

Nun ja, “nicht nett” ist keine befriedigende Antwort im Zusammenhang mit IT-Systemen, es sollte hier ein dokumentiertes und reproduzierbares Verhalten geben. Reproduzierbar ist das ganze ja, aber dokumentiert nicht, zumindest habe ich nichts gefunden, was die Änderung von Einstellungen zwischen zwei Funktionsaufrufen verbietet. Daher würde ich das als Bug klassifizieren. Ich mach da mal einen Bug Report draus, mal sehen, was die meinen.
Grüße

Du erzeugst 2 Verzeichnisse, legst in einem eine Datei ab und rufst im anderen Ordner stehend und nur mit relativem Pfad cat ./ auf und wunderst Dich, daß es die Datei nicht findet? So gesehen sind alle Betriebssysteme buggy.

Hier zu Info meine Antwort auf meinen Bug_Report :
This example works as you’re expecting in 9.3 and up, as per this 9.3
release note item:

Force cached plans to be replanned if the search_path changes (Tom Lane)

Previously, cached plans already generated in the current session
were not redone if the query was re-executed with a new
search_path setting, resulting in surprising behavior.

So in previous versions, the function latches onto whichever
company_objects table it sees during its first execution. Although that’s
arguably a bug, we felt it was not safe to change the behavior so
fundamentally in minor releases. 9.2.x will not get changed in this way.

regards, tom lane

grüße Rolf

Cool :wink: