Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

st 8. 3. 2023 v 22:29 odesílatel Bryn Llewellyn napsal: > t...@sss.pgh.pa.us wrote: > > david.g.johns...@gmail.com wrote: > > So I found where this difference in behavior is at least explicitly noted: > > /* > * If it's a named composite type (or domain over one), find the typcache > * entry and

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On 3/8/23 15:29, Bryn Llewellyn wrote: [snip] create table s.t(k int primary key, c1 int, c2 int, c3 int); insert into s.t(k, c1, c2, c3) values(1, 17, 42, 57); create type s.x as (c1 int, c2 int, c3 int); [snip] This is an excellent analysis. Native PG doesn't provide much metadata or

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> t...@sss.pgh.pa.us wrote: > >> david.g.johns...@gmail.com wrote: >> >> So I found where this difference in behavior is at least explicitly noted: >> >> /* >> * If it's a named composite type (or domain over one), find the typcache >> * entry and record the current tupdesc ID, so we can detect

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On Tue, Mar 7, 2023 at 10:19 PM Bryn Llewellyn wrote: > Why not err on the side of caution and (I trust) guaranteed currency of > each session's in-memory representation of a PL/pgSQL program with the > environment in which it executes? > > After all, you add a column in order to use it. And

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >>> david.g.johns...@gmail.com wrote: >>> b...@yugabyte.com wrote: Regard a DDL on any object that an application uses as unsafe while the app is in use. You must terminate all client-sessions before

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On Tue, Mar 7, 2023 at 9:49 PM Tom Lane wrote: > "David G. Johnston" writes: > > So I found where this difference in behavior is at least explicitly > noted: > > >/* > > * If it's a named composite type (or domain over one), find the typcache > > * entry and record the current tupdesc ID, so we

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

"David G. Johnston" writes: > So I found where this difference in behavior is at least explicitly noted: >/* > * If it's a named composite type (or domain over one), find the typcache > * entry and record the current tupdesc ID, so we can detect changes > * (including drops). We don't currently

Fwd: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

Forwarding this to the list; Note the potential bug found at the end. My actual follow-on reply notes the lack of documentation regarding the composite cache-checking behavior (relative to the non-composite situation) -- Forwarded message - From: David G. Johnston Date: Tue, Mar

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

(adding back the list) On Tue, Mar 7, 2023 at 8:24 PM David G. Johnston wrote: > On Tue, Mar 7, 2023 at 7:54 PM Bryn Llewellyn wrote: > >> >> > This is what I expected actually, though I can't point to exactly why. > >> >> Where can I read what I need in order to understand the difference

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On Tue, Mar 7, 2023 at 5:52 PM Bryn Llewellyn wrote: > > Regard a DDL on any object that an application uses as unsafe while the > app is in use. You must terminate all client-sessions before doing such a > DDL and re-start them only when all such DDLs are done successfully. > > No. If you

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> create table s.t(k serial primary key, v text); >> create function s.f(k_in in int) >> select s.f(1); > > text, function is now compiled with that type resolution fixed. > I think mostly attributable to: > > > The mutable

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On Tue, Mar 7, 2023 at 1:24 PM Bryn Llewellyn wrote: > create table s.t(k serial primary key, v text); > create function s.f(k_in in int) > select s.f(1); > text, function is now compiled with that type resolution fixed. I think mostly attributable to: > The mutable nature of record variables

Behavior of PL/pgSQL function following drop and re-create of a table that it uses

I copied my self-contained testcase, and its output (using PG Version 15.2), at the end. I read the doc section "43.11. PL/pgSQL under the Hood": www.postgresql.org/docs/15/plpgsql-implementation.html Is my mental model,