So finally I was able to figure out the problem.
The system catalog table for pg_proc needed to be updated. It looks like
the upgrade right now does not have the ability to update the pg_proc to
reflect the changed behavior in the OUT for the procedure/function
pxf_get_item_fields.
Here's how the function looked earlier.
postgres=# \df pxf_get_item_fields
List of functions
Schema | Name | Result data type |
Argument data types
| Type
------------+---------------------+------------------+------------------------------------------------------------------------------------------------------+--------
pg_catalog | pxf_get_item_fields | SETOF record | profile text,
pattern text, OUT path text, OUT itemname text, OUT fieldname text, OUT
fieldtype text | normal
(1 row)
And here is how the function looks like after updating the pg_proc catalog
table:
postgres=# \df pxf_get_item_fields
List of functions
Schema | Name | Result data type |
Argument data types
| Type
------------+---------------------+------------------+--------------------------------------------------------------------------------------------------------------------------------+--------
pg_catalog | pxf_get_item_fields | SETOF record | profile text,
pattern text, OUT path text, OUT itemname text, OUT fieldname text, OUT
fieldtype text, OUT sourcefieldtype text | normal
(1 row)
The function is now working as expected.
postgres=# \d hcatalog.default.hive_table
PXF Hive Table "default.hive_table"
Column | Type
--------+------
id | int4
fname | text
lname | text
postgres=# SELECT * FROM pxf_get_item_fields('Hive','default.hive_table');
path | itemname | fieldname | fieldtype | sourcefieldtype
---------+------------+-----------+-----------+-----------------
default | hive_table | id | int4 | int
default | hive_table | fname | text | string
default | hive_table | lname | text | string
(3 rows)
I have created an issue HAWQ-1314 to either update the documentation to
mention this step or improve the upgrade process which can take care of the
catalog table update.
https://issues.apache.org/jira/browse/HAWQ-1314
Regards,
Gagan Brahmi
On Thu, Feb 2, 2017 at 9:42 PM, Gagan Brahmi <gaganbrahmi@gmail.com> wrote:
> Yes the PXF was upgraded as well. It is at 3.0.1 right now. The steps
> followed are the same mentioned in the document.
>
>
> Regards,
> Gagan Brahmi
>
> On Thu, Feb 2, 2017 at 9:30 PM, Pratheesh Nair <prnair@pivotal.io> wrote:
>
>> Can you confirm pxf has also upgraded in the cluster ? Please have a look
>> into the below document for the steps .
>>
>> http://hdb.docs.pivotal.io/201/hdb/install/HDB20to201Upgrade
>> .html#20to201up_pxfup
>>
>> Thanks
>> Pratheesh Nair
>>
>> On Thu, Feb 2, 2017 at 7:11 PM, Gagan Brahmi <gaganbrahmi@gmail.com>
>> wrote:
>>
>>> Hi All,
>>>
>>> I tried to upgrade from HDB 2.0.0.0 to HDB 2.0.1.0. The upgrade went
>>> fine as per the mentioned documentation. However, I am seeing some odd
>>> behavior around PXF Hive integration via HCatalog.
>>>
>>> When I try to describe the Hive table (or list the fields) using PXF
>>> HCatalog integration it seems to run into issues. The expected fields out
>>> of the function pxf_get_item_fields still seems to be 4. I know the newer
>>> HDB 2.0.1.0 returns 5 fields from this function.
>>>
>>> However, the data access seems to be alright and no issues encountered
>>> there.
>>>
>>> The following is how my testing looks like.
>>>
>>> ---------
>>>
>>> postgres=# \d hcatalog.default.hive_table
>>> ERROR: function return row and query-specified return row do not match
>>> DETAIL: Returned row contains 5 attributes, but query expects 4.
>>>
>>> postgres=# \d hcatalog.default.*
>>> ERROR: function return row and query-specified return row do not match
>>> DETAIL: Returned row contains 5 attributes, but query expects 4.
>>>
>>> postgres=# \d hcatalog.*.*
>>> ERROR: function return row and query-specified return row do not match
>>> DETAIL: Returned row contains 5 attributes, but query expects 4.
>>>
>>> postgres=# SELECT * FROM pxf_get_item_fields('Hive','de
>>> fault.hive_table');
>>> ERROR: function return row and query-specified return row do not match
>>> DETAIL: Returned row contains 5 attributes, but query expects 4.
>>>
>>> ---------
>>>
>>> The following is the expected result from a HDB 2.0.1.0 cluster. This is
>>> a clean install 2.0.1.0 cluster (not an upgrade).
>>>
>>> ---------
>>>
>>> postgres=# SELECT * FROM pxf_get_item_fields('Hive','de
>>> fault.hive_table');
>>> path | itemname | fieldname | fieldtype | sourcefieldtype
>>> ---------+------------+-----------+-----------+-----------------
>>> default | hive_table | id | int4 | int
>>> default | hive_table | fname | text | string
>>> default | hive_table | lname | text | string
>>> (3 rows)
>>>
>>> ---------
>>>
>>> Is this a known issue? Am I missing something here?
>>>
>>>
>>>
>>> Regards,
>>> Gagan Brahmi
>>>
>>
>>
>
|