How to return argument data type from sql function

[Andrus]

PostgreSQL 12.2+ function is defined as

    create FUNCTION torus(eevarus text) returns text immutable AS $f$
 select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
    $f$ LANGUAGE SQL ;

This function is called as CHAR(n) or text columns like

    create temp table test (
    charcol char(10),
    textcol text );

    insert into test values ('test', 'test');

    select torus(charcol), torus(textcol), charcol

torus(charcol) returns text column and loses original column width. How 
to force torus() to return argument type:


if char(n) column is passed as argument, torus() should also return 
char(n) data type.

I tried to use bpchar instead on text

    create or replace FUNCTION torusbpchar(eevarus bpchar) returns 
bpchar immutable AS $f$

 select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
    $f$ LANGUAGE SQL ;

torusbpchar(charcol) still returns text data type.

npgsql DataReader is used to get data.

Andrus.

does postgres has snapshot standby feature?

[milist ujang]

hi all,

read about removing standby.signal file behavior in pg14 from
https://dbaclass.com/article/how-to-open-postgres-standby-database-for-read-writesnapshot-standby/

I was shocked about the hidden feature of snapshot standby, does it really
exist?
I could not find anything about snapshot standby in docs so far.

I have removed the standby.signal file in pg12 but got strange behavior
until I have to recreate the standby.

-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: recovery.conf and archive files

[Guillaume Lelarge]

Le jeu. 13 oct. 2022 à 12:42, Rita  a écrit :

> The primary's recovery.conf looks like this
>
> listen_address='*'
> wal_level=replica
> synchronous_commit=local
> archive_move = on
> archive_command = 'cp %p /var/lib/pgsql/11/data/archive/%f'
> max_wal_senders = 10
> wal_keep_segments=10
> synchronous_standby_names='standby0'
> wal_log_hints=on
>
>
The archive command stores the WAL in a local directory. That's what I said
earlier.


>
> On Sun, Oct 9, 2022 at 8:45 AM Guillaume Lelarge 
> wrote:
>
>> Hi,
>>
>> Le dim. 9 oct. 2022 à 13:54, Rita  a écrit :
>>
>>> I have primary and standby replication setup.
>>>
>>> On my primary the archive directory is rather large (30GB) and growing.
>>> On my standby I have recovery.conf which has
>>> archive_cleanup_command = 'pg_archivecleanup -d
>>> /var/lib/pgsql/11/data/archive %r'
>>>
>>> I was under the impression this line would remove data from my primary
>>> AND standby. Is that not the case?
>>>
>>>
>> pg_archivecleanup will clean up the *local* directory. It won't clean up
>> the archive directory if it's stored on the primary.
>>
>> If I misunderstood your issue, it would be great to send us the
>> postgresql.conf file from your primary.
>>
>>
>> --
>> Guillaume.
>>
>
>
> --
> --- Get your facts first, then you can distort them as you please.--
>


-- 
Guillaume.

Re: recovery.conf and archive files

[Rita]

is the archive command even needed? I can see my standby fully synced.

On Fri, Oct 14, 2022 at 5:27 AM Guillaume Lelarge 
wrote:

> Le jeu. 13 oct. 2022 à 12:42, Rita  a écrit :
>
>> The primary's recovery.conf looks like this
>>
>> listen_address='*'
>> wal_level=replica
>> synchronous_commit=local
>> archive_move = on
>> archive_command = 'cp %p /var/lib/pgsql/11/data/archive/%f'
>> max_wal_senders = 10
>> wal_keep_segments=10
>> synchronous_standby_names='standby0'
>> wal_log_hints=on
>>
>>
> The archive command stores the WAL in a local directory. That's what I
> said earlier.
>
>
>>
>> On Sun, Oct 9, 2022 at 8:45 AM Guillaume Lelarge 
>> wrote:
>>
>>> Hi,
>>>
>>> Le dim. 9 oct. 2022 à 13:54, Rita  a écrit :
>>>
 I have primary and standby replication setup.

 On my primary the archive directory is rather large (30GB) and growing.
 On my standby I have recovery.conf which has
 archive_cleanup_command = 'pg_archivecleanup -d
 /var/lib/pgsql/11/data/archive %r'

 I was under the impression this line would remove data from my primary
 AND standby. Is that not the case?


>>> pg_archivecleanup will clean up the *local* directory. It won't clean up
>>> the archive directory if it's stored on the primary.
>>>
>>> If I misunderstood your issue, it would be great to send us the
>>> postgresql.conf file from your primary.
>>>
>>>
>>> --
>>> Guillaume.
>>>
>>
>>
>> --
>> --- Get your facts first, then you can distort them as you please.--
>>
>
>
> --
> Guillaume.
>


-- 
--- Get your facts first, then you can distort them as you please.--

[libpq] OIDs of extension types? Of custom types?

[Dominique Devienne]

Hi. I'm using binary binds and results for DMLs, as well as for COPY.

So far, I've stayed within built-in scalar and array types, thus I
could hardcode the OIDs of values "in the type-system" (in C++, via
traits).
But I'd like to venture into extension (e.g. hstore) and custom
(enums, domain, etc...) types.
Thus I'm wondering:
1) whether "official" extensions have fixed/stable OIDs, like in my
hstore example. If so, where are they defined?
2) how should I be looking up OIDs for custom (or extension?) types
with libpq? Any specific APIs? Or I need to do SQL instead?
3) If I duplicate custom types per-schema, to keep them standalone,
they'll get different OIDs, right?

Thanks for any insights on the above. --DD

Re: Number of updated rows with LibPQ

[Dominique Devienne]

On Wed, Oct 5, 2022 at 8:17 PM Tom Lane  wrote:
> Laurenz Albe  writes:
> > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> > Yes, you have to use PQcmdTuples(), and you have to convert the string to 
> > an integer.
> > But don't worry: the result will *not* be "INSERT 0 5", it will be just "5"

Thanks. What's the leading 0 though, then?
I guessed it might be the number of rows returned, but it isn't, see below:

postgres=# create table foo (id serial primary key, v int);
CREATE TABLE
postgres=# insert into foo (v) values (1), (2) returning id;
 id

  1
  2
(2 rows)

INSERT 0 2

> Yeah, just applying atoi() or atol() to the result should be enough.

Thanks too. Since I'm in C++, I used  instead, and
discovered it can be empty something, not 0.
I guess atoi() would have hidden that distinction, and worked anyway
(returning 0).

In the same topic, I've noticed an INSERT returns PGRES_COMMAND_OK,
while an INSERT-RETURNING returns PGRES_TUPLES_OK. So there's no way
to use the status to distinguish a SELECT from anything else? A RETURNING clause
makes any statement supporting it an hybrid of a command and a query,
but then how
does one know the exact "kind" of the statement?

E.g. So how does psql show INSERT in either cases? By parsing the SQL
itself, client-side?
Or is there a libpq API on PGresult that would allow to get the type
of statement the result is from?

Thanks, --DD

Re: Number of updated rows with LibPQ

[Guillaume Lelarge]

Le ven. 14 oct. 2022 à 13:52, Dominique Devienne  a
écrit :

> On Wed, Oct 5, 2022 at 8:17 PM Tom Lane  wrote:
> > Laurenz Albe  writes:
> > > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> > > Yes, you have to use PQcmdTuples(), and you have to convert the string
> to an integer.
> > > But don't worry: the result will *not* be "INSERT 0 5", it will be
> just "5"
>
> Thanks. What's the leading 0 though, then?
>

The leading number was the OID of the inserted row if you only had one row
and if the table had OID on rows. Otherwise, it was zero. It's always 0
nowadays since you can't have OID on rows.


-- 
Guillaume.

Re: Number of updated rows with LibPQ

[Laurenz Albe]

On Fri, 2022-10-14 at 13:52 +0200, Dominique Devienne wrote:
> On Wed, Oct 5, 2022 at 8:17 PM Tom Lane  wrote:
> > Laurenz Albe  writes:
> > > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> > > Yes, you have to use PQcmdTuples(), and you have to convert the string to 
> > > an integer.
> > > But don't worry: the result will *not* be "INSERT 0 5", it will be just 
> > > "5"
> 
> Thanks. What's the leading 0 though, then?
> I guessed it might be the number of rows returned, but it isn't, see below:
> 
> postgres=# create table foo (id serial primary key, v int);
> CREATE TABLE
> postgres=# insert into foo (v) values (1), (2) returning id;
>  id
> 
>   1
>   2
> (2 rows)
> 
> INSERT 0 2

That 0 is the OID of the newly inserted tuple.
Since there are no more tables WITH OIDS, the number is always 0, but
is left in the output for compatibility reasons.

> > Yeah, just applying atoi() or atol() to the result should be enough.
> 
> Thanks too. Since I'm in C++, I used  instead, and
> discovered it can be empty something, not 0.
> I guess atoi() would have hidden that distinction, and worked anyway
> (returning 0).
> 
> In the same topic, I've noticed an INSERT returns PGRES_COMMAND_OK,
> while an INSERT-RETURNING returns PGRES_TUPLES_OK. So there's no way
> to use the status to distinguish a SELECT from anything else? A RETURNING 
> clause
> makes any statement supporting it an hybrid of a command and a query,
> but then how
> does one know the exact "kind" of the statement?
> 
> E.g. So how does psql show INSERT in either cases? By parsing the SQL
> itself, client-side?
> Or is there a libpq API on PGresult that would allow to get the type
> of statement the result is from?

The command tag is not what you look at.

You simply check the result from an INSERT statement.  If it is PGRES_TUPLES_OK,
it must have been INSERT ... RETRUNING.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: [libpq] OIDs of extension types? Of custom types?

[Laurenz Albe]

On Fri, 2022-10-14 at 13:39 +0200, Dominique Devienne wrote:
> Hi. I'm using binary binds and results for DMLs, as well as for COPY.
> 
> So far, I've stayed within built-in scalar and array types, thus I
> could hardcode the OIDs of values "in the type-system" (in C++, via
> traits).
> But I'd like to venture into extension (e.g. hstore) and custom
> (enums, domain, etc...) types.
> Thus I'm wondering:
> 1) whether "official" extensions have fixed/stable OIDs, like in my
> hstore example. If so, where are they defined?
> 2) how should I be looking up OIDs for custom (or extension?) types
> with libpq? Any specific APIs? Or I need to do SQL instead?
> 3) If I duplicate custom types per-schema, to keep them standalone,
> they'll get different OIDs, right?

You use the #defines like TEXTOID for the built-in Oids, right?

For types from an extensions, you would run a query on "pg_type".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: does postgres has snapshot standby feature?

[Laurenz Albe]

On Fri, 2022-10-14 at 16:20 +0700, milist ujang wrote:
> read about removing standby.signal file behavior in pg14 from 
> https://dbaclass.com/article/how-to-open-postgres-standby-database-for-read-writesnapshot-standby/
> 
> I was shocked about the hidden feature of snapshot standby, does it really 
> exist?
> I could not find anything about snapshot standby in docs so far.

The referenced article talks about "pg_rewind", so look under that name.

> I have removed the standby.signal file in pg12 but got strange behavior until 
> I have to recreate the standby.

If you removed the file, you did something wrong.
You have to promote the standby properly.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: does postgres has snapshot standby feature?

[Christoph Moench-Tegeder]

## milist ujang (ujang.mil...@gmail.com):

> read about removing standby.signal file behavior in pg14 from
> https://dbaclass.com/article/how-to-open-postgres-standby-database-for-read-writesnapshot-standby/

That article is fractally wrong, and that starts right in the first
sentence. See
https://www.postgresql.org/docs/15/functions-info.html#FUNCTIONS-PG-SNAPSHOT
(not getting into detail discussions, but still).

Anyhow, the article does not discuss requirements and limitations of
pg_rewind, and promotes unsafe practices. After reading this article,
I'd be more than careful with anything else published by that source.

> I was shocked about the hidden feature of snapshot standby, does it really
> exist?

It's not an official feature. There's a limited amount of thing you can
get away with when breaking and rewinding replication, but the guarantees
on those are rather weak.

Regards,
Christoph

-- 
Spare Space.

Re: [libpq] OIDs of extension types? Of custom types?

[Dominique Devienne]

On Fri, Oct 14, 2022 at 2:31 PM Laurenz Albe  wrote:
> You use the #defines like TEXTOID for the built-in Oids, right?

I don't. I used
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
as a reference.
I suspect that should be fairly stable, right? I have at least 2 or 3
dozen OIDs pairs (scalar + array) of primitives
and other types (Oid, Name, Text, Bytea, Uuid, etc...). Are there
#defines for all of those? Where?

template<> struct OidTraits {
// boolean, true/false
static constexpr Type type{ "bool", 1, Oid{ 16 }, Oid{ 1000 } };
};

template<> struct OidTraits {
// variable-length string, binary values escaped
static constexpr Type type{ "bytea", -1, Oid{ 17 }, Oid{ 1001 } };
};

etc...

> For types from an extensions, you would run a query on "pg_type".

 OK, thanks.

Re: [libpq] OIDs of extension types? Of custom types?

[Tom Lane]

Dominique Devienne  writes:
> On Fri, Oct 14, 2022 at 2:31 PM Laurenz Albe  wrote:
>> You use the #defines like TEXTOID for the built-in Oids, right?

> I don't. I used
> https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
> as a reference.
> I suspect that should be fairly stable, right? I have at least 2 or 3
> dozen OIDs pairs (scalar + array) of primitives
> and other types (Oid, Name, Text, Bytea, Uuid, etc...). Are there
> #defines for all of those? Where?

They're stable, but writing magic numbers leads to unreadable code.
Use the macros from catalog/pg_type_d.h.

>> For types from an extensions, you would run a query on "pg_type".

>  OK, thanks.

In SQL queries, you can avoid hard-wiring anything by writing
things like "'hstore'::regtype".  It may or may not be possible
to avoid fetching the OID altogether that way.

regards, tom lane

Re: Number of updated rows with LibPQ

[Daniel Verite]

Laurenz Albe wrote:

> > Or is there a libpq API on PGresult that would allow to get the type
> > of statement the result is from?
> 
> The command tag is not what you look at.

Yet that's what psql does. from PrintQueryResult():

/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
if (last || pset.show_all_results)
{
cmdstatus = PQcmdStatus(result);
if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
strncmp(cmdstatus, "UPDATE", 6) == 0 ||
strncmp(cmdstatus, "DELETE", 6) == 0)
PrintQueryStatus(result, printStatusFout);
}



> You simply check the result from an INSERT statement.  If it is
> PGRES_TUPLES_OK, it must have been INSERT ... RETRUNING.

Or a SELECT, or an UPDATE RETURNING, or a DELETE RETURNING.
The code still need to look at PQcmdStatus() to learn which kind it is,
if it needs to know this.


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Re: How to return argument data type from sql function

[Tom Lane]

Andrus  writes:
> PostgreSQL 12.2+ function is defined as
>      create FUNCTION torus(eevarus text) returns text immutable AS $f$
>   select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
>      $f$ LANGUAGE SQL ;

> if char(n) column is passed as argument, torus() should also return 
> char(n) data type.

You can't preserve the length constraint, if that's what you're worried
about; we simply don't track those for function arguments or results.

> I tried to use bpchar instead on text

>      create or replace FUNCTION torusbpchar(eevarus bpchar) returns 
> bpchar immutable AS $f$
>   select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
>      $f$ LANGUAGE SQL ;

> torusbpchar(charcol) still returns text data type.

Making separate functions for text and bpchar works for me.

regression=# select pg_typeof(torus(f1)) from char_tbl;
 pg_typeof 
---
 character

Another possibility is to have just one function declared
to take and return anyelement.  You'd get failures at
execution if the actual argument type isn't coercible
to and from text (since translate() deals in text) but
that might be fine.

regards, tom lane

Re: [libpq] OIDs of extension types? Of custom types?

[Dominique Devienne]

On Fri, Oct 14, 2022 at 4:35 PM Tom Lane  wrote:
> > I don't. I used
> > https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.dat
>
> They're stable

Good to know, thanks.

> but writing magic numbers leads to unreadable code.
> Use the macros from catalog/pg_type_d.h.

OK. I see that header. But it seems to be a "server" header, not a client one.
I.e. I'm not sure it's a good idea to depend on such a header for pure
client-side libpq code.

> >> For types from an extensions, you would run a query on "pg_type".
> >  OK, thanks.
>
> In SQL queries, you can avoid hard-wiring anything by writing
> things like "'hstore'::regtype".  It may or may not be possible
> to avoid fetching the OID altogether that way.

Something like below you mean?
Thanks for the tip.

I do need to OIDs, on binds and results (defines in OCI speak), in my C++ code.
Because I try to enforce strong type safety between the C++ world, and
the libpq "bytes".
I don't check OIDs of the values on binds, I just give them to libpq
on execute().
But for results, I compare the actual OID (from PGresult) and the
expected OID from the C++ type (via the traits).

ddevienne=> select 'hstore'::regtype;
 regtype
-
 hstore
(1 row)


ddevienne=> select 'hstore'::regtype::oid;
oid
---
 207025799
(1 row)


ddevienne=> select 'uuid'::regtype::oid;
 oid
--
 2950
(1 row)


ddevienne=> select 'uuid[]'::regtype::oid;
 oid
--
 2951
(1 row)

Re: Number of updated rows with LibPQ

[Dominique Devienne]

On Fri, Oct 14, 2022 at 4:39 PM Daniel Verite  wrote:
> Or a SELECT, or an UPDATE RETURNING, or a DELETE RETURNING.
> The code still need to look at PQcmdStatus() to learn which kind it is,

Thanks Daniel. This is exactly what I needed. --DD

PS: Now I wonder whether PQcmdTuples() points to the same buffer as
PQcmdStatus(), except with an offset...

Re: How to return argument data type from sql function

[Andrus]

Hi!

Making separate functions for text and bpchar works for me.

regression=# select pg_typeof(torus(f1)) from char_tbl;
  pg_typeof
---
  character


I tried

create or replace FUNCTION torus(eevarus bpchar) returns bpchar 
immutable AS $f$

select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

create temp table test (
charcol char(10) );
insert into test values ('test');
select torus(charcol)
FROM Test

but it still returns result without trailing spaces. So it is not working.


Another possibility is to have just one function declared
to take and return anyelement.  You'd get failures at
execution if the actual argument type isn't coercible
to and from text (since translate() deals in text) but
that might be fine.


I tried

create or replace FUNCTION torus(eevarus anylement ) returns anylement 
immutable AS $f$

select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

but got error

type anyelement does not exists.

Finally I tried

create or replace FUNCTION torus(eevarus text ) returns text immutable 
AS $f$

select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

create or replace function public.ColWidth(p_namespace text, p_table 
text, p_field text)

    returns int as $f$
select atttypmod-4 from pg_namespace n, pg_class c, pg_attribute a
     where n.nspname = p_namespace and
     c.relnamespace = n.oid and
     c.relname = p_table and
     a.attrelid = c.oid and
     a.attname = p_field;
$f$ LANGUAGE SQL ;

create table public.test ( charcol char(10) );
insert into test values ('test');
select rpad ( torus(charcol),  colwidth('public', 'test', 'charcol') )
FROM Test

as Adrian Klaver recommends in

https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290

at this worked. In this best solution?

How to remove p_namespace  parameter from colwidth()? ColWidth() should 
return column width in first search_path table just like select ... from 
test finds table test.


Andrus.

Re: How to return argument data type from sql function

[Tom Lane]

Andrus  writes:
> I tried

> create or replace FUNCTION torus(eevarus bpchar) returns bpchar 
> immutable AS $f$
> select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
> $f$ LANGUAGE SQL ;

> but it still returns result without trailing spaces. So it is not working.

As I said, width constraints don't propagate through functions.

> I tried

> create or replace FUNCTION torus(eevarus anylement ) returns anylement 
> immutable AS $f$
> select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
> $f$ LANGUAGE SQL ;

> but got error

> type anyelement does not exists.

Might've helped to spell "anyelement" correctly ;-).  However, if you're
insistent on those trailing spaces, this approach won't change anything
about that.

> select rpad ( torus(charcol),  colwidth('public', 'test', 'charcol') )
> FROM Test

Yeah, you could do that if you have the column information at hand.

> How to remove p_namespace parameter from colwidth()?

select atttypmod-4 from pg_attribute
where attrelid = p_table::regclass and attname = p_field

Personally I'd also throw in "... and atttypid = 'bpchar'::regtype",
because that atttypmod calculation will give you garbage for types
other than bpchar and varchar.

regards, tom lane

Re: How to return argument data type from sql function

[David G. Johnston]

On Fri, Oct 14, 2022 at 2:00 PM Andrus  wrote:

> I tried
>
> create or replace FUNCTION torus(eevarus bpchar) returns bpchar immutable
> AS $f$
> select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
> $f$ LANGUAGE SQL ;
>
> but it still returns result without trailing spaces. So it is not working.
>
As was said, only the data type itself was going to be handled, not the
length.


> Another possibility is to have just one function declared
> to take and return anyelement.  You'd get failures at
> execution if the actual argument type isn't coercible
> to and from text (since translate() deals in text) but
> that might be fine.
>
> I tried
>
> create or replace FUNCTION torus(eevarus anylement ) returns anylement
> immutable AS $f$
> select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
> $f$ LANGUAGE SQL ;
>
> but got error
>
> type anyelement does not exists.
>
I'm inclined to believe that your code actually has the same typo you are
showing in this email - you spelled anyelement incorrectly.




> Finally I tried
>
> create or replace FUNCTION torus(eevarus text ) returns text immutable AS
> $f$
> select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
> $f$ LANGUAGE SQL ;
>
> create or replace function public.ColWidth(p_namespace text, p_table text,
> p_field text)
> returns int as $f$
> select atttypmod-4 from pg_namespace n, pg_class c, pg_attribute a
>  where n.nspname = p_namespace and
>  c.relnamespace = n.oid and
>  c.relname = p_table and
>  a.attrelid = c.oid and
>  a.attname = p_field;
> $f$ LANGUAGE SQL ;
>
> create table public.test ( charcol char(10) );
> insert into test values ('test');
> select rpad ( torus(charcol),  colwidth('public', 'test', 'charcol') )
> FROM Test
>
> as Adrian Klaver recommends in
>
>
> https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290
>
> at this worked. In this best solution?
>
Padding a text typed output with actual significant spaces "works"?  It is
not equivalent to a bpchar with insignificant padding spaces...

Using the system catalogs is probably required.  Though I imagine you could
create something like: text10 and text20 domains and enforce an explicit
length in their constraints.

There isn't too much out there to make this easy - it isn't exactly
considered desirable or useful to incorporate blank padding space into
data.  Most of us just pretend char(n) doesn't exist.  Frankly, varchar(n)
is the same - one can live a long and happy life with just text.

> How to remove p_namespace  parameter from colwidth()? ColWidth() should
> return column width in first search_path table just like  select ... from
> test finds table test.
>
Not sure on the full syntax but it probably involves doing something like:
table_name::regclass to get the OID and perform the lookup using that.

https://www.postgresql.org/docs/current/datatype-oid.html

David J.

Where to flag an issue with EDB's PG15 Windows installer?

[Anthony DeBarros]

Hi,

Where is the appropriate place to file a concern/issue with the EDB
installer -- StackBuilder in particular -- for PostgreSQL 15 on Windows?

Thanks,
Anthony DeBarros

Re: How to return argument data type from sql function

[Andrus]

Hi!

>Yeah, you could do that if you have the column information at hand.

Personally I'd also throw in "... and atttypid = 'bpchar'::regtype",
because that atttypmod calculation will give you garbage for types
other than bpchar and varchar.


I added this:

create or replace function public.ColWidth(p_namespace text, p_table 
text, p_field text)

    returns int as $f$
select atttypmod-4 from pg_namespace n, pg_class c, pg_attribute a
 where n.nspname = p_namespace and
    c.relnamespace = n.oid and
    c.relname = p_table and
    a.attrelid = c.oid and
    atttypid = 'bpchar'::regtype and
    a.attname = p_field;
$f$ LANGUAGE SQL ;

Tables with same name are in different schemas.

How to change this query so that it searches schemas in set search_path 
order and returns column width from it ? In this case p_namespace 
parameter can removed.


Or should it replaced with dynamic query like

execute 'select ' || p_field || ' from ' || p_table || ' limit 0'

and get column size from this query result somehow ?

Andrus.

Re: How to return argument data type from sql function

[Andrus]

> Adrian Klaver recommends in



https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290

at this worked. In this best solution?

Padding a text typed output with actual significant spaces "works"? It 
is not equivalent to a bpchar with insignificant padding spaces...


You are right. I need char(n) type and this is not working.  How to use 
expression in cast, like


select torus(charcol) :: CHAR( ColWidth('public', 'test', 'charcol')  ) 
from test


This throws error in Postgres. ColWidth is immutable and called with 
constant arguments so it should work. How to fix postgres to allow 
constant ColWidth() expression in cast ?


Andrus.

Re: How to return argument data type from sql function

[David G. Johnston]

On Fri, Oct 14, 2022 at 2:56 PM Andrus  wrote:

> select torus(charcol) :: CHAR( ColWidth('public', 'test',  'charcol')  )
> from test
>
> This throws error in Postgres. ColWidth is immutable and called with
> constant arguments so it should work. How to fix postgres to allow constant
> ColWidth() expression in cast ?
>
ColWidth is NOT IMMUTABLE, your declaration of that property is a lie (the
function in your email actually defines it as volatile though...).  It is
STABLE.

You are basically stuck dealing with this one layer up, outside the
server.  You would need to execute ColWidth then write the returned value
of the function call into the text body of the SQL Command.

David J.

Re: How to return argument data type from sql function

[Tom Lane]

Andrus  writes:
> How to change this query so that it searches schemas in set search_path 
> order and returns column width from it ? In this case p_namespace 
> parameter can removed.

I showed you that already: regclass will take care of it.

regards, tom lane

Re: Where to flag an issue with EDB's PG15 Windows installer?

[Adrian Klaver]

On 10/14/22 14:45, Anthony DeBarros wrote:

Hi,

Where is the appropriate place to file a concern/issue with the EDB 
installer -- StackBuilder in particular -- for PostgreSQL 15 on Windows?


The installer is maintained by EDB so they will need to be notified. 
Your choices:


1) From here:

https://www.postgresql.org/download/windows/

webmas...@enterprisedb.com

Though I have never actually received a response from there.

2) From here:

https://www.enterprisedb.com/contact/support

techsupp...@enterprisedb.com

Not sure if the above covers non-paid tech support.

3) List your concerns/issues in a message to this mailing list and see 
if someone from EDB picks it up.




Thanks,
Anthony DeBarros


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

Zheap Tech Problem

[jacktby]

What's Zheap tech? Can you give me some details or stuff to study? and which version will be realized in ?








 


jacktbyjack...@gmail.com


 

Re: Zheap Tech Problem

[Adrian Klaver]

On 10/14/22 18:59, jacktby wrote:
What's Zheap tech? Can you give me some details or stuff to study? and 
which version will be realized in ?


1) Go to search engine of choice.

2) Enter zheap

3) Pick  a link from something like:

https://duckduckgo.com/?t=ffsb&q=zheap&ia=web




jacktby
jack...@gmail.com




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

Re: Zheap Tech Problem

[Michael Paquier]

On Fri, Oct 14, 2022 at 07:53:17PM -0700, Adrian Klaver wrote:
> On 10/14/22 18:59, jacktby wrote:
>> What's Zheap tech? Can you give me some details or stuff to study? and
>> which version will be realized in ?

There are a few videos on youtube that can provide some insight about
all that, mainly from EDB:
https://www.youtube.com/watch?v=ZbdWOuTTWrw
https://www.youtube.com/watch?v=7jVz4TTB5-4

The community wiki has a page with few references to the code of the
project:
https://wiki.postgresql.org/wiki/Zheap
--
Michael


signature.asc
Description: PGP signature

Re: Attaching database

[Igor Korot]

Hi, guys,

On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer  wrote:
>
> On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> > po 19. 10. 2020 v 20:18 odesílatel Igor Korot  napsal:
> > On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule 
> > wrote:
> > > It is a different thing - postgresql_fdw does nested connect - it uses
> > > client-server protocol.
> > >
> > > For postgres connect and sql engine process is one entity - and 
> > engine is
> > > written without a possibility to reconnect to another database.
> >
> > So if I understand correctly the postgresql_fdw is creating a second
> > connection and uses it as kind of "virtualizing mechanism"
> > in order to give access to the second database.
> >
> >
> > yes - it does new connect to somewhere (local or remote server, but 
> > mechanism
> > is absolutely same)
>
> In case this isn't clear:
>
> It is the server (or more specifically, the foreign data wrapper) which
> opens that connection. To the client it looks like it's just accessing a
> normal table within the same database.

Sorry for resurrecting this old thread...
If an attaching the DB creates new connection which will be cmpletely
independent - how the INFORMATION_SCHEMA.table@table_catalog
field is handled.

Lets say I open connection to the DB (finance) and then attached another DB
(finance_2021).

So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
I will get all tables from (finance) DB only.
And to get all tables from (finance_2021) I need to make this catalog current
and reissue the query.

Am I right?

Thank you.

>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"

Re: Attaching database

[David G. Johnston]

On Fri, Oct 14, 2022 at 9:17 PM Igor Korot  wrote:

> Hi, guys,
>
> On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer  wrote:
> >
> > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
>
> > In case this isn't clear:
> >
> > It is the server (or more specifically, the foreign data wrapper) which
> > opens that connection. To the client it looks like it's just accessing a
> > normal table within the same database.
>
> Sorry for resurrecting this old thread...
>

Then why did you do it?  You couldn't send a new email without copying
possibly no longer interested people and with better thought out
self-contained content that simply notes you are somehow using an FDW.


> If an attaching the DB creates new connection which will be cmpletely
> independent - how the INFORMATION_SCHEMA.table@table_catalog
> field is handled.
>
> Lets say I open connection to the DB (finance) and then attached another DB
> (finance_2021).
>
> So, when I call SELECT table_schema, table_name FROM
> INFORMATION_SCHEMA.table
>

Call this how exactly? There are three information_schema instances that
you can be talking about, though only the one in the local database is
going to be called that.  If you are dealing with FDWs you would have to
have different names involved.


> I will get all tables from (finance) DB only.
> And to get all tables from (finance_2021) I need to make this catalog
> current
> and reissue the query.
>
> Am I right?
>

Do it and find out?  Then if still confused, show what you attempted.  But
I don't know what this concept of "make the catalog current" you speak of
comes from.  That isn't a thing that I am aware of.  Where do you see this
documented?

David J.

Re: Attaching database

[Igor Korot]

Hi, David et al,

On Fri, Oct 14, 2022 at 11:39 PM David G. Johnston
 wrote:
>
> On Fri, Oct 14, 2022 at 9:17 PM Igor Korot  wrote:
>>
>> Hi, guys,
>>
>> On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer  wrote:
>> >
>> > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
>>
>> > In case this isn't clear:
>> >
>> > It is the server (or more specifically, the foreign data wrapper) which
>> > opens that connection. To the client it looks like it's just accessing a
>> > normal table within the same database.
>>
>> Sorry for resurrecting this old thread...
>
>
> Then why did you do it?  You couldn't send a new email without copying 
> possibly no longer interested people and with better thought out 
> self-contained content that simply notes you are somehow using an FDW.
>
>>
>> If an attaching the DB creates new connection which will be cmpletely
>> independent - how the INFORMATION_SCHEMA.table@table_catalog
>> field is handled.
>>
>> Lets say I open connection to the DB (finance) and then attached another DB
>> (finance_2021).
>>
>> So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
>
>
> Call this how exactly? There are three information_schema instances that you 
> can be talking about, though only the one in the local database is going to 
> be called that.  If you are dealing with FDWs you would have to have 
> different names involved.
>
>>
>> I will get all tables from (finance) DB only.
>> And to get all tables from (finance_2021) I need to make this catalog current
>> and reissue the query.
>>
>> Am I right?
>
>
> Do it and find out?  Then if still confused, show what you attempted.  But I 
> don't know what this concept of "make the catalog current" you speak of comes 
> from.  That isn't a thing that I am aware of.  Where do you see this 
> documented?

Making catalog current means switching between DBs.
Remember initially I connected to (finance) DB, which made the (finance) catalog
current.
Then I "opened a second connection" to (finance_2021), which made
that current catalog and so that select would give me all tables from
(finance_2021).

I hope now its clearer.

Thank you.

>
> David J.

Re: Attaching database

[David G. Johnston]

On Fri, Oct 14, 2022 at 9:47 PM Igor Korot  wrote:

>
> Making catalog current means switching between DBs.
> Remember initially I connected to (finance) DB, which made the (finance)
> catalog
> current.
> Then I "opened a second connection" to (finance_2021), which made
> that current catalog and so that select would give me all tables from
> (finance_2021).
>
> I hope now its clearer.
>

No, it is not clearer.  I suggest you show some code.  Given what you've
said my guess is you are not correct.

David J.

Re: Attaching database

[Julien Rouhaud]

Hi,

On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote:
>
> Sorry for resurrecting this old thread...
> If an attaching the DB creates new connection which will be cmpletely
> independent - how the INFORMATION_SCHEMA.table@table_catalog
> field is handled.
>
> Lets say I open connection to the DB (finance) and then attached another DB
> (finance_2021).
>
> So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
> I will get all tables from (finance) DB only.
> And to get all tables from (finance_2021) I need to make this catalog current
> and reissue the query.
>
> Am I right?

No.  In postgres, databases are completely disjoint containers and once you
have a connection on a given database it will stay on that database, there's no
way around that.

Using postgres_fdw allows you to create a local table that will point to
another table, possibly on another database or even another server, but it will
still be a (foreign) table, that has to be created in the current database in
the schema of your choice.

Depending on your use case, maybe what you could do is create a finance_2021
schema, and create all the foreign tables you need in that schema pointing to
the finance_2021 database.  Any table existing in both "finance" and
"finance_2021" will then be visible in information_schema.tables, with a
different table_schema.  If you have multiple schemas in each database, then
find a way to make it work, maybe adding a _2021 suffix on each schema or
something like that.

You can then maybe use the search_path (see
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) to
use by default one of the set of tables rather than the other.  But in any
case, it's only a workaround that has to be implemented on your client, as you
will always be connected on the same database, and see both set of object in
information_schema.