Re: [GENERAL] recordings of pgconf us 2016

2016-05-31 Thread Jim Mlodgenski 
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

2016-02-29 Thread Jim Mlodgenski 
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?

2016-01-19 Thread Jim Mlodgenski 
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

2016-01-12 Thread Jim Mlodgenski 
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

2015-05-13 Thread Jim Mlodgenski 
On Wed, May 13, 2015 at 9:24 AM, Lukas Lehner webleh...@gmail.com 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] PGConf NYC 2015 videos

2015-05-13 Thread Jim Mlodgenski 
On Wed, May 13, 2015 at 9:43 AM, Yves Dorfsman y...@zioup.com 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] debugging with gdb in postgres

2014-07-08 Thread Jim Mlodgenski 
On Tue, Jul 8, 2014 at 12:40 PM, Ravi Kiran ravi.kolanp...@gmail.com
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

2014-01-24 Thread Jim Mlodgenski 
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

2010-01-27 Thread Jim Mlodgenski 
On Wed, Jan 27, 2010 at 3:59 PM, Mike Bresnahan
mike.bresna...@bestbuy.comwrote:

 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.000.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] Amazon EC2 CPU Utilization

2010-01-27 Thread Jim Mlodgenski 
On Wed, Jan 27, 2010 at 6:37 PM, Mike Bresnahan
mike.bresna...@bestbuy.comwrote:

 Greg Smith greg at 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] Swapped download links for 8.4.1 zip binaries

2009-09-09 Thread Jim Mlodgenski 
On Wed, Sep 9, 2009 at 11:01 AM, Thomas Kellerer spam_ea...@gmx.net 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

2009-06-15 Thread Jim Mlodgenski 
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] horizontal sharding

2009-06-15 Thread Jim Mlodgenski 
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)