[jira] [Commented] (HAWQ-1195) Synchrony:Union not working on external tables ERROR:"Two or more external tables use the same error table ""xxxxxxx"" in a statement (execMain.c:274)"

["Zhanwei Wang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HAWQ-1195?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15747072#comment-15747072 ] 

Zhanwei Wang commented on HAWQ-1195:
------------------------------------

It's me who added such constrain that one error table cannot be used in the same query more than once. And I still think this constrain is valid right now.

External table is usually used in two cases. 1)  loading data from external data source and 2) export cold data into external storage to save cost.

For the first case, error table is useful, but such external table is not suitable for query and even worse for complex query, because repeatedly running query on such table will cause error table expansion. User should import the data into a normal table and then run query on normal table. 

{code:sql}
 insert into normal_table select * from external_table;
{code}

For the second case, exported data is clean, and attaching an error table to an external table is useless. And if I remember clearly, if no dirty data exists in the external table, error table can be attached even if the external table is used more than once in the query without reporting error.

So I think we should not break such constrain and keep report error in the case reported in this JIRA.





> Synchrony:Union not working on external tables ERROR:"Two or more external tables use the same error table ""xxxxxxx"" in a statement (execMain.c:274)"
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HAWQ-1195
>                 URL: https://issues.apache.org/jira/browse/HAWQ-1195
>             Project: Apache HAWQ
>          Issue Type: Bug
>          Components: External Tables
>            Reporter: Ming LI
>            Assignee: Ming LI
>             Fix For: backlog
>
>
> Hello,
> User create an external table and define the error table. Then he do the union on the same external table with different where condition. Then return the error:ERROR:  Two or more external tables use the same error table "err_ext_pdr_cdci_pivotal_request_43448" in a statement (execMain.c:274)
> Below is the master log when I reproduce: （the whole log file attached in attachement）
> {code}
> 2016-11-29 22:49:51.976864 PST,"gpadmin","postgres",p769199,th-2123704032,"[local]",,2016-11-29 22:46:14 PST,1260,con72,cmd10,seg-1,,,x1260,sx1,"ERROR","XX000","Two or more external tables use the same error table ""err_ext_pdr_cdci_pivotal_request_43448"" in a statement (execMain.c:274)",,,,,,"select current_account_nbr,yearmonthint, bank_name, first_date_open, max_cr_limit, care_credit_flag, cc1_flag, partition_value, 'US' as loc from pdr_cdci_pivotal_request_43448 where care_credit_flag<1
> union
> select current_account_nbr,yearmonthint, bank_name, first_date_open, max_cr_limit, care_credit_flag, cc1_flag, partition_value, 'Non-US' as loc from pdr_cdci_pivotal_request_43448 where care_credit_flag=1;",0,,"execMain.c",274,"Stack trace:
> 1    0x8c5858 postgres errstart (??:0)
> 2    0x8c75db postgres elog_finish (??:0)
> 3    0x65f669 postgres <symbol not found> (??:0)
> 4    0x77d06a postgres walk_plan_node_fields (??:0)
> 5    0x77e3ee postgres plan_tree_walker (??:0)
> 6    0x77c70a postgres expression_tree_walker (??:0)
> 7    0x77e35d postgres plan_tree_walker (??:0)
> 8    0x77d06a postgres walk_plan_node_fields (??:0)
> 9    0x77dfe6 postgres plan_tree_walker (??:0)
> 10   0x77d06a postgres walk_plan_node_fields (??:0)
> 11   0x77e1e5 postgres plan_tree_walker (??:0)
> 12   0x77d06a postgres walk_plan_node_fields (??:0)
> 13   0x77dfe6 postgres plan_tree_walker (??:0)
> 14   0x77d06a postgres walk_plan_node_fields (??:0)
> 15   0x77e1e5 postgres plan_tree_walker (??:0)
> 16   0x66079b postgres ExecutorStart (??:0)
> 17   0x7ebf1d postgres PortalStart (??:0)
> 18   0x7e4288 postgres <symbol not found> (??:0)
> 19   0x7e54c2 postgres PostgresMain (??:0)
> 20   0x797d50 postgres <symbol not found> (??:0)
> 21   0x79ab19 postgres PostmasterMain (??:0)
> 22   0x4a4069 postgres main (??:0)
> 23   0x7fd97d486d5d libc.so.6 __libc_start_main (??:0)
> 24   0x4a40e9 postgres <symbol not found> (??:0)
> "
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)