Thread: Likely bug in executing pgScript on wrong database
I've found what appears to be a bug in the way pgAdmin III executes pgScript, which can cause it to run scripts on the wrong database. I couldn't find any records of the bug in your Trac site, so I'm reporting it here.
Here's how to reproduce. In this example, assume I have two databases called 'one' and 'two' on a PostgreSQL server:
- Open pgAdmin III
- Connect to a server and click on database 'one' in the object browser
- Click the SQL button in the toolbar to open a query window
- Click the dropdown at the top of the query window, choose <new connection>
- Choose database 'two' and click OK to connect
- The query window (and dropdown) now shows that I am connected to database 'two'
- Write some SQL in the query window and execute it -- it executes on database 'two'
- Write some pgScript SQL in the query window and execute it -- it executes on database 'one'
In other words, it seems that pgScript is executed on the original connection that existed when the query window was opened, not the current connection that you have selected later. The problem occurs regardless of whether the connections are to the same server or different servers. As you might imagine, this caused some confusion and heartache here for us!
A simple way to test this is to use the current_database() function in your SQL like this:
-- SQL
select current_database();
-- pgScript
declare @db;
set @db = select current_database();
print @db;
You should find that they return different results if you've followed the steps above.
My configuration is as follows:
Mac OS X version 10.6.8
pgAdmin III version 1.12.2
Tested with two servers, same problem occurs with both:
PostgreSQL 8.3.14 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370)
PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
Cheers
Charles
On Wed, 2012-02-29 at 11:22 +1100, Charles Gutjahr wrote: > I've found what appears to be a bug in the way pgAdmin III executes pgScript, which can cause it to run scripts on thewrong database. I couldn't find any records of the bug in your Trac site, so I'm reporting it here. > > Here's how to reproduce. In this example, assume I have two databases called 'one' and 'two' on a PostgreSQL server: > > * Open pgAdmin III > * Connect to a server and click on database 'one' in the object browser > * Click the SQL button in the toolbar to open a query window > * Click the dropdown at the top of the query window, choose <new connection> > * Choose database 'two' and click OK to connect > * The query window (and dropdown) now shows that I am connected to database 'two' > * Write some SQL in the query window and execute it -- it executes on database 'two' > * Write some pgScript SQL in the query window and execute it -- it executes on database 'one' > > In other words, it seems that pgScript is executed on the original connection that existed when the query window was opened,not the current connection that you have selected later. The problem occurs regardless of whether the connectionsare to the same server or different servers. As you might imagine, this caused some confusion and heartache herefor us! > > A simple way to test this is to use the current_database() function in your SQL like this: > > -- SQL > select current_database(); > > -- pgScript > declare @db; > set @db = select current_database(); > print @db; > > You should find that they return different results if you've followed the steps above. > Yes, it's definitely a bug. I fixed it a few minutes ago. Thanks for your report. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com