Re: [BUGS] In 8.2, shutdown wrongly caused automatic restart
On Thu, Aug 5, 2010 at 1:45 AM, Alvaro Herrera wrote: > Excerpts from Tom Lane's message of mié ago 04 12:37:23 -0400 2010: >> My recollection is that that change was associated with some pretty >> significant revisions to the postmaster state machine. I'm concerned >> about the risks involved in back-patching that. This seems to be a >> corner case with pretty minimal consequences anyway, so I'm inclined >> to leave 8.2 alone. > > IIRC this is the kind of thing that "dead-end backends" were invented > for. It was too a large patch for backpatching, IMHO. Though I thought about this issue for a while, I end up agreeing that the back-patching has a risk. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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 #5604: Setting NOT NULL on inherited column turns to real column in dump
The following bug has been logged online: Bug reference: 5604 Logged by: Jon Erdman (aka StuckMojo) Email address: postgre...@thewickedtribe.net PostgreSQL version: Tested 9.0, 8.3 Operating system: Ubuntu Lucid 10.04 Description:Setting NOT NULL on inherited column turns to real column in dump Details: I think the fix here is relatively simple: make NOT NULL on an inherited column dump as an ALTER TABLE. If you set NOT NULL on an inherited column in a child table, then drop the column from the parent, it's gone from both and all is well. However, if you dump and restore the db, then drop the parent column, the inherited column remains in the child table. This is a result of the NOT NULL dumping as a column create in the child, which I assume then shadows the inherited column after restore. This example illustrates the issue nicely I think: j...@stuck-64:~$ cat bug.sh createdb foo psql -X foo
Re: [BUGS] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
On Fri, Aug 6, 2010 at 12:20 PM, Tom Lane wrote: > Hm, I was looking at that and thinking it seemed unsafe for entirely > different reasons. But if you didn't write it, who did? Incrementally Updated Backups technique has been in the document since 8.2. In the development cycle of 9.0, I and Heikki appended some description to make the technique more robust; pg_control file should be backed up first and the backup end point should be checked before running query. If it's unsafe, I'm happy to modify it. Which part looks unsafe? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
Simon Riggs writes: > On Thu, 2010-08-05 at 11:28 -0700, valgog wrote: >> It was done as documented in >> http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html > The procedure used does differ from that documented. However, IMHO the > procedure *documented* is *not* safe and could lead to corrupt indexes > in the way described, since the last recovered point might be mid-way > between two halves of an index split record, which will never be > corrected during HS. Hm, I was looking at that and thinking it seemed unsafe for entirely different reasons. But if you didn't write it, who did? 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 #5599: Vacuum fails due to index corruption issues
On Thu, Aug 5, 2010 at 7:28 PM, Alvaro Herrera wrote: > > The scope is further reduced by the fact that this only seems to happen > on Windows, and then only when the antivirus is messing around with the > files. So I suspect this could be triggered lots of ways. Imagine a ZFS volume that runs out of space temporarily. Even truncate would need additional blocks to replace the meta information. Or a network filesystem like AFS where your kerberos tickets have expired and you get a permission failure until they've been renewed. Or, in the case of a very large table being truncated I suspect there's a CHECK_FOR_INTERRUPTS lying around that can cancel the backend at the wrong time. It would be nice to have a regression test harness that caused system calls to fail randomly -- the difficult part would be testing the results. -- greg -- 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 #5603: pg_tblspc and pg_twoface directories get deleted when starting up service
The following bug has been logged online: Bug reference: 5603 Logged by: Nacho Mezzadra Email address: nachomezza...@gmail.com PostgreSQL version: 8.3.11 Operating system: Red Hat Enterprise 5.3 Description:pg_tblspc and pg_twoface directories get deleted when starting up service Details: This issue happened not very frequently, but it happened to me 3 times, in 3 different Red Hat servers. The thing is that when stopping the Postgresql service with the "/sbin/service postgresql-8.3 stop" command, and after that starting it with the "/sbin/service postgresql-8.3 start" command (haven't tried with the restart one though), a few times both pg_tblspc and pg_twoface directories (inside data directory) get somehow deleted and hence the start service command fails. Looking in the log files I find the following error: 2010-07-19 16:54:55 ISTFATAL: could not open directory "pg_tblspc": No such file or directory So I manually create the "pg_tblspc" directory, and then try to start again the service unsuccessfully, getting this time a similar error, but saying that pg_twoface directory doesn't exist. After creating the pg_twoface directory, service can be successfully started. Please note that all these always happened running the service command as root. All 3 linux boxes are running over a VMWare host. -- 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] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
On Thu, 2010-08-05 at 11:28 -0700, valgog wrote: > > This process seems almost entirely unrelated to the documented way of > > doing it; I'm not surprised that you end up with some files not in sync. > > Please see pg_start_backup and friends. > It was done as documented in > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html The procedure used does differ from that documented. However, IMHO the procedure *documented* is *not* safe and could lead to corrupt indexes in the way described, since the last recovered point might be mid-way between two halves of an index split record, which will never be corrected during HS. What I find surprising is that the technique the OP describes should be safe, assuming step 5 waits for the correct point of consistency before attempting to run queries. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Peter Eisentraut writes: > On tor, 2010-08-05 at 14:38 -0400, Tom Lane wrote: >> Huh? The functionality proposed for removal is only that of omitting >> an explicit delimiter argument for string_agg(). Since the default >> value (an empty string) doesn't seem to be the right thing all that >> often anyway, I'm not following why you think this is a significant >> downgrade. > I just think it's useful to have the one-argument version. I understand > the functionality is available in other ways. Well, other things being equal I'd have preferred to keep the one-argument version too. But this thread has made it even clearer than before that we will get continuing bug reports if we leave the behavior alone. I don't think the ability to leave off the delimiter value is worth the amount of confusion it'll cause. 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 #5595: Documentation is not installs from VPATH build.
Peter Eisentraut writes: > I think I can explain this behavior. When building outside the source > tree, in looks in the build tree and then in the source tree for > documentation to install. This is done by looking for a 'html' > directory. When you already have an 'html' directory in the source tree > before you run configure, an 'html' directory is also created in the > build tree, because the build tree is created by making an empty copy of > every directory in the source tree. So then it thinks the documentation > is present in the build tree and installs that, but the directory is > empty, so nothing is installed. Hm, but prep_buildtree doesn't create an html_stamp file in the build tree, so this explanation doesn't seem to hold water completely. Why didn't it try to build new docs in the build tree? 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 #5595: Documentation is not installs from VPATH build.
On tis, 2010-08-03 at 14:36 +, Dmtiriy Igrishin wrote: > When 'configure' executed in a directory outside the source tree the > documentation is not installs later nevertheless the "gmake > install-docs" or > "gmake install-world" (I tried to build with "gmake world" also) typed > to > install PostgreSQL. > When 'configure' executed inside the source tree - all is okay. I think I can explain this behavior. When building outside the source tree, in looks in the build tree and then in the source tree for documentation to install. This is done by looking for a 'html' directory. When you already have an 'html' directory in the source tree before you run configure, an 'html' directory is also created in the build tree, because the build tree is created by making an empty copy of every directory in the source tree. So then it thinks the documentation is present in the build tree and installs that, but the directory is empty, so nothing is installed. We could fix this in several ways. Either hardcode exceptions for the 'html' directory in prep_buildtree, or only create directories that contain a makefile, which could also be achieved by pruning empty directories at the end of prep_buildtree. All of these might have other side effects I'm not seeing right now. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On tor, 2010-08-05 at 14:38 -0400, Tom Lane wrote: > Huh? The functionality proposed for removal is only that of omitting > an explicit delimiter argument for string_agg(). Since the default > value (an empty string) doesn't seem to be the right thing all that > often anyway, I'm not following why you think this is a significant > downgrade. I just think it's useful to have the one-argument version. I understand the functionality is available in other ways. -- 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 #5599: Vacuum fails due to index corruption issues
Heikki Linnakangas writes: > On 05/08/10 21:28, Alvaro Herrera wrote: >> Excerpts from Tom Lane's message of jue ago 05 14:01:15 -0400 2010: >>> Maybe write-the-buffers-first is a sufficient longterm solution. >> >> Yeah, perhaps it is, though it's a pity that a single platform problem >> is going to slow down everyone else. > How about performing the truncate as usual, but if it fails, fill the > truncated portion with zeros instead? This is blithely assuming that you're going to be able to do anything at all to the file after having failed to truncate it. The permissions problem might be persistent, or you might crash, or several other things. If we could do this safely, we could also do the original #3 solution (write the dirty buffers for the to-be-truncated block range only after failing to truncate). 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 #5599: Vacuum fails due to index corruption issues
On 05/08/10 21:28, Alvaro Herrera wrote: Excerpts from Tom Lane's message of jue ago 05 14:01:15 -0400 2010: Maybe write-the-buffers-first is a sufficient longterm solution. Yeah, perhaps it is, though it's a pity that a single platform problem is going to slow down everyone else. How about performing the truncate as usual, but if it fails, fill the truncated portion with zeros instead? Zeroed pages should be handled gracefully, and this would be a very non-invasive fix. Now if the write() to zero the pages fails too, I guess we'll have to panic, but that's not much different from flushing the dirty pages first. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
valgog writes: >> This process seems almost entirely unrelated to the documented way of >> doing it; I'm not surprised that you end up with some files not in sync. >> Please see pg_start_backup and friends. > It was done as documented in > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html Oh, I missed that you were copying from a hot-standby slave rather than the master. Still, your procedure doesn't clearly match step 2, and that step is the weak point of the process --- the grandchild slave isn't consistent until it's replayed WAL far enough, but we don't have any automated support for verifying that. (I hope that's going to get improved in 9.1.) I suspect you allowed the grandchild to go live before it was really consistent. 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Robert Haas writes: > On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane wrote: >> Next question: exactly how should the variant HINT be phrased? >> I'm inclined to drop the bit about explicit casts and make it read >> something like >> >> HINT: No aggregate function matches the given name and argument >> types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all >> regular arguments of the aggregate. > Could we arrange to emit this error message only when there is an > aggregate with the same name but different arguments? That'd move it into the category of needing significant restructuring, I'm afraid. At the moment it's not apparent that it's worth it. We're already able to limit the use of the variant hint to a pretty darn narrow set of cases, and it is only a hint after all. 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 12:16 PM, Tom Lane wrote: > HINT: No aggregate function matches the given name and argument > types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all > regular arguments of the aggregate. +1 David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane wrote: > Josh Berkus writes: >>> Well, maybe we need to expend some more sweat on the error message then. >>> But this patch was still a prerequisite thing, because without it there >>> is no error that we can complain about. > >> Yes, I'd say an addition to the HINT is in order *assuming* at that >> stage we can tell if the user passed an ORDER BY or not. > > I was just looking at this, and realized I was mistaken earlier: the > error is issued in ParseFuncOrColumn, which already is passed the > agg_order list, so actually it's completely trivial to tell whether > a variant error message is appropriate. I suggest that we key it off > there being not just an ORDER BY, but an ORDER BY with more than one > element; if there's only one then this cannot be the source of > confusion. > > Next question: exactly how should the variant HINT be phrased? > I'm inclined to drop the bit about explicit casts and make it read > something like > > HINT: No aggregate function matches the given name and argument > types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all > regular arguments of the aggregate. Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus writes: >> Well, maybe we need to expend some more sweat on the error message then. >> But this patch was still a prerequisite thing, because without it there >> is no error that we can complain about. > Yes, I'd say an addition to the HINT is in order *assuming* at that > stage we can tell if the user passed an ORDER BY or not. I was just looking at this, and realized I was mistaken earlier: the error is issued in ParseFuncOrColumn, which already is passed the agg_order list, so actually it's completely trivial to tell whether a variant error message is appropriate. I suggest that we key it off there being not just an ORDER BY, but an ORDER BY with more than one element; if there's only one then this cannot be the source of confusion. Next question: exactly how should the variant HINT be phrased? I'm inclined to drop the bit about explicit casts and make it read something like HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
> Well, maybe we need to expend some more sweat on the error message then. > But this patch was still a prerequisite thing, because without it there > is no error that we can complain about. Yes, I'd say an addition to the HINT is in order *assuming* at that stage we can tell if the user passed an ORDER BY or not. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
> This process seems almost entirely unrelated to the documented way of > doing it; I'm not surprised that you end up with some files not in sync. > Please see pg_start_backup and friends. > > regards, tom lane > Hallo Tom, It was done as documented in http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html With my best regards, -- Valentine -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:45 AM, Tom Lane wrote: >> I'm confused: that looks like the two-argument form to me. Have I missed >> something? > > Yeah, the whole point of the thread: that's not a call of a two-argument > aggregate. It's a call of a one-argument aggregate, using a two-column > sort key to order the aggregate input rows. OH!. Wow, weird. I never noticed that. >> It confuses the shit out of me. It says "string_agg(text)" doesn't exist >> when that clearly is not the name of the function you've called. > > Well, maybe we need to expend some more sweat on the error message then. > But this patch was still a prerequisite thing, because without it there > is no error that we can complain about. Yeah, understood. Best, David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:42 AM, Thom Brown wrote: >>> LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; >>> ^ >> >> I'm confused: that looks like the two-argument form to me. Have I missed >> something? >> >>> HINT: No function matches the given name and argument types. You might >>> need to add explicit type casts. >>> >>> It's not perfect (I don't think it's practical to get the HINT to >>> read "Put the ORDER BY at the end" ;-)) but at least it should >>> get people pointed in the right direction when they do this. >> >> It confuses the shit out of me. It says "string_agg(text)" doesn't exist >> when that clearly is not the name of the function you've called. >> > > What function name do you believe was called? The message says: string_agg(f1 order by f1, ',') That looks like string_agg(text, text) or string_agg(anyelement, text). Best, David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
"David E. Wheeler" writes: > On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: >> Applied to HEAD and 9.0. The mistaken case will now yield this: >> regression=# select string_agg(f1 order by f1, ',') from text_tbl; >> ERROR: function string_agg(text) does not exist > I'm confused: that looks like the two-argument form to me. Have I missed > something? Yeah, the whole point of the thread: that's not a call of a two-argument aggregate. It's a call of a one-argument aggregate, using a two-column sort key to order the aggregate input rows. > It confuses the shit out of me. It says "string_agg(text)" doesn't exist when > that clearly is not the name of the function you've called. Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 12:25, Tom Lane wrote: > regression=# select string_agg(f1 order by f1, ',') from text_tbl; > ERROR: function string_agg(text) does not exist > LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > It's not perfect (I don't think it's practical to get the HINT to > read "Put the ORDER BY at the end" ;-)) but at least it should > get people pointed in the right direction when they do this. Not to mention I think most of the confusion came from using the 1 argument version first (with an order by) and then jumping straight to the 2 arg version. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 5 August 2010 19:39, David E. Wheeler wrote: > On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: > >> Applied to HEAD and 9.0. The mistaken case will now yield this: >> >> regression=# select string_agg(f1 order by f1, ',') from text_tbl; >> ERROR: function string_agg(text) does not exist >> LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; >> ^ > > I'm confused: that looks like the two-argument form to me. Have I missed > something? > >> HINT: No function matches the given name and argument types. You might need >> to add explicit type casts. >> >> It's not perfect (I don't think it's practical to get the HINT to >> read "Put the ORDER BY at the end" ;-)) but at least it should >> get people pointed in the right direction when they do this. > > It confuses the shit out of me. It says "string_agg(text)" doesn't exist when > that clearly is not the name of the function you've called. > What function name do you believe was called? -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: > Applied to HEAD and 9.0. The mistaken case will now yield this: > > regression=# select string_agg(f1 order by f1, ',') from text_tbl; > ERROR: function string_agg(text) does not exist > LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; > ^ I'm confused: that looks like the two-argument form to me. Have I missed something? > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > It's not perfect (I don't think it's practical to get the HINT to > read "Put the ORDER BY at the end" ;-)) but at least it should > get people pointed in the right direction when they do this. It confuses the shit out of me. It says "string_agg(text)" doesn't exist when that clearly is not the name of the function you've called. Best, David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Peter Eisentraut writes: > I vote against this patch. There are plenty of other places that SQL is > confusing, and this move seems excessive to me, and I find the > functionality that is proposed for removal quite useful. Huh? The functionality proposed for removal is only that of omitting an explicit delimiter argument for string_agg(). Since the default value (an empty string) doesn't seem to be the right thing all that often anyway, I'm not following why you think this is a significant downgrade. 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: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On ons, 2010-08-04 at 18:19 -0400, Tom Lane wrote: > > This policy also implies that we are never going to allow default > arguments for aggregates, or at least never have any built-in ones > that use such a feature. > > By my count the following people had offered an opinion on making > this change: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the > patch? I vote against this patch. There are plenty of other places that SQL is confusing, and this move seems excessive to me, and I find the functionality that is proposed for removal quite useful. -- 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 #5599: Vacuum fails due to index corruption issues
Excerpts from Tom Lane's message of jue ago 05 14:01:15 -0400 2010: > You're right, I misremembered. That code is just plain gone in 9.0: > http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/nbtree.c.diff?r1=1.174;r2=1.175;f=h > > Still, we have a live issue with heap truncation during plain VACUUM. > However, the scope of the problem seems a lot less than I was thinking. The scope is further reduced by the fact that this only seems to happen on Windows, and then only when the antivirus is messing around with the files. > Maybe write-the-buffers-first is a sufficient longterm solution. Yeah, perhaps it is, though it's a pity that a single platform problem is going to slow down everyone else. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
I wrote: > Well, I forgot that an aggregate involves more than one catalog row ;-). > So it's a bit bigger patch than that, but still pretty small and safe. > See attached. Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read "Put the ORDER BY at the end" ;-)) but at least it should get people pointed in the right direction when they do this. 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 #5599: Vacuum fails due to index corruption issues
Alvaro Herrera writes: > Excerpts from Tom Lane's message of jue ago 05 13:19:41 -0400 2010: >> In any case, the removal of VACUUM FULL didn't completely disable >> shrinking of btree indexes did it? I don't recall having removed >> that. > I see no call to RelationTruncate in the btvacuumscan code, but then it > was only called in vacuum full before. I'm not sure how it worked > previously with only lazy vacuum. Did we simply leave the pages as free > for a later extension? You're right, I misremembered. That code is just plain gone in 9.0: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/nbtree.c.diff?r1=1.174;r2=1.175;f=h Still, we have a live issue with heap truncation during plain VACUUM. However, the scope of the problem seems a lot less than I was thinking. Maybe write-the-buffers-first is a sufficient longterm solution. 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 #5599: Vacuum fails due to index corruption issues
Excerpts from Tom Lane's message of jue ago 05 13:19:41 -0400 2010: > In any case, the removal of VACUUM FULL didn't completely disable > shrinking of btree indexes did it? I don't recall having removed > that. I see no call to RelationTruncate in the btvacuumscan code, but then it was only called in vacuum full before. I'm not sure how it worked previously with only lazy vacuum. Did we simply leave the pages as free for a later extension? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] string_agg delimiter having no effect with order by
2010/8/5 Tom Lane : > Pavel Stehule writes: >> but still when we remove one parametric string_agg, then this issue >> will not be documented. > > How so? This paragraph will still be there: > > > When dealing with multiple-argument aggregate functions, note that the > ORDER BY clause goes after all the aggregate arguments. > For example, this: > > SELECT string_agg(a, ',' ORDER BY a) FROM table; > > not this: > > SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect > > The latter is syntactically valid, but it represents a call of a > single-argument aggregate function with two ORDER BY keys > (the second one being rather useless since it's a constant). > > > > regards, tom lane > ok Regards Pavel Stehule -- 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] string_agg delimiter having no effect with order by
On 5 August 2010 16:31, Pavel Stehule wrote: > 2010/8/5 Tom Lane : >> Pavel Stehule writes: >> The same problem can be with custom aggregates :( so this syntax isn't >> too robust. >> >> BTW, I'm really not worried about that case. By the time someone is >> advanced enough to have written their own multi-argument aggregate >> definitions, they'll have absorbed the idea that the ORDER BY goes at >> the end. What we need to accomplish here is just to not set traps at >> the feet of novices using the feature for the first time. Which is >> why I think it's sufficient to have a policy of not having built-in >> aggregates that conflict in this way; I'm not proposing that we restrict >> or discourage custom aggregates with optional arguments. >> > > +1 > > but still when we remove one parametric string_agg, then this issue > will not be documented. > > Pavel > I think the problem is people like me not reading this important information:http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SYNTAX-AGGREGATES It's clear as day upon reading that. It's a case of one page reading: string_agg(expression [, delimiter ] ) and another reading: aggregate_name (expression [ , ... ] [ order_by_clause ] ) and you effectively end up with: string_agg(expression [, delimiter ] [ order_by_clause ] ) -- Thom Brown Registered Linux user: #516935 -- 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 #5599: Vacuum fails due to index corruption issues
Alvaro Herrera writes: > Excerpts from Tom Lane's message of jue ago 05 12:36:24 -0400 2010: >> Gone? Looks like it's still there to me. > I mean the btree code that does the truncation on vacuum full is > truncated. There are other uses for truncation, but it doesn't look to > that they are as problematic ... or are they? I think what Hitesh reported is a special case of a generic problem. > Hmm, I guess truncation of heap on lazy vacuum is still a problem > precisely because page compaction will be forgotten. Page compaction is the least of it :-( Imagine that we have some rows at the end of a table, we delete them, we vacuum before the next checkpoint. Vacuum decides it can now truncate away the last pages, but fails to do so. The original page state is still on disk, which means we have lost the fact of the deletion --- the rows are now effectively live again, though their index entries are probably gone. In any case, the removal of VACUUM FULL didn't completely disable shrinking of btree indexes did it? I don't recall having removed that. 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 #5599: Vacuum fails due to index corruption issues
Excerpts from Tom Lane's message of jue ago 05 12:36:24 -0400 2010: > Alvaro Herrera writes: > > Excerpts from Tom Lane's message of jue ago 05 11:06:57 -0400 2010: > >> 1. Write the dirty buffers before dropping them. Kind of ugly from a > >> performance viewpoint, but simple and safe. > > > I think "simple" is good, considering that this code is gone in 9.0 and > > HEAD. IMHO investing too much effort on this problem is not worth it. > > Gone? Looks like it's still there to me. I mean the btree code that does the truncation on vacuum full is truncated. There are other uses for truncation, but it doesn't look to that they are as problematic ... or are they? Hmm, I guess truncation of heap on lazy vacuum is still a problem precisely because page compaction will be forgotten. > > Perhaps it'd be good to come up with a better solution for HEAD: > > Yeah, the panic-on-replay aspect is troublesome. I feel like we need a > rethink here. But I agree that solution #1 is the only one that feels > safe enough for backpatching. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
"Chris" writes: > A Hot-Standby database is being backed up to a new machine like this: > 0. pause WAL file deletion from Hot-Standby > 1. backup pg_control > 2. sync data and tablespace files > 3. wait for checkout using pg_controldata and waiting for UNDO address > change > 4. copy all WAL files > 5. recover the newly created database using WAL files This process seems almost entirely unrelated to the documented way of doing it; I'm not surprised that you end up with some files not in sync. Please see pg_start_backup and friends. 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 #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
The following bug has been logged online: Bug reference: 5602 Logged by: Chris Email address: christoph.sprin...@magicinternet.de PostgreSQL version: 9.0beta3 Operating system: Debian 5.0.3 Description:Recovering from Hot-Standby file backup leads to the currupted indexes Details: A Hot-Standby database is being backed up to a new machine like this: 0. pause WAL file deletion from Hot-Standby 1. backup pg_control 2. sync data and tablespace files 3. wait for checkout using pg_controldata and waiting for UNDO address change 4. copy all WAL files 5. recover the newly created database using WAL files The database recovers and starts normally, but some of the indexes are corrupt afterwards. I could not find any strange log record in logs for the recovered database. This is a definition of a index that is corrupt afterwards: CREATE INDEX idx_tbl_textcol1_textcol2 ON tbl USING btree (textcol1, textcol2) TABLESPACE data5_tbs; Thank you in advance, Chris -- 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 #5599: Vacuum fails due to index corruption issues
Alvaro Herrera writes: > Excerpts from Tom Lane's message of jue ago 05 11:06:57 -0400 2010: >> 1. Write the dirty buffers before dropping them. Kind of ugly from a >> performance viewpoint, but simple and safe. > I think "simple" is good, considering that this code is gone in 9.0 and > HEAD. IMHO investing too much effort on this problem is not worth it. Gone? Looks like it's still there to me. > Perhaps it'd be good to come up with a better solution for HEAD: Yeah, the panic-on-replay aspect is troublesome. I feel like we need a rethink here. But I agree that solution #1 is the only one that feels safe enough for backpatching. 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 #5599: Vacuum fails due to index corruption issues
Excerpts from Tom Lane's message of jue ago 05 11:06:57 -0400 2010: > Seems like we need to think harder about recovering from a truncate > failure. A few random ideas: Ugh. > 1. Write the dirty buffers before dropping them. Kind of ugly from a > performance viewpoint, but simple and safe. I think "simple" is good, considering that this code is gone in 9.0 and HEAD. IMHO investing too much effort on this problem is not worth it. Perhaps it'd be good to come up with a better solution for HEAD: > 3. Don't discard the dirty buffers until after successfully truncating. > The hard part here is to be sure no other process (like bgwriter) will > try to write them in between. I don't see a way to guarantee that, > at least not without interlock infrastructure that doesn't exist today. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] string_agg delimiter having no effect with order by
Pavel Stehule writes: > but still when we remove one parametric string_agg, then this issue > will not be documented. How so? This paragraph will still be there: When dealing with multiple-argument aggregate functions, note that the ORDER BY clause goes after all the aggregate arguments. For example, this: SELECT string_agg(a, ',' ORDER BY a) FROM table; not this: SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect The latter is syntactically valid, but it represents a call of a single-argument aggregate function with two ORDER BY keys (the second one being rather useless since it's a constant). 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] string_agg delimiter having no effect with order by
2010/8/5 Tom Lane : > Pavel Stehule writes: > The same problem can be with custom aggregates :( so this syntax isn't > too robust. > > BTW, I'm really not worried about that case. By the time someone is > advanced enough to have written their own multi-argument aggregate > definitions, they'll have absorbed the idea that the ORDER BY goes at > the end. What we need to accomplish here is just to not set traps at > the feet of novices using the feature for the first time. Which is > why I think it's sufficient to have a policy of not having built-in > aggregates that conflict in this way; I'm not proposing that we restrict > or discourage custom aggregates with optional arguments. > +1 but still when we remove one parametric string_agg, then this issue will not be documented. Pavel > 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 #5601: cannot create language plperl;
On Thu, Aug 5, 2010 at 00:13, Rene Novotny wrote: > > The following bug has been logged online: > PostgreSQL version: 9.0 beta 4 > Operating system: Win 7 64 bit > Description: cannot create language plperl; > Details: > > ERROR: Could not load library > c:/Program Files/PostgreSQL/9.0/lib/plperl.dll > unknown error 126 Near as I can tell that means it cant find a .dll or it is incompatible. > For a memory reasons we need to use 64 bit Windows > postgresql , you finally made one. > But when we installed Activestate 64 bit perl 5.12 perl > ( for 64 bit postgres i suppose 64 bit perl ) > it doesn't work. Please let me know , with what > perl you compiled the plperl.dll on windows ( i suppose that ActiveState ). If you are using the enterprisedb one click installer it looks like you need to grab something that has perl510.dll (maybe activestate 5.10?) Just a guess, I dont have a windows machine here (or really know anything about the windows build) I just downloaded the windows .zip file and skimmed the contents of plperl.dll. Of course you are always welcome to build your own postgresql if you need 5.12. -- 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] string_agg delimiter having no effect with order by
Pavel Stehule writes: The same problem can be with custom aggregates :( so this syntax isn't too robust. BTW, I'm really not worried about that case. By the time someone is advanced enough to have written their own multi-argument aggregate definitions, they'll have absorbed the idea that the ORDER BY goes at the end. What we need to accomplish here is just to not set traps at the feet of novices using the feature for the first time. Which is why I think it's sufficient to have a policy of not having built-in aggregates that conflict in this way; I'm not proposing that we restrict or discourage custom aggregates with optional arguments. 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 #5599: Vacuum fails due to index corruption issues
Hitesh Bhambhani writes: >> From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] >> Sent: Wednesday, August 04, 2010 11:30 PM >> There probably is. What kind of relation are the ones unable to truncate? >> Please see in pg_class where relfilenode = '41274' in this >> case: >> > [HiteshB] the relation is called pmoinstance_idx_pmotypeid. So the relation that couldn't be truncated is indeed the same one complained of in the later message. What it looks like to me is that page 30 was deleted, and then vacuum tried to truncate it off the index altogether, and that failed because of Windows randomness, and then later it's trying to delete page 30 again. Which naturally fails because the parent downlink entry is long gone. But it's odd that it tries to delete page 30 twice. The page should have been in BTP_DELETED state before the truncate attempt, and therefore should still be dead later, so why's it trying again? [ thinks for a bit ... ] I do see a way that could happen. The page could still be live on disk when we reach smgrtruncate(): the update to BTP_DELETED state might only exist in a dirty shared buffer. And lookee here what smgrtruncate does: /* * Get rid of any buffers for the about-to-be-deleted blocks. bufmgr will * just drop them without bothering to write the contents. */ DropRelFileNodeBuffers(reln->smgr_rnode, forknum, isTemp, nblocks); So we throw away the BTP_DELETED state update without ever writing it to disk, and then when the truncate fails, the old page state is still out there, ready to confuse us later. Seems like we need to think harder about recovering from a truncate failure. A few random ideas: 1. Write the dirty buffers before dropping them. Kind of ugly from a performance viewpoint, but simple and safe. 2. Treat truncation failure as a PANIC condition; then WAL replay will fix things. Unpleasant. Actually, worse than unpleasant: I think what the comment in RelationTruncate() is pointing out is that if the failure is repeatable, we'd fail to recover at all. 3. Don't discard the dirty buffers until after successfully truncating. The hard part here is to be sure no other process (like bgwriter) will try to write them in between. I don't see a way to guarantee that, at least not without interlock infrastructure that doesn't exist today. And see also that comment in RelationTruncate(). Seems like the whole problem of coping with truncation failure needs more thought than we've given 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] string_agg delimiter having no effect with order by
2010/8/5 Tom Lane : > Thom Brown writes: >> On 5 August 2010 10:29, Greg Stark wrote: >>> On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule >>> wrote: The same problem can be with custom aggregates :( so this syntax isn't too robust. We can support Oracle's syntax in future releases, where syntax divide aggregate call and ORDER BY clause. >>> >>> What syntax is that? > >> An example I've found is: >> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees >> FROM emp >> GROUP BY deptno; > > That wouldn't help this problem in the least. The problem is that > novices unfamiliar with the SQL-standard aggregrate ORDER BY syntax > may try to put the ORDER BY in the wrong place. Offering a different > syntax won't stop them from doing that. The only way it might stop > would be if we documented *only* the Oracle syntax and not the > spec-compliant syntax. Which ain't gonna happen. > > [ does a bit more research ... ] Actually, the syntax Thom mentions > is not Oracle-specific; it's in SQL:2008, and AFAICT it means something > different from an aggregate ORDER BY anyway. Maybe Pavel had something > else in mind. But my point is still that offering a different syntax > doesn't fix the problem unless we eliminate the mistake-prone syntax; > which we can't because it's in the spec. > I though this syntax - and what I know Oracle use it for explicit order and I found lot of sources on net, where is syntax of aggregates like name(parameters) [within group ( order by ... ) ] but my knowledge of this subject is minimal, based on Oracle doc, when I worked on string_agg function. I agree, so different syntax doesn't remove a risks, but can decrease some risks. SQL has lot of a possible dangerous syntaxes and everybody can selects the most robust syntax. But this issue can be solved a better documentation. Regards Pavel > 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] string_agg delimiter having no effect with order by
Thom Brown writes: > On 5 August 2010 10:29, Greg Stark wrote: >> On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule >> wrote: >>> The same problem can be with custom aggregates :( so this syntax isn't >>> too robust. We can support Oracle's syntax in future releases, where >>> syntax divide aggregate call and ORDER BY clause. >> >> What syntax is that? > An example I've found is: > SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees > FROM emp > GROUP BY deptno; That wouldn't help this problem in the least. The problem is that novices unfamiliar with the SQL-standard aggregrate ORDER BY syntax may try to put the ORDER BY in the wrong place. Offering a different syntax won't stop them from doing that. The only way it might stop would be if we documented *only* the Oracle syntax and not the spec-compliant syntax. Which ain't gonna happen. [ does a bit more research ... ] Actually, the syntax Thom mentions is not Oracle-specific; it's in SQL:2008, and AFAICT it means something different from an aggregate ORDER BY anyway. Maybe Pavel had something else in mind. But my point is still that offering a different syntax doesn't fix the problem unless we eliminate the mistake-prone syntax; which we can't because it's in the spec. 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] My computer shut down automatically
wrote: > I tried to uninstall Postgresql. But, "data" folder was denied > to remove. How? And how was it installed in the first place? > When I tried to remove "data" folder in 8.4 folder by force, my > computer shut down and startup automatically. Tried to remove it how? > Can I delete "data" folder? If you uninstalled PostgreSQL, I don't see that it would be PostgreSQL stopping you. In general, PostgreSQL doesn't stop you anyway, beyond having files open when it's running. > (When I open the folder, my computer shut down, too) I've never heard of anything remotely like that. Have you checked the Windows logs? You might also see if anything unusual is showing up on the process list. Before posting again, please read this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- 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 #5599: Vacuum fails due to index corruption issues
Hitesh Bhambhani wrote: > Could you give an example of what an 'extraordinary circumstance' > would be? Normal vacuums will remove old tuples (versions of rows) which can no longer be seen by any transaction, and make that space available for re-use within the PostgreSQL files. It will not normally give space back to the OS, but that's usually a *good* thing, because normally the space will soon be needed again by PostgreSQL, and it would be less efficient to constantly be giving space back and allocating it again. If you neglect to vacuum aggressively enough, or do a mass UPDATE or DELETE which affects a large percentage of your rows, without anticipating that you will need that space again soon, you might want to do aggressive maintenance to shrink the PostgreSQL files. VACUUM FULL will move tuples around within the table to free up space at the end so that it can be released. But wait -- you probably *still* don't want to use VACUUM FULL, because it is *very* slow and will bloat your indexes, requiring a REINDEX to restore decent performance. CLUSTER will rewrite the table without dead space and will rebuild the indexes -- usually much faster than VACUUM FULL. But CLUSTER needs room for a second copy of the table in order to copy it. If you have a very bloated table which you want to shrink and you don't have room for a second copy of it, *that* is the time to consider VACUUM FULL (usually followed by REINDEX). If you ever find you *do* need to run VACUUM FULL, you probably need to re-evaluate your maintenance procedures to see how you can avoid having to do it again. -Kevin -- 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 #5599: Vacuum fails due to index corruption issues
Greg, thanks for your answers. My comments below... > From: gsst...@gmail.com [mailto:gsst...@gmail.com] On Behalf Of Greg Stark > Sent: Wednesday, August 04, 2010 9:35 PM > Firstly, the current release of 8.2 is 8.2.17. There are a long list of bugs > fixed in those intervening releases including one involving vacuum truncating > relations. I don't think it's the same problem but I would recommend updating > immediately to 8.2.17. > [HiteshB] I have noted your recommendation and will work with our Product Management to upgrade to the latest and greatest. Although we can't change the version that the customer has installed (8.2.9-1). > > > Secondly we don't recommend running VACUUM FULL routinely. It should only be > necessary in extraordinary circumstances. Normally a plain VACUUM (or VACUUM > ANALYZE or VACUUM VERBOSE) should be sufficient as long as it's being run > regularly. Regular VACUUM without the "FULL" > has much less impact on the system. > [HiteshB] Point noted. The decision to use VACUUM FULL is something we'll re-examine. Could you give an example of what an 'extraordinary circumstance' would be? > > "Permission denied" smells like a Windows problem with concurrent file > operations. Are you sure you're not running any anti-virus software or backup > software which could have these files open and prevent Postgres from > performing regular file operations on its files? Many people have reported > other problems with anti-virus software in particular. > [HiteshB] We have worked with the customer to exclude the postgre directory from their antivirus scans. Hopefully we won't see this Permission issue again. Thanks again. Regards, Hitesh -- 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 #5599: Vacuum fails due to index corruption issues
Alvaro, thanks for your response. My comments below... > From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] > Sent: Wednesday, August 04, 2010 11:30 PM > There probably is. What kind of relation are the ones unable to truncate? > Please see in pg_class where relfilenode = '41274' in this > case: > [HiteshB] the relation is called pmoinstance_idx_pmotypeid. This is an index on a table created by our product. The definition for this index is: CREATE INDEX pmoinstance_idx_pmotypeid ON pmoinstances USING btree (pmotype_id); Regards, Hitesh -- 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 #5599: Vacuum fails due to index corruption issues
On Thu, Aug 5, 2010 at 12:55 PM, Hitesh Bhambhani wrote: > [HiteshB] I have noted your recommendation and will work with our Product > Management to upgrade to the latest and greatest. Although we can't change > the version that the customer has installed (8.2.9-1). > The latest and greatest is 8.4.x which has lots of new features including improvements to VACUUM. 9.0 will be out soon and will replace VACUUM FULL entirely too. However 8.2.17 is the same as the version you're using except with dozens of known bugs fixed and security holes patched. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] My computer shut down automatically
This is Seol. I have a problem with Postgresql, so let me know how to solve it. I'm using 8.4 version of Postgresql. The problem occured when I made a tablespace on "pgAdmin 3". I assigned the name and the location for a new tablespace. then a new tablespace was made. At this time, I realized that the location was mistaken.(I wanted to make it on the different location). So, I tried to delete it, but I was denied. Therefore, I tried to uninstall Postgresql. But, "data" folder was denied to remove. When I tried to remove "data" folder in 8.4 folder by force, my computer shut down and startup automatically. Can I delete "data" folder? I send you the composition of "data" folder. "C:/Program Files/PostgreSQL/8.4/data/pg_tblspc/16436/16436/..."(When I open the folder, my computer shut down, too) -- 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] string_agg delimiter having no effect with order by
On 5 August 2010 10:29, Greg Stark wrote: > On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule wrote: >> The same problem can be with custom aggregates :( so this syntax isn't >> too robust. We can support Oracle's syntax in future releases, where >> syntax divide aggregate call and ORDER BY clause. >> > > What syntax is that? > > -- > greg > An example I've found is: SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; -- Thom Brown Registered Linux user: #516935 -- 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] string_agg delimiter having no effect with order by
On Thu, Aug 5, 2010 at 5:18 AM, Pavel Stehule wrote: > The same problem can be with custom aggregates :( so this syntax isn't > too robust. We can support Oracle's syntax in future releases, where > syntax divide aggregate call and ORDER BY clause. > What syntax is that? -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
