Re: Was my question inappropriate for postgres?

[Alicja Kucharczyk]

>
> 3) "...compatible to Microsoft's M365." I who am not a Windows user,
> knew what the OP meant. And if you go here:
>
> https://en.wikipedia.org/wiki/Microsoft_365#As_%22Microsoft_365%22
>
> You will see:
>
> "On March 30, 2020, Microsoft announced that the consumer plans of
> Office 365 would be rebranded as "Microsoft 365" (a brand also used by
> Microsoft for an enterprise subscription bundle of Windows, Office 365,
> and security services) on April 21, 2020, succeeding existing consumer
> plans of Office 365."
>
> So M365 is actually more correct then 0365.
>

I asked a colleague, who is managing M365 licenses at MS and he agrees with
your statement Adrian that M365 is more correct than O365. What he also
mentioned that the case is a bit more complicated and in practice both,
i.e. O365 and M365 are fine to use.

pozdrawiam,
best regards,
mit freundlichen Grüßen,
Alicja Kucharczyk
*Warsaw PostgreSQL User Group*

Password reset link / 'less' does not exit in psql version 13.4

[Michael J. Baars]

Hello,

I have two very simple questions:

1) I have an account at postgresql.org, but a link to a 'forgot password' seems 
to be missing on the login page. I have my password stored only on an old 
Fedora 32 computer. To change the password
when logged in, you need to supply the old password. In short, I have no way to 
migrate this postgresql.org account to my new Fedora 35 and Fedora 36 
computers. What can be done about this?

2) I have three psql clients running, a version 12.6, a version 13.4 and a 
version 14.3. Until now a 'select * from table;' showed the output in 'less' or 
something alike and exited from 'less' when
the output was complete. Both version 12.6 and version 13.4 work that way. 
Version 14.3 does not exit from 'less' when the output is complete. Did anyone 
notice this already?

Best regards,
Mischa Baars.

Re: Password reset link / 'less' does not exit in psql version 13.4

[Francisco Olarte]

Michael:

On Mon, 25 Jul 2022 at 12:01, Michael J. Baars
 wrote:
> 2) I have three psql clients running, a version 12.6, a version 13.4 and a 
> version 14.3. Until now a 'select * from table;' showed the output in 'less' 
> or something alike and exited from 'less' when
> the output was complete. Both version 12.6 and version 13.4 work that way. 
> Version 14.3 does not exit from 'less' when the output is complete. Did 
> anyone notice this already?

AFAIK psql just pipes and waits ( and I've piped through some programs
of mine which could normally show bizarre behaviour if psql did
otherwise )This seems like you tuned the pager command or the default
less options, from man less:
>>>
   -e or --quit-at-eof
  Causes less to automatically exit the second time it
reaches end-of-file.   By  default,  the
  only way to exit less is via the "q" command.

   -E or --QUIT-AT-EOF
  Causes less to automatically exit the first time it
reaches end-of-file.
<<<

This can come from a variety of sources, see man less, search for lesskey, ...

Check also PAGER and PSQL_PAGER, and .psqlrc and friends, you may have
some things there.

Francsico Olarte.








>
> Best regards,
> Mischa Baars.
>
>
>
>

Re: Was my question inappropriate for postgres?

[Mladen Gogala]

On 7/25/22 00:36, Adrian Klaver wrote:
Suitable driver is just that a driver that will suit the needs of 
working on Windows 10 with MS Office/Office/Microsoft 365.


Literally every ODBC driver on Windows fits this definition. That is the 
purpose of ODBC: having a uniform set of calls for various databases. 
That is why people still use it, even on Linux. And yes, like you, I am 
a Linux user too.


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

Re: Password reset link / 'less' does not exit in psql version 13.4

[Adrian Klaver]

On 7/25/22 03:01, Michael J. Baars wrote:

Hello,

I have two very simple questions:

1) I have an account at postgresql.org, but a link to a 'forgot password' seems 
to be missing on the login page. I have my password stored only on an old 
Fedora 32 computer. To change the password
when logged in, you need to supply the old password. In short, I have no way to 
migrate this postgresql.org account to my new Fedora 35 and Fedora 36 
computers. What can be done about this?


If you go here:

https://www.postgresql.org/account/login/?next=/account/

there is a password reset link that takes you to page where you can 
specify the email address for the account and have a password reset link 
sent to that email. At that link you can create a new password without 
knowing the old one.




2) I have three psql clients running, a version 12.6, a version 13.4 and a 
version 14.3. Until now a 'select * from table;' showed the output in 'less' or 
something alike and exited from 'less' when
the output was complete. Both version 12.6 and version 13.4 work that way. 
Version 14.3 does not exit from 'less' when the output is complete. Did anyone 
notice this already?


Are all the clients running on the same machine?



Best regards,
Mischa Baars.







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

Re: Password reset link / 'less' does not exit in psql version 13.4

[Michael J. Baars]

On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote:
> On 7/25/22 03:01, Michael J. Baars wrote:
> > Hello,
> > 
> > I have two very simple questions:
> > 
> > 1) I have an account at postgresql.org, but a link to a 'forgot password' 
> > seems to be missing on the login page. I have my password stored only on an 
> > old Fedora 32 computer. To change the password
> > when logged in, you need to supply the old password. In short, I have no 
> > way to migrate this postgresql.org account to my new Fedora 35 and Fedora 
> > 36 computers. What can be done about this?
> 
> If you go here:
> 
> https://www.postgresql.org/account/login/?next=/account/
> 
> there is a password reset link that takes you to page where you can 
> specify the email address for the account and have a password reset link 
> sent to that email. At that link you can create a new password without 
> knowing the old one.

Thank you Adrian!

> 
> > 2) I have three psql clients running, a version 12.6, a version 13.4 and a 
> > version 14.3. Until now a 'select * from table;' showed the output in 
> > 'less' or something alike and exited from 'less'
> > when
> > the output was complete. Both version 12.6 and version 13.4 work that way. 
> > Version 14.3 does not exit from 'less' when the output is complete. Did 
> > anyone notice this already?
> 
> Are all the clients running on the same machine?

Nope, three out of four machines run psql. I have four identical machines. 
Small ones. Beelink MII-Vs.

I was preparing to expand to twenty, but I have problems booting Fedora Server 
from a centralized Fedora Server / Fedora Workstation dual boot over NFS. It 
looks like the nfsroot= kernel parameter
conflicts with the initrd, which holds some Fedora configuration scripts. It 
would have been really nice to see the PostgreSQL server running over an 
aggregated 20Gbit/s SFP+ connection, serving 20
1Gbit/s number crunchers without loss of any bandwidth :) Hopefully one day it 
will work.

That's why I was fiddling around with the different Fedora and psql versions 
instead of working :)

> 
> > Best regards,
> > Mischa Baars.
> > 
> > 
> > 
> > 
> 
> 

Re: Password reset link / 'less' does not exit in psql version 13.4

[Adrian Klaver]

On 7/25/22 09:03, Michael J. Baars wrote:

On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote:

On 7/25/22 03:01, Michael J. Baars wrote:

Hello,



Are all the clients running on the same machine?


Nope, three out of four machines run psql. I have four identical machines. 
Small ones. Beelink MII-Vs.


The machines are identical but is the software also?

If you run Postgres 14 on one of the machines that has 12 or 13 does the 
pager work the same?




I was preparing to expand to twenty, but I have problems booting Fedora Server 
from a centralized Fedora Server / Fedora Workstation dual boot over NFS. It 
looks like the nfsroot= kernel parameter
conflicts with the initrd, which holds some Fedora configuration scripts. It 
would have been really nice to see the PostgreSQL server running over an 
aggregated 20Gbit/s SFP+ connection, serving 20
1Gbit/s number crunchers without loss of any bandwidth :) Hopefully one day it 
will work.

That's why I was fiddling around with the different Fedora and psql versions 
instead of working :)


I'm going to say we have reached the core of the issue:)






Best regards,
Mischa Baars.



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

Re: Password reset link / 'less' does not exit in psql version 13.4

[Michael J. Baars]

On Mon, 2022-07-25 at 09:13 -0700, Adrian Klaver wrote:
> On 7/25/22 09:03, Michael J. Baars wrote:
> > On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote:
> > > On 7/25/22 03:01, Michael J. Baars wrote:
> > > > Hello,
> > > Are all the clients running on the same machine?
> > 
> > Nope, three out of four machines run psql. I have four identical machines. 
> > Small ones. Beelink MII-Vs.
> 
> The machines are identical but is the software also?
> 
> If you run Postgres 14 on one of the machines that has 12 or 13 does the 
> pager work the same?

I thought I already told this before, but some of the mails, apparently do not 
get through.

I'm running Fedora 32, Fedora 35 and Fedora 36 at the moment. Fedora 32 and 35 
do not show any problems with the pager, nor are there any PAGER or PSQL_PAGER 
environment variables. Fedora 36 behaves
different from the older two versions.

> 
> > I was preparing to expand to twenty, but I have problems booting Fedora 
> > Server from a centralized Fedora Server / Fedora Workstation dual boot over 
> > NFS. It looks like the nfsroot= kernel parameter
> > conflicts with the initrd, which holds some Fedora configuration scripts. 
> > It would have been really nice to see the PostgreSQL server running over an 
> > aggregated 20Gbit/s SFP+ connection, serving
> > 20
> > 1Gbit/s number crunchers without loss of any bandwidth :) Hopefully one day 
> > it will work.
> > 
> > That's why I was fiddling around with the different Fedora and psql 
> > versions instead of working :)
> 
> I'm going to say we have reached the core of the issue:)
> 
> > > > Best regards,
> > > > Mischa Baars.
> 
> 

Re: Password reset link / 'less' does not exit in psql version 13.4

[Adrian Klaver]

On 7/25/22 09:23, Michael J. Baars wrote:

On Mon, 2022-07-25 at 09:13 -0700, Adrian Klaver wrote:

On 7/25/22 09:03, Michael J. Baars wrote:

On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote:

On 7/25/22 03:01, Michael J. Baars wrote:

Hello,




I thought I already told this before, but some of the mails, apparently do not 
get through.


I didn't seem them.



I'm running Fedora 32, Fedora 35 and Fedora 36 at the moment. Fedora 32 and 35 
do not show any problems with the pager, nor are there any PAGER or PSQL_PAGER 
environment variables. Fedora 36 behaves
different from the older two versions.



So the issue is with Fedora not Postgres.

Does Fedora 36 have PAGER* env variables?

In Fedora 36 at a terminal command line what happens if you do ls -al in 
a directory with more then screen full of file listings?




Best regards,
Mischa Baars.








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

Re: Password reset link / 'less' does not exit in psql version 13.4

[Michael J. Baars]

On Mon, 25 Jul 2022, 18:41 Adrian Klaver,  wrote:

> On 7/25/22 09:23, Michael J. Baars wrote:
> > On Mon, 2022-07-25 at 09:13 -0700, Adrian Klaver wrote:
> >> On 7/25/22 09:03, Michael J. Baars wrote:
> >>> On Mon, 2022-07-25 at 07:55 -0700, Adrian Klaver wrote:
>  On 7/25/22 03:01, Michael J. Baars wrote:
> > Hello,
>
> >
> > I thought I already told this before, but some of the mails, apparently
> do not get through.
>
> I didn't seem them.
>

It's an evolution problem. I think it's had it's longest time.


> >
> > I'm running Fedora 32, Fedora 35 and Fedora 36 at the moment. Fedora 32
> and 35 do not show any problems with the pager, nor are there any PAGER or
> PSQL_PAGER environment variables. Fedora 36 behaves
> > different from the older two versions.
> >
>
> So the issue is with Fedora not Postgres.
>

No, it's psql. Setting PAGER to "more -e" solved the problem l, but I never
had to before. There are no other variables affecting this behavior, so it
must be psql internal default piping command that has changed.


> Does Fedora 36 have PAGER* env variables?
>

No, none of the distributions has by default.


> In Fedora 36 at a terminal command line what happens if you do ls -al in
> a directory with more then screen full of file listings?
>
>
> > Best regards,
> > Mischa Baars.
> >>
> >>
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Password reset link / 'less' does not exit in psql version 13.4

[Adrian Klaver]

On 7/25/22 9:55 AM, Michael J. Baars wrote:






So the issue is with Fedora not Postgres.


No, it's psql. Setting PAGER to "more -e" solved the problem l, but I 
never had to before. There are no other variables affecting this 
behavior, so it must be psql internal default piping command that has 
changed.




Hmm, I don't see a -e option to 'more' I do see one for 'less'. I'm
guessing 'more' is symlinked to 'less'.

From here:

https://www.greenwoodsoftware.com/less/faq.html#dashe

"Normally, less doesn't quit until you type "q". You can make it quit as 
soon as it reaches the end of the file by using the -E option. However, 
many (but not all) people prefer to have less remain active until they 
explicitly quit, because they might wish to back up and look at earlier 
parts of the file again. "


I'm betting that between Fedora 35 and 36 the project changed the 
configuration of `less` to follow the normal pattern.


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

PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

[Bryn Llewellyn]

I copied my testcase at the end. It runs OK and produces the output that I 
expect using PG Version 14.4. But using Version 11.9 (and earlier 11 
sub-versions), it fails to compile with this error:

syntax error at or near "."
lhs[j].a := rhs[j].a;
  ^
If I comment out the "Ideal approach" loop, then it runs fine in PG 11 and 
produces the same output as the "Ideal approach" does in PG 14,

I looked at the Release Notes for each of Versions 14, 13, and 12:

https://www.postgresql.org/docs/12/release-12-1.html 

https://www.postgresql.org/docs/13/release-13-1.html 

https://www.postgresql.org/docs/14/release-14-1.html 


I searched in the page for "array". But there were no relevant hits on any of 
those three pages.

Can anybody tell me which PG release brought the improvement?

p.s., I'm asking because YugabyteDB still uses the PG 11.2 SQL processing code. 
I'm promised that fairly soon a new YugabyteDB version will use the PG 13 SQL 
processing code. I'm hoping that, then, I'll be able to retire my workaround.



create type t as (a text, b text);

create function f()
  returns table(z text)
  language plpgsql
as $body$
declare
  lhs  t[] not null := array[('??', '??'), ('??', '??'), ('??', '??')];
  rhs constant t[] not null := array[('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3')];

  rt   not null :=   ('??', '??');
begin
  for j in 1..3 loop
z := rhs[j].a||', '||rhs[j].b;return next;
  end loop;

  -- Workaround
  for j in 1..3 loop
r := rhs[j];
r.a := rhs[j].a;
r.b := rhs[j].b;
lhs[j] := r;
  end loop;

  -- Ideal approach.
  for j in 1..3 loop
lhs[j].a := rhs[j].a;
lhs[j].b := rhs[j].b;
  end loop;

  z := '';return next;
  for j in 1..3 loop
z := lhs[j].a||', '||lhs[j].b;return next;
  end loop;
end;
$body$;

select z from f();

Re: PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

[David G. Johnston]

On Mon, Jul 25, 2022 at 10:34 AM Bryn Llewellyn  wrote:

> I copied my testcase at the end. It runs OK and produces the output that I
> expect using PG Version 14.4. But using Version 11.9 (and earlier 11
> sub-versions), it fails to compile with this error:
>
> *syntax error at or near "."*
> *lhs[j].a := rhs[j].a;*
> *  ^*
> If I comment out the "Ideal approach" loop, then it runs fine in PG 11 and
> produces the same output as the "Ideal approach" does in PG 14,
>
> I looked at the Release Notes for each of Versions 14, 13, and 12:
>
>
>From the change to this page I'd say v14:

https://www.postgresql.org/docs/14/plpgsql-statements.html

The release note for v14 say:

PL/PgSQL:
Improve PL/pgSQL's expression and assignment parsing (Tom Lane)
This change allows assignment to array slices and nested record fields.

David J.

Re: Password reset link / 'less' does not exit in psql version 13.4

[Adrian Klaver]

On 7/25/22 10:37 AM, Michael J. Baars wrote:

Reply to list also
Ccing list






Use 'man more', manual pages are your friend.


I did, I just picked the wrong version. Got an the manual for ancient 
version of more.




"Normally, less doesn't quit until you type "q". You can make it
quit as
soon as it reaches the end of the file by using the -E option. However,
many (but not all) people prefer to have less remain active until they
explicitly quit, because they might wish to back up and look at earlier
parts of the file again. "


Scroll-back buffers do not work using less as the psql piping command. 
Try more, talk less, Adrian.


As far back as I can remember they have. When I work with Postgres I 
live in psql. Not only can I scroll forward and backward I can use vim 
shortcuts gg(start of buffer), G(end of file),  /. j(cursor 
down one line), k(up one line), etc.







I'm betting that between Fedora 35 and 36 the project changed the
configuration of `less` to follow the normal pattern.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: PL/pgSQL: « arr[j].a := v » works fine in PG Version 14.4, fails to compile in Version 11.2. Which version brought the fix?

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I copied my testcase at the end. It runs OK and produces the output that I 
>> expect using PG Version 14.4. But using Version 11.9 (and earlier 11 
>> sub-versions), it fails to compile with this error:
>> 
>> syntax error at or near "."
>> lhs[j].a := rhs[j].a;
>>   ^
>> 
>> If I comment out the "Ideal approach" loop, then it runs fine in PG 11 and 
>> produces the same output as the "Ideal approach" does in PG 14,
>> 
>> I looked at the Release Notes for each of Versions 14, 13, and 12:
> 
> 
> From the change to this page I'd say v14:
> 
> https://www.postgresql.org/docs/14/plpgsql-statements.html
> 
> The release note for v14 say:
> 
> «
> PL/PgSQL: 
> Improve PL/pgSQL's expression and assignment parsing (Tom Lane)
> This change allows assignment to array slices and nested record fields.
> »

Thanks for the quick reply, David.

I looked at "43.5.1. Assignment" 
(https://www.postgresql.org/docs/14/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT).

I searched in the page for « ]. » and found this:

complex_array[n].realpart = 12.3;

The corresponding page for Version 13 has no hits for « ]. ». So that seems to 
be a clear answer to my question.

Re: Password reset link / 'less' does not exit in psql version 13.4

[Alvaro Herrera]

On 2022-Jul-25, Michael J. Baars wrote:

> No, it's psql. Setting PAGER to "more -e" solved the problem l, but I never
> had to before. There are no other variables affecting this behavior, so it
> must be psql internal default piping command that has changed.

Perhaps the settings are in the environment variable LESS, which sets
options for less(1).

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/

Streaming Replication, can't select row in backup without specifying collation

[Kelly Burkhart]

I have a primary and backup database running 12.10 and synced with
streaming replication.  I have a simple table that is returning different
results for the same query in the primary vs backup database.

On the primary DB:

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719';
 tag  |
   tag|   event_ts
--+---+---
 \x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 |
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05
(1 row)

On the backup:

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719';
 tag | tag | event_ts
-+-+--
(0 rows)

> select tag::bytea, * from sentinel where tag =
'quote_merge_locs_mnj_NSX_20220719' collate "C";
 tag  |
   tag|   event_ts
--+---+---
 \x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 |
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05
(1 row)

Can anyone explain why the backup does not show the row without specifying
a collation?

The primary is running CentOS 7.4, the backup is running CentOS 8.  The
backup was created from a basebackup.  The postgres binaries were built on
the OS on which they're running.  Neither were built with ICU (which I
didn't know existed until tracking this down...).  Both databases show
LC_COLLATE=en_US.UTF-8.

The query "select * from pg_collation where collname like '%en_US%';"
returns identical results for both databases.

I'm concerned that somehow I've created a backup that is not compatible in
some mysterious way with my primary.

Can anyone provide any insight on what is going on?

Thank you,

-Kelly

Re: Streaming Replication, can't select row in backup without specifying collation

[Adrian Klaver]

On 7/25/22 15:36, Kelly Burkhart wrote:
I have a primary and backup database running 12.10 and synced with 
streaming replication.  I have a simple table that is returning 
different results for the same query in the primary vs backup database.


On the primary DB:

 > select tag::bytea, * from sentinel where tag = 
'quote_merge_locs_mnj_NSX_20220719';
                                  tag                                  | 
                tag                |           event_ts

--+---+---
  \x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 | 
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05

(1 row)

On the backup:

 > select tag::bytea, * from sentinel where tag = 
'quote_merge_locs_mnj_NSX_20220719';

  tag | tag | event_ts
-+-+--
(0 rows)

 > select tag::bytea, * from sentinel where tag = 
'quote_merge_locs_mnj_NSX_20220719' collate "C";
                                  tag                                  | 
                tag                |           event_ts

--+---+---
  \x71756f74655f6d657267655f6c6f63735f6d6e6a5f4e53585f3230323230373139 | 
quote_merge_locs_mnj_NSX_20220719 | 2022-07-19 20:29:14.114238-05

(1 row)

Can anyone explain why the backup does not show the row without 
specifying a collation?


The primary is running CentOS 7.4, the backup is running CentOS 8.  The 
backup was created from a basebackup.  The postgres binaries were built 
on the OS on which they're running.  Neither were built with ICU (which 
I didn't know existed until tracking this down...).  Both databases show 
LC_COLLATE=en_US.UTF-8.


The query "select * from pg_collation where collname like '%en_US%';" 
returns identical results for both databases.


I'm concerned that somehow I've created a backup that is not compatible 
in some mysterious way with my primary.


Can anyone provide any insight on what is going on?


See:

https://wiki.postgresql.org/wiki/Locale_data_changes

In particular:

https://wiki.postgresql.org/wiki/Locale_data_changes#RHEL.2FCentOS



Thank you,

-Kelly





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