pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug / Unintentional Feature: non-immutable functions can be used for generated columns.
4+ messages / 3 participants
[nested] [flat]
* Bug / Unintentional Feature: non-immutable functions can be used for generated columns.
@ 2022-10-01 15:18 Shane Plesner <gebnar@gmail.com>
2022-10-01 18:54 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Shane Plesner @ 2022-10-01 15:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Important preface: This "bug" turns out to enable functionality that is
extremely useful. Please carefully consider how the usefulness of this can
be retained when "fixing" the bug.
No error or warning or behavioral problems occur if the function used to
fill a generated column is replaced with a non-immutable version after it
is assigned as the column generator.
SQL which reproduces this issue, as well as showing one valid (useful)
use-case for why this isn't a bad thing in itself:
create function gen_tmp() returns text
> language plpgsql
> immutable
> as $$
> declare
> _tmp text;
> begin
> loop
> select 'C-'||array_to_string(array(select
> substr('ABCDEFGHJKLMNPQRSTUVWXYZ0123456789',((random()*(33)+1)::integer),1)
> from generate_series(1,8)),'') into _tmp;
> exit when not exists(select 1 from tmp where k = _tmp);
> end loop;
> return _tmp;
> end
> $$;
>
> create table tmp(
> k text unique primary key generated always as (gen_tmp()) stored,
> v text
> );
>
> create or replace function gen_tmp() returns text
> language plpgsql
> as $$
> declare
> _tmp text;
> begin
> loop
> select 'C-'||array_to_string(array(select
> substr('ABCDEFGHJKLMNPQRSTUVWXYZ0123456789',((random()*(33)+1)::integer),1)
> from generate_series(1,8)),'') into _tmp;
> exit when not exists(select 1 from tmp where k = _tmp);
> end loop;
> return _tmp;
> end
> $$;
>
> insert into tmp (v) select 'test' from generate_series(1,10);
>
> select * from tmp;
>
Discovered by Shane Plesner, by accident, about 10 minutes ago...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns.
2022-10-01 15:18 Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
@ 2022-10-01 18:54 ` David G. Johnston <david.g.johnston@gmail.com>
2022-10-01 21:44 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2022-10-01 18:54 UTC (permalink / raw)
To: Shane Plesner <gebnar@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Sat, Oct 1, 2022 at 8:27 AM Shane Plesner <gebnar@gmail.com> wrote:
> Important preface: This "bug" turns out to enable functionality that is
> extremely useful. Please carefully consider how the usefulness of this can
> be retained when "fixing" the bug.
>
> No error or warning or behavioral problems occur if the function used to
> fill a generated column is replaced with a non-immutable version after it
> is assigned as the column generator.
>
>
> Discovered by Shane Plesner, by accident, about 10 minutes ago...
>
Discovered (in various incarnations) by many people over the years...
The documentation clearly states this is not a supported setup. In
particular it tends to break during dump/restore (though I think that
mostly happens for check constraints). It is unfortunate that the system
cannot simply prohibit the action and instead must rely upon documentation,
but that is how things are. Furthermore, if you lie to the system like in
your example, about your function being immutable when it actually is not,
the consequences are on you alone (i.e., we do not try to static analyze
the function body). That includes fixing any problems if you determine a
truly immutable function is buggy and thus needs to be changed to produce
the correct output which is different that prior output.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns.
2022-10-01 15:18 Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
2022-10-01 18:54 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. David G. Johnston <david.g.johnston@gmail.com>
@ 2022-10-01 21:44 ` Shane Plesner <gebnar@gmail.com>
2022-10-01 23:04 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Shane Plesner @ 2022-10-01 21:44 UTC (permalink / raw)
To: ; +Cc: pgsql-bugs@lists.postgresql.org
A lot of what makes postgres valuable and trustworthy is the way it
guarantees data consistency.
One cannot apply a mutable function as the generator. The database throws
an exception.
This replacement behavior is, at the very least, completely undocumented.
It seems to me that any time a function is replaced, there could be a check
to see if it was changed from immutable to mutable. If it was, check the
schema for any references to that function that require immutable function
calls, and raise an exception.
Is there something I'm missing that makes this unfeasible?
On Sat, Oct 1, 2022 at 1:54 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Sat, Oct 1, 2022 at 8:27 AM Shane Plesner <gebnar@gmail.com> wrote:
>
>> Important preface: This "bug" turns out to enable functionality that is
>> extremely useful. Please carefully consider how the usefulness of this can
>> be retained when "fixing" the bug.
>>
>> No error or warning or behavioral problems occur if the function used to
>> fill a generated column is replaced with a non-immutable version after it
>> is assigned as the column generator.
>>
>>
>
>> Discovered by Shane Plesner, by accident, about 10 minutes ago...
>>
>
> Discovered (in various incarnations) by many people over the years...
>
> The documentation clearly states this is not a supported setup. In
> particular it tends to break during dump/restore (though I think that
> mostly happens for check constraints). It is unfortunate that the system
> cannot simply prohibit the action and instead must rely upon documentation,
> but that is how things are. Furthermore, if you lie to the system like in
> your example, about your function being immutable when it actually is not,
> the consequences are on you alone (i.e., we do not try to static analyze
> the function body). That includes fixing any problems if you determine a
> truly immutable function is buggy and thus needs to be changed to produce
> the correct output which is different that prior output.
>
> David J.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns.
2022-10-01 15:18 Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
2022-10-01 18:54 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. David G. Johnston <david.g.johnston@gmail.com>
2022-10-01 21:44 ` Re: Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
@ 2022-10-01 23:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-10-01 23:04 UTC (permalink / raw)
To: Shane Plesner <gebnar@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Shane Plesner <gebnar@gmail.com> writes:
> It seems to me that any time a function is replaced, there could be a check
> to see if it was changed from immutable to mutable. If it was, check the
> schema for any references to that function that require immutable function
> calls, and raise an exception.
> Is there something I'm missing that makes this unfeasible?
Cost, added maintenance burden, race conditions.
Also, just complaining about dropping the immutability flag seems
rather pointless. If you redefine the function in a way that changes its
results, but continue to mark it immutable, you've still broken a lot
of situations (e.g., expression indexes that depend on the function's
results). If memory serves, we've actually discussed refusing any
alterations at all of an "immutable" function once it's defined.
But that's still not much help, because we can't usefully verify that
an "immutable" function's behavior actually is immutable. In the end
it's on the user to preserve its behavior, or do appropriate cleanup
(such as reindexing) after a change.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-10-01 23:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-01 15:18 Bug / Unintentional Feature: non-immutable functions can be used for generated columns. Shane Plesner <gebnar@gmail.com>
2022-10-01 18:54 ` David G. Johnston <david.g.johnston@gmail.com>
2022-10-01 21:44 ` Shane Plesner <gebnar@gmail.com>
2022-10-01 23:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox