Re: [SQL] age() vs. timestamp substraction
Hi all, Where did you get that idea? age's reference point is current_date (ie, midnight) not now(). There are also some differences in the calculation compared to a plain timestamp subtraction. I'm jumping on this thread to point out a little strange thing to me. CURRENT_DATE, converted (stupidly) as a string *with* hour is current date at mid-day: test=# select to_char(current_date,'-MM-DD HH:MI:SS'); to_char - 2006-10-06 12:00:00 (1 ligne) It was a day when I had to debug a strange behaviour in a customer's (bad) code :-) Cheers, -- Jean-Paul Argudo www.PostgreSQLFr.org www.dalibo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] age() vs. timestamp substraction
On Fri, 6 Oct 2006, Jean-Paul Argudo wrote: Hi all, Where did you get that idea? age's reference point is current_date (ie, midnight) not now(). There are also some differences in the calculation compared to a plain timestamp subtraction. I'm jumping on this thread to point out a little strange thing to me. CURRENT_DATE, converted (stupidly) as a string *with* hour is current date at mid-day: test=# select to_char(current_date,'-MM-DD HH:MI:SS'); to_char - 2006-10-06 12:00:00 (1 ligne) It was a day when I had to debug a strange behaviour in a customer's (bad) code :-) That's both midnight and mid-day. It's asking for 12 hour time and no AM/PM marker. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
Given something like: create table foo (id int primary key not null); create table bar (id int primary key not null, a_id int references foo(id)); select a.id, b.id from foo a left outer join bar b on (b.a_id = a.id) for update; PG 8.1.4 balks, saying: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join Is this behavior spec-mandated, or could only the rows in B which are not-null be locked? James Robinson Socialserve.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] SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
James Robinson [EMAIL PROTECTED] writes: select a.id, b.id from foo a left outer join bar b on (b.a_id = a.id) for update; SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join Is this behavior spec-mandated, or could only the rows in B which are not-null be locked? I don't believe that the spec expects FOR UPDATE to work on outer joins. The problem with it is what does it mean to lock the result of a select from an outer join? To my mind, a lock on a select result means that you've guaranteed that no one else can change the rows you selected. In an outer join it's impossible to guarantee that --- someone could insert a B row that matches a formerly unmatched A row. If you now re-did the SELECT you would get a different result, ie, your null-extended A row would be replaced by a normal row, even though you had lock on that A row. (This does not speak to the question of new rows showing up in the second SELECT --- that's always possible. The point is that a row you got the first time is now different despite being locked.) So I tend to feel that if you think you need this, you need to rethink your data model. Note that you can select FOR UPDATE OF a in this situation, it's just the B side that is problematic. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
Oh that's sweet and all I needed anyway thanks. On Oct 6, 2006, at 12:25 PM, Tom Lane wrote: Note that you can select FOR UPDATE OF a in this situation, it's just the B side that is problematic. James Robinson Socialserve.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals
Great, it's nice to see that this might get rolled into one of the next releases. Thanks, Graham. Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: Considering how late it is in the cycle, perhaps the change in behavior should come in 8.3. Yeah, there's not really enough time to think through the consequences now. I'd like to experiment with it for 8.3 though. regards, tom lane -- Graham Davis Refractions Research Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Too much time to delete 19000 rows
Hi list,I am having many problems to delete a table with only 19000 rows.The selection takes less than 1 second but on delete I wait more than 4 minutes and notthing. Can someone tell me what could happen ? I tryed only one transaction but it does not work too:BEGIN WORK; delete from base.ticket where session_id = 17 and cash_id = 99 and promo_id = 5;COMMIT WORK; I just think it could be occuring becouse of many constraints (7 at all) Please any help is welcomed.RegardsEzequias-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] timestamps over the web - suggestions
On Tue, Oct 03, 2006 at 14:44:12 -0700, chester c young [EMAIL PROTECTED] wrote: My server is based MST, but web clients from Maine to Hawaii, and they wish to see timestamps based in their own locale. Can anyone tell me how they're handling this? (sorry - can't get rid of my clients) Do they login in as themselves? If so, perhaps storing a timezone for each client would work in your case. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Too much time to delete 19000 rows
On Fri, Oct 06, 2006 at 03:10:01PM -0300, Ezequias Rodrigues da Rocha wrote: BEGIN WORK; delete from base.ticket where session_id = 17 and cash_id = 99 and promo_id = 5; COMMIT WORK; Try putting EXPLAIN ANALYSE on the beginning to see what's going on. That said. . . I just think it could be occuring becouse of many constraints (7 at all) . . .it could be, yes. Are all the other tables c. indexed correctly? VACUUMed and ANALYSEd? A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Too much time to delete 19000 rows
Ezequias Rodrigues da Rocha [EMAIL PROTECTED] writes: The selection takes less than 1 second but on delete I wait more than 4 minutes and notthing. Can someone tell me what could happen ? Perhaps foreign keys referencing this table and no indexes on the referencing columns? You're not required to have an index on the referencing side, but you pay for it when you update or delete in the master table... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
