Forums PostgreSQL.fr

domleg

Membre

erreur pg_dump vue matérialisée avec function language SQL hors schéma

Bonjour,
un collège rencontre un problème lors de la restauration (à partir d'un fichier issu de pg_dump) d'une vue matérialisée exprimée en langage SQL et utilisant une fonction qui n'est ni dans le schéma en cours ni dans le catalogue.
Je vais essayer d'être + clair avec un exemple utilisant la fonction st_intersects de postgis mais le problème est + général

--postgresql 9.4.4
--installation extension postgis 2.1.8 dans schéma postgis
--création d'un utilisateur et d'un schéma du même nom
--le search path par défaut inclut $user et postgis
--connexion utilisateur tout juste créé
CREATE TABLE t1
(
  id bigint,
  geom geometry(Point,4326)
);
insert into t1 values (1,ST_GeomFromEWKT('SRID=4326;POINT(0 30)'));
insert into t1 values (2,ST_GeomFromEWKT('SRID=4326;POINT(1 43)'));
create materialized view v1 as select t1.id,t1.geom from t1 where st_intersects(t1.geom,ST_GeomFromEWKT('SRID=4326;POLYGON((0 40,4 40,4 50,0 50,0 40))'));
--export (pg_dump) du schéma

--destruction du schéma
--import depuis superutilisateur
--erreur sur la création de la vue matérialisée

ERROR:  operator does not exist: postgis.geometry && postgis.geometry
LINE 1: SELECT $1 && $2 AND _ST_Intersects($1,$2)
                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT $1 && $2 AND _ST_Intersects($1,$2)
CONTEXT:  SQL function "st_intersects" during inlining

Nous avons contourné le problème en modifiant manuellement le search path dans le script pour y ajouter le schéma postgis, néanmoins ce n'est qu'un pis aller.

N'ayant pas vu comment résoudre d'une manière plus définitive la difficulté, nous nous tournons vers la communauté en espérant ne pas rabâcher une question mille fois posée.

Merci à tous

Dominique

domleg

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Désolé, je me rends compte que j'ai été un peu trop succinct.
Je voulais écrire :
d'une vue matérialisée utilisant une fonction exprimée en langage SQL qui utilise elle-même une fonction qui n'est ni dans le schéma en cours ni dans le catalogue.

merci d'avance pour votre aide.

Marc Cousin

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

C'est étrange. La vue matérialisée doit stocker une définition de la vue, telle que retranscrite par PostgreSQL.

Vous devriez pouvoir la voir avec un \d+ ma_vue, dans psql. Pouvez-vous déjà nous dire ce qui s'y trouve ?

---

Marc.

domleg

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

merci pour cette réponse.
En premier lieu je voudrais préciser que le problème n'existe que si la vue est matérialisée, pour une vue classique tout va bien.

Voici la réponse au \d+

                           Materialized view "bss_p.v1"
 Column |         Type         | Modifiers | Storage | Stats target | Description
--------+----------------------+-----------+---------+--------------+-------------
 id     | bigint               |           | plain   |              |
 geom   | geometry(Point,4326) |           | main    |              |
View definition:
 SELECT t1.id,
    t1.geom
   FROM bss_p.t1
  WHERE st_intersects(t1.geom, st_geomfromewkt('SRID=4326;POLYGON((0 40,4 40,4 50,0 50,0 40))'::text));

le script extrait du pg_dump à plat est le suivant :

CREATE MATERIALIZED VIEW v1 AS
 SELECT t1.id,
    t1.geom
   FROM t1
  WHERE postgis.st_intersects(t1.geom, postgis.st_geomfromewkt('SRID=4326;POLYGON((0 40,4 40,4 50,0 50,0 40))'::text))
  WITH NO DATA;

avec un REFRESH MATERIALIZED VIEW v1 en fin de fichier

Le problème n'est aussi présent que si la fonction (ici st_intersects) est une fonction "language SQL" et donc définie par une chaîne de caractères.

Merci pour votre attention

Dominique

Marc Cousin

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Ok. Le problème, c'est que la fonction SQL st_intersects n'a pas de schéma indiqué. Et que donc si le search_path n'est pas bon, elle ne peut pas être utilisée (pour créer la vue matérialisée). C'est un bug dans PostGIS, en l'état, l'extension ne devrait pas être «relocatable» (c'est à dire permettre le changement de schéma), vu que ça veut dire que le comportement de la fonction dépend de search_path, ce qui est quand même plutôt dangereux.

Il y a une discussion sur le sujet : http://lists.osgeo.org/pipermail/postgi … 24796.html , mais je n'ai pas vu de correctif...

Pour le moment, à part avoir les vues matérialisées dans le même schéma que Postgis, je ne vois pas de solution simple.

---

Marc.

domleg

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Bonjour et merci pour cette réponse qui conforte notre propre analyse.
Cependant, je pense que le problème n'est pas limité à Postgis, même si j'ai utilisé une fonction Postgis comme exemple.
voici un autre exemple :

--connexion postgres (superutilisateur)
CREATE SCHEMA s1;
GRANT USAGE ON SCHEMA s1 TO public;

CREATE FUNCTION s1.ajouteun(IN entree integer) RETURNS integer AS
'select $1 +1'
LANGUAGE sql STABLE;
GRANT EXECUTE ON FUNCTION s1.ajouteun(integer) TO public;

CREATE SCHEMA s2;
GRANT USAGE ON SCHEMA s2 TO public;

set search_path to s2,s1;
CREATE FUNCTION s2.ajoutedeux(IN entree integer) RETURNS integer AS
'select ajouteun($1) +1'
LANGUAGE sql STABLE;
GRANT EXECUTE ON FUNCTION s2.ajoutedeux(integer) TO public;

CREATE ROLE u3 LOGIN;
CREATE SCHEMA u3 AUTHORIZATION u3;

--connexion u3
set search_path to u3,s2,s1;
create table t1 (valeur integer);
create materialized view v1 as select valeur from t1 where ajoutedeux(valeur) > 0 ;

et le script issu de pg_dump du schéma u3 (extrait)

....
CREATE SCHEMA u3;


ALTER SCHEMA u3 OWNER TO u3;

SET search_path = u3, pg_catalog;
...
CREATE TABLE t1 (
    valeur integer
);
...
CREATE MATERIALIZED VIEW v1 AS
 SELECT t1.valeur
   FROM t1
  WHERE (s2.ajoutedeux(t1.valeur) > 0)
  WITH NO DATA;
...

provoque une erreur

ERROR:  function ajouteun(integer) does not exist
LINE 1: select ajouteun($1) +1
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  select ajouteun($1) +1
CONTEXT:  SQL function "ajoutedeux" during inlining

Excellente journée et une fois encore merci pour votre aide

Dominique

Marc Cousin

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Non, ce n'est pas un problème limité à PostGIS. Simplement, avoir des appels de fonctions dont le schéma n'est pas spécifié dans une fonction (SQL ou autre), c'est chercher les ennuis (pour la sécurité entre autres): leur exécution dépend de l'environnement. On peut donc détourner complètement une fonction en changeant son search_path. C'est pour ça qu'il vaut mieux le forcer dans la déclaration de la fonction dans ce cas.

Ça se voit bien plus nettement dans les fonctions SQL: comme elles sont inlinées, les appeler à la création de la vue fait que PostgreSQL essaye de résoudre et inliner le bout de SQL de la fonction dans la vue matérialisée… et ça casse.

---

Marc.

domleg

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Suis tout à fait d'accord, je recommande très très fortement de spécifier explicitement le schéma de tous les objets.
Cependant, les développeurs étant ce qu'ils sont, je crois qu'une action au niveau de PostgreSQL serait la bienvenue.
Elle pourrait avoir lieu lors de la création d'une fonction "language SQL" par exemple en modifiant le texte fourni par l'utilisateur pour y ajouter le nom du schéma pour tous les objets utilisés par le select, table, vue ou fonction. C'est la même démarche que celle utilisée pour les vues, l'utilisateur n'est pas obligé de préciser un schéma pour spécifier un objet, mais si le schéma n'est pas fourni, il est alors automatiquement ajouté.

Marc Cousin

Membre

Re : erreur pg_dump vue matérialisée avec function language SQL hors schéma

Difficile, ça casserait une quantité énorme de code existant. Et on aurait le même problème avec les autres langages PL de toutes façons.

---

Marc.