Re: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore - Mailing list pgsql-general

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
Pricesare 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
Pricesare 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
NotConfigured'::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
NotConfigured'::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@compulab.co.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@compulab.co.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