Re: [HACKERS] Stats collector on rampage (8.2.3)
Gaetano Mendola wrote: Hi all, it seems that the stats collector on my box is using more CPU than it did in the past. This is a known bug that was fixed in 8.2.4, so you need to upgrade. //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Stats collector on rampage (8.2.3)
On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... best regards, depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] Stats collector on rampage (8.2.3)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
it seems that the stats collector on my box is using more CPU than
it did in the past.
This is what I'm observing:
CPU usage for the stat process: 25% flat
$ psql -c select version()
version
-
---
PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-3)
(1 row)
$ strace -tt -p 10773
[...]
09:47:37.867655 write(3,
\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
09:47:37.867738 write(3,
\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 823) = 823
09:47:37.867820 close(3)= 0
09:47:37.867862 munmap(0xb7ced000, 4096) = 0
09:47:37.867906 rename(global/pgstat.tmp, global/pgstat.stat) = 0
09:47:37.868188 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868245 recv(7,
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347..., 1000, 0) = 976
09:47:37.868317 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 500}},
NULL) = 0
09:47:37.868372 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868428 recv(7,
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0g\n\0..., 1000, 0) = 976
09:47:37.868501 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868559 recv(7,
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0i\n\0..., 1000, 0) = 976
09:47:37.868629 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868687 recv(7,
\1\0\0\0\4\3\0\0\rg\0\0\v\0\0\0\0\0\0\0\0\0\0\0^\n\0\0..., 1000, 0) = 772
09:47:37.868757 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868815 recv(7,
\1\0\0\0\240\0\0\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0\354..., 1000, 0) = 160
09:47:37.868886 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868944 recv(7,
\1\0\0\0\240\0\0\0\rg\0\0\2\0\0\0\1\0\0\0\0\0\0\0\n\0..., 1000, 0) = 160
09:47:37.869012 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869070 recv(7,
\1\0\0\0l\1\0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\354\4\0..., 1000, 0) = 364
09:47:37.869141 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869198 recv(7,
\1\0\0\0\300\2\0\0\rg\0\0\n\0\0\0\1\0\0\0\0\0\0\0007\n..., 1000, 0) = 704
09:47:37.869267 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869329 recv(7,
\1\0\0\0(\1\0\0\rg\0\0\4\0\0\0\1\0\0\0\0\0\0\0/\n\0\0\0..., 1000, 0) = 296
09:47:37.869398 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869456 recv(7,
\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347..., 1000, 0) = 976
09:47:37.869524 --- SIGALRM (Alarm clock) @ 0 (0) ---
09:47:37.869575 sigreturn() = ? (mask now [])
09:47:37.869659 getppid() = 10768
09:47:37.869702 open(global/pgstat.tmp, O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE,
0666) = 3
09:47:37.869775 fstat64(3, {st_mode=S_IFREG|0600, st_size=0, ...}) = 0
09:47:37.869871 mmap2(NULL, 4096, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7ced000
09:47:37.869928 write(3,
\226\274\245\1D\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
09:47:37.870252 write(3,
;\n\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 4096) = 4096
[...]
and doing the statistics on the system calls:
$ time strace -c -p 10773
Process 10773 attached - interrupt to quit
Process 10773 detached
% time seconds usecs/call callserrors syscall
- -- --- --- - -
62.704.980721 16307851 write
25.722.043299 156 13058 3039 poll
9.610.763046 248 3078 rename
0.640.050992 17 3079 open
0.490.038819 4 10019 recv
0.260.020469 7 3078 munmap
0.130.010344 3 3078 close
0.120.009425 3 3079 mmap2
0.110.008353 3 3079 setitimer
0.090.007114 2 3079 3039 sigreturn
0.070.005923 2 3079 fstat64
0.060.004734 2 3079 getppid
- -- --- --- - -
100.007.943239358636 6078 total
real0m16.313s
user0m1.428s
sys 0m3.802s
so instead of 32 or such rename it did the rename 3K times.
To solve the problem is it possible to kill that process? (will it be
respawned?)
Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHJZ9I7UpzwH2SGd4RAqwrAJ9vyt5fd1rdEu+uTnef6QpFYVBbhwCePB69
b0jA7Ko85TyEfMqAmVVRy/w=
=ax+l
Re: [HACKERS] Stats collector on rampage (8.2.3)
hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. Regards Gaetano Mendola ---(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] Stats collector on rampage (8.2.3)
hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. Regards Gaetano Mendola ---(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] Stats collector on rampage (8.2.3)
Gaetano Mendola wrote: hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. IIRC, it will. You need to change postgresql.conf and disable the stats collector. If you do that, it won't be started. Shouldn't be trigged by DST. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stats collector on rampage (8.2.3)
Andrew Dunstan wrote: Gaetano Mendola wrote: hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. you do realize that this upgrade wouldn't require a dump/restore, don't you? It would be close to instantaneous. Sure I do, for me it's even easier than that, I use DRBD so I can just shut down one node upgrade it and then upgrade the other one; however upgrade the DB means for me touch an entire satellite trasmission platform, I have to schedule a platform maintenance for that... Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collector on rampage (8.2.3)
Magnus Hagander wrote: Gaetano Mendola wrote: hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. IIRC, it will. You need to change postgresql.conf and disable the stats collector. If you do that, it won't be started. Shouldn't be trigged by DST. The high cpu usage started at that time, may be that is another problem of some applications stuck on it. I will investigate further. Gaetano ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stats collector on rampage (8.2.3)
Gaetano Mendola wrote: hubert depesz lubaczewski wrote: On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: it seems that the stats collector on my box is using more CPU than it did in the past. it's well known bug, and it was fixed in 8.2.4: http://www.postgresql.org/docs/current/interactive/release-8-2-4.html ... Prevent the statistics collector from writing to disk too frequently (Tom) ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. you do realize that this upgrade wouldn't require a dump/restore, don't you? It would be close to instantaneous. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hmmm ... isn't count_nondeletable_pages all wet?
* Tom Lane: I am fairly sure that this bug explains problems previously reported by Merlin Moncure: http://archives.postgresql.org/pgsql-general/2006-10/msg01312.php and Florian Weimer: http://archives.postgresql.org/pgsql-general/2006-11/msg00305.php In both those cases, off-list investigation showed that the symptoms were caused by multiple index entries pointing to the same heap tuples, where one index entry matched the actual contents of the row and the other did not. In both cases this occurred for a fairly small number of rows that were clumped together into small ranges of blocks. It looks to me like this is perfectly explained by the theory that that range of blocks had been truncated away by a VACUUM at some point in the table's history, and that the non-matching index entries stemmed from an insert or update that occurred and then aborted after VACUUM had examined the blocks the first time but before it could return to check whether the blocks were still empty. We did have auto-vacuum running, and while the table in question was supposedly INSERT-only, some rollback might have occurred before the corruption hit us, resulting in the dead tuples. So your explanation makes sense to me (but I'm not really familiar with PostgreSQL internals). Regarding Scott's commment of other reports, I don't think we've experienced the issue again; we've switched servers since then, and the usage patterns have changed over time. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Obfuscated definitions of database objects
* Joshua D. Drake: If you need obfuscation (and you don't, you just think you do, no offense) use C. Or put the relevant code into some package/module/whatever, stored on the file system, and include that. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] FUNCTION network(inet,inet) ?
LS,
I don't know if this is the right mailing list to post my request. But here
it goes. PostgreSQL has greatly support for data types inet and cidr. But so
far I haven't been able to figure out how one would convert a ip/netmask
(what one will find on a network card) pair into a network cidr.
I've written three functions which help me to help me with my problem:
CREATE OR REPLACE FUNCTION get_masklen(inet)
RETURNS integer AS
$BODY$
DECLARE
_netmask ALIAS FOR $1;
BEGIN
IF _netmask IS NULL THEN return NULL;
ELSIF _netmask = '255.255.255.255'::inet THEN return 32;
ELSIF _netmask = '255.255.255.254'::inet THEN return 31;
ELSIF _netmask = '255.255.255.252'::inet THEN return 30;
ELSIF _netmask = '255.255.255.248'::inet THEN return 29;
ELSIF _netmask = '255.255.255.240'::inet THEN return 28;
ELSIF _netmask = '255.255.255.224'::inet THEN return 27;
ELSIF _netmask = '255.255.255.192'::inet THEN return 26;
ELSIF _netmask = '255.255.255.128'::inet THEN return 25;
ELSIF _netmask = '255.255.255.0'::inet THEN return 24;
ELSIF _netmask = '255.255.254.0'::inet THEN return 23;
ELSIF _netmask = '255.255.252.0'::inet THEN return 22;
ELSIF _netmask = '255.255.248.0'::inet THEN return 21;
ELSIF _netmask = '255.255.240.0'::inet THEN return 20;
ELSIF _netmask = '255.255.224.0'::inet THEN return 19;
ELSIF _netmask = '255.255.192.0'::inet THEN return 18;
ELSIF _netmask = '255.255.128.0'::inet THEN return 17;
ELSIF _netmask = '255.255.0.0'::inet THEN return 16;
ELSIF _netmask = '255.254.0.0'::inet THEN return 15;
ELSIF _netmask = '255.252.0.0'::inet THEN return 14;
ELSIF _netmask = '255.248.0.0'::inet THEN return 13;
ELSIF _netmask = '255.240.0.0'::inet THEN return 12;
ELSIF _netmask = '255.224.0.0'::inet THEN return 11;
ELSIF _netmask = '255.192.0.0'::inet THEN return 10;
ELSIF _netmask = '255.128.0.0'::inet THEN return 9;
ELSIF _netmask = '255.0.0.0'::inet THEN return 8;
ELSIF _netmask = '254.0.0.0'::inet THEN return 7;
ELSIF _netmask = '252.0.0.0'::inet THEN return 6;
ELSIF _netmask = '248.0.0.0'::inet THEN return 5;
ELSIF _netmask = '240.0.0.0'::inet THEN return 4;
ELSIF _netmask = '224.0.0.0'::inet THEN return 3;
ELSIF _netmask = '192.0.0.0'::inet THEN return 2;
ELSIF _netmask = '128.0.0.0'::inet THEN return 1;
ELSIF _netmask = '0.0.0.0'::inet THEN return 0;
ELSE
RAISE EXCEPTION 'get_masklen(''%''): Invalid netmask', _netmask;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
CREATE OR REPLACE FUNCTION set_masklen(inet,inet)
RETURNS inet AS
$BODY$
BEGIN
RETURN set_masklen($1,get_masklen($2))::inet;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
CREATE OR REPLACE FUNCTION network(inet,inet)
RETURNS cidr AS
$BODY$
BEGIN
RETURN set_masklen($1,get_masklen($2))::cidr;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT
COST 100;
# SELECT network('1.2.3.4'::inet,'255.255.0.0'::inet);
network
-
1.2.0.0/16
(1 row)
My question is: are these interesting enough to adopt in the PostgreSQL core
set of functions. Ideally not in plpgsql but written in C. A trivial thing
for one of the core team to convert into C if you ask me. If no one want to
do this but still interest of adding the routines to the core set of
functions I can do the rewrite in C my self Well need to dust of my C
skills a bit first to be honest.
Happy hacking,
|N.
[HACKERS] grep command
Hi All, I am giving the command cat config.log|grep -w 'PG_VERSION' Which gives the following Output: | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 #define PG_VERSION 8.3beta2 But the output that I would require is PG_VERSION 8.3beta2 that should occur only once, can anyone please tell me the command to get this output. Thanks in advance Regards Cinu Kuriakose ---(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] grep command
Leaving aside the question of why one might want to do this, Unix 101 should show you many ways to do it. For example, sed -n -e 's/.*PG_VERSION /PG_VERSION /p' -e /PG_VERSION/q config.log Please don't cross-post questions like this, especially when it's not really a PostgreSQL question at all. cheers andrew Kuriakose, Cinu Cheriyamoozhiyil wrote: Hi All, I am giving the command cat config.log|grep -w 'PG_VERSION' Which gives the following Output: | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 #define PG_VERSION 8.3beta2 But the output that I would require is PG_VERSION 8.3beta2 that should occur only once, can anyone please tell me the command to get this output. Thanks in advance Regards Cinu Kuriakose ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] WAL archiving idle database
On Fri, Oct 26, 2007 at 6:39 PM, in message
[EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED]
wrote:
On Fri, 2007-10-26 at 18:06 -0500, Kevin Grittner wrote:
Hmmm... We would actually prefer to get the WAL file at the
specified interval. We have software to ensure that the warm
standby instances are not getting stale, and that's pretty simple
with the current behavior.
Another thought: when you say it's pretty simple, what do you do now?
My monitoring scripts for this particular situation employ some pretty
ugly code.
Here's our script:
#! /bin/bash
if [ $1 == ] ; then
savepwd=$PWD
cd /var/pgsql/data/county/
find * -maxdepth 0 -type d | xargs -idirname $0 dirname
cd $savepwd
exit 0
fi
for countyName ; do
echo County: $countyName
/usr/local/pgsql/bin/pg_controldata /var/pgsql/data/county/$countyName/data |
grep -E '(Database cluster state|pg_control last modified)'
/etc/init.d/postgresql-${countyName}-cc status
grep basebackup
/var/pgsql/data/county/$countyName/data/basebackup-of-this-instance
echo ''
done
Here's an example of running it (although the opcenter usually runs
it without a parameter, to get all counties):
[EMAIL PROTECTED]:~ sudo pgstatus.sh iowa
County: iowa
Database cluster state: in archive recovery
pg_control last modified: Mon 29 Oct 2007 09:03:16 AM CDT
pg_ctl: server is running (PID: 15902)
/usr/local/pgsql-8.2.4/bin/postgres -D /var/pgsql/data/county/iowa/data
basebackupcc-2007-10-26_190001
This gets parsed by a script in our monitor (python, I think) and
winds up feeding a status display. It's probably a bit crude, but
it has worked well for us, with very little time required to get it
going. This thread has made me aware that it is dependent on the
checkpoint frequency as well as the archive frequency. Our
checkpoint_timeout setting is 30min and our archive_timeout is the
default (one hour). The monitor shows red if the cluster state
isn't in archvie recovery or pg_ctl doesn't report server is
running or the last modified is older than 75 minutes.
We are OK with a one hour archive interval because we have a
separate application-oriented transaction stream (independent of
the database product) coming back real-time, which we can replay
to top off a database backup.
-Kevin
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] WAL archiving idle database
On Fri, Oct 26, 2007 at 6:28 PM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: [ of course, there's no guarantee that the archive_command succeeds in that time ] Which is one of the things we would want to cause an alert. -Kevin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] PANIC caused by open_sync on Linux
On Fri, Oct 26, 2007 at 10:39:12PM -0400, Greg Smith wrote: There's a couple of potential to-do list ideas that build on the changes in this area in 8.3: I think that's the right way to go. It's too bad that this may still happen in 8.3, but we're way past the point that this is a bug fix, IMO. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_ctl configurable timeout
Am Freitag, 17. August 2007 schrieb Peter Eisentraut: I'm having trouble with the hardcoded 60 second timeout in pg_ctl. pg_ctl sometimes just times out and there is no way to make it wait a little longer. I would like to add an option to be able to change that, say pg_ctl -w --timeout=120. Comments? Lost track of this, but it keeps biting me. Somehow, the 60 second timeout seems completely arbitrary anyway. Maybe we should remove it altogether. We could add an option as described above, but then the packager who creates the init script or whoever creates the initial configuration will have to make an equally arbitrary choice. And most likely you will not notice that your configuration is insufficient until you are really in a bind. What should we do? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] WAL archiving idle database
On Mon, 2007-10-29 at 09:56 -0500, Kevin Grittner wrote: Here's our script: Thanks, I think that is better than what I'm doing. One minor thing: I think it's still dependent on locale though, because the output of pg_controldata is locale-dependent, right? It would work fine for me, but it would be nice if there was something that could be released that anyone could use, including non-english installations. Also, did you publish your pg_clearxlogtail program anywhere? I think that would be helpful to many people, but I don't see it on pgfoundry. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] WAL archiving idle database
On Mon, Oct 29, 2007 at 11:50 AM, in message [EMAIL PROTECTED], Jeff Davis [EMAIL PROTECTED] wrote: Also, did you publish your pg_clearxlogtail program anywhere? I think that would be helpful to many people, but I don't see it on pgfoundry. So far I've just included with the email on the hackers list. I have made one fix since: I found that an explicit close of stdout speeds the worst-case situation to break-even. (I'm guessing that's safer, too.) So in all of my tests it is now as fast or faster to pipe through this on the way to gzip than to just pipe through gzip. I'll see about getting that onto pgfoundry soon. -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] WAL archiving idle database
On Mon, 2007-10-29 at 14:20 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: One minor thing: I think it's still dependent on locale though, because the output of pg_controldata is locale-dependent, right? It would work fine for me, but it would be nice if there was something that could be released that anyone could use, including non-english installations. Wouldn't export LANG=C fix that? Ah, of course. Thanks, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [HACKERS] grep command
Or ... ask the application not the OS psql select version() ; Cheers Medi On 10/29/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Leaving aside the question of why one might want to do this, Unix 101 should show you many ways to do it. For example, sed -n -e 's/.*PG_VERSION /PG_VERSION /p' -e /PG_VERSION/q config.log Please don't cross-post questions like this, especially when it's not really a PostgreSQL question at all. cheers andrew Kuriakose, Cinu Cheriyamoozhiyil wrote: Hi All, I am giving the command cat config.log|grep -w 'PG_VERSION' Which gives the following Output: | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 | #define PG_VERSION 8.3beta2 #define PG_VERSION 8.3beta2 But the output that I would require is PG_VERSION 8.3beta2 that should occur only once, can anyone please tell me the command to get this output. Thanks in advance Regards Cinu Kuriakose ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] WAL archiving idle database
Jeff Davis [EMAIL PROTECTED] writes: One minor thing: I think it's still dependent on locale though, because the output of pg_controldata is locale-dependent, right? It would work fine for me, but it would be nice if there was something that could be released that anyone could use, including non-english installations. Wouldn't export LANG=C fix that? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_ctl configurable timeout
Bruce Momjian wrote: How about an environment variable to control the timeout? Is that cleaner? I don't see why it should be. I think Peter's --timeout suggestion should be just fine. cheers andrtew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_ctl configurable timeout
Peter Eisentraut [EMAIL PROTECTED] writes: Somehow, the 60 second timeout seems completely arbitrary anyway. Maybe we should remove it altogether. We could add an option as described above, but then the packager who creates the init script or whoever creates the initial configuration will have to make an equally arbitrary choice. Yeah. One problem is that we use the same timeout for startup and shutdown, which really are entirely different; and the other problem is that we've not wanted pg_ctl to have too many smarts about the server's internal behavior. On startup, it would be reasonable to assume failure if we don't see a postmaster pid-file appear PDQ, but then after that it might stay in the database is starting up state for a long time (maybe even indefinitely if it's a warm standby server). Still, you could argue that it's reasonable to keep waiting as long as the postmaster keeps returning database is starting up when pinged. On shutdown, it'd be reasonable to expect that the postmaster starts returning database is shutting down almost immediately, and to report failure if not. However, if it was a default smart mode stop you could again wait indefinitely for clients to decide to give up their sessions. I'm not sure if it's sane for pg_ctl to wait indefinitely in that scenario. I agree that just pushing the choice of timeout onto the user's shoulders wouldn't be much of an improvement. He can always hit ^C if he gets tired of waiting. regards, tom lane ---(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] pg_ctl configurable timeout
Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: How about an environment variable to control the timeout? Is that cleaner? I don't see why it should be. I think Peter's --timeout suggestion should be just fine. I wrote a moment ago that the user can hit control-C when he gets bored, but that argument only works for interactive use of pg_ctl. In a script I think you'd want a --timeout option. I don't see the advantage of an environment variable in either scenario. regards, tom lane ---(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] pg_ctl configurable timeout
Alvaro Herrera [EMAIL PROTECTED] writes: I think the mythical pg_ping utility should be written. It seems the easiest way out of the problem. If pg_ctl were still a shell script there would be some point in that, but since it's a C program it can certainly do anything a separate utility would do. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_ctl configurable timeout
On Mon, 2007-10-29 at 17:34 -0300, Alvaro Herrera wrote: Maybe hack the postmaster to have a new special connection mode which keeps the connection open until the startup process exits, to avoid polling continuously (ideally report progress too, if at all possible). That sounds good to me. The spurious connection messages look weird and its difficult to say that's one of the ERRORs that isn't an error. There has to be a way for pg_ctl to ask whether the server is still starting up without causing a message every second in the server log. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_ctl configurable timeout
Peter Eisentraut wrote: Am Freitag, 17. August 2007 schrieb Peter Eisentraut: I'm having trouble with the hardcoded 60 second timeout in pg_ctl. pg_ctl sometimes just times out and there is no way to make it wait a little longer. I would like to add an option to be able to change that, say pg_ctl -w --timeout=120. Comments? Lost track of this, but it keeps biting me. Somehow, the 60 second timeout seems completely arbitrary anyway. Maybe we should remove it altogether. We could add an option as described above, but then the packager who creates the init script or whoever creates the initial configuration will have to make an equally arbitrary choice. And most likely you will not notice that your configuration is insufficient until you are really in a bind. What should we do? I think the mythical pg_ping utility should be written. It seems the easiest way out of the problem. Maybe hack the postmaster to have a new special connection mode which keeps the connection open until the startup process exits, to avoid polling continuously (ideally report progress too, if at all possible). -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Y dijo Dios: Que sea Satanás, para que la gente no me culpe de todo a mÃ. Y que hayan abogados, para que la gente no culpe de todo a Satanás ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_ctl configurable timeout
Peter Eisentraut wrote: Am Freitag, 17. August 2007 schrieb Peter Eisentraut: I'm having trouble with the hardcoded 60 second timeout in pg_ctl. pg_ctl sometimes just times out and there is no way to make it wait a little longer. I would like to add an option to be able to change that, say pg_ctl -w --timeout=120. Comments? Lost track of this, but it keeps biting me. Somehow, the 60 second timeout seems completely arbitrary anyway. Maybe we should remove it altogether. We could add an option as described above, but then the packager who creates the init script or whoever creates the initial configuration will have to make an equally arbitrary choice. And most likely you will not notice that your configuration is insufficient until you are really in a bind. What should we do? How about an environment variable to control the timeout? Is that cleaner? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: real procedures again (8.4)
Ühel kenal päeval, L, 2007-10-27 kell 14:10, kirjutas David Fetter: On Sun, Oct 28, 2007 at 12:05:26AM +0300, Hannu Krosing wrote: Ühel kenal päeval, L, 2007-10-27 kell 12:55, kirjutas Josh Berkus: Merlin, Pavel, Mutable session variables would be nice, but I'll take a plpgsql langauge (or psm) with or without them, so long as transactions are manual. It's possible to emulate variables using scalar functions with the desired volatility currently (but you still have to be careful with transactions). The other big useful feature we're missing from Functions is multisets. I think that support for multisets has been removed from our fe-be protocol implementation bit-by-bit. How do you mean? The only way I've done multisets is by creating functions that return multiple refcursors, either in a row or as SETOF. Is or was there some other way? I _think_ that originally an SQL function with multiple SELECTs was meant to return results for all these in a row, as a multiset. I don't think that this has ever been the case, at least not after switch from Postgres 4.2 Quel to Postgres95 SQL. What I was referring to, was a code cleanup of libpq several years ago, when someone (maybe Bruce IIRC) removed ability to accept multiple recordsets from backend altogether, on the basis that it is not used anyway. Hannu ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] pg_ctl configurable timeout
--- Original Message --- From: Peter Eisentraut [EMAIL PROTECTED] To: pgsql-hackers@postgresql.org Sent: 29/10/07, 17:54:00 Subject: Re: [HACKERS] pg_ctl configurable timeout Am Freitag, 17. August 2007 schrieb Peter Eisentraut: I'm having trouble with the hardcoded 60 second timeout in pg_ctl. pg_ctl sometimes just times out and there is no way to make it wait a little longer. I would like to add an option to be able to change that, say pg_ctl -w --timeout=120. Comments? Lost track of this, but it keeps biting me. Somehow, the 60 second timeout seems completely arbitrary anyway. Maybe we should remove it altogether. We could add an option as described above, but then the packager who creates the init script or whoever creates the initial configuration will have to make an equally arbitrary choice. And most likely you will not notice that your configuration is insufficient until you are really in a bind. What should we do? We need the option on Windows to prevent dependent services being started too quickly. The same problem occurs there with pg_ctl reporting it's status to the service control manager. The scm interface handles this by having the service regularly increment a variable, and if required, updating the estimated startup time. A similar architecture might be feasible if we had the postmaster signal pg_ctl periodically until started at which point a different signal is sent. We then only timeout if no pulse or started signal is received within X seconds. Regards, Dave ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: real procedures again (8.4)
Gregory Stark wrote: Hannu Krosing [EMAIL PROTECTED] writes: What I was referring to, was a code cleanup of libpq several years ago, when someone (maybe Bruce IIRC) removed ability to accept multiple recordsets from backend altogether, on the basis that it is not used anyway. You can still receive multiple record sets just fine using libpq. psql doesn't handle them but they're there. When I was doing the concurrent psql patch I also had it handling multiple record sets. Something else you may be thinking of, I don't think it's legal to do queries like select 1 ; select 2 in the new protocol. That was legal in the old protocol. I think the cool thing that Josh Berkus wants is return query select a, b, c from foo; return query select d, e, f from bar; in a plpgsql function, and getting two result sets (I'm fuzzy about the exact syntax but you get the idea). Can this be done at all? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Proposal: real procedures again (8.4)
Josh Berkus wrote: Not only would they be generally useful for SP programming, but multisets would eliminate one of the big hurdles in re-writing T-SQL stored procedures in PG, and thus make it easier to port from SQL Server. You don't hear a lot of demand for multisets on the mailing lists because we're not getting those SQL Server / Sybase crossovers now. Its true that multiple result sets are a big deal with T-SQL programming: but I think you'll also need to provide a way for the locking model to behave in a similar way and also very importantly to be able to emulate the after-statement triggers view of new and old images. James ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal: real procedures again (8.4)
Hannu Krosing [EMAIL PROTECTED] writes: What I was referring to, was a code cleanup of libpq several years ago, when someone (maybe Bruce IIRC) removed ability to accept multiple recordsets from backend altogether, on the basis that it is not used anyway. You can still receive multiple record sets just fine using libpq. psql doesn't handle them but they're there. When I was doing the concurrent psql patch I also had it handling multiple record sets. Something else you may be thinking of, I don't think it's legal to do queries like select 1 ; select 2 in the new protocol. That was legal in the old protocol. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Recovery of Multi-stage WAL actions
We've had two hard to diagnose errors in recovery in recent months. ISTM that the core issue is the way we allow Resource Managers to have multi-stage WAL actions that persist for long periods of time. This means we have no way of telling whether the answer rm_safe_restartpoint() == false is a momentary, valid state or a progressively worsening indicator of a subtle RM bug. An example of a multi-stage WAL action would be an index split inside one of the Resource Managers. Now that kind of action shouldn't take very long, though theoretically it could for various reasons. Right now we have a log message to cope with this: http://archives.postgresql.org/pgsql-committers/2007-08/msg00374.php but its not nearly as helpful as we'd like it to be. We could back-patch this to 8.2, but I have a potentially better proposal. I very much want to encourage authors of new Resource Managers and it looks like we may be getting at least 3 new RMs that produce WAL records: hash indexes (currently not WAL-logged), bitmap indexes and clustered indexes for 8.4. We should be realistic that new bugs probably will occur in recovery code for existing and new RMs. What I'd like to do is force all of the RMs to record the lsn of any WAL record that starts an incomplete action. Then, if an incomplete action lives for more than a certain period of time it will be possible to produce a log message saying incomplete split has survived for X seconds, in xlog time. That way we'll see log messages if any of the RMs start to push an incomplete action onto their list and then not consume it again. We might trust each RM to implement code to LOG messages if their code goes a little awry, but I'd prefer some mechanism that allows the main server to check what's happening in each RM. That way we'd have a cross-check on whether the RM is well-behaved, plus we'd only need to implement the checking code once. Right now very similar, yet different code runs inside each RM. So my proposal is to have an incomplete split remember/forget API that forces each RM to expose its incomplete split List. Currently each RM has a hook on rm_saferestartpoint(), so that each RM manages its own List. My new thought is to have one function safeRestartPoint() that inspects each of the incomplete split Lists to see if they are empty. If the lists are non-empty then inspect the age of each list entry to see if it is worth reporting as a possible issue. Each RM would then store incomplete splits using a ResourceManagerRememberIncompleteEvent(lsn, id_data, payload??) and ResourceManagerforgetIncompleteEvent(id_data). Implementation is a a bit hazy on that last part, but I think the overall idea is clear. That should mean that any incomplete split that lasts for the length of one restartpoint, which is *at least* one checkpoint duration, should cause a LOG message to be produced. We might even go as far as to ignore super long-lived and therefore spurious incomplete splits when we issue rm_cleanup() for fear of allowing RM bugs to kill recovery. I'd like to suggest that those changes be performed now for 8.3 *and* back-patched for 8.2. I want to make sure that all users are able to diagnose server errors and report them. I'm guessing that might raise a few eyebrows, but I think its justifiable. Bugs in complex code are inevitable and should not be seen to reflect badly upon RM authors. However, our inability to recognise RM bugs that do occur doesn't seem acceptable to me, especially since they may save themselves up for the moment of PITR fail-over. You might persuade me I'm being over-zealous here, but High Availability is something we have to be zealous about. It should also be possible to allow the server to stay up even if one of the RM's fails to recover properly. That would need to be settable, so I really only mean that for optional RMs, i.e. index RMs only. For those cases we should be able to mark effected indexes by marking them corrupt. Automatic rebuild of corrupt indexes could also be possible, should it occur. That would be an 8.4 action... :-) Comments appreciated, as ever. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.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] Proposal: real procedures again (8.4)
On Mon, Oct 29, 2007 at 07:32:11PM -0300, Alvaro Herrera wrote:
Gregory Stark wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
What I was referring to, was a code cleanup of libpq several
years ago, when someone (maybe Bruce IIRC) removed ability to
accept multiple recordsets from backend altogether, on the basis
that it is not used anyway.
You can still receive multiple record sets just fine using libpq.
psql doesn't handle them but they're there. When I was doing the
concurrent psql patch I also had it handling multiple record sets.
Something else you may be thinking of, I don't think it's legal to
do queries like select 1 ; select 2 in the new protocol. That
was legal in the old protocol.
I think the cool thing that Josh Berkus wants is
return query select a, b, c from foo;
return query select d, e, f from bar;
in a plpgsql function, and getting two result sets (I'm fuzzy about the
exact syntax but you get the idea). Can this be done at all?
Based on the example in TFM for PL/PgSQL:
BEGIN;
CREATE TABLE foo(foo_id SERIAL PRIMARY KEY, foo_text TEXT);
CREATE TABLE bar(bar_id SERIAL PRIMARY KEY, bar_text TEXT);
INSERT INTO foo(foo_text) VALUES ('a'),('b'),('c'),('d');
INSERT INTO bar(bar_text) VALUES ('e'),('f'),('g'),('h');
CREATE FUNCTION wtf(refcursor, refcursor)
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $$
BEGIN
OPEN $1 FOR SELECT * FROM foo;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM bar;
RETURN NEXT $2;
END;
$$;
SELECT * FROM wtf('a','b');
FETCH all FROM a;
FETCH all FROM b;
ROLLBACK;
Cheers,
David.
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] install-strip causes dyld errors on OS X
Hi all, Three years ago, Andrew MacRae logged the following bug: http://archives.postgresql.org/pgsql-bugs/2004-02/msg00042.php In brief, when installing on OS X with make install-strip, installation goes fine, but initdb dies here: creating conversions... ERROR: could not load library /usr/local/pgsql/lib/ascii_and_mic.so: dyld: /usr/local/pgsql/bin/postgres Undefined symbols: /usr/local/pgsql/lib/ascii_and_mic.so undefined reference to _pg_ascii2mic expected to be defined in the executable I encountered this problem today with OS X 10.4 and CVS HEAD, and came up with a few possible solutions. The problem has to do with the difference between shared libraries and dynamic libraries, and how strip behaves with them on OS X. Here's how it works: When called on an executable which uses dyld, strip silently passes itself the -u (save all undefined symbols) and -r (save all dynamically-referenced symbols) options. However, the internationalization libraries (and a few others, like libplperl and libplpython) are compiled as shared libraries (.so as opposed to .dylib), so strip doesn't know that those libraries contain undefined symbols and are expecting to acquire them from the executable. I see three possible fixes: 1) Patch config/install-sh such that on OS X, install-strip calls 'strip -x'. This removes local symbols only. 2) Determine which symbols are global and undefined in the shared libraries that are built, make a list of them, and call 'strip -u -r -s symbols_to_keep'. This saves undefined symbols, dynamically referenced symbols, and symbols that the shared libraries need. 3) Change the OS X build such that the i18n libs and other shared libs are instead built as dylibs. I've tested (1) as far as getting through initdb, starting the postmaster and a few quick things with psql. It would be about a four-line patch to install-sh (check for the OS, set stripcmd appropriately). It might not leave the resulting binary *as* small as it could be, but it certainly works. (2) sounds like a giant pain in the ass. It's scriptable and could be added to the build, but the build takes pretty long as it is and I can't imagine anyone wants to make it longer. Unless there's a good reason, I'm pre-emptively -1'ing this one. I haven't done any looking into (3) yet. Why *are* the i18n libs built as shared on OS X, anyway? Cheers, --mlp _ Meredith L. Patterson Founder and CTO Osogato, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Recovery of Multi-stage WAL actions
Simon Riggs [EMAIL PROTECTED] writes: What I'd like to do is force all of the RMs to record the lsn of any WAL record that starts an incomplete action. ... I'd like to suggest that those changes be performed now for 8.3 *and* back-patched for 8.2. There is zero chance of the former and less than zero of the latter; making major, hard-to-test changes in the rather-hypothetical hope of discovering entirely-hypothetical bugs is not my idea of the way to run a stable release branch. Feel free to create a patch for 8.4 though --- I think this is a reasonable suggestion for making *future* RM development safer. (Also, if there are any bugs in the extant code, we'd presumably back-patch fixes discovered in 8.4 testing, long as you don't whack the code around to the point of unrecognizability...) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] install-strip causes dyld errors on OS X
Meredith L. Patterson [EMAIL PROTECTED] writes: In brief, when installing on OS X with make install-strip, installation goes fine, but initdb dies here: ... I see three possible fixes: 1) Patch config/install-sh such that on OS X, install-strip calls 'strip -x'. This removes local symbols only. +1 on this one --- seems about the right level of effort, considering that install-strip isn't exactly an encouraged option anyway. (Does anyone still care about the amount of disk space involved? And it makes debugging problems infinitely harder.) Just for the record, I'd like to put forward the alternative solution of removing support for install-strip. But assuming there are objections to that, please send in the mentioned 4-line patch ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
