Re: Reformatage inopiné des requêtes dans les vues

Bonjour,

Étant un peu neuf dans la communauté PostgreSQL et encore un peu
timidou, j'ai choisi de poster d'abord cette question ici, dans la
langue de Molière, afin que toutes les nuances de ma langue maternelle
puissent être perçues. J'ai bien conscience que ce message aurait plus
sa place sur les listes anglophones: plus tard, quand je serai
suffisamment aguerri, j'oserai m'y exprimer.

Voilà mon souci: quand je fais une requête qui me donne satisfaction et
que je veux l'immortaliser en tant que vue, je constate, depuis
longtemps, qu'une fois stockée, elle est quelque peu modifiée.

Un exemple, avec une table qui contient des coordonnées de points, et
une vue qui crée automagiquement des entités géographiques ponctuelles
liées dynamiquement aux coordonnées des attributs de la table (un peu
l'équivalent des bons vieux "event themes" de chez Arcview).

La table, avec quelques coordonnées (les deux derniers meetups où je me
suis rendus):
CREATE TABLE coords (id SERIAL PRIMARY KEY, x float, y float, z float,
srid integer);

INSERT INTO coords (x, y, z) VALUES
(1.3635448815, 43.6602665901, 196.50),
(1.5070172183, 43.5418566310, 193.80);
UPDATE coords SET srid = 4326;

Puis la vue:

CREATE VIEW coords_points AS
SELECT *,
GeomFromewkt(
'SRID='|| srid ||';
POINT(' ||
x || ' ' ||
y || ' ' ||
z ||
')'
)
FROM coords;

Vous aurez noté la mise en forme, qui met ici en évidence la
construction du WKT de la requête: c'est essentiellement à des fins
didactiques, quand je fais des cours. Je fais des mises en forme
purement cosmétiques, pour faciliter la relecture, assez systématiquement.

Mais quand je consulte la définition de ma vue:

pierre(at)bdexplo=> \d+ coords_points
Vue « pierre.coords_points »
Colonne | Type | Modificateurs | Stockage | Description
--------------+------------------+---------------+----------+-------------
id | integer | | plain |
x | double precision | | plain |
y | double precision | | plain |
z | double precision | | plain |
srid | integer | | plain |
geomfromewkt | geometry | | main |
Définition de la vue :
SELECT coords.id,
coords.x,
coords.y,
coords.z,
coords.srid,
geomfromewkt(((((((('SRID='::text || coords.srid) ||
';POINT('::text) || coords.x) || ' '::text) || coords.y) || ' '::text)
|| coords.z) || ')'::text) AS geomfromewkt
FROM coords;

, patatras: non solum ma belle (c'est subjectif) mise en forme a fichu
le camp, sed etiam il y a des cast que je trouve assez curieux, et des
parenthèses que je jugerais un peu superfétatoires. Et je trouve ça
assez illisible, pour la partie construction du WKT.

Je trouvais cela frustrant, et gardais donc toutes mes définitions de
vues dans des fichiers à part, les rejouant, quand le besoin s'en
faisait sentir. Mais cela s'est avéré fort embêtant dans certains cas,
par exemple quand d'autres instances de la même base, déployées chez des
clients, avaient eu des changements dans les vues. Dans ces cas, faire
des diffs sur les sources était un peu, laborieux, dirais-je.

J'ai parcouru ce fil:
http://postgresql.nabble.com/Preserving-the-source-code-of-views-td5775163.html
et j'ai bien pigé que postgres ne stockait en interne qu'une version
remoulinée du SQL qui lui était passé.

D'où mes questionnements; déjà: la situation a-t-elle évolué depuis
octobre 2013, date de la fin de la conversation mentionnée ci-dessus, ou
pas?
Je me demandais s'il n'y aurait pas moyen de stocker à la fois le SQL
pur, joliment formaté, dans un coin, tel qu'il a été rédigé par son
auteur humain, ET la définition telle qu'on la trouve maintenant,
fournie par, si j'ai bien suivi, le biais de pg_get_viewdef().

En espérant avoir été à peu près clair.

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre(dot)chevalier1967(at)jabber(dot)fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Bonjour,

pourquoi ne pas faire dans toutes les instances, un :
pg_dump -s mabase > export.sql

et ainsi pouvoir comparer les fichiers produits.

JLL

Le 09/03/2016 18:17, Pierre Chevalier Géologue a écrit :
> Bonjour,
>
> Étant un peu neuf dans la communauté PostgreSQL et encore un peu
> timidou, j'ai choisi de poster d'abord cette question ici, dans la
> langue de Molière, afin que toutes les nuances de ma langue maternelle
> puissent être perçues. J'ai bien conscience que ce message aurait plus
> sa place sur les listes anglophones: plus tard, quand je serai
> suffisamment aguerri, j'oserai m'y exprimer.
>
> Voilà mon souci: quand je fais une requête qui me donne satisfaction et
> que je veux l'immortaliser en tant que vue, je constate, depuis
> longtemps, qu'une fois stockée, elle est quelque peu modifiée.
>
>
> Un exemple, avec une table qui contient des coordonnées de points, et
> une vue qui crée automagiquement des entités géographiques ponctuelles
> liées dynamiquement aux coordonnées des attributs de la table (un peu
> l'équivalent des bons vieux "event themes" de chez Arcview).
>
> La table, avec quelques coordonnées (les deux derniers meetups où je me
> suis rendus):
> CREATE TABLE coords (id SERIAL PRIMARY KEY, x float, y float, z float,
> srid integer);
>
> INSERT INTO coords (x, y, z) VALUES
> (1.3635448815, 43.6602665901, 196.50),
> (1.5070172183, 43.5418566310, 193.80);
> UPDATE coords SET srid = 4326;
>
>
> Puis la vue:
>
> CREATE VIEW coords_points AS
> SELECT *,
> GeomFromewkt(
> 'SRID='|| srid ||';
> POINT(' ||
> x || ' ' ||
> y || ' ' ||
> z ||
> ')'
> )
> FROM coords;
>
>
> Vous aurez noté la mise en forme, qui met ici en évidence la
> construction du WKT de la requête: c'est essentiellement à des fins
> didactiques, quand je fais des cours. Je fais des mises en forme
> purement cosmétiques, pour faciliter la relecture, assez systématiquement.
>
> Mais quand je consulte la définition de ma vue:
>
> pierre(at)bdexplo=> \d+ coords_points
> Vue « pierre.coords_points »
> Colonne | Type | Modificateurs | Stockage | Description
> --------------+------------------+---------------+----------+-------------
> id | integer | | plain |
> x | double precision | | plain |
> y | double precision | | plain |
> z | double precision | | plain |
> srid | integer | | plain |
> geomfromewkt | geometry | | main |
> Définition de la vue :
> SELECT coords.id,
> coords.x,
> coords.y,
> coords.z,
> coords.srid,
> geomfromewkt(((((((('SRID='::text || coords.srid) ||
> ';POINT('::text) || coords.x) || ' '::text) || coords.y) || ' '::text)
> || coords.z) || ')'::text) AS geomfromewkt
> FROM coords;
>
>
> , patatras: non solum ma belle (c'est subjectif) mise en forme a fichu
> le camp, sed etiam il y a des cast que je trouve assez curieux, et des
> parenthèses que je jugerais un peu superfétatoires. Et je trouve ça
> assez illisible, pour la partie construction du WKT.
>
> Je trouvais cela frustrant, et gardais donc toutes mes définitions de
> vues dans des fichiers à part, les rejouant, quand le besoin s'en
> faisait sentir. Mais cela s'est avéré fort embêtant dans certains cas,
> par exemple quand d'autres instances de la même base, déployées chez des
> clients, avaient eu des changements dans les vues. Dans ces cas, faire
> des diffs sur les sources était un peu, laborieux, dirais-je.
>
> J'ai parcouru ce fil:
> http://postgresql.nabble.com/Preserving-the-source-code-of-views-td5775163.html
>
> et j'ai bien pigé que postgres ne stockait en interne qu'une version
> remoulinée du SQL qui lui était passé.
>
> D'où mes questionnements; déjà: la situation a-t-elle évolué depuis
> octobre 2013, date de la fin de la conversation mentionnée ci-dessus, ou
> pas?
> Je me demandais s'il n'y aurait pas moyen de stocker à la fois le SQL
> pur, joliment formaté, dans un coin, tel qu'il a été rédigé par son
> auteur humain, ET la définition telle qu'on la trouve maintenant,
> fournie par, si j'ai bien suivi, le biais de pg_get_viewdef().
>
> En espérant avoir été à peu près clair.
>
> À+
> Pierre

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Le Wed, 9 Mar 2016 18:17:29 +0100,
Pierre Chevalier Géologue <pierrechevaliergeol(at)free(dot)fr> a écrit :

> Je me demandais s'il n'y aurait pas moyen de stocker à la fois le SQL
> pur, joliment formaté, dans un coin, tel qu'il a été rédigé par son
> auteur humain,

vous pouvez attacher un commentaire aux objets (dont les vues).
Allez jeter un œil sur la commande COMMENT
(à http://docs.postgresql.fr/9.5/sql-comment.html par exemple)

Comme indiqué dans la doc le commentaire est récupérable avec certaines
commandes \d sous psql mais également via des fonctions dédiées:
http://docs.postgresql.fr/9.5/functions-info.html#functions-info-comment-table

--
Éric de la Musse

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Bonsoir,

Le 09/03/2016 18:50, Jean-Louis Louër a écrit :
> pourquoi ne pas faire dans toutes les instances, un :
> pg_dump -s mabase > export.sql
> et ainsi pouvoir comparer les fichiers produits.

C'est un peu ce que je faisais, oui: soit je faisais des \d
lavuesuspecte, soit des pg_dump en y greppant à l'envi. Mais c'était
tout de même très pénible à lire/comprendre.

L'exemple que j'ai pris où je construis dynamiquement un WKT se
reproduit souvent, avec des choses parfois un peu plus touffues, du genre:

CREATE OR REPLACE VIEW dh_traces_3d_20136 AS
SELECT *,
GeomFromEWKT(
'SRID=' || srid ||
';LINESTRING (' ||
x || ' ' ||
y || ' ' ||
z ||
', ' ||
x1 || ' ' ||
y1 || ' ' ||
z1 ||
')'
)
FROM (
SELECT *,
x + length * cos((dip_hz / 180) * pi())
* sin((azim_ng / 180) * pi())
AS x1,
y + length * cos((dip_hz / 180) * pi())
* cos((azim_ng / 180) * pi())
AS y1,
z - length * sin((dip_hz / 180) * pi())
AS z1
FROM dh_collars
WHERE srid = 20136
) tmp
ORDER BY tmp.id;

(il est possible que le formatage soit resté correct via l'émail; je ne
me limite bien souvent pas aux 80 caractères de largeur usuels)

Ce qui donne, quand on regarde la sortie du pg_dump:

CREATE VIEW dh_traces_3d_20136 AS
SELECT tmp.id,
tmp.shid,
tmp.location,
tmp.profile,
tmp.srid,
tmp.x,
tmp.y,
tmp.z,
tmp.azim_ng,
tmp.azim_nm,
tmp.dip_hz,
tmp.dh_type,
tmp.date_start,
tmp.contractor,
tmp.geologist,
tmp.length,
tmp.nb_samples,
tmp.comments,
tmp.completed,
tmp.numauto,
tmp.date_completed,
tmp.opid,
tmp.purpose,
tmp.x_local,
tmp.y_local,
tmp.z_local,
tmp.accusum,
tmp.id_pject,
tmp.x_pject,
tmp.y_pject,
tmp.z_pject,
tmp.topo_survey_type,
tmp.db_update_timestamp,
tmp.username,
tmp.datasource,
tmp.x1,
tmp.y1,
tmp.z1,
public.geomfromewkt((((((((((((((('SRID='::text || tmp.srid) ||
';LINESTRING ('::text) || tmp.x) || ' '::text) || tmp.y) || ' '::text)
|| tmp.z) || ', '::text) || tmp.x1) || ' '::text) || tmp.y1) || '
'::text) || tmp.z1) || ')'::text)) AS geomfromewkt
FROM ( SELECT dh_collars.id,
dh_collars.shid,
dh_collars.location,
dh_collars.profile,
dh_collars.srid,
dh_collars.x,
dh_collars.y,
dh_collars.z,
dh_collars.azim_ng,
dh_collars.azim_nm,
dh_collars.dip_hz,
dh_collars.dh_type,
dh_collars.date_start,
dh_collars.contractor,
dh_collars.geologist,
dh_collars.length,
dh_collars.nb_samples,
dh_collars.comments,
dh_collars.completed,
dh_collars.numauto,
dh_collars.date_completed,
dh_collars.opid,
dh_collars.purpose,
dh_collars.x_local,
dh_collars.y_local,
dh_collars.z_local,
dh_collars.accusum,
dh_collars.id_pject,
dh_collars.x_pject,
dh_collars.y_pject,
dh_collars.z_pject,
dh_collars.topo_survey_type,
dh_collars.db_update_timestamp,
dh_collars.username,
dh_collars.datasource,
((dh_collars.x)::double precision +
(((dh_collars.length)::double precision * cos((((dh_collars.dip_hz /
(180)::numeric))::double precision * pi()))) * sin((((dh_collars.azim_ng
/ (180)::numeric))::double precision * pi())))) AS x1,
((dh_collars.y)::double precision +
(((dh_collars.length)::double precision * cos((((dh_collars.dip_hz /
(180)::numeric))::double precision * pi()))) * cos((((dh_collars.azim_ng
/ (180)::numeric))::double precision * pi())))) AS y1,
((dh_collars.z)::double precision -
((dh_collars.length)::double precision * sin((((dh_collars.dip_hz /
(180)::numeric))::double precision * pi())))) AS z1
FROM dh_collars
WHERE (dh_collars.srid = 20136)) tmp
ORDER BY tmp.id;

C'est certes joliment présenté, mais bon, ça ne correspond pas à la
logique que j'avais initialement exprimée.
Et là, à détricoter, ça commence à devenir un peu plus velu.
L'absence de la conservation du joker * au début de la définition de la
vue est aussi un peu embêtant, dans la mesure où certains clients
rajoutaient des champs (ce qui ne simplifiait pas les choses). Avoir les
champs du * non "expanded" m'aurait ôté quelque peine dans les
traitements des diffs.

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre(dot)chevalier1967(at)jabber(dot)fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Le 09/03/2016 19:37, Éric de la Musse a écrit :
>> Je me demandais s'il n'y aurait pas moyen de stocker à la fois le SQL
>> pur, joliment formaté, dans un coin, tel qu'il a été rédigé par son
>> auteur humain,
>
> vous pouvez attacher un commentaire aux objets (dont les vues).

Oui, et je me force à faire cela systématiquement, mais je n'avais
-curieusement- jamais songé à faire un COMMENT sur une vue!

> Allez jeter un œil sur la commande COMMENT
> (à http://docs.postgresql.fr/9.5/sql-comment.html par exemple)
>
> Comme indiqué dans la doc le commentaire est récupérable avec certaines
> commandes \d sous psql mais également via des fonctions dédiées:
> http://docs.postgresql.fr/9.5/functions-info.html#functions-info-comment-table

D'ac, merci. Je vais bûcher ça.

Faire un petit wrapper autour du CREATE VIEW pour y stocker la
définition de la vue dans le commentaire pourrait en effet satisfaire
mon petit besoin.

Merci du conseil.

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre(dot)chevalier1967(at)jabber(dot)fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Bonjour,

Le 9 mars 2016 6:17 PM, "Pierre Chevalier Géologue" <
pierrechevaliergeol(at)free(dot)fr> a écrit :
>
> Bonjour,
>
> Étant un peu neuf dans la communauté PostgreSQL et encore un peu timidou,
j'ai choisi de poster d'abord cette question ici, dans la langue de
Molière, afin que toutes les nuances de ma langue maternelle puissent être
perçues. J'ai bien conscience que ce message aurait plus sa place sur les
listes anglophones: plus tard, quand je serai suffisamment aguerri,
j'oserai m'y exprimer.
>
> Voilà mon souci: quand je fais une requête qui me donne satisfaction et
que je veux l'immortaliser en tant que vue, je constate, depuis longtemps,
qu'une fois stockée, elle est quelque peu modifiée.
>
>
> Un exemple, avec une table qui contient des coordonnées de points, et une
vue qui crée automagiquement des entités géographiques ponctuelles liées
dynamiquement aux coordonnées des attributs de la table (un peu
l'équivalent des bons vieux "event themes" de chez Arcview).
>
> La table, avec quelques coordonnées (les deux derniers meetups où je me
suis rendus):
> CREATE TABLE coords (id SERIAL PRIMARY KEY, x float, y float, z float,
srid integer);
>
> INSERT INTO coords (x, y, z) VALUES
> (1.3635448815, 43.6602665901, 196.50),
> (1.5070172183, 43.5418566310, 193.80);
> UPDATE coords SET srid = 4326;
>
>
> Puis la vue:
>
> CREATE VIEW coords_points AS
> SELECT *,
> GeomFromewkt(
> 'SRID='|| srid ||';
> POINT(' ||
> x || ' ' ||
> y || ' ' ||
> z ||
> ')'
> )
> FROM coords;
>
>
> Vous aurez noté la mise en forme, qui met ici en évidence la construction
du WKT de la requête: c'est essentiellement à des fins didactiques, quand
je fais des cours. Je fais des mises en forme purement cosmétiques, pour
faciliter la relecture, assez systématiquement.
>
> Mais quand je consulte la définition de ma vue:
>
> pierre(at)bdexplo=> \d+ coords_points
> Vue « pierre.coords_points »
> Colonne | Type | Modificateurs | Stockage | Description
> --------------+------------------+---------------+----------+-------------
> id | integer | | plain |
> x | double precision | | plain |
> y | double precision | | plain |
> z | double precision | | plain |
> srid | integer | | plain |
> geomfromewkt | geometry | | main |
> Définition de la vue :
> SELECT coords.id,
> coords.x,
> coords.y,
> coords.z,
> coords.srid,
> geomfromewkt(((((((('SRID='::text || coords.srid) || ';POINT('::text)
|| coords.x) || ' '::text) || coords.y) || ' '::text) || coords.z) ||
')'::text) AS geomfromewkt
> FROM coords;
>
>
> , patatras: non solum ma belle (c'est subjectif) mise en forme a fichu le
camp, sed etiam il y a des cast que je trouve assez curieux, et des
parenthèses que je jugerais un peu superfétatoires. Et je trouve ça assez
illisible, pour la partie construction du WKT.
>
> Je trouvais cela frustrant, et gardais donc toutes mes définitions de
vues dans des fichiers à part, les rejouant, quand le besoin s'en faisait
sentir. Mais cela s'est avéré fort embêtant dans certains cas, par exemple
quand d'autres instances de la même base, déployées chez des clients,
avaient eu des changements dans les vues. Dans ces cas, faire des diffs sur
les sources était un peu, laborieux, dirais-je.
>
> J'ai parcouru ce fil:
>
http://postgresql.nabble.com/Preserving-the-source-code-of-views-td5775163.html
> et j'ai bien pigé que postgres ne stockait en interne qu'une version
remoulinée du SQL qui lui était passé.
>
> D'où mes questionnements; déjà: la situation a-t-elle évolué depuis
octobre 2013, date de la fin de la conversation mentionnée ci-dessus, ou
pas?
> Je me demandais s'il n'y aurait pas moyen de stocker à la fois le SQL
pur, joliment formaté, dans un coin, tel qu'il a été rédigé par son auteur
humain, ET la définition telle qu'on la trouve maintenant, fournie par, si
j'ai bien suivi, le biais de pg_get_viewdef().
>
> En espérant avoir été à peu près clair.
>

La solution généralement proposée est de placer les requetes DDL dans un
outil de gestion de sources (git par exemple).

Bonjour,

Le 10/03/2016 12:46, Guillaume Lelarge a écrit :
> La solution généralement proposée est de placer les requetes DDL dans un
> outil de gestion de sources (git par exemple).

Oui, c'est ce que je fais:
https://github.com/pierrechtux/geolllibre/blob/master/bdexplo_structure.sql
contient les définitions de ma base.

Mais c'est assez ch!4nt, quand il faut gérer ça avec un serveur en
production, où des modifs se font un peu "à l'arrache", qu'il faut faire
les DROP VIEWs dans un certain ordre, refaire les autres, qu'il faut
satisfaire les demandes du client toujours pressé, et qu'on se retrouve
à devoir, in fine, maintenir ce qui est dans le git à partir de ce qu'il
y a dans la base... ce qui est un peu le monde à l'envers... :-/

Voilà pourquoi avoir les définitions de vues stockées de la même manière
qu'on les a mises dans la base pourrait avoir un grand intérêt; de mon
humble petit point de vue, tout du moins.

À+
Pierre

PS: d'ailleurs, je viens de me rendre compte que le github est,
précisément, en "retard" par rapport à l'implémentation de ma base de
production... Hm. Mea culpa.
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre(dot)chevalier1967(at)jabber(dot)fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

Pierre Chevalier Géologue <pierrechevaliergeol(at)free(dot)fr> writes:
> Mais c'est assez ch!4nt, quand il faut gérer ça avec un serveur en
> production, où des modifs se font un peu "à l'arrache", qu'il faut faire les
> DROP VIEWs dans un certain ordre, refaire les autres, qu'il faut satisfaire
> les demandes du client toujours pressé, et qu'on se retrouve à devoir, in
> fine, maintenir ce qui est dans le git à partir de ce qu'il y a dans la
> base... ce qui est un peu le monde à l'envers... :-/

Il existe des outils qui permettent d'assister ce genre de manœuvre.
J'ai une tendance naturelle à être très prudent et à bien tout relire à
chaque fois, cependant ça semble tout à fait adapter au scénario :

http://apgdiff.com

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)