Event Triggers unable to capture the DDL script executed

[Neethu P]

Hi team,

We are using event triggers to capture the DDL changes in a postgres database. 
However, we are unable to get the column information & the actual DDL script 
executed, while a table is altered.
Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- it is 
mentioned as below.
pg_ddl_command  A complete representation of the command, in internal format. 
This cannot be output directly, but it can be passed to other functions to 
obtain different pieces of information about the command.

Is it possible to access pg_ddl_command in postgresql? Or is there any scripts 
which can help to get the actual Alter DDL statement that was executed by the 
user?

Thanks & Regards,
Neethu

PostgreSQL optimizations for CoW FS

[HECTOR INGERTO]

Let’s say we have to run a PostgreSQL instance on top of a copy on write 
filesystem like ZFS or BTRFS. In adittion to set full_page_writes = off, what 
other optimizations can be done on the PostgreSQL side?

Regards,


Héctor

Re: Event Triggers unable to capture the DDL script executed

[Laurenz Albe]

On Wed, 2023-02-22 at 07:57 +, Neethu P wrote:
> We are using event triggers to capture the DDL changes in a postgres database.
> However, we are unable to get the column information & the actual DDL script
> executed, while a table is altered. 
> Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- it 
> is mentioned as below.
> pg_ddl_commandA complete representation of the command, in internal
> format. Thiscannot be output directly, but it can be passed to other functions
> to obtain different pieces of information about the command.
> 
> Is it possible to access pg_ddl_command in postgresql? Or is there any scripts
> which can help to get theactual Alter DDL statement that was executed by the 
> user? 

That is simple if you write the event trigger in C.  I would say that that is 
the
only way to get at the actual statement.

Yours,
Laurenz Albe

RE: Event Triggers unable to capture the DDL script executed

[n.kobzarev]

>>-Исходное сообщение-
>>От: Laurenz Albe  
>>Отправлено: 22 февраля 2023 г. 12:52
>>Кому: Neethu P ; pgsql-general 
>>
>>Тема: Re: Event Triggers unable to capture the DDL script executed

>>On Wed, 2023-02-22 at 07:57 +, Neethu P wrote:
>>> We are using event triggers to capture the DDL changes in a postgres 
>>> database.
>>> However, we are unable to get the column information & the actual DDL 
>>> script executed, while a table is altered.
>>> Also, in the postgres documentation for pg_event_trigger_ddl_commands ()- 
>>> it is mentioned as below.
>>> pg_ddl_command  A complete representation of the command, in internal
>>> format. Thiscannot be output directly, but it can be passed to other 
>>> functions to obtain different pieces of information about the command.
>>> 
>>> Is it possible to access pg_ddl_command in postgresql? Or is there any 
>>> scripts which can help to get theactual Alter DDL statement that was 
>>> executed by the user?

>>That is simple if you write the event trigger in C.  I would say that that is 
>>the only way to get at the actual statement.

>>Yours,
>>Laurenz Albe



In MSSQL there is a brilliant possibility to have a server wide trigger to 
monitor commands. We are using It to have a history for all DDL operations.

Please try this (on new empty database) and give a feedback.

CREATE OR REPLACE FUNCTION public.notice_ddl()
RETURNS event_trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
r RECORD;
begin
raise info '%', session_user || ' ran '||tg_tag||' '||current_query();
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
RAISE NOTICE 'we got a % event for object " %"', 
r.command_tag, r.object_identity;
END LOOP;
end;
$BODY$;

CREATE OR REPLACE FUNCTION public.notice_ddl_drop()
RETURNS event_trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
r RECORD;
begin
raise info '%', session_user || ' ran '||tg_tag||' '||current_query();
FOR r IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE 'dropped: type "%" identity %',
r.object_type, r.object_identity;
END LOOP;
end;
$BODY$;


CREATE EVENT TRIGGER etg ON DDL_COMMAND_END
EXECUTE PROCEDURE public.notice_ddl();

CREATE EVENT TRIGGER etg_drop ON SQL_DROP
EXECUTE PROCEDURE public.notice_ddl_drop();

Memory leak using when using libpq PQExecParams() CRYPTO_zalloc()

[Michael Arnold]

Hi,

Am looking for guidance on how to fix a memory leak when using libpq
PQExecParams().  Memory leaks through CRYPTO_zalloc() and arises when using
json_agg().  None-JSON based PQExecParams() calls are not leaking.

Using Postgresql 13.6 (Ubuntu 13.6-0ubuntu0.21.10.1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
Accessed by a c/c++ application via libpq (libssl3 and libcrypt are also
linked)

Valgrind reports:

==4107== 2,712 bytes in 3 blocks are definitely lost in loss record 265 of
276
==4107==at 0x4848899: malloc (in
/usr/libexec/valgrind/vgpreload_memcheck-amd64-linux.so)
==4107==by 0x4B1F41D: CRYPTO_zalloc (in
/usr/lib/x86_64-linux-gnu/libcrypto.so.3)
==4107==by 0x4AD50A8: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
==4107==by 0x4AD51EC: ERR_clear_error (in
/usr/lib/x86_64-linux-gnu/libcrypto.so.3)
==4107==by 0x488D52A: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
==4107==by 0x488E89D: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
==4107==by 0x488E923: PQsendQueryParams (in
/usr/lib/x86_64-linux-gnu/libpq.so.5.14)
==4107==by 0x4893C27: PQexecParams (in
/usr/lib/x86_64-linux-gnu/libpq.so.5.14)
==4107==by 0x3E1613: getJSON(std::__cxx11::basic_string, std::allocator >) (dbInterface.cpp:7202)
==4107==by 0x47ACEC:
tickerHandler::handleRequest(Poco::Net::HTTPServerRequest&,
Poco::Net::HTTPServerResponse&) (externalInterface.cpp:615)
==4107==by 0x5096E18: Poco::Net::HTTPServerConnection::run() (in
/usr/lib/x86_64-linux-gnu/libPocoNet.so.80)
==4107==by 0x50D935A: Poco::Net::TCPServerConnection::start() (in
/usr/lib/x86_64-linux-gnu/libPocoNet.so.80)

i.e. call to PQExecParams() within getJSON() is leaking via
CRYTO_zalloc().  The code for getJSON() is reproduced below.

std::string getJSON(std::string sqlQuery){
PGconn *dbConn = nullptr;
int nParams = 0;
int resultFormat = 1; //binary format
size_t noRecords = 0;
PGresult *res = nullptr;
std::string resultJSON;

std::string fullQuery = "select json_agg(t) FROM (" + sqlQuery + ") t;";

if ((dbConn = getConnection(connectionPool)) != nullptr) {
res = PQexecParams(dbConn,
fullQuery.c_str(),
nParams,
NULL,
NULL,
NULL,
NULL, resultFormat);

//Check if there was a problem
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
if (dbConn != nullptr){
returnConnection(connectionPool, dbConn);
PQclear(res);
}
return (resultJSON);
} //End of result checking

//If we successfully get the information then populate resultJSON
noRecords = (int64_t) PQntuples(res);
if (noRecords == 1)
resultJSON = std::string((const char*) PQgetvalue(res, 0, 0));

//Clean-up
PQclear(res);
returnConnection(connectionPool, dbConn);
}

//select json_arr may return an empty string "", but this is not valid json
and json.parse will error
// to avoid this return a valid json empty string
if (resultJSON.empty())
resultJSON = "\"\"";

return (resultJSON);
}

getConnection() and returnConnection() are application specific calls to
get and replace valid PGconn* to / from an in-app postgres connection pool.

Setup is really basic:
1. In postgresql.conf
1a. Uncomment listen_addresses
1b. Replace localhost with *
1c. Uncomment password_encryption

2. In pg_hba.conf
2a. Add the local network:
host all all 192.168.0.101/24 md5
2b. Replace ident with md5

Re: Memory leak using when using libpq PQExecParams() CRYPTO_zalloc()

[Jeffrey Walton]

On Wed, Feb 22, 2023 at 8:35 AM Michael Arnold  wrote:
>
> Am looking for guidance on how to fix a memory leak when using libpq 
> PQExecParams().  Memory leaks through CRYPTO_zalloc() and arises when using 
> json_agg().  None-JSON based PQExecParams() calls are not leaking.
>
> Using Postgresql 13.6 (Ubuntu 13.6-0ubuntu0.21.10.1) on x86_64-pc-linux-gnu, 
> compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
> Accessed by a c/c++ application via libpq (libssl3 and libcrypt are also 
> linked)
>
> Valgrind reports:
>
> ==4107== 2,712 bytes in 3 blocks are definitely lost in loss record 265 of 276
> ==4107==at 0x4848899: malloc (in 
> /usr/libexec/valgrind/vgpreload_memcheck-amd64-linux.so)
> ==4107==by 0x4B1F41D: CRYPTO_zalloc (in 
> /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x4AD50A8: ??? (in /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x4AD51EC: ERR_clear_error (in 
> /usr/lib/x86_64-linux-gnu/libcrypto.so.3)
> ==4107==by 0x488D52A: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x488E89D: ??? (in /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x488E923: PQsendQueryParams (in 
> /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x4893C27: PQexecParams (in 
> /usr/lib/x86_64-linux-gnu/libpq.so.5.14)
> ==4107==by 0x3E1613: getJSON(std::__cxx11::basic_string std::char_traits, std::allocator >) (dbInterface.cpp:7202)
> ==4107==by 0x47ACEC: 
> tickerHandler::handleRequest(Poco::Net::HTTPServerRequest&, 
> Poco::Net::HTTPServerResponse&) (externalInterface.cpp:615)
> ==4107==by 0x5096E18: Poco::Net::HTTPServerConnection::run() (in 
> /usr/lib/x86_64-linux-gnu/libPocoNet.so.80)
> ==4107==by 0x50D935A: Poco::Net::TCPServerConnection::start() (in 
> /usr/lib/x86_64-linux-gnu/libPocoNet.so.80)
>
> i.e. call to PQExecParams() within getJSON() is leaking via CRYTO_zalloc().  
> The code for getJSON() is reproduced below.
>
> std::string getJSON(std::string sqlQuery){
> PGconn *dbConn = nullptr;
> int nParams = 0;
> int resultFormat = 1; //binary format
> size_t noRecords = 0;
> PGresult *res = nullptr;
> std::string resultJSON;
>
> std::string fullQuery = "select json_agg(t) FROM (" + sqlQuery + ") t;";
>
> if ((dbConn = getConnection(connectionPool)) != nullptr) {
> res = PQexecParams(dbConn,
> fullQuery.c_str(),
> nParams,
> NULL,
> NULL,
> NULL,
> NULL, resultFormat);
>
> //Check if there was a problem
> if (PQresultStatus(res) != PGRES_TUPLES_OK) {
> if (dbConn != nullptr){
> returnConnection(connectionPool, dbConn);
> PQclear(res);
> }
> return (resultJSON);
> } //End of result checking
>
> //If we successfully get the information then populate resultJSON
> noRecords = (int64_t) PQntuples(res);
> if (noRecords == 1)
> resultJSON = std::string((const char*) PQgetvalue(res, 0, 0));
>
> //Clean-up
> PQclear(res);
> returnConnection(connectionPool, dbConn);
> }
>
> //select json_arr may return an empty string "", but this is not valid json 
> and json.parse will error
> // to avoid this return a valid json empty string
> if (resultJSON.empty())
> resultJSON = "\"\"";
>
> return (resultJSON);
> }
>
> getConnection() and returnConnection() are application specific calls to get 
> and replace valid PGconn* to / from an in-app postgres connection pool.
>
> Setup is really basic:
> 1. In postgresql.conf
> 1a. Uncomment listen_addresses
> 1b. Replace localhost with *
> 1c. Uncomment password_encryption
>
> 2. In pg_hba.conf
> 2a. Add the local network:
> host all all 192.168.0.101/24 md5
> 2b. Replace ident with md5

Before you do anything with Valgrind, you should rebuild Postgres,
your program, and dependent libraries of interest with -g -O1.
Otherwise you risk wasting [a lot] time on false positives.

Also see https://valgrind.org/docs/manual/quick-start.html .

Jeff

Re: Debugging postgres on Windows - could not open directory "/lib"

[Adrian Klaver]

On 2/21/23 17:18, Cathy Xie wrote:



On Wed, Feb 22, 2023 at 6:54 AM Adrian Klaver 



Hi Adrian,

Thanks for your email!

1) How did you build it?


I installed ActivateState Perl, Bison, and Flex. Then I ran the command 
```build Debug``` under directory postgres/src/tools/msvc


2) Open pgsql.sln in a text editor and report the results here.

Here was the result when I opened pgsql.sln in Visual Studio 2022, and 
ran the postgres.exe.


1) That just repeats the error message from your previous post.

2) Please use copy and paste of text instead of screenshots. Those of us 
with old eyes would appreciate it.


3) Per previous request:

Open pgsql.sln in a text editor and report the results here.

It is the contents of pgsql.sln that I am looking for.



Screenshot_20230222_091621.png
I look forward to hearing back from you.

Thanks!
Cathy


 >
 > Best regards,
 > Cathy
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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