Re: [HACKERS] Postgres / plpgsql equivalent to python's getattr() ?
On Aug 4, 2011, at 4:55 AM, Florian Pflug wrote: @OP: Here's my implementation of the feature you desire as a set of C-language functions: https://github.com/fgp/pg_record_inspect. Other people did code up similar things in the past, but I currently cannot find any links to their work. But it little bit digging in the mailing list archives should turn them up. Many thanks, Florian, we'll be checking that out. James ---- James Robinson Socialserve.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres / plpgsql equivalent to python's getattr() ?
Hackers, Python's getattr() allows for dynamic lookup of attributes on an object, as in: inst = MyClass(x=12, y=24) v = getattr(inst, 'x') assert v == 12 Oftentimes in writing data validating trigger functions, it'd be real handy to be able to do a similar thing in plpgsql against column values in a row or record type, such as making use of a trigger argument for hint as what column to consider in this table's case. Oh, to be able to do something like (toy example known to be equivalent to a check): CREATE OR REPLACE FUNCTION must_be_positive() RETURNS TRIGGER AS $$ begin if getattr(NEW, TG_ARGV[0]) <= 0 then raise exception(TG_ARGV[0] || ' must be positive'); end if; -- after trigger return null; end; $$ LANGUAGE PLPGSQL; A function which takes a row + a text column name, and / or a peer function taking row + index within row would really open up plpgsql's expressivity in cases where you're writing mainly SQL stuff, not really wanting to go over to plpythonu or whatnot (whose description of rows are as dicts). Is there something in the internals which inherently prevent this? Or am I fool and it already exists? Not having to defer to EXECUTE would be attractive. James Robinson Socialserve.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres vs. intel ccNUMA on Linux
Hackers, Any tips / conventional wisdom regarding running postgres on large- ish memory ccNUMA intel machines, such as a 32G dual-quad-core, showing two NUMA nodes of 16G each? I expect each postgres backend's non-shared memory usage to remain nice and reasonably sized, hopefully staying within the confines of its processor's local memory region, but how will accesses to shared memory and / or buffer cache play out? Do people tune their backends via 'numactl' ? Furthermore, if one had more than one database being served by the machine, would it be advisable to do this via multiple clusters instead of a single cluster, tweaking the processor affinity of each postmaster accordingly, trying to ensure each cluster's shared memory segments and buffer cache pools remain local for the resulting backends? Thanks! James Robinson Socialserve.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER TABLE ... DISABLE TRIGGER vs. AccessExclusiveLock
Hackers, Experience and a read through backend/commands/tablecmds.c's AlterTable() indicate that ALTER TABLE ... DISABLE TRIGGER obtains an exclusive lock on the table (as does any ALTER TABLE). Blocking other readers from a table when we've, within the body of a transaction performing a bulk update operation where we don't want / need triggers to fire, seems at first glance to be over-kill. I can see how AlterTable()'s complex logic is made less complex through 'get and keep a big lock', since most of its operational modes really do need exclusive access, but is it strictly required for ... DISABLE / REENABLE TRIGGER? Could, say, RowExclusiveLock hypothetically provide adequate protection, allowing concurrent reads, but blocking out any other writers (for ENABLE / DISABLE TRIGGER) -- such as if driven through a new statement other than ALTER TABLE -- such as "DISABLE TRIGGER foo ON tbar" ? Thanks! James Robinson Socialserve.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] NOTIFY / LISTEN silently parses and discards schema-ish portion of notification name ...
On Apr 16, 2009, at 6:51 AM, pgsql-hackers-ow...@postgresql.org wrote:
Considered unexpected behavior, or at least in its undocumented form.
If value given to NOTIFY seems schema-qualified, the schema
qualification is eroded by the time it is presented to the listener
--
See:
http://archives.postgresql.org//pgsql-patches/2007-02/msg00437.php
Pretty sure 8.4 will not use "relation".
Thanks for pointer on a proposed patch, Greg. That patch looks like it
hasn't been applied to the 8.2 maintenance stream.
Looks like in 8.3 the grammar changed the name argument to ColId
production, which goes unparsed. Shame on me for using such an old
version [ 8.2 ], but hey, it works.
James Robinson
Socialserve.com
[HACKERS] NOTIFY / LISTEN silently parses and discards schema-ish portion of notification name ...
Considered unexpected behavior, or at least in its undocumented form. If value given to NOTIFY seems schema-qualified, the schema qualification is eroded by the time it is presented to the listener -- the [ nonexistent ] schema-ish-looking 'foo.' portion of 'foo.bar' is not presented at all to the listening end -- just 'bar' - $ psql Welcome to psql 8.2.11, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit social=# listen foo.bar; LISTEN social=# notify foo.bar; NOTIFY Asynchronous notification "bar" received from server process with PID 5663. social=# \q --- I expect this behavior is for the benefit of notify / listen users who happen to pass table name values over and / or when postgres became schema aware -- are listen condition names implicitly separated by schemas [ but if so, why would a listen for a schema-qualified name 'foo.bar' succeed when schema 'foo' does not exist? Create table certainly wouldn't. ] The docs for listen / notify don't mention any sort of parsing / value filtering of the notification signal value if it smelt schema qualified, just that a common use is for it to hold a table name. I wandered into this surprise by holding a dotted constant shared between my notifier and my listener [ who listens for a few different types of events, separated by notification names ], but the listener didn't receive the expected string with schema qualification, it got the eroded value instead -- easily worked around by not using constants containing dotted strings, but this was found to be surprising. Aah -- gram.y shows LISTEN / NOTIFY taking a qualified_name production as their argument, and it seems to split up a dotted name into schema / relname subcomponents. Probably least effort to have the docs mention listen / notify values containing periods are eroded to their relname portion, and that > 2 dots == death: social=# listen foo.bar.blat.blam; ERROR: improper qualified name (too many dotted names): foo.bar.blat.blam Thanks! James Robinson Socialserve.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug in 8.2B1 plpgsql ...
Seems that plpgsql in 8.2B1 thinks that selects of the form ' and foo not in (select ... )' should be function calls, not subselects. These worked fine in 8.1. Here's a smallish script which reproduces the problem on 8.2RC1 / OSX: If you comment out the 'and NEW.id not in (select t1_id from skip_t1_ids) ' clause in the trigger, then the script completes. social=# \i 8.2.bug.sql BEGIN psql:8.2.bug.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE CREATE TABLE CREATE SEQUENCE CREATE TABLE CREATE FUNCTION CREATE TRIGGER INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 psql:8.2.bug.sql:52: ERROR: cache lookup failed for function 0 CONTEXT: SQL statement "SELECT $1 <> $2 and $3 not in (select t1_id from skip_t1_ids)" PL/pgSQL function "track_t1_changes" line 2 at if ROLLBACK 8.2.bug.sql Description: Binary data James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Webcluster session storage, was vacuum, performance, and MVCC
Wishlist 6 and 7 items remind me more and more of AFS or NFS4 client / server interaction. Not trivial unfortunately. I hate to say it, but would mysql / myisam not work well for points 1-5 ? I have not idea about it and 'fast as hell' -- not ever run it in production for anything. 6 + 7 could possibly be done atop mysql using a 3-tier model. James Robinson Socialserve.com ---(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: [HACKERS] Pl/Python -- current maintainer?
I see neilc has hacked on it very recently to reduce memory leaks. I take that as both good and bad signs. We're a [ small ] python shop, and would be most interested in being able to simplify our life through doing some things in plpython instead of pl/pgsql where appropriate. Keeping our constants and so forth in the appropriate python module would make things ever so much simpler here and there at the very least. But we've never hacked on the backend, nor at the C python API level. But I see no reason why not to start now -- lurked here for many a year. For example, I see that plpython functions cannot be declared to return void. That can't be too tough to remedy. Implementing the DBI 2.0 API interface to SPI can wait another day. On Feb 24, 2006, at 11:08 PM, Joshua D. Drake wrote: James Robinson wrote: I'm interested in poking though and taking a shot at getting my feet wet with pl/python. I see the file is copyright Andrew Bosma -- is he still around perhance? Is anyone currently the 'owner' ? To my knowledge there is no current maintainer of plPython and it definitely needs some love. James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ James Robinson Socialserve.com ---(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
[HACKERS] Pl/Python -- current maintainer?
I'm interested in poking though and taking a shot at getting my feet wet with pl/python. I see the file is copyright Andrew Bosma -- is he still around perhance? Is anyone currently the 'owner' ? James Robinson Socialserve.com ---(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: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
On Nov 28, 2005, at 4:13 PM, Tom Lane wrote: Yeah, could be. Anyway it doesn't seem like we can learn much more today. You might as well just zing the vacuumdb process and let things get back to normal. If it happens again, we'd have reason to dig deeper. Final report [ and apologies to hackers list in general -- sorry for the noise today ]. Killed the vacuumdb frontend. Then went off killing processes spawned by cron on Nov25th related to the cronjob. All of the related backends exited peacefully, and all is well. Manual vacuum verbose analyze completes successfully. One possibly curious thing -- one final process remains on the backup box dated Nov25: root 19912 3 0 Nov25 ?00:00:12 [pdflush] Coincidence? This is some sort of kernel thread, right? Flushes dirty pages to disk? There are two on this machine: root 9211 3 0 Nov22 ?00:02:56 [pdflush] root 19912 3 0 Nov25 ?00:00:12 [pdflush] The Nov25'ths pdflush's pid is suspiciously close to the pids which would be in use around the beginning of the cron'd process. [ checks / var/log/messages ... ] -- yep -- real close -- last known cross- referencable pid is: Nov 25 04:59:01 db02 /usr/sbin/cron[20590]: (root) CMD ( rm -f /var/ spool/cron/lastrun/cron.hourly) and the vacuumdb sshd connection on the production db box is logged at 05:02:22 AM, so that pdflush would have been started real close to the time which the remote backup + vacuum script would have been running. Any Linux 2.6 gurus lurking? Under what circumstances do pdflush'es get spawned? The filesystem upon which the outputs were going is a software raid partition (raid-0? raid-1? Always confuse the two) -- the interleaved one anyway, not mirrored -- formatted reiser3. Neither pdflush instance on this machine was started anywhere near the boot time of the machine -- both much later. Whereas on the production box the two pdflush instances are both dated from machine boot time. Does this perchance indicate unhappiness afoot perhaps hardware-wise? James Robinson Socialserve.com ---(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: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
On Nov 28, 2005, at 1:46 PM, Tom Lane wrote: James Robinson <[EMAIL PROTECTED]> writes: backtrace of the sshd doesn't look good: Stripped executable :-( ... you won't get much info there. What of the client at the far end of the ssh connection? You should probably assume that the blockage is there, rather than in a commonly used bit of software like ssh. Ok: cron fired off a bash running our script which performs the backup + vacuuming on the backup box side, and that script was at the point of driving vacuumdb sscadmin 20612 20610 0 Nov25 ?00:00:00 bash /usr/local/bin/ db_backup.sh sscadmin 20622 20612 0 Nov25 ?00:00:00 ssh -c blowfish [ ssh identity file + host edited out ] /usr/local/pgsql/bin/vacuumdb -U postgres --all --analyze --verbose [ yes, verbose vacuum. Who knew that'd be the camel-breaking straw ??! ] The lines in the script invoking the ssh'd vacuumdb is: -- # Vacuum all databases, storing log results. $SSHCMD $DBHOST /usr/local/pgsql/bin/vacuumdb -U postgres --all -- analyze --verb ose >& $DATE/vacuum.log -- Unfortunately the dir holding that date + hour's vacuum.log was swept away by the next day's activities. The stuck bash is backtraceable: (gdb) bt #0 0xe410 in ?? () #1 0xb928 in ?? () #2 0x in ?? () #3 0xb918 in ?? () #4 0xb7ed1513 in __waitpid_nocancel () from /lib/tls/libc.so.6 #5 0x080935bf in default_tty_job_signals () #6 0x080949ca in wait_for () #7 0x0808acd7 in execute_command_internal () #8 0x0808a4f0 in execute_command () #9 0x0808241d in reader_loop () #10 0x08081364 in main () (gdb) Nothing unexpected there. Funny though, file reports /bin/bash as being stripped ( as it does for /usr/bin/ssh and /usr/sbin/sshd ), but I could get far better debugging support from it. Could the debugging issue be ssh / sshd's apparent multithreadedness: gdb /usr/bin/ssh GNU gdb 6.2.1 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 "i586-suse-linux"...(no debugging symbols found)...Using host libthread_db library "/lib/tls/libthread_db.so.1". (gdb) run localhost Starting program: /usr/bin/ssh localhost (no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...[Thread debugging using libthread_db enabled] [New Thread 1078408704 (LWP 29932)] (no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...(no debugging symbols found)...The authenticity of host 'localhost (127.0.0.1)' can't be established. RSA key fingerprint is f4:cd:bc:37:d7:08:bc:4f:04:91:45:9b:44:cf:d5:b9. Are you sure you want to continue connecting (yes/no)? Program received signal SIGINT, Interrupt. [Switching to Thread 1078408704 (LWP 29932)] 0xe410 in ?? () (gdb) bt #0 0xe410 in ?? () #1 0xbfffb9e8 in ?? () #2 0x0001 in ?? () #3 0xbfffb3af in ?? () #4 0x402f94b3 in __read_nocancel () from /lib/tls/libc.so.6 #5 0x080742e1 in mkstemp64 () #6 0x080684c8 in error () #7 0x08054e91 in ?? () #8 0xbfffcac0 in ?? () #9 0x0001 in ?? () #10 0x08096230 in ?? () #11 0xbfffcac0 in ?? () I know you have far better things to do than teach someone how to use gdb on multithreaded programs, but could a proper backtrace be salvageable on the ssh client? If you really care, that is, otherwise I'm off to kill that vacuumdb client. At this moment in time, should we kill off the offending processes from Nov 25 -- starting from client-most side all the way to the vacuumdb process on the production server. The other vacuums would probably then complete happily, and we'd be cool again, eh? If you just want to get out of it, killing the vacuumdb should be the least dangerous way to get out of the problem. I'd suggest taking a little more time to try to find out what's stuck though. Given the other culprits in play are bash running a straightforward shellscript line with redirected output to a simple file on a non- full filesystem, I'm leaning more towards the odds that
Re: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
On Nov 28, 2005, at 12:00 PM, Tom Lane wrote: Your next move is to look at the state of sshd and whatever is running at the client end of the ssh tunnel. backtrace of the sshd doesn't look good: (gdb) bt #0 0xe410 in ?? () #1 0xbfffdb48 in ?? () #2 0x080a1e28 in ?? () #3 0x080a1e78 in ?? () #4 0xb7d379fd in ___newselect_nocancel () from /lib/tls/libc.so.6 #5 0x08054d64 in ?? () #6 0x000a in ?? () #7 0x080a1e78 in ?? () #8 0x080a1e28 in ?? () #9 0x in ?? () #10 0xbfffdb30 in ?? () #11 0x in ?? () #12 0xbfffdb48 in ?? () #13 0x0806c796 in ?? () #14 0x080a9d3c in ?? () #15 0x0001 in ?? () #16 0xbfffdb64 in ?? () #17 0x08054c3d in ?? () #18 0x0019 in ?? () #19 0x000acda0 in ?? () #20 0x080a9d3c in ?? () #21 0x in ?? () #22 0xbfffdb6c in ?? () #23 0x in ?? () #24 0xbfffdb78 in ?? () ---Type to continue, or q to quit--- #25 0x08055632 in ?? () #26 0xbfffdb6c in ?? () #27 0x in ?? () #28 0x080a1e78 in ?? () #29 0x08098ee8 in ?? () #30 0x080a1e78 in ?? () #31 0x080a1e28 in ?? () #32 0x0009 in ?? () #33 0x0004 in ?? () #34 0x0001 in ?? () #35 0x0001 in ?? () #36 0xbfffdbb8 in ?? () #37 0x0805b816 in ?? () #38 0x08098ee8 in ?? () #39 0x080a2e10 in ?? () #40 0x0007 in ?? () #41 0x08098ee8 in ?? () #42 0x08080fd2 in _IO_stdin_used () #43 0x08098ee8 in ?? () #44 0xbfffdbb8 in ?? () #45 0x080574a3 in ?? () #46 0x in ?? () #47 0x08098ee8 in ?? () #48 0x08098ee8 in ?? () #49 0x08098f30 in ?? () ---Type to continue, or q to quit--- #50 0x08080fd2 in _IO_stdin_used () #51 0x08098ee8 in ?? () #52 0xbfffeb98 in ?? () #53 0x0804fc90 in ?? () #54 0x08098ee8 in ?? () #55 0x08098f74 in ?? () #56 0x08098f30 in ?? () #57 0xbfffe110 in ?? () #58 0xbfffe110 in ?? () #59 0x0808014a in _IO_stdin_used () #60 0xb7ffad95 in malloc () from /lib/ld-linux.so.2 Previous frame inner to this frame (corrupt stack?) The client-side ssh is worse -- 507 frames before it reports '(corrupt stack?)'. At this moment in time, should we kill off the offending processes from Nov 25 -- starting from client-most side all the way to the vacuumdb process on the production server. The other vacuums would probably then complete happily, and we'd be cool again, eh? I suppose we're darn lucky the process got ultimately gummed up on a table that sees no traffic at all to it, eh? The lock that vacuum has taken out on it would prevent at least some things happening to the table in question -- possibly even new inserts or updates? Could this potentially be alleviated in the future by a little code reordering in vacuumdb or postmaster by completing working on the current table completely before emitting output, either postmaster -> vacuumdb client, or possibly the vacuumdb client -> whatever stdout is directed to so as to get gummed up in a state when no locks are being held? Or would that uglify the code too much and/or people would find that additional buffering a damnable offense? James Robinson Socialserve.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
On Nov 28, 2005, at 11:38 AM, Tom Lane wrote: Can you get a similar backtrace from the vacuumdb process? (Obviously, give gdb the vacuumdb executable not the postgres one.) OK: (gdb) bt #0 0xe410 in ?? () #1 0xbfffe4f8 in ?? () #2 0x0030 in ?? () #3 0x08057b68 in ?? () #4 0xb7e98533 in __write_nocancel () from /lib/tls/libc.so.6 #5 0xb7e4aae6 in _IO_new_file_write () from /lib/tls/libc.so.6 #6 0xb7e4a7e5 in new_do_write () from /lib/tls/libc.so.6 #7 0xb7e4aa63 in _IO_new_file_xsputn () from /lib/tls/libc.so.6 #8 0xb7e413a2 in fputs () from /lib/tls/libc.so.6 #9 0xb7fd8f99 in defaultNoticeProcessor () from /usr/local/pgsql/lib/ libpq.so.4 #10 0xb7fd8fe5 in defaultNoticeReceiver () from /usr/local/pgsql/lib/ libpq.so.4 #11 0xb7fe2d34 in pqGetErrorNotice3 () from /usr/local/pgsql/lib/ libpq.so.4 #12 0xb7fe3921 in pqParseInput3 () from /usr/local/pgsql/lib/libpq.so.4 #13 0xb7fdb174 in parseInput () from /usr/local/pgsql/lib/libpq.so.4 #14 0xb7fdca99 in PQgetResult () from /usr/local/pgsql/lib/libpq.so.4 #15 0xb7fdcc4b in PQexecFinish () from /usr/local/pgsql/lib/libpq.so.4 #16 0x0804942c in vacuum_one_database () #17 0x080497a1 in main () Things to know which could possibly be of use. This cron is kicked off on the backup database box, and the vacuumdb is run via ssh to the primary box. The primary box is running the vacuumdb operation with --analyze --verbose, with the output being streamed to a logfile on the backup box. Lemme guess __write_nocancel calls syscall write, and 0x0030 might could well be the syscall entry point? Something gumming up the networking or sshd itself could have stopped up the ouput queues, and the backups populated all the way down to this level? If so, only dummies backup / vacuum direct to remote? James Robinson Socialserve.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
Here ya go -- BTW -- your guys support is the _best_. But you know that already: [EMAIL PROTECTED]:/home/sscadmin> gdb /usr/local/pgsql/bin/postgres 19244 GNU gdb 6.2.1 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 "i586-suse-linux"...Using host libthread_db library "/lib/tls/libthread_db.so.1". Attaching to program: /usr/local/pgsql/bin/postgres, process 19244 Reading symbols from /lib/libz.so.1...done. Loaded symbols for /lib/libz.so.1 Reading symbols from /lib/libreadline.so.5...done. Loaded symbols for /lib/libreadline.so.5 Reading symbols from /lib/libcrypt.so.1...done. Loaded symbols for /lib/libcrypt.so.1 Reading symbols from /lib/libresolv.so.2...done. Loaded symbols for /lib/libresolv.so.2 Reading symbols from /lib/libnsl.so.1...done. Loaded symbols for /lib/libnsl.so.1 Reading symbols from /lib/libdl.so.2...done. Loaded symbols for /lib/libdl.so.2 Reading symbols from /lib/tls/libm.so.6...done. Loaded symbols for /lib/tls/libm.so.6 Reading symbols from /lib/tls/libc.so.6...done. Loaded symbols for /lib/tls/libc.so.6 Reading symbols from /lib/libncurses.so.5...done. Loaded symbols for /lib/libncurses.so.5 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /lib/libnss_compat.so.2...done. Loaded symbols for /lib/libnss_compat.so.2 Reading symbols from /lib/libnss_nis.so.2...done. Loaded symbols for /lib/libnss_nis.so.2 Reading symbols from /lib/libnss_files.so.2...done. Loaded symbols for /lib/libnss_files.so.2 0xe410 in ?? () (gdb) bt #0 0xe410 in ?? () #1 0xbfffd508 in ?? () #2 0x082aef97 in PqSendBuffer () #3 0xbfffd4f0 in ?? () #4 0xb7ec03e1 in send () from /lib/tls/libc.so.6 #5 0x08137d27 in secure_write () #6 0x0813c2a7 in internal_flush () #7 0x0813c4ff in pq_flush () #8 0x0820bfec in EmitErrorReport () #9 0x0820b5ac in errfinish () #10 0x0811d0a8 in lazy_vacuum_rel () #11 0x0811ac5a in vacuum_rel () #12 0x0811bb93 in vacuum () #13 0x0819c84d in PortalRunUtility () #14 0x0819d9b8 in PortalRun () #15 0x0819b221 in PostgresMain () #16 0x0816ffa9 in ServerLoop () #17 0x08170de9 in PostmasterMain () #18 0x0813e5e5 in main () (gdb) quit James Robinson Socialserve.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
As fate would have it, the vacuumdb frontend and backend which were initially afflicted are still in existence: sscadmin 19236 19235 0 Nov25 ?00:00:00 /usr/local/pgsql/bin/ vacuumdb -U postgres --all --analyze --verbose postgres 19244 3596 0 Nov25 ?00:00:02 postgres: postgres social [local] VACUUM pid 19244. And here's pg_locks: social=# select * from pg_locks; relation | database | transaction | pid | mode | granted --+--+-+---+-- +- | |38790657 | 19244 | ExclusiveLock| t 6586066 | 6585892 | | 28406 | ShareUpdateExclusiveLock | f | |39097312 | 28861 | ExclusiveLock| t | |39089744 | 28756 | ExclusiveLock| t 6586066 | 6585892 | | 28756 | ShareUpdateExclusiveLock | f 6586066 | 6585892 | | 19244 | ShareUpdateExclusiveLock | t 6586066 | 6585892 | | 19244 | ShareUpdateExclusiveLock | t 8417138 | 6585892 | | 19244 | ShareUpdateExclusiveLock | t 16839 | 6585892 | | 28861 | AccessShareLock | t | |39063661 | 28560 | ExclusiveLock| t | |39056736 | 28406 | ExclusiveLock| t 6586066 | 6585892 | | 28560 | ShareUpdateExclusiveLock | f (12 rows) pid --- 19244 28406 28560 28756 28861 (5 rows) Of those 5 pids: 19244 -- vaccuum backend initally afflicted -- status in argv: 'postgres: postgres social [local] VACUUM' 28406 -- a 10AM today vacuum started up by cron this morning after I got things half-way working again early in the diagnosis of this situation. args: 'postgres: postgres social [local] VACUUM waiting' 28560 -- a 10:16 today by-hand vacuum session futher in diagnosis land. args: 'postgres: postgres social [local] VACUUM waiting' 28756 -- 11AM cron'd process. Yes, I see a quickly mounting issue here. args: 'postgres: postgres social [local] VACUUM waiting' 28861 -- production servicing backend, now back in idle state. [ not in tx idle by regular idle ]. On Nov 28, 2005, at 11:09 AM, Tom Lane wrote: James Robinson <[EMAIL PROTECTED]> writes: Comparing the logs further with when it did complete, it seems that one table in particular (at least) seems afflicted: social=# vacuum verbose analyze agency.swlog_client; hangs up forever -- have to control-c the client. Likewise for w/o 'analyze'. Given that it's not eating CPU time, one would guess that it's blocked waiting for a lock. Can you find any relevant locks in pg_locks? regards, tom lane James Robinson Socialserve.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Help: 8.0.3 Vacuum of an empty table never completes ...
G'day folks. We have a production database running 8.0.3 which gets fully pg_dump'd and vacuum analyze'd hourly by cron. Something strange happened to us on the 5AM Friday Nov. 25'th cron run -- the: /usr/local/pgsql/bin/vacuumdb -U postgres --all --analyze --verbose >& $DATE/vacuum.log step in our cron procedure never completed. Strange, since no known event of note happened on Friday since we were all out of the office past Wed. for the american Thanksgiving holiday. Anyway, running the vacuum line by hand shows it getting stuck -- processes the majority of our tables, then just stops, and the backend postmaster just stops accumulating CPU time. Comparing the logs further with when it did complete, it seems that one table in particular (at least) seems afflicted: social=# vacuum verbose analyze agency.swlog_client; hangs up forever -- have to control-c the client. Likewise for w/o 'analyze'. pg-dump'ing the entire database works (phew!) and upon restoring on a backup box, said table can be vacuum'd: social=# vacuum verbose analyze agency.swlog_client; INFO: vacuuming "agency.swlog_client" INFO: index "swlog_client_pkey" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "swlog_client": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "agency.swlog_client" INFO: "swlog_client": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows VACUUM That's right -- completely empty table -- which is what we actually expect. How should we proceed such that we can learn from this as well as we can proceed and get our entire database vacuuming again successfully? Running on Linux 2.6.8-24.18-smp (SuSE 9.2). No juicy filesystem- related messages in dmesg nor /var/log/messages. 11% disk used on the postgres-related partition. The table in question is defined as: social=# \d agency.swlog_client; Table "agency.swlog_client" Column | Type | Modifiers ++--- swlog | bigint | not null client | bigint | not null Indexes: "swlog_client_pkey" PRIMARY KEY, btree (swlog, client) Foreign-key constraints: "$2" FOREIGN KEY (client) REFERENCES agency.client(id) "$1" FOREIGN KEY (swlog) REFERENCES agency.swlog(id) And the two fk'd tables: social=# select count(*) from agency.client; count --- 0 (1 row) social=# select count(*) from agency.swlog; count --- 69 We doubt that there could be any strange oddball extremely longrunning transaction in any of those related tables gumming up this table. Finally, the only possibly potentially interesting event database- wise happened on Wed. Nov. 23'rd -- we SIGHUP'd the postmaster to have it learn a higher value for work_mem (10240, up from default of 1024). But the hourly crons went great for the subsequent two days. maintenance_work_mem is still at the default of 16384. Many thanks in advance! James James Robinson Socialserve.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS]
> After reconnecting to database all is ok. Is it supposed behaviour? Yes. The plpgsql interpreter in the backend directs the backend to prepare and cache every sql statement in the function. The planned statements reference oids of the tables referenced. Dropping the connection gets rid of the (now invalid) cached plan. People have proposed some sort of reparse / replan command, but I don't know if they could gain consensus. It'd be really fancy if plpgsql could sniff into the plan structure of each planned query, looking for table oids, and then registering itself as being a dependent object of that table, so that upon table drop the planned function body could be abandoned, and upon next call to the function, hopefully the table might have been recreated, and then the function gets planned successfully again. Or, if the table does not exist yet, then it just fails as normal. James Robinson Socialserve.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Error handling in plperl and pltcl
On Dec 3, 2004, at 2:04 PM, Jan Wieck wrote: [snip] The point we where coming from was Tom's proposal to wrap each and every single SPI call into its own subtransaction for semantic reasons. My proposal was an improvement to that with respect to performance and IMHO also better matching the semantics. Your suggestion to expose a plain savepoint interface to the programmer leads directly to the possiblity to commit a savepoint made by a sub-function in the caller and vice versa - which if I understood Tom correctly is what we need to avoid. The JDBC interface exposes the savepoint interface, via setSavepoint(), releaseSavepoint(), and rollback(Savepoint sp) methods on the Connection, and Thomas's design of PL/Java offers the SPI via mapping it onto JDBC. Would client-side JDBC also suffer from the same potential issue of 'commit a savepoint made by a sub-function'? Or is this something SPI-specific? Or, finally, is this an issue of interacting with other PL languages who won't expose savepoint-ish functionality? IMO, if it smells like JDBC, it oughta smell as close to 100% like JDBC, allowing folks to possibly relocate some of their code to run inside PG. Ugly savepoint handling and all. James Robinson Socialserve.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Encrypt data type LO
We do all of our encryption in the middleware: 1) translate our data which requires encryption into an XML string 2) compress + encrypt, yielding byte []. 3) Store byte [] as a bytea column. The resulting byte arrays are relatively small in our case (1 -> 3K), so bytea has seemed to suit us just fine. ---- James Robinson Socialserve.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] plpgsql on 8.0b4 bug?
Between ugly #1 and ugly #2, I'd think that a wart teaching it that 'ELSEIF' is not a valid manner to start a statement (i.e. following a semicolon) would be preferable. Allowing us hacks to write functions containing both spellings makes the language look poor since it ought to slap us into formal shape. This isn't [insert a lossy SQL implementation or slop-inspiring scripting language here]. We're typesafe and syntax checking! On Nov 18, 2004, at 12:09 PM, Tom Lane wrote: It occurs to me that one simple thing we could do is make plpgsql accept both ELSIF and ELSEIF as legal spellings of the keyword. This seems a bit ugly but I can't think of any really good objections. James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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: [HACKERS] plpgsql on 8.0b4 bug?
Hmm. Teach me to not send in the exact script / dataset that caused the issue. Indeed, I must have (luckily) had a separate syntax error in the version that caused the 7.4.6 parser to trip up on the ELSEIF line (it did happen else I'd still be staring at the thing). Humble apologies and thanks. James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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
[HACKERS] plpgsql on 8.0b4 bug?
It seems that 8.0B4's plpgsql does not diagnose 'ELSIF' being misspelled as 'ELSEIF' nearly as readily as did 7.4.x. 7.4 emits a compile error at the right place, whereas 8.0b4 seems to treat misspelled 'ELSEIF' blocks as unreachable code, making it quite hard to diagnose what is actually amiss. If not plpgsql coding often, the difference in spelling can be rather hard to see even when staring at the docs. Attached is a sample script used to massage data in a table from a bad representation to a little better one (with misspellings in place). Here's a minimal table definition to run it: create table unit ( id int8 not null primary key, pets boolean not null, petscondition text ); insert into unit values (1, true, 'Outside Only'); 8.0B4 results: [dynamic-94:~/cvs/social/misc-db-utils] jlrobins% psql < pets.sql BEGIN ALTER TABLE CREATE FUNCTION ERROR: No code determined for unit 1, t, "Outside Only" ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK 7.4.6 results: xs2 /tmp> psql ssc_sav < pets.sql BEGIN ALTER TABLE CREATE FUNCTION ERROR: syntax error at or near "ELSEIF" at character 1 CONTEXT: PL/pgSQL function "fix_pets" line 16 at SQL statement ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block COMMIT Many thanks in advance, James pets.sql Description: Binary data James Robinson Socialserve.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Backend 8.0.0B4 crash on SELECT ...
Patch applied, fixes beta4 for the query with our data. Many thanks again! James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Backend 8.0.0B4 crash on SELECT ...
tion | text | amenities | text | parkingcomment| text | qualifiers| text | upgrades_addons | text | lastmodusername | text | sliding_qualifiers| text | Indexes: "unit_pkey" PRIMARY KEY, btree (id) "unit_building" btree (building, represents) "unit_forsale_search" btree (city, forsaleprice) WHERE forsaleprice > 0 "unit_rental_search" btree (city, status, belowhudfmr, "delete") WHERE statu s = 2 AND belowhudfmr = true AND "delete" = false "unit_sys_disabled" btree (building, status) WHERE status = 8 Foreign-key constraints: "unit_building_fkey" FOREIGN KEY (building) REFERENCES building(id) "unit_city_fkey" FOREIGN KEY (city) REFERENCES housingcity(id) "unit_leadpaintunit_fkey" FOREIGN KEY (leadpaintunit) REFERENCES leadpaintun it(id) "unit_waitinglist_id_fkey" FOREIGN KEY (waitinglist_id) REFERENCES waiting_l ist(id) "unit_zipcode_fkey" FOREIGN KEY (zipcode) REFERENCES zipcode(id) James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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: [HACKERS] Postgres performs a Seq Scan instead of an Index Scan!
On Oct 26, 2004, at 12:12 PM, Jos van Roosmalen wrote: ATTR1 INT8 Looks like your column is int8, yet your query is sending in an int4. Therefore the index is not used. This is fixed in PG 8.0. In the mean time, you can: SELECT * FROM TESTTABLE WHERE ATTR1=1::INT8 ... which explicitly casts the literal int4 to an int8, making the int8 column index useable. James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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
[HACKERS] Thank you ...
Postgresql Hackers, Just a quick but heartfelt "Thank You!" to all of you who make postgresql as sturdy as it is. Noon today, our datacenter provider suffered an, um, interruption in their uninterruptable redundant power supply systems. Power was restored seconds later, and this is what the postmaster had to say about it: LOG: database system was interrupted at 2004-10-08 11:55:47 EDT FATAL: the database system is starting up LOG: checkpoint record is at 1C/2A4CF040 LOG: redo record is at 1C/2A4CF040; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 16776013; next OID: 3686077 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 1C/2A4CF080 LOG: record with zero length at 1C/2A521034 LOG: redo done at 1C/2A521010 FATAL: the database system is starting up FATAL: the database system is starting up LOG: database system is ready We were back online within minutes of the interruption w/o any data loss. So, I raise my glass to you! Thank you! ---- James Robinson Socialserve.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Statement parsing problem ?
On Sep 15, 2004, at 9:43 AM, Chris Dunlop wrote: Either that, or I'm missing something... From the SELECT docs ... A JOIN clause combines two FROM items. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items. CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE. --- Since you're doing a simple join, you'd be better off using form select 1 as "OK" from t1, t2, t3, t4 on where t4.foo6 = t3.foo5 and t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; and then you can vary the order of the and clauses any way you like. But using the "FROM t1, t2, t3 JOIN t4" form binds left-to-right tigher than the comma separated list, so it is operating on exactly two tables (t3 and t4), not the t1, t2, t3 cartesian product joined with t4. James Robinson Socialserve.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] User Quota Implementation
On Jul 9, 2004, at 12:04 PM, Jonah H. Harris wrote: - Quota acts on any object owned by the user. Is this adequate for everyone? Does changing owner also trigger new quota calculations on both the new and old owner? Is there any additional functionality you would like to see in a quota implementation? Quotas per user per tablespace, assuming 7.5 gets tablespaces. User quotas would make postgres on a shared university box much more pleasant. James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] bug in GUC
On Jun 24, 2004, at 10:45 AM, Thomas Hallgren wrote: So, what you are saying is that there's no need for the functions I suggested and that a palloc using the TopMemoryContext will guarantee correct behavior on "out of memory"? Perhaps a section regarding proper memory management code in the backend could be written , say, somewhere in the internals document around the coding conventions chapter: http://developer.postgresql.org/docs/postgres/source.html I myself don't have a clue, not being a backend hacker, so I'll just slink back to my cave. James Robinson Socialserve.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PREPARE and transactions
[ all snipped ] A problem with redefining the lifetime of a PREPARE'd statement according to if it was defined within an explicitly managed transaction or not would be with middlewares such as J2EE / EJB containers. The container / JDBC driver performs most operations within explicitly managed transactions *by the middleware container*, and, if the middleware container is configured to cache prepared statements between transactions, then it will expect them to live well beyond their initial explicitly-managed transaction. James Robinson Socialserve.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Why frequently updated tables are an issue
On Jun 10, 2004, at 10:30 AM, [EMAIL PROTECTED] wrote: Prior to lazy vacuum, this was impossible. Do you know for sure that lazy vacuum and/or autovacuum does not indeed solve / alleviate the symptoms of the general problem of very high rate table updates? Back to lurking! James Robinson Socialserve.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump --comment?
On May 28, 2004, at 10:48 AM, Andrew Dunstan wrote: A better answer to this particular problem might be incremental dumps, though :-) Oh would incremental dumps be ever so hungrily accepted by ever so many shops. I had imagined that PITR transaction log archiving would allow one to perform an equivalent to "repay initial full data dump", then foreach transaction log, replay. If no writes have occurred, then the transaction log would be small / nongrowing, right? For example, we perform a full dump hourly, scp to a backup db box who then imports it in its entirety, giving us, on average, a half-hour's worth of potential data lossage at any given moment in time if the primary box goes horribly bad. With current database size / machine speed, etc., this process takes ~8 minutes, so we're not sweating it. But we know that the vast majority of the data did not change in the past hour, so the majority of that work was not truly necessary. With PITR log archiving, could we kick off this transfer + replay activity on the second box to ultimately just ship deltas? I suspect that we could not get such fixed-time guarantees anymore, since any individual transaction log file would not become full until, well, enough writes have occurred to push it over the edge, so our 'data hanging in the wind not yet on the backup box' becomes a function of 'most recent N Kbytes of changed data'. By lowering transaction segment size (or whatever controls the size of individual transaction log files) we could force archiving to occur more / less frequently according to write traffic, right? Poor man's (very) async replication. If all of this is correct, kudos to the forward-thinkers involved with PITR and the abstract archive transaction log protocol. If my proposed usage is flawed, then negative kudos to my puny mind. James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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: [HACKERS] tablespaces and DB administration
On May 26, 2004, at 7:14 PM, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: First, we keep the standard PostgreSQL directory the way it has always been with template0, template1, pg_xlog, pg_clog, etc. in the same place. We can refer to this as the "system" directory. This makes sense because all the system level stuff is there. User databases should be discouraged from the system, and users should be encouraged to create and use separate tablespaces for their databases. Why? This seems to me to be pushing complexity onto users whether they want/need it or not. I think that only a relatively small number of installations will have any use for tablespaces, and we should not try to coerce average users into worrying about them. I forgot to specify that tablepaces should be on separate volumes. (sorry) If all they have is one volume, no worries, but instructing the use of alternate volumes for system and data will improve performance by separating WAL and data operations. Tablespaces are a familiar construct to experienced DBAs who may not be familiar with PostgreSQL. PostgreSQL being similar to other databases will have it better "make sense" to new users. Users are primarily, if not stupid, ignorant. They will read the absolute minimum needed to achieve a goal and little else. I say this with the utmost respect, because I and probably everyone else on this group is guilty of the same thing. So, the "preferred" installation procedure, i.e. the one with the easy to follow directions, should showcase features the user should know, and leave the user in a good place. IMHO, the user's database on one volume and pg_xlog on another is a better starting place. Yes, that is generally the case (prefer pg_xlog on separate spindle), but no need to *forcibly* overcomplicate things if the box has only one spindle, or if they have only one single RAID'd partition configured. We should continue to err on the side of keeping the path to a functional system nice and simple, yet still offering superb functionality. Oracle gets this wrong. pg_autovacuum is another good step in this direction. James Robinson Socialserve.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL pre-fork speedup
Tom Lane writes: ... too much flushing ... I agree. I'll bet replacing the pool_write_and_flush() calls in BinaryRow() and AsciiRow() with just pool_write(), followed by removing the fflush() calls at the bottom of those two methods should go a long way towards fixing things, since the CompleteCommandResponse handler method ends with a call to pool_write_and_flush(), and you've pretty much gotta get a CompleteCommand message trailing all of those rows. ---- James Robinson Socialserve.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL pre-fork speedup
Quick overview of the code for differences in TCP-on-the-frontend code is a call to setsockopt(..., TCP_NODELAY, ...) if the connection to the frontend is a TCP socket. Could this be producing pseudo-fragmentation, resulting in over-the-top context switches? Looks like pool_process_query() does a lot of little itty bitty writes to the frontend filedescriptor. What do you get if you comment out that block in child.c, around line 372? Either a faster system or a non-working one? James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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: [HACKERS] PostgreSQL pre-fork speedup
On May 6, 2004, at 12:19 PM, sdv mailer wrote: 15x Slower: --- Client <--TCP--> PgPool <--UNIX--> PostgreSQL Client <--TCP--> PgPool <--TCP--> PostgreSQL 5x Faster: -- Client <--UNIX--> PgPool <--UNIX--> PostgreSQL Client <--UNIX--> PgPool <--TCP--> PostgreSQL If the problem were in the TCP stack utilization itself, one would expect case #1 to be equivalent to case #4, since both use one UNIX domain connection and one TCP connection. Likewise, one would expect case #2 to be the worst. Does PgPool markedly differ codewise when reading from TCP socket instead of UNIX domain? Pulling down code ... James Robinson Socialserve.com ---(end of broadcast)--- TIP 3: 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: [HACKERS] Weird prepared stmt behavior
[ WRT/ automagically re-parsing prepared statement from source when dependency plan changes.] If done, this would have the wonderful side-effect of being able to use regular queries in plpgsql procedures which must currently be done using the EXECUTE form, such as those that just need to manipulate temporary tables. Quite spiffy, reducing the amount of surprise encountered by postgres neophytes. James Robinson Socialserve.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] user-defined default public acl
[ discussion re/ default state of minimal rights, as opposed to the more generous situation today snipped ] Just to add fuel to the fire, as an ex-college sys-admin having had to deploy both Oracle and postgres, I would have to say that Oracle allowed me to deploy a database container shared by many many students relatively securely, while at the same time allowing them to perform cross-schema queries to their teammates tables if and when they needed to. The users could manage the ACLs of their own schema objects, allowing their peers into their tables on a mutual need basis, but the default action was to be closed off completely. Combined with per-user quotas on the tablespaces and per-user connection limits, I could create a new student user and be comfortable knowing they're not going to be able to steal from others nor consume all disk space. I didn't have to deal with CPU / memory based attacks on the box just 'cause I was lucky I guess. With postgres, I had to create a new database for each user (this was pre-schema, anyway), then tweak the hba conf file and sighup. I had no disk-based resource limits available to me (there's a creative use of linux loopback mounts to enforce per-database quotas floatin' around on the net somewhere, but I didn't think of that, as well as that probably wouldn't scale to, say, thousands of users). I wasn't about to dblink databases for 'em, so it ended up that the mass-student-consumption learn-SQL box was Oracle, and the lesser-used DB was postgres. So, finally, from the perspective of a college admin with a 'centralize the student services' mindset, being able to sandbox SQL users relatively easily while also being able to provide higher-level service such as cross-schema queries, centralized / guaranteed backup, etc. would have been fantastic using postgres. I don't work there anymore, but I'm sure other university shops still have the same issue to solve. If a tight schema sandbox isn't the default, then a system-catalog expert managed schema patch would have been greatly appreciated to solve at least the 'stay out of other user's crud by default' issues. James Robinson Socialserve.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Proposal for a cascaded master-slave replication system
Speaking from a non-profit whose enterprise data sits inside postgres, we would be willing to invest a few thousand dollars into the pot of synchronous multi-master replication. Postgres-r sounded absolutely marvelous to us back in the day that it was rumored to be one of the possible deliverables of 7.4. Not so much for nine-nines of uptime, but for the case of being able to take a full hit on a DB box in production yet still remain running w/o any data loss. Our application servers are JBoss and will be high-available clustered / fully-mirrored, but even with RAID on the DB box one bad thing could take it down, and the data between the hourly backup would go down with it. We have experimented in-house with C-JDBC [ being 'lucky' enough to have all DB writes to go through JDBC ], but would feel more confident w/o involving another service in-between the application and the DB layers, especially since it is not yet fully high-available -- currently shifts the single-point of failure from the DB layer to the CJDBC controller single point. It is reported to have HA via group communication 'soon', but, you never can tell. Read up on it at http://c-jdbc.objectweb.org/ , but the end feel I got from it was not nearly so warm and cozy with the problem being solved at the right place -- the postgres-r way felt much more robust / speedy. We won't ever have parallel oracle dollars, but we would have dollars to bring higher-availability to postgres. 'Cause its our butt on the line hosting our client's data. James Robinson Socialserve.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
