Re: Is there a way to know write statistics on an individual index

[higherone]

It's not a partial index, but I believe the pg_stat_user_tables contains
all the writes on the table, which not necessarily updates the individual
index I'm interested in (e.g. when the updated column is not part of the
index columns) .


On Fri, Dec 9, 2022 at 2:06 PM Maciek Sakrejda  wrote:

> On Thu, Dec 8, 2022 at 2:53 AM David Rowley  wrote:
> >
> > On Thu, 8 Dec 2022 at 19:59, higherone  wrote:
> > > I know there's a pg_stats_user_indexes that gives statistics about the
> index scans/fetches, but I don't find any information about how many
> writes(insert/update/delete) happen on this individual index.
> > > Is there some tool that could achieve this?
> >
> > Is this a partial index? If not, those should just increase at the
> > same rate that's mentioned in pg_stat_user_tables for the table that
> > the index belongs to.
>
> Well, not deletes, right? And HOT updates won't affect indexes either,
> so they should be subtracted from n_tup_upd.
>

Is there a way to detect that code is inside CREATE EXTENSION?

[Michel Pelletier]

I'm working with an event trigger that fires on ALTER TABLE and regenerates
certain objects, but unfortunately those objects end up being owned by any
extensions that run ALTER TABLE and any subsequent alterations fail to
regenerate because they are owned by that extension.

Ideally, I'd like to be able to detect inside my trigger if I'm being
called from CREATE EXTENSION or not, but I can't find any obvious way to
detect that.  I hope this isn't obvious and I just missed something in the
documentation.  Does anyone have any pointers or hacks they know of that
can accomplish this?

Thanks,

-Michel

Re: Is there a way to detect that code is inside CREATE EXTENSION?

[Tom Lane]

Michel Pelletier  writes:
> I'm working with an event trigger that fires on ALTER TABLE and regenerates
> certain objects, but unfortunately those objects end up being owned by any
> extensions that run ALTER TABLE and any subsequent alterations fail to
> regenerate because they are owned by that extension.

> Ideally, I'd like to be able to detect inside my trigger if I'm being
> called from CREATE EXTENSION or not, but I can't find any obvious way to
> detect that.

At the C-code level you can check the creating_extension global variable,
or maybe better look at the in_extension fields of CollectedCommands.

I don't think we expose that state at the SQL level, but it's pretty
hard to make a useful event trigger without writing any C ...

regards, tom lane

Re: Is there a way to detect that code is inside CREATE EXTENSION?

[Julien Rouhaud]

On Tue, Dec 13, 2022 at 7:49 PM Tom Lane  wrote:
>
> Michel Pelletier  writes:
> > I'm working with an event trigger that fires on ALTER TABLE and regenerates
> > certain objects, but unfortunately those objects end up being owned by any
> > extensions that run ALTER TABLE and any subsequent alterations fail to
> > regenerate because they are owned by that extension.
>
> > Ideally, I'd like to be able to detect inside my trigger if I'm being
> > called from CREATE EXTENSION or not, but I can't find any obvious way to
> > detect that.
>
> At the C-code level you can check the creating_extension global variable,
> or maybe better look at the in_extension fields of CollectedCommands.
>
> I don't think we expose that state at the SQL level, but it's pretty
> hard to make a useful event trigger without writing any C ...

AFAICS it's exposed in pg_event_trigger_ddl_commands().in_extension.

Re: Is there a way to detect that code is inside CREATE EXTENSION?

[Michel Pelletier]

>
> At the C-code level you can check the creating_extension global variable,
> or maybe better look at the in_extension fields of CollectedCommands.
>

Thanks Tom!

That was the hint I needed, looks like pg_event_trigger_ddl_commands() has
an in_extension boolean that seems like it will do what I need?   If I
understand you correctly that's what you're referring to with
CollectedCommands.   Testing it now but that looks like the answer for me,
if any of those are true then I can just skip the regeneration.

-Michel

Re: print in plpython not appearing in logs

[Shaozhong SHI]

What is brilliant about plpython?  Any brilliant examples to look at?

On Friday, 2 December 2022, Ludwig Isaac Lim  wrote:

> I'm having problems wherein my print() statements inside my plpython
> stored proc are not appearing in postgresql log. I tried setting the
> file=sys.stderr
>
> To reproduce:
>
> CREATE OR REPLACE PROCEDURE p_ludwig_test()
> AS
> $$
>import sys
>try:
>   x = 1 / 0
>except:
>   plpy.log("hello")
>   print("oink oink", file=sys.stderr)
>   print("oink oink - v2")
>   plpy.log("haha")
> $$
> LANGUAGE plpython3u;
>
> call p_ludwig_test()
>
>
>
> Output:
> -
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  hello
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  haha
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
>
>
> Notice that the "oink oink"  is  not there.
>
>
> Relevant logging configuration:
> logging_collector = on
> log_directory = 'logs'
> log_min_messages = info
> log_min_error_statement = error
>
> PG version
> ---
>  PostgreSQL 14.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
> 20180712 (Red Hat 7.3.1-15), 64-bit
>
>
>
> The reason why I'm trying to use print() is because plpy logger is quite
> verbose. Each logging will output 3 line
>
> for example:
>plpy.log("hello")
>
> Will generate the following 3 lines in the log:
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  hello
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
>
> Another thing is there is another stored procedure that I have wherein the
> print() to stderr actually works, so I'm not sure what I'm doing wrong.
>
>
> Thank you in advance,
> Ludwig
>
>
>

Re: print in plpython not appearing in logs

[Adrian Klaver]

On 12/13/22 13:20, Shaozhong SHI wrote:

What is brilliant about plpython?  Any brilliant examples to look at?



1) It is actually plython(3)u where the u means untrusted. This means 
you can reach outside the database and do things. That can seen as 
brilliant or dangerous.


2) You have no end of Python libraries you can work with. Again combined 
 with 1) up you whether that is brilliant or dangerous.


3) Python is more dynamic then plpgsql so you can get more adventurous 
with doing dynamic SQL.


Downside is that plpython(3)u is not tied as closely to SQL as plpgsql 
so it very often takes you many more lines of code to get something done.


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

compiling postgres on windows - how to deal with unmatched file extension?

[Yang, T. Andy]

I'm trying to build postgresql from source code on windows. The build ran into 
a bunch of errors like 
'src/backend/bootstrap/bootparse.c': No such file or directory
 'src/backend/parser/gram.c': No such file or directory

These .c files are referenced in postgres.vcxproj. They actual exist but their 
extension is either .l or .y instead of .c. What am I supposed to do so the 
build process will find these files with correct extension? I don't think I 
need to modify vcxproj files to make it work.
I checked out source code from the latest master version on github. I was 
expecting the build would go smoothly since I didn't modify anything.
If this is not the right group to post this kind of question, please let me 
know the right one.
Thanks,Andy

Re: compiling postgres on windows - how to deal with unmatched file extension?

[Tom Lane]

"Yang, T. Andy"  writes:
> I'm trying to build postgresql from source code on windows. The build ran 
> into a bunch of errors like 
> 'src/backend/bootstrap/bootparse.c': No such file or directory
>  'src/backend/parser/gram.c': No such file or directory

I know little about building on Windows, but these are derived files
that should be built by running flex on the corresponding .l file
or bison on the corresponding .y file.  A likely explanation is that
you don't have those tools installed, in which case you should have
seen associated errors earlier in the build log.

regards, tom lane

Re: compiling postgres on windows - how to deal with unmatched file extension?

[David G. Johnston]

On Tue, Dec 13, 2022 at 9:56 PM Yang, T. Andy  wrote:

> I checked out source code from the latest master version on github. I was
> expecting the build would go smoothly since I didn't modify anything.
>

That philosophy unfortunately doesn't work here.  There are many
pre-requisites and there isn't a package.json equivalent kind of file in
which we can define all of our dependencies (especially due to the lack of
intermediary engine like the JVM or Node to cover over platform
differences) so that the build is fully self-contained.  Instead we have to
document those requirements in the documentation (they are indirectly
documented in the configuration files/scripts I suppose...).

https://www.postgresql.org/docs/current/install-windows-full.html

The msvc README in the repo also covers these two extensions explicitly:

https://github.com/postgres/postgres/tree/master/src/tools/msvc

pgbison.pl internal tool to process .y files using bison
pgflex.pl  internal tool to process .l files using flex

David J.

SV: compiling postgres on windows - how to deal with unmatched file extension?

[Niels Jespersen]

>I'm trying to build postgresql from source code on windows. The build ran into 
>a bunch of errors like
>
>'src/backend/bootstrap/bootparse.c': No such file or directory
>src/backend/parser/gram.c': No such file or directory
>
>These .c files are referenced in postgres.vcxproj. They actual exist but their 
>extension is either .l or .y instead of .c. What am I supposed to do so the 
>build process will find these files with correct extension? I don't think I 
>need to modify vcxproj files to make it work.
>
>I checked out source code from the latest master version on github. I was 
>expecting the build would go smoothly since I didn't modify anything.
>
I once wrote this:

https://github.com/njesp/build_pgaudit_on_windows#build-pgaudit-as-part-of-a-full-postgresql-build

as part of something else.

Regards Niels

tcp keepalives not sent during long query

[Willy-Bas Loos]

Hi!

Some users of our database have a NAT firewall and keep a postgres client
(e.g. pgAdmin ) open for hours. To prevent the connection from being killed
by the firewall due to inactivity, we configured tcp_keepalives_idle = 120
so that the server sends keepalives and keeps the connection active. (this
is on debian)

We've recently upgraded from postgres 9.3 to 13 and from debian 6 to 11.
I'm getting the complaint that since the upgrade, the connection breaks.
But only when they run a long query.

Has anything changed in postgres that might cause this? e.g. that
keepalives are only sent when the session is idle?

Thanks
-- 
Willy-Bas Loos