Re: [HACKERS] [GENERAL] Allowing SYSDATE to Work
Josh Berkus wrote: > Matt, > > > I now agree completely. My purpose is to migrate Oracle databases to > > Posgres, and I had thought that Oracle didn't support CURRENT_DATE, > > CURRENT_TIMESTAMP, and so on. However, I've just learned otherwise. So, > > I think the proper migration process for a production database would be > > to first change the Oracle DB to use CURRENT_DATE (or some other > > standard psuedo column), since that will work properly under both Oracle > > and Postgres. > > Yep, or use the Orafce project.We're happy to support compatibility > syntax > in completely separate add-in projects. Just not in the core code. How does Orafce allow for grammar extensions like what would be needed for SYSDATE to work? (Note no parens) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
On Sun, 2006-11-19 at 12:03 -0800, David Fetter wrote:
> On Sun, Nov 19, 2006 at 12:01:15PM -0800, Joshua D. Drake wrote:
> > On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
> > > Tom,
> > >
> > > > Let's go with the easy fix. With regular 1GB segment size,
> > > > having a few empty files in the data directory isn't going to
> > > > hurt anyone.
> > >
> > > No, but it will confuse DBAs ("What the heck are all these 0B
> > > files?"). Maybe we should add code to VACUUM to look for these
> > > empty file segments and unlink them if they haven't been touched
> > > in a while (say, a day?).
> >
> > I don't buy this argument. A smart DBA isn't going to do any such
> > thing. If you go around deleting unknown files you deserve
> > everything you get. Especially if you are in the middle of a
> > PostgreSQL cluster tree.
>
> A mention in the README in that directory wouldn't hurt, tho.
Definitely :)
Sincerely,
Joshua D. Drake
>
> Cheers,
> D
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Heikki Linnakangas wrote:
Florian G. Pflug wrote:
Joshua D. Drake wrote:
On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
Tom,
Let's go with the easy fix. With regular 1GB segment size, having a
few
empty files in the data directory isn't going to hurt anyone.
No, but it will confuse DBAs ("What the heck are all these 0B
files?"). Maybe we should add code to VACUUM to look for these
empty file segments and unlink them if they haven't been touched in
a while (say, a day?).
I don't buy this argument. A smart DBA isn't going to do any such thing.
If you go around deleting unknown files you deserve everything you get.
Especially if you are in the middle of a PostgreSQL cluster tree.
Couldn't some application trigger this problem regularly (Say, once a
day while
importing new data), and therefore create an ever increasing number of
empty
files that you can't get rid of without backup,reinit and restore?
No, the old empty files would get reused if the relation grows again.
And they would get deleted if you truncate or drop the relation.
Ah, sorry, I had misunderstood that point. I believed that a new
segment would be created when the relation grows again, and the
empty file would linger around forever...
Thanks for your explaination
greetings, Florian Pflug
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Florian G. Pflug wrote:
Joshua D. Drake wrote:
On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
Tom,
Let's go with the easy fix. With regular 1GB segment size, having a few
empty files in the data directory isn't going to hurt anyone.
No, but it will confuse DBAs ("What the heck are all these 0B
files?"). Maybe we should add code to VACUUM to look for these empty
file segments and unlink them if they haven't been touched in a while
(say, a day?).
I don't buy this argument. A smart DBA isn't going to do any such thing.
If you go around deleting unknown files you deserve everything you get.
Especially if you are in the middle of a PostgreSQL cluster tree.
Couldn't some application trigger this problem regularly (Say, once a
day while
importing new data), and therefore create an ever increasing number of
empty
files that you can't get rid of without backup,reinit and restore?
No, the old empty files would get reused if the relation grows again.
And they would get deleted if you truncate or drop the relation.
Maybe postgres could delete them during recovery?
Yes, though it would have to run not only when recovering from WAL, but
on normal startup as well. It would require sweeping through the data
directory, looking for the empty files, which would increase the startup
time a bit. We discussed a solution like that to find orphaned relation
files some time ago, which IMHO is a real problem, but that idea got
forgotten.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Joshua D. Drake wrote:
On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
Tom,
Let's go with the easy fix. With regular 1GB segment size, having a few
empty files in the data directory isn't going to hurt anyone.
No, but it will confuse DBAs ("What the heck are all these 0B files?"). Maybe
we should add code to VACUUM to look for these empty file segments and unlink
them if they haven't been touched in a while (say, a day?).
I don't buy this argument. A smart DBA isn't going to do any such thing.
If you go around deleting unknown files you deserve everything you get.
Especially if you are in the middle of a PostgreSQL cluster tree.
Couldn't some application trigger this problem regularly (Say, once a day while
importing new data), and therefore create an ever increasing number of empty
files that you can't get rid of without backup,reinit and restore?
Maybe postgres could delete them during recovery?
greetings, Florian Pflug
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
On Sun, Nov 19, 2006 at 12:01:15PM -0800, Joshua D. Drake wrote:
> On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
> > Tom,
> >
> > > Let's go with the easy fix. With regular 1GB segment size,
> > > having a few empty files in the data directory isn't going to
> > > hurt anyone.
> >
> > No, but it will confuse DBAs ("What the heck are all these 0B
> > files?"). Maybe we should add code to VACUUM to look for these
> > empty file segments and unlink them if they haven't been touched
> > in a while (say, a day?).
>
> I don't buy this argument. A smart DBA isn't going to do any such
> thing. If you go around deleting unknown files you deserve
> everything you get. Especially if you are in the middle of a
> PostgreSQL cluster tree.
A mention in the README in that directory wouldn't hurt, tho.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
On Sun, 2006-11-19 at 11:28 -0800, Josh Berkus wrote:
> Tom,
>
> > Let's go with the easy fix. With regular 1GB segment size, having a few
> > empty files in the data directory isn't going to hurt anyone.
>
> No, but it will confuse DBAs ("What the heck are all these 0B files?").
> Maybe
> we should add code to VACUUM to look for these empty file segments and unlink
> them if they haven't been touched in a while (say, a day?).
I don't buy this argument. A smart DBA isn't going to do any such thing.
If you go around deleting unknown files you deserve everything you get.
Especially if you are in the middle of a PostgreSQL cluster tree.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Tom,
> Let's go with the easy fix. With regular 1GB segment size, having a few
> empty files in the data directory isn't going to hurt anyone.
No, but it will confuse DBAs ("What the heck are all these 0B files?"). Maybe
we should add code to VACUUM to look for these empty file segments and unlink
them if they haven't been touched in a while (say, a day?).
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Tom Lane wrote: I think that the easiest fix might be to not remove no-longer-used segment files during a truncate, but simply reduce them to zero size rather than delete them. Then any open file pointers aren't invalidated. The only alternative I can see is to invent some new signaling mechanism to force closure of open files, but that seems ugly, complex, and perhaps subject to race conditions. Thoughts? Ouch. Let's go with the easy fix. With regular 1GB segment size, having a few empty files in the data directory isn't going to hurt anyone. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nasty VACUUM/bgwriter/segmentation bug
Tom Lane wrote: > > I think that the easiest fix might be to not remove no-longer-used > segment files during a truncate, but simply reduce them to zero size > rather than delete them. Then any open file pointers aren't > invalidated. The only alternative I can see is to invent some new > signaling mechanism to force closure of open files, but that seems > ugly, complex, and perhaps subject to race conditions. > Setting the files to zero size seems to make sense all around, as when the bgwriter wants to use one of those segments it can 'figure out' that there's nothing in the file and it should start at offset zero. Regards, Paul Bort ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Fwd: Index Advisor]
Hi, Am 15.11.2006 um 15:34 schrieb Gurjeet Singh: = .) The SELECTs in the pg_advise are returning wrong results, when the same index is suggested twice, because of the SUM() aggregates. I don't think that this is a bug. If the same index is recommended for two different queries it will appear two times in pg_indexadvisor. So, if you want to calculate the overall benefit of this index, then you have to sum up the local benefits for each query. .) I doubt that on a table t(a,b), for a suggestion of idx(b,a), pg_advise will suggest idx(a,b); ?? Not sure, if I understand you right. idx(b,a) and idx(a,b) are completely different indexes. Why should pg_advise suggest idx(a,b). But there is another bug: if there are recommendations like idx (a,b,c), idx(a,b) and idx(a) it would be a good idea to create just idx(a). I will add this to pg_advise as an optional feature. Best, Kai ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
