[
https://issues.apache.org/jira/browse/HIVE-5264?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13763767#comment-13763767
]
Alexander Behm commented on HIVE-5264:
--------------------------------------
Thanks, Sergey. I don't know when the change went in, so you are probably
correct about the version.
Afaik, Postgres does not have an ANSI-compliance flag or any other means to
change that specific behavior with identifiers.
Just trying to be a good citizen so I reported the issue. I understand the
difficulties and I'm not suggesting any particular fix.
Setting metastore.try.direct.sql=false is an acceptable workaround for me (but
others may disagree).
The fallback didn't work for me. I think the reason may be that the original
(failed) SQL statement and the fallback are executed under the same transaction
(see ObjectStore.java getPartitionsByNamesInternal()), so the fallback is also
aborted, see this snippet from the Postgres log immediately after the error
message:
2013-09-09 19:10:01 PDT ERROR: current transaction is aborted, commands
ignored until end of transaction block
I didn't dig too deep into the code so don't take my word for it.
> SQL generated by MetaStoreDirectSql.java not compliant with Postgres.
> ---------------------------------------------------------------------
>
> Key: HIVE-5264
> URL: https://issues.apache.org/jira/browse/HIVE-5264
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Affects Versions: 0.11.0
> Environment: Ubuntu 12.04
> PostgreSQL 9.1.8
> Reporter: Alexander Behm
>
> Some operations against the Hive Metastore seem broken
> against Postgres.
> For example, when using HiveMetastoreClient.listPartitions()
> the Postgres logs show queries such as:
> 2013-09-09 19:10:01 PDT STATEMENT: select PARTITIONS.PART_ID from
> PARTITIONS inner join TBLS on PARTITIONS.TBL_ID = TBLS.TBL_ID inner
> join DBS on TBLS.DB_ID = DBS.DB_ID where TBLS.TBL_NAME = $1 and
> DBS.NAME = $2 order by PART_NAME asc
> with a somewhat cryptic (but correct) error:
> ERROR: relation "partitions" does not exist at character 32
> Postgres identifiers are somewhat unusual. Unquoted identifiers are
> interpreted as lower case (there is no Postgres option to change this). Since
> the Metastore table schema uses upper case table names, the correct SQL
> requires escaped identifiers to those tables, i.e.,
> select "PARTITIONS"."PART_ID" from "PARTITIONS"...
> Hive sets metastore.try.direct.sql=true by default, so the above SQL is
> generated by hive/metastore/MetaStoreDirectSql.java, i.e., this is not a
> Datanucleus problem.
> When I set metastore.try.direct.sql=false, then the Metastore backed by
> Postgres works.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
