Re: [External] Re: md5 issues Postgres14 on OL7

[Michael Mühlbeyer]

thanks Christoph,

best,
Michael

-Original Message-
From: Christoph Moench-Tegeder 
Date: Monday, 20. December 2021 at 15:22
To: Michael Mühlbeyer 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: [External] Re: md5 issues Postgres14 on OL7

Hi!

## Michael Mühlbeyer (michael.muehlbe...@trivadis.com):

> postgres=# select md5('just a test');
> ERROR:  out of memory

Active FIPS mode (/proc/sys/crypto/fips_enabled => 1) on the server does
produce this behaviour.

Regards,
Christoph

-- 
Spare Space

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Pavel Stehule]

>
> From PostgreSQL's SQL perspective the session variables are common
> database objects (contra SQL/PL where package variables are SQL/PL language
> objects), and SQL disallows ambiguity. This is a little bit more complex
> problem, because session variables can be used everywhere in Postgres (not
> just in PL/pgSQL).
>
>
>
I though Oracle's PL/SQL, not SQL/PL

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Pavel Stehule]

Hi

Yes, I did read your “schema variables” post on your site “Pavel Stehule’s
> blog — Some notes about PostgreSQL”. It gives me a very good idea about
> what you have in mind.
>
> But as I’ve come to understand the term “Functional Spec”, this denotes a
> formal deliverable that a product development (sub)group owns and maintains
> collectively. Especially, it has a status which, eventually, is set to
> “Approved”. And apart from minor bug fixes, no code makes its way into a
> released version of the (sub)product in question unless the group has
> approved its Functional Spec.
>
> Is this model not used for the PostgreSQL system?
>

Development model in Postgres is not too formal, the specification can be
changed until the last moment, the main word has commetter with an
agreement of the author of patch and all other people. The development of
Postgres is much more agile than waterfall.


> Your blogpost and all the comments are dated Feb 2018—so almost four years
> ago now. What needs to happen for your project to be exposed in a PG
> Release? And which release might this be?
>

Technically, this patch is not too complex, but a) it was harder to find
cleaner with good performance implementation in architecture (the current
architecture knows queries and utility commands, and LET is something
between). b) it was harder to find an agreement about specification because
global temporal objects like global temporary tables or session variables
are not in Postgres today (there is not long experience with this feature)
. Unfortunately 1. the related part of standard SQL/PSM is not widely
accepted and this part of the standard is almost dead , 2. the introduced
modules are +/- Postgres's schema, so it is a really redundant concept, 3.
SET command (in SQL/PSM (ANSI/SQL) is used for different purpose in
Postgres, and I had to use keyword LET (there is not possibility to use
keyword SET without compatibility break), c) there was long discussion if
variables should be transactional or non transactional (I strongly support
not transactional by default - like any other databases does, and
transactional behavior will be optional in next step).

I invite any help with code and documentation review (and support in
discussion) - I am not native speaker, and my English is very poor. I hope
there is a change to commit this patch in Postgres 15. But it depends on
commiter's capacite - and there are a lot of patches in the queue.

You can watch the progress of this work on pgsql-hackers mailing list

older
https://www.postgresql.org/message-id/flat/cafj8prdy+m9ooxfo10r7j0pakccaum-tweatrdsrslgmb1v...@mail.gmail.com

current
https://www.postgresql.org/message-id/flat/CAFj8pRD053CY_N4=6SvPe7ke6xPbh=k50luaowjc3jm8me9...@mail.gmail.com


>
> Back to the substance of your proposal,
>
> 1. I see that a schema variable will be another kind of object in the
> class that has tables, views, sequences, indexes, UDTs, procedures,
> functions, and so on. So it’s persisted within a database; it’s owned by a
> user; and it’s localized in a schema. Does it have its own namespace too?
> For example, can a table x, a function x(), and a schema variable x all
> live in the same schema s in some database. And can I use the normal
> qualified name syntax to access a schema variable?
>

variables have their own namespace, because they have their own catalog
table. It is designed like any catalog object - so you can use (or you
don't need) to use a qualified identifier. But there is not any schema
scope in Postgres now. Anything depends on SEARCH_PATH setting. So
variables can be in the same schema with tables and functions (the access
rules are the same).


> 2. It seems, then, that all the familiar notions, and especially the
> ability for a non-owner to access it apply. I see that the SELECT privilege
> governs who can read the value of a schema variable. But there seems to be
> no mechanism that governs who can change the value of a schema variable. It
> looks like only the owner can change the value—using the new LET SQL
> statement. Does this mean both that a top-level call from a client session
> that’s authorized as the owner can change it and a “security definer”
> subprogram with the same owner can change it—but that there is no explicit
> (say, WRITE) privilege for this. Is this what you intend? If so, why not
> have a WRITE privilege?
>

In the last patch I renamed schema variables to session variables (on the
community request). You can grant READ or WRITE rights to other users by
command GRANT, or you can write a security owner function, if you need more
precious work with sensitive data.


> 4. You said “security definer function”. Is this what you mean? Or do you
> mean ANY function or procedure as long as the current user (i.e. the owner
> of the most tightly enclosing security definer unit) is the schema
> variable’s owner?
>

inside the security owner function you are running under functions's owner
identity. It can have the 

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>>   update t set t.v = p.v where t.k = p.k;
>> 
>> At run-time, p() terminates with an obscurely worded error:
>> 
>> 42703: column "t" of relation "t" does not exist.
> 
> "set t.v" is simply invalid SQL and the error has nothing with this 
> discussion.
> 
> https://www.postgresql.org/docs/current/sql-update.html

Well, yes, David. Please forgive me. I elided too much. This syntax:

update t set t.v = 'mouse' where t.k = 1;

feels like it ought to be legal. And indeed it is in Oracle Database. And so, 
therefore, is it legal too in embedded form in a PL/SQL procedure that uses 
formals or local variables in place of the manifest constants.

I meant only to say “This expresses my aim. How can I spell it so that I can 
name the formals as I want?”

I’m afraid that I was too short of time, earlier today, to take it further 
myself. I thought that it was the example that I’d remembered. But it simply 
isn’t. This works perfectly well:

drop procedure if exists u1.p(int, text) cascade;
create procedure u1.p(k in t.k%type, v in t.v%type)
  security definer
  language plpgsql
as $body$
begin
  update t set v = p.v where t.k = p.k;
end;
$body$;

It feels strange to me not to me able to qualify the name of the to-be-updated 
column. But I do see that this has no practical consequence. The syntax 
disambiguates the meaning here in a way that it cannot in a WHERE predicate.

So, unless I later find a better example, I’ll assume that I can always dot 
qualify the name of a schema variable in PL/pgSQL code.

In other words, my concern here simply falls away—which is a good thing.

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[David G. Johnston]

On Wed, Dec 22, 2021 at 6:27 PM Bryn Llewellyn  wrote:

>
> *  update t set t.v = p.v where t.k = p.k;*
> At run-time, p() terminates with an obscurely worded error:
>
> 42703: column "t" of relation "t" does not exist.
>
>
"set t.v" is simply invalid SQL and the error has nothing with this
discussion.

https://www.postgresql.org/docs/current/sql-update.html

David J.

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Bryn Llewellyn]

> david.g.johns...@gmail.com  wrote:
> 
>> b...@yugabyte.com  wrote:
>> 
>> Is this model not used for the PostgreSQL system?
> 
> It is not.  Basically we have an "Proposed Patches" tracker and they either 
> get committed, or they do not.  Once committed, new features become available 
> at the next annual release.
> 
>> 1. I see that a schema variable will be another kind of object in the class 
>> that has tables, views, sequences, indexes, UDTs, procedures, functions, and 
>> so on. So it’s persisted within a database; it’s owned by a user; and it’s 
>> localized in a schema. Does it have its own namespace too? For example, can 
>> a table x, a function x(), and a schema variable x all live in the same 
>> schema s in some database. And can I use the normal qualified name syntax to 
>> access a schema variable?
> 
> I haven't read the patch in great detail (or recently...) but: The first four 
> items you listed (relations) share a namespace.  Procedures and functions 
> share a different namespace.  I'm presuming schema variables will share the 
> relations namespace.  These namespaces are subdivisions of schemas.
> 
>> 2. It seems, then, that all the familiar notions, and especially the ability 
>> for a non-owner to access it apply. I see that the SELECT privilege governs 
>> who can read the value of a schema variable. But there seems to be no 
>> mechanism that governs who can change the value of a schema variable. It 
>> looks like only the owner can change the value—using the new LET SQL 
>> statement. Does this mean both that a top-level call from a client session 
>> that’s authorized as the owner can change it and a “security definer” 
>> subprogram with the same owner can change it—but that there is no explicit 
>> (say, WRITE) privilege for this. Is this what you intend? If so, why not 
>> have a WRITE privilege?
> 
> We’d probably call it INSERT and/or UPDATE privileges to avoid creating a new 
> enumeration value.
> 
>> 4. You said “security definer function”. Is this what you mean? Or do you 
>> mean ANY function or procedure as long as the current user (i.e. the owner 
>> of the most tightly enclosing security definer unit) is the schema 
>> variable’s owner?
> 
> https://www.postgresql.org/docs/current/sql-createfunction.html 
> 
> 
> If the invoker (current user) and the function owner are the same user the 
> specified mode is immaterial for purposes of that query.  Though in the 
> presence of views and triggers it can still get a bit confusing.
> 
>> 5. Could you please consider allowing a CONSTANT schema variable (with the 
>> usual syntax and requirement for an initialization expression)? One very 
>> popular use of a spec-level package variable is for a universal constant 
>> that has to be accessed in several places—like, for example, the conversion 
>> factor between some metric unit and some imperial unit.
> 
> Interesting.  Specifically, though, constant even for a superuser and the 
> owner? Usually constant is enforced by just not allowing people to update.
> 
> Another way to think of the question, are these immutable or stable?
> 
>> 3. What is the precedence scheme? For example, if a SQL statement in a 
>> PL/pgSQL unit has a restriction like this:
>> 
>>…where col = x…
>> 
>> and x is both the name of an in-scope variable (or formal parameter) in the 
>> unit and the name of a schema variable?
>>  
>> When the table has a column called x, then there’s (at least sometimes) no 
>> way round a run-time collision error except to rename one of the X’s. 
>> (Qualifying the names doesn’t fix it.)
>> 
>> Usually qualifying fixes it just fine - just some syntax elements presently 
>> do not allow for a qualifier to be added preventing the option from being 
>> used.
>>  
>> Will it be the same with schema variables? The question extends to ordinary 
>> assignment statements that become SQL statements under the covers:
>> 
>>   v := a + b;
>> 
>> where b happens to be both an in-scope variable and a schema variable.
> 
> I'm light on specifics at the moment but this is already a solved problem and 
> schema variables should stick with the existing convention.  Which I think is 
> telling the user there is ambiguity and to add an appropriate qualifier.
> 
> [If I understand correctly], a schema variable is slightly augmented 
> shorthand for what today can be done by writing a scalar subquery (i.e., 
> schema.col == (SELECT tbl.vars FROM schema.vars)) with the presumption that 
> the table vars has exactly one row.

Thanks for clarifying how code that brings a new feature gets into a PG Release.

1. Yes, I worded my question about namespaces loosely aiming only to elicit a 
clear account of the rules. Having schema variables share the relations 
namespace sounds sensible.

2. Your point about preferring to reuse existing keywords over inventing new 

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Is this model not used for the PostgreSQL system?
> 
> It is not.  Basically we have an "Proposed Patches" tracker and they either 
> get committed, or they do not.  Once committed, new features become available 
> at the next annual release.
> 
>> 1. I see that a schema variable will be another kind of object in the class 
>> that has tables, views, sequences, indexes, UDTs, procedures, functions, and 
>> so on. So it’s persisted within a database; it’s owned by a user; and it’s 
>> localized in a schema. Does it have its own namespace too? For example, can 
>> a table x, a function x(), and a schema variable x all live in the same 
>> schema s in some database. And can I use the normal qualified name syntax to 
>> access a schema variable?
> 
> I haven't read the patch in great detail (or recently...) but: The first four 
> items you listed (relations) share a namespace.  Procedures and functions 
> share a different namespace.  I'm presuming schema variables will share the 
> relations namespace.  These namespaces are subdivisions of schemas.
> 
>> 2. It seems, then, that all the familiar notions, and especially the ability 
>> for a non-owner to access it apply. I see that the SELECT privilege governs 
>> who can read the value of a schema variable. But there seems to be no 
>> mechanism that governs who can change the value of a schema variable. It 
>> looks like only the owner can change the value—using the new LET SQL 
>> statement. Does this mean both that a top-level call from a client session 
>> that’s authorized as the owner can change it and a “security definer” 
>> subprogram with the same owner can change it—but that there is no explicit 
>> (say, WRITE) privilege for this. Is this what you intend? If so, why not 
>> have a WRITE privilege?
> 
> We’d probably call it INSERT and/or UPDATE privileges to avoid creating a new 
> enumeration value.
> 
>> 4. You said “security definer function”. Is this what you mean? Or do you 
>> mean ANY function or procedure as long as the current user (i.e. the owner 
>> of the most tightly enclosing security definer unit) is the schema 
>> variable’s owner?
> 
> https://www.postgresql.org/docs/current/sql-createfunction.html
> 
> If the invoker (current user) and the function owner are the same user the 
> specified mode is immaterial for purposes of that query.  Though in the 
> presence of views and triggers it can still get a bit confusing.
> 
>> 5. Could you please consider allowing a CONSTANT schema variable (with the 
>> usual syntax and requirement for an initialization expression)? One very 
>> popular use of a spec-level package variable is for a universal constant 
>> that has to be accessed in several places—like, for example, the conversion 
>> factor between some metric unit and some imperial unit.
> 
> Interesting.  Specifically, though, constant even for a superuser and the 
> owner? Usually constant is enforced by just not allowing people to update.
> 
> Another way to think of the question, are these immutable or stable?
> 
>> 3. What is the precedence scheme? For example, if a SQL statement in a 
>> PL/pgSQL unit has a restriction like this:
>> 
>>…where col = x…
>> 
>> and x is both the name of an in-scope variable (or formal parameter) in the 
>> unit and the name of a schema variable?
>>  
>> When the table has a column called x, then there’s (at least sometimes) no 
>> way round a run-time collision error except to rename one of the X’s. 
>> (Qualifying the names doesn’t fix it.)
>> 
>> Usually qualifying fixes it just fine - just some syntax elements presently 
>> do not allow for a qualifier to be added preventing the option from being 
>> used.
>>  
>> Will it be the same with schema variables? The question extends to ordinary 
>> assignment statements that become SQL statements under the covers:
>> 
>>   v := a + b;
>> 
>> where b happens to be both an in-scope variable and a schema variable.
> 
> I'm light on specifics at the moment but this is already a solved problem and 
> schema variables should stick with the existing convention.  Which I think is 
> telling the user there is ambiguity and to add an appropriate qualifier.
> 
> [If I understand correctly], a schema variable is slightly augmented 
> shorthand for what today can be done by writing a scalar subquery (i.e., 
> schema.col == (SELECT tbl.vars FROM schema.vars)) with the presumption that 
> the table vars has exactly one row.

Thanks for clarifying how code that brings a new feature gets into a PG Release.

1. Yes, I worded my question about namespaces loosely aiming only to elicit a 
clear account of the rules. Having schema variables share the relations 
namespace sounds sensible.

2. Your point about preferring to reuse existing keywords over inventing new 
ones is well-taken. UPDATE seems to me to be the right choice. If we can 
informally consider NULL to be a special value rather than 

Re: Tab-completion error...?

[Adrian Klaver]

On 12/22/21 2:14 PM, Theodore M Rolle, Jr. wrote:
Please reply to list also.
Ccing list.

From below, what did pacman -Syyuu do?


You are correct in guessing what I did...

config.log:
a bunch of
#define HAVE_LIBREADLINE 1
then
configure:13450: checking readline/readline.h usability
configure:13450: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Werror=vla -Wendif-labels 
-Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -Wno-format-truncation 
-Wno-stringop-truncation -O2 -I/home/ted/hercules-helper/rexx/include 
-D_GNU_SOURCE  conftest.c >&5

configure:13450: $? = 0

configure:13450: result: yes
configure:13450: checking readline/readline.h presence
configure:13450: gcc -E -I/home/ted/hercules-helper/rexx/include
-D_GNU_SOURCE  conftest.c
configure:13450: $? = 0
configure:13450: result: yes
configure:13450: checking for readline/readline.h
configure:13450: result: yes
configure:13480: checking readline/history.h usability
configure:13480: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wimplicit-fallthrough=3
-Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv
-fexcess-precision=standard -Wno-format-truncation
-Wno-stringop-truncation -O2
-I/home/ted/hercules-helper/rexx/include -D_GNU_SOURCE  conftest.c >&5
configure:13480: $? = 0
configure:13480: result: yes
configure:13480: checking readline/history.h presence
configure:13480: gcc -E -I/home/ted/hercules-helper/rexx/include
-D_GNU_SOURCE  conftest.c
configure:13480: $? = 0
configure:13480: result: yes
configure:13480: checking for readline/history.h
configure:13480: result: yes
configure:13602: checking zlib.h usability
configure:13602: gcc -c -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wimplicit-fallthrough=3
-Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv
-fexcess-precision=standard -Wno-format-truncation
-Wno-stringop-truncation -O2
-I/home/ted/hercules-helper/rexx/include -D_GNU_SOURCE  conftest.c >&5
configure:13602: $? = 0

It looks good, doesn't it?
N.B. v14.1 is the first version to have this problem. Another thought: 
perhaps the pacman -Syyuu  update did it.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Tab-completion error...?

[Magnus Hagander]

On Wed, Dec 22, 2021 at 10:16 PM Theodore M Rolle, Jr. 
wrote:

> I'm having a problem with v14.1 on my Raspberry Pi 4[8Gb]
> After a clean download and compile, when using psql, using tab completion
> psql -U postgres
> postgres=# \i my_databases\tpsql: symbol lookup error: psql: undefined
> symbol: PQmblenBounded
>
>
This looks like your libpq is out of sync with your psql. That is, likely
your psql is 14.1, but libpq is an older version. You may have more than
one version of libpq on the system. For example, you have your own compiled
version of psql, but it's using a system-default version of libpq.so which
is from an older version.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Tab-completion error...?

[Adrian Klaver]

On 12/22/21 1:15 PM, Theodore M Rolle, Jr. wrote:

I'm having a problem with v14.1 on my Raspberry Pi 4[8Gb]
After a clean download and compile, when using psql, using tab completion
psql -U postgres
postgres=# \i my_databases\tpsql: symbol lookup error: psql: undefined 
symbol: PQmblenBounded




Not sure what you are showing.

My best guess is you:

1) Typed \i my_databases
2) Hit the Tab key to complete the file listing.

Is this correct?
If not write out what you did.

I'm going to say it is an issue with whatever is being used to supply 
readline on the RPI.


Do you know what that is?

Or can you look at config.log where you ran ./configure to see what it 
found or did not finc?





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[David G. Johnston]

On Wed, Dec 22, 2021 at 1:54 PM Bryn Llewellyn  wrote:

>
> Is this model not used for the PostgreSQL system?
>

It is not.  Basically we have an "Proposed Patches" tracker and they either
get committed, or they do not.  Once committed, new features become
available at the next annual release.


> 1. I see that a schema variable will be another kind of object in the
> class that has tables, views, sequences, indexes, UDTs, procedures,
> functions, and so on. So it’s persisted within a database; it’s owned by a
> user; and it’s localized in a schema. Does it have its own namespace too?
> For example, can a table x, a function x(), and a schema variable x all
> live in the same schema s in some database. And can I use the normal
> qualified name syntax to access a schema variable?
>

I haven't read the patch in great detail (or recently...) but: The first
four items you listed (relations) share a namespace.  Procedures and
functions share a different namespace.  I'm presuming schema variables will
share the relations namespace.  These namespaces are subdivisions of
schemas.


> 2. It seems, then, that all the familiar notions, and especially the
> ability for a non-owner to access it apply. I see that the SELECT privilege
> governs who can read the value of a schema variable. But there seems to be
> no mechanism that governs who can change the value of a schema variable. It
> looks like only the owner can change the value—using the new LET SQL
> statement. Does this mean both that a top-level call from a client session
> that’s authorized as the owner can change it and a “security definer”
> subprogram with the same owner can change it—but that there is no explicit
> (say, WRITE) privilege for this. Is this what you intend? If so, why not
> have a WRITE privilege?
>

We'd probably call it INSERT and/or UPDATE privileges to avoid creating a
new enumeration value.

4. You said “security definer function”. Is this what you mean? Or do you
> mean ANY function or procedure as long as the current user (i.e. the owner
> of the most tightly enclosing security definer unit) is the schema
> variable’s owner?
>

https://www.postgresql.org/docs/current/sql-createfunction.html

If the invoker (current user) and the function owner are the same user the
specified mode is immaterial for purposes of that query.  Though in the
presence of views and triggers it can still get a bit confusing.

5. Could you please consider allowing a CONSTANT schema variable (with the
> usual syntax and requirement for an initialization expression)? One very
> popular use of a spec-level package variable is for a universal constant
> that has to be accessed in several places—like, for example, the conversion
> factor between some metric unit and some imperial unit.
>

Interesting.  Specifically, though, constant even for a superuser and the
owner?  Usually constant is enforced by just not allowing people to update.

Another way to think of the question, are these immutable or stable?


>
> 3. What is the precedence scheme? For example, if a SQL statement in a
> PL/pgSQL unit has a restriction like this:
>
>…where col = x…
>
> and x is both the name of an in-scope variable (or formal parameter) in
> the unit and the name of a schema variable?
>


> When the table has a column called x, then there’s (at least sometimes) no
> way round a run-time collision error except to rename one of the X’s.
> (Qualifying the names doesn’t fix it.)
>

Usually qualifying fixes it just fine - just some syntax elements presently
do not allow for a qualifier to be added preventing the option from being
used.


> Will it be the same with schema variables? The question extends to
> ordinary assignment statements that become SQL statements under the covers:
>
>   v := a + b;
>
> where b happens to be both an in-scope variable and a schema variable.
>

I'm light on specifics at the moment but this is already a solved problem
and schema variables should stick with the existing convention.  Which I
think is telling the user there is ambiguity and to add an appropriate
qualifier.

IIUC, a schema variable is slightly augmented shorthand for what today can
be done by writing a scalar subquery (i.e., schema.col == (SELECT tbl.vars
FROM schema.vars)) with the presumption that the table vars has exactly one
row.

David J.

Tab-completion error...?

[Theodore M Rolle, Jr.]

I'm having a problem with v14.1 on my Raspberry Pi 4[8Gb]
After a clean download and compile, when using psql, using tab completion
psql -U postgres
postgres=# \i my_databases\tpsql: symbol lookup error: psql: undefined
symbol: PQmblenBounded

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

[Bryn Llewellyn]

> pavel.steh...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>>> pavel.steh...@gmail.com wrote:
>>>  
 b...@yugabyte.com wrote:
 
 I’m still hoping that I might get some pointers to whitepapers or blog 
 posts that expand on those bullets that I quoted from the PG doc: «Instead 
 of packages, use schemas to organize your functions into groups.» and 
 «Since there are no packages, there are no package-level variables either. 
 This is somewhat annoying. You can keep per-session state in temporary 
 tables instead.»
>>> 
>>> I fixed a patch https://commitfest.postgresql.org/36/1608/ so you can check 
>>> it.
>>> 
>>> Using temporary tables instead of session variables is not too practical. 
>>> There are more alternative ways - a) one extension, b) using global 
>>> variables from Perl, c) using global configuration variables. The @c is 
>>> most common today
>>> 
>>> http://okbob.blogspot.com/2021/06/current_setting
>> 
>> Do you have a plain English account of what your patch will bring for the 
>> application programmer?
> 
> I wrote about it. Did you read this article?
> 
> https://okbob.blogspot.com/2018/02/schema-variables.html
> 
> The goals of this project:
>   • fast non transactional non persistent (session based) storage,
>   • possibility to control access to stored data with PostgreSQL 
> GRANT/REVOKE commands - schema variable can be filled by security definer 
> function, and anywhere in session can be read, but cannot be changed,
>   • possibility to share data between different PostgreSQL environments 
> (client side, server side, PL/Python, PL/Perl, ...)
>   • possibility to have an analogy of package variables for PLpgSQL,
>   • it should not block a possibility to check PLpgSQL code by 
> plpgsql_check.
>  
>> b...@yugabyte.com continued:
>> 
>> Your current_setting blog post shows me that you use this:
>> 
>> pts := current_setting('tps.ts', true)::timestamp with time zone;
>> 
>> Is that your point? I so, then thanks. Yes, I’ve used this technique myself.
> 
> The advantage of my implementation against GUC is: 
>   a) performance and correctness - session variables are typed and stored in 
> binary format, GUC is just text, data should be converted every time
>   b) possibility to set secure access,
>   c) persistence in schema (data are not persistent) 
>   d) more comfortable work - there is not necessary to use helper functions
> 
> The advantages/disadvantage against PL/SQL package variables is deeper 
> integration with SQL engine and generally missing schema private objects. But 
> this is not supported by Postgres yet, and this needs to be supported by 
> Postgres, if we want to use this feature from PL/pgSQL. 
> 
> There is an important difference between PL/SQL and PL/pgSQL. PL/SQL is an 
> independent environment with possibility to embedded SQL. PL/pgSQL is just 
> glue for SQL - any expression in PL/pgSQL is SQL expression and it is 
> evaluated by SQL engine. So everything supported in PL/pgSQL has to be 
> supported by SQL engine.

Yes, I did read your “schema variables” post on your site “Pavel Stehule’s blog 
— Some notes about PostgreSQL”. It gives me a very good idea about what you 
have in mind.

But as I’ve come to understand the term “Functional Spec”, this denotes a 
formal deliverable that a product development (sub)group owns and maintains 
collectively. Especially, it has a status which, eventually, is set to 
“Approved”. And apart from minor bug fixes, no code makes its way into a 
released version of the (sub)product in question unless the group has approved 
its Functional Spec.

Is this model not used for the PostgreSQL system?

Your blogpost and all the comments are dated Feb 2018—so almost four years ago 
now. What needs to happen for your project to be exposed in a PG Release? And 
which release might this be?

Back to the substance of your proposal, 

1. I see that a schema variable will be another kind of object in the class 
that has tables, views, sequences, indexes, UDTs, procedures, functions, and so 
on. So it’s persisted within a database; it’s owned by a user; and it’s 
localized in a schema. Does it have its own namespace too? For example, can a 
table x, a function x(), and a schema variable x all live in the same schema s 
in some database. And can I use the normal qualified name syntax to access a 
schema variable?

2. It seems, then, that all the familiar notions, and especially the ability 
for a non-owner to access it apply. I see that the SELECT privilege governs who 
can read the value of a schema variable. But there seems to be no mechanism 
that governs who can change the value of a schema variable. It looks like only 
the owner can change the value—using the new LET SQL statement. Does this mean 
both that a top-level call from a client session that’s authorized as the owner 
can change it and a “security definer” subprogram with the same owner can 
change it—but that there 

Re: surprisingly slow creation of gist index used in exclude constraint

[Laurenz Albe]

On Wed, 2021-12-22 at 09:58 +, Chris Withers wrote:
> A year and a half later, now that version 14 is the latest available,
> I wonder if anything has changed with respect to gist index creation?
> Probably also worth asking: are there now different index types this
> application should be using?
>
> > I'm upgrading a database from 9.4 to 11.5 by dumping from the old
> > cluster and loading into the new cluster.
> > The database is tiny: around 2.3G, but importing this table is proving 
> > problematic:
> >
> > Column  |   Type|Modifiers
> > +---+--
> >  period | tsrange   | not null
> >  col1   | character varying | not null
> >  col2   | character varying | not null
> >  col3   | integer   | not null
> >  col4   | character varying | not null default ''::character varying
> >  id | integer   | not null default 
> > nextval('mkt_profile_id_seq'::regclass)
> >  deleted| boolean   | not null default false
> >  managed| boolean   | not null default false
> >  col5   | character varying |
> > Indexes:
> > "mkt_profile_pkey" PRIMARY KEY, btree (id)
> > "mkt_profile_period_col1_col4_col2_chan_excl" EXCLUDE USING gist 
> > (period WITH &&, col1 WITH =, col4 WITH =, col2 WITH =, col3 WITH =)
> > Check constraints:
> > "mkt_profile_period_check" CHECK (period <> 'empty'::tsrange)
> > Foreign-key constraints:
> > "mkt_profile_col1_fkey" FOREIGN KEY (col1) REFERENCES host(name)
> >
> > It has 4.1 million rows in it and while importing the data only takes a 
> > couple of minutes,
> > when I did a test load into the new cluster, building the 
> > mkt_profile_period_col1_col4_col2_chan_excl
> > index for the exclude constraint took 15 hours.

You could use "pg_upgrade" for upgrading, that will be much faster.

Creating GiST indexes is still slow.  You could test if any of the improvements
since v11 have made your case faster.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Postgresql crashing during pg_dump

[Ron]

On 12/22/21 8:40 AM, Tom Lane wrote:

Paulo Silva  writes:

I have a huge table with 141456059 records on a PostgreSQL 10.18 database.
When I try to do a pg_dump on that table, postgresql gives a segfault,
displaying this message:
2021-12-22 14:08:03.437 UTC [15267] LOG:  server process (PID 25854) was
terminated by signal 11: Segmentation fault

What this sounds like is corrupt data somewhere in that table.

There's some advice about dealing with such cases here:

https://wiki.postgresql.org/wiki/Corruption

If this is extremely valuable data, you might prefer to hire somebody
who specializes in data recovery, rather than trying to handle it
yourself.  I'd still follow the wiki page's "first response" advice,
ie take a physical backup ASAP.


COPY the table in PK ranges to narrow down the offending record?

--
Angular momentum makes the world go 'round.

Re: Postgresql crashing during pg_dump

[Tom Lane]

Paulo Silva  writes:
> I have a huge table with 141456059 records on a PostgreSQL 10.18 database.
> When I try to do a pg_dump on that table, postgresql gives a segfault,
> displaying this message:
> 2021-12-22 14:08:03.437 UTC [15267] LOG:  server process (PID 25854) was
> terminated by signal 11: Segmentation fault

What this sounds like is corrupt data somewhere in that table.

There's some advice about dealing with such cases here:

https://wiki.postgresql.org/wiki/Corruption

If this is extremely valuable data, you might prefer to hire somebody
who specializes in data recovery, rather than trying to handle it
yourself.  I'd still follow the wiki page's "first response" advice,
ie take a physical backup ASAP.

regards, tom lane

Postgresql crashing during pg_dump

[Paulo Silva]

Hello,

I have a huge table with 141456059 records on a PostgreSQL 10.18 database.

When I try to do a pg_dump on that table, postgresql gives a segfault,
displaying this message:

2021-12-22 14:08:03.437 UTC [15267] LOG:  server process (PID 25854) was
terminated by signal 11: Segmentation fault
2021-12-22 14:08:03.437 UTC [15267] DETAIL:  Failed process was running:
COPY ** TO stdout;
2021-12-22 14:08:03.437 UTC [15267] LOG:  terminating any other active
server processes
2021-12-22 14:08:03.438 UTC [15267] LOG:  archiver process (PID 16034)
exited with exit code 2
2021-12-22 14:08:04.196 UTC [15267] LOG:  all server processes terminated;
reinitializing
2021-12-22 14:08:05.785 UTC [25867] LOG:  database system was interrupted
while in recovery at log time 2021-12-22 14:02:29 UTC
2021-12-22 14:08:05.785 UTC [25867] HINT:  If this has occurred more than
once some data might be corrupted and you might need to choose an earlier
recovery target.

On the linux log I only see this:

Dec 22 14:08:03 kernel: postmaster[25854]: segfault at 14be000 ip
7f828fabb5f9 sp 7fffe43538b8 error 6 in libc-2.17.so
[7f828f96d000+1c2000]

I'm guessing I'm hitting some (memory?) limit, is there anything I can do
to prevent this? Shouldn't  PostgreSQL have some different behavior instead
of crashing the server?
-- 
Paulo Silva 

Re: surprisingly slow creation of gist index used in exclude constraint

[Chris Withers]

  
  
Hi,
A year and a half later, now that version 14 is the latest
  available, I wonder if anything has changed with respect to gist
  index creation?
Probably also worth asking: are there now different index types
  this application should be using?
cheers,
Chris

On 14/05/2020 21:11, Chris Withers
  wrote:


  
  Hi,
  I'm upgrading a database from 9.4 to 11.5 by dumping from the
old cluster and loading into the new cluster.
  The database is tiny: around 2.3G, but importing this table is
proving problematic:
  Column  |   Type|Modifiers
+---+--
 period | tsrange   | not null
 col1   | character varying | not null
 col2   | character varying | not null
 col3   | integer   | not null
 col4   | character varying | not null default ''::character varying
 id | integer   | not null default nextval('mkt_profile_id_seq'::regclass)
 deleted| boolean   | not null default false
 managed| boolean   | not null default false
 col5   | character varying |
Indexes:
"mkt_profile_pkey" PRIMARY KEY, btree (id)
"mkt_profile_period_col1_col4_col2_chan_excl" EXCLUDE USING gist (period WITH &&, col1 WITH =, col4 WITH =, col2 WITH =, col3 WITH =)
Check constraints:
"mkt_profile_period_check" CHECK (period <> 'empty'::tsrange)
Foreign-key constraints:
"mkt_profile_col1_fkey" FOREIGN KEY (col1) REFERENCES host(name)
  It has 4.1 million rows in it and while importing the data only
takes a couple of minutes, when I did a test load into the new
cluster, building the
mkt_profile_period_col1_col4_col2_chan_excl index for the
exclude constraint took 15 hours.
  I feel like asking what I'm doing wrong here? The new server is
pretty decent hardware...
  Concrete questions:
  - what, if anything, am I getting badly wrong here?
  - what can I do to speed up creation of this index?
  - failing that, what can I do to import and then create the
index in the background? 
  
  As you can imagine, a 15hr outage for an upgrade has not met
with large amounts of happiness from the people whose
application it is ;-)
  Chris