Re: [GENERAL] InitDB: Bad system call
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 8/15/10 1:32 AM, Tom Lane wrote: 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. Regards, - -- Glen Barber -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iQEcBAEBAgAGBQJMZ4e4AAoJEFJPDDeguUajxlAH/0Q7hXCTRnsooq9+Xqs+QPGW Ti77c1D2bcvt3Uq+BdBhbCW6Hx+8kKWPIo8wHG5ca6I5BXnb0ieZftrbPlHUzoNv xnBSAQWWpmL01zt0LOgD2mVrC9b0Q0FUg+ZDXAQCwcZA/FhwA9Vmbf7y+6Eht1JQ 12mSqnAGzuNHvNhMd76+YQPhYo4/5cPQLvH9JKJG7K7CbD9kaP8q9qXoUM4VfcOP NlNMk5huIGBZQVpYYiSPaKeWkjRy4TK5/bubLoRuQ9lYKWfRqDe+3tjqMWk07lyC LJ8hf0cLUV45L0lHXtydQM+mCm0ZN7CgytdyXzt1vVEdfg/flkkf3oxR1aH6ygk= =IpDN -END PGP SIGNATURE- -- 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] Wrong ORDER BY on a numeric value result
Thank you guys! That was the point. --- hubert depesz lubaczewski dep...@depesz.com schrieb am So, 15.8.2010: Von: hubert depesz lubaczewski dep...@depesz.com Betreff: Re: [GENERAL] Wrong ORDER BY on a numeric value result An: Stefan Wild wild...@yahoo.de CC: pgsql-general@postgresql.org Datum: Sonntag, 15. August, 2010 05:53 Uhr On Sat, Aug 14, 2010 at 09:46:37PM +, Stefan Wild wrote: Hello guys, I have following sorting problem and need your help. When executing this SELECT statement: SELECT d.id, d.name, d.description, ts.name, d.opentimestamp, d.initialvalue, d.plmoney, d.performance, d.performancepa, d.currentopenmoney, d.investedmoney, d.investedpercent, d.cashmoney, d.realizedwinmoney, d.realizedlossmoney, d.currenttotalvalue, d.depotriskpercent, d.taxesratepercent, d.taxallowance, d.paidtaxes, d.paidfees FROM c_depots d INNER JOIN c_tradingsystems ts ON d.tradingsystem_id=ts.id INNER JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE u.login='' ORDER BY UPPER(CAST (d.currenttotalvalue AS numeric) ) DESC The column currenttotalvalue has a numeric type with a length of 14 and 2 digits for percision. The initial SELECT didn't used the CAST, but the result was also wrong. column is numeric, but upper() works on text, and returns text, so your numeric column got casted to text by using upper (which is pointless anyway - there is no upper version of digits). remove upper() and you'll be fine. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Wrong ORDER BY on a numeric value result
Stefan Wild wrote on 15.08.2010 10:36: column is numeric, but upper() works on text, and returns text, so your numeric column got casted to text by using upper (which is pointless anyway - there is no upper version of digits). remove upper() and you'll be fine. Thank you guys! That was the point. The real question is: what did you try to accomplish with the UPPER() on a numeric column? Regards Thomas -- 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
On 15 Aug 2010, at 7:32, Tom Lane wrote: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= f...@meisterderspiele.de writes: 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? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c67aeef967631104912678! -- 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] undefined symbol: PQconnectdbParams
When I try using createdb or createlang I get the error: undefined symbol: PQconnectdbParams It's probably finding the wrong library. Which binaries are you using? If you do ldd /path/to/binary, what does it show? Maybe you need to set LD_LIBRARY_PATH to the lib directory of your new postgres install? Regards, Jeff Davis That worked. I added LD_LIBRARY_PATH to my bash.profile and its working fine now. Thanks Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] return setof : alternatives to holder table
Hello List, I have a plpgsql function returning a set of records. The record is effectively a join of some tables. For example, table a (column a1,column a2,column a3,column a4) table b(column b1,column b2,column b4) I am returning a set of (a2,a4,b2). What I do now is to create a empty table foo(column a2,column a4,column b2) then in my function I have record r foo%rowtype I'm not happy with this solution because this foo tables has to be kept around Thanks for any better solution to this mr.wu -- 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] return setof : alternatives to holder table
On 15/08/10 18:00, zhong ming wu wrote: Thanks for any better solution to this CREATE TYPE However, you still have to have a special type around just for that function, and you have to *maintain* it to ensure it always matches the types/columns of the input tables. I frequently wish for type inference in PL/PgSQL functions returning query results, so Pg could essentially create and destroy a type along with the function, allowing you to reference columns in the functions results without having to use RETURNS RECORD and all that AS (column-list) pain. Of course, I don't want it badly enough to put my time where my mouth is and try to code it ;-) . I'm not whining about the current situation, just thinking about ways it could improve further. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] return setof : alternatives to holder table
In response to zhong ming wu : Hello List, I have a plpgsql function returning a set of records. The record is effectively a join of some tables. For example, table a (column a1,column a2,column a3,column a4) table b(column b1,column b2,column b4) I am returning a set of (a2,a4,b2). What I do now is to create a empty table foo(column a2,column a4,column b2) then in my function I have record r foo%rowtype I'm not happy with this solution because this foo tables has to be kept around Thanks for any better solution to this You can create a aown typ or you can use IN/OUT-Parameters. I'm prefering IN/OUT-Parameters, see here: http://www.postgresonline.com/journal/archives/129-Use-of-OUT-and-INOUT-Parameters.html http://bytes.com/topic/postgresql/answers/674690-output-parameters-functions Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] return setof : alternatives to holder table
On Sun, Aug 15, 2010 at 3:10 AM, Craig Ringer cr...@postnewspapers.com.auwrote: On 15/08/10 18:00, zhong ming wu wrote: Thanks for any better solution to this CREATE TYPE However, you still have to have a special type around just for that function, and you have to *maintain* it to ensure it always matches the types/columns of the input tables. I frequently wish for type inference in PL/PgSQL functions returning query results, so Pg could essentially create and destroy a type along with the function, allowing you to reference columns in the functions results without having to use RETURNS RECORD and all that AS (column-list) pain. Of course, I don't want it badly enough to put my time where my mouth is and try to code it ;-) . I'm not whining about the current situation, just thinking about ways it could improve further. How about just using OUT parameters? CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text) RETURNS SETOF record AS BEGIN select col1, col2 from test where id=_id; END; Then your output just has to match the signature of the OUT parameters. And you don't need to define anything when you call it. Mike
Re: [GENERAL] return setof : alternatives to holder table
On 15/08/2010 6:18 PM, Mike Christensen wrote: How about just using OUT parameters? CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text) RETURNS SETOF record AS BEGIN select col1, col2 from test where id=_id; END; Then your output just has to match the signature of the OUT parameters. And you don't need to define anything when you call it. That works - and in fact is what I often do. For a couple of functions I have a little query that re-generates the OUT param lists based on the contents of the INFORMATION_SCHEMA for those tables and dynamically re-creates the function, too. It'd be kind of nice to have ALTERing a table propagate that sort of change to dependent functions so it didn't have to be manually maintained. Given that it doesn't do that for even views at the momement, though, it'd a pretty minor thing, and after development slows down post-release schema don't tend to change that fast anyway. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Relationship between ulimit and effective_cache_size, work_mem, shared_buffers, maintenance_work_mem and vacuum
hi guys, we have a single Ubuntu 10.04 box on which we are going to be running a Postgres 8.4 server, ROR passenger and a solr search server. I was looking at ways to optimize the postgres database and yet limit the amount of memory that it can consume. I am gonna set my shared_buffers to 256mb and work_mem to 12mb, temp_buffers to 20mb (on a 4GB machine). Now, the effective cache size variable seems more of a hint to the query planner, than any hard limit on the database server. Q1. if I add ulimit -m and ulimit -v lines in my postgres upstart files will that be good enough to hard-limit Postgres memory usage ? Q2. once I have decided my max memory allocation (call it MY_ULIMIT) - should effective cache size be set to MY_ULIMIT - 256 - 12 -20 ? round it off to MY_ULIMIT - 512mb maybe Q3. Or will doing something like this play havoc with the query planner/unexplained OOM/crashes ? I ask this because I see that there are other variables that I am not sure, will play nice with ulimit: 1. will this affect the memory usage of vacuum (going to be using default vacuum settings for 8.4) - because ideally I would want to have some control over it as well. 2. Would I have to tune max_connections, max_files_per_process (and any related variables) ? 3. When I turn on WAL, would I have to tune wal_buffers accordingly set effective cache size to account for wal_buffers as well ? thanks -Sandeep
Re: [GENERAL] return setof : alternatives to holder table
On Sun, Aug 15, 2010 at 6:18 AM, Mike Christensen m...@kitchenpc.com wrote: On Sun, Aug 15, 2010 at 3:10 AM, Craig Ringer cr...@postnewspapers.com.au wrote: On 15/08/10 18:00, zhong ming wu wrote: Thanks for any better solution to this CREATE TYPE However, you still have to have a special type around just for that function, and you have to *maintain* it to ensure it always matches the types/columns of the input tables. I frequently wish for type inference in PL/PgSQL functions returning query results, so Pg could essentially create and destroy a type along with the function, allowing you to reference columns in the functions results without having to use RETURNS RECORD and all that AS (column-list) pain. Of course, I don't want it badly enough to put my time where my mouth is and try to code it ;-) . I'm not whining about the current situation, just thinking about ways it could improve further. How about just using OUT parameters? CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text) RETURNS SETOF record AS BEGIN select col1, col2 from test where id=_id; END; Then your output just has to match the signature of the OUT parameters. And you don't need to define anything when you call it. Mike My function loops through some rows and do return next which I think works only with some predefined type. My first pass on trying to make it work with OUT does not work. I don't have that function with me to give a better try though. Here is what I have tried create or replace function te(out a int,out b int) returns setof record as $pgsql$ declare r record; begin r.a := 1; r.b := 2; return next; end; $pgsql$ language plpgsql; -- 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] return setof : alternatives to holder table
On 08/15/2010 07:57 AM, zhong ming wu wrote: Here is what I have tried create or replace function te(out a int,out b int) returns setof record as $pgsql$ declare r record; begin r.a := 1; r.b := 2; return next; end; $pgsql$ language plpgsql; Try: create or replace function te(out a int,out b int) returns setof record as $pgsql$ begin a := 1; b := 2; return next; end; $pgsql$ language plpgsql; contrib_regression=# select * from te(); a | b ---+--- 1 | 2 (1 row) -- or -- create or replace function te() returns TABLE(a int, b int) as $pgsql$ begin a := 1; b := 2; return next; end; $pgsql$ language plpgsql; contrib_regression=# select * from te(); a | b ---+--- 1 | 2 (1 row) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [GENERAL] InitDB: Bad system call
=?ISO-8859-1?Q?Torsten_Z=FChlsdorff?= f...@meisterderspiele.de writes: 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. Now that I'm a bit more awake, I do notice something interesting about that stack trace: the shmctl() is being executed to see whether a shared memory segment ID mentioned in postmaster.pid still exists. This implies that some previous incarnation of the postmaster got as far as writing postmaster.pid, which implies that it successfully executed shmget() and shmat(), and then crashed later. 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. I concur with the upthread suggestion that you need to update your FreeBSD instance. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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] return setof : alternatives to holder table
On Sun, Aug 15, 2010 at 12:21 PM, zhong ming wu mr.z.m...@gmail.com wrote: On Sun, Aug 15, 2010 at 11:57 AM, Joe Conway m...@joeconway.com wrote: On 08/15/2010 07:57 AM, zhong ming wu wrote: Here is what I have tried create or replace function te(out a int,out b int) returns setof record as $pgsql$ declare r record; begin r.a := 1; r.b := 2; return next; end; $pgsql$ language plpgsql; Try: create or replace function te(out a int,out b int) returns setof record as $pgsql$ begin a := 1; b := 2; return next; end; $pgsql$ language plpgsql; contrib_regression=# select * from te(); a | b ---+--- 1 | 2 (1 row) -- or -- create or replace function te() returns TABLE(a int, b int) as $pgsql$ begin a := 1; b := 2; return next; end; $pgsql$ language plpgsql; contrib_regression=# select * from te(); a | b ---+--- 1 | 2 (1 row) The second example is perfect in that I just need to insert my table definition into function definition. Thanks. -- 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
I wrote: ... 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] .psqlrc and custom functions (mysql-like)
Hello 2010/8/14 Sandeep Srinivasa s...@clearsenses.com: hi, As part of a product we are building, we are using postgres as our database. But most of our developers have a (loud) mysql background. I want to potentially provide aliases for a few commonly used mysql functions. I see that the .psqlrc could potentially be the place where you could define these custom functions - I looked at http://okbob.blogspot.com/2009/12/macros-for-epsql.html but it seems to be a patched psql. Is is possible to have the exact same commands as defined in that page in a .psqlrc .. via plpgsql or something ? no, and it will not be a possible in near future. I might be totally way off the target here, so please correct me if I am wrong. As an example, how would I do something as simple as DESCRIBE table (yes I know \d+ equivalent.. I still want to be able to define this function) thanks and regards Sandeep Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
