Re: [HACKERS] [SQL] Interval subtracting
Hi, Scott, Scott Marlowe wrote: >>But it isn't '-2 months, -1 day'. I think what you are saying is what I >>am saying, that we should make the signs consistent. > Pretty much. It just seems wrong to have different signs in what is > essentially a single unit. > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > again, maybe some folks do. It just seems wrong to me. But we say "quarter to twelve", at least in some areas on this planet. The problem is that months have different lengths. '2 months - 1 day' can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-08 at 06:07, Markus Schaber wrote: > Hi, Scott, > > Scott Marlowe wrote: > > >>But it isn't '-2 months, -1 day'. I think what you are saying is what I > >>am saying, that we should make the signs consistent. > > Pretty much. It just seems wrong to have different signs in what is > > essentially a single unit. > > > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > > again, maybe some folks do. It just seems wrong to me. > > But we say "quarter to twelve", at least in some areas on this planet. > > The problem is that months have different lengths. '2 months - 1 day' > can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 > month 30 days', depending on the timestamp we apply the interval. I made this point before. In the military they say 1145 or 2345 instead of quarter to twelve, because 1: there are two "quarter to twelves" a day, and 2: It's easy to get it confused. For same reasons, i.e. a need for precision, I find it hard to accept the idea of mixing positive and negative units in the same interval. The plus or minus sign should be outside of the interval. Then, it's quite certain what you mean. If you say select '2006-06-12'::date - interval '1 month 2 days' there is no ambiguity. If you say: select '2006-06-12'::date + interval '-1 month -2 days' do you mean (1 month - 2 days) subtracted from the date, or do you mean to subtract 1 month, then 2 days from the date? Putting the + or - outside the interval seems to make the most sense to me. Allowing them inside makes no sense to me. And colloquialisms aren't really a good reason. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
Scott Marlowe <[EMAIL PROTECTED]> writes: > For same reasons, i.e. a need for precision, I find it hard to accept > the idea of mixing positive and negative units in the same interval. The semantics are perfectly well defined, so I don't buy this. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Errors ignored on restore
Hi, I'm transfering data between postgre 8.0 and 8.1 using pg_dump and pg_restore, but I get "x errors ignored on restore". What could be the reason ? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [GENERAL] Errors ignored on restore
Emil Rachovsky <[EMAIL PROTECTED]> writes: > I'm transfering data between postgre 8.0 and 8.1 using > pg_dump and pg_restore, but I get "x errors ignored on > restore". What could be the reason ? If you aren't going to show us what the errors were, how can we guess? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
For same reasons, i.e. a need for precision, I find it hard to accept the idea of mixing positive and negative units in the same interval. The plus or minus sign should be outside of the interval. The interval data type is really useful. I see no reason to restrict its usefulness with an arbitrary constraint. Date arithmetic is treacherous and INTERVAL is a lifesaver. Forcing a global sign on the interval would break interval arithmetic. How would you compute '1 month'::interval - '1 week'::interval ? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Is there any way to stop triggers from cycling?
Folks, I'm experimenting with a set of triggers to automagically maintain ltrees-organized tables. I almost have it working, except for a pesky problem with re-ordering groups. The idea is that I want to set up a set of triggers such that: a) If the user moves item (1) to item (3), then the existing items (2) and (3) will be "bumped down" to (1) and (2), or b) if the user moves item (3) to item (1) then the existing items (1) and (2) will be "bumped up". (btw, the reason I want to use triggers and not data-push functions is that the triggers are *much* more reliable for maintaining the tree fields) I have a set of triggers that are working except for a problem with cycling. What I'm looking for is a reliable, elegant way to make sure that the trigger is executed for each row only once. Currently I'm doing this by only cascade-updating the row adjacent to the one I'm moving. However, this is resulting in a cycle, and I don't see how to break it. Namely: Given: A 1 B 2 C 3 If I move A --> 3 then: B moves to 1 C moves to 2 ... but *so does A* ... and I end up with: A 2 B 1 C 2 So I'm trying to come up with a way to ensure that each row is visited only once, but it doesn't seem to be possible. Ideas? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is there any way to stop triggers from cycling?
> I'm experimenting with a set of triggers to automagically maintain
> ltrees-organized tables. I almost have it working, except for a pesky
> problem with re-ordering groups.
> Currently I'm doing this by only cascade-updating the row adjacent to
the
> one I'm moving. However, this is resulting in a cycle, and I don't
see
> how to break it. Namely:
> So I'm trying to come up with a way to ensure that each row is visited only
> once, but it doesn't seem to be possible. Ideas?
I've played this game. Not elegant, but workable. Don't use an update
trigger.
Have an Insert trigger. From the client do a DELETE and INSERT to move A
to 3 instead of an update.
Within that trigger use updates -- thus no cascade.
Option #2 is equally un-elegant and works best for a 'session' flag. Use
a sequences state as a boolean value.
Have trigger #1 grab a value from the sequence and fix all of the data.
Have the cascaded triggers use a PG_TRY {} to determine if it can
successfully call currval() or not. If it can, then the trigger has
already run. If not, then it should do the work.
Option #3, probably better than #2 but I've not used it before: declare
a standard named cursor.
If the cursor exists then your cascaded triggers can read it for the
work that they should do (nothing in this case) (test with PG_TRY{}).
If the cursor does not exist then the trigger should make a cursor with
instructions, do the work (cascades to sub-triggers), and remove the
cursor.
Named cursors are better than temporary tables because they don't cause
system table bloat.
--
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Is there any way to stop triggers from cycling?
trying to do this exlusively in triggers is a forray into folly. take advantage of "instead of" or "do also" rules to create a compound statement before your triggers do their work. (in terms of maintenance and sanity, it's best if a trigger touches only its own record.) as a handsweep example: create view tree_v as select * from tree; grant select, insert, update on tree_v to public; create or replace rule 'tree_update' as on update to tree_v do instead( -- update tree set seq = seq+1 where old.pnt=new.pnt and old.seqhttp://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Is there any way to stop triggers from cycling?
Chester, > take advantage of "instead of" or "do also" rules to create a compound > statement before your triggers do their work. (in terms of maintenance > and sanity, it's best if a trigger touches only its own record.) Ah, I see ... so: 1) create a view on the table 2) put a rule on the view to re-order, which re-orders the *table* so there's no cascade 3) use the triggers to do the other tree-maintenance stuff, only for their own rows/children (cascading triggers work *very* well for tree maintenance). -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
