Thread: Tab completion of SET TRANSACTION ISOLATION
Some time ago, the tab completion code for the SET command was changed to read the list of available settings from the pg_settings table. This means that by the time you're done completing SET TRANSACTION ISOLATION, you've already sent a query and the command will be disallowed. It's not a major issue, but I figured I'd mention it since it confused me a while ago. If someone has an ingenious plan for working around this, let me know. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> Some time ago, the tab completion code for the SET command was changed
> to read the list of available settings from the pg_settings table.
> This means that by the time you're done completing SET TRANSACTION
> ISOLATION, you've already sent a query and the command will be
> disallowed. It's not a major issue, but I figured I'd mention it
> since it confused me a while ago. If someone has an ingenious plan
> for working around this, let me know.
Hm, that's a bit nasty.
The only plan I can think of involves reading the list of available
variable names in advance and keeping it around. However, I'm not
sure I want psql issuing such a query at connection startup whether
or not the info will ever be used :-(
We also have the ability to check the current in-transaction status,
so one possibility is to read the variable list only if not within
a transaction (and we didn't do it already in the current session).
Making the behavior of tab completion be state-dependent may seem
like a non-starter, but really it is anyway --- anything involving
a query will stop working in a failed transaction.
regards, tom lane
Is there any chance for psql opening a new session if it's inside a transaction and use that to do whatever querying is needed ? Just something like the control connection on ftp (analogy not very good). That could cause other surprises though (could fail for example due to too many connections open), and I have no idea about psql internals so it might be completely against it's philosophy... Cheers, Csaba. On Tue, 2006-01-31 at 15:29, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Some time ago, the tab completion code for the SET command was changed > > to read the list of available settings from the pg_settings table. > > This means that by the time you're done completing SET TRANSACTION > > ISOLATION, you've already sent a query and the command will be > > disallowed. It's not a major issue, but I figured I'd mention it > > since it confused me a while ago. If someone has an ingenious plan > > for working around this, let me know. > > Hm, that's a bit nasty. > > The only plan I can think of involves reading the list of available > variable names in advance and keeping it around. However, I'm not > sure I want psql issuing such a query at connection startup whether > or not the info will ever be used :-( > > We also have the ability to check the current in-transaction status, > so one possibility is to read the variable list only if not within > a transaction (and we didn't do it already in the current session). > Making the behavior of tab completion be state-dependent may seem > like a non-starter, but really it is anyway --- anything involving > a query will stop working in a failed transaction. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
On Tue, Jan 31, 2006 at 03:41:06PM +0100, Csaba Nagy wrote: > Is there any chance for psql opening a new session if it's inside a > transaction and use that to do whatever querying is needed ? Just > something like the control connection on ftp (analogy not very good). > That could cause other surprises though (could fail for example due to > too many connections open), and I have no idea about psql internals so > it might be completely against it's philosophy... Well, one problem there is that the connection could well have different parameters, like search_path. Granted, probably wouldn't matter in this case, but... Plus of course there's the cost of startup. Something that's asked for periodically is the ability to run things outside of a current transaction. The normal reply is to use DBLink, but if there was backend support for that it could probably be used here. But I suspect adding that ability would be a pretty large amount of work :( -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Csaba Nagy wrote: > Is there any chance for psql opening a new session if it's inside a > transaction and use that to do whatever querying is needed ? Just > something like the control connection on ftp (analogy not very good). > That could cause other surprises though (could fail for example due to > too many connections open), and I have no idea about psql internals so > it might be completely against it's philosophy... Perhaps not multiple connections, but multiple transactions per connection, like Oracle supports, AFAIK. All with a big ;-) of course. I doubt it would be easy to implement that. The assumption one-connection-has-one-transaction is probably pretty deeply burried in many backend components. Has this been changed by the prepared-transactions stuff? I may be mistaken, which would be very positive news. Best Regards, Michael Paesold
Michael Paesold wrote: > Perhaps not multiple connections, but multiple transactions per connection, > like Oracle supports, AFAIK. All with a big ;-) of course. I doubt it would > be easy to implement that. The assumption > one-connection-has-one-transaction is probably pretty deeply burried in > many backend components. Has this been changed by the prepared-transactions > stuff? I may be mistaken, which would be very positive news. No, you're not mistaken. The 2PC stuff works by reassigning the transaction to a sort-of "phantom backend". -- Alvaro Herrera http://www.advogato.org/person/alvherre "Llegará una época en la que una investigación diligente y prolongada sacará a la luz cosas que hoy están ocultas" (Séneca, siglo I)
Tom Lane <tgl@sss.pgh.pa.us> writes: > Hm, that's a bit nasty. > > The only plan I can think of involves reading the list of available > variable names in advance and keeping it around. However, I'm not > sure I want psql issuing such a query at connection startup whether > or not the info will ever be used :-( Well, it could just lazily cache the data if it's ever fetched. That would at least limit the occurrence of this problem to only happening once per connection. psql could also hard code "SET TRANSACTION ISOLATION" specifically. If psql knew that "SET TRANS" completes to SET TRANSACTION and "SET TRANSACTION I" completes to "SET TRANSACTION ISOLATION" it could avoid doing the query at all. That would only fail if someone uses TAB to view the available completions for "SET" or any shorter string. And since transaction isolation is strangely absent from the list of completions that seems like not such a big concern. If he's doing that he's not going to find it anyways. -- greg
Added to TODO:
o Prevent tab completion of SET TRANSACTION from querying the database and therefore preventing the
transactionisolation level from being set.
Currently, SET <tab> causes a database lookup to check all supported session variables. This query
causesproblems because setting the transaction isolation level must be the first statement of a
transaction.
---------------------------------------------------------------------------
Greg Stark wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > Hm, that's a bit nasty.
> >
> > The only plan I can think of involves reading the list of available
> > variable names in advance and keeping it around. However, I'm not
> > sure I want psql issuing such a query at connection startup whether
> > or not the info will ever be used :-(
>
> Well, it could just lazily cache the data if it's ever fetched. That would at
> least limit the occurrence of this problem to only happening once per
> connection.
>
> psql could also hard code "SET TRANSACTION ISOLATION" specifically. If psql
> knew that "SET TRANS" completes to SET TRANSACTION and "SET TRANSACTION I"
> completes to "SET TRANSACTION ISOLATION" it could avoid doing the query at
> all.
>
> That would only fail if someone uses TAB to view the available completions for
> "SET" or any shorter string. And since transaction isolation is strangely
> absent from the list of completions that seems like not such a big concern. If
> he's doing that he's not going to find it anyways.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
It could read all the SET variables in at startup? Peter Eisentraut wrote: > Some time ago, the tab completion code for the SET command was changed to read > the list of available settings from the pg_settings table. This means that > by the time you're done completing SET TRANSACTION ISOLATION, you've already > sent a query and the command will be disallowed. It's not a major issue, but > I figured I'd mention it since it confused me a while ago. If someone has an > ingenious plan for working around this, let me know. >
Christopher Kings-Lynne wrote: > It could read all the SET variables in at startup? Right, but do we want to do that even if they never ask for a tab completion? I think the easiest might be to just save the list on first tab call. --------------------------------------------------------------------------- > > Peter Eisentraut wrote: > > Some time ago, the tab completion code for the SET command was changed to read > > the list of available settings from the pg_settings table. This means that > > by the time you're done completing SET TRANSACTION ISOLATION, you've already > > sent a query and the command will be disallowed. It's not a major issue, but > > I figured I'd mention it since it confused me a while ago. If someone has an > > ingenious plan for working around this, let me know. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 2006-01-31 at 20:18 -0500, Bruce Momjian wrote: > Christopher Kings-Lynne wrote: > > It could read all the SET variables in at startup? > > Right, but do we want to do that even if they never ask for a tab > completion? I think the easiest might be to just save the list on first > tab call. As mentioned earlier the problem exists for all tab completion in aborted transactions. Perhaps a second database connection could be established during situations when running tab completion and other psql commands is impossible on the main one? --
Rod Taylor wrote: > On Tue, 2006-01-31 at 20:18 -0500, Bruce Momjian wrote: > > Christopher Kings-Lynne wrote: > > > It could read all the SET variables in at startup? > > > > Right, but do we want to do that even if they never ask for a tab > > completion? I think the easiest might be to just save the list on first > > tab call. > > As mentioned earlier the problem exists for all tab completion in > aborted transactions. > > Perhaps a second database connection could be established during > situations when running tab completion and other psql commands is > impossible on the main one? What if you need a password to be supplied? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 2006-01-31 at 20:32 -0500, Rod Taylor wrote: > Perhaps a second database connection could be established during > situations when running tab completion and other psql commands is > impossible on the main one? That would lead to inconsistencies, because of differences between the two sessions -- for example, one session's search path might be different from the other's. -Neil
On Tue, 2006-01-31 at 20:53 -0500, Bruce Momjian wrote: > Rod Taylor wrote: > > On Tue, 2006-01-31 at 20:18 -0500, Bruce Momjian wrote: > > > Christopher Kings-Lynne wrote: > > > > It could read all the SET variables in at startup? > > > > > > Right, but do we want to do that even if they never ask for a tab > > > completion? I think the easiest might be to just save the list on first > > > tab call. > > > > As mentioned earlier the problem exists for all tab completion in > > aborted transactions. > > > > Perhaps a second database connection could be established during > > situations when running tab completion and other psql commands is > > impossible on the main one? > > What if you need a password to be supplied? I believe psql keeps the password in memory. \c seems to be able to change databases without asking for the password again. --
> I believe psql keeps the password in memory. > > \c seems to be able to change databases without asking for the password > again. What if that role has a maximum of one connection, etc.? Chris
On Wed, 2006-02-01 at 10:28 +0800, Christopher Kings-Lynne wrote: > > I believe psql keeps the password in memory. > > > > \c seems to be able to change databases without asking for the password > > again. > > What if that role has a maximum of one connection, etc.? Considering it would only be used when the alternative was to say "Sorry, tab completion unavailable", I really don't see these as problems -- fall back to saying it cannot be done. --
Rod Taylor <pg@rbt.ca> writes:
> On Wed, 2006-02-01 at 10:28 +0800, Christopher Kings-Lynne wrote:
>> What if that role has a maximum of one connection, etc.?
> Considering it would only be used when the alternative was to say
> "Sorry, tab completion unavailable", I really don't see these as
> problems -- fall back to saying it cannot be done.
The point is that this can hardly be claimed to be a "zero failure mode"
implementation, any more than is the method of saving the tab completion
list after first successful fetch. Since the latter seems far simpler
and lower-overhead, I'd go with it...
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> Some time ago, the tab completion code for the SET command was changed
> to read the list of available settings from the pg_settings table.
> This means that by the time you're done completing SET TRANSACTION
> ISOLATION, you've already sent a query and the command will be
> disallowed.
Of course, there's always Plan B: revert that patch and go back to
a hard-coded list of variable names.
regards, tom lane