Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
Here is a snapshot of installed postgresql packages: mayank@server:~$ dpkg -l | grep postgres ii postgresql-9.1 9.1.5-1~bpo60+1 object-relational SQL database, version 9.1 server ii postgresql-client-9.1 9.1.5-1~bpo60+1 front-end programs for PostgreSQL 9.1 ii postgresql-client-common 130~bpo60+1 manager for multiple PostgreSQL client versions ii postgresql-common 130~bpo60+1 PostgreSQL database-cluster manager ii postgresql-contrib 9.1+130~bpo60+2 additional facilities for PostgreSQL (supported version) ii postgresql-contrib-9.1 9.1.5-1~bpo60+1 additional facilities for PostgreSQL Regards, Mayank Mittal > Date: Thu, 20 Sep 2012 16:15:11 + > Subject: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": > readonly 0 of 8192 bytes > To: pgsql-bugs@postgresql.org > From: mayank.mittal.1...@hotmail.com > > The following bug has been logged on the website: > > Bug reference: 7562 > Logged by: Mayank Mittal > Email address: mayank.mittal.1...@hotmail.com > PostgreSQL version: 9.1.5 > Operating system: Debian Linux 6.0 > Description: > > We are using 2 node set-up of PostgreSQL 9.1.5 in which one is master and > other is slave which is in sync of master with streaming replication. > The design is in such a way that in case of master node failure the slave > node has to take master role. I'm controlling this behaviour using Corosync > and Heartbeat. > My application is requirement needs heavy database updates. Upon fail-over > I've noticed that database indexes got corrupted. > I'm not sure why this is happening. I was referring release notes of 9.1.3 > and found similar issue is already fixed in it, but we are facing the same. > > > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7559: syslogger doesn't close stdout and stderr
On Thu, 20 Sep 2012 at 11:06, Tom Lane wrote: (I assume you mean pg_ctl not pg_init?) Yes, sorry for the confusion. Well, I would have no objection to changing pg_ctl so that it redirects the postmaster's stdout/stderr when a -l switch is given (actually, I thought it did that already...). Well, going that route forces me to either introduce yet another log file for the user to look into when something goes wrong with PostgreSQL, or to suppress that information completely (when using -l /dev/null). I think it is common practice for daemons to report early errors to stderr (so that the user starting the serivice gets to see them on the terminal) and after successfull startup redirect to /dev/null and log to syslog or their own logging mechanism. I do object to changing the logger's behavior as you suggest, because that will break use-cases that work today. One that I've used personally is adding "fprintf(stderr)" calls in the logger for debugging the logger itself. Do you also have use cases in mind that are relevant for end users of PostgreSQL who never even look into the source code? If not (i.e. if the use cases are more developer-centric), I think the default should be to let the logger do the redirection and having a command line switch or postgresql.conf variable to suppress it for debugging purposes. cu Reinhard -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
Andres Freund writes: > On Thursday, September 20, 2012 11:38:52 PM Tom Lane wrote: >> Sure, but what about the heap? The case I was speculating about was >> that the heap had been truncated, but because of the corruption problem, >> the index still had heap pointers in it. We don't know what file 16585 >> is supposed to be. > Wouldn't the truncation have created a completely new index relation? If it were an actual TRUNCATE, yeah. But it could be a case of VACUUM truncating a now-empty table to zero blocks. But nothing like this would explain the OP's report that corruption is completely reproducible for him. So I like your theory about hash index use better. We really oughta get some WAL support in there. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
On Thursday, September 20, 2012 11:38:52 PM Tom Lane wrote: > Andres Freund writes: > > On Thursday, September 20, 2012 07:15:17 PM Tom Lane wrote: > >> Hmm. There is a fix for a slave-side-index-corruption problem in 9.1.6, > >> which is due to be announced Monday. I am not certain whether this is > >> the same thing though; that bug is low-probability as far as we can > >> tell (it would only happen if the master had been in the middle of an > >> index page split or page deletion at the instant of failover). Anyway > >> the first thing to find out is whether 9.1.6 fixes it. > > > > I think the likelihood of that bug causing the the index file to be zero > > bytes > > > - at least thats what I read from $subject - is really, really small: > Sure, but what about the heap? The case I was speculating about was > that the heap had been truncated, but because of the corruption problem, > the index still had heap pointers in it. We don't know what file 16585 > is supposed to be. Hm. Interesting thought. *think* Wouldn't the truncation have created a completely new index relation? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
Andres Freund writes: > On Thursday, September 20, 2012 07:15:17 PM Tom Lane wrote: >> Hmm. There is a fix for a slave-side-index-corruption problem in 9.1.6, >> which is due to be announced Monday. I am not certain whether this is >> the same thing though; that bug is low-probability as far as we can >> tell (it would only happen if the master had been in the middle of an >> index page split or page deletion at the instant of failover). Anyway >> the first thing to find out is whether 9.1.6 fixes it. > I think the likelihood of that bug causing the the index file to be zero > bytes > - at least thats what I read from $subject - is really, really small: Sure, but what about the heap? The case I was speculating about was that the heap had been truncated, but because of the corruption problem, the index still had heap pointers in it. We don't know what file 16585 is supposed to be. Your point about hash indexes is definitely worth asking though... that would square with the reported symptoms. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
On Thursday, September 20, 2012 07:15:17 PM Tom Lane wrote: > mayank.mittal.1...@hotmail.com writes: > > The following bug has been logged on the website: > > Bug reference: 7562 > > Logged by: Mayank Mittal > > Email address: mayank.mittal.1...@hotmail.com > > PostgreSQL version: 9.1.5 > > Operating system: Debian Linux 6.0 > > Description: > > > > We are using 2 node set-up of PostgreSQL 9.1.5 in which one is master and > > other is slave which is in sync of master with streaming replication. > > The design is in such a way that in case of master node failure the slave > > node has to take master role. I'm controlling this behaviour using > > Corosync and Heartbeat. > > My application is requirement needs heavy database updates. Upon > > fail-over I've noticed that database indexes got corrupted. What kind of indexes are you using? Hash indexes by any chance? As you say downthread the failures are frequent could you provide a bit more details about your setup (including configuration, initial setup etc) and the logs on both machines? > Hmm. There is a fix for a slave-side-index-corruption problem in 9.1.6, > which is due to be announced Monday. I am not certain whether this is > the same thing though; that bug is low-probability as far as we can > tell (it would only happen if the master had been in the middle of an > index page split or page deletion at the instant of failover). Anyway > the first thing to find out is whether 9.1.6 fixes it. I think the likelihood of that bug causing the the index file to be zero bytes - at least thats what I read from $subject - is really, really small: The index would need to be created (setting a proper BM_PERMANENT flag on the meta page), evicted from the buffer cache and thus written to the filesystem, the root page would need to split causing the meta page to be rewritten (this time without a proper BM_PERMANENT) in a very quick succession followed by a OS/HW failure loosing the data already in the OS cache. So, unless I am missing something, I don't see how that can happen. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
Hello Tom, Thanks for the information. But problem is it is occurring quite frequently in my case. Regards, Mayank Mittal > From: t...@sss.pgh.pa.us > To: mayank.mittal.1...@hotmail.com > CC: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #7562: could not read block 0 in file > "base/16385/16585": read only 0 of 8192 bytes > Date: Thu, 20 Sep 2012 13:15:17 -0400 > > mayank.mittal.1...@hotmail.com writes: > > The following bug has been logged on the website: > > Bug reference: 7562 > > Logged by: Mayank Mittal > > Email address: mayank.mittal.1...@hotmail.com > > PostgreSQL version: 9.1.5 > > Operating system: Debian Linux 6.0 > > Description: > > > We are using 2 node set-up of PostgreSQL 9.1.5 in which one is master and > > other is slave which is in sync of master with streaming replication. > > The design is in such a way that in case of master node failure the slave > > node has to take master role. I'm controlling this behaviour using Corosync > > and Heartbeat. > > My application is requirement needs heavy database updates. Upon fail-over > > I've noticed that database indexes got corrupted. > > Hmm. There is a fix for a slave-side-index-corruption problem in 9.1.6, > which is due to be announced Monday. I am not certain whether this is > the same thing though; that bug is low-probability as far as we can > tell (it would only happen if the master had been in the middle of an > index page split or page deletion at the instant of failover). Anyway > the first thing to find out is whether 9.1.6 fixes it. > > regards, tom lane > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
mayank.mittal.1...@hotmail.com writes: > The following bug has been logged on the website: > Bug reference: 7562 > Logged by: Mayank Mittal > Email address: mayank.mittal.1...@hotmail.com > PostgreSQL version: 9.1.5 > Operating system: Debian Linux 6.0 > Description: > We are using 2 node set-up of PostgreSQL 9.1.5 in which one is master and > other is slave which is in sync of master with streaming replication. > The design is in such a way that in case of master node failure the slave > node has to take master role. I'm controlling this behaviour using Corosync > and Heartbeat. > My application is requirement needs heavy database updates. Upon fail-over > I've noticed that database indexes got corrupted. Hmm. There is a fix for a slave-side-index-corruption problem in 9.1.6, which is due to be announced Monday. I am not certain whether this is the same thing though; that bug is low-probability as far as we can tell (it would only happen if the master had been in the middle of an index page split or page deletion at the instant of failover). Anyway the first thing to find out is whether 9.1.6 fixes it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7559: syslogger doesn't close stdout and stderr
Reinhard Max writes: > On Thu, 20 Sep 2012 at 11:06, Tom Lane wrote: >> Well, I would have no objection to changing pg_ctl so that it >> redirects the postmaster's stdout/stderr when a -l switch is given >> (actually, I thought it did that already...). > Well, going that route forces me to either introduce yet another log > file for the user to look into when something goes wrong with > PostgreSQL, or to suppress that information completely (when using -l > /dev/null). I think it is common practice for daemons to report early > errors to stderr (so that the user starting the serivice gets to see > them on the terminal) and after successfull startup redirect to > /dev/null and log to syslog or their own logging mechanism. Well, at least in the Fedora/RHEL packages I had such an additional log file for years. Printing complaints to "the terminal" turns out to not be tremendously useful in service start scripts, because even if there's somebody watching the console during boot, the info tends to fly offscreen pretty quick. (systemd finally improved that by connecting services' stdout/stderr to syslog by default --- but it's still not going to the user's screen.) >> I do object to changing the logger's behavior as you suggest, >> because that will break use-cases that work today. One that I've >> used personally is adding "fprintf(stderr)" calls in the logger for >> debugging the logger itself. > Do you also have use cases in mind that are relevant for end users of > PostgreSQL who never even look into the source code? Sure. Under systemd the logger's stderr will go to /var/log/messages. Admittedly, it shouldn't ever print anything there during normal operation, but we don't have control over all the code in the process. For instance, if glibc were to detect malloc-arena corruption, its complaints about that would end up in /var/log/messages. Under your proposal they'd end up in /dev/null. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7562: could not read block 0 in file "base/16385/16585": read only 0 of 8192 bytes
The following bug has been logged on the website: Bug reference: 7562 Logged by: Mayank Mittal Email address: mayank.mittal.1...@hotmail.com PostgreSQL version: 9.1.5 Operating system: Debian Linux 6.0 Description: We are using 2 node set-up of PostgreSQL 9.1.5 in which one is master and other is slave which is in sync of master with streaming replication. The design is in such a way that in case of master node failure the slave node has to take master role. I'm controlling this behaviour using Corosync and Heartbeat. My application is requirement needs heavy database updates. Upon fail-over I've noticed that database indexes got corrupted. I'm not sure why this is happening. I was referring release notes of 9.1.3 and found similar issue is already fixed in it, but we are facing the same. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7559: syslogger doesn't close stdout and stderr
Reinhard Max writes: > After starting PostgreSQL with pg_init, stdout and stderr of all > processes are pipes to the logger (as intended), but the logger itself > still has FDs 1 and 2 open as inherited from pg_init. I think > requiring the caller of pg_init to redirect them is not practical, > because then pg_init itself can't give feedback to the user. That's a fair point. (I assume you mean pg_ctl not pg_init?) > So it has > to be done either in pg_init or in the logger when those channels > aren't needed anymore. I'd prefer doing it in the logger, because the > code for it is already there and so that it also works when starting > PostgreSQL without using pg_init. Well, I would have no objection to changing pg_ctl so that it redirects the postmaster's stdout/stderr when a -l switch is given (actually, I thought it did that already...). I do object to changing the logger's behavior as you suggest, because that will break use-cases that work today. One that I've used personally is adding "fprintf(stderr)" calls in the logger for debugging the logger itself. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7556 addition info
plan of http://archives.postgresql.org/pgsql-bugs/2012-09/msg00222.php test=# explain select max(a.info)from sli_test a where a.id not in(select b.id from sli_test2 b where b.id<5); QUERY PLAN --- Aggregate (cost=9243466274.00..9243466274.01 rows=1 width=12) -> Seq Scan on sli_test a (cost=0.00..9243465024.00 rows=50 width=12) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.00..18359.60 rows=50919 width=4) -> Seq Scan on sli_test2 b (cost=0.00..17906.00 rows=50919 width=4) Filter: (id < 5)
Re: [BUGS] BUG #7559: syslogger doesn't close stdout and stderr
On Thu, 20 Sep 2012 at 10:31, Heikki Linnakangas wrote:
I believe Tom is referring to the removal of silent_mode in 9.2, see
http://archives.postgresql.org/pgsql-general/2011-06/msg00796.php
and
http://archives.postgresql.org/pgsql-hackers/2011-06/msg02156.php.
"We removed logic associated with daemonization" meant that the
logic was removed from postmaster, because the preferred way to do
it is by calling "pg_ctl start". pg_ctl redirects to /dev/null as
you'd expect.
Well, I was involved in that discussion and as a consequence stopped
using silent_mode in the SUSE RPMs. That's what triggered the problem
and I just verified that it still exists on 9.2.
After starting PostgreSQL with pg_init, stdout and stderr of all
processes are pipes to the logger (as intended), but the logger itself
still has FDs 1 and 2 open as inherited from pg_init. I think
requiring the caller of pg_init to redirect them is not practical,
because then pg_init itself can't give feedback to the user. So it has
to be done either in pg_init or in the logger when those channels
aren't needed anymore. I'd prefer doing it in the logger, because the
code for it is already there and so that it also works when starting
PostgreSQL without using pg_init.
I've attached the patch I sent to Peter which applies to 9.1 and 9.2.
It also fixes a problem with the reopening of stdout and stderr from
/dev/null where the temporary FD must not be closed if it is either 1
or 2, which is quite likely to happen as we've just closed these two
and open() typically gets the lowest unused FD number.
BTW, I think the other dup2() for stderr in syslogger.c should get
such a check as well, even if the clash is less likely to happen
there.
cu
Reinhard--- src/backend/postmaster/syslogger.c
+++ src/backend/postmaster/syslogger.c
@@ -184,31 +184,22 @@ SysLoggerMain(int argc, char *argv[])
init_ps_display("logger process", "", "", "");
+ int fd = open(DEVNULL, O_WRONLY, 0);
+
/*
- * If we restarted, our stderr is already redirected into our own input
- * pipe. This is of course pretty useless, not to mention that it
- * interferes with detecting pipe EOF. Point stderr to /dev/null. This
- * assumes that all interesting messages generated in the syslogger will
- * come through elog.c and will be sent to write_syslogger_file.
+ * The closes might look redundant, but they are not: we want to be
+ * darn sure the pipe gets closed even if the open failed. We can
+ * survive running with stderr pointing nowhere, but we can't afford
+ * to have extra pipe input descriptors hanging around.
*/
- if (redirection_done)
+ close(fileno(stdout));
+ close(fileno(stderr));
+ if (fd != -1)
{
- int fd = open(DEVNULL, O_WRONLY, 0);
-
- /*
- * The closes might look redundant, but they are not: we want to be
- * darn sure the pipe gets closed even if the open failed. We can
- * survive running with stderr pointing nowhere, but we can't afford
- * to have extra pipe input descriptors hanging around.
- */
- close(fileno(stdout));
- close(fileno(stderr));
- if (fd != -1)
- {
- dup2(fd, fileno(stdout));
- dup2(fd, fileno(stderr));
+ dup2(fd, fileno(stdout));
+ dup2(fd, fileno(stderr));
+ if (fd != fileno(stdout) && fd != fileno(stderr))
close(fd);
- }
}
/*
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7556 addition info
On 09/20/2012 01:14 PM, l...@tom.com wrote: > plan of http://archives.postgresql.org/pgsql-bugs/2012-09/msg00222.php > test=# explain select max(a.info)from sli_test a where a.id not > in(select b.id from sli_test2 b where b.id<5); >QUERY PLAN > --- > Aggregate (cost=9243466274.00..9243466274.01 rows=1 width=12) > -> Seq Scan on sli_test a (cost=0.00..9243465024.00 rows=50 > width=12) > Filter: (NOT (SubPlan 1)) > SubPlan 1 > -> Materialize (cost=0.00..18359.60 rows=50919 width=4) > -> Seq Scan on sli_test2 b (cost=0.00..17906.00 > rows=50919 width=4) > Filter: (id < 5) That takes about 500ms here, on 9.1. What did you expect? What's the EXPLAIN ANALYZE if your results differ? You've failed to show your NOT EXISTS query and plan for comparison, but I'm guessing: regress=# explain analyze select max(a.info)from sli_test a where not exists (select 1 from sli_test2 b where a.id=b.id and b.id<5); QUERY PLAN Aggregate (cost=50256.22..50256.23 rows=1 width=12) (actual time=671.993..671.993 rows=1 loops=1) -> Hash Anti Join (cost=18510.11..47877.04 rows=951671 width=12) (actual time=103.048..459.508 rows=950001 loops=1) Hash Cond: (a.id = b.id) -> Seq Scan on sli_test a (cost=0.00..16274.00 rows=100 width=16) (actual time=0.016..71.871 rows=100 loops=1) -> Hash (cost=17906.00..17906.00 rows=48329 width=4) (actual time=86.948..86.948 rows=4 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 1758kB -> Seq Scan on sli_test2 b (cost=0.00..17906.00 rows=48329 width=4) (actual time=0.018..76.489 rows=4 loops=1) Filter: (id < 5) Total runtime: 672.379 ms (9 rows) which, if so performs signficiantly worse. I'm struggling to understand what exactly your complaint is. It's kind of hard to work out what the intention of the query is in real-world terms to suggest alternatives, too. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7559: syslogger doesn't close stdout and stderr
On 20.09.2012 00:05, Reinhard Max wrote: On Wed, 19 Sep 2012 at 12:39, Tom Lane wrote: reinh...@m4x.de writes: And what makes /dev/null necessarily the right substitute? Because it is what virtually all deamons have been doing for the last 3..4 decades? I don't think we should change this within Postgres. We removed logic associated with daemonization altogether in 9.2 Huh - why that? I believe Tom is referring to the removal of silent_mode in 9.2, see http://archives.postgresql.org/pgsql-general/2011-06/msg00796.php and http://archives.postgresql.org/pgsql-hackers/2011-06/msg02156.php. "We removed logic associated with daemonization" meant that the logic was removed from postmaster, because the preferred way to do it is by calling "pg_ctl start". pg_ctl redirects to /dev/null as you'd expect. - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6412: psql & fe-connect truncate passwords
On 15.02.2012 07:09, Andy Grimm wrote: Sorry that it's been a couple of weeks, but I have gotten around to working on a patch that address more of these concerns. The attached patch should 1) allow arbitrary length passwords to be read from a file via initdb --pwfile 2) allow the client to accept a password of arbitrary length at the password prompt 3) allow a password of arbitrary length in a pgpass file In #2 I say "allow the client to accept", because there's a pq_getmessage call in src/backend/libpq/auth.c which limits the password message length to 1000 characters. Changing that part of the code should allow longer passwords, but there may be other lurking backend issues after that, and I'm not concerned about going beyond 1000 at this point. Thanks for the patch. A few comments: * Most of the simple_prompt() calls are for passwords, which now have no limit, but there's a few others. How about we remove the maxlen argument altogether, and just have it always return a malloc'd string that can be arbitrarily long. (maybe with a sanity-check limit within simple_prompt(), like 100k) * .pg_service.conf handling still has a fixed limit on line length of 256 bytes. See parseServiceInfo() in fe-connect. I think we should lift that limit too, for the sake of consistency. You can pass a password in the service file, too. * Missed a few simple_prompt() calls in contrib (oid2name, vacuumlo, pgbench) - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
