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/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster