Ask Your Question

Streamsets JDBC Lookup Can't read postgresql sequence Nextval

asked 2020-04-06 10:39:55 -0500

Sujata HS gravatar image

Hi All,

I am trying fetch nextval of postgresql sequence in streamsets jdbc lookup stage, But it give mes below error. " com.streamsets.pipeline.api.base.OnRecordErrorException: JDBC_02 - Exception executing query: 'select nextval('ann."seq_fundid"') as fund_id;' - 'ERROR: cannot execute nextval() in a read-only transaction' "

However I can eliminate this error by unchecking " Enforce read-only connection" option on advanced tab

But I am not sure about the risk of unchecking it . Can anyone address risk of disabling read-only connection or any alternate solution to this ?

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2020-04-09 07:59:42 -0500

Hi Sujata,

The read-only connection will enforce read privilege for the session. When using the PG function nextval(), a write is made back to PG to advance the sequence. This modification will not occur when in read-only session which is why PG sends back the error: "cannot execute nextval() in a read-only transaction".

The risk of unchecking "Enforce read-only connection" would depend on your level of privilege in the DBMS. In this instance, you are modifying a sequence. Is that allowed by your DBA?

Alternatives. Depending on your usage, you could attach the sequence directly to the PG table. For example, if the sequence is being used as a primary key, you could add the nextval() function to the DDL.

Example: CREATE TABLE foo ( id integer NOT NULL DEFAULT nextval('my_seq') PRIMARY KEY, col_a VARCHAR(50), col_b VARCHAR(50));

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-06 10:39:55 -0500

Seen: 24 times

Last updated: Apr 06