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ó:

> 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 studied now the fine docs again and have some additional questions. The
backup was done fine to the directory /data/postgresql133/backup-20210810-1
which contains now:

$ ls -l
total 717252
-rw--- 1 postgres postgres430331 Aug 10 08:54 backup_manifest
-rw--- 1 postgres postgres 734006592 Aug 10 08:54 base.tar.gz
-rw--- 1 postgres postgres 17667 Aug 10 08:54 pg_wal.tar.gz

I un-tar'ed the two archives with

$ tar xzf base.tar.gz
$ tar xzf pg_wal.tar.gz

And then I run pg_verifybackup it gives the following errors:

$ pg_verifybackup /data/postgresql133/backup-20210810-1
pg_verifybackup: error: "000100D9" is present on disk but not 
in the manifest
pg_verifybackup: error: "pg_wal.tar.gz" is present on disk but not in the 
manifest
pg_verifybackup: error: "base.tar.gz" is present on disk but not in the manifest
pg_verifybackup: error: "archive_status/000100D9.done" is 
present on disk but not in the manifest
pg_waldump: fatal: could not find any WAL file
pg_verifybackup: error: WAL parsing failed for timeline 1

The two messages about the present of pg_wal.tar.gz and base.tar.gz are
clear. One could move the archives out of the sight of pg_verifybackup.
But the others are unclear to me. The files 000100D9 and
archive_status/000100D9.done are part of pg_wal.tar.gz
written by pg_basebackup:

tar tvf pg_wal.tar.gz
-rw--- postgres/postgres 16777216 2021-08-10 08:52 000100D9
-rw--- postgres/postgres0 2021-08-10 08:54 
archive_status/000100D9.done

About WAL the file backup_manifest contains only:

"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/D928", "End-LSN": "0/D9000138" }
],

What is the problem here or what I've missed?
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: php connection failure

[ourdiaspora]

On Sunday, August 8th, 2021 at 1:51 AM, Adrian Klaver 
 wrote:

> 1.  Get rid of the first local line .
> 2.  Then for the remaining local line below change peer to trust and
>
> restart Postgres.

Thank you, but repeat error occurred:

sudo sudo service postgresql restart
[ ok ] Restarting PostgreSQL 9.6 database server: main.

psql -d cpacweb  -U cpaca
psql: FATAL:  Peer authentication failed for user "cpaca"

Re: php connection failure

[ourdiaspora]

On Sunday, August 8th, 2021 at 2:15 AM, Tom Lane  wrote:

> ourdiaspora ourdiasp...@protonmail.com writes:
>
> > psql -d cpacweb -U cpaca
> >
> > psql: FATAL: Peer authentication failed for user "cpaca"
>
>  It's a gold
>
> plated certainty that the default packet filter configuration
>
> wouldn't allow traffic to port 5432 from off-machine.

A quick ddg search revealed:

sudo ss -tulpn
Netid  State  Recv-Q Send-Q Local Address:Port   Peer 
Address:Port
tcpLISTEN 0  128 ::1:5432 :::*  
 users:(("postgres",pid=21794,fd=3))

Is the correct conclusion from this output, that the port 5432 is open?

Re: (13.1) pg_basebackups ./. pg_verifybackup

[Matthias Apitz]

El día martes, agosto 10, 2021 a las 09:23:34a. m. +0200, Matthias Apitz 
escribió:

> I've studied now the fine docs again and have some additional questions. The
> backup was done fine to the directory /data/postgresql133/backup-20210810-1
> which contains now:
> 
> $ ls -l
> total 717252
> -rw--- 1 postgres postgres430331 Aug 10 08:54 backup_manifest
> -rw--- 1 postgres postgres 734006592 Aug 10 08:54 base.tar.gz
> -rw--- 1 postgres postgres 17667 Aug 10 08:54 pg_wal.tar.gz
> 
> I un-tar'ed the two archives with
> 
> $ tar xzf base.tar.gz
> $ tar xzf pg_wal.tar.gz
> 
> And then I run pg_verifybackup it gives the following errors:
> 
> $ pg_verifybackup /data/postgresql133/backup-20210810-1
> pg_verifybackup: error: "000100D9" is present on disk but not 
> in the manifest
> pg_verifybackup: error: "pg_wal.tar.gz" is present on disk but not in the 
> manifest
> pg_verifybackup: error: "base.tar.gz" is present on disk but not in the 
> manifest
> pg_verifybackup: error: "archive_status/000100D9.done" is 
> present on disk but not in the manifest
> pg_waldump: fatal: could not find any WAL file
> pg_verifybackup: error: WAL parsing failed for timeline 1
> 
> The two messages about the present of pg_wal.tar.gz and base.tar.gz are
> clear. One could move the archives out of the sight of pg_verifybackup.
> But the others are unclear to me. The files 000100D9 and
> archive_status/000100D9.done are part of pg_wal.tar.gz
> written by pg_basebackup:
> 
> tar tvf pg_wal.tar.gz
> -rw--- postgres/postgres 16777216 2021-08-10 08:52 
> 000100D9
> -rw--- postgres/postgres0 2021-08-10 08:54 
> archive_status/000100D9.done
> 
> About WAL the file backup_manifest contains only:
> 
> "WAL-Ranges": [
> { "Timeline": 1, "Start-LSN": "0/D928", "End-LSN": "0/D9000138" }
> ],
> 
> What is the problem here or what I've missed?
> Thanks

I think, I sorted it out by doing this:

I moved away the tar-archives:

$ cd /data/postgresql133/backup-20210810-1
$ mkdir ../saved
$ mv *.tar.gz ../saved

I unpacked 'base.tar.gz' the usual way:

$ tar xzf ../saved/base.tar.gz

but 'pg_wal.tar.gz' in the sub dir 'pg_wal':

$ cd pg_wal
$ tar xzf ../../saved/pg_wal.tar.gz
$ cd ..
$ find pg_wal
pg_wal
pg_wal/000100D9
pg_wal/archive_status
pg_wal/archive_status/000100D9.done

now pg_verifybackup seems to be happy:

$ pg_verifybackup  /data/postgresql133/backup-20210810-1
backup successfully verified

Is this the supposed way it should work? Esp. this unpacking of
'pg_wal.tar.gz' in a directory level below?

Ofc, one could specify the location of the WAL file with '-w path', but
the doc only suggests this when "Try to parse WAL files stored in the
specified directory, rather than in pg_wal. This may be useful if the
backup is stored in a separate location from the WAL archive."

But having to unpack 'pg_wal.tar.gz' below pg_wal is not obvious. 

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: php connection failure

[Tom Lane]

ourdiaspora  writes:
> A quick ddg search revealed:

> sudo ss -tulpn
> Netid  State  Recv-Q Send-Q Local Address:Port   Peer 
> Address:Port
> tcpLISTEN 0  128 ::1:5432 :::*
>users:(("postgres",pid=21794,fd=3))

> Is the correct conclusion from this output, that the port 5432 is open?

No.  The postgres process is listening, but this has nothing to do with
whether the kernel will allow any packets to arrive there.

On recent Red Hat systems, "man nft" might help you.
Not sure about other Linux variants.

regards, tom lane

Re: php connection failure

[Adrian Klaver]

On 8/10/21 1:59 AM, ourdiaspora wrote:


On Sunday, August 8th, 2021 at 1:51 AM, Adrian Klaver 
 wrote:


1.  Get rid of the first local line .
2.  Then for the remaining local line below change peer to trust and

 restart Postgres.


Thank you, but repeat error occurred:

sudo sudo service postgresql restart
[ ok ] Restarting PostgreSQL 9.6 database server: main.

psql -d cpacweb  -U cpaca
psql: FATAL:  Peer authentication failed for user "cpaca"


Then either:

1) There is another local line with peer that you missed.

2) You changed the wrong pg_hba.conf file.
What was the path of the file you changed?


What is returned when you do?:

ps ax | grep postgres


What do you see in:

sudo vi /var/log/postgresql/postgresql-9.6-main.log

when you do a restart?


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

Re: archive_command / single user mode

[Christophe Pettus]

> On Aug 9, 2021, at 18:03, David G. Johnston  
> wrote:
> 
> 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.

That seems entirely logical.  Thank you!

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

[Bryn Llewellyn]

> pavel.steh...@gmail.com wrote:
> 
>> t...@sss.pgh.pa.us wrote:
>> 
>>> pavel.steh...@gmail.com wrote:
>>> 
>>> 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.

Consider this example:

create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;

It seems to be perfectly plausible—and so it seems equally plausible that you'd 
want to do it using PL/pgSQL. Each of these two alternatives, inspired by the 
advice that I got in this thread, works and produces the expected output:

do $body$
declare
  r record;
begin
  select ((b, t)).*
  into r
  from tab1
  where k = 1;
  raise info 'Alt 1: % | %', r.f1::text, r.f2::text;

  r := (
select (b, t)
from tab1
where k = 1);
  raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;

It feels a smidge uncomfortable because I'm forced to use reference by field 
position (f1, f2) rather than by field name (b, t). But reference by position 
is commonplace in PostgreSQL (for example, in the PREPARE statement). So 
I'spose that I have to learn to like it.

Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best way to 
implement the requirement? I prefer "Alt 2" because it doesn't have the clutter 
(and the burden for understanding and readability) of the extra parentheses and 
the ".*".

I can get the reference by field name that I prefer with a schema-level type:

create type tmp as (b text, t type1);

and by declaring "r" with this data type. But this is a greater discomfort than 
using the dynamically shaped "record" because it needs you to create a 
dedicated schema-level type for every new SELCT list that you come need.

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

[Pavel Stehule]

út 10. 8. 2021 v 19:48 odesílatel Bryn Llewellyn  napsal:

> pavel.steh...@gmail.com wrote:
>
> t...@sss.pgh.pa.us wrote:
>
> *pavel.steh...@gmail.com  wrote:*
>
> 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.
>
>
> Consider this example:
>
> create type type1 as (a1 int, a2 int);
> create table tab1(k int primary key, b boolean not null, t type1 not null);
> insert into tab1(k, b, t) values(1, true, (10, 20));
> select b::text, t::text from tab1 where k = 1;
>
> It seems to be perfectly plausible—and so it seems equally plausible that
> you'd want to do it using PL/pgSQL. Each of these two alternatives,
> inspired by the advice that I got in this thread, works and produces the
> expected output:
>

Yes, this works. This syntax is not ambiguous.



> do $body$
> declare
>   r record;
> begin
>   select ((b, t)).*
>   into r
>   from tab1
>   where k = 1;
>   raise info 'Alt 1: % | %', r.f1::text, r.f2::text;
>
>   r := (
> select (b, t)
> from tab1
> where k = 1);
>   raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
> end;
> $body$;
>
> It feels a smidge uncomfortable because I'm forced to use reference by
> field position (f1, f2) rather than by field name (b, t). But reference by
> position is commonplace in PostgreSQL (for example, in the PREPARE
> statement). So I'spose that I have to learn to like it.
>

postgres=# do $$
declare r record;
begin
  select 10 as a, 20 as b into r;
  raise notice '% %', r.a, r.b;
end;
$$;
NOTICE:  10 20
DO

The composite value always has structure, and types, but sometimes it can
lose labels. You can push labels by casting

r := (select (b, t) -- this is dynamic composity value, but without labels
- the scalar value doesn't hold label

or

r := (select (b, t)::type1 -- it is composite with labels again


>
> Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best
> way to implement the requirement? I prefer "Alt 2" because it doesn't have
> the clutter (and the burden for understanding and readability) of the extra
> parentheses and the ".*".
>

I prefer Alt 2 too.


>
> I can get the reference by field name that I prefer with a schema-level
> type:
>
> create type tmp as (b text, t type1);
>
> and by declaring "r" with this data type. But this is a greater discomfort
> than using the dynamically shaped "record" because it needs you to create a
> dedicated schema-level type for every new SELCT list that you come need.
>

When It is possible I use a record type - some years ago, the work with
this type was a little bit slower, but not now. The work with this type is
little bit safer - because it gets real labels. Values with declared
composite types uses positional assignment, that is not too safe.

create table foo(a int, b int);
create type footype as (a int, b int);
insert into foo values(10, 200);

postgres=# do $$
declare f footype;
r record;
begin
  select b, a from foo into f;
  select b, a from foo into r;
  raise notice 'f.a: %, f.b: %', f.a, f.b;
  raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE:  f.a: 200, f.b: 10
NOTICE:  r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌┬─┐
│ a  │  b  │
╞╪═╡
│ 10 │ 200 │
└┴─┘
(1 row)

But sometimes explicit type is necessary - when you want to return
composite value and when you want to work with composite outside function,
or when you want to serialize, or deserialize composite value to/from json.

When you work with composite values, is good to enable warnings

https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS

Regards

Pavel

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

[Pavel Stehule]

>
>
>> I can get the reference by field name that I prefer with a schema-level
>> type:
>>
>> create type tmp as (b text, t type1);
>>
>> and by declaring "r" with this data type. But this is a greater
>> discomfort than using the dynamically shaped "record" because it needs you
>> to create a dedicated schema-level type for every new SELCT list that you
>> come need.
>>
>
> When It is possible I use a record type - some years ago, the work with
> this type was a little bit slower, but not now. The work with this type is
> little bit safer - because it gets real labels. Values with declared
> composite types uses positional assignment, that is not too safe.
>
> create table foo(a int, b int);
> create type footype as (a int, b int);
> insert into foo values(10, 200);
>
> postgres=# do $$
> declare f footype;
> r record;
> begin
>   select b, a from foo into f;
>   select b, a from foo into r;
>   raise notice 'f.a: %, f.b: %', f.a, f.b;
>   raise notice 'r.a: %, r.b: %', r.a, r.b;
> end;
> $$;
> NOTICE:  f.a: 200, f.b: 10
> NOTICE:  r.a: 10, r.b: 200
> DO
> postgres=# select * from foo;
> ┌┬─┐
> │ a  │  b  │
> ╞╪═╡
> │ 10 │ 200 │
> └┴─┘
> (1 row)
>
> But sometimes explicit type is necessary - when you want to return
> composite value and when you want to work with composite outside function,
> or when you want to serialize, or deserialize composite value to/from json.
>
> When you work with composite values, is good to enable warnings
>
>
> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
>

static composite types can be good when you use dynamic SQL. The
plpgsql_check cannot derive output composite type from dynamic SQL.  And it
can stop checking. When you use static composite type, then the check can
continue.

Regards

Pavel


> Regards
>
> Pavel
>

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.
>

I use some fragments of this routine. But the problem was so I did implicit
unnesting, although plpgsql doesn't do this

https://github.com/okbob/plpgsql_check/commit/c06c9e3dbf175c8d7d5b1df20e01dc3fea339281

postgres=# create or replace function broken_into()
returns void as $$
declare v typ2;
begin
  -- should to fail
  select (10,20)::typ2 into v;
  -- should be ok
  select ((10,20)::typ2).* into v;
  -- should to fail
  execute 'select (10,20)::typ2' into v;
  -- should be ok
  execute 'select ((10,20)::typ2).*' into v;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from plpgsql_check_function('broken_into', fatal_errors
=> false);
┌┐
│   plpgsql_check_function
  │
╞╡
│ error:42804:5:SQL statement:cannot cast composite value of "typ2" type to
a scalar value of "integer" type │
│ warning:0:5:SQL statement:too few attributes for composite variable
 │
│ error:42804:9:EXECUTE:cannot cast composite value of "typ2" type to a
scalar value of "integer" type   │
│ warning:0:9:EXECUTE:too few attributes for composite variable
 │
│ warning extra:0:2:DECLARE:never read variable "v"
 │
└┘
(5 rows)

Regards

Pavel




> 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
>

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

[Pavel Stehule]

út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule 
napsal:

>
>>> I can get the reference by field name that I prefer with a schema-level
>>> type:
>>>
>>> create type tmp as (b text, t type1);
>>>
>>> and by declaring "r" with this data type. But this is a greater
>>> discomfort than using the dynamically shaped "record" because it needs you
>>> to create a dedicated schema-level type for every new SELCT list that you
>>> come need.
>>>
>>
>> When It is possible I use a record type - some years ago, the work with
>> this type was a little bit slower, but not now. The work with this type is
>> little bit safer - because it gets real labels. Values with declared
>> composite types uses positional assignment, that is not too safe.
>>
>> create table foo(a int, b int);
>> create type footype as (a int, b int);
>> insert into foo values(10, 200);
>>
>> postgres=# do $$
>> declare f footype;
>> r record;
>> begin
>>   select b, a from foo into f;
>>   select b, a from foo into r;
>>   raise notice 'f.a: %, f.b: %', f.a, f.b;
>>   raise notice 'r.a: %, r.b: %', r.a, r.b;
>> end;
>> $$;
>> NOTICE:  f.a: 200, f.b: 10
>> NOTICE:  r.a: 10, r.b: 200
>> DO
>> postgres=# select * from foo;
>> ┌┬─┐
>> │ a  │  b  │
>> ╞╪═╡
>> │ 10 │ 200 │
>> └┴─┘
>> (1 row)
>>
>> But sometimes explicit type is necessary - when you want to return
>> composite value and when you want to work with composite outside function,
>> or when you want to serialize, or deserialize composite value to/from json.
>>
>
There are a lot of use cases for static composite types. Everywhere on the
interface.

http://okbob.blogspot.com/2013/10/using-custom-composite-types-in.html



>> When you work with composite values, is good to enable warnings
>>
>>
>> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
>>
>
> static composite types can be good when you use dynamic SQL. The
> plpgsql_check cannot derive output composite type from dynamic SQL.  And it
> can stop checking. When you use static composite type, then the check can
> continue.
>
> Regards
>
> Pavel
>
>
>> Regards
>>
>> Pavel
>>
>

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

[Bryn Llewellyn]

> pavel.steh...@gmail.com wrote:
> 
> b...@yugabyte.com  wrote:
> 
>> pavel.steh...@gmail.com  wrote:
>> 
>>> t...@sss.pgh.pa.us  wrote:
>>> 
 pavel.steh...@gmail.com  wrote:
 
 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.
> 
> Consider this example:
> 
> create type type1 as (a1 int, a2 int);
> create table tab1(k int primary key, b boolean not null, t type1 not null);
> insert into tab1(k, b, t) values(1, true, (10, 20));
> select b::text, t::text from tab1 where k = 1;
> 
> It seems to be perfectly plausible—and so it seems equally plausible that 
> you'd want to do it using PL/pgSQL. Each of these two alternatives, inspired 
> by the advice that I got in this thread, works and produces the expected 
> output:
> 
> Yes, this works. This syntax is not ambiguous.
> 
> do $body$
> declare
>   r record;
> begin
>   select ((b, t)).*
>   into r
>   from tab1
>   where k = 1;
>   raise info 'Alt 1: % | %', r.f1::text, r.f2::text;
> 
>   r := (
> select (b, t)
> from tab1
> where k = 1);
>   raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
> end;
> $body$;
> 
> It feels a smidge uncomfortable because I'm forced to use reference by field 
> position (f1, f2) rather than by field name (b, t). But reference by position 
> is commonplace in PostgreSQL (for example, in the PREPARE statement). So 
> I'spose that I have to learn to like it.
> 
> postgres=# do $$
> declare r record;
> begin
>   select 10 as a, 20 as b into r;
>   raise notice '% %', r.a, r.b;
> end;
> $$;
> NOTICE:  10 20
> DO
> 
> The composite value always has structure, and types, but sometimes it can 
> lose labels. You can push labels by casting
> 
> r := (select (b, t) -- this is dynamic composity value, but without labels - 
> the scalar value doesn't hold label
> 
> or
> 
> r := (select (b, t)::type1 -- it is composite with labels again

Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is 
composite with labels again". I tried this:

create procedure p(i in int)
  language plpgsql
as $body$
declare
  r record;
begin
  case i
when 1 then
  select (b, t)::type1
  into r
  from tab1
  where k = 1;

when 2 then
  r := (
select (b, t)::type1
from tab1
where k = 1);

else null;
  end case;
end;
$body$;

call p(3);
call p(2);
call p(1);

My idea with using a procedure and choosing which code path is followed at 
run-time is to distinguish between compile-time errors (there are none here) 
and run-time errors. Of course, "call p(3)" finishes with no error.

But both the other calls cause the same error:

42846: cannot cast type record to type1

But you say that this should work!

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

[Pavel Stehule]

>
> r := (select (b, t)::type1 -- it is composite with labels again
>
>
>
postgres=# do $$
declare r record;
begin
  r := (select (10,20));
  raise notice '%', to_json(r);
end;
$$;
NOTICE:  {"f1":10,"f2":20}
DO
postgres=# do $$
declare r record;
begin
  r := (select (10,20)::footype);
  raise notice '%', to_json(r);
end;
$$;
NOTICE:  {"a":10,"b":20}
DO



> Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is
> composite with labels again". I tried this:
>
> create procedure p(i in int)
>   language plpgsql
> as $body$
> declare
>   r record;
> begin
>   case i
> when 1 then
>   select (b, t)::type1
>   into r
>   from tab1
>   where k = 1;
>
> when 2 then
>   r := (
> select (b, t)::type1
> from tab1
> where k = 1);
>
> else null;
>   end case;
> end;
> $body$;
>
> call p(3);
> call p(2);
> call p(1);
>
> My idea with using a procedure and choosing which code path is followed at
> run-time is to distinguish between compile-time errors (there are none
> here) and run-time errors. Of course, "call p(3)" finishes with no error.
>
> But both the other calls cause the same error:
>
> 42846: cannot cast type record to type1
>

the message is maybe not too intuitive, these casts are broken - you try to
cast (boolean, type1) => type1

The cast can ignore some fields from right or can add nulls from right, but
it cannot skip fields from left.







> But you say that this should work!
>
>
>

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

[Bryn Llewellyn]

> On 10-Aug-2021, at 13:12, Pavel Stehule  wrote:
> 
> 
>> 
>> r := (select (b, t)::type1 -- it is composite with labels again
> 
> 
> postgres=# do $$
> declare r record;
> begin
>   r := (select (10,20));
>   raise notice '%', to_json(r);
> end;
> $$;
> NOTICE:  {"f1":10,"f2":20}
> DO
> postgres=# do $$
> declare r record;
> begin
>   r := (select (10,20)::footype);
>   raise notice '%', to_json(r);
> end;
> $$;
> NOTICE:  {"a":10,"b":20}
> DO
> 
>  
> Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is 
> composite with labels again". I tried this:
> 
> create procedure p(i in int)
>   language plpgsql
> as $body$
> declare
>   r record;
> begin
>   case i
> when 1 then
>   select (b, t)::type1
>   into r
>   from tab1
>   where k = 1;
> 
> when 2 then
>   r := (
> select (b, t)::type1
> from tab1
> where k = 1);
> 
> else null;
>   end case;
> end;
> $body$;
> 
> call p(3);
> call p(2);
> call p(1);
> 
> My idea with using a procedure and choosing which code path is followed at 
> run-time is to distinguish between compile-time errors (there are none here) 
> and run-time errors. Of course, "call p(3)" finishes with no error.
> 
> But both the other calls cause the same error:
> 
> 42846: cannot cast type record to type1
> 
> the message is maybe not too intuitive, these casts are broken - you try to 
> cast (boolean, type1) => type1 
> 
> The cast can ignore some fields from right or can add nulls from right, but 
> it cannot skip fields from left. 

I simply cannot mange this list's "partial quoting and bottom posting 
convention". Forgive me.

The code that I tried looked obviously broken. But it's what you said would 
work.

Anyway, it seems to me that what I wrote originally still stands. I can use a 
schema level type or a record. Each approach has its pros and cons.