persistent portals/cursors (between transactions)

Florian Wunderlich <fwunderlich@devbrain.de> writes:

But there is no check in CreatePortal or SPI_cursor_open, as far as I've
seen, but as SPI doesn't allow transaction control statements I don't
know if SPI_connect probably begins a transaction implicitly.

Any sort of SPI operation is implicitly within a transaction, since it
can (by assumption) only be called from a function, which is being
called within a query, which is explicitly or implicitly within a
transaction. So I think the lack of check there is okay.

I'm wondering now why portals have to be dropped at the end of a
transaction.

Because the table-level locks guaranteeing the existence and schema
stability of the referenced tables will go away when the transaction
ends. Against that, there's not much point in sweating the small stuff
like whether we could drop and reacquire buffer pins ...

regards, tom lane

Tom Lane wrote:

Florian Wunderlich <fwunderlich@devbrain.de> writes:

But there is no check in CreatePortal or SPI_cursor_open, as far as I've
seen, but as SPI doesn't allow transaction control statements I don't
know if SPI_connect probably begins a transaction implicitly.

Any sort of SPI operation is implicitly within a transaction, since it
can (by assumption) only be called from a function, which is being
called within a query, which is explicitly or implicitly within a
transaction. So I think the lack of check there is okay.

Since you cannot escalate from an implicit transaction to a
transaction block from inside a function, this was the only
way to enable cursors in PL/pgSQL without the requiremet to
call them inside of an explicit begin/commit block allways.

But I don't like the idea of cross transaction cursors. The
locking issues, mentioned in the code by MAO, which are the
reason for rejecting FOR UPDATE on cursors, should be gone.
And the capability to select for update is a requirement for
updateable cursors, that I intend to work on for 7.3.

So please, no cross transaction cursors only because they
might be handy for ODBC!

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Tom Lane wrote:

Florian Wunderlich <fwunderlich@devbrain.de> writes:

But there is no check in CreatePortal or SPI_cursor_open, as far as I've
seen, but as SPI doesn't allow transaction control statements I don't
know if SPI_connect probably begins a transaction implicitly.

Any sort of SPI operation is implicitly within a transaction, since it
can (by assumption) only be called from a function, which is being
called within a query, which is explicitly or implicitly within a
transaction. So I think the lack of check there is okay.

I'm wondering now why portals have to be dropped at the end of a
transaction.

Because the table-level locks guaranteeing the existence and schema
stability of the referenced tables will go away when the transaction
ends. Against that, there's not much point in sweating the small stuff
like whether we could drop and reacquire buffer pins ...

Of course, never thought of that. But why does the lock (AccessShareLock
from what I see) keep UPDATE (that acquires a RowExclusiveLock from what
I see) from running?

Where is the problem in simply holding this lock, if it's really just an
AccessShareLock, for the lifetime of the cursor?

I've seen that this topic (cursors outside transactions) is also an item
on the TODO list, so it's probably worth investing some time.

I'd really like to have persistent insensitive cursors, and it'd
probably make life a lot easier for the ODBC guys as I already said, and
probably the JDBC guys would switch too transactions too. I've looked
through all the documents on the developer website and read the slides
to your talk about transaction processing (a *real* timesaver, thanks),
which works more or less as I expected from the name, and I wonder if
you could implement an insensitive cursor simply by declaring it inside
a transaction, ending the transaction and then using the information
from this transaction for returning the necessary consistent set of data
as if you were still inside this transaction.

To see your own updates though you would need some kind of accept-list
in addition to the ignore-list that is there already for those
transactions you did later.

Would this approach actually work? Or do you think it should be done
differently?

Florian Wunderlich <fwunderlich@devbrain.de> writes:

Of course, never thought of that. But why does the lock (AccessShareLock
from what I see) keep UPDATE (that acquires a RowExclusiveLock from what
I see) from running?

It shouldn't do that. Can you provide an example?

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.
You *cannot* expect deleted data in a table to hang around for you after
you close your transaction --- there is nothing to protect it from being
VACUUMed, for example.

regards, tom lane

-----Original Message-----
From: Tom Lane

Florian Wunderlich <fwunderlich@devbrain.de> writes:

But there is no check in CreatePortal or SPI_cursor_open, as far as I've
seen, but as SPI doesn't allow transaction control statements I don't
know if SPI_connect probably begins a transaction implicitly.

Any sort of SPI operation is implicitly within a transaction, since it
can (by assumption) only be called from a function, which is being
called within a query, which is explicitly or implicitly within a
transaction. So I think the lack of check there is okay.

This isn't necessarily true in other dbms's.

I'm wondering now why portals have to be dropped at the end of a
transaction.

At the end of a transaction PG system releases many resources
automatically. It isn't unclear to me what kind of resources should
be kept for persistent cursors between transactions and how to
keep them between transactions and finally release them cleanly.
As for locking Tom already implemented cross transaction locking.
But for example buffer pin/locks there isn't. It doesn't seem easy to
solve such items safely and cleanly. Of cource it isn't preferable to
introduce new bugs or needless complexity.
This is my long TODO item but unfortunately I have no clear idea
to achieve it.

regards,
Hiroshi Inoue

Jan Wieck wrote:

Tom Lane wrote:

Florian Wunderlich <fwunderlich@devbrain.de> writes:

But there is no check in CreatePortal or SPI_cursor_open, as far as I've
seen, but as SPI doesn't allow transaction control statements I don't
know if SPI_connect probably begins a transaction implicitly.

Any sort of SPI operation is implicitly within a transaction, since it
can (by assumption) only be called from a function, which is being
called within a query, which is explicitly or implicitly within a
transaction. So I think the lack of check there is okay.

Since you cannot escalate from an implicit transaction to a
transaction block from inside a function, this was the only
way to enable cursors in PL/pgSQL without the requiremet to
call them inside of an explicit begin/commit block allways.

I don't understand that.
What do you mean by "this"? The omission of a check? But there's a
transaction anyway as Tom said?

But I don't like the idea of cross transaction cursors. The
locking issues, mentioned in the code by MAO, which are the
reason for rejecting FOR UPDATE on cursors, should be gone.
And the capability to select for update is a requirement for
updateable cursors, that I intend to work on for 7.3.

But can't cursors not only be updateable inside transactions and
read-only outside transactions, as a work-around, which would bring
PostgreSQL at least a little closer to the standard?

And in the long term, can't the lock that is acquired with FOR UPDATE be
released when the cursor is closed and not when the transaction is
finished?

I'm sorry if I completely miss the point but it's been years since I
looked at the source of PostgreSQL.

So please, no cross transaction cursors only because they
might be handy for ODBC!

Not only ODBC, I don't use ODBC anyway, I just thought that the current
ODBC driver probably has to considerably work around that.

Consider the following scenario: You present the user with a set of
records, which he can scroll, and which can be modified interactively.
As the SELECT takes quite a while, you can't re-execute the query
(re-declare the cursor) every time a row is modified, but as this view
might be open for an arbitrary time, you don't want to execute all
UPDATEs in this transaction.

Currently, you would need one transaction with the cursor, and another
one if you update a record, and you would need a cache which holds the
records that were actually updated as you don't see them yet in the
transaction with the cursor.

Florian Wunderlich wrote:

Jan Wieck wrote:

Since you cannot escalate from an implicit transaction to a
transaction block from inside a function, this was the only
way to enable cursors in PL/pgSQL without the requiremet to
call them inside of an explicit begin/commit block allways.

I don't understand that.
What do you mean by "this"? The omission of a check? But there's a
transaction anyway as Tom said?

Yes, there is at least an implicit transaction allways. But
not necessarily an explicit transaction block (BEGIN/COMMIT).
Cursors used to be possible only inside of explicit
transaction blocks. That's "this".

But I don't like the idea of cross transaction cursors. The
locking issues, mentioned in the code by MAO, which are the
reason for rejecting FOR UPDATE on cursors, should be gone.
And the capability to select for update is a requirement for
updateable cursors, that I intend to work on for 7.3.

But can't cursors not only be updateable inside transactions and
read-only outside transactions, as a work-around, which would bring
PostgreSQL at least a little closer to the standard?

And in the long term, can't the lock that is acquired with FOR UPDATE be
released when the cursor is closed and not when the transaction is
finished?

The way it has to be is that you say

UPDATE ... WHERE CURRENT OF <cursor>

My idea is to hold the CTID, retrieved via a junk attribute,
of the last FETCH'ed row (of the table the locks are for)
inside of the cursor information, and basically rewrite the
WHERE CURRENT OF into a WHERE ctid = ... during parse.

As long as we cannot safely hold such locks across Xact
boundaries and guarantee that rows locked that way don't get
moved by vacuum, I'd vote for making cursors that are FOR
UPDATE inaccessable at Xact end.

Reminds me that Al Dev is right. The law's of physics apply
to software! Proof: vacuum sucks!

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Tom Lane wrote:

Florian Wunderlich <fwunderlich@devbrain.de> writes:

Of course, never thought of that. But why does the lock (AccessShareLock
from what I see) keep UPDATE (that acquires a RowExclusiveLock from what
I see) from running?

It shouldn't do that. Can you provide an example?

With a trivial example, the server doesn't hang. I'm still checking out
what exactly happened before that caused the server to hang.

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.

Sure, that was the first thing I thought about, but one table has at
least 100,000 records in 28 columns with a data-only row length of about
256 bytes. At least 50 user will do this concurrently. With at least two
tables. Concurrently.

Which amounts to at least 2.38 GB temporary data, counting only the
payload.

And yes, the whole table has to be scrolled. This was not my idea.

You *cannot* expect deleted data in a table to hang around for you after
you close your transaction --- there is nothing to protect it from being
VACUUMed, for example.

I see. So not only the transaction information would have to be retained
after the transaction with the cursor declaration finished, but you'd
also have to have this transaction marked as kind of still in progress,
while it really has to be finished because there can only be one
transaction per backend.

Is a cursor that has a transaction associated with it a concept that can
be cleanly implemented?

Is there any simpler solution to the problem? Has anyone ever thought
about that before? I searched the mailing lists but couldn't find
anything.

But I don't like the idea of cross transaction cursors. The
locking issues, mentioned in the code by MAO, which are the
reason for rejecting FOR UPDATE on cursors, should be gone.
And the capability to select for update is a requirement for
updateable cursors, that I intend to work on for 7.3.

But can't cursors not only be updateable inside transactions and
read-only outside transactions, as a work-around, which would bring
PostgreSQL at least a little closer to the standard?

And in the long term, can't the lock that is acquired with FOR UPDATE be
released when the cursor is closed and not when the transaction is
finished?

The way it has to be is that you say

UPDATE ... WHERE CURRENT OF <cursor>

My idea is to hold the CTID, retrieved via a junk attribute,
of the last FETCH'ed row (of the table the locks are for)
inside of the cursor information, and basically rewrite the
WHERE CURRENT OF into a WHERE ctid = ... during parse.

As long as we cannot safely hold such locks across Xact
boundaries and guarantee that rows locked that way don't get
moved by vacuum, I'd vote for making cursors that are FOR
UPDATE inaccessable at Xact end.

As long as the cursor is not insensitive, as it has to keep vacuum from
removing rows then anyway. Though the lock would then still be necessary
for obvious reasons too.

And Hiroshi wrote in this thread in
<EKEJJICOHDIEMGPNIFIJEEDKGJAA.Inoue@tpf.co.jp> that Tom already
implemented cross transaction locking, though I don't know if this can
be applied here.

Reminds me that Al Dev is right. The law's of physics apply
to software! Proof: vacuum sucks!

:))

But can't cursors not only be updateable inside transactions and
read-only outside transactions, as a work-around, which would bring
PostgreSQL at least a little closer to the standard?

And in the long term, can't the lock that is acquired with FOR UPDATE be
released when the cursor is closed and not when the transaction is
finished?

The way it has to be is that you say

UPDATE ... WHERE CURRENT OF <cursor>

My idea is to hold the CTID, retrieved via a junk attribute,
of the last FETCH'ed row (of the table the locks are for)
inside of the cursor information, and basically rewrite the
WHERE CURRENT OF into a WHERE ctid = ... during parse.

As long as we cannot safely hold such locks across Xact
boundaries and guarantee that rows locked that way don't get
moved by vacuum, I'd vote for making cursors that are FOR
UPDATE inaccessable at Xact end.

Reminds me that Al Dev is right. The law's of physics apply
to software! Proof: vacuum sucks!

I am not sure that is true anymore of non-FULL vacuum. I thought it
didn't move tids, though it will remove expired ones. Tom?

Wonder if we should think of some kind of anti-FULL vacuum lock that can
be held during transactions with FOR UPDATE.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hey,

Thought I would let everyone know that we have released some screenshots
of the upcoming pgManage from Command Prompt. You can see them here:

http://www.commandprompt.com/entry.lxp?lxpe=126

Initially it will run on Win32, Solaris and Linux. It is expected to be
released in the next 3 weeks.

Here is a brief (incomplete) list of features:

Multiple machine management (done)
Ability to manage the postgresql and pg_hba conf files (unfinished)
Ability to manage users and groups (50% done)
Ability to Magage tables and databases (including creation/deletion) (70% done)
Ability to back up over the wire to a local machine (50% done)
Ability to run queries and save the results in a readable format (50% done)
Ability to connect via SSL for increased security (done)
Ability to have stored queries (not just views) (50% done)

Sincerely,

Joshua Drake
jd@commandprompt.com

--
--
by way of pgsql-general@commandprompt.com
http://www.postgresql.info/
http://www.commandprompt.com/

-----Original Message-----
From: Tom Lane

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.

1) It may need large space.
2) It may take long time to respond.

If no one would try this importgant TODO I would try it
in the next year release.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:

-----Original Message-----
From: Tom Lane

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.

1) It may need large space.
2) It may take long time to respond.

Which are exactly the problems in my case.

If no one would try this importgant TODO I would try it
in the next year release.

I absolutely want to have this feature because it makes my life a lot
easier, so I'll contribute as much time as possible, though I can't do
it right now as we're done evaluating databases, stuck with Postgresql,
and are bound to implement a workaround on client side first, as that's
crappy and anything but nice, but it'll get the product out on time.

When I start hacking it I'll give you notice in case you already started
on it.

BTW, if anyone wants to put up short comparison of Interbase, SAPDB and
PostgreSQL feature and restriction wise, drop me a line.

Florian Wunderlich wrote:

Hiroshi Inoue wrote:

-----Original Message-----
From: Tom Lane

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.

1) It may need large space.
2) It may take long time to respond.

Which are exactly the problems in my case.

If no one would try this importgant TODO I would try it
in the next year release.

I absolutely want to have this feature because it makes my life a lot
easier,

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:

-----Original Message-----
From: Tom Lane

I'd really like to have persistent insensitive cursors,

Seems a lot easier to just select the data you want into a temp table.

1) It may need large space.
2) It may take long time to respond.

Which are exactly the problems in my case.

If no one would try this importgant TODO I would try it
in the next year release.

I absolutely want to have this feature because it makes my life a lot
easier,

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

That is a good idea, especially read-only, that will not require any
locks.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

That is a good idea, especially read-only, that will not require any
locks.

If it's not holding any locks, I can guarantee you it's not insensitive.
Consider VACUUM, or even DROP TABLE.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

That is a good idea, especially read-only, that will not require any
locks.

If it's not holding any locks, I can guarantee you it's not insensitive.
Consider VACUUM, or even DROP TABLE.

I assumed it would be an in-memory copy of the cursor, like a portal
that doesn't go away on transaction exit.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

That is a good idea, especially read-only, that will not require any
locks.

If it's not holding any locks, I can guarantee you it's not insensitive.
Consider VACUUM, or even DROP TABLE.

I assumed it would be an in-memory copy of the cursor, like a portal
that doesn't go away on transaction exit.

Ever realized what a portal is? So far it's a query for which
ExecutorStart() has been called, just sitting there, waiting
for subsequent ExecutorRun() calls.

How such a thing can live outside of any transaction context
isn't totally clear to me, even if I have to admit that I see
by now the desire for cross transaction cursors. It's just
these lil' details like "how does the portal maintain it's
snapshot POV after the transaction creating it is long
gone?", that make me nervous.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I forgot to mention that I'd like to implement a cross
transaction insensitive(and read-only) cursors which
any proper dbms seems to have the functionality.

That is a good idea, especially read-only, that will not require any
locks.

If it's not holding any locks, I can guarantee you it's not insensitive.
Consider VACUUM, or even DROP TABLE.

It's already possible to keep a lock accross transactions.
So it would keep an AccessShareLock across transactions.

regards,
Hiroshi Inoue