Re: [GENERAL] glitch installing xml support in 9.1.beta2

[Guillaume Lelarge]

On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote:
> OpenSuse 11.4 x86-64
> 
> gmake install  builds and places the requisite pieces as expected.
> 
> Running 
> psql --username postgres -d postgres -f xml2--1.0,sql
> results in 
> psql:xml2--1.0.sql:8: ERROR:  function "xml_valid" already
> exists with same argument types
> psql:xml2--1.0.sql:12: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> psql:xml2--1.0.sql:16: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> psql:xml2--1.0.sql:20: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> psql:xml2--1.0.sql:24: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> psql:xml2--1.0.sql:28: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> psql:xml2--1.0.sql:34: ERROR:  could not access file
> "MODULE_PATHNAME": No such file or directory
> (This from second run, so xml_valid worked the first time).  
> 
> I see that the xml2.control file defines module_pathname =
> '$libdir/pgxml', but how is that passed to the psql run? Or am I to
> manually update the sql scripts?
>  .

You don't use it explicitely. You use "CREATE EXTENSION", and it will
use it.

psql --username postgres -d postgres -c "CREATE EXTENSION xml2"

and it should work. That's one of the great things in 9.1.

> 
> Here's my configuration of the main build
> ./configure --with-libraries=/usr/local/lib --with-python
> --with-openssl --with-ldap --with-ossp-uuid --with-libxml
> --with-libxslt --enable-debug --prefix=/opt/PostgreSQL/9.1
> 
> Haven't gone into uuid stuff yet...
> 
> Cheers,
> 


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] glitch installing xml support in 9.1.beta2

[Rob Sargent]

Guillaume Lelarge wrote:

On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote:
  

OpenSuse 11.4 x86-64

gmake install  builds and places the requisite pieces as expected.

Running 
psql --username postgres -d postgres -f xml2--1.0,sql
results in 
psql:xml2--1.0.sql:8: ERROR:  function "xml_valid" already

exists with same argument types
psql:xml2--1.0.sql:12: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
psql:xml2--1.0.sql:16: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
psql:xml2--1.0.sql:20: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
psql:xml2--1.0.sql:24: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
psql:xml2--1.0.sql:28: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
psql:xml2--1.0.sql:34: ERROR:  could not access file
"MODULE_PATHNAME": No such file or directory
(This from second run, so xml_valid worked the first time).  


I see that the xml2.control file defines module_pathname =
'$libdir/pgxml', but how is that passed to the psql run? Or am I to
manually update the sql scripts?
 .



You don't use it explicitely. You use "CREATE EXTENSION", and it will
use it.

psql --username postgres -d postgres -c "CREATE EXTENSION xml2"

and it should work. That's one of the great things in 9.1.

  

Very cool. Obviously I missed a release note or two.

Thanks


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] blks_read/blks_hit stats

[Nigel Heron]

On 11-06-15 12:38 PM, Nigel Heron wrote:

Hi everyone,

I'm playing with the stats views and functions to graph them in cacti..
Adding up *_blks_hit (heap, idx, toast and tidx) from pg_statio 
doesn't match blks_hit in pg_stat_database.
Sometimes the sum is higher, sometimes lower. Do they have similar 
names but represent different metrics?

Same issue with blks_read.

eg.
SELECT
SUM(pg_statio_all_tables.heap_blks_hit)::bigint +
SUM(pg_statio_all_tables.idx_blks_hit)::bigint +
SUM(pg_statio_all_tables.toast_blks_hit)::bigint +
SUM(pg_statio_all_tables.tidx_blks_hit)::bigint AS blks_hit
FROM pg_statio_all_tables;
  blks_hit

 1275299563
(1 row)


SELECT blks_hit
FROM pg_stat_database
where datname='mydb';
  blks_hit
---
 674295210
(1 row)


here's 2 graphs from different databases on the same cluster (8.4.2).
first 4 stacked graph items are from pg_statio_all_tables and the red 
line is from pg_stat_database.


blks_hit is way under the sum:

blks_hit seems pretty close to the sum of table+idx (but no toast):



I understand now.. querying pg_statio_all_tables includes stats from the 
cluster wide objects (pg_database, etc.), those stats increase from 
queries on other databases as well, hence the gap between the sum and 
blks_hit from pg_stat_database.


I think changing the 1st query to this should do the trick..

SELECT
SUM(pg_statio_all_tables.heap_blks_hit)::bigint +
SUM(pg_statio_all_tables.idx_blks_hit)::bigint +
SUM(pg_statio_all_tables.toast_blks_hit)::bigint +
SUM(pg_statio_all_tables.tidx_blks_hit)::bigint AS blks_hit
FROM pg_statio_all_tables
WHERE relid NOT IN (
  SELECT rel.oid
  FROM pg_class rel INNER JOIN pg_tablespace spc ON 
(rel.reltablespace=spc.oid)

  WHERE spc.spcname='pg_global'
)

i'll put the cluster wide object stats on another graph

-nigel.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] An amusing MySQL weakness--not!

[Michael Nolan]

Earlier today I was working on a MySQL database (not by choice, I assure
you),
and I typed a statement like this:

Update tablexyz set field1 = '15' where field2 - 20;

The '-' was supposed to be an equal sign, but MySQL executed it anyway.
(Field2 is an integer.)

I was not amused.

PostgreSQL reports this as an error, of course.
--
Mike Nolan
no...@tssi.com

Re: [GENERAL] An amusing MySQL weakness--not!

[Randal L. Schwartz]

> "Michael" == Michael Nolan  writes:

Michael> Earlier today I was working on a MySQL database (not by choice, I 
assure
Michael> you),

Friends don't let friends use MySQL.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general