Binary encoding of TIMESTAMP WITH TIME ZONE
I'm dealing with an issue where a query uses 'today'::date to select one of a number of rows depending on the day modulo the number of rows. The intent is that different information will be shown starting after midnight local time. The query runs as expected in psql and using psycopg2. However, when using the same query using the Rust adapter the transition to a new row started showing up after midgnight GMT. I opened an issue on Github (https://github.com/sfackler/rust-postgres/issues/608 ) and the maintainer claimed the Rust adapter *had* to initialize timezone to UTC in order to properly convert "to and from time datatypes". I pointed out that the timezone offset is available in psql and psycopg2, but then he replied the binary encoding of timestamptz does *not* include the timezone offset. He pointed me to the function timestamptz_send() which per the comments "converts timestamptz to binary format". I found that the TimestampTz used in the function is a typedef for an int64, but since I'm not familiar with the code, I can't tell if timezone offset is embedded in there or not. I'm hoping someone reading this can confirm (or deny) the above (or do I need to ask the -hackers list?). Regards, Joe
DOMAIN/composite TYPE vs. base TYPE
Hello, I'm considering creating a TYPE for what may be called a "possibly imprecise date" (pidate). The most obvious use is for recording dates such as births or deaths of historical individuals, where we may know that someone died precisely on a given year-month-day, but the birth may only be known down to year-month or just the year (or perhaps we know precisely the baptism date [Adam Smith], but not the actual birth, so we want to record the former but qualified so it can be annotated on display). Another use is for publications, like magazines that are issued on a monthly basis or journals that are issued on a quarterly or seasonal basis. We currently have two instances of this kind, using a standard DATE column plus a CHAR(1) column that encodes (on a limited basis for now) the YMD, YM or Y level of precision, and a simple SQL function to return a textual representation of the pidate. It would be nice to generalize this before going further. The first option I explored was creating a composite type with the two attributes, but that doesn't allow specification of DEFAULTs, NOT NULL or CHECK expressions on the precision code attribute. It seems I'd have to create a DOMAIN first, then use DATE and that domain to create a composite TYPE, to finally use the latter in actual tables. That layering looks cumbersome. Another option, which I havent't tried, is to subvert PG by creating an empty table, since that creates a "record type", but even if possible that would be a hack. Finally there's the base TYPE. This entails writing some seven functions "in C or another low-level language" (does PG support *any* other such language?), plus installing a library with those functions in a production environment. Doable, yes, but not very friendly either. Am I overlooking something or is the practice of creating abstractions in object-relational databases mostly unchanged? Regards, Joe
Re: DOMAIN/composite TYPE vs. base TYPE
Hello Rob, On 28/9/20 17:17, Rob Sargent wrote: just record all three fields (day, month, year) with nulls and do the to-date as needed. That is not sufficient. An earlier implementation had something like a CHAR(8) to record MMDD, but how can you indicate, for example, an issue date of a bimonthly magazine, say July-Aug 2020? We can store 2020-07-01 in the DATE attribute, but we need another attribute to indicate it's really two months. Also, by storing three separate columns, you loose the beauty of the PG DATE abstraction. Joe
Re: DOMAIN/composite TYPE vs. base TYPE
Hello Tom, On 28/9/20 17:25, Tom Lane wrote: Domain-over-composite might be a slightly simpler answer than your first one. It's only available in relatively late-model PG, and I'm not sure about its performance relative to your other design, but it is an alternative to think about. "Domain-over-composite" meaning create a TYPE first (DATE, CHAR(1)) and then a DOMAIN based on that type? (1) How late model are we talking? The DOMAIN syntax doesn't seem changed from PG 11 to PG 13? (2) Can a CHECK constraint specify attributes of the composite? Note that attaching NOT NULL constraints at the domain level is almost never a good idea, because then you find yourself with a semantically impossible situation when, say, a column of that type is on the nullable side of an outer join. We allow such constraints, but they will be nominally violated in cases like that. NULLs: Tony Hoare's "billion dollars of pain and damage" transported to SQL. Joe
Re: DOMAIN/composite TYPE vs. base TYPE
Hello Gavan, On 28/9/20 17:52, Gavan Schneider wrote: Consider expressing all the component fields as a range. This allows you the ability to be a precise as you need and still have the benefits of well defined comparison functions. I did consider that, but it's a tradeoff between 80% of the cases being a single precise date, 18% being a single date with some imprecision around a single month, and the rest with "ranges" of months or other intervals. Joe
checkpointer and other server processes crashing
Hello, We've been experiencing PG server process crashes about every other week on a mostly read only website (except for a single insert/update on page access). Typical log entries look like LOG: checkpointer process (PID 11200) was terminated by signal 9: Killed LOG: terminating any other active server processes Other than the checkpointer, the server process that was terminated was either doing a "BEGIN READ WRITE", a "COMMIT" or executing a specific SELECT. The database is always recovered within a second and everything else appears to resume normally. We're not certain about what triggers this, but in several instances the web logs show an external bot issuing multiple HEAD requests on what is logically a single page. The web server logs show "broken pipe" and EOF errors, and PG logs sometimes shows a number of "incomplete startup packet" messages before the termination message. This started roughly when the site was migrated to Go, whose web "processes" run as "goroutines", scheduled by Go's runtime (previously the site used Python and Gunicorn to serve the pages, which probably isolated the PG processes from a barrage of nearly simultaneous requests). As I understand it, the PG server processes doing a SELECT are spawned as children of the Go process, so presumably if a "goroutine" dies, the associated PG process would die too, but I'm not sure I grasp why that would cause a recovery/restart. I also don't understand where the checkpointer process fits in the picture (and what would cause it to die). For the record, this is on PG 11.9 running on Debian. TIA, Joe
Re: checkpointer and other server processes crashing
On 15/2/21 16:29, Adrian Klaver wrote: On 2/15/21 1:15 PM, Joe Abbate wrote: We've been experiencing PG server process crashes about every other week on a mostly read only website (except for a single insert/update on page access). Typical log entries look like LOG: checkpointer process (PID 11200) was terminated by signal 9: Killed LOG: terminating any other active server processes Have you looked at the system logs to see if the OOM killer is involved? No, I hadn't, but now I see that of the past three crashes, four postgres processes were OOM victims. I guess I have to read the OOM articles I've been skipping (read one a long time ago). If you have any specific suggestions, let me know. Thanks Adrian, Joe
