From: Marc Cousin <mcousin@sigma.fr>
To: pgsql-admin@postgresql.org
Subject: Re: cannot restore a view after a dump
Date: Tue, 8 Apr 2008 09:11:55 +0200
Message-ID: <200804080911.56042.mcousin@sigma.fr> (raw)
In-Reply-To: <200804041521.52137.mcousin@sigma.fr>
References: <200804041022.31746.mcousin@sigma.fr>
<200804041521.52137.mcousin@sigma.fr>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mcousin@sigma.fr
Subject: Re: cannot restore a view after a dump
In-Reply-To: <200804080911.56042.mcousin@sigma.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox