Re: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore

OOps. Didn't send it to the list. (There has to be a better way of
doing this then always having to remember to change the recipient.)

I'm sorry, I thought I described the problem pretty clearly.
Here is the actual queries with comment annotations where the problem
occurred.

1) This is the query that I typed in:
create or replace view qry_AssembliesMissingInfo as
SELECT a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, a.NumPins, case when numpins is null then '' else 'Num Pins Not Specified. ' end || case when PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricingGroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end AS Problem
FROM qry_AssembliesMissingInfo1 a LEFT JOIN

/*Here is the virtual table I mentioned using select * on a join*/
(select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID = c.PricingGroupID) groups

ON a.PackageID = groups.PackageID AND a.SupplierID = groups.SupplierID
WHERE (a.NumPins Is Null AND groups.BasePrice Is Null) OR (groups.BasePrice Is Null AND groups.PricePerPin Is Null)
OR (coalesce(PricePerPin,0)>0 and coalesce(NumPins,0)=0)
GROUP BY a.AssemblyID, a.AssemblyName, a.PackageID, a.Package, a.SupplierID, a.NumPins, case when numpins is null then '' else 'Num Pins Not Specified. ' end || case when PackagePricingGroupID is null then '' else 'Package Not in a Pricing Group. ' end || case when (PackagePricingGroupID is not null And BasePrice is null) Or PricePerPin is null then 'Group Prices are Not Configured' else '' end, groups.BasePrice, groups.PricePerPin;

2) This is what PGAdmin shows after I have created view:
-- View: "qry_assembliesmissinginfo"

-- DROP VIEW qry_assembliesmissinginfo;

CREATE OR REPLACE VIEW qry_assembliesmissinginfo AS
SELECT a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, a.numpins, (
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END ||
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END AS problem
FROM qry_assembliesmissinginfo1 a

/*Here is where the problem comes in as you can see there are a
number of fields with the same name, such as pricinggroupid,
createuserid... */

LEFT JOIN ( SELECT packagepricinggroupid, pricinggroupid, packageid, createuserid, createdate, modifyuserid, modifydate, pricinggroupid, description, supplierid, baseprice, priceperpin, currencyid, createuserid, createdate, modifyuserid, modifydate
FROM packagepricinggroups b
JOIN pricinggroups c ON b.pricinggroupid = c.pricinggroupid) groups ON a.packageid = groups.packageid AND a.supplierid = groups.supplierid
WHERE a.numpins IS NULL AND groups.baseprice IS NULL OR groups.baseprice IS NULL AND groups.priceperpin IS NULL OR COALESCE(priceperpin, 0::double precision) > 0::double precision AND COALESCE(numpins, 0) = 0
GROUP BY a.assemblyid, a.assemblyname, a.packageid, a.package, a.supplierid, a.numpins, (
CASE
WHEN numpins IS NULL THEN ''::text
ELSE 'Num Pins Not Specified. '::text
END ||
CASE
WHEN packagepricinggroupid IS NULL THEN ''::text
ELSE 'Package Not in a Pricing Group. '::text
END) ||
CASE
WHEN packagepricinggroupid IS NOT NULL AND baseprice IS NULL OR priceperpin IS NULL THEN 'Group Prices are Not Configured'::text
ELSE ''::text
END, groups.baseprice, groups.priceperpin;

3) Exactly what happened.
My view worked fine after I created it.
I did a Backup using PGAdmin (which uses pg_dump)
I did a restore to a new database using PGAdmin (which uses PG_restore)
The restore gave me the ambiguous error I mentioned before
on that view.
I took the PGAdmin version of my view and saw that it had
extrapolated the fields from * and that was causing the
ambiguity. I then changed it back to * and executed the
create or replace view statement.
The view now works again.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> I just did a dump and restore of my database and one of my views did
> not recreate.

> The error received was :
> pg_restore.exe: [archiver (db)] could not execute query: ERROR: column reference "pricinggroupid" is ambiguous

> I checked the function in the original database, using PGAdmin, and
> the system seemed to have slightly changed my query.
> Part of my query was a virtual table (i.e joining on (select *
> from ...) as tablename ) and the system changed that to:
> (select fielda,fieldb,fieldc from...) the problem is that there were
> joined tables in that virtual table with identical fieldnames.

The view creation should have failed to begin with, then. Could we see
the *exact* problem and not a handwavy description?

regards, tom lane

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> I just did a dump and restore of my database and one of my views did
> not recreate.

> The error received was :
> pg_restore.exe: [archiver (db)] could not execute query: ERROR: column reference "pricinggroupid" is ambiguous

> I checked the function in the original database, using PGAdmin, and
> the system seemed to have slightly changed my query.
> Part of my query was a virtual table (i.e joining on (select *
> from ...) as tablename ) and the system changed that to:
> (select fielda,fieldb,fieldc from...) the problem is that there were
> joined tables in that virtual table with identical fieldnames.

The view creation should have failed to begin with, then. Could we see
the *exact* problem and not a handwavy description?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match