Re: cannot restore a view after a dump

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
>