Re: Strange results when casting string to double
On 18.02.22 22:42, Peter J. Holzer wrote: If there has been a glibc update (or a postgresql update) in those 480 days (Ubuntu 14.04 is out of its normal maintenance period but ESM is still available) the running processes may well run different code than a newly started program. So it could be a bug which has since been fixed. That would be visible via `lsof`. `libc. The file `...libc...so` that `postgres` is keeping open would have the text `DEL` (as in deleted) in the `FD` column of `lsof`'s output. As opposed to a newly started program which would have `REG` (regular file) there. *t
Re: Strange results when casting string to double
On Sat, Feb 19, 2022 at 8:35 AM Tomas Pospisek wrote: > > On 18.02.22 22:42, Peter J. Holzer wrote: > > > If there has been a glibc update (or a postgresql update) in those 480 > > days (Ubuntu 14.04 is out of its normal maintenance period but ESM is > > still available) the running processes may well run different code than > > a newly started program. So it could be a bug which has since been > > fixed. > > That would be visible via `lsof`. `libc. The file `...libc...so` that > `postgres` is keeping open would have the text `DEL` (as in deleted) in > the `FD` column of `lsof`'s output. > > As opposed to a newly started program which would have `REG` (regular > file) there. If this doesn't bear fruit, are there debugging symbols? Setting a breakpoint might produce some insight. merlin
Detecting schema changes via WAL logs
Hello everyone, I’m building an open-source data integration tool (SQLpipe) and am now trying to incorporate CDC into the product. Is it possible to detect when schema changes happen via the WAL? The WAL doesn’t have to tell me what was changed, it just needs to tell me when something changed. For example, anytime I run create table or some other DDL command, there is a begin and commit message in the log, with nothing in between. If there aren’t any other operations that create such “empty” logs, it seems possible to just re-scan the tables that are being synced for schema changes whenever one of these logs is encountered.
Re: Strange results when casting string to double
On 19.02.2022 15:35, Tomas Pospisek wrote: That would be visible via `lsof`. `libc. The file `...libc...so` that `postgres` is keeping open would have the text `DEL` (as in deleted) in the `FD` column of `lsof`'s output. As opposed to a newly started program which would have `REG` (regular file) there. *t Actually, PostgreSLQ uses the same libc than any recently started program. lsof reports REG and my /lib/x86_64-linux-gnu/libc-2.19.so has file date Mar 27, 2019, so it's much older than the point in time when PostgreSQL was started the last time (aprx. 480 days ago). After all, I guess the idea of a wrong rounding setting (set to round down, raised by Peter J. Holzer) seems most plausible to me. In particular, since the "right" and "wrong" values caused by wrong rounding shown by Tom Lane are exactly the values I am seeing. Also, there is a quite aggressive import script running for some days, which even tried to replace some of the core functions, like array_length (it added a pure PL/pgSQL version in public schema). Seems like they wanted to "Polyfill" some required functions. Maybe that script is responsible for changing the process' rounding mode? The customer started that script without thinking too much about it... :( @Tom Lane: you say, PostgreSQL does not / cannot change rounding mode at any time? There is no function to do so? So, the script (it's more like a module) must provide a Shared Object module with a C function in order to change the FPU's rounding mode? Because several people recommended using a debugger in order to see whats going on here: actually I have no expertise with debugging on Linux without an IDE. So I did not yet think of using a debugger so far. I will try a restart of the DB ASAP. However, in order to prove the rounding mode thesis: someone knows whether fesetround(FE_DOWNWARD) just sets a flag in the C runtime environment or does this call actually set the rounding mode in the FPU's control word? Is there any chance to get that current rounding mode with or even without a debugger? (Maybe some file in /proc filesystem?) I'm very unhappy with the customer starting that import script. In order to find the `smoking gun`, I will now have a closer look at the "product" that import is based on and what they have done to the database... Regards, Carsten
Re: Strange results when casting string to double
Carsten Klein writes: > @Tom Lane: you say, PostgreSQL does not / cannot change rounding mode at > any time? There is no function to do so? So, the script (it's more like > a module) must provide a Shared Object module with a C function in order > to change the FPU's rounding mode? Per grep, there is no call of fesetround() in the Postgres source tree. I'm not sure offhand whether libc exposes any other APIs that could change the rounding mode, but I am quite sure that we wouldn't be intentionally changing it anywhere. > I will try a restart of the DB ASAP. However, in order to prove the > rounding mode thesis: someone knows whether fesetround(FE_DOWNWARD) just > sets a flag in the C runtime environment or does this call actually set > the rounding mode in the FPU's control word? Is there any chance to get > that current rounding mode with or even without a debugger? (Maybe some > file in /proc filesystem?) The OS would surely allow each process to have its own setting of the rounding mode, so I doubt you can see it from outside. Another point to keep in mind is that no matter how invasive that import script might be, it's still hard to explain how it'd affect the rounding mode in other backend processes. You have to postulate either that the rounding mode has been changed in the postmaster process (and then inherited by session backends via fork()), or that some code running at the time of child process creation changes the mode, or that they replaced numeric_float8 with something else. I think the only way that the postmaster's rounding mode could change after postmaster start is the cosmic-ray hypothesis; while we do have features that'd allow loading extra code into the postmaster, I'm pretty sure they only take effect at postmaster start. So even if that import script tried to do that, it wouldn't have succeeded yet. Of the other two hypotheses, "substitute numeric_float8" seems like the most likely, especially given the other stuff you mentioned the script doing. Have you checked the relevant pg_cast entry to see if it's been changed? It'd also be interesting to see if the odd rounding behavior happens in all databases of the cluster or just one. regards, tom lane
Additional accessors via the Extension API ?
Suppose I have defined an additional type in a PG extension. Is it possible to add custom accessors to that type -much like jsonb does- but use an API/hook without touching the core PG grammar & parser? Hypothetical Examples: Assuming I have a TextFile type I’d like to implement syntax like: (‘/home/me/a.txt’::TextFile).firstline (‘/home/me/a.txt’::TextFile).lastline (‘/home/me/a.txt’::TextFile).countlines() (‘/home/me/a.txt’::TextFile).size() (‘/home/me/a.txt’::TextFile).datemodified() The only relevant patch I could find is [1] but it’s a dead-end [1] https://www.postgresql.org/message-id/20210501072458.adqjoaqnmhg4l34l%40nol
