Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
These numbers don't even have any demonstrable connection to Postgres, let alone to an xpath-related memory leak. You're going to need to come up with a concrete test case if you want anyone to investigate. regards, tom lane As I said in the start of this thread, this is all just a hunch, and the graphs only show you the overall picture of this machine. However Postgres is the only application running, and I can see on the box that all the memory is being consumed by various postgres processes. In addition when Postgres is restarted, all this memory is freed. Something changed in the behavior of our database between running 8.1 and 8.3, and the most significant change we made was the use of xpath() and the XML type. My general question remains: should Postgres slowly be accumulating memory like this, possibly up to the maximum amount of shared memory we've allocated for it (4GB in this case)? If so then this memory trend isn't something I should worry about. -- m@ -- 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] pg_restore fails on Windows
Tom Tom wrote: Tom Tom wrote: Hello, We have a very strange problem when restoring a database on Windows XP. The PG version is 8.1.10 The backup was made with the pg_dump on the same machine. pg_restore -F c -h localhost -p 5432 -U postgres -d configV3 -v c:\Share\POSTGRES.backup pg_restore: connecting to database for restore Password: pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating SEQUENCE hi_value pg_restore: executing SEQUENCE SET hi_value pg_restore: creating TABLE hibconfigelement pg_restore: creating TABLE hibrefconfigbase pg_restore: creating TABLE hibrefconfigreference pg_restore: creating TABLE hibtableattachment pg_restore: creating TABLE hibtableattachmentxmldata pg_restore: creating TABLE hibtableelementversion pg_restore: creating TABLE hibtableelementversionxmldata pg_restore: creating TABLE hibtablerootelement pg_restore: creating TABLE hibtablerootelementxmldata pg_restore: creating TABLE hibtableunversionedelement pg_restore: creating TABLE hibtableunversionedelementxmldata pg_restore: creating TABLE hibtableversionedelement pg_restore: creating TABLE hibtableversionedelementxmldata pg_restore: creating TABLE versionedelement_history pg_restore: creating TABLE versionedelement_refs pg_restore: restoring data for table hibconfigelement pg_restore: restoring data for table hibrefconfigbase pg_restore: restoring data for table hibrefconfigreference pg_restore: restoring data for table hibtableattachment pg_restore: restoring data for table hibtableattachmentxmldata pg_restore: [archiver (db)] could not execute query: no result from server pg_restore: *** aborted because of error The restore unexpectedly fails on hibtableattachmentxmldata table, which is as follows: CREATE TABLE hibtablerootelementxmldata ( xmldata_id varchar(255) NOT NULL, xmldata text ) WITHOUT OIDS; and contains thousands of rows with text field having even 40MB, encoded in UTF8. The database is created as follows: CREATE DATABASE configV3 WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; The really strange is that the db restore runs OK on linux (tested on RHEL4, PG version 8.1.9). The pg_restore output is _not_ very descriptive but I suspect some dependency on OS system libraries (encoding), or maybe it is also related to the size of the CLOB field. Anyway we are now effectively without any possibility to backup our database, which is VERY serious. Have you ever came across something similar to this? Check what you have in your server logs (pg_log directory) and the eventlog around this time. There is probably a better error message available there. //Magnus Thank you for your hint. The server logs does not display any errors, except for 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently (14 seconds apart) 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently (22 seconds apart) 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently (19 seconds apart) 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently (17 seconds apart) 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently (22 seconds apart) 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently (20 seconds apart) 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently (20 seconds apart) 2008-08-08 11:16:16 CEST HINT: Consider increasing the configuration parameter checkpoint_segments. The warnings disappeared when the checkpoint_segments value was increased to 10. The restore still failed however :( The Windows eventlogs show no errors, just informational messages about starting/stopping the pg service. That's rather strange. There really should be *something* in the logs there. Hmm. Does this happen for just this one dump, or does it happen for all dumps you create on this machine (for example, can you dump single tables and get those to come through - thus isolating the issue to one table or so)? //Magnus -- 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] problem using a xpath function
Am Wednesday, 6. August 2008 schrieb erithema:
SELECT id_autori , xpath ('/Authority/Nome', testo)
FROM autori
WHERE xpath_bool('/Authority[Nome=ABELARDO]', testo) ;
I get this error:
ERROR : the function xpath_bool(unknown , xml) do not exsist at character
69 HINT: no function matches the given name and argument types. You might
need to add explicit type casts
The xpath_bool function takes its XML data as type text. The new xpath()
function uses the XML type. Those are two different sets of functions.
--
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] compiling Xpath functions in PostgreSQL 8.3.3
Am Wednesday, 6. August 2008 schrieb sagswe: When i run ' \i /usr/local/pgsql/share/pgxml.sql' in postgre , I get error saying file or directory named 'MODULE_PATHNAME' doesn't exist. How to get this MODULE_PATHNAME exist?. This sounds like your installation is botched? How did you install this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: setting datestyle
Dear Friends, I have tried to set datestyle to 'DMY'. As per manual and many other posts related to this point, I have set datestyle to 'DMY' at database. but still it is needed to execute each time *set datestyle to 'DMY' *before starting any new connection. Once this is set, there is no problem for that connection till it was live. Is there any solution to this problem? Why even after setting database property datastyle to 'DMY' gives an error when *set datestyle to 'DMY' *is not executed at starting connection? Please help. Thanks CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Re: [GENERAL] Fwd: setting datestyle
C K wrote: Dear Friends, I have tried to set datestyle to 'DMY'. As per manual and many other posts related to this point, I have set datestyle to 'DMY' at database. but still it is needed to execute each time *set datestyle to 'DMY' *before starting any new connection. Once this is set, there is no problem for that connection till it was live. Is there any solution to this problem? Why even after setting database property datastyle to 'DMY' gives an error when *set datestyle to 'DMY' *is not executed at starting connection? Maybe you have a setting at database or login level? ALTER ROLE ... SET ... can be effective or ALTER DATABASE ... SET These would override whatever you set in the config file. Also config file changes only take effect when you restart the cluster in most cases. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] diagnostic, admin, maintenance tool
Hello all, could you please recommend tools to make diagnostic, admin and maintenance work easier... I imagine there are tools (maybe graphical, or browser based) that allow me to connect to postgres and receive diagnostic data and pointers/recommendations on how to solve specific problems or optimize some configuration parameters... I'dd like to receive your suggestions. thanks in advance Joao -- 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] Unlinked files in PGDATA/base following unclean shutdown
Tom Lane wrote: Jack Orenstein [EMAIL PROTECTED] writes: The question is how to check for consistency in the case of large tables, which are split into multiple segments, (e.g. 123456.1, 123456.2). I.e., how can I find out how many segments there should be? The kernel-defined EOF is the truth, the whole truth, and nothing but the truth. There is no other authority. All segments before the last one should be exactly 1GB, but the last one can be anything up to that. Consult the comments in md.c for more details. (I think 7.4 may treat some corner cases differently from 8.3 anyway.) regards, tom lane Just to be clear, you mean 2**30 (1,073,741,824) bytes, not 10**9? Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] big database with very small dump !?
Hello all, I'm finding it very strange that my pg takes 9Giga on disk but pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed yesterday. Is this normal ? Should I be worried ? details bellow: -- # pg_dumpall --oids --clean pg_dumpall.sql # ls -lh total 232M -rw-r--r--1 postgres postgres 231M Aug 11 15:46 pg_dumpall.sql # du -sh /var/pgsql/data/ 9.4G/var/pgsql/data -- thx joao -- 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] 100% CPU pg processes that don't die.
On Sun, 10 Aug 2008, Scott Marlowe wrote: The good news is that both Centos 5.2 and Ubuntu 7.10 seem immune to this particular bug, and have been running 13 hours now without a hitch. Not sure if it's relevant here, but you do know that I've been kicking back to lkml that pgbench has issues on the 2.6.24 kernel, right? I haven't tried simulating 1000 clients like you're case, but it fails miserably to scale to even 10 with the select-only workload. The CFS scheduler used in 2.6.23+ is only about a year old right now, and it sure seems like it's still a baby going through its share of teething pains. Lest you think it's just me complaining, read https://bugs.launchpad.net/ubuntu/+source/linux/+bug/188226 Maybe your problem is somewhere else instead, but particularly given the known pgbench issues it may be worth spending a minute investigating that area. One bit of black magic I was told is that you can turn off some of the new scheduling features (one of which was the main cause of the pgbench incompatibility) by updating a kernel tunable. You can turn off all the fancy features with: echo 0 /proc/sys/kernel/sched_features See http://lkml.org/lkml/2008/5/26/288 for more details. Whether the problem still shows up with that change should help narrow whether your issue is likely related to the scheduler changes or likely something else that's different with the newer kernel. Your pgbench results should be higher after that change, too, but you shouldn't run the system like that normally--it's optimizing for something bad pgbench does but will hurt other, more realistic workloads. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to calculate number of rows per page in postgresql
Hello, The following is the procedure to calculate the disk space occupied by postgresql from a flat file. http://www.postgresql.org/docs/faqs.FAQ.html#item4.5 In this I didn't understood some terms 24 bytes: each row header (approximate) 24 bytes: one int field and one text field + 4 bytes: pointer on page to tuple what is that first 24 bytes and last 4 bytes represents? I did practically but for a table with two integer columns it takes 185 rows for a page size of 8192 for 186th it changes to 16384.But when I applied this procedure... 8 bytes: each row header 8 bytes : two int fields + 4 bytes: pointer on page to tuple total 20 bytes 8192/20 =410 rows per page (approx.) Can you please tell me how to caluculate number of rows per page size. Thank You, Avin.
Re: [GENERAL] big database with very small dump !?
On Mon, 11 Aug 2008, Joao Ferreira gmail wrote: I'm finding it very strange that my pg takes 9Giga on disk but pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed yesterday. If you've been running VACUUM FULL, it's probably so-called index bloat. Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to figure out where all your space has gone inside the database. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] big database with very small dump !?
On Mon, Aug 11, 2008 at 10:30 AM, Joao Ferreira gmail [EMAIL PROTECTED] wrote: Hello all, I'm finding it very strange that my pg takes 9Giga on disk but pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed yesterday. Is this normal ? Should I be worried ? It really depends. If you've got a lot of indexes then maybe that's normal. But most the time you don't see more than about 4 times the space used in the db as in the dump. It's likely you've got index bloat. If you reload a pg_dump of the database in question into another server how much space does that take up? Look into using CLUSTER or REINDEX to fix the space usage. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stored procedure compilation error checking
I am fairly new to Postgres. I noticed that the stored procedures written in pgplsql are checked only for syntax errors and nothing more at compile time. It does not even do that basic error checking like if the table/field names or variable names used in the procedure are even valid. This causes ugly compile time like error at run time. Is there a configuration setting to increase the error checking level or any tool available to do the same. Thank you, Arun M
Re: [GENERAL] stored procedure compilation error checking
On Monday 11 August 2008 20:15:37 Mani, Arun wrote: Is there a configuration setting to increase the error checking level or any tool available to do the same. No -- 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] psqlodbc on Vista Ultimate 64
thank you!!! =] 2008/8/8 Hiroshi Saito [EMAIL PROTECTED] Hi. Is this helpful? http://winpg.jp/~saito/psqlODBC/psqlODBC64/http://winpg.jp/%7Esaito/psqlODBC/psqlODBC64/ as for AMD64. http://www.geocities.jp/inocchichichi/psqlodbc/index.html Regards, Hiroshi Saito - Original Message - From: Lucas Felix To: pgsql-general@postgresql.org Sent: Saturday, August 09, 2008 12:00 AM Subject: [GENERAL] psqlodbc on Vista Ultimate 64 Olá, estou com um problema, o Windows Vista não reconhece o odbc do PostgreSQL, alguma dica? Hello, I have a problem, Windows Vista does not recognize the odbc of PostgreSQL, any hint? -- Lucas Felix de Sousa Técnico em Informática [EMAIL PROTECTED] Se enxerguei mais longe que outros homens, foi porque me ergui em ombros de gigantes. -- Lucas Felix de Sousa Técnico em Informática [EMAIL PROTECTED] Se enxerguei mais longe que outros homens, foi porque me ergui em ombros de gigantes.
Re: [GENERAL] big database with very small dump !?
I'd do du -sh /var/pgsql/data/base rather then /var/pgsql/data depending on how your pgsql server logging is setup, there are other folders and/or files that might take considerable disk space under ./data/ you may want to exclude those. I find this query useful for something like this as well: select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ; V. Joao Ferreira gmail wrote: Hello all, I'm finding it very strange that my pg takes 9Giga on disk but pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed yesterday. Is this normal ? Should I be worried ? details bellow: -- # pg_dumpall --oids --clean pg_dumpall.sql # ls -lh total 232M -rw-r--r--1 postgres postgres 231M Aug 11 15:46 pg_dumpall.sql # du -sh /var/pgsql/data/ 9.4G/var/pgsql/data -- thx joao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can I search for text in a function?
Greetings! Sometimes I need to track down how something happens in the database our application relies on, but whatever's happening may be buried in some old function that everybody here has forgotten about long ago. IIRC, functions are stored internally merely as fields in a table owned by the system. Is there a query I can use to find what function contains the string previous_charge? Thank you very much. Robert D. Richardson Product Engineer Software file:///t:/Sales/Images/Marketing%20Pictures/Logos/LOGOs%20from%2010th% 20Floor/RAD-CON%20Logo%20for%20Signature.jpg RAD-CON, Inc. TECHNOLOGY: Innovative Proven Phone : +1.216.706.8905 Fax: +1.216.221.1135 Website: www.RAD-CON.com http://www.rad-con.com/ E-mail: [EMAIL PROTECTED] RAD-CON Logo for Signature.jpg
Re: [GENERAL] Unlinked files in PGDATA/base following unclean shutdown
Jack Orenstein wrote: Tom Lane wrote: All segments before the last one should be exactly 1GB, but the last one can be anything up to that. Consult the comments in md.c for more details. (I think 7.4 may treat some corner cases differently from 8.3 anyway.) Just to be clear, you mean 2**30 (1,073,741,824) bytes, not 10**9? It's RELSEG_SIZE * BLCKSZ, where #define RELSEG_SIZE 131072 #define BLCKSZ 8192 (both from pg_config.h) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running a PL/pgSQL function
I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream counter-intuitive. -- 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] Can I search for text in a function?
Rob Richardson wrote: Sometimes I need to track down how something happens in the database our application relies on, but whatever's happening may be buried in some old function that everybody here has forgotten about long ago. IIRC, functions are stored internally merely as fields in a table owned by the system. Is there a query I can use to find what function contains the string previous_charge? select proname from pg_proc where prosrc ilike '%previous_charge%'; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] Running a PL/pgSQL function
On Monday 11 August 2008 4:24:17 pm Christophe wrote: I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream counter-intuitive. Similar to doing: test=# SELECT 1+1; ?column? -- 2 (1 row) -- Adrian Klaver [EMAIL PROTECTED] -- 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] Running a PL/pgSQL function
Christophe [EMAIL PROTECTED] writes: I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream counter-intuitive. Yeah, it is a little weird but it works fine. We treat VOID as a more-or-less-real datatype that has only one value, an empty string... regards, tom lane -- 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] Running a PL/pgSQL function
On Mon, Aug 11, 2008 at 6:03 PM, Adrian Klaver [EMAIL PROTECTED] wrote: On Monday 11 August 2008 4:24:17 pm Christophe wrote: I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream counter-intuitive. Similar to doing: test=# SELECT 1+1; ?column? -- 2 Hey, it beats select 1+1 from dual! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] different results based solely on existence of index (no, seriously)
In reference to the script below (I know it can be rewritten, that's not the point), I get 3 rows if the referenced index exists but only two rows if it does not. This is observable and repeatable just by dropping/creating the index. Drop the index and two rows are returned. Create the index, three rows are returned. Drop the index, two rows again. In addition, in no case does the selected column t2.c2 actually contain a value (it's always null). Since in the 3 row case, it returns a row with t1.c1=2, I would have expected a value from t2 (if you add t2.c1 to select clause you can see that is null as well). It's probably worth mentioning (since it actually took me a while to notice) that the plans are subtlety different. Neither plan (with or without index existing) actually uses the index, but in one case there is an extra filter node. version string is PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33) create table t1(c1 int); create table t2(c1 int, c2 timestamp with time zone); --problem index create index someidx on t2 using btree(c2); insert into t1 values (1),(2),(3); insert into t2 values(2, now()); select t1.c1, t2.c2 from t1 left join t2 on t1.c1 = t2.c1 where t2.c2 is null or ( t2.c2 = (select max(c2) from t2 where t1.c1 = t2.c1) and t2.c2 now() - '1 day'::interval );
