Thread: persistent portals/cursors (between transactions)
I've found that, surprisingly, the attempt to declare a cursor outside a transaction is caught already in the parser, and this code is preceeded by a comment from 1991 that this was introduced in Postgres 3.0 because of locking issues. 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. I've #ifdef'd out the elog in the parser, but then the cursor is obviously immediately dropped after the statement, so I guess auto-commit really means what it says in psql. I'm wondering now why portals have to be dropped at the end of a transaction. I've #ifdef'd the AtEOXact_portals calls out too and a fetch from a cursor in such circumstances now seems to return the correct data, but the server says "NOTICE: Buffer Leak: [004] (freeNext=-3, freePrev=-3, rel=1058334/1058690, blockNum=0, flags=0x4, refcount=1 2)" (multiple times, different values). Additionally, the server seems to keep some lock on the table or rows, as I can't update them in another session, and, uh, seems to hang then. Ahem. ps ax says UPDATE but state is S so I assume it's some kind of lock that is not released. So before I dig in deeper I thought I'd simply ask here why cursors have to be dropped at the end of a transaction, and where this buffer leak comes from. And what's up with the other session that hangs, why do you need a lock for a SELECT, besides implementing an INSENSITIVE cursor? Wouldn't persistent cursors make life a lot easier for the ODBC guys? I've seen that the official JDBC driver fetches the whole ResultSet at once, but ODBC seems to use a cursor.
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
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
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?
> -----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 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
> > > 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! :))
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
Jan Wieck wrote: > > 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. Hiroshi, that's exactly what I need, though I am not sure if we are all really talking about the same thing. In case I misunderstood something: as far as I know, SQL92 defines that a cursor is by default sensitive, which means that it displays the data from all comitted transactions at any time. If the data changes, so does what the cursor returns. Bruce, as far as I understand, you really only need to hold an AccessShareLock then, to keep the table structure from being modified. In contrast, an insensitive cursor returns only those rows from the query which were committed when the cursor was declared (or opened? I don't remember). This requires at least a method to keep vacuum from removing rows that still have to be returned, as Tom already said. FYI, none of the other open source RDBMS implement insensitive cursors, though it's probably the thing that would be the most useful in today's interactive applications. Firebird (ex Interbase): Implements FOR UPDATE and WHERE CURRENT OF for UPDATE and DELETE, but no INSENSITIVE cursor. http://www.ibphoenix.com/60sqlref.html#RSf40642 SAPDB (ex Adabas-D): Implements FOR UPDATE etc. etc., but is not clear on wether the default cursor is sensitive or insensitive, as they are talking about "named result tables" all the time and have a "FOR REUSE" clause, which makes me wonder wether they always use a temporary table. http://www.sapdb.org/htmhelp/e2/55683ab81fd846e10000000a11402f/frameset.htm http://www.sapdb.org/htmhelp/40/13120f2fa511d3a98100a0c9449261/content.htm The commercial databases all implement insensitive cursors of course, though with different means. Microsoft SQL Server: Implements read-only INSENSITIVE, by using a temporary table. What did you expect anyway. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp Sybase: Implements read-write (?) INSENSITIVE, by using a temporary table. To quote, "INSENSITIVE cursors can be expensive if the cursor defines a large result set." Oh really, and that's what I thought cursors are to remedy. http://manuals.sybase.com/onlinebooks/group-sas/awg0702e/dbugen7/@Generic__BookTextView/21130 IBM DB2: Implements read-write INSENSITIVE, but uses a temporary table always, also for sensitive, as it provides a modified FETCH that can fetch either sensitive or insensitive. Sorry, no URL. Oracle: Implements INSENSITIVE, though I don't know how. Sorry, no URL either. So, in conclusion, sensitive cross-transaction cursors are probably easy, and everybody has them, but insensitive are not so, though it should be possible without using a temporary table because of PostgreSQL's storage management.
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> 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.
AccessShareLock would fend off DROP/ALTER TABLE, but not VACUUM anymore.
We'd need to invent Yet Another lock type that would prevent VACUUM.
Clearly that's perfectly doable.
But: having just finished a lot of work to ensure that VACUUM could run
in parallel with all "normal" database operations, I'm not that thrilled
at the prospect of introducing a new mechanism that will block VACUUM.
Especially not one that's *designed* to hold its lock for a long period
of time. This will just get us right back into all the operational
problems that lazy VACUUM was intended to get around. For example, this
one: if transaction A has an insensitive-cursor lock on table T, and a
VACUUM comes along to vacuum T and blocks waiting for the lock, then
when subsequent transaction B wants to create an insensitive cursor on T
it's going to be forced to queue up behind the VACUUM.
While temp tables may seem like an ugly, low-tech way to support
insensitive cursors, I think they may have more merit than you realize.
regards, tom lane
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > Tom Lane wrote: > >> 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. > > AccessShareLock would fend off DROP/ALTER TABLE, but not VACUUM anymore. > We'd need to invent Yet Another lock type that would prevent VACUUM. > Clearly that's perfectly doable. > > But: having just finished a lot of work to ensure that VACUUM could run > in parallel with all "normal" database operations, I'm not that thrilled > at the prospect of introducing a new mechanism that will block VACUUM. > Especially not one that's *designed* to hold its lock for a long period > of time. This will just get us right back into all the operational > problems that lazy VACUUM was intended to get around. For example, this > one: if transaction A has an insensitive-cursor lock on table T, and a > VACUUM comes along to vacuum T and blocks waiting for the lock, then > when subsequent transaction B wants to create an insensitive cursor on T > it's going to be forced to queue up behind the VACUUM. Why do you have to lock the whole table when all you want is just one set of rows from a set of versions? Am I missing something here? When you're talking about in-transaction cursors for the above example, why would the cursor need anything more than the transaction A needs anyway? And for cross-transaction cursors, why lock the whole table when you could use the transaction information from the transaction in which the cursor was declared? Generally spoken, where's the difference between an insensitive persistent cursor and a still running transaction? > While temp tables may seem like an ugly, low-tech way to support > insensitive cursors, I think they may have more merit than you realize. Obviously, that's the easy way to do it, and lots of other databases make use of that already to implement insensitive cursors (see my other post). But as the long-term goal should be updateable insensitive persistent cursors, I think the temp table solution will get really messy.
Florian Wunderlich <fwunderlich@devbrain.de> writes:
> When you're talking about in-transaction cursors for the above example,
> why would the cursor need anything more than the transaction A needs
> anyway?
It wouldn't.
> And for cross-transaction cursors, why lock the whole table when
> you could use the transaction information from the transaction in which
> the cursor was declared?
The problem is to keep the rows that are supposed to be still visible to
you from disappearing. If other backends think that transaction A is
history, they will not think that they need to preserve rows that would
have been visible to A, but are not visible to any still-running
transaction.
[ ... thinks for awhile ... ] Maybe we could extend the notion of
"oldest XMIN" a little. Perhaps what each backend should record in the
PROC array is not just the oldest XMIN visible to its current
transaction, but the oldest XMIN visible to either its current xact or
any of its open cross-transaction cursors. That together with an
AccessShareLock on tables referenced by the cursors might work.
A drawback of this approach is that opening a cursor and sitting on it
for a long time would effectively defeat VACUUM activity --- it wouldn't
be blocked, but it wouldn't be able to reclaim rows either. Anywhere,
not only in the tables actually used by the cursor.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > Tom Lane wrote: > >> 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. > > AccessShareLock would fend off DROP/ALTER TABLE, but not VACUUM anymore. Really ? VACUUM FULL conflicts with AccessShareLock from the first. If new vacuum does wrong thing with persistent read-only cursors it would do the wrong thing with the current cursors as well. Of cource as Vadim mentioned before, HeapTupleSatisfiesVacuum() should take the transaction id in which the cursor was opened into account. regards, Hiroshi Inoue
> > And for cross-transaction cursors, why lock the whole table when > > you could use the transaction information from the transaction in which > > the cursor was declared? > > The problem is to keep the rows that are supposed to be still visible to > you from disappearing. If other backends think that transaction A is > history, they will not think that they need to preserve rows that would > have been visible to A, but are not visible to any still-running > transaction. > > [ ... thinks for awhile ... ] Maybe we could extend the notion of > "oldest XMIN" a little. Perhaps what each backend should record in the > PROC array is not just the oldest XMIN visible to its current > transaction, but the oldest XMIN visible to either its current xact or > any of its open cross-transaction cursors. That together with an > AccessShareLock on tables referenced by the cursors might work. > > A drawback of this approach is that opening a cursor and sitting on it > for a long time would effectively defeat VACUUM activity --- it wouldn't > be blocked, but it wouldn't be able to reclaim rows either. Anywhere, > not only in the tables actually used by the cursor. Isn't that exactly what beginning a transaction and keeping it uncommitted for a long time would do too? I see the problem - your last sentence - but getting rid of that would mean to not only save an oldest XMIN, but also a reference to all tables that this not-quite-a-xact uses, kind of like a "selective transaction". I doubt that there really are any problems in the real world though, so having a naive implementation first would be fine too. So from the vacuum perspective, it looks like more than just one transaction is running per backend, right? Probably I don't understand anything at all, or that's what I suggested way back in my second or third mail. Whatever. Assuming I understood a bit here, a read-write cross-transaction cursor shouldn't be too hard to implement then either.
Florian Wunderlich <fwunderlich@devbrain.de> writes:
> Isn't that exactly what beginning a transaction and keeping it
> uncommitted for a long time would do too?
Sure, but then you haven't got a cross-transaction cursor, only a plain
cursor.
regards, tom lane
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> AccessShareLock would fend off DROP/ALTER TABLE, but not VACUUM anymore.
> Really ? VACUUM FULL conflicts with AccessShareLock from the
> first.
I was speaking of lazy VACUUM, of course.
> If new vacuum does wrong thing with persistent read-only cursors
> it would do the wrong thing with the current cursors as well.
No, because current cursors don't span transactions.
> Of cource as Vadim mentioned before, HeapTupleSatisfiesVacuum()
> should take the transaction id in which the cursor was opened into
> account.
I haven't read all of that thread yet; maybe Vadim already had the idea
I just had of playing games with oldest-XMIN.
regards, tom lane
> -----Original Message----- > From: florian@hq.factor3.com [mailto:florian@hq.factor3.com]On > > > Hiroshi, that's exactly what I need, though I am not sure if we are all > really talking about the same thing. > > In case I misunderstood something: as far as I know, SQL92 defines that > a cursor is by default sensitive, which means that it displays the data > from all comitted transactions at any time. If the data changes, so does > what the cursor returns. AFAIK SQL92's default is indeterminate which guarantees nothing about sensitivity. Though we don't have insensitive cursors yet INSENSITIVE cursors are very natural for MVCC and it's not hard to implement. In reality the current cursors see no changes after the cursor was opened other than the ones made by the bakend itself. regards, Hiroshi Inoue
Tom Lane wrote: > > Isn't that exactly what beginning a transaction and keeping it > > uncommitted for a long time would do too? > > Sure, but then you haven't got a cross-transaction cursor, only a plain > cursor. Sorry for being unclear - I wanted to say that this problem obviously already exists, so there's not a new (conceptual) problem here. I'm sure you read the second part of my post where I suggested what a possible solution could look like.