pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc Cousin <mcousin@sigma.fr>
To: pgsql-admin@postgresql.org
Subject: cannot restore a view after a dump
Date: Fri, 4 Apr 2008 10:22:31 +0200
Message-ID: <200804041022.31746.mcousin@sigma.fr> (raw)

Hi,

I'm having a strange problem : I created a view in a database, and I cannot restore it after a pg_dump.



I'm creating this view :

CREATE VIEW vj_icsi_integration_winaudit_isiparc AS
SELECT DISTINCT objet.c_barre                   ,
        resume.computer                         ,
        resume.site                             ,
        (resume.loctime)::DATE                                                                                  AS "DATEINVENLOG",
        resume.operatingsystem                                                                                  AS "UC-SE"       ,
        systeme.servicepack                                                                                     AS "UC-SEVERS"   ,
        processeurs.nbprocessor                                                                                 AS "UC-CPUNB"    ,
        resume.processordescription                                                                             AS "UC-CPUTYP"   ,
        (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISION) / (1024)::DOUBLE PRECISION) AS "UC-CPUVIT"   ,
        disques.disknumber                                                                                      AS "UC-DDNB"     ,
        ROUND(((((resume.totalharddrive / 1024) / 1024) / 1024))::DOUBLE PRECISION)                             AS "UC-DDESP"    ,
        CASE
                WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[])
                THEN 6
                WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
                THEN 5
                WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
                THEN 4
                WHEN (memoire.devicenumber = '{1,2,3}'::text[])
                THEN 3
                WHEN (memoire.devicenumber = '{1,2}'::text[])
                THEN 2
                WHEN (memoire.devicenumber = '{1}'::text[])
                THEN 1
                ELSE NULL::INTEGER
        END                                  AS "UC-SIMM"  ,
        ((resume.totalmemory / 1024) / 1024) AS "UC-RAM"   ,
        reseau.ipaddress[1]                  AS "UC-CRIP"  ,
        reseau.ipaddress[2]                  AS "UC-CRIP_1",
        reseau.ipaddress[3]                  AS "UC-CRIP_2",
        CASE
                WHEN (reseau.dhcpipaddress IS NOT NULL)
                THEN 'oui'::text
                ELSE 'non'::text
        END AS "UC-DHCP"
FROM ((((((winaudit.winaudit_resum_systeme resume
        JOIN winaudit.winaudit_systeme_exploitation systeme
        ON ((resume.computer = systeme.computer)))
        JOIN
                (SELECT winaudit_reseau.computer                           ,
                        group_array(winaudit_reseau.ipaddress)              AS ipaddress,
                        group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress
                FROM    winaudit.winaudit_reseau
                GROUP BY winaudit_reseau.computer
                ) reseau
        ON ((resume.computer = reseau.computer)))
        JOIN
                (SELECT winaudit_processeurs.computer                               ,
                        MAX(winaudit_processeurs.processornumber)   AS processornumber,
                        COUNT(winaudit_processeurs.processornumber) AS nbprocessor    ,
                        MAX(winaudit_processeurs.speedregistry)     AS speedregistry
                FROM    winaudit.winaudit_processeurs
                GROUP BY winaudit_processeurs.computer
                ) processeurs
        ON ((resume.computer = processeurs.computer)))
        JOIN
                (SELECT winaudit_management_systeme_memoire.computer,
                        group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS devicenumber
                FROM    winaudit.winaudit_management_systeme_memoire
                GROUP BY winaudit_management_systeme_memoire.computer
                ) memoire
        ON ((resume.computer = memoire.computer)))
        JOIN
                (SELECT winaudit_disques_physiques.computer,
                        COUNT(winaudit_disques_physiques.disknumber) AS disknumber
                FROM    winaudit.winaudit_disques_physiques
                GROUP BY winaudit_disques_physiques.computer
                ) disques
        ON ((resume.computer = disques.computer)))
        LEFT JOIN isilog.objet
        ON ((resume.computername = (objet.i_ob_nom)::text)))
ORDER BY objet.c_barre                                                                                         ,
        resume.computer                                                                                        ,
        resume.site                                                                                            ,
        (resume.loctime)::DATE                                                                                 ,
        resume.operatingsystem                                                                                 ,
        systeme.servicepack                                                                                    ,
        processeurs.nbprocessor                                                                                ,
        resume.processordescription                                                                            ,
        (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISION) / (1024)::DOUBLE PRECISION),
        disques.disknumber                                                                                     ,
        ROUND(((((resume.totalharddrive / 1024) / 1024) / 1024))::DOUBLE PRECISION)                            ,
        CASE
                WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[])
                THEN 6
                WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
                THEN 5
                WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
                THEN 4
                WHEN (memoire.devicenumber = '{1,2,3}'::text[])
                THEN 3
                WHEN (memoire.devicenumber = '{1,2}'::text[])
                THEN 2
                WHEN (memoire.devicenumber = '{1}'::text[])
                THEN 1
                ELSE NULL::INTEGER
        END                                 ,
        ((resume.totalmemory / 1024) / 1024),
        reseau.ipaddress[1]                 ,
        reseau.ipaddress[2]                 ,
        reseau.ipaddress[3]                 ,
        CASE
                WHEN (reseau.dhcpipaddress IS NOT NULL)
                THEN 'oui'::text
                ELSE 'non'::text
        END;

(I know it's ugly, but the source database is ugly too :( )


Here's the result from \d on this view :

 SELECT DISTINCT objet.c_barre, resume.computer, resume.site, resume.loctime::date AS "DATEINVENLOG", resume.operatingsystem AS "UC-SE", systeme.servicepack AS "UC-SEVERS", 
processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP", processeurs.speedregistry::double precision / 1024::double precision / 1024::double precision 
AS "UC-CPUVIT", disques.disknumber AS "UC-DDNB", round((resume.totalharddrive / 1024 / 1024 / 1024)::double precision) AS "UC-DDESP",
        CASE
            WHEN memoire.devicenumber = '{1,2,3,4,5,6}'::text[] THEN 6
            WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
            WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
            WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
            WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
            WHEN memoire.devicenumber = '{1}'::text[] THEN 1
            ELSE NULL::integer
        END AS "UC-SIMM", resume.totalmemory / 1024 / 1024 AS "UC-RAM", reseau.ipaddress[1] AS "UC-CRIP", reseau.ipaddress[2] AS "UC-CRIP_1", reseau.ipaddress[3] AS "UC-CRIP_2",
        CASE
            WHEN reseau.dhcpipaddress IS NOT NULL THEN 'oui'::text
            ELSE 'non'::text
        END AS "UC-DHCP"
   FROM winaudit_resum_systeme resume
   JOIN winaudit_systeme_exploitation systeme ON resume.computer = systeme.computer
   JOIN ( SELECT winaudit_reseau.computer, group_array(winaudit_reseau.ipaddress) AS ipaddress, group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress
      FROM winaudit_reseau
     GROUP BY winaudit_reseau.computer) reseau ON resume.computer = reseau.computer
   JOIN ( SELECT winaudit_processeurs.computer, max(winaudit_processeurs.processornumber) AS processornumber, count(winaudit_processeurs.processornumber) AS nbprocessor, 
max(winaudit_processeurs.speedregistry) AS speedregistry
   FROM winaudit_processeurs
  GROUP BY winaudit_processeurs.computer) processeurs ON resume.computer = processeurs.computer
   JOIN ( SELECT winaudit_management_systeme_memoire.computer, group_array(winaudit_management_systeme_memoire.devicenumber::text) AS devicenumber
   FROM winaudit_management_systeme_memoire
  GROUP BY winaudit_management_systeme_memoire.computer) memoire ON resume.computer = memoire.computer
   JOIN ( SELECT winaudit_disques_physiques.computer, count(winaudit_disques_physiques.disknumber) AS disknumber
   FROM winaudit_disques_physiques
  GROUP BY winaudit_disques_physiques.computer) disques ON resume.computer = disques.computer
   LEFT JOIN objet ON resume.computername = objet.i_ob_nom::text
  ORDER BY objet.c_barre, resume.computer, resume.site, resume.loctime::date, resume.operatingsystem, systeme.servicepack, processeurs.nbprocessor, resume.processordescription, 
processeurs.speedregistry::double precision / 1024::double precision / 1024::double precision, disques.disknumber, round((resume.totalharddrive / 1024 / 1024 / 1024)::double precision),
CASE
    WHEN memoire.devicenumber = '{1,2,3,4,5,6}'::text[] THEN 6
    WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
    WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
    WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
    WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
    WHEN memoire.devicenumber = '{1}'::text[] THEN 1
    ELSE NULL::integer
END, resume.totalmemory / 1024 / 1024, reseau.ipaddress[1], reseau.ipaddress[2], reseau.ipaddress[3],
CASE
    WHEN reseau.dhcpipaddress IS NOT NULL THEN 'oui'::text
    ELSE 'non'::text
END;


Notice postgreSQL added an order by ...

pg_dump gives me this :

CREATE VIEW vj_icsi_integration_winaudit_isiparc AS
    SELECT DISTINCT objet.c_barre, resume.computer, resume.site, (resume.loctime)::date AS "DATEINVENLOG", resume.operatingsystem AS "UC-SE", systeme.servicepack AS "UC-SEVERS", 
processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP", (((processeurs.speedregistry)::double precision / (1024)::double precision) / (1024)::double precision) 
AS "UC-CPUVIT", disques.disknumber AS "UC-DDNB", round(((((resume.totalharddrive / 1024) / 1024) / 1024))::double precision) AS "UC-DDESP", CASE WHEN (memoire.devicenumber 
= '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber 
= '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM", 
((resume.totalmemory / 1024) / 1024) AS "UC-RAM", reseau.ipaddress[1] AS "UC-CRIP", reseau.ipaddress[2] AS "UC-CRIP_1", reseau.ipaddress[3] AS "UC-CRIP_2", CASE WHEN 
(reseau.dhcpipaddress IS NOT NULL) THEN 'oui'::text ELSE 'non'::text END AS "UC-DHCP" FROM ((((((winaudit.winaudit_resum_systeme resume JOIN winaudit.winaudit_systeme_exploitation 
systeme ON ((resume.computer = systeme.computer))) JOIN (SELECT winaudit_reseau.computer, group_array(winaudit_reseau.ipaddress) AS ipaddress, 
group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress FROM winaudit.winaudit_reseau GROUP BY winaudit_reseau.computer) reseau ON ((resume.computer = 
reseau.computer))) JOIN (SELECT winaudit_processeurs.computer, max(winaudit_processeurs.processornumber) AS processornumber, count(winaudit_processeurs.processornumber) AS 
nbprocessor, max(winaudit_processeurs.speedregistry) AS speedregistry FROM winaudit.winaudit_processeurs GROUP BY winaudit_processeurs.computer) processeurs ON ((resume.computer = 
processeurs.computer))) JOIN (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS devicenumber 
FROM winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer) memoire ON ((resume.computer = memoire.computer))) JOIN (SELECT 
winaudit_disques_physiques.computer, count(winaudit_disques_physiques.disknumber) AS disknumber FROM winaudit.winaudit_disques_physiques GROUP BY 
winaudit_disques_physiques.computer) disques ON ((resume.computer = disques.computer))) LEFT JOIN isilog.objet ON ((resume.computername = (objet.i_ob_nom)::text))) ORDER BY 
objet.c_barre, resume.computer, resume.site, (resume.loctime)::date, resume.operatingsystem, systeme.servicepack, processeurs.nbprocessor, resume.processordescription, 
(((processeurs.speedregistry)::double precision / (1024)::double precision) / (1024)::double precision), disques.disknumber, round(((((resume.totalharddrive / 1024) / 1024) / 1024))::double 
precision), CASE WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber 
= '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) 
THEN 1 ELSE NULL::integer END, ((resume.totalmemory / 1024) / 1024), reseau.ipaddress[1], reseau.ipaddress[2], reseau.ipaddress[3], CASE WHEN (reseau.dhcpipaddress IS NOT NULL) 
THEN 'oui'::text ELSE 'non'::text END;

And when I try to restore it, here's what I've got :

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list




I'm a bit lost on this ...

Can anyone provide some help ?

Thanks a lot ...



view thread (7+ messages)  latest in thread

Message-ID: <200804041022.31746.mcousin@sigma.fr>
Permalink:  ../200804041022.31746.mcousin@sigma.fr/
Also on:    postgresql.org/message-id/200804041022.31746.mcousin@sigma.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: mcousin@sigma.fr
  Subject: Re: cannot restore a view after a dump
  In-Reply-To: <200804041022.31746.mcousin@sigma.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox