Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 054BB2E003A for ; Tue, 8 Apr 2008 04:12:15 -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 52359-09 for ; Tue, 8 Apr 2008 04:12:05 -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 4C3A32E0038 for ; Tue, 8 Apr 2008 04:11:58 -0300 (ADT) Received: from gsw3mes2.groupesigma.fr (unknown [192.168.1.70]) by gslimes3.sigma.fr (Postfix) with ESMTP id 643CB36473 for ; Tue, 8 Apr 2008 09:11:56 +0200 (CEST) Received: from marc.sigma.fr ([89.195.0.5]) by gsw3mes2.groupesigma.fr with Microsoft SMTPSVC(6.0.3790.3959); Tue, 8 Apr 2008 09:11:56 +0200 From: Marc Cousin Organization: Sigma Informatique To: pgsql-admin@postgresql.org Subject: Re: cannot restore a view after a dump Date: Tue, 8 Apr 2008 09:11:55 +0200 User-Agent: KMail/1.9.9 References: <200804041022.31746.mcousin@sigma.fr> <200804041521.52137.mcousin@sigma.fr> In-Reply-To: <200804041521.52137.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: <200804080911.56042.mcousin@sigma.fr> X-OriginalArrivalTime: 08 Apr 2008 07:11:56.0260 (UTC) FILETIME=[D43DB240:01C89947] 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/62 X-Sequence-Number: 28826 Hi, Sorry to post again, but I feel this issue is a bit strange and I'd like to= =20 understand it. The problem is that I've got the same query that runs=20 sometimes, and fails with a syntax error at other times... It's the first=20 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=3D# SELECT * from version(); > version > -------------------------------------------------------------------------= =2D- >--------------- 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 link= ed > 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 E= ND > 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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{1}'::text[]) THEN 1 ELSE NULL::integer END; ERREUR: pour SELECT > DISTINCT, ORDER BY, les expressions doivent appara=EEtre dans la liste SE= LECT > > 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[]) THE= N 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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{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=3D# CREATE VIEW v_test_marc AS > SELECT DISTINCT CASE WHEN (memoire.devicenumber =3D '{1,2}'::text[]) = THEN > 2 WHEN (memoire.devicenumber =3D '{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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{1}'::text[]) THEN 1 ELSE NULL::integer 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[]) = THEN > 2 WHEN (memoire.devicenumber =3D '{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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{1}'::text[]) THEN 1 ELSE NULL::integer 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[]) = THEN > 2 WHEN (memoire.devicenumber =3D '{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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{1}'::text[]) THEN 1 ELSE NULL::integer 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[]) = THEN > 2 WHEN (memoire.devicenumber =3D '{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 =3D '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenum= ber > =3D '{1}'::text[]) THEN 1 ELSE NULL::integer END; ERROR: for SELECT > DISTINCT, ORDER BY expressions must appear in select list >