Skip site navigation (1) Skip section navigation (2)

Re: cannot restore a view after a dump

From: Marc Cousin <mcousin(at)sigma(dot)fr>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: cannot restore a view after a dump
Date: 2008-04-04 13:21:52
Message-ID: 200804041521.52137.mcousin@sigma.fr (view raw or flat)
Thread:
Lists: pgsql-admin
I've forgotten to add this information :

Version : 
infocentre_dte=# SELECT * from version();
                                         version
------------------------------------------------------------------------------------------
 PostgreSQL 8.3.1 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Debian 4.2.3-2)

It's from a x86_64 debian sid...



I've narrowed it down to a simple test case... it doesn't seem to be linked with pg_dump but with the parsing of the query :

SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM (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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
ERREUR:  pour SELECT DISTINCT, ORDER BY, les expressions doivent apparaître dans la
liste SELECT

infocentre_dte=# SET lc_messages to 'C';
SET

infocentre_dte=# SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END 
AS "UC-SIMM" FROM (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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
 UC-SIMM
---------
       2

(2 rows)


The SQL is exactly the same (it's the same query I've run twice with the up arrow in psql ...)



I've continued playing with it : adding the create view works then, than after some time fails again :

infocentre_dte=# CREATE VIEW v_test_marc AS
    SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM 
(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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
CREATE VIEW
infocentre_dte=# DROP VIEW v_test_marc ;
DROP VIEW
infocentre_dte=# CREATE VIEW v_test_marc AS
    SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM 
(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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
CREATE VIEW
infocentre_dte=# DROP VIEW v_test_marc ;
DROP VIEW
infocentre_dte=# CREATE VIEW v_test_marc AS
    SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM 
(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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
CREATE VIEW
infocentre_dte=# DROP VIEW v_test_marc ;
DROP VIEW
infocentre_dte=# DROP VIEW v_test_marc ;
ERROR:  view "v_test_marc" does not exist
infocentre_dte=# CREATE VIEW v_test_marc AS
    SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM 
(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 ORDER BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) 
THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list


On Friday 04 April 2008 10:22:31 Marc Cousin wrote:
> 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 ...



In response to

Responses

pgsql-admin by date

Next:From: Sonia Perez PeñaDate: 2008-04-04 14:58:03
Subject: RV: instalacion del PHPPGADMIN
Previous:From: Jignesh K. ShahDate: 2008-04-04 13:14:02
Subject: Re: Postgres and SUN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group