Re: Interesting case of IMMUTABLE significantly hurting performance
On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote: > On 4/9/25 14:21, Nico Williams wrote: > > That to_char is not immutable is not documented though. Though it's > > clear when looking at the docs for the `jsonb_.*_tz()` functions. > > From here: > > https://www.postgresql.org/docs/current/catalog-pg-proc.html > > select proname, provolatile, prosrc from pg_proc where proname='to_char'; > [...] I'm surprised to see that counted as docs, but good to know.
Re: Interesting case of IMMUTABLE significantly hurting performance
On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote: > The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE. Q: Why would to_char() not be IMMUTABLE? A: Because it makes use of locales, and I guess the guc-timezone GUC, which could change if the expression is ultimately used in a PlPgSQL fragment, or if it's in a prepared statement. (I think.) That to_char is not immutable is not documented though. Though it's clear when looking at the docs for the `jsonb_.*_tz()` functions. Nico --
Re: Feature Proposal: Column-Level DELETE Operation in SQL
On Mon, Apr 21, 2025 at 10:23:30PM +0530, Abhishek Hatgine wrote: > These would act as a shortcut or expressive alias for setting one or more > column values to NULL. NULL values are not quite no-values, and setting some column of some row to NULL is not quite the same as deleting the column from the row. Even if the database's row encoding is such that a "null value" is not written there will be bits that indicate that the column's value is null. Now in an entity-attribute-value (EAV) sort of schema setting a column to NULL could be made to be the same as deleting that one row. But PG does not currently map tables to EAV stores, though I suppose it could eventually have table types that do just that, but that doesn't mean PG would need a new statement type. Even an RDBMS that only used an EAV metaschema internally would not need a new statement type. Setting a column value to NULL is just not "deleting a colun value". Having two ways to do this doesn't seem necessary at all. Moreover, with UPDATE one can set some columns to NULL values and others to non-NULL values in the same statement, whereas one could not do that with the new statement type you propose. [At this point this is just piling on, for which my apologies, but I thought the EAV thing might be worth noting.] Nico --
Re: Interesting case of IMMUTABLE significantly hurting performance
On Fri, Apr 11, 2025 at 07:15:44AM +0200, Laurenz Albe wrote: > On Thu, 2025-04-10 at 22:18 -0500, Merlin Moncure wrote: > > Facts. This is black magic. This has come up over and over. > > Perhaps it would help to add the excellent information from the Wiki to the > documentation: > https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions Adding a warning in the functions _docs_ to check their various attributes in the cataclog would suffice.
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
On Wed, Apr 16, 2025 at 03:53:53PM -0400, Ron Johnson wrote: > On Wed, Apr 16, 2025 at 3:42 PM Nico Williams wrote: > > I would care about what happened. To know what happened I'd have to see > > either only audit logs for committed transactions, or also see the > > COMMIT and ROLLBACK statements. > > Decode the logical replication stream? Yes, or use Debezium or the like which... do just that.
Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately
On Wed, Apr 16, 2025 at 02:43:59PM -0400, Ron Johnson wrote: > You'll have to bring that up with the PgAudit maintainer. Note, though, > that the purpose of PgAudit is not "recreate the database from audit logs"; > it's "what Auditors care about". In my experience, auditors do not care > about COMMIT and ROLLBACK statements. I would care about what happened. To know what happened I'd have to see either only audit logs for committed transactions, or also see the COMMIT and ROLLBACK statements. Nico --
