Re: [GENERAL]COPY still running
Hello i start yesterday afternoon a COPY command into a table. the file is 15 Mb i've set triggers before and after insert on the table the before trigger just perform data check. the after trigger update a 10 000 rows linked table. the COPY command runs all the night ad has not yet finished... what is the problem PostgreSQL 6.5.2 running on Pentium II debian server use 98% CPU... I had a similar thing once, while compiling a program, it took the computer 28 hours to complete. (on a P200) My best guess is is that it's still running, make a quick check that the main process is still running. The action you are doing requires not just alot of CPU-power, but also a lot of HD-read and writes. Another check you could do is to see if the database itself is growing, to do this, look if the total disk-usage of the database is growing, if it is, then you just gave a whole lot of work-load to the server. If either of these 2 checks come up negative, then you crashed the server, however my experience with PostgreSQL is, is that when it crashes, CPU-usage drops, not rises with kind regards, Joost Roeleveld
[GENERAL] upgrade postgreSQL
I got the file .patch and tried to upgrade postgres sous FreeBSD with the command patch from the version 6.5.2 to the version 6.5.3 but it doesn't work. Could somebody explain to me how to upgrade postgres on my server. Thank's for your help __ Matthias Zehnder - Informatique E-mail: [EMAIL PROTECTED] __ M C net MC Management Communications SA A VIA NET.WORKS COMPANY Rue de Romont 35, CH-1700 Fribourg Tél.: ++41 (0)26 347 20 40, fax: ++41 (0)26 347 20 49 E-mail: [EMAIL PROTECTED], http://www.mcnet.ch
Re: [GENERAL]COPY still running
I had a similar thing once, while compiling a program, it took the computer 28 hours to complete. (on a P200) My best guess is is that it's still running, make a quick check that the main process is still running. The action you are doing requires not just alot of CPU-power, but also a lot of HD-read and writes. the postmaster is still running and the database grows (it is not very fast but it grows and is now 60Mb large..) glad to hear, it's not crashed... :) is thare a way to stop the copy command wihtout crashing the database? It depends, if you issued the command within 'psql' then yes, by pressing ctrl+C however, this will cause the entire transaction to be reversed, and will very likely take the same amount of time, my advise would still be to let this command to finish normally... with kind regards, Joost Roeleveld
Re: [GENERAL] Re: server hardware recommendations
Marc Tardif wrote: Now I'm back to square one, looking for a storage solution for my postgresql db. More specifically, something scallable and very fast. In my ... of hardware raid out there. But the question remains, how can all this be scallable, ie how can I expand on existing storage space. Well, Linux and *BSD are still lacking robust implementations of logical volumes. Things probably will change in the near future, as SGI is already porting XFS to Linux. With SGI moving towards a Linux platform for its low-end servers, XLV (the logical volumes layered on top of XFS) are bound to arrive there as well, sooner or later. Sevo -- Sevo Stille [EMAIL PROTECTED]
[GENERAL] pqReadData() error
Hi, during execution of block of commands like BEGIN INSERT INTO... INSERT INTO... . INSERT INTO... COMMIT sometimes receive the error pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. 3 last lines in postmaster's log are: ERROR: Cannot insert a duplicate key into a unique index ERROR: Cannot insert a duplicate key into a unique index FATAL 1: my bits moved right off the end of the world! Between BEGIN-COMMIT the program (written in C) makes 100 to 5 INSERTs. In the database I have two tables, say t1 and t2. Table t1 has an unique index and two first lines from the error-messages above are when the program tryis to make INSERT duplicate rows. But the error pqReadData() I recieve when try to insert into the second table t2. It hasn't the unique index. My configurations is: postgresql 6.5.3 , RedHat 6.1 on Intel-based server, RAM 256 Mb, swap 256 Mb, HDD 2x9Gb. Best regards, Alex
Re: [GENERAL]COPY still running
Arnaud FLORENT writes: Hello i start yesterday afternoon a COPY command into a table. the file is 15 Mb i've set triggers before and after insert on the table the before trigger just perform data check. the after trigger update a 10 000 rows linked table. the COPY command runs all the night ad has not yet finished... what is the problem You probably have indexes defined on this table. You should drop all the indexes before the copy and create them after the copy if the number of rows in the copy commands exceeds the number of the current rows in the table. I have dropped the indexes while copy'ing which seems to be no problem. Just create them after the copy is finished. Ymmv... Dirk
Re: [GENERAL] Can || be used in ORDER BY?
create table tablename ( field1 text, field2 text);
CREATE
insert into tablename values('bottom','yes');
INSERT 2282464 1
insert into tablename values('top','no');
INSERT 2282465 1
select field1,field2 from tablename order by (field1||'-top');
field1|field2
--+--
bottom|yes
top |no
(2 rows)
Jos
Jeremy Malcolm wrote:
-BEGIN PGP SIGNED MESSAGE-
I would like to do this:
select
field1,field2
from tablename
order by (field1||'-top')
ie. order the records by the contents of field1 with the text "-top"
concatenated to it. It doesn't work, I get a parse error.
Can anyone offer advice?
Thanks.
- --
JEREMY MALCOLM [EMAIL PROTECTED] http://malcolm.wattle.id.au
SIG of the day: [ ] Contact [ ] Web [ ] PGP [ ] Taglines
#1 [x] #2
"I'm a lawyer." "Honest?" "No, the usual kind." | Linux, the choice
of
a GNU generation. | Are you the brain specialist? | "Could anyone pass
the sodium chloride, please?" - Adric (5W) | The Nanites have lawyers?
-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.0.2i
iQB1AwUBOI3a6L/mBljD2JABAQG3RQL8DxUkukKGm7jEa9rSgyFzXMcd5KJejRxU
abscA8SuVq7ENXdFncx+5OsAk0VQfzBkUkRlobD9LEFXm6aTsK2zqmlhdVsJFKyh
f/YOixdaGiNzE+9xfIpEz+iizzKBwPRy
=bED8
-END PGP SIGNATURE-
RE: [GENERAL] Can || be used in ORDER BY?
-BEGIN PGP SIGNED MESSAGE- Hmm well it definitely doesn't work for me, so I guess I need to upgrade from version 6.3.2 in order for it to work (unless there's some other workaround). Thanks... - -- JEREMY MALCOLM [EMAIL PROTECTED] http://malcolm.wattle.id.au SIG of the day: [x] Contact [ ] Web [ ] PGP [ ] Taglines #1 [ ] #2 Residence: 208/112 Mounts Bay Road, West Perth, Western Australia 6005 Phone: +61-8-9226 0689 (H), +61-8-9325 4400 (W) | Fax: +61-8-9421 1762 Mobile: 0419 911 079 | Email: [EMAIL PROTECTED], [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQB1AwUBOI6Hmr/mBljD2JABAQFnTgMArXpc2rom84IwGdtNVs4K/yeGTbhOjRjp JC20qoZnAnkusR8BSfrZ7cUSJGnEKvAfyI7hQDXxLVXFsWuSgRhsdnJgtLjKMcJV xXrsg+nK5hBw0opkFXjpX2Fl2XtLiknD =6/MF -END PGP SIGNATURE-
Re: [GENERAL] what is view?
A view is a table with a rule SELECT For excample if you have a table named my_table and you create a rule like: CREATE RULE "_RETmy_table" AS ON SELECT TO "my_table" DO INSTEAD SELECT * FROM your_table; In this way you your table my_table became a view. José Marc Tardif wrote: When listing my tables and indices in psql, I see a "view?" in type. What is this type? Where can I read about it in the manual? The table listed as "view?" use to be listed as "table" but suddently changed when I added rules. Do rules make a table a view? Marc
Re: [GENERAL] upgrade postgreSQL
Under FreeBSD, you should be using the ports. In this case, you could simply use the following commands in the postgresql port directory: make pkg_delete postgresql-6.5.2 make install Though the procedure was simple, there was a little gotcha. You need to: psql template1 select oid,* from pg_database; remove one of the two template1 tables by using the oid. Good luck, Marc On Wed, 26 Jan 2000, Matthias Zehnder wrote: I got the file .patch and tried to upgrade postgres sous FreeBSD with the command patch from the version 6.5.2 to the version 6.5.3 but it doesn't work. Could somebody explain to me how to upgrade postgres on my server. Thank's for your help __ Matthias Zehnder - Informatique E-mail: [EMAIL PROTECTED] __ M C net MC Management Communications SA A VIA NET.WORKS COMPANY Rue de Romont 35, CH-1700 Fribourg Tél.: ++41 (0)26 347 20 40, fax: ++41 (0)26 347 20 49 E-mail: [EMAIL PROTECTED], http://www.mcnet.ch
Re: [GENERAL] how to use pg-connect ?
I would like more info about the pgtcl commands, too. In the "Integrated Document" on the postgres web page, there's a list of commands (chapter 49), but not detailed information. When I enter pgtclsh, I can get a list of options. For pg_connect, I get this: % pg_connect pg_connect: database name missing pg_connect databaseName [-host hostName] [-port portNumber] [-tty pgtty]] pg_connect -conninfo conninfo-string This is fairly intuitive, but the pg_result command isn't as obvious: % pg_result Wrong # of arguments pg_result result ?option? where option is -status -error -conn -oid -numTuples -numAttrs -assign arrayVarName -assignbyidx arrayVarName ?appendstr? -getTuple tupleNumber -tupleArray tupleNumber arrayVarName -attributes -lAttributes -clear Is there a description of these options in one of the other documents? Sarah Officer [EMAIL PROTECTED] Cécile DESNOYERS wrote: Hello, I'd like to know if there is a way to use pg_connect() with a different host than « localhost ». I tried to put an IP address or a server name but it didn't work. Has anybody ever used pg_connect with something else than localhost ? Thank you Cecile
Re: [GENERAL] how to use pg-connect ?
Dear Cecile, You can do this with a connection string: pg_connect -conninfo $string ...or directly, dos/unix style, as in pg_connect $database -host $host -port $portNumber Cheers Rob C écile DESNOYERS [EMAIL PROTECTED] on 01/26/2000 12:48:14 PM To: [EMAIL PROTECTED] cc:(bcc: Robert Wagner/SIAC) Subject: [GENERAL] how to use pg-connect ? Hello, I'd like to know if there is a way to use pg_connect() with a different host than « localhost ». I tried to put an IP address or a server name but it didn't work. Has anybody ever used pg_connect with something else than localhost ? Thank you Cecile
Re: [GENERAL] what is view?
On 2000-01-25, Marc Tardif mentioned: When listing my tables and indices in psql, I see a "view?" in type. What is this type? Where can I read about it in the manual? CREATE VIEW The table listed as "view?" use to be listed as "table" but suddently changed when I added rules. Do rules make a table a view? The behaviour you observed is a bug. (But views still do exists, see above.) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[GENERAL] Backup, Vacuume scheduling tips?
I would like to know if anyone has tips on scheduling daily backups of PostgreSQL databases, scheduling daily vacuum's, or any other ongoing maintenance hints about PostgreSQL. The documentation seems fairly thin in this area, and I am sure a lot of people would benefit by this being discussed. Do most people schedule a nightly vacuum, then a pg_dump (or pg_dumpall)? Any issues with doing a compressed dump? Thanks, Bruce
[GENERAL] Creating groups and granting privs to it
Another thing that I cannot seem to figure out how to do is this, and I would appreciate any help: How does one create a GROUP, add users to it, and grant access to the group? CREATE USER doesn't seem to understand "IN GROUP ~" nor does GRANT seem to understand "TO GROUP ~". Am I doing something wrong? Thanks, Don Dade __ Get Your Private, Free Email at http://www.hotmail.com
[GENERAL] Format of PgDatabase::PgDatabase(char *conn)
Hello all, I am using libpq++, and I cannot find the format for the connect string passed as parameter to the PgDatabase constructor. Right now, I am forced to use environment variables. Can anyone educate me? Thanks, Don Dade __ Get Your Private, Free Email at http://www.hotmail.com
[GENERAL] psql bug?
I am running postgres 6.5.3 on an IRIX6.5. In psql, I can't describe any table I create. I am able to select contents. Is this a bug? psql mydb mydb= \d Database= bcams +--+--+--+ | Owner | Relation | Type | +--+--+--+ | dusty| sites| table| | dusty| sites_pkey | index| +--+--+--+ mydb= \d sites ERROR: typeidTypeRelid: Invalid type - oid = 0 mydb= select * from sites mydb- ; id|site_name |first| second|category_code --++-+-+- -1|TRI-CITY AIRPORT|44.111000|18.111000|1 -2|USED CAR LOT|44.222000|18.222000|1 -3|BIG CITY MALL |44.333000|18.333000|2 -4|TOWN DUMP |44.444000|18.444000|1 -5|VILLAGE PARK NE |44.555000|18.555000|1 (5 rows)
Re: [GENERAL] scaling
"Scott V. McGuire" wrote: I'm surprised about how long certain things are taking... What I'm surprised about is that the 10,000 row copies take 1 minute while the 5 row copies take 10 minutes. See http://www.deja.com/getdoc.xp?AN=563958392 I suspect vacuum also would have a dramatic impact on performance in your test case. Cheers, Ed Loehr
[GENERAL] too many open files by postgresql backends
This is really an OS question, but I'm posting here as it is caused by pgsql backends and I suspect some of you have already dealt with it... My combination of apache and postgresql is quickly exceeding the maximum number of open files (4096) under linux RH6.1. In digging around for how to up that number, I did find this article: http://www.redhat.com/mirrors/LDP/LDP/LG/issue37/tag/4.html It seems to suggest that you can up the maximum number of files at any time by doing the following: root# echo 1 /proc/sys/fs/file-max Could this possibly be true? I was expecting to have to recompile the kernel, but would be oh-so-pleased if it were that easy. Thanks in advance... Cheers, Ed Loehr
Re: [GENERAL] scaling
On Wed, Jan 26, 2000 at 04:51:26PM -0600, Ed Loehr wrote: See http://www.deja.com/getdoc.xp?AN=563958392 I suspect vacuum also would have a dramatic impact on performance in your test case. Cheers, Ed Loehr Ok, well there's at least two things I should have said. I am running with -F and I did a vacuum analyze last night. Does vacuum analyze do the analyze stuff in addition to or instead of the plain vacuum stuff? -- Scott V. McGuire [EMAIL PROTECTED] GnuPG key available at http://physics.syr.edu/~svmcguir GnuPG key fingerprint: 21EA 4999 3620 3E1D 71EC 98A9 5B9B EF52 1258 6D53 GnuPG is at http://www.gnupg.org/
[GENERAL] reverse sorting
I need to sort the domains of email addresses from right to left, so that all similar domains are grouped together. For instance, all .ca's will be in the same batch. I think I should be writing a trigger for this, using a secondary table for the reverse domain name, then using a query like: select email from table1, table2 where table1.oid=table2.id order by table2.reverse_domain; I'm not sure if keeping a secondary table is worthwhile, I will only be executing this query about 50 times per day. Let me know if there's a better way, Marc Tardif
RE: [GENERAL] too many open files by postgresql backends
This is really an OS question, but I'm posting here as it is caused by pgsql backends and I suspect some of you have already dealt with it... My combination of apache and postgresql is quickly exceeding the maximum number of open files (4096) under linux RH6.1. In digging around for how to up that number, I did find this article: http://www.redhat.com/mirrors/LDP/LDP/LG/issue37/tag/4.html It seems to suggest that you can up the maximum number of files at any time by doing the following: root# echo 1 /proc/sys/fs/file-max Could this possibly be true? I was expecting to have to recompile the kernel, but would be oh-so-pleased if it were that easy. From memory you need to recompile the kernel to increase the number of 'file descriptors' as oppose to the number of files opened. What's the diff? No idea :) Unless of course they've moved this out of the kernel into proc which I doubt. Have a poke around for info on file descriptors. Cheers, Graeme
Re: [GENERAL] scaling
"Scott V. McGuire" wrote: See http://www.deja.com/getdoc.xp?AN=563958392 I suspect vacuum also would have a dramatic impact on performance in your test case. Ok, well there's at least two things I should have said. I am running with -F and I did a vacuum analyze last night. Does vacuum analyze do the analyze stuff in addition to or instead of the plain vacuum stuff? In addition, I believe. It also occurred to me that it probably doesn't really matter how much *time* has passed since your last vacuum, but rather how much *activity* (ie., inserts/deletes) has occurred since you last vacuumed... Cheers, Ed Loehr
[GENERAL] ERROR: heap_delete: (am)invalid tid
I've been trying to see the limit for entering data in a text field using the following commands: copy test_table from '/my/file'; delete from test_table; Suddenly, after a successful delte, I got: ERROR: heap_delete: (am)invalid tid What does this mean? Is it a bug or have I done something wrong? Marc Tardif
RE: [GENERAL] how to use pg-connect ?
Dear Cecile, You can do this with a connection string: pg_connect -conninfo $string ...or directly, dos/unix style, as in pg_connect $database -host $host -port $portNumber PS : port number can be found in /tmp/socket file
