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.
Show quoted text
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?
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 transaction isolation
level from being set.
Currently, SET <tab> causes a database lookup to check all
supported session variables. This query causes problems
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, Pennsylvania 19073
It could read all the SET variables in at startup?
Peter Eisentraut wrote:
Show quoted text
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, Pennsylvania 19073
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, Pennsylvania 19073
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