Обсуждение: Where to get column length

I know it is in the docs, but right now I can't see it.

How do I pull the length of a specific varchar column?  I have just found a problem with our schema being out of sync on one specific column, and need to find out how many of our 200+ databases need to be fixed.

Here is what I have so far:
select *
from pg_class, pg_attribute
where pg_class.relkind = 'r' and
pg_attribute.attrelid = pg_class.oid and
pg_class.relname = 'mytable' and
pg_attribute.attname = 'problem_column' and
???? != 256;  -- need to report anytime this column is not a varchar(256).

Thanks,

Chris

Chris Hoover wrote:
> I know it is in the docs, but right now I can't see it.
>
> How do I pull the length of a specific varchar column?  I have just found a
> problem with our schema being out of sync on one specific column, and need
> to find out how many of our 200+ databases need to be fixed.
>
> Here is what I have so far:
> select *
> from pg_class, pg_attribute
> where pg_class.relkind = 'r' and
> pg_attribute.attrelid = pg_class.oid and
> pg_class.relname = 'mytable' and
> pg_attribute.attname = 'problem_column' and
> ???? != 256;  -- need to report anytime this column is not a varchar(256).

pg_attribute.atttypmod <> 256 + 4

(your 256 plus fixed 4 bytes of overhead for all variable length
attributes).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330,
1.8 Ghz
CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once
/ day.

I am watching a recently created database grow; as it grows, I am finding
that some of the performance statistics appear to be falling out of bed. The
functions I have used
to capture this information are the standard functions that come with
PostgreSQL:

    date      commits   rollbks  dsk_reads   mem_reads  pct_mem_hits
db_size
 2006-06-19  94115102        64  553053905  13126498559   95.9600    "1674
MB"
 (server restarted)
 2006-06-20   4383600        26   24129603   1162150532   97.9700    "1471
MB"
 2006-06-21   9179799        27   56084142   2456950412   97.7700    "1587
MB"
 2006-06-22  14447111        28   89452397   3875993962   97.7400    "1710
MB"
 2006-06-23  20233946        47  128309666   5553425498   97.7400    "1858
MB"
 2006-06-26  34252036      4765  237496776   9024547005   97.4400    "2218
MB"
 2006-06-27  40290065    403368  273062334   9156477077   97.1000    "2401
MB"
 2006-06-28  46436389    870211  307983449   9260208418   96.7800    "2497
MB"
 2006-06-29  52251198   1352370  348552701   9367093206   96.4100    "2575
MB"
 2006-06-30  58585373   1839034  392822069   9477661079   96.0200    "2647
MB"
 2006-07-03  75747589   3318388  551767504   9816311112   94.6800    "2815
MB"
 2006-07-05  87768328   4524047  678763032  10076029919   93.6900    "2973
MB"
 (server restarted - shared_buffers changed)
 2006-07-06   4841006    326389   50641814    121507743   70.5800    "3031
MB"
 2006-07-07  10711794    732981  113816094    274683161   70.7000    "3076
MB"
 2006-07-10  19428743   1300797  287848710    517770353   64.2700    "3452
MB"

My questions concerning these statistics:
(1) This application monitors networks. On 6/26, it began monitoring a
number of
    new network nodes (traffic increase of 40 - 50%); but the application
itself
has not been changed, either in terms of number of users, program or
database
changes, or other changes. Yet, the rollback column has increased
substantially.
    QUESTION: is there a database setting that can affect this statistic?

(2) I noticed that pct_mem_hits was dropping in early July at about the same
time
    that the platform started monitoring additional network nodes. On late
July
5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits
continue to decline.
    QUESTION: I evidently touched the wrong thing. What should I be
adjusting to
    help keep the database in memory?

All other statistics appear to be normal.

Check the stats at the end of your vacuum to ensure your max_fsm_pages (free space map) is large enough. Also check work_mem and maintenance_work_mem are not running at defaults that may be too small. If you have many updates, increase the number of wal_buffers and checkpoint_segments.

I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330,
1.8 Ghz CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once
/ day.

I am watching a recently created database grow; as it grows, I am finding
that some of the performance statistics appear to be falling out of bed. The
functions I have used  to capture this information are the standard functions 
that come with PostgreSQL:

   date      commits   rollbks  dsk_reads   mem_reads  pct_mem_hits
db_size
2006-06-19  94115102        64  553053905  13126498559   95.9600    "1674
MB
(server restarted)
2006-06-20   4383600        26   24129603   1162150532   97.9700    1471
MB
2006-06-21   9179799        27   56084142   2456950412   97.7700    1587
MB
2006-06-22  14447111        28   89452397   3875993962   97.7400    1710
MB
2006-06-23  20233946        47  128309666   5553425498   97.7400   1858
MB
2006-06-26  34252036      4765  237496776   9024547005   97.4400  2218
MB
2006-06-27  40290065    403368  273062334   9156477077   97.1000    2401
MB
2006-06-28  46436389    870211  307983449   9260208418   96.7800    2497
MB
2006-06-29  52251198   1352370  348552701   9367093206   96.4100    2575
MB
2006-06-30  58585373   1839034  392822069   9477661079   96.0200    2647
MB
2006-07-03  75747589   3318388  551767504   9816311112   94.6800    2815
MB
2006-07-05  87768328   4524047  678763032  10076029919   93.6900   2973
MB
(server restarted - shared_buffers changed)
2006-07-06   4841006    326389   50641814    121507743   70.5800    3031
MB
2006-07-07  10711794    732981  113816094    274683161   70.7000   3076
MB
2006-07-10  19428743   1300797  287848710    517770353   64.2700  3452
MB

My questions concerning these statistics:
(1) This application monitors networks. On 6/26, it began monitoring a
number of  new network nodes (traffic increase of 40 - 50%); but the application
itself  has not been changed, either in terms of number of users, program or
database  changes, or other changes. Yet, the rollback column has increased
substantially.
   QUESTION: is there a database setting that can affect this statistic?

(2) I noticed that pct_mem_hits was dropping in early July at about the same
time that the platform started monitoring additional network nodes. On late
July5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits continues  to decline.
   QUESTION: I evidently touched the wrong thing. What should I be
adjusting to help keep the database in memory?

All other statistics appear to be normal.