Thread: cannot restore a view after a dump
Hi,
I'm having a strange problem : I created a view in a database, and I cannot 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 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 = '{1,2,3,4,5,6}'::text[])
THEN 6
WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
THEN 5
WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
THEN 4
WHEN (memoire.devicenumber = '{1,2,3}'::text[])
THEN 3
WHEN (memoire.devicenumber = '{1,2}'::text[])
THEN 2
WHEN (memoire.devicenumber = '{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 = 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 = 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 = 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 = 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 = disques.computer)))
LEFT JOIN isilog.objet
ON ((resume.computername = (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 = '{1,2,3,4,5,6}'::text[])
THEN 6
WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
THEN 5
WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
THEN 4
WHEN (memoire.devicenumber = '{1,2,3}'::text[])
THEN 3
WHEN (memoire.devicenumber = '{1,2}'::text[])
THEN 2
WHEN (memoire.devicenumber = '{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.operatingsystemAS "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 = '{1,2,3,4,5,6}'::text[] THEN 6
WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
WHEN memoire.devicenumber = '{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 = 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 = 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 = 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 = 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 = disques.computer
LEFT JOIN objet ON resume.computername = 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 = '{1,2,3,4,5,6}'::text[] THEN 6
WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
WHEN memoire.devicenumber = '{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.operatingsystemAS "UC-SE", systeme.servicepack AS "UC-SEVERS",
processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP",
(((processeurs.speedregistry)::doubleprecision / (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
= '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber
='{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber
= '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber =
'{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_systemeresume JOIN winaudit.winaudit_systeme_exploitation
systeme ON ((resume.computer = 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 =
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 =
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= memoire.computer))) JOIN (SELECT
winaudit_disques_physiques.computer, count(winaudit_disques_physiques.disknumber) AS disknumber FROM
winaudit.winaudit_disques_physiquesGROUP BY
winaudit_disques_physiques.computer) disques ON ((resume.computer = disques.computer))) LEFT JOIN isilog.objet ON
((resume.computername= (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 = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber =
'{1,2,3,4,5}'::text[])THEN 5 WHEN (memoire.devicenumber
= '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber =
'{1,2}'::text[])THEN 2 WHEN (memoire.devicenumber = '{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 ...
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[])
THEN1 ELSE NULL::integer END AS "UC-SIMM" FROM (SELECT
winaudit_management_systeme_memoire.computer, group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS
devicenumberFROM
winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER BY
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
CASEWHEN (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
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 cannot
> 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
> 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 = '{1,2,3,4,5,6}'::text[])
> THEN 6
> WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
> THEN 5
> WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
> THEN 4
> WHEN (memoire.devicenumber = '{1,2,3}'::text[])
> THEN 3
> WHEN (memoire.devicenumber = '{1,2}'::text[])
> THEN 2
> WHEN (memoire.devicenumber = '{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 = 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 = 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 = 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 = 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 = disques.computer)))
> LEFT JOIN isilog.objet
> ON ((resume.computername = (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 = '{1,2,3,4,5,6}'::text[])
> THEN 6
> WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[])
> THEN 5
> WHEN (memoire.devicenumber = '{1,2,3,4}'::text[])
> THEN 4
> WHEN (memoire.devicenumber = '{1,2,3}'::text[])
> THEN 3
> WHEN (memoire.devicenumber = '{1,2}'::text[])
> THEN 2
> WHEN (memoire.devicenumber = '{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 = '{1,2,3,4,5,6}'::text[] THEN 6
> WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
> WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
> WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
> WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
> WHEN memoire.devicenumber = '{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 =
> 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 =
> 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 =
> 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 = 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
> = disques.computer LEFT JOIN objet ON resume.computername =
> 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 = '{1,2,3,4,5,6}'::text[] THEN 6
> WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5
> WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4
> WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3
> WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2
> WHEN memoire.devicenumber = '{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 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 =
> '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber =
> '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber =
> '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[])
> THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> (memoire.devicenumber = '{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 =
> 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 = 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 = 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
> = 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 =
> disques.computer))) LEFT JOIN isilog.objet ON ((resume.computername =
> (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 = '{1,2,3,4,5,6}'::text[]) THEN
> 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN
> (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN
> (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN
> (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber
> = '{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 ...
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
>
I didn't put it in, pg_dump dit it for me... it seems that when a view has a
distinct, the dumped view has automatically the order by. that's what
triggered the whole problem.
But the query is supposed to be the same with the order by, because of the
distinct, so pg_dump is not wrong.
It looks like a parsing problem to me (as it sometimes work and sometimes not
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 <mcousin@sigma.fr> wrote:
> > 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
> >
> > --
> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-admin
Marc Cousin <mcousin@sigma.fr> writes: > 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 ... Oh, sorry for not replying directly to you. This was analyzed over in -hackers and we now understand the cause, but haven't a fix quite yet: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00278.php It's been broken since approximately forever, so if you'd been using that view definition for awhile I'm surprised you didn't run into the problem before. The reason for the erratic behavior is that it depends on what happened to already be in the chunks of memory used to form the array constants. In a freshly-started backend the view creation would probably always succeed, but as soon as memory has gotten dirtied a bit, maybe not. regards, tom lane
Marc Cousin <mcousin@sigma.fr> writes: > I'm having a strange problem : I created a view in a database, and I cannot restore it after a pg_dump. > ... > ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list You need this patch: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?r1=1.140&r2=1.140.2.1 I found that this problem was introduced in 8.2, which may explain why you'd not seen it before, if you were previously using an older release. regards, tom lane
I've installed it and it works. Thanks a lot. On Saturday 12 April 2008 01:01:06 Tom Lane wrote: > Marc Cousin <mcousin@sigma.fr> writes: > > I'm having a strange problem : I created a view in a database, and I > > cannot restore it after a pg_dump. ... > > ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select > > list > > You need this patch: > > http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayf >uncs.c?r1=1.140&r2=1.140.2.1 > > I found that this problem was introduced in 8.2, which may explain why > you'd not seen it before, if you were previously using an older release. > > regards, tom lane