pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedqueries problems
6+ messages / 3 participants
[nested] [flat]
* queries problems
@ 2005-09-02 00:33 Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
0 siblings, 1 reply; 6+ messages in thread
From: Joÿffffffffffe3o Carvalho @ 2005-09-02 00:33 UTC (permalink / raw)
To: pgsql-sql
Hi
Im having problems doing some SQL queries.
I want information about the following statistics:
-List lock users
-Show schema owners, it may also so more schema information
-Check if a certain table has a toast table
If any one could help that would be very good.
Regards
__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: queries problems
@ 2005-09-02 00:50 Michael Fuhr <mike@fuhr.org>
parent: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-09-02 00:50 UTC (permalink / raw)
To: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>; +Cc: pgsql-sql
On Fri, Sep 02, 2005 at 12:33:00AM +0000, Joÿffffffffffe3o Carvalho wrote:
> I want information about the following statistics:
> -List lock users
> -Show schema owners, it may also so more schema information
> -Check if a certain table has a toast table
See "Monitoring Database Activity" and "System Catalogs" in the
documentation. Here are links to the documentation for the latest
released version of PostgreSQL:
http://www.postgresql.org/docs/8.0/static/monitoring.html
http://www.postgresql.org/docs/8.0/static/catalogs.html
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: queries problems
@ 2005-09-02 02:03 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-09-02 02:03 UTC (permalink / raw)
To: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>; +Cc: pgsql-sql
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]
On Thu, Sep 01, 2005 at 10:33:44PM -0300, João Carvalho wrote:
> Michael Fuhr <mike@fuhr.org> escreveu:
> > http://www.postgresql.org/docs/8.0/static/monitoring.html
> > http://www.postgresql.org/docs/8.0/static/catalogs.html
>
> Tanks a lot, it is part of that information in there. I resolved
> the schema problem and probably the toast table problem. but I still
> can´t get any information in there about the users of the locks and
> wich atributes are part of each index.
For locks, you can join pg_locks.pid with pg_stat_activity.procpid
and get user names from pg_stat_activity.usename.
http://www.postgresql.org/docs/8.0/static/view-pg-locks.html
http://www.postgresql.org/docs/8.0/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE
For index attributes, see pg_index.indkey and pg_attribute.attnum,
or the pg_get_indexdef() function.
http://www.postgresql.org/docs/8.0/static/catalog-pg-index.html
http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.0/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: queries problems
@ 2005-09-08 22:33 Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Joÿffffffffffe3o Carvalho @ 2005-09-08 22:33 UTC (permalink / raw)
To: pgsql-sql
The problem here is to compare int2vector with int2.
Regards
João Carvalho
Michael Fuhr <mike@fuhr.org> escreveu:
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]
On Thu, Sep 01, 2005 at 10:33:44PM -0300, João Carvalho wrote:
> Michael Fuhr escreveu:
> > http://www.postgresql.org/docs/8.0/static/monitoring.html
> > http://www.postgresql.org/docs/8.0/static/catalogs.html
>
> Tanks a lot, it is part of that information in there. I resolved
> the schema problem and probably the toast table problem. but I still
> can´t get any information in there about the users of the locks and
> wich atributes are part of each index.
For locks, you can join pg_locks.pid with pg_stat_activity.procpid
and get user names from pg_stat_activity.usename.
http://www.postgresql.org/docs/8.0/static/view-pg-locks.html
http://www.postgresql.org/docs/8.0/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE
For index attributes, see pg_index.indkey and pg_attribute.attnum,
or the pg_get_indexdef() function.
http://www.postgresql.org/docs/8.0/static/catalog-pg-index.html
http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.0/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
---------------------------------
Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: queries problems
@ 2005-09-08 23:54 Michael Fuhr <mike@fuhr.org>
parent: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-09-08 23:54 UTC (permalink / raw)
To: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>; +Cc: pgsql-sql
On Thu, Sep 08, 2005 at 10:33:48PM +0000, Joÿffffffffffe3o Carvalho wrote:
> The problem here is to compare int2vector with int2.
Presumably you're talking about joining pg_index.indkey against
pg_attribute.attnum -- is that correct? Will pg_get_indexdef() not
work for your needs?
I don't know if there's an easier way to check if an int2 is a
member of an int2vector, but you could write a function to convert
an int2vector to an int2 array and then use an "= ANY" expression.
The following seems a little ugly, but it works for me in simple
tests:
CREATE FUNCTION int2vector2array(int2vector) RETURNS int2[] AS $$
BEGIN
RETURN string_to_array(textin(int2vectorout($1)), ' ');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
SELECT 2::smallint = ANY (int2vector2array('1 2 3'));
?column?
----------
t
(1 row)
SELECT 4::smallint = ANY (int2vector2array('1 2 3'));
?column?
----------
f
(1 row)
Improvements or other suggestions welcome.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: queries problems
@ 2005-09-09 00:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-09-09 00:16 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>; pgsql-sql
Michael Fuhr <mike@fuhr.org> writes:
> I don't know if there's an easier way to check if an int2 is a
> member of an int2vector, but you could write a function to convert
> an int2vector to an int2 array and then use an "= ANY" expression.
FWIW, as of 8.1 an int2vector *is* an int2 array, so = ANY just works.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-09-09 00:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-02 00:33 queries problems Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
2005-09-02 00:50 ` Michael Fuhr <mike@fuhr.org>
2005-09-02 02:03 ` Michael Fuhr <mike@fuhr.org>
2005-09-08 22:33 ` Joÿffffffffffe3o Carvalho <joaocarvalho127@yahoo.com.br>
2005-09-08 23:54 ` Michael Fuhr <mike@fuhr.org>
2005-09-09 00:16 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox