Re: [GENERAL] russian case-insensitive regexp search not working
alexander, lc_ctype and lc_collate can be changed only at initdb ! You need to read localization chapter http://www.postgresql.org/docs/current/static/charset.html Oleg On Thu, 12 Jul 2007, alexander lunyov wrote: Tom Lane wrote: alexander lunyov [EMAIL PROTECTED] writes: With this i just wanted to say that lower() doesn't work at all on russian unicode characters, In that case you're using the wrong locale (ie, not russian unicode). Check show lc_ctype. db= SHOW LC_CTYPE; lc_ctype -- C (1 запись) db= SHOW LC_COLLATE; lc_collate C (1 запись) Where can i change this? Trying to SET this parameters gives error parameter lc_collate cannot be changed Or [ checks back in thread... ] maybe you're using the wrong operating system. Not so long ago FreeBSD didn't have Unicode locale support at all; I'm not sure if 6.2 has that problem but it is worth checking. Does it work for you to do case-insensitive russian comparisons in grep, for instance? I put to textfile 3 russian strings with different case of first char and grep'ed them all: # cat textfile Зеленая Зеленодольская зеленая # grep -i зелен * textfile:Зеленая textfile:Зеленодольская textfile:зеленая So i think system is fine about unicode. 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] russian case-insensitive regexp search not working
Oleg Bartunov wrote:
alexander,
lc_ctype and lc_collate can be changed only at initdb !
You need to read localization chapter
http://www.postgresql.org/docs/current/static/charset.html
Yes, i knew about this, but i thought maybe somehow it can be changed
onthefly.
... (10 minutes later)
Yes, now when initdb done with --locale=ru_RU.UTF-8,
lower('RussianString') gives me 'russianstring', though, case-insensiive
regexp still not working. I guess i'll stick with lower() ~ lower()
construction.
And thanks everybody who replied!
Oleg
On Thu, 12 Jul 2007, alexander lunyov wrote:
Tom Lane wrote:
alexander lunyov [EMAIL PROTECTED] writes:
With this i just wanted to say that lower() doesn't work at all on
russian unicode characters,
In that case you're using the wrong locale (ie, not russian unicode).
Check show lc_ctype.
db= SHOW LC_CTYPE;
lc_ctype
--
C
(1 запись)
db= SHOW LC_COLLATE;
lc_collate
C
(1 запись)
Where can i change this? Trying to SET this parameters gives error
parameter lc_collate cannot be changed
Or [ checks back in thread... ] maybe you're using the wrong operating
system. Not so long ago FreeBSD didn't have Unicode locale support at
all; I'm not sure if 6.2 has that problem but it is worth checking.
Does it work for you to do case-insensitive russian comparisons in
grep, for instance?
I put to textfile 3 russian strings with different case of first char
and grep'ed them all:
# cat textfile
Зеленая
Зеленодольская
зеленая
# grep -i зелен *
textfile:Зеленая
textfile:Зеленодольская
textfile:зеленая
So i think system is fine about unicode.
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
--
alexander lunyov
[EMAIL PROTECTED]
---(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: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?
On 11.07.2007 23:07, Anton Melser wrote: Just a word of advice... unless you plan to spend lots of time on your db (like you want to think about it more than twice a week sort of thing...), just go with what you have in terms of the distro. We are running 8.1.4. And it just works, yes, even after all this time! You are certainly behind a good firewall, so if you have X.X.X, and it works (ie, your developpers have certified for X.X.X), why think about having the latest? Upgrading to new versions may well expose problems (like I remember someone talking about query optimisations a while back) that are non issues. If you are going to be pushing the limits, then compiling your own versions is not going to be an issue... Just my 2c Cheers Anton ps. I know, when a new version comes out so often it is soo hard to resist! Well, a good reason for upgrades are fixed bugs, and as minor releases focus on that, there is a good reason to stay half way up-to-date within the branch you are using. This god like faith of some admins in package maintainers, that they know what's right, good and stable for them, sometimes really worries me. Besides that.. I'd really expect my distribution to offer me the choice of what version of PostgreSQL to install. -- Regards, Hannes Dorbath ---(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: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?
On Thu, Jul 12, 2007 at 09:04:38AM +0200, Hannes Dorbath wrote: This god like faith of some admins in package maintainers, that they know what's right, good and stable for them, sometimes really worries me. The problem is the mismatch between what distrbuters want and what the postgres team wants. For distributors stable means no behavioural changes, whereas the postgresql team does bug fixes, some of which definitly make behavioural changes that would make previously working programs break. Backports is usually a good compromise. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Mac OS X
I tried to install postgres onto my macbook via 'fink' and don't like it all that much. I decided to install from source, it's a fallback to my slackware days. But fink already created a user postgres and I can't seem to find anything to change it's configuration settings for shell, home director... If anyone has suggestions I would appreciate it. Also, does anyone know of a more current installation write-up for Mac other than what Apple provides? It's written around 7.4 and I'm not sure it's going to be optimal. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Mac OS X
I tried to install postgres onto my macbook via 'fink' and don't like it all that much. I decided to install from source, it's a fallback to my slackware days. But fink already created a user postgres and I can't seem to find anything to change it's configuration settings for shell, home director... Im not sure how fink adds the user, but try NetInfo Manager (In Applications/Utlilities). If the user is not there, then it might be in /etc/passwd as for other Unix OS's Also, does anyone know of a more current installation write-up for Mac other than what Apple provides? It's written around 7.4 and I'm not sure it's going to be optimal. PostgreSQL has been building out of the box on OSX since at least the later 7.4.x series, but certainly all of the 8.x series. So you should just follow the installation instructions for Unix in the INSTALL file that comes with the source. If you have any problems then post the errors back to the list and someone should be able to help... Cheers Adam ---(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: [GENERAL] Mac OS X
On 7/12/07, Tom Allison [EMAIL PROTECTED] wrote: I tried to install postgres onto my macbook via 'fink' and don't like it all that much. I decided to install from source, it's a fallback to my slackware days. Try MacPorts (http://www.macports.org/), a modern BSD-style ports system for OS X. The PostgreSQL port sets up everything except initdb, and installs a launchd script for controlling the postmaster daemon: $ port install postgresql82-server MacPorts has a good selection of support packages -- PostGIS, language bindings, etc. One advantage of using MacPorts is that, quite unlike Debian's packages, ports are incredibly easy to write -- each port is just a small text file. If the sources have a reasonably modern layout (tarball, configure script, makefile), it's around three lines in addition to the package name, description etc. MacPorts handles the downloading, compiling, staging, file tracking, uninstallation and so on. So if MacPorts doesn't have a package you want, you can still install it from source and have it integrated into the MacPorts universe. Alexander. ---(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
[GENERAL] Database corruption: finding the bad block
Hi all, I just had the following error on one of our data bases: ERROR: could not access status of transaction 1038286848 DETAIL: could not open file pg_clog/03DE: No such file or directory I researched on the mailing list and it looks like the usual suspect is disk page corruption. There are few previous discussions about how to dump the suspect disk page, e.g.: http://archives.postgresql.org/pgsql-general/2006-10/msg01372.php http://groups.google.com/group/comp.databases.postgresql.hackers/browse_frm/thread/4988752a6939f45a/fd9f12468e86dd3?hl=enlr=ie=UTF-8rnum=8prev=/groups%3Fq%3Dpg_filedump%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D20030922162322.E12708%2540quartz.newn.cam.ac.uk%26rnum%3D8#fd9f12468e86dd3 You can probably find more searching for ERROR: could not access status of transaction or pg_filedump. What I could not find was a simple and fast way to find the bad block. The error message itself is not useful in this regard, and the binary search method is anything but fast on a big table. So I wrote the following plpgsql function: create or replace function find_bad_block(p_TableName text) returns tid as $find_bad_block$ declare result tid; crtRow record; count bigint := 0; begin for crtRow in execute 'select ctid from ' || p_TableName loop result = crtRow.ctid; count := count + 1; if count % 50 = 0 then raise notice 'rows processed: %', count; end if; end loop; return result; exception when OTHERS then raise notice '%: %', SQLSTATE, SQLERRM; return result; end; $find_bad_block$ language plpgsql; This will spit out the error + the last valid block visited. If there's no bad block, you won't get the notice with the error, just the last block of the table... worked fine for me, resulting in: NOTICE: 58P01: could not access status of transaction 1038286848 find_bad_block (657780,157) (1 row) Now to finding the file I should dump: select oid from pg_database where datname = 'dbname'; oid --- 16389 (1 row) select relfilenode from pg_class where relname='table_name'; relfilenode - 20418 (1 row) The file is then 'db_path/base/16389/20418'. Or a collection of '20418.x' files if the table's data is more than 1 GB, each file being a 1GB chunk of the table... so which one to dump ? First calculate the block count in one chunk: 1024 * 1024 / block_size, where block_size is the block size in KB with which postgres was compiled. That's normally 8, but we have systems where it is set to 32. If you didn't change that yourself, it is likely 8. So in my case the block count per chunk was: 1024 * 1024 / 8 = 131072. So the chunk number will be: blockId / blocks_per_chunk = 657780 / 131072 = 5 So I should look in the file 'db_path/base/16389/20418.5'... but wait, the block id has to be made relative to the chunk file's start: chunk_block_id = block_id % blocks_per_chunk So the block id to use with pg_filedump is: 657780 % 131072 = 2420 So my final command line was: pg_filedump -if -R 2420 db_path/base/16389/20418.5 resulting in something like: [snip] Data -- Item 1 -- Length: 44 Offset: 8148 (0x1fd4) Flags: USED XMIN: 1048587 CMIN: 90130188 XMAX: 0 CMAX|XVAC: 1036845056 Block Id: 655376 linp Index: 18451 Attributes: 1375 Size: 0 infomask: 0x0200 (XMIN_INVALID) Error: Computed header length not equal to header size. Computed 28 Header: 0 1fd4: 0b001000 0c475f05 cd3d .G_= 1fe4: 0a001000 13485f05 0002 2b03 .H_.+... 1ff4: 2d03 0100 -... [snip] So I found my bad block, and the previous and following looked OK. Now I want to fix just that one block even if the rows on it are lost, as the table data is not detail-critical (massive amounts of lost data would be critical, small loss is tolerable). Now to the actual fixing: from my searches it looks like zeroing out the complete block + reindex the table seems to be the recommended solution if it is not possible to downright drop the table and restore from backup (in my case that is not possible - this error is there from last year's October, and all our backups failed from then to now - and nobody checked the logs till now when I tried to upgrade postgres via slony and failed to do it because of this error - rather telling for the importance of this DB). So, how to zero out the page ? The following message shows a way to do it: http://www.webservertalk.com/archive142-2004-7-319751.html Basically set the 'zero_damaged_pages' setting to 'on': http://www.postgresql.org/docs/8.1/interactive/runtime-config-developer.html ... and select that page again. Unfortunately this did not work for me... looks like if the page header is not corrupt but only individual tuples are a problem, the 'zero_damaged_pages' thing doesn't work. Next try: http://usr-share-man.org/man1/dd.html
Re: [GENERAL] Database corruption: finding the bad block
On Thu, 2007-07-12 at 15:09 +0200, Csaba Nagy wrote: Luckily I remembered I have a WAL logging based replica, so I recovered the rest of the truncated file from the replica's same file... this being an insert only table I was lucky I guess that this was an option. To my surprise, the same block on the replica was not mangled... I say to my surprise, because on other occasions the bad blocks readily replicated over. In any case if you have a WAL logged replica you might be lucky to recover the corrupt block(s) from there (or just switch over, but that is risky too, you can't know for sure in what state the replica is, and that is actually harder to investigate than the master, as you can execute no SQL on the replica). The corruption could only migrate if the WAL records themselves caused the damage, which is much less likely than corruption of the data blocks at hardware level. ISTM that both Slony and Log shipping replication protect fairly well against block corruption on the standby, but only log shipping allows you to recover the precise block, as you describe. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database corruption: finding the bad block
On Thu, 2007-07-12 at 16:18, Simon Riggs wrote: The corruption could only migrate if the WAL records themselves caused the damage, which is much less likely than corruption of the data blocks at hardware level. ISTM that both Slony and Log shipping replication protect fairly well against block corruption on the standby, but only log shipping allows you to recover the precise block, as you describe. Well, I could only speak of what I experienced, and that is that in the total of 2 former file system level corruptions the replica was corrupted too. This time it was not... Because of that I had the impression Slony will be more immune to such glitches, as it is not shuffling raw file data around... I mean you still can have data corruption replicated, but the replica will be functional. Our WAL standby did not start up at all when we had that former file corruption issue... Cheers, Csaba. ---(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: [GENERAL] Force SSL / username combination
On Mon, 2007-07-09 at 09:05 +0200, Koen Vermeer wrote: I want to set up a database server that is connected to the Internet. Therefore, I want my users to use SSL/TLS certificates. I provide these certificates to the users, so I can control the information that is in there. Now, I know that I can set up PostgreSQL to use SSL/TLS, but I am unable to find a way to map a SSL/TLS certificate to a specific user. Is there a way to set this up? If so, how? Thanks for any pointers! Despite the somewhat less than overwhelming number of replies, I think I found a way around this. Just in case someone else may be looking for this: In contrib, there is something called 'sslinfo', which provides details of the X509 certificate that was used by the client for the session. Based on that, I can validate whether a username indeed matches the certificate, or make sure a user can only get data matching some field in the table to a field of the certificate, which probably is all I need. Koen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database corruption: finding the bad block
On Jul 12, 2007, at 8:09 AM, Csaba Nagy wrote: Hi all, I just had the following error on one of our data bases: ERROR: could not access status of transaction 1038286848 DETAIL: could not open file pg_clog/03DE: No such file or directory I researched on the mailing list and it looks like the usual suspect is disk page corruption. There are few previous discussions about how to dump the suspect disk page, e.g.: http://archives.postgresql.org/pgsql-general/2006-10/msg01372.php http://groups.google.com/group/comp.databases.postgresql.hackers/ browse_frm/thread/4988752a6939f45a/fd9f12468e86dd3? hl=enlr=ie=UTF-8rnum=8prev=/groups%3Fq%3Dpg_filedump%26hl%3Den% 26lr%3D%26ie%3DUTF-8%26selm%3D20030922162322.E12708% 2540quartz.newn.cam.ac.uk%26rnum%3D8#fd9f12468e86dd3 You can probably find more searching for ERROR: could not access status of transaction or pg_filedump. What I could not find was a simple and fast way to find the bad block. The error message itself is not useful in this regard, and the binary search method is anything but fast on a big table. So I wrote the following plpgsql function: create or replace function find_bad_block(p_TableName text) returns tid as $find_bad_block$ declare result tid; crtRow record; count bigint := 0; begin for crtRow in execute 'select ctid from ' || p_TableName loop result = crtRow.ctid; count := count + 1; if count % 50 = 0 then raise notice 'rows processed: %', count; end if; end loop; return result; exception when OTHERS then raise notice '%: %', SQLSTATE, SQLERRM; return result; end; $find_bad_block$ language plpgsql; This will spit out the error + the last valid block visited. If there's no bad block, you won't get the notice with the error, just the last block of the table... worked fine for me, resulting in: NOTICE: 58P01: could not access status of transaction 1038286848 find_bad_block (657780,157) (1 row) Now to finding the file I should dump: select oid from pg_database where datname = 'dbname'; oid --- 16389 (1 row) select relfilenode from pg_class where relname='table_name'; relfilenode - 20418 (1 row) The file is then 'db_path/base/16389/20418'. Or a collection of '20418.x' files if the table's data is more than 1 GB, each file being a 1GB chunk of the table... so which one to dump ? First calculate the block count in one chunk: 1024 * 1024 / block_size, where block_size is the block size in KB with which postgres was compiled. That's normally 8, but we have systems where it is set to 32. If you didn't change that yourself, it is likely 8. So in my case the block count per chunk was: 1024 * 1024 / 8 = 131072. So the chunk number will be: blockId / blocks_per_chunk = 657780 / 131072 = 5 So I should look in the file 'db_path/base/16389/20418.5'... but wait, the block id has to be made relative to the chunk file's start: chunk_block_id = block_id % blocks_per_chunk So the block id to use with pg_filedump is: 657780 % 131072 = 2420 So my final command line was: pg_filedump -if -R 2420 db_path/base/16389/20418.5 resulting in something like: [snip] Data -- Item 1 -- Length: 44 Offset: 8148 (0x1fd4) Flags: USED XMIN: 1048587 CMIN: 90130188 XMAX: 0 CMAX|XVAC: 1036845056 Block Id: 655376 linp Index: 18451 Attributes: 1375 Size: 0 infomask: 0x0200 (XMIN_INVALID) Error: Computed header length not equal to header size. Computed 28 Header: 0 1fd4: 0b001000 0c475f05 cd3d .G_= 1fe4: 0a001000 13485f05 0002 2b03 .H_.+... 1ff4: 2d03 0100 -... [snip] So I found my bad block, and the previous and following looked OK. Now I want to fix just that one block even if the rows on it are lost, as the table data is not detail-critical (massive amounts of lost data would be critical, small loss is tolerable). Now to the actual fixing: from my searches it looks like zeroing out the complete block + reindex the table seems to be the recommended solution if it is not possible to downright drop the table and restore from backup (in my case that is not possible - this error is there from last year's October, and all our backups failed from then to now - and nobody checked the logs till now when I tried to upgrade postgres via slony and failed to do it because of this error - rather telling for the importance of this DB). So, how to zero out the page ? The following message shows a way to do it: http://www.webservertalk.com/archive142-2004-7-319751.html Basically set the 'zero_damaged_pages' setting to 'on': http://www.postgresql.org/docs/8.1/interactive/runtime-config- developer.html ... and select that page again. Unfortunately this did not work for me... looks like if the page header is not corrupt but only individual tuples are a problem, the
[GENERAL] libpq: Specify pass phrase for SSL key
Hi, I am using libpq to set up an SSL connection to a PostgreSQL server. The client key is protected by a pass phrase, so when I issue a PQconnectdb(), I get a prompt stating: Enter PEM pass phrase: Instead, I would like to give the user a nice message box, asking for his/her password. So, how do I 'inject' the right SSL password into the connection sequence? Thanks for any help! Koen ---(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
[GENERAL] Panic error on attempted update
One of the servers I am responsible for maintaining was apparently having problems earlier today. The servers are all running SuSE 9.2, Apache 2 (not sure what version), and Postgres 8.1.4. Our main server is running Slony 1.1, I think, creating Log-Shipping records that the rest of the servers are then updated off of by means of an hourly cron script. When I went into the server to take a look at the cause of the problems, I noticed that there were numerous copies of the updating cron script running, all of them trying to do a select on that server's local database. (There was a VACUUM command running at the same time; I do not know whether that had anything to do with this or not.) I disabled the script so it wouldn't be able to run again and create more overhead to deal with, then attempted to let the backlogged copies finish. They have since dissappeared, but attempting to run the script manually produces the following error messages: slony1_log_1_03476171.sql ERROR: Slony-I: set 11 is on sync 3475773, this archive log expects 3476170 ERROR: current transaction is aborted, commands ignored until end of transaction block ... ... ERROR: current transaction is aborted, commands ignored until end of transaction block PANIC: right sibling's left-link doesn't match server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost Any clue what's causing the panic message to occur? I understand the aborted transactions, since I'm giving it archive logs later than the one it expects, but I have no clue why I'm getting the Panic call, nor do I understand what it means completely. Would someone please explain to me? - Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online.
Re: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Thu, Jul 12, 2007 at 09:04:38AM +0200, Hannes Dorbath wrote: This god like faith of some admins in package maintainers, that they know what's right, good and stable for them, sometimes really worries me. The problem is the mismatch between what distrbuters want and what the postgres team wants. For distributors stable means no behavioural changes, whereas the postgresql team does bug fixes, some of which definitly make behavioural changes that would make previously working programs break. I think we have a pretty good track record of not doing that except when it's forced by a need to plug a security hole. However, distributors certainly have more constraints than one could wish. For instance, at Red Hat I can't just push a new Postgres update into RHEL releases at my whim --- there are company constraints based on available QA resources and suchlike. So sometimes the RHEL version of PG lags behind the community version just because of manpower/scheduling issues. They have been pretty good about letting me push security updates promptly, though. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Mac OS X
I tried to install postgres onto my macbook via 'fink' and don't like it all that much. I decided to install from source, it's a fallback to my slackware days. But fink already created a user postgres and I can't seem to find anything to change it's configuration settings for shell, home director... If anyone has suggestions I would appreciate it. Also, does anyone know of a more current installation write-up for Mac other than what Apple provides? It's written around 7.4 and I'm not sure it's going to be optimal. Personally, I use the PG package available on http://www.entropy.ch/ software/macosx/postgresql/. It worked flawlessly, set up initdb, and configured PG to start at boot time. Thanks, Aurynn Shaw The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103 PostgreSQL Replication, Consulting, Custom Development, 24x7 support [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) -x86_64 but Redhat 5 Ent Benjamin ---(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
[GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) -x86_64 but Redhat 5 Ent Benjamin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] troubble with contrib compile
Tom Thanks works fine! best regards mdc --- Tom Lane [EMAIL PROTECTED] escribió: marcelo Cortez [EMAIL PROTECTED] writes: i downloaded postgres8.2.4 sources , expand and ./configure and install with success. Now i need one module from contrib directory , fuzzystrmatch cd /postgres/contrib/fuzzymatchstr [ ok ] make throws make Makefile:15: ../../src/Makefile.global: No such file or directory You seem to have removed the results of configure. As a general rule it's best to build the contrib modules in the same tree where you just built the Postgres core --- they definitely need configure's outputs and I think some of them require other files that get built along the way. If you are trying to match a previously built core system, be sure to re-configure with the exact same configure options, else the contrib modules may not work. pg_config --configure will help refresh your memory if you forgot what you used ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Benjamin Arai wrote: Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives RAID 5 with 16 spindles? RAID 10 will give you better performance I would think. -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) Assuming 8.1+ I would try something much more aggressive, like 4GB. Dont' forget your effective_cache_size. Joshua D. Drake -x86_64 but Redhat 5 Ent Benjamin ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Updates/Changes to a database
I am building an application with Postrges as the backend foundation. This is my first application and it has struck me that as we add features/functionality to the application and database with each new version, we will need some method of obtaining the current structure of the customers database and then modifying/updating the structure so that it matches the application revision standard. Are there pre-existing tools out there that does this sort of thing ?? My present direction is to create a small SQLite db that has there expected structure, compare each table against the SQL information_Schema.columns and the create a series of SQL commands to be executed that would add columns and/or table as needed. -- any thoughts or comments ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Panic error on attempted update
Andrew Edson [EMAIL PROTECTED] writes: PANIC: right sibling's left-link doesn't match Any clue what's causing the panic message to occur? Corrupt btree index. If you can determine which table is being inserted/updated when this happens (admittedly the message is not being helpful), REINDEX should fix it. Did you have a system crash before this started? Running something newer than 8.1.4 would be advisable anyway. I seem to recall that we fixed at least one bug that could produce this symptom, though I don't see anything specifically about it in the 8.1 CVS logs. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Benjamin Arai wrote: Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives RAID 5 with 16 spindles? RAID 10 will give you better performance I would think. -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) Assuming 8.1+ I would try something much more aggressive, like 4GB. Dont' forget your effective_cache_size. How is 4GB more aggressive? How large should the effective_cache_size be? Joshua D. Drake -x86_64 but Redhat 5 Ent Benjamin ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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: [GENERAL] Updates/Changes to a database
On Jul 12, 2007, at 10:07 AM, imageguy wrote: I am building an application with Postrges as the backend foundation. This is my first application and it has struck me that as we add features/functionality to the application and database with each new version, we will need some method of obtaining the current structure of the customers database and then modifying/updating the structure so that it matches the application revision standard. Are there pre-existing tools out there that does this sort of thing ?? My present direction is to create a small SQLite db that has there expected structure, compare each table against the SQL information_Schema.columns and the create a series of SQL commands to be executed that would add columns and/or table as needed. -- any thoughts or comments ? I tend to keep a schema version field (typically in a one-row, one- column table) in the database. Then I have a set of SQL scripts that'll upgrade from version n to version n+1, and they can be applied manually or automatically in sequence to bring the schema version up to the version required by the application. That's pretty common amongst big database backed apps, and if you're nice you also provide downgrade scripts to back out revisions. Maintaining the SQL patch scripts by hand isn't too hard to do, but I've found these tools useful too: http://dbmstools.sourceforge.net/ Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Benjamin, what version of postgres and what type of index you used ? The best setup is to use partitioning with rather small table for new data and GiST index and big archive table with static data and GIN index. I have some slides from PGDay http://www.sai.msu.su/~megera/postgres/talks/fts-pgday-2007.pdf Also, did you consider using dblink/dbilink to scale your search ? Oleg On Thu, 12 Jul 2007, Benjamin Arai wrote: Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) -x86_64 but Redhat 5 Ent Benjamin ---(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 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Oleg, I am running the latest 8.2.4. I am using GIN. The data is static. I do a batch upload every week of about 500GB and the ata is never touched again, it is always add and never delete or update. From your slides you state: GIN_FUZZY_SEARCH_LIMIT - maximum number of returned rows GIN_FUZZY_SEARCH_LIMIT=0, disabled on default When I do a search with say LIMIT 100 isn't this essentially the same thing? Benjamin Benjamin, what version of postgres and what type of index you used ? The best setup is to use partitioning with rather small table for new data and GiST index and big archive table with static data and GIN index. I have some slides from PGDay http://www.sai.msu.su/~megera/postgres/talks/fts-pgday-2007.pdf Also, did you consider using dblink/dbilink to scale your search ? Oleg On Thu, 12 Jul 2007, Benjamin Arai wrote: Hi, I have a really big Tsearch2 table (100s GB) that takes a while to perform queries and takes days to index. Is there any way to fix these issues using UNIONs or partitioning? I was thinking that I could partition the data by date but since I am always performing queries on the Tsearch2 field I do not know if this will help performance. I think paritioning will help the indexing problem since I can incrementally re-index the data but again I figured it would be better to ask. Any suggestions will be greatly appreciated. Thanks in advance. System I am running on: -Raid 5 with 16x drives -Quad core XEON 16 GB of memory (Any suggestion on the postgresql.conf setup would also be great! Currently I am just setting shared mem to 8192MB) -x86_64 but Redhat 5 Ent Benjamin ---(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 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Updates/Changes to a database
imageguy wrote: I am building an application with Postrges as the backend foundation. This is my first application and it has struck me that as we add features/functionality to the application and database with each new version, we will need some method of obtaining the current structure of the customers database and then modifying/updating the structure so that it matches the application revision standard. Are there pre-existing tools out there that does this sort of thing ?? My present direction is to create a small SQLite db that has there expected structure, compare each table against the SQL information_Schema.columns and the create a series of SQL commands to be executed that would add columns and/or table as needed. -- any thoughts or comments ? Not sure why you need SQLite when you, *ahem*, have and are modifying PostgreSQL. All the info you seek is in the system tables. To get a jump-start, try running psql with the -E option to see the backend queries that generate the displays of tables and table layouts. How you go about performing the updates will depend on many things: Are other apps running against the DB - especially the tables your app uses? Will the app be running on various versions of PG or will you control that? Will you allow any version to any version updates or only updates to the next version? What about the ability to downgrade to prior versions? Will the client-side be updated simultaneously with the database schema? What permissions will be required to perform the update? Updates in a sophisticated system will not be as simple as just matching table structures. You need to consider alterations to constraints - especially foreign-key constraints. Also the effect on views. It is likely that any version-to-version updates will need to be done in a specific and tested order. As a simple example, you would need to update a table to add a column before updating a view that refers to that column. One thing that might be useful is to create a simple function that just returns a version number: create or replace function my_app_version() returns text language sql as 'select ''1.01''::text;'; You can use this as needed. The client application can check the database-side version and either modify its behavior appropriately (ie. hide unavailable features) or refuse to start if there is an un-reconcilable mismatch. You could also create scripts to verify your database setup against the returned version and report errors, and you can base your update activity on the returned value. For example: 1. Test that existing tables/views/indexes/etc. match the returned version number - exit if not 2. If yes, check for availability of handler to change existing version to desired version - exit if one isn't available. 3. Perform backup. 4. Perform update including update of version-number function. As appropriate to your situation, you could change the version-number function at the start of your operation, say from '1.01' to '1.01-1.15' and program the clients to display an appropriate message if they try to connect during the upgrade. You will, of course, need to use transactions, locks, etc. to prevent access during the upgrade. 5. Verify database against new value of my_app_version() Cheers, Steve ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Big table with UNION ALL or partitioning with Tsearch2
Joshua D. Drake wrote: Benjamin Arai wrote: RAID 5 with 16 spindles? RAID 10 will give you better performance I would think. I'd say RAID 5 is not that bad for this kind of query, at least if the controller is worth anything. RAID 10 is the best choice for OLTP, but if the main job for this query is to read an index as fast as possible, RAID 5 might be well suited. I have good experience with TSearch queries on a 8 drive RAID 6 setup. Even if those 16 drives are cheap desktop SATAs, he should be able to read with around 800MB/sec. Benjamin, could you try if the following does change your query performance in any way? blockdev --setra 16384 /dev/sdX -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate Unique Key constraint error
How can one rollover a sequence back to zero after you delete records from a table with one such sequence. I see it starting with the last value of the sequence inserted. On 7/11/07, Tom Allison [EMAIL PROTECTED] wrote: On Jul 10, 2007, at 3:09 PM, Tom Lane wrote: Harpreet Dhaliwal [EMAIL PROTECTED] writes: Transaction 1 started, saw max(dig_id) = 30 and inserted new dig_id=31. Now the time when Transaction 2 started and read max(dig_id) it was still 30 and by the time it tried to insert 31, 31 was already inserted by Transaction 1 and hence the unique key constraint error. This is exactly why you're recommended to use sequences (ie serial columns) for generating IDs. Taking max()+1 does not work, unless you're willing to lock the whole table and throw away vast amounts of concurrency. I wonder how SQL server is handling this? Are they locking the table? I realize it's off-topic, but I'm still curious. Sequences are your friend. they come in INT and BIGINT flavors, but BIGINT is a lot of rows. Can set set Sequences to automatically rollover back to zero?
[GENERAL] Limit number connections by IP
Hi I know that i can limited the total number of connections in postgresql.conf with max_connections, but... can i limite the max number of connections from an specific IP? For example y like have total max connections of 40 buy the same ip can't have more than 5 connections open. It is possible? Thanks ---(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
[GENERAL] optimizing postgres
Hello all - I'm working on a postgres project after coming from a MySQL background ( no flames, please :). We are importing fairly large xml datasets ( 10-20 MB of xml files per 'project', currently 5 projects) into the database for querying. We are using PHP to create a web interface where users can create projects and upload their files. We have a parsing routine that gets the xml data into the database. The parsing has turned out to be pretty intense. It takes about 10-20 minutes for any project. When we are parsing data, it really slows down the site's response. I tested serving static webpages from apache, endless loops in php , but the choke point seems to be doing any other query on postgres when constructing a php page during parsing. As an example, the original designer specified separate tables for each project. Since they were all the same structure, I suggested combining them into a single table with a project_id column, but he said it would take too long to query. I was suspicious, but I went with his design anyway. It turned out he was right for our current set up. When I needed to empty the project table to re-parse data, doing a cascading delete could take up to 10 minutes! I cut re-parsing time in half by just dropping the table and creating a new one. Which was an okay thing to do, since the table only belonged to one project anyway. But I hate to think how long it would have taken to do a delete, cascading to child tables, if all the project data was in a single table. Since I'm not an expert in Postgres database design, I'm assuming I've done something sub-optimal. Are there some common techniques for tuning postgres performance? Do we need beefier hardware? Or is it a problem with how PHP or apache pools connections to the database? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] doubt
Does postgreSQL support distributive query processing? - 5, 50, 500, 5000. Store N number of mails in your inbox. Click here.
[GENERAL] How to pg_dumpall without root password
Hi, I am the new sysadmin and was given the task to move a postgresql installation from one box to another. So I want to do pg_dumpall . However, there is no record of postgresql superuser/root password. I do have root access to the system however. Does anyone have a suggestion on what I should do? ---(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
[GENERAL] Problems of connexion with Tsearch2 functions
I am a beginner with Postgresql and Full-Text indexing enabled by Tsearch2.
I have recently set all the configuration to run Tsearch2 on my
databases (Postgresql 8.2), under Linux (Fedora).
Everything worked great for a period of time. The queries I frequently
used were :
- select finddoc('word') - a special query using the function to_tsquery
- select lexize('word')
Then today, all the queries that call a Tsearch2 function,
instanteanously breaks the connection to the base, failing the query of
course.
1) Example when attempting the query from a Xterminal (french version)
# select set_curdict('fr_ispell');
La connexion au serveur a été coupée à l'improviste
Le serveur s'est peut-être arrêté anormalement
avant ou durant le traitement de la requête.
La connexion au serveur a été perdue. Tentative de réinitialisation : Echec.
2) Example when attempting from our JAVA Application (english version)
# The backend has broken the connection. Possibly the action you have
attempted has caused it to close.
at org.postgresql.PG_Stream.ReceiveChar(PG_Stream.java:140)
at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:76) at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
Queries like the following work just fine,
# select count(*) from table ;
So what is the problem really ? Anyone experienced something like this
before with Tsearch2 ? Should I reinstall Tsearch2 completely in
Postgresql ? I have already applied 'uninstall_tsearch2.sql' on my base,
but it does not change anything
Thanks you for advise, hope someone can help.
S.G.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] xpath_* namespace bug
On 7/10/07, CG [EMAIL PROTECTED] wrote: Can't wait... Got to have it now. :( I patched xpath.c and created a function which takes a third parameter which are the known namespaces in a space delimited hash list ... I based the patch on some example code from the libxml website which works perfectly from the command line. However, in PostgreSQL, it only works 65% of the time. The other 35% of the time it returns an empty string. I have no idea how to debug that kind of problem. Maybe if you look at the current CVS code (xml.c) it can help (as I've said, namespaces support is implemented there). You make it sound like 8.3 will have full-featured xpath_* frunctions. Will it have full-featured xslt support as well? No. For XSLT, you'll still have to use contrib/xml2. This most probably will be changed in future versions (separate contrib/xslt or something). -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] One Large Table or Multiple DBs?
Hi, What is efficient approach toward storing a web application's user data. How do applications such as basecamp, SalesForce or QuickBooks online store their data? Is it in one DB with huge tables each record having a user account's foreign key or do they create a separate database for each of their accounts? Which one is more efficient? My guess was in one large DB with large tables. Thank you, Mike ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate Unique Key constraint error
Harpreet Dhaliwal escribió: How can one rollover a sequence back to zero after you delete records from a table with one such sequence. I see it starting with the last value of the sequence inserted. You can use setval(), but normally you just leave it alone. Having numbers not starting from 0 is not a problem in most cases. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ Hi! I'm a .signature virus! cp me into your .signature file to help me spread! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] how to resolve invalid primary checkpoint
Hi, I'm new to Postgresql and need to support on any IT related problem. One of my customer not able to start the postgresql services. The log said that .. record with zero length at 0/2E16910 invalid primary checkpoint record record with zero length at 0/2E168D0 invalid secondary checkpoint record panic: could not locate a valid checkpoint record. how should i resolve this issue. Really appreciate any inputs. Thanks.
[GENERAL] TOAST, large objects, and ACIDity
Hi all, I want to use postgres to store data and large files, typically audio files from 100ko to 20Mo. For those files, I just need to store et retrieve them, in an ACID way. (I don't need search, or substring, or others functionnalities) I saw postgres offers at least 2 method : bytea column with TOAST, or large objects API. I wonder what are the differences of the 2 methods. * I found that large objects need a vacuum after delete to really release place. That may be handled by a trigger or automatic vaccum, is it right ? * Large objects are used via a special API available in libpq C client library. * I really care keeping my transaction fully ACID. Documentation on large objects doesn't explicitly say if lo_import an lo_export (and other primitives) are fully ACID. Some ideas ? * I going to bench insertion and read with this 2 methods. other advices are wellcome. thanks Benoit ---(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: [GENERAL] Performance Question - Table Row Size
I see. Thank you for the elaborate response. I have a clearer idea of what is going on now. In designing my application I was thinking of storing pieces of my data as serialized python data structures into a binary field (no more than 15KB), while a friend was arguing I should store the data in other tables and relate the tables together. He was arguing storing binary data on a table, even though, it is not queried slows down other queries and with this. Thanks again, Mike ---(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
[GENERAL] Accent-insensitive search
Hello, I would like to perform some accent-insensitive searches on my database, which means that a select query with condition, say, WHERE NAME = 'HELLÔ' would return records where name is 'HELLO' as well. My data is encoded in Unicode (UTF8) and therefore I cannot use Postgre's to_ascii() trick to achieve accent-insensitive searches. Is there any way I could perform such searches with an UTF8 encoding ? Thank you. ---(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
[GENERAL] returns setof rec... simple exampe doesn't work
I've googled this one and tried everything (except the correct solution
of course) until tears are starting to flow. Please help. Complete
example below. 7.4.13 on suse-64 x86
create table mytable (mystring varchar, myreal real);
insert into mytable (mystring,myreal) values ('abc',1.23);
insert into mytable (mystring,myreal) values ('def',4.56);
create type myrec as (mystring varchar, myreal real);
create or replace function myfunc() returns setof myrec as '
declare
crec myrec;
begin
for crec in select * from mytable loop
return next crec;
end loop;
return;
end;
' language 'plpgsql';
stdb=# select myfunc();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function myfunc line 6 at return next
[GENERAL] Limit connections per username
PostgreSQL 8.2 on Windows 2003 Server. Is it possible to limit number of connections per username? ---(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
[GENERAL] question on scan of clustered index
Hello:
I am running the following query:
SELECT COUNT(*) FROM orders WHERE o_orderdate date('1995-03-15');
Here are some stats for the orders relation:
select relname, relpages, reltuples from pg_class where relname = 'orders';
orders;29278;1.49935e+06
For my query above, the reduction factor is about 50%; 700,000 out of 1.5
millions tuples satisfy the WHERE clause.
I have a clustered index defined as follows:
CREATE INDEX orders_orderdate_idx
ON orders
USING btree
(o_orderdate);
ALTER TABLE orders CLUSTER ON orders_orderdate_idx;
I am running three ways: sequential scan, bitmap index scan and index scan.
The I/O cost for the index scan is 24+ times more than the other two. I do not
understand why this happens. If I am using a clustered index, it is my
understanding that there should be no need to retrieve a single page multiple
times, as tuples are sorted. Am I misunderstanding something?
Paula
Here are the results of explain analyze, and I/O results from
pg_statio_user_tables:
Aggregate (cost=1470905.79..1470905.80 rows=1 width=0) (actual
time=9040.320..9040.321 rows=1 loops=1)
- Index Scan using orders_orderdate_idx on orders (cost=0.00..1469101.03
rows=721902 width=0) (actual time=0.098..8222.234 rows=727305 loops=1)
Index Cond: (o_orderdate '1995-03-15'::date)
Total runtime: 9040.375 ms
I/O cost:
Heap Blocks Read: 649966 (from disk)
Heap Blocks Hit:70070 (from buffer)
Index Blocks Read: 1591
__
Aggregate (cost=52033.65..52033.66 rows=1 width=0) (actual
time=2364.470..2364.471 rows=1 loops=1)
- Bitmap Heap Scan on orders (cost=11927.12..50228.90 rows=721902 width=0)
(actual time=338.547..1609.118 rows=727305 loops=1)
Recheck Cond: (o_orderdate '1995-03-15'::date)
- Bitmap Index Scan on orders_orderdate_idx (cost=0.00..11746.65
rows=721902 width=0) (actual time=329.249..329.249 rows=727305 loops=1)
Index Cond: (o_orderdate '1995-03-15'::date)
Total runtime: 2364.697 ms
I/O cost:
Heap Blocks Read: 29278
Index Blocks Read: 1591
__
Aggregate (cost=49832.76..49832.77 rows=1 width=0) (actual
time=2215.752..2215.753 rows=1 loops=1)
- Seq Scan on orders (cost=0.00..48028.00 rows=721902 width=0) (actual
time=0.042..1458.734 rows=727305 loops=1)
Filter: (o_orderdate '1995-03-15'::date)
Total runtime: 2215.801 ms
I/O cost:
Heap Blocks Read: 29278
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Function to ADD a value into each column of real[]
Hello,
if I have this table:
CREATE TABLE teste (
id integer,
picos real[],
);
and insert this values:
INSERT INTO teste VALUES(1, '{{1,2,3},{1,2,3},{1,2,3},{1,2,3},
{1,2,3}}');
INSERT INTO teste VALUES(2, '{{1,2,3},{1,2,3},{1,2,3},{1,2,3},{1,2,3},
{1,2,3},{1,2,3}}');
INSERT INTO teste VALUES(3, '{{1,2,3},{1,2,3},{1,2,3},{1,2,3},{1,2,3},
{1,2,3},{1,2,3},{1,2,3}}');
INSERT INTO teste VALUES(4, '{{1,2,3},{1,2,3},{1,2,3}}');
I need to write a function to return all the record of this table and
add a 0 in the begin of each array.
Ex.:
1, {{0,1,2,3},{0,1,2,3},{0,1,2,3},{0,1,2,3},{0,1,2,3}}
.
How can I do that?
Any idea?!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance Question - Table Row Size
Mike [EMAIL PROTECTED] writes: I see. Thank you for the elaborate response. I have a clearer idea of what is going on now. In designing my application I was thinking of storing pieces of my data as serialized python data structures into a binary field (no more than 15KB), while a friend was arguing I should store the data in other tables and relate the tables together. He was arguing storing binary data on a table, even though, it is not queried slows down other queries and with this. A 15KB column value is going to be stored out-of-line in the TOAST table anyway, so your table tuple will just contain a pointer to it, which isn't very big. If you don't use that column value in a given query its effect will be minimal. -Doug ---(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: [GENERAL] Force SSL / username combination
On Thursday 12 July 2007 10:44, Koen Vermeer wrote:
On Mon, 2007-07-09 at 09:05 +0200, Koen Vermeer wrote:
I want to set up a database server that is connected to the Internet.
Therefore, I want my users to use SSL/TLS certificates. I provide these
certificates to the users, so I can control the information that is in
there. Now, I know that I can set up PostgreSQL to use SSL/TLS, but I am
unable to find a way to map a SSL/TLS certificate to a specific user.
Is there a way to set this up? If so, how?
Thanks for any pointers!
Despite the somewhat less than overwhelming number of replies, I think I
found a way around this. Just in case someone else may be looking for
this:
In contrib, there is something called 'sslinfo', which provides details
of the X509 certificate that was used by the client for the session.
Based on that, I can validate whether a username indeed matches the
certificate, or make sure a user can only get data matching some field
in the table to a field of the certificate, which probably is all I
need.
I'm guessing the lack of response is due to a lack of knowledge on the topic.
Personally I've never quite understood how you'd make use of the sslinfo
functions to manage connections without something like on commit triggers, so
I hope you'll consider submitting some documentation once you figure it out.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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: [GENERAL] question on scan of clustered index
[EMAIL PROTECTED] writes: I am running three ways: sequential scan, bitmap index scan and index scan. The I/O cost for the index scan is 24+ times more than the other two. I do not understand why this happens. If I am using a clustered index, it is my understanding that there should be no need to retrieve a single page multiple times, as tuples are sorted. Am I misunderstanding something? How long ago did you cluster the table? It sounds like it has degraded far away from a fully-ordered state. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] returns setof rec... simple exampe doesn't work
* Gauthier, Dave ([EMAIL PROTECTED]) wrote: stdb=# select myfunc(); ERROR: set-valued function called in context that cannot accept a set select * from myfunc(); ? Stephen signature.asc Description: Digital signature
Re: [GENERAL] optimizing postgres
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: Since I'm not an expert in Postgres database design, I'm assuming I've done something sub-optimal. Are there some common techniques for tuning postgres performance? Do we need beefier hardware? Honestly, it sounds like the database design might be the best place to start. Can you provide the schema definition and queries (the actual queries and the 'explain' output from them)? 10-20MB is certainly small enough that you'd have to be doing something particularly terrible to make it slow on any decent hardware... Or is it a problem with how PHP or apache pools connections to the database? This seems unlikely to be the issue.. If you're doing alot of web page loads and they were all sluggish or something I might say you want to make sure you're using connection pooling to Postgres but it sounds like you've got a different problem (perhaps a constraint against a column which doesn't have an index?). Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] question on scan of clustered index
* [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: I am running three ways: sequential scan, bitmap index scan and index scan. The I/O cost for the index scan is 24+ times more than the other two. I do not understand why this happens. If I am using a clustered index, it is my understanding that there should be no need to retrieve a single page multiple times, as tuples are sorted. Am I misunderstanding something? That does seem kind of bad (24+ times is quite a bit). At the same time though, you are having to go through the index when you're doing an index scan whereas you don't with the seq scan, so you're certainly pulling in more data of the disk. I'm a bit mystified why you'd think an index scan to pull half the data from a table is going to be faster than a seq scan anyway though... If you're trying to speed up the query to pull half the records you might want to look into partitioned tables instead, though I doubt it'll get much faster... Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] doubt
* Narasimha Rao P.A ([EMAIL PROTECTED]) wrote: Does postgreSQL support distributive query processing? PostgreSQL does not directly support splitting one query across multiple nodes (cpus, machines, whatever). It's certainly possible to set up distributed load balancing over some set of PostgreSQL instances with things like read-only slaves and whatnot. Also, of course, PostgreSQL can handle lots of and lots of seperate queries simultaneously with good scalability. You can also partition your data across systems and basically write application stuff to handle doing aggregated queries on each and then merging them together, etc... Stephen signature.asc Description: Digital signature
Re: [GENERAL] optimizing postgres
[EMAIL PROTECTED] writes: It turned out he was right for our current set up. When I needed to empty the project table to re-parse data, doing a cascading delete could take up to 10 minutes! You mean ON CASCADE DELETE foreign keys? Usually the reason that's slow is you forgot to put an index on the referencing column. PG doesn't force you to have such an index, but unless the referenced table is nearly static you'll want one. I too am fairly suspicious of the N-tables-are-faster-than-another- key-column mindset, but you'd need to do some actual experimentation (with correctly optimized table definitions ;-)) to be sure. regards, tom lane ---(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: [GENERAL] How to pg_dumpall without root password
Dat Chu [EMAIL PROTECTED] writes: I am the new sysadmin and was given the task to move a postgresql installation from one box to another. So I want to do pg_dumpall . However, there is no record of postgresql superuser/root password. I do have root access to the system however. Edit pg_hba.conf to allow local trust access. SIGHUP or restart postmaster. Connect as postgres user and set password as you see fit. Restore the previous pg_hba.conf settings (and SIGHUP again). If you're worried about having the system insecure even transiently against local bad guys, it's possible to do this without opening any hole, but it requires taking the DB down for a few minutes so you can do the password change in standalone mode. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to pg_dumpall without root password
* Tom Lane ([EMAIL PROTECTED]) wrote: If you're worried about having the system insecure even transiently against local bad guys, it's possible to do this without opening any hole, but it requires taking the DB down for a few minutes so you can do the password change in standalone mode. An alternative to 'trust' might be to change pg_hba.conf to use 'ident' over a local socket (on systems which support that). You'd need to know the superuser name (usually 'postgres') and either become that user on the unix side or set up an ident map from whatever user you want to be... Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Accent-insensitive search
On Monday 09 July 2007 18:33:49 turbovince wrote: Hello, I would like to perform some accent-insensitive searches on my database, which means that a select query with condition, say, WHERE NAME = 'HELLÔ' would return records where name is 'HELLO' as well. My data is encoded in Unicode (UTF8) and therefore I cannot use Postgre's to_ascii() trick to achieve accent-insensitive searches. Is there any way I could perform such searches with an UTF8 encoding ? Check the translate() documentation. It isn't the best thing in the world to have to use it and code things by yourself, but it works. -- Jorge Godoy [EMAIL PROTECTED] ---(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: [GENERAL] One Large Table or Multiple DBs?
On 07/09/07 16:18, Mike wrote: Hi, What is efficient approach toward storing a web application's user data. How do applications such as basecamp, SalesForce or QuickBooks online store their data? Is it in one DB with huge tables each record having a user account's foreign key or do they create a separate database for each of their accounts? Which one is more efficient? My guess was in one large DB with large tables. How big is big? What is efficient? Speed, management, upgrades, backups, scalability? If each customer has 80GB of data, then separate databases are the way to go, since it eases scalability and allows for parallel backups. But then upgrades must be applied to each of thousands of databases. If each customer has 100MB of data, then unified tables keyed off of account number would be simpler. In between is schema-per-account. But upgrades are still a chore. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] returns setof rec... simple exampe doesn't work
I'll answer my own question...
select * from myfunc();
(dumb, dumb, dumb)
-dave
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gauthier, Dave
Sent: Monday, July 09, 2007 4:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] returns setof rec... simple exampe doesn't work
I've googled this one and tried everything (except the correct solution
of course) until tears are starting to flow. Please help. Complete
example below. 7.4.13 on suse-64 x86
create table mytable (mystring varchar, myreal real);
insert into mytable (mystring,myreal) values ('abc',1.23);
insert into mytable (mystring,myreal) values ('def',4.56);
create type myrec as (mystring varchar, myreal real);
create or replace function myfunc() returns setof myrec as '
declare
crec myrec;
begin
for crec in select * from mytable loop
return next crec;
end loop;
return;
end;
' language 'plpgsql';
stdb=# select myfunc();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function myfunc line 6 at return next
Re: [GENERAL] returns setof rec... simple exampe doesn't work
Gauthier, Dave wrote:
I’ll answer my own question...
select * from myfunc();
(dumb, dumb, dumb)
If it makes you feel any better, it is a common mistake :)
Joshua D. Drake
-dave
* From: * [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] *On Behalf Of *Gauthier, Dave
*Sent:* Monday, July 09, 2007 4:07 PM
*To:* pgsql-general@postgresql.org
*Subject:* [GENERAL] returns setof rec... simple exampe doesn't work
I’ve googled this one and tried everything (except the correct solution
of course) until tears are starting to flow. Please help. Complete
example below. 7.4.13 on suse-64 x86
create table mytable (mystring varchar, myreal real);
insert into mytable (mystring,myreal) values ('abc',1.23);
insert into mytable (mystring,myreal) values ('def',4.56);
create type myrec as (mystring varchar, myreal real);
create or replace function myfunc() returns setof myrec as '
declare
crec myrec;
begin
for crec in select * from mytable loop
return next crec;
end loop;
return;
end;
' language 'plpgsql';
stdb=# select myfunc();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function myfunc line 6 at return next
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---(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
