Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 79E7B2E00C5 for ; Tue, 8 Apr 2008 11:01:19 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 85876-05-5 for ; Tue, 8 Apr 2008 11:01:02 -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 E7BEB2E00CD for ; Tue, 8 Apr 2008 10:57:19 -0300 (ADT) Received: from gsw3mes2.groupesigma.fr (unknown [192.168.1.70]) by gslimes3.sigma.fr (Postfix) with ESMTP id 4530D36726; Tue, 8 Apr 2008 15:57:16 +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 15:57:15 +0200 From: Marc Cousin Organization: Sigma Informatique To: "Sergio Gabriel Rodriguez" , pgsql-admin@postgresql.org Subject: Re: cannot restore a view after a dump Date: Tue, 8 Apr 2008 15:57:09 +0200 User-Agent: KMail/1.9.9 References: <200804041022.31746.mcousin@sigma.fr> <200804080911.56042.mcousin@sigma.fr> <15aa6b3e0804080624p3673aebfi4a0b19cf72458fb@mail.gmail.com> In-Reply-To: <15aa6b3e0804080624p3673aebfi4a0b19cf72458fb@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200804081557.09818.mcousin@sigma.fr> X-OriginalArrivalTime: 08 Apr 2008 13:57:15.0962 (UTC) FILETIME=[73E9B9A0:01C89980] 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/75 X-Sequence-Number: 28839 I didn't put it in, pg_dump dit it for me... it seems that when a view has = a=20 distinct, the dumped view has automatically the order by. that's what=20 triggered the whole problem. But the query is supposed to be the same with the order by, because of the= =20 distinct, so pg_dump is not wrong. It looks like a parsing problem to me (as it sometimes work and sometimes n= ot=20 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 wrote: > > Hi, > > > > Sorry to post again, but I feel this issue is a bit strange and I'd li= ke > > 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 fir= st > > 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, compil= ed > > > 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 =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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > ERREUR: pour SELECT DISTINCT, ORDER BY, les expressions doivent > > > appara=EEtre 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 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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > 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}'::tex= t[]) > > > 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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > 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}'::tex= t[]) > > > 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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > 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}'::tex= t[]) > > > 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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > 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}'::tex= t[]) > > > 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.devicenumber =3D '{1}'::text[]) THEN 1 ELSE NULL::integer E= ND; > > > 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