Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Tom Lane]

Andres Freund  writes:
> It doesn't seem impossible to get into a situation where syslogger is
> the source of the OOM. Just enabling a lot of logging in a workload with
> many large query strings might do it.  So making it less likely to be
> killed might make the problem worse...

Hm, so that's another angle David didn't report on: is it possible that
his workload could have resulted in a very large volume of incomplete
in-progress log messages?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Andres Freund]

On November 16, 2017 7:06:23 PM PST, Tom Lane  wrote:
>Andres Freund  writes:
>> On 2017-11-16 21:39:49 -0500, Tom Lane wrote:
>>> What might be worth thinking about is allowing the syslogger process
>to
>>> inherit the postmaster's OOM-kill-proofness setting, instead of
>dropping
>>> down to the same vulnerability as the postmaster's other child
>processes.
>
>> Hm. I'm a bit scared about that - it doesn't seem that inconceivable
>> that various backends log humongous multi-line messages, leading to
>> syslogger *actually* taking up a fair amount of memory. Note that
>we're
>> using plain stringinfos that ereport(ERROR) out of memory situations,
>> rather than failing more gracefully.
>
>True, but there's no hard limits on the postmaster's memory consumption
>either ... 

Is there a credible scenario where it'd allocate many gigabytes of memory?

> and if the syslogger does get killed on such a basis, we
>have at the least lost a bunch of log output.  On the whole I think we'd be
>better off trying to prevent OOM kills on the syslogger.  (That doesn't
>preclude other mitigation measures.)

It doesn't seem impossible to get into a situation where syslogger is the 
source of the OOM. Just enabling a lot of logging in a workload with many large 
query strings might do it.  So making it less likely to be killed might make 
the problem worse...

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Tom Lane]

Andres Freund  writes:
> On 2017-11-16 21:39:49 -0500, Tom Lane wrote:
>> What might be worth thinking about is allowing the syslogger process to
>> inherit the postmaster's OOM-kill-proofness setting, instead of dropping
>> down to the same vulnerability as the postmaster's other child processes.

> Hm. I'm a bit scared about that - it doesn't seem that inconceivable
> that various backends log humongous multi-line messages, leading to
> syslogger *actually* taking up a fair amount of memory. Note that we're
> using plain stringinfos that ereport(ERROR) out of memory situations,
> rather than failing more gracefully.

True, but there's no hard limits on the postmaster's memory consumption
either ... and if the syslogger does get killed on such a basis, we have
at the least lost a bunch of log output.  On the whole I think we'd be
better off trying to prevent OOM kills on the syslogger.  (That doesn't
preclude other mitigation measures.)

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Andres Freund]

On 2017-11-16 21:39:49 -0500, Tom Lane wrote:
> > We could work around a situation like that if we made postmaster use a
> > *different* pipe as stderr than the one we're handing to normal
> > backends. If postmaster created a new pipe and closed the read end
> > whenever forking a syslogger, we should get EPIPEs when writing after
> > syslogger died and could fall back to proper stderr or such.
> 
> I think that's nonsense, unfortunately.

Nice phrasing.


> If the postmaster had its own pipe, that would reduce the risk of this
> deadlock because only the postmaster would be filling that pipe, not
> the postmaster and all its other children --- but it wouldn't
> eliminate the risk.

The deadlock happens because postmaster is waiting for syslogger accept
a message, and syslogger waits for postmaster to restart it. To resolve
the deadlock postmasterneeds to not wait for a dead sylogger, even if it
hasn't yet received & processed the SIGCLD - what other postmaster
children do or don't do doesn't matter for resolving that cycle. The
reason postmaster currently block on writing to the pipe, instead of
getting EPIPE, is because both ends of the pipe are still
existing. Which in turn is the case because we need to be able to
restart syslogger without passing a new file descriptor to all
subprocesses.  If postmaster instead uses a different pipe to write to
it'll not block anymore, instead getting EPIPE, and can continue towards
starting a new syslogger.  So I don't think the described deadlock
exists if we were to apply my proposed fix.


What this obviously would not *not* guarantee is being able start a new
syslogger, but it seems fairly impossible to guarantee that. So sure,
other processes would still block until syslogger has successfully
restarted - but it's a resolvable situation rather than a hard deadlock,
which the described situation appears to be.


Note that there's plenty of cases where you could run into this even
without being unable to fork new processes. You'd e.g. could also run
into this while logging the exit of some other subprocess or such,
there's enough ereports in postmaster.


> I doubt the increase in reliability would be enough to justify the
> extra complexity and cost.

I'm doubtful about that too.


> What might be worth thinking about is allowing the syslogger process to
> inherit the postmaster's OOM-kill-proofness setting, instead of dropping
> down to the same vulnerability as the postmaster's other child processes.
> That presumes that this was an otherwise-unjustified OOM kill, which
> I'm not quite sure of ... but it does seem like a situation that could
> arise from time to time.

Hm. I'm a bit scared about that - it doesn't seem that inconceivable
that various backends log humongous multi-line messages, leading to
syslogger *actually* taking up a fair amount of memory. Note that we're
using plain stringinfos that ereport(ERROR) out of memory situations,
rather than failing more gracefully.

- Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Tom Lane]

Andres Freund  writes:
> On 2017-11-06 15:35:03 -0500, Tom Lane wrote:
>> David Pacheco  writes:
>>> I ran into what appears to be a deadlock in the logging subsystem.  It
>>> looks like what happened was that the syslogger process exited because it
>>> ran out of memory.  But before the postmaster got a chance to handle the
>>> SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
>>> That also failed, and the postmaster went to log a message about it, but
>>> it's blocked on the pipe that's normally connected to the syslogger,
>>> presumably because the pipe is full because the syslogger is gone and
>>> hasn't read from it.

>> Ugh.

> I'm somewhat inclined to say that one has to live with this if the
> system is so resource constrainted that processes barely using memory
> get killed.

David's report isn't too clear: did the syslogger process actually run
out of memory and exit of its own volition after an ENOMEM, or did it get
killed by the dreaded OOM killer?  In either case, it's unclear whether
it was really using an excessive amount of memory.  We have not heard
reports suggesting a memory leak in the syslogger, but maybe there is
one under unusual circumstances?

I think you're probably right that the real cause here is the OOM
killer just randomly seizing on the syslogger as a victim process;
although since the syslogger disconnects from shared memory, it's
not very clear why it would score high on the OOM killer's metrics.
The whole thing is definitely odd.

> We could work around a situation like that if we made postmaster use a
> *different* pipe as stderr than the one we're handing to normal
> backends. If postmaster created a new pipe and closed the read end
> whenever forking a syslogger, we should get EPIPEs when writing after
> syslogger died and could fall back to proper stderr or such.

I think that's nonsense, unfortunately.  If the postmaster had its
own pipe, that would reduce the risk of this deadlock because only
the postmaster would be filling that pipe, not the postmaster and
all its other children --- but it wouldn't eliminate the risk.
I doubt the increase in reliability would be enough to justify the
extra complexity and cost.

What might be worth thinking about is allowing the syslogger process to
inherit the postmaster's OOM-kill-proofness setting, instead of dropping
down to the same vulnerability as the postmaster's other child processes.
That presumes that this was an otherwise-unjustified OOM kill, which
I'm not quite sure of ... but it does seem like a situation that could
arise from time to time.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Michael Paquier]

On Fri, Nov 17, 2017 at 11:14 AM, Andres Freund  wrote:
> On 2017-11-17 11:09:56 +0900, Michael Paquier wrote:
>> when redirection_done is switched to true because the first process
>> generating a message to the syslogger pipe needs to open it first if
>> not done yet?
>
> I can't follow. The syslogger pipe is created when the first syslogger
> is started (before it's forked!). Which happens before other processes
> are created, because they all need to inherit that file descriptor.

Ah, OK. I didn't recall this dependency. Sorry for the confusion.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Andres Freund]

On 2017-11-17 11:09:56 +0900, Michael Paquier wrote:
> On Fri, Nov 17, 2017 at 10:50 AM, Andres Freund  wrote:
> > On 2017-11-06 15:35:03 -0500, Tom Lane wrote:
> >> David Pacheco  writes:
> >> > I ran into what appears to be a deadlock in the logging subsystem.  It
> >> > looks like what happened was that the syslogger process exited because it
> >> > ran out of memory.  But before the postmaster got a chance to handle the
> >> > SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
> >> > That also failed, and the postmaster went to log a message about it, but
> >> > it's blocked on the pipe that's normally connected to the syslogger,
> >> > presumably because the pipe is full because the syslogger is gone and
> >> > hasn't read from it.
> >>
> >> Ugh.
> >
> > I'm somewhat inclined to say that one has to live with this if the
> > system is so resource constrainted that processes barely using memory
> > get killed.
> >
> > We could work around a situation like that if we made postmaster use a
> > *different* pipe as stderr than the one we're handing to normal
> > backends. If postmaster created a new pipe and closed the read end
> > whenever forking a syslogger, we should get EPIPEs when writing after
> > syslogger died and could fall back to proper stderr or such.
> 
> I don't have the code on top of my mind, but isn't a custom fd causing
> a small penalty

Sure, there's some minor overhead because every process would need to
close another fd after forking.


> when redirection_done is switched to true because the first process
> generating a message to the syslogger pipe needs to open it first if
> not done yet?

I can't follow. The syslogger pipe is created when the first syslogger
is started (before it's forked!). Which happens before other processes
are created, because they all need to inherit that file descriptor.


> So you'd need proper locking to save from race conditions.

I completely fail to see why this'd be the case. All I'm talking about
is using another pipe between syslogger and postmaster than between
other-processes and syslogger.

Greetings,

Andres Freund


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Michael Paquier]

On Fri, Nov 17, 2017 at 10:50 AM, Andres Freund  wrote:
> On 2017-11-06 15:35:03 -0500, Tom Lane wrote:
>> David Pacheco  writes:
>> > I ran into what appears to be a deadlock in the logging subsystem.  It
>> > looks like what happened was that the syslogger process exited because it
>> > ran out of memory.  But before the postmaster got a chance to handle the
>> > SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
>> > That also failed, and the postmaster went to log a message about it, but
>> > it's blocked on the pipe that's normally connected to the syslogger,
>> > presumably because the pipe is full because the syslogger is gone and
>> > hasn't read from it.
>>
>> Ugh.
>
> I'm somewhat inclined to say that one has to live with this if the
> system is so resource constrainted that processes barely using memory
> get killed.
>
> We could work around a situation like that if we made postmaster use a
> *different* pipe as stderr than the one we're handing to normal
> backends. If postmaster created a new pipe and closed the read end
> whenever forking a syslogger, we should get EPIPEs when writing after
> syslogger died and could fall back to proper stderr or such.

I don't have the code on top of my mind, but isn't a custom fd causing
a small penalty when redirection_done is switched to true because the
first process generating a message to the syslogger pipe needs to open
it first if not done yet? So you'd need proper locking to save from
race conditions. Or is the first message redirected message always
generated by the postmaster or the syslogger? I don't recall that this
is actually true..
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[Andres Freund]

On 2017-11-06 15:35:03 -0500, Tom Lane wrote:
> David Pacheco  writes:
> > I ran into what appears to be a deadlock in the logging subsystem.  It
> > looks like what happened was that the syslogger process exited because it
> > ran out of memory.  But before the postmaster got a chance to handle the
> > SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
> > That also failed, and the postmaster went to log a message about it, but
> > it's blocked on the pipe that's normally connected to the syslogger,
> > presumably because the pipe is full because the syslogger is gone and
> > hasn't read from it.
> 
> Ugh.

I'm somewhat inclined to say that one has to live with this if the
system is so resource constrainted that processes barely using memory
get killed.

We could work around a situation like that if we made postmaster use a
*different* pipe as stderr than the one we're handing to normal
backends. If postmaster created a new pipe and closed the read end
whenever forking a syslogger, we should get EPIPEs when writing after
syslogger died and could fall back to proper stderr or such.

Greetings,

Andres Freund


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[David Pacheco]

On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane  wrote:

> David Pacheco  writes:
> > I ran into what appears to be a deadlock in the logging subsystem.  It
> > looks like what happened was that the syslogger process exited because it
> > ran out of memory.  But before the postmaster got a chance to handle the
> > SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
> > That also failed, and the postmaster went to log a message about it, but
> > it's blocked on the pipe that's normally connected to the syslogger,
> > presumably because the pipe is full because the syslogger is gone and
> > hasn't read from it.
>
> Ugh.
>


Should I file a bug on this issue?

Thanks,
Dave

Re: [GENERAL] pg_restore load data

[Ron Johnson]

On 11/16/2017 03:13 PM, bricklen wrote:


On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson > wrote:


v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.)

During a "whole database" restore using pg_restore of a custom dump,
when is the data actually loaded?  I've looked in the list output and
don't see any "load" statements.


Look for COPY lines, that's how the data is restored.


$ pg_restore -l CDSHA01.dump > CDSHA01.txt
$ grep --color -i copy CDSHA01.txt
$ echo $?
1

There are lots of "restoring data", though.  I should have thought to grep 
for that.


One thing that puzzles me is how fast the tables (even large ones) loaded 
compared to how slow the pg_dump -Fc was.  Granted, I'm running -j4 but 
still, these were some really large, poorly compressible tables (the dump 
file was about as big as du -mc data/base).


--
World Peace Through Nuclear Pacification

Re: [GENERAL] pg_restore load data

[melvin6925]

The command is COPY, not load.


Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone
 Original message From: Ron Johnson  
Date: 11/16/17  16:07  (GMT-05:00) To: pgsql-general@postgresql.org Subject: 
[GENERAL] pg_restore load data 
Hi,

v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.)

During a "whole database" restore using pg_restore of a custom dump, when is 
the data actually loaded?  I've looked in the list output and don't see any 
"load" statements.

Thanks

-- 
World Peace Through Nuclear Pacification



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_restore load data

[bricklen]

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson  wrote:

> v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.)
>
> During a "whole database" restore using pg_restore of a custom dump, when
> is the data actually loaded?  I've looked in the list output and don't see
> any "load" statements.
>

Look for COPY lines, that's how the data is restored.

[GENERAL] pg_restore load data

[Ron Johnson]

Hi,

v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.)

During a "whole database" restore using pg_restore of a custom dump, when is 
the data actually loaded?  I've looked in the list output and don't see any 
"load" statements.


Thanks

--
World Peace Through Nuclear Pacification



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] expanded auto and header linestyle double

[Pavel Stehule]

2017-11-16 21:16 GMT+01:00 Matt Zagrabelny :

> Thanks for the reply, Pavel!
>
> On Thu, Nov 16, 2017 at 1:01 AM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny :
>>
>>> Greetings,
>>>
>>> Using PG 10.1.
>>>
>>> In my .psqlrc I have:
>>>
>>> \x auto
>>> \pset linestyle 'unicode'
>>> \pset unicode_header_linestyle double
>>>
>>> and when the output is expanded, I do not see a double line for the
>>> first record, but I do for all subsequent records. For example:
>>>
>>> % select  * from artist;
>>> ─[ RECORD 1 ]─
>>> artistid │ 1
>>> name │ AC/DC
>>> ═[ RECORD 2 ]═
>>> artistid │ 2
>>> name │ Accept
>>> ═[ RECORD 3 ]═
>>> artistid │ 3
>>> name │ Aerosmith
>>> ═[ RECORD 4 ]═
>>> artistid │ 4
>>> name │ Alanis Morissette
>>> ═[ RECORD 5 ]═
>>> artistid │ 5
>>>
>>> I would like to have the initial "RECORD 1" line have the same "double"
>>> linestyle as the other records.
>>>
>>> Am I missing a config item?
>>>
>>
>> yes - it is border line
>>
>> use \pset border 2
>>
>>
> Hmmm I didn't use the "border" setting.
>
>
>> and you understand
>>
>> you are missing
>>
>> \pset unicode_border_linestyle double
>>
>
> But I did use the above setting.
>
> So my .psqlrc looks like:
>
> \pset linestyle 'unicode'
> \pset unicode_border_linestyle double
> \pset unicode_header_linestyle double
>
> ═[ RECORD 1 ]
> artistid │ 1
> name │ AC/DC
> ═[ RECORD 2 ]
> artistid │ 2
> name │ Accept
> ═[ RECORD 3 ]
> artistid │ 3
> name │ Aerosmith
>
> Thanks for helping me get it corrected.
>
> It still feels strange that when using "expanded" display the first record
> separator would be categorized under "border_linestyle" and the remaining
> record separators would be categorized under "header_linestyle".
>

There is some simplification - this line is header and together border. It
has sense when you use border 2

Regards

Pavel


> Cheers!
>
> -m
>

Re: [GENERAL] expanded auto and header linestyle double

[Matt Zagrabelny]

Thanks for the reply, Pavel!

On Thu, Nov 16, 2017 at 1:01 AM, Pavel Stehule 
wrote:

> Hi
>
> 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny :
>
>> Greetings,
>>
>> Using PG 10.1.
>>
>> In my .psqlrc I have:
>>
>> \x auto
>> \pset linestyle 'unicode'
>> \pset unicode_header_linestyle double
>>
>> and when the output is expanded, I do not see a double line for the first
>> record, but I do for all subsequent records. For example:
>>
>> % select  * from artist;
>> ─[ RECORD 1 ]─
>> artistid │ 1
>> name │ AC/DC
>> ═[ RECORD 2 ]═
>> artistid │ 2
>> name │ Accept
>> ═[ RECORD 3 ]═
>> artistid │ 3
>> name │ Aerosmith
>> ═[ RECORD 4 ]═
>> artistid │ 4
>> name │ Alanis Morissette
>> ═[ RECORD 5 ]═
>> artistid │ 5
>>
>> I would like to have the initial "RECORD 1" line have the same "double"
>> linestyle as the other records.
>>
>> Am I missing a config item?
>>
>
> yes - it is border line
>
> use \pset border 2
>
>
Hmmm I didn't use the "border" setting.


> and you understand
>
> you are missing
>
> \pset unicode_border_linestyle double
>

But I did use the above setting.

So my .psqlrc looks like:

\pset linestyle 'unicode'
\pset unicode_border_linestyle double
\pset unicode_header_linestyle double

═[ RECORD 1 ]
artistid │ 1
name │ AC/DC
═[ RECORD 2 ]
artistid │ 2
name │ Accept
═[ RECORD 3 ]
artistid │ 3
name │ Aerosmith

Thanks for helping me get it corrected.

It still feels strange that when using "expanded" display the first record
separator would be categorized under "border_linestyle" and the remaining
record separators would be categorized under "header_linestyle".

Cheers!

-m

Re: [GENERAL] Build in function to verify email addresses

[Michael Nolan]

On Thu, Nov 16, 2017 at 1:56 AM, Nick Dro  wrote:

> I beleieve that every information system has the needs to send emails.
> Currently PostgreSQL doesn't have a function which gets TEXT and return
> true if it's valid email address (x...@yyy.com / .co.ZZ)
> Do you believe such function should exist in PostgreSQL or it's best to
> let every user to implement his own function?
>

There's a world of difference between an email address that is well-formed
and one that actually works.

In the systems I administer there's a lot of time spent dealing with
bounced mail to make sure that the email addresses we have actually reach
someone, hopefully the intended target.  And in the US, bulk emailers also
have to deal with the CAN-SPAM act, which specifies procedures that must be
in place to allow easy administrative options to remove one'e email address
from mailing lists.

Procedures to verify that an email address works and to administer its use
under rules like CAN_SPAM cannot exist solely within the database itself.
And as others have noted, what makes for a 'well-formed' email address has
always been a bit complicated.
--
Mike Nolan

Re: [GENERAL] Build in function to verify email addresses

[Andreas Joseph Krogh]

På torsdag 16. november 2017 kl. 09:05:00, skrev Pavel Stehule <
pavel.steh...@gmail.com >:
Hi   2017-11-16 8:56 GMT+01:00 Nick Dro >: I beleieve that every information system has 
the needs to send emails.
Currently PostgreSQL doesn't have a function which gets TEXT and return true 
if it's valid email address (x...@yyy.com / .co.ZZ)
Do you believe such function should exist in PostgreSQL or it's best to let 
every user to implement his own function?

 
 I don't think so this functionality should be in upstream - but it is good 
use for some extension and placing it in PGXN or PostgreSQL community 
repository.
 
Postgres has good regexp support and this case can be implemented by one 
regexp.
 
you can use PLPerlu and use some modules from CPAN
 
http://search.cpan.org/~rjbs/Email-Valid-1.202/lib/Email/Valid.pm 

 
Regards
 
Pavel



 
In general, I see no reason for a modern RDBMS not to provide an 
email-datatype. IMV that's no different from other types which also could have 
been plain-text but are convenient to have datatypes for.
Being an open-source project I guess one must show initiative and start a 
discussion on -hackers to see what interesst there's in having one in core. I 
for one hope there will be.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Build in function to verify email addresses

[bto...@computer.org]

- Original Message -


From: "Nick Dro"  
To: pgsql-general@postgresql.org 
Sent: Thursday, November 16, 2017 2:56:42 AM 
Subject: [GENERAL] Build in function to verify email addresses 

I beleieve that every information system has the needs to send emails. 
Currently PostgreSQL doesn't have a function which gets TEXT and return true if 
it's valid email address (x...@yyy.com / .co.ZZ) 
Do you believe such function should exist in PostgreSQL or it's best to let 
every user to implement his own function? 




I don't mean to sound snarky, but did you even try to google search? 

https://pgxn.org/dist/email/ 

https://github.com/asotolongo/email 

or 

https://github.com/petere/pgemailaddr 



-- B 

Re: [GENERAL] Build in function to verify email addresses

[Chris Withers]

I'm pretty sure this isn't as simple as you think it is, I'd suggest 
having a good read of:


https://stackoverflow.com/a/201378/216229

Chris


On 16/11/2017 07:56, Nick Dro wrote:

I beleieve that every information system has the needs to send emails.
Currently PostgreSQL doesn't have a function which gets TEXT and 
return true if it's valid email address (x...@yyy.com / .co.ZZ)
Do you believe such function should exist in PostgreSQL or it's best 
to let every user to implement his own function?




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Build in function to verify email addresses

[Pavel Stehule]

Hi

2017-11-16 8:56 GMT+01:00 Nick Dro :

> I beleieve that every information system has the needs to send emails.
> Currently PostgreSQL doesn't have a function which gets TEXT and return
> true if it's valid email address (x...@yyy.com / .co.ZZ)
> Do you believe such function should exist in PostgreSQL or it's best to
> let every user to implement his own function?
>

I don't think so this functionality should be in upstream - but it is good
use for some extension and placing it in PGXN or PostgreSQL community
repository.

Postgres has good regexp support and this case can be implemented by one
regexp.

you can use PLPerlu and use some modules from CPAN

http://search.cpan.org/~rjbs/Email-Valid-1.202/lib/Email/Valid.pm

Regards

Pavel