Re: [HACKERS] Total memory allocated to postgres

[Robert Haas]

On Fri, Oct 8, 2010 at 10:37 AM, vamsi krishna
 wrote:
> I want to know how much main memory  is actually allocated to be the maximum
> memory for postgresql, because my hardware may have 4GB main memory but at
> the end of the day, only 1GB may be allocated for postgres.
>
> I want to set (increase) it manually. Can someone tell me how to do it?
>
> In aset.c we have a function
> AllocSetContextCreate(MemoryContext parent,const char *name,Size
> minContextSize,Size initBlockSize,Size maxBlockSize) defn.
>
> maxBlockSize is initialized to 8MB in mcxt.c where AllocSetContextCreate()
> is called. If I increase this value to 80 MB or so, will it increase the
> memory allocated to postgresql or will it just increase the block size?

That's not going to work.

> OR
>
> do I need to change some parameter in postgresql.conf file in data directory
> in pgsql folder?

The total amount of memory used by postgresql isn't something that you
can easily control exactly.  The two configuration variables that have
the most impact on it are shared_buffers and work_mem.  See
http://www.postgresql.org/docs/current/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

Keep in mind that PostgreSQL relies on the system buffer cache to some
degree.  You may need to play around with the parameters a bit to see
what makes your system work best.  I wouldn't focus so much on
measuring memory usage as looking at whether everything you have
running on the machine is running well and giving decent performance.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] getting set up on git (finally)

[Robert Haas]

On Fri, Oct 8, 2010 at 4:31 PM, Joe Conway  wrote:
> I'm finally trying to get current with the switch to git, following this
> wiki page:
>
>  http://wiki.postgresql.org/wiki/Committing_with_Git
>
> Specifically, I am trying to do:
> 
> Dependent Clone per Branch, Pushing and Pulling From a Local Repository
>  -plus-
> Clone Locally, Repoint Origin
> 
>
> In order to set this up, I used the attached recipe. Does that look
> reasonable and correct?
>
> I am hoping that from this point forward I can treat each of those
> checked out branches exactly (more-or-less) as I did under cvs -- true? E.g.
>
>  #make some changes
>  git commit -a
>  git push

Looks fairly sane on a quick once-over.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Keywords in pg_hba.conf should be field-specific

[Robert Haas]

On Fri, Oct 8, 2010 at 10:13 PM, Brendan Jurd  wrote:
> One of the speedbumps I hit when setting up HS+SR was naming the user
> the slave would connect as for streaming replication.  At first I
> picked 'replication', which seemed quite natural to me (and I don't
> doubt will seem natural to others as well).
>
> When I started up the slave, I got this error:
>
> FATAL:  could not connect to the primary server: FATAL:  no
> pg_hba.conf entry for replication connection from host
> "192.168.21.10", user "replication", SSL on
>
> Bzzzt.  Wrong.  There *was* such an entry in pg_hba.conf.  I wasted a
> lot of time checking my conf files for typos before I wondered whether
> there might be something wrong with using 'replication' as a username.
>  I changed the username to 'streamrep' and it all started working
> perfectly.
>
> I understand that 'replication' is a keyword as far as the database
> name is concerned, but I was surprised to find that it was treated as
> a keyword in the username field also.

Yikes.  That does seems surprising.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] GIN vs. Partial Indexes

[David E. Wheeler]

On Oct 8, 2010, at 1:47 PM, Tom Lane wrote:

> How so?  In a typical application, there would not likely be very many
> such rows --- we're talking about cases like documents containing zero
> indexable words.  In any case, the problem right now is that GIN has
> significant functional limitations because it fails to make any index
> entry at all for such rows.  Even if there are in fact no such rows
> in a particular table, it has to fail on some queries because there
> *might* be such rows.  There is no way to fix those limitations
> unless it undertakes to have some index entry for every row.  That
> will take disk space, but it's *necessary*.  (To adapt the old saw,
> I can make this index arbitrarily small if it doesn't have to give
> the right answers.)

And could you not keep it the same with a partial index?

Best,

David

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

[HACKERS] Keywords in pg_hba.conf should be field-specific

[Brendan Jurd]

Hi folks,

One of the speedbumps I hit when setting up HS+SR was naming the user
the slave would connect as for streaming replication.  At first I
picked 'replication', which seemed quite natural to me (and I don't
doubt will seem natural to others as well).

When I started up the slave, I got this error:

FATAL:  could not connect to the primary server: FATAL:  no
pg_hba.conf entry for replication connection from host
"192.168.21.10", user "replication", SSL on

Bzzzt.  Wrong.  There *was* such an entry in pg_hba.conf.  I wasted a
lot of time checking my conf files for typos before I wondered whether
there might be something wrong with using 'replication' as a username.
 I changed the username to 'streamrep' and it all started working
perfectly.

I understand that 'replication' is a keyword as far as the database
name is concerned, but I was surprised to find that it was treated as
a keyword in the username field also.  I had a look in
src/backend/libpq/hba.c, and next_token() appears to be completely
naive about this.  'replication' (along with 'all', 'sameuser',
'samegroup' and 'samerole')  is treated as a keyword wherever it
appears, not just in the field where it is relevant.  next_token()
appends a newline to the end of the 'replication' username token, and
that's why the entry doesn't match my connection attempt.

I suspect this is going to trip a lot of people up.  We could just
document it and tell people that if they want to use 'replication' as
a username, they'd better quote it in pg_hba.conf.  But I'd prefer to
actually solve the problem.  We could do this by teaching next_token
to be sensitive to which field it is parsing, and limit keyword
detection appropriately.  Or, if that's awkward, we could teach
parse_hba_line to ignore keyword markers where the keyword is not
applicable.  The more I think about it, the more I incline towards the
latter.

Thoughts?

Cheers,
BJ

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

[HACKERS] Docs for archive_cleanup_command are poor

[Brendan Jurd]

Hi folks,

I have just set up HS+SR for the first time, and for the most part,
the docs were excellent.  The one exception for me was the discussion
of archive_cleanup_command.  This is a pretty important part of
constructing a healthy standby server, and IMO the docs don't give it
the treatment it deserves.

Under "25.2.4. Setting Up a Standby Server", we have:

"You can use archive_cleanup_command to prune the archive of files no
longer needed by the standby."

... then a few paragraphs later ...

"If you're using a WAL archive, its size can be minimized using the
archive_cleanup_command  option to remove files that are no longer
required by the standby server. Note however, that if you're using the
archive for backup purposes, you need to retain files needed to
recover from at least the latest base backup, even if they're no
longer needed by the standby."

So there are a couple of brief mentions of what
archive_cleanup_command is for, but nothing about how it works, no
exampes of how to use it, and no links at all.  Contrast how we deal
with archive_command, restore_command and primary_conninfo.

I'd like to suggest a few ways we could improve on this:

1. Remove the former paragraph.  It's stranded out there on its own in
the middle of some unrelated text, and doesn't say anything of
substance not also said in the latter paragraph.

2. Include an example archive_cleanup_command in the recovery.conf
example snippet.

3. Link to 26.1 which actually explains how a_c_c works.

4. Mention, and link to, pg_archivecleanup from both 25.2.4 and 26.1.
This is the utility that most newcomers to WAL archiving will want to
use, so it's rather weird of us not to advertise it.

I'm willing to write a patch for this, but I thought I'd raise the
suggestions on-list first, before getting too invested.  So, please
comment if you have an opinion on this.

Cheers,
BJ

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

Re: [HACKERS] Sync Replication with transaction-controlled durability

[Robert Haas]

On Fri, Oct 8, 2010 at 5:59 PM, Dimitri Fontaine  wrote:
> Robert Haas  writes:
>> It seems like it would be more helpful if you were working on
>> implementing a design that had more than one vote.  As far as I can
>> tell, we have rough consensus that for the first commit we should only
>> worry about the case where k = 1; that is, only one ACK is ever
>> required for commit
>
> My understanding by reading the mails here and quick-reading the patch
> (in my MUA, that's how quick the reading was), is that what you want
> here is what's done in the patch, which has been proposed as a WIP, too.

It's not.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Review: Fix snapshot taking inconsistencies

[Marko Tiikkaja]

On 2010-10-04 5:31 PM +0300, Tom Lane wrote:

Marko Tiikkaja  writes:

Nope.  I think I grepped contrib/ at some point and none of those were
using pg_parse_and_rewrite() so this is all just speculation.  And yes,
it's not really part of any stable API but breaking third party modules
needlessly is not something I want to do.  However, in this case there
is no way to avoid breaking them.


In the particular case at hand here, I rather wonder why SQL functions
are depending on postgres.c at all.  It might be better to just
duplicate a bit of code to make them independent.  pg_parse_and_rewrite
would then be dead code and could be deleted.


I'm confused.  Even if we get rid of pg_parse_and_rewrite, SQL functions 
need pg_parse_query and pg_analyze_and_rewrite from postgres.c.  You're 
not suggesting duplicating the code in those two, are you?



Regards,
Marko Tiikkaja

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

Re: [HACKERS] Sync Replication with transaction-controlled durability

[Dimitri Fontaine]

Robert Haas  writes:
> It seems like it would be more helpful if you were working on
> implementing a design that had more than one vote.  As far as I can
> tell, we have rough consensus that for the first commit we should only
> worry about the case where k = 1; that is, only one ACK is ever
> required for commit

My understanding by reading the mails here and quick-reading the patch
(in my MUA, that's how quick the reading was), is that what you want
here is what's done in the patch, which has been proposed as a WIP, too.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] GIN vs. Partial Indexes

[Robert Haas]

On Fri, Oct 8, 2010 at 1:47 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Oct 7, 2010 at 10:52 PM, Tom Lane  wrote:
>>> IMO, what's needed is to fix GIN so it doesn't go insane for empty
>>> values or non-restrictive queries, by ensuring there's at least one
>>> index entry for every row.  This has been discussed before; see the TODO
>>> section for GIN.
>
>> That seems like it could waste an awful lot of disk space (and
>> therefore I/O, etc.).  No?
>
> How so?  In a typical application, there would not likely be very many
> such rows --- we're talking about cases like documents containing zero
> indexable words.  In any case, the problem right now is that GIN has
> significant functional limitations because it fails to make any index
> entry at all for such rows.  Even if there are in fact no such rows
> in a particular table, it has to fail on some queries because there
> *might* be such rows.  There is no way to fix those limitations
> unless it undertakes to have some index entry for every row.  That
> will take disk space, but it's *necessary*.  (To adapt the old saw,
> I can make this index arbitrarily small if it doesn't have to give
> the right answers.)
>
> In any case, I would expect that GIN could actually do this quite
> efficiently.  What we'd probably want is a concept of a "null word",
> with empty indexable rows entered in the index as if they contained the
> null word.  So there'd be just one index entry with a posting list of
> however many such rows there are.



Yeah, I think you're right.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 16:34 -0400, Greg Smith wrote:
> Tom Lane wrote:
> > How are you going to "mark the standby as degraded"?  The
> > standby can't keep that information, because it's not even connected
> > when the master makes the decision.
> 
>  From a high level, I'm assuming only that the master has a list in 
> memory of the standby system(s) it believes are up to date, and that it 
> is supposed to commit to synchronously.  When I say mark as degraded, I 
> mean that the master merely closes whatever communications channel it 
> had open with that system and removes the standby from that list.

My current coding works with two sets of parameters: 

The "master marks standby as degraded" is handled by the tcp keepalives.
When it notices no response, it kicks out the standby. We already had
this, so I never mentioned it before as being part of the solution.

The second part is the synchronous_replication_timeout which is a user
settable parameter defining how long the app is prepared to wait, which
could be more or less time than the keepalives.

> If that standby now reconnects again, I don't see how resolving what 
> happens at that point is any different from when a standby is first 
> started after both systems were turned off.  If the standby is current 
> with the data available on the master when it has an initial 
> conversation, great; it's now available for synchronous commit too 
> then.  If it's not, it goes into a catchup mode first instead.  When the 
> master sees you're back to current again, if you're on the list of sync 
> servers too you go back onto the list of active sync systems.
> 
> There's shouldn't be any state information to save here.  If the master 
> and standby can't figure out if they are in or out of sync with one 
> another based on the conversation they have when they first connect to 
> one another, that suggests to me there needs to be improvements made in 
> the communications protocol they use to exchange messages. 

Agreed.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 17:06 +0200, Markus Wanner wrote:
> Well, full cluster outages are infrequent, but sadly cannot be avoided
> entirely. (Murphy's laughing). IMO we should be prepared to deal with
> those. 

I've described how I propose to deal with those. I'm not waving away
these issues, just proposing that we consciously choose simplicity and
therefore robustness.

Let me say it again for clarity. (This is written for the general case,
though my patch uses only k=1 i.e. one acknowledgement):

If we want robustness, we have multiple standbys. So if you lose one,
you continue as normal without interruption. That is the first and most
important line of defence - not software.

When we start to wait, if there aren't sufficient active standbys to
acknowledge a commit, then the commit won't wait. This behaviour helps
us avoid situations where we are hours or days away from having a
working standby to acknowledge the commit. We've had a long debate about
servers that "ought to be there" but aren't; I suggest we treat standbys
that aren't there as having a strong possibility they won't come back,
and hence not worth waiting for. Heikki disagrees; I have no problem
with adding server registration so that we can add additional waits, but
I doubt that the majority of users prefer waiting over availability. It
can be an option

Once we are waiting, if insufficient standbys acknowledge the commit we
will wait until the timeout expires, after which we commit and continue
working. If you don't like timeouts, set the timeout to 0 to wait
forever. This behaviour is designed to emphasise availability. (I
acknowledge that some people are so worried by data loss that they would
choose to stop changes altogether, and accept unavailability; I regard
that as a minority use case, but one which I would not argue against
including as an options at some point in the future.)

To cover Dimitri's observation that when a streaming standby first
connects it might take some time before it can sensibly acknowledge, we
don't activate the standby until it has caught up. Once caught up, it
will advertise it's capability to offer a sync rep service. Standbys
that don't wish to be failover targets can set
synchronous_replication_service = off.

The paths between servers aren't defined explicitly, so the parameters
all still work even after failover.
 
-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Greg Smith]

Tom Lane wrote:

How are you going to "mark the standby as degraded"?  The
standby can't keep that information, because it's not even connected
when the master makes the decision.


From a high level, I'm assuming only that the master has a list in 
memory of the standby system(s) it believes are up to date, and that it 
is supposed to commit to synchronously.  When I say mark as degraded, I 
mean that the master merely closes whatever communications channel it 
had open with that system and removes the standby from that list.


If that standby now reconnects again, I don't see how resolving what 
happens at that point is any different from when a standby is first 
started after both systems were turned off.  If the standby is current 
with the data available on the master when it has an initial 
conversation, great; it's now available for synchronous commit too 
then.  If it's not, it goes into a catchup mode first instead.  When the 
master sees you're back to current again, if you're on the list of sync 
servers too you go back onto the list of active sync systems.


There's shouldn't be any state information to save here.  If the master 
and standby can't figure out if they are in or out of sync with one 
another based on the conversation they have when they first connect to 
one another, that suggests to me there needs to be improvements made in 
the communications protocol they use to exchange messages. 


--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us



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

[HACKERS] getting set up on git (finally)

[Joe Conway]

I'm finally trying to get current with the switch to git, following this
wiki page:

  http://wiki.postgresql.org/wiki/Committing_with_Git

Specifically, I am trying to do:

Dependent Clone per Branch, Pushing and Pulling From a Local Repository
  -plus-
Clone Locally, Repoint Origin


In order to set this up, I used the attached recipe. Does that look
reasonable and correct?

I am hoping that from this point forward I can treat each of those
checked out branches exactly (more-or-less) as I did under cvs -- true? E.g.

 #make some changes
 git commit -a
 git push

Thanks,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
mkdir /opt/src/pgsql-git
cd /opt/src/pgsql-git
git clone --bare --mirror ssh://g...@gitmaster.postgresql.org/postgresql.git

cd /opt/src/pgsql-git
git clone postgresql master
cd master
git checkout master
git config branch.master.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch '+refs/heads/master:refs/remotes/origin/master'

cd /opt/src/pgsql-git
git clone postgresql REL9_0_STABLE
cd REL9_0_STABLE
git checkout REL9_0_STABLE
git config branch.REL9_0_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL9_0_STABLE:refs/remotes/origin/REL9_0_STABLE'
git branch -D master

cd /opt/src/pgsql-git
git clone postgresql REL8_4_STABLE
cd REL8_4_STABLE
git checkout REL8_4_STABLE
git config branch.REL8_4_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL8_4_STABLE:refs/remotes/origin/REL8_4_STABLE'
git branch -D master

cd /opt/src/pgsql-git
git clone postgresql REL8_3_STABLE
cd REL8_3_STABLE
git checkout REL8_3_STABLE
git config branch.REL8_3_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL8_3_STABLE:refs/remotes/origin/REL8_3_STABLE'
git branch -D master

cd /opt/src/pgsql-git
git clone postgresql REL8_2_STABLE
cd REL8_2_STABLE
git checkout REL8_2_STABLE
git config branch.REL8_2_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL8_2_STABLE:refs/remotes/origin/REL8_2_STABLE'
git branch -D master

cd /opt/src/pgsql-git
git clone postgresql REL8_1_STABLE
cd REL8_1_STABLE
git checkout REL8_1_STABLE
git config branch.REL8_1_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL8_1_STABLE:refs/remotes/origin/REL8_1_STABLE'
git branch -D master

cd /opt/src/pgsql-git
git clone postgresql REL8_0_STABLE
cd REL8_0_STABLE
git checkout REL8_0_STABLE
git config branch.REL8_0_STABLE.rebase true
git config user.name "Joe Conway"
git config user.email m...@joeconway.com
git config remote.origin.fetch 
'+refs/heads/REL8_0_STABLE:refs/remotes/origin/REL8_0_STABLE'
git branch -D master

cd /opt/src/pgsql-git/master
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL9_0_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL8_4_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL8_3_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL8_2_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL8_1_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin

cd /opt/src/pgsql-git/REL8_0_STABLE
git remote set-url origin ssh://g...@gitmaster.postgresql.org/postgresql.git
git remote update
git remote prune origin



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Issues with Quorum Commit

[Greg Smith]

Markus Wanner wrote:

..and how do you make sure you are not marking your second standby as
degraded just because it's currently lagging? Effectively degrading the
utterly needed one, because your first standby has just bitten the dust?
  


People are going to monitor the standby lag.  If it gets excessive 
relative to where it's approaching the known timeout, the flashing 
yellow lights should go off at this point, before it gets this bad.  And 
if you've set a reasonable business oriented timeout on how long you can 
stand for the master to be held up waiting for a lagging standby, the 
right thing to do may very well be to cut it off.  At some point people 
will want to stop waiting for a standby if it's taking so long to commit 
that it's interfering with the ability of the master to operate 
normally.  Such a master is already degraded, if your performance 
metrics for availability includes processing transactions in a timely 
manner.



And how do you prevent the split brain situation in case the master dies
shortly after these events, but fails to come up again immediately?
  


How is that a new problem?  It's already possible to end up with a 
standby pair that has suffered through some bizarre failure chain such 
that it's not necessarily obvious which of the two systems has the most 
recent set of data on it.  And that's not this project's problem to 
solve.  Useful answers to the split brain problem involve fencing 
implementations that normally drop to the hardware level, and clustering 
solutions including those features are already available that PostgreSQL 
can integrate into.  Assuming you have to solve this in order to deliver 
a useful database replication component is excessively ambitious. 

You seem to be under the assumption that a more complicated replication 
implementation here will make reaching a bad state impossible.  I think 
that's optimistic, both in theory and in regards to how successful code 
gets built.  Here's the thing:  the difficultly of testing to prove your 
code actually works is also proportional to that complexity.  This 
project can chose to commit and potentially ship a simple solution that 
has known limitations, and expect that people will fill in the gap with 
existing add-on software to handle the clustering parts it doesn't:  
fencing, virtual IP address assignment, etc.  All while getting useful 
testing feedback on the simple bottom layer, whose main purpose in life 
is to transport WAL data synchronously.  Or, we can argue in favor of 
adding additional complexity on top first instead, so we end up with 
layers and layers of untested code.  That path leads to situations where 
you're lucky to ship at all, and when you do the result is difficult to 
support.


--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us



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

Re: [HACKERS] WIP: Triggers on VIEWs

[Tom Lane]

BTW, while I'm looking at this: it seems like the "index" arrays in
struct TrigDesc are really a lot more complication than they are worth.
It'd be far easier to dispense with them and instead iterate through
the main trigger array, skipping any triggers whose tgtype doesn't match
what we need.  If you had a really huge number of triggers on a table,
it's possible that could be marginally slower, but I'm having a hard
time envisioning practical situations where anybody could tell the
difference.

regards, tom lane

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

Re: [HACKERS] Issues with Quorum Commit

[Heikki Linnakangas]

On 08.10.2010 17:26, Fujii Masao wrote:

On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
  wrote:

Do we really need that?


Yes. But if there is no unsent WAL when the master goes down,
we can start new standby without new backup by copying the
timeline history file from new master to new standby and
setting recovery_target_timeline to 'latest'.


.. and restart the standby.


In this case,
new standby advances the recovery to the latest timeline ID
which new master uses before connecting to the master.

This seems to have been successful in my test environment.
Though I'm missing something.


Yeah, that should work, but it's awfully complicated.


I don't think that's acceptable, we'll need to fix
that if that's the case.


Agreed.


You can cross timelines with the archive, though. But IIRC there was some
issue with that too, you needed to restart the standbys because the standby
scans what timelines exist at the beginning of recovery, and won't notice
new timelines that appear after that?


Yes.


We need to address that, apart from any of the other things discussed wrt.
synchronous replication. It will benefit asynchronous replication too. IMHO
*that* is the next thing we should do, the next patch we commit.


You mean to commit that capability before synchronous replication? If so,
I disagree with you. I think that it's not easy to address that problem.
So I'm worried about that implementing that capability first means the miss
of sync rep in 9.1.


It's a pretty severe shortcoming at the moment. For starters, it means 
that you need a shared archive, even if you set wal_keep_segments to a 
high number. Secondly, it's a lot of scripting to get it working, I 
don't like the thought of testing failovers in synchronous replication 
if I have to do all that. Frankly, this seems more important to me than 
synchronous replication.


It shouldn't be too hard to fix. Walsender needs to be able to read WAL 
from preceding timelines, like recovery does, and walreceiver needs to 
write the incoming WAL to the right file.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Issues with Quorum Commit

[Rob Wultsch]

*

On 10/8/10, Fujii Masao  wrote:
> On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
>  wrote:
>> Do we really need that?
>
> Yes. But if there is no unsent WAL when the master goes down,
> we can start new standby without new backup by copying the
> timeline history file from new master to new standby and
> setting recovery_target_timeline to 'latest'. In this case,
> new standby advances the recovery to the latest timeline ID
> which new master uses before connecting to the master.
>
> This seems to have been successful in my test environment.
> Though I'm missing something.
>
>> I don't think that's acceptable, we'll need to fix
>> that if that's the case.
>
> Agreed.
>
>> You can cross timelines with the archive, though. But IIRC there was some
>> issue with that too, you needed to restart the standbys because the
>> standby
>> scans what timelines exist at the beginning of recovery, and won't notice
>> new timelines that appear after that?
>
> Yes.
>
>> We need to address that, apart from any of the other things discussed wrt.
>> synchronous replication. It will benefit asynchronous replication too.
>> IMHO
>> *that* is the next thing we should do, the next patch we commit.
>
> You mean to commit that capability before synchronous replication? If so,
> I disagree with you. I think that it's not easy to address that problem.
> So I'm worried about that implementing that capability first means the miss
> of sync rep in 9.1.
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


-- 
Rob Wultsch
wult...@gmail.com

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

Re: [HACKERS] WIP: Triggers on VIEWs

[Tom Lane]

Robert Haas  writes:
> On Fri, Oct 8, 2010 at 11:50 AM, Tom Lane  wrote:
>> Although we already have macros TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE
>> that seem to mask the details here, the changes you had to make in
>> contrib illustrate that the macros' callers could still be embedding this
>> basic mistake of testing "!before" when they mean "after" or vice versa.
>> I wonder whether we should intentionally rename the macros to force
>> people to take another look at their logic.  Or is that going too far?
>> Comments anyone?

> I'm less sold on this one.

I'm not sold on it either, just wanted to run it up the flagpole to see
if anyone would salute.  For the moment I'm thinking that calling out
the point in the 9.1 release notes should be sufficient.  I made an
extra commit to make sure the issue is salient in the commit log.

regards, tom lane

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

Re: [HACKERS] GIN vs. Partial Indexes

[Tom Lane]

Robert Haas  writes:
> On Thu, Oct 7, 2010 at 10:52 PM, Tom Lane  wrote:
>> IMO, what's needed is to fix GIN so it doesn't go insane for empty
>> values or non-restrictive queries, by ensuring there's at least one
>> index entry for every row.  This has been discussed before; see the TODO
>> section for GIN.

> That seems like it could waste an awful lot of disk space (and
> therefore I/O, etc.).  No?

How so?  In a typical application, there would not likely be very many
such rows --- we're talking about cases like documents containing zero
indexable words.  In any case, the problem right now is that GIN has
significant functional limitations because it fails to make any index
entry at all for such rows.  Even if there are in fact no such rows
in a particular table, it has to fail on some queries because there
*might* be such rows.  There is no way to fix those limitations
unless it undertakes to have some index entry for every row.  That
will take disk space, but it's *necessary*.  (To adapt the old saw,
I can make this index arbitrarily small if it doesn't have to give
the right answers.)

In any case, I would expect that GIN could actually do this quite
efficiently.  What we'd probably want is a concept of a "null word",
with empty indexable rows entered in the index as if they contained the
null word.  So there'd be just one index entry with a posting list of
however many such rows there are.

regards, tom lane

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

Re: [HACKERS] WIP: Triggers on VIEWs

[Robert Haas]

On Fri, Oct 8, 2010 at 11:50 AM, Tom Lane  wrote:
> I think the
> right thing here is to replace "before" with a three-valued enum,
> perhaps called "timing", so as to force people to take another look
> at any code that touches the field directly.

+1.  That seems much nicer.

> Although we already have macros TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE
> that seem to mask the details here, the changes you had to make in
> contrib illustrate that the macros' callers could still be embedding this
> basic mistake of testing "!before" when they mean "after" or vice versa.
> I wonder whether we should intentionally rename the macros to force
> people to take another look at their logic.  Or is that going too far?
> Comments anyone?

I'm less sold on this one.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] standby registration (was: is sync rep stalled?)

[Robert Haas]

On Fri, Oct 8, 2010 at 12:29 PM, Josh Berkus  wrote:
> On 10/07/2010 06:38 PM, Robert Haas wrote:
>>
>> Yes, let's please just implement something simple and get it
>> committed.  k = 1.  Two GUCs (synchronous_standbys = name, name, name
>> and synchronous_waitfor = none|recv|fsync|apply), SUSET so you can
>> change it per txn.  Done.  We can revise it *the day after it's
>> committed* if we agree on how.  And if we*don't*  agree, then we can
>> ship it and we still win.
>
> If we have all this code, and it appears that we do, +1 to commit it now so
> that we can start testing.

To the best of my knowledge we don't have exactly that thing, but it
seems like either of the two patches on the table could probably be
beaten into that shape with a large mallet in fairly short order, and
I think we should pick one of them and do just that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] GIN vs. Partial Indexes

[Robert Haas]

On Thu, Oct 7, 2010 at 10:52 PM, Tom Lane  wrote:
> Josh Berkus  writes:
>> I thought we fixed this in 8.4.4, but apparently not.  In the event that
>> you have a GIN index containing a WHERE clause which is sufficiently
>> restrictive, PostgreSQL will attempt to use the index even though it
>> can't.
>
> We could probably kluge the planner to avoid that case, but in view
> of the other issues explained here:
> http://developer.postgresql.org/pgdocs/postgres/gin-limit.html
> I'm not sure it's worth the trouble.  There's nothing the planner can do
> to guard against the equivalent issue of non-restrictive queries, ie
> there is a WHERE clause but it's something like "array-column contains
> empty-array".  The fact that the comparison value is empty might not be
> known until runtime.
>
> IMO, what's needed is to fix GIN so it doesn't go insane for empty
> values or non-restrictive queries, by ensuring there's at least one
> index entry for every row.  This has been discussed before; see the TODO
> section for GIN.

That seems like it could waste an awful lot of disk space (and
therefore I/O, etc.).  No?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] WIP: Triggers on VIEWs

[Tom Lane]

Dean Rasheed  writes:
> On 8 October 2010 16:50, Tom Lane  wrote:
>> I've started looking at this patch now.  I think it would have been best
>> submitted as two patches: one to add the SQL-spec "INSTEAD OF" trigger
>> functionality, and a follow-on to extend INSTEAD OF triggers to views.

> SQL-spec "INSTEAD OF" triggers *are* view triggers. I don't see how
> you can separate the two.

Oh, they're not allowed on tables?  Why not?  AFAICS they'd be exactly
equivalent to a BEFORE trigger that always returns NULL.

> I think that you're confusing 2 different parts of code here. The
> TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE macros operate on the bits
> from the tg_event field of the TriggerData structure.

Yeah, I'm aware of that.  My point is that all code that deals with
trigger firing times now has to consider three possible states where
before there were two; and it's entirely likely that some places are
testing for the wrong condition once a third state is admitted as a
possibility.

> The scope for user
> code being broken is limited beause they'd have to put one of these
> trigger functions in an INSTEAD OF trigger on a view.

Well, the only reason those tests are being made at all is as
self-defense against being called via an incorrect trigger definition.
So they're worth nothing if they fail to treat the INSTEAD OF case
correctly.

regards, tom lane

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

Re: [HACKERS] WIP: Triggers on VIEWs

[Dean Rasheed]

On 8 October 2010 16:50, Tom Lane  wrote:
> Bernd Helmle  writes:
>> I would like to do some more tests/review, but going to mark this patch as
>> "Ready for Committer", so that someone more qualified on the executor part
>> can have a look on it during this commitfest, if that's okay for us?
>
> I've started looking at this patch now.  I think it would have been best
> submitted as two patches: one to add the SQL-spec "INSTEAD OF" trigger
> functionality, and a follow-on to extend INSTEAD OF triggers to views.

SQL-spec "INSTEAD OF" triggers *are* view triggers. I don't see how
you can separate the two.


> I'm thinking of breaking it apart into two separate commits along that
> line, mainly because I think INSTEAD OF is probably committable but
> I'm much less sure about the other part.
>
> In the INSTEAD OF part, the main gripe I've got is the data
> representation choice:
>
> ***
> *** 1609,1614 
> --- 1609,1615 
>      List       *funcname;       /* qual. name of function to call */
>      List       *args;           /* list of (T_String) Values or NIL */
>      bool        before;         /* BEFORE/AFTER */
> +     bool        instead;        /* INSTEAD OF (overrides BEFORE/AFTER) */
>      bool        row;            /* ROW/STATEMENT */
>      /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */
>      int16       events;         /* INSERT/UPDATE/DELETE/TRUNCATE */
>
> This pretty much sucks, because not only is this a rather confusing
> definition, it's not really backwards compatible: any code that thinks
> "!before" must mean "after" is going to be silently broken.  Usually,
> when you do something that necessarily breaks old code, what you want
> is to make sure the breakage is obvious at compile time.  I think the
> right thing here is to replace "before" with a three-valued enum,
> perhaps called "timing", so as to force people to take another look
> at any code that touches the field directly.
>
> Although we already have macros TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE
> that seem to mask the details here, the changes you had to make in
> contrib illustrate that the macros' callers could still be embedding this
> basic mistake of testing "!before" when they mean "after" or vice versa.
> I wonder whether we should intentionally rename the macros to force
> people to take another look at their logic.  Or is that going too far?
> Comments anyone?
>

I think that you're confusing 2 different parts of code here. The
TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE macros operate on the bits
from the tg_event field of the TriggerData structure. This has a
completely different representation for the trigger timing compared to
the code you mention above, which is from the CreateTrigStmt
structure. That structure is only used internally in a couple of
places, by the parser and CreateTrigger().

I agree that perhaps it would be neater to represent it as an enum,
but I think the scope for code breakage is far smaller than you claim.
This structure is not being exposed to trigger code.

The changes I made in contrib are unrelated to the representation used
in CreateTrigStmt. It's just a matter of tidying up code in before
triggers to say "if (!fired_before) error" rather than "if
(fired_after) error". That's neater anyway, even in the case where
they're mutually exclusive (as they are on tables). The scope for user
code being broken is limited beause they'd have to put one of these
trigger functions in an INSTEAD OF trigger on a view.

Regards,
Dean



>                        regards, tom lane
>

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

Re: [HACKERS] Issues with Quorum Commit

[Josh Berkus]

And, I'd like to know whether the master waits forever because of the
standby failure in other solutions such as Oracle DataGuard, MySQL
semi-synchronous replication.


MySQL used to be fond of simiply failing sliently.  Not sure what 5.4 
does, or Oracle.  In any case MySQL's replication has always really been 
async (except Cluster, which is a very different database), so it's not 
really a comparison.


Here's the comparables:
Oracle DataGuard
DRBD
SQL Server
DB2

If anyone knows what the above do by default, please speak up!

--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] Timeout and Synch Rep

[Josh Berkus]

So, if something happens to the standby, and it cannot return an ack in
30 seconds, they would like it to degrade to asynch mode.  At that
point, they would also like to trigger a nagios alert which will wake up
the sysadmin with flashing red lights.


How?


TBD, and before 9.1.  It's clear to *me* that we're going to need some 
read-only system views around replication in order to make monitoring 
work.  Also, given that we have this shiny new LISTEN/NOTIFY 
implementation, it would be peachy keen to maybe make use of it to emit 
an event when replication changes, no?


Otherwise, DBAs are forced to tail the logs to figure out what 
replication status is.  This is NOT adequate.



--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] standby registration (was: is sync rep stalled?)

[Josh Berkus]

On 10/07/2010 06:38 PM, Robert Haas wrote:

Yes, let's please just implement something simple and get it
committed.  k = 1.  Two GUCs (synchronous_standbys = name, name, name
and synchronous_waitfor = none|recv|fsync|apply), SUSET so you can
change it per txn.  Done.  We can revise it *the day after it's
committed* if we agree on how.  And if we*don't*  agree, then we can
ship it and we still win.


If we have all this code, and it appears that we do, +1 to commit it now 
so that we can start testing.


--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] GIN vs. Partial Indexes

[Josh Berkus]

IMO, what's needed is to fix GIN so it doesn't go insane for empty
values or non-restrictive queries, by ensuring there's at least one
index entry for every row.  This has been discussed before; see the TODO
section for GIN.


Well, what is that fix waiting on, then?  Oleg, Teodor?  We may even 
have a modest budget for a patch, so if funding would help, I'll ask.


--
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] Sync Replication with transaction-controlled durability

[Robert Haas]

On Fri, Oct 8, 2010 at 11:10 AM, Simon Riggs  wrote:
> On Tue, 2010-09-14 at 18:48 +0100, Simon Riggs wrote:
>
>> I'm working on a patch to implement synchronous replication for
>> PostgreSQL, with user-controlled durability specified on the master. The
>> design also provides high throughput by allowing concurrent processes to
>> handle the WAL stream. The proposal requires only 3 new parameters and
>> takes into account much community feedback on earlier ideas.
>
> I'm now implementing v5, which simplifies the parameters still further
>
> USERSET on master
> * synchronous_replication = off (default) | on
> * synchronous_replication_timeout >=0 default=0 means wait forever
>
> set in postgresql.conf on standby
> * synchronous_replication_service = on (default) | off
>
> WALwriter is not active, nor are multiple sync rep modes available.
> Coding allows us to extend number of modes in future.
>
> Coding also solves problem raised by Dimitri: we don't advertise the
> sync rep service until the standby has caught up.
>
> This patch is a rough WIP, mostly stripping out and streamlining. It
> doesn't work yet, but people say they like to see me working, so here
> 'tis.

It seems like it would be more helpful if you were working on
implementing a design that had more than one vote.  As far as I can
tell, we have rough consensus that for the first commit we should only
worry about the case where k = 1; that is, only one ACK is ever
required for commit; and Greg Smith spelled out some more particulars
for a minimum acceptable implementation in the second part of the
email found here:

http://archives.postgresql.org/pgsql-hackers/2010-10/msg00384.php

That proposal is, AFAICT, the ONLY one that has got more than one
vote, and certainly the only one that has got as many votes as that
one does.  If you want to implement that, then I think we could reach
critical consensus on committing it very quickly.  If you DON'T want
to implement that proposal, then I suggest that we let Fujii Masao or
Heikki implement and commit it.  I realize, as you've pointed out
before, that there is no danger of missing 9.1 at this point, but on
the flip side I don't see that there's anything to be gained by
spending another month rehashing the topic when there's a good
proposal on the table that's got some momentum behind it.  Let's not
make this more complicated than it needs to be.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] On Scalability

[Greg Stark]

On Fri, Oct 8, 2010 at 3:20 AM, Vincenzo Romano
 wrote:
> Do the same conclusions apply to partial indexes?
> I mean, if I have a large number (n>=100 or n>=1000) of partial indexes
> on a single very large table (m>=10**12), how good is the planner to choose 
> the
> right indexes to plan a query?
> Has also this algorithm superlinear complexity?

No, it's also linear. It needs to look at every partial index and
check to see whether it's a candidate for your query. Actually that's
true for regular indexes as well but it has the extra step of proving
that the partial index includes all the rows your query needs which is
not a cheap step.

The size of the table isn't relevant though, except inasmuch as the
savings when actually running the query will be larger for larger
tables so it may be worth spending more time planning queries on large
tables.

-- 
greg

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

[HACKERS] Italian PGDay 2010, Call for papers

[Gabriele Bartolini]

The fourth edition of the Italian PostgreSQL Day (PGDay.IT 2010) will be 
held on December 10 in Rome.


It will cover topics for users, developers and contributors to 
PostgreSQL, as well as decision and policy makers. For more information 
about the conference, please visit the website at http://2010.pgday.it/.


Proposals for talks are now being accepted. Please note that talks will 
be accepted in both English and Italian. This call for papers is 
targeted for English speakers.


Each session will last 30 minutes, and may be on any topic related to 
PostgreSQL. Suggested topic areas include:


   * Developing applications for PostgreSQL
   * Administering large scale PostgreSQL installations
   * Case studies of PostgreSQL deployments
   * PostgreSQL tools and utilities
   * PostgreSQL hacking
   * Community & user groups
   * Tuning the server
   * Migrating from other systems
   * Scaling/replication
   * Benchmarking & hardware
   * PostgreSQL related products

Of course, proposals for talks on other PostgreSQL related topics will 
also be gladly accepted.A limited number of longer, 60-minute, slots 
will be available, especially aimed at practical tutorials. Please 
indicate clearly in your submission if you wish to make a 60-minute talk.


It is also possible for you to give a tutorial on advanced use, 
administration or development of a PostgreSQL cluster. Every tutorial 
must have a live demo, didactic material for attendees and have to be no 
more long than 60 minutes. All the material required for the tutorial 
itself (e.g., slides, code snippets, howtos, etc.) are on the lecturer(s).


Finally, there will be a session of five minute lightning talks. A 
separate call for proposals will be made for these further on.


The submission deadline is October 24th, 2010. Selected speakers will be 
notified on October 27th, 2010. Please email your proposals to 
pgday2...@itpug.org , and include the 
following information:


   * Name
   * Company (if applicable)
   * Biography (a paragraph about you and your involvement with PostgreSQL)
   * Presentation title
   * Presentation abstract
   * Specific requests

The proposals will be considered by a committee that will finalise the 
schedule to be published nearer the conference date. If your proposal 
has been accepted, you will be informed by email.


We look forward to hearing from you, and seeing you in Rome in December!

Gabriele Bartolini
Italian PostgreSQL Users Group (ITPUG), President

Re: [HACKERS] WIP: Triggers on VIEWs

[Tom Lane]

Bernd Helmle  writes:
> I would like to do some more tests/review, but going to mark this patch as 
> "Ready for Committer", so that someone more qualified on the executor part 
> can have a look on it during this commitfest, if that's okay for us?

I've started looking at this patch now.  I think it would have been best
submitted as two patches: one to add the SQL-spec "INSTEAD OF" trigger
functionality, and a follow-on to extend INSTEAD OF triggers to views.
I'm thinking of breaking it apart into two separate commits along that
line, mainly because I think INSTEAD OF is probably committable but
I'm much less sure about the other part.

In the INSTEAD OF part, the main gripe I've got is the data
representation choice:

***
*** 1609,1614 
--- 1609,1615 
  List   *funcname;   /* qual. name of function to call */
  List   *args;   /* list of (T_String) Values or NIL */
  boolbefore; /* BEFORE/AFTER */
+ boolinstead;/* INSTEAD OF (overrides BEFORE/AFTER) */
  boolrow;/* ROW/STATEMENT */
  /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */
  int16   events; /* INSERT/UPDATE/DELETE/TRUNCATE */

This pretty much sucks, because not only is this a rather confusing
definition, it's not really backwards compatible: any code that thinks
"!before" must mean "after" is going to be silently broken.  Usually,
when you do something that necessarily breaks old code, what you want
is to make sure the breakage is obvious at compile time.  I think the
right thing here is to replace "before" with a three-valued enum,
perhaps called "timing", so as to force people to take another look
at any code that touches the field directly.

Although we already have macros TRIGGER_FIRED_AFTER/TRIGGER_FIRED_BEFORE
that seem to mask the details here, the changes you had to make in
contrib illustrate that the macros' callers could still be embedding this
basic mistake of testing "!before" when they mean "after" or vice versa.
I wonder whether we should intentionally rename the macros to force
people to take another look at their logic.  Or is that going too far?
Comments anyone?

regards, tom lane

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

[HACKERS] Bug in information_schema: column names don't match spec

[Tom Lane]

While reading the proposed triggers-on-views patch I noticed that
information_schema.sql defines these columns in the TRIGGERS view:

   CAST(
 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' WHEN t.tgtype & 64 = 64 
THEN 'INSTEAD OF' ELSE 'AFTER' END
 AS character_data) AS condition_timing,
   CAST(null AS sql_identifier) AS condition_reference_old_table,
   CAST(null AS sql_identifier) AS condition_reference_new_table,
   CAST(null AS sql_identifier) AS condition_reference_old_row,
   CAST(null AS sql_identifier) AS condition_reference_new_row,

In SQL:2008 I see the corresponding columns named as 

ACTION_TIMING, ACTION_REFERENCE_OLD_TABLE,
ACTION_REFERENCE_NEW_TABLE, ACTION_REFERENCE_OLD_ROW,
ACTION_REFERENCE_NEW_ROW,

Was this just a thinko in our transcription, or did they rename the view
columns somewhere along the line?

Perhaps more to the point, what shall we do about it?  Just renaming the
view columns is easy enough, but should we try to do something to limit
the impact on existing clients?

regards, tom lane

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 04:48 PM, Fujii Masao wrote:
> I believe many systems require write-availability.

Sure. Make sure you have enough standbies to fail over to.

(I think there are even more situations where read-availability is much
more important, though).

>> Start with 0 (i.e. replication off), then add standbies, then increase
>> quorum_commit to your new requirements.
> 
> No. This only makes the procedure of failover more complex.

Huh? This doesn't affect fail-over at all. Quite the opposite, the
guarantees and requirements remain the same even after a fail-over.

> What is a full-cluster crash?

The event that all of your cluster nodes are down (most probably due to
power failure, but fires or other catastrophic events can be other
causes). Chances for that to happen can certainly be reduced by
distributing to distant locations, but that equally certainly increases
latency, which isn't always an option.

> Why does it cause a split-brain?

First master node A fails, a standby B takes over, but then fails as
well. Let node C take over. Then the power aggregates catches fire, the
infamous full-cluster crash (where "lights out management" gets a
completely new meaning ;-) ).

Split brain would be the situation that arises if all three nodes (A, B
and C) start up again and think they have been the former master, so
they can now continue to apply new transactions. Their data diverges,
leading to what could be seen as a split-brain from the outside.

Obviously, you must disallow A and B to take the role of the master
after recovery. Ideally, C would continue as the master. However, if the
fire destroyed node C, let's hope you had another (sync!) standby that
can act as the new master. Otherwise you've lost data.

Hope that explains it. Wikipedia certainly provides a better (and less
Postgres colored) explanation.

Regards

Markus Wanner

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

Re: [HACKERS] Sync Replication with transaction-controlled durability

[Simon Riggs]

On Tue, 2010-09-14 at 18:48 +0100, Simon Riggs wrote:

> I'm working on a patch to implement synchronous replication for
> PostgreSQL, with user-controlled durability specified on the master. The
> design also provides high throughput by allowing concurrent processes to
> handle the WAL stream. The proposal requires only 3 new parameters and
> takes into account much community feedback on earlier ideas.

I'm now implementing v5, which simplifies the parameters still further

USERSET on master
* synchronous_replication = off (default) | on
* synchronous_replication_timeout >=0 default=0 means wait forever

set in postgresql.conf on standby
* synchronous_replication_service = on (default) | off

WALwriter is not active, nor are multiple sync rep modes available.
Coding allows us to extend number of modes in future.

Coding also solves problem raised by Dimitri: we don't advertise the
sync rep service until the standby has caught up.

This patch is a rough WIP, mostly stripping out and streamlining. It
doesn't work yet, but people say they like to see me working, so here
'tis.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


syncrep5.1.patch.bz2
Description: application/bzip

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 04:47 PM, Simon Riggs wrote:
> Yes, I really want to avoid such issues and likely complexities we get
> into trying to solve them. In reality they should not be common because
> it only happens if the sysadmin has not configured sufficient number of
> redundant standbys.

Well, full cluster outages are infrequent, but sadly cannot be avoided
entirely. (Murphy's laughing). IMO we should be prepared to deal with
those. Or am I understanding you wrongly here?

> I don't
> think we should be spending too much time trying to help people that say
> they want additional durability guarantees but do not match that with
> sufficient hardware resources to make it happen smoothly.

I fully agree to that statement.

Regards

Markus Wanner

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

Re: [HACKERS] Git cvsserver serious issue

[Andrew Dunstan]

On 10/08/2010 10:53 AM, Aidan Van Dyk wrote:

On Fri, Oct 8, 2010 at 8:13 AM, Andrew Dunstan  wrote:


That's what the default SQLite setup does anyway. The only difference here
is that we are leveraging the fact that with the Pg driver the database must
first exist. Of course, with Pg the database can live on a different host or
in a server run on a different port, if you need to avoid naming conflicts.

That can be done in SQLite to, just set the "database name" to a path
where there is no create access.  Pre-create the database, and don't
let SQLite "create" new ones every time they are accessed.  Standard
unix permissins should easily allow that setup.  chmod -w on the
directory the database files go in.


*shrug*. We are the PostgreSQL project after all :-) But whatever Magnus 
wants to do is OK with me.


cheers

andrew


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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 23:55 +0900, Fujii Masao wrote:
> On Fri, Oct 8, 2010 at 6:00 PM, Simon Riggs  wrote:
> > >From the perspective of an observer, randomly selecting a standby for
> > load balancing purposes: No, they are not guaranteed to see the "latest"
> > answer, nor even can they find out whether what they are seeing is the
> > latest answer.
> 
> To guarantee that each standby returns the same result, we would need to
> use the cluster-wide snapshot to run queries. IIRC, Postgres-XC provides
> that feature. Though I'm not sure if it can be applied in HS/SR.

That is my understanding.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Fujii Masao]

On Fri, Oct 8, 2010 at 6:00 PM, Simon Riggs  wrote:
> >From the perspective of an observer, randomly selecting a standby for
> load balancing purposes: No, they are not guaranteed to see the "latest"
> answer, nor even can they find out whether what they are seeing is the
> latest answer.

To guarantee that each standby returns the same result, we would need to
use the cluster-wide snapshot to run queries. IIRC, Postgres-XC provides
that feature. Though I'm not sure if it can be applied in HS/SR.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Git cvsserver serious issue

[Aidan Van Dyk]

On Fri, Oct 8, 2010 at 8:13 AM, Andrew Dunstan  wrote:

> That's what the default SQLite setup does anyway. The only difference here
> is that we are leveraging the fact that with the Pg driver the database must
> first exist. Of course, with Pg the database can live on a different host or
> in a server run on a different port, if you need to avoid naming conflicts.

That can be done in SQLite to, just set the "database name" to a path
where there is no create access.  Pre-create the database, and don't
let SQLite "create" new ones every time they are accessed.  Standard
unix permissins should easily allow that setup.  chmod -w on the
directory the database files go in.

a.

-- 
Aidan Van Dyk                                             Create like a god,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

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

Re: [HACKERS] Issues with Quorum Commit

[Fujii Masao]

On Fri, Oct 8, 2010 at 5:16 PM, Markus Wanner  wrote:
> On 10/08/2010 05:41 AM, Fujii Masao wrote:
>> But, even with quorum commit, if you choose wait-forever option,
>> failover would decrease availability. Right after the failover,
>> no standby has connected to new master, so if quorum >= 1, all
>> the transactions must wait for a while.
>
> That's a point, yes. But again, this is just write-availability, you can
> happily read from all active standbies.

I believe many systems require write-availability.

>> Basically we need to take a base backup from new master to start
>> the standbys and make them connect to new master. This might take
>> a long time. Since transaction commits cannot advance for that time,
>> availability would goes down.
>
> Just don't increase your quorum_commit to unreasonable values which your
> hardware cannot possible satisfy. It doesn't make sense to set a
> quorum_commit of 1 or even bigger, if you don't already have a standby
> attached.
>
> Start with 0 (i.e. replication off), then add standbies, then increase
> quorum_commit to your new requirements.

No. This only makes the procedure of failover more complex.

>> Or you think that wait-forever option is applied only when the
>> standby goes down?
>
> That wouldn't work in case of a full-cluster crash, where the
> wait-forever option is required again. Otherwise you risk a split-brain
> situation.

What is a full-cluster crash? Why does it cause a split-brain?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 10:11 -0400, Tom Lane wrote:

> 1. a unique identifier for each standby (not just role names that
> multiple standbys might share);

That is difficult because each standby is identical. If a standby goes
down, people can regenerate a new standby by taking a copy from another
standby. What number do we give this new standby?...

> 2. state on the master associated with each possible standby -- not just
> the ones currently connected.
> 
> Both of those are perhaps possible, but the sense I have of the
> discussion is that people want to avoid them.

Yes, I really want to avoid such issues and likely complexities we get
into trying to solve them. In reality they should not be common because
it only happens if the sysadmin has not configured sufficient number of
redundant standbys.

My proposed design is that the timeout does not cause the standby to be
"marked as degraded". It is up to the user to decide whether they wait,
or whether they progress without sync rep. Or sysadmin can release the
waiters via a function call.

If the cluster does become degraded the sysadmin just generates a new
standby and plugs in back into the cluster and away we go. Simple, no
state to be recorded and no state to get screwed up either. I don't
think we should be spending too much time trying to help people that say
they want additional durability guarantees but do not match that with
sufficient hardware resources to make it happen smoothly.

If we do try to tackle those problems who will be able to validate our
code actually works?

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 04:38 PM, Tom Lane wrote:
> Markus Wanner  writes:
>> IIUC you seem to assume that the master node keeps its master role. But
>> users who value availability a lot certainly want automatic fail-over,
> 
> Huh?  Surely loss of the slaves shouldn't force a failover.  Maybe the
> slaves really are all dead.

I think we are talking across each other. I'm speaking about the need to
be able to fail-over to a standby in case the master fails.

In case of a full-cluster crash after such a fail-over, you need to take
care you don't enter split brain. Some kind of STONITH, lamport clock,
or what not. Figuring out which node has been the most recent (and thus
most up to date) master is far from trivial.

(See also my mail in answer to Dimitri a few minutes ago).

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Tom Lane]

Markus Wanner  writes:
> On 10/08/2010 04:11 PM, Tom Lane wrote:
>> Actually, #2 seems rather difficult even if you want it.  Presumably
>> you'd like to keep that state in reliable storage, so it survives master
>> crashes.  But how you gonna commit a change to that state, if you just
>> lost every standby (suppose master's ethernet cable got unplugged)?

> IIUC you seem to assume that the master node keeps its master role. But
> users who value availability a lot certainly want automatic fail-over,

Huh?  Surely loss of the slaves shouldn't force a failover.  Maybe the
slaves really are all dead.

regards, tom lane

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

[HACKERS] Total memory allocated to postgres

[vamsi krishna]

Hi

I want to know how much main memory  is actually allocated to be the maximum
memory for postgresql, because my hardware may have 4GB main memory but at
the end of the day, only 1GB may be allocated for postgres.

I want to set (increase) it manually. Can someone tell me how to do it?

In aset.c we have a function
AllocSetContextCreate(MemoryContext parent,const char *name,Size
minContextSize,Size initBlockSize,Size maxBlockSize) defn.

maxBlockSize is initialized to 8MB in mcxt.c where AllocSetContextCreate()
is called. If I increase this value to 80 MB or so, will it increase the
memory allocated to postgresql or will it just increase the block size?

OR

do I need to change some parameter in postgresql.conf file in data directory
in pgsql folder?

Thanks
Vamsi

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 12:05 PM, Dimitri Fontaine wrote:
> Markus Wanner  writes:
>> ..and a whole lot of manual work, that's prone to error for something
>> that could easily be automated
> 
> So, the master just crashed, first standby is dead and second ain't in
> sync. What's the easy and automated way out? Sorry, I need a hand here.

Thinking this through, I'm realizing that this can potentially work
automatically with three nodes in both cases. Each node needs to keep
track of whether or not it is (or became) the master - and when (lamport
timestamp, maybe, not necessarily wall clock). A new master might
continue to commit new transactions after a fail-over, without the old
master being able to record that fact (because it's down).

This means there's a different requirement after a full-cluster crash
(i.e. master failure and no up-to-date standby is available). With the
timeout, you absolutely need the former master to come back up again for
zero data loss, no matter what your quorum_commit setting was. To be
able to automatically tell who was the most recent master, you need to
query the state of all other nodes, because they could be a more recent
master. If that's not possible (or not feasible, because the replacement
part isn't currently available), you are at risk of data loss.

With the given three node scenario, the zero data loss guarantee only
holds true as long as either at least one node (that is in sync) is
running or if you can recover the former master after a full cluster crash.

When waiting forever, you only need one of the k nodes to come back up
again. You also need to query other nodes to find out which the k of N
nodes are, but being able to recovery (N - k + 1) nodes is sufficient to
figure that out. So any (k-1) nodes may fail, even permanently, at any
point in time, and you are still not at risk of losing data. (Nor at
risk of losing availability, BTW). I'm still of the opinion that that's
the way easier and clearer guarantee.

Also note that with higher values for N, this gets more and more
important, because the chance to be able to recovery all N nodes after a
full crash shrinks with increasing N (while the time required to do so
increases). But maybe the current sync rep feature doesn't need to
target setups with that many nodes.

I certainly agree that either way is complicated to implement. With
Postgtres-R, I'm clearly going the way that's able to satisfy large
numbers of nodes.

Thanks for an interesting discussion. And for respectful disagreement.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Dimitri Fontaine]

Tom Lane  writes:
> Well, actually, that's *considerably* more complicated than just a
> timeout.  How are you going to "mark the standby as degraded"?  The
> standby can't keep that information, because it's not even connected
> when the master makes the decision.  ISTM that this requires
>
> 1. a unique identifier for each standby (not just role names that
> multiple standbys might share);
>
> 2. state on the master associated with each possible standby -- not just
> the ones currently connected.
>
> Both of those are perhaps possible, but the sense I have of the
> discussion is that people want to avoid them.

What we'd like to avoid is for the users to have to cope with such
needs. Now, if that's internal to the code and automatic, that's not the
same thing at all.

What I'd have in mind is a "Database standby system identifier" that
would be part of the initial hand shake in the replication protocol. And
a system function to be able to "unregister" the standby.

> Actually, #2 seems rather difficult even if you want it.  Presumably
> you'd like to keep that state in reliable storage, so it survives master
> crashes.  But how you gonna commit a change to that state, if you just
> lost every standby (suppose master's ethernet cable got unplugged)?

I don't see that as a huge problem myself, because I'm already well sold
to the per-transaction replication-synchronous behaviour. So any change
done there by the master would be hard-coded as async. What I'm missing?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 04:11 PM, Tom Lane wrote:
> Actually, #2 seems rather difficult even if you want it.  Presumably
> you'd like to keep that state in reliable storage, so it survives master
> crashes.  But how you gonna commit a change to that state, if you just
> lost every standby (suppose master's ethernet cable got unplugged)?

IIUC you seem to assume that the master node keeps its master role. But
users who value availability a lot certainly want automatic fail-over,
so any node can potentially be the new master.

After recovery from a full-cluster outage, the first question is which
node was the most recent master (or which former standby is up to date
and could take over).

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Fujii Masao]

On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
 wrote:
> Do we really need that?

Yes. But if there is no unsent WAL when the master goes down,
we can start new standby without new backup by copying the
timeline history file from new master to new standby and
setting recovery_target_timeline to 'latest'. In this case,
new standby advances the recovery to the latest timeline ID
which new master uses before connecting to the master.

This seems to have been successful in my test environment.
Though I'm missing something.

> I don't think that's acceptable, we'll need to fix
> that if that's the case.

Agreed.

> You can cross timelines with the archive, though. But IIRC there was some
> issue with that too, you needed to restart the standbys because the standby
> scans what timelines exist at the beginning of recovery, and won't notice
> new timelines that appear after that?

Yes.

> We need to address that, apart from any of the other things discussed wrt.
> synchronous replication. It will benefit asynchronous replication too. IMHO
> *that* is the next thing we should do, the next patch we commit.

You mean to commit that capability before synchronous replication? If so,
I disagree with you. I think that it's not easy to address that problem.
So I'm worried about that implementing that capability first means the miss
of sync rep in 9.1.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] a few small bugs in plpgsql

[Pavel Stehule]

2010/10/8 Tom Lane :
> Pavel Stehule  writes:
>> Now I understand to interpretation. But there is little bit difficult
>> to understand to error message. Can be message enhanced to show a
>> complete expression?
>
> It does already:
>
> regression=# select foo();
> ERROR:  column "label" does not exist
> LINE 2:   <>
>            ^
> QUERY:  SELECT 10

the keyword QUERY is misleading :(

but you have a true - there are all necessary information.

Regards

Pavel Stehule


>  <>
> CONTEXT:  PL/pgSQL function "foo" line 3 at FOR with integer loop variable
>
> The "query" is SELECT followed by whatever plpgsql thought the
> expression was.
>
>                        regards, tom lane
>

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

Re: [HACKERS] proposal: plpgsql, solution for derivated types of parameters

[Pavel Stehule]

2010/10/8 Tom Lane :
> Pavel Stehule  writes:
>> the current plpgsql syntax doesn't offer a functionality to define
>> some variable with type as element of some other array variable or
>> reverse order. The primary goal of this proposal is enahancing plpgsql
>> for better working with polymorphic types.
>
> I haven't seen any actual field complaints that would be solved by this.
> How often is it really going to be useful?
>
>> DECLARE elementvar IS ELEMENT OF arrayvar;
>> DECLARE arrayvar IS ARRAY OF varname;
>
> Both of these notations seem pretty inconsistent with the rest of
> plpgsql, as well as being dead ends when you think about extensions to
> more complex situations like arrays of records or records of arrays.
> I'd expect to do the former with something like "arrayvar[0]%type" and

uff  "arrayvar[0]%type" should be in plpgsql spirit? There is nothing
similar in Ada, PL/SQL or languages similar to modula. I don't see why
my proposal cannot work with more complex types. This is maybe C
construct - it must not be used for plpgsql - subscripts can start
with other number than zero.

DECLARE arrayvar IS ARRAY OF sometype

or

DECLARE arrayvar IS ARRAY OF somevar%type;

possible

DECLARE arrayvar IS ARRAY OF somerec.field%type;

This syntax is similar to Ada language.



> the latter with "var%type[]".  Possibly some parentheses would be needed
> to make this non-ambiguous.
>
>                        regards, tom lane
>

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

Re: [HACKERS] Issues with Quorum Commit

[Tom Lane]

Greg Smith  writes:
> I don't see this as needing any implementation any more complicated than 
> the usual way such timeouts are handled.  Note how long you've been 
> trying to reach the standby.  Default to -1 for forever.  And if you hit 
> the timeout, mark the standby as degraded and force them to do a proper 
> resync when they disconnect.  Once that's done, then they can re-enter 
> sync rep mode again, via the same process a new node would have done so.

Well, actually, that's *considerably* more complicated than just a
timeout.  How are you going to "mark the standby as degraded"?  The
standby can't keep that information, because it's not even connected
when the master makes the decision.  ISTM that this requires

1. a unique identifier for each standby (not just role names that
multiple standbys might share);

2. state on the master associated with each possible standby -- not just
the ones currently connected.

Both of those are perhaps possible, but the sense I have of the
discussion is that people want to avoid them.

Actually, #2 seems rather difficult even if you want it.  Presumably
you'd like to keep that state in reliable storage, so it survives master
crashes.  But how you gonna commit a change to that state, if you just
lost every standby (suppose master's ethernet cable got unplugged)?
Looks to me like it has to be reliable non-replicated storage.  Leaving
aside the question of how reliable it can really be if not replicated,
it's still the case that we have noplace to put such information given
the WAL-is-across-the-whole-cluster design.

regards, tom lane

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

Re: [HACKERS] proposal: plpgsql, solution for derivated types of parameters

[Tom Lane]

Pavel Stehule  writes:
> the current plpgsql syntax doesn't offer a functionality to define
> some variable with type as element of some other array variable or
> reverse order. The primary goal of this proposal is enahancing plpgsql
> for better working with polymorphic types.

I haven't seen any actual field complaints that would be solved by this.
How often is it really going to be useful?

> DECLARE elementvar IS ELEMENT OF arrayvar;
> DECLARE arrayvar IS ARRAY OF varname;

Both of these notations seem pretty inconsistent with the rest of
plpgsql, as well as being dead ends when you think about extensions to
more complex situations like arrays of records or records of arrays.
I'd expect to do the former with something like "arrayvar[0]%type" and
the latter with "var%type[]".  Possibly some parentheses would be needed
to make this non-ambiguous.

regards, tom lane

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

Re: [HACKERS] Issues with Quorum Commit

[Fujii Masao]

On Fri, Oct 8, 2010 at 5:07 PM, Markus Wanner  wrote:
> On 10/08/2010 04:01 AM, Fujii Masao wrote:
>> Really? I don't think that ko-count=0 means "wait-forever".
>
> Telling from the documentation, I'd also say it doesn't wait forever by
> default. However, please note that there are different parameters for
> the initial wait for connection during boot up (wfc-timeout and
> degr-wfc-timeout). So you might to test what happens on a node failure,
> not just absence of a standby.

Unfortunately I've already taken down my DRBD environment. As far as
I heard from my colleague who is familiar with DRBD, standby node
failure doesn't prevent the master from writing data to the DRBD disk
by default. If there is DRBD environment available around me, I'll try
the test.

And, I'd like to know whether the master waits forever because of the
standby failure in other solutions such as Oracle DataGuard, MySQL
semi-synchronous replication.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] a few small bugs in plpgsql

[Tom Lane]

Pavel Stehule  writes:
> Now I understand to interpretation. But there is little bit difficult
> to understand to error message. Can be message enhanced to show a
> complete expression?

It does already:

regression=# select foo();
ERROR:  column "label" does not exist
LINE 2:   <>
^
QUERY:  SELECT 10
  <>
CONTEXT:  PL/pgSQL function "foo" line 3 at FOR with integer loop variable

The "query" is SELECT followed by whatever plpgsql thought the
expression was.

regards, tom lane

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

Re: [HACKERS] Timeout and Synch Rep

[Thom Brown]

On 7 October 2010 20:50, Josh Berkus  wrote:
> All,
> So, if something happens to the standby, and it cannot return an ack in
> 30 seconds, they would like it to degrade to asynch mode.  At that
> point, they would also like to trigger a nagios alert which will wake up
> the sysadmin with flashing red lights.

How?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

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

Re: [HACKERS] Timeout and Synch Rep

[Fujii Masao]

On Fri, Oct 8, 2010 at 4:50 AM, Josh Berkus  wrote:
> In my effort to make the discussion around the design decisions of synch
> rep less opaque, I'm starting a separate thread about what has developed
> to be one of the more contentious issues.
>
> I'm going to champion timeouts because I plan to use them.  In fact, I
> plan to deploy synch rep with a timeout if it's available within 2 weeks
> of 9.1 being released.  Without a timeout (i.e. "wait forever" is the
> only mode), that project will probably never use synch rep.
>
> Let me give you my use-case so that you can understand why I want a timeout.
>
> Client is a telecommunications service provider.  They have a primary
> server and a failover server for data updates.  They also have two async
> slaves on older machines for reporting purposes.   The failover
> currently does NOT accept any queries in order to keep it as current as
> possible.
>
> They would like the failover to be synchronous so that they can
> guarentee no data loss in the event of a master failure.  However, zero
> data loss is less important to them than uptime ... they have a five9's
> SLA with their clients, and the hardware on the master is very good.
>
> So, if something happens to the standby, and it cannot return an ack in
> 30 seconds, they would like it to degrade to asynch mode.  At that
> point, they would also like to trigger a nagios alert which will wake up
> the sysadmin with flashing red lights.  Once he has resolved the
> problem, he would like to promote the now-asynch standby back to synch
> standby.
>
> Yes, this means that, in the event of a standby failure, they have a
> window where any failure on the master will mean data loss.  The user
> regards this risk as acceptable, given that both the master and the
> failover are located in the same data center in any case, so there is
> always a risk of a sufficient disaster wiping out all data back to the
> daily backup.

This explains very well why some systems require the timeout.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

Re: [HACKERS] Git cvsserver serious issue

[Andrew Dunstan]

On 10/08/2010 09:15 AM, Marko Kreen wrote:

On Fri, Oct 8, 2010 at 3:13 PM, Andrew Dunstan  wrote:

On 10/08/2010 02:09 AM, Magnus Hagander wrote:

On Fri, Oct 8, 2010 at 03:52, Andrew Dunstanwrote:

There's a simpler solution which I have just tested. Instead of patching,
use the Pg driver instead of SQLite. Set the dbname to %m. If the
database
doesn't exist the cvs checkout will fail. So we just set up databases for
the modules we want to export (master and RELn_m_STABLE for the live
branches).

Wouldn't it be simpler be to generate hourly tarball on some host and wget it?
It can be generated even more often, as no history need to be kept.

Considering the state of cvsserver, can you be certain that whatever
is coming from it is really the most recent code?


Sure you can, why not? It will be coming from the same git repo that 
servers git requests. git-cvsserver doesn't create a new CVS repo, it 
emulates CVS from a git repo.


cheers

andrew

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

Re: [HACKERS] Git cvsserver serious issue

[Marko Kreen]

On Fri, Oct 8, 2010 at 3:13 PM, Andrew Dunstan  wrote:
> On 10/08/2010 02:09 AM, Magnus Hagander wrote:
>> On Fri, Oct 8, 2010 at 03:52, Andrew Dunstan  wrote:
>>> There's a simpler solution which I have just tested. Instead of patching,
>>> use the Pg driver instead of SQLite. Set the dbname to %m. If the
>>> database
>>> doesn't exist the cvs checkout will fail. So we just set up databases for
>>> the modules we want to export (master and RELn_m_STABLE for the live
>>> branches).

Wouldn't it be simpler be to generate hourly tarball on some host and wget it?
It can be generated even more often, as no history need to be kept.

Considering the state of cvsserver, can you be certain that whatever
is coming from it is really the most recent code?

-- 
marko

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

Re: [HACKERS] standby registration (was: is sync rep stalled?)

[Robert Haas]

On Fri, Oct 8, 2010 at 4:29 AM, Yeb Havinga  wrote:
> Robert Haas wrote:
>>
>> Yes, let's please just implement something simple and get it
>> committed.  k = 1.  Two GUCs (synchronous_standbys = name, name, name
>> and synchronous_waitfor = none|recv|fsync|apply), SUSET so you can
>> change it per txn.  Done.  We can revise it *the day after it's
>> committed* if we agree on how.  And if we *don't* agree, then we can
>> ship it and we still win.
>>
>
> I like the idea of something simple committed first, and am trying to
> understand what's said above.
>
> k = 1 : wait for only one ack
> two gucs: does this mean configurable in postgresql.conf at the master, and
> changable with SET commands on the master depending on options? Are both
> gucs mutable?
> synchronous_standbys: I'm wondering if this registration is necessary in
> this simple setup. What are the named used for? Could they be removed?
> Should they also be configured at each standby?
> synchronous_waitfor: If configured on the master, how is it updated to the
> standbys? What does being able to configure 'none' mean? k = 0? I smell a
> POLA violation here.

Well, there's got to be some way to turn synchronous replication off.
The obvious methods are to allow synchronous_standbys to be set to
empty or to allow synchronous_waitfor to be set to none.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Git cvsserver serious issue

[Andrew Dunstan]

On 10/08/2010 02:09 AM, Magnus Hagander wrote:

On Fri, Oct 8, 2010 at 03:52, Andrew Dunstan  wrote:


There's a simpler solution which I have just tested. Instead of patching,
use the Pg driver instead of SQLite. Set the dbname to %m. If the database
doesn't exist the cvs checkout will fail. So we just set up databases for
the modules we want to export (master and RELn_m_STABLE for the live
branches).

A database per branch seems like a horrible idea in general, but if it
works us around the bug, it seems like a doable idea.. As long as
we'll never have a branch called "postgres" or "git" (already in use
on that box).

I'll look into it.


That's what the default SQLite setup does anyway. The only difference 
here is that we are leveraging the fact that with the Pg driver the 
database must first exist. Of course, with Pg the database can live on a 
different host or in a server run on a different port, if you need to 
avoid naming conflicts.


cheers

andrew

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

Re: [HACKERS] On Scalability

[Vincenzo Romano]

2010/10/7 Simon Riggs :
> On Thu, 2010-10-07 at 14:10 +0200, Vincenzo Romano wrote:
>
>> Making these things sub-linear (whether not O(log n) or even O(1) ),
>> provided that there's  way to, would make this RDBMS more appealing
>> to enterprises.
>> I mean also partial indexes (as an alternative to table partitioning).
>> Being able to effectively cope with "a dozen child tables or so" it's more
>> like an amateur feature.
>> If you really need partitioning (or just hierarchical stuff) I think you'll 
>> need
>> for quite more than a dozen items.
>> If you partition by just weeks, you'll need 50+ a year.
>>
>> Is there any precise direction to where look into the code for it?
>>
>> Is there a way to put this into a wish list?
>
> It's already on the wish list ("TODO") and has been for many years.
>
> We've mostly lacked somebody with the experience and time/funding to
> complete that implementation work. I figure I'll be doing it for 9.2
> now; it may be difficult to do this for next release.
>
> Theoretically, this can be O(n.log n) for range partitioning and O(1)
> for exact value partitioning, though the latter isn't a frequent use
> case.
>
> Your conclusion that the current partitioning only works with a dozen or
> so items doesn't match the experience of current users however.
>
> --
>  Simon Riggs           www.2ndQuadrant.com
>  PostgreSQL Development, 24x7 Support, Training and Services
>

Do the same conclusions apply to partial indexes?
I mean, if I have a large number (n>=100 or n>=1000) of partial indexes
on a single very large table (m>=10**12), how good is the planner to choose the
right indexes to plan a query?
Has also this algorithm superlinear complexity?


-- 
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS

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

Re: [HACKERS] Issues with Quorum Commit

[Dimitri Fontaine]

Markus Wanner  writes:
> ..and a whole lot of manual work, that's prone to error for something
> that could easily be automated

So, the master just crashed, first standby is dead and second ain't in
sync. What's the easy and automated way out? Sorry, I need a hand here.

-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 11:41 AM, Dimitri Fontaine wrote:
> Same old story. Either you're able to try and fix the master so that you
> don't lose any data and don't even have to check for that, or you take a
> risk and start from a non synced standby. It's all availability against
> durability again.

..and a whole lot of manual work, that's prone to error for something
that could easily be automated, at certainly less than 2000 EUR initial,
additional cost (if any at all, in case you already have three servers).
Sorry, I still fail to understand that use case.

It reminds me of the customer that wanted to save the cost of the BBU
and ran with fsync=off. Until his server got down due to a power outage.

But yeah, we provide that option as well, yes. Point taken.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Dimitri Fontaine]

Markus Wanner  writes:
> ..and how do you make sure you are not marking your second standby as
> degraded just because it's currently lagging? 

Well, in sync rep, a standby that's not able to stay under the timeout
is degraded. Full stop. The presence of the timeout (or its value not
being -1) means that the admin has chosen this definition.

> Effectively degrading the
> utterly needed one, because your first standby has just bitten the
> dust?

Well, now you have a worst case scenario: first standby is dead and the
remaining one was not able to keep up. You have lost all your master's
failover replacements.

> And how do you prevent the split brain situation in case the master dies
> shortly after these events, but fails to come up again immediately?

Same old story. Either you're able to try and fix the master so that you
don't lose any data and don't even have to check for that, or you take a
risk and start from a non synced standby. It's all availability against
durability again.

What I really want us to be able to provide is the clear facts so that
whoever has to take the decision is able to. Meaning, here, that it
should be easy to see that neither the standby are in sync at this
point.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

[HACKERS] compiling C library under mingw

[Marios Vodas]

I have a library that compiles fine under linux.
But when I try to compile it under mingw on windows 7 I get the following
errors.

gcc.exe -c -O2 -I/C/PostgreSQL/9.0/include/server
> -I/C/PostgreSQL/9.0/include/server/utils
> -I/C/PostgreSQL/9.0/include/server/access
> -I/C/PostgreSQL/9.0/include/server/port/win32 -I/C/PostgreSQL/9.0/include
> -MMD -MP -MF build/Release/MinGW-Windows/mylib.o.d -o
> build/Release/MinGW-Windows/mylib.o mylib.c
>

The previous command is successful and produces .o files (only some warnings
here like: warning: 'pg_finfo_mytype_in' redeclared without dllimport
attribute: previous dllimport ignored).
The following though gives errors.

gcc.exe -shared -o dist/Release/MinGW-Windows/libMyLib.dll
> build/Release/MinGW-Windows/mylib.o
>

Here is the log:

build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x128): undefined
> reference to `errstart'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x13d): undefined
> reference to `_imp__CurrentMemoryContext'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x147): undefined
> reference to `MemoryContextAlloc'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x17b): undefined
> reference to `timestamp_in'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x180): undefined
> reference to `DirectFunctionCall3'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x1be): undefined
> reference to `timestamp_in'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x1c3): undefined
> reference to `DirectFunctionCall3'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x1f4): undefined
> reference to `errmsg'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x202): undefined
> reference to `errcode'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x20e): undefined
> reference to `errfinish'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x230): undefined
> reference to `_imp__CurrentMemoryContext'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x23a): undefined
> reference to `MemoryContextAlloc'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x251): undefined
> reference to `Int64GetDatum'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x25c): undefined
> reference to `timestamp_out'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x261): undefined
> reference to `DirectFunctionCall1'
> build/Release/MinGW-Windows/mylib.o:mylib.c:(.text+0x284): undefined
> reference to `Int64GetDatum'
>

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 11:00 AM, Simon Riggs wrote:
> From the perspective of an observer, randomly selecting a standby for
> load balancing purposes: No, they are not guaranteed to see the "latest"
> answer, nor even can they find out whether what they are seeing is the
> latest answer.

I completely agree. The application (or at least the load balancer)
needs to be aware of that fact.

Regards

Markus

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 01:44 AM, Greg Smith wrote:
> They'll use Sync Rep to maximize
> the odds a system failure doesn't cause any transaction loss.  They'll
> use good quality hardware on the master so it's unlikely to fail.

.."unlikely to fail"?

Ehm.. is that you speaking, Greg? ;-)

> But
> when the database finds the standby unreachable, and it's left with the
> choice between either degrading into async rep or coming to a complete
> halt, you must give people the option of choosing to degrade instead
> after a timeout.  Let them set off the red flashing lights, sound the
> alarms, and pray the master doesn't go down until you can fix the
> problem.

Okay, okay, fair enough - if there had been red flashing lights. And
alarms. And bells and whistles. But that's what I'm afraid the timeout
is removing.

> I don't see this as needing any implementation any more complicated than
> the usual way such timeouts are handled.  Note how long you've been
> trying to reach the standby.  Default to -1 for forever.  And if you hit
> the timeout, mark the standby as degraded

..and how do you make sure you are not marking your second standby as
degraded just because it's currently lagging? Effectively degrading the
utterly needed one, because your first standby has just bitten the dust?

And how do you prevent the split brain situation in case the master dies
shortly after these events, but fails to come up again immediately?

Your list of data recovery projects will get larger and the projects
more complicated. Because there's a lot more to it than just the
implementation of a timeout.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 11:27 +0300, Heikki Linnakangas wrote:
> On 08.10.2010 11:25, Simon Riggs wrote:
> > On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:
> >>>
> >>> Or what kind of customers do you think really need a no-lag solution for
> >>> read-only queries? In the LAN case, the lag of async rep is negligible
> >>> and in the WAN case the latencies of sync rep are prohibitive.
> >>
> >> There is a very good use case for that particular set up, actually. If
> >> your hot standby is guaranteed to be up-to-date with any transaction
> >> that has been committed in the master, you can use the standby
> >> interchangeably with the master for read-only queries.
> >
> > This is an important point. It is desirable, but there is no such thing.
> > We must not take any project decisions based upon that false premise.
> >
> > Hot Standby is never guaranteed to be up-to-date with master. There is
> > no such thing as certainty that you have the same data as the master.
> 
> Synchronous replication in the 'replay' mode is supposed to guarantee 
> exactly that, no?

>From the perspective of the person making the change on the master: yes.
If they make the change, wait for commit, then check the value on a
standby, yes it will be there (or a later version).

>From the perspective of an observer, randomly selecting a standby for
load balancing purposes: No, they are not guaranteed to see the "latest"
answer, nor even can they find out whether what they are seeing is the
latest answer.

What sync rep does guarantee is that if the person making the change is
told it succeeded (commit) then that change is safe on at least k other
servers. Sync rep is about guarantees of safety, not observability.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 09:56 AM, Heikki Linnakangas wrote:
> Imagine a web application that's mostly read-only, but a
> user can modify his own personal details like name and address, for
> example. Imagine that the user changes his street address and clicks
> 'save', causing an UPDATE, and the next query fetches that information
> again to display to the user.

I don't think that use case justifies sync replication and the
additional network overhead that brings. Latency is low in that case,
okay, but so is the lag for async replication.

Why not tell the load balancer to read from the master for n seconds
after the last write. After that, it should be save to query standbies,
again.

If the load on the master is the problem, and you want to reduce that by
moving the read-only transactions to the slave, sync replication pretty
certainly won't help you, either, because it actually *increases*
concurrency (by increased commit latency).

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 10:27 AM, Heikki Linnakangas wrote:
> Synchronous replication in the 'replay' mode is supposed to guarantee
> exactly that, no?

The master may lag behind, so it's not strictly speaking the same data.

Regards

Markus Wanner


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

Re: [HACKERS] Issues with Quorum Commit

[Heikki Linnakangas]

On 08.10.2010 11:25, Simon Riggs wrote:

On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:


Or what kind of customers do you think really need a no-lag solution for
read-only queries? In the LAN case, the lag of async rep is negligible
and in the WAN case the latencies of sync rep are prohibitive.


There is a very good use case for that particular set up, actually. If
your hot standby is guaranteed to be up-to-date with any transaction
that has been committed in the master, you can use the standby
interchangeably with the master for read-only queries.


This is an important point. It is desirable, but there is no such thing.
We must not take any project decisions based upon that false premise.

Hot Standby is never guaranteed to be up-to-date with master. There is
no such thing as certainty that you have the same data as the master.


Synchronous replication in the 'replay' mode is supposed to guarantee 
exactly that, no?


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] standby registration (was: is sync rep stalled?)

[Yeb Havinga]

Robert Haas wrote:

Yes, let's please just implement something simple and get it
committed.  k = 1.  Two GUCs (synchronous_standbys = name, name, name
and synchronous_waitfor = none|recv|fsync|apply), SUSET so you can
change it per txn.  Done.  We can revise it *the day after it's
committed* if we agree on how.  And if we *don't* agree, then we can
ship it and we still win.
  
I like the idea of something simple committed first, and am trying to 
understand what's said above.


k = 1 : wait for only one ack
two gucs: does this mean configurable in postgresql.conf at the master, 
and changable with SET commands on the master depending on options? Are 
both gucs mutable?
synchronous_standbys: I'm wondering if this registration is necessary in 
this simple setup. What are the named used for? Could they be removed? 
Should they also be configured at each standby?
synchronous_waitfor: If configured on the master, how is it updated to 
the standbys? What does being able to configure 'none' mean? k = 0? I 
smell a POLA violation here.


regards
Yeb Havinga


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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:
> >
> > Or what kind of customers do you think really need a no-lag solution for
> > read-only queries? In the LAN case, the lag of async rep is negligible
> > and in the WAN case the latencies of sync rep are prohibitive.
> 
> There is a very good use case for that particular set up, actually. If 
> your hot standby is guaranteed to be up-to-date with any transaction 
> that has been committed in the master, you can use the standby 
> interchangeably with the master for read-only queries. 

This is an important point. It is desirable, but there is no such thing.
We must not take any project decisions based upon that false premise.

Hot Standby is never guaranteed to be up-to-date with master. There is
no such thing as certainty that you have the same data as the master.

All sync rep gives you is a better durability guarantee that the changes
are safe. It doesn't guarantee those changes are transferred to all
nodes prior to making the data changes on any one standby.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: I: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Leonardo Francalanci]

> Applied with some  significant editorialization.  The biggest problem I
> found was that the  code for expression indexes didn't really work, and
> would leak memory like  there's no tomorrow even when it did work.


Sorry I couldn't write the way it was supposed to... I'll look at the
differences to see what I did wrong... (so maybe next time I'll do
better!)


Thank you

Leonardo




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

Re: [HACKERS] Issues with Quorum Commit

[Heikki Linnakangas]

On 08.10.2010 01:25, Simon Riggs wrote:

On Thu, 2010-10-07 at 13:44 -0400, Aidan Van Dyk wrote:


To get "non-stale" responses, you can only query those k=3 servers.
But you've shot your self in the foot because you don't know which
3/10 those will be.  The other 7 *are* stale (by definition).  They
talk about picking the "caught up" slave when the master fails, but
you actually need to do that for *every query*.


There is a big confusion around that point and I need to point out that
statement isn't accurate. It's taken me a long while to understand this.

Asking for k>  1 does *not* mean those servers are time synchronised.
All it means is that the master will stop waiting after 3
acknowledgements. There is no connection between the master receiving
acknowledgements and the standby applying changes received from master;
the standbys are all independent of one another.

In a bad case, those 3 acknowledgements might happen say 5 seconds apart
on the worst and best of the 3 servers. So the first standby to receive
the data could have applied the changes ~4.8 seconds prior to the 3rd
standby. There is still a chance of reading stale data on one standby,
but reading fresh data on another server. In most cases the time window
is small, but still exists.

The other 7 are stale with respect to the first 3. But then so are the
last 9 compared with the first one. The value of k has nothing
whatsoever to do with the time difference between the master and the
last standby to receive/apply the changes. The gap between first and
last standby (i.e. N, not k) is the time window during which a query
might/might not see a particular committed result.

So standbys are eventually consistent whether or not the master relies
on them to provide an acknowledgement. The only place where you can
guarantee non-stale data is on the master.


Yes, that's a good point. Synchronous replication for load-balancing 
purposes guarantees that when *you* perform a commit, after it finishes 
it will be visible in all standbys. But if you run the same query across 
different standbys, you're not guaranteed get same results. If you just 
pick a random server for every query, you might even see time moving 
backwards. Affinity is definitely a good idea for the load-balancing 
scenario, but even then the anomaly is possible if you get re-routed to 
a different server because the one you were bound to dies.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 05:41 AM, Fujii Masao wrote:
> But, even with quorum commit, if you choose wait-forever option,
> failover would decrease availability. Right after the failover,
> no standby has connected to new master, so if quorum >= 1, all
> the transactions must wait for a while.

That's a point, yes. But again, this is just write-availability, you can
happily read from all active standbies. And connection time is certainly
negligible compared to any kind of timeout (which certainly needs to be
way bigger than a couple of network round-trips).

> Basically we need to take a base backup from new master to start
> the standbys and make them connect to new master. This might take
> a long time. Since transaction commits cannot advance for that time,
> availability would goes down.

Just don't increase your quorum_commit to unreasonable values which your
hardware cannot possible satisfy. It doesn't make sense to set a
quorum_commit of 1 or even bigger, if you don't already have a standby
attached.

Start with 0 (i.e. replication off), then add standbies, then increase
quorum_commit to your new requirements.

> Or you think that wait-forever option is applied only when the
> standby goes down?

That wouldn't work in case of a full-cluster crash, where the
wait-forever option is required again. Otherwise you risk a split-brain
situation.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Simon Riggs]

On Fri, 2010-10-08 at 09:52 +0200, Markus Wanner wrote:

> One addendum: a timeout increases availability at the cost of
> increased danger of data loss and higher complexity. Don't use it,
> just increase (N - k) instead. 

Completely agree.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services


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

Re: [HACKERS] Issues with Quorum Commit

[Heikki Linnakangas]

On 08.10.2010 06:41, Fujii Masao wrote:

On Thu, Oct 7, 2010 at 3:01 AM, Markus Wanner  wrote:

Of course, it doesn't make sense to wait-forever on *every* standby that
ever gets added. Quorum commit is required, yes (and that's what this
thread is about, IIRC). But with quorum commit, adding a standby only
improves availability, but certainly doesn't block the master in any
way.


But, even with quorum commit, if you choose wait-forever option,
failover would decrease availability. Right after the failover,
no standby has connected to new master, so if quorum>= 1, all
the transactions must wait for a while.


Sure, the new master can't proceed with commits until enough standbys 
have connected to it.



Basically we need to take a base backup from new master to start
the standbys and make them connect to new master.


Do we really need that? I don't think that's acceptable, we'll need to 
fix that if that's the case.


I think you're right, streaming replication doesn't work across timeline 
changes. We left that out of 9.0, to keep things simple, but it seems 
that we really should fix that for 9.1.


You can cross timelines with the archive, though. But IIRC there was 
some issue with that too, you needed to restart the standbys because the 
standby scans what timelines exist at the beginning of recovery, and 
won't notice new timelines that appear after that?


We need to address that, apart from any of the other things discussed 
wrt. synchronous replication. It will benefit asynchronous replication 
too. IMHO *that* is the next thing we should do, the next patch we commit.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 04:01 AM, Fujii Masao wrote:
> Really? I don't think that ko-count=0 means "wait-forever".

Telling from the documentation, I'd also say it doesn't wait forever by
default. However, please note that there are different parameters for
the initial wait for connection during boot up (wfc-timeout and
degr-wfc-timeout). So you might to test what happens on a node failure,
not just absence of a standby.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Heikki Linnakangas]

On 07.10.2010 21:38, Markus Wanner wrote:

On 10/07/2010 03:19 PM, Dimitri Fontaine wrote:

I think you're all into durability, and that's good. The extra cost is
service downtime


It's just *reduced* availability. That doesn't necessarily mean
downtime, if you combine cleverly with async replication.


if that's not what you're after: there's also
availability and load balancing read queries on a system with no lag (no
stale data servicing) when all is working right.


All I'm saying is that those use cases are much better served with async
replication. Maybe together with something that warns and takes action
in case the standby's lag gets too big.

Or what kind of customers do you think really need a no-lag solution for
read-only queries? In the LAN case, the lag of async rep is negligible
and in the WAN case the latencies of sync rep are prohibitive.


There is a very good use case for that particular set up, actually. If 
your hot standby is guaranteed to be up-to-date with any transaction 
that has been committed in the master, you can use the standby 
interchangeably with the master for read-only queries. Very useful for 
load balancing. Imagine a web application that's mostly read-only, but a 
user can modify his own personal details like name and address, for 
example. Imagine that the user changes his street address and clicks 
'save', causing an UPDATE, and the next query fetches that information 
again to display to the user. If you use load balancing, the query can 
be routed to the hot standby server, and if it lags even 1-2 seconds 
behind it's quite possible that it will still return the old address. 
The user will go "WTF, I just changed that!".


That's the "load balancing" use case, which is quite different from the 
"zero data loss on server failure" use case that most people here seem 
to be interested in.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

Simon,

On 10/08/2010 12:25 AM, Simon Riggs wrote:
> Asking for k > 1 does *not* mean those servers are time synchronised.

Yes, it's technically impossible to create a fully synchronized cluster
(on the basis of shared-nothing nodes we are aiming for, that is). There
always is some kind of "lag" on either side.

Maybe the use case for a no-lag cluster doesn't exist, because it's
technically not feasible.

> In a bad case, those 3 acknowledgements might happen say 5 seconds apart
> on the worst and best of the 3 servers. So the first standby to receive
> the data could have applied the changes ~4.8 seconds prior to the 3rd
> standby. There is still a chance of reading stale data on one standby,
> but reading fresh data on another server. In most cases the time window
> is small, but still exists.

Well, the transaction isn't committed on the master, so one could argue
it shouldn't matter. The guarantee just needs to be one way: as soon as
confirmed committed to the client, all k standbies need to have it
committed, too. (At least for the "apply" replication level).

> So standbys are eventually consistent whether or not the master relies
> on them to provide an acknowledgement. The only place where you can
> guarantee non-stale data is on the master.

That's formulated a bit too strong. With "apply" replication level, you
should be able to rely on the guarantee that a committed transaction is
visible on at least k standbies. Maybe in advance of the commit on the
master, but I wouldn't call that "stale" data.

Given the current proposals, the master is the one that's "lagging" the
most, compared to the k standbies.

> High values of k reduce the possibility of data loss, whereas expected
> cluster availability is reduced as N - k gets smaller.

Exactly. One addendum: a timeout increases availability at the cost of
increased danger of data loss and higher complexity. Don't use it, just
increase (N - k) instead.

Regards

Markus Wanner

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

Re: [HACKERS] Issues with Quorum Commit

[Markus Wanner]

On 10/08/2010 12:30 AM, Simon Riggs wrote:
> I do, but its not a parameter. The k = 1 behaviour is hardcoded and
> considerably simplifies the design. Moving to k > 1 is additional work,
> slows things down and seems likely to be fragile.

Perfect! So I'm all in favor of committing that, but leaving away the
timeout thing, which I think is just adding unneeded complexity and
fragility.

Regards

Markus Wanner



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

Re: [HACKERS] proposal: plpgsql, solution for derivated types of parameters

[Pavel Stehule]

2010/10/8 Itagaki Takahiro :
> On Fri, Oct 8, 2010 at 3:08 PM, Pavel Stehule  wrote:
>> I propose a following syntax:
>>
>> -- variable as element of some array
>> DECLARE elementvar IS ELEMENT OF arrayvar;
>> DECLARE elementvar IS ELEMENT OF arrayvar%type
>>
>> -- variable as array of some variable
>> DECLARE arrayvar IS ARRAY OF varname;
>> DECLARE arrayvar IS ARRAY OF varname%type
>>
>> This proposal doesn't introduce any new reserved keywords.
>
> FYI, Oracle PL/SQL supports type declaration and array
> variables are declare as the type.
>
>  DECLARE
>    TYPE array_type_name IS {VARRAY | VARYING ARRAY} (size_limit)
>      OF element_type [NOT NULL];
>    a1 array_type_name;
>
> "IS ARRAY OF" syntax is similar enough to PL/SQL, but is not compatible.

it is compatible, but used in different place (clause).

>
> I'm not sure whether PL/SQL has "IS ELEMENT OF" variants.
>

yes, it is inspiration. I think so we can use this pattern because
plpgsql cannot to declare a local type. IS ELEMENT OF isn't supported
by Oracle. But Oracle must not solve this problem, because it doesn't
support a polymorphic types.

Pavel

> --
> Itagaki Takahiro
>

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

Re: [HACKERS] leaky views, yet again

[Dimitri Fontaine]

Hi,

Stephen Frost  writes:
> Wow, I just kind of assumed you could; I'm not really sure why.  Perhaps
> it'll be possible in the future though, so might be something to think
> about if/when it happens.  Can't see a way to abuse the view from inside
> a DO or in a function in the same way either.

It took me some time and reviewing the patch to think about it this way,
so maybe that would help some readers here: DO is a utility command, not
a query. That also explains why it does not get parameters.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] Issues with Quorum Commit

[Dimitri Fontaine]

Greg Smith  writes:
[…]
> I don't see this as needing any implementation any more complicated than the
> usual way such timeouts are handled.  Note how long you've been trying to
> reach the standby.  Default to -1 for forever.  And if you hit the timeout,
> mark the standby as degraded and force them to do a proper resync when they
> disconnect.  Once that's done, then they can re-enter sync rep mode again,
> via the same process a new node would have done so.

Thank you for this post, which is so much better than anything I could
achieve.

Just wanted to add that it should be possible in lots of cases to have a
standby rejoin the party without getting as far back as taking a new
base backup. Depends on wal_keep_segments and standby's degraded state,
among other parameters (archives, etc).

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

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

Re: [HACKERS] proposal: plpgsql, solution for derivated types of parameters

[Itagaki Takahiro]

On Fri, Oct 8, 2010 at 3:08 PM, Pavel Stehule  wrote:
> I propose a following syntax:
>
> -- variable as element of some array
> DECLARE elementvar IS ELEMENT OF arrayvar;
> DECLARE elementvar IS ELEMENT OF arrayvar%type
>
> -- variable as array of some variable
> DECLARE arrayvar IS ARRAY OF varname;
> DECLARE arrayvar IS ARRAY OF varname%type
>
> This proposal doesn't introduce any new reserved keywords.

FYI, Oracle PL/SQL supports type declaration and array
variables are declare as the type.

  DECLARE
TYPE array_type_name IS {VARRAY | VARYING ARRAY} (size_limit)
  OF element_type [NOT NULL];
a1 array_type_name;

"IS ARRAY OF" syntax is similar enough to PL/SQL, but is not compatible.

I'm not sure whether PL/SQL has "IS ELEMENT OF" variants.

-- 
Itagaki Takahiro

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