Thread: 1.8.4 bug DB Restriction field

1.8.4 bug DB Restriction field

From
Zach Conrad
Date:
DB Restriction field borks on datname IN ('blah') or datname='blah' with the error: "ERROR:  operator does not exist:
name<> boolean LINE 5:  WHERE datname NOT IN (datname='blah')
 

Here's the full query from the logs being sent from pgAdmin:
SELECT db.oid, datname, db.dattablespace AS spcoid, spcname, datallowconn, datconfig, datacl,
pg_encoding_to_char(encoding)AS serverencoding, pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid,
'CREATE')as cancreate, current_setting('default_tablespace') AS default_tablespace FROM pg_database db LEFT OUTER JOIN
pg_tablespaceta ON db.dattablespace=ta.OID WHERE datname NOT IN (datname='blah')
 

Sincerely,

Zach Conrad


Re: 1.8.4 bug DB Restriction field

From
Guillaume Lelarge
Date:
Zach Conrad a écrit :
> DB Restriction field borks on datname IN ('blah') or datname='blah' with the error: "ERROR:  operator does not exist:
name<> boolean LINE 5:  WHERE datname NOT IN (datname='blah')
 
> 
> Here's the full query from the logs being sent from pgAdmin:
> SELECT db.oid, datname, db.dattablespace AS spcoid, spcname, datallowconn, datconfig, datacl,
pg_encoding_to_char(encoding)AS serverencoding, pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid,
'CREATE')as cancreate, current_setting('default_tablespace') AS default_tablespace FROM pg_database db LEFT OUTER JOIN
pg_tablespaceta ON db.dattablespace=ta.OID WHERE datname NOT IN (datname='blah')
 
> 

The DB Restriction field should not contain : datname IN ('blah')

It should contain : 'blah'
or : 'foo','bar'

Regards.


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com


Re: 1.8.4 bug DB Restriction field

From
Zach Conrad
Date:
Thank you for your quick response.

There are a few things this brings up:

1. The pgAdmin help file states the use of datname IN ('blah') in the DB Restriction field
2. Most people know which databases they want to connect to. Wouldn't it make  more sense to use datename IN (...)
ratherthan NOT IN? 

Thank you all for creating a great application!

Sincerely,

Zach Conrad

----- Original Message -----
From: "Guillaume Lelarge" <guillaume@lelarge.info>
To: "Zach Conrad" <zach.conrad@digitecinc.com>
Cc: pgadmin-support@postgresql.org
Sent: Thursday, June 5, 2008 3:31:55 PM GMT -06:00 US/Canada Central
Subject: Re: [pgadmin-support] 1.8.4 bug DB Restriction field

Zach Conrad a écrit :
> DB Restriction field borks on datname IN ('blah') or datname='blah' with the error: "ERROR:  operator does not exist:
name<> boolean LINE 5:  WHERE datname NOT IN (datname='blah') 
>
> Here's the full query from the logs being sent from pgAdmin:
> SELECT db.oid, datname, db.dattablespace AS spcoid, spcname, datallowconn, datconfig, datacl,
pg_encoding_to_char(encoding)AS serverencoding, pg_get_userbyid(datdba) AS datowner,has_database_privilege(db.oid,
'CREATE')as cancreate, current_setting('default_tablespace') AS default_tablespace FROM pg_database db LEFT OUTER JOIN
pg_tablespaceta ON db.dattablespace=ta.OID WHERE datname NOT IN (datname='blah') 
>

The DB Restriction field should not contain : datname IN ('blah')

It should contain : 'blah'
or : 'foo','bar'

Regards.


--
Guillaume. http://www.postgresqlfr.org http://dalibo.com


Re: 1.8.4 bug DB Restriction field

From
"Michael Shapiro"
Date:
I think there is a problem with the syntax -- highlighted below<br /><br />It seems that<br /><div style="margin-left:
40px;"> datnameNOT in (datname='blah')<br /><br /></div>should just be<br /><div style="margin-left: 40px;">  datname
NOTin ('blah')<br /><br /><br /></div><div style="margin-left: 40px;">SELECT db.oid, datname, db.dattablespace AS
spcoid,spcname, datallowconn, datconfig, datacl, pg_encoding_to_char(encoding) AS serverencoding,
pg_get_userbyid(datdba)AS datowner,has_database_privileg<div class="ArwC7c ckChnd" id="1exj">e(db.oid, 'CREATE') as
cancreate,current_setting('default_tablespace') AS default_tablespace FROM pg_database db LEFT OUTER JOIN pg_tablespace
taON db.dattablespace=ta.OID WHERE datname NOT IN (<b>datname='blah'</b>)</div></div><br /><div class="gmail_quote">On
Thu,Jun 5, 2008 at 3:43 PM, Zach Conrad <<a
href="mailto:zach.conrad@digitecinc.com">zach.conrad@digitecinc.com</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Thank you for your
quickresponse.<br /><br /> There are a few things this brings up:<br /><br /> 1. The pgAdmin help file states the use
ofdatname IN ('blah') in the DB Restriction field<br /> 2. Most people know which databases they want to connect to.
Wouldn'tit make<br />   more sense to use datename IN (...) rather than NOT IN?<br /><br /> Thank you all for creating
agreat application!<br /><br /> Sincerely,<br /><br /> Zach Conrad<br /><br /> ----- Original Message -----<br /> From:
"GuillaumeLelarge" <<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>><br /> To: "Zach Conrad"
<<ahref="mailto:zach.conrad@digitecinc.com">zach.conrad@digitecinc.com</a>><br /> Cc: <a
href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a><br/> Sent: Thursday, June 5, 2008
3:31:55PM GMT -06:00 US/Canada Central<br /> Subject: Re: [pgadmin-support] 1.8.4 bug DB Restriction field<br /><br />
ZachConrad a écrit :<br /> > DB Restriction field borks on datname IN ('blah') or datname='blah' with the error:
"ERROR: operator does not exist: name <> boolean LINE 5:  WHERE datname NOT IN (datname='blah')<br /> ><br />
>Here's the full query from the logs being sent from pgAdmin:<br /> > SELECT db.oid, datname, db.dattablespace AS
spcoid,spcname, datallowconn, datconfig, datacl, pg_encoding_to_char(encoding) AS serverencoding,
pg_get_userbyid(datdba)AS datowner,has_database_privilege(db.oid, 'CREATE') as cancreate,
current_setting('default_tablespace')AS default_tablespace FROM pg_database db LEFT OUTER JOIN pg_tablespace ta ON
db.dattablespace=ta.OIDWHERE datname NOT IN (datname='blah')<br /> ><br /><br /> The DB Restriction field should not
contain: datname IN ('blah')<br /><br /> It should contain : 'blah'<br /> or : 'foo','bar'<br /><br /> Regards.<br
/><br/><br /> --<br /> Guillaume.<br />  <a href="http://www.postgresqlfr.org"
target="_blank">http://www.postgresqlfr.org</a><br/>  <a href="http://dalibo.com"
target="_blank">http://dalibo.com</a><br/><font color="#888888"><br /> --<br /> Sent via pgadmin-support mailing list
(<ahref="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgadmin-support"
target="_blank">http://www.postgresql.org/mailpref/pgadmin-support</a><br/></font></blockquote></div><br /> 

Re: 1.8.4 bug DB Restriction field

From
Guillaume Lelarge
Date:
Zach Conrad a écrit :
> Thank you for your quick response.
> 
> There are a few things this brings up:
> 
> 1. The pgAdmin help file states the use of datname IN ('blah') in the DB Restriction field
> 2. Most people know which databases they want to connect to. Wouldn't it make
>    more sense to use datename IN (...) rather than NOT IN?
> 

OK, it seems my bugfix was wrong. And I suppose the one on schema 
restriction is also wrong.

My patch is more user friendly (you don't have to know the name of 
pg_database's columns), but it allows less stuff (you can just give 
databases' names, you won't be able to filter on columns other than 
datname). It seems a regression to me, sorry about this.

Dave, what do you think I should do ? remove the patch ?

Regards.


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com


Re: 1.8.4 bug DB Restriction field

From
"Dave Page"
Date:
On Fri, Jun 6, 2008 at 3:12 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Zach Conrad a écrit :
>>
>> Thank you for your quick response.
>>
>> There are a few things this brings up:
>>
>> 1. The pgAdmin help file states the use of datname IN ('blah') in the DB
>> Restriction field
>> 2. Most people know which databases they want to connect to. Wouldn't it
>> make
>>   more sense to use datename IN (...) rather than NOT IN?
>>
>
> OK, it seems my bugfix was wrong. And I suppose the one on schema
> restriction is also wrong.
>
> My patch is more user friendly (you don't have to know the name of
> pg_database's columns), but it allows less stuff (you can just give
> databases' names, you won't be able to filter on columns other than
> datname). It seems a regression to me, sorry about this.
>
> Dave, what do you think I should do ? remove the patch ?

Just remove the NOT?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com


Re: 1.8.4 bug DB Restriction field

From
Guillaume Lelarge
Date:
Dave Page a écrit :
> On Fri, Jun 6, 2008 at 3:12 PM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
>> Zach Conrad a écrit :
>>> Thank you for your quick response.
>>>
>>> There are a few things this brings up:
>>>
>>> 1. The pgAdmin help file states the use of datname IN ('blah') in the DB
>>> Restriction field
>>> 2. Most people know which databases they want to connect to. Wouldn't it
>>> make
>>>   more sense to use datename IN (...) rather than NOT IN?
>>>
>> OK, it seems my bugfix was wrong. And I suppose the one on schema
>> restriction is also wrong.
>>
>> My patch is more user friendly (you don't have to know the name of
>> pg_database's columns), but it allows less stuff (you can just give
>> databases' names, you won't be able to filter on columns other than
>> datname). It seems a regression to me, sorry about this.
>>
>> Dave, what do you think I should do ? remove the patch ?
> 
> Just remove the NOT?
> 

I can do this. But, for example, if someone was using this filter field 
to get out template databases, removing the NOT won't fix this.


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com


Re: 1.8.4 bug DB Restriction field

From
"Dave Page"
Date:
On Fri, Jun 6, 2008 at 3:22 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:

>>> Dave, what do you think I should do ? remove the patch ?
>>
>> Just remove the NOT?
>>
>
> I can do this. But, for example, if someone was using this filter field to
> get out template databases, removing the NOT won't fix this.

Hmm, good point. The original intent behind the feature was for
teaching environments in which there may be one database for each
student, so the students could limit their list to just their own
database without seeing all their schoolmates as well. I think that's
probably the most important case to fix (which removing the NOT should
do), as those people will likely have *lot's* of clutter otherwise.

Alternatively, you could make the NOT optional with a checkbox.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com


Re: 1.8.4 bug DB Restriction field

From
Zach Conrad
Date:
Dave, Guillaume,

What I have grown accustomed to has been the ability to limit the connection to a particular db or db's instead of
seeingall the databases I don't need to work with on that connection. I would be in favor of dropping the NOT. 
 

Perhaps this is an UI issue where the DB Restriction field is trying to do too much. Maybe there could be two fields:
1. Listed Databases (comma separated list of database names)
2. Filters (uses namespaces for filtering)

I could see Filters as a separate tab (Properties | Filters | SQL) for the connection properties, similar to how a
tableproperties tabs work, specifically the Column tab where you could have the Add button add a new filter. If someone
reallywants that much control over the connection, they can have it. Generally speaking, the Listed Databases field is
aFilter, but less confusing to general users.
 

Sincerely,

Zach Conrad

----- Original Message -----
From: "Dave Page" <dpage@pgadmin.org>
To: "Guillaume Lelarge" <guillaume@lelarge.info>
Cc: "Zach Conrad" <zach.conrad@digitecinc.com>, pgadmin-support@postgresql.org
Sent: Friday, June 6, 2008 9:26:57 AM GMT -06:00 US/Canada Central
Subject: Re: [pgadmin-support] 1.8.4 bug DB Restriction field

On Fri, Jun 6, 2008 at 3:22 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:

>>> Dave, what do you think I should do ? remove the patch ?
>>
>> Just remove the NOT?
>>
>
> I can do this. But, for example, if someone was using this filter field to
> get out template databases, removing the NOT won't fix this.

Hmm, good point. The original intent behind the feature was for
teaching environments in which there may be one database for each
student, so the students could limit their list to just their own
database without seeing all their schoolmates as well. I think that's
probably the most important case to fix (which removing the NOT should
do), as those people will likely have *lot's* of clutter otherwise.

Alternatively, you could make the NOT optional with a checkbox.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com


Re: 1.8.4 bug DB Restriction field

From
Guillaume Lelarge
Date:
Dave Page a écrit :
> On Fri, Jun 6, 2008 at 3:22 PM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
> 
>>>> Dave, what do you think I should do ? remove the patch ?
>>> Just remove the NOT?
>>>
>> I can do this. But, for example, if someone was using this filter field to
>> get out template databases, removing the NOT won't fix this.
> 
> Hmm, good point. The original intent behind the feature was for
> teaching environments in which there may be one database for each
> student, so the students could limit their list to just their own
> database without seeing all their schoolmates as well. I think that's
> probably the most important case to fix (which removing the NOT should
> do), as those people will likely have *lot's* of clutter otherwise.
> 

I propose doing this for next 1.8 release as a bug fix...

> Alternatively, you could make the NOT optional with a checkbox.
> 

... and this as new feature in the dev release. Or I can add a new tab, 
as suggested by Zach Conrad.

Comments?


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com


Re: 1.8.4 bug DB Restriction field

From
"Dave Page"
Date:
On Wed, Jun 11, 2008 at 9:06 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
>> Hmm, good point. The original intent behind the feature was for
>> teaching environments in which there may be one database for each
>> student, so the students could limit their list to just their own
>> database without seeing all their schoolmates as well. I think that's
>> probably the most important case to fix (which removing the NOT should
>> do), as those people will likely have *lot's* of clutter otherwise.
>>
>
> I propose doing this for next 1.8 release as a bug fix...

By all means commit the change, but my suspicion is that there won't
be another 1.8 release. I don't think we've ever done a .5...

>> Alternatively, you could make the NOT optional with a checkbox.
>>
>
> ... and this as new feature in the dev release. Or I can add a new tab, as
> suggested by Zach Conrad.
>
> Comments?

I think the checkbox would suffice. I don't think this is a widely
used feature that requires significant effort.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com


Re: 1.8.4 bug DB Restriction field

From
Guillaume Lelarge
Date:
Dave Page a écrit :
> On Wed, Jun 11, 2008 at 9:06 AM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
>>> Hmm, good point. The original intent behind the feature was for
>>> teaching environments in which there may be one database for each
>>> student, so the students could limit their list to just their own
>>> database without seeing all their schoolmates as well. I think that's
>>> probably the most important case to fix (which removing the NOT should
>>> do), as those people will likely have *lot's* of clutter otherwise.
>>>
>> I propose doing this for next 1.8 release as a bug fix...
> 
> By all means commit the change, but my suspicion is that there won't
> be another 1.8 release. I don't think we've ever done a .5...
> 

Done.

>>> Alternatively, you could make the NOT optional with a checkbox.
>>>
>> ... and this as new feature in the dev release. Or I can add a new tab, as
>> suggested by Zach Conrad.
>>
>> Comments?
> 
> I think the checkbox would suffice. I don't think this is a widely
> used feature that requires significant effort.
> 

This remains on my TODO list.


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com