Re: [SQL] PG 8.2beta reordering working for this case?
Kyle Bateman <[EMAIL PROTECTED]> writes: > BTW, explain analyze still says 10 seconds of run time (and takes 10 > seconds to run), but when I remove the explain analyze, the query runs > in about a second. What's that all about? Instrumentation overhead ... you're probably running this on a PC with a very slow clock-reading capability. Each node output row counted by explain analyze takes two gettimeofday() calls, and apparently it's not unusual for those to take several microseconds on cheap motherboards, even when the CPU is nominally very fast. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] i have table
On 10/5/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Wed, Oct 04, 2006 at 11:20:07AM -0500, Aaron Bono wrote:>> So do it as needed and convert your application slowly.You don't even need to do that.ALTER TABLE tablename RENAME TO tablename_real; CREATE VIEW tablename [&c.]Now the view looks to the application just like the old table. Ifyou want to insert &c., you put some rules there.If you do this you need to make the view updateable or inserts/updates/deletes will break. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] deleting rows in specific order
Hi, What is the best way to do something like: delete from tablename where something order by somefield... Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On 10/5/06, Jim Nasby <[EMAIL PROTECTED]> wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:> regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01> 09:30:41'::timestamp);> ?column?> --> 14 days 14:28:19
> (1 row)>> should be reporting '350:28:19' instead.>> This is a hack that was done to minimize the changes in the regression> test expected outputs when we changed type interval from months/
> seconds> to months/days/seconds. But I wonder whether it wasn't a dumb idea.> It is certainly inconsistent, as noted in the code comments.>> I'm tempted to propose that we remove the justify_hours call, and tell
> anyone who really wants the old results to apply justify_hours() to> the> subtraction result for themselves. Not sure what the fallout would> be,> though.I suspect there's applications out there that are relying on that
being nicely formated for display purposes.I agree it should be removed, but we might need a form of backwardscompatibility for a version or two... I am personally of the opinion that display logic should never be put into the database. Applications that rely on the database formatting - that is tightly coupling your application to the database which does not follow good programming principles.
None-the-less, the feature would be nice and may be very valuable for reporting.== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On Mon, Oct 09, 2006 at 02:57:28PM -0500, Aaron Bono wrote:
> On 10/5/06, Jim Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> >> regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
> >> 09:30:41'::timestamp);
> >> ?column?
> >> --
> >> 14 days 14:28:19
> >> (1 row)
> >>
> >> should be reporting '350:28:19' instead.
> >>
> >> This is a hack that was done to minimize the changes in the regression
> >> test expected outputs when we changed type interval from months/
> >> seconds
> >> to months/days/seconds. But I wonder whether it wasn't a dumb idea.
> >> It is certainly inconsistent, as noted in the code comments.
> >>
> >> I'm tempted to propose that we remove the justify_hours call, and tell
> >> anyone who really wants the old results to apply justify_hours() to
> >> the
> >> subtraction result for themselves. Not sure what the fallout would
> >> be,
> >> though.
> >
> >I suspect there's applications out there that are relying on that
> >being nicely formated for display purposes.
> >
> >I agree it should be removed, but we might need a form of backwards
> >compatibility for a version or two...
>
> I am personally of the opinion that display logic should never be put into
> the database. Applications that rely on the database formatting - that is
> tightly coupling your application to the database which does not follow good
> programming principles.
>
> None-the-less, the feature would be nice and may be very valuable for
> reporting.
I agree in general, except most languages have terrible support for
time/date data, so I can see a much bigger case for the database being
able to do it (and it's not like we'll be removing justify_*). Be that
as it may, there are probably apps out there that will break if this is
just changed.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] deleting rows in specific order
At 04:14 PM 10/9/06, Daniel Drotos wrote: What is the best way to do something like: delete from tablename where something order by somefield... You cannot, because it doesn't make sense. The "order by" clause is not valid on delete statement. Queries from other processes that start while your delete is running will either see all the deleted rows or (perhaps) they will see none of them. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Jim, > I agree in general, except most languages have terrible support for > time/date data, so I can see a much bigger case for the database being > able to do it (and it's not like we'll be removing justify_*). Be that > as it may, there are probably apps out there that will break if this is > just changed. Many. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
