non-immutable kod deklarovany jako immutable

[Michal Bartak]

Ahoj.
V posledni dobe si hodne hraju s TimescaleDB. Definice CAGG (Continuous Aggregate) je hodne omezena tim, ze nedovoluje pouzivat konstrukce jako CTE, subquery, window funkce a non-immutable funkce.

BTW: TimescaleDB od verze 2.20 (kveten 2025) ma experimentalni podporu pro window a non-immutable funkce. Jeste jsem to nezkousel. Takze mozna vyse uvedeny problem brzo spadne do kategorie "jen pro uspokojeni zvedavosti".

Bohuzel s temito omezenimi nejde spousty veci jednoduse dosahnout primo v CAGG (je nutne delat triggery na zdrojovych tabulkach, ktere dopocitavaji a ukladaji pomocna data pro pouziti v CAGG).

Napadlo mne, ze bych mohl "oblafnout" Postgres tim, ze funkci nadeklaruji jako IMMUTABLE, prestoze ve skutecnosti cte data z tabulek (napr. CAGG, ktery pocita ceny za energii a musi se podivat do tabulek s cennikem).
A funguje to... Jen nevim, za jakych presne okolnosti fungovat prestane.

Nemam rad takove hacky, ale momentalne by mi tato finta hodne pomohla.

Z dokumentace Postgresu:

There is relatively little difference between STABLE and IMMUTABLE categories when considering simple interactive queries that are planned and immediately executed: it doesn't matter a lot whether a function is executed once during planning or once during query execution startup. But there is a big difference if the plan is saved and reused later. Labeling a function IMMUTABLE when it really isn't might allow it to be prematurely folded to a constant during planning, resulting in a stale value being re-used during subsequent uses of the plan. This is a hazard when using prepared statements or when using function languages that cache plans (such as PL/pgSQL).

Z toho mi vychazi:

1. Nevadi to, pokud jsou dotazy provadene kazdy v ramci nove session (noveho pripojeni). Plan cache je podle mne per session (???).

2. Nevadi to, pokud je funkce SQL?

Diky
Michal Bartak

[Pavel Stehule]

čt 4. 9. 2025 v 15:19 odesílatel Michal Bartak <maxym...@gmail.com> napsal:

Tyhle fake immutable funkce funguji, pokud nedojde ke zmene te tabulky do ktere sahaji. Na rozdil od stable funkci muze dojit u immutable funkce k substituci volani konstantou - a ta konstanta muze byt ulozene budto ve funkcnim indexu nebo v provadecim planu. Provadeci plan muze byt ulozeny v plan cache a tam se dostane a) pokud je SQL pouzite v PL/pgSQL, b) pokud aplikace pouzila explicitne prikaz PREPARE nebo pokud se pouzije extended protokol a prepare se udela na urovni protokolu - napriklad to dela JDBC. 

CREATE OR REPLACE FUNCTION public.fx(_id integer)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$ BEGIN RETURN (SELECT a FROM tab WHERE id = _id); END $function$

(2025-09-04 16:24:16) postgres=# CREATE TABLE tab(id int, a int);
CREATE TABLE
(2025-09-04 16:24:41) postgres=# INSERT INTO tab VALUES(1, 10);
INSERT 0 1
(2025-09-04 16:25:05) postgres=# PREPARE p(int) AS SELECT fx(1);
PREPARE

(2025-09-04 16:25:51) postgres=# set plan_cache_mode to force_generic_plan ;
SET

(2025-09-04 16:26:37) postgres=# PREPARE p(int) AS SELECT fx(1);
PREPARE
(2025-09-04 16:26:46) postgres=# EXECUTE p(0);
┌────┐
│ fx │
╞════╡
│ 10 │
└────┘
(1 row)

(2025-09-04 16:27:03) postgres=# update tab set a = 20;
UPDATE 1
(2025-09-04 16:27:25) postgres=# EXECUTE p(0);
┌────┐
│ fx │
╞════╡
│ 10 │
└────┘
(1 row)

 

2. Nevadi to, pokud je funkce SQL?

to asi vubec nehraje roli. Muze pomoct, kdyz nastavis plan_cache_mode na force_custom_plan. Ale to resi Postgresovou plan cache - je otazkou jestli timescale nema nejakou vlastni cache, a pak by plan_cache_mode vubec pro timescale vubec nic neresilo. 

Ukazal jsem zavislost na datech. Ale muzes mit treba zavislost na konfiguraci - napr. casto datestyle nebo timezone. Fakeova immutable funkce by pak nereagovala na zmenu nastaveni. 

Tyhle fake immutable funkce se obcas pouzivaji - v podstate tim muzes "oblafnout" planner a redukovat chybu v odhadech joinu (nebo si vynutit umoznit funkcionalni index i pro nejake specificke funkce nad timestampem). Ale ma to svoje rizika, takze se to obecne nedoporucuje, a navic neni absolutni jistota, ze tohle chovani se v budoucnu nezablokuje. Hlavne v pripade funkcionalnich indexu by to mohlo udelat dost velkou paseku. Je to takova seda zona. Experti to obcas pouziji, ale neradi o tom mluvi :-).

Diky
Michal Bartak

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny „PostgreSQL-cz“ ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.
Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/CAAVzF_FREJHRHWUC3%3D7gnRNC%3DEx-BYQV0isrid_gxMBBRX-sag%40mail.gmail.com.

[Pavel Stehule]

čt 4. 9. 2025 v 16:45 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

a od 18 plan cache pouzivaji i SQL funkce. Ve starsich verzich se do plan cache muze dostat i vysledek SQL funkce skrze inlining.

[Antonin Houska]

Pavel Stehule <pavel....@gmail.com> wrote:

> Tyhle fake immutable funkce se obcas pouzivaji - v podstate tim muzes "oblafnout" planner a redukovat chybu v odhadech joinu (nebo si
> vynutit umoznit funkcionalni index i pro nejake specificke funkce nad
> timestampem). Ale ma to svoje rizika,

Nemám po ruce konkrétní příklad, ale myslím si, že za určitých okolností může
nesprávné označení funkce jako IMMUTABLE způsobit, že planner vytvoří plán,
který vrací nesprávný výsledek.

Tonda H.

[Pavel Stehule]

čt 4. 9. 2025 v 19:15 odesílatel 'Antonin Houska' via PostgreSQL-cz <postgr...@googlegroups.com> napsal:

Mohl bys zkusit najit nejaky priklad? 

Dovedu si predstavit, ze napisu funkci, ktera je chovanim VOLATILE, ale oznacim ji IMMUTABLE, a to asi bude delat brikule.

Ale pokud bych mel funkci, ktera je prirozene STABLE, a oznacim ji IMMUTABLE, tak by to nic spatnyho snad delat nemelo. V ramci jednoho dotazu mi ta funkce bude vracet korektni hodnoty.

 

Tonda H.

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny PostgreSQL-cz ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.

Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/45452.1757006105%40localhost.

[Antonin Houska]

Pavel Stehule <pavel....@gmail.com> wrote:

> čt 4. 9. 2025 v 19:15 odesílatel 'Antonin Houska' via PostgreSQL-cz <postgr...@googlegroups.com> napsal:
>
> Pavel Stehule <pavel....@gmail.com> wrote:
>
> > Tyhle fake immutable funkce se obcas pouzivaji - v podstate tim muzes "oblafnout" planner a redukovat chybu v odhadech joinu (nebo
> si
> > vynutit umoznit funkcionalni index i pro nejake specificke funkce nad
> > timestampem). Ale ma to svoje rizika,
>
> Nemám po ruce konkrétní příklad, ale myslím si, že za určitých okolností může
> nesprávné označení funkce jako IMMUTABLE způsobit, že planner vytvoří plán,
> který vrací nesprávný výsledek.
>
> Mohl bys zkusit najit nejaky priklad?
>
> Dovedu si predstavit, ze napisu funkci, ktera je chovanim VOLATILE, ale oznacim ji IMMUTABLE, a to asi bude delat brikule.
>
> Ale pokud bych mel funkci, ktera je prirozene STABLE, a oznacim ji IMMUTABLE, tak by to nic spatnyho snad delat nemelo. V ramci jednoho
> dotazu mi ta funkce bude vracet korektni hodnoty.

Pred casem jsem zaznamenal, jak Tom Lane nekomu psal "if you lie to the
planner ...". Tohle je tedy ten email:

https://www.postgresql.org/message-id/469536.1633969664%40sss.pgh.pa.us

Nejde tedy o chybny plan, ale poskozeny index taky neni legrace.

Tonda H.

[Pavel Stehule]

pá 5. 9. 2025 v 7:14 odesílatel 'Antonin Houska' via PostgreSQL-cz <postgr...@googlegroups.com> napsal:

poškozené indexy jsem zmiňoval - to je fakt průser - v lepším případě to dává špatné výsledky, v horším případě to totálně dokope data.

jakákoliv fake immutable funkce pokud závisí na datech by se neměla (ani skrz závislosti) objevit ve výrazu funkčního indexu. U fake immutable funkcí které zakrývají konfiguraci se to dělá, ale je to křehké (a někteří uživatelé netuší, co je index, natož immutable funkce), a ta konfigurace se nesmí změnit. Vždy bude lepší najít jiný způsob - např. explicitně používat konkrétní časovou zónu, atd. Je důležité nepodceňovat potenciální neznalost uživatelů, když to napíšu slušně :-)

 

Tonda H.

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny PostgreSQL-cz ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.

Tuto diskuzi najdete na adrese https://groups.google.com/d/msgid/postgresql-cz/3338.1757049239%40localhost.