Re: system variable can be edited by all user?

[Pierre Forstmann]

Hello,

You ccan try to use my extension pg_set_acl:
https://github.com/pierreforstmann/pg_set_acl


Le mar. 22 nov. 2022 à 09:07, chris navarroza 
a écrit :

> Hi,
>
> Ive created a read only user (SELECT PRIVILEGE) but it turns out that this
> user can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I
> limit him?
>
> Thanks,
>
> Butching
>
>

how to use query_tree_walker to get all relations used in a query

[Pierre Forstmann]

Hello,

I am trying to get the list of all relations used in a SELECT query using
the post parse analyze hook.

I can get all relations from top level FROM clause but I cannot get them
for a simple subquery like:

select * from t1 where x1=(select max(x2) from t2);

My current code is:

static bool pgds_tree_walker(Query *node, void *context)
{

/*
* from setrefs.c
* extract_query_dependencies_walker
*/

if (node == NULL)
 return false;

  if (IsA(node, Query))
{
 Query  *query = (Query *) node;
 ListCell   *lc;

 /* Collect relation OIDs in this Query's rtable */
 foreach(lc, query->rtable)
 {
 RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);

 if (rte->rtekind == RTE_RELATION ||
 (rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)) ||
 (rte->rtekind == RTE_NAMEDTUPLESTORE &&
OidIsValid(rte->relid)))
 elog(INFO, "pgds_tree_walker: relid=%d", rte->relid);
 }

/*
* from rewriteHandler.c
* AcquireRewriteLocks
*/
if (rte->rtekind == RTE_SUBQUERY)
return query_tree_walker(rte->subquery, pgds_tree_walker, (void *) context,
QTW_EXAMINE_RTES_BEFORE);

 }

 /* And recurse  ...*/
 query_tree_walker(query, pgds_tree_walker, (void *) context,
QTW_EXAMINE_RTES_BEFORE);
}
}

/*
 * build_rel_array
 */
static void pgds_build_rel_array(Query *query)
{
ListCell *cell;
Oid rel_id;
void * context = NULL;
bool result;

foreach(cell, query->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(cell);
rel_id = rte->relid;
if (pgds_rel_index < MAX_REL )
{
pgds_rel_array[pgds_rel_index] = rel_id;
pgds_rel_index++;
}
else elog(ERROR, "pgds_build_rel_array: too many relations (%d)", MAX_REL);
}

result = query_tree_walker(query, pgds_tree_walker, context,
QTW_EXAMINE_RTES_BEFORE);
}

Could someone help me ?

Thanks

Re: how to trace a backend session

[Pierre Forstmann]

Hello,

I have coded an extension to trace SQL statements for specific backends:
https://github.com/pierreforstmann/pg_log_statements
(only SQL statements are traced - no wait events data is collected).

Pierre

Le lun. 22 janv. 2024 à 08:29, James Pang  a écrit :

> experts,
> We migrated our database from Oracle to Postgresql recently, we used
> to turn on Oracle session trace(that will capture all activities, SQL
> statements, waiting, waiting time), to do living troubleshooting.  could
> you direct any similar tracing in Postgresql v13 , v14.
>
> Thanks,
>
> James
>

Re: Unexpected results from CALL and AUTOCOMMIT=off

[Pierre Forstmann]

You declared function f_get_x as stable which means:

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

STABLE indicates that the function cannot modify the database, and that
within a single table scan it will consistently return the same result for
the same argument values, but that its result could change across SQL
statements. This is the appropriate selection for functions whose results
depend on database lookups, parameter variables (such as the current time
zone), etc. (It is inappropriate for AFTER triggers that wish to query rows
modified by the current command.) Also note that the current_timestamp
family of functions qualify as stable, since their values do not change
within a transaction.

If you remove stable from function declaration, it works as expected:

drop table t_test;
DROP TABLE
create table t_test(x bigint);
CREATE TABLE
insert into t_test values(0);
INSERT 0 1
create or replace function f_get_x()
returns bigint
language plpgsql
-- stable
as $function$
declare
l_result bigint;
begin
select x into l_result from t_test;
--raise notice 'f_get_x() >> x=%', l_result;
--raise notice 'f_get_x() >> xact=%', txid_current_if_assigned();
return l_result;
end;
$function$;
CREATE FUNCTION
create or replace procedure f_print_x(x bigint)
language plpgsql
as $procedure$
begin
raise notice 'f_print_x() >> x=%', x;
--raise notice 'f_print_x() >> xact=%', txid_current_if_assigned();
end;
$procedure$;
CREATE PROCEDURE
do
$$ begin
--raise notice 'do >> xact=%', txid_current_if_assigned();
update t_test set x = 1;
--raise notice 'do >> xact=%', txid_current_if_assigned();
raise notice 'do >> x=%', f_get_x();
--raise notice 'do >> xact=%', txid_current_if_assigned();
call f_print_x(f_get_x());
end; $$;
psql:test.sql:38: NOTICE:  do >> x=1
psql:test.sql:38: NOTICE:  f_print_x() >> x=1
DO

Le lun. 3 juin 2024 à 16:42, Victor Yegorov  a écrit :

> Greetings.
>
> I am observing the following results on PostgreSQL 15.7
> First, setup:
>
> create table t_test(x bigint);
> insert into t_test values(0);
>
> create or replace function f_get_x()
> returns bigint
> language plpgsql
> stable
> as $function$
> declare
> l_result bigint;
> begin
> select x into l_result from t_test;
> --raise notice 'f_get_x() >> x=%', l_result;
> --raise notice 'f_get_x() >> xact=%', txid_current_if_assigned();
> return l_result;
> end;
> $function$;
>
> create or replace procedure f_print_x(x bigint)
> language plpgsql
> as $procedure$
> begin
> raise notice 'f_print_x() >> x=%', x;
> --raise notice 'f_print_x() >> xact=%', txid_current_if_assigned();
> end;
> $procedure$;
>
>
> Now, the case:
> \set AUTOCOMMIT off
> do
> $$ begin
> --raise notice 'do >> xact=%', txid_current_if_assigned();
> update t_test set x = 1;
> --raise notice 'do >> xact=%', txid_current_if_assigned();
> raise notice 'do >> x=%', f_get_x();
> --raise notice 'do >> xact=%', txid_current_if_assigned();
> call f_print_x(f_get_x());
> end; $$;
> NOTICE:  do >> x=1
> NOTICE:  f_print_x() >> x=0
> DO
>
> I don't understand why CALL statement is not seeing an updated record.
> With AUTOCOMMIT=on, all goes as expected.
>
> I tried to examine snapshots and xids (commented lines), but they're
> always the same.
>
> Can you explain this behavior, please? Is it expected?
>
> --
> Victor Yegorov
>

Re: Copy Statistics Tables During Upgrade

[Pierre Forstmann]

Hello,

This is a pg_upgrade known limitation that has been summarized by Greg 
Sabino Mullane on 
https://www.endpoint.com/blog/2016/12/07/postgres-statistics-and-pain-of-analyze


this way:

The nominal reason for not copying the data is that the table format may 
change from version to version. The real reason is that nobody has 
bothered to write the conversion logic yet, for pg_upgrade could 
certainly copy the pg_statistics information: the table has not changed 
for many years.


Regards

Pierre Forstmann

Le 31/03/2021 à 20:15, Virendra Kumar a écrit :

Hello Team,

I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and 
came across the question that what is reason PostgreSQL is not doing 
copy of stats and we have to analyze right after upgrade. There are 
two situations where this will help when we use this DB as our 
enterprise database:


1. If DB is in magnitude of hundreds of TB and we have to do analyze 
it might take several hours before we handover this to application 
services.


2. We are loosing critical information like most common values and 
most common freqs because these values are populated over time when 
many-many sessions hit table and queries with different values of a 
column.


Any input on this is higly appreciated.


Regards,
Virendra Kumar