Re: what makes the PL cursor life-cycle must be in the same transaction?

Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> writes:

> for example:
> begin;
> declare cur cursor for select * from t;
> insert into t2 values(...);
> fetch next cur;
> commit;
>
> // after this, I can't fetch cur any more.
>
> My question are:
> 1. Is this must in principle? or it is easy to implement as this in PG?

It is already implemented. If you declare the cursor WITH HOLD, you can
keep using it after the transaction commits.

> 2. Any bad thing would happen if I keep the named portal (for the cursor)
> available even the transaction is commit, so that I can fetch the cursor
> after the transaction is committed?

According to the documentation
(https://www.postgresql.org/docs/current/sql-declare.html):

| In the current implementation, the rows represented by a held cursor
| are copied into a temporary file or memory area so that they remain
| available for subsequent transactions.

> Thanks

- ilmari
--
"I use RMS as a guide in the same way that a boat captain would use
a lighthouse. It's good to know where it is, but you generally
don't want to find yourself in the same spot." - Tollef Fog Heen