Questions about the Debian Package version of pgAdmin
I sent this to the PgAdmin list but didn't get any replies. Not sure how many people are on that list, but since these questions are general enough, I figure someone on the main list will know: I'm using the apt repository version for Debian 12 (Bookworm). I'm also using Ansible to manage the server, and I have some questions about the pgAdmin Debian package version. 1. If we're using the pgAdmin DEB repo, will `apt-update` always update pgAdmin to the latest major version? ie. Will we be doing an upgrade every four weeks? 2. Should `sudo /usr/pgadmin4/bin/setup-web.sh` be run every time `apt- update` runs, or only once? 3. Since the package is also installing Apache for us and configuring it, is there a safe way for us to modify the Apache config without breaking updates? We'd like to enable TLS for Apache, but in a way that doesn't break anything each time the setup playbook runs and updates PgAdmin. Thanks, Nick
Re: Questions about the Debian Package version of pgAdmin
On Sun, Jan 5, 2025 at 7:27 PM Nick wrote: > > I sent this to the PgAdmin list but didn't get any replies. Not sure > how many people are on that list, but since these questions are general > enough, I figure someone on the main list will know: > > I'm using the apt repository version for Debian 12 (Bookworm). I'm also > using Ansible to manage the server, and I have some questions about the > pgAdmin Debian package version. > > 1. If we're using the pgAdmin DEB repo, will `apt-update` always update > pgAdmin to the latest major version? ie. Will we be doing an upgrade > every four weeks? > Debian's Stable repos *never* (well, very very rarely) upgrade the version. It's always bug fixes, and why Stable software can get s old. > 2. Should `sudo /usr/pgadmin4/bin/setup-web.sh` be run every time `apt- > update` runs, or only once? > I doubt it, since you're not setting things up just for a patch. > 3. Since the package is also installing Apache for us and configuring > it, is there a safe way for us to modify the Apache config without > breaking updates? > > We'd like to enable TLS for Apache, but in a way that doesn't break > anything each time the setup playbook runs and updates PgAdmin. > > Thanks, > Nick > -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Questions about the Debian Package version of pgAdmin
On Sun, 2025-01-05 at 22:18 -0500, Ron Johnson wrote: > On Sun, Jan 5, 2025 at 7:27 PM Nick wrote: > > > > I have some questions about the > > pgAdmin Debian package version. > > > > 1. If we're using the pgAdmin DEB repo, will `apt-update` always > > update > > pgAdmin to the latest major version? ie. Will we be doing an > > upgrade > > every four weeks? > > > > Debian's Stable repos never (well, very very rarely) upgrade the > version. It's always bug fixes, and why Stable software can get > s old. > > This was in reference to the PgAdmin repository, not the official Debian package repository. I didn't know if the PgAdmin repo maintainers followed the same rule of one major version of PgAdmin per version of Debian, or if the PgAdmin repo was always the latest version, since PgAdmin only supports rolling releases.
Re: search_path for PL/pgSQL functions partially cached?
On Sun, 2025-01-05 at 00:12 +0100, Jan Behrens wrote:
> I constructed the following new example:
>
>
>
> CREATE TABLE "tbl" ("col" NUMERIC(15, 0));
>
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
> BEGIN
> RETURN '2.4';
> END;
> $$;
>
> BEGIN;
>
> CREATE SCHEMA "myschema";
> SET LOCAL search_path TO 'myschema';
>
> CREATE TABLE "tbl" ("col" NUMERIC);
>
> CREATE FUNCTION "foo"() RETURNS TEXT LANGUAGE plpgsql AS $$
> BEGIN
> RETURN '5.4';
> END;
> $$;
>
> CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql AS $$
> DECLARE
> "old_search_path" TEXT;
> BEGIN
> "old_search_path" := current_setting('search_path');
> SET LOCAL search_path TO "myschema";
> -- At this point, search_path is always set to 'myschema'!
> DECLARE
> "variable" "tbl"."col"%TYPE;
> BEGIN
> "variable" := "foo"();
> RETURN "variable";
> END;
> PERFORM set_config('search_path', "old_search_path", TRUE);
> END;
> $$;
>
> COMMIT;
>
> Even if
>
> DECLARE "variable" "tbl"."col"%TYPE;
>
> follows *after* the schema is set to "myschema" in the example above, I
> still get differing results, depending on how the search_path was set
> when the function was first called.
So what you should do is set the "search_path" *on* the function, not *in*
the function:
CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql
SET search_path = myschema
AS $$
DECLARE
"variable" "tbl"."col"%TYPE;
BEGIN
"variable" := "foo"();
RETURN "variable";
END;
$$;
Yours,
Laurenz Albe
Re: search_path for PL/pgSQL functions partially cached?
On Sun, 05 Jan 2025 07:48:56 +0100 Laurenz Albe wrote: > So what you should do is set the "search_path" *on* the function, not *in* > the function: > > CREATE FUNCTION "run"() RETURNS TEXT LANGUAGE plpgsql > SET search_path = myschema > AS $$ > DECLARE > "variable" "tbl"."col"%TYPE; > BEGIN > "variable" := "foo"(); > RETURN "variable"; > END; > $$; > > Yours, > Laurenz Albe Yes, that's what works and what I would also do whenever possible (probably in the form "SET search_path FROM CURRENT"). Summarizing the remaining thread, some issues are: * The documentation isn't providing a prominent warning that behavior can be surprising if "SET search_path" is not used in the function's or procedure's defintion. (E.g. searching for "schema" in the documentation page for "CREATE FUNCTION" doesn't give any helpful hints or warning.) * Things get more complicated when it's impossible to use "SET search_path" in the function's/procedure's definition, for which there are two scenarios: Scenario 1: The function or procedure needs or wants to access or use the search_path of the caller. Scenario 2: A procedure wants to execute transactional statements such as COMMIT or ROLLBACK within its body. In scenario 1, using "SET search_path" will overwrite the caller's search_path at runtime, so I cannot access it. (In my post from Sat, 4 Jan 2025 14:23:10 +0100, I have proposed a wrapper function to work around that.) In scenario 2, using "SET search_path" is simply not possible and will be rejected by PostgreSQL. * It is a bit unclear how the exact behavior is when I set a search_path from within the functions body (e.g. due to one of the two scenarios above). There are some examples that show some quite surprising behavior, at least if you don't fully understand the plan caching mechanism that is used. Kind regards, Jan Behrens
