Thread: queries problems

__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/

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

[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

Is there anyway to determine when a database was last accessed, or when 
a user last connected to the database using the pg_* views? I'm trying 
to determine what databases in my system are currently active.

[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

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

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