[GENERAL] very slow updates in 8.3?
Hi, I have noticed that the first time you execute an: update table1 set params_count=0; it takes too long to complete: 11000 rows - 100 s. Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP Which compiler is used to build Postgresql on Windows? Is it 9? Any comment? Cheers, Anton -- 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. createdb: could not connect to database postgres: could not connect to server: No such file or directory
On Sat, Apr 19, 2008 at 10:14:00AM +0530, Jaisen N.D. wrote: > Hi., Sorry for my late reply, I wasn't on desk for last few days. My goal is > to set up a spatial database, with postgresql 8.1. I removed the postgresql > installation using apt-get --purge remove. and removed the > var/lib/postgresql/data directory. And then reinstalled it again. The result > I got is here:- Why are you creating a database here: > -- > I created data folder again. But When I tried to create a test database, I > couldn't. Here is the steps I followed:- > -- > localhost:/home/user# adduser postgres > adduser: The user `postgres' already exists. > localhost:/home/user# mkdir /var/lib/postgresql/data > localhost:/home/user# chown postgres /var/lib/postgresql/data > localhost:/home/user# su - postgres When it told you it already created one and it's already running. > Creating new cluster (configuration: /etc/postgresql/8.1/main, data: > /var/lib/po stgresql/8.1/main)... > Configuring postgresql.conf to use port 5432... > Starting PostgreSQL 8.1 database server: main. Delete directory just created and do this as root: su - postgres createuser # Say you wanto to be superuser createdb > Where is the problem?? > The logfile in home folder of postgres user is as follows:- > -- > LOG: could not bind IPv4 socket: Address already in use > HINT: Is another postmaster already running on port 5432? If not, wait a > few seconds and retry. Use the server already running, instead of making another one. 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] I need to ecrypt one column to an output file
On Tue, Apr 15, 2008 at 01:11:10PM -0700, Ralph Smith wrote: > I need to do a simple query and output to a file. > No problem. > > But how do I encrypt one column's output? COPY (SELECT a, b, c, some_func(d) AS d_s3krit FROM your_tab) TO...; Does that help? Cheers, David. > There are lots of developer related links here, and info to "use the > /contrib pgcrypto", but I'm not a PG developer and I failed to find any > info on HOW TO USE that library function... > > Thanks all, > > Ralph Smith > [EMAIL PROTECTED] > = > > > -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] very slow updates in 8.3?
On Sat, Apr 19, 2008 at 1:33 AM, Anton Andreev <[EMAIL PROTECTED]> wrote: > update table1 set params_count=0; > it takes too long to complete: 11000 rows - 100 s. > Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP > Which compiler is used to build Postgresql on Windows? Is it 9? Does this table have alot of indexs, or trigger function for deletes? Remember that in postgresql, an update is really a delete and an insert since postgreSQL uses the MVCC model. So you are actually doing twice the work. To start, could you post the EXPLAIN ANALYZE for this query? Also, if you are going to run this query alot, you might want to alter your FILLFACTOR for you table and associated index to 50%. ALTER TABLE yourtable SET ( FILLFACTOR = 50 ); ALTER INDEX yourindex1 SET ( FILLFACTOR = 50); -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL error
HI everyone, I´m trying to capture all the possible errors that a statement can have. And, if there´s any error i will do a rollback; What i´m trying to do its: BEGIN insert into temp values(1, 2, 3); IF ANY_ERROR_OCCURED THEN ROLLBACK; RETURN FALSE; END IF; END; Did anyone knows how can i do that? Thanks a lot!
[GENERAL] Initial ugly reverse-translator
Hi all
I've chucked together a quick and very ugly script to read the .po files
from the backend and produce a simple database to map translations back
to the original strings and their source locations. It's a very dirty
.po reader that doesn't try to parse the format properly, but it does
the job. There's no search interface yet, this is just intended to get
to the point where useful queries can be run on the data and the most
effective queries can be figured out.
Right now queries against errors without format-string substitutions
work ok, if not great, with pg_tgrm based lookups, eg:
test=# SELECT message_id, is_format, message, translation
test-# FROM po_translation INNER JOIN po_message ON
po_translation.message_id = po_message.id INNER JOIN
test-# WHERE 'el valor de array debe comenzar con «{» o información de
dimensión' % translation
test-# ORDER BY similarity('el valor de array debe comenzar con «{» o
información de dimensión', translation) desc;
message_id | is_format |
message | translation
+---++-
4470 | f | array value must start with \"{\" or dimension
information | el valor de array debe comenzar con «{» o información de
dimensión"
4437 | f | argument must be empty or one-dimensional
array| el argumento debe ser vacío o un array unidimensional"
(2 rows)
test=# SELECT DISTINCT srcfile, srcline FROM po_location WHERE
message_id = 4437;
srcfile | srcline
-+-
/a/pgsql/HEAD/pgtst/src/backend/utils/adt/array_userfuncs.c | 121
utils/adt/array_userfuncs.c | 99
utils/adt/array_userfuncs.c | 121
utils/adt/array_userfuncs.c | 124
(4 rows)
It's also useful for format-string based messages, but more thought is
needed on how best to handle them. A LIKE query using the format-string
message as the pattern (after converting the pattern syntax to SQL
style) would be (a) slow and (b) very sensitive to formatting and other
variation. I haven't spent any time on that bit yet, but if anybody has
any ideas I'd be glad to hear them.
Anyway, the initial version of the script can be found at:
http://www.postnewspapers.com.au/~craig/poread.py
Consider running it in a new database as it's extremely poorly tested,
written very quickly and dirtily, and contains DDL commands. The schema
can be found inline in the script. The psycopg2 Python module is
required, and the pg_tgrm contrib module must be loaded in the database
you use the script with.
Once I'm happy with the queries for translation lookups I'll bang
together a quick web interface for the script and clean it up. At that
point it might start being useful to people here.
--
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] very slow updates in 8.3?
Anton Andreev wrote: Hi, I have noticed that the first time you execute an: update table1 set params_count=0; it takes too long to complete: 11000 rows - 100 s. Postgresql 8.3 configuration on Turion 64 with 1.4 Gb RAM, Windows XP Which compiler is used to build Postgresql on Windows? Is it 9? Any comment? You really haven't provided any information. What kind of disk do you have? What does your postgresql.conf look like? How many times have you run update against that table without vacuuming? What does your update statement look like? Joshua D. Drake -- 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] SQL error
On Sat, Apr 19, 2008 at 11:47:19AM -0300, x asasaxax wrote: > I´m trying to capture all the possible errors that a statement can have. > And, if there´s any error i will do a rollback; What i´m trying to do its: What are you trying to do? Any error automatically rolls back the transaction, so: > BEGIN > insert into temp values(1, 2, 3); > END; Should do what you want. 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] SQL error
x asasaxax wrote: HI everyone, I´m trying to capture all the possible errors that a statement can have. And, if there´s any error i will do a rollback; What i´m trying to do its: BEGIN insert into temp values(1, 2, 3); IF ANY_ERROR_OCCURED THEN ROLLBACK; RETURN FALSE; END IF; END; Did anyone knows how can i do that? Use a PL/pgSQL stored procedure. PostgreSQL does not support ad-hoc blocks or unnamed procedures, but it's OK to create the procedure, use it, and drop it again. http://www.postgresql.org/docs/current/static/plpgsql.html and particularly: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING However, the EXCEPTION blocks you use to trap errors aren't free in performance terms, so you should probably look for other approaches first. -- 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] SQL error
x asasaxax wrote: HI everyone, I´m trying to capture all the possible errors that a statement can have. And, if there´s any error i will do a rollback; What i´m trying to do its: BEGIN insert into temp values(1, 2, 3); IF ANY_ERROR_OCCURED THEN ROLLBACK; Sorry, I didn't see the ROLLBACK statement there. Please disregard my previous reply; I thought you were trying to do something other than what you appear to be doing. As Martijn van Oosterhout noted, the transaction will be put in an error state that ignores further commands. When in an error state, the transaction will treat COMMIT as ROLLBACK. I assume you wish to attempt to do something and if it fails, abort the transaction and leave everything in a working state ready for more commands? If so, consider doing that at the application level rather than trying to do it in SQL. All database interfaces give you a way to find out if an error occurred and get some information about the error. Maybe if you explained your end goal and why you're trying to do this it might help people give you more useful answers? -- 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] Initial ugly reverse-translator
Craig Ringer <[EMAIL PROTECTED]> writes: > It's also useful for format-string based messages, but more thought is > needed on how best to handle them. A LIKE query using the format-string > message as the pattern (after converting the pattern syntax to SQL > style) would be (a) slow and (b) very sensitive to formatting and other > variation. I haven't spent any time on that bit yet, but if anybody has > any ideas I'd be glad to hear them. I don't really see the problem. I assume from your reference to pg_trgm that you're using trigram similarity as the prefilter for potential matches, so a slow final LIKE match shouldn't be an issue really. (And besides, speed doesn't seem like the be-all and end-all here.) AFAICS you just need to translate %-string format escapes to %, quote any other % or _, and away you go. One thing that might be worth doing is avoiding spacing sensitivity, since whitespace is frequently mangled in copy-and-paste. Perhaps strip all spaces from both strings before matching? 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] Initial ugly reverse-translator
Tom Lane wrote: I don't really see the problem. I assume from your reference to pg_trgm that you're using trigram similarity as the prefilter for potential matches It turns out that's no good anyway, as it appears to ignore characters outside the ASCII range. Rather less than useful for searching a database of translated strings ;-) so a slow final LIKE match shouldn't be an issue really. (And besides, speed doesn't seem like the be-all and end-all here.) True. It's not so much the speed as the fragility when faced with small changes to formatting. In addition to whitespace, some clients mangle punctuation with features like automatic "curly"-quoting. AFAICS you just need to translate %-string format escapes to %, quote any other % or _, and away you go. One thing that might be worth doing is avoiding spacing sensitivity, since whitespace is frequently mangled in copy-and-paste. Perhaps strip all spaces from both strings before matching? Yep, that sounds pretty reasonable. As usual I'm making things more complicated than they need to be. I suspect it'll be necessary to strip quotes and some other punctuation too, but that's not a big deal. -- 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] Initial ugly reverse-translator
Craig Ringer <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I don't really see the problem. I assume from your reference to pg_trgm >> that you're using trigram similarity as the prefilter for potential >> matches > It turns out that's no good anyway, as it appears to ignore characters > outside the ASCII range. Rather less than useful for searching a > database of translated strings ;-) A quick look at the pg_trgm code suggests that it is only prepared to deal with single-byte encodings; if you're working in UTF8, which I suppose you'd have to be, it's dead in the water :-(. Perhaps fixing that should be on the TODO list. But in any case maybe the full-text-search stuff would be more useful as a prefilter? Although honestly, for the speed we need here, I'm not sure a prefilter is needed at all. Full text might be useful if a LIKE-based match fails, though. >> (And besides, speed doesn't seem like the be-all and end-all here.) > True. It's not so much the speed as the fragility when faced with small > changes to formatting. In addition to whitespace, some clients mangle > punctuation with features like automatic "curly"-quoting. Yeah. I was wondering whether encoding differences wouldn't be a huge problem in practice, as well. 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] Initial ugly reverse-translator
On Sat, 19 Apr 2008, Tom Lane wrote: Craig Ringer <[EMAIL PROTECTED]> writes: Tom Lane wrote: I don't really see the problem. I assume from your reference to pg_trgm that you're using trigram similarity as the prefilter for potential matches It turns out that's no good anyway, as it appears to ignore characters outside the ASCII range. Rather less than useful for searching a database of translated strings ;-) A quick look at the pg_trgm code suggests that it is only prepared to deal with single-byte encodings; if you're working in UTF8, which I suppose you'd have to be, it's dead in the water :-(. Perhaps fixing that should be on the TODO list. as well as ltree. they are in our todo list: http://www.sai.msu.su/~megera/wiki/TODO But in any case maybe the full-text-search stuff would be more useful as a prefilter? Although honestly, for the speed we need here, I'm not sure a prefilter is needed at all. Full text might be useful if a LIKE-based match fails, though. (And besides, speed doesn't seem like the be-all and end-all here.) True. It's not so much the speed as the fragility when faced with small changes to formatting. In addition to whitespace, some clients mangle punctuation with features like automatic "curly"-quoting. Yeah. I was wondering whether encoding differences wouldn't be a huge problem in practice, as well. regards, tom lane Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Initial ugly reverse-translator
Tom Lane wrote: True. It's not so much the speed as the fragility when faced with small changes to formatting. In addition to whitespace, some clients mangle punctuation with features like automatic "curly"-quoting. Yeah. I was wondering whether encoding differences wouldn't be a huge problem in practice, as well. I'm not *too* worried about text encoding issues. In general it's very obvious when text has been mangled due to bad encoding handling, and it's extremely rare to see anything subtle like an app that transforms accented chars to their base variants. Demangling strings damaged by bad encoding handling is way out of scope, and sometimes not possible anyway. I guess that UTF-8's delightful support for various composed and decomposed forms of same glyph might be a problem. It's something I may face in some other works I'm doing too, so I might have to see how hard it'd be to put together a DB function that normalizes a UTF-8 string to its fully composed variant. I don't think the decomposed forms see much use in the wild though; they mostly come up as a security issue for path/URL matching and the like. http://unicode.org/reports/tr15/ http://msdn2.microsoft.com/en-us/library/ms776393(VS.85).aspx http://earthlingsoft.net/ssp/blog/2006/07/unicode_normalisation I don't know much about the CJK text representations, though, either in Unicode or in other encodings like Big5 . I *hope* the Unicode normalization rules will be enough there but I'm not sure. All strings must be converted from their original encoding to utf-8 for queries of course. That might be troublesome when using something like a web form where it might be hard to know the encoding of the input text (and where browser bugs are the rule rather than the exception) but it's thankfully not necessary to cater to every weird and broken browser. So in this case I don't think encodings will be *too* much trouble unless alternate unicode normalization forms turn out to be more common than I think they are. -- 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] No server after starting
On Apr 18, 2008, at 2:00 PM, Bayless Kirtley wrote: First, I am new to Postgres. I am hoping to migrate an existing Java application from a couple of tried but unreliable open source Java databases. I have a fair amount of experience with Oracle, Informix and DB2 but it has been a few years. No on to my problem. I finally got PostgreSQL installed on Windows XP as a service. After restarting the computer, the Task Manager shows 5 postgres.exe processes running. From a CMD window, I issue a pg_ctl status command and get the response: "No server running." What am I doing wrong. I took all the defaults on installation except for assigning a password rather than a random generated one. Earlier today, I had it installed as a program and all worked fine after creating and initializing the database cluster. I was able to start the database from a non-admin user. Then I could check status and even shutdown as an admin user. I have added the ...\bin directory to the PATH and added the PGDATA environment variable. After this, I decided to remove Postgres and reinstall it as a service. I suspect that PGDATA might be set incorrectly; you should verify that. You can see what directory it's actually using by connecting and doing a SHOW data_directory; -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Slony-I for upgrades - was Re: In the belly of the beast (MySQLCon)
On Apr 18, 2008, at 2:42 PM, Chris Browne wrote: However, it is unusual for a database to consist of just one table of that sort. If you have a case like this, it will make plenty of sense to split this set of tables into pieces, and add them in at least somewhat incrementally. Does anyone happen to have any scripts/code that will just trawl through a database, adding tables to a set one at a time? -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] In the belly of the beast (MySQLCon)
On Apr 18, 2008, at 2:18 PM, Alvaro Herrera wrote: Actually, electrons themselves flow rather slowly -- millimeters per second according to Wikipedia. The signal propagation is a bit faster: "typically 75% of light speed", Wikipedia again. Yeah, electrons move *very* slowly in a solid. Presumably somewhat faster in liquid. Definitely faster in a gas, but you have to have a pretty good vacuum for them to actually approach the speed of light. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
