Re: Feature request: psql --idle

[Wiwwo Staff]

Thanks all for the alternative solutions.
Yet, despite of my (wrong and misleading) use-case, I still share Julien's
view of this being useful, whatever the parameter or the use-case.

My 2 cents :-)


On Wed, Jul 27, 2022 at 2:49 PM Wiwwo Staff  wrote:

> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.
>
> Still, those users need some login shell.
>
> It would be great to give them psql as a login shell (in /etc/passwd).
> But doing so, will result in psql exiting with error with the usual
> $ psql
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
> No such file or directory
> Is the server running locally and accepting connections on that socket?
>
> What would help, is a --idle option, where psql does not exit, stays idle
> and waits for user to give a \conn command.
> Something similar to
> sqlplus /nolog
>
> Is anything like that feasible or is there another solution/workaround?
>
> Tnx!
>

Feature request(?): Proxy User

[Wiwwo Staff]

Sorry to post solutions and links about alternative DBMSs, but I miss this
sort of "sudo" ad database user level:
https://oracle-base.com/articles/misc/proxy-users-and-connect-through

Is there any approach to achieve the same result in Postgresql?
Anyone sharing the need of this functionality?

Tnx!

xmin of slot is not moving | hot standby feedback sending old xmin.

[T T]

Hi Team,

*Issues :-*  xmin of physical replication slot is not moving/hung, slave is
refering/stuck on old xmin. which causes it to send old xmin to primary
when hot_standby_feedback is on.

*PG Version* :- 10.12

*Slot Details : - *

postgres=# select * from pg_replication_slots ;
   slot_name   | plugin | slot_type | datoid | database | temporary |
active | active_pid |   xmin| catalog_xmin | restart_lsn  |
confirmed_flush_lsn
---++---++--+---+++---+--+--+-
 repmgr_slot_3 || physical  ||  | f |
t  |   4841 | *193762035* |  | 560/96081368 |
 repmgr_slot_1 || physical  ||  | f |
t  |  18787 | 200856612 |  | 560/96081368 |
 repmgr_slot_4 || physical  ||  | f |
t  |  22825 | 200856607 |  | 560/96081368 |
(3 rows)


*Observation :- *

 1. message in DB logs.

2022-07-28 08:51:43 UTC [32119]: [2-1] user=,db=,app=,client=DEBUG:
sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:51:46 UTC [32119]: [61-1] user=,db=,app=,client=DEBUG:
sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:51:56 UTC [32119]: [292-1] user=,db=,app=,client=DEBUG:
sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:06 UTC [32119]: [583-1] user=,db=,app=,client=DEBUG:
sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:16 UTC [32119]: [824-1] user=,db=,app=,client=DEBUG:
sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:26 UTC [32119]: [1056-1] user=,db=,app=,client=DEBUG:
 sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:36 UTC [32119]: [1347-1] user=,db=,app=,client=DEBUG:
 sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:46 UTC [32119]: [1621-1] user=,db=,app=,client=DEBUG:
 sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:52:56 UTC [32119]: [1848-1] user=,db=,app=,client=DEBUG:
 sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0
2022-07-28 08:53:06 UTC [32119]: [2161-1] user=,db=,app=,client=DEBUG:
 sending hot standby feedback xmin 193762035 epoch 0 catalog_xmin 0
catalog_xmin_epoch 0


*Tried troubleshooting :-*

1. Checked for a long running query on primary and slave and found no SQL
present.
2. Turn off hot_standby_feedback for sometime.Post turning on
hot_standby_feedback
the same xmin is assigned to the slot.
3. Killed wal receiver process.
4. Restarted slave/standby databases still hot standby feedback
sending/refering old xmin.

Can you please help us to understand why this is happening, Is this a bug ?

Also is there any solution other than recreating the slot or turning off
the hot_standby_feedback to fix this?

-
Thanks & Regards,

Tushar K Takate .
Mob-No : +91-860-030-2404
LinkedIn : Tushar Takate 

pg_dump query failed

[karol . malinowski]

Hi all, 

last week backup of my one database stops with error like below: 


"pg_dump: [archiver (db)] query failed: server closed the connection
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: [archiver (db)] query was: SELECT l.oid, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner)))
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner)))
AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner)))
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner)))
AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, NULL AS
initlomacl, NULL AS initrlomacl FROM pg_largeobject_metadata l LEFT JOIN
pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid =
'pg_largeobject'::regclass AND pip.objsubid = 0) " 

Additional info: 


PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit 

database size 220GB 

System CentOS 7 


I am not sure how to recognize the problem.

--
Karol Malinowski
Operation Manager
22-118-90-30 | karol.malinow...@trustedeyes.pl

Trustedeyes
ul. Fasolowa 13A, lok.7, Warszawa 02-482 | https://trustedeyes.pl

Re: pg_dump query failed

[David G. Johnston]

On Thursday, July 28, 2022,  wrote:

> Additional info:
>
> PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-16), 64-bit
>
> database size 220GB
>
> System CentOS 7
>
> I am not sure how to recognize the problem.
>

Does the server log have more info?

Can you reproduce if you run that query manually?

David J.

Re: Feature request(?): Proxy User

[Mateusz Henicz]

Hi,
Did you check "SET SESSION AUTHORIZATION" or "SET ROLE" commands?
I think that is what you are looking for.

Cheers,
Mateusz

czw., 28 lip 2022 o 11:29 Wiwwo Staff  napisał(a):

> Sorry to post solutions and links about alternative DBMSs, but I miss this
> sort of "sudo" ad database user level:
> https://oracle-base.com/articles/misc/proxy-users-and-connect-through
>
> Is there any approach to achieve the same result in Postgresql?
> Anyone sharing the need of this functionality?
>
> Tnx!
>
>

Re: pg_dump query failed

[Ron]

On 7/28/22 05:08, karol.malinow...@trustedeyes.pl wrote:


Hi all,

last week backup of my one database stops with error like below:

"pg_dump: [archiver (db)] query failed: server closed the connection 
unexpectedly

This probably means the server terminated abnormally
before or while processing the request.
pg_dump: [archiver (db)] query was: SELECT l.oid, (SELECT rolname FROM 
pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, NULL AS 
initlomacl, NULL AS initrlomacl FROM pg_largeobject_metadata l LEFT JOIN 
pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 
'pg_largeobject'::regclass AND pip.objsubid = 0) "



Additional info:

PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-16), 64-bit




You will of course patch that to 9.6.24, right?


database size 220GB

System CentOS 7

I am not sure how to recognize the problem.



I got these when there's a network hiccup while dumping a remote database.

--
Angular momentum makes the world go 'round.

Re: pg_dump query failed

[Tom Lane]

karol.malinow...@trustedeyes.pl writes:
> last week backup of my one database stops with error like below: 

> "pg_dump: [archiver (db)] query failed: server closed the connection
> unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.

> Additional info: 
> PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-16), 64-bit 

The first thing I'd suggest is updating to the latest release
in that branch (9.6.24).  You're missing four years' worth of
bug fixes, and it's not improbable that the underlying issue
here is included in that.

If that doesn't help, you'll need to start debugging.  The
symptom looks a bit like catalog corruption; if you are lucky
then REINDEX'ing the catalogs used in that query would help.

regards, tom lane

Re: Feature request(?): Proxy User

[Alicja Kucharczyk]

czw., 28 lip 2022 o 14:18 Mateusz Henicz 
napisał(a):

> Hi,
> Did you check "SET SESSION AUTHORIZATION" or "SET ROLE" commands?
> I think that is what you are looking for.
>
> Cheers,
> Mateusz
>
> czw., 28 lip 2022 o 11:29 Wiwwo Staff  napisał(a):
>
>> Sorry to post solutions and links about alternative DBMSs, but I miss
>> this sort of "sudo" ad database user level:
>> https://oracle-base.com/articles/misc/proxy-users-and-connect-through
>>
>> Is there any approach to achieve the same result in Postgresql?
>> Anyone sharing the need of this functionality?
>>
>> Tnx!
>>
>>
Can you elaborate what exact problem are you trying to solve here?

 looking at the article you sent:
"Some DBA tasks, like creating private database links or setting up jobs
using the DBMS_JOB package, require the administrator to log in as a
specific user. This can present a problem if the administrator doesn't know
the password."

probably as Mateusz mentioned SET ROLE is the solution, if you are looking
for something more sophisticated you might want to look what INHERIT
attribute means:
https://www.postgresql.org/docs/current/role-membership.html or look at
functions with security definer:
https://www.postgresql.org/docs/current/sql-createfunction.html

"You have multiple developers working in a shared schema. Letting multiple
people share the same credentials represents a security risk. Instead you
create a separate proxy user for each individual, allowing them to connect
to the schema owner with their own credentials. If a user leaves a project,
you simply lock or drop their user, and they no longer have access to the
shared schema."

It's totally not needed in Postgres as the architecture and the concept of
schema and users is different and you don't have any credentials to the
schema, which is just a namespace in postgres

Re: Was my question inappropriate for postgres?

[Peter J. Holzer]

On 2022-07-24 20:27:56 -0400, Mladen Gogala wrote:
> On 7/24/22 19:56, Taka Taka wrote:
> I would like to know if psqlodbc_13_02 is compatible with M365.
> Also, could you please tell me which of the psqlodbc_13_02 would be
> suitable to Windows 10 64-bit?
> 
> What is M365? Is it a part of Microsoft Office 365, aka "O365"? Is it related
> to M-16? What di you mean by "driver suitable for Windows 10"? If the driver
> can be installed and configured by the MS ODBC driver administrator, then I
> guess it's suitable.

Not at all. There are 32 bit and 64 bit variants. You can install and
configure both on a 64 bit Windows, but you can only use the one which
matches the architecture of your application. I am guessing that M365 is
now 64 bit only, but I've certainly seen 32 bit MS Office installed on
64 bit MS Windows, and the confusion that caused.

Also there is a Unicode and an "ANSI" variant of the PostgreSQL ODBC
driver. My advice has always been to use the Unicode variant, but I
assume that the ANSI variant still exists because some (legacy) Windows
applications can't deal with Unicode.

So while I'm fairly confident that the answer to Taka's answer is "64
bit, Unicode", I see that this might not be so obvious to them.


> Also, I find variety in the odbc driver.
> 
> I don't. ODBC drivers implement the same protocol when communicating with the
> database and they all work the same.

Actually, no: The protocol between the application and the driver is the
same (for a given platform and ODBC version), but the protoocl between
the driver and the database is different:

> Here is what I have:
> 
> [mgogala@umajor ~]$ rpm -qa *odbc*
> oracle-instantclient-odbc-21.5.0.0.0-1.x86_64
> postgresql-odbc-13.01.-2.fc36.x86_64
> msodbcsql17-17.10.1.1-1.x86_64

That's why you have three different drivers. You can't use the oracle
driver to connect to a postgresql database. You may not even be able to
use an oracle driver to connect to an oracle database that's too old or
too new (or you may be able to connect and then get weird errors -
BTDT). PostgreSQL is in my experience rather tolerant of client/server
mismatches, but I wouldn't be surprised if some stuff wouldn't work if
the versions are too different. Also, an application written for ODBC
4.0 might not work with a driver implementing ODBC 3.8.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Syntax error when combining --set and --command has me stumped

[Ron]

$ alias psql12
alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'

This works ask expected:

$ psql12 --set num=42 -ac "\echo :num"
echo :num
42

And so does this:

$ psql12 --set num=42
psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1))
Type "help" for help.

postgres=# select :num;
 ?column?
--
   42
(1 row)


But trying to use a variable (both with and without single quotes) in a 
--command statement other than "\echo" throws a syntax error at the colon:


$ psql12 --set num=42 -ac "select :num;"
select :num;
ERROR:  syntax error at or near ":"
LINE 1: select :num;
   ^
$ psql12 --set num=42 -ac "select :'num';"
select :'num';
ERROR:  syntax error at or near ":"
LINE 1: select :'num';
   ^

What secret sauce am I missing to get this to work?

--
Angular momentum makes the world go 'round.

Re: Syntax error when combining --set and --command has me stumped

[David G. Johnston]

On Thu, Jul 28, 2022 at 12:40 PM Ron  wrote:

> What secret sauce am I missing to get this to work?
>

Given that the documentation says:

"command must be either a command string that is completely parsable by the
server (i.e., it contains no psql-specific features), or a single backslash
command."

I don't see how you can do anything to make that work.

David J.

Re: Syntax error when combining --set and --command has me stumped

[Ron]

On 7/28/22 14:47, David G. Johnston wrote:

On Thu, Jul 28, 2022 at 12:40 PM Ron  wrote:

What secret sauce am I missing to get this to work?


Given that the documentation says:

"command must be either a command string that is completely parsable by 
the server (i.e., it contains no psql-specific features), or a single 
backslash command."


And all this time, I was looking in the |--set=/|assignment|/| section of 
the psql doc page...


https://www.postgresql.org/docs/12/app-psql.html



I don't see how you can do anything to make that work.

David J.



--
Angular momentum makes the world go 'round.

Re: Syntax error when combining --set and --command has me stumped

[Adrian Klaver]

On 7/28/22 12:40, Ron wrote:


$ alias psql12
alias psql12='/usr/lib/postgresql/12/bin/psql -p5433'

This works ask expected:

$ psql12 --set num=42 -ac "\echo :num"
echo :num
42

And so does this:

$ psql12 --set num=42
psql (12.11 (Ubuntu 12.11-1.pgdg18.04+1))
Type "help" for help.

postgres=# select :num;
  ?column?
--
    42
(1 row)


But trying to use a variable (both with and without single quotes) in a 
--command statement other than "\echo" throws a syntax error at the colon:


$ psql12 --set num=42 -ac "select :num;"
select :num;
ERROR:  syntax error at or near ":"
LINE 1: select :num;
    ^
$ psql12 --set num=42 -ac "select :'num';"
select :'num';
ERROR:  syntax error at or near ":"
LINE 1: select :'num';
    ^

What secret sauce am I missing to get this to work?



From here:

https://www.postgresql.org/docs/current/app-psql.html

-c command

...

Because of this behavior, putting more than one SQL command in a single 
-c string often has unexpected results. It's better to use repeated -c 
commands or feed multiple commands to psql's standard input, either 
using echo as illustrated above, or via a shell here-document, for example:


psql < \set num 42
> SELECT :num;
> EOF
Null display is "NULL".
 ?column?
--
   42
(1 row)




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

« The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Bryn Llewellyn]

The subject line is copied from "PL/pgSQL under the Hood" 
(https://www.postgresql.org/docs/current/plpgsql-implementation.html). It 
implies the question:

« What does the term "parse" mean? »

I couldn't find more than what I quoted. Have I missed something?

Anyway, I tried some tests. Here's an example that aims to make a reasonable 
compromise between brevity and its capacity to illustrate. First, I create a 
domain and then leave it unchanged:

create domain tt as text[];

Then I do this:

create or replace function f()
  returns text
  language plpgsql
as $body$
declare
  n int;
  arr tt := array['dog', 'cat'];
begin
  n := (select count(*) from (select unnest(art)) as a);
  return n::text;
end;
$body$;

\sf+ f()
select f();

The "create or replace" completes without error and the "select" runs to 
produce the result, 2, that I expect.

If I simulate a typo by changing "n" on the LHS of the assignments to "m", then 
I get this error at "create or replace" time:

"m" is not a known variable

Moreover, "\sf+" shows that the former definition has remained intact—as I've 
come to expect.

If I fix the "n" typo and simulate a second typo by changing "tt" in the 
declaration of "arr" to "tz", then I get this error at "create or replace" time:

type "tz" does not exist

If I fix the "tz" typo and simulate a third typo by changing "arr" in the 
scalar subquery expression to "art", then "create or replace" completes without 
error and "\sf+" confirms that the new source is in place. Then, at "select" 
time, I get this error:

column "art" does not exist

So far, I'm tempted to think that "parse" covers everything about "regular" 
(i.e. not embedded SQL) PL/pgSQL statements, including syntactic analysis *and* 
the resolution of identifiers—both within the scope of the to-be-created 
subprogram and within schema scopes. 

But, as it seems, embedded SQL statements receive only syntactic 
analysis—leaving the resolution of identifiers (even when this can be done in 
the scope of the to-be-created subprogram) to runtime. (I tried changing "from" 
to "frim" and that caused a syntax error.)

Then I dropped "f()" and extended the test, thus:

create or replace function f()
  returns table(z text)
  language plpgsql
as $body$
declare
  v_sqlstate text not null := '';
  v_message  text not null := '';
  n int;
  arr tt := array['dog', 'cat'];
begin
  z := (select count(*) from (select unnest(arr)) as a)::text; return next;
exception when others then
  get stacked diagnostics
  v_sqlstate = returned_sqlstate,
  v_message  = message_text;

  z := ''; return next;
  z := v_sqlstate; return next;
  z := v_message;  return next;
end;
$body$;

\sf+ f()
select f();

"create or replace" succeeds and "select" reports what I expect: 2. Now if I 
change "arr" to "art", I get the error report from my "others" handler that I 
expect:

 42703
 column "art" does not exist

If I fix "art" back to "arr" and change "v_message" in "z := v_message;  return 
next;" to "q_message", then "create or replace" succeeds—very much to my 
surprise. Moreover; "select" succeeds too—presumably because the point of 
execution never enters the "others" handler. Only if (with the "q_message"" 
typo still in place) I change "arr" to "art" again, do I get this error on 
"select":

column "q_message" does not exist

Is this expected? In other words, is there a careful explanation of what 
"parse" means in the context of "create or replace" for a subprogram that 
predicts all of the outcomes that I reported here?

Or might my final observation be considered to be a bug—and if so, might it be 
fixed?

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Christophe Pettus]

> On Jul 28, 2022, at 18:04, Bryn Llewellyn  wrote:
> Is this expected?

Yes.  This isn't a bug.

> In other words, is there a careful explanation of what "parse" means in the 
> context of "create or replace" for a subprogram that predicts all of the 
> outcomes that I reported here?


Database objects (such as tables and columns) are left as identifiers until 
they are executed, because that is the point at which a plan for those 
statements is created.  The other components of PL/pgSQL are translated to 
internal form (and thus checked for existence) as compile time.

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Bryn Llewellyn]

> x...@thebuild.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Is this expected?
> 
> Yes.  This isn't a bug… Database objects (such as tables and columns) are 
> left as identifiers until they are executed, because that is the point at 
> which a plan for those statements is created.  The other components of 
> PL/pgSQL are translated to internal form (and thus checked for existence) at 
> compile time.

My example was carefully contrived to test what you said—which is what I had 
earlier understood. My deliberate typo thus:

>> change "v_message" in "z := v_message;  return next;" to "q_message"

(surely) has nothing to do with possible database objects. The context is a 
straight PL/pgSQL assignment statement (with no scalar subquery in sight).

It's this that surprises me. And it's this, and only this, that I'm asking 
about: might _just_ this be a fixable bug?

Re: Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Tom Lane]

Christophe Pettus  writes:
>> On Jul 28, 2022, at 18:04, Bryn Llewellyn  wrote:
>> Is this expected?

> Yes.  This isn't a bug.

It's actually a feature: there are cases where it's useful that we
don't do semantics checking on statements until they are reached.
A trivial example is

begin
  create table foo(...);
  insert into foo values(...);
end;

which would never work at all if we insisted on semantic validity
of the INSERT before the CREATE is executed.

Having said that, there are certainly aspects of what happens when
in plpgsql that don't have a lot of justification other than being
implementation artifacts.  For instance, things that are certainly
plpgsql variable names (e.g. the lefthand side of an assignment)
are checked sooner than things that might not be (e.g. the righthand
side).  That's defensible on a couple of grounds but it undoubtedly
leads to surprising results if you expect error conditions to be
recognized left-to-right or anything like that.

Of course, SQL commands themselves have to be analyzed in not
particularly left-to-right order, and we don't get that many
complaints about that.

regards, tom lane

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Christophe Pettus]

> On Jul 28, 2022, at 18:49, Bryn Llewellyn  wrote:
> It's this that surprises me. And it's this, and only this, that I'm asking 
> about: might _just_ this be a fixable bug?

It might be surprising, but it's not a bug.  You can demonstrate it with a very 
small test case:

CREATE FUNCTION f() RETURNS VOID AS $$
DECLARE
   x int not null := 0;
BEGIN
   x := y;
END;
$$ language plpgsql;

But gets an error on execution:

xof=# SELECT f();
ERROR:  column "y" does not exist
LINE 1: x := y
 ^
QUERY:  x := y
CONTEXT:  PL/pgSQL function f() line 5 at assignment

The clue is that it is complaining about a missing "column."  Assignment in 
PL/pgSQL is essentially syntactic sugar around a SELECT ... INTO.  The 
assignment there is processed pretty much as if it were written:

SELECT y INTO x;

Note, however, that this does *not* compile:

CREATE OR REPLACE FUNCTION f() RETURNS VOID AS $$
DECLARE
   x int not null := 0;
BEGIN
   y := x;
END;
$$ language plpgsql;

ERROR:  "y" is not a known variable
LINE 5:y := x;

Unquestionably, this is surprising!  The reasons, such as they are, are based 
in how PL/pgSQL processes SQL statements.  (For example, if you look at the 
grammar, it literally takes "SELECT x INTO y;" turns it into "SELECT x   
;", and passes that to the SPI.  This has the virtue that it doesn't have to 
have a complete PostgreSQL SQL grammar replicated in it (what a nightmare), but 
it does result in some of the implementation poking through.

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> x...@thebuild.com wrote:
>> 
>> This isn't a bug.
> 
> It's actually a feature…
> 
> Having said that, there are certainly aspects of what happens when in plpgsql 
> that don't have a lot of justification other than being implementation 
> artifacts…

Thanks, Tom. I'll take your « aspects of… plpgsql [are simply] implementation 
artifacts » to mean that my hope to understand what is checked at "create or 
replace " time and what is checked first at runtime is futile.

There does seem to be a general rule. But, as my example shows, there are 
exceptions to the rule. And it's impossible to make a simple user-facing 
statement of what determines "exceptional" status.

I suppose that the conclusion is clear: you can't be sure that a subprogram is 
good until every single code path (in the basic block coverage sense of this) 
has been tested. But, anyway, it was ever thus. (Error-free compilation never 
did guarantee error-free runtime outcomes.)

I'll call this "case closed" then.

Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

[Pavel Stehule]

pá 29. 7. 2022 v 4:57 odesílatel Bryn Llewellyn  napsal:

>
> *t...@sss.pgh.pa.us  wrote:*
>
> x...@thebuild.com wrote:
>
> This isn't a bug.
>
>
> It's actually a feature…
>
> Having said that, there are certainly aspects of what happens when in
> plpgsql that don't have a lot of justification other than
> being implementation artifacts…
>
>
> Thanks, Tom. I'll take your « aspects of… plpgsql [are simply]
> implementation artifacts » to mean that my hope to understand what is
> checked at "create or replace " time and what is checked
> first at runtime is futile.
>
> There does seem to be a general rule. But, as my example shows, there are
> exceptions to the rule. And it's impossible to make a simple user-facing
> statement of what determines "exceptional" status.
>
> I suppose that the conclusion is clear: you can't be sure that a
> subprogram is good until every single code path (in the basic block
> coverage sense of this) has been tested. But, anyway, it was ever thus.
> (Error-free compilation never did guarantee error-free runtime outcomes.)
>

plpgsql_check https://github.com/okbob/plpgsql_check can help with it. It
does full static (without execution) analyze

Regards

Pavel



> I'll call this "case closed" then.
>