By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Bug: 8.0 beta1 either view optimization or pgdump/pgrestore

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sim Zacks <si*@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

Nov 23 '05 #2

P: n/a
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 <si*@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 <si*@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
---------------------------(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

Nov 23 '05 #3

P: n/a
Sim Zacks <si*@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

P: n/a
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 <si*@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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.