PostgreSQL general set of Questions.

[David G. Johnston]

On Sunday, August 8, 2021, A Z  wrote:
>
> 2) How may I get PostgreSQL to output the create table statement(s) for
> one or more tables inside one database, without issuing instructions via
> the command line, but only inside a database login, as a query or pl/sql?
> If not possible, what can you do at the command line, outside
> of PostgreSQL?
>

Asked and answered


>
> 3) I know that I can use COPY to import or export one database table
> between it and a *.csv file. Can I use it to do this with multiple TABLES
> and *.csv files specified in one COPY COMMAND, or not?
>

Read the docs, if it doesn’t say you can, you can’t.


>
> 4) In the absence of OS command line instructions, is there an internal
> PostgreSQL way, via COPY or another function for example, to backup an
> entire database, with all it's create table statements and all insert
> statements, and any other associated objects as well, in one hit?
> Or is this ill-advised?
>

Asked and answered


>
> 5) When setting up communication to remote databases on remote machines, I
> need to use the OPTIONS() function. It seems to require as its first
> function parameter, the schema of the table (the second parameter) that it
> wants to access. Can I supply a null schema, and still be able to reference
> the remote table, or must I also make use of IMPORT FOREIGN SCHEMA?
>

Asked and answered


>
> 6) How may I access, via a query, the log for the details of a normal
> table, or similar?
>

Asked and answered.


>
> 7) I have found that the native trigonometry functions, namely the radians
> versions, do produce error results around key trigonometry input values. I
> have discovered that these errors persist, even if I cast the input
> parameter away from DOUBLE PRECISION and into DECIMAL. I would like to know
> if there are any freely available scripts out there that include Arbitrary
> Precision mathematical functions support that calls on DECIMAL and not on
> DOUBLE PRECISION, that do not produce any error values or amounts around
> key inputs? Could someone refer me to a website that has a script that is
> such?
>

I suggest starting a dedicated thread for this demonstrating the perceived
problem, maybe we can fix it or explain why it happens.

If you want to discuss any of the other questions in more depth reply to
the original thread, quote the replies you did get, and ask related
questions.

David J.

Re: PostgreSQL general set of Questions.

[Vijaykumar Jain]

On Mon, 9 Aug 2021 at 12:14, A Z  wrote:

> 1) Are there free scripts for CREATE TYPE (native type), more advanced,
>  or  sorts of types out there, online, free for commercial
> use? With function support, too? Can someone reply with a link or a
> suggestion?
>
> PostgreSQL: Documentation: 13: Chapter 8. Data Types

PostgreSQL: Documentation: 13: CREATE TYPE

postgres/create_type.sql at master · postgres/postgres (github.com)




> 2) How may I get PostgreSQL to output the create table statement(s) for
> one or more tables inside one database, without issuing instructions via
> the command line, but only inside a database login, as a query or pl/sql?
> If not possible, what can you do at the command line, outside
> of PostgreSQL?
>

postgres/describe.c at f68b609230689f9886a46e5d9ab8d6cdd947e0dc ·
postgres/postgres (github.com)

what is your goal? you can clone a table schema using  *create table*
create table y (like x INCLUDING ALL);
PostgreSQL: Documentation: 13: CREATE TABLE



>
> 3) I know that I can use COPY to import or export one database table
> between it and a *.csv file. Can I use it to do this with multiple TABLES
> and *.csv files specified in one COPY COMMAND, or not?
>

it should be fairly easy to script it though,

do $$
declare tbl text; stmt text;
begin
for tbl in select table_name from information_schema.tables where
table_name in ('aa', 'bb', 'cc') loop
stmt = format($_$ copy %I TO '/tmp/%s'; $_$, tbl, tbl);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:   copy aa TO '/tmp/aa';
NOTICE:   copy bb TO '/tmp/bb';
NOTICE:   copy cc TO '/tmp/cc';
DO
postgres=# \! cat /tmp/aa
1
postgres=# \! cat /tmp/bb
1
postgres=# \! cat /tmp/cc
1



> 4) In the absence of OS command line instructions, is there an internal
> PostgreSQL way, via COPY or another function for example, to backup an
> entire database, with all it's create table statements and all insert
> statements, and any other associated objects as well, in one hit?
> Or is this ill-advised?
>
>
i saw some tools doing schema sync, but they too used pg_dump cmd line
ankane/pgsync: Sync data from one Postgres database to another (github.com)


also, its all there in the source though,
postgres/pg_dump.c at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)

If you really want that bad, you can enable log all statements,
and run a pg_dump or psql \d+ and see what sql is generated for that pid,
and start building up on it.




> 5) When setting up communication to remote databases on remote machines, I
> need to use the OPTIONS() function. It seems to require as its first
> function parameter, the schema of the table (the second parameter) that it
> wants to access. Can I supply a null schema, and still be able to reference
> the remote table, or must I also make use of IMPORT FOREIGN SCHEMA?
>
I think this is wrt fdw. What is your goal here?
I am not sure what you want would make sense. there can be 100s of schema
on remote server. there might be same table on diff schemas.
how would your import know, which one to use etc.
postgres/postgres_fdw.sql at c30f54ad732ca5c8762bb68bbe0f51de9137dd72 ·
postgres/postgres (github.com)




> 6) How may I access, via a query, the log for the details of a normal
> table, or similar?
>
What log? an example of what you want would be more helpful. even if it
does not work or exists, you can hypothetically run some command and say
when i `run this command|query`
i should `get this output`
but i  `get this output`



> 7) I have found that the native trigonometry functions, namely the radians
> versions, do produce error results around key trigonometry input values. I
> have discovered that these errors persist, even if I cast the input
> parameter away from DOUBLE PRECISION and into DECIMAL. I would like to know
> if there are any freely available scripts out there that include Arbitrary
> Precision mathematical functions support that calls on DECIMAL and not on
> DOUBLE PRECISION, that do not produce any error values or amounts around
> key inputs? Could someone refer me to a website that has a script that is
> such?
>

imho, your questions where you suggest you have found something not as
expected. It would be best if you could simulate the same on
DB Fiddle - SQL

Partitioning a table by integer value (preferably in place)

[Pól Ua Laoínecháin]

Hi all,


Linux Fedora 34
1TB Samsung SSD
4 CPUs, 2 cores

PostgreSQL 12.7 (can upgrade if a better solution is to be found in 13
or even 14 beta2 - currently testing a proposed solution, so by the
time it's fully implemented, 14 should be on GA and hey, I might even
find a bug or two to help with  the project!).

I have a 400GB joining table (one SMALLINT and the other INTEGER -
Primary Keys on other tables) with 1000 fields on one side and 10M on
the other, so 10,000M (or 10Bn) records all told.

What I would like to do is to partition by the SMALLINT (1 - 1000)
value - which would give 1,000 tables of 400MB each.

I wish to avoid having to do this manually 1,000 times - is there a
way of telling PostgreSQL to partition by value without specifying the
SMALLINT value each time?

I looked here:

https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql

and there is LIST, RANGE and HASH partitioning.

I think a RANGE of 1 would be what I want here?

So, questions;

Is 1,000 partitions reasonable? This:

https://elephas.io/is-there-a-limit-on-number-of-partitions-handled-by-postgres/

appears to suggest that it shouldn't be a problem?


Could I go with a RANGE of, say, 10 values per partition? If I have to
explicitly code, I'd prefer this for my test - at least it would save
on the typing! :-)

This would product 100 tables of ~ 4GB each. Would I see much
performance degradation with a 4GB table on an SSD?

Finally, the icing on the cake would be if this could be done in place
- my SSD is 1TB and the output from df -h is:

test=# \! df -h
Filesystem   Size  Used Avail Use% Mounted on
devtmpfs  16G 0   16G   0% /dev
tmpfs 16G  212K   16G   1% /dev/shm
tmpfs6.3G  1.8M  6.3G   1% /run
/dev/mapper/fedora_localhost--live-root   69G   11G   55G  17% /
tmpfs 16G  284K   16G   1% /tmp
/dev/sda5976M  192M  718M  22% /boot
/dev/mapper/fedora_localhost--live-home  1.3T  898G  270G  77% /home
/dev/sda2 96M   52M   45M  54% /boot/efi
tmpfs3.2G   96K  3.2G   1% /run/user/1000
test=#


So, I only have 270 GB left on disk - and it took > 12 Hrs to fill it
with indexes and Foreign Key constraints so I'd like to be able to do
it without having to go through that again.

This:

https://www.2ndquadrant.com/en/blog/partitioning-a-large-table-without-a-long-running-lock/

appears to suggest that it can be done online. There will be no other
activity on the table while any partitioning &c. will be ongoing.
However, the article makes no mention of space considerations.

This is my first time considering partitioning, so I'd be grateful for
any advice, pointers, references, URLs &c and please let me know
if I"ve left out any important information.


TIA and rgs,


Pól...


Some (relevant?) settings;

max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

as suggested by pgtune. Is pgtune a good bet for configuration suggestions?

Re: Partitioning a table by integer value (preferably in place)

[Pól Ua Laoínecháin]

Hi again all,

Just a quick follow-up - could I script the creation of 1000
partitions using bash or PL/pgSQL? I think (rightly/wrongly?) that
this may be the best solution?

I've found samples on the web, but they are for partitioning by date -
a quick sample by integer would be gratefully received!

Is partman recommended by the community?

TIA and rgs,

Pól...

Re: Partitioning a table by integer value (preferably in place)

[Ron]

On 8/9/21 9:14 AM, Pól Ua Laoínecháin wrote:

Hi again all,

Just a quick follow-up - could I script the creation of 1000
partitions using bash


Sure.  That's what scripting languages are for.


  or PL/pgSQL? I think (rightly/wrongly?) that
this may be the best solution?

I've found samples on the web, but they are for partitioning by date -
a quick sample by integer would be gratefully received!

Is partman recommended by the community?


--
Angular momentum makes the world go 'round.

Re: Partitioning a table by integer value (preferably in place)

[Vijaykumar Jain]

>
>
>
> I have a 400GB joining table (one SMALLINT and the other INTEGER -
> Primary Keys on other tables) with 1000 fields on one side and 10M on
> the other, so 10,000M (or 10Bn) records all told.
>

My queries:

>
> Do you have any explain analyze,buffers

  results with the existing setup? Does it look problematic?

How would your table grow on either side of the join ? Append only, static
data or too frequently updated etc, or dropped periodically, so that delete
based bloating can be skipped completely.

How distributed is the data based on smallint keys, equally or unequally.
What kind of queries would be run and results returned ? Oltp or olap like
? Quick queries with few rows retuned or heavy queries with lot of rows
returned.

Partitioning has been ever improving, so the best option if possible would
be to use the latest pg version is possible,.
Also is there any scope of normalisation of that table, I mean I know
theoretically it is possible, but I have not seen any design with that wide
table( of 1000 cols), so would be good to know.

Just asking, maybe partitioning would the best option but wanting to
know/see the benefit pre and post partitioning.

>
>
-- 
Thanks,
Vijay
Mumbai, India

Re: JWT decoder

[Mladen Gogala]

Hmmm, Pl/Python and Pl/Perl are languages usable from within Postgres. 
You can write Python functions in Postgres. I apologize for not making 
that clear.


On 8/9/21 1:15 AM, Masih Tavassoli wrote:

There are lots of decoders but I need to do it within postgresql.

On Monday, August 9, 2021, 01:24:33 PM GMT+10, Mladen Gogala 
 wrote:



You could probably use Pl/Python. Python has base64 module and urllib 
module which can deal with all URL strings I am aware of.  Pl/Perl 
would probably do as well.



--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Bryn Llewellyn]

The problem that I report here seems to be known and seems, too, to astonish 
and annoy users. It's a bare "computer says No". It's hard to find anything of 
ultimate use with Google search (either constrained to the PG doc or 
unconstrained). Here's an example on stackoverflow: 

https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter

However, it does give the _clue_ to the workaround.

Here's an illustration of the issue, starting with what works fine. I tested in 
using PG 13.3.

create type rect as (h int, w int);
create table t1(k int primary key, r1 rect not null);
insert into t1(k, r1) values(1, (10, 20));

do $body$
declare
  r rect;
begin
  r := (
select r1
from t1
where k = 1);
  raise info '%', r::text;
end;
$body$;

The "raise info" shows what you'd expect.

This re-write fails. It simply uses the approach that anybody who hasn't yet 
been bitten by this would expect to work.

do $body$
declare
  r rect;
begin
  select r1 -- line 5
  into r
  from t1
  where k = 1;
end;
$body$;

This is the error:

22P02: invalid input syntax for type integer: "(10,20)" ... at line 5

With "VERBOSITY" set to "verbose", there's not hint to tell you what the 
problem is and how to work around it.

Question 1.
---
Where, in the PL/pgSQL doc, does it state that "select col into var" when col 
is a user-defined type doesn't work—and where is the viable approach shown?


Question 2.
---
If I can easily re-write a failing approach by hand (once I know that I must) 
so that it works, why cannot the PL/pgSQL compiler do this under the covers?

(13.1) pg_basebackups ./. pg_verifybackup

[Matthias Apitz]

Hello,

We do backups with something like:

$ pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}

and they seems to work fine:

$ ls -l backup-20210809-1/
total 7656108
-rw--- 1 postgres postgres4191873 Aug  9 14:19 backup_manifest
-rw--- 1 postgres postgres 7835635150 Aug  9 14:19 base.tar.gz
-rw--- 1 postgres postgres  17671 Aug  9 14:19 pg_wal.tar.gz

But, if I verify the backup with

$ pg_verifybackup /data/postgresql13/backup-20210809-1

it gives thousands of error messages like

pg_verifybackup: error: "base/1032792/1034229" is present in the manifest but 
not on disk
pg_verifybackup: error: "base/1076178/1101524" is present in the manifest but 
not on disk

and at the end:

pg_waldump: error: could not open directory 
"/data/postgresql13/backup-20210809-1/pg_wal": No such file or directory
pg_verifybackup: error: WAL parsing failed for timeline 1

The base files are there:

$ find /data/postgresql13 -name 1101524
/data/postgresql13/data/base/1076178/1101524

$ find /data/postgresql13 -name pg_wal
/data/postgresql13/data/pg_wal

What we do wrong here with pg_verifybackup?

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
27.7.1900: Kaiser Wilhelm II Deutsches Ostasiatisches Expeditionskorps,   
target China
02.8.2021: Deutsche Kriegsministerin Kramp-Karrenbauer Fregatte "Bayern", 
target China

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Adrian Klaver]

On 8/9/21 1:15 PM, Matthias Apitz wrote:

Hello,

We do backups with something like:

$ pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}

and they seems to work fine:

$ ls -l backup-20210809-1/
total 7656108
-rw--- 1 postgres postgres4191873 Aug  9 14:19 backup_manifest
-rw--- 1 postgres postgres 7835635150 Aug  9 14:19 base.tar.gz
-rw--- 1 postgres postgres  17671 Aug  9 14:19 pg_wal.tar.gz

But, if I verify the backup with

$ pg_verifybackup /data/postgresql13/backup-20210809-1

it gives thousands of error messages like

pg_verifybackup: error: "base/1032792/1034229" is present in the manifest but 
not on disk
pg_verifybackup: error: "base/1076178/1101524" is present in the manifest but 
not on disk

and at the end:

pg_waldump: error: could not open directory 
"/data/postgresql13/backup-20210809-1/pg_wal": No such file or directory
pg_verifybackup: error: WAL parsing failed for timeline 1

The base files are there:

$ find /data/postgresql13 -name 1101524
/data/postgresql13/data/base/1076178/1101524

$ find /data/postgresql13 -name pg_wal
/data/postgresql13/data/pg_wal

What we do wrong here with pg_verifybackup?


Not read the docs?:

https://www.postgresql.org/docs/13/app-pgverifybackup.html

"pg_verifybackup is used to check the integrity of a database cluster 
backup taken using pg_basebackup against a backup_manifest generated by 
the server at the time of the backup. The backup must be stored in the 
"plain" format; a "tar" format backup can be checked after extracting it."




Thanks

matthias




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

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Matthias Apitz]

El día lunes, agosto 09, 2021 a las 01:32:58p. m. -0700, Adrian Klaver escribió:

> > 
> > What we do wrong here with pg_verifybackup?
> 
> Not read the docs?:
> 
> https://www.postgresql.org/docs/13/app-pgverifybackup.html
> 
> "pg_verifybackup is used to check the integrity of a database cluster backup
> taken using pg_basebackup against a backup_manifest generated by the server
> at the time of the backup. The backup must be stored in the "plain" format;
> a "tar" format backup can be checked after extracting it."

I've read exactly this page, but missed the sentence about "tar" format
because I jumped to fast to the options sections. Sorry, my fault.

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
27.7.1900: Kaiser Wilhelm II Deutsches Ostasiatisches Expeditionskorps,   
target China
02.8.2021: Deutsche Kriegsministerin Kramp-Karrenbauer Fregatte "Bayern", 
target China

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[John W Higgins]

On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn  wrote:

>
> *https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter
> *
>
> *Question 1.*
> *---*
> *Where, in the PL/pgSQL doc, does it state that "select col into var" when
> col is a user-defined type doesn't work—and where is **the viable
> approach **shown?*
>
>
The first paragraph of the SO answer completely explains why this occurs.

However, the following 2 locations explain how we get here

1) https://www.postgresql.org/docs/current/rowtypes.html

Opening sentence of that page.

"A *composite type* represents the structure of a row or record;"

2) https://www.postgresql.org/docs/current/plpgsql-statements.html -
Section 42.5.3

"The result of a SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables."

You did not provide a scalar variable - you provided a composite type -
which equates to a record/row-type variable and therefore, as described,
the engine tried to place each column returned into a column of your
composite type. Therefore the first column of the select result is placed
in the first column of your composite type - and you get an error.

It would seem rather clear that a sentence discussing composite types is
very much an option here in 42.5.3 to clarify it further given your
confusion today.


> *Question 2.*
> *---*
> *If I can easily re-write a failing approach by hand (once I know that I
> must) so that it works, why cannot the PL/pgSQL compiler do this under the
> covers?*
>

First, there is no need to not write the select almost the way you
initially tried. The following works just fine.

 select (r1).h, (r1).w
  into r
  from t1
  where k = 1;

The engine sees a composite type as the receiver and places the data in it
as it is supposed to.

As another example, switch out your do with the following and it works
fine. It's not user-defined types - but rather how they work.

create type rect_bucket as (r rect);

do $body$
declare
  r rect_bucket;
begin
  select r1
  into r
  from t1
  where k = 1;
end;

$body$;

Since the composite type is a single column of rect type - the select into
works - as does the variant I showed earlier.

John

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Pavel Stehule]

po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn  napsal:

> *The problem that I report here seems to be known and seems, too, to
> astonish and annoy users. It's a bare "computer says No". **It's hard to
> find anything of ultimate use with Google search (either constrained to the
> PG doc or unconstrained). Here's an example on stackoverflow: *
>
> *https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter
> *
>
> *However, it does give the _clue_ to the workaround.*
>
> *Here's an illustration of the issue, starting with what works fine. I
> tested in using PG 13.3.*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *create type rect as (h int, w int);create table t1(k int primary key, r1
> rect not null);insert into t1(k, r1) values(1, (10, 20));do
> $body$declare  r rect;begin  r := (select r1from t1where k =
> 1);  raise info '%', r::text;end;$body$;*
>
> *The "raise info" shows what you'd expect.*
>
> *This re-write **fails.** It simply uses the approach that anybody who
> hasn't yet been bitten by this would expect to work.*
>
>
>
>
>
>
>
>
>
>
>
>
> *do $body$declare  r rect;begin  select r1 -- line 5  into r  from
> t1  where k = 1;end;$body$;*
> *This is the error:*
>
>
> *22P02: invalid input syntax for type integer: "(10,20)" ... at line 5*
>
> *With "**VERBOSITY" set to "verbose", there's not hint to tell you what
> the problem is and how to work around it.*
>

This is true. There is no possibility to list source code with line
numbers, because anonymous blocks are not persistent. The most simple way
is creating simple function from your example

postgres=# \sf+ fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS void
 LANGUAGE plpgsql
1   AS $function$
2   declare
3 r rect;
4   begin
5 select r1 -- line 5
6 into r
7 from t1
8 where k = 1; raise notice '%', r;
9   end;
10  $function$

postgres=# select fx();
ERROR:  invalid input syntax for type integer: "(10,20)"
CONTEXT:  PL/pgSQL function fx() line 5 at SQL statement




>
> *Question 1.*
> *---*
> *Where, in the PL/pgSQL doc, does it state that "select col into var" when
> col is a user-defined type doesn't work—and where is **the viable
> approach **shown?*
>
>
The problem is in implicit build of composite values. Postgres knows two
types - scalars and composites. The result of the query is always tuple,
and there are different rules when the target is composite or when the
target is scalar. Unfortunately, until execution the PLpgSQL engine has no
idea what type of expression will result. In your case, PLpgSQL got a
tuple, and try to make a composite value, because the target is a composite
value.

postgres=# do $body$
declare
  r rect;
begin
  select 10, 20
  into r;
  raise notice '%', r;
end;
$body$
;

NOTICE:  (10,20)
DO

But same mechanism break your example -

postgres=# do $body$
declare
  r rect;
begin
  select (10, 20)
  into r;
  raise notice '%', r;
end;
$body$
;
ERROR:  invalid input syntax for type integer: "(10,20)"
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

when you replace rect type by record type, you can see result

postgres=# do $body$
declare
  r record;
begin
  select (10, 20)::rect
  into r;
  raise notice '%', r;
end;
$body$
;
NOTICE:  ("(10,20)")
DO

The result has a nested rect type. The solution is easy - you can unpack
composite value, and assign it

postgres=# do $body$
declare
  r record;
begin
  select ((10, 20)::rect).*
  into r;
  raise notice '%', r;
end;
$body$
;
NOTICE:  (10,20)
DO

or your example
postgres=# do $body$
declare
  r rect;
begin
  select (r1).* -- line 5
  into r
  from t1
  where k = 1; raise notice '%', r;
end;
$body$;
NOTICE:  (10,20)
DO



>
> *Question 2.*
> *---*
> *If I can easily re-write a failing approach by hand (once I know that I
> must) so that it works, why cannot the PL/pgSQL compiler do this under the
> covers?*
>
>
The compiler checks just SQL syntax, but doesn't check semantic
(identifiers). At compile time, the referred objects should not exist. So
there is not any information about query results at compile time. The
database objects have to exist before execution. There are advantages (and
disadvantages) of this design. PL/pgSQL should not use forward declarations
- and the relations between database objects and code are not too strong
(PLpgSQL is much more dynamic than PL/SQL). On the other hand, some errors
can be detected at runtime only. And because both sides are composite,
plpgsql tries to run dynamic IO conversions, and it fails.

Although PL/pgSQL looks like PL/SQL, it is an absolutely different
technology. PL/SQL is a classic compiler based environment with strong type
checking - and composite types have to be known at compile time. PL/pgSQL
is interpreted environment,

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Tom Lane]

Pavel Stehule  writes:
> Some errors like this, but not this can be detected by plpgsql_check
> https://github.com/okbob/plpgsql_check - probably the heuristic for type
> check is not complete.

STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late.  We'd need to count the fields
*before* trying to assign values, not after.

In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.

regards, tom lane

archive_command / single user mode

[Christophe Pettus]

Does archive_command run in single user mode?  This isn't an actual situation 
at the moment, but for runbook / disaster planning purposes, I was curious.

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> pavel.steh...@gmail.com writes:
>> 
>> Some errors like this, but not this can be detected by plpgsql_check
>> https://www.google.com/url?q=https://github.com/okbob/plpgsql_check&source=gmail-imap&ust=162914843400&usg=AOvVaw3f9UAP7RvDPC2QKi3_4Mj0
>>  - probably the heuristic for type
>> check is not complete.
> 
> STRICTMULTIASSIGNMENT would detect most cases of this, except that the 
> condition is checked too late. We'd need to count the fields
> *before* trying to assign values, not after.
> 
> In the meantime, it does seem like the docs could be more explicit about 
> this, and perhaps give an example showing the (x).* solution.

Tom, Pavel, and John, thanks for your quick responses. I've filed them all away 
and I'm hoping that I won't be caught out by this in the future.

It now seems to me to be odd, in the light of the explanations for why the 
naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a scalar 
subquery to a variable of the composite type in question _does_ work! But don't 
take that as a question. I'm going to regard this as "case closed".

Re: archive_command / single user mode

[David G. Johnston]

On Mon, Aug 9, 2021 at 4:17 PM Christophe Pettus  wrote:

> Does archive_command run in single user mode?  This isn't an actual
> situation at the moment, but for runbook / disaster planning purposes, I
> was curious.
>
>
Nope.

If I follow the source code correctly...main.c calls PostgresMain
(postgres.c) which is basically a hybrid front-end/back-end standalone
setup which, as far as I can tell, never launches the archiver.  The "else"
PostmasterMain (postmaster.c) call in main.c, however, leads to ServerLoop
which starts, and restarts if necessary, the archiver based upon the
current state machine state (e.g., PM_RUN).

Skimming the imports for postgres.c likewise confirms an omission of
pgarch.h ...

David J.

Serious List of PostgreSQL usage questions.

[A Z]

1) Are there free scripts for CREATE TYPE (native type), more advanced,  
or  sorts of types out there, online, free for commercial use? With 
function support, too? Can someone reply with a link or a suggestion?

2) How may I get PostgreSQL to output the create table statement(s) for one or 
more tables inside one database, without issuing instructions via the command 
line, but only inside a database login, as a query or pl/sql?

3) I know that I can use COPY to import or export one database table between it 
and a *.csv file. Can I use it to do this with multiple TABLES and *.csv files 
specified in one COPY COMMAND, or not?

4) In the absence of OS command line instructions, is there an internal 
PostgreSQL way, via COPY or another function for example, to backup an entire 
database, with all it's create table statements and all insert statements, and 
any other associated object, in one hit? Or is this ill-advised?

5) When setting up communication to remote databases on remote machines, I need 
to use the OPTIONS() function. It seems to require as its first function 
parameter, the schema of the table (the second parameter) that it wants to 
access. Can I supply a null schema, and still be able to reference the remote 
table, or must I also make use of IMPORT FOREIGN SCHEMA?

6) I have found that the native trigonometry functions, namely the radians 
versions, do produce error results around key trigonometry input values. I have 
discovered that these errors persist, even if I cast the input parameter away 
from DOUBLE PRECISION and into DECIMAL. I would like to know if there are any 
freely available scripts out there that include Arbitrary Precision 
mathematical functions support that work on DECIMAL and not on DOUBLE 
PRECISION, that do not produce any error values around key inputs? Could 
someone refer me to a website that has a script that is such?

Re: Serious List of PostgreSQL usage questions.

[David G. Johnston]

As this is your third email of this form, with mostly the same questions, I
would suggestion you send a single email, with an appropriate subject line,
per question to facilitate discussion and avoid subjecting people to
looking at multiple bulk threads to see what has and hasn't been said.

Then, once you get replies, reply back.



On Mon, Aug 9, 2021, 19:01 A Z  wrote:

>
>

Re: Serious List of PostgreSQL usage questions.

[Mladen Gogala]

Responses in-line:


On 8/9/21 10:01 PM, A Z wrote:
1) Are there free scripts for CREATE TYPE (native type), more 
advanced,  or  sorts of types out there, online, free 
for commercial use? With function support, too? Can someone reply with 
a link or a suggestion?



ORAFCE? That extension has Oracle date/time functions.




2) How may I get PostgreSQL to output the create table statement(s) 
for one or more tables inside one database, without issuing 
instructions via the command line, but only inside a database login, 
as a query or pl/sql?


Postgres doesn't store DDL into the catalog, DDL has to be created from 
the catalog data. I would advise pg_tables, pg_attribute and pg_indexes. 
That shouldn't be too hard of an exercise with PlPgSQL, If you want, you 
can play with the ddlx extension: https://pgxn.org/dist/ddlx/





3) I know that I can use COPY to import or export one database table 
between it and a *.csv file. Can I use it to do this with multiple 
TABLES and *.csv files specified in one COPY COMMAND, or not?


You can not. Copy does only one table at one time. Of course, you can 
use "screen" and do multiple tables from multiple windows or something 
similar.





4) In the absence of OS command line instructions, is there an 
internal PostgreSQL way, via COPY or another function for example, to 
backup an entire database, with all it's create table statements and 
all insert statements, and any other associated object, in one hit? Or 
is this ill-advised?
Postgres pg_dump utility will do that. The results should be interesting 
for a muti-TB database. BTW, any backup utility, even the plain and 
simple pg_basebackup with --format=tar will be able to restore the 
database and recover the database. You can always reconstruct the DDL 
with pg_dump --schema-only. PgAdmin4 will also provide the DDL on request.


5) When setting up communication to remote databases on remote 
machines, I need to use the OPTIONS() function. It seems to require as 
its first function parameter, the schema of the table (the second 
parameter) that it wants to access. Can I supply a null schema, and 
still be able to reference the remote table, or must I also make use 
of IMPORT FOREIGN SCHEMA?


6) I have found that the native trigonometry functions, namely the 
radians versions, do produce error results around key trigonometry 
input values. I have discovered that these errors persist, even if I 
cast the input parameter away from DOUBLE PRECISION and into DECIMAL. 
I would like to know if there are any freely available scripts out 
there that include Arbitrary Precision mathematical functions support 
that work on DECIMAL and not on DOUBLE PRECISION, that do not produce 
any error values around key inputs? Could someone refer me to a 
website that has a script that is such?


PlPgPython can use numpy: https://numpy.org


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Pavel Stehule]

po 9. 8. 2021 v 23:13 odesílatel Tom Lane  napsal:

> Pavel Stehule  writes:
> > Some errors like this, but not this can be detected by plpgsql_check
> > https://github.com/okbob/plpgsql_check - probably the heuristic for type
> > check is not complete.
>
> STRICTMULTIASSIGNMENT would detect most cases of this, except that
> the condition is checked too late.  We'd need to count the fields
> *before* trying to assign values, not after.
>
> In the meantime, it does seem like the docs could be more explicit
> about this, and perhaps give an example showing the (x).* solution.
>

Yes, a more detailed explanation of this behavior can be nice.  There can
be an example of value unnesting, but I think so for this case, there
should be mainly an example of ANSI assign syntax.

var := (SELECT x FROM ..)

This syntax has advantages so is not amigonuous for this case, and explicit
unnesting is not necessary (and it is not possible). Moreover, this is ANSI
SQL syntax.

Regards

Pavel



> regards, tom lane
>

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[Pavel Stehule]

>
>
>
> It now seems to me to be odd, in the light of the explanations for why the
> naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a
> scalar subquery to a variable of the composite type in question _does_
> work! But don't take that as a question. I'm going to regard this as "case
> closed".
>

This depends on how integration of PL/pgSQL and SQL is designed.  PL/pgSQL
is a relatively small procedural interpretation over SQL engine. When you
evaluate a query, then you always get a composite value (named tuple)
always (in all cases).

SELECT 10, 20 INTO rec;

In this case you get composite (10,20) and it can be assigned to composite
without problems.

SELECT (10,20) INTO rec

returns composite ((10,20)), and that cannot be assigned to your composite.

Syntax rec := (SELECT 10,20) is not possible. Subquery can return only one
value always. More values are not allowed.

rec := (SELECT (10,20)) is working, because you can assign (in all cases)
the first field of returned composite value. This syntax cannot be
ambiguous.

If you work intensively with plpgsql, then it can be a very informative
look at plpgsql source code.  Don't be afraid it is not too long, and you
will see. It is very simple. Then you can understand how it works.

https://github.com/postgres/postgres/tree/master/src/pl/plpgsql/src

Regards

Pavel