[SQL] Embedded C++ with ecpg?
I have a bunch of application programs written in C++ that use Embedded SQL. This have been working for many years with IBM's DB2. I am about to upgrade my Red Hat Enterprise Linux 3 system to RHEL 5, and I would like to use postgresql instead of DB2. I cannot find an authoritative statement that ecpg works with C++. There are hints here and there, including old messages on this mailing list, that it either is meant to work, that it partly works, or is silent. What is the current status of (pre) compiling Embedded SQL in C++ programs? I would absolutely hate to have to rewrite all these programs in C. Red Hat seem to be on postgresql version 8.1.4 for the initial release of RHEL5, and they may have updates a little higher. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 11:30:01 up 1 day, 19:05, 3 users, load average: 4.25, 4.25, 4.25 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Embedded C++ with ecpg?
Tom Lane wrote: > Jean-David Beyer <[EMAIL PROTECTED]> writes: >> What is the current status of (pre) compiling Embedded SQL in C++ programs? > > I just asked Michael Meskes about that (had you put a support request > into Red Hat asking this?). Yes, and Red Hat's answer amounted to no answer at all. They merely repeated a subset of what I already knew, which is why I placed the support request in the first place. > He says > > : There are some C++ constructs that ecpg doesn't parse well, but they are > : not that widely used afaik. Most of the code compiles cleanly. And > : people used to workaround by just putting the sql part in a different > : source file where they didn't need those constructs. > : > : Some effort has been made to make ecpg C++ compliant, so most should > : work out of the box. > Thank you. I am glad to hear that. I imagine almost all my C++ constructs are pretty simple. And most of my SQL ones are, too. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 13:35:01 up 1 day, 21:10, 3 users, load average: 4.16, 4.23, 4.19 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] another simple SQL question
Joshua wrote: > Ok here is another simple question from a novice > > Here is what my table looks like > > firstname lastname fullname > -- -- --- > smith, john > green, susan > white, jeff > > > How can I break the fullname field into firstname lastname fields so it > looks like the following: > > firstname lastname fullname > - - - > john smith smith, john > susan green green, susan > jeff white white, jeff > > Please let me know. Sorry for such simple novice questions, I appreciate > your support. > How I would do it would be to write a trivial application program to do it. I spent a long time working on databases of telephone directory information, and we needed to look up people by name, by address, by town, etc. It turned out that the best way to handle finding-name fields was to leave the first, middle, and last names in one field. A big problem is multicultural. Some people (e.g., Chinese) tend to give family name first followed by given name. Others (e.g., English) tend to give given names first, followed by family name. Telephone operating companies do not get these things correct, so it better just to keep them together. The relational database management system we used, that I originally wrote for UNIX, allowed partial matching on fields, and I even put a SOUNDEX scheme on the name fields. Thus, a query like 'ristorante italiano' would locate 'Moms Pizza Italian Restaurant' without doing a sequential search of the entire database. Similarly, 'T S Eliot' would locate 'Eliot Thomas Stearns'. Problems remain, such as 'Doug Mc Ilroy' would not find 'M Douglas McIlroy" the way we built the program below. You might look here: http://ieeexplore.ieee.org/xpl/freeabs_all.jsp?tp=&arnumber=810466&isnumber=16537 for one way to do this. It explains briefly how to make a suitable index for it. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 12:25:02 up 4 days, 20:00, 3 users, load average: 4.25, 4.14, 4.12 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] how to download linux 7.3 image
Mohd Ghalib Akhtar wrote: > how to download linux 7.3 image file(means os) ? > It is not clear to me what you are trying to do. If you wish to download the .iso files for Red Hat Linux 7.3, it has nothing to do with this mailing list. Furthermore, RHL 7.3 has been discontinued for several years and is no longer supported. It is obsolete. If you want a Red Hat looking product, you should consider running the latest version of Fedora. If you really want an Enterprise version of linux, the current one from Red Hat is called Red Hat Enterprise Linux 5. OTOH, if you want a version of postgreSQL that will run on RHL 7.3, that might be a problem since the current versions of postgreSQL probably all demand a much newer kernel (RHL 7.3 used a 2.2 kernel, IIRC) and associated libraries. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 17:55:01 up 14 days, 12 min, 2 users, load average: 4.22, 4.17, 4.17 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Starting autovacuum in postgresql-8.1.9
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am running Red Hat Enterprise Linux 5 and postgresql-8.1.9-1.el5 (&c.). It seems autovacuum would be a good idea, but I cannot see how to start it, mainly because I cannot find it. There are autovacuum settings in postgresql.conf, but they are all commented out. Does it suffice to turn them on and restart the postmaster? Or are they off because autovacuum is not supported? I have looked around in the PostgreSQL book by Douglas & Douglas and they say it is in a contrib directory, but the one that makes sense does not seem to contain it. Is it built into the server now, or is it to be found somewhere else? In particular, pgavd does not exist anywhere on my system. - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 09:05:01 up 7 days, 12:27, 3 users, load average: 4.16, 4.35, 4.29 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFGxE8DPtu2XpovyZoRAuAgAJ9hrXdGSfX02BRQ/ZZpu+/4fcF+CQCdFAlT RtTL04V+dNhpWi/wh4MLc/w= =Am4q -END PGP SIGNATURE- ---(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
[SQL] there is already a transaction in progress ?
It probably shows I am new to postgreSQL. I recently started running this
instead of DB2, and am converting the applications I already wrote. These
use ecpg.
The problem I have concerns transactions. I have an application (the first
one I am converting) that inserts a lot of stuff into three tables. (It is
normalizing a .tsv file from a spreadsheet.) The program is in C++.
The structure of the program is, I think,
...
dbBase stock_database(STOCK_DB); // Constructor opens connection
...
EXEC SQL SET AUTOCOMMIT = off; // Just in case.
...
while(input.next()) { // Process each line of the file.
...
cerr << "BEGIN WORK" << endl;
EXEC SQL BEGIN WORK;
...
[insert stuff]
[if error] {
cerr << "ROLLBACK WORK" << endl;
EXEC SQL ROLLBACK WORK;
continue;
}
...
[if no error] {
cerr << "COMMIT WORK" << endl;
EXEC SQL COMMIT WORK;
}
}
...
[dbBase destructor closes the connection to the postmaster]
I have shortened the program to run three iterations instead of the normal
30,000 or so, and I get this output:
BEGIN WORK
COMMIT WORK
BEGIN WORK
COMMIT WORK
BEGIN WORK
COMMIT WORK
and it inserts the three items; I can see them with psql.
The trouble is that the /src/dbms/dataB/pgsql/pg_xlog says this:
2007-08-18 07:26:28 EDT LOG: autovacuum: processing database "stock"
2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
2007-08-18 07:28:20 EDT LOG: autovacuum: processing database "stock"
The autovacuum is just the regular stuff. I put the timestamps into the
logfiles because it was otherwise too difficult to see what was what.
I restarted the postgres system (/etc/rc.d/init.d/postgres restart) in case
some leftover transaction was lying around -- though I am not sure this is
enough.
I cannot believe this is normal. Do incomplete transactions persist around a
shutdown and restart of postmaster? And if so, how do I clear the lost
transaction?
BTW, when I test this, I DELETE FROM all the tables, and reset all the
sequences with this kind of thing:
ALTER SEQUENCE company_company_id_seq
RESTART WITH 1;
before running the test program.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jerseyhttp://counter.li.org
^^-^^ 07:45:01 up 9 days, 11:07, 3 users, load average: 4.15, 4.21, 4.13
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] there is already a transaction in progress ?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jean-David Beyer wrote:
> It probably shows I am new to postgreSQL. I recently started running this
> instead of DB2, and am converting the applications I already wrote. These
> use ecpg.
>
> The problem I have concerns transactions. I have an application (the first
> one I am converting) that inserts a lot of stuff into three tables. (It is
> normalizing a .tsv file from a spreadsheet.) The program is in C++.
>
> The structure of the program is, I think,
> ...
> dbBase stock_database(STOCK_DB); // Constructor opens connection
> ...
> EXEC SQL SET AUTOCOMMIT = off; // Just in case.
> ...
> while(input.next()) {// Process each line of the file.
> ...
> cerr << "BEGIN WORK" << endl;
> EXEC SQL BEGIN WORK;
> ...
> [insert stuff]
> [if error] {
> cerr << "ROLLBACK WORK" << endl;
> EXEC SQL ROLLBACK WORK;
> continue;
> }
> ...
> [if no error] {
> cerr << "COMMIT WORK" << endl;
> EXEC SQL COMMIT WORK;
> }
> }
> ...
> [dbBase destructor closes the connection to the postmaster]
>
> I have shortened the program to run three iterations instead of the normal
> 30,000 or so, and I get this output:
>
> BEGIN WORK
> COMMIT WORK
> BEGIN WORK
> COMMIT WORK
> BEGIN WORK
> COMMIT WORK
>
> and it inserts the three items; I can see them with psql.
>
> The trouble is that the /src/dbms/dataB/pgsql/pg_xlog says this:
>
> 2007-08-18 07:26:28 EDT LOG: autovacuum: processing database "stock"
> 2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
> 2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
> 2007-08-18 07:27:20 EDT WARNING: there is already a transaction in progress
> 2007-08-18 07:28:20 EDT LOG: autovacuum: processing database "stock"
>
> The autovacuum is just the regular stuff. I put the timestamps into the
> logfiles because it was otherwise too difficult to see what was what.
>
> I restarted the postgres system (/etc/rc.d/init.d/postgres restart) in case
> some leftover transaction was lying around -- though I am not sure this is
> enough.
>
> I cannot believe this is normal. Do incomplete transactions persist around a
> shutdown and restart of postmaster? And if so, how do I clear the lost
> transaction?
>
> BTW, when I test this, I DELETE FROM all the tables, and reset all the
> sequences with this kind of thing:
>
> ALTER SEQUENCE company_company_id_seq
> RESTART WITH 1;
>
> before running the test program.
>
Sorry: false alarm.
Just outside of a loop I called a function that does a query on the
database. This silently begins a transaction, just as in DB2.
But I did not realize this in postgreSQL because I changed around the code a
bit to combine each iteration of the loop into a single transaction instead
of a bunch of smaller ones. Thus I implicitly began a transaction with the
one function call that I forgot did a query on the database and thus began a
transaction. (And NO, I do not want to be able to do nested transactions.)
- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jerseyhttp://counter.li.org
^^-^^ 11:55:01 up 9 days, 15:17, 6 users, load average: 4.04, 4.06, 4.07
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org
iD8DBQFGxyAaPtu2XpovyZoRAjb6AKCtP5urRKntgL8+k729hLy1PoUEvwCgv6XL
qmfWYjSYPWxsg5h/J1c1rIE=
=acAS
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Block size with pg_dump?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 When I make a backup of a database, I put the output file directly on magnetic tape; i.e., my command looks like this: pg_dump --file=/dev/st0 This way I do not have to worry if the total backup exceeds the size of a file system, and it saves me the trouble of copying it to the tape as a separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I enable hardware compression (assuming 2:1 compression happens). Now it says in the documentation that if I use format c it will compress the data in software, so I doubt the hardware compression will do much. I do not know what blocksize pg_dump uses, or if it insists on a particular blocksize on input. Now my tape drive will work with any blocksize, but prefers 65536-byte blocks. I do not see any options for this in pg_dump, but I could pipe the output of pg_dump through dd I suppose to make any blocksize I want. On the way back, likewise I could pipe the tape through dd before giving it to pg_restore. Does pg_dump care what blocksize it gets? If so, what is it? - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 17:20:01 up 17 days, 20:42, 5 users, load average: 5.12, 5.26, 5.21 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG0fITPtu2XpovyZoRAouwAKCTEour7jbi3uKWmEjerOM3U51xKQCeKYrQ 6jbamlqvTvH04jD7oRbTAKY= =piNw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Block size with pg_dump?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: > Jean-David Beyer wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> When I make a backup of a database, I put the output file directly on >> magnetic tape; i.e., my command looks like this: >> >> pg_dump --file=/dev/st0 >> >> This way I do not have to worry if the total backup exceeds the size of a >> file system, and it saves me the trouble of copying it to the tape as a >> separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I >> enable hardware compression (assuming 2:1 compression happens). Now it says >> in the documentation that if I use format c it will compress the data in >> software, so I doubt the hardware compression will do much. >> >> I do not know what blocksize pg_dump uses, or if it insists on a particular >> blocksize on input. >> >> Now my tape drive will work with any blocksize, but prefers 65536-byte >> blocks. I do not see any options for this in pg_dump, but I could pipe the >> output of pg_dump through dd I suppose to make any blocksize I want. >> >> On the way back, likewise I could pipe the tape through dd before giving it >> to pg_restore. >> >> Does pg_dump care what blocksize it gets? If so, what is it? > > I assume you could pipe pg_dump into dd and specify the block size in > dd. > Of course on the way out I can do that. The main question is, If I present pg_restore with a 65536-byte blocksize and it is expecting, e.g., 1024-bytes, will the rest of each block get skipped? I.e., do I have to use dd on the way back too? And if so, what should the blocksize be? - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 21:05:01 up 18 days, 27 min, 0 users, load average: 4.32, 4.12, 4.09 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG0iRlPtu2XpovyZoRAsXeAKCDuWnpDzTSEhvcBGjKXLO1oS2iAgCgrWB4 6Wj1bz9QoFOXrfL3galipDU= =pxyE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Block size with pg_dump?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: > Erik Jones wrote: >>>>> On the way back, likewise I could pipe the tape through dd before >>>>> giving it >>>>> to pg_restore. >>>>> >>>>> Does pg_dump care what blocksize it gets? If so, what is it? >>>> I assume you could pipe pg_dump into dd and specify the block size in >>>> dd. >>>> >>> Of course on the way out I can do that. >>> >>> The main question is, If I present pg_restore with a 65536-byte >>> blocksize >>> and it is expecting, e.g., 1024-bytes, will the rest of each block get >>> skipped? I.e., do I have to use dd on the way back too? And if so, >>> what >>> should the blocksize be? >> Postgres (by default) uses 8K blocks. > > That is true of the internal storage, but not of pg_dump's output > because it is using libpq to pull rows and output them in a stream, > meaning there is no blocking in pg_dumps output itself. > Is that true for both input and output (i.e., pg_restore and pg_dump)? I.e., can I use dd to write 65536-byte blocks to tape, and then do nothing on running pg_restore? I.e., that pg_restore will accept any block size I choose to offer it? - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:25:01 up 18 days, 11:47, 2 users, load average: 4.34, 4.31, 4.27 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG0sNpPtu2XpovyZoRAvVpAKCD0YPHpZVXwIweDwDfozA/79XJSACg0Jao qmFsnsJpy8209W8CGwhJ31Y= =u7p6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Block size with pg_dump?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: > Jean-David Beyer wrote: >>>>> The main question is, If I present pg_restore with a 65536-byte >>>>> blocksize >>>>> and it is expecting, e.g., 1024-bytes, will the rest of each block get >>>>> skipped? I.e., do I have to use dd on the way back too? And if so, >>>>> what >>>>> should the blocksize be? >>>> Postgres (by default) uses 8K blocks. >>> That is true of the internal storage, but not of pg_dump's output >>> because it is using libpq to pull rows and output them in a stream, >>> meaning there is no blocking in pg_dumps output itself. >>> >> Is that true for both input and output (i.e., pg_restore and pg_dump)? >> I.e., can I use dd to write 65536-byte blocks to tape, and then do nothing >> on running pg_restore? I.e., that pg_restore will accept any block size I >> choose to offer it? > > Yes. > Did not work at first: ... pg_dump: dumping contents of table vl_ranks 51448+2 records in 401+1 records out 26341760 bytes (26 MB) copied, 122.931 seconds, 214 kB/s So I suppose that worked. (This database just has some small initial tables loaded. The biggest one is still empty.) But then trillian:postgres[~]$ ./restore.db pg_restore: [archiver] did not find magic string in file header trillian:postgres[~]$ I fixed it by changing my backup script as follows: $ cat backup.db #!/bin/bash # # This is to backup the postgreSQL database, stock. # DD=/bin/dd DD_OPTIONS="obs=65536 of=/dev/st0" MT=/bin_mt MT_OPTIONS="-f /dev/st0 setblk 0" PG_OPTIONS="--format=c --username=postgres --verbose" PG_DUMP=/usr/bin/pg_dump $PG_DUMP $PG_OPTIONS stock | $DD $DD_OPTIONS and it still would not restore until I changed the restore script to this: $ cat restore.db #!/bin/bash # This is to restore database stock. FILENAME=/dev/st0 DD=/bin/dd DD_OPTIONS="ibs=65536 if=$FILENAME" MT=/bin/mt MT_OPTIONS="-f $FILENAME setblk 0" PG_OPTIONS="--clean --dbname=stock --format=c --username=postgres --verbose" PG_RESTORE=/usr/bin/pg_restore $MT $MT_OPTIONS $DD $DD_OPTIONS | $PG_RESTORE $PG_OPTIONS It appears that I must read in the same blocksize as I wrote. My normal backup program (BRU) can infer the blocksize from the first record, but apparently pg_restore does not. But dd will read it if I tell it the size. Hence the above. The MT stuff is to tell the tape driver to accept variable block size so the program that opens it can set it. DD can do that, but I infer that pg_restore does not. - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 11:00:01 up 18 days, 14:22, 3 users, load average: 5.54, 4.84, 4.45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG0vQuPtu2XpovyZoRAlwcAKC5ApaGOoZrnHDUa5vgg9tx4jrqjwCeLfLV oPLB1xCbJ0/WLYrg5/qVs2g= =BkQ6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to use serial variable to insert into muiti-recrods?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hu js wrote: > run: > "CREATE TABLE xxx ( id serial NOT NULL, name character varying ); > insert into xxx select default values,place_name from air_bui;" > > fail: "ERROR: syntax error at or near "default" SQL state: 42601 > Character: 24" > > How can I do? > It is not clear what you are trying to do. Your Subject line does not quite agree with the text. 1.) Are you trying to use the same serial number in multiple records (tuples)? That is what I would infer from your Subject line. If so, and if id is the primary key, then those multiple tuples better in different tables (relations). 2.) Are you trying to use a different serial number in each tuple you insert? That is what your example seems to show. In either case, I assume there is more than one column (attribute) in each tuple. So in case 2 you might wish to declare and operate thus: CREATE TABLE xxx id serial NOT NULL, namecharacter varying ); INSERT INTO xxx (name) SELECT place_name FROM air_bui WHERE ... ; If you are trying to use the same serial number in multiple records, then you should get the serial number direct from the SEQUENCE and plug it in each tuple as you need it. - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 07:20:01 up 28 days, 10:42, 4 users, load average: 5.23, 5.18, 4.78 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFG3+hkPtu2XpovyZoRAvYaAJ0VRP5u3BXhihtoM60PPeh819hjGgCbB4j8 99RzX9EobFUU4u7d9qk2QKI= =YcQX -END PGP SIGNATURE- ---(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
[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?
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?
Tom Lane wrote: > 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. I guess so. I _am_ impressed by how much compression is achieved. > 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. I do not get that. If the physical tape speed is the bottleneck, why is it only about 242 kB/s in the software-compressed case, and 4.2 MB/s in the hardware-uncompressed case? The tape drive usually gives over 6 MB/s rates when running a BRU (similar to find > cpio) when doing a backup of the rest of my system (where not all the files compress very much)? Also, when doing a BRU backup, the amount of cpu time is well under 100%. If I am right, the postgres server is running 100% of the CPU and the client (pg_dump) is the one that actually compresses (if it is enabled in software) is either 40% or 12%. > > 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. Agreed. The times for backup (and restore) are acceptable. Being new to postgres, I am just interested in how it works from a user's point-of-view. > 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. Sure, I always have something to do with the excess cycles, though it is not an obsession of mine. But from intellectual curiousity, why is the postgres _server_ taking 100% of a cpu when doing a backup when it is the postgres _client_ that is actually running the tape drive -- especially if it is tape IO limited? -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 07:40:01 up 24 days, 58 min, 0 users, load average: 4.30, 4.29, 4.21 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is there anything special about pg_dump's compression?
Shane Ambler wrote: > Jean-David Beyer wrote: >>> 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. >> >> I do not get that. If the physical tape speed is the bottleneck, why is >> it only about 242 kB/s in the software-compressed case, and 4.2 MB/s in >> the hardware-uncompressed case? The tape drive usually gives over 6 >> MB/s rates when running a BRU (similar to find > cpio) when doing a >> backup of the rest > > It would really depend on where the speed measurement comes from and how > they are calculated. Is it data going to the drive controller or is it > data going to tape? Is it the uncompressed size of data going to tape? I imagine it is the speed measured by the CPU of data going to the (Linux) operating system's write() calls. > > My guess is that it is calculated as the uncompressed size going to tape. > In the two examples you give similar times for the same original > uncompressed data. True. But that tells me that it is the CPU that is the limiting factor. In other words, if I send compressed data, it sends 30 Megabytes in about the same time that if I send uncompressed data (for the tape drive hardware to compress -- the SCSI controller driving the tape drive sure does not compress anything much). I originally started this thread because I wanted to know if the compression in pg_dump was anything special, and I was told that it was probably not. And this seems to be the case as it takes about the same amount of time do dump the database whether I compress it in pg_dump or in the tape drive. But then it seemed, and still seems to me, that instead of being limited by the tape speed, it is limited by the CPU speed of the CPU running the postgres server -- and that confuses me, since intuitively it is not doing much. > > I would say that both methods send 30MB to tape which takes around 124 > seconds You are right about this. In other words, the time to send the data to the tape drive, whether it is 30 Megabytes (compressed by the program) or 524 megabytes (compressed by the drive) will put down about the same number of bytes onto the tape. I.e., the tape head sees (about) the same number of bytes either way. This means the transmission speed of the SCSI controller is certainly fast enough to handle what is going on (though I do not think there was any questioning of that). But since the tape drive can take 6 uncompressed megabytes per second (and it does -- this is not advertizing hype: I get that when doing normal backups of my system), and is getting only 4.3, that means the bottleneck is _before_ the SCSI controller. Here is an typical example. Bru does a backup of my entire system (except for the postgres stuff), rewinds the tape, and reads it all back in, verifying the checksum of every block on the tape. It does not (although it could) do any compression. bru: execution summary Started:Wed Nov 14 01:04:16 2007 Completed: Wed Nov 14 02:02:56 2007 Archive id: 473a8fe017a4 Messages: 0 warnings, 0 errors Archive I/O:5588128 blocks (11176256Kb) written Archive I/O:5588128 blocks (11176256Kb) read Files written: 202527 files (170332 regular, 32195 other) So we wrote 11.176Gb, rewound the tape, and then read 11.176Gb and then rewound the tape again in about an hour. Ignoring rewind times, this would say it wrote or read 6.2 uncompressed megabytes/second. It would be a little faster if we consider that the rewind times are not really important in this discussion. This is the rate of stuff going to the interface. This just shows that the 6 Megabytes/second claimed by the manufacturer is realistic -- that you actually get this in a real application. Now what is on this machine? A lot of binary program files that probably do not compress much. Quite a bunch of .jpeg files that are already compressed, so they probably do not compress much. Some .mp3 files: I do not know how much they compress. Program source files (but not lots of them). _Lots_ of files that have been zipped, so they probably do not compress much; 1,347,184 blocks worth of that stuff. > > The first example states 4.2MB/s - calculated from the uncompressed size > of 524MB, yet the drive compresses that to 30MB which is written to tape. > So it is saying it got 524MB and saved it to tape in 125 seconds > (4.2MB/s), but it still only put 30MB on the tape. > > 524MB/125 seconds = 4.192MB per second > > The second example states 242KB/s - calculated from the size sent to the > drive - as the data the drive gets is compressed it can't compress it any > smaller - the data received is the same size as the data written to ta
Re: [SQL] Check before INSERT INTO
Shavonne Marietta Wijesinghe wrote: > Thanks for the reply Grogory. I am trying to do a INSERT INTO. > > Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key > (Serial not null) That is sure confusing. What could a DDL saying INTEGER n_gen SERIAL NOT NULL; INTEGER n_sheet SERIAL NOT NULL; INTEGER tot_n_sheet SERIAL NOT NULL; PRIMARY KEY (n_gen, n_sheet, tot_n_sheet) mean? Is this what your DDL says? Do you enter rows of this table specifying the id and expecting the three serial generators to pick non-null sequential numbers for the other three fields? I think you are very unclear about what is going on here. Are you perhaps saying the table has three (distinct) primary keys? Because if you assign them values, why would they be SERIAL? > id | n_gen | n_sheet | tot_n_sheet > --+---+---+- > a| 1| 1 | 1 > b| 2| 1 | 2 > x| 2| 2 | 2 > u| 3| 1 | 1 > r| 4| 1 | 3 > a| 4| 2 | 3 > s| 4| 3 | 3 > > > So there are 2 users inserting in to the db. In my ASP page i have a > field that shows the value of n_gen +1. So when the 2 users both login > at the same time, with different sessions, they both see "7" in the > n_gen field. But when they click on the sumbit button only one record is > inserted and the other is lost. Whatever you are doing that I do not understand with your keys, if you have two users doing inserts on the same table, would you not have to run this with Serializable Isolation Level (12.2.2. in the manual)? Would this not fix your problem especially if you have a SERIAL as primary key? > > I though it was possible to change the SQL string before it does the > update.. But i can't seem to find a solution for it.. Any idea ?? > -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 11:35:01 up 18 days, 1:21, 1 user, load average: 4.22, 4.28, 4.27 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to find double entries
Andreas wrote: > Hi, > > how can I find double entries in varchar columns where the content is not > 100% identical because of a spelling error or the person considered it > "looked nicer" that way? > > I'd like to identify and then merge records of e.g. 'google', 'gogle', > 'guugle' Then I want to match abbrevations like 'A-Company Ltd.', 'a > company ltd.', 'A-Company Limited' > > Is there a way to do this? It would be OK just to list candidats up to be > manually checked afterwards. > > This is really tough, whether you use postgreSQL or not. I once worked for a large regulated monopoly who had to look up stuff in telephone books a lot. Several of us got a magnetic tape with all the business, professional, and government listings for a county on Long Island in it; we thought residential would be too easy and did not have the disk space for it (in those days, hard drives cost $40,000 and held 40 Megabytes). One of the things we did was do leading substring partial matching. I.e., we could look for "J Smith" and find "Smith, John Robert"; we could find him with "Rob Smit" as well. This helped because people did not put their names in the right order. Sometimes they said "Smith, John" and other times they said "John Smith" or "J Smith" and meant the same guy. Sometimes they said "White St" or "White Street" when they meant "White Road". And so it went. Sometimes they spelled her name "Jeannine" when she spelled it "Genine". So the question always ended up being what did they really mean. To make matters worse, someone had run a program over the data to spell out abbreviations, but that generated "42 Saint" instead of "42 Street" and problems like that. Also, if a field was too big, the data-entry clerks just kept on typing into the next field, so a lot of entries had, as an address, "If no entry call" I stuck in a phonetic matcher (similar to Soundex coding) so that a query for "Ristorante Italiano" would find "Mom's Pizza Italian Restaurant". It would also find Genine whey you were looking for Jeannine. People often got the towns wrong. Around here, there is a town on the map, but the telephone company had that in another town, and the tax collector had it in yet another town. So towns were weighted lower than names. For government listings, there was a separate record for each line in the telephone book, so you would get entries like this, each line a separate record: U S Government Federal Aviations Administration Kennedy Airport Pilot Information Arrivals Departures We had to make it find "Pilot Arrivals" so indexing was not trivial until you figured out how to do it. But when all was said and done, we put a program on the output that displayed answers in terms of decreasing goodness of match and stuck the users with deciding what they wanted. A big trick was to do all this without doing a sequential search of the database. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 21:30:01 up 33 days, 2:32, 1 user, load average: 4.06, 4.07, 4.11 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Curious about wide tables.
In another thread, the O.P. had a question about a large table with over 100 columns. Is this usual? Whenever I make a database, which is not often, it ends up with tables that rarely have over to columns, and usually less than that. When normalized, my tables rarely get very wide. Without criticising the O.P., since I know nothing about his application, I am curious how it comes about that such a wide table is justified. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:55:01 up 40 days, 13:57, 2 users, load average: 4.32, 4.27, 4.18 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Curious about wide tables.
Shane Ambler wrote: > Jean-David Beyer wrote: >> In another thread, the O.P. had a question about a large table with >> over 100 columns. Is this usual? Whenever I make a database, which is >> not often, it ends up with tables that rarely have over to columns, and >> usually less than that. When normalized, my tables rarely get very >> wide. >> >> Without criticising the O.P., since I know nothing about his >> application, I am curious how it comes about that such a wide table is >> justified. >> > > Depends on the application. > > Something like drivers license db will have a few things like name, > address, type, dob, restrictions and end date > > Then something like an insurance policy where each record needs to know > who it is for, the item(car - rego make model... house - address suburb > state), effective date, end date, date of inception, type of cover, value > of cover, excess amount, base premium, agent fees, gov fees, total > premium, invoice sent, who entered it and when.. > > Sometimes you can have a lot of data that makes up one instance. > I guess it depends on the application and its use. I guess I _could_ normalize that insurance policy database to where there would be lots of tables with few fields. E.g., Policy Number, Owner Policy Number, make Policy Number, house address Policy Number, State ... And that would make sense _if_ there were lots of queries such as "How many Oldsmobiles are there?" or even "How many Fords are in Indiana?" But that would be carrying normalization too far if the typical query is something like "Print out everything about policy number xxx., or "Raise all rates in Indiana for Chryslers that expire in August by yy%" So I guess it would depend on what the typical "query" is. On the one hand, I like to normalize things a lot. But on the other hand, data to be retrieved together should be stored together. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 10:15:01 up 43 days, 15:17, 2 users, load average: 4.20, 4.20, 4.21 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
TJ O'Donnell wrote:
> I use a c function, nbits_set that will do what you need.
> I've posted the code in this email.
>
> TJ O'Donnell
> http://www.gnova.com
>
> #include "postgres.h"
> #include "utils/varbit.h"
>
> Datum nbits_set(PG_FUNCTION_ARGS);
> PG_FUNCTION_INFO_V1(nbits_set);
> Datum
> nbits_set(PG_FUNCTION_ARGS)
> {
> /* how many bits are set in a bitstring? */
>
> VarBit *a = PG_GETARG_VARBIT_P(0);
> int n=0;
> int i;
> unsigned char *ap = VARBITS(a);
> unsigned char aval;
> for (i=0; i < VARBITBYTES(a); ++i) {
> aval = *ap; ++ap;
> if (aval == 0) continue;
> if (aval & 1) ++n;
> if (aval & 2) ++n;
> if (aval & 4) ++n;
> if (aval & 8) ++n;
> if (aval & 16) ++n;
> if (aval & 32) ++n;
> if (aval & 64) ++n;
> if (aval & 128) ++n;
> }
> PG_RETURN_INT32(n);
> }
>
>
>
>> Hi all,
>> Am looking for a fast and efficient way to count the number of bits set
>> (to 1) in a VARBIT field. I am currently using
>> "LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g'))".
>>
>> Allan.
>
>
When I had to do that, in days with smaller amounts of RAM, but very long
bit-vectors, I used a faster function sort-of like this:
static char table[256] = {
0,1,1,2,1,2,2,3,1,.
};
Then like above, but instead of the loop,
n+= table[aval];
You get the idea.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jerseyhttp://counter.li.org
^^-^^ 20:20:01 up 7 days, 1:08, 4 users, load average: 4.16, 4.15, 4.10
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] subtract two dates to get the number of days
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Campbell, Lance wrote: > I want to subtract to dates to know the number of days different. > > > > Example: > > 01/02/2010 - 01/01/2010 = 1 day > > 08/01/2010 - 07/31/2010 = 1 day > > > > How do I do this? > Others have posted SQL answers to this. Which is fine if you need to do it in SQL. My dates are of the form -mm-dd and such. And I want to do things like adding or subtracting days, months, or years to it or from it. Also the logical comparisons. Years ago, I made a C++ data type that allowed a date datatype where I could add, subtract, and so on. I use it in programs that do not necessarily use a database, but also in programs that do when the computations are the big part of the cpu load, as contrasted to just "gentle" massaging of existing data. - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 08:20:01 up 6 days, 17:06, 4 users, load average: 4.77, 4.78, 4.87 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org/ iD8DBQFMPa9yPtu2XpovyZoRAnuDAJ9U9yghDl8NkGNv1pWSxIwXsDBTXwCgiv1L INK1dzbUQnWBjhXXrQu6ZsM= =lyR9 -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] subtract two dates to get the number of days
Thomas Kellerer wrote: > Jean-David Beyer wrote on 14.07.2010 14:37: >> My dates are of the form -mm-dd and such. > Storing a date as a string is never a good idea. I started this long ago, when postgreSQL did not really work very well (1998?). One version of it would not do views, and another had trouble with primary keys, IIRC. So I first used Informix, until it would not work any more. It did not really support embedded SQL in C++, only in C, so that was a pain. But it quit working when Red Hat updated from release 5 to release 6. I then moved to IBM's DB2, and that worked very well, but it got too expensive to keep it when I went from one release of my OS to another for just my own workstation use. Somewhere around 2004, or a little before, I decided to give postgreSQL another chance, and it works just fine. I just looked them up in my data definitions. Dates are _stored_ as type DATE NOT NULL so I store them OK. It is just when I want to compute with them that it gets a bit tricky. Or it did way back when I wrote that stuff in the late 1990s. > >> And I want to do things like adding or subtracting days, months, or years to >> it or from it. >> Also the logical comparisons. > Which is all a piece of cake when you use the proper datatype Yes, if the data happen to be stored at all. But when a program generates the dates dynamically and wants to produce queries from them, it is easier to use the C++ class to generate the dates. > >> Years ago, I made a C++ data type that allowed a date datatype where I >> could add, subtract, and so on. >> I use it in programs that do not necessarily use a database, > To be honest: I expect the programming language to support those things. I would love it. For all I know, the C++ Standard Library supports it now, but I do not believe it did when I wrote that class. > >> but also in programs that do when the computations are the big part of the >> cpu load, >> as contrasted to just "gentle" massaging of existing data. > I would expect doing "date maths" with strings is wasting more CPU than using > a native date datatype. My class pretty much does not do it as strings, but as integers (internally) > > Just my €0.02 > Thomas > Well, €0.02 is still more than my US$0.02, I believe. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 12:45:01 up 6 days, 21:31, 4 users, load average: 4.65, 4.69, 4.71 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Database consistency after a power shortage
Samuel Gendler wrote: > > > On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe <mailto:scott.marl...@gmail.com>> wrote: > > On Wed, Dec 15, 2010 at 8:12 AM, Alberto <mailto:blob2...@gmail.com>> wrote: > > > > > Is there any way to make the 3 operations be one transaction for the > > database, so that it keeps them all consistent in case a power > shortage > > occurs in the middle? > > Yes, put them in a transaction. > > begin; > insert into head_invoice ... > insert into detail_invocie ... > insert into payments_x_header_invoice ... > commit; > > Then they either all go or none go. > > > But if the database transaction concept is new to you, I highly > recommend you do a little reading about database transactions in general > and postgres' implementation specifics as well. It can be very easy for > you to make mistakes that can cause the database to get slow or use up a > lot of disk if you use transactions without understanding at least a > little of what is happening in the database while the transaction is > open but uncommitted. > > Incidentally, any error on a query within the transaction will cause the > transaction to automatically 'rollback' when the transaction completes, > undoing all of the changes, or you can manually cancel a transaction by > issuing a 'rollback;' statement instead of 'commit;' at the end. > > You can also (or more appropriately, in addition) equip your system with an uninterruptable power supply with enough capacity to coast over the power shortage interval, or to perform a controlled shutdown. I do not know how long it takes to do such a shutdown with postgreSQL, but it could involve stopping all new transactions from entering the system, and allowing those in process to complete. A UPS to allow 10 minutes of run-time is not normally considered too expensive. Mine will run for about an hour with new batteries, but after a few years it dwindles to about 1/2 hour. Then I get new ones. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 09:30:01 up 14 days, 23:16, 4 users, load average: 5.61, 4.98, 4.89 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
