In an earlier post it was established that a Postgresql query such as: ‘select * from myFunc();’ cannot be used in DBExecute component, only in a DBInputTable component.
However, when I enter the above query, I get: Node DB_INPUT_TABLE0 finished with status: ERROR caused by: org.postgresql.util.PSQLException: ERROR: transaction is read-only
I have not set transactions to read-only and cannot set the transaction to read-write in DBInputTable.
Hello Chris,
I can’t reproduce the problem. select from function is properly executed, as well as executing the function, that returns a result set (see ExtExamples/graph/graphDBExecutePostgre.grf from our examples).
Does the problem occur when trying to read data from the table also?
Hi Chris,
An issue may occur if myFunc() attempts to write/update a table because the select statement set the transaction as read-only. What does your myFunc() actually do?
I’m asking now because I have the same problem now with DBInputTable when trying to select from certain OOB Vertica 6.1 functions like:
select analyze_statistics(‘sometable’);
where DBInput table errors because the connnection is set to read-only mode and I can’t get DBExecute to process it because the statement has a result set that causes DBExecute to error too.