Re: [GENERAL] SRF written in C
On Tue, Jul 01, 2008 at 10:02:39AM -0500, Felipe de Jesús Molina Bravo wrote:
Hi
what can i do for a SRF written in C, can called as follow:
select * from obtAscendencia('(11099,15685)','(6808,9621)');
I can call the function:
select obtAscendencia('(11099,15685)','(6808,9621)');
I'm afraid you did not explain what exactly the problem is. Do you mean
that one or the other of the statements doesn't work? Which one, and
what is the error message?
Have a nice day,
--
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
Re: [GENERAL] pg crashing
Tom Lane wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. Yes. And the traditional question should be asked - is there any antivirus or other personal security software running on tihs machine? If so, uninstall (not just disable!) it and see if the problem goes away. 2008-07-01 10:46:30 CDT LOG: all server processes terminated; reinitializing I think your real problem is with what happened *before* that. Agreed. There is some reason that they all terminated... But: 2008-07-01 10:46:31 CDT FATAL: pre-existing shared memory block is still in use 2008-07-01 10:46:31 CDT HINT: Check if there are any old server processes still running, and terminate them. Hmm ... the code in win32_shmem.c that generates this message seems mighty bogus to me --- it's just hoping that one-second delay is enough. Well, we're basically waiting for the kernel cleanup thread to get run. In my tests it never came above 20ms or so, so 1 second is a pretty long time. And you need *some* kind of timeout... It would also be interesting to know if there are actually any other processes running at this time. Another problem is that postmaster children that do PGSharedMemoryDetach will still have valid inherited handles for the shmem segment --- does that factor into the behavior? It looks to me like the CloseHandle ought to be in PGSharedMemoryDetach. Not as long as the processes die. If they die, their handles go with them, and once the reference count goes to zero, the object goes away. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Target lists can have at most 1664 entries?
What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x... Regards, BTJ -- --- Bjørn T Johansen [EMAIL PROTECTED] --- Someone wrote: I understand that if you play a Windows CD backwards you hear strange Satanic messages To which someone replied: It's even worse than that; play it forwards and it installs Windows --- -- 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 crashing
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Another problem is that postmaster children that do PGSharedMemoryDetach will still have valid inherited handles for the shmem segment --- does that factor into the behavior? It looks to me like the CloseHandle ought to be in PGSharedMemoryDetach. Not as long as the processes die. If they die, their handles go with them, and once the reference count goes to zero, the object goes away. But the syslogger process (and maybe others) is *not* supposed to die. 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] pg crashing
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: Another problem is that postmaster children that do PGSharedMemoryDetach will still have valid inherited handles for the shmem segment --- does that factor into the behavior? It looks to me like the CloseHandle ought to be in PGSharedMemoryDetach. Not as long as the processes die. If they die, their handles go with them, and once the reference count goes to zero, the object goes away. But the syslogger process (and maybe others) is *not* supposed to die. Right. But are you saying we actually want to start up a new backend in a directory where we already have a running syslogger (and maybe others) processes, just no postmaster? I'd assume we might run into such simple things as sharing violations on the logfile - if nothing inside the db itself.. //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] pg crashing
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: But the syslogger process (and maybe others) is *not* supposed to die. Right. But are you saying we actually want to start up a new backend in a directory where we already have a running syslogger (and maybe others) processes, just no postmaster? Not great, maybe, but what it looks to me is that the current system guarantees that a postmaster with a syslogger child will never recover from a backend-child crash. That's not better. 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] Target lists can have at most 1664 entries?
Bjørn T Johansen wrote: What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x... What query is Hibernate generating? That's an error from the planner. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Can someone shed some light on what's happening here? D:\backupspg_dump -Z 9 -Fc -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump: Dumping the contents of table uploadeddatafiles failed: PQgetCopyData () failed. pg_dump: Error message from server: lost synchronization with server: got messag e type d, length 6036499 pg_dump: The command was: COPY public.uploadeddatafiles (id, username, projectid , aspsession, filename, filetype, filesize, filedata, uploadedon, timestamp) T O stdout; pg 8.3.1 server on w2k3 pg_dump 8.3.1 and 8.3.3 on the same machine both do the same thing. data directory and backup directory excluded from virus scanner. The followinng variations also failed. The length seems to be either 6036499 or 8435588 (84... is most common). pg_dump -Z 9 -Fp -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump -Fp -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 uploadeddatafiles holds excel spreadsheets in the filedata column. sheepcrc2=# select count(*) from uploadeddatafiles; count --- 405 (1 row) sheepcrc2=# select sum(length(filedata)) from uploadeddatafiles; sum --- 271067619 (1 row) This completes and returns all rows select id, md5(filedata) from uploadeddatafiles 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] Memory use in 8.3 plpgsql with heavy use of xpath()
OK, I'll try to come up with something. Do you have a recommended way of capturing the amount memory being used by Postgres related to this? I was thinking I would have a plpgsql function that loops a large number of times, calling a few xpath() calls, Yeah, that's what I'd try first. regards, tom lane Below is a test case that simulates the use of xpath() within a plpgsql function in my application. I'm not sure of a good way to measure the retained memory before/after the script runs, however. I ran this several times and the postgres process that ran it does have more memory allocated afterwards than before, but I don't know what is expected and what isn't. You can adjust the number of loops that run at num_loops int8 := 100; -- m@ - DROP TABLE IF EXISTS tmp_xml_test CASCADE; DROP TABLE IF EXISTS tmp_xml_addr CASCADE; CREATE TEMPORARY TABLE tmp_xml_test( id int4, x xml, primary key (id)); CREATE TEMPORARY TABLE tmp_xml_addr( id int4, xmlpos int4, street text, city text, state text, zip text, primary key (id, xmlpos)); CREATE OR REPLACE FUNCTION tmp_extract_address(data_row tmp_xml_test) RETURNS void AS $BODY$ DECLARE addr_row tmp_xml_addr%ROWTYPE; tmp_txt text; tmp_array xml[]; BEGIN addr_row.id := data_row.id; DELETE FROM tmp_xml_addr WHERE id = data_row.id; tmp_array := xpath( '/po:purchaseOrder/*[name(.) = shipTo or name(.) = billTo]', data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]); IF array_upper(tmp_array, 1) 0 THEN FOR idx IN 1..array_upper(tmp_array, 1) LOOP addr_row.xmlpos := idx; addr_row.street := upper(XMLSERIALIZE(CONTENT(xpath( '/po:purchaseOrder/*[name(.) = shipTo or name(.) = billTo][' ||idx|| ']/po:street[1]/text()', data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]))[1] as text)); addr_row.city := upper(XMLSERIALIZE(CONTENT(xpath( '/po:purchaseOrder/*[name(.) = shipTo or name(.) = billTo][' ||idx|| ']/po:city[1]/text()', data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]))[1] as text)); addr_row.state := upper(XMLSERIALIZE(CONTENT(xpath( '/po:purchaseOrder/*[name(.) = shipTo or name(.) = billTo][' ||idx|| ']/po:state[1]/text()', data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]))[1] as text)); addr_row.zip := upper(XMLSERIALIZE(CONTENT(xpath( '/po:purchaseOrder/*[name(.) = shipTo or name(.) = billTo][' ||idx|| ']/po:zip[1]/text()', data_row.x, ARRAY[ARRAY['po', 'http://www.example.com/PO1']]))[1] as text)); INSERT INTO tmp_xml_addr (id, xmlpos, street, city, state, zip) VALUES (addr_row.id, addr_row.xmlpos, addr_row.street, addr_row.city, addr_row.state, addr_row.zip); END LOOP; END IF; RETURN; END; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION tmp_populate_addr() RETURNS trigger AS $BODY$ BEGIN PERFORM tmp_extract_address(NEW); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER tmp_populate_addr_trigger AFTER INSERT OR UPDATE ON tmp_xml_test FOR EACH ROW EXECUTE PROCEDURE tmp_populate_addr(); DROP FUNCTION IF EXISTS tmp_test_loop(); CREATE OR REPLACE FUNCTION tmp_test_loop() RETURNS SETOF tmp_xml_addr AS $BODY$ DECLARE num_loops int8 := 100; BEGIN FOR idx IN 1..num_loops LOOP INSERT INTO tmp_xml_test VALUES (idx, $$purchaseOrder xmlns=http://www.example.com/PO1; orderDate=1999-10-20 shipTo country=US nameAlice Smith/name street123 Maple Street/street cityMill Valley/city stateCA/state zip90952/zip /shipTo billTo country=US nameRobert Smith/name street8 Oak Avenue/street cityOld Town/city statePA/state zip95819/zip /billTo commentHurry, my lawn is going wild!/comment items item partNum=872-AA productNameLawnmower/productName quantity1/quantity USPrice148.95/USPrice commentConfirm this is electric/comment /item item partNum=926-AA productNameBaby Monitor/productName quantity1/quantity USPrice39.98/USPrice shipDate1999-05-21/shipDate /item /items /purchaseOrder$$); END LOOP; FOR idx IN 1..num_loops LOOP UPDATE tmp_xml_test SET id = idx WHERE id = idx;
Re: [GENERAL] Target lists can have at most 1664 entries?
Richard Huxton wrote: Bjørn T Johansen wrote: What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x... What query is Hibernate generating? That's an error from the planner. It does sound like you're trying to query back more than 1664 columns in one query. That's a *lot*, but auto generated queries can do the strangest things :-) //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] Target lists can have at most 1664 entries?
=?UTF-8?Q?Bj=C3=B8rn?= T Johansen [EMAIL PROTECTED] writes: What does this mean and how can it be fixed? Reduce the number of columns in your SELECTs? This whiffs to me of excruciatingly bad schema design. How could you possibly need upwards of a thousand columns in a query result? IMHO reasonable column counts are O(10), not O(bignum). 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] Target lists can have at most 1664 entries?
On Wed, 02 Jul 2008 09:03:04 +0200 Magnus Hagander [EMAIL PROTECTED] wrote: Richard Huxton wrote: Bjørn T Johansen wrote: What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x... What query is Hibernate generating? That's an error from the planner. It does sound like you're trying to query back more than 1664 columns in one query. That's a *lot*, but auto generated queries can do the strangest things :-) //Magnus That might be the case Is it possible to increase this value for PostgreSQL or do we have to look at Hibernate? BTJ -- 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] Target lists can have at most 1664 entries?
Magnus Hagander wrote: Richard Huxton wrote: Bjørn T Johansen wrote: What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x... What query is Hibernate generating? That's an error from the planner. It does sound like you're trying to query back more than 1664 columns in one query. That's a *lot*, but auto generated queries can do the strangest things :-) I *thought* it was referring to columns, but then thought no, can't be. -- Richard Huxton Archonet Ltd -- 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] Target lists can have at most 1664 entries?
Tom Lane wrote: =?UTF-8?Q?Bj=C3=B8rn?= T Johansen [EMAIL PROTECTED] writes: What does this mean and how can it be fixed? Reduce the number of columns in your SELECTs? This whiffs to me of excruciatingly bad schema design. How could you possibly need upwards of a thousand columns in a query result? IMHO reasonable column counts are O(10), not O(bignum). (I'm pretty new to Hibernate, so I can only share my general understanding, but:) One possible reason is that sometimes tools like Hibernate like to fetch records from multiple related tables in the database in one query with chained left joins. They then scan the results and eliminate duplicates where appropriate. It sounds horrifying, but it can actually be very fast where fairly small data sets are being fetched from highly normalized tables with appropriate indexes. In other circumstances, however, like when there are very high row counts or lots of fields being returned, it's a very bad strategy. My guess is that they haven't told Hibernate to use an appropriate fetching strategy (multiple SELECTs) for the data they're trying to load, and for some reason Hibernate is choosing a left join fetch. If they apply the appropriate annotations to their Hibernate data model or adjust their HQL queries to avoid left join fetch they might find that the problem goes away - and performance improves significantly. -- Craig Ringer -- 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] Target lists can have at most 1664 entries?
On Wed, 02 Jul 2008 03:04:04 -0400 Tom Lane [EMAIL PROTECTED] wrote: =?UTF-8?Q?Bj=C3=B8rn?= T Johansen [EMAIL PROTECTED] writes: What does this mean and how can it be fixed? Reduce the number of columns in your SELECTs? This whiffs to me of excruciatingly bad schema design. How could you possibly need upwards of a thousand columns in a query result? IMHO reasonable column counts are O(10), not O(bignum). regards, tom lane Well, I do agree but it is not my design and a fix in PostgreSQL would be quicker than fixing the design BTJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Memory Problem
Hi, We have an IBM System x3850 machine running on RHEL 4.5 Cluster Suite with high-availability enabled. During a huge delete process, PostgreSQL (8.3.1) exhausts available memory and receives an OOM kill. $ /srv/usr/bin/psql -e -f ~/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql 1_5_1_0_20080625 ... DELETE FROM mudailyreportlog WHERE NOT EXISTS (SELECT 1 FROM mobileunit WHERE mobileunit.muid = mudailyreportlog.muid); DELETE 0 DELETE FROM mudistancelog WHERE NOT EXISTS (SELECT 1 FROM mobileunit WHERE mobileunit.muid = mudistancelog.muid); psql:/home/postgres/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql:16: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:/home/postgres/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625.sql:16: connection to server was lost Above DELETE FROM mudistancelog ... query runs for nearly 1 hour and then causes PostgreSQL to receive an OOM kill. mudistancelog is a table of size ~11GiG. And below is the execution plan of the DELETE statement. Seq Scan on mudistancelog (cost=0.00..1730580560.83 rows=104441936 width=6) Filter: (NOT (subplan)) SubPlan - Index Scan using pk_mobileunit_muid on mobileunit (cost=0.00..8.27 rows=1 width=0) Index Cond: (muid = $0) Here are the related PostgreSQL log lines. 2008-07-02 10:36:09.032 EEST LOG: server process (PID 22391) was terminated by signal 9: Killed 2008-07-02 10:36:09.032 EEST LOG: terminating any other active server processes ... test_1_5_1_0 emove 2008-07-02 10:36:09.151 EEST FATAL: the database system is in recovery mode test_1_5_1_0 emove 2008-07-02 10:36:09.152 EEST FATAL: the database system is in recovery mode test_1_5_1_0 emove 2008-07-02 10:36:09.159 EEST FATAL: the database system is in recovery mode ... test_1_5_1_0 emove 2008-07-02 10:36:09.209 EEST FATAL: the database system is in recovery mode 2008-07-02 10:36:09.210 EEST LOG: all server processes terminated; reinitializing test_1_5_1_0 emove 2008-07-02 10:36:09.528 EEST FATAL: the database system is in recovery mode ... test_1_5_0_0 postgres 2008-07-02 10:36:09.537 EEST FATAL: the database system is in recovery mode 2008-07-02 10:36:09.540 EEST LOG: database system was interrupted; last known up at 2008-07-02 10:12:57 EEST test_1_5_1_0 emove 2008-07-02 10:36:09.542 EEST FATAL: the database system is in recovery mode ... test_1_5_1_0 emove 2008-07-02 10:36:09.567 EEST FATAL: the database system is in recovery mode 2008-07-02 10:36:09.567 EEST LOG: database system was not properly shut down; automatic recovery in progress test_1_5_1_0 emove 2008-07-02 10:36:09.572 EEST FATAL: the database system is in recovery mode test_1_5_1_0 emove 2008-07-02 10:36:09.574 EEST FATAL: the database system is in recovery mode test_1_5_1_0 emove 2008-07-02 10:36:09.575 EEST FATAL: the database system is in recovery mode test_1_5_1_0 emove 2008-07-02 10:36:09.577 EEST FATAL: the database system is in recovery mode 2008-07-02 10:36:09.578 EEST LOG: redo starts at 4F/2600EFF0 test_1_5_1_0 emove 2008-07-02 10:36:09.578 EEST FATAL: the database system is in recovery mode 2008-07-02 10:37:09.073 EEST LOG: autovacuum launcher started 2008-07-02 10:37:09.074 EEST LOG: database system is ready to accept connections I've attached my postgresql.conf and related /var/log/messages parts. (Server has a memory and swap space of size 8GiG.) What might be causing this problem? How can I configure postgresql.conf to avoid such situations? Any kind of help will be really appreciated. Regards. Jul 2 10:33:00 mobilizc1 kernel: oom-killer: gfp_mask=0x1d2 Jul 2 10:33:00 mobilizc1 kernel: Mem-info: Jul 2 10:33:00 mobilizc1 kernel: Node 0 DMA per-cpu: Jul 2 10:33:00 mobilizc1 kernel: cpu 0 hot: low 2, high 6, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 0 cold: low 0, high 2, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 1 hot: low 2, high 6, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 1 cold: low 0, high 2, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 2 hot: low 2, high 6, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 2 cold: low 0, high 2, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 3 hot: low 2, high 6, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 3 cold: low 0, high 2, batch 1 Jul 2 10:33:00 mobilizc1 kernel: cpu 4 hot: low 2, high 6, batch 1 Jul 2 10:36:09 mobilizc1 kernel: cpu 4 cold: low 0, high 2, batch 1 Jul 2 10:36:09 mobilizc1 kernel: cpu 5 hot: low 2, high 6, batch 1 Jul 2 10:36:09 mobilizc1 kernel: cpu 5 cold: low 0, high 2, batch 1 Jul 2 10:36:09 mobilizc1 kernel: cpu 6 hot: low 2, high 6, batch 1 Jul 2 10:36:09 mobilizc1 kernel: cpu 6 cold: low
[GENERAL] Insert into ... returning ... before 8.2?
What should I replace the command
INSERT INTO table (name) VALUES (value) RETURNING currval('my_id_seq')
into my_var;
with if I have to use version 8.1?
--
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] Insert into ... returning ... before 8.2?
am Wed, dem 02.07.2008, um 11:58:19 +0200 mailte A B folgendes:
What should I replace the command
INSERT INTO table (name) VALUES (value) RETURNING currval('my_id_seq')
into my_var;
with if I have to use version 8.1?
select currval('my_id_seq');
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
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] Target lists can have at most 1664 entries?
On Wed, 02 Jul 2008 15:24:38 +0800 Craig Ringer [EMAIL PROTECTED] wrote: Tom Lane wrote: =?UTF-8?Q?Bj=C3=B8rn?= T Johansen [EMAIL PROTECTED] writes: What does this mean and how can it be fixed? Reduce the number of columns in your SELECTs? This whiffs to me of excruciatingly bad schema design. How could you possibly need upwards of a thousand columns in a query result? IMHO reasonable column counts are O(10), not O(bignum). (I'm pretty new to Hibernate, so I can only share my general understanding, but:) One possible reason is that sometimes tools like Hibernate like to fetch records from multiple related tables in the database in one query with chained left joins. They then scan the results and eliminate duplicates where appropriate. It sounds horrifying, but it can actually be very fast where fairly small data sets are being fetched from highly normalized tables with appropriate indexes. In other circumstances, however, like when there are very high row counts or lots of fields being returned, it's a very bad strategy. My guess is that they haven't told Hibernate to use an appropriate fetching strategy (multiple SELECTs) for the data they're trying to load, and for some reason Hibernate is choosing a left join fetch. If they apply the appropriate annotations to their Hibernate data model or adjust their HQL queries to avoid left join fetch they might find that the problem goes away - and performance improves significantly. -- Craig Ringer Ok, guess we have to look at our Hibernate config (we are only using default fetching strategy...) Thx... BTJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple tool for building web forms
Can anyone recommend a good tool for building simple web forms w/ a PostgreSQL backend? Emphasis on simple - single table contact info forms, etc. Something that can be presented to end users w/out a lot of hand-holding. E.g. I want a form w/ last name, first name, # of guests, arrival date. Send notification email of new entries to [EMAIL PROTECTED] and [EMAIL PROTECTED] Ideally the output could easily be embedded in other web pages. F/OSS preferred, but proprietary not out of the question. This isn't a terribly difficult thing to write, but I imagine it's already been done many times over. I'd rather re-use something existing that re-invent the wheel. Dr. Google hasn't been as helpful as I'd like so far. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- 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] simple tool for building web forms
http://www.sqlmaestro.com/products/postgresql/ I've used the PHP Code Generator with great success for simple stuff like you describe. You could then write a function to do email notifications or whatever you want. Jon -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Ron Peterson Sent: Wednesday, July 02, 2008 7:05 AM To: pgsql-general@postgresql.org Subject: [GENERAL] simple tool for building web forms Can anyone recommend a good tool for building simple web forms w/ a PostgreSQL backend? Emphasis on simple - single table contact info forms, etc. Something that can be presented to end users w/out a lot of hand-holding. E.g. I want a form w/ last name, first name, # of guests, arrival date. Send notification email of new entries to [EMAIL PROTECTED] and [EMAIL PROTECTED] Ideally the output could easily be embedded in other web pages. F/OSS preferred, but proprietary not out of the question. This isn't a terribly difficult thing to write, but I imagine it's already been done many times over. I'd rather re-use something existing that re-invent the wheel. Dr. Google hasn't been as helpful as I'd like so far. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 crashing
Roberts, Jon [EMAIL PROTECTED] writes:
Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400
Well, there are plenty of known bugs in 8.3.0 by now. You really
should update before complaining, not after.
I'm not complaining. I just want to make sure that if I upgrade, it
will fix the problem. An upgrade could possible introduce a new
problem. I also wonder if this is isolated to Win32 because we are
upgrading to Solaris very soon.
Problem: My database keeps on crashing every few days with this type
of
error message:
2008-07-01 10:46:30 CDT LOG: all server processes terminated;
reinitializing
I think your real problem is with what happened *before* that.
I found the first instance of crash and then got the rest of the log
file.
2008-07-01 10:43:42 CDT LOG: server process (PID 3524) exited with exit
code 128
2008-07-01 10:43:42 CDT LOG: terminating any other active server
processes
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT CONTEXT: SQL statement SELECT
pg_sleep(cast(control.fn_get_variable('sleep_time') as int))
PL/pgSQL function fn_update_status line 136 at PERFORM
SQL statement SELECT gp_load.fn_update_status( $1 , $2 ,
'Processing', '', 0)
PL/pgSQL function fn_load line 56 at PERFORM
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT CONTEXT: PL/pgSQL function fn_get_job_details
line 114 at IF
PL/pgSQL function fn_load line 465 at FOR over SELECT rows
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT CONTEXT: SQL statement SELECT
pg_sleep(cast(control.fn_get_variable('sleep_time') as int))
PL/pgSQL function fn_update_status line 136 at PERFORM
SQL statement SELECT gp_load.fn_update_status( $1 , $2 ,
'Processing', '', 0)
PL/pgSQL function fn_load line 56 at PERFORM
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2008-07-01 10:43:42 CDT HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2008-07-01 10:43:42 CDT WARNING: terminating connection because of
crash of another server process
2008-07-01 10:43:42 CDT DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another
Re: [GENERAL] pg crashing
Tom Lane wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. Yes. And the traditional question should be asked - is there any antivirus or other personal security software running on tihs machine? If so, uninstall (not just disable!) it and see if the problem goes away. I am not able to un-install this. However, this problem only started as the database grew in size and usage. It is 232 MB in size now and it has 30 or so active sessions 24x7. 2008-07-01 10:46:30 CDT LOG: all server processes terminated; reinitializing I think your real problem is with what happened *before* that. Agreed. There is some reason that they all terminated... I just emailed a larger part of the log file. But: 2008-07-01 10:46:31 CDT FATAL: pre-existing shared memory block is still in use 2008-07-01 10:46:31 CDT HINT: Check if there are any old server processes still running, and terminate them. Hmm ... the code in win32_shmem.c that generates this message seems mighty bogus to me --- it's just hoping that one-second delay is enough. Well, we're basically waiting for the kernel cleanup thread to get run. In my tests it never came above 20ms or so, so 1 second is a pretty long time. And you need *some* kind of timeout... It would also be interesting to know if there are actually any other processes running at this time. Yes, there were about 30 active sessions executing functions. Another problem is that postmaster children that do PGSharedMemoryDetach will still have valid inherited handles for the shmem segment --- does that factor into the behavior? It looks to me like the CloseHandle ought to be in PGSharedMemoryDetach. Not as long as the processes die. If they die, their handles go with them, and once the reference count goes to zero, the object goes away. //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] pg crashing
Magnus Hagander [EMAIL PROTECTED] writes: Tom Lane wrote: But the syslogger process (and maybe others) is *not* supposed to die. Right. But are you saying we actually want to start up a new backend in a directory where we already have a running syslogger (and maybe others) processes, just no postmaster? Not great, maybe, but what it looks to me is that the current system guarantees that a postmaster with a syslogger child will never recover from a backend-child crash. That's not better. When you say current system, do you mean PG on Windows? Jon -- 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 crashing
Roberts, Jon wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. I'm not complaining. I just want to make sure that if I upgrade, it will fix the problem. An upgrade could possible introduce a new problem. I also wonder if this is isolated to Win32 because we are upgrading to Solaris very soon. Are you able to get your hands on a cheap Linux box (say, convert a spare desktop) and do some testing on that? It may also be helpful to test on a Windows machine without a virus scanner, personal firewall, or other such system-mangling poison. If you can reproduce the fault in either of those configurations I'm sure it'd be very informative. Personally (and my opinion isn't worth much) I'd blame the virus scanner basically by default, because they're always causing subtle problems and introducing weird quirks. Something like PostgreSQL really does rely on the OS doing what it says it will, and virus scanners tend to bend or break the rules. -- Craig Ringer -- 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 crashing
Roberts, Jon wrote: Tom Lane wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. Yes. And the traditional question should be asked - is there any antivirus or other personal security software running on tihs machine? If so, uninstall (not just disable!) it and see if the problem goes away. I am not able to un-install this. However, this problem only started as the database grew in size and usage. It is 232 MB in size now and it has 30 or so active sessions 24x7. But you do run antivirus on the machine? Which antivirus? It's quite possible that this is the reason. As the files grow, the AV may take longer to do whatever crap it's doing to them, thus exceeding timeouts. In general, you can get all sorts of strange things when you run AV on your database server. The general recommendation is never to do that if you want things to work, and it will be the first thing you're told to remove. It would be good if you could at least temporarily remove it and see if it fixes the issue. It would also be interesting to know if there are actually any other processes running at this time. Yes, there were about 30 active sessions executing functions. Right, but are the processes still active at the moment whrere it crashes, or has the postmaster managed to kill them off for the restart? //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] pg crashing
Roberts, Jon wrote: Tom Lane wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. Yes. And the traditional question should be asked - is there any antivirus or other personal security software running on tihs machine? If so, uninstall (not just disable!) it and see if the problem goes away. I am not able to un-install this. However, this problem only started as the database grew in size and usage. It is 232 MB in size now and it has 30 or so active sessions 24x7. But you do run antivirus on the machine? Which antivirus? We are running Computer Associates eTrust Antivirus. The realtime scanner has an exclusion of e:\PostgreSQL and all subdirectories. It's quite possible that this is the reason. As the files grow, the AV may take longer to do whatever crap it's doing to them, thus exceeding timeouts. The log files are located here: e:\PostgreSQL\data\pg_log Before I had that exclusion on the directory, the database would crash because data files would get locked. We don't get those errors now at all so I don't think this problem is caused by the antivirus program. We do have lots of sessions running at once. It seems that the extra load on the server could also cause it to exceed the timeout. In general, you can get all sorts of strange things when you run AV on your database server. The general recommendation is never to do that if you want things to work, and it will be the first thing you're told to remove. I agree to an extent. I've used Oracle and SQL Server on Windows and when the antivirus program scans any database files, it screws it up. However, I've never had a problem with an antivirus program once the database directories were excluded. It would be good if you could at least temporarily remove it and see if it fixes the issue. I can't do that. In this corporate environment, I would get shot if I did that. It would also be interesting to know if there are actually any other processes running at this time. Yes, there were about 30 active sessions executing functions. Right, but are the processes still active at the moment whrere it crashes, or has the postmaster managed to kill them off for the restart? Yes, there are active sessions. Could this be the problem? We have a queue table in which we only allow x number of concurrent jobs. A job is first inserted into the Queue with a Status of Queued. Next the job executes a function to update the Status to Processing. Function Details: 1. Creates a db_link 2. lock table gp_load.queue in access exclusive mode 3. executes this in the linked session: select count(*) from gp_load.queue where status = 'Processing' 4. if current jobs less than max jobs allowed then Update gp_load.queue set status = 'Processing' Commit 5. Close connection 6. Returns the status of still Queued or Processing I have to use a dblink because PostgreSQL doesn't have autonomous transactions. We call a single function to do all of this work and the other jobs need to see the current Status so they know if they should wait or run right now. The calling function uses a while loop to execute this function. If the function returns Queued, it means it was unable to change the status so it uses pg_sleep to wait 10 seconds and then it trys again until it is able to set the status to Processing. Sorry for the lengthy answer but yes, we could have active sessions using pg_sleep waiting for a slot in the Queue to process a job. The log file shows that this happened too. Jon -- 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] SAST FATAL: could not access private key file server.key
On Tue, Jul 1, 2008 at 5:52 PM, Albe Laurenz [EMAIL PROTECTED] wrote: If you don't need SSL, set 'ssl=off' in postgresql.conf. If you want SSL, 1) read the manual on SSL support http://www.postgresql.org/docs/8.3/static/ssl-tcp.html 2) Is there a file server.key? If yes, make it readable to the postgres user. If not, create it as documented. Hi Albe, thanks very much for helping me here... Here is the contents of my /var/lib/postgresql/8.2/main/ : [EMAIL PROTECTED]:/var/lib/postgresql/8.2/main# ls -l total 9 drwx-- 7 postgres postgres 168 2008-06-29 11:27 base drwx-- 2 postgres postgres 768 2008-06-30 13:01 global drwx-- 2 postgres postgres 72 2008-06-24 09:37 pg_clog drwx-- 4 postgres postgres 96 2008-06-24 09:37 pg_multixact drwx-- 2 postgres postgres 72 2008-06-24 09:37 pg_subtrans drwx-- 2 postgres postgres 48 2008-06-24 09:37 pg_tblspc drwx-- 2 postgres postgres 48 2008-06-24 09:37 pg_twophase -rw--- 1 postgres postgres 4 2008-06-24 09:37 PG_VERSION drwx-- 3 postgres postgres 120 2008-06-24 09:37 pg_xlog -rw--- 1 postgres postgres 125 2008-06-30 08:59 postmaster.opts lrwxrwxrwx 1 root root 31 2008-06-24 09:37 root.crt - /etc/postgresql-common/root.crt lrwxrwxrwx 1 root root 36 2008-06-24 09:37 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 2008-06-24 09:37 server.key - /etc/ssl/private/ssl-cert-snakeoil.key 'server.key' seems to be writable to all and sundry, although the file it is linked to (ssl-cert-snakeoil.key) is not: [EMAIL PROTECTED]:/etc/ssl/private# ls -l total 4 -rw--- 1 root ssl-cert 887 2008-06-11 12:18 ssl-cert-snakeoil.key -- 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] SAST FATAL: could not access private key file server.key
Dave Coventry wrote: If you want SSL, 2) Is there a file server.key? If yes, make it readable to the postgres user. If not, create it as documented. Here is the contents of my /var/lib/postgresql/8.2/main/ : [EMAIL PROTECTED]:/var/lib/postgresql/8.2/main# ls -l total 9 drwx-- 7 postgres postgres 168 2008-06-29 11:27 base drwx-- 2 postgres postgres 768 2008-06-30 13:01 global drwx-- 2 postgres postgres 72 2008-06-24 09:37 pg_clog drwx-- 4 postgres postgres 96 2008-06-24 09:37 pg_multixact drwx-- 2 postgres postgres 72 2008-06-24 09:37 pg_subtrans drwx-- 2 postgres postgres 48 2008-06-24 09:37 pg_tblspc drwx-- 2 postgres postgres 48 2008-06-24 09:37 pg_twophase -rw--- 1 postgres postgres 4 2008-06-24 09:37 PG_VERSION drwx-- 3 postgres postgres 120 2008-06-24 09:37 pg_xlog -rw--- 1 postgres postgres 125 2008-06-30 08:59 postmaster.opts lrwxrwxrwx 1 root root 31 2008-06-24 09:37 root.crt - /etc/postgresql-common/root.crt lrwxrwxrwx 1 root root 36 2008-06-24 09:37 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 2008-06-24 09:37 server.key - /etc/ssl/private/ssl-cert-snakeoil.key 'server.key' seems to be writable to all and sundry, although the file it is linked to (ssl-cert-snakeoil.key) is not: [EMAIL PROTECTED]:/etc/ssl/private# ls -l total 4 -rw--- 1 root ssl-cert 887 2008-06-11 12:18 ssl-cert-snakeoil.key You will need to give postgres read permission to /etc/ssl/private/ssl-cert-snakeoil.key This also means to give 'traverse directory' (x) permissions on all the directories in the path to user postgres. You can test it by becoming user postgres and trying to 'cat' the file. Was it you who set up the system like that? Maybe there are good reasons why the key file is only accessible by root. Maybe you shouldn't use this file as your server key. But these are considerations beyond my view here. Yours, Laurenz Albe -- 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 crashing
Roberts, Jon wrote: Roberts, Jon wrote: Tom Lane wrote: Roberts, Jon [EMAIL PROTECTED] writes: Version: PostgreSQL 8.3.0, compiled by Visual C++ build 1400 Well, there are plenty of known bugs in 8.3.0 by now. You really should update before complaining, not after. Yes. And the traditional question should be asked - is there any antivirus or other personal security software running on tihs machine? If so, uninstall (not just disable!) it and see if the problem goes away. I am not able to un-install this. However, this problem only started as the database grew in size and usage. It is 232 MB in size now and it has 30 or so active sessions 24x7. But you do run antivirus on the machine? Which antivirus? We are running Computer Associates eTrust Antivirus. The realtime scanner has an exclusion of e:\PostgreSQL and all subdirectories. Yeah. Exclusion alone often isn't enough though. CA isn't one of the ones on my blacklist, but *all* AV is on my very-dark-graylist. It's quite possible that this is the reason. As the files grow, the AV may take longer to do whatever crap it's doing to them, thus exceeding timeouts. The log files are located here: e:\PostgreSQL\data\pg_log Before I had that exclusion on the directory, the database would crash because data files would get locked. We don't get those errors now at all so I don't think this problem is caused by the antivirus program. It can still very much be caused by the AV. We do have lots of sessions running at once. It seems that the extra load on the server could also cause it to exceed the timeout. Lots of sessions = lots of processes = lots of AV don't like that. In general, you can get all sorts of strange things when you run AV on your database server. The general recommendation is never to do that if you want things to work, and it will be the first thing you're told to remove. I agree to an extent. I've used Oracle and SQL Server on Windows and when the antivirus program scans any database files, it screws it up. However, I've never had a problem with an antivirus program once the database directories were excluded. The difference is that PostgreSQL uses a multi-process architecture with inherited handles. Most AV don't deal well with that, and it can show up as very strange errors. SQL Server and Oracle are both threadead on win32, which is what the AV software is used to seeing. It would be good if you could at least temporarily remove it and see if it fixes the issue. I can't do that. In this corporate environment, I would get shot if I did that. Oops. We wouldn't want *that* to happen :D It would also be interesting to know if there are actually any other processes running at this time. Yes, there were about 30 active sessions executing functions. Right, but are the processes still active at the moment whrere it crashes, or has the postmaster managed to kill them off for the restart? Yes, there are active sessions. Could this be the problem? We have a queue table in which we only allow x number of concurrent jobs. A job is first inserted into the Queue with a Status of Queued. Next the job executes a function to update the Status to Processing. Function Details: 1. Creates a db_link 2. lock table gp_load.queue in access exclusive mode 3. executes this in the linked session: select count(*) from gp_load.queue where status = 'Processing' 4. if current jobs less than max jobs allowed then Update gp_load.queue set status = 'Processing' Commit 5. Close connection 6. Returns the status of still Queued or Processing I have to use a dblink because PostgreSQL doesn't have autonomous transactions. We call a single function to do all of this work and the other jobs need to see the current Status so they know if they should wait or run right now. The calling function uses a while loop to execute this function. If the function returns Queued, it means it was unable to change the status so it uses pg_sleep to wait 10 seconds and then it trys again until it is able to set the status to Processing. Sorry for the lengthy answer but yes, we could have active sessions using pg_sleep waiting for a slot in the Queue to process a job. The log file shows that this happened too. Not having looked at the internals of db_link, I'd say it's certainly possible that this is the reason for the failed restart. If db_link is blocking something, the postmaster can't kill it off, and it'll still be sitting there holding a reference to the shared memory segment. That said, it shouldn't be the reason why it's crashing in the first place - just the reason why it won't restart properly. //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] SRF written in C
El mié, 02-07-2008 a las 08:02 +0200, Martijn van Oosterhout escribió:
On Tue, Jul 01, 2008 at 10:02:39AM -0500, Felipe de Jesús Molina Bravo wrote:
Hi
what can i do for a SRF written in C, can called as follow:
select * from obtAscendencia('(11099,15685)','(6808,9621)');
I can call the function:
select obtAscendencia('(11099,15685)','(6808,9621)');
I'm afraid you did not explain what exactly the problem is. Do you mean
that one or the other of the statements doesn't work? Which one, and
what is the error message?
Have a nice day,
ok... sorry
the next statement:
select * from obtAscendencia('(11099,15685)','(6808,9621)');
never end i need to kill the process assigned for my statement...
and the other statement:
select obtAscendencia('(11099,15685)','(6808,9621)');
is correct ... the output is:
obtascendencia
(4291, 6064)
(1774, 2507)
(1031, 1457)
(288, 407)
(121, 171)
(75, 106)
(29, 41)
(12, 17)
(7, 10)
(2, 3)
(10 filas)
and my question is :
Why can not perform my function as the first statement?
I suspect that my error is:
r = obtPadre( intF-izq, intF-der );
if ( ( r-num != 1 )
( r-den != 2 ) )
{
intF-der.num = intF-izq.num;
intF-der.den = intF-izq.den;
intF-izq.num = r-num;
intF-izq.den = r-den;
SRF_RETURN_NEXT(funcctx, (Datum)(r));
}
Where r is type Racional (Rational)...
thanks in advance
see you
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres benchmark?
I am interesting in finding a good Postgres benchmark. I an not interested in seeing how fast Postgres is compared to MySql, Firebird, or any other SQL database. What I am interested in is how file systems, memory, and X-64 vs X-32 effects the performance of Postgres. It is more for my own curiosity to be honest. Right now Postgres is more than fast enough for what I am doing. -- 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] Postgres benchmark?
David Siebert wrote: I am interesting in finding a good Postgres benchmark. I an not interested in seeing how fast Postgres is compared to MySql, Firebird, or any other SQL database. What I am interested in is how file systems, memory, and X-64 vs X-32 effects the performance of Postgres. It is more for my own curiosity to be honest. Right now Postgres is more than fast enough for what I am doing. Do you want to use PostgreSQL for any particular task? If so, the best benchmark is probably one that simulates your specific workload. Comparing generic benchmark results like pgbench etc may not usefully reflect performance in real-world use with your load and your data. -- Craig Ringer -- 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] SRF written in C
Felipe de Jesús Molina Bravo wrote: I suspect that my error is: r = obtPadre( intF-izq, intF-der ); You didn't show obtPadre(). -- 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
Re: [GENERAL] pg crashing
Not having looked at the internals of db_link, I'd say it's certainly possible that this is the reason for the failed restart. If db_link is blocking something, the postmaster can't kill it off, and it'll still be sitting there holding a reference to the shared memory segment. That said, it shouldn't be the reason why it's crashing in the first place - just the reason why it won't restart properly. Is this a problem in Unix? We are about 1 - 2 weeks away from moving this database to Solaris. Jon -- 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 crashing
Roberts, Jon wrote: Not having looked at the internals of db_link, I'd say it's certainly possible that this is the reason for the failed restart. If db_link is blocking something, the postmaster can't kill it off, and it'll still be sitting there holding a reference to the shared memory segment. That said, it shouldn't be the reason why it's crashing in the first place - just the reason why it won't restart properly. Is this a problem in Unix? We are about 1 - 2 weeks away from moving this database to Solaris. Not likely, but I'd test it anyway. If the issue is related to AV, it's certainly fine - you won't be running AV on your Solaris. But more importantly, Unix has actual support for signals and not just the fake stuff we have on Win32, so it's likely that the postmaster will be capable of killing the child processes. //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] Memory Problem
Volkan YAZICI [EMAIL PROTECTED] writes: We have an IBM System x3850 machine running on RHEL 4.5 Cluster Suite with high-availability enabled. During a huge delete process, PostgreSQL (8.3.1) exhausts available memory and receives an OOM kill. Are there any foreign keys referencing this table? If so, you're probably running out of memory for the list of pending trigger events (to verify that the FK constraint isn't violated by the delete). Allowing the triggers to fire individually would take forever anyway, so it might be best to drop the foreign key constraint(s) and then re-establish them after the delete. 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] Postgres benchmark?
Craig Ringer wrote: David Siebert wrote: I am interesting in finding a good Postgres benchmark. I an not interested in seeing how fast Postgres is compared to MySql, Firebird, or any other SQL database. What I am interested in is how file systems, memory, and X-64 vs X-32 effects the performance of Postgres. It is more for my own curiosity to be honest. Right now Postgres is more than fast enough for what I am doing. Do you want to use PostgreSQL for any particular task? If so, the best benchmark is probably one that simulates your specific workload. Comparing generic benchmark results like pgbench etc may not usefully reflect performance in real-world use with your load and your data. and If you are running Solaris/OpenSolaris, DTrace is your friend. Mayuresh -- Craig Ringer -- 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] Postgres benchmark?
Well that is the rub. I am currently using Postgres everyday. It runs like a champ and even on an old PIII 600 MHZ machine with a single old and slow IDE drive and 256 megs of ram it is fast enough for what we do. This is more for me to do some testing with. I think it would useful as a tunning tool if nothing else. What I would like to try just for my own amusment is to build a small test box. It will not be a server class machine. I am thinking of using an AMD X2 and to start a SATA hard drive. Then I would like to test different file systems, then different operating systems, different amounts of ram, 32 vs 64 bit, and software raids. I would use the same machine for all the tests so it would have a good base line. I doubt that would ever publish my results. The flame war that would happen would take all the fun out of it for me. I am sure that someone would say that since I wasn't using a server machine that my results where invalid, others would say that I made errors in tuning for the different operating systems or that X would show benefits if I was using a real server machine. And all of them may be right. But sometimes you just want to play. Do you want to use PostgreSQL for any particular task? If so, the best benchmark is probably one that simulates your specific workload. Comparing generic benchmark results like pgbench etc may not usefully reflect performance in real-world use with your load and your data. -- Craig Ringer -- 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 crashing
Roberts, Jon [EMAIL PROTECTED] writes: I'm not complaining. I just want to make sure that if I upgrade, it will fix the problem. An upgrade could possible introduce a new problem. I also wonder if this is isolated to Win32 because we are upgrading to Solaris very soon. The specific problem you are seeing is clearly isolated to Win32, because the error message you are getting comes out of Win32-only code. This is not to say that there might not be comparable failures on Unixen, but PG has a lot more track record on Unixen ... 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] SRF written in C
El mié, 02-07-2008 a las 10:39 -0400, Alvaro Herrera escribió:
Felipe de Jesús Molina Bravo wrote:
I suspect that my error is:
r = obtPadre( intF-izq, intF-der );
You didn't show obtPadre().
ok it is:
Racional* obtPadre( Racional *li, Racional *ld){
Racional *lip;
Racional *tr = NULL; //ap temporal, para hacer el intercambio
bool cont;
int opAnt; //operacion anterior
lip = (Racional *) palloc(sizeof(Racional));
tr = (Racional *) palloc(sizeof(Racional));
cont = true;
opAnt = 0;
while( cont ){
if ( li-num ld-num ){
//intercambiamos li - ld
SWAP_RACIONAL(li, ld );
cont = false;
}
lip-num = ld-num - li-num;
lip-den = ld-den - li-den;
if ( cont ){
while( cont ){
//recorremos
SWAP_RACIONAL(li, ld );
li-num = lip-num;
li-den = lip-den;
if ( li-num ld-num ){ //si hay intercambio ...
continua en el ciclo
SWAP_RACIONAL(li, ld );
}else{ //si no hay intercambio
cont = false; //salimos del ciclo
}
//... pero antes de salir calculamos
lip-num = ld-num - li-num;
lip-den = ld-den - li-den;
}
}
}
return ( lip );
}
--
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 crashing
BTW, just looking at win32_shmem.c ... retptr = malloc(bufsize + 1 + 18);/* 1 NULL and 18 for * Global\PostgreSQL: */ if (retptr == NULL) elog(FATAL, could not allocate memory for shared memory name); strcpy(retptr, Global\\PostgreSQL:); r = GetFullPathName(DataDir, bufsize, retptr + 11, NULL); Surely that 11 ought to be 18. Also, since the loop immediately below this is going to convert \ to /, wouldn't it be clearer to describe the path prefix as Global/PostgreSQL: in the first place? (BTW, as far as I can tell the +1 added to the malloc request is useless: bufsize includes the trailing null, and the code would not work if it did not.) 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Klint Gore [EMAIL PROTECTED] writes: Can someone shed some light on what's happening here? D:\backupspg_dump -Z 9 -Fc -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump: Dumping the contents of table uploadeddatafiles failed: PQgetCopyData () failed. pg_dump: Error message from server: lost synchronization with server: got messag e type d, length 6036499 Hmm ... I think what is actually happening here is that pg_dump doesn't have enough memory available to buffer the message. The only places where libpq could report that error text with those parameters are where pqCheckInBufferSpace has failed to enlarge the input buffer sufficiently. Per the code comment: /* * XXX add some better recovery code... plan is to skip over * the message using its length, then report an error. For the * moment, just treat this like loss of sync (which indeed it * might be!) */ 6 meg doesn't seem particularly enormous though. Are you running pg_dump under some especially restrictive user limits? Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. 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] pg crashing
Magnus Hagander [EMAIL PROTECTED] writes: Not likely, but I'd test it anyway. If the issue is related to AV, it's certainly fine - you won't be running AV on your Solaris. But more importantly, Unix has actual support for signals and not just the fake stuff we have on Win32, so it's likely that the postmaster will be capable of killing the child processes. I'm not sure what failure mode you're imagining, but the postmaster has already verified that all the children that are supposed to be connected to shared memory are dead before it attempts to recreate shared memory. So the above sounds completely bogus. I'm still suspicious of the syslogger holding onto an inherited handle to the shared-memory file, though that theory would seem to mean that crash recovery would never work at all on Windows if the syslogger were enabled. But maybe there is some additional gating factor needed to cause the problem to manifest. 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] pg crashing
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Not likely, but I'd test it anyway. If the issue is related to AV, it's certainly fine - you won't be running AV on your Solaris. But more importantly, Unix has actual support for signals and not just the fake stuff we have on Win32, so it's likely that the postmaster will be capable of killing the child processes. I'm not sure what failure mode you're imagining, but the postmaster has already verified that all the children that are supposed to be connected to shared memory are dead before it attempts to recreate shared memory. So the above sounds completely bogus. I'm still suspicious of the syslogger holding onto an inherited handle to the shared-memory file, though that theory would seem to mean that crash recovery would never work at all on Windows if the syslogger were enabled. But maybe there is some additional gating factor needed to cause the problem to manifest. Well, the syslogger is enabled by default on *all* binary installs on windows, so I think we would've seen more if it never works. I'll see if I can repro a case like it to see if the syslogger prevents the shared mem from going away when I get back to a dev box. Should be enough to just stick a sleep preventing it from stopping, right? //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] pg crashing
Magnus Hagander [EMAIL PROTECTED] writes: I'll see if I can repro a case like it to see if the syslogger prevents the shared mem from going away when I get back to a dev box. Should be enough to just stick a sleep preventing it from stopping, right? The syslogger isn't restarted at all during a crash --- this isn't a race-condition scenario. If there is a race condition here, it must be associated with cleanup for a process continuing to happen after win32_waitpid has already reported it dead. Hmm ... how much do we trust that bit of spaghetti around pgwin32_deadchild_callback? What condition is it really waiting for? 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
[GENERAL] Delete from Join
Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G -- 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] simple tool for building web forms
On Wed, Jul 2, 2008 at 8:35 AM, Roberts, Jon [EMAIL PROTECTED] wrote: http://www.sqlmaestro.com/products/postgresql/ I've used the PHP Code Generator with great success for simple stuff like you describe. You could then write a function to do email notifications or whatever you want. Jon -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Ron Peterson Sent: Wednesday, July 02, 2008 7:05 AM To: pgsql-general@postgresql.org Subject: [GENERAL] simple tool for building web forms Can anyone recommend a good tool for building simple web forms w/ a PostgreSQL backend? Emphasis on simple - single table contact info forms, etc. Something that can be presented to end users w/out a lot of hand-holding. E.g. I want a form w/ last name, first name, # of guests, arrival date. Send notification email of new entries to [EMAIL PROTECTED] and [EMAIL PROTECTED] Ideally the output could easily be embedded in other web pages. F/OSS preferred, but proprietary not out of the question. This isn't a terribly difficult thing to write, but I imagine it's already been done many times over. I'd rather re-use something existing that re-invent the wheel. Dr. Google hasn't been as helpful as I'd like so far. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I too would recommend PHP. For simple forms, there really isn't much to it, and can easily be integrated with an existing site. If you're looking to a more complex tool, I would recommend any one of the popular Content Management Systems (CMSs). I've had success with many but have been using Drupal lately, combined with the OSS / flash-based, Flex framework. As far a proprietary software, www.businessobjects.com is quite popular with the corporate industry. jcvlz -- 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 with roles and permissions
Bah. Must be pgAdmin, then. In its role creation dialogue, INHERIT is definitely off by default. Guess that's not you guys, though. Sorry to have impugned your judgement... =) -cb. Chandra Barnett [EMAIL PROTECTED] writes: That did it! Thanks, all. (Out of curiosity, what's the reason for not making this the default? Uh ... it *is* the default. regards, tom lane No virus found in this outgoing message. Checked by AVG. Version: 7.5.526 / Virus Database: 270.4.4/1530 - Release Date: 7/2/2008 8:05 AM -- 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] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 3:15 PM Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G i have a example delete from t1 a using t2 b where a.id = b.oid A standard way to do it is delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) -- 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] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: Below is a test case that simulates the use of xpath() within a plpgsql function in my application. I'm able to duplicate the memory leak in this function with the current Fedora 8 libxml2 (2.6.32). The leak is definitely inside libxml2 itself, because the bloat shows up here: LibxmlContext: 150986752 total in 27 blocks; 6025448 free (204 chunks); 144961304 used and that context is only used for allocations requested by libxml2. (We reset that context at every transaction end, so the only way for the problem to become really noticeable in Postgres is to do a whole lot of xpath() calls in a single transaction.) Some idle looking through the context suggests that the leakage consists of short individually palloc'd null-terminated strings that seem to be words from your test data. I tried dumping the context stats at successive entries to xpath(): LibxmlContext: 260038656 total in 40 blocks; 3934120 free (204 chunks); 256104536 used LibxmlContext: 260038656 total in 40 blocks; 3934088 free (204 chunks); 256104568 used LibxmlContext: 260038656 total in 40 blocks; 3934056 free (204 chunks); 256104600 used LibxmlContext: 260038656 total in 40 blocks; 3934056 free (205 chunks); 256104600 used LibxmlContext: 260038656 total in 40 blocks; 3934000 free (205 chunks); 256104656 used LibxmlContext: 260038656 total in 40 blocks; 3933960 free (204 chunks); 256104696 used LibxmlContext: 260038656 total in 40 blocks; 3933928 free (204 chunks); 256104728 used LibxmlContext: 260038656 total in 40 blocks; 3933896 free (204 chunks); 256104760 used LibxmlContext: 260038656 total in 40 blocks; 3933856 free (204 chunks); 256104800 used LibxmlContext: 260038656 total in 40 blocks; 3933816 free (204 chunks); 256104840 used LibxmlContext: 260038656 total in 40 blocks; 3933784 free (204 chunks); 256104872 used LibxmlContext: 260038656 total in 40 blocks; 3933752 free (204 chunks); 256104904 used LibxmlContext: 260038656 total in 40 blocks; 3933752 free (205 chunks); 256104904 used LibxmlContext: 260038656 total in 40 blocks; 3933696 free (205 chunks); 256104960 used LibxmlContext: 260038656 total in 40 blocks; 3933656 free (204 chunks); 256105000 used LibxmlContext: 260038656 total in 40 blocks; 3933624 free (204 chunks); 256105032 used which shows that not every call in this test case leaks memory, but the majority do, and that the leakage runs between 32 and 56 bytes per call. This is on a 64-bit machine with cassert enabled, which means that the palloc overhead is 24 bytes per chunk. So I think we can fairly confidently assert that libxml is leaking exactly one short string per xpath() call --- the leak is too small for it to be more than one palloc chunk at a time. I looked through the libxml2 sources a little bit but couldn't immediately find the problem. I'm fairly confident though that this could be reproduced outside Postgres, by replicating the sequence of libxml2 calls we make in xpath(). The next step should probably be to build a reproducer program and submit it to the libxml authors, or maybe run it under a debugging malloc package that could help identify where the leak is. Anyone want to do that? 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
[GENERAL] changing text search treatment of puncutation
Text with the '/' character gets treated as a file path, e.g.
select * from to_tsvector('english', 'home/work');
gives only the single token:
'home/work':1
Changing '/' to '-' gives
'home':2 'work':3 'home-work':1
which is much more desirable for this application.
Is there an easy way to change '/' to be treated like '-' ? I've
looked over the documentation several times and could not find
anything. Even just a way to get the two tokens 'home' and 'work'
without the joined form would be helpful.
Thanks,
John DeSoi, Ph.D.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SET CLIENT_ENCODING
Hallo, I would to ask about function (vide subject) transcoding the characters between different character pages on the fly: is it smart enough to skip any transcoding operation, when both client and database are set to use the same encoding already? To make it more clear: I've got an application, which always uses LATIN2 encoding, and wants database using this encoding. Unfortunately, some Postgres packages allow to set it - but some are UTF-8 only. I'm wondering, whether can I add into application command SET CLIENT_ENCODING TO 'LATIN2'; for steady, or it should be kept optional? I would to avoid situation, when Postgres will transcode from LATIN2 to LATIN2. Perhaps someone knows the details, how is it working in practice? -- pozdrawiam / regards Zbigniew Baniewski -- 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] Memory use in 8.3 plpgsql with heavy use of xpath()
I looked through the libxml2 sources a little bit but couldn't immediately find the problem. I'm fairly confident though that this could be reproduced outside Postgres, by replicating the sequence of libxml2 calls we make in xpath(). The next step should probably be to build a reproducer program and submit it to the libxml authors, or maybe run it under a debugging malloc package that could help identify where the leak is. Anyone want to do that? Ugh, I'd love to give that a shot but that is a bit outside my comfort zone. -- 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] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 3:15 PM Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G i have a example delete from t1 a using t2 b where a.id = b.oid A standard way to do it is delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) Thank you for your reply, You are absolutely correct, it IS the standard way. What I am actually trying to do here is write a program to convert MS SQL to Postgres. I have had quite a bit of success so far, but this is a sticking point. Apparently using the JOIN keyword directly in a delete statement is valid in MS. I am trying to determine if it is valid in postgres which I figure it is not but cannot find it exactly in the documentation. So I guess the real question is, can the JOIN keyword be used directly in a delete as above. G -- 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] Memory use in 8.3 plpgsql with heavy use of xpath()
On Wed, 2 Jul 2008, Tom Lane wrote:
Matt Magoffin [EMAIL PROTECTED] writes:
Below is a test case that simulates the use of xpath() within a plpgsql
function in my application.
I'm able to duplicate the memory leak in this function with the current
Fedora 8 libxml2 (2.6.32). The leak is definitely inside libxml2
itself, because the bloat shows up here:
I think this should fix it.
Kris JurkaIndex: src/backend/utils/adt/xml.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/xml.c,v
retrieving revision 1.74
diff -c -r1.74 xml.c
*** src/backend/utils/adt/xml.c 12 May 2008 00:00:51 - 1.74
--- src/backend/utils/adt/xml.c 2 Jul 2008 22:22:57 -
***
*** 3160,3165
--- 3160,3166
{
str = xmlXPathCastNodeToString(cur);
result = (xmltype *) cstring_to_text((char *) str);
+ xmlFree(str);
}
return result;
--
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] Memory use in 8.3 plpgsql with heavy use of xpath()
I'm able to duplicate the memory leak in this function with the current Fedora 8 libxml2 (2.6.32). The leak is definitely inside libxml2 itself, because the bloat shows up here: I think this should fix it. Kris Jurka It looks like xml.c source has changed considerably since 8.3 (looking at revision 1.68.2.2 from the 8.3.3. release). Do you know where/if this patch would apply to the 8.3 branch? -- 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] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: It looks like xml.c source has changed considerably since 8.3 No, hardly at all actually, but this patch happens to be right next door to one of the lines that did change. cstring_to_text() replaces some grottier stuff that used to be used for the same purpose. In a few minutes I'm going to verify whether Kris' fix makes the leak go away for me, and commit to CVS if so. 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] Memory use in 8.3 plpgsql with heavy use of xpath()
I think this should fix it. Kris Jurka It looks like xml.c source has changed considerably since 8.3 (looking at revision 1.68.2.2 from the 8.3.3. release). Do you know where/if this patch would apply to the 8.3 branch? I diff'ed 1.74 and 1.68.2.2, and I'm guessing this new line could be added after line 3203 in 1.68.2.2? I'll give that a try... -- m@ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_ctl start check sum failed
Hello, I try to use an old cluster into a new system. The new system comes with a newer version of postgres so I uninstalled it and I installed the same version that I had in the older system ---8.1 I got impressed when I Installed the 8.1 with dpkg -i and it started to run without starting the daemon... Is it possible to know what directory is the server using to store the data? --- main question Once I have installed the server I try to start it using pg_control start -D /oldCluster directory but I get FATAL checksum incorrect. I want to use the old data, any ideas? Many thanks
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: I think this should fix it. Kris Jurka Confirmed, that makes it go away nicely here: LibxmlContext: 57344 total in 3 blocks; 55720 free (202 chunks); 1624 used It looks like xml.c source has changed considerably since 8.3 (looking at revision 1.68.2.2 from the 8.3.3. release). Do you know where/if this patch would apply to the 8.3 branch? Here's what I just committed to the 8.3 branch: Index: xml.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/xml.c,v retrieving revision 1.68.2.2 diff -c -r1.68.2.2 xml.c *** xml.c 24 Mar 2008 19:12:58 - 1.68.2.2 --- xml.c 2 Jul 2008 23:57:20 - *** *** 3201,3206 --- 3201,3207 result = (text *) palloc(len + VARHDRSZ); SET_VARSIZE(result, len + VARHDRSZ); memcpy(VARDATA(result), str, len); + xmlFree(str); } return result; 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] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: Later, I added a large set of plpgsql trigger functions that operate on that new xml column data, using the xpath() function to extract bits of XML and populate them into normal tables. The server has been running in this fashion for many months now, and there is a noticeable difference in how Postgres is using memory now, in that over time it's non-shared memory use is climbing higher and higher. Right now I'm tracking this from data captured by Munin on the system. The memory creep is very slight, but over many months is easy to discern. Looking back, I'm not sure that what we just found explains that part of your original report. If I run some of these plpgsql functions using a lot of xpath() calls on large sets of data, huge amounts of memory are consumed (gigabytes) and the memory seems to accumulate until the transaction the functions are running in completes. This part seems to match the bug though --- the leak is approximately the same size as all the text returned by xpath() within the current transaction. So there may be a second issue remaining to be found. Can you put together a test case for the long-term small leak? 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] changing text search treatment of puncutation
John DeSoi [EMAIL PROTECTED] writes: Is there an easy way to change '/' to be treated like '-' ? I've looked over the documentation several times and could not find anything. Even just a way to get the two tokens 'home' and 'work' without the joined form would be helpful. Seems like the simplest solution is just to apply regexp_replace(text, '/', '-', 'g') before letting the text search stuff have the string. If you're using a trigger to update a tsvector column, this would be pretty trivial to do within the trigger. In general there seem to be a lot of ways that people wish they could tweak the text search parser, and telling them to write their own parser isn't a very helpful response for most folk. I don't have an idea about how to improve the situation, but it seems like something that should be thought about. 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] Delete from Join
Take a look here, in the notes section: http://www.postgresql.org/docs/8.3/interactive/sql-delete.html on the using keyword. -- 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] SET CLIENT_ENCODING
Zbigniew Baniewski [EMAIL PROTECTED] writes: I would to ask about function (vide subject) transcoding the characters between different character pages on the fly: is it smart enough to skip any transcoding operation, when both client and database are set to use the same encoding already? Yes. 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Can someone shed some light on what's happening here? D:\backupspg_dump -Z 9 -Fc -C -U postgres -f sheepcrc\dbback.dmp sheepcrc2 pg_dump: Dumping the contents of table uploadeddatafiles failed: PQgetCopyData () failed. pg_dump: Error message from server: lost synchronization with server: got messag e type d, length 6036499 6 meg doesn't seem particularly enormous though. Are you running pg_dump under some especially restrictive user limits? Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. I'm running it under my own account which has adminstrator rights. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Is there a binary debug build for win32 somewhere? 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Well, that might well explain the failure. pg_dump does suck a lot of schema information into memory at startup, but 540m seems excessive. Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? 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] Memory use in 8.3 plpgsql with heavy use of xpath()
This part seems to match the bug though --- the leak is approximately the same size as all the text returned by xpath() within the current transaction. So there may be a second issue remaining to be found. Can you put together a test case for the long-term small leak? regards, tom lane Hmm, I'm not sure what else to add to this test case. This test case was a good example of what our database is doing with xpath(); it is using quite a number of them, that's all. Is there something else in particular you'd be looking for in another test case? -- 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_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe it's dying here after having leaked a lot of memory for some other reason --- try watching the pg_dump process size while it runs. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Well, that might well explain the failure. pg_dump does suck a lot of schema information into memory at startup, but 540m seems excessive. Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? regards, tom lane I wouldn't call it large - 27 tables, 111 functions, 21 custom types (used for set returning function results). The biggest row count table has about 200k records (structure is int,int,timestamp) The biggest physical table is the one thats failiing. The table itself is physically 81m and its toast table is 82m. 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
[GENERAL] Confusion about ident sameuser
Hi All, I am confused with the 'ident sameuser' authentication. Here's my setup: [EMAIL PROTECTED] ~]$ grep -v ^# data_dir/pg_hba.conf local all all ident sameuser hostall all 127.0.0.1/32 ident sameuser hostall all 10.0.0.0/8password [EMAIL PROTECTED] ~]$ pg_ctl -D data_dir reload [EMAIL PROTECTED] ~]$ psql -h 10.10.0.27 -p 6543 -d postgres -U postgres Password for user postgres: [EMAIL PROTECTED] ~]$ psql -p 6543 -d postgres -U postgres Welcome to psql 8.1.11, the PostgreSQL interactive terminal. [EMAIL PROTECTED] ~]$ psql -h localhost -p 6543 -d postgres -U postgres psql: FATAL: Ident authentication failed for user postgres As you can see, if I use the machine's interface or unix sockets, it either asks for password or lets me in. But when I use localhost, it correctly uses 127.0 line for authentication, but does not let me in!!! Is this not supposed to work? Could it be because I might not be having an ident server running on my box? How do I determine if I have an ident server running? Thanks in advance, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [GENERAL] Confusion about ident sameuser
On Wed, Jul 2, 2008 at 9:55 PM, Gurjeet Singh [EMAIL PROTECTED] wrote: As you can see, if I use the machine's interface or unix sockets, it either asks for password or lets me in. But when I use localhost, it correctly uses 127.0 line for authentication, but does not let me in!!! Is this not supposed to work? Could it be because I might not be having an ident server running on my box? How do I determine if I have an ident server running? It's almost certain that that's the problem. ident is configured off by default on most systems. Whether it's a separate daemon or invoked through inetd/xinetd depends on the distribution. -Doug -- 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] LDAP Authentication
On Sun, 2008-06-29 at 17:58 +0200, Magnus Hagander wrote: This is not something you currently can do. We can only do LDAP authentication, not authorization. There's no way to restrict it to a particular group. We're very interested in this functionality (nss_ldap for PgSQL) -- so if there's a joint-development effort that we can contribute man-hours or development resources (challenge grant funding, hardware, etc.) to, let us know. ~BAS One way to accomplish what you're trying to do is to have a script that synchronizes the members of the group to PostgreSQL accounts (account name and role membership only), and still use LDAP for authentication. IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date Formatting for dd/mm/yyyy
Hi all.
I am using Postgres 8.3 with Windows XP Pro.
System date format is dd/MM/
Having problems when I writing records to a database with a single
'date' type field.
e.g. FieldByName('Date').ASString:='2/2/2003'
When the row is viewed in pgAdmin it is always 1899-12-30
I have tried changing the datestyle in postgresql.conf thus:
datestyle='iso,dmy'
datestyle='dmy'
datestyle='ymd'
datestyle='European'
also
lc_time= 'English_Australia.1252'
(obviously stopping and starting Server)
Nothing seems to make any difference.
Who has got an answer to this.
Also thanks to Craig Ringer and others for answering my earlier
questions, it greatly helped me get up and running with postgres.
--
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] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: So there may be a second issue remaining to be found. Can you put together a test case for the long-term small leak? Hmm, I'm not sure what else to add to this test case. This test case was a good example of what our database is doing with xpath(); it is using quite a number of them, that's all. Is there something else in particular you'd be looking for in another test case? Well, you tell me --- *you* reported a behavior that isn't obviously explained by the bug we found. It's possible that what you were seeing was an indirect effect of the now-known bug: if the xpath leak were to occur repeatedly on a large scale in a long-lived session, I think it's possible that memory allocation behavior might suffer due to fragmentation effects. I feel that that's a pretty hand-wavy explanation though. Probably the right thing for you to do now is just to install the known fix, and keep an eye on your server for awhile to see if you still see any indication of the long-term leak behavior. 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? I wouldn't call it large - 27 tables, 111 functions, 21 custom types (used for set returning function results). [ squint... ] Hard to see how that could be eating half a gig of pg_dump memory space. Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Maybe you've found a memory leak in pg_dump (it wouldn't be the first one). Does this database have a particularly large number of objects? I wouldn't call it large - 27 tables, 111 functions, 21 custom types (used for set returning function results). [ squint... ] Hard to see how that could be eating half a gig of pg_dump memory space. Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. Upon more investigation pg_dump 8.3.1 on linux (ubuntu feisty) across the network completes properly. 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] pg_dump - lost synchronization with server: got message type d, length 6036499
Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. attached. created with pg_dump 8.3.3 win32 to the 8.3.1 win32 server. Thanks. When I reload this here and try to pg_dump it, I see no evidence of any problem --- pg_dump completes pretty quickly and seems to eat no more than a megabyte or so. I'm not sure what to make of that. Could there be a Windows-specific memory problem in pg_dump? 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] Memory use in 8.3 plpgsql with heavy use of xpath()
Probably the right thing for you to do now is just to install the known fix, and keep an eye on your server for awhile to see if you still see any indication of the long-term leak behavior. Certainly, that is my plan. Once I can get the patch rolled out to these systems, I should be able to see if the memory-creep trend is resolved after a couple of weeks or so. -- 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] Date Formatting for dd/mm/yyyy
J Ottery wrote:
Hi all.
I am using Postgres 8.3 with Windows XP Pro.
System date format is dd/MM/
Having problems when I writing records to a database with a single
'date' type field.
e.g. FieldByName('Date').ASString:='2/2/2003'
What is delphi's ShortDateFormat set to?
Does fieldbyname('Date').asdatetime := strtodate('2/2/2003') make any
difference?
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
