Is it possible to perform commit in cursor

That is not surprising, since a cursor is a single SQL statement, and an SQL statement is always part of one transaction.

In the above example we had no problem, because a DO statement is always executed in a single transaction anyway. Cursors are automatically closed at the end of a transaction, so it is usually not necessary to explicitly close them, unless they are part of a long-running transaction and you want to free the resources allocated by the statement.

Some execution plans, like a B-tree index scan or a sequential scan, can be executed in both directions. A cursor for a query with such an execution plan is implicitly scrollable , that is, you can move the cursor position backwards in the result set.

Such cursors incur an overhead, because the server must cache the entire result set. A sensitive cursor reflects modifications of the underlying data; one consequence of this is that scrolling back to a previous row might fetch a different result. PostgreSQL does not implement sensitive cursors: that would be difficult, because a statement always sees a stable snapshot of the data in PostgreSQL. PostgreSQL cursors are always insensitive , which means that changes in the underlying data after the cursor has started processing are not visible in the data fetched from the cursor.

The value of such a variable is actually a string. That string is the name of the portal that is opened when a query is bound to the cursor variable and the cursor is opened. Both cursors and transactions are well-known database features. Normally, cursors exist only within a single database transaction. Ask Question. Asked 11 years, 5 months ago. Active 8 years, 7 months ago. Viewed 9k times. Thanks for any help, Javid.

Improve this question. Add a comment. Active Oldest Votes. For the case in the question you would need a DYNAMIC cursor Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor.

Improve this answer. Martin Smith Martin Smith k 80 80 gold badges silver badges bronze badges. Depending on this, all records, no records or any random subset of records could be updated. Could you please explain in plain English what your stored procedure should do? Quassnoi Quassnoi k 88 88 gold badges silver badges bronze badges. Hi, The reason I had to use cursor is i would have to calculate moving average for every record based on the Event column value i. No Event of previous records.

The moving average is calculated to determine some abnormality and I use it to set the Event value to something like 'Abnormal'. But, if i find three or more consecutive 'Abnormal', I need to reset all of them to 'No Event', so when the average is calculated for the next record, the previous data points needs to be used.

Sorry if I had confused you. Hope this information helps.. The final results would be deterministic it would just be undeterministic how they were achieved. If it happened to process the rows in descending order of date it would require much fewer operations than if in ascending order. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog.