Why in pgAdmin an active session is marked/highlighted in Red

[Shaozhong SHI]

I never came across this before and wonder why?

Regards,

David

Re:Why in pgAdmin an active session is marked/highlighted in Red

[chris]

Which version? Active session should be green.


Regards,
Chris
On 11/11/2021 21:24，Shaozhong SHI wrote：
I never came across this before and wonder why?


Regards,


David

Phonetic representation LENGHT for DMETAPHONE, is there a way to make it longer?

[Jean Baro]

Hello people,

Please, I am working on a PoC for Real-time Person Identification, and one
of the critical aspects of it is to support both minor misspelling and
phonetic variations of First, Middle, and Last name. Like HarinGton ==
HarrinBton or RaphEAl == RafAEl. It's working for longer names, but it's a
bit more imprecise for names like Lee and John. I am using Double Metaphone
through dmetaphone() and dmetaphone_alt() in PostgreSQL 13.3 (Supabase.io).
And although I appreciate Double Metaphone it returns a (too?) short string
as the phonetic representation compared to metaphone(string, length).
metaphone() has parameters to make the resulting phonetic representation
longer. I investigated dmetaphone() and couldn't find anything other than
the default function. Is there a way of making dmetaphone() and
dmetaphone_alt() return a longer phonetic representation similar to
metaphone()'s, but with a ALT variation?.

Any help would be much appreciated.

Thanks

Re: Phonetic representation LENGHT for DMETAPHONE, is there a way to make it longer?

[Jean Baro]

Sorry, please remove this question, I sent it to the wrong mailing list.

On Thu, Nov 11, 2021 at 11:20 AM Jean Baro  wrote:

> Hello people,
>
> Please, I am working on a PoC for Real-time Person Identification, and one
> of the critical aspects of it is to support both minor misspelling and
> phonetic variations of First, Middle, and Last name. Like HarinGton ==
> HarrinBton or RaphEAl == RafAEl. It's working for longer names, but it's a
> bit more imprecise for names like Lee and John. I am using Double Metaphone
> through dmetaphone() and dmetaphone_alt() in PostgreSQL 13.3 (Supabase.io).
> And although I appreciate Double Metaphone it returns a (too?) short string
> as the phonetic representation compared to metaphone(string, length).
> metaphone() has parameters to make the resulting phonetic representation
> longer. I investigated dmetaphone() and couldn't find anything other than
> the default function. Is there a way of making dmetaphone() and
> dmetaphone_alt() return a longer phonetic representation similar to
> metaphone()'s, but with a ALT variation?.
>
> Any help would be much appreciated.
>
> Thanks
>

Re: Why in pgAdmin an active session is marked/highlighted in Red

[Laurenz Albe]

On Thu, 2021-11-11 at 13:24 +, Shaozhong SHI wrote:
> I never came across this before and wonder why?

I think I remember that means that the session is stuck behind a lock,
but I am not certain.  Look for "wait_event_type" in "pg_stat_statement".

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

Are all unlogged tables in any case truncated after a server-crash?

[sch8el]

Hi everyone,

every few weeks I use Postgres ability, to import huge data sets very 
fast by means of "unlogged tables". The bulk load (consisting of plenty 
"copy"- & DML-Stmts) and the spatial index creation afterwards, takes 
about 5 hours on a proper server  (pg12.7 & PostGIS-Extension). After 
that all unlogged tables remain completely unchanged (no 
DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get 
never "unclean" and should not be truncated after a server crash.


BTW, if I set all unlogged tables to logged after bulk load, it takes 
additional 1.5 hours, mainly because of re-indexing, I suppose. I assume 
that a restart of the database after a server crash takes another 1.5 
hours (reading from WAL) until the database is up and running.


Therefore I am seeking a strategy, to not tagging those tables as 
"unclean" and not truncating all unlogged tables on server restart.



Cheers and regards.

Re: Are all unlogged tables in any case truncated after a server-crash?

[David G. Johnston]

On Thu, Nov 11, 2021 at 11:39 AM  wrote:

> After
> that all unlogged tables remain completely unchanged (no
> DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get
> never "unclean" and should not be truncated after a server crash.
>

The server cannot make this assumption so it truncates unlogged relations
upon an unclean shutdown/crash because it has no WAL with which to ensure a
proper restoration.

BTW, if I set all unlogged tables to logged after bulk load, it takes
> additional 1.5 hours, mainly because of re-indexing, I suppose.


More likely it is writing the entire table, and all of its indexes, to WAL.

I assume
> that a restart of the database after a server crash takes another 1.5
> hours (reading from WAL) until the database is up and running.
>

That would be incorrect.  See "CHECKPOINT".


> Therefore I am seeking a strategy, to not tagging those tables as
> "unclean" and not truncating all unlogged tables on server restart.
>
>
There is no middle ground that I am aware of.  Either the contents of the
table are in WAL ,or they are not.  If not, they can be lost upon an
unclean shutdown.  For manually initiated shutdowns you do have the option
to do so cleanly.

This topic (unlogged optimizations) does draw quite a bit of attention
every year but so far the problem of proving to the system that the
physical file on disk is a truly accurate representation of the post-crash
relation is yet unsolved.

David J.

Pg_hba.conf problem after unexpected IP change

[Yessica Brinkmann]

 Hello. I write for the following reason: I had configured a pg_hba.conf
file, which I am using with some debian virtual machines, to be able to
communicate between two different virtual machines.
Well, it turns out that at one point when I was using my virtual machines,
suddenly and unexpectedly, the IP of one of the virtual machines changed.
I was trying to find out why the commands that I was executing between one
virtual machine and the other did not work for me, until finally I came to
the conclusion that the IP of one of my virtual machines had changed.
Well, then, I went to modify my pg_hba.conf file so that it reflected the
new IP that I did not know why it changed, and to continue communicating
between the two virtual machines, but it turns out that when I change my
pg_hba.conf file, the connection still does not work for me .
The new line in my pg_hba.conf file is as follows:
host ds2 postgres 192.168.52.153/32 trust
and the line above was:
host ds2 postgres 192.168.52.150/32 trust
I clarify that right now when doing ip addr in the virtual machine whose IP
changed unexpectedly, the following IP address appears: 192.168.52.153/24.
I will greatly appreciate a help please.
Regards,
Yessica Brinkmann


Libre
de virus. www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: Pg_hba.conf problem after unexpected IP change

[Adrian Klaver]

On 11/11/21 17:14, Yessica Brinkmann wrote:
Hello. I write for the following reason: I had configured a pg_hba.conf 
file, which I am using with some debian virtual machines, to be able to 
communicate between two different virtual machines.


Well, then, I went to modify my pg_hba.conf file so that it reflected 
the new IP that I did not know why it changed, and to continue 
communicating between the two virtual machines, but it turns out that 
when I change my pg_hba.conf file, the connection still does not work 
for me .


Did you reload or restart the server after making the change?

I clarify that right now when doing ip addr in the virtual machine whose 
IP changed unexpectedly, the following IP address appears: 
192.168.52.153/24 .

I will greatly appreciate a help please.
Regards,
Yessica Brinkmann




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

I added a ?? operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[??????????????]

Hello everyone??


I added a ?? operator to scan.l. The sqrt function is still used internally, 
but there is a problem now, which affects the := and .. operators of the 
database.


# Description of Requirement:
1??select ?? num1; function
2??The value of num1 is required to be: [0,9223372036854775807]
3 The operation does not allow decimals


I have now developed this feature on the PostgreSQL 14.0 kernel! But it affects 
the original function of the database:


# Affected place
1?? := assignment operator
2?? Operator in 1..10


# Recurring problem
1??make check

sqrt_bigint.patch
Description: Binary data

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[David G. Johnston]

On Thu, Nov 11, 2021 at 8:42 PM 孤傲小二～阿沐 <2903807...@qq.com> wrote:

> I added a √ operator to scan.l.
>

Why?


> The sqrt function is still used internally, but there is a problem now,
> which affects the := and .. operators of the database.
>

Someone else will have to volunteer their time to cover this learning curve
(I couldn't even if I wanted to).  But given that what you are trying to
accomplish is not something we'd likely consider adding to the core server
(we created CREATE OPERATOR instead) that seems like a bit of an ask.


> # Description of Requirement:
> 1、select √ num1; function
> 2、The value of num1 is required to be: [0,9223372036854775807]
> 3、√ The operation does not allow decimals
>

Per 2 the value of num1 is a decimal yet per 3 the operation is required to
not allow decimals?

David J.

?????? I added a ?? operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[??????????????]

Hello, thank you for your busy reply. The purpose of this development is to 
learn PostgreSQL's lexical parsing process in depth, and it has no substantial 
effect. You can ignore this. The limitation of the scene can also be ignored. 


The problem now is that select ?? 1024; can be used normally, but it will 
affect the existing functions. 


I don??t know why this happened. Please forgive me for not clarifying my focus.




--  --
??: 
   "David G. Johnston"  
  

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[David G. Johnston]

Guessing here but try a different symbol, something in ASCII (I'm guessing
the current choice is not).  The fact that you added your symbol directly
above dotdot and colonequals is suspicious...

David J.

On Thu, Nov 11, 2021, 21:32 孤傲小二～阿沐 <2903807...@qq.com> wrote:

> Hello, thank you for your busy reply. The purpose of this development is
> to learn PostgreSQL's lexical parsing process in depth, and it has no
> substantial effect. You can ignore this. The limitation of the scene can
> also be ignored.
>
> The problem now is that select √ 1024; can be used normally, but it will
> affect the existing functions.
>
> I don’t know why this happened. Please forgive me for not clarifying my
> focus.
>
>
> -- 原始邮件 --
> *发件人:* "David G. Johnston" ;
> *发送时间:* 2021年11月12日(星期五) 中午12:23
> *收件人:* "孤傲小二～阿沐"<2903807...@qq.com>;
> *抄送:* "pgsql-general";
> *主题:* Re: I added a √ operator, the sqrt function is still used
> internally, but now there is a problem, it affects the := and .. operators
> of the database
>
> On Thu, Nov 11, 2021 at 8:42 PM 孤傲小二～阿沐 <2903807...@qq.com> wrote:
>
>> I added a √ operator to scan.l.
>>
>
> Why?
>
>
>> The sqrt function is still used internally, but there is a problem now,
>> which affects the := and .. operators of the database.
>>
>
> Someone else will have to volunteer their time to cover this learning
> curve (I couldn't even if I wanted to).  But given that what you are trying
> to accomplish is not something we'd likely consider adding to the core
> server (we created CREATE OPERATOR instead) that seems like a bit of an ask.
>
>
>> # Description of Requirement:
>> 1、select √ num1; function
>> 2、The value of num1 is required to be: [0,9223372036854775807]
>> 3、√ The operation does not allow decimals
>>
>
> Per 2 the value of num1 is a decimal yet per 3 the operation is required
> to not allow decimals?
>
> David J.
>
>

?????? I added a ?? operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[??????????????]

Hello, do you mean there is a problem with this symbol itself? But so far, 
there are no problems when using it.


sqrt "??"


So where do you think this should be added? I tried to change the position but 
the error still persists





--  --
??: 
   "David G. Johnston"  
  

Re: I added a ?? operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[Tom Lane]

"=?gb18030?B?ucKwwdChtv6hq7Ci4+U=?=" <2903807...@qq.com> writes:
> # Description of Requirement:
> 1¡¢select ¡Ì num1; function
> 2¡¢The value of num1 is required to be: [0,9223372036854775807]
> 3¡¢¡Ì The operation does not allow decimals

Looks suspiciously like a homework assignment.

> I have now developed this feature on the PostgreSQL 14.0 kernel! But it 
> affects the original function of the database:
> # Affected place
> 1¡¢ := assignment operator
> 2¡¢ Operator in 1..10

Today's lesson is: read the comments on the code you're modifying.
Notably on gram.y's list of "non keyword" tokens:

 * Non-keyword token types.  These are hard-wired into the "flex" lexer.
 * They must be listed first so that their numeric codes do not depend on
 * the set of keywords.  PL/pgSQL depends on this so that it can share the
 * same lexer.  If you add/change tokens here, fix PL/pgSQL to match!

Since you didn't do that, PL/pgSQL is confused about the token codes
in use for DOT_DOT and so on.

regards, tom lane

回复： I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[孤傲小二～阿沐]

Hello, I think what you said is right, it should be the problem. But I don't 
know what to do in the lexical analysis system of plpgsql to solve this problem.




-- 原始邮件 --
发件人:
"Tom Lane"  
  

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[David G. Johnston]

On Thursday, November 11, 2021, 孤傲小二～阿沐 <2903807...@qq.com> wrote:

> Hello, I think what you said is right, it should be the problem. But I
> don't know what to do in the lexical analysis system of plpgsql to solve
> this problem.
>
>
“To match” means keep two copies identical, in this case manually.  Try
grepping for the pre-change line you are modifying to see where its twin is.

David J.

?????? I added a ?? operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

[??????????????]

Hello everyone, I modified src/fe_utils/psqlscan.l 
src/interfaces/ecpg/preproc/pgc.l src/pl/plpgsql/src/pl_gram.y according to his 
suggestion to keep scan.l gram.y consistent , But still error




--  --
??: 
   "David G. Johnston"  
  

sqrt_bigint_v2

[??????????????]

This function is already OK, I think tom is correct. After I modified the 
relevant files, the problem was solved.


Now my newly added functions and the regression test of the database itself are 
all OK


Thank you both for your continuous support and blessings from China!

sqrt_bigint_v2.patch
Description: Binary data

sqrt_bigint_v2

[??????????????]

This function is already OK, I think tom is correct. After I modified the 
relevant files, the problem was solved.


Now my newly added functions and the regression test of the database itself are 
all OK


Thank you both for your continuous support and blessings from China!

sqrt_bigint_v2.patch
Description: Binary data

Re: Are all unlogged tables in any case truncated after a server-crash?

[Laurenz Albe]

On Thu, 2021-11-11 at 18:39 +, sch...@posteo.de wrote:
> every few weeks I use Postgres ability, to import huge data sets very 
> fast by means of "unlogged tables". The bulk load (consisting of plenty 
> "copy"- & DML-Stmts) and the spatial index creation afterwards, takes 
> about 5 hours on a proper server  (pg12.7 & PostGIS-Extension). After 
> that all unlogged tables remain completely unchanged (no 
> DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get 
> never "unclean" and should not be truncated after a server crash.

There is no way to achieve that.

But you could keep the "huge data sets" around and load them again if
your server happens to crash (which doesn't happen often, I hope).

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