[HACKERS] 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
Peter Eisentraut [EMAIL PROTECTED] 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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 [EMAIL PROTECTED] 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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 Herrerahttp://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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
Tom Lane [EMAIL PROTECTED] 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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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? -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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. -- ---(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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
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. -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
Rod Taylor [EMAIL PROTECTED] 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 ---(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
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
