Horton
Menu

Thread

  1. Tab completion of SET TRANSACTION ISOLATION

    Peter Eisentraut <peter_e@gmx.net> — 2006-01-31T12:21:07Z

    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/

  2. Re: Tab completion of SET TRANSACTION ISOLATION

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-01-31T14:29:30Z

    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

  3. Re: Tab completion of SET TRANSACTION ISOLATION

    Csaba Nagy <nagy@ecircle-ag.com> — 2006-01-31T14:41:06Z

    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

  4. Re: Tab completion of SET TRANSACTION ISOLATION

    Jim Nasby <jnasby@pervasive.com> — 2006-01-31T19:11:51Z

    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

  5. Re: Tab completion of SET TRANSACTION ISOLATION

    Michael Paesold <mpaesold@gmx.at> — 2006-01-31T20:04:34Z

    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

  6. Re: Tab completion of SET TRANSACTION ISOLATION

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2006-01-31T20:16:13Z

    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)

  7. Re: Tab completion of SET TRANSACTION ISOLATION

    Greg Stark <gsstark@mit.edu> — 2006-01-31T21:19:18Z

    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

  8. Re: Tab completion of SET TRANSACTION ISOLATION

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-02-01T00:03:21Z

    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

  9. Re: Tab completion of SET TRANSACTION ISOLATION

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-01T01:15:47Z

    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.
>

  10. Re: Tab completion of SET TRANSACTION ISOLATION

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-02-01T01:18:33Z

    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

  11. Re: Tab completion of SET TRANSACTION ISOLATION

    Rod Taylor <pg@rbt.ca> — 2006-02-01T01:32:42Z

    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?
--

  12. Re: Tab completion of SET TRANSACTION ISOLATION

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-02-01T01:53:39Z

    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

  13. Re: Tab completion of SET TRANSACTION ISOLATION

    Neil Conway <neilc@samurai.com> — 2006-02-01T02:17:51Z

    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

  14. Re: Tab completion of SET TRANSACTION ISOLATION

    Rod Taylor <pg@rbt.ca> — 2006-02-01T02:25:06Z

    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.

--

  15. Re: Tab completion of SET TRANSACTION ISOLATION

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-02-01T02:28:15Z

    > 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

  16. Re: Tab completion of SET TRANSACTION ISOLATION

    Rod Taylor <pg@rbt.ca> — 2006-02-01T02:30:23Z

    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.

--

  17. Re: Tab completion of SET TRANSACTION ISOLATION

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-02-01T05:56:00Z

    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

  18. Re: Tab completion of SET TRANSACTION ISOLATION

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-02-01T06:23:34Z

    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