[GENERAL] Disconnected but query still running
Hi, list. I've been running a query from pgAdmin for a couple of hours, just to realize that the pgAdmin timer that counts ellapsed time had stopped, but the status bar still said executing. So I first thought it could've been some kind of bug with the counter. However, after some more time, I found that a popup telling me that the connection had been reset (the timer had stopped almost exactly at 2 hours, 7.210.123 ms to be exact, which makes me think of a 2 hours timeout somewhere). So I closed the dialog, and then it told me that the connection had been reset. Ok, status was updated to connection reset, but the query was (actually is) still running, despite the disconnection (it's still hammering the disk, and it shows up in one of the pg_views showing running quesries). I've read that that a query still running after a disconnection is normal, since the server won't realize about this disconnection until it needs to send some data back to the client, which is ok and understandable. But my question is, what happens when the query finishes? FYI, the query was an update, in what would be autocommit mode (no explicit transaction was open). Will the changes be commited? or rollbacked because it will detect the disconnection later on? In other words, is it worth waiting for this query to finish (it has been running for almost 7 hours now), or should I cancel it because it will irremediably be rolled back when the server finds theres no one on the other end of the tcp connection? Thanks. Eduardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bit datatype performance?
Hi, if you are thinking to access data in that manner, what's the point of bits (or tags)? The idea behind having a value and then using a bitmask is to be able to test the value against different bitmasks, each bitmask corresponding to a different tag or tag combination. The *where *statement you are suggesting differs in nothing from a regular id or in this case a category id (instead of a combination of tags)You will be fetching all records that only have a specific mask. I think you are a little bit confused: Let's say you have several tags each with an identifier: TAG_NATURE = 1 TAG_ANIMALS = 2 TAG_CARS = 4 TAG_SPORTS = 8 then you have a record ... the idea to use bits is to be able to assign that record a single value, formed by the combination of the different tags. For example if an element corresponds to TAG_NATURE and TAG_ANIMALS, you would want to have that element with a value of TAG_NATURE + TAG_ANIMALS resulting in a tag value of 3. Then if you want to extract all ANIMALS you just do: *... where value TAG_ANIMALS = TAG_ANIMALS;* because if you just do: *... where value = TAG_ANIMALS* you will only get the elements that *exclusively *have the tag TAG_ANIMALS. You will miss for instance those that have the NATURE *and* ANIMALS (or any other tag). So, your simple index on value willl not be of any help, since you won't be doing *... where value = ANY_SPECIFIC_TAG* because of the latter. Now, if you are going to have a different TAG for every TAG COMBINATION well, you can do that, but that would be no different than any regular id, in this case, it would be more of a CATEGORY, and elements will only be able to have one single category for them. One alternative would be to try to make some helping indexes on expressions, maybe with the help of a function like: create or replace function hasTag(data integer, tag integer) returns boolean as $$ declare begin return (data tag) = tag; end; $$ language plpgsql immutable; -- this function would return select hasTag(1, 1); -- true select hasTag(3, 1); -- true select hasTag(4, 1); -- false This way, you could reformulate your query in the following fashion: ... where hasTag(value, TAG_NATURE); and you could now build an index on yourtable based on that expression like: create index idx_yourtable_hasTag_1 on yourtable (hasTag(value, 1 /* TAG_NATURE */)); If you would like to fetch a combination of tags, you could do: ... where hasTag(value, TAG_NATURE) and hasTag(value, TAG_ANIMALS) requiring an extra index on (hasTag(value, TAG_ANIMALS)). In this way, you will end up requiring 256 indexes :) (which can be from acceptable to *ridiculous*, depending on how much often the indexes should be updated, volume, etc), it's up to you. I'm not actually suggesting you use this approach, it's just a raw idea, and it's just the conclusion of one line of thought, that may or may have not crossed your mind. Maybe with some refinement, you can get to something more practical. But nonetheless (if I'm not missing something huge), the *where *statement you provided is just the wrong approach to tags. hope it helps, regards, eduardo On Wed, Sep 14, 2011 at 12:58 PM, Antonio Vieiro anto...@antonioshome.netwrote: Hi again, Thanks for the tip. In fact I was thinking of creating an index on the bitmask, so I could use: ... where t.bits = :mymask directly, avoiding a full table scan. I assume this is possible (indexing bit and comparing bits), isn't it? Thanks, Antonio El 14/09/11 15:58, Radosław Smogura escribió: On Wed, 14 Sep 2011 12:00:35 +0200, Antonio Vieiro wrote: Hi all, One of my entities 'E' may be 'tagged' with an arbitrary set of 256 tags 'T'. A first approach could be to add a M:N relationship between 'E' and 'T'. A second way to do this could be to add a BIT(256) datatype to 'E', setting bits to '1' if the entity is tagged with each one of the 256 tags (i.e. using a 'bitmask' on the set of tags). Since querying entities 'E' with a certain set of tags 'T' must be very fast I was wondering if the second approach would be faster. What do you think? Thanks for any hints, Antonio I assume each entity may have one or more different tags. Actually performing test like ... where (t.bits :mymask) = :mymask should be quite fast and faster then creating additional relations, but only if it's highly probable that your query will almost always scan whole table. The advantage of indexes is that the index is used 1st and tail (slower) parts of query will always get subset of table. In bitset, You will probably scan whole table. So I think, you should do some performance test for large number of data, and compare both ways. I think bitset will be fast for really small data, but M:N relations may be faster for really large data sets. You need to measure size of your database too, in M:N case with 256 tags it may be quite large. -- Sent via pgsql-general mailing list
Re: [GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.
I'm sorry Reid, driving back home I realized that the previous query I
suggested didn't do what I was expecting, cause it would compute all of val2
for each val1, even if they belonged to another group (not for a particular
val1/date pair), or in other words, to another date.
I've considered this fact in the previous post but the resulting query
appeared too complex for such a simple task, and then disregarded it, but
well, after all it seems it was necessary to do this little extra work,
because you wanted the exact val2 associated to the max(date) for val1.
I've come up with this alternative, basically a key extractor followed by
diving in the original table looking for val2 for that key.
As I've said before, the data you provided showed that there could be
multiple rows for each key made of val1 and max(date) for that specific
val1.
So, you will still have to define a criteria on which val2 to keep.
Since I don't know the reason for this query, I've suggested an array_agg so
that it is more clear to you.
select
a.val1,
a.date,
array_agg(mytable.val2) -- given there can be multiple rows for any
val1/date pair, this is where you are allowed to define which one you want
(or all of them)
from (
select val1, max(date) as date from mytable group by val1) a -- this
gives you only rows satisfying val1/max(date)
inner join mytable on a.val1 = mytable.val1 and a.date = mytable.date --
this join allows the retrieval of val2 for that key formed by
val1/max(date), but be prepared for many rows
group by
a.val1,
a.date
order by
val1;
test case:
select val1, val2, date from mytable;
1;16;2011-09-13;1
1;15;2011-09-13;2
1;14;2011-09-13;3
1;23;2011-09-12;4
1;22;2011-09-12;5
1;21;2011-09-12;6
2;6;2011-09-13;7
2;5;2011-09-13;8
2;4;2011-09-13;9
2;3;2011-09-14;10
2;2;2011-09-14;11
2;1;2011-09-14;12
This is the result coming from the new query, you see, only val2's of 14, 15
and 16 are computed (you will still eventually have to select one from it,
using a more specific aggregate, like max, min, avg, etc.)
1;2011-09-13;{14,15,16}
2;2011-09-14;{1,2,3}
This is the result coming from the original, simpler (but flawed) query,
which as it clearly shows computes val2's of 14, 15, 16, 23, 22 and 21,
beging those last 3 (23, 22 and 21) from another date associated with val1
(which is not the max date, so they shouldn't have been considered at all).
select val1, max(date), array_agg(val2) from mytable group by val1
1;2011-09-13;{14,15,16,23,22,21}
2;2011-09-14;{1,2,3,4,5,6}
hope it helps.
regards,
eduardo
On Tue, Sep 13, 2011 at 1:13 PM, Reid Thompson reid.thomp...@ateb.comwrote:
On Tue, 2011-09-13 at 16:39 +0200, Hannes Erven wrote:
Reid,
where any one of these 3
11 2011-01-01
11 2011-01-01
13 2011-01-01
or any one of these 2
31 2011-01-05
32 2011-01-05
are suitable for val = 1, val = 3 respectively.
Can you please describe in words what you are trying to accomplish? When
I look at your data and expected output, I'd say you want this:
For each distinct value of val1, return any value of val2 and
the lowest value of date.
for each distinct value of val1', return the highest value(most recent)
of date and the value of val2 associated with that date
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign key check only if not null?
hi, fks do just that. you can create your fk with just one command: alter table xxx add constraint fk_name foreign key (user_id) references users (id); parent table's id field should also be of the same type and also it should be primary key or at least unique. you can create your pk with (if you don't already have one): alter table users add constraint pk_users primary key (id); also i wouldn't use a varchar(100) as a pk field, i would suggest using some other datatype maybe a bigint, but that always finally depends on the model, like if there is a really good reason for using a varchar(100), well, it's your call. regards, eduardo On Mon, Sep 12, 2011 at 10:48 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: Hi, I bet this is a simple solution but I have been racking my brains. I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is ok. (Because the functionality in question is open to both unregistered and registered users). Any idea on how I can implement a FOREIGN KEY constraint? Or do I need a pre-insert and pre-update RULE for this? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
I'm sorry Tom. This happens (at least) both in 8.4 and 9.0. I've just tested it in 2 different databases with the same results. I have workarounds to this, but I was wondering what could be the actual root of the problem, in order to (try to) achieve the best solution possible. Following is a test case with which you can reproduces the issue. Statements should be executed in the following order. -- session T1 means that the following lines should be executed from pg session 1. -- session T2 means that the following lines should be executed from pg session 2. create table b ( id bigint not null, x double precision, constraint pk_b primary key (id)); create table a ( id bigint not null, id_b bigint, x double precision, constraint pk_a primary key (id), constraint fk_b foreign key (id_b) references b (id)); insert into b (id, x) values (1, 0); insert into a (id, id_b, x) values (1, 1, 0); -- session T1 begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- Query returned successfully: 1 row affected, 47 ms execution time. rollback; -- session T1 rollback; begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- ERROR: could not obtain lock on row in relation b. SQL state: 55P03 Regarding my original question, I would like to know if this is a known issue/feature/bug/unwanted optimization consequence/or is it just a normal behavior that I should've had predicted. Best regards, Eduardo. On Wed, Sep 7, 2011 at 9:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B; I think it's probably explained by this: PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates. IIRC there are some optimizations in the FK stuff that don't apply once a single transaction has updated a relevant row more than once. You haven't given enough details (not even a PG version) to be sure about it, but that's what I'd bet on. regards, tom lane
[GENERAL] feature request - update nowait
Hi, would it be possible to implement a *nowait *modifier to the *update*statement in order to tell it not to wait and raise an error -just like a select for update nowait would-, instead of defaulting to waiting forever until the lock becomes available? The lack of such a modifier nowadays forces me to do a select for update before every update on which I need the fastest response possible, and it would be great if it could be integrated into the command itself. Just an idea. Best regards, Eduardo.
Re: [GENERAL] feature request - update nowait
Nice. Much more maintainable IMO and quite close to what I was looking for. Thanks a lot for the suggestion, I will definitely try it/implement it right away. Still has some redundancy compared to an hypothetical nowait modifier but I think it's the very best alternative so far. Eduardo On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino drak...@gmail.com wrote: Hi, would it be possible to implement a nowait modifier to the update statement in order to tell it not to wait and raise an error -just like a select for update nowait would-, instead of defaulting to waiting forever until the lock becomes available? The lack of such a modifier nowadays forces me to do a select for update before every update on which I need the fastest response possible, and it would be great if it could be integrated into the command itself. Just an idea. +1 note you may be able to emulate this by sneaking a nolock into the update statement in a highly circuitous fashion with something like: update foo set v = 2 from (select 1 from foo where id = 1 for update nowait) q where id = 1; merlin
Re: [GENERAL] feature request - update nowait
I'm sorry, isn't it meant for table locks? I was talking about row level locking. Eduardo 2011/9/8 pasman pasmański pasma...@gmail.com Try a command LOCK NOWAIT 2011/9/8, Eduardo Piombino drak...@gmail.com: Hi, would it be possible to implement a *nowait *modifier to the *update*statement in order to tell it not to wait and raise an error -just like a select for update nowait would-, instead of defaulting to waiting forever until the lock becomes available? The lack of such a modifier nowadays forces me to do a select for update before every update on which I need the fastest response possible, and it would be great if it could be integrated into the command itself. Just an idea. Best regards, Eduardo. -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] feature request - update nowait
Don't worry ! I will surely try some different alternatives anyways, but the idea is the same, include somehow a select for update in the same sentence as the update. I'm most inclined to the last one you suggested, maybe with an equals instead of an in (I'd rather always instinctively use an equals over an in for a single record match, whatever the context is). Considering of course it is a pk. If multiple rows should be affected by the update, well an in would then be way, but I don't think it will be the case for me. Thanks again! Eduardo PS: Please feel free to mail me directly if you happen to come up with a better alternative too, so as not to bore the list to death, if that was the case. On Thu, Sep 8, 2011 at 6:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Sep 8, 2011 at 4:32 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino drak...@gmail.com wrote: Hi, would it be possible to implement a nowait modifier to the update statement in order to tell it not to wait and raise an error -just like a select for update nowait would-, instead of defaulting to waiting forever until the lock becomes available? The lack of such a modifier nowadays forces me to do a select for update before every update on which I need the fastest response possible, and it would be great if it could be integrated into the command itself. Just an idea. +1 note you may be able to emulate this by sneaking a nolock into the update statement in a highly circuitous fashion with something like: update foo set v = 2 from (select 1 from foo where id = 1 for update nowait) q where id = 1; On Thu, Sep 8, 2011 at 3:33 PM, Eduardo Piombino drak...@gmail.com wrote: Nice. Much more maintainable IMO and quite close to what I was looking for. Thanks a lot for the suggestion, I will definitely try it/implement it right away. Still has some redundancy compared to an hypothetical nowait modifier but I think it's the very best alternative so far. Eduardo Thanks -- in hindsight though I think it's better to write it this way: explain update foo set v = 2 from ( select id from foo where id = 1 for update nowait ) q where q.id = foo.id; another interesting way to write it that is 9.1 only is like this: with x as ( select id from foo where id = 1 for update nowait ) update foo set v = 2 where exists (select 1 from x where x.id = foo.id ); which gives approximately the same plan. ...I spoke to soon! either use the CTE method, or write it like this: update foo set v = 2 where id in (select id from foo where id = 1 for update); sorry for the noise :-). (update...using can be tricky to get right) merlin
[GENERAL] Lock problem
Hello list, I'm having a locking problem and I'm not sure what is causing it. I have two pgsql concurrent transactions, running each in a separate connection to postgres (I can reproduce it from pgadmin). T1) operates only on table A begin transaction; select id from A where id = 100 for update nowait; update A set x = x + 15 where id = 100; *update A set x = x + 15 where id = 100; *commit; T2) operates only on table B begin transaction; select x from B where id = 116 for update nowait; update B set x = x + 1; commit; If I run transaction T1 up to the beginning of the second update, and then i stall there, transaction T2 is allowed to do the select for update with no problem at all. However, if transaction T1 goes a step further, and does the second update, from that point on, transaction T2 is not able to get the lock on B. I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B; This behaviour is backed up with a consistent increase in the locks from the server status views. I don't see how: select * from A for update nowait; update A set x = x + 1; has a different effect than (locks-wise) select * from A for update nowait; update A set x = x + 1; update A set x = x + 1; PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates. I don't see how a second update (identical to the previous one if you wish) to A on T1 will prevent T2 from getting a row level lock on B. Does anyone have an explanation on why this happens? Thank you, Eduardo.
[GENERAL] select now() problem?
Hello list, I'm having a problem with a production system. I have an application that runs 7x24. It is multithreaded, and every thread has its own connection to the database. We have two types of devices connected to each thread, human operated PCs for billing purposes, and rabbit microcontrollers controlling some electronic devices. The problem we are having is that once a month approximately, all the microcontrollers attached to their threads (its a common pool of threads), stop responding. Having a closer look at the logs, we found that these threads are stalled at a SELECT NOW() immediately after a BEGIN TRANSACTION. And then the thread stops responding, since it is waiting for that sql command to finish. The curious part is that all PCs still work ok, even though they also execute SELECT NOW(). This log represents the session for one PC as an example. 02/09/2010 09:26:32.593 | 192.168.203.104:2255 | Thread 17: (CAJA 4...@parkingb-Exterior)Thread 17: [TRANSACTION BEGIN] 02/09/2010 09:26:32.593 | (17) | ROLLBACK 02/09/2010 09:26:32.593 | (17) | BEGIN TRANSACTION 02/09/2010 09:26:32.593 | (17) | SELECT NOW() 02/09/2010 09:26:32.593 | 192.168.203.104:2255 | Thread 17: (CAJA 4...@parkingb-Exterior)0 ms (errorCode=[200] bytesRead=[17] bytesWritten=[3]) This log represents the session for one thread attached to a device. 02/09/2010 09:15:01.265 | 192.168.203.52:1127 | Thread 8: (SAL2ABJ t...@parking B-Exterior)Thread 8: [EVENT AUTH TR 005001040719 02/09/2010 09:15:01 1] 02/09/2010 09:15:01.265 | (8) | BEGIN TRANSACTION 02/09/2010 09:15:01.265 | (8) | SELECT NOW() and the thread never continues, it never comes by the select now(). The only difference from the database point of view, is that PCs transactions, since they are much more complex, usually start with a ROLLBACK (arguable, it will be eventually removed) and for devices they usually just start with a COMMIT. Do you have any idea on what could be causing a SELECT NOW() to never come back? I'm dazzled. The problem gets solved restarting the server application (and by doing so, all connections to the database are dropped, and after that it doesnt present the problem anymore for at least a month). It has happened approximately 4 or 5 times already. Thank you, Eduardo.
Re: [GENERAL] select now() problem?
The only difference from the database point of view, is that PCs transactions, since they are much more complex, usually start with a ROLLBACK (arguable, it will be eventually removed) and for devices they usually just start with a COMMIT. Correction ... and for devices they usually just start with a BEGIN TRANSACTION. Real thing is that when this happens, I'm several kilometers away, with no remote access (yet at least), and since it is a production system as soon as something like this happens they need to get it back up asap. I find it extremely difficult to be able to gather that data. Besides that I'm not familiar with the procedure of attaching to someone else's process in windows. It is a 8.4.2 postgres running in windows 2003 server. Im tempted in upgrading to 8.4.latest, since it should not require a full backup/restore and practically no server downtime. But i'm not sure about the real gain of that. On Thu, Sep 2, 2010 at 2:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: Do you have any idea on what could be causing a SELECT NOW() to never come back? That's truly bizarre. Can you attach to one of these stuck processes with a debugger and get a stack trace? regards, tom lane
Re: [GENERAL] Date with time zone
On Mon, Nov 30, 2009 at 7:22 AM, Martijn van Oosterhout klep...@svana.orgwrote: On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote: Analysis of the extra complications added by DST's does not add anything, yet, to the point I'm trying to make, regardless the lack of such cases in practice. The major problem with timezone support in SQL is that they basically punt on DST altogether, making it somewhat useless for general use. (Which is why the timetz type as it is defined by SQL doesn't actually do what you want.) Saying that you're going to ignore DST in the first round is ignoring the elephant in the room: you *have* to deal with it. While your example of 6pm London Time is good, I'm having a hard time imagining you'd want to store such a value in a database. From a technical point of view, that time, 6PM London Time, can be easily defined by a time with time zone data type, contrary to any other setup based on assumptions (such as assigning the default local time zone of where the server is to the time without time zone, or keeping track of the time zone on a different data field), with a simple 18:00:00+00 (+00 stands for London Time). Bzzt. +00 is not London Time, it's UTC. London time is sometimes +01. My bad. Wouldn't it be nice/elegant to be able to specify that specific day in a date with time zone format? Something like 24/12/2009+00, that would be like adding an offset to both start and end time. That way, the date itself knows where in the world its being placed (London), as an instance of an abstract definition of a date (December 24th/2009). Frankly, I think it's easier and clearer to say the interval from 1261612800 to 1261699200 seconds after 1970-01-01 00:00:00 UTC. That's at least totally unambiguous, now and into the future. And everybody can trivially convert it to whatever view they want. Me too. I was just hoping to be able to come up with another totally unambiguous way of expressing the same interval in a more human readable form, like 24/12/2009+00, that would denote the same exact interval that you mentioned: 1261612800 to 1261699200 seconds after 1970-01-01 00:00:00 UTC. A day in this context meant midnight to midnight. That's your definition, but hardly the only useful one. I agree. What I just wanted to explain is that in my original sentence/context, it meant from midnight to midnight. Answer me this question then: What day is it now? You can't answer me Monday, November 30th. You should instead ask me: -Where? Because the current day will depend on the location, aka, time zone. Indeed, the question is invalid. Long experience has taught me that when dealing with times you must strictly seperate the concept of an instant in time and what your clock says. An instant in time is what is represented by the timestamptz type and is (barring relativity) universal. What your clock says is what the timestamp type gives and any time I've seen it used to store data it causes grief in the end. Mainly due to the fact that even with timezone information it's ambiguous. If your argument is that what we actually need is an interval with time zone type, then I could possibly agree with you there. Everything seems to point to something like that, yes. Have a ncie day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iD8DBQFLE5zJIB7bNG8LQkwRAjnJAJ96UZjaAy13METKCHN87mT65TVf5ACcCamb OFS1DdzDfZIWy9AGW5Gspv8= =ZdrH -END PGP SIGNATURE- You too, and thank you all guys for your comments, specially Adrian, they are really appreciated.
Re: [GENERAL] Date with time zone
On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver akla...@comcast.net wrote: On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to Dec 19th without adding an innecesary reference to a dummy time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say Dec 19th (GMT-3) ? The problem arises around the dates when DST starts and ends. For instance here, Washington State USA, Nov 1st was the change over date. This occurred at 2:00 AM in the morning, so on Nov 1st we where in two time zones PDT then PST. Without a reference to time it makes it hard to keep track. For the sake of simplicity I would like to leave the DST's modifications off the discussion, just for now. Considering a fixed setup, where every country has a fixed time zone (or many, but fixed), I will try to make my point. If I we can all agree on the point at a fixed setup, I'm more than willing to get into a more complex scenario and just then introduce summer times, etc. If you are ok with it. On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? I'm no guru, but I don't see any obvious technical impossibility to do so. Is this so just because SQL standard says so? Can it be possible that SQL standard is a little short on this kind of need? I will let the SQL gurus answer this one. Again, of course I can always use a timestamp set to 00:00:00 just to use its time zone tracking capabilities, but It is just as dirty as any other patch. As stated above time zones only have meaning with respect to date and time together. I'm not quite sure about this. For example, if you want to say I will accept bets until 6 o'clock (London Time), every day. How does that time reference need to be tied to a specific date?. It's just a reference to a time in the day, by itself. Plus a time zone, because 6 o'clock by itself is also not sufficient, it still needs a time zone reference. It's also not a timestamp, nor a date, its just a time (with time zone tracking capabilities). And that's where I see that time with time zone is a good, and the one data type to use, IMO. A date is a date, and a timestamp is a timestamp, and both, used independently, should be able to keep track of its associated time zone, I think. Am I wrong on this? Apart from what SQL Standard may say, for instance. I would suggest searching the archives. There has been discussions in the past about 'tagged' fields that would track timezones independent of a time/date/timestamp field. I will, thanks for the reference. -- Adrian Klaver akla...@comcast.net Going back to the date with datetime, follow me with this reasoning: If you consider a specific date (in a specific time zone) defined as a specific starting point in time (absolute and universal), and a specific ending point in time, one could say that a date is a period of time ranging from 00:00 hs to 23:59:59. (just as some other member list suggested, with which I totally agree), both times tied to a specific time zone. One could then say that, Dec 19th (London Time) goes from Dec 19th 00:00:00 (London Time) to Dec 19th 23:59:59.9... (London Time). So, making a reference to Dec 19th (London Time) would make sense as it refers to a specific absolute time frame, or range. This range, could be (or not, but that is not the point) useful for some practical situations, but the impossibility to be able to state a reference to a date, without recurring to some tweaks or patches is sthing that at the least, bugs me a little. For instance, if you have a server and you want to tell someone it will be up tomorrow, all day long, with that single reference, your buddy knows exactly that it will be up from 00:00:00 of that day (Your Local Time), to 23:59:59. that day (also in your local time), at least in theory, according to what u said. And tomorrow in that sentence, works as an implicit reference to a day, with a time zone, which combined, references a specific absolute time range. Maybe another thing that goes against this, is that there would seem to be no reasonable applications for that data type, but I recall having the need for this once, and I think that was the first time I would have liked to be able to specify a date along with a time zone. But again, apart from the possible applications, I still think it is a logic data type to have. To be able to reference a specific date, in a specific country, or time zone. Another example that comes into my mind is that you may have some coupons, that u
Re: [GENERAL] Date with time zone
On Sun, Nov 29, 2009 at 8:23 PM, Adrian Klaver akla...@comcast.net wrote: On Sunday 29 November 2009 2:38:43 pm Eduardo Piombino wrote: On Sat, Nov 28, 2009 at 8:55 PM, Adrian Klaver akla...@comcast.net wrote: On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to Dec 19th without adding an innecesary reference to a dummy time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say Dec 19th (GMT-3) ? The problem arises around the dates when DST starts and ends. For instance here, Washington State USA, Nov 1st was the change over date. This occurred at 2:00 AM in the morning, so on Nov 1st we where in two time zones PDT then PST. Without a reference to time it makes it hard to keep track. For the sake of simplicity I would like to leave the DST's modifications off the discussion, just for now. Considering a fixed setup, where every country has a fixed time zone (or many, but fixed), I will try to make my point. If I we can all agree on the point at a fixed setup, I'm more than willing to get into a more complex scenario and just then introduce summer times, etc. If you are ok with it. Not really because such an animal does not exist AFAIK. If does it is the exception. Analysis of the extra complications added by DST's does not add anything, yet, to the point I'm trying to make, regardless the lack of such cases in practice. As stated above time zones only have meaning with respect to date and time together. I'm not quite sure about this. For example, if you want to say I will accept bets until 6 o'clock (London Time), every day. How does that time reference need to be tied to a specific date?. It's just a reference to a time in the day, by itself. Plus a time zone, because 6 o'clock by itself is also not sufficient, it still needs a time zone reference. It's also not a timestamp, nor a date, its just a time (with time zone tracking capabilities). And that's where I see that time with time zone is a good, and the one data type to use, IMO. Because this assumes you are in the London time zone. If you are placing bets from outside the London time zone you need to be aware of the time offset, because the local time you can place the bet is going to change based on the time zone in effect. To know what time zone is in effect you need to know the date. I don't really care about the local time from where the bets are being taken. The server is located in London, configured with the London Time offset, and it is this server who accepts or rejects the bets. So if you place a bet after 6PM London Time, regardless it's 10AM in your country, it will still be rejected. It is also very clearly stated in the web page that the bets are taken until 6PM London Time. From a technical point of view, that time, 6PM London Time, can be easily defined by a time with time zone data type, contrary to any other setup based on assumptions (such as assigning the default local time zone of where the server is to the time without time zone, or keeping track of the time zone on a different data field), with a simple 18:00:00+00 (+00 stands for London Time). You can even have a server setup anywhere in the world, with any arbitrary time zone (for the server) and still be able to take bets until 6PM London Time only having specified the same value for the deadline as before 18:00:00+00 (time with time zone). I find it particularly more elegant to use this data type if available, you kill two birds at once. Going back to the date with datetime, follow me with this reasoning: If you consider a specific date (in a specific time zone) defined as a specific starting point in time (absolute and universal), and a specific ending point in time, one could say that a date is a period of time ranging from 00:00 hs to 23:59:59. (just as some other member list suggested, with which I totally agree), both times tied to a specific time zone. One could then say that, Dec 19th (London Time) goes from Dec 19th 00:00:00 (London Time) to Dec 19th 23:59:59.9... (London Time). So, making a reference to Dec 19th (London Time) would make sense as it refers to a specific absolute time frame, or range. This range, could be (or not, but that is not the point) useful for some practical situations, but the impossibility to be able to state a reference to a date, without recurring to some tweaks or patches is sthing that at the least, bugs me a little. You can referr to date just not with a time zone. Yes, but you would miss the important
[GENERAL] Date with time zone
Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a date with time zone data type? I mean, in the same manner that every country does not have the same time (due to the time zone they are in), they also don't have to be in the same day (for the same reason). Maybe it's January 10th in one place, and January 11st a couple of time zones ahead. So, in the same way that a simple time data type is not enough for precise time specification on multi time zone setups, a simple date data type is also not enough for a precise date specification in those setups. Of course you can always set another column, specifying that that date actually corresponds to a specific timezone, but in the same manner that u dont need an extra column for time values (cause u have the time with time zone), you shouldn't be needing to create another one to host the time zone for the date. I don't know, am I crazy? Thanks a lot. Eduardo.
Re: [GENERAL] Date with time zone
Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to Dec 19th without adding an innecesary reference to a dummy time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say Dec 19th (GMT-3) ? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? I'm no guru, but I don't see any obvious technical impossibility to do so. Is this so just because SQL standard says so? Can it be possible that SQL standard is a little short on this kind of need? Again, of course I can always use a timestamp set to 00:00:00 just to use its time zone tracking capabilities, but It is just as dirty as any other patch. A date is a date, and a timestamp is a timestamp, and both, used independently, should be able to keep track of its associated time zone, I think. Am I wrong on this? Apart from what SQL Standard may say, for instance. On Sat, Nov 28, 2009 at 4:00 PM, Adrian Klaver akla...@comcast.net wrote: On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote: Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a date with time zone data type? I mean, in the same manner that every country does not have the same time (due to the time zone they are in), they also don't have to be in the same day (for the same reason). Maybe it's January 10th in one place, and January 11st a couple of time zones ahead. So, in the same way that a simple time data type is not enough for precise time specification on multi time zone setups, a simple date data type is also not enough for a precise date specification in those setups. Of course you can always set another column, specifying that that date actually corresponds to a specific timezone, but in the same manner that u dont need an extra column for time values (cause u have the time with time zone), you shouldn't be needing to create another one to host the time zone for the date. I don't know, am I crazy? Thanks a lot. Eduardo. The best explanation I can offer comes from the manual. http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are: * Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries. * The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries. To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time. -- Adrian Klaver akla...@comcast.net
