[GENERAL] Windows 2003 server installation issue
Hello Folks, I am facing an issue starting postgresql using windows service when my user belongs to Administrator group. I applied patch from following thread to 8.4.1 source code and it seems to be working with every windows operating system except Windows 2003 . http://archives.postgresql.org/pgsql-bugs/2009-10/msg00128.php I get an error as below OpenProcessToken : error code 5 when I start as service but if I start postgresql from command line as "pg_ctl start -D " Then it starts up. Please provide some input on this. Thanks & Regards, Vikram
Re: [GENERAL] Windows 2003 server installation issue
On Sat, Aug 14, 2010 at 12:49 AM, Vikram Patil wrote: > Hello Folks, > > > > I am facing an issue starting postgresql using windows service when my > user belongs to Administrator group. > > > > I applied patch from following thread to 8.4.1 source code and it seems > to be working with every windows operating system except Windows 2003 . Is this patch not included in 8.4.4? If so it would be much easier to just upgrade. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] .psqlrc and custom functions (mysql-like)
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 ? 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
Re: [GENERAL] InitDB: Bad system call
Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= 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
=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: > Hm... /path/to/postgres? Not initdb? Yes; it's postgres that is failing, not initdb. > 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? 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
[GENERAL] Delete Trigger
When I update, insert or delete to one table, I need a trigger to delete and possibly insert into another table. The trigger I have works for insert and update, but returns an error when I do a delete. The is no explicit link between the tables, so I can't do 'DELETE CASCADE' or anything like that. Here is the trigger: CREATE OR REPLACE FUNCTION event_tweet() RETURNS TRIGGER AS $textetweet_textetweet$ DECLARE new_id varchar; new_date date; BEGIN DELETE FROM textetweet_textetweet ; IF(TG_OP='INSERT') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; INSERT INTO textetweet_textetweet (date,title,content,link,creation) values (NEW.date,'new event','new event details','/ievent/' || NEW.id, NOW()); END IF; IF(TG_OP='UPDATE') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; INSERT INTO textetweet_textetweet (date,title,content,link,creation) values (NEW.date,'new event','new event details','/ievent/' || NEW.id, NOW()); END IF; IF(TG_OP='DELETE') THEN DELETE FROM textetweet_textetweet WHERE link='/ievent/' || NEW.id; END IF; The problem seems to be the 'NEW.id'. How do I get the deleted id for the trigger? Thanks a lot
[GENERAL] heap-only tuples, and constraints
Hi, Question about heap-only tuples, and constraint checking. Does this statement (UPDATE user SET email='newChangedVal', password='existingVal') requires updating an index on user.password? Or more generally, if an UPDATE includes an explicit but unchanged value for an index column, does postgres need to also update the index entries? Or does HOT apply only when indexed column is missing or not explicit (i.e. password=password)? Along the same line of thought as above, if password is a foreign key column and if an UPDATE includes an explicit but unchanged value for this fk column, does postgres need to check constraint satisfiability? 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] Delete Trigger
Chris Berry writes: > When I update, insert or delete to one table, I need a trigger to delete and > possibly insert into another table. The trigger I have works for insert and > update, but returns an error when I do a delete. There's no "NEW" row in a delete trigger (... and the error message should have told you that pretty explicitly ...). You can look at the OLD row instead. BTW, you should probably think carefully about what the UPDATE case is doing and whether it needs to look at NEW or OLD or a combination. 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] Windows 2003 server installation issue
On Fri, 2010-08-13 at 23:49 -0700, Vikram Patil wrote: > Hello Folks, > > > >I am facing an issue starting postgresql using windows service when > my user belongs to Administrator group. > > > >I applied patch from following thread to 8.4.1 source code and it > seems to be working with every windows operating system except Windows > 2003 . > There is a reason the community provides binary packages. The idea that you are patching 8.4.1 is quite ridiculous. Upgrade to 8.4.4. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Compression on SSL links?
On Fri, Aug 13, 2010 at 10:56 AM, Joshua D. Drake wrote: > On Fri, 2010-08-13 at 11:38 -0400, Tom Lane wrote: >> >> I don't really see that. The case where it's sensible to use >> compression on the connection is where you're pushing data across >> a WAN. That's also pretty much exactly the situation where it's >> sensible to use encryption. I guess there'd be a use case for >> compression-without-encryption if you had a trustworthy private WAN, >> but who's got one of those? >> >> So I'm much more in favor of doing something like this than doing it >> directly in our own protocol. > > Well as a company who implemented compression for postgresql on the wire > long ago :D. I can say it is actually useful as a whole. Even on private > networks. We use a SSH tunnel with compression over a 2MB/s link (IPSEC VPN) from a production site to a DR site. We run slony over it and it works quite well. From what I can tell, average about 95% compression. This effectively gives us a 40MB/s link for replication. We created a Solaris SMF service to maintain the SSH tunnel and a restricted shell on the remote end. SSH connects with a private key. SMF for slony has required dependencies on the local postgresql and the SSH tunnel, so it all starts up and shutdowns down in the proper sequence. The SSH client created one forward port tunnel and one reverse port tunnel, both on localhost. To abuse the tunnel one would have to already be on the database server(s), so it seems (famous last words?) fairly secure. I think that adding compression to the SSL layer in Postgresql's wire protocol would be great. We could eliminate our kludgey tunnel (aka, yet another failure point). Just my $0.02... -- 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
[GENERAL] undefined symbol: PQconnectdbParams
I compiled postgresql beta 4 on a users account and installed it with a
different port into my home directory (~/pg9)
This was recommended by my web host because they refused to give me
plpythonu access on a shared server. (perfectly understandable from
their perspective)
I can get into psql using psql -p{port number} -U{username} postgres
pg_dump works as well.
When I try using createdb or createlang I get the error:
undefined symbol: PQconnectdbParams
Obviously I can get around this problem by going into psql and running
these commands, but I would like my environment set up correctly.
Any thoughts
Sim
--
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
On Sat, 2010-08-14 at 23:18 +0300, Sim Zacks wrote:
>
> I compiled postgresql beta 4 on a users account and installed it with a
> different port into my home directory (~/pg9)
>
> This was recommended by my web host because they refused to give me
> plpythonu access on a shared server. (perfectly understandable from
> their perspective)
>
>
> I can get into psql using psql -p{port number} -U{username} postgres
>
> pg_dump works as well.
>
>
> When I try using createdb or createlang I get the error:
>
> undefined symbol: PQconnectdbParams
>
>
> Obviously I can get around this problem by going into psql and running
> these commands, but I would like my environment set up correctly.
>
>
> Any thoughts
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wrong "ORDER BY" on a numeric value result
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 resulting ordering is wrong: (d.currenttotalvalue) 999,99 99,99 9,99 9,99 100947,51 100251,14 100100 100 100 10 I would expect: 999,99 99,99 100 100 9,99 9,99 100947,51 100251,14 100100 10 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. I'm (still) using the postgres version 8.2. Thanks for your support. -- 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
2010/8/14 Stefan Wild > 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 resulting ordering is wrong: > (d.currenttotalvalue) > 999,99 > 99,99 > 9,99 > 9,99 > 100947,51 > 100251,14 > 100100 > 100 > 100 > 10 > > I would expect: > > 999,99 > 99,99 > 100 > 100 > 9,99 > 9,99 > 100947,51 > 100251,14 > 100100 > 10 > > 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. > > I'm (still) using the postgres version 8.2. > > Thanks for your support. > > > I'm just curious why do you use UPPER() function in the ORDER BY clause? regards Szymon
Re: [GENERAL] Wrong "ORDER BY" on a numeric value result
On 14 August 2010 22:46, 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 resulting ordering is wrong: > (d.currenttotalvalue) > 999,99 > 99,99 > 9,99 > 9,99 > 100947,51 > 100251,14 > 100100 > 100 > 100 > 10 > > I would expect: > > 999,99 > 99,99 > 100 > 100 > 9,99 > 9,99 > 100947,51 > 100251,14 > 100100 > 10 > > 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. > > I'm (still) using the postgres version 8.2. > > Thanks for your support. > I'm confused as to why UPPER appears in your ORDER BY clause. -- Thom Brown Registered Linux user: #516935 -- 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
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] InitDB: Bad system call
=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= 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 ... 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
-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
