Re: JIT on Windows with Postgres 12.1

[Michael Paquier]

On Wed, Feb 12, 2020 at 07:32:08PM -0800, Andres Freund wrote:
> That's not really the hard part. That's integrating the generation of
> LLVM bitcode files into the buildsystem. As the absolute minimum
> llvmjit_types.bc needs to be generated, but to be meaningfully supported
> we'd need to generate all the .bc files we're generating on make based
> builds.

Oh, I see.  That's indeed...  Not straight-forward.
--
Michael


signature.asc
Description: PGP signature

Re: JIT on Windows with Postgres 12.1

[Andres Freund]

Hi,

On 2020-02-13 12:25:40 +0900, Michael Paquier wrote:
> On Wed, Feb 12, 2020 at 12:05:11PM -0800, Andres Freund wrote:
> > Yes, LLVM would work on windows. I'd not even be surprised if one could
> > make it work on windows already when using a mingw based build, instead
> > of msvc.
> 
> For MSVC, assuming that you have the proper dependencies for JIT
> compilation and LLVM available, and just for the reference if anybody
> is interesting in making that happen (Tom and Andres are aware of that
> already), one would need to do roughly an update of src/tools/msvc/ to
> add a new option path to LLVM, and update install-windows.sgml to add
> the new optional dependency.  Note that including LLVM in the builds
> means adding conditionally the files part of src/backend/jit/llvm/,
> but one can take for example OpenSSL as a model of how it happens.
> That would take care of the build.

That's not really the hard part. That's integrating the generation of
LLVM bitcode files into the buildsystem. As the absolute minimum
llvmjit_types.bc needs to be generated, but to be meaningfully supported
we'd need to generate all the .bc files we're generating on make based
builds.

Greetings,

Andres Freund

Re: JIT on Windows with Postgres 12.1

[Michael Paquier]

On Wed, Feb 12, 2020 at 12:05:11PM -0800, Andres Freund wrote:
> Yes, LLVM would work on windows. I'd not even be surprised if one could
> make it work on windows already when using a mingw based build, instead
> of msvc.

For MSVC, assuming that you have the proper dependencies for JIT
compilation and LLVM available, and just for the reference if anybody
is interesting in making that happen (Tom and Andres are aware of that
already), one would need to do roughly an update of src/tools/msvc/ to
add a new option path to LLVM, and update install-windows.sgml to add
the new optional dependency.  Note that including LLVM in the builds
means adding conditionally the files part of src/backend/jit/llvm/,
but one can take for example OpenSSL as a model of how it happens.
That would take care of the build.  There may be some
Windows-dependent stuff needed to make JIT work properly on Windows
though.
--
Michael


signature.asc
Description: PGP signature

Re: Function not imported in Entity Framework

[Vikram Sah]

Thanks for your prompt response sir,.
For now We have decided to use Dapper ORM by considering the future
changes.

I surely look for you guidelines in coming future.

*Thank you so much again for clearing my confusion.*

Best regards
Ravi


On Wed, Feb 12, 2020 at 5:37 PM farjad.farid 
wrote:

> Thanks for highlighting this issue.
>
>
>
> I have tested this using .net 4.8 and Core 3.1 against Sql Server, they
> all exhibit the same problem.
>
>
>
> The best course of action is probably to identify a workaround, based on
> your project, until Microsoft team have the time to fix the issue.
> It would be good idea to report this to Microsoft.
>
>
>
>
>
>
>
> *From:* Vikram Sah 
> *Sent:* 2020 February 12 01:18
> *To:* Adrian Klaver 
> *Cc:* pgsql-gene...@postgresql.org
> *Subject:* Re: Function not imported in Entity Framework
>
>
>
> Sir,
>
> I asked this in npgsql community who have developed 
> *"**EntityFramework6.Npgsql"
> , *link is :
>
>  https://github.com/npgsql/npgsql/issues/948. and developer named  *Shay
> Rojansky *aka *roji* has replied as below image:
>
> [image: @roji]
>
>
>
> On Tue, Feb 11, 2020 at 10:30 PM Adrian Klaver 
> wrote:
>
> On 2/11/20 8:35 AM, Vikram Sah wrote:
> > Got it sir, but unfortunately they have not provided any solution for
> > the last 5 days.
>
> Where have you asked besides here?
>
> >
> > Thanks
> >
> > On Tue, 11 Feb 2020, 10:12 pm Adrian Klaver,  > > wrote:
> >
> > On 2/11/20 3:56 AM, Vikram Sah wrote:
> >  > Thank you so much sir, but it didn't work for me as the database
> >  > function in PostgreSQL and EntityFramework6.Npgsql  is used as
> > provider
> >  > for entity framework.
> >
> > I have no idea where to go from here. Your best bet would be to reach
> > out to the EntityFramework6.Npgsql community
> >
> >  >
> >  > Thanks!
> >  > Vikram
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Avoiding out of date statistics / planner

[Tomas Vondra]

On Wed, Feb 12, 2020 at 10:23:22AM -0700, Michael Lewis wrote:

It may also be worth noting that it is possible to make autovacuum/analyze
more aggressive, perhaps only on the tables that see large changes in data
that might result in a statistics issue. If you could share a query,
explain analyze output, and pseudo code or at least description of what
sort of bulk operations are being done, then more insight could be offered.


Another thing you can do is deploy auto_explain, and log explain plan
for long-runnning queries. That won't fix the root cause, but it will
help you with confirming the root cause - you'll see the query plan,
which should give you enough context.

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: JIT on Windows with Postgres 12.1

[Andres Freund]

Hi,

On 2020-02-12 14:54:56 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > On 2020-02-12 09:39:19 +0100, Josef Šimánek wrote:
> >> I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.
> 
> > It's not currently supported by the windows buildsystem. I don't
> > personally have the expertise to change that (I've not worked on windows
> > after ~2003). So somebody would have to step in to make that work,
> > first.
> 
> First you would need either a Windows-native LLVM, or a JIT provider
> based on Microsoft's compiler.  I don't know if the former exists
> (it would not be our job to create it).

Yes, LLVM would work on windows. I'd not even be surprised if one could
make it work on windows already when using a mingw based build, instead
of msvc.


> I'm not sure if the latter is possible, but for sure it'd be a large
> task if it is possible.

Yea, it'd be a lot of work to add a second provider. No iea if there's
any support for it around visual studio - but I also see no point going
there.

Greetings,

Andres Freund

Re: JIT on Windows with Postgres 12.1

[Justin]

Here is a link to build  LLVM on windows

http://llvm.org/docs/GettingStartedVS.html

On Wed, Feb 12, 2020 at 2:55 PM Tom Lane  wrote:

> Andres Freund  writes:
> > On 2020-02-12 09:39:19 +0100, Josef Šimánek wrote:
> >> I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.
>
> > It's not currently supported by the windows buildsystem. I don't
> > personally have the expertise to change that (I've not worked on windows
> > after ~2003). So somebody would have to step in to make that work,
> > first.
>
> First you would need either a Windows-native LLVM, or a JIT provider
> based on Microsoft's compiler.  I don't know if the former exists
> (it would not be our job to create it).  I'm not sure if the latter
> is possible, but for sure it'd be a large task if it is possible.
>
> regards, tom lane
>
>
>

Re: JIT on Windows with Postgres 12.1

[Tom Lane]

Andres Freund  writes:
> On 2020-02-12 09:39:19 +0100, Josef Šimánek wrote:
>> I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.

> It's not currently supported by the windows buildsystem. I don't
> personally have the expertise to change that (I've not worked on windows
> after ~2003). So somebody would have to step in to make that work,
> first.

First you would need either a Windows-native LLVM, or a JIT provider
based on Microsoft's compiler.  I don't know if the former exists
(it would not be our job to create it).  I'm not sure if the latter
is possible, but for sure it'd be a large task if it is possible.

regards, tom lane

Re: JIT on Windows with Postgres 12.1

[Andres Freund]

Hi,

On 2020-02-12 09:39:19 +0100, Josef Šimánek wrote:
> I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.

It's not currently supported by the windows buildsystem. I don't
personally have the expertise to change that (I've not worked on windows
after ~2003). So somebody would have to step in to make that work,
first.

Greetings,

Andres Freund

Re: Avoiding out of date statistics / planner

[Michael Lewis]

It may also be worth noting that it is possible to make autovacuum/analyze
more aggressive, perhaps only on the tables that see large changes in data
that might result in a statistics issue. If you could share a query,
explain analyze output, and pseudo code or at least description of what
sort of bulk operations are being done, then more insight could be offered.

Re: Natural sort order extension.

[Dmitry Igrishin]

On Wed, 12 Feb 2020, 19:55 Laurenz Albe,  wrote:

> On Wed, 2020-02-12 at 18:45 +0300, Dmitry Igrishin wrote:
> > I've implemented a PostgreSQL extension for natural sort order. I.e.
> > strings like "z20", "z0004", "z11", "z2" sorted in ascending order as
> > "z2", "z0004", "z11", "z20".
> >
> > Currently it implements the type textnso which is binary-coercible
> > to/from the text type. It's possible to declare table columns of type
> > textnso and create indexes on them.
> >
> > Details are here https://github.com/dmitigr/pgnso
> >
> > Any feedback are welcome!
>
> I don't want to detract from this, but from PostgreSQL v10 on you can use
> ICU collations with the "kn-true" variant to have natural sort order.
>
Thanks for the point. But my extension works with default collation and
UTF-8 encoding. And ICU is not required.

>
>

Re: How to restore roles without changing postgres password

[Tom Lane]

Adrian Klaver  writes:
> On 2/11/20 11:31 PM, Andrus wrote:
>> Also I dont understand why GRANTED BY clauses appear in file. This looks 
>> like noice.
>> GRANT documentation
>> https://www.postgresql.org/docs/current/sql-grant.html
>> does not contain GRANTED BY clause. It looks like pg_dumpall generates 
>> undocumented clause.

> It is not noise, see:

Indeed, but it's a fair question why it's not documented.
The clause does appear in the SQL standard:

 ::=
GRANT  TO  [ {   }... ]
  [ WITH HIERARCHY OPTION ]
  [ WITH GRANT OPTION ]
  [ GRANTED BY  ]

so I suppose whoever added the implementation just forgot about
fixing the docs.

regards, tom lane

Re: Natural sort order extension.

[Laurenz Albe]

On Wed, 2020-02-12 at 18:45 +0300, Dmitry Igrishin wrote:
> I've implemented a PostgreSQL extension for natural sort order. I.e.
> strings like "z20", "z0004", "z11", "z2" sorted in ascending order as
> "z2", "z0004", "z11", "z20".
> 
> Currently it implements the type textnso which is binary-coercible
> to/from the text type. It's possible to declare table columns of type
> textnso and create indexes on them.
> 
> Details are here https://github.com/dmitigr/pgnso
> 
> Any feedback are welcome!

I don't want to detract from this, but from PostgreSQL v10 on you can use
ICU collations with the "kn-true" variant to have natural sort order.

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

Re: How to restore roles without changing postgres password

[Adrian Klaver]

On 2/11/20 11:31 PM, Andrus wrote:

Hi!
Thank you.
 >pg_dumpall creates an SQL file which is just a simple text file
 >you can then edit sql removing postgres user from  the file
 >This can be automated in a script that searches the generated sql file 
for the postgres user  replacing it with a blank/empty line or adds -- 
to the bringing of >the line which comments it out.
This script creates cluster copy in every night. So this should be done 
automatically.

I have little experience with Linux.
Can you provide example, how it should it be done using sed or other tool.
There is also second user named dbandmin whose password  cannot changed 
also.
It would be best if  CREATE ROLE and ALTER ROLE  clauses for postgres 
and dbadmin users are removed for file.


Then we would get all sorts of posts about why they are not showing up 
anymore. This suggestion is a non starter.


Or if this is not reasonable, same passwords or different role names can 
used in both clusters.


They can be, you just have to track/manipulate that yourself. What it 
comes down to is that the Postgres project is not the admin for 
everyone's install.


Also I dont understand why GRANTED BY clauses appear in file. This looks 
like noice.

GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html
does not contain GRANTED BY clause. It looks like pg_dumpall generates 
undocumented clause.


It is not noise, see:

~/src/bin/pg_dump/pg_dumpall.cpg_dumpall.c

/*
* We don't track the grantor very carefully in the backend, so cope
* with the possibility that it has been dropped.
*/
if (!PQgetisnull(res, i, 3))
{
char   *grantor = PQgetvalue(res, i, 3);

fprintf(OPF, " GRANTED BY %s", fmtId(grantor));
}
fprintf(OPF, ";\n");



Andrus.



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

Natural sort order extension.

[Dmitry Igrishin]

Hi,

I've implemented a PostgreSQL extension for natural sort order. I.e.
strings like "z20", "z0004", "z11", "z2" sorted in ascending order as
"z2", "z0004", "z11", "z20".

Currently it implements the type textnso which is binary-coercible
to/from the text type. It's possible to declare table columns of type
textnso and create indexes on them.

Details are here https://github.com/dmitigr/pgnso

Any feedback are welcome!

Thanks.

Re: Avoiding out of date statistics / planner

[Tom Lane]

Tim Kane  writes:
> Every now and again, I will encounter an unexplained long-running query.
> It’s a head scratcher moment, because this query that is still running for
> 20 minutes (not blocking) can be run independently in about 500ms

Without some kind of context (like, have you been doing something to
the table(s) involved that would drastically change their statistics)
it's hard to comment on this.  It's not obvious from the info
provided that this is a bad-plan issue rather than something else.

> On the application side, we can explicitly issue a VACUUM ANALYZE after
> each bulk operation - and often that is precisely what happens..
> But - I am keenly aware that this cannot be performed within a transaction.

Plain ANALYZE can be, and that's all you need if the problem is to
update stats.

regards, tom lane

Re: pg_basebackup connection closed unexpectedly...

[Tom Lane]

=?UTF-8?Q?Mladen_Marinovi=C4=87?=  writes:
> Recently I am having some strange problems with pg_basebackup. About once a
> week the backup process ends with an error message like this:
> 2020-02-11 23:25:40 UTC [25790]: [1-1] user=replicator,db=[unknown] LOG:
>  could not send data to client: Connection reset by peer

Hmmm 

> The problem started occurring after a hardware (RAM + SSD) upgrade and an
> OS Upgrade to Ubuntu 18.04. Both the server and backup process run in
> separate docker containers on the same machine. This happens randomly on
> multiple servers with the same configuration and it is probably not
> hardware related. Also, this happens evenly on 9.4 and 9.6, and using the
> same docker images that worked flawlessly on the previous installation.
> I have been investigating the issue for at least a month and found no
> problems in any log or metric before or after the event. I suspect that
> this is related to some OS/docker parameter that is not well configured.

How long does the backup run before failing?  If the connection were going
between different machines my suspicions would lean toward a network
timeout.  That seems somewhat unlikely in this configuration, but you
never know.

> Would increasing the database log level give me any more info about what
> caused the connection to close?

Nope, not directly.  It might be useful to figure out whether data
transfer continues full throttle right up until the connection drop,
or whether it stops sooner (and then there's some sort of timeout
before the error occurs).

regards, tom lane

Re: Help : Removal of leading spaces in all the columns of a table

[Rob Sargent]

> On Feb 12, 2020, at 7:09 AM, Susan Hurst  
> wrote:
> 
> 
> I once wrote a trigger function to do just what you asked, however, it was a 
> huge drain on performance so I didn't use it for long, so I dropped the 
> trigger.  Hopefully, someone has a more practical approach.  I would be 
> interested in this also.
> 
> Sue
> 
> ---
> 
> Susan E Hurst
> Principal Consultant
> Brookhurst Data LLC
> Email: susan.hu...@brookhurstdata.com
> Mobile: 314-486-3261
>> On 2020-02-12 06:42, srikkanth wrote:
>> 
>> Hi Team,
>>  
>> How can i write the syntax to remove the leading spaces on a table for all 
>> the columns.
>>  
>> Also, want to know how to do the all words of all the columns in capital 
>> along with removing of leading\excessive\trailing spaces at a time.
>> 
>> Can you please help me out, let me know in case of any

For larger production tables I would stream the converted data through COPY 
into a new table of identical shape, build indeces then rename both and 
truncate/drop old table. 

Re: Help : Removal of leading spaces in all the columns of a table

[Susan Hurst]

 

I once wrote a trigger function to do just what you asked, however, it
was a huge drain on performance so I didn't use it for long, so I
dropped the trigger. Hopefully, someone has a more practical approach. I
would be interested in this also. 

Sue 

---

Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hu...@brookhurstdata.com
Mobile: 314-486-3261

On 2020-02-12 06:42, srikkanth wrote: 

> Hi Team, 
> 
> How can i write the syntax to remove the leading spaces on a table for all 
> the columns. 
> 
> Also, want to know how to do the all words of all the columns in capital 
> along with removing of leadingexcessivetrailing spaces at a time.
> 
> Can you please help me out, let me know in case of any inputs. 
> 
> Thanks, 
> 
> Srikanth B
 

Re: Help : Removal of leading spaces in all the columns of a table

[Charles Clavadetscher]

CCing the list.

Hello

On 2020-02-12 14:30, Pete Yunker wrote:

Shouldn’t the replacement string in regexp_replace be a single space
instead of a 0-length string?


Yes, correct.

SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', ' ', 'g') FROM test;
 regexp_replace

 ABC
 ABC
 A B C
 A B C
(4 rows)

The 3rd row was not modified correctly in my previous example.
Thank you for pointing out.

And to get back to the OP I saw that he wants a statement for all 
columns - I assume of a table.


In this case it is possible to generate the statement using the system 
catalogs.


I modified the test table to illustrate this.

\d test
 Table "public.test"
 Column |   Type| Collation | Nullable | Default
+---+---+--+-
 txt| text  |   |  |
 i  | integer   |   |  |
 txt2   | character varying |   |  |

select * from test;
  txt   | i |   txt2
+---+---
 abc| 1 |   de   f
 abc| 2 | d ef
   ab c | 3 |d  e f
 a b c  | 4 | def
(4 rows)

Now create the statement replacing 'test' with the name of the table 
that must be processed.


SELECT format($$UPDATE %I.%I SET (%s) = (%s)$$,
  c.relnamespace::REGNAMESPACE,
  c.relname,
  string_agg(a.attname, ', '),
  string_agg(format($$regexp_replace(upper(trim(%s)),'[ 
]{2,}', ' ', 'g')$$, a.attname), ', '))

FROM pg_catalog.pg_attribute a,
 pg_catalog.pg_class c
WHERE a.attrelid = c.oid
AND a.atttypid::regtype IN ('text','varchar')
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relname = 'test'
GROUP BY c.relnamespace::regnamespace,
   c.relname;

And execute the resulting statement.

UPDATE public.test SET (txt, txt2) = (regexp_replace(upper(trim(txt)),'[ 
]{2,}', ' ', 'g'), regexp_replace(upper(trim(txt2)),'[ ]{2,}', ' ', 
'g'));


Content of the table after the update.

select * from test;
  txt  | i | txt2
---+---+---
 ABC   | 1 | DE F
 ABC   | 2 | D E F
 A B C | 3 | D E F
 A B C | 4 | DEF
(4 rows)

Regards
Charles



On Feb 12, 2020, at 8:23 AM, Charles Clavadetscher 
 wrote:


Hello

On 2020-02-12 13:42, srikkanth wrote:

Hi Team,
How can i write the syntax to remove the leading spaces on a table 
for

all the columns.
Also, want to know how to do the all words of all the columns in
capital along with removing of leading\excessive\trailing spaces at a
time.
Can you please help me out, let me know in case of any inputs.


You may combine existing functions:

CREATE TABLE test (txt TEXT);

INSERT INTO test VALUES ('abc'),('abc'),('  ab c'),('a 
b c');


SELECT * FROM test;
 txt

abc
abc
  ab c
a b c
(4 rows)

Now, assuming that "excessive" spaces means that there must be at most 
one between words:


SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', '', 'g') FROM test;
regexp_replace

ABC
ABC
AB C
A B C
(4 rows)

first you remove all leading and trailing spaces (trim).
Then you transform all letters to upper case (upper).
Finally you remove all spaces but one between the words 
(regexp_replace with '[ ]{2,}' meaning 2 or more spaces to be replaced 
with empty string '' for all occurrences in the string. 'g' means 
global).


The order of the calls is not really relevant for the result.

Use UPDATE test SET txt instead of a select if you want to update your 
table at once.

I usually prefer to see the result before I act on the data ;-)

Bye
Charles


Thanks,
Srikanth B


--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Re: Help : Removal of leading spaces in all the columns of a table

[Charles Clavadetscher]

Hello

On 2020-02-12 13:42, srikkanth wrote:

Hi Team,

How can i write the syntax to remove the leading spaces on a table for
all the columns.

Also, want to know how to do the all words of all the columns in
capital along with removing of leading\excessive\trailing spaces at a
time.

Can you please help me out, let me know in case of any inputs.


You may combine existing functions:

CREATE TABLE test (txt TEXT);

INSERT INTO test VALUES ('abc'),('abc'),('  ab c'),('a b 
c');


SELECT * FROM test;
  txt

 abc
 abc
   ab c
 a b c
(4 rows)

Now, assuming that "excessive" spaces means that there must be at most 
one between words:


SELECT regexp_replace(upper(trim(txt)),'[ ]{2,}', '', 'g') FROM test;
 regexp_replace

 ABC
 ABC
 AB C
 A B C
(4 rows)

first you remove all leading and trailing spaces (trim).
Then you transform all letters to upper case (upper).
Finally you remove all spaces but one between the words (regexp_replace 
with '[ ]{2,}' meaning 2 or more spaces to be replaced with empty string 
'' for all occurrences in the string. 'g' means global).


The order of the calls is not really relevant for the result.

Use UPDATE test SET txt instead of a select if you want to update your 
table at once.

I usually prefer to see the result before I act on the data ;-)

Bye
Charles


Thanks,

Srikanth B


--
Charles Clavadetscher
Swiss PostgreSQL Users Group
Treasurer
Spitzackerstrasse 9
CH - 8057 Zürich

http://www.swisspug.org

+---+
|   __  ___ |
|/)/  \/   \|
|   ( / ___\)   |
|\(/ o)  ( o)   )   |
| \_  (_  )   \ ) _/|
|   \  /\_/\)/  |
|\/   |
| _|  | |
| \|_/  |
|   |
| Swiss PGDay 2020  |
|  18/19.06.2020|
|  HSR Rapperswil   |
|   |
+---+

Help : Removal of leading spaces in all the columns of a table

[srikkanth]

Hi Team,

How can i write the syntax to remove the leading spaces on a table for all the 
columns.

Also, want to know how to do the all words of all the columns in capital along 
with removing of leading\excessive\trailing spaces at a time.Can you please 
help me out, let me know in case of any inputs.

Thanks,
Srikanth B

RE: Function not imported in Entity Framework

[farjad . farid]

Thanks for highlighting this issue.

I have tested this using .net 4.8 and Core 3.1 against Sql Server, they all 
exhibit the same problem.

The best course of action is probably to identify a workaround, based on your 
project, until Microsoft team have the time to fix the issue.
It would be good idea to report this to Microsoft.



From: Vikram Sah 
Sent: 2020 February 12 01:18
To: Adrian Klaver 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Function not imported in Entity Framework

Sir,
I asked this in npgsql community who have developed "EntityFramework6.Npgsql" , 
link is :
 https://github.com/npgsql/npgsql/issues/948. and developer named  Shay 
Rojansky aka roji has replied as below image:
[@roji]

On Tue, Feb 11, 2020 at 10:30 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:
On 2/11/20 8:35 AM, Vikram Sah wrote:
> Got it sir, but unfortunately they have not provided any solution for
> the last 5 days.

Where have you asked besides here?

>
> Thanks
>
> On Tue, 11 Feb 2020, 10:12 pm Adrian Klaver, 
> mailto:adrian.kla...@aklaver.com>
> >> wrote:
>
> On 2/11/20 3:56 AM, Vikram Sah wrote:
>  > Thank you so much sir, but it didn't work for me as the database
>  > function in PostgreSQL and EntityFramework6.Npgsql  is used as
> provider
>  > for entity framework.
>
> I have no idea where to go from here. Your best bet would be to reach
> out to the EntityFramework6.Npgsql community
>
>  >
>  > Thanks!
>  > Vikram
>  >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com 
> >
>


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

Re: How to restore roles without changing postgres password

[Andrus]

Hi!

>Not a bad idea,  would want to extend this to all the roles on the server not 
>just postgres  

>I've  edited the global dump many times  removing/editing table spaces, 
>comment old users, etc..  

Maybe it is easier to create plpgsql procedure which returns desired script as 
text.
Or it retrieves globals from other cluster using dblink and applies changes to 
new cluster.

This can be called instead of pq_dumpall and can edited for custom needs.
Editing plpgsql script is easier for postgres users than creating sed script to 
delete commands from sql file.

Andrus.

pg_basebackup connection closed unexpectedly...

[Mladen Marinović]

Hi,

Recently I am having some strange problems with pg_basebackup. About once a
week the backup process ends with an error message like this:

2020-02-11 23:25:40,447 INFO pg_basebackup: could not read COPY data:
server closed the connection unexpectedly
2020-02-11 23:25:40,447 INFOThis probably means the server terminated
abnormally
2020-02-11 23:25:40,447 INFObefore or while processing the request.
2020-02-11 23:25:40,447 ERROR Error creating basebackup! RC: 1

On the database side the logs show the same error:
2020-02-11 23:25:40 UTC [25790]: [1-1] user=replicator,db=[unknown] LOG:
 could not send data to client: Connection reset by peer
2020-02-11 23:25:40 UTC [25790]: [2-1] user=replicator,db=[unknown] ERROR:
 base backup could not send data, aborting backup
2020-02-11 23:25:40 UTC [25790]: [3-1] user=replicator,db=[unknown] LOG:
 could not send data to client: Broken pipe
2020-02-11 23:25:40 UTC [25790]: [4-1] user=replicator,db=[unknown] FATAL:
 connection to client lost
2020-02-11 23:25:40 UTC [29824]: [1-1] user=replicator,db=[unknown] LOG:
 unexpected EOF on standby connection

The problem started occurring after a hardware (RAM + SSD) upgrade and an
OS Upgrade to Ubuntu 18.04. Both the server and backup process run in
separate docker containers on the same machine. This happens randomly on
multiple servers with the same configuration and it is probably not
hardware related. Also, this happens evenly on 9.4 and 9.6, and using the
same docker images that worked flawlessly on the previous installation.

I have been investigating the issue for at least a month and found no
problems in any log or metric before or after the event. I suspect that
this is related to some OS/docker parameter that is not well configured.

Would increasing the database log level give me any more info about what
caused the connection to close?

Regards,
Mladen Marinović

Avoiding out of date statistics / planner

[Tim Kane]

Every now and again, I will encounter an unexplained long-running query.

It’s a head scratcher moment, because this query that is still running for
20 minutes (not blocking) can be run independently in about 500ms

I can only assume that the problem query ran against the table(s) at a time
when it was perhaps in need of a vacuum analyze...  I’m guessing here, that
the table had seen some amount of change and simply had out of date
statistics.

How can I avoid this?
The auto-vacuum daemon is doing it’s thing, but there is always going to be
an opportunity for a query to sneak in against a table that has recently
seen large change, but not yet been analysed.

On the application side, we can explicitly issue a VACUUM ANALYZE after
each bulk operation - and often that is precisely what happens..

But - I am keenly aware that this cannot be performed within a transaction.
That means there is always a (small) window in which a query can still
execute in this scenario.

Are there any other best practices that can mitigate this kind of problem?

It’s rare, sure - but I don’t like sweeping these under the rug.

I’m on PG 9.6.. perhaps there are planner improvements since then that
might reduce the incidence of these (rare) issues.

Any advice appreciated, thanks.

Re: JIT on Windows with Postgres 12.1

[Josef Šimánek]

Hello.

If I understand it well, JIT is enabled only for builds with "--with-llvm"
flag. Seems EDB Windows PG 12.1 installer is not build with llvm (and jit)
support.

I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.

út 11. 2. 2020 v 22:41 odesílatel Catch All  napsal:

> I am using the EDB Windows PG 12.1 installer and it seems not to have JIT
> enabled:
> C:\Program Files\PostgreSQL\12\bin>pg_config --configure
> --enable-thread-safety --enable-nls --with-ldap --with-openssl
> --with-ossp-uuid
> --with-libxml --with-libxslt --with-icu --with-tcl --with-perl
> --with-python
>
> Is this expected?  Is there a Windows installer available for PG 12 with
> JIT enabled?
>