Re: [GENERAL] PG replication across DataCenters

[Michael Paquier]

On Fri, Nov 22, 2013 at 11:46 PM, Albe Laurenz  wrote:
> Michael Paquier wrote:
>> On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan  
>> wrote:
>>> I am not sure i understand the difference between async and sync replication
>>> and on what scenarios i should use async or sync replication. Does it mean
>>> if it is within same DC then sync replication is the best and if it is
>>> across DC replication async is better than sync. Please help me understand.
>
>> In the case of synchronous replication, master node waits for the
>> confirmation that a given transaction has committed on slave side
>> before committing itself. This wait period can cause some delay, hence
>> it is preferable to use sync replication with nodes that far from each
>> other.
>
> I am sure that you wanted to say
> "with nodes *not* that far from each other".
Oops sorry for the typo. Yes I meant of course "not that far".
-- 
Michael


-- 
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] "shared_buffers" and "temp_buffers" why manual and code are different?

[Michael Paquier]

On Sat, Nov 23, 2013 at 4:30 PM, Tianyin Xu  wrote:
> Hi,
>
> I want to tune the memory usage of PG (9.3.1) on my environment. I'm really
> confused by the following two configuration parameters,
>
> shared_buffers,
> temp_buffers,
>
> Take "shared_buffers" as the example, the manual says,
>
> "Sets the amount of memory the database server uses for shared memory
> buffers. The default is typically 128 megabytes (128MB),  This setting
> must be at least 128 kilobytes. "
>
> However, when I look at the configuration code (below), the code shows it
> should be the number of shared memory buffers *NOT* the size of the buffer .
>
> /* guc.c */
> 1638 {"shared_buffers", PGC_POSTMASTER, RESOURCES_MEM,
> 1639 gettext_noop("Sets the number of shared memory buffers used
> by the server."),
> 1640 NULL,
> 1641 GUC_UNIT_BLOCKS
> 1642 },
> 1643 &NBuffers,
> 1644 1024, 16, INT_MAX / 2,
> 1645 NULL, NULL, NULL
>
> So which one is correct?
Both. The GUC parameter uses as input the physical size of shared
buffers given by postgresql.conf, then converts it internally to the
number of shared buffers, number calculated depending on BLCKSZ. All
the calculation is done in guc.c:parse_int.

> Also, what exactly is the default value? The manual says that the default is
> 128MB while the min is 128KB. But from the code, you can clearly see that
> it's not the case.
There is no exact default value. The default is usually 128MB, but it
can be lower depending of what initdb decides.
Regards,
-- 
Michael


-- 
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] ERROR: out of memory DETAIL: Failed on request of size ???

[Tomas Vondra]

Hi,

On 22 Listopad 2013, 20:09, Edson Richter wrote:
>
> Excuse me (or just ignore me) if it is a stupid question, but have you
> configured sysctl.conf accordingly?
> For instance, to use larget memory settings, I had to configure my EL as
> follows:
>
> # Controls the maximum shared segment size, in bytes
> kernel.shmmax = 68719476736
>
> # Controls the maximum number of shared memory segments, in pages
> kernel.shmall = 4294967296

This only influences shared memory, which is used for shared buffers (not
for memory allocated during query execution, as that's translated to plain
malloc). With sysctl.conf misconfigured, the database would not start at
all and you'd get a reasonable error message in the log, suggesting pretty
clearly where/how to fix it.

Tomas



-- 
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] Getting non_NULL right-side values on a non-matching join?

[Vik Fearing]

On 11/23/2013 07:41 AM, Ken Tanzer wrote:
> OTOH, if there were a very clear and credible page with good
> instructions on installing build environment + postgres (for say RHEL,
> Ubuntu & Fedora) that would install side by side with an existing
> installation (and how to remove it all cleanly) it would make me and
> maybe others more able/likely to test patches.  There may be such a
> page--I just didn't find it.  And I was somewhat dissuaded from
> building an RPM on my CentOs machine by the note in the Postgres wiki
> that the ubuntu packages allow "multiple versions more easily than
> other packaging schemes."
>
> Just a thought.  I know all the information is out there and can be
> pieced together.  Like many computing endeavors, I'm sure the second
> time would be quick and easy, but likely not so much the first!

Chapter 15 of our documentation handles installing from source.
http://www.postgresql.org/docs/current/static/installation.html

-- 
Vik

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

[Ken Tanzer]

On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing  wrote:

>  On 11/23/2013 07:41 AM, Ken Tanzer wrote:
>
> OTOH, if there were a very clear and credible page with good instructions
> on installing build environment + postgres (for say RHEL, Ubuntu & Fedora)
> that would install side by side with an existing installation (and how to
> remove it all cleanly) it would make me and maybe others more able/likely
> to test patches.  There may be such a page--I just didn't find it.  And I
> was somewhat dissuaded from building an RPM on my CentOs machine by the
> note in the Postgres wiki that the ubuntu packages allow "multiple
> versions more easily than other packaging schemes."
>
>  Just a thought.  I know all the information is out there and can be
> pieced together.  Like many computing endeavors, I'm sure the second time
> would be quick and easy, but likely not so much the first!
>
>
> Chapter 15 of our documentation handles installing from source.
> http://www.postgresql.org/docs/current/static/installation.html
>
> --
> Vik
>
>  Thanks for the link.  I really do appreciate all the documentation that
Postgres has put together.  In this case I especially like the short
version provided, which covers part of what I was looking for.  It would be
great if there were a similar page that addressed how to set this up
side-by-side with an existing installation, and had a cheat sheet for
pulling in build tools and libraries.  (As in, on Cent OS run "yum install
x y z...", Ubunutu "apt-get install a x z".)  I get that the build
environment and libraries are outside of the scope of Postgres proper and
maybe unfair to ask it be documented, but they're still steps people have
to go through.  If they were included in that short version format, it
would be fantastic!

Cheers,
Ken




-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/ *
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Recursive function

[Alban Hertroys]

On 22 Nov 2013, at 21:56, Juan Daniel Santana Rodés 
 wrote:

> Hi everyone...
> I have a problem. I am programming a recursive function in plpgsql language. 
> This function use a cursor and when the function try to call the same 
> function throw a exception that it say me that the cursor is using.
> My friends how I can to resolve this problem.

Getting that error means that you are effectively trying to run multiple 
queries simultaneously in the same session. I don’t think that’s possible.

You didn’t tell what you’re trying to achieve, but it sounds to me that you 
either:
1. forgot to close the cursor for a short (non-recursive) lookup query before 
recursing to the same/next function,
2. or that you are actually trying to perform  a recursive query.

In case 1 the solution is simple; close the cursor before recursing.
For case 2, you may be able to rewrite your function to not recurse, but 
instead to use the results of a recursive CTE to achieve recursion.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



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

[GENERAL] WITH and exclusive locks

[Jayadevan M]

Hi,

When conducting performance tests of our application, I saw quite a few
exclusive locks in SELECTs with WITH clause. So I created a big table and
executed a query which takes time.
with x as (select * from emp where first_name like 'Sco%')
select * from x;
I got 2 locks - one accesshare lock against the table and one ExclusiveLock
with out  any database/relation.
2417postgres10/9510/95ExclusiveLockYes
2013-11-23 17:38:58+05:30

My guess is that the Exclusive lock is against some in-memory structure
(the temp table created by the WITH clause) and hence is not an issue. Is
that correct?
Regards,
Jayadevan

Re: [GENERAL] Getting non_NULL right-side values on a non-matching join?

[Adrian Klaver]

On 11/23/2013 02:45 AM, Ken Tanzer wrote:




On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing mailto:vik.fear...@dalibo.com>> wrote:

On 11/23/2013 07:41 AM, Ken Tanzer wrote:

OTOH, if there were a very clear and credible page with good
instructions on installing build environment + postgres (for say
RHEL, Ubuntu & Fedora) that would install side by side with an
existing installation (and how to remove it all cleanly) it would
make me and maybe others more able/likely to test patches.  There
may be such a page--I just didn't find it.  And I was somewhat
dissuaded from building an RPM on my CentOs machine by the note in
the Postgres wiki that the ubuntu packages allow "multiple
versions more easily than other packaging schemes."

Just a thought.  I know all the information is out there and can
be pieced together.  Like many computing endeavors, I'm sure the
second time would be quick and easy, but likely not so much the first!


Chapter 15 of our documentation handles installing from source.
http://www.postgresql.org/docs/current/static/installation.html

--
Vik

Thanks for the link.  I really do appreciate all the documentation that
Postgres has put together.  In this case I especially like the short
version provided, which covers part of what I was looking for.  It would
be great if there were a similar page that addressed how to set this up
side-by-side with an existing installation, and had a cheat sheet for
pulling in build tools and libraries.  (As in, on Cent OS run "yum
install x y z...", Ubunutu "apt-get install a x z".)  I get that the
build environment and libraries are outside of the scope of Postgres
proper and maybe unfair to ask it be documented, but they're still steps
people have to go through.  If they were included in that short version
format, it would be fantastic!



You will need the basic build tools. In Debian/Ubuntu that is 
build-essential in RH/CentOS that is 'Development Tools'. The other 
devel libraries will depend on what you want to include in the build. So 
for example if you want to use OpenSSL you will need libopenssl-devel 
and if you want plpythonu you will need python-devel. Running 
./configure will help you in that regard, it will flag those libraries 
not present. As to a separate installation, that is something you set up 
in the configure step. The two important things to know is that the new 
instance needs to be in a separate directory from the old and it needs 
to listen on a different port. You can install the new instance in your 
own home directory if that suits. Both the steps can be handled as follows:


$ ./configure --with-python --with-openssl 
--prefix=/home/aklaver/pgsqlTest --with-pgport=5462



When you run it, use the appropriate binaries. In the example I show 
above they would be in /home/aklaver/pgsqlTest/bin/. If you want you 
could set up symlinks to the binaries to make it easier, that is what 
the Debian/Ubuntu process does(among other things).




Cheers,
Ken





follow the discussion.



--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Getting non_NULL right-side values on a non-matching join?

[Tom Lane]

Ken Tanzer  writes:
> On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing  wrote:
>> Chapter 15 of our documentation handles installing from source.
>> http://www.postgresql.org/docs/current/static/installation.html

> Thanks for the link.  I really do appreciate all the documentation that
> Postgres has put together.  In this case I especially like the short
> version provided, which covers part of what I was looking for.  It would be
> great if there were a similar page that addressed how to set this up
> side-by-side with an existing installation, and had a cheat sheet for
> pulling in build tools and libraries.  (As in, on Cent OS run "yum install
> x y z...", Ubunutu "apt-get install a x z".)  I get that the build
> environment and libraries are outside of the scope of Postgres proper and
> maybe unfair to ask it be documented, but they're still steps people have
> to go through.  If they were included in that short version format, it
> would be fantastic!

FWIW, I think this is outside the scope of Chapter 15, and especially
outside the scope of the short version ;-).  If you're not wanting to
do the /usr/local approach, you're most likely wanting to build a
replacement for some distro-supplied packaging of Postgres.  There
are too many of those, and they change too often, for us to be able
to provide reasonable instructions for that in our formal docs.
Moreover, 99% of what you need to know for that is not PG-specific but
distro-specific.

Perhaps it'd be worth setting up page(s) on our wiki about this, though?
The question certainly comes up often enough.

regards, tom lane


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