Обсуждение: cannot restore a view after a dump

Поиск

Список

Период

Сортировка

cannot restore a view after a dump

От

Marc Cousin

Дата:

04 апреля 2008 г., 08:22:38

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.operatingsystemAS "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.operatingsystemAS "UC-SE", systeme.servicepack AS "UC-SEVERS",  
processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP",
(((processeurs.speedregistry)::doubleprecision / (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_systemeresume 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_physiquesGROUP 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 ...

Re: cannot restore a view after a dump

От

Marc Cousin

Дата:

04 апреля 2008 г., 18:13:58

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[])
THEN1 ELSE NULL::integer END AS "UC-SIMM" FROM (SELECT  
winaudit_management_systeme_memoire.computer, group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS
devicenumberFROM  
winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER BY
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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 ...

Re: cannot restore a view after a dump

От

Marc Cousin

Дата:

08 апреля 2008 г., 07:12:15

Hi,

Sorry to post again, but I feel this issue is a bit strange and I'd like to
understand it. The problem is that I've got the same query that runs
sometimes, and fails with a syntax error at other times... It's the first
time I've seen it, and I've been using PostgreSQL for a while now ...

Thanks in advance.


On Friday 04 April 2008 15:21:52 Marc Cousin wrote:
> 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
>

Re: cannot restore a view after a dump

От

Marc Cousin

Дата:

08 апреля 2008 г., 14:01:20

I didn't put it in, pg_dump dit it for me... it seems that when a view has a
distinct, the dumped view has automatically the order by. that's what
triggered the whole problem.

But the query is supposed to be the same with the order by, because of the
distinct, so pg_dump is not wrong.

It looks like a parsing problem to me (as it sometimes work and sometimes not
with exactly the same query)


On Tuesday 08 April 2008 15:24:33 Sergio Gabriel Rodriguez wrote:
> May be your problem is ORDER BY in a view, try to delete ORDER BY clause
>
> Sergio.
>
> On Tue, Apr 8, 2008 at 4:11 AM, Marc Cousin <mcousin@sigma.fr> wrote:
> > Hi,
> >
> >  Sorry to post again, but I feel this issue is a bit strange and I'd like
> > to understand it. The problem is that I've got the same query that runs
> > sometimes, and fails with a syntax error at other times... It's the first
> > time I've seen it, and I've been using PostgreSQL for a while now ...
> >
> >  Thanks in advance.
> >
> >  On Friday 04 April 2008 15:21:52 Marc Cousin wrote:
> >  > 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
> >
> >  --
> >  Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> >  To make changes to your subscription:
> >  http://www.postgresql.org/mailpref/pgsql-admin

Re: cannot restore a view after a dump

От

Tom Lane

Дата:

08 апреля 2008 г., 14:07:13

Marc Cousin <mcousin@sigma.fr> writes:
> Sorry to post again, but I feel this issue is a bit strange and I'd like to
> understand it. The problem is that I've got the same query that runs
> sometimes, and fails with a syntax error at other times... It's the first
> time I've seen it, and I've been using PostgreSQL for a while now ...

Oh, sorry for not replying directly to you.  This was analyzed over in
-hackers and we now understand the cause, but haven't a fix quite yet:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00278.php

It's been broken since approximately forever, so if you'd been using
that view definition for awhile I'm surprised you didn't run into the
problem before.

The reason for the erratic behavior is that it depends on what happened
to already be in the chunks of memory used to form the array constants.
In a freshly-started backend the view creation would probably always
succeed, but as soon as memory has gotten dirtied a bit, maybe not.

            regards, tom lane

Re: cannot restore a view after a dump

От

Tom Lane

Дата:

11 апреля 2008 г., 23:01:14

Marc Cousin <mcousin@sigma.fr> writes:
> I'm having a strange problem : I created a view in a database, and I cannot restore it after a pg_dump.
> ...
> ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

You need this patch:

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?r1=1.140&r2=1.140.2.1

I found that this problem was introduced in 8.2, which may explain why
you'd not seen it before, if you were previously using an older release.

            regards, tom lane

Re: cannot restore a view after a dump

От

Marc Cousin

Дата:

22 апреля 2008 г., 09:45:22

I've installed it and it works. Thanks a lot.

On Saturday 12 April 2008 01:01:06 Tom Lane wrote:
> Marc Cousin <mcousin@sigma.fr> writes:
> > I'm having a strange problem : I created a view in a database, and I
> > cannot restore it after a pg_dump. ...
> > ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select
> > list
>
> You need this patch:
>
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayf
>uncs.c?r1=1.140&r2=1.140.2.1
>
> I found that this problem was introduced in 8.2, which may explain why
> you'd not seen it before, if you were previously using an older release.
>
>             regards, tom lane

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: cannot restore a view after a dump

cannot restore a view after a dump

Re: cannot restore a view after a dump

Re: cannot restore a view after a dump

Re: cannot restore a view after a dump

Re: cannot restore a view after a dump

Re: cannot restore a view after a dump

Re: cannot restore a view after a dump