Re: Storage Inefficiency In PostgreSQL

ency (in > comparison to MySQL) problem? > Long run pluggable storage should give people a different set of options and choices to make here. > > Many thanks, > > sps-ray > > > --- > This email has been checked for viruses by Avast antivirus software. > https://

Re: When to store data that could be derived

x foo on bar ((id % 1000)); And then use the index on: select * from bar where id % 1000 = 45; You could similarly create index foo on bar (extract(day from date_field)); The left side needs to be indexed (and an immutable expression) but beyond that. > > > > >> > >>

Re: Fwd: Camel case identifiers and folding

that opens (but I'll just > mention that "which characters are letters" doesn't even have a well > defined universal answer). > +1 > > regards, tom lane > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Camel case identifiers and folding

. > > The only mechanism available to put any semantics into the database is via > the naming of identifiers. Why screw with that? Imagine what would happen > if your IDE decided to do that to your java code. > > > Different languages address case and identifiers differently, no

Re: POSTGRES/MYSQL

re considering MySQL you might want to use MariaDB instead. But PostgreSQL avoids most of these issues and ensures that even if you are distributing the db with a proprietary application, there are no licensing implications of doing that. > Regards, > Sonam > -- Best Wishes, Chris Trav

Re:POSTGRES/MYSQL

More blog online, choose one for your attention. https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/ Best Regards, | | Chris | | yuanzefuwa...@126.com | 签名由网易邮箱大师定制 On 03/11/2019 19:31，Sonam Sharma wrote： Hi All, We are planning to migrate our

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 replac

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

t; > Don't use Postgres like cache, don't use Postgres for non transactional > short life often updated data. > > Use inmemory databases instead > > Pavel > > >> >> Regards, >>Thomas Güttler >> >> >> -- >> Thomas Guettler http://www.thom

Re: How to insert bulk data with libpq in C?

every N records, where N is the total count :) I just wanted to make shure OP will not commit after each COPY. Bye. Chris.

Re: How to insert bulk data with libpq in C?

, I suggesttransaction bundling (one commit every 1000 records or so), depending on context. Bye, Chris.

Re: postgresql10-server RPM unpacking of archive failed

time later (and after a reboot) yum update succeeded and we never found out why. I think we can archive this as a one-time glitch with yum. Bye, Chris.

why would postgres be throttling a streaming replication slot's sending?

On 11/12/2018 14:48, Achilleas Mantzios wrote: On 11/12/18 4:00 μ.μ., Chris Withers wrote: I'm looking after a multi-tenant PG 9.4 cluster, and we've started getting alerts for the number of WALs on the server. It'd be great to understand what's generating all that WAL and what's likely

finding out what's generating WALs

to be causing any problems.\ More generally, what's number of WALs is "too much"? check_postgres.pl when used in nagios format only appears to be able to alert on absolute thresholds, does this always make sense? What's a good threshold to alert on? cheers, Chris

!= ANY(array) does not behave as expected

viour of != ANY (ARRAY...) is not useful, then is there any support for (or opposition to) fixing it? And is it a bug that one can't use unnest in a NOT IN expression in the WHERE clause? Thanks, Chris.

Re: debugging intermittent slow updates under higher load

down together with it? Nope, only ones logged are these updates. Chris

Re: debugging intermittent slow updates under higher load

while competing for to lock the same row or object. Is there any existing tooling that does this? I'm loath to start hacking something up when I'd hope others have done a better job already... Chris

Re: surprising query optimisation

On 05/12/2018 14:38, Stephen Frost wrote: Greetings, * Chris Withers (ch...@withers.org) wrote: On 30/11/2018 15:33, Stephen Frost wrote: * Chris Withers (ch...@withers.org) wrote: On 28/11/2018 22:49, Stephen Frost wrote: For this, specifically, it's because you end up with exactly what

Re: surprising query optimisation

I've set effective_cache_size to 200GB, I only see 9G of memory being used. How can I persuade postgres to keep more in memory? cheers, Chris

Re: surprising query optimisation

On 30/11/2018 15:33, Stephen Frost wrote: Greetings, * Chris Withers (ch...@withers.org) wrote: On 28/11/2018 22:49, Stephen Frost wrote: * Chris Withers (ch...@withers.org) wrote: We have an app that deals with a lot of queries, and we've been slowly seeing performance issues emerge. We

Re: psql is hanging

We're kind of pulling out our hair here, any ideas? You might try issuing the command   analyze; right *before* the command that hangs. Clarification: I meant to say: "put it into the script at the location right before the command, that hangs". Bye, Chris.

Re: psql is hanging

en further optimize by doing analyze only on the changed tables. Bye, Chris.

Re: surprising query optimisation

g a big scan rather than some quick lookups based on the index? cheers, Chris

Re: surprising query optimisation

On 28/11/2018 22:49, Stephen Frost wrote: * Chris Withers (ch...@withers.org) wrote: We have an app that deals with a lot of queries, and we've been slowly seeing performance issues emerge. We take a lot of free form queries from users and stumbled upon a very surprising optimisation. So, we

surprising query optimisation

ifferent (better? worse?) if we got all the way up to 10 or 11? cheers, Chris

postgresql10-server RPM unpacking of archive failed

pm package postgresql10-server-10.6-1PGDG.rhel7.x86_64 error: unpacking of archive failed: cpio: lstat (full output below) I'm a bit at loss here... I'd interpret the message towards a corrupted package (?). Has anybody seen something like this? Thanks, Bye, Chris. # yum update Load

Re: Impact on PostgreSQL due to Redhat acquisition by IBM

> Thanks and Regards, > Sachin Kotwal > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: DB size difference after restore

;> On Wed, Oct 3, 2018, 7:43 PM Sonam Sharma wrote: >>> >>>> I have restored the database from backup dump but the size of source >>>> and target databases are different. What can be the reason for this ? >>>> >>>> Regards, >>>> Sonam >>>> >>> -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: How to investigate what postgres is spending time on

tructed with pg_dump and restore on fresh postgres > installation. > Sounds like warming up the cache, but still in a test environment you may want to add auto_explain to your list of preloads and perhaps set it to dump explain analyze when it hits a certain threshold. Note that while du

Re: Code of Conduct

> -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

ve communication and positive change. > Agreed on this. My objection to the additional wording is simply that a) I think it does not tackle the problem it needs to tackle, and b) creates a claim which covers a bunch of things that it really shouldn't. It's a serious bug and I still hope it gets fixed before it causes problems. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct

think that feedback gathered now will have any impact at all in the next revision. > > regards, tom lane > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct

ather that the community won't let that sort of abuse happen no matter who is on the CoC committee. > > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Logical locking beyond pg_advisory

ontent=emailclient> > <#m_-9091154853724945458_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

On Mon, Sep 17, 2018 at 6:08 PM Steve Litt wrote: > On Mon, 17 Sep 2018 17:39:20 +0200 > Chris Travers wrote: > > > > Exactly. And actually the first sentence is not new. The second one > > is a real problem though. I am going to try one last time at an &g

Re: Code of Conduct plan

and Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc > *** A fault and talent of mine is to tell it exactly how it is. *** > PostgreSQL centered full stack support, consulting and development. > Advocate: @amplifypostgres || Learn: https://postgresconf.org > * Unless otherwise stated, opinions are my own. * > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Logical locking beyond pg_advisory

ince it means you can have race conditions in advisory locks that can't happen with other locking issues. I still love advisory locks but they are not a good tool for this. The real solution most of the time is actually to lock the rows by selecting FOR UPDATE and possibly SKIP LOCKED. The way updat

Re: Code of Conduct plan

hink that sentence solves the problems you are trying to solve, and I think it creates new ones. However I have said my piece. Unless there are replies that provide something new for me to add, I won't continue arguing over that from here. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

nt community taking action on the basis of political views, I do see a problem more generally with how these fights get picked and would prefer to see some softening of language to protect the community in that case. But again, I am probably being paranoid. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Slow shutdowns sometimes on RDS Postgres

. Best, Chris On Fri, Sep 14, 2018 at 8:43 AM Jeremy Schneider wrote: > Hi Chris - this is an interesting one that we do see from time to time; > seems worth responding here as actually our best understanding right now > is that this is something in community code, not AWS-specific. > &g

Re: Code of Conduct plan

room for civic engagement even on divisive issues. And frankly I am probably being paranoid here though I find paranoia is a good thing when it comes to care of databases and computer systems. But I do worry about the interactions between the PostgreSQL community and the larger world with thi

Re: Code of Conduct plan

orld, people from my culture (the US) have a tendency to take disagreements regarding political policies, social theories, etc. personally and see abuse/attack where mere disagreement was present. People making complaints aren't necessarily acting in bad faith. 3. If we don't set the expectation ahead of time that we remain pluralistic in terms of political philosophy, culture, then it is way too easy to end up in a situation where people are bringing up bad press for failing to kick out people who disagree with them. Like it or not there are precedents for this in the open source community, such as the dismissal of Brendan Eich, and in an international project with developers from all kinds of cultures with different views on deeply divisive issues, such conflicts could hurt our community. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

On Fri, Sep 14, 2018 at 4:16 PM Tom Lane wrote: > [ Let's try to trim this discussion to just -general, please ] > > Robert Eckhardt writes: > > On Fri, Sep 14, 2018 at 9:41 AM, Adrian Klaver > > wrote: > >> On 9/14/18 1:31 AM, Chris Travers wrote:

Re: Code of Conduct plan

On Fri, Sep 14, 2018 at 4:14 PM Dave Page wrote: > > > On Fri, Sep 14, 2018 at 3:08 PM, Joshua D. Drake > wrote: > >> On 09/14/2018 01:31 AM, Chris Travers wrote: >> >> >> I apologize for the glacial slowness with which this has all been moving. &

Re: Code of Conduct plan

On Fri, Sep 14, 2018 at 11:45 AM Ilya Kosmodemiansky wrote: > On Fri, Sep 14, 2018 at 10:31 AM, Chris Travers > wrote: > > I really have to object to this addition: > > "This Code is meant to cover all interaction between community members, > > whether

Re: Code of Conduct plan

On Fri, Sep 14, 2018 at 10:31 AM Chris Travers wrote: > > > On Wed, Sep 12, 2018 at 10:53 PM Tom Lane wrote: > >> I wrote: >> > Stephen Frost writes: >> >> We seem to be a bit past that timeline... Do we have any update on >> when >>

Re: Code of Conduct plan

greSQL. > > I think we are about ready to announce the initial membership of the > CoC committee, as well, but that should be a separate post. > > regards, tom lane > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Slow shutdowns sometimes on RDS Postgres

y shutting down all of my apps ahead of time is a real headache. Given that I haven't gotten anywhere with their support, I figured I'd try asking on the mailing list. Thanks, Chris On Thu, Sep 13, 2018 at 3:17 PM Joshua D. Drake wrote: > On 09/13/2018 03:04 PM, Chris Williams wrote: > &

Slow shutdowns sometimes on RDS Postgres

e had seen this behavior on their RDS Postgres instances or had any suggestions on how I could shorten the shutdown time? Thanks, Chris

Re: Postgresql

ly free ? >> > Any performance issues or any limitations? >> >> Yes, yes, depends >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Replication protocol question for logical replication.

this is clearly intended to be supported we would like to work with upstream library vendors to make sure this functionality is included. My reading of the docs is that parameterized queries are not possible but simple queries are. Is this correct? -- Best Wishes, Chris Travers Effic

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

pg now, I know more people use it. —pgadmin4: I don’t like web client for database, I used it and it is good for there is simple monitor-windows. For now, I used jetbrains’s product:datagrip, it is also coded by java,but is better for dbeaver. Best Wishes, Chris > 在 2018年7月17日，上午6

RE: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

VS Code is open source (MIT License). You may be thinking of Visual Studo, the closed source IDE - the two are not the same Met vriendlijke groet, REDstack BV Chris Coutinho Onderzoeker/Data analist -Original Message- From: Tim Clarke [mailto:tim.cla...@minerva-analytics.info] Sent

Re: Code of Conduct plan

; I'd probably leave it up to the CoC team/people to figure it out. :) +1 If it were me I would just say that CoC has an obligation to try in good faith to resolve things in line with the common interest of an international community and leave it at that. > > > + Justin > >

Re: Code of Conduct plan

have to sort out such > situations and try to reconcile the two people without either starting a > war or driving away either person. They may not always succeed. But not > trying is not a better answer. For what its worth, I am 100% in agreement with everything Tom just said here. --

Re: Code of Conduct plan

ything bad > where I come from." > This is true but it is needed that the committee is culturally diverse and advertised as such. Otherwise I am concerned that it would signal to some people that a certain sort of abuse would be permitted. > > -- > -- Christophe Pettus >x...@thebuild.com > > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

On Thu, Jun 7, 2018 at 2:14 AM, Jan Claeys wrote: > On Wed, 2018-06-06 at 07:27 +0200, Chris Travers wrote: > > The real fear here is the code of conduct being co-opted as a weapon > > of world-wide culture war and that's what is driving a lot of the > > resistance here.

Re: Code of Conduct plan

On Wed, Jun 6, 2018 at 2:07 AM, Jan Claeys wrote: > On Tue, 2018-06-05 at 16:45 +0200, Chris Travers wrote: > > If I may suggest: The committee should be international as well > > and include people from around the world. The last thing we want is > > for it to be dominat

Re: Code of Conduct plan

On Tue, Jun 5, 2018 at 8:42 PM, James Keener wrote: > I accidentally didn't send this to the whole list. I'll let Chris resend > his response if he'd like. > > On Tue, Jun 5, 2018 at 1:58 PM, James Keener wrote: > >> I think the fundamental outcome is likely to be

Re: Code of Conduct plan

ghout the world with a lot of people having very deep-seated feelings on the issue, where one can expect small differences to lead to big conflicts. And I think we want to avoid wading into those conflicts. > > Jim > > -- > Sent from my Android device with K-9 Mail. Please excuse

Re: Code of Conduct plan

third parties in the US political landscape. An international CoC committee is our best defense against an effort to co-opt the community in the way you are worried about. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

l as of > July 1 2018. > > regards, tom lane > > [1] https://www.postgresql.org/message-id/56a8516b.8000...@agliodbs.com > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: Code of Conduct plan

gt; > Be very careful in attempting to codify 'correct' behaviour! >> >> > Correct. I think one way to look at all of this is, "if you wouldn't say > it to your boss or a client don't say it here". That too has problems but > generally speaking I think it keeps the restrictions rational. > > I will post a more specific set of thoughts here but in general I think the presumption ought to be that people are trying to work together. Misunderstanding can happen. But let's try to act in a collegial and generally respectful way around eachother. -- Best Regards, Chris Travers Database Administrator Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com Saarbrücker Straße 37a, 10405 Berlin

Re: JDBC connectivity issue

I did the re install not to change versions but to now know what version I am running On 03/21/2018 02:44 PM, Adrian Klaver wrote: On 03/21/2018 01:16 PM, chris wrote: I wasnt able to find what version we had installed so we went ahead and reinstalled it Maybe I am missing something

Re: JDBC connectivity issue

I wasnt able to find what version we had installed so we went ahead and reinstalled it. we downloaded the current version JDBC 4.1 Driver 42.2.1.jre7 We are still having the same problem. Thanks On 03/14/2018 03:27 PM, Adrian Klaver wrote: On 03/14/2018 01:47 PM, chris wrote: Oh sorry

Re: JDBC connectivity issue

Oh sorry for getting the wrong information. How would I get the information on what driver is currently installed? On 03/14/2018 09:44 AM, Adrian Klaver wrote: On 03/14/2018 07:51 AM, chris wrote: I believe its Postgresql-9.4.1208.jre7.jar Pretty sure solving this is going to require

Re: JDBC connectivity issue

I believe its Postgresql-9.4.1208.jre7.jar On 03/13/2018 05:48 PM, Adrian Klaver wrote: On 03/13/2018 04:46 PM, chris wrote: I'm sorry that took a few days but I am running; Postgresql-9.4 and jre7.jar What we are looking for is the JDBC driver you are using? Thanks in advance

Re: JDBC connectivity issue

I'm sorry that took a few days but I am running; Postgresql-9.4 and jre7.jar Thanks in advance. On 03/08/2018 02:30 PM, chris wrote: Given that the syntax looks correct for the url, how would we go about debugging that it's not seeing the comma? On 03/08/2018 02:27 PM, Adrian Klaver

Re: JDBC connectivity issue

Given that the syntax looks correct for the url, how would we go about debugging that it's not seeing the comma? On 03/08/2018 02:27 PM, Adrian Klaver wrote: On 03/08/2018 01:12 PM, chris wrote: Hi, I have a JDBC temping to connect to 2 postgres nodes in a hot standby configuration

JDBC connectivity issue

Hi, I have a JDBC temping to connect to 2 postgres nodes in a hot standby configuration. Running postgres 9.4 on centos6. What we are trying to accomplish is in an event of a failover, to first try to connect to the master. If that fails to then reach out to the standby. I looked online

plpgsql function-parsing

uthors_cdc TO bob I'm looking into parsing the string but i can't find a way to parse in a way that'll work. Any ideas? Thanks, Chris

Re: selecting timestamp

Thank you everyone for the help. Sorry there wasn't much detail given originally. CURRENT_TIMESTAMP at time zone 'UTC' as ct4 Seems to be working well. Thanks, Chris On 02/27/2018 01:26 PM, David G. Johnston wrote: On Tue, Feb 27, 2018 at 1:16 PM, chris <chr...@pgsqlrocket.com <mail

selecting timestamp

What is the best way of selecting current timestamp in UTC? SELECT CURRENT_TIMESTAMP   as ct1 ,timezone('UTC',CURRENT_TIMESTAMP) as ct2 ,timezone('utc',now()) as ct3 ,CURRENT_TIMESTAMP at time zone 'UTC' as ct4 ,NOW() at time zone 'utc' as ct5

Re: gathering ownership and grant permissions

I'm sorry I realized that I only need to know which users have permissions to the table which I can do through $ psql -t SELECT grantee FROM information_schema.role_table_grants WHERE table_name='table_name' GROUP BY grantee; thanks! On 02/16/2018 01:13 PM, chris wrote: Thanks

Re: gathering ownership and grant permissions

which my script can isolate the owner's name and set it to a variable on its own. Same with grant. Thanks On 02/16/2018 01:05 PM, Melvin Davidson wrote: On Fri, Feb 16, 2018 at 2:47 PM, chris <chr...@pgsqlrocket.com <mailto:chr...@pgsqlrocket.com>> wrote: HI,

gathering ownership and grant permissions

OWNER TO bob. This is on postgresl 9.6. Thank you, Chris

Re: Re: PostgreSQL needs percentage function

to write a function that displays X as a >>> percentage of Y, rounded to 1 digit to the right of the decimal point. >>> -- >>> Mike Nolan >>> >>> >> > >Percentage calculation exists in almost any databse and information > system > > Th

transaction wrap around

Hello, How would I investigate if my database is nearing a transaction wrap around. Best Regards, Chris

Re: Adding identity column to a non-empty table

orce the user to jump through hoops to add an IDENTITY column. Thanks, Chris.

Re: Searching for big differences between values

I need to search for big differences between values. [...] Hi, from an SQL point of view this is not difficult, but you need to carefully define a criteria for the outliers. For example, to find values that are more than a standard deviation away from the mean, do something like this: chris

Re: Searching for big differences between values

On Nov 30, 2017 08:35, "Durumdara" wrote: Hello! Somewhere the users made mistakes on prices (stock). I need to search for big differences between values. For example: 20 21 21,5 30 28 .. 46392 <- 46392 <- But it could be: 42300 43100 44000 43800 65000 <- 42100 Human

<    1   2