Обсуждение: error handling

Поиск
Список
Период
Сортировка

error handling

От
"Little, Douglas"
Дата:

Hello,

 

Greenplum 4.1.2.4 (PG 8.2.3)

We are revising how we implement functions in order to better capture and handle fatal errors.

 

What we want to have happen,

1.       is to have the fatal error captured,

2.       logged to our processing table,

3.       then have the function & psql exit with a non-zero return code, informing Informatica of the process failure.

 

I’m having several problems.

1.       Under GP,  any function called from psql is committed as a single transaction.   I can handle the sql exception, and log the error to the table,  and return a non-zero return code to psql.

2.       I don’t know how to raise exception in the psql script.  All I can imagine is to nest the function calls,  having the outer function issue raise exception – but then the entire transaction is rolled back since the transactions are nested.  so I loose the logging messages.

3.       I’d like to have psql capture the return value and provide to a 2nd function which would then raise exeception.   But don’t know and can’t tell from the doc if I can assign function output to psql metavariables.

4.       Don’t know how to do anything with the psql metavariable   ie    if :last_return_code >= 16 then raise exception.

 

 

Suggestions would be appreciated.

Thanks

 

 

 

Doug Little

 

 

Re: error handling

От
Jasen Betts
Дата:
On 2012-06-20, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> wrote:
> --_000_8585BA53443004458E0BAA6134C5A7FBADD4CD8CEGEXCMB01owwroo_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
>
> Greenplum 4.1.2.4 (PG 8.2.3)
> We are revising how we implement functions in order to better capture and h=
> andle fatal errors.
>
> What we want to have happen,
>
> 1.       is to have the fatal error captured,
>
> 2.       logged to our processing table,
>
> 3.       then have the function & psql exit with a non-zero return code, in=
> forming Informatica of the process failure.

you're going to have to return the result as a string to the script
that calls psql and have that script generate the return code,


  retval=`psql -c "copy(select funcname('arg')) to stdout"`
  exit $retval


--
⚂⚃ 100% natural