Re: Overloaded && operator from intarray module prevents index usage.

I'll try to stay off your lawn. >

Re: Overloaded && operator from intarray module prevents index usage.

On 2/28/19 10:26 PM, Michael Lewis wrote: Arrays are -- by definition -- not atomic, and so they fundamentally break the model that relational databases are founded upon.  If you want to be a good database designer, don't use arrays. Thanks. I was reading about Codd after your last

Re: Overloaded && operator from intarray module prevents index usage.

> > Arrays are -- by definition -- not atomic, and so they fundamentally break > the model that relational databases are founded upon. If you want to be a > good database designer, don't use arrays. > Thanks. I was reading about Codd after your last email, but couldn't guess at which point was ob

Re: validation of hot standby

On 2/28/19 8:00 PM, Julie Nishimura wrote: Hello, We set up our hot standby by putting master into archive mode and issuing pg_basebackup command, and we are about to start our hot standby instance. What are the things you suggest for validation (assuming it will come up cleanly) and replicati

Re: Overloaded && operator from intarray module prevents index usage.

On 2/28/19 7:53 PM, Michael Lewis wrote: On Thu, Feb 28, 2019 at 4:57 PM Ron > wrote: On 2/28/19 4:53 PM, Michael Lewis wrote: [snip] Would a sixth option be to re-create the column as array type Codd is spinning in his grave... I'd hope he

validation of hot standby

Hello, We set up our hot standby by putting master into archive mode and issuing pg_basebackup command, and we are about to start our hot standby instance. What are the things you suggest for validation (assuming it will come up cleanly) and replication caught up? What do I need to check in add

Re: Overloaded && operator from intarray module prevents index usage.

On Thu, Feb 28, 2019 at 4:57 PM Ron wrote: > On 2/28/19 4:53 PM, Michael Lewis wrote: > [snip] > > Would a sixth option be to re-create the column as array type > > > Codd is spinning in his grave... > I'd hope he would be fine with people asking questions to learn. I'm open to studying any sugg

Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

I think setting up PAM authentication with AD on Linux server joined to domain via realm SSSD was much easier and transparent. Something like this worked for me to create SPN mapping and keytab in one command without need to use UPPERCASE for POSTGRES: ktpass -out postgres.keytab -princ POSTGRES/u

Re: Mind of its own?

writes: > I am using Postgresql 9.6, and the latest phppgadmin. OS is Debian 9.6. > 1. Created a database. > 2. Created tables inside that database. > 3. Dropped that database. > Now, what happens is when I create another database with the same name or > different name, all those tables are alr

RE: Barman disaster recovery solution

Thanks for clarifying guys. Best Regards, Nawaz Ahmed Software Development Engineer Fujitsu Australia Software Technology Pty Ltd 14 Rodborough Road, Frenchs Forest NSW 2086, Australia T +61 2 9452 9027 na...@fast.au.fujitsu.com fastware.com.au -Original Message- From: David Steele Sen

Re: Mind of its own?

On 3/1/19 9:10 AM, s4...@yahoo.co.jp wrote: Hopefully not, but I feel so helpless. What happened? I am using Postgresql 9.6, and the latest phppgadmin. OS is Debian 9.6. 1. Created a database. 2. Created tables inside that database. 3. Dropped that database. Now, what happens is when I creat

Mind of its own?

Hopefully not, but I feel so helpless. What happened? I am using Postgresql 9.6, and the latest phppgadmin. OS is Debian 9.6. 1. Created a database. 2. Created tables inside that database. 3. Dropped that database. Now, what happens is when I create another database with the same name or diff

Re: Overloaded && operator from intarray module prevents index usage.

On 2/28/19 4:53 PM, Michael Lewis wrote: [snip] Would a sixth option be to re-create the column as array type Codd is spinning in his grave... -- Angular momentum makes the world go 'round.

Re: Pgbackrest Comparability issue

On 2/28/19 12:28 PM, chiru r wrote: On Thu, Feb 28, 2019 at 1:28 PM Adrian Klaver > wrote: On 2/28/19 10:06 AM, chiru r wrote: > > We are suing pgBackRest 1.24 version . Hmm, that cut off in the previous post, sorry for the noise. Seems email

PostgreSQL (linux) configuration with GSSAPI to a Windows domain

I'm trying to configure authentication between PostgreSQL database server on linux and Windows Active Directory. First part of configuration is working but when I'm trying to authenticate from Windows client, it is not working with message: Can't obtain database list from the server. SSPI conti

Re: Overloaded && operator from intarray module prevents index usage.

On Thu, Feb 28, 2019 at 3:34 PM Tom Lane wrote: > Michael Lewis writes: > > Can you dumb down how to change the index or column type such that an > index > > will be used for the && operator while intarray extension is installed? > We > > have the intarray extension installed and I doubt that I

Re: race condition when checking uniqueness between two tables

=?UTF-8?B?R3nFkXrFkSBQYXBw?= writes: > A periodic task moves rows form `available_jobs` to `assigned_jobs` > the jobs that has been assigned: > WITH jobs_to_assign AS ( > DELETE FROM available_jobs > USING v_assignable_jobs > WHERE available_jobs.id = v_assignable_jobs.id > RETURNIN

Re: Overloaded && operator from intarray module prevents index usage.

Michael Lewis writes: > Can you dumb down how to change the index or column type such that an index > will be used for the && operator while intarray extension is installed? We > have the intarray extension installed and I doubt that I can get it > removed. There's no magic nice solution to this,

Re: specifying table in function args

On Thu, Feb 28, 2019 at 1:56 PM Rob Sargent wrote: > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. Pretty sure you need to heed the advice to make it work. select pvr(s::public.segment) from mm.segment AS s limit 5; David J.

race condition when checking uniqueness between two tables

Hi all, First and foremost I admit what follows is quite awkward and a bit long. Sorry for both. However I still would really like to understand what I am missing in regards of transaction isolation levels and MVCC if CTE involved. Given a sort of scheduler application with two tables: CREATE T

Re: specifying table in function args

On 2/28/19 12:27 PM, David G. Johnston wrote: On Thursday, February 28, 2019, Rob Sargent > wrote: but this fails in one of two ways:  either the create function call fails lacking a definition of "segment" or, if I create a public.segment table, create

Re: Overloaded && operator from intarray module prevents index usage.

Michael Lewis schrieb am 28.02.2019 um 21:23: Yeah, because it's an exact datatype match while the core operator is anyarray && anyarray which is not. Can you dumb down how to change the index or column type such that an index will be used for the && operator while intarray extension is

Re: Pgbackrest Comparability issue

On Thu, Feb 28, 2019 at 1:28 PM Adrian Klaver wrote: > On 2/28/19 10:06 AM, chiru r wrote: > > > > We are suing pgBackRest 1.24 version . > > > > Installing using rpms > > > > yum -y install perl-parent > > yum -y install perl-Time-HiRes > > yum -y install perl-JSON > > yum -y install perl-Digest

Refresh Publication takes hours and doesn´t finish

*We use logical replication from a PG version 10.6 to a 11.2. Both are Ubuntu 16.04.We have a hundred schemas with more or less a hundred tables, so number of tables is about 10.000. All replication is ok but when we try to do a REFRESH SUBSCRIPTION because we added a new schema, it takes hours and

Re: Overloaded && operator from intarray module prevents index usage.

> > Yeah, because it's an exact datatype match while the core operator > is anyarray && anyarray which is not. Can you dumb down how to change the index or column type such that an index will be used for the && operator while intarray extension is installed? We have the intarray extension install

Re: specifying table in function args

On Thursday, February 28, 2019, Rob Sargent wrote: > but this fails in one of two ways: either the create function call fails > lacking a definition of "segment" or, if I create a public.segment table, > create the function, set search_path to include a project's schema then > drop public.segmen

Re: automated refresh of dev from prod

On Feb 28, 2019, at 8:04 AM, Stephen Frost wrote: > > Greetings, > > * Julie Nishimura (juliez...@hotmail.com) wrote: >> Hello everybody, I am new to postgresql environment, but trying to get up to >> speed. >> Can you please share your experience on how you can automate refreshment of >> dev

specifying table in function args

Using PG10.7, I have a database per investigator with one or more identical schemata per project.  So far I've gotten by defining public functions (as postgres) which reference tables /without/ schema reference.  Each project has a role which sets the search_path such that the project specific

Re: Pgbackrest Comparability issue

On 2/28/19 10:06 AM, chiru r wrote: We are suing pgBackRest 1.24 version . Installing using rpms yum -y install perl-parent yum -y install perl-Time-HiRes yum -y install perl-JSON yum -y install perl-Digest-SHA yum -y install perl-Digest yum -y install perl-DBD-Pg FYI, please do not top post

Re: Pgbackrest Comparability issue

We are suing pgBackRest 1.24 version . Installing using rpms yum -y install perl-parent yum -y install perl-Time-HiRes yum -y install perl-JSON yum -y install perl-Digest-SHA yum -y install perl-Digest yum -y install perl-DBD-Pg On Thu, Feb 28, 2019 at 10:15 AM Adrian Klaver wrote: > On 2/28/

Re: Where **not** to use PostgreSQL?

Doh, sorry I missed your postscript! On Thu, Feb 28, 2019 at 8:02 AM Nicolas Grilly wrote: > Le jeu. 28 févr. 2019 à 16:48, Michel Pelletier < > pelletier.mic...@gmail.com> a écrit : > >> Check out the RUM index extension, it adds ranking information to indexes >> to speed up exactly the problem

Re: Where **not** to use PostgreSQL?

I often avoid PostgreSQL when using software for which PostgreSQL support is secondary. Usually this is the case where MySQL is the default, but PostgreSQL is on the "also supported" list. "Also" is too often a synonym for "badly", here, and unless I really want to be able to approach the underlyi

Re: Overloaded && operator from intarray module prevents index usage.

Thomas Kellerer writes: > While testing a query on an integer array with a GIN index, I stumbled over a > behaviour which surprised me and which I would consider a bug - but maybe I > am wrong. It's not a bug --- the problem is that that operator is capturing your query reference, and it's not

Re: automated refresh of dev from prod

Greetings, * Julie Nishimura (juliez...@hotmail.com) wrote: > Hello everybody, I am new to postgresql environment, but trying to get up to > speed. > Can you please share your experience on how you can automate refreshment of > dev environment on regular basis (desirably weekly), taking for cons

Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 4:50 AM Nicolas Grilly wrote: > On Thu, Feb 28, 2019 at 1:24 PM Chris Travers > wrote: > >> 1. a) TB-scale full text search systems. >> b) PostgreSQL's full text search is quite capable but not so >> powerful that it can completely replace Lucene-based systems. So

Re: Pgbackrest Comparability issue

On 2/28/19 6:59 AM, chiru r wrote: Hi All, I am using Pgbackrest and have an issue while upgrading our PostgreSQL 9.5.10 to PostgreSQL 10.4 version on same Physical linux server. As part of testing I have stop the PostgreSQL 9.5 database on server and PostgreSQL 10 is up and running and trying

Re: Where **not** to use PostgreSQL?

On 2/28/19 5:47 AM, Thomas Güttler wrote: Hi experts, where would you suggest someone to **not** use PostgreSQL? 1. Small embedded systems.  SQLite is great for that. 2. Easy-to-implement Master-Master replication.  (The Percona fork of MySQL does that really well, if you can handle MySQL's

Pgbackrest Comparability issue

Hi All, I am using Pgbackrest and have an issue while upgrading our PostgreSQL 9.5.10 to PostgreSQL 10.4 version on same Physical linux server. As part of testing I have stop the PostgreSQL 9.5 database on server and PostgreSQL 10 is up and running and trying to configure and execute the backups f

Re: create unique constraint on jsonb->filed during create table

Got it, thank you! On Thu, Feb 28, 2019 at 12:48 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, February 27, 2019, Andy Fan > wrote: > >> >> The following way works with 2 commands: >> >> zhifan=# create table t1 (a jsonb); >> CREATE TABLE >> zhifan=# create unique ind

Re: Where **not** to use PostgreSQL?

I wish more people would ask this question, to me, it is the true mark of experience. In general, I think of PostgreSQL as the leading Relational Database. The farther you get away from relational data and relational queries, the more I would say, you should look for other products or solutions.

Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 2:12 PM Chris Travers wrote: > Where I did this on the TB scale, we had some sort of ranking but it was > not based on ts_rank. > > On the PB scale systems I work on now, it is distributed, and we don't > order in PostgreSQL (or anywhere else, though if someone wants to wr

Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 1:50 PM Nicolas Grilly wrote: > On Thu, Feb 28, 2019 at 1:24 PM Chris Travers > wrote: > >> 1. a) TB-scale full text search systems. >> b) PostgreSQL's full text search is quite capable but not so >> powerful that it can completely replace Lucene-based systems. So

Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 1:24 PM Chris Travers wrote: > 1. a) TB-scale full text search systems. > b) PostgreSQL's full text search is quite capable but not so powerful > that it can completely replace Lucene-based systems. So you have to > consider complexity vs functionality if you are ty

Re: Where **not** to use PostgreSQL?

On Thu, Feb 28, 2019 at 1:09 PM Pavel Stehule wrote: > Hi > > čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler < > guettl...@thomas-guettler.de> napsal: > >> Hi experts, >> >> where would you suggest someone to **not** use PostgreSQL? >> > Hard question. There are a lot of general places where P

Re: Where **not** to use PostgreSQL?

Hi čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler < guettl...@thomas-guettler.de> napsal: > Hi experts, > > where would you suggest someone to **not** use PostgreSQL? > > Why would you do this? > > What alternative would you suggest instead? > Don't use Postgres like cache, don't use Postgres

Where **not** to use PostgreSQL?

Hi experts, where would you suggest someone to **not** use PostgreSQL? Why would you do this? What alternative would you suggest instead? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-guidelines

Re: crosstab function

Professor Mueller! I believe that we met, long ago. I graduated from your department in 1984 where I worked closely with the wonderful, late Prof. Dipple. Postgres.app is a very easy way to work with Postgres, and it does include support for tablefunc. If you ever want to check which extensions ar

Re: Overloaded && operator from intarray module prevents index usage.

Andrew Gierth schrieb am 28.02.2019 um 10:29: > [intarray woes] > > Thomas> Is this expected behaviour? Is this caused by the Postgres core > Thomas> (e.g. the optimizer to taking the opclass into account) or is > Thomas> it a "problem" in the way the intarray module defines its > Thomas> oper

Re: Optimizing Database High CPU

Alright will try the upgrade. > Is it a few transactions updating a lot of rows each, or many transactions > updating a few rows each? It is a lot of transaction updating a few rows. Then will look into a connection pooler. Thanks for the response. On Wed, Feb 27, 2019 at 2:01 PM Michael Lewis

Re: Overloaded && operator from intarray module prevents index usage.

> "Thomas" == Thomas Kellerer writes: [intarray woes] Thomas> Is this expected behaviour? Is this caused by the Postgres core Thomas> (e.g. the optimizer to taking the opclass into account) or is Thomas> it a "problem" in the way the intarray module defines its Thomas> operators? It's b

Re: Barman disaster recovery solution

On 2/28/19 9:21 AM, Achilleas Mantzios wrote: On 28/2/19 1:08 π.μ., Ahmed, Nawaz wrote: Hi, I believe the "file copy" method (listed in the table) in pgbackrest is based on pg_basebackup, so i think it should be "pg_basebackup over ssh" as pgbackrest internally calls pg_basebackup. David Ste