Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> It seems that there must be different underlying mechanisms at work and that 
>> this explains why creating a cursor using SQL to execute a prepared 
>> statement fails but doing this using PL/pgSQL succeeds. What's going on 
>> under the covers?
> 
> Pretty sure:
> 
> www.postgresql.org/docs/current/spi.html

I think that I see what you mean, Adrian. I had read the "PL/pgSQL Under the 
Hood" section to mean that, at run time, ordinary SQL calls were invariably 
made whenever the point of execution reached anything that implied SQL 
functionality (including, famously, expression evaluation). I'd assumed, 
therefore, that when the PL/pgSQL has an "open" statement, and when this is 
encountered at run time, the ordinary SQL "declare" statement was invoked.

But it seems, rather, that the SQL "declare" and the PL/pgSQL "open" each as 
its own implementation by lower-level prinitives—and that these differ in their 
details and in their power of expression. That would explain why the 
"pg_cursors.statement" text differs for cursors with identical properties (like 
scrollability), and the identically spelled subquery, like I showed in my 
earlier email.

It seems odd that the two approaches each has its own  limitation(s).

— You can't create a cursor to execute a prepared statement using the SQL API; 
and you can't create a holdable cursor using the (static) PL/pgSQL API but can 
work around this with dynamic SQL.

—You have to dive down to a lower-level API like "Embedded SQL in C" (a.k.a. 
ECPG) to create a holdable cursor to execute a prepared statement.

But I appreciate that this comes with the territory and that anyone who feels 
strongly about this, and who knows how to do it, can develop their own patch 
and submit it for consideration.

Re: Call a Normal function inside a Trigger Function

[FOUTE K . Jaurès]

Thanks @Adrian Klaver 
It's clear for me now.

On Sun, 16 Apr 2023, 20:13 Adrian Klaver,  wrote:

> On 4/16/23 11:47, FOUTE K. Jaurès wrote:
> > Can I have an example please? Or a link
>
> create table trg_test (id integer, fld_1 varchar, fld_2 boolean);
>
>
> CREATE OR REPLACE FUNCTION public.child_fnc(token character varying)
>   RETURNS void
>   LANGUAGE plpgsql
> AS $function$
>  BEGIN
>  IF token = 'yes' THEN
>  RAISE NOTICE 'Child';
>  END IF;
>  END;
> $function$
> ;
>
>
> CREATE OR REPLACE FUNCTION public.parent_fnc()
>   RETURNS trigger
>   LANGUAGE plpgsql
> AS $function$
>  BEGIN
>  RAISE NOTICE 'Id is %', NEW.id;
>  RAISE NOTICE 'Fld_1 is %', NEW.fld_1;
>  RAISE NOTICE 'Parent';
>  PERFORM child_fnc('yes');
>  RETURN NEW;
>  END;
> $function$
> ;
>
> create trigger test_trg before insert on trg_test for each row execute
> function parent_fnc();
>
> insert into trg_test values (1, 'dog', 'f');
> NOTICE:  Id is 1
> NOTICE:  Fld_1 is dog
> NOTICE:  Parent
> NOTICE:  Child
> INSERT 0 1
>
> >
> > On Sun, 16 Apr 2023, 17:08 Pavel Stehule,  > > wrote:
> >
> > Hi
> >
> >
> > ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès
> > mailto:jauresfo...@gmail.com>> napsal:
> >
> > Hello,
> >
> > Is it possible to call a function inside a trigger function ?
> > Any idea or link are welcome. Thanks in advance
> >
> >
> > sure, there is not any limit.
> >
> > Regards
> >
> > Pavel
> >
> >
> > Best Regards
> > --
> > Jaurès FOUTE
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Call a Normal function inside a Trigger Function

[Pavel Stehule]

Hi


ne 16. 4. 2023 v 20:47 odesílatel FOUTE K. Jaurès 
napsal:

> Can I have an example please? Or a link
>
> On Sun, 16 Apr 2023, 17:08 Pavel Stehule,  wrote:
>
>> Hi
>>
>>
>> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès 
>> napsal:
>>
>>> Hello,
>>>
>>> Is it possible to call a function inside a trigger function ?
>>> Any idea or link are welcome. Thanks in advance
>>>
>>
>> sure, there is not any limit.
>>
>
CREATE OR REPLACE FUNCTION allow_update(d date)
RETURNS bool AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM d) = EXTRACT(YEAR FROM current_date);
END;
$$ LANGUAGE plpgsql;

-- allow update record only from current year
CREATE OR REPLACE FUNCTION trg_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
IF NOT allow_update(NEW.inserted) THEN
  RAISE EXCEPTION 'cannot insert';
 END IF;
ELSE IF TG_OP = 'UPDATE' THEN
 IF NOT allow_update(NEW.inserted) OR NOT allow_update(OLD.inserted)
THEN
   RAISE EXCEPTION 'cannot update';
 END IF;
   ELSE
 IF NOT allow_update(OLD.inserted) THEN
  RAISE EXCEPTION 'cannot delete';
 END IF;
   END IF;
   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON sometab
FOR EACH ROW EXECUTE FUNCTION trg_func();

Regards

Pavel


p.s. You can do everything in trigger - Postgres is not Oracle where there
were some issues (if my memory serves well). There is only one risk -
possible recursion



>> Regards
>>
>> Pavel
>>
>>
>>>
>>> Best Regards
>>> --
>>> Jaurès FOUTE
>>>
>>

Re: Call a Normal function inside a Trigger Function

[Adrian Klaver]

On 4/16/23 11:47, FOUTE K. Jaurès wrote:

Can I have an example please? Or a link


create table trg_test (id integer, fld_1 varchar, fld_2 boolean);


CREATE OR REPLACE FUNCTION public.child_fnc(token character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
IF token = 'yes' THEN
RAISE NOTICE 'Child';
END IF;
END;
$function$
;


CREATE OR REPLACE FUNCTION public.parent_fnc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'Id is %', NEW.id;
RAISE NOTICE 'Fld_1 is %', NEW.fld_1;
RAISE NOTICE 'Parent';
PERFORM child_fnc('yes');
RETURN NEW;
END;
$function$
;

create trigger test_trg before insert on trg_test for each row execute 
function parent_fnc();


insert into trg_test values (1, 'dog', 'f');
NOTICE:  Id is 1
NOTICE:  Fld_1 is dog
NOTICE:  Parent
NOTICE:  Child
INSERT 0 1



On Sun, 16 Apr 2023, 17:08 Pavel Stehule, > wrote:


Hi


ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès
mailto:jauresfo...@gmail.com>> napsal:

Hello,

Is it possible to call a function inside a trigger function ?
Any idea or link are welcome. Thanks in advance


sure, there is not any limit.

Regards

Pavel


Best Regards
-- 
Jaurès FOUTE




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

Re: Call a Normal function inside a Trigger Function

[FOUTE K . Jaurès]

Can I have an example please? Or a link

On Sun, 16 Apr 2023, 17:08 Pavel Stehule,  wrote:

> Hi
>
>
> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès 
> napsal:
>
>> Hello,
>>
>> Is it possible to call a function inside a trigger function ?
>> Any idea or link are welcome. Thanks in advance
>>
>
> sure, there is not any limit.
>
> Regards
>
> Pavel
>
>
>>
>> Best Regards
>> --
>> Jaurès FOUTE
>>
>

Re: PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

[Adrian Klaver]

On 4/16/23 11:02, Bryn Llewellyn wrote:> statement fails but doing this 
using PL/pgSQL succeeds.


What's going on under the covers?


Pretty sure:

https://www.postgresql.org/docs/current/spi.html

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

PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements

[Bryn Llewellyn]

> b...@yugabyte.com wrote:
> 
>> david.g.johns...@gmail.com wrote:
>> 
>>> b...@yugabyte.com wrote:
>>> 
>>> I found this email from Peter Eisentraut:
>>> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com
>>> 
>>> It caused the 42601 error, « syntax error at or near “execute” ». So it 
>>> looks like Peter’s patch hasn’t yet been adopted. What is the likelihood 
>>> that it will be adopted in a future version?
>> 
>> Closer to zero than one I'd say, given how that thread ended and not 
>> subsequent activity on the feature in the five years since.
> 
> Thanks. Shall I assume, too, that there’s no under-the-hood functionality for 
> cursors, analogous to what happens with a PL/pgSQL program at run-time, that 
> does the moral equivalent of on-demand prepare for a cursor’s defining 
> subquery?

I tried this test.

create procedure s.p(n in int)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  c0  constant refcursor not null := 'c0';
  c1  constant refcursor not null := 'c1';
  c2  constant refcursor not null := 'c2';

  r   int;
  r0  int[];
  r1  int[];
  r2  int[];
begin
  execute 'declare c0 scroll cursor for select s.v from generate_series(1, 10) 
as s(v)';
  open c1 for execute 'select s.v from generate_series(1, $1) as s(v)' using n;
  open c2 for execute format('execute series(%s)', n);

  for j in 1.. 100 loop
fetch c0 into r; r0[j] := r;
exit when not found;
  end loop;

  for j in 1.. 100 loop
fetch c1 into r; r1[j] := r;
exit when not found;
  end loop;

  for j in 1.. 100 loop
fetch c2 into r; r2[j] := r;
exit when not found;
  end loop;

  assert (r1 = r0) and (r2 = r1);
end;
$body$;

prepare series(int) as select s.v from generate_series(1, $1) as s(v);
start transaction;
  call s.p(10);
  select name, statement from pg_cursors order by name;
rollback;

 I expected to get an error from the PL/pgSQL statement that, after "format()" 
has been consumed, boils down to this:

open c2 for execute 'execute series(10)';

And I wondered how this would be reported. But it ran without error. The loop 
over each cursor showed that the result sets from the three alternatives are 
identical. And this is what the "pg_cursors" query showed:

 name |  statement  

--+-
 c0   | declare c0 scroll cursor for select s.v from generate_series(1, 10) as 
s(v)
 c1   | select s.v from generate_series(1, $1) as s(v)
 c2   | execute series(10)
 c9   | execute series(10)

The difference in the value of "pg_cursors.statement" for two cursors based on 
the identical subquery where one is created with the SQL "declare" and  the 
other is created with the PL/pgSQL "open" has puzzled me from the first time 
that I noticed it.

It seems that there must be different underlying mechanisms at work and that 
this explains why creating a cursor using SQL to execute a prepared statement 
fails but doing this using PL/pgSQL succeeds.

What's going on under the covers?

RE: FW: Error!

[Arquimedes Aguirre]

Hi Dear

If I have the postgres and admin 4 program running with the password that I set 
from the beginning, but when I want to start the psql shell and set the same 
password, it throws me the error, I send the screen I think it's self 
explanatory .

I repeat that when I put the password, which is the same one that I configured 
at the beginning, it throws me the error that I sent you on the screen of my 
first communication or email.

The password does not work because it does not accept the one that I 
established from the beginning when I installed postgres and admin 4 and it 
does not allow me to proceed in the psql shell, again I tell you I sent you the 
screen with the error.

If I can log in to the pgadmin 4 program, I don't have a problem with that.

My approach to the problem has always been the same, the password that I 
established from the beginning when I installed postgres and pgadmin 4 is the 
one that I am using to start in the psql shell and it is not accepted, which is 
why it does not allow me to move forward, I hope these clarifications help you 
allow you to understand and attend to the problem that is being presented to me 
with these tools.

Then you went into the MS dance of reinstalling/rebooting with no
explanation of what prompted that initial reinstall? I don’t understand what do 
you want mean? I have never reinstalling/rebooting, what I said in the email is 
that I had to install and uninstall it several times due to the errors that 
were thrown at me, I also sent the screenshot.

Thanks!

Sent from Mail for Windows

From: Adrian Klaver
Sent: Saturday, April 15, 2023 11:41 AM
To: Arquimedes Aguirre; pgsql-general 
list
Subject: Re: FW: Error!

On 4/15/23 07:39, Arquimedes Aguirre wrote:
> Hi Dear!
>
> I was able to install the postgresql program and admin 4, as I told you
> before, but I got this type of error more than 30 times downloading
> various versions of postgresql that are in the link that you sent me,

This is the part that confuses me. You said you had it working.

At that point could you log in using psql with the password?

Then you went into the MS dance of reinstalling/rebooting with no
explanation of what prompted that initial reinstall?

Was it:

1) The password did not work

2) Something else?

Can you currently log in via pgAdmin4?

Also what version of Postgres are you currently using?

Is that different from the one you initially started with?


> that's why I had to ask for help from Microsoft Team team, since it is a
> rather weird and odd situation. Now I have this problem with the psql
> shell I can't start the program but it don’t allows me to proceed with

 From your previous posts you show you can start the shell script that
runs psql and that psql actually starts it just do not connect due to
password error.

> the password, which is the same one that I set from the beginning, now
> what should I do to fix this once and for all???.
>
> Thanks!
>


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

Re: Call a Normal function inside a Trigger Function

[Pavel Stehule]

Hi


ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès 
napsal:

> Hello,
>
> Is it possible to call a function inside a trigger function ?
> Any idea or link are welcome. Thanks in advance
>

sure, there is not any limit.

Regards

Pavel


>
> Best Regards
> --
> Jaurès FOUTE
>

Re: Suppress logging of "pg_hba.conf rejects connection for host"

[Erik Wienhold]

> On 16/04/2023 17:02 CEST ertan.kucuko...@1nar.com.tr wrote:
>
> One of the systems running PostgreSQL 14.7 receive a lot of lines like in
> the subject. I have below pg_hba.conf line and that line causes these to be
> logged.
>
> host all all 0.0.0.0/0 reject
>
> If possible, I do not want to see these lines in my logs. But, I failed to
> find a parameter for it.
>
> Is it possible to turn this specific message logging off?

There's no special config for this specific error message.  It is logged as
FATAL so the only way to silence it *and any other messages from DEBUG5 to 
FATAL*
is to set log_min_messages = PANIC.  I don't recommend it.  It also complicates
troubleshooting failing connections in the future if you don't log this message.

When logging to syslog you may be able to discard specific messages.
rsyslog has property-based filters[0] for example:

:msg, contains, "pg_hba.conf rejects connection for host" ~

You should also investigate the clients that try connecting ("a lot" as you
write) and figure out why they keep connecting if you want to reject their
attempts anyway.

[0] 
https://rsyslog.readthedocs.io/en/latest/configuration/filters.html#property-based-filters

--
Erik

Re: Call a Normal function inside a Trigger Function

[Erik Wienhold]

> On 16/04/2023 16:18 CEST FOUTE K. Jaurès  wrote:
>
> Is it possible to call a function inside a trigger function ?
> Any idea or link are welcome. Thanks in advance

Depends on what you want to do with the return value.  Use PERFORM to ignore
the result. [0]  Use SELECT INTO to handle a single-row result. [1]

PERFORM myfunc();
SELECT myfunc() INTO myresult;

[0] 
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
[1] 
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

--
Erik

Suppress logging of "pg_hba.conf rejects connection for host"

[ertan.kucukoglu]

Hello,

One of the systems running PostgreSQL 14.7 receive a lot of lines like in
the subject. I have below pg_hba.conf line and that line causes these to be
logged.

host all all 0.0.0.0/0 reject

If possible, I do not want to see these lines in my logs. But, I failed to
find a parameter for it.

Is it possible to turn this specific message logging off?

Thanks & Regards,
Ertan

Call a Normal function inside a Trigger Function

[FOUTE K . Jaurès]

Hello,

Is it possible to call a function inside a trigger function ?
Any idea or link are welcome. Thanks in advance

Best Regards
-- 
Jaurès FOUTE