[HN Gopher] Dbcritic: Constructively criticizing your Postgres s...
___________________________________________________________________
Dbcritic: Constructively criticizing your Postgres schema (using
Idris)
Author : rkrzr
Score : 62 points
Date : 2021-07-07 11:59 UTC (11 hours ago)
(HTM) web link (www.channable.com)
(TXT) w3m dump (www.channable.com)
| m_j_g wrote:
| It is nice to see practical application of dependently typed
| language!
| dwohnitmok wrote:
| Looking through the GitHub repo it doesn't look to actually use
| any of the dependently typed features of Idris.
|
| (And I see now at the end of the article that they mention
| this).
|
| If this grows in the future, I'd be curious to see if the team
| starts to integrate any dependent types.
| neolog wrote:
| Why is this written in Idris instead of a normal language?
| lgas wrote:
| From the post:
|
| _dbcritic came to life as a Channable Hackathon project.
| dbcritic is written in Idris, a functional programming
| language, to get an idea of what the language and tooling are
| like. It is quite similar to Haskell, which we use for some of
| our larger projects, but it has some neat extra features, such
| as the early returns in do expressions, and of course dependent
| types (which we didn't really use in dbcritic)._
| yzan wrote:
| There is also DBLint (https://archive.codeplex.com/?p=dblint),
| which has a fairly comprehensive set of checks.
| yosamino wrote:
| > Because the vast majority of our use cases for storing
| timestamps is to represent specific, unambiguous moments in time,
| we pretty much always want to use the timestamptz type.
|
| This advice is not great. One needs to specifically know their
| data.
|
| Postgresql stores the timezone numerically, and not together with
| the location the timezone applies to. This means it is impossible
| to deduce DST from a timestamptz alone, but this is neccessary
| because two different locations in the same timezone might very
| possibly switch to and from DST at different times of the year.
|
| To store an unambiguous moment in time ( e.g. when scheduling an
| international conference call) one can store it as a timestamptz
| but this doesn't gain a lot over just treating all time in the
| database as UTC and then converting from/to local time for
| input/display.
|
| To store a time that is unambiguous in a certain location (e.g. a
| doctor's appointment in 4 months time) one needs to store it as
| timezone without timezone and an extra field with the locality.
| It makes no sense to store the timezone, unless you _also store
| the DST_ information or unless you think you can rule out
| unexpected DST changes.
|
| Storing timestamptz really makes a lot of sense for past events
| though - for example when logging.
|
| So it really depends on what kind of timestamp you are trying to
| store, and I am not sure there is a general rule that can be
| applied.
| fulafel wrote:
| > To store an unambiguous moment in time ( e.g. when scheduling
| an international conference call) one can store it as a
| timestamptz but this doesn't gain a lot over just treating all
| time in the database as UTC and then converting from/to local
| time for input/display.
|
| This "interpret tz-less time as utc" in requires discipline to
| treat it counter to the default human interpretation of the
| data type, so is error prone. DB client libs will default to
| using ambiguous time types like LocalDate etc. Wouldn't
| recommend it.
| mason55 wrote:
| Yeah, another way to think about it is whether you're:
|
| * Storing info about an instant in time when something happened
| (or is going to happen), which would be the same across the
| entire universe (relativity notwithstanding)
|
| * You're storing info about when something should happen (or
| something happen) in the context of a specific location
|
| Typically, if you're recording things that happened (think
| logging an event), then you want to store the instant that they
| happened. This means storing them in UTC and translating to the
| user's time zone on display. If you're storing info about when
| something should happen (think of an appointment or meeting)
| then you want to store it in the context of a single, specific
| location, and translate to other user's time zones on demand.
|
| Scheduling for future events should always be in the context of
| a specific time zone or location. If someone from another time
| zone needs to see the translation into their time then you
| apply the what the differential will be between the two time
| zones at the time the event is supposed to occur in the source
| time zone (so if the destination time zone will be +6 at the
| date/time of the event in the source tz then you add 6 hours to
| the source date/time).
|
| It's complicated doing it like that but it's the only way to
| account for all the various ways TZs can change that you hadn't
| accounted for. The key is understanding what you're measuring
| date/time relative to.
| heurisko wrote:
| I often refer to this article
| https://phili.pe/posts/timestamps-and-time-zones-in-postgres...
| regarding timestamp and timestamptz
|
| >> Because the vast majority of our use cases for storing
| timestamps is to represent specific, unambiguous moments in
| time, we pretty much always want to use the timestamptz type. >
| This advice is not great. One needs to specifically know their
| data.
|
| I think the advice is good advice: when you want to store an
| unambiguous point in time, you should use the timestamptz type,
| as it stores the time in UTC, whatever you give it.
|
| "To store a time that is unambiguous in a certain location
| (e.g. a doctor's appointment in 4 months time) one needs to
| store it as timezone without timezone and an extra field with
| the locality. It makes no sense to store the timezone, unless
| you also store the DST information or unless you think you can
| rule out unexpected DST changes."
|
| In postgres, timestamptz doesn't store the timezone, only the
| UTC value, given a timezone.
|
| I think your example of "A doctor's appointment in 4 month's
| time" _isn't_ an unambiguous point in time, which is why you
| would need to store the wall time with the timezone
| information.
|
| I can save my doctor's appointment as timestamptz in 4 months
| time with
|
| # SELECT timezone('Europe/London', '2021-10-07
| 20:00'::timestamp);
|
| 2021-10-07 19:00:00+00
|
| However storing the Europe/London timezone doesn't help me as I
| can't rule out unexpected DST rule changes. If the UK
| parliament abolishes BST before that time, I will have to
| recalculate that value, otherwise I will arrive an hour early.
|
| SELECT timezone('Europe/London', '2021-10-07
| 19:00Z'::timestamptz);
|
| 2021-10-07 20:00:00 (1 row)
|
| will change to
|
| SELECT timezone('Europe/London', '2021-10-07
| 19:00Z'::timestamptz);
|
| 2021-10-07 19:00:00 (1 row)
|
| So the wall time is used, as the point in time is ambiguous.
| ruuda wrote:
| Great to see this being open sourced! Every project that relies
| on a Postgres database should put this in their CI pipeline, it's
| that good.
___________________________________________________________________
(page generated 2021-07-07 23:02 UTC)