Forums PostgreSQL.fr

je sais tout ca, ma question est ce que on peut limiter cela a 30.

salut, ca marche, la requete n'est pas trop optimisé, mais ca pourra aider d'autre presonnes, et si vous aviez une version optimisé, svp, j'attend votre solution:

CREATE OR REPLACE FUNCTION bis_ca_prog_get_adress(
id integer, opostal_address_street character varying,opostal_address_city character varying,opostal_address_state character varying, opostal_address_postcode character varying,
omain_address_street character varying,omain_address_city character varying,omain_address_state character varying, omain_address_postcode character varying,
ipostal_address_street character varying,ipostal_address_city character varying,ipostal_address_state character varying, ipostal_address_postcode character varying,
imain_address_street character varying,imain_address_city character varying,imain_address_state character varying, imain_address_postcode character varying)
RETURNS SETOF address_ind_org_type AS
$BODY$
DECLARE
mon_record address_ind_org_type;
id ALIAS FOR $1;
request_orga int;
request_cont int;
BEGIN
request_orga := contact_organisation_stk_stakeholder_id from ca_programme where ca_programme_id = id;
request_cont := contact_stk_stakeholder_id from ca_programme where ca_programme_id = id;

  if request_orga is not null then
        if (opostal_address_street !='' or opostal_address_city !='' or opostal_address_state !='' or opostal_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.postal_address_street != '' then so2.postal_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.postal_address_city != '' then so2.postal_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.postal_address_state != '' then so2.postal_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.postal_address_postcode != '' then so2.postal_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
       
             elsif (omain_address_street !='' or omain_address_city !='' or omain_address_state !='' or omain_address_postcode !='') then
            FOR mon_record IN
        SELECT case when so2.main_address_street != '' then so2.main_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when so2.main_address_city != '' then so2.main_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when so2.main_address_state != '' then so2.main_address_state else '[INSERT THE STATE]' end as address_state,
               case when so2.main_address_postcode != '' then so2.main_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
         end if;
     

elsif (request_orga is null and request_cont is not null) then
        if (ipostal_address_street !='' or ipostal_address_city !='' or ipostal_address_state !='' or ipostal_address_postcode !='') then
            FOR mon_record IN
        SELECT case when i2.postal_address_street != '' then i2.postal_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when i2.postal_address_city != '' then i2.postal_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when i2.postal_address_state != '' then i2.postal_address_state else '[INSERT THE STATE]' end as address_state,
               case when i2.postal_address_postcode != '' then i2.postal_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
       
             elsif (imain_address_street !='' or imain_address_city !='' or imain_address_state !='' or imain_address_postcode !='') then
            FOR mon_record IN
        SELECT case when i2.main_address_street != '' then i2.main_address_street else '[INSERT THE STREET AND NUMBER]' end as address_street,
               case when i2.main_address_city != '' then i2.main_address_city else '[INSERT THE CITY/SUBURB]' end as address_city,
               case when i2.main_address_state != '' then i2.main_address_state else '[INSERT THE STATE]' end as address_state,
               case when i2.main_address_postcode != '' then i2.main_address_postcode else '[INSERT THE POSTAL CODE]' end as address_postcode
        FROM ca_programme p
        LEFT JOIN catalog_table fs ON p.ca_cat_fund = fs.catalog_table_id
        LEFT JOIN stk_stakeholder ss2 ON ss2.stk_stakeholder_id = p.contact_stk_stakeholder_id
        LEFT JOIN ind_individual i2 ON ss2.individual_id = i2.individual_id
        LEFT JOIN vw_stk_stakeholder vs2 ON vs2.stk_stakeholder_id = p.contact_organisation_stk_stakeholder_id
        LEFT JOIN stk_organisation so2 ON vs2.stk_organisation_id = so2.stk_organisation_id
        left join (
          SELECT * FROM cat_currency WHERE currency_country = (SELECT param_value FROM cat_global_params WHERE param_name = 'Country')
        ) bar on true
        WHERE ca_programme_id = id   
                loop
             return next mon_record;
                end loop;
        end if;    
       
else --if request_cont is null then
    FOR mon_record IN
        SELECT '[INSERT THE STREET AND NUMBER]' as address_street, '[INSERT THE CITY/SUBURB]' as address_city,'[INSERT THE STATE]' as address_state,
        '[INSERT THE POSTAL CODE]' as address_postcode
    loop
        return next mon_record;
        end loop;

end if;
       
return;
END
$BODY$
LANGUAGE plpgsql STABLE;
ALTER FUNCTION bis_ca_prog_get_adress(
id integer, postal_address_street character varying,postal_address_city character varying,postal_address_state character varying, postal_address_postcode character varying,
main_address_street character varying,main_address_city character varying,main_address_state character varying, main_address_postcode character varying)
  OWNER TO postgres;

Salut,
apres avoir installer strawberry perl, et télécharger pgbadger,
j'ai lancé l'outil perl(commande line) de strawberry.
et ensuite
C:\>cd pgbadger-master
C:\pgbadger-master>Makefile.PL
Cheicking if your kit is complete...
Writing Makefile for pgBadger
Writing MYMETA.yml and MYMETA.json

C:\pgbadger-master>Makefile
'Makefile' n'est pas reconnu en tant que commande interne ou externe,.......

Jai lu le readme sur https://github.com/dalibo/pgbadger
mais je ne vois pas comment générer les rapports.

MErci

Salut,
Il me semble que le problème ne vient pas forcement de la  car hier on a constaté une lenteur du serveur vers 19h, et j'ai vu dans les logs une 20taine de ce message de suite provenant de notre serveur web.
je pense que ce doit être le paramètre max-connexions qui a été dépassé par le nombre de connexion.
je vais augmenter ce paramètre de 100 a 150.
qu'est ce que tu en penses?