Thread: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore
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. As the optimizer did not qualify the field names with the table names, it makes ambiguous columns. That being said, the view does work after it is created. I assume this is a bug in the view optimization. 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
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
Sim Zacks <sim@compulab.co.il> writes:
> /*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
Okay, evidently the problem is that you have identically named
columns in the two tables PackagePricingGroups and PricingGroups,
so the "groups" join contains duplicate column names. (AFAICS this
is not illegal per the SQL spec, but I wonder whether it shouldn't
be, because it's very hard to avoid ambiguity.)
I've tweaked ruleutils.c for 8.0 so that the display looks like
...
LEFT JOIN ( SELECT b.packagepricinggroupid, b.pricinggroupid,
b.packageid, b.createuserid, b.createdate, b.modifyuserid,
b.modifydate, c.pricinggroupid, c.description,
c.supplierid, c.baseprice, c.priceperpin, c.currencyid,
c.createuserid, c.createdate, c.modifyuserid, c.modifydate
FROM packagepricinggroups b
JOIN pricinggroups c ON
...
which solves this particular issue. I'm not sure a complete solution is
possible in the presence of duplicate column names; perhaps you should
modify the query to avoid that.
regards, tom lane
As I was not using any of those duplicate columns, * was easier to use
and I did not think about trying to use the other ones.
In fact when you do try to use one of those columns in the query, it
doesn't allow the query because of ambiguous columns.
Thank you for fixing this particular problem, even though it doesn't
solve the global question it does allow you to seamlessly backup and
restore functions that work correctly in PG. I do not think that
anything needs to be answered in this regards, because if you wanted
to actually use any of those fields it would give you the same
ambiguity error and you would have to change the query to use aliases
for the identical field names. In any case, a query trying to use identical
field names would not work to begin with, so there is no call to fix
anything so long as it does not change existing behavior.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
________________________________________________________________________________
Sim Zacks <sim@compulab.co.il> writes:
> /*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
Okay, evidently the problem is that you have identically named
columns in the two tables PackagePricingGroups and PricingGroups,
so the "groups" join contains duplicate column names. (AFAICS this
is not illegal per the SQL spec, but I wonder whether it shouldn't
be, because it's very hard to avoid ambiguity.)
I've tweaked ruleutils.c for 8.0 so that the display looks like
...
LEFT JOIN ( SELECT b.packagepricinggroupid, b.pricinggroupid,
b.packageid, b.createuserid, b.createdate, b.modifyuserid,
b.modifydate, c.pricinggroupid, c.description,
c.supplierid, c.baseprice, c.priceperpin, c.currencyid,
c.createuserid, c.createdate, c.modifyuserid, c.modifydate
FROM packagepricinggroups b
JOIN pricinggroups c ON
...
which solves this particular issue. I'm not sure a complete solution is
possible in the presence of duplicate column names; perhaps you should
modify the query to avoid that.
regards, tom lane
On second thought another way to optimize a query like that would be to remove the * and only put in the columns that are actually being used, as opposed to taking the * literally. Such that if the fields in the select list use 2 columns and the join uses 1 column, only those 3 columns should be expanded by the optimizer. This would probably make the query more efficient as well, being that it selects fewer fields. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax