Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Nick Renders]

Hi Thomas,

We are setting up a new test environment with 12.1.
Once it is running, I'll try out those commands and get back with the 
results.


Thanks,

Nick Renders


On 11 Feb 2020, at 2:51, Thomas Munro wrote:


On Mon, Feb 10, 2020 at 4:35 AM Marc  wrote:
We will keep the 12.1 in place so that we can run additional tests to 
assist to pin-point the issue.


Feel free to ask but allow us to recover from these hectic days ;-)


Here's how to get a stack so we can see what it was doing, assuming
you have the Apple developer tools installed:

1.  Find the PID of the backend you're connected to with SELECT
pg_backend_pid().
2.  "lldb -p PID" from a shell to attach to the process, then "cont"
to let it continue running.
3.  Run the query in that backend and wait for the SIGKILL.
4.  In the lldb session, type "bt".

It'll only make sense if your PostgreSQL build has debug symbols, but 
let's see.

Re: Function not imported in Entity Framework

[Vikram Sah]

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.

Thanks!
Vikram

On Thu, Feb 6, 2020 at 9:23 PM Adrian Klaver 
wrote:

> On 2/6/20 1:36 AM, Vikram Sah wrote:
> > Dear sir/mam,
> >
> > I want to integrate PostgreSQL database to entity framework in asp.net
> >  using database first approach. but when try to add
> > function then error occurred:
> >
> > *Error 6046: Unable to generate function import return type of the store
> > function.*
>
> This really a question for the EntityFramework maintainers/users. Still
> I did find this:
>
>
> https://stackoverflow.com/questions/24161005/error-6046-unable-to-generate-function-import-return-type-of-the-store-function
>
>
>
> >
> > I have following configuration:
> >
> > PostgreSQL 11.6,
> > npgsql v.4.0.9.0,
> > EntityFramework6.Npgsql 3.2.1.1,
> > in .net framework 4.5.2
> >
> > *My function in postgresql database is :*
> >
> > CREATE OR REPLACE FUNCTION dbo.EmployeeDetail ()
> > RETURNS TABLE (
> > emp_name VARCHAR,
> > emp_Id UUID
> > )
> > AS $$
> > BEGIN
> > RETURN QUERY SELECT
> > "EmployeName",
> > "EmployeeID"
> > FROM
> > dbo.Employee;
> > END; $$
> >
> > LANGUAGE 'plpgsql';
> >
> >
> > Can you pls help! I did more researches but no luck.
> >
> >
> > Thanks!
> >
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Mon, 10 Feb 2020, Adrian Klaver wrote:


So you already have 11 and 12 instances of Postgres running?


Adrian,

No. Both 11 and 12 are installed; neither is running. I have a cron job that
runs pg_dumpall every weekday night.


If so why use pg_upgrade?


Because I wanted to try it rather than use 'psql -f .sql'


To verify what is going on do:
/usr/lib64/postgresql/11/bin/psql --version


The pg_upgrade page says to not have either the old or new versions running.


vi /var/lib/pgsql/11/data/PG_VERSION


/var/lib/pgsql/11/data/PG_VERSION is 11; /var/lib/pgsql/12/data/PG_VERSION
is 12.

Rich

Re: PostgreSQL Installer issue

[George Weaver]

Good morning Adrian,

I believe your right.

(Never did get a reply back from EDB when I brought this to their 
attention.)


Cheers,

George

On 31/01/2020 2:38 p.m., Adrian Klaver wrote:

On 1/31/20 12:27 PM, George Weaver wrote:

Good afternoon,

I am updating a PostgreSQL 10.3 installation to PostgreSQL 10.11 
installation on Windows 10 using the EDB installer.


On the Select Components dialog, all of the components are selected 
and not enabled: no component can be de-selected.


My guess is because it just a minor version upgrade and all its doing 
is upgrading the binaries.




George






--
Cleartag Software, Inc.
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
(204) 284-9838 fax
gwea...@cleartagsoftware.com

Fast. Accurate. Easy.

Re: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Tue, 11 Feb 2020, Rich Shepard wrote:


So you already have 11 and 12 instances of Postgres running?


Adrian,

I just started 12.1 and, as user postgres, read last Friday's backup file.
All's well.

A final question: which conf file do I edit so when I enter 'psql -l' (or
open a specific database) I don't need to enter my password? I don't recall
having to reset this permission with prior upgrades and want to do so now.

Regards,

Rich

Re: Pre-version pg_upgrade syntax check [FIXED]

[Rich Shepard]

On Tue, 11 Feb 2020, Rich Shepard wrote:


A final question: which conf file do I edit so when I enter 'psql -l' (or
open a specific database) I don't need to enter my password? I don't
recall having to reset this permission with prior upgrades and want to do
so now.


Never mind: I found it: /var/lib/pgsql/12/data/pg_hba.conf.

Thanks for all you help.

Rich

Re: Function not imported in Entity Framework

[Adrian Klaver]

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

Re: Pre-version pg_upgrade syntax check

[Adrian Klaver]

On 2/11/20 5:43 AM, Rich Shepard wrote:

On Mon, 10 Feb 2020, Adrian Klaver wrote:


So you already have 11 and 12 instances of Postgres running?


Adrian,

No. Both 11 and 12 are installed; neither is running. I have a cron job 
that

runs pg_dumpall every weekday night


If so why use pg_upgrade?


Because I wanted to try it rather than use 'psql -f .sql'


To verify what is going on do:
/usr/lib64/postgresql/11/bin/psql --version


The pg_upgrade page says to not have either the old or new versions 
running.


The above runs the psql client not the server. It is a way of 
determining what version binaries  /usr/lib64/postgresql/11/bin/ 
actually contains.





vi /var/lib/pgsql/11/data/PG_VERSION


/var/lib/pgsql/11/data/PG_VERSION is 11; /var/lib/pgsql/12/data/PG_VERSION
is 12.

Rich





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

Re: Function not imported in Entity Framework

[Vikram Sah]

Got it sir, but unfortunately they have not provided any solution for the
last 5 days.

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
>

Re: Function not imported in Entity Framework

[Adrian Klaver]

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: Pre-version pg_upgrade syntax check

[Rich Shepard]

On Tue, 11 Feb 2020, Adrian Klaver wrote:


The above runs the psql client not the server. It is a way of determining
what version binaries /usr/lib64/postgresql/11/bin/ actually contains.


Adrian,

Aha! Running the command taught me a couple of valuable lessons because both
11 and 12 show they're running 12.1. That's because the SlackBuilds.org
build script apparently upgraded 11.5 to 12.1 in the same
/usr/lib64/postgresql/11/ directory. Before the next major version upgrade
I'll ensure the new version is installed in the proper subdirectory.

Since 12.1 is running I'll remove the version 11 directories.

Thanks very much,

Rich

Re: Pre-version pg_upgrade syntax check

[Adrian Klaver]

On 2/11/20 9:00 AM, Rich Shepard wrote:

On Tue, 11 Feb 2020, Adrian Klaver wrote:


The above runs the psql client not the server. It is a way of determining
what version binaries /usr/lib64/postgresql/11/bin/ actually contains.


Adrian,

Aha! Running the command taught me a couple of valuable lessons because 
both

11 and 12 show they're running 12.1. That's because the SlackBuilds.org
build script apparently upgraded 11.5 to 12.1 in the same
/usr/lib64/postgresql/11/ directory. Before the next major version upgrade



From here:

https://slackbuilds.org/repository/14.2/system/postgresql/

the latest build is for Postgres 10.10 and it does:

PG_VERSION=${PG_VERSION:-10.10}

./configure \
  --prefix=/usr/lib${LIBDIRSUFFIX}/$PRGNAM/$PG_VERSION \
  --sysconfdir=/etc/$PRGNAM/$PG_VERSION \
  --includedir=/usr/include \
  --datarootdir=/usr/share \
  --mandir=/usr/man \
  --docdir=/usr/doc/$PRGNAM-$VERSION \
  --datadir=/usr/share/$PRGNAM-$PG_VERSION \
  ...

Is there maybe a custom script in the mix somewhere?
Or something else setting PG_VERSION?




I'll ensure the new version is installed in the proper subdirectory.

Since 12.1 is running I'll remove the version 11 directories.

Thanks very much,

Rich





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

Re: Query returns no results until REINDEX

[Peter Geoghegan]

On Sun, Feb 9, 2020 at 12:50 PM Colin Adler  wrote:
> Looks like it found something. I checked out the contrib/pageinspect docs but
> wasn't too sure what to run. Are incompatible libc versions causing btree
> corruption something you consider a bug? If it's something you'd like to look
> into further I can gladly send over the database files.

No, this is not considered a bug.

It's unfortunate that there is no built in collation versioning
mechanism, or something like that -- that might have alerted you to
the problem before any real damage occurred. We have that for the ICU
collations, but it currently isn't possible to use ICU as the default
collation provider. You really have to go out of your way to use ICU
collations.

-- 
Peter Geoghegan

Re: Query returns no results until REINDEX

[Julien Rouhaud]

On Tue, Feb 11, 2020 at 8:33 PM Peter Geoghegan  wrote:
>
> On Sun, Feb 9, 2020 at 12:50 PM Colin Adler  wrote:
> > Looks like it found something. I checked out the contrib/pageinspect docs 
> > but
> > wasn't too sure what to run. Are incompatible libc versions causing btree
> > corruption something you consider a bug? If it's something you'd like to 
> > look
> > into further I can gladly send over the database files.
>
> No, this is not considered a bug.
>
> It's unfortunate that there is no built in collation versioning
> mechanism, or something like that -- that might have alerted you to
> the problem before any real damage occurred.

FTR, this is something being actively worked on [1], and I still hope
that in pg13 a glibc or ICU upgrade will be detected and raise proper
warning.

[1] https://commitfest.postgresql.org/27/2367/

JIT on Windows with Postgres 12.1

[Catch All]

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?

How to restore roles without changing postgres password

[Andrus]

Hi!

How to create backup script which restores all roles and role memberships 
from other server without changing postgres user password.


I tried shell script

PGHOST=example.com
PGUSER=postgres
PGPASSWORD=mypass
export PGHOST  PGPASSWORD  PGUSER
pg_dumpall --roles-only --file=globals.sql
psql -f globals.sql postgres

but this changes user postgres  password also.
How to restore roles so that postgres user password is not changed on 
restore.


Script runs on Debian 10 with Postgres 12
Server from where it reads users runs on Debian Squeeze with Postgres 9.1

Andrus 

Re: How to restore roles without changing postgres password

[Justin]

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.


On Tue, Feb 11, 2020 at 5:27 PM Andrus  wrote:

> Hi!
>
> How to create backup script which restores all roles and role memberships
> from other server without changing postgres user password.
>
> I tried shell script
>
> PGHOST=example.com
> PGUSER=postgres
> PGPASSWORD=mypass
> export PGHOST  PGPASSWORD  PGUSER
> pg_dumpall --roles-only --file=globals.sql
> psql -f globals.sql postgres
>
> but this changes user postgres  password also.
> How to restore roles so that postgres user password is not changed on
> restore.
>
> Script runs on Debian 10 with Postgres 12
> Server from where it reads users runs on Debian Squeeze with Postgres 9.1
>
> Andrus
>
>
>
>

Re: How to restore roles without changing postgres password

[Tom Lane]

"Andrus"  writes:
> How to create backup script which restores all roles and role memberships 
> from other server without changing postgres user password.

[ shrug... ]  Edit the command(s) you don't want out of the script.
This seems like a mighty random requirement to expect pg_dump to
support out-of-the-box.

I wonder though if there's a case for making that easier by breaking
up the output into multiple ALTER commands.  Right now you get
something like

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN 
REPLICATION BYPASSRLS PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';

but perhaps we could make it print

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER;
ALTER ROLE postgres WITH INHERIT;
ALTER ROLE postgres WITH CREATEROLE;
ALTER ROLE postgres WITH CREATEDB;
ALTER ROLE postgres WITH LOGIN;
ALTER ROLE postgres WITH REPLICATION;
ALTER ROLE postgres WITH BYPASSRLS;
ALTER ROLE postgres WITH PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';

That would make scripted edits a bit easier, and it'd also make the
output a bit more cross-version portable, eg if you try to load the
latter into a version without BYPASSRLS, the rest of the commands
would still work.

regards, tom lane

Re: How to restore roles without changing postgres password

[Justin]

HI Tom

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


On Tue, Feb 11, 2020 at 5:46 PM Tom Lane  wrote:

> "Andrus"  writes:
> > How to create backup script which restores all roles and role
> memberships
> > from other server without changing postgres user password.
>
> [ shrug... ]  Edit the command(s) you don't want out of the script.
> This seems like a mighty random requirement to expect pg_dump to
> support out-of-the-box.
>
> I wonder though if there's a case for making that easier by breaking
> up the output into multiple ALTER commands.  Right now you get
> something like
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
> REPLICATION BYPASSRLS PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> but perhaps we could make it print
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER;
> ALTER ROLE postgres WITH INHERIT;
> ALTER ROLE postgres WITH CREATEROLE;
> ALTER ROLE postgres WITH CREATEDB;
> ALTER ROLE postgres WITH LOGIN;
> ALTER ROLE postgres WITH REPLICATION;
> ALTER ROLE postgres WITH BYPASSRLS;
> ALTER ROLE postgres WITH PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> That would make scripted edits a bit easier, and it'd also make the
> output a bit more cross-version portable, eg if you try to load the
> latter into a version without BYPASSRLS, the rest of the commands
> would still work.
>
> regards, tom lane
>
>
>

Re: Function not imported in Entity Framework

[Vikram Sah]

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: POLL: Adding transaction status to default psql prompt

[Anthony Nowocien]

+1 also.
Thanks for the proposal.
Anthony

On Fri, Feb 7, 2020, 17:52 Kasun Kulathunga  wrote:

> On Thu, Feb 6, 2020, 20:29 Ken Tanzer  wrote:
>
> Em qua, 5 de fev de 2020 às 23:55, Vik Fearing 
> escreveu:
>
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
>
> +1
>
> I liked the idea just reading about it, but thought it would be good form
> to at least try it out before voting.  If I read the patch right, people
> can try this out by setting their prompt without having to change their
> .psqlrc file:
>
> \set PROMPT1 ''%/%R%x%# '
>
> Having done so, I'm still a +1!
>
> Cheers,
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>
>
> I've been using this setting for so long in .psqlrc that I forgot its not
> a default thing!
>
> +1 from me as well!
>
> -Kasun
>
>

Re: How to restore roles without changing postgres password

[Andrus]

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.

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

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.

Andrus.