Re: [EXT]Re: Accessing system information functions

[Johnson, Bruce E - (bjohnson)]

Thank you.

On Sep 25, 2023, at 4:18 AM, Erik Wienhold  wrote:

External Email

On 2023-09-22 17:37 +, Johnson, Bruce E - (bjohnson) wrote:
postgres=# pg_database_size(avi);
ERROR:  syntax error at or near "pg_database_size"
LINE 1: pg_database_size(avi);

Call functions with SELECT, e.g.:

SELECT pg_database_size('avi');

Also, the database name must be a string.

Do I have to add anything to the search_path?

Not for objects in schema pg_catalog which is always searched whether
it's listed in the search path or not.

--
Erik


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })

[dld]

Hi there!

I followed the discussion about the schema resolution, and I really 
think there is need for an early bound (at function definition time) 
version of CURRENT_SCHEMA (the first member of search_path)


Avoiding hard-coding of schema names, (and avoiding polluting the actual 
users schema) is hard.


My current code generates some plpgsql functions, which need some helper 
functions to construct fcolumn lists, query fragments, etc.


These helpers should live in the same schema, IMHO


It is not impossible: I ended up with the following kludge to refer to 
functions in the same schema as the calling function.


It involves an extra layer of dynamic SQL, which self-destructs after 
use. It is not pretty, but it works.


Example of such a nested dynamic function is attached. (the helper 
functions are not included, but they live in the same "compilation unit")


Cheers,

Adriaan van Kessel
-- -
CREATE OR REPLACE FUNCTION disposable_factory ()
RETURNS text
VOLATILE
LANGUAGE plpgsql
AS
$WTF$
DECLARE
 _fmt text;
 _sql text;
BEGIN

_fmt = $FMT$
-- "Factory function"
-- Create a table-returning function for table "_fqn"
-- with the same columns.
-- But: restricted to the most recent, upto (and including)  asof_date.
-- The function name is the table name, with '_asof' appended,
-- and it is created in the same schema as the table.
-- The generated function takes one argument _datum
-- , with the same type as tbl.asof_date
-- 
CREATE OR REPLACE FUNCTION create_asof (_fqn text, asof_date text )
RETURNS text
VOLATILE
SECURITY INVOKER
LANGUAGE plpgsql
AS
$func$
DECLARE sql text;
basepair text[];
funcpair text[];
fnc_name text;
org_name text;
allkeys text[];
keys text[];
BEGIN

basepair := %1$s.split_name (_fqn);

funcpair[1] = basepair[1];
funcpair[2] = concat(basepair[2] , '_asof' );

org_name := %1$s.format_pair(basepair);
fnc_name := %1$s.format_pair(funcpair);

allkeys := %1$s.fetch_pk_array (basepair[1] , basepair[2] , asof_date );
keys := array_remove (allkeys, asof_date);

-- RAISE NOTICE 'Allkeys=%%' , all_keys[1];
-- RAISE NOTICE 'Keys=%%' , keys[1];

-- CREATE OR REPLACE FUNCTION %%1$s (IN _datum DATE DEFAULT now() ) -- name
sql := format ('
CREATE OR REPLACE FUNCTION %%1$s (IN _datum %%2$s DEFAULT now() ) -- fnc_name 
dtype
RETURNS SETOF %%3$s -- orgtable
STABLE SECURITY INVOKER ROWS 30
LANGUAGE sql
AS
$omg$
SELECT *-- all columns
FROM %%3$s src  -- org table
WHERE %%4$s -- date treshold
AND NOT EXISTS (
SELECT * 
FROM %%3$s nx   -- org table
WHERE %%5$s -- key fields
AND %%6$s   -- date treshold
AND %%7$s   -- gap
)
;
$omg$ 
;'
, fnc_name  -- 1 Function 
name
, %1$s.fetch_typename(basepair[1], basepair[2], asof_date)  
-- 2 typeof Date field argument
, org_name  -- 3 table name
, %1$s.format_reference ('src', asof_date) || ' <= $1'  -- 4 source 
Date treshold
, %1$s.format_equal_and_list ('nx', 'src', keys)-- 5 
Same Keys
, %1$s.format_reference ('nx', asof_date) || ' <= $1'   -- 6 nx Date 
treshold
, %1$s.format_reference ('nx', asof_date) || ' > ' || 
%1$s.format_reference( 'src', asof_date)  -- 7 Gap Date
);

-- RAISE NOTICE 'Pair= [%%,%%]' , basepair[1], basepair[2];
-- RAISE NOTICE 'Fnc=%%' , fnc_name;
-- RAISE NOTICE 'Sql=%%' , sql;
EXECUTE sql;
-- RETURN sql ;
RETURN fnc_name ;
END;
$func$;
$FMT$ ;

-- RAISE NOTICE '_Fmt=%' , _fmt;
_sql = format (_fmt, quote_ident(current_schema) );
-- RAISE NOTICE '_Sql=%', _sql;

EXECUTE _sql;
DROP FUNCTION disposable_factory (); -- suicide
-- return _sql;
return 'create_asof';
END;
$WTF$ ;

\echo SELECT disposable_factory();
SELECT disposable_factory();
-- \df create_asof

-- EOF

Ad hoc SETOF type definition?

[Ron]

Pg 9.6.24 (Yes, I know it's EOL.)

This simple "programming example" function works perfectly. However, it 
requires me to create the TYPE "foo".


CREATE TYPE foo AS (tab_name TEXT, num_pages INT);
CREATE FUNCTION dba.blarge()
    RETURNS SETOF foo
    LANGUAGE plpgsql
    AS
$$
    DECLARE
    ret foo;
    bar CURSOR FOR
    select relname::text as table_name, relpages
    from pg_class where relkind = 'r'
    order by 1;
    BEGIN
    FOR i IN bar LOOP
    SELECT i.table_name, i.relpages INTO ret;
    RETURN NEXT ret;
    END LOOP;
    END;
$$;

Is there a way to define the SETOF record on the fly, like you do with 
RETURNS TABLE (f1 type1, f2 type2)?


--
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

[Tom Lane]

Ron  writes:
> Is there a way to define the SETOF record on the fly, like you do with 
> RETURNS TABLE (f1 type1, f2 type2)?

Doesn't RETURNS TABLE meet the need already?

regards, tom lane

Re: Ad hoc SETOF type definition?

[Ron]

On 9/26/23 12:46, Tom Lane wrote:

Ron  writes:

Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?

Doesn't RETURNS TABLE meet the need already?


That rationale means that RETURN SETOF is not needed, and can be removed 
from Pg, since "RETURNS TABLE meet the need already".


But of course there /are/ times when RETURN SETOF /is/ useful.

So... can ad hoc SETOF definitions be created in the function definition, or 
is CREATE TYPE the only way to do it?


--
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

[Adrian Klaver]

On 9/26/23 11:03 AM, Ron wrote:

On 9/26/23 12:46, Tom Lane wrote:

Ron  writes:

Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?

Doesn't RETURNS TABLE meet the need already?


That rationale means that RETURN SETOF is not needed, and can be 
removed from Pg, since "RETURNS TABLE meet the need already".


But of course there /are/ times when RETURN SETOF /is/ useful.

So... can ad hoc SETOF definitions be created in the function 
definition, or is CREATE TYPE the only way to do it?



That is what RETURNS TABLE does:

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

"/|column_name|/

   The name of an output column in the |RETURNS TABLE| syntax. This is
   effectively another way of declaring a named |OUT| parameter, except
   that |RETURNS TABLE| also implies |RETURNS SETOF|.

"



--
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

[Tom Lane]

Ron  writes:
> On 9/26/23 12:46, Tom Lane wrote:
>> Ron  writes:
>>> Is there a way to define the SETOF record on the fly, like you do with
>>> RETURNS TABLE (f1 type1, f2 type2)?

>> Doesn't RETURNS TABLE meet the need already?

> That rationale means that RETURN SETOF is not needed, and can be removed 
> from Pg, since "RETURNS TABLE meet the need already".

Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't.  SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.

> So... can ad hoc SETOF definitions be created in the function definition, or 
> is CREATE TYPE the only way to do it?

I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.  Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing.  That makes
it clear that the type exists independently of the function.  (Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)

regards, tom lane

Is the logfile the only place to find the finish LSN?

[pgchem pgchem]

Hello all,
 
according to:
 
https://www.postgresql.org/docs/current/logical-replication-conflicts.html
 
or
 
https://www.postgresql.fastware.com/blog/addressing-replication-conflicts-using-alter-subscription-skip
 
The logfile is the _only_ place to find the transaction finish LSN that must be 
skipped if logical replication is stuck on a conflict. Is the logfile the only 
place to find this information, or can it also be found somewhere inside the 
database, e. g. in some system catalog view?
 
best regards
 
Ernst-Georg

log_statement vs log_min_duration_statement

[Atul Kumar]

Hi,

I have a query about parameters  and log_statement

my postgres version is 12 and running on centos 7

my log_statement is set to "DDL".

and log_min_duration_statement is set to "1ms"

so technically it should log "ONLY DDLs" that take more than 1ms.

but to my surprise, postgres is logging DML as well which is taking more
than 1ms.

What am I missing here to understand, because as per my understanding
log_statement and log_min_duration_statement are correlated, postgres
should log according to log_statement parameter.

Please advise me on this behavior.



Regards,
Atul

Re: log_statement vs log_min_duration_statement

[Adrian Klaver]

On 9/26/23 11:31 AM, Atul Kumar wrote:

Hi,

I have a query about parameters  and log_statement

my postgres version is 12 and running on centos 7

my log_statement is set to "DDL".

and log_min_duration_statement is set to "1ms"

so technically it should log "ONLY DDLs" that take more than 1ms.

but to my surprise, postgres is logging DML as well which is taking 
more than 1ms.


What am I missing here to understand, because as per my understanding 
log_statement and log_min_duration_statement are correlated, postgres 
should log according to log_statement parameter.


Please advise me on this behavior.



https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT


"

|log_min_duration_statement| (|integer|)

   Causes the duration of each completed statement to be logged if the
   statement ran for at least the specified amount of time.

    <...>


 Note

When using this option together with log_statement 
, 
the text of statements that are logged because of |log_statement| will 
not be repeated in the duration log message. If you are not using 
syslog, it is recommended that you log the PID or session ID using 
log_line_prefix 
 
so that you can link the statement message to the later duration message 
using the process ID or session ID.


"




Regards,
Atul

Re: Ad hoc SETOF type definition?

[Ron]

On 9/26/23 13:15, Tom Lane wrote:

Ron  writes:

On 9/26/23 12:46, Tom Lane wrote:

Ron  writes:

Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?

Doesn't RETURNS TABLE meet the need already?

That rationale means that RETURN SETOF is not needed, and can be removed
from Pg, since "RETURNS TABLE meet the need already".

Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't.  SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.


So... can ad hoc SETOF definitions be created in the function definition, or
is CREATE TYPE the only way to do it?

I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.


There might be some other logic in the body of the FOR loop that is not 
practical to embed in the body of the SELECT statement.



   Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing.  That makes
it clear that the type exists independently of the function.


If you're going to only use that type with the function, then an anonymous 
record type is Good Enough.


Just like anonymous DO blocks are useful.


(Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)


It makes developers/DBAs lives just that much easier.

--
Born in Arizona, moved to Babylonia.

Re: Ad hoc SETOF type definition?

[Adrian Klaver]

On 9/26/23 12:30, Ron wrote:

On 9/26/23 13:15, Tom Lane wrote:

Ron  writes:

On 9/26/23 12:46, Tom Lane wrote:



I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.


There might be some other logic in the body of the FOR loop that is not 
practical to embed in the body of the SELECT statement.


I think you are conflating RETURNS TABLE and RETURN QUERY. You can build 
a 'TABLE' from variables outside of a query.



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

Re: log_statement vs log_min_duration_statement

[Maciek Sakrejda]

On Tue, Sep 26, 2023, 12:34 Atul Kumar  wrote:

> What am I missing here to understand, because as per my understanding
> log_statement and log_min_duration_statement are correlated, postgres
> should log according to log_statement parameter.
>

The two settings are independent. One logs *all* statements in the
specified category, and the other *all* statements that execute longer than
the specified threshold. I don't believe there is a built-in way of getting
the behavior you're looking for.

Re: Ad hoc SETOF type definition?

[Adrian Klaver]

On 9/26/23 13:29, Adrian Klaver wrote:

On 9/26/23 12:30, Ron wrote:

On 9/26/23 13:15, Tom Lane wrote:

Ron  writes:

On 9/26/23 12:46, Tom Lane wrote:



I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.


There might be some other logic in the body of the FOR loop that is 
not practical to embed in the body of the SELECT statement.


I think you are conflating RETURNS TABLE and RETURN QUERY. You can build 
a 'TABLE' from variables outside of a query.




As a very simple example:

create table source(id integer, fld_1 varchar);

insert into source values (1, 'cat'), (2, 'dog'), (3, 'fish');

CREATE OR REPLACE FUNCTION public.table_return(multiplier integer, 
suffix character varying)
 RETURNS TABLE(multiplied integer, fld_suffix character varying, 
rand_number numeric)

 LANGUAGE plpgsql
AS $function$
DECLARE
_idinteger;
_fld   varchar;
BEGIN

FOR _id, _fld IN
SELECT
id, fld_1
FROM
source
LOOP
multiplied = _id * multiplier;
fld_suffix = _fld || '_' || suffix;
rand_number = random() * 100;

RETURN NEXT;
END LOOP;

END;
$function$
;

select * from table_return(2, 'test');
 multiplied | fld_suffix |   rand_number
++--
  2 | cat_test   | 79.7745033326483
  4 | dog_test   | 12.5713231966519
  6 | fish_test  | 3.21770069680842
--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })

[Erik Wienhold]

On 2023-09-26 14:44 +0200, dld wrote:
> I followed the discussion about the schema resolution, and I really think
> there is need for an early bound (at function definition time) version of
> CURRENT_SCHEMA (the first member of search_path)

You mean something like

CREATE FUNCTION myfunc()
RETURNS void
SET search_path = CURRENT_SCHEMA
...

where CURRENT_SCHEMA would resolve to the current schema instead of
meaning literal "CURRENT_SCHEMA"?

> Avoiding hard-coding of schema names, (and avoiding polluting the actual
> users schema) is hard.
> 
> My current code generates some plpgsql functions, which need some helper
> functions to construct fcolumn lists, query fragments, etc.
> 
> These helpers should live in the same schema, IMHO

The helper functions can't be created in a common schema with a fixed
name?

> It is not impossible: I ended up with the following kludge to refer to
> functions in the same schema as the calling function.
> 
> It involves an extra layer of dynamic SQL, which self-destructs after use.
> It is not pretty, but it works.

You can do without the self-dropping disposable_factory() by using a DO
block instead.

> Example of such a nested dynamic function is attached. (the helper functions
> are not included, but they live in the same "compilation unit")

It's already possible to "inject" the current schema name by setting the
search path after creating the function.  Define the function as usual
but with unqualified names in the function body and then alter the
function to set the search path to the value of current_schema.

BEGIN;

CREATE FUNCTION create_asof(_fqn text, asof_date text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
basepair text[];
BEGIN
basepair := split_name(_fqn);
-- etc.
END $$;

DO $$
BEGIN
EXECUTE format(
'ALTER FUNCTION create_asof(text, text) SET search_path = %I',
current_schema);
END $$;

COMMIT;

By the looks of it, your create_asof() creates functions with qualified
names provided in variable fnc_name.  So the executed CREATE FUNCTION
statement is not subject to the current search path.

-- 
Erik

Re: log_statement vs log_min_duration_statement

[Jeff Janes]

On Tue, Sep 26, 2023 at 5:33 PM Atul Kumar  wrote:

> Hi,
>
> I have a query about parameters  and log_statement
>
> my postgres version is 12 and running on centos 7
>
> my log_statement is set to "DDL".
>
> and log_min_duration_statement is set to "1ms"
>
> so technically it should log "ONLY DDLs" that take more than 1ms.
>
> but to my surprise, postgres is logging DML as well which is taking more
> than 1ms.
>

That doesn't surprise me, it is just what I expected.



>
> What am I missing here to understand, because as per my understanding
> log_statement and log_min_duration_statement are correlated, postgres
> should log according to log_statement parameter.
>

If something in the docs led you to this misunderstanding, we should
identify it and fix it.  Otherwise, there isn't much we can do, except
point out that that is simply not how it works.

Cheers,

Jeff

>

Re: Ad hoc SETOF type definition?

[Ron]

On 9/26/23 16:29, Adrian Klaver wrote:
[snip]

As a very simple example:


This is EXACTLY what I was looking for.  Thank you.



create table source(id integer, fld_1 varchar);

insert into source values (1, 'cat'), (2, 'dog'), (3, 'fish');

CREATE OR REPLACE FUNCTION public.table_return(multiplier integer, suffix 
character varying)
 RETURNS TABLE(multiplied integer, fld_suffix character varying, 
rand_number numeric)

 LANGUAGE plpgsql
AS $function$
DECLARE
    _id    integer;
    _fld   varchar;
BEGIN

FOR _id, _fld IN
    SELECT
    id, fld_1
    FROM
    source
    LOOP
    multiplied = _id * multiplier;
    fld_suffix = _fld || '_' || suffix;
    rand_number = random() * 100;

    RETURN NEXT;
    END LOOP;

END;
$function$
;

select * from table_return(2, 'test');
 multiplied | fld_suffix |   rand_number
++--
  2 | cat_test   | 79.7745033326483
  4 | dog_test   | 12.5713231966519
  6 | fish_test  | 3.21770069680842


--
Born in Arizona, moved to Babylonia.

Re: Early binding of CURRENT_SCHEMA (Was: CREATE FUNCTION ... SEARCH {, DEFAULT | SYSTEM | SESSION })

[Erik Wienhold]

ccing list

On 2023-09-27 00:12 +0200, dld write:
> On 26-09-2023 23:47, Erik Wienhold wrote:
> > On 2023-09-26 14:44 +0200, dld wrote:
> > > I followed the discussion about the schema resolution, and I really think
> > > there is need for an early bound (at function definition time) version of
> > > CURRENT_SCHEMA (the first member of search_path)
> > You mean something like
> > 
> > CREATE FUNCTION myfunc()
> > RETURNS void
> > SET search_path = CURRENT_SCHEMA
> > ...
> > 
> > where CURRENT_SCHEMA would resolve to the current schema instead of
> > meaning literal "CURRENT_SCHEMA"?
> > 
> > > Avoiding hard-coding of schema names, (and avoiding polluting the actual
> > > users schema) is hard.
> > > 
> > > My current code generates some plpgsql functions, which need some helper
> > > functions to construct fcolumn lists, query fragments, etc.
> > > 
> > > These helpers should live in the same schema, IMHO
> > The helper functions can't be created in a common schema with a fixed
> > name?
> > 
> > > It is not impossible: I ended up with the following kludge to refer to
> > > functions in the same schema as the calling function.
> > > 
> > > It involves an extra layer of dynamic SQL, which self-destructs after use.
> > > It is not pretty, but it works.
> > You can do without the self-dropping disposable_factory() by using a DO
> > block instead.
> > 
> > > Example of such a nested dynamic function is attached. (the helper 
> > > functions
> > > are not included, but they live in the same "compilation unit")
> > It's already possible to "inject" the current schema name by setting the
> > search path after creating the function.  Define the function as usual
> > but with unqualified names in the function body and then alter the
> > function to set the search path to the value of current_schema.
> > 
> > BEGIN;
> > 
> > CREATE FUNCTION create_asof(_fqn text, asof_date text)
> > RETURNS text
> > LANGUAGE plpgsql
> > AS $$
> > DECLARE
> > basepair text[];
> > BEGIN
> > basepair := split_name(_fqn);
> > -- etc.
> > END $$;
> > 
> > DO $$
> > BEGIN
> > EXECUTE format(
> > 'ALTER FUNCTION create_asof(text, text) SET search_path = %I',
> > current_schema);
> > END $$;
> > 
> > COMMIT;
> > 
> > By the looks of it, your create_asof() creates functions with qualified
> > names provided in variable fnc_name.  So the executed CREATE FUNCTION
> > statement is not subject to the current search path.
> > 
> No: you misunderstood, IMO
> 
> It is about the worker functions being called. (which need to be in the same
> schema) The worker funcvtioens just need to be found.
> 
> CURRENT_SCHEMA just changes the schema to whatever schema the caller appears
> to live in. (at execution time!!11!1)

Ah, I see.  But still wondering if this is necessary.

> I do not want to interfere, I do not want to pollute their schema with my
> nonsense-functions..

Again, why can't create_asof() and the helper/worker functions be in a
hard coded schema?  Are those functions defined once in the database or
does each user get their own version, perhaps in a multitenancy design?
And who is calling create_asof()?

I'm currently working on a database that I also designed in large parts
where trigger functions (SECURITY DEFINER) create views that give users
a restricted view of the data for ease of use.  Quite similar to that
create_asof() function but with hard coded schema names.  So I'm also
interested to learn what designs other people came up with.

>  I just want to keep them in my own secret schema. [remember POSTGIS?]

Secretive for having security through obscurity?  But you can't really
hide schema information when users still need access to system catalogs.
But you can decide to revoke EXECUTE privilege from those functions and
give users a few SECURITY DEFINER functions as entry points to the
"private" parts of the schema.

Anything special about PostGIS in this regard?  In my databases PostGIS
either lives in public or a dedicated schema.  But there's nothing
secretive about it.

-- 
Erik