Re: [GENERAL] recordings of pgconf us 2016
On Sun, May 29, 2016 at 12:36 AM, Johannes wrote: > I guess I have seen all video recording from pgconf us 2015 at youtube. > Are there any recording from this year available? > We are still waiting to have them edited by the video company. Hopefully it will be soon.
Re: [GENERAL] multicolumn index and setting effective_cache_size using human-readable-numbers
On Mon, Feb 29, 2016 at 8:56 AM, Geoff Winkless wrote: > I'm sure I'm missing something here. > > A query takes 50 seconds; it's doing a seq-scan on a joined table, > even though the table is joined via a field that's the leftmost column > in a multicolumn index > (http://www.postgresql.org/docs/9.5/static/indexes-multicolumn.html > says "equality constraints on leading columns ... will be used to > limit the portion of the index that is scanned") > > http://explain.depesz.com/s/suv > > If I create an individual index on just the linked key, the explain > shows the index being used and the query takes 1.7s. > > http://explain.depesz.com/s/b9ZS > > Now here's the odd bit: > > SET effective_cache_size TO '2146435072' > > causes the index to be used. > >SET effective_cache_size TO '2047MB' > > causes it to use tablescan. Shouldn't those two be equivalent? No they are not the same. When you don't include a unit for effective_cache_size, it defaults to page size so you're saying 2146435072 * 8K > Is > there a blowup in the planner checking effective_cache_size value not > expecting the human-readable value? > > Thanks for suggestions > > Geoff > > > -- > 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] How can I use a dynamic column name after NEW. or OLD. in trigger procedure?
On Tue, Jan 19, 2016 at 5:05 PM, Peter Devoy wrote: > As part of the extension I am writing I am trying to create a trigger > procedure in which the value of the primary key of the NEW or OLD row > is used. The trigger will be fired by arbitrary tables so the column > name must be dynamic. Something like: > > pk_column := 'foo_id'; --example assignment only > One way to define the pk_column for each table is to define it as a parameter on the CREATE TRIGGER on each table. You can then use that inside of the trigger function. CREATE TRIGGER foo_trigger BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE test_trigger('foo_id'); > > EXECUTE 'INSERT INTO bar (baz) VALUES ($1)' > USING NEW.quote_literal(pk_column); > > NEW is really just a ROW structure so you can turn it into JSON and dynamically pull out the values however you wish. CREATE OR REPLACE FUNCTION test_trigger() RETURNS TRIGGER AS $$ DECLARE pk_column VARCHAR; pk_valINT; BEGIN pk_column := TG_ARGV[0]; pk_val := row_to_json(NEW)->>pk_column; INSERT INTO bar (baz) VALUES (pk_val); RETURN NEW; END; $$ LANGUAGE plpgsql; > Out of desperation I have pretty much brute forced many weird > combinations of quote_literal, quote_ident, ::regclass, || and USING. > Unfortunately, I have not been able to get anything to work so any > help would be very much appreciated. > > Thanks for reading > > > Peter Devoy > > > -- > 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] WIP: CoC V4
On Tue, Jan 12, 2016 at 11:50 AM, Joshua D. Drake wrote: > Tl;dr; > > * Removed specific examples and reworded #3 to be more concise > > PostgreSQL Global Development Group (PGDG) Code of Conduct (CoC): > > 1. The CoC is to provide community guidelines for creating and enforcing a > safe, respectful, productive, and collaborative place for any person who is > willing to contribute in a safe, respectful, productive and collaborative > way. > > 2. The CoC is not about being offended. As with any diverse community, > anyone can get offended at anything. > Maybe add a little to #2. "Assume positive intent from your fellow community member." > > 3. A safe, respectful, productive and collaborative environment is free of > personal attacks and disparaging remarks of any kind. > > 4. Any sustained disruption of the collaborative space (mailing lists, IRC > etc..) or other PostgreSQL events shall be construed as a violation of the > CoC and appropriate action will be taken by the CoC committee. > > 5. The CoC is only about interaction with the PostgreSQL community. Your > private and public lives outside of the PostgreSQL community are your own. > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > Announcing "I'm offended" is basically telling the world you can't > control your own emotions, so everyone else should do it for you. > > > -- > 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] PGConf NYC 2015 videos
On Wed, May 13, 2015 at 9:43 AM, Yves Dorfsman wrote: > On 2015-05-13 07:37, Jim Mlodgenski wrote: > > > > Yes, the talks were recorded. There was a lot of content so we are still > > working on getting everything together. We hope to have them up soon. > > > > Will you be posting on this list when they are ready? > > We were not planning on posting to -general because to me it seems off topic, but if no one objects we can. We were planning on announcing it through the normal social media channels for the conference like the @PGConfUS Twitter handle.
Re: [GENERAL] PGConf NYC 2015 videos
On Wed, May 13, 2015 at 9:24 AM, Lukas Lehner wrote: > Hi > > Where can I download PGConf NYC 2015 videos? Was there a recording? > > Yes, the talks were recorded. There was a lot of content so we are still working on getting everything together. We hope to have them up soon. > I live in Europe. Please point me to torrent link. > > There are lots of interesting talks. > > Lukas >
Re: [GENERAL] debugging with gdb in postgres
On Tue, Jul 8, 2014 at 12:40 PM, Ravi Kiran wrote: > hi, > > I am trying to learn how postgresql implements the join algorithms. > > So I am trying to learn about the source code of the executor precisely > the file nodenestloop.c . > > In the executor file I have nodenestloop.o but no binary executor file. > > I am using helios eclipse to edit the source code. > > I want to use gdb for debugging that file, but for that gdb needs a > executor file. > > I want to Debug that file using gdb and attach the postgress process file. > > Could anyone help me with how to debug that file using gdb and attach a > process, I know that we can find out about the pid of the process by using > this command in psql, *select pg_backend_pid* , but how exactly are we > going to give this pid to gdb. > The wiki page may help https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD > any help or related links wwould be grateful. > > Thank you >
Re: [GENERAL] postgres-fdw questions
On Fri, Jan 24, 2014 at 4:20 AM, Emmanuel Medernach < meder...@clermont.in2p3.fr> wrote: > Hello, > > I'm currently testing postgres_fdw feature on PostgreSQL 9.3.2 and I have > some questions: > > - What are the limits to the number of foreign tables ? > - What is the current status about foreign joins push-down ? > The Custom Scan API patch adds the ability to push down joins to foreign tables, but its still pending https://commitfest.postgresql.org/action/patch_view?id=1282 > > Thanks in advance, > -- > > > -- > 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] Amazon EC2 CPU Utilization
On Wed, Jan 27, 2010 at 6:37 PM, Mike Bresnahan wrote: > Greg Smith 2ndquadrant.com> writes: > > Could you try this again with "top -c", which will label these > > postmaster processes usefully, and include the pgbench client itself in > > what you post? It's hard to sort out what's going on in these > > situations without that style of breakdown. > > As a further experiment, I ran 8 pgbench processes in parallel. The result > is > about the same. > > Let's start from the beginning. Have you tuned your postgresql.conf file? What do you have shared_buffers set to? That would have the biggest effect on a test like this. > top - 18:34:15 up 17 min, 2 users, load average: 0.39, 0.40, 0.36 > Tasks: 217 total, 8 running, 209 sleeping, 0 stopped, 0 zombie > Cpu(s): 22.2%us, 8.9%sy, 0.0%ni, 68.7%id, 0.0%wa, 0.0%hi, 0.0%si, > 0.3%st > Mem: 7358492k total, 1611148k used, 5747344k free,11416k buffers > Swap:0k total,0k used,0k free, 1248408k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > > > 1506 postgres 20 0 197m 134m 132m S 29.4 1.9 0:09.27 postgres: > postgres > postgres [local] idle > > 1524 postgres 20 0 197m 134m 132m R 29.4 1.9 0:05.13 postgres: > postgres > postgres [local] idle > > 1509 postgres 20 0 197m 134m 132m R 27.1 1.9 0:08.58 postgres: > postgres > postgres [local] SELECT > > 1521 postgres 20 0 197m 134m 132m R 26.4 1.9 0:05.77 postgres: > postgres > postgres [local] SELECT > > 1512 postgres 20 0 197m 134m 132m S 26.1 1.9 0:07.62 postgres: > postgres > postgres [local] idle > > 1520 postgres 20 0 197m 134m 132m R 25.8 1.9 0:05.31 postgres: > postgres > postgres [local] idle > > 1515 postgres 20 0 197m 134m 132m S 23.8 1.9 0:06.94 postgres: > postgres > postgres [local] SELECT > > 1527 postgres 20 0 197m 134m 132m S 21.8 1.9 0:04.46 postgres: > postgres > postgres [local] SELECT > > 1517 postgres 20 0 49808 2012 1544 R 5.3 0.0 0:01.02 pgbench -S -c > 1 -T > 30 > > 1507 postgres 20 0 49808 2012 1544 R 4.6 0.0 0:01.70 pgbench -S -c > 1 -T > 30 > > 1510 postgres 20 0 49808 2008 1544 S 4.3 0.0 0:01.32 pgbench -S -c > 1 -T > 30 > > 1525 postgres 20 0 49808 2012 1544 S 4.3 0.0 0:00.79 pgbench -S -c > 1 -T > 30 > > 1516 postgres 20 0 49808 2016 1544 S 4.0 0.0 0:01.00 pgbench -S -c > 1 -T > 30 > > 1504 postgres 20 0 49808 2012 1544 R 3.3 0.0 0:01.81 pgbench -S -c > 1 -T > 30 > > 1513 postgres 20 0 49808 2016 1544 S 3.0 0.0 0:01.07 pgbench -S -c > 1 -T > 30 > > 1522 postgres 20 0 49808 2012 1544 S 3.0 0.0 0:00.86 pgbench -S -c > 1 -T > 30 > > 1209 postgres 20 0 63148 1476 476 S 0.3 0.0 0:00.11 postgres: > stats > collector process > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] Amazon EC2 CPU Utilization
On Wed, Jan 27, 2010 at 3:59 PM, Mike Bresnahan wrote: > I have deployed PostgresSQL 8.4.1 on a Fedora 9 c1.xlarge (8x1 cores) > instance > in the Amazon E2 Cloud. When I run pgbench in read-only mode (-S) on a > small > database, I am unable to peg the CPUs no matter how many clients I throw at > it. > In fact, the CPU utilization never drops below 60% idle. I also tried this > on > Fedora 12 (kernel 2.6.31) and got the same basic result. What's going on > here? > Am I really only utilizing 40% of the CPUs? Is this to be expected on > virtual > (xen) instances? > > I have seen behavior like this in the past on EC2. I believe your bottleneck may be pulling the data out of cache. I benchmarked this a while back and found that memory speeds are not much faster than disk speeds on EC2. I am not sure if that is true of Xen in general or if its just limited to the cloud. > [r...@domu-12-31-39-0c-88-c1 ~]# uname -a > Linux domU-12-31-39-0C-88-C1 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 > 17:48:28 EST 2009 x86_64 x86_64 x86_64 GNU/Linux > > -bash-4.0# pgbench -S -c 16 -T 30 -h domU-12-31-39-0C-88-C1 -U postgres > Password: > starting vacuum...end. > transaction type: SELECT only > scaling factor: 64 > query mode: simple > number of clients: 16 > duration: 30 s > number of transactions actually processed: 590508 > tps = 19663.841772 (including connections establishing) > tps = 19710.041020 (excluding connections establishing) > > top - 15:55:05 up 1:33, 2 users, load average: 2.44, 0.98, 0.44 > Tasks: 123 total, 11 running, 112 sleeping, 0 stopped, 0 zombie > Cpu(s): 18.9%us, 8.8%sy, 0.0%ni, 70.6%id, 0.0%wa, 0.0%hi, 1.7%si, > 0.0%st > Mem: 7348132k total, 1886912k used, 5461220k free,34432k buffers > Swap:0k total,0k used,0k free, 1456472k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > > > 2834 postgres 15 0 191m 72m 70m S 16 1.0 0:00.66 postmaster > > > 2838 postgres 15 0 191m 66m 64m R 15 0.9 0:00.62 postmaster > > > 2847 postgres 15 0 191m 70m 68m S 15 1.0 0:00.59 postmaster > > > 2837 postgres 15 0 191m 72m 70m S 14 1.0 0:00.47 postmaster > > > 2842 postgres 15 0 191m 66m 64m R 14 0.9 0:00.48 postmaster > > > 2835 postgres 15 0 191m 69m 67m S 14 1.0 0:00.54 postmaster > > > 2839 postgres 15 0 191m 69m 67m R 14 1.0 0:00.60 postmaster > > > 2840 postgres 15 0 191m 68m 67m R 14 1.0 0:00.58 postmaster > > > 2833 postgres 15 0 191m 68m 66m R 14 1.0 0:00.50 postmaster > > > 2845 postgres 15 0 191m 70m 68m R 14 1.0 0:00.50 postmaster > > > 2846 postgres 15 0 191m 67m 65m R 14 0.9 0:00.51 postmaster > > > 2836 postgres 15 0 191m 66m 64m S 12 0.9 0:00.43 postmaster > > > 2844 postgres 15 0 191m 68m 66m R 11 1.0 0:00.40 postmaster > > > 2841 postgres 15 0 191m 65m 64m R 11 0.9 0:00.43 postmaster > > > 2832 postgres 15 0 191m 67m 65m S 10 0.9 0:00.38 postmaster > > > 2843 postgres 15 0 191m 67m 66m S 10 0.9 0:00.43 postmaster > > > > [r...@domu-12-31-39-0c-88-c1 ~]# iostat -d 2 -x > Linux 2.6.21.7-2.ec2.v1.2.fc8xen (domU-12-31-39-0C-88-C1) 01/27/10 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda1 0.5715.011.323.5634.39 148.5737.52 > 0.28 57.35 3.05 1.49 > sdb1 0.03 112.385.50 12.1187.98 995.9161.57 > 1.88 106.61 2.23 3.93 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda1 0.00 0.000.001.79 0.0028.5716.00 > 0.002.00 1.50 0.27 > sdb1 0.00 4.460.00 14.29 0.00 150.0010.50 > 0.37 26.00 2.56 3.66 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda1 0.00 0.000.000.00 0.00 0.00 0.00 > 0.000.00 0.00 0.00 > sdb1 0.00 0.000.000.00 0.00 0.00 0.00 > 0.000.00 0.00 0.00 > > Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz > avgqu-sz await svctm %util > sda1 0.00 3.570.00 0.79 0.0034.9244.00 > 0.003.00 3.00 0.24 > sdb1 0.00 0.000.000.00 0.00 0.00 0.00 > 0.000.00 0.00 0.00 > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] Swapped download links for 8.4.1 zip binaries
On Wed, Sep 9, 2009 at 11:01 AM, Thomas Kellerer wrote: > Hi, > > if someone from EnterpriseDB is "listening": > > The links for Windows and OSX binaries at > http://www.enterprisedb.com/products/pgbindownload.do > > are interchanged. > When clicking on the Windows icon, you'll get the Mac binaries, when > clicking on the Mac icon, you'll get the windows binaries :) > > This is now fixed. > Regards > Thomas > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] horizontal sharding
what about queries that need to do joins or aggregate reporting across the partitions?!? I can't see how that could be done transparently short of something like Oracle RAC. GridSQL actually does a nice job of breaking up the query and optimizing it appropriately to handle cross node joins. There are agents running on each of the nodes that handles the inter-node communication allowing it to be transparent to the calling application. -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] horizontal sharding
what is a good way to horizontal shard in postgresql > 1. pgpool 2 > 2. gridsql > > which is a better way to use sharding > Both are good methods of sharding, but it depends on your goals. GridSQL is better in reporting applications where as PG Pool2 is better in transactional situations. > > also is it possible to paritition without changing client code Yes, but it depends on the SQL in your client code. If you are just using simple SQL with no stored functions, you should be able to slip in either solution without changing the client code. -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] What's up with EnterpriseDB?
Title: Fwd: [GENERAL] What's up with EnterpriseDB? Once the flood gates of new features are opened up for 8.2, we'd be happy to submit a patch for anonymous blocks. Also, if there are other features we've added that makes since to roll back, let us know and we could submit that as well. Thanks, Jim --------- Jim Mlodgenski Sr. Architect http://www.enterprisedb.com From: David Fetter <[EMAIL PROTECTED]>Date: Aug 10, 2005 5:54 PMSubject: Re: [GENERAL] What's up with EnterpriseDB?To: "Joshua D. Drake" <[EMAIL PROTECTED]>Cc: Tony Caduto <[EMAIL PROTECTED]>, pgsql-general@postgresql.orgOn Wed, Aug 10, 2005 at 03:42:59PM -0700, Joshua D. Drake wrote:> Tony Caduto wrote:> >I saw their announment today and they said they have Oracle> >compatiblility.> >> >What exactly does this mean? They don't go into any details. Did> >they implement a Oracle compatible proc language or something?>> I don't know the full details but my understanding is they> implemented things like IN/OUT parameters and a superset of pl/SQL.I spoke with them today at LWE/SF. They have a subset of PL/SQL (nopackages yet, but they're working on it for A Really Big Client) andare able to do in-line anonymous PL/SQL blocks. I'm hoping we cantalk them into helping us do in-line anonymous PL/Foo based on whatthey've done for this. :)Cheers,D--David Fetter [EMAIL PROTECTED] http://fetter.org/phone: +1 510 893 6100 mobile: +1 415 235 3778Remember to vote!---(end of broadcast)---TIP 6: explain analyze is your friend