Thread: hanging psql session while granting...

On Thu, 2002-02-14 at 13:45, Kumar, Lakshmi (Cognizant) wrote:

> How can i change the Grants over tables to other users ... is there any
> round about way ?
> Or where could be the problem.

First, I imagine you have permissions to make this.
When I want to change the permissions over a table, or tables, I use
this:

psql name_of_you_database < kk.sql | sed -e '1,3d' | awk '{print "GRANT
"$priv" ON "$1" TO "$user";"}'>gdornaout

psql motograndprix < gdornaout

You need to set $priv (privileges), $user (affected user) firts.

--
Manuel Trujillo         manueltrujillo@dorna.es
Technical Engineer      http://www.motograndprix.com
Dorna Sports S.L.       +34 93 4702864

HELP!!!

    Okay, now that I have your attention :).  I have a table with
about 560M rows in it.  Performance is usually pretty good except when I
do a specific query:

SELECT fooid FROM foo
WHERE bazid = 123456
ORDER BY score DESC LIMIT 100 ;

My index looks like:

CREATE INDEX foo_index ON foo (bazid, score) ;

This query should return the 100 highest scores out a possible 1.1M rows
(for the query that got me attention).  When I do an explain, I get:

# explain select fooid from foo where bazid = 123456 order by score desc
limit 100 ;
NOTICE:  QUERY PLAN:

Limit  (cost=1552.10..1552.10 rows=100 width=6)
  ->  Sort  (cost=1552.10..1552.10 rows=382 width=6)
        ->  Index Scan using foo_index on foo  (cost=0.00..1535.69
rows=382 width=6)

EXPLAIN

Doesn't look too bad, but I have a LOT of data where there is only a
single fooid for a bazid, which really skews the EXPLAIN results.  On
mysql (and I believe Oracle, gotta find my other hat), I could create the
index as:

CREATE INDEX foo_index ON foo (bazid, score desc) ;

Which would be exactly what I want, and would complete in a split second.
Instead, this thing runs FOREVER (okay, it just seems that way to my
client :).  Is there any way to get the equivalent index from PostgreSQL?
This is a major show stopper for me at this point.  I have looked through
Chapter 7 of the 'idocs', but I didn't find anything that would help.

- brian

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

Brian McCane <bmccane@mccons.net> writes:
> CREATE INDEX foo_index ON foo (bazid, score desc) ;

> Which would be exactly what I want, and would complete in a split second.
> Instead, this thing runs FOREVER (okay, it just seems that way to my
> client :).  Is there any way to get the equivalent index from PostgreSQL?

You don't need a funny index, you just need to get the planner to notice
that that index can serve to create the desired output ordering.  Try

create table foo(bazid int, score int);
CREATE INDEX foo_index ON foo (bazid, score) ;

explain select * from foo where bazid = 123456
order by bazid desc, score desc limit 100 ;

NOTICE:  QUERY PLAN:

Limit  (cost=0.00..17.07 rows=5 width=8)
  ->  Index Scan Backward using foo_index on foo  (cost=0.00..17.07 rows=5 width=8)

EXPLAIN


            regards, tom lane

Tom Lane wrote:
> Brian McCane <bmccane@mccons.net> writes:
> > CREATE INDEX foo_index ON foo (bazid, score desc) ;
>
> > Which would be exactly what I want, and would complete in a split second.
> > Instead, this thing runs FOREVER (okay, it just seems that way to my
> > client :).  Is there any way to get the equivalent index from PostgreSQL?
>
> You don't need a funny index, you just need to get the planner to notice
> that that index can serve to create the desired output ordering.  Try
>
> create table foo(bazid int, score int);
> CREATE INDEX foo_index ON foo (bazid, score) ;
>
> explain select * from foo where bazid = 123456
> order by bazid desc, score desc limit 100 ;
>
> NOTICE:  QUERY PLAN:
>
> Limit  (cost=0.00..17.07 rows=5 width=8)
>   ->  Index Scan Backward using foo_index on foo  (cost=0.00..17.07 rows=5 width=8)

Yes, I suspected it was the mixing of non-DESC and DESC that caused the
index to be ignored.  The user knows the index can be used because the
have specified 'col = constant' but the index doesn't code doesn't have
those details.  Certainly a case of:

    col >= 10 AND col <= 11

would not allow a secondary DESC column to be used, while a secondary
non-DESC column would be fine.  I suppose there is no automatic fix we
can do here except to ask users to use matching DESC when they are
testing or a constant.

In fact, I am unsure why you are specifying the primary column in the
ORDER BY anyway if you know it will be a single value, except perhaps to
try and get it to use the index, right?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In fact, I am unsure why you are specifying the primary column in the
> ORDER BY anyway if you know it will be a single value, except perhaps to
> try and get it to use the index, right?

Exactly.  The sort ordering of the index is (col1,col2) while the
query as originally written wanted an ordering of (col2 desc).
The planner's not smart enough to realize that since the WHERE
constrains col1 to a single value, you could pretend the query
requests an ordering of (col1 desc, col2 desc) which is compatible
with the index.

            regards, tom lane

On Thu, 14 Feb 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In fact, I am unsure why you are specifying the primary column in the
> > ORDER BY anyway if you know it will be a single value, except perhaps to
> > try and get it to use the index, right?
>
> Exactly.  The sort ordering of the index is (col1,col2) while the
> query as originally written wanted an ordering of (col2 desc).
> The planner's not smart enough to realize that since the WHERE
> constrains col1 to a single value, you could pretend the query
> requests an ordering of (col1 desc, col2 desc) which is compatible
> with the index.
>
>             regards, tom lane
>

The annoying thing is that it is smart enough to get the correct index, I
have another index that has the right col1, but different col2.  It seems
it should be possible to have the optimizer leave col1 in a "high
impedence" state, and then allow col2 to make a decision.  I have not
looked at the code, but if we have a known value for col1 as a constant,
I would think we could let subsequent information make the decision and
then just bubble it up to the top.

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

Here is another peeve

Having to specify the type of a constant in a SELECT DISTINCT 'foo'...

I which I had more spare time to contribute...

Jean-Luc


Tom Lane wrote:
>
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In fact, I am unsure why you are specifying the primary column in the
> > ORDER BY anyway if you know it will be a single value, except perhaps to
> > try and get it to use the index, right?
>
> Exactly.  The sort ordering of the index is (col1,col2) while the
> query as originally written wanted an ordering of (col2 desc).
> The planner's not smart enough to realize that since the WHERE
> constrains col1 to a single value, you could pretend the query
> requests an ordering of (col1 desc, col2 desc) which is compatible
> with the index.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Brian McCane wrote:
> On Thu, 14 Feb 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > In fact, I am unsure why you are specifying the primary column in the
> > > ORDER BY anyway if you know it will be a single value, except perhaps to
> > > try and get it to use the index, right?
> >
> > Exactly.  The sort ordering of the index is (col1,col2) while the
> > query as originally written wanted an ordering of (col2 desc).
> > The planner's not smart enough to realize that since the WHERE
> > constrains col1 to a single value, you could pretend the query
> > requests an ordering of (col1 desc, col2 desc) which is compatible
> > with the index.
> >
> >             regards, tom lane
> >
>
> The annoying thing is that it is smart enough to get the correct index, I
> have another index that has the right col1, but different col2.  It seems
> it should be possible to have the optimizer leave col1 in a "high
> impedence" state, and then allow col2 to make a decision.  I have not
> looked at the code, but if we have a known value for col1 as a constant,
> I would think we could let subsequent information make the decision and
> then just bubble it up to the top.

Tom, do we have a TODO item here?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, do we have a TODO item here?

I'm not excited about it; it seems a weird case and there's a simple
query change to get the right thing to happen.  AFAICS we'd have to
expend a lot of planner cycles to detect this case, and most of the
time those cycles would be wasted.

            regards, tom lane

> hello ,
>    Am facing a problem (strange or not, am not sure).
> When i loginto psql as user x
> and execute "Grant select on <table> to <usrY>"
> it was hanging... am not able to grant any of the
tables to any of the users
> available.
>
> I cud grant tables of another user to other user...
>
> How can i change the Grants over tables to other
users ... is there any
> round about way ?
> Or where could be the problem.
>
>
> Thanks
> laks


You can´t do that. Only the postgres super-user or the
table creator can change this permissions.



Marco Tulio
Desenvolvimento de sistemas para Internet


__________________________________________________________________________
Quer ter seu próprio endereço na Internet?
Garanta já o seu e ainda ganhe cinco e-mails personalizados.
DomíniosBOL - http://dominios.bol.com.br