[BUGS] BUG #3260: Subtracting intervals
The following bug has been logged online: Bug reference: 3260 Logged by: Dhugael McLean Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.8 Operating system: FreeBSD Description:Subtracting intervals Details: select '1 day'::interval - '55 minutes'::interval; ?column? - 1 day -00:55:00 If the interval periods are both minutes (hours - hours, days - days, etc), this works fine. Days - minutes seems to fail. This should output 23:05:00. In case this was some weird casting error, I checked: select interval '1 day' - interval '55 minutes'; and that failed as well. ---(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: [BUGS] BUG #3260: Subtracting intervals
"Dhugael McLean" <[EMAIL PROTECTED]> writes:
> select '1 day'::interval - '55 minutes'::interval;
> ?column?
> -
> 1 day -00:55:00
> If the interval periods are both minutes (hours - hours, days - days, etc),
> this works fine. Days - minutes seems to fail. This should output 23:05:00.
No, this result is correct IMHO. Days and minutes are not interconvertible,
because there are not always 24 hours in a day. As an example using
EST5EDT zone (current US DST law):
regression=# select '2007-03-11'::timestamptz;
timestamptz
2007-03-11 00:00:00-05
(1 row)
regression=# select '2007-03-11'::timestamptz + '1 day'::interval;
?column?
2007-03-12 00:00:00-04
(1 row)
regression=# select ('2007-03-11'::timestamptz + '1 day'::interval) - '55
minutes'::interval;
?column?
2007-03-11 23:05:00-04
(1 row)
regression=# select '2007-03-11'::timestamptz + ('1 day'::interval - '55
minutes'::interval);
?column?
2007-03-11 23:05:00-04
(1 row)
regression=# select '2007-03-11'::timestamptz + '23:05:00'::interval;
?column?
2007-03-12 00:05:00-04
(1 row)
Postgres gets the fourth case right, but would fail if we adopted
your approach, as shown by the fifth case.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3260: Subtracting intervals
Dhugael McLean wrote:
> select '1 day'::interval - '55 minutes'::interval;
>
> ?column?
> -
> 1 day -00:55:00
>
> If the interval periods are both minutes (hours - hours, days - days, etc),
> this works fine. Days - minutes seems to fail. This should output 23:05:00.
No, that answer would be wrong because not all days are 24 hours long
(think DST). You can use justify_hours() if you want to make that
assumption:
alvherre=# select justify_hours('1 day'::interval - '55 minutes'::interval);
justify_hours
---
23:05:00
(1 fila)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[BUGS] order by question.
I'm using version 7.3 of postgres and when I issue the following command select author, sort_author from itemsbyauthor where sort_author like 'tan%'; I get the following results: Author sort_author Tan, Weihong| tan, weihong Tang, S. C. | tang, s. c. Tan, Fang | tan, fang Note how the tan's are not grouped together when I think they should be. Is this something that is fixed in a more current version of postgres? Thank you!
Re: [BUGS] order by question.
Your query doesn't appear to include an "order by" clause. The results won't be sorted without an "order by". On May 3, 2007, at 4:12 PM, Jose Blanco wrote: I’m using version 7.3 of postgres and when I issue the following command select author, sort_author from itemsbyauthor where sort_author like 'tan%'; I get the following results: Author sort_author Tan, Weihong| tan, weihong Tang, S. C. | tang, s. c. Tan, Fang | tan, fang Note how the tan’s are not grouped together when I think they should be. Is this something that is fixed in a more current version of postgres? Thank you! Douglas Toltzman [EMAIL PROTECTED] (910) 526-5938
Re: [BUGS] Grantor name gets lost when grantor role dropped
Jeff Davis wrote: > GRANT "postgres" TO "test_role" GRANTED BY ""; > > We either need to rethink the way we handle grantor information and when it's > valid. > Or we need to at least allow dump/restore to work as expected when a dropped > role > granted privileges to other users. I've been staring at this for a while. Upon first reading it, I thought that it would be simply a matter of adding pg_shdepend entries for the pg_auth_members rows. This starts sounding suspicious the moment you consider that there will be one pg_shdepend entry for each role granted, that is, a lot. The second problem with this idea is that it's not at all possible, because pg_shdepend entries can reference an object by OID, but pg_auth_members rows don't have OIDs. So the most we could do is add entries for pg_authid. So I'm currently considering the following alternatives: 1. do nothing at all with pg_shdepend. Upon role deletion, seqscan pg_auth_members and reject the drop altogether if there is a role granted to another which mentions the to-be-dropped role ID as grantor. This is easiest in terms of code (it's even mentioned in the comments in DropRole). 2. record one pg_shdepend entry for each role that has granted something to each role (unless the grantor is the same role being granted, in which case we needn't record anything). So if role A grants Z and X to C, and role B grants Y and W to C, C now has access to W, Y, X and Z and there are two pg_shdepend entries: C -> A C -> B So dropping a role would be disallowed automatically without any code changes, with the checkSharedDependencies() call that's already in DropRole. Adding a role membership would require a bit more work, because we'd first need to check that there's not already a pg_shdepend entry for that combination. Removing a role membership also becomes more work; we need to check that no other grant depends on the same grantor before removing the entry. Note that I'm considering that this alternative requires adding a GRANTOR symbol to the SharedDependencyType, which probably rules this out for backpatching. Comments? I'm leaning towards implementing (2). The patch for pg_dumpall would also be needed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
