Re: [GENERAL] pg_basebackup running from a remote machine

SL 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

') $$; 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

o 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 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. >> > > > I thought

Re: [GENERAL] [HACKERS] SSL and Encryption

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

e in 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 wrote: > On 10/09/2017 11:33 AM, Jeff Janes wrote: > > On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > >> Hi, >> >> v8.4.20 >> >> This is what the current backup script uses: >> >>

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

so 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 postgres template1 -c "SELE

Re: [GENERAL] pgcrypto encrypt

ould 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" > > And fails to get back up

Re: [GENERAL] pg_ident mapping Kerberos Usernames

/(.*) \1 testnet/^([0-9A-Za-z_-]+)@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

t 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 pg_ident to map "user1" and "us...@a.domain.tld" >> to "user1" in postgres,

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

On Tue, Aug 29, 2017 at 1:20 AM, Alban Hertroys wrote: > On 28 August 2017 at 21:32, Jeff Janes wrote: > > On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys > wrote: > >> > >> Hi all, > >> > >> It's been a while since I actually got to use

Re: [GENERAL] Confused about max_standby_streaming_delay

On Thu, Sep 7, 2017 at 1:16 AM, Robert Inder wrote: > > > On 6 September 2017 at 20:47, Jeff Janes wrote: > >> >>> Have I misunderstood something? Or is Postgres not actually configured >>> the way I think it is? >>> >> >> The standby

Re: [GENERAL] Confused about max_standby_streaming_delay

> 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

n ("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

dw.control": > No such file or directory > ft_node=# \conninfo > You are connected to database "ft_node" as user "postgres" on host > "localhost" at port "5432". > SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-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

y fully qualifying the operator. OPERATOR(pg_catalog.@>) Cheers, Jeff

Re: [GENERAL] How to delete default privileges

;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 return > more notification

Re: [GENERAL] hot standby questions

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

't cause differences. 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 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 > of the hashcode of tha

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

On Mon, Jul 31, 2017 at 12:29 PM, Dmitry Lazurkin 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 > incentive to make the regular in-l

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

ng 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

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 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 >> table would be

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

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

uple 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 wrote: > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier < > michael.paqu...@gmail.com> > > wrote: > >> What do you think about the patch attached? >

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

t; still ignored unless we force using enable_seqscan=no or reduce > random_page_cost to 2. The query response times using the new indexes are > still as slow when we do this. > Still as slow as what? As slow as when you use the seq scan, or as slow as when you used index 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

sing 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 wrote: > On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes wrote: > > On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier > > wrote: > >> > >> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes > wrote: > >> >

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

On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier wrote: > On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes wrote: > > > > I think that pg_stat_wal_receiver should be crossreferenced in > > https://www.postgresql.org/docs/9.6/static/hot-standby.html, near the >

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

cate 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 wrote: > On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes wrote: > > > > I think that none of the recovery information functions > > (https://www.postgresql.org/docs/9.6/static/functions-admin. > html#FUNCTIONS-RECOVERY-INFO-TA

[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

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

Re: [GENERAL] Strange case of database bloat

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 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 becomes an old txid, how co

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

iewing 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 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 is > trying to perform the

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

hen 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

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 wrote: > On Thu, Jun 22, 2017 at 1:34 PM, Adrian Klaver > 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

Re: [GENERAL] 10beta1 role

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

Re: [GENERAL] 10beta1 role

On Thu, Jun 22, 2017 at 1:22 PM, Adrian Klaver 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>> wrote: >> >> I used "initdb -U" to specify an alternate superuser. On startup it

Re: [GENERAL] 10beta1 role

s 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

rmance 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 wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > > 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 &

Re: [GENERAL] effective_io_concurrency increasing

nly 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

n hash join (due to poor estimate?) caused OOM. > As far as I know, the only way a hash join should do this is if the join includes a huge number of rows with exactly the same 32 bit hash codes. Otherwise, it should spill to disk without causing OOM. Hash aggregates, on the other hand, are a different matter. Cheers, Jeff

Re: [GENERAL] Performance issue with Pointcloud extension

7;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?

t 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

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

d. > 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 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>> >> wrote: >> >> I

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

trade 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?

p 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?

lly vacuumed the table recently? Cheers, Jeff

Re: [GENERAL] streaming replication and archive_status

On Tue, Apr 18, 2017 at 5:20 AM, Luciano Mittmann 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 > some reason or some parame

Re: [GENERAL] # of connections and architecture design

scared it can happen again. > The logs 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 : > On Mon, Apr 17, 2017 at 12:22 PM, Luciano Mittmann > wrote: > >> Hi All, >> >> anyone knows why there are so many files in the directory >> pg_xlog/archive_status/ in replication server? >> >> # pg_xlog

Re: [GENERAL] streaming replication and archive_status

es 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

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

"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

ch I would 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

red 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

s 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 wrote: > On Wed, 22 Feb 2017 13:19:11 -0800 > Jeff Janes wrote: > > > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure > wrote: > > > > > > On Thursday, February 16, 2017, Tom Lane wrote: > > > > >

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

On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis wrote: > > > > > *From:* Jeff Janes [mailto:jeff.ja...@gmail.com] > *Sent:* 17 February 2017 02:59 > *To:* Tim Bellis > *Cc:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Autovacuum stuck for hours, blocking que

Re: [GENERAL] bloat indexes - opinion

reported should include toast, then change it to do that, or request the author of whatever-this-is to make that change. What indication is there that the index is bloated? If the meat-and-potatoes of a table is held in toast, then wouldn't you expect the size of the table and the size of the index to be about the same? Cheers, Jeff

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

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

le, 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

y, in case the hash join 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

2 operation in the number of updates. It will probably be easier to refactor the code than to quantify just how much damage it does. cheers, Jeff

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

letion of the vacuum? Or look in pg_stat_user_tables to see when last_vacuum was. If it runs every night and succeeds, it is hard to see why wraparound would ever kick in. Unless you are hitting 150,000,000 transactions in a day. Cheers, Jeff

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

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

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

rogressively 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

Surprisingly enough, a hash anti-join doesn't automatically 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

on't know how busy. A single complex query can easily hold several dozens locks. Cheers, Jeff

Re: [GENERAL] Transaction apply speed on the standby

both CPU and of IO. If your IO is a big RAID system, the primary could keep multiple spindles active simultaneously by having multiple connections waiting on different pieces of data independently, while replay will wait on them serially. There is currently not a prefetch mechanism for replay. Cheers, Jeff

Re: [GENERAL] Why autvacuum is not started?

as yet to be finished, and certainly won't be back-ported to 9.4. Cheers, Jeff

Re: [GENERAL] Plpgsql - Custom fields Postgres 9.5

ate 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

o once it is done you don't see the light anymore. If you do a clean shutdown (kill -15 of the postgres master) this fast checkpoint happens upon shutdown. If you do an abort (kill -9) then the fast checkpoint happens upon start-up, once recovery is finished but before the database is opened of regular use. Cheers, Jeff

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

to be 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

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

gument, 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"

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

ints? Are you reindexing 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"

ot very safe as the pg_receivexlog could stop working and your database would happy run along without protection. Also, it is hard to be sure you are reliably issuing an fsyncs over NFS, so with archive_command over NFS there is always the risk your WAL data is not actually reaching 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 wrote: > > > 2016-11-19 22:12 GMT+01:00 Jeff Janes : > >> I need "strict" MIN and MAX aggregate functions, meaning they return NULL >> upon any NULL input, and behave like the built-in aggregates if none of the &g

[GENERAL] Strict min and max aggregate functions

SELECT CASE WHEN $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

s 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

ing? 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

ld 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

  1   2   3   4   5   6   7   8   9   10   >