Re: [SQL] trap for any exception
That's ok, I found SQLSTATE and SQLERRM. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Is there anything special about pg_dump's compression?
When I run pg_dump, the computer spends a great amount of time in system state. Like 100% of one cpu and part of another. The small part seems to be the postgreSQL server, and the big part the client (pg_dump) compressing the data. Now my tape drive has built-in compression anyway (although I could turn it off). I prefer to let the hardware compression run since it is a nuisance to turn it on and off and I want it on for my normal backups of the rest of the system. Does pg_dump's compression do anything really special that it is not likely the tape drive already does? The drive claims 2:1 compression for average data (e.g., not already compressed stuff like .jpeg files). -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 10:50:01 up 23 days, 4:08, 5 users, load average: 4.16, 4.40, 4.44 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is there anything special about pg_dump's compression?
On Thu, Nov 15, 2007 at 11:05:44AM -0500, Jean-David Beyer wrote: Does pg_dump's compression do anything really special that it is not likely the tape drive already does? The drive claims 2:1 compression for average data (e.g., not already compressed stuff like .jpeg files). It's zlib, if I recall correctly. So probably not. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] trap for any exception
Hi there, I'd like to build a PL/pgSQL function which is able to generic trap any error, and interpret it. I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? TIA, Sabin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Erlang PostgreSQL native driver
Hi,
We are using Erlang, PostgreSQL 8.2.5, and the Erlang library pgsql
developed by Christian Sunesson
(http://jungerl.cvs.sourceforge.net/jungerl/jungerl/lib/pgsql/).
The Erlang library pgsql is easy to use and does the job so far, at
least for our prototyping phase. However, this library has a problem
with stored function that returns nothing such as:
CREATE OR REPLACE FUNCTION foo()
RETURNS void
AS $$
...
The problem seems to happen when the Erlang library pgsql tries to
decode the result value return by the stored function, which is void.
The transaction has been successfully committed in database.
([EMAIL PROTECTED])3 pgsql:squery(Connection, SELECT foo()).
=ERROR REPORT 15-Nov-2007::11:40:25 ===
Error in process 0.38.0 on node '[EMAIL PROTECTED]' with exit value:
{{nocatch,{unknown_oid,2278}},[{pgsql_util,decode_oid,1},{pgsql_util,dec
ode_descs,1},{pgsql_proto,process_squery,1},{pgsql_proto,idle,2}]}
** exited: {{nocatch,{unknown_oid,2278}},
[{pgsql_util,decode_oid,1},
{pgsql_util,decode_descs,1},
{pgsql_proto,process_squery,1},
{pgsql_proto,idle,2}]} **
I don't think this is a big issue. According to the error
(unknown_oid), I suppose that the pgsql library doesn't support void
type yet. We should be able to fix easily the library.
However, because our prototyping phase is close to the end, we are
thinking to use another library that would perhaps better fit production
constraints (pool, etc.). We have tested the PostgreSQL ODBC driver but
we have faced some issues on Windows (Unicode, PostgreSQL data type
support).
We are interesting in having some feedback from people who use both
Erlang and PostgreSQL. Which PostgreSQL driver do you use? Does
someone use the PostgreSQL driver from Erlang Consulting? Are there
other native PostgreSQL driver?
Thanks,
--
Daniel
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] trap for any exception
On Thu, 15 Nov 2007, Sabin Coanda wrote: I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? Sorry, I found the OTHERS condition that trap any error. But the question still remains to find how could I interpret it and found the error code and message ? I think you'll want to look at SQLSTATE and SQLERRM inside the exception handler. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trap for any exception
On Thu, 15 Nov 2007, Sabin Coanda wrote: Hi there, I'd like to build a PL/pgSQL function which is able to generic trap any error, and interpret it. I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? Won't OTHERS catch most of what you want? From the 8.2 docs: The special condition name OTHERS matches every error type except QUERY_CANCELED. (It is possible, but often unwise, to trap QUERY_CANCELED by name.) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] trap for any exception
I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? Sorry, I found the OTHERS condition that trap any error. But the question still remains to find how could I interpret it and found the error code and message ? Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Is there anything special about pg_dump's compression?
Andrew Sullivan wrote: On Thu, Nov 15, 2007 at 11:05:44AM -0500, Jean-David Beyer wrote: Does pg_dump's compression do anything really special that it is not likely the tape drive already does? The drive claims 2:1 compression for average data (e.g., not already compressed stuff like .jpeg files). It's zlib, if I recall correctly. So probably not. I turned the software compression off. It took: 524487428 bytes (524 MB) copied, 125.394 seconds, 4.2 MB/s When I let the software compression run, it uses only 30 MBytes. So whatever compression it uses is very good on this kind of data. 29810260 bytes (30 MB) copied, 123.145 seconds, 242 kB/s Since the whole database like that was probably in RAM, I would not expect much IO time. Also the data transfer light was on a lot of the time instead of short blinks. It did not seem to lighten the CPU load much. The postgres server process got 100% of a cpu and the client took about 12% of another when running uncompressed. I imagined the client did the compression and writing to tape, and the server just picked up the data from the shared_buffers (= 253000 @ 8KB each); i.e., that holds about 2 GBytes. When the client is compressing, the client's cpu takes about 40% of a processor. When it is not compressing, it takes about 12% of a processor. If I am right, it seems to take a lot of time to pick up the database from RAM if it requires 100% of a 3.06GHz Xeon processor. The tape drive (Exabyte VXA-2) has a 12 MB/sec transfer rate, so it should be the limiting factor (but it does not seem to be), but I do not notice a whole lot of IO-Wait time (though there is some). Any idea why the server is compute-limited just reading from the shared buffers and delivering it to the client to write to tape? Is it that I have too many shared buffers and I should reduce it from about 2 GBytes? Does it sequentially search the shared buffers or something? I made it large so I could get at least all the active indices in, and preferably the hot data pages as well. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 23:15:01 up 23 days, 16:33, 2 users, load average: 5.25, 5.32, 5.34 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Is there anything special about pg_dump's compression?
Jean-David Beyer [EMAIL PROTECTED] writes: I turned the software compression off. It took: 524487428 bytes (524 MB) copied, 125.394 seconds, 4.2 MB/s When I let the software compression run, it uses only 30 MBytes. So whatever compression it uses is very good on this kind of data. 29810260 bytes (30 MB) copied, 123.145 seconds, 242 kB/s Seems to me the conclusion is obvious: you are writing about the same number of bits to physical tape either way. The physical tape speed is surely the real bottleneck here, and the fact that the total elapsed time is about the same both ways proves that about the same number of bits went onto tape both ways. The quoted MB and MB/s numbers are not too comparable because they are before and after compression respectively. The software compression seems to be a percent or two better than the hardware's compression, but that's not enough to worry about really. What you should ask yourself is whether you have other uses for the main CPU's cycles during the time you're taking backups. If so, offload the compression cycles onto the tape hardware. If not, you might as well gain the one or two percent win. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
