Re: [GENERAL] pg_basebackup running from a remote machine

t; pg_basebackup over an SSL connection with SSL compression enabled. > But ssl compression is disabled by default on most systems, and not even supported at all on many without doing a custom compilation of the ssl library. Cheers, Jeff

Re: [GENERAL] filter records by substring match of an postgresql array column

array_to_string($1,', ') $$; And then build your index on that function. I don't think there are any caveats on this. Array_to_string is not immutable because it can work with dates and numbers, which can change with configuration settings, such as timezone. But when given text[] argument, I think it actually is immutable. Cheers, Jeff

Re: [GENERAL] filter records by substring match of an postgresql array column

e? The *Contains `@>` operator* will do full string comparisons as far as I understood. The extension parray_gin ( https://pgxn.org/dist/parray_gin/doc/parray_gin.html) offers the @@> operator. Cheers, Jeff

Re: [GENERAL] [HACKERS] SSL and Encryption

On Thu, Nov 2, 2017 at 10:22 PM, John R Pierce <pie...@hogranch.com> wrote: > On 11/2/2017 10:12 PM, Jeff Janes wrote: > >> https://wiki.postgresql.org/wiki/List_of_drivers >> >> What is 'python native'? psycopg works as long you update your libpq. >> > &g

Re: [GENERAL] [HACKERS] SSL and Encryption

f the more popular bindings now? jdbc, python native, > etc ? > https://wiki.postgresql.org/wiki/List_of_drivers What is 'python native'? psycopg works as long you update your libpq. Cheers, Jeff

Re: [GENERAL] Where to find development builds of pg for windows

season: https://www.enterprisedb.com/products-services-training/pgdevdownload (As linked from https://www.postgresql.org/download/snapshots/) But since v10 was just released, it will be a long time before the next beta (for v11) is out. Cheers, Jeff

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 9:41 AM, Ron Johnson <ron.l.john...@cox.net> wrote: > On 10/09/2017 11:33 AM, Jeff Janes wrote: > > On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson <ron.l.john...@cox.net> wrote: > >> Hi, >> >> v8.4.20 >> >> This is what

Re: [GENERAL] Using cp to back up a database?

ocal directory > also be consistent? > But it isn't consistent by itself. That is why the log shipping is required (or an equivalent method of keeping the necessary logs around), to fix up the consistency. Cheers, Jeff

Re: [GENERAL] Using cp to back up a database?

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > Hi, > > v8.4.20 > > This is what the current backup script uses: > > /usr/bin/psql -U postgres -c "SELECT pg_start_backup(' > Incrementalbackup',true);" > cp -r /var/lib/pgsql/data/* $dumpdir/data/ > /usr/bin/psql -U

Re: [GENERAL] pgcrypto encrypt

ng seen it done. But it should of course be possible to make it work, even if that means rearranging the code of pgcrypto and compiling into something that is standalone. Cheers, Jeff

Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

On Sep 14, 2017 7:07 AM, "Arthur Zakirov" wrote: On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote: > Putting either of these two lines in the .conf file cause it to fail to start. Can you show error messages from logs? And the version.

Re: [GENERAL] contrecord is requested

On Mon, Sep 11, 2017 at 1:27 PM, Scott Marlowe wrote: > So we have a db we're trying to rewind and get synced to the master. > pg_rewind says it doesn't need rewinding, and when we try to bring it > up, it gets this error: > > "contrecord is requested by 2E7/4028" >

Re: [GENERAL] pg_ident mapping Kerberos Usernames

_-]+)@A\.DOMAIN\.TLD$ \1 testnet /^([0-9A-Za-z_-]+) \1...@a.domain.tld But since your pg_hba has include_realm=1, I don't know how you are getting the realmless "system user" names in the first place, so the last line really shouldn't be necessary. Cheers, Jeff

Re: [GENERAL] Needing verification on instructions for streaming replication

e choice of how to host it would depend on those reasons. Cheers, Jeff

Re: [GENERAL] pg_ident mapping Kerberos Usernames

On Sun, Sep 10, 2017 at 11:25 AM, wrote: > On 09/10/2017 02:39 AM, Magnus Hagander wrote: > >> On Sat, Sep 9, 2017 at 6:44 PM, > > wrote: >> >> Hi, >> >> I'm trying to get

Re: [GENERAL] Performance with high correlation in group by on PK

On Tue, Aug 29, 2017 at 1:20 AM, Alban Hertroys <haram...@gmail.com> wrote: > On 28 August 2017 at 21:32, Jeff Janes <jeff.ja...@gmail.com> wrote: > > On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys <haram...@gmail.com> > wrote: > >> > >> Hi all,

Re: [GENERAL] Confused about max_standby_streaming_delay

On Thu, Sep 7, 2017 at 1:16 AM, Robert Inder <rob...@interactive.co.uk> wrote: > > > On 6 September 2017 at 20:47, Jeff Janes <jeff.ja...@gmail.com> wrote: > >> >>> Have I misunderstood something? Or is Postgres not actually configured >>> the wa

Re: [GENERAL] Confused about max_standby_streaming_delay

lly configured > the way I think it is? > The standby will wait for ten minutes to obtain the lock it wishes to obtain. In 9.4, if something other than dump of database b was already blocking it for 8 minutes before the dump starts, then the dump of database b will only have 2 minutes, not 10, before it gets cancelled. So, are there any long running jobs in database b other than the pg_dump? Cheers, Jeff

Re: [GENERAL] Performance with high correlation in group by on PK

"VBAK_MANDT", > "VBAK_VBELN") with very few distinct values for "VBAK_MANDT" (in fact, > we only have 1 at the moment!). > You need an "index skip-scan" or "loose index scan". PostgreSQL doesn't currently detect and implement them automatically, but you can use a recursive CTE to get it to work. There are some examples at https://wiki.postgresql.org/wiki/Loose_indexscan Cheers, Jeff

Re: [GENERAL] install the oracle data wrapper extension

256-GCM-SHA384, > bits: 256, compression: off) > > Any suggestions? > What steps did you take to install oracle_fdw? Cheers, Jeff

Re: [GENERAL] A question on GIN indexes and arrays

or one of your databases. Try fully qualifying the operator. OPERATOR(pg_catalog.@>) Cheers, Jeff

Re: [GENERAL] How to delete default privileges

> and don't see it on the documentation either. > > > Any help would be greatly appreciated. You just reverse them, changing GRANT...TO into REVOKE...FROM. When the altered-default is the same thing as the default-default, then the altered-default disappears. Cheers, Jeff

Re: [GENERAL] How to make server generate more output?

On Fri, Aug 11, 2017 at 1:14 PM, Rui Pacheco wrote: > Hello, > > I know this is a bit vague but I’m looking for a configuration > parameter/startup switch that once set or enabled would make Postgresql > return more data that normal. Specifically the wire protocol would

Re: [GENERAL] hot standby questions

-X, or --xlog-method, then I would be reluctant to take any of that site's other advice seriously. But note that in version 10, -x will go away and the default will be changed so that not specifying anything will be the same as -X stream. perhaps you are reading advice aimed at a future version. Cheers, Jeff

Re: [GENERAL] select md5 result set

nces. You could use something like: \copy (select * from blah order by something) to program 'md5sum' binary but I don't know how you would get the output back into your program once it shows up on your screen. Cheers, Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Tue, Aug 1, 2017 at 9:24 AM, Dmitry Lazurkin <dila...@gmail.com> wrote: > On 08/01/2017 07:13 PM, Jeff Janes wrote: > > I think that HashSet is a Java-specific term. It is just a hash table in > which there is no data to store, just the key itself (and probably a cash

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 31, 2017 at 12:29 PM, Dmitry Lazurkin <dila...@gmail.com> wrote: > On 31.07.2017 19:42, Jeff Janes wrote: > > I think it is simply because no one has gotten around to implementing it > that way. When you can just write it as a values list instead, the > incentiv

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

inue digging through the hash collision chain lookin for key collisions that can't exist? I don't know, maybe there are some bits set that make it still do semi-join, just doesn't present itself as such? Cheers, Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

r running with your patch from https://www.postgresql.org/message-id/10078.1471955...@sss.pgh.pa.us. On that server, it did choose the semi-join. But I have no idea why, as it seems like the effect of that patch would have been to change the distinct estimate from the magic hard-coded 200, to the natural 200 coming from the query itself. Why would that affect the cost? Cheers, Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Tue, Jul 25, 2017 at 2:03 AM, Dmitry Lazurkin <dila...@gmail.com> wrote: > On 25.07.2017 05:50, Jeff Janes wrote: > >> It isn't either-or. It is the processing of millions of rows over the >> large in-list which is taking the time. Processing an in-list as a hash

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

tgreSQL has Hash Semi-Join... For which task the database > has node of this type? > > I think it is simply because no one has gotten around to implementing it that way. When you can just write it as a values list instead, the incentive to make the regular in-list work better is not all that strong. Cheers, Jeff

Re: [GENERAL] Index Only Scan and Heap Fetches

ou visit a heap tuple based on reference from an index tuple, and find that the heap tuple is dead-to-all, then when you get back to the index you can kill that index's reference to the heap tuple. Future accesses via that same index for the same tuple then no longer need to visit the heap. Cheers, Jeff

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier < > michael.paqu...@gmail.com> > > wrote: &g

Re: [GENERAL] Indexes being ignored after upgrade to 9.5

dex scans back under 9.2, or as slow as the the non-duplicate indexes were? Cheers, Jeff

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

t which is taking the time. Processing an in-list as a hash table would be great, but no one has gotten around to it implementing it yet. Maybe Dmitry will be the one to do that. Cheers, Jeff

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier > > <michael.paqu...@gmail.com> wrote: > >

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier <michael.paqu...@gmail.com > wrote: > On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > > > I think that pg_stat_wal_receiver should be crossreferenced in > > https://www.postgresql.org

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

OU which is why I’m trying to authenticate with just the DC. > > Have you tried using the "simple bind mode" where you specify just the ldapsuffix and the ldapserver? Cheers, Jeff

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > > > I think that none of the recovery information functions > > (https://www.postgresql.org/docs/9

[GENERAL] Monitoring of a hot standby with a largely idle master

crashed, or someone has changed the firewall rules. Is there a way to monitor from SQL the last time the standby was able to contact the master and initiate streaming with it? Other than trying to write a function that parses it out of pg_log? Cheers, Jeff

[GENERAL] debugging SSL connection problems

s and ends within the larger server response method. Thanks, Jeff

Re: [GENERAL] Strange case of database bloat

ll the contrib extension pg_freespacemap and use "select * from pg_freespace('table_name')" to see if PostgreSQL agrees that the space is re-usable? Cheers, Jeff

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Mon, Jul 3, 2017 at 10:39 AM, rajan <vgmon...@gmail.com> wrote: > Thanks, Jeff. > > Now I am going back to my old question. > > Even though *Session 2* fails to update with UPDATE 0 message, its txid is > saved in xmax of updated(by *Session 1*) tuple. > > As it

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

should refer for viewing the > pointer. > It is 't_ctid' Cheers, Jeff

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 8:55 PM, rajan <vgmon...@gmail.com> wrote: > Thanks, Jeff. That helps understanding it 50%. > > *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple > is > marked for deletion. It means that *(0,2) never exists* when Session 2 i

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

s you use pgeinspect) and then re-evaluates if it still meets the criterion. Since it doesn't meet the criterion anymore, it doesn't finish updating the tuple. Cheers, Jeff

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

of pg_dump to dump things out of a 9.2 server which you want to load to another 9.2 server. Don't be at the mercy of your $PATH. (Or even more better yet, upgrade the servers from 9.2 to 9.6, and then use 9.6's pg_dump) Cheers, Jeff

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:39 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Thu, Jun 22, 2017 at 1:34 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 06/22/2017 01:29 PM, Jeff Janes wrote: >> >>> On Thu, Jun 22, 2017 at 1:22 PM, Adr

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:34 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 06/22/2017 01:29 PM, Jeff Janes wrote: > >> On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>> wrote: >>

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 06/22/2017 01:13 PM, Jeff Janes wrote: > >> On Thu, Jun 22, 2017 at 12:06 PM, Ray Stell <ste...@vt.edu > ste...@vt.edu>> wrote: >> >> I used "initdb -U&

Re: [GENERAL] 10beta1 role

rting up the server pg_ctl keeps trying to connect to the server so once it is running, it can report success. But it doesn't know who to connect as, so it just uses the default. Cheers, Jeff

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

as on the performance of other tasks. If acceptable, repeat this on production (although really, I don't that you have much of a choice on whether the effect it is acceptable or not--it needs to be done.) Cheers, Jeff

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 8:49 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > If you have a RAID, set it to the number of spindles in your RAID and > forget > > it. It is usually one

Re: [GENERAL] effective_io_concurrency increasing

to pre-fetch it. That is only easy to know for bitmap scans. If you have a RAID, set it to the number of spindles in your RAID and forget it. It is usually one of the less interesting knobs to play with. (Unless your usage pattern of the database is unusual and exact fits the above pattern.) Cheers, Jeff

Re: [GENERAL] workaround for column cross-correlation

he other hand, are a different matter. Cheers, Jeff

Re: [GENERAL] Performance issue with Pointcloud extension

not anymore :) I don't see where the Pointcloud > and PostGIS extensions could conflict. > Can you run 'perf top' on the slow query? That might pretty quickly tell you which function is taking up your time. Cheers, Jeff

Re: [GENERAL] Why is posgres picking a suboptimal plan for this query?

Maybe it should first execute the subquery and then re-plan the rest of the query based on the results. But there is no provision for it to do that, and no concrete plans (that I know of) to implement such a thing. > > The results here simply do not make sense to me, should I be piping > dates in here to avoid this issue and running 2 queries instead of 1? > That is the most pragmatic approach. It isn't very nice, but the alternatives are worse. Cheers, Jeff

Re: [GENERAL] Encrypt with BouncyCastle and decrypt with pg_pub_decrypt

it is hard to provide more advice. encrypt a dummy payload with a dummy password and show us what you get and what you do with it. Cheers, Jeff

Re: [GENERAL] database is not accepting commands

t same error appeared. > The error appeared after two hours of vacuuming where verbose shows passing > tables. > I don't see any way that error message can be generated while in standalone mode. Are you sure you sure you don't have multiple instances running, and you are mixing up the logs between them? Cheers, Jeff

Re: [GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

On Wed, May 3, 2017 at 3:57 AM, Thomas Güttler <guettl...@thomas-guettler.de > wrote: > Am 02.05.2017 um 05:43 schrieb Jeff Janes: > >> On Sun, Apr 30, 2017 at 4:37 AM, Thomas Güttler < >> guettl...@thomas-guettler.de <mailto:guettl...@thomas-guettler.de>>

Re: [GENERAL] Can PG replace redis, amqp, s3 in the future?

e offs. > - s3 (Blob storage) > No. You can certainly use PostgreSQL to store blobs. But then, you need to store the PostgreSQL data **someplace**. If you don't store it in S3, you have to store it somewhere else. Cheers, Jeff

Re: [GENERAL] full text search on hstore or json with materialized view?

email: > An idea that has come up is to use a materialized view or secondary table with triggers, where we would have 3 columns (id, key, value). How would this be different from the "triple store" you are abandoning? Cheers, Jeff

Re: [GENERAL] Why so long?

cuum on? Have you manually vacuumed the table recently? Cheers, Jeff

Re: [GENERAL] streaming replication and archive_status

On Tue, Apr 18, 2017 at 5:20 AM, Luciano Mittmann <mittm...@gmail.com> wrote: > > > Hi Jeff, > > **Does each file in pg_xlog/archive_status/ have a corresponding file one > directory up? > > no corresponding file on pg_xlog directory. That is the question.. for &

Re: [GENERAL] # of connections and architecture design

s being clean doesn't help much, if your log settings are set to be too terse. Is log_lock_waits on? log_checkpoints? track_io_timing (doesn't show up in the logs, you have to query database views)? Is log_min_duration_statement set to a reasonable value? log_autovacuum_min_duration? Are you using pg_stat_statement (also doesn't show up in the logs, you have to query it), and perhaps auto_explain? Cheers, Jeff

Re: [GENERAL] streaming replication and archive_status

2017-04-17 17:08 GMT-03:00 Jeff Janes <jeff.ja...@gmail.com>: > On Mon, Apr 17, 2017 at 12:22 PM, Luciano Mittmann <mittm...@gmail.com> > wrote: > >> Hi All, >> >> anyone knows why there are so many files in the directory >> pg_xlog/archive_s

Re: [GENERAL] streaming replication and archive_status

le to clean this .done files or just don't need to worry ? > > It's not occurs on primary or standalone servers, just on replication. > What version? Are you logging checkpoints, and if so what do they say? Cheers, Jeff

Re: [GENERAL] Service outage: each postgres process use the exact amount of the configured work_mem

nt problems. Nothing shown in that output is alarming. > What can be happened? > What error messages are the customers getting when they fail to connect? What error messages are showing up in the server log file? What do you see in pg_stat_actvity? Cheers, Jeff

Re: [GENERAL] store key name pattern search

_name"=>"slc", > "item_add_by"=>"557652" > (2 rows) > > Is there anyway I can do a pattern search by hstore key name something like > > select * from foo where skeys(col1) like '%add_by%'; > > I looked on the doc but did not see anything , or did I miss it ? > select * from foo where array_to_string(akeys(x),';') like '%add\_by%'; Note that I back-slashed the underscore, otherwise it acts as a wildcard and may match more than you bargained for. Cheers, Jeff

[GENERAL] Trigger based logging alternative to table_log

probably use if doing this from scratch. But I'd rather preserve the existing log tables than either throw away that data, or port it over to a new format. Is there any better-maintained code out there which would be compatible with the existing schema used by table_log? Cheers, Jeff

Re: [GENERAL] Table not cleaning up drom dead tuples

a physical replication slot was created in the shared memory of a previously existing logical slot, catalog_xmin would not be cleared. That in turn would prevent vacuum from doing its duties. Cheers, Jeff

Re: [GENERAL] hight cpu %sy usage

happened to the TPS? It is easy to shift load from sy to us if you do it by making things slower overall by bloating the time spent in user space. I suspect that that is what happened. Cheers, Jeff

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Thu, Feb 23, 2017 at 2:42 AM, Bill Moran <wmo...@potentialtech.com> wrote: > On Wed, 22 Feb 2017 13:19:11 -0800 > Jeff Janes <jeff.ja...@gmail.com> wrote: > > > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure <mmonc...@gmail.com> > wrote: > > > >

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis <tim.bel...@metaswitch.com> wrote: > > > > > *From:* Jeff Janes [mailto:jeff.ja...@gmail.com] > *Sent:* 17 February 2017 02:59 > *To:* Tim Bellis <tim.bel...@metaswitch.com> > *Cc:* pgsql-general@postgresql.org &

Re: [GENERAL] bloat indexes - opinion

e and the size of the index to be about the same? Cheers, Jeff

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

for a long timer or a short time. Them a short requestor which is blocked behind a long requestor could let other compatible-with-held requests jump over it. But once it was only blocked by short locks, it would reassert the normal order, so it can't get permanently blocked by a constantly overlapping stream of short locks. But how would you get all lock requestors to provide a reasonable estimate? Cheers, Jeff

Re: [GENERAL] Indexes and MVCC

r table, it will inhibit this mechanism from working (as well as inhibiting vacuum itself from cleaning them up) Cheers, Jeff

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

oin is actually faster than the merge join. Also, it just seems cleaner than fiddling with enable_* parameters and then having to remember to reset them when done. Cheers, Jeff

Re: [GENERAL] disk writes within a transaction

be easier to refactor the code than to quantify just how much damage it does. cheers, Jeff

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle <hin...@cipafilter.com> wrote: > Thanks Jeff, > > No triggers or foreign key constrains: > > psql:postgres@cipafilter = \d+ titles > Table "public.ti

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

to the WHERE clause which you progressively move. Or select the rows you want to keep into a new table, and then drop the old one, rename the new one, and rebuild any constraints or indexes and other dependencies. This can be pretty annoying if there a lot of them. Cheers, Jeff

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

tically de-duplicate the hash table as it is being built. So n_distinct correctly does not have an influence on the estimated RAM usage, because it doesn't influence the actual ram usage either. It sounds like your work_mem is set way too high. What is it set to? And what version of PostgreSQL are you using? Cheers, Jeff

Re: [GENERAL] Locks Postgres

u already knew). But we don't know how busy. A single complex query can easily hold several dozens locks. Cheers, Jeff

Re: [GENERAL] Transaction apply speed on the standby

prefetch mechanism for replay. Cheers, Jeff

Re: [GENERAL] Why autvacuum is not started?

just analyzed. But that re-think has yet to be finished, and certainly won't be back-ported to 9.4. Cheers, Jeff

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

PostgreSQL already knows what date today is. Why does cron have to tell it? Just do 'select logextract()' and let Postgresql compute the dates for itself. Cheers, Jeff

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

atabase is opened of regular use. Cheers, Jeff

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

e sure that the full stack, from PostgreSQL down to the hardware on the SSD, is crash safe, the only real way is to do some "pull the plug" tests. Cheers, Jeff

Re: [GENERAL] Moving pg_xlog

disk write pattern of data in pg_xlog is sequential > writes, while there will be likely random writes on the main data folder. > This is only the case if you have a write cache, or are doing bulk loads. With small transactions and without a write cache, the need for constant syncs totally destroys the benefits of sequential writes. Cheers, Jeff

Re: [GENERAL] Moving pg_xlog

on on every combination of hardware, OS, file-system type, version of that file-system, and your usage pattern. That is inherently the realm of the wiki or the blogs. Cheers, Jeff

Re: [GENERAL] Backup "Best Practices"

, it just looks at the name itself. So you could pull backup_label out of the tar file, parse the contents and use them to construct the command to give to pg_archivecleanup. I think it would really be nice if pg_basebackup -D backup_dir -Ft would create the backup_label file not only in the tarball, but also (as the final step) create it as a loosie file in the backup_dir. Cheers, Jeff

Re: [GENERAL] Backup "Best Practices"

a flag to pg_basebackup which even allows you to bypass the creation of those files. You are looking in the WAL archive itself, correct? Not somewhere in a listing of the base.tar.gz file? Cheers, Jeff

Re: [GENERAL] Query regarding deadlock

exing multiple tables in the same transaction? If not, I don't see why these should deadlock. One should win, and the other should block. If you are reindexing multiple tables in the same transaction, why are you doing that? I can't think of a situation where you couldn't use separate transactions per table. Cheers, Jeff

Re: [GENERAL] Backup "Best Practices"

ng disk in a timely fashion. So if you can run pg_receivexlog running on the NFS-host machine pointed to the local storage, not looping back over NFS, that is safer. Cheers, Jeff

Re: [GENERAL] Strict min and max aggregate functions

On Sun, Nov 20, 2016 at 2:45 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2016-11-19 22:12 GMT+01:00 Jeff Janes <jeff.ja...@gmail.com>: > >> I need "strict" MIN and MAX aggregate functions, meaning they return NULL >> upon any NULL

[GENERAL] Strict min and max aggregate functions

$1 IS NULL THEN NULL ELSE $1[1] END ; $$; CREATE AGGREGATE strict_min (x anyelement) ( sfunc = strict_min_agg, stype = anyarray, finalfunc = strict_min_final ); Cheers, Jeff

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

ns rows in each table. > "Properly" normalizing your data is a wonderful thing, no doubt about it, if you are prepared to deal with the consequences of doing so. But not everyone has that luxury. Which is why there is more than one way of doing things. Cheers, Jeff

Re: [GENERAL] Trigram is slow when 10m rows

using? It might work better under 9.6/1.3 Since your query doesn't use wildcards, it is probably more well suited to a regular btree index, perhaps with citext. Cheers, Jeff

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

ed at index build time? If he doesn't want to update to 9.6, I think he would need to rebuild the index, removing the "gin__int_ops" specification. Cheers, Jeff

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

most_common_elems. Is it empty, or is it not empty? If not empty, does it contain the specific values you used in your queries? Cheers, Jeff

  1   2   3   4   5   6   7   8   9   10   >