Re: [GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Hi, So I'd call this an oracle_fdw bug. It needs to postpone what it's doing here to the first normal FDW function call in a session. Thanks a lot for looking so quickly into this! I've opened an issue with oracle_fdw: https://github.com/laurenz/oracle_fdw/issues/215 Thanks, Chris

[GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

ps! Below is the back trace from gdb from each of the cores. Let me know if there's anything else I can do or check! Thanks :) Chris. [centos@asia data]$ ls -l core* -rw---. 1 centos centos 152059904 Nov 19 17:43 core.30430 -rw---. 1 centos centos 152059904 Nov 19 17:43 core.30431 -rw

Re: [GENERAL] Build in function to verify email addresses

I'm pretty sure this isn't as simple as you think it is, I'd suggest having a good read of: https://stackoverflow.com/a/201378/216229 Chris On 16/11/2017 07:56, Nick Dro wrote: I beleieve that every information system has the needs to send emails. Currently PostgreSQL doesn't have

[GENERAL] Fwd: standby stop replicating, then picked back up

Forwarded Message Subject:standby stop replicating, then picked back up Date: Tue, 7 Nov 2017 13:04:18 -0700 From: chris kim <chr...@propaas.com> To: pgsql-in-gene...@postgresql.org <pgsql-in-gene...@postgresql.org> Hello, I had a standby hang

[GENERAL] Can't build ODBC -- odbc_config not found

I just tried to build Postgres ODBC from source (psqlodbc-10.00..tar.gz) I type "./configure" Then get this message: configure: error: odbc_config not found (required for unixODBC build) So it must be looking for a file called "odic_config"? It's not 100% clear what is

Re: [GENERAL] Joining 16 tables seems slow

your query time right there and then you have the overhead in the joins on top of that. Quick eyeball estimates is that this is where approx 200ms of your query time comes from. Looking at this in more detail it doesn't look This is not a problem with too many tables in the join but the fact that you are joining the same tables in multiple times in ways you end up needing to repeatedly sequentially scan them. I also don't think an index is going to help unless you have accounting data going way back (since you are looking for about a year's worth of data) or unless 90% of your transactions get marked as deleted. So I think you are stuck with the sequential scans on this table and optimizing will probably mean reducing the number of times you scan that table. > > Frank > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

[GENERAL] pgxn manager down

). If folks are working on this, is there an ETA on a fix? Is there anything I can do to help? -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] bidirectional mapping?

riage m on p1 = any(marriage.parties) join people p2 on p2 = any(marriage.parties) and p2.id <> p1.id > > > -- > Veni, Vidi, VISA: I came, I saw, I did a little shopping. > > Maranatha! <>< > John McKown > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] vacuum on streaming replication

your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

[GENERAL] DATA Integrity & Recovery

this loss. I would appreciate the communities help in the following: 1. Determine if data from the incremental backups can be restored or recovered. 2. Determine if data can be recovered from individual files backed up from main Postgres data directory. Thanks in advance Chris

Re: [GENERAL] Strange case of database bloat

On Wed, Jul 5, 2017 at 3:51 PM, Bill Moran <wmo...@potentialtech.com> wrote: > On Wed, 5 Jul 2017 13:28:29 +0200 > Chris Travers <chris.trav...@gmail.com> wrote: > > > On Wed, Jul 5, 2017 at 1:00 PM, PT <wmo...@potentialtech.com> wrote: > > > > > 2x

Re: [GENERAL] Strange case of database bloat

speculation until you know how frequently autovacuum runs on > that table and how long it takes to do its work. > Given the other time I have seen similar behaviour, the question in my mind is why free pages near the beginning of the table don't seem to be re-used. I would like to try to verify that

Re: [GENERAL] Imperative Query Languages

, there are other interpretations of the declarative that are not at all equivalent. The hoops we have to jump through to make this work in an imperative way in SQL are sometimes rather amusing. > > Kind Regards, > Jason > -- Best Wishes, Chris Travers Efficito: Hosted A

Re: [GENERAL] Strange case of database bloat

On Wed, Jul 5, 2017 at 7:18 AM, Chris Travers <chris.trav...@gmail.com> wrote: > Hi; > > First, I haven't seen major problems of database bloat in a long time > which is why I find this case strange. I wanted to ask here what may be > causing it. > > Probl

Re: [GENERAL] Imperative Query Languages

Nope. You have chained generators and you really need to watch what is parallelizable and what is not, and what is running on the partitions and what is running post-gathering/shuffling. Spark has no real facility for parallelising a comprehension. > > Kind Regards, > Jason > &g

Re: [GENERAL] Imperative Query Languages

there it is extremely important to understand the imperative side of the data flow in that case (what is partitioned and what is not). -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

[GENERAL] Strange case of database bloat

which is the problem here? But why doesn't Postgres re-use any of the empty disk pages? More importantly, is there anything that can be done to mitigate this issue other than a frequent vacuum full? -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible

Re: [GENERAL] Is the row version available in SQL?

ad > > … and if that gets 0 rows, it can handle the conflict. > For that, you could use xmin. That tracks the transaction where the row first became visible. Best Wishes, Chris Travers > > Rob > > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] Unable to understand index only scan as it is not happening for one table while it happens for other

> View this message in context: http://www.postgresql-archive. > org/Unable-to-understand-index-only-scan-as-it-is-not- > happening-for-one-table-while-it-happens-for-other-tp5968835.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent vi

Re: [GENERAL] Oracle to PostgreSQL Migration.

r data only in one table. Did you notice the "Out of memory!" you got there? I guess that's the problem now... Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

p by step. I guess you are running this on some test data, so the file will be small enough to open it with an editor. You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever you're using). You can then see at what point you get an error (and hopefully understand what's h

Re: [GENERAL] Oracle to PostgreSQL Migration.

ake sure that in ora2pg.conf you put the lines: ORACLE_DSN dbi:Oracle:host=myhost;sid=mysid ORACLE_USER myuser ORACLE_PWD mypass Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pg

Re: [GENERAL] Migration Query

and have used it successfully in the past: http://ora2pg.darold.net/ Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

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

you should increase shared_memory to 40GB. General philosphy is to allocate 80% of system memory to shared_memory Uhm... 80% is too much, likely: https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general

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

Sorry, my mistake (I'm a bit nervous...) that's not work_mem, but shared_buffers Hi. The resident set size of the worker processes includes all shared memory blocks they touched. So it's not that each of those workers allocated their own 3GB... (in Linux at least) Bye, Chris

Re: [GENERAL] keeping WAL after dropping replication slots

Postgres version? 9.6.1 Have you considered upgrading to 9.6.2? There were some fixes, including WAL related: https://www.postgresql.org/docs/9.6/static/release-9-6-2.html Not exactly regarding what you see, though... Bye, Chris. -- Sent via pgsql-general mailing

Re: [GENERAL] expensive function in select list vs limit clause

, Tom Lane just pointed out that 9.6 is able to optimise this (at least the synthetic example). Anyway, my real problem could be beautifully improved by subselect-trick! Thanks a lot! Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] expensive function in select list vs limit clause

https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=9118d03a8 Hi, thanks! I've just tested with 9.6 and the test runs fast with or without expensive(). So the above patch does indeed improve this case a lot! Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general

[GENERAL] expensive function in select list vs limit clause

is right and there must be a reason for this :) Could someone help me understand this behaviour? Thanks & Bye, Chris. -- *** select version(); -- setup: create a time wasting function and a table with 1M rows cr

Re: [GENERAL] options for logical replication plugins?

On 15/03/2017 19:18, Andres Freund wrote: Hi, On 2017-03-15 18:29:06 +, Chris Withers wrote: Shame the decoding has to be done on the server-side rather than the client side. Why? Requiring compiled extensions to be installed on the server is always going to be a pain, especially

[GENERAL] options for logical replication plugins?

ide rather than the client side. Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] json aggregation question

et of|tag1|value that have a|tag2|value of|t2val1|? The closes I can get is: |#selectcount(*),json_agg(tags)fromthing wheretags->'tag2'?'t2val1';count |json_agg ---+--2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)| ...but I really want: |count |tag1 ---+-2|["val1","val2","val3"](1row)| cheers, Chris

Re: [GENERAL] json aggregation question

Thanks, this is closer, but regex really scares me for something like this... On 28/02/2017 17:19, Yasin Sari wrote: Hi Chris, Maybe there is an another better solution; 1. sending values into jsonb_array_elements to getting elements (lateral join) 2. distinct to eliminate duplicates 3

[GENERAL] json aggregation question

et of|tag1|value that have a|tag2|value of|t2val1|? The closes I can get is: |#selectcount(*),json_agg(tags)fromthing wheretags->'tag2'?'t2val1';count |json_agg ---+--2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)| ...but I really want: |count |tag1 ---+-2|["val1","val2","val3"](1row)| cheers, Chris

[GENERAL] Recorded PostgreSQL at 10TB and Beyond

discussed here on Feb 28- Mar 1 in Malmo. Those in the area who are interested can book online at https://edument.se/education/categories/sql/advanced-postgresql/book -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com

[GENERAL] Free seminar in Malmo: PostgreSQL at 10TB and Beyond

are interested and in the Southern Sweden or Copenhagen areas, please feel free to register at https://www.eventbrite.com/e/postgresql-at-10-tb-and-beyond-tickets-30841174784 -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

wo different queries. There's also a user_name = 'dd' that has become a user_name = 'rdoyleda' ... Ravi, could you please send the current query you're testing and the explain analyze of that query on 9.5 and 9.6? Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

=1177.25..1177.26 rows=1 width=0) [...] I'm not seeing the "(actual ... )" part here. THe plan you show is from an explain, not an explain analyze... Can you provide the explain analyze output? Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

did NOT yet show the complete actual query + plan. All we saw is the explain analyze of the call to the procedure function_cloud_view_orari(), but we don't know what's happening inside the procedure. Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] default representation of null in psql

On 12/12/2016 14:33, Adrian Klaver wrote: On 12/11/2016 11:34 PM, Chris Withers wrote: On 01/12/2016 12:12, Francisco Olarte wrote: On Thu, Dec 1, 2016 at 12:56 PM, Chris Withers <ch...@simplistix.co.uk> wrote: So, first observation: if I make room nullable, the exclude constrain

Re: [GENERAL] Performance PLV8 vs PLPGSQL

My recommendation. See them as tools in a toolkit, not a question of what is best. For places where you have SQL statements as primary do SQL or PLPGSQL functions. For places where you are manipulating values (parsing strings for example) use something else (I usually use pl/perl for string

[GENERAL] Securing Information

. Thank you, Chris

[GENERAL] explain analyze showed improved results without changes, why?

. The indices and two explains follow below. Thanks in advance for the help. Cheers, Chris Indexes: "blocks_pkey" PRIMARY KEY, btree (cloudidx, blkid) "blocks_blkid_idx" btree (blkid) "blocks_cloudidx_idx" btree (cloudidx) "blocks_off_sz_idx"

Re: [GENERAL] Appending to multidimentional array.

> Result should be : >Array_1 = {{1,2,3,4,5}}; > Forgot to reply all (in case someone searches the archives later): Array_1 :== Array_1 || Array[Array_2] > > > > -- > Regards : > Venktesh Guttedar. > > -- Best Wishes, Chris Travers Efficito: Hosted

Re: [GENERAL] Appending to multidimentional array.

ray_1 = [][]; > Array_2 = '{1,2,3,4,5}'; > > Result should be : >Array_1 = {{1,2,3,4,5}}; > > > -- > Regards : > Venktesh Guttedar. > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] schema advice for event stream with tagging and filtering

row) # SELECT '[{"tag1":"v1"}, {"tag1": "v2"}, {"tag1": "v3"}]'::jsonb @> '[{"tag1": "v1"}]'::jsonb; ?column? -- t (1 row) So, should I go for a tag name that maps to a list of values for that tag, or should

Re: [GENERAL] btree gist indices, null and open-ended tsranges

On 01/12/2016 12:12, Francisco Olarte wrote: On Thu, Dec 1, 2016 at 12:56 PM, Chris Withers <ch...@simplistix.co.uk> wrote: So, first observation: if I make room nullable, the exclude constraint does not apply for rows that have a room of null. I guess that's to be expected, right? I

[GENERAL] Out of memory error

inginfo.c:268 07:56:20 EST LOG: 0: disconnection: session time: 0:01:59.960 user=xxx database=xx host=xxx.xxx.xxx.xxx port=57736 What is happening here? Is it of concern? Will tuning the DB memory parameters help avoid this? Thanks, Chris

Re: [GENERAL] Looking for an online mentor

sten > -- > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailp

Re: [GENERAL] PDF files: to store in database or not

esql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] pgbasebackup necessary for master slave change?

-- > > Achilleas Mantzios > > IT DEV Lead > > IT DEPT > > Dynacom Tankers Mgmt > > > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://w

Re: [GENERAL] pgbasebackup necessary for master slave change?

postgresql.org/docs/9.5/static/app-pgrewind.html > > > > Subhankar Chattopadhyay > Bangalore, India > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-

Re: [GENERAL] When to use COMMENT vs --

On Dec 7, 2016 5:07 PM, "Karsten Hilbert" wrote: > > On Wed, Dec 07, 2016 at 07:57:54AM -0800, Rich Shepard wrote: > > > I have used '-- ' to enter comments about tables or columns and am curious > > about the value of storing comments in tables using the COMMENT key

Re: [GENERAL] high transaction rate

I prevent contention? > > This is pgdg postgres 9.5 > 1262 is 'pg_database'::regclass::oid I don't know for sure but things I would worry about given the performance profile are: 1. NUMA swap insanity 2. Accumulation of dead tuples leading to what should be very short operations taking longer. No idea of that is helpful but where I would probably start > Thanks, > Torsten > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

[GENERAL] btree gist indices, null and open-ended tsranges

he btree gist index backing the exclude constraint? cheers, Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

mas-guettler.de/ > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

Hey Tom, I appreciate you're busy, but did you ever get a chance to look at this? On 19/09/2016 08:40, Chris Withers wrote: On 16/09/2016 15:29, Tom Lane wrote: Chris Withers <ch...@simplistix.co.uk> writes: On 16/09/2016 14:54, Igor Neyman wrote: So, what is the value for "en

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

Hey Tom, I appreciate you're busy, but did you ever get a chance to look at this? On 19/09/2016 08:40, Chris Withers wrote: On 16/09/2016 15:29, Tom Lane wrote: Chris Withers <ch...@simplistix.co.uk> writes: On 16/09/2016 14:54, Igor Neyman wrote: So, what is the value for "en

Re: [GENERAL] dumping table contents in a sensible order

wer from StackOverflow without full understanding the details... Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dumping table contents in a sensible order

order? - have the load only apply the foreign key constraint at the end of each table import? cheers, Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High load average every 105 minutes

down until the credits recover. I could imagine that this way some cyclic load patterns emerge, if there is constant load on the machines. Nhan, what instance types are you running? Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] High load average every 105 minutes

down until the credits recover. I could imagine that this way some cyclic load patterns emerge, if there is constant load on the machines. Nhan, what instance types are you running? Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] High load average every 105 minutes

down until the credits recover. I could imagine that this way some cyclic load patterns emerge, if there is constant load on the machines. Nhan, what instance types are you running? Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Best way to return Random rows from a table with non-repeatability of rows

use it. Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

I have another application that consumes all of the huge pages; they aren't for pgsql. :) I've modified the configuration file from "try" to "off" and munmap is no more. Mischief managed. Thanks for your help. On Tue, Oct 11, 2016 at 6:15 PM, Tom Lane <t...@sss.pgh

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

]@[unknown] LOG: incomplete startup packet On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Chris Richards <ch...@infinite.io> writes: > > Setting up postgresql-9.5 (9.5.4-1.pgdg14.04+2) ... > > Creating new cluster 9.5/main ... > >

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

:1048576 kB DirectMap4k: 83776 kB DirectMap2M: 4110336 kB DirectMap1G: 6291456 kB On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Chris Richards <ch...@infinite.io> writes: > > Setting up postgresql-9.5 (9.5.4-1.pgdg14.04+2) ... > > Cr

Re: [GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf Cheers, Chris On Mon, Oct 10, 2016 at 5:21 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Chris Richards <ch...@infinite.io> writes: > > Sett

[GENERAL] LOG: munmap(0x7fff80000000) failed: Invalid argument

rver. Thanks, Chris

Re: [GENERAL] Query killed with Out of memory

unless you have a small value for max_connection... - 8.4.8 was released in 2011, the latest 8.4 release is 8.4.22, you'r missing lots of patches (and 8.4 was EOLed more than two years ago) Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On 16/09/2016 15:29, Tom Lane wrote: Chris Withers <ch...@simplistix.co.uk> writes: On 16/09/2016 14:54, Igor Neyman wrote: So, what is the value for "end ts", when the record is inserted (the range just started)? It's open ended, so the period is [start_ts, ) I've not lo

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

of the indexed fields, or just "value" ? Yeah, it's a temporal table, so "updates" involve modifying the period column for a row to set its end ts, and then inserting a new row with a start ts running on from that. Of course, the adds are just inserting new rows. cheers,

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On 16/09/2016 12:00, John R Pierce wrote: On 9/16/2016 3:46 AM, Chris Withers wrote: when you do updates, are you changing any of the indexed fields, or just "value" ? Yeah, it's a temporal table, so "updates" involve modifying the period column for a row to set its end ts

[GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

y python code suggests that most of the time is being taken by Postgres (9.4 in this case...) What can I do to speed things up? Is there a different type of index I can use to achieve the same exclude constraint? Is there something I can do to have the index changes only done on the commit of

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On 16/09/2016 14:54, Igor Neyman wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Withers Sent: Friday, September 16, 2016 6:47 AM To: John R Pierce <pie...@hogranch.com>; pgsql-general@postgres

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

On 16/09/2016 12:00, John R Pierce wrote: On 9/16/2016 3:46 AM, Chris Withers wrote: when you do updates, are you changing any of the indexed fields, or just "value" ? Yeah, it's a temporal table, so "updates" involve modifying the period column for a row to set its end ts

Re: [GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

of the indexed fields, or just "value" ? Yeah, it's a temporal table, so "updates" involve modifying the period column for a row to set its end ts, and then inserting a new row with a start ts running on from that. Of course, the adds are just inserting new rows. cheers,

[GENERAL] performance problems with bulk inserts/updates on tsrange with gist-based exclude constrains

ofiling my python code suggests that most of the time is being taken by Postgres (9.4 in this case...) What can I do to speed things up? Is there a different type of index I can use to achieve the same exclude constraint? Is there something I can do to have the index changes only done on the comm

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

stgresql.org/docs/current/static/sql-insert.html > <https://www.postgresql.org/docs/current/static/sql-insert.html> has > more > info. > > > Thanks Chris! > > But the problem is that test2 table has 180 rows with different j_id and > I need to insert eac

Re: [GENERAL] postgres insert + select + values - Pgsql 9.5

On 16/09/16 07:45, Patrick B wrote: > Hi guys, > > > I got the test1 table with three columns: > > id(BIGINT) - j_id(BIGINT) - comments(CHARACTER VARYING) > > > *This needs to be done 180 times:* > > INSERT INTO test1 (id,j_id,comments) VALUES (default,123321,'test-1 > -

Re: [GENERAL] PG vs ElasticSearch for Logs

of a factor 4-5 (machine has 8 logical CPUs) and the whole table fits in cache. For a use case as this, the parallel query feature in 9.6 is so good it's almost like cheating ;) Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] schema advice for event stream with tagging and filtering

r this colo between time x and y, etc). I've deliberately tried to be abstract here as I'm trying to ask a question rather than proposing a solution that might have problems, if there's any more information that would help, please let me know! cheers, Chris -- Sent via pgsql-general mailing

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

On Tue, Aug 16, 2016 at 3:11 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Sun, Aug 14, 2016 at 5:58 AM, Chris Travers <chris.trav...@gmail.com> > wrote: > > >> > >> All this seems to be a huge change which will definitely not appear any > >>

Re: [GENERAL] schema advice for event stream with tagging and filtering

ou may load data from this table to denormalized olap table once a day and build index there to speed-up queries. What kind of index is recommended here? The kind of queries would be: - show me a list of tag types and the count of the number of events of that type - show me all events tha

[GENERAL] schema advice for event stream with tagging and filtering

r this colo between time x and y, etc). I've deliberately tried to be abstract here as I'm trying to ask a question rather than proposing a solution that might have problems, if there's any more information that would help, please let me know! cheers, Chris -- Sent via pgsql-general mailing

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Sun, Aug 14, 2016 at 3:42 PM, Karsten Hilbert <karsten.hilb...@gmx.net> wrote: > Hello Chris, > > I am getting closer but ... > > > > > Sure. What I prefer to do is to allow for a (cacheable) lookup on > the > > > > basis of some criteria, e

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

ght back at the same amount of overhead as a temporary function. > > On Sun, Aug 14, 2016 at 10:42 AM, Chris Travers <chris.trav...@gmail.com> > wrote: > >> If all you want is a temporary function, you *can* create it in the >> pg_temp namespace though that seems hack

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Sun, Aug 14, 2016 at 12:35 PM, Karsten Hilbert <karsten.hilb...@gmx.net> wrote: > On Fri, Aug 12, 2016 at 01:32:33PM +0200, Chris Travers wrote: > > >>> My preference is stored procedures plus service locators > >> > >> Would you care to elaborat

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

no luck with that feature in foreseeable > future :(. > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

Of course that differs depending on environment and requirements but it is a decent starting point. > > > > > -- > john r pierce, recycling bits in santa cruz > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscrip

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 11:32 AM, Karsten Hilbert <karsten.hilb...@gmx.net> wrote: > På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < > chris.trav...@gmail.com[chris.trav...@gmail.com]>: > > > My preference is stored procedures plus service locators > &g

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

n really handy. The same basic approach can be used to create a mapping layer generally. But again, if you are primarily worried about development time, then that is more important, usually, than information management as a whole. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 10:58 AM, Andreas Joseph Krogh <andr...@visena.com> wrote: > På fredag 12. august 2016 kl. 10:33:19, skrev Chris Travers < > chris.trav...@gmail.com>: > > > > >> >> Of course you *can* use them well. I remember talking about thi

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Fri, Aug 12, 2016 at 9:59 AM, Andreas Joseph Krogh <andr...@visena.com> wrote: > På fredag 12. august 2016 kl. 05:27:42, skrev Chris Travers < > chris.trav...@gmail.com>: > > > > On Thu, Aug 11, 2016 at 10:20 PM, Andreas Joseph Krogh <andr...@visena.com >

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

; -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more

Re: [GENERAL] connection file descriptors created with identical number after process fork on mac

On 04/08/2016 00:20, Tom Lane wrote: Chris Withers <ch...@simplistix.co.uk> writes: I'm writing some multi-process code in Python and trying to make sure I open a new connection for each process. Here's the really cut down code: ... What's really surpising to me is the output on

[GENERAL] connection file descriptors created with identical number after process fork on mac

ub.com/psycopg/psycopg2/blob/master/psycopg/connection_type.c#L898 Is there something I'm missing about file descriptors on Macs or is something bad happening here? Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 8:09 PM, Edson Richter <edsonrich...@hotmail.com> wrote: > Em 28/07/2016 13:07, Chris Travers escreveu: > > > > On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marl...@gmail.com> > wrote: > >> On Wed, Jul 27, 2016 at 9:51

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 6:32 PM, Alex Ignatov wrote: > > On 28.07.2016 18:41, Igor Neyman wrote: > >> -Original Message- >> From: Alex Ignatov [mailto:a.igna...@postgrespro.ru] >> Sent: Thursday, July 28, 2016 11:26 AM >> To: Igor Neyman

Re: [GENERAL] Uber migrated from Postgres to MySQL

this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)? > > As someone who has gotten more than one bug fix from pgsql in less > than 48 hours, I feel sorry for anyone who finds a bug in a MySQL > version they are running in production. &g

  1   2   3   4   5   6   7   8   9   10   >