Gagan - thank you for reporting this. To be specific - how exactly did you
update pg_proc catalog table?
On Sun, Feb 5, 2017 at 8:59 AM Gagan Brahmi <gaganbra...@gmail.com> wrote:
> 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 <gaganbra...@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 <prn...@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 <gaganbra...@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
> >>>
> >>
> >>
> >
>
