Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id E56B12E0094 for ; Fri, 4 Apr 2008 15:13:55 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 31415-05-4 for ; Fri, 4 Apr 2008 15:13:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gslimes3.sigma.fr (mx.sigma.fr [194.50.39.191]) by developer.postgresql.org (Postfix) with ESMTP id 468BC2E0100 for ; Fri, 4 Apr 2008 15:09:54 -0300 (ADT) Received: from gsw3mes2.groupesigma.fr (unknown [192.168.1.70]) by gslimes3.sigma.fr (Postfix) with ESMTP id 34E3934DB4 for ; Fri, 4 Apr 2008 15:21:52 +0200 (CEST) Received: from marc.sigma.fr ([89.195.0.5]) by gsw3mes2.groupesigma.fr with Microsoft SMTPSVC(6.0.3790.3959); Fri, 4 Apr 2008 15:21:52 +0200 From: Marc Cousin Organization: Sigma Informatique To: pgsql-admin@postgresql.org Subject: Re: cannot restore a view after a dump Date: Fri, 4 Apr 2008 15:21:52 +0200 User-Agent: KMail/1.9.9 References: <200804041022.31746.mcousin@sigma.fr> In-Reply-To: <200804041022.31746.mcousin@sigma.fr> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200804041521.52137.mcousin@sigma.fr> X-OriginalArrivalTime: 04 Apr 2008 13:21:52.0113 (UTC) FILETIME=[D8591610:01C89656] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/42 X-Sequence-Number: 28806 I've forgotten to add this information : Version :=20 infocentre_dte=3D# SELECT * from version(); version =2D------------------------------------------------------------------------= =2D---------------- PostgreSQL 8.3.1 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (D= ebian 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 =3D '{1,2}'::text[]) THEN 2= WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer EN= D AS "UC-SIMM" FROM (SELECT=20 winaudit_management_systeme_memoire.computer, group_array((winaudit_managem= ent_systeme_memoire.devicenumber)::text) AS devicenumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger END; ERREUR: pour SELECT DISTINCT, ORDER BY, les expressions doivent appara=EEt= re dans la liste SELECT infocentre_dte=3D# SET lc_messages to 'C'; SET infocentre_dte=3D# SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,= 2}'::text[]) THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 EL= SE NULL::integer END=20 AS "UC-SIMM" FROM (SELECT winaudit_management_systeme_memoire.computer, gro= up_array((winaudit_management_systeme_memoire.devicenumber)::text) AS devic= enumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger END; UC-SIMM =2D-------- 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 af= ter some time fails again : infocentre_dte=3D# CREATE VIEW v_test_marc AS SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,2}'::text[]) TH= EN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::intege= r END AS "UC-SIMM" FROM=20 (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit= _management_systeme_memoire.devicenumber)::text) AS devicenumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger END; CREATE VIEW infocentre_dte=3D# DROP VIEW v_test_marc ; DROP VIEW infocentre_dte=3D# CREATE VIEW v_test_marc AS SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,2}'::text[]) TH= EN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::intege= r END AS "UC-SIMM" FROM=20 (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit= _management_systeme_memoire.devicenumber)::text) AS devicenumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger END; CREATE VIEW infocentre_dte=3D# DROP VIEW v_test_marc ; DROP VIEW infocentre_dte=3D# CREATE VIEW v_test_marc AS SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,2}'::text[]) TH= EN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::intege= r END AS "UC-SIMM" FROM=20 (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit= _management_systeme_memoire.devicenumber)::text) AS devicenumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger END; CREATE VIEW infocentre_dte=3D# DROP VIEW v_test_marc ; DROP VIEW infocentre_dte=3D# DROP VIEW v_test_marc ; ERROR: view "v_test_marc" does not exist infocentre_dte=3D# CREATE VIEW v_test_marc AS SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,2}'::text[]) TH= EN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::intege= r END AS "UC-SIMM" FROM=20 (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit= _management_systeme_memoire.devicenumber)::text) AS devicenumber FROM=20 winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_s= ysteme_memoire.computer) memoire ORDER BY CASE WHEN (memoire.devicenumber = =3D '{1,2}'::text[])=20 THEN 2 WHEN (memoire.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::inte= ger 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 cann= ot > 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 = =20 > AS "DATEINVENLOG", > resume.operatingsystem = =20 > AS "UC-SE" , systeme.servicepack = =20 > = =20 > AS "UC-SEVERS" , processeurs.nbprocessor = =20 > AS "UC-CPUNB" , > resume.processordescription = =20 > AS "UC-CPUTYP" , > (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISIO= N) > / (1024)::DOUBLE PRECISION) AS "UC-CPUVIT" , disques.disknumber = =20 > = =20 > AS "UC-DDNB" , ROUND(((((resume.totalharddrive / 1024) / 1024) / > 1024))::DOUBLE PRECISION) AS "UC-DDESP" , > CASE > WHEN (memoire.devicenumber =3D '{1,2,3,4,5,6}'::text[]) > THEN 6 > WHEN (memoire.devicenumber =3D '{1,2,3,4,5}'::text[]) > THEN 5 > WHEN (memoire.devicenumber =3D '{1,2,3,4}'::text[]) > THEN 4 > WHEN (memoire.devicenumber =3D '{1,2,3}'::text[]) > THEN 3 > WHEN (memoire.devicenumber =3D '{1,2}'::text[]) > THEN 2 > WHEN (memoire.devicenumber =3D '{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 =3D systeme.computer))) > JOIN > (SELECT winaudit_reseau.computer = =20 > , group_array(winaudit_reseau.ipaddress) AS ipaddress, > group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress FROM= =20 > winaudit.winaudit_reseau > GROUP BY winaudit_reseau.computer > ) reseau > ON ((resume.computer =3D reseau.computer))) > JOIN > (SELECT winaudit_processeurs.computer = =20 > , MAX(winaudit_processeurs.processornumber) AS processornumber, > COUNT(winaudit_processeurs.processornumber) AS nbprocessor , > MAX(winaudit_processeurs.speedregistry) AS speedregistry FROM =20 > winaudit.winaudit_processeurs > GROUP BY winaudit_processeurs.computer > ) processeurs > ON ((resume.computer =3D processeurs.computer))) > JOIN > (SELECT winaudit_management_systeme_memoire.computer, > =20 > 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 =3D 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 =3D disques.computer))) > LEFT JOIN isilog.objet > ON ((resume.computername =3D (objet.i_ob_nom)::text))) > ORDER BY objet.c_barre = =20 > , resume.computer = =20 > , > resume.site = =20 > , (resume.loctime)::DATE = =20 > , > resume.operatingsystem = =20 > , systeme.servicepack = =20 > , > processeurs.nbprocessor = =20 > , resume.processordescription = =20 > , > (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISIO= N) > / (1024)::DOUBLE PRECISION), disques.disknumber = =20 > , > ROUND(((((resume.totalharddrive / 1024) / 1024) / 1024))::DOUBLE PRECISIO= N) > , CASE > WHEN (memoire.devicenumber =3D '{1,2,3,4,5,6}'::text[]) > THEN 6 > WHEN (memoire.devicenumber =3D '{1,2,3,4,5}'::text[]) > THEN 5 > WHEN (memoire.devicenumber =3D '{1,2,3,4}'::text[]) > THEN 4 > WHEN (memoire.devicenumber =3D '{1,2,3}'::text[]) > THEN 3 > WHEN (memoire.devicenumber =3D '{1,2}'::text[]) > THEN 2 > WHEN (memoire.devicenumber =3D '{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 =3D '{1,2,3,4,5,6}'::text[] THEN 6 > WHEN memoire.devicenumber =3D '{1,2,3,4,5}'::text[] THEN 5 > WHEN memoire.devicenumber =3D '{1,2,3,4}'::text[] THEN 4 > WHEN memoire.devicenumber =3D '{1,2,3}'::text[] THEN 3 > WHEN memoire.devicenumber =3D '{1,2}'::text[] THEN 2 > WHEN memoire.devicenumber =3D '{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 =3D > 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 =3D > 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 = =3D > 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 =3D 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 > =3D disques.computer LEFT JOIN objet ON resume.computername =3D > 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 =3D '{1,2,3,4,5,6}'::text[] THEN 6 > WHEN memoire.devicenumber =3D '{1,2,3,4,5}'::text[] THEN 5 > WHEN memoire.devicenumber =3D '{1,2,3,4}'::text[] THEN 4 > WHEN memoire.devicenumber =3D '{1,2,3}'::text[] THEN 3 > WHEN memoire.devicenumber =3D '{1,2}'::text[] THEN 2 > WHEN memoire.devicenumber =3D '{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 precisio= n) > / (1024)::double precision) AS "UC-CPUVIT", disques.disknumber AS > "UC-DDNB", round(((((resume.totalharddrive / 1024) / 1024) / 1024))::doub= le > precision) AS "UC-DDESP", CASE WHEN (memoire.devicenumber =3D > '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber =3D > '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber =3D > '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber =3D '{1,2,3}'::tex= t[]) > THEN 3 WHEN (memoire.devicenumber =3D '{1,2}'::text[]) THEN 2 WHEN > (memoire.devicenumber =3D '{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 =3D > 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 =3D 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 =3D 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 > =3D 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 =3D > disques.computer))) LEFT JOIN isilog.objet ON ((resume.computername =3D > (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 precisio= n) > / (1024)::double precision), disques.disknumber, > round(((((resume.totalharddrive / 1024) / 1024) / 1024))::double > precision), CASE WHEN (memoire.devicenumber =3D '{1,2,3,4,5,6}'::text[]) = THEN > 6 WHEN (memoire.devicenumber =3D '{1,2,3,4,5}'::text[]) THEN 5 WHEN > (memoire.devicenumber =3D '{1,2,3,4}'::text[]) THEN 4 WHEN > (memoire.devicenumber =3D '{1,2,3}'::text[]) THEN 3 WHEN > (memoire.devicenumber =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{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 ...