Re: [GENERAL] Improve MMO Game Performance
Hello Arvind, the cyclic change idea is really marvellous , thank you You're welcome. :) but we store each round, because we need player actions for further analysis about game trends Normally the different analyze-forms and goals are known from the beginning. You could use a more compact format for the analysis, which is optimized for this. Also: if you just need this data for further analysis: don't store them together with the actual game data! Big Performance NO GO! Set up a second database-server! It can be a simple and even slow machine. Cache the round-data compressed in RAM or another fast location and import them from there in a steady flow into the second database-server. Their you can do your heavy analyze at any time without annoying your users! (You can even use more than a second server to do the analyze in parallel and sum up the aggregated data. Most the analyze must not be actual, so it doesn't bother if it takes some hours more. It is a big win for your user-performance) Greetings from Germany, Torsten -- 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] How to convert HEX to ASCII?
Marti Raudsepp schrieb: On Fri, Dec 2, 2011 at 16:16, Torsten Zuehlsdorff f...@meisterderspiele.de wrote: But i clearly have a missunderstanding of other chars, like umlauts or utf-8 chars. This, for example, should return a 'ö': # SELECT chr(x'C3B6'::int); chr - 쎶 (1 row) That gives you the Unicode codepoint C3B6, but %C3%B6 is UTF-8-encoded and actually decodes to the codepoint 00F6. There is a fundamental problem that a decoded URL may actually be a binary string -- it might not have a textual representation at all. But if text is what you want, RFC3986 strongly suggests using UTF-8 for encoding text strings in URLs, and that works almost always in the real world. Text is what i want. :) I've created a highly specialiced CMS, which handle a bunch of big sites (in meaning of a great numbers of users and content). It has a build-in traffic-analyze and with this function it creates a real time analyze of the keywords, a user used to find the sites in search engines. This is very needful if you try to do SEO for websites with more than 20.000 unique content-pages. :) CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text LANGUAGE plpgsql IMMUTABLE STRICT AS $$ DECLARE bin bytea = ''; byte text; BEGIN FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP IF length(byte) = 3 THEN bin = bin || decode(substring(byte, 2, 2), 'hex'); ELSE bin = bin || byte::bytea; END IF; END LOOP; RETURN convert_from(bin, 'utf8'); END $$; Hey, this function looks similar to my encoding function :) Thank you very munch! This will break for binary-encoded data in URLs, though. Thats no problem, i just have text. Big thanks to all of you, Torsten -- 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] Query runs in 335ms; function in 100,239ms : date problem?
Rory Campbell-Lange schrieb: Try to run it as a prepared query - I guess you'll get about the same run time as with the function (i.e. over 100 seconds). The prepared query runs in almost exactly the same time as the function, but thanks for the suggestion. A very useful aspect of it is that I was able to get the EXPLAIN output which I guess gives a fairly good picture of the plan used for the function. The explain output is here: http://campbell-lange.net/media/files/explain.txt.html I'm inexperienced in reading EXPLAIN output, but it looks like the Nested Loop Semi Join at line 72 is running very slowly. I added in more filtering conditions to the clause at line 72 and the prepared statement dropped in runtime to 24043.902 ms. Unfortunately the function ran slower -- 47957.796 -- but even that is a 50% improvement. Also declare your function as STABLE. This can trigger an speed-increase. Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] PostgreSQL 'hosting'
Dragan Zubac schrieb: Does anybody know if there're any companies offering PostgreSQL 'hosting' ? By 'hosting', I mean you get access to a database to which your application connects remotely and do sql stuff. 'Hosting' company takes care of database maintenance,backup,etc. Have a look at: http://www.postgresql.org/support/professional_hosting Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] You could be a PostgreSQL Patch Reviewer!
Josh Berkus schrieb: You do not have to be a C coder to be a patch reviewer. Pretty much all you need to know is: - how to checkout PostgreSQL from Git - how to build PostgreSQL from source - how to apply a patch If you know those three things, you can help with patch review. Of course, if you do know C, you can be even more help ... and learn the PostgreSQL source in the process. I can build PostgreSQL on different FreeBSD-Versions (7, 8.1, 8.2). Is this in any kind related to the PG-Buildfarm? It sounds like this could be automated very easily. We especially need folks who are able to build PostgreSQL on Windows, as we have several Windows-specific patches and no reviewers for them. If there is an rough descriptions, this should be also possible. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] Changing SHMMAX
Florian Weimer schrieb: Please guide me how to change it permanently and what is the correct value for it. I am going for 8GB . Usually, you can put these lines kernel.shmall = 90 kernel.shmmax = 90 into /etc/sysctl.conf. Run sysctl -p to activate them. However, this is a bit distribution-specific. If you're using FreeBSD you even have to restart the machine. Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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 with initdb: creates database which do not exists
Thom Brown schrieb: initdb creates a database cluster, not a database. [..] Now i'm feeling like fool - this is so obviously. -.- I will stop posting stressed to the Usenet. I'm sorry. Thanks for your replies and time! Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with initdb: creates database which do not exists
Hello, i'm using initdb of an PostgreSQL 8.4 installed over the port-system of FreeBSD: = initdb foo --locale=de_DE.UTF-8 --lc-collate=de_DE.UTF-8 --lc-ctype=de_DE.UTF-8 --lc-messages=de_DE.UTF-8 --lc-monetary=de_DE.UTF-8 --lc-numeric=de_DE.UTF-8 --lc-time=de_DE.UTF-8 The files belonging to this database system will be owned by user thorny. This user must also own the server process. The database cluster will be initialized with locale de_DE.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to german. creating directory foo ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in foo/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling trust authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: postgres -D foo or pg_ctl -D foo -l logfile start = After that the database is not visible: = psql -l List of databases Name| Owner | Encoding | Collation | Ctype | Access privileges ++--+---+---+--- pgsql | pgsql | UTF8 | C | C | postgres | pgsql | UTF8 | C | C | template0 | pgsql | UTF8 | C | C | =c/pgsql : pgsql=CTc/pgsql template1 | pgsql | UTF8 | C | C | =c/pgsql : pgsql=CTc/pgsql (4 rows) = So i try again to create it: = initdb foo --locale=de_DE.UTF-8 --lc-collate=de_DE.UTF-8 --lc-ctype=de_DE.UTF-8 --lc-messages=de_DE.UTF-8 --lc-monetary=de_DE.UTF-8 --lc-numeric=de_DE.UTF-8 --lc-time=de_DE.UTF-8 The files belonging to this database system will be owned by user thorny. This user must also own the server process. The database cluster will be initialized with locale de_DE.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to german. initdb: directory foo exists but is not empty If you want to create a new database system, either remove or empty the directory foo or run initdb with an argument other than foo. = Thats interessting. But i can't drop the database: = dropdb foo dropdb: database removal failed: ERROR: database foo does not exist = So: any idea why the database is not visible? And how do get rid of the non-existing databases ;) Thanks for your time, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] InitDB: Bad system call
Hello, ... The simplest explanation I can think of is that it's *only* shmctl that is malfunctioning, not the other SysV shared memory calls. Which is even weirder, and definitely seems to move the problem into the category of kernel bug rather than configuration mistake. Hmmm ... Google turned up the information that FreeBSD migrated from int to size_t variables for shared memory size between 7.0 and 8.0, and in particular that the size of the struct used by shmctl() changed in 8.0. So I'm now wondering if what you're dealing with is some sort of version skew problem. Could it be that you built Postgres against system header files that don't match your kernel version? I'm not exactly sure how that would manifest as this particular signal, but it seems worth checking. I have the correct header files, but that brings me to an interesting notice and a workaround. Before i had build the new jail, i checked out the newest sources for FreeBSD 7.0 and recompile the world. With the new world i build the jail and the problems occurs. Meanwhile there are two running jails with postgresql in at the same server. And IPC-problems seems unfamiliar to me, because the error-messages normally looks very different and other instances running without problems;) What i've done now, was disableing an old jail and copy it to an new location. After some reconfiguration i use the copy as new jail and install postgresql. And it works. That fortify your assumption, that the problem must lie in FreeBSD. But this will be hard to debug, because the last make world was 3 years ago of the machine. I will discribe the problem to the FreeBSD-Community. Thanks for all your help and time, Torsten -- 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] InitDB: Bad system call
Alban Hertroys schrieb: Core was generated by `postgres'. Program terminated with signal 12, Bad system call. Reading symbols from /lib/libm.so.5...done. Loaded symbols for /lib/libm.so.5 Reading symbols from /lib/libc.so.7...done. Loaded symbols for /lib/libc.so.7 Reading symbols from /libexec/ld-elf.so.1...done. Loaded symbols for /libexec/ld-elf.so.1 #0 0x000800bb166c in shmctl () from /lib/libc.so.7 (gdb) bt #0 0x000800bb166c in shmctl () from /lib/libc.so.7 #1 0x005b158f in PGSharedMemoryIsInUse (id1=Variable id1 is not available. ) at pg_shmem.c:247 #2 0x006a0844 in CreateLockFile (filename=0x7ea036 postmaster.pid, amPostmaster=0 '\0', isDDLock=1 '\001', refName=0x800e0b180 /usr/local/pgsql/data) at miscinit.c:835 #3 0x0049baf0 in AuxiliaryProcessMain (argc=3, argv=0x7fffebc8) at bootstrap.c:350 #4 0x0056742e in main (argc=4, argv=0x7fffebc0) at main.c:180 Well, this seems to be clear proof for what everyone suspected all along: your kernel is rejecting SysV-shared-memory calls. I'm too tired to go check that that shmctl() is the first such syscall during the boot sequence, but it looks about right. So we're now back to the question of *why* it's rejecting those calls, when you apparently have the proper support configured. I'm afraid you now need to seek the assistance of some FreeBSD kernel experts; it's beyond the ken of a simple database hacker ... Hmm... shared memory in a jail, there used to be some issues with that and I don't think they have been (or are going to be) solved. I recall that shared memory can't be local to a jail (it's shared after all), so you probably need(ed) to allow access to it somehow for your jails. Or you're running into issues sharing the same shared memory across multiple jails (and the base system) maybe? The problems are known and i already have taken care of it. As written at the beginning i already have two jails at the server with running postgresql-instances. Normally you have to tweak up the IPC-Params and use different user-ids for each postgres-user to avoid the problem with the shared memory. Thats why my problem is very strange. I never run into such a problem and i run nearly a dozen postgresqls in jails at different FreeBSDs. Greetings, Torsten -- 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] InitDB: Bad system call
Hello, Well, this seems to be clear proof for what everyone suspected all along: your kernel is rejecting SysV-shared-memory calls. I'm too tired to go check that that shmctl() is the first such syscall during the boot sequence, but it looks about right. So we're now back to the question of *why* it's rejecting those calls, when you apparently have the proper support configured. I'm afraid you now need to seek the assistance of some FreeBSD kernel experts; it's beyond the ken of a simple database hacker ... 7.0-STABLE is ... old. I would recommend upgrading to something more recent before moving forward with this bug, as I expect the FreeBSD community to recommend such anyway. FreeBSD 7 is from 2007. Thats not very old - you use FreeBSD for services which just should run (like postgresql :)). In my supervised server-park are half a dolzen FreeBSD-Server with uptimes around 7 years. Upgrading is something you do very very rarely. And till now i didn't get such recommendation from the community. Its more likely to add a new server with a new Version of FreeBSD. Hm... i can't start debugging the kernel of a live-maschine. I will add a new server therefor. Maybe i can reproduce the problem at another machine for the FreeBSD-Community. Thanks to all for you help und time, Torsten -- 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] InitDB: Bad system call
Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= f...@meisterderspiele.de writes: It's the same like before, but this time with core-file! :) I don't know why, but now there is one. You can find it here: http://www.dddbl.de/postgres.core (2,4 MB) That's good, but the core file is pretty much useless to anyone else. Please gdb it and post a stack trace: gdb /path/to/postgres /path/to/core gdb bt gdb quit Hm... /path/to/postgres? Not initdb? But regardless what i use, it looks like: #0 0x000800bb166c in ?? () #1 0x005b158f in ?? () #2 0x00300020 in ?? () #3 0x7fffe620 in ?? () #4 0x7fffe560 in ?? () #5 0x00080091607a in ?? () #6 0x000800c04a60 in ?? () #7 0x000800913496 in ?? () #8 0x7fffeab8 in ?? () #9 0x7fffeab0 in ?? () #10 0xff00423f38e0 in ?? () #11 0x7fffe618 in ?? () #12 0x0031 in ?? () #13 0xaa8a in ?? () #14 0x007ea036 in ?? () #15 0x00080091056d in ?? () #16 0x0207 in ?? () #17 0x05c8 in ?? () #18 0x7fffe618 in ?? () #19 0xff00423f38e0 in ?? () #20 0x7fffe65d in ?? () #21 0x007ea094 in ?? () #22 0x7fffeab0 in ?? () #23 0x7fffeab8 in ?? () #24 0x in ?? () I believe that is not very helpful, is it? Greetings, Torsten -- 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] InitDB: Bad system call
Tom Lane schrieb: Hm... /path/to/postgres? Not initdb? Yes; it's postgres that is failing, not initdb. Ok. But regardless what i use, it looks like: #0 0x000800bb166c in ?? () #1 0x005b158f in ?? () ... I believe that is not very helpful, is it? Nope, it's not. Could you reconfigure with --enable-debug, rebuild, try again? Hm, that was already with --enable-debug. But i believe i just missused gdb at the first time. Now i get the following result, which seems more helpful. But i have to reuse an save core-dump, because like before postgres don't create new ones. Here the result: %gdb /usr/local/pgsql/bin/postgres /tmp/postgres.core GNU gdb 6.1.1 [FreeBSD] Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as amd64-marcel-freebsd... warning: exec file is newer than core file. Core was generated by `postgres'. Program terminated with signal 12, Bad system call. Reading symbols from /lib/libm.so.5...done. Loaded symbols for /lib/libm.so.5 Reading symbols from /lib/libc.so.7...done. Loaded symbols for /lib/libc.so.7 Reading symbols from /libexec/ld-elf.so.1...done. Loaded symbols for /libexec/ld-elf.so.1 #0 0x000800bb166c in shmctl () from /lib/libc.so.7 (gdb) bt #0 0x000800bb166c in shmctl () from /lib/libc.so.7 #1 0x005b158f in PGSharedMemoryIsInUse (id1=Variable id1 is not available. ) at pg_shmem.c:247 #2 0x006a0844 in CreateLockFile (filename=0x7ea036 postmaster.pid, amPostmaster=0 '\0', isDDLock=1 '\001', refName=0x800e0b180 /usr/local/pgsql/data) at miscinit.c:835 #3 0x0049baf0 in AuxiliaryProcessMain (argc=3, argv=0x7fffebc8) at bootstrap.c:350 #4 0x0056742e in main (argc=4, argv=0x7fffebc0) at main.c:180 (gdb) quit Greetings, Torsten -- 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] InitDB: Bad system call
Hi Glen, How annoying :-(. I think what you need to do is use truss or strace or local equivalent with the follow-forks flag, so that you can see what the stand-alone backend process does, not just initdb itself. Ok, next round. I just have truss as an option, because strace didn't work at my AMD64. Hope its helpfull: $ truss -f -o /tmp/pgtuss-f.txt /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Result: http://www.dddbl.de/pg-truss-f.txt [ scratches head ... ] That looks like it got interrupted before getting to anything interesting. Did the console printout show any Bad system call reports? I didn't see it mentioned earlier in this thread - is security.jail.sysvipc_allowed=1? This will automatically be set to 1 if you have jail_sysvipc_allow=YES in rc.conf. Yes, it is: # sysctl -a | grep sysvipc_allowed security.jail.sysvipc_allowed: 1 Greetings, Torsten -- 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] InitDB: Bad system call
Hello Tom, How annoying :-(. I think what you need to do is use truss or strace or local equivalent with the follow-forks flag, so that you can see what the stand-alone backend process does, not just initdb itself. Ok, next round. I just have truss as an option, because strace didn't work at my AMD64. Hope its helpfull: $ truss -f -o /tmp/pgtuss-f.txt /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Result: http://www.dddbl.de/pg-truss-f.txt [ scratches head ... ] That looks like it got interrupted before getting to anything interesting. Did the console printout show any Bad system call reports? Yes, it does. But because i believed that it's not very helpful without a core-file, i rebuild everything again. I checked out the newsted sources from bsd, build the world new, the jail new and than the postgresql. It's the same like before, but this time with core-file! :) I don't know why, but now there is one. You can find it here: http://www.dddbl.de/postgres.core (2,4 MB) If helpful, i can give you access to the jail. This should be easier for us, than communication over multiple timezones. Greetings, Torsten -- 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] InitDB: Bad system call
Hello, The first suspicious i can see are a lots of ERR#32 'Broken pipe' entries. This is the result of postgres crashing and thus initdb being unable to write any more data to it. I think you should try harder to generate the core file. Maybe you have too low an ulimit -c setting? There is no ulimit at FreeBSD. Greetings, Torsten -- 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] InitDB: Bad system call
Hello, Excerpts from Torsten ZÌhlsdorff's message of mié ago 11 02:52:34 -0400 2010: Bad system call (core dumped) I think you should try harder to generate the core file. Maybe you have too low an ulimit -c setting? The kernel message indicates that core *is* being dumped. Possibly it's being dumped in the $PGDATA directory, which initdb will rm -rf on failure. Try using initdb --noclean. So... yesterday night i was able to change the SyS-IPC Settings and restart the server. Good bye 216 days uptime :D After that i recreate the jail from the scratch and compiled PG 9.0 Beta 4 again. I've compiled PG with: $ ./configure --enable-debug InitDB is: $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/ --noclean Running in noclean mode. Mistakes will not be cleaned up. The files belonging to this database system will be owned by user pgsql. This user must also own the server process. The database cluster will be initialized with locale en_US.ISO8859-1. The default database encoding has accordingly been set to LATIN1. The default text search configuration will be set to english. creating directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call 10 selecting default shared_buffers ... Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call Bad system call 400kB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... Bad system call child process exited with exit code 140 initdb: data directory /usr/local/pgsql/data not removed at user's request Result in $PGDATA is: $ ls -lah /usr/local/pgsql/data/ total 84 drwx-- 12 pgsql pgsql 512B Aug 12 08:56 . drwx-- 6 pgsql pgsql 512B Aug 12 08:56 .. -rw--- 1 pgsql pgsql 4B Aug 12 08:56 PG_VERSION drwx-- 3 pgsql pgsql 512B Aug 12 08:56 base drwx-- 2 pgsql pgsql 512B Aug 12 08:56 global drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_clog -rw--- 1 pgsql pgsql 3.8K Aug 12 08:56 pg_hba.conf -rw--- 1 pgsql pgsql 1.6K Aug 12 08:56 pg_ident.conf drwx-- 4 pgsql pgsql 512B Aug 12 08:56 pg_multixact drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_notify drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_stat_tmp drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_subtrans drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_tblspc drwx-- 2 pgsql pgsql 512B Aug 12 08:56 pg_twophase drwx-- 3 pgsql pgsql 512B Aug 12 08:56 pg_xlog -rw--- 1 pgsql pgsql17K Aug 12 08:56 postgresql.conf -rw--- 1 pgsql pgsql49B Aug 12 08:56 postmaster.pid Please notice, that after changing the IPC-Settings of the system, no core-file is dumped anymore. Quiet interessting. Greetings, Torsten -- 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] InitDB: Bad system call
Hi Tom, Please notice, that after changing the IPC-Settings of the system, no core-file is dumped anymore. Quiet interessting. How annoying :-(. I think what you need to do is use truss or strace or local equivalent with the follow-forks flag, so that you can see what the stand-alone backend process does, not just initdb itself. Ok, next round. I just have truss as an option, because strace didn't work at my AMD64. Hope its helpfull: $ truss -f -o /tmp/pgtuss-f.txt /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Result: http://www.dddbl.de/pg-truss-f.txt Greetings, Torsten -- 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] InitDB: Bad system call
Hi Tom, Bad system call (core dumped) Have you tried running the initdb with strace or truss? That might give you a clue as to exactly what system call is failing. Your jail isn't allowing something fundamental here, but it's hard to guess what. Or even easier, gdb the core file ... As written early i can't locate the core file. But now i use truss: $ truss -o /tmp/pg.truss /usr/local/bin/initdb /usr/local/pgsql/ Here is the result: http://www.dddbl.de/pg.truss.txt The first suspicious i can see are a lots of ERR#32 'Broken pipe' entries. I also changed some ipc-values from: kern.ipc.semmni=512 kern.ipc.semmns=1024 kern.ipc.semmnu=512 to: kern.ipc.semmnu: 4096 kern.ipc.semmns: 8192 kern.ipc.semmni: 32767 But these are read-only values. I have to reboot the machine. But it's a live-machine and it will take some time to prepare rebooting. -.- Greetings from Germany, Torsten -- 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] InitDB: Bad system call
Hello Thom, See http://www.postgresql.org/docs/9.0/static/kernel-resources.html and the section under NetBSD/OpenBSD. I already know the FreeBSD section. My current values are: kern.ipc.shmall: 131072 kern.ipc.shmmax: 2684225436 kern.ipc.semmap: 4096 kern.ipc.semmnu: 512 kern.ipc.semmns: 1024 kern.ipc.semmni: 512 kern.ipc.shm_use_phys: 0 security.jail.sysvipc_allowed: 1 I also run the user with different UIDs: $ grep pgsql -h /usr/local/jail/*/*/etc/passwd pgsql:*:1070:70:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh pgsql:*:7575:7575:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh pgsql:*:1074:70:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh pgsql:*:1071:70:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh I also rebuild the complete jail to make sure, that it is not an error while creating the jail. I also disable all - but one (the live-db ;)) - postgresql instance to make sure, that enough shared memory is free. But the bad system call messages don't go away. Any other hint? Greetings, Torsten -- 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] InitDB: Bad system call
Torsten Zühlsdorff schrieb: i've just compiled a new Jail at my FreeBDS 7.0-STABLE machine and trying to get PostgreSQL 9.0 Beta 4 running. Compiling etc works fine. But when i call the initdb, i get Bad System Call messages. Here is the output: $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -d [output] First i believed this is an error relating to SYSVSHM-, SYSVSEM-, SYSVMSG-options or User-Id (http://www.freebsddiary.org/jail-multiple.php). But the postgres-user has a user-id which is not used by other postgres-instances in other jails. And the other options are enabled in the root-instance. I also tried to build postgres from a fresh portstree, to make sure, that i have nothing miss-./configured, but there are the same problems. I've tried the initdb in the only jail PostgreSQL is already running. There it works. I have no clue what to do next. I didn't even find the core-dump -.- Should i just tune-up the System V IPC parameters and hope? Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] InitDB: Bad system call
Reko Turja schrieb: i've just compiled a new Jail at my FreeBDS 7.0-STABLE machine and trying to get PostgreSQL 9.0 Beta 4 running. Compiling etc works fine. Is the machine really running a pre-RELENG 7.0? As far as i now, we used the 7.0 versions some month after their release. So: no. When i look in, i see in the welcome message: FreeBSD 7.0-STABLE (GENERIC) #1: Fri Aug 15 19:33:13 CEST 2008 That are 6 months after initial release of 7.0. But when i call the initdb, i get Bad System Call messages. Here is the output: The system throwing out a coredump instead of failing gracefully suggests an OS bug and as you are seemingly running an ancient development branch, that seems even quite plausible. I'm running a development *jail* at the *same* machine like the live-database. The live-database works greats. There is also a second jail were a postgresql-instance is running. In both i can use Postgresql (versions 8.3 and 8.4) without any limitations. But in the third-jail i get the problems. Greetings, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] InitDB: Bad system call
Hello, i've just compiled a new Jail at my FreeBDS 7.0-STABLE machine and trying to get PostgreSQL 9.0 Beta 4 running. Compiling etc works fine. But when i call the initdb, i get Bad System Call messages. Here is the output: $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -d Running in debug mode. VERSION=9.0beta4 PGDATA=/usr/local/pgsql/data share_path=/usr/local/pgsql/share PGPATH=/usr/local/pgsql/bin POSTGRES_SUPERUSERNAME=postgres POSTGRES_BKI=/usr/local/pgsql/share/postgres.bki POSTGRES_DESCR=/usr/local/pgsql/share/postgres.description POSTGRES_SHDESCR=/usr/local/pgsql/share/postgres.shdescription POSTGRESQL_CONF_SAMPLE=/usr/local/pgsql/share/postgresql.conf.sample PG_HBA_SAMPLE=/usr/local/pgsql/share/pg_hba.conf.sample PG_IDENT_SAMPLE=/usr/local/pgsql/share/pg_ident.conf.sample The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 10 selecting default shared_buffers ... Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) Bad system call (core dumped) 400kB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... Bad system call (core dumped) child process exited with exit code 140 initdb: removing contents of data directory /usr/local/pgsql/data There is no further message in /var/log/messages. First i believed this is an error relating to SYSVSHM-, SYSVSEM-, SYSVMSG-options or User-Id (http://www.freebsddiary.org/jail-multiple.php). But the postgres-user has a user-id which is not used by other postgres-instances in other jails. And the other options are enabled in the root-instance. I also tried to build postgres from a fresh portstree, to make sure, that i have nothing miss-./configured, but there are the same problems. I have no clue, what the problem is. Any hints? Thanks, Torsten -- 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] MySQL versus Postgres
Scott Frankel schrieb: On Aug 6, 2010, at 6:13 AM, Torsten Zühlsdorff wrote: John Gage schrieb: On reflection, I think what is needed is a handbook that features cut and paste code to do the things with Postgres that people do today with MySQL. Everyone of my trainees want such thing - for databases, for other programming-languages etc. It's the worst thing you can give them. The will copy, they will paste and they will understand nothing. Learning is the way to understanding, not copying. I couldn't disagree more. Presenting working code (at least snippets) should continue to be a fundamental part of any documentation project. You missunderstand me. Working code is a fundamental part of any documentation. But we talk about a handbook with code that works in PostgreSQL and does the same thinks in MySQL. This way the trainees won't learn how PostgreSQL works, the just learn the different examples. Giving them training-problems and the PostgreSQL handbook is out of my experience the best way. It tooks longer for them to solve the problems, but in this way they are able to solve problems, which are not related to the presented examples. Greetings from Germany, Torsten -- 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] MySQL versus Postgres
John Gage schrieb: So, perhaps what is needed in any sort of battle with MySQL is an introductory documentation that gives specific examples of how to achieve oh wow! worthwhile results quickly with Postgres. I receive my oh wow when i do the same things in Postgres like in MySQL: Writting some procedures, triggers and use foreign key. The oh wow was that it just *works*. After some years of using MySQL this is a very uncommon feeling, even if you are experienced which MySQL. ;) And after that oh wow you starting reading the manual. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] MySQL versus Postgres
John Gage schrieb: On reflection, I think what is needed is a handbook that features cut and paste code to do the things with Postgres that people do today with MySQL. Everyone of my trainees want such thing - for databases, for other programming-languages etc. It's the worst thing you can give them. The will copy, they will paste and they will understand nothing. Learning is the way to understanding, not copying. Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] Cognitive dissonance
Brian Modra schrieb: Personally I like to use html docs, and it would be good if the documentation were downloadable from the postgresql website in other formats, for convenience... But, what I use is this, which works pretty well: (e.g. to get the 8.1 dosc) mkdir postgresql cd postgresql wget -r -nH -l 10 -k -np http://www.postgresql.org/docs/8.1/interactive/index.html ... then after it all downloads: open the file docs/8.1/interactive/index.html in your web browser. e.g. links docs/8.1/interactive/index.html HTML is text, so you can search using grep e.g. grep -r ALTER TABLE .* ADD COLUMN docs/8.1 Thats the way i do too. A huge pdf is often not very helpful. In my personal case i programm often in a train, using my laptop. Searching a PDF with more than 1.000 pages really hits my battery. With html-files i could preselect the items to search. Also it's possible to import the html-files in a postgres-db and using fulltext-search. ;) Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] 110,000,000 rows
John Gage schrieb: Please forgive this intrusion, and please ignore it, but how many applications out there have 110,000,000 row tables? I recently multiplied 85,000 by 1,400 and said now way Jose. I have two private applications with about 250,000,000 rows a table. I could cluster them, but the performance is good enough. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] 110,000,000 rows
John Gage schrieb: Herbert Simon must be spinning in his grave...or smiling wisely. What does a human do with a petabyte of data? for example i have a private search-engine for my most often used sites. google and the other ones always know just a part of the whole site, my own one knowns all. its a good research-tool (and mirror) and support a lot more filter-posibilities than google. there are many great internet sites out there, which have no search. after waiting for crawling this is no longer a problem for me. another big example in my private use is a neural network for figuring out relations between news and stock-prices. or statistical data of website usage. oh - analyse of the behavior of google is also a great fun with much data. or a database for typical games like chess or poker or something like this. i also have some databases with geo-data or free avaiable data like statistics about birthnumbers in germany, a list of all germany citys with its habitants (grouped by gender) and so on. or calculating a list of prim-numbers on your own just to make some implementation tests. sometime this databases just grow because you want to see how long it can take to get x results and forgot to disable the test after reaching the border :D But when a desktop machine for $1700 retail has a terabyte of storage, the unix operating system, 4 gigs of memory, and an amazing 27 inch display, I guess hardware isn't the problem (and I know one could put together the same machine on Linux etc. for much less). yes and for private use you can use such a desktop machine as database-server. it can work while you're on work ;) Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] PHP and PostgreSQL boolean data type
Thom Brown schrieb: A long-standing problem we've had with PostgreSQL queries in PHP is that the returned data for boolean columns is the string 'f' instead of the native boolean value of false. This problem is solved since nearly 5 years with PDO. You can use an abstraction like DDDBL (see my signature) if you want to save time while using PDO. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] PHP and PostgreSQL boolean data type
Thom Brown schrieb: A long-standing problem we've had with PostgreSQL queries in PHP is that the returned data for boolean columns is the string 'f' instead of the native boolean value of false. This problem is solved since nearly 5 years with PDO. You can use an abstraction like DDDBL (see my signature) if you want to save time while using PDO. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] masking the code
Jasen Betts schrieb: I've wrote a PLPGSQL stored procedure for a DB I've to delivery to my customer. The problem is that I want to hide the code of the stored procedure. I don't want that my customer is able to read the code of the my sp. Do exist a way to mask the code of the store procedure shipped with my DB? rewrite it in a compiled language. And hope, that the customer could not read the result. Greetings, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to return SETOF RECORD?
Hello,
i'm writting some functions for parsing urls and handling strings. But i
have problems with the result set.
I already figured out how to return a single record/row. But i need
more. A good example for what i want is ts_debug();
cse= SELECT alias, token from
ts_debug('http://www.postgresql.org/docs/index.html');
alias | token
--+
protocol | http://
url | www.postgresql.org/docs/index.html
host | www.postgresql.org
url_path | /docs/index.html
I try to get an output like that:
cse= SELECT alias, token from
parse_uri('http://www.postgresql.org/docs/index.html');
alias | token
--+
scheme | http://
url | www.postgresql.org/docs/index.html
host | www.postgresql.org
path | /docs/
file | index.html
But i never get more than one row return. I need a hint how i can return
more than one row. Or even better: a littel example ;)
Thanks very much and greetings from Germany,
Torsten
--
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] Fulltext: problem with english words in german text
Torsten Zühlsdorff schrieb: Hello, i have a problem with understanding fulltext search in PG 8.3. [..] I solved it. I have to specify the language in to_tsquery(). -.- Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] Fulltext: problem with english words in german text
[..] I've just noticed, that i forgot to change the subject. While writing i figured out, that the question is not the one, i want to ask. I am sorry for confusions. Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fulltext: problem with english words in german text
Hello,
i have a problem with understanding fulltext search in PG 8.3.
Example:
CREATE TABLE tfulltext (body text, fulltext tsvector);
INSERT INTO tfulltext VALUES ('title und description sind wichtige
grundlagen', to_tsvector('pg_catalog.german', 'title und description
sind wichtige grundlagen'));
SELECT * from tfulltext;
body |
fulltext
+---
title und description sind wichtige grundlagen | 'titl':1 'wichtig':5
'grundlag':6 'description':3
I expect, that the query:
SELECT * FROM tfulltext WHERE fulltext @@ to_tsquery('title');
Will return the entry. But the result-set is empty. If i use 'titl'
(without 'e') as parameter of to_tsquery it returns the entry.
I try to cast the search-parameter to ts_vector, but it didn't work. How
can i solve the problem?
Thanks for every hint and greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.
--
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] question about performance
A. Kretschmer schrieb: if I have a table, the_table, with a DATE field, i'll call it 'day', and I'd like to find all rows whos day falls within a given month, which of the following methods is faster/costs less: 1. SELECT * FROM the_table WHERE day LIKE '2008-01-%'; 2. SELECT * FROM the_table WHERE ( day BETWEEN '$month_begin' AND '$month_end' ); # where $month_begin is '2008-01-01' and $month_end is '2008-01-31'; Probably the second one, but it depends if you have a propper index. Other solution: create a functional index based on date_trunc and rewrite your where-condition also. Can you give an example? Greetings, Torsten -- 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] Persistent connections in PHP
Hannes Dorbath schrieb: On 14.08.2007 23:13, Dmitry Koterov wrote: Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on each DB connection. Too much pain. We've been using pconnect for exactly the same reason. Though startup time for our dictionary is even higher (around 2 seconds). The problem is that persistent connections in PHP are not clean implemented, they can get randomly garbage collected. The problem seems well known, though I'm unaware of any fix. I think it's best to use pgbouncer and plain connect ATM. Additionally, as mentioned earlier, using pconnect under the Apache webserver is not a good idea at all, at least not with it's current architecture. If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Another advantage of the solution is, that you have one dictionary for all php-childs - so you do not waste memory by loading the dictionary each request. Greetings, Torsten ---(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] Persistent connections in PHP
Hannes Dorbath schrieb: On 15.08.2007 10:53, Torsten Zühlsdorff wrote: If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Uhm, but how does TSearch get it from there? And even if it does, it will still held its own copy? In this case I misunderstood you. I believed, that you get a dictionary from pg and use it. -.- Because often people load a dictionary from db to get the translation of $content, shared memory can make this very fast. My advise based on this assumption. Greetings, Torsten ---(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
