Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Simon Riggs]

On Thu, 2010-02-11 at 00:52 +0100, Joachim Wieland wrote:
> On Mon, Feb 8, 2010 at 5:16 PM, Alvaro Herrera
>  wrote:
> > These are the on-disk notifications, right?  It seems to me a bit
> > wasteful to store channel name always as NAMEDATALEN bytes.  Can we
> > truncate it at its strlen?
> 
> Attached is a new and hopefully more or less final patch for LISTEN / NOTIFY.
> 
> The following items have been addressed in this patch:
> 
>  - only store strlen(channel) instead of NAMEDATALEN bytes on disk
>  - limit to 7-bit ASCII
>  - forbid 2PC and LISTEN/NOTIFY for now
>  - documentation changes
>  - add missing tab completion for NOTIFY
>  - fix pg_notify() behavior with respect to NULLs, too long and too
> short parameters
>  - rebased to current HEAD, OID conflicts resolved


Some minor review comments without having taken in much of previous
discussion:

* Patch doesn't apply cleanly anymore, close.

* In async.c you say "new async notification model". Please say "async
notification model in 9.0 is". In (2) you say there is a central queue,
but don't describe purpose of queue or what it contains. Some other
typos in header comments.

* There is no mention of what to do with pg_notify at checkpoint. Look
at how pg_subtrans handles this. Should pg_notify do the same?

* Is there a lazy backend avoidance scheme as exists for relcache
invalidation messages? see storage/ipc/sinval...
OK, I see asyncQueueFillWarning() but nowhere else says it exists and
there aren't any comments in it to say what it does or why

* What do you expect the DBA to do when they receive a queue fill
warning? Where is that written down?

* Not clear of the purpose of backendSendsNotifications. In
AtCommit_NotifyAfterCommit() the logic seems strange. Code is
/* Allow transactions that have not executed
 LISTEN/UNLISTEN/NOTIFY to
 * return as soon as possible */
if (!pendingActions && !backendSendsNotifications)
return;
but since backendSendsNotifications is set true earlier there is no fast
path. 

* AtSubCommit_Notify doesn't seem to have a fastpath when no notify
commands have been executed.

* In Send_Notify() you throw ERROR while still holding the lock. It
seems better practice to drop the lock then ERROR.

* Why is Send_Notify() a separate function? It's only called from one
point in the code.

* We know that sometimes a FATAL error can occur without properly
processing the abort. Do we depend upon this never happening?

* Can we make NUM_ASYNC_BUFFERS = 8? 4 just seems too small

* backendSendsNotifications is future tense. The variable should be
called something like hasSentNotifications. Couple of other variables
with similar names

Hope that helps

-- 
 Simon Riggs   www.2ndQuadrant.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] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Greg Stark]

On Fri, Feb 12, 2010 at 3:49 PM, Robert Haas  wrote:
> Greg Stark, have you managed to get your access issues sorted out?  If

Yep, will look at this today.


-- 
greg

-- 
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] [NOVICE] Python verison for build in config.pl (Win32)

[Magnus Hagander]

2010/1/20 James William Pye :
> On Jan 20, 2010, at 12:27 PM, Magnus Hagander wrote:
>> Well, it needs the version to match it to the DLL name. For python
>> 2.6, it needs python26.dll. But yes, there should probably be some way
>> to ask python itself about that - that would be the non-naive method.
>> But as long as python is installed per default, we got it for free,
>> which is why it has "worked so far".
>
>
> [on tom's question]
> IIRC, the reason you can't query Python in the same way that 
> configure/python.m4 does is because the generated Makefile that supports 
> distutils.sysconfig does not exist in standard win32 builds. That is, AFAIK, 
> there is no way to request the exact path of the dll/lib file in win32. 
> However, I'm not particularly familiar with Python on win32, so that may not 
> be the case.
>
>
> Given the absence of a more precise method, I'd recommend considering 
> something along the lines of:
>
> Allow the user specify (config.pl?) the Python executable to build against 
> and default to the python.exe in %PATH%. (this may already be the case, idk)
>
> Query Python for the version information and installation prefix.
>
>  python -c 'import sys; print(str(sys.version_info[0]) + 
> str(sys.version_info[1]))'
>  python -c 'import sys; print(sys.prefix)'
>
> Assume that the prefix has a normal layout, and construct the lib path from 
> the extracted version and prefix.

From what I can tell, it at least makes no assumptions worse than we
have now. I'm sure there are ways to break it :-), but I think there
are less than there are now.

I have applied a patch that does something like this.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Streaming Replication on win32

[Magnus Hagander]

2010/2/8 Fujii Masao :
> On Mon, Jan 18, 2010 at 11:46 PM, Magnus Hagander  wrote:
 From what I can tell, this indicates that pq_getbyte_if_available() is
 not working - because it's supposed to never block, right?
>>>
>>> Right, it's not supposed to block.
>>>
 This could be because the win32 socket emulation layer simply wasn't
 designed to deal with non-blocking sockets. Specifically, it actually
 *always* sets the socket to non-blocking mode, and then uses that to
 properly emulate how sockets work under unix.
>>>
>>> I presume the win32 emulation layer can be taught about non-blocking
>>> sockets? Or maybe pq_getbyte_if_available() can be implemented using
>>> some other simpler method on Windows.
>>
>> It could be taught that, but it would probably be a lot easier to put
>> platform specific code in pq_getbyte_if_available().
>
> Umm.. in this case, for SSL on win32 case, we also need to create
> new function like my_sock_read_if_available() that receives data
> from non-blocking socket, and reassign it to the SSL BIO function.
> Right? If so, it seems easier for me to tell the win32 layer about
> non-blocking.

Sorry about the delay in responding to this.

Remember that the win32 code *always* puts the socket in non-blocking
mode. So we can't just "teach the layer about it". We need some way to
pass the information down that this is actually something we want to
be non-blocking, and it can't be the normal flag on the socket. I
don't really have an idea of where else we'd put it though :( It's in
the port structure, but not beyond it.

What we could do, is have an ugly global flag specifically for the
use-case we have here. Assuming we do create a plataform specific
pq_getbyte_if_available(), the code-path that would have trouble now
would be when we call pq_getbyte_if_available(), and it in turns asks
the socket if there is data, there is, but we end up calling back into
the SSL code to fetch the data, and it gets an incomplete packet.
Correct? So the path is basically:

pq_getbyte_if_available() -> secure_read() -> SSL_read() ->
my_sock_read() -> pgwin32_recv()

Given that we know we are working on a single socket here, we could
use a global flag to tell pgwin32_recv() to become nonblocking. We
could set this flag directly in the win32-specific version of
pq_getbyte_if_available(), and make sure it's cleared as soon as we
exit.

It will obviously fail if we do anything on a *different* socket
during this time, so it has to be set for a very short time. But that
seems doable. And we don't call any socket stuff from signal handlers
so that shouldn't cause issues.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

[HACKERS] Optimizing GetConflictingVirtualXIDs()

[Simon Riggs]

Optimize GetConflictingVirtualXIDs() in roughly the same manner we
optimize TransactionIdIsInProgress().

Views?

-- 
 Simon Riggs   www.2ndQuadrant.com
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 12de877..7f9b10e 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -1686,12 +1686,21 @@ GetCurrentVirtualXIDs(TransactionId limitXmin, bool excludeXmin0,
  * this array sufficiently often that we use malloc for the result.
  */
 VirtualTransactionId *
-GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid)
+GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid, int *return_count)
 {
 	static VirtualTransactionId *vxids;
 	ProcArrayStruct *arrayP = procArray;
 	int			count = 0;
 	int			index;
+	TransactionId globalxmin;
+
+	/*
+	 * Don't bother checking a valid TransactionId older than RecentGlobalXmin;
+	 * it could not possibly cause a conflict.
+	 */
+	if (TransactionIdPrecedes(limitXmin, RecentGlobalXmin) &&
+		TransactionIdIsValid(limitXmin))
+		return NULL;
 
 	/*
 	 * If not first time through, get workspace to remember main XIDs in. We
@@ -1717,19 +1726,28 @@ GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid)
 	if (!TransactionIdIsValid(limitXmin))
 		limitXmin = ShmemVariableCache->latestCompletedXid;
 
+	globalxmin = ShmemVariableCache->latestCompletedXid;
+
 	for (index = 0; index < arrayP->numProcs; index++)
 	{
 		volatile PGPROC *proc = arrayP->procs[index];
+		TransactionId pxmin;
 
 		/* Exclude prepared transactions */
 		if (proc->pid == 0)
 			continue;
 
+		/* Fetch xmin just once - can't change on us, but good coding */
+		pxmin = proc->xmin;
+
+		/* Update globalxmin to be the smallest valid xmin */
+		if (TransactionIdIsNormal(pxmin) &&
+			TransactionIdPrecedes(pxmin, globalxmin))
+			globalxmin = pxmin;
+
 		if (!OidIsValid(dbOid) ||
 			proc->databaseId == dbOid)
 		{
-			/* Fetch xmin just once - can't change on us, but good coding */
-			TransactionId pxmin = proc->xmin;
 
 			/*
 			 * We ignore an invalid pxmin because this means that backend
@@ -1748,6 +1766,10 @@ GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid)
 
 	LWLockRelease(ProcArrayLock);
 
+	RecentGlobalXmin = globalxmin;
+
+	*return_count = count;
+
 	/* add the terminator */
 	vxids[count].backendId = InvalidBackendId;
 	vxids[count].localTransactionId = InvalidLocalTransactionId;
diff --git a/src/backend/storage/ipc/standby.c b/src/backend/storage/ipc/standby.c
index c8fde2f..ea9c2e3 100644
--- a/src/backend/storage/ipc/standby.c
+++ b/src/backend/storage/ipc/standby.c
@@ -244,11 +244,12 @@ void
 ResolveRecoveryConflictWithSnapshot(TransactionId latestRemovedXid, RelFileNode node)
 {
 	VirtualTransactionId *backends;
+	int	conflicts = 0;
 
 	backends = GetConflictingVirtualXIDs(latestRemovedXid,
-		 node.dbNode);
-
-	ResolveRecoveryConflictWithVirtualXIDs(backends,
+		 node.dbNode, &conflicts);
+	if (conflicts > 0)
+		ResolveRecoveryConflictWithVirtualXIDs(backends,
 		   PROCSIG_RECOVERY_CONFLICT_SNAPSHOT);
 }
 
@@ -256,6 +257,7 @@ void
 ResolveRecoveryConflictWithTablespace(Oid tsid)
 {
 	VirtualTransactionId *temp_file_users;
+	int	conflicts = 0;
 
 	/*
 	 * Standby users may be currently using this tablespace for
@@ -277,7 +279,7 @@ ResolveRecoveryConflictWithTablespace(Oid tsid)
 	 * non-transactional.
 	 */
 	temp_file_users = GetConflictingVirtualXIDs(InvalidTransactionId,
-InvalidOid);
+InvalidOid, &conflicts);
 	ResolveRecoveryConflictWithVirtualXIDs(temp_file_users,
 		   PROCSIG_RECOVERY_CONFLICT_TABLESPACE);
 }
@@ -333,8 +335,9 @@ ResolveRecoveryConflictWithLock(Oid dbOid, Oid relOid)
 			backends = GetLockConflicts(&locktag, AccessExclusiveLock);
 		else
 		{
+			int	conflicts = 0;
 			backends = GetConflictingVirtualXIDs(InvalidTransactionId,
- InvalidOid);
+ InvalidOid, &conflicts);
 			report_memory_error = true;
 		}
 
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index 5a026e9..e01ad79 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -57,7 +57,8 @@ extern bool IsBackendPid(int pid);
 extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
 	  bool excludeXmin0, bool allDbs, int excludeVacuum,
 	  int *nvxids);
-extern VirtualTransactionId *GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid);
+extern VirtualTransactionId *GetConflictingVirtualXIDs(TransactionId limitXmin,
+	  Oid dbOid, int *return_count);
 extern pid_t CancelVirtualTransaction(VirtualTransactionId vxid, ProcSignalReason sigmode);
 
 extern int	CountActiveBackends(void);

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

[HACKERS] function to display ddl

[Little, Douglas]

Hi,

Is there a PG command or fuction that will return table ddl?

Thanks


Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image001.jpg@01CAAD58.A438BEB0]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [HACKERS] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Greg Stark]

On Sun, Feb 14, 2010 at 2:03 PM, Greg Stark  wrote:
> On Fri, Feb 12, 2010 at 3:49 PM, Robert Haas  wrote:
>> Greg Stark, have you managed to get your access issues sorted out?  If
>
> Yep, will look at this today.

So I think we have a bigger problem than just copydir.c. It seems to
me we should be fsyncing the table space data directories on every
checkpoint. Otherwise any newly created relations or removed relations
could disappear even though the data in them was fsynced. I'm thinking
I should add an _mdfd_opentblspc(reln) call which returns a file
descriptor for the tablespace and have mdsync() use that to sync the
directory whenever it fsyncs a relation. It would be nice to remember
which tablespaces have been fsynced and only fsync them once though,
that would need another hash table just for tablespaces.

We probably also need to fsync the pg_xlog directory every time we
create or rename an xlog segment.

Are there any other places we do directory operations which we need to
be permanent?


-- 
greg

-- 
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] knngist patch support

[Yeb Havinga]

Dimitri Fontaine wrote:

Then there's the metric space which is a data type with a distance
function. This function must be non-negative, commutative, etc.

So I guess what we need here is a Operator Group to define our plus and
minus operators, and the fact that it's a group says (by convention,
like the total ordering of a BTree) that the + is commutative and the -
its opposite. Or we have an "option" called abelian for specifying the
commutativity?
  
Would the group analogy work with partially ordered domains, e.g. with a 
location on a sphere datatype together with an identifier for the sphere 
- so poi on earth can be compared to another, but not a poi on earth 
with a poi on the moon. ?


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] function to display ddl

[Yeb Havinga]

Little, Douglas wrote:


Hi,

 


Is there a PG command or fuction that will return table ddl?


If you just want the definition,in psql type \d tablename.
To dump ddl the pg_dump with proper arguments can dump just the ddl of a 
single table. (see pg_dump --help)


I'm not aware of a command or function that returns it though.

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] Optimizing GetConflictingVirtualXIDs()

[Greg Stark]

On Sun, Feb 14, 2010 at 2:59 PM, Simon Riggs  wrote:
> Optimize GetConflictingVirtualXIDs() in roughly the same manner we
> optimize TransactionIdIsInProgress().
>
> Views?

EINSUFFICIENTEXPLANATION :)


-- 
greg

-- 
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] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Tom Lane]

Greg Stark  writes:
> So I think we have a bigger problem than just copydir.c. It seems to
> me we should be fsyncing the table space data directories on every
> checkpoint.

Is there any evidence that anyone anywhere has ever lost data because
of a lack of directory fsyncs?  I sure don't recall any bug reports
that seem to match that theory.

It seems to me that we're talking about a huge hit in both code
complexity and performance to deal with a problem that doesn't actually
occur in the field; and which furthermore is trivially solved on any
modern filesystem by choosing the right filesystem options.  Why don't
we just document those options, instead?

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] function to display ddl

[Ross J. Reedstrom]

On Sun, Feb 14, 2010 at 05:08:05PM +0100, Yeb Havinga wrote:
> Little, Douglas wrote:
> >
> >Hi,
> >
> > 
> >
> >Is there a PG command or fuction that will return table ddl?
> >
> If you just want the definition,in psql type \d tablename.
> To dump ddl the pg_dump with proper arguments can dump just the ddl of a 
> single table. (see pg_dump --help)

more of a pg-users question, but since we've got a partial answer here,
might as well complete it. Try psql -E, then \d sometable to see the
commands psql issues to generate its display.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
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] knngist patch support

[Tom Lane]

Robert Haas  writes:
> OK.  In that case, any objections to my applying the attached patch,
> which I believe implements this as you suggested?

Um, did you test this version?  It looks like the macros are still
defined according to the idea that SearchSysCache takes five arguments.

Also, I'd suggest adding explicit comments to syscache.h suggesting
that SearchSysCache etc are meant to be called via the macros
rather than directly.

I didn't check all the individual calls, but it looks generally
sane except for those points.

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] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Andres Freund]

On Sunday 14 February 2010 18:11:39 Tom Lane wrote:
> Greg Stark  writes:
> > So I think we have a bigger problem than just copydir.c. It seems to
> > me we should be fsyncing the table space data directories on every
> > checkpoint.
> 
> Is there any evidence that anyone anywhere has ever lost data because
> of a lack of directory fsyncs?  I sure don't recall any bug reports
> that seem to match that theory.
I have actually seen the issue during create database at least. In a 
virtualized hw though...
~1GB template database, lots and lots of small tables, the crash occured maybe 
a minute after CREATE DB, filesystem was xfs, kernel 2.6.30.y.
 
> It seems to me that we're talking about a huge hit in both code
> complexity and performance to deal with a problem that doesn't actually
> occur in the field; and which furthermore is trivially solved on any
> modern filesystem by choosing the right filesystem options.  Why don't
> we just document those options, instead?
Which options would that be? I am not aware that there any for any of the 
recent linux filesystems.
Well, except "sync" that is, but that sure would be more of a performance hit 
than fsyncing the directory...

Andres

-- 
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] psycopg2 license changed

[Josh Berkus]

On 2/13/10 5:13 PM, Federico Di Gregorio wrote:
> Hi *,
> 
> I just wanted all interested people know that psycopg2 2.0.14 to be
> released in the next few days will be under the LGPL3 + OpenSSL
> exception (example code and tests under the LGPL3 alone because they are
> never linked to OpenSSL).

Yaaay!

Of course, now I'm on the hook to fix bugs.

--Josh Berkus

-- 
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] Streaming Replication docs

[Josh Berkus]

On 2/13/10 10:17 AM, Greg Stark wrote:
> But having the separate recovery.conf seems kind of pointless legacy
> structure at this point.

Agreed, but I've been trying to minimize my tinkering with HS/SR.
Several things would need to be changed to get rid of Recovery.conf for
HS and PITR.

I expect that we'll get rid of Recovery.conf, and fold it into
PostgreSQL.conf, in 9.1.

--Josh Berkus

-- 
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] psycopg2 license changed

[Federico Di Gregorio]

On 14/02/2010 18:33, Josh Berkus wrote:
>> I just wanted all interested people know that psycopg2 2.0.14 to be
>> > released in the next few days will be under the LGPL3 + OpenSSL
>> > exception (example code and tests under the LGPL3 alone because they are
>> > never linked to OpenSSL).
> Yaaay!
> 
> Of course, now I'm on the hook to fix bugs.

Bugs? Which bugs? :)

-- 
Federico Di Gregorio   f...@initd.org
   I filosofi son come i sociologi: il mondo non lo capiscono. -- A.R.M.



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Tom Lane]

Andres Freund  writes:
> On Sunday 14 February 2010 18:11:39 Tom Lane wrote:
>> It seems to me that we're talking about a huge hit in both code
>> complexity and performance to deal with a problem that doesn't actually
>> occur in the field; and which furthermore is trivially solved on any
>> modern filesystem by choosing the right filesystem options.  Why don't
>> we just document those options, instead?

> Which options would that be? I am not aware that there any for any of the 
> recent linux filesystems.

Shouldn't journaling of metadata be sufficient?

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] Optimizing GetConflictingVirtualXIDs()

[Simon Riggs]

On Sun, 2010-02-14 at 17:06 +, Greg Stark wrote:
> On Sun, Feb 14, 2010 at 2:59 PM, Simon Riggs  wrote:
> > Optimize GetConflictingVirtualXIDs() in roughly the same manner we
> > optimize TransactionIdIsInProgress().
> >
> > Views?
> 
> EINSUFFICIENTEXPLANATION :)

...I like that error code.


The patch adds a calculation of RecentGlobalXmin each time it accesses
the proc array to derive conflicts. It then uses the derived value to
provide a fast-path out if a potential snapshot conflict arrives that we
already know will not conflict with any backends. The purpose of this is
to reduce the number of scans of the procarray and improve the
performance of the startup process.

The mechanism and purpose is the same as the first check in
TransactionIdIsInProgress().

-- 
 Simon Riggs   www.2ndQuadrant.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] psycopg2 license changed

[Devrim GÜNDÜZ]

On Sun, 2010-02-14 at 18:37 +0100, Federico Di Gregorio wrote:
> > 
> > Of course, now I'm on the hook to fix bugs.
> 
> Bugs? Which bugs? :) 

Come on, you know, "some people" report bugs sometimes ;)
 
-- 
Devrim GÜNDÜZ, RHCE
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Simon Riggs]

On Sun, 2010-02-14 at 17:22 +0100, Joachim Wieland wrote:
> > * There is no mention of what to do with pg_notify at checkpoint.
> Look
> > at how pg_subtrans handles this. Should pg_notify do the same?
> 
> Actually we don't care... We even hope that the pg_notify pages are
> not flushed at all. Notifications don't survive a server restart
> anyway and upon restart we just delete whatever is in the directory.

Suspected that was true, just checking it was commented somewhere.

-- 
 Simon Riggs   www.2ndQuadrant.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] Optimizing GetConflictingVirtualXIDs()

[Tom Lane]

Simon Riggs  writes:
> On Sun, 2010-02-14 at 17:06 +, Greg Stark wrote:
>> EINSUFFICIENTEXPLANATION :)

> ...I like that error code.

I think more EINSUFFICIENTCOMMENTS.  The patch should also add a comment
to the function, along the lines of "While we have the lock, also update
RecentGlobalXmin, so that we will have as up-to-date a value as possible
for next time".  You've also failed to document the meaning or purpose
of the added output parameter.

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] CommitFest Status Summary - 2010-02-14

[Tom Lane]

Robert Haas  writes:
> * Listen / Notify rewrite.  This is the only one of the remaining
> patches that is not marked as Ready for Committer, but I think it
> would be good if someone (probably Tom) at least took a look at it.
> I'm not sure if it's committable at this point, but we should at least
> try to provide some good feedback.

I will look at this one.  It'd be nice to get it in if at all possible,
because the existing listen/notify infrastructure won't play very nicely
with HS --- eg, inspecting pg_listener on the slave might yield the
false impression that some of the slave-side backends had active LISTENs
because of chance matches of PID.

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] knngist patch support

[Robert Haas]

On Sun, Feb 14, 2010 at 12:24 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> OK.  In that case, any objections to my applying the attached patch,
>> which I believe implements this as you suggested?
>
> Um, did you test this version?  It looks like the macros are still
> defined according to the idea that SearchSysCache takes five arguments.

You are correct.  I realized that this morning while I was shaving.
Sorry about that.

> Also, I'd suggest adding explicit comments to syscache.h suggesting
> that SearchSysCache etc are meant to be called via the macros
> rather than directly.

Good idea.

> I didn't check all the individual calls, but it looks generally
> sane except for those points.

Will fix and commit.

...Robert

-- 
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] CommitFest Status Summary - 2010-02-14

[Josh Berkus]

> I will look at this one.  It'd be nice to get it in if at all possible,
> because the existing listen/notify infrastructure won't play very nicely
> with HS --- eg, inspecting pg_listener on the slave might yield the
> false impression that some of the slave-side backends had active LISTENs
> because of chance matches of PID.

It'll also serve a major need for integrating PostgreSQL with caching
infrastructures.  So it's not just an "insider" feature.

--Josh Berkus


-- 
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] Listen / Notify - what to do when the queue is full

[Tom Lane]

Joachim Wieland  writes:
> + #define ERRCODE_TOO_MANY_ENTRIESMAKE_SQLSTATE('5','4', 
> '0','3','1')

Do you have any evidence that there is actually a DB2 error code
matching this, or is this errcode just invented?  The one page
Google finds doesn't list it:
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/rzala/rzalastc.html

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] Confusion over Python drivers

[Florian Weimer]

* Jeff Davis:

> Agreed. Ultimately, the conversion has to be done somewhere, but I don't
> believe the driver is the place for it. Type conversions are always
> going to be imperfect, and this has some important consequences:
>  * The type conversion system will be endlessly tweaked to improve it
>  * Developers will always run into problems with it in any complex
> application, so we need to allow them to circumvent the system and do it
> themselves when necessary.

The downside is that passing strings up to the application may have
distinctly worse performance characteristics than passing a number.

> In ruby-pg, you can just do:
>
>   conn.exec("INSERT INTO foo VALUES($1)", ["Jeff"])
>
> And I think that's appropriate. What I'm saying is that there should
> still exist some way to pass explicit types or formats (although that
> should still be easier than it is in C ;). Here's the long form:
>
>   conn.exec("INSERT INTO foo VALUES($1)",
> [{:value => "Jeff", :format => 0, :type => 0}])

Okay, this isn't too bad an API.  I will use the same approach.  In my
case, it means no transparent support for arrays, but per your own
guidelines, this is okay.

> That copies value so that foo and bar have the same contents: a 4 byte
> value "\000". What would happen though, if val was transparently
> decoded? It would decode it once in ruby, and again inside of postgres
> (in byteain), leaving you with a one byte value in bar, even though foo
> has a four-byte value.

I've never viewed it from this angle, and I agree that it makes sense.

Thanks for your observations and explanations, they were helpful.

-- 
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] Streaming Replication docs

[Peter Eisentraut]

On fre, 2010-02-12 at 11:14 +0200, Heikki Linnakangas wrote:
> So the new layout would be:
> 
> III. Server Administration
> 
> ...
> 21. Managing Databases
> 22. Localization
> 23. Routine Database Maintenance Tasks
> 24. Backup and Restore
> 25. High Availability, Load Balancing, and Replication
> *26. Recovery Configuration *
> 27. Monitoring Database Activity
> 28. Monitoring Disk Usage
> 29. Reliability and the Write-Ahead Log
> 30. Regression Tests
> 
> Thoughts, better ideas?

Seems like 25 and 26 should be the same chapter.  And chapter 29 should
perhaps come before them (and probably before 24 as well).



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

[HACKERS] LISTEN/NOTIFY versus encoding conversion

[Tom Lane]

There's been a lot of thrashing about whether LISTEN/NOTIFY should
restrict payload strings to 7-bit ASCII to avoid possible encoding
conversion failures.  I was on the side of "yes" but I'm having
second thoughts about it.  The point I had failed to think about
is that we already restrict notifies to only be received by backends
in the same database as the sending backend.  (This is an inherent
implementation restriction in the pg_listener-based implementation,
and is kept for compatibility in the new code.)  This means that
sender and receiver must have the same server_encoding, and so no
conversion issue can arise as far as the two backends are concerned.

Now it's true that we could get an encoding conversion failure while
trying to send the payload *to the client*, but it's not apparent
to me why we should restrict the feature because of that.  There are
plenty of other reasons why we might fail to send the notification
to the client.  Most obviously, we could also get an encoding
conversion failure on the notify condition name --- but we've never
enforced a character set restriction on that, and nobody's ever
complained about it AFAIR.

So the currently submitted patch is logically inconsistent.  If we
enforce a character set restriction on the payload for fear of
being unable to convert it to the destination client_encoding, then
we should logically do the same for the condition name.  But then
why not also restrict a lot of other things to pure ASCII?

I'm now thinking that we should just drop that restriction.

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] Re: Faster CREATE DATABASE by delaying fsync

[Florian Weimer]

* Tom Lane:

>> Which options would that be? I am not aware that there any for any of the 
>> recent linux filesystems.
>
> Shouldn't journaling of metadata be sufficient?

You also need to enforce ordering between the directory update and the
file update.  The file metadata is flushed with fsync(), but the
directory isn't.  On some systems, all directory operations are
synchronous, but not on Linux.

-- 
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] Re: Faster CREATE DATABASE by delaying fsync

[Mark Mielke]

On 02/14/2010 03:24 PM, Florian Weimer wrote:

* Tom Lane:
   

Which options would that be? I am not aware that there any for any of the
recent linux filesystems.
   

Shouldn't journaling of metadata be sufficient?
 

You also need to enforce ordering between the directory update and the
file update.  The file metadata is flushed with fsync(), but the
directory isn't.  On some systems, all directory operations are
synchronous, but not on Linux.
   


   dirsync
  All directory updates within the filesystem should be 
done  syn-
  chronously.   This  affects  the  following system calls: 
creat,

  link, unlink, symlink, mkdir, rmdir, mknod and rename.

The widely reported problems, though, did not tend to be a problem with 
directory changes written too late - but directory changes being written 
too early. That is, the directory change is written to disk, but the 
file content is not. This is likely because of the "ordered journal" 
mode widely used in ext3/ext4 where metadata changes are journalled, but 
file pages are not journalled. Therefore, it is important for some 
operations, that the file pages are pushed to disk using fsync(file), 
before the metadata changes are journalled.


In theory there is some open hole where directory updates need to be 
synchronized with file updates, as POSIX doesn't enforce this ordering, 
and we can't trust that all file systems implicitly order things 
correctly, but in practice, I don't see this sort of problem happening.


If you are concerned, enable dirsync.

Cheers,
mark

--
Mark Mielke


--
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] Re: Faster CREATE DATABASE by delaying fsync

[Andres Freund]

On Sunday 14 February 2010 21:41:02 Mark Mielke wrote:
> On 02/14/2010 03:24 PM, Florian Weimer wrote:
> > * Tom Lane:
> >>> Which options would that be? I am not aware that there any for any of
> >>> the recent linux filesystems.
> >> 
> >> Shouldn't journaling of metadata be sufficient?
> > 
> > You also need to enforce ordering between the directory update and the
> > file update.  The file metadata is flushed with fsync(), but the
> > directory isn't.  On some systems, all directory operations are
> > synchronous, but not on Linux.
> 
> dirsync
>All directory updates within the filesystem should be
> done  syn-
>chronously.   This  affects  the  following system calls:
> creat,
>link, unlink, symlink, mkdir, rmdir, mknod and rename.
> 
> The widely reported problems, though, did not tend to be a problem with
> directory changes written too late - but directory changes being written
> too early. That is, the directory change is written to disk, but the
> file content is not. This is likely because of the "ordered journal"
> mode widely used in ext3/ext4 where metadata changes are journalled, but
> file pages are not journalled. Therefore, it is important for some
> operations, that the file pages are pushed to disk using fsync(file),
> before the metadata changes are journalled.
Well, but thats not a problem with pg as it fsyncs the file contents.

> In theory there is some open hole where directory updates need to be
> synchronized with file updates, as POSIX doesn't enforce this ordering,
> and we can't trust that all file systems implicitly order things
> correctly, but in practice, I don't see this sort of problem happening.
I can try to reproduce it if you want...

> If you are concerned, enable dirsync.
If the filesystem already behaves that way a fsync on it should be fairly 
cheap. If it doesnt behave that way doing it is correct...

Besides there is no reason to fsync the directory before the checkpoint, so 
dirsync would require a higher cost than doing it correctly.

Andres

-- 
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] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Robert Haas]

On Sun, Feb 14, 2010 at 10:31 AM, Greg Stark  wrote:
> On Sun, Feb 14, 2010 at 2:03 PM, Greg Stark  wrote:
>> On Fri, Feb 12, 2010 at 3:49 PM, Robert Haas  wrote:
>>> Greg Stark, have you managed to get your access issues sorted out?  If
>>
>> Yep, will look at this today.
>
> So I think we have a bigger problem than just copydir.c. It seems to
> me we should be fsyncing the table space data directories on every
> checkpoint. Otherwise any newly created relations or removed relations
> could disappear even though the data in them was fsynced. I'm thinking
> I should add an _mdfd_opentblspc(reln) call which returns a file
> descriptor for the tablespace and have mdsync() use that to sync the
> directory whenever it fsyncs a relation. It would be nice to remember
> which tablespaces have been fsynced and only fsync them once though,
> that would need another hash table just for tablespaces.
>
> We probably also need to fsync the pg_xlog directory every time we
> create or rename an xlog segment.
>
> Are there any other places we do directory operations which we need to
> be permanent?

I agree with Tom that we need to see some actual reproducible test
cases where this is an issue before we go too crazy with it.  In
theory what you're talking about could also happen when extending a
relation, if we extend into a new file; but I think we need to
convince ourselves that it really happens before we make any more
changes.

On a pragmatic note, if this does turn out to be a problem, it's a
bug: and we can and do fix bugs whenever we discover them.  But the
other part of this patch - to speed up createdb - is a feature - and
we are very rapidly running out of time for 9.0 features.  So I'd like
to vote for getting the feature part of this committed (assuming it's
in good shape, of course) and we can continue to investigate the other
issues but without quite as much urgency.

...Robert

-- 
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] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Andres Freund]

On Sunday 14 February 2010 21:57:08 Robert Haas wrote:
> On Sun, Feb 14, 2010 at 10:31 AM, Greg Stark  wrote:
> > On Sun, Feb 14, 2010 at 2:03 PM, Greg Stark  wrote:
> >> On Fri, Feb 12, 2010 at 3:49 PM, Robert Haas  
wrote:
> >>> Greg Stark, have you managed to get your access issues sorted out?  If
> >> 
> >> Yep, will look at this today.
> > 
> > So I think we have a bigger problem than just copydir.c. It seems to
> > me we should be fsyncing the table space data directories on every
> > checkpoint. Otherwise any newly created relations or removed relations
> > could disappear even though the data in them was fsynced. I'm thinking
> > I should add an _mdfd_opentblspc(reln) call which returns a file
> > descriptor for the tablespace and have mdsync() use that to sync the
> > directory whenever it fsyncs a relation. It would be nice to remember
> > which tablespaces have been fsynced and only fsync them once though,
> > that would need another hash table just for tablespaces.
> > 
> > We probably also need to fsync the pg_xlog directory every time we
> > create or rename an xlog segment.
> > 
> > Are there any other places we do directory operations which we need to
> > be permanent?
> 
> I agree with Tom that we need to see some actual reproducible test
> cases where this is an issue before we go too crazy with it.  In
> theory what you're talking about could also happen when extending a
> relation, if we extend into a new file; but I think we need to
> convince ourselves that it really happens before we make any more
> changes.
Ok, will try to reproduce.

> On a pragmatic note, if this does turn out to be a problem, it's a
> bug: and we can and do fix bugs whenever we discover them.  But the
> other part of this patch - to speed up createdb - is a feature - and
> we are very rapidly running out of time for 9.0 features.  So I'd like
> to vote for getting the feature part of this committed (assuming it's
> in good shape, of course) and we can continue to investigate the other
> issues but without quite as much urgency.
Sound sensible.

Andres

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

[HACKERS] subtransaction cancellation in HS

[Andres Freund]

Hi Simon, Hi all,

I am not sure this is 9.0 material - even if I would like it to get 
included... If HS wouldnt be new I wouldnt even consider suggesting it, but as 
its stands its a pretty small change...

It simply allows queries involving subtransaction not to get FATALed but 
canceled. This works by doing the recursive abort you erlier did in 
ProcessInterrupts in the PostgresMain where there cannot be any references 
higher up in the chain.

It would likely be sensible to check that errorcode in some PLs. I have code 
for that but I dont think its sensible to continue on those before the 
approach is agreed uppon.

I would like to get the part about a seperate error code for HS cancellations 
to get commited independently. Its kinda sensible for a client to accept 
specifically about such a cancellation and requiring them to play around with 
the message...
Currently its called ERRCODE_QUERY_CANCELED_HS but perhaps 
ERRCODE_QUERY_CANCELED_STANDBY_CONFLICT or such would be better. I dont really 
know how errorcodes gets assigned, so I picked one which is likely wrong...

Additionally there is a very small cleanup removing the errno saving from 
RecoveryConflictInterrupt - its somewhat incomplete (I think harmlessly 
though)  and more importantly the only caller in procsignal.c already does 
that...

Andres

From 0dc602ca41fee1ca8bd85056add8bcb8f44bf510 Mon Sep 17 00:00:00 2001
From: Andres Freund 
Date: Sun, 14 Feb 2010 11:24:45 +0100
Subject: [PATCH] Dont try to save the errno in RecoveryConflictInterrupt to avoid confusion.

In the current state the errno saving in there is confusing as there
are several returns ignoring to set it. I think its currently harmless
as there should be no changes to errno at those places - beside that
the only caller (procsignal_sigusr1_handler) already saves it.
---
 src/backend/tcop/postgres.c |8 ++--
 1 files changed, 2 insertions(+), 6 deletions(-)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8c0c8b9..3505fc4 100644
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*** SigHupHandler(SIGNAL_ARGS)
*** 2753,2763 
  void
  RecoveryConflictInterrupt(ProcSignalReason reason)
  {
- 	int save_errno = errno;
- 
  	/*
! 	* Don't joggle the elbow of proc_exit
! 	*/
  	if (!proc_exit_inprogress)
  	{
  		RecoveryConflictReason = reason;
--- 2753,2761 
  void
  RecoveryConflictInterrupt(ProcSignalReason reason)
  {
  	/*
! 	 * Don't joggle the elbow of proc_exit
! 	 */
  	if (!proc_exit_inprogress)
  	{
  		RecoveryConflictReason = reason;
*** RecoveryConflictInterrupt(ProcSignalReas
*** 2856,2863 
  			ProcessInterrupts();
  		}
  	}
- 
- 	errno = save_errno;
  }
  
  /*
--- 2854,2859 
-- 
1.6.5.12.gd65df24

From 9d421934f737f607d550d95f5e64b630c143f013 Mon Sep 17 00:00:00 2001
From: Andres Freund 
Date: Mon, 11 Jan 2010 17:43:01 +0100
Subject: [PATCH 1/2] Add a new error code ERRCODE_QUERY_CANCELED_HS for use with HS indicating a failure
 that is more than a plain ERRCODE_QUERY_CANCELED - namely it should not be caught from
 various places like savepoints and in PLs.

In want for a better name.
---
 src/backend/tcop/postgres.c  |4 ++--
 src/include/utils/errcodes.h |1 +
 2 files changed, 3 insertions(+), 2 deletions(-)

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 8c0c8b9..67fa16b 100644
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*** ProcessInterrupts(void)
*** 2947,2959 
   */
  silent_error_while_idle = true;
  ereport(ERROR | LOG_NO_CLIENT,
! 		(errcode(ERRCODE_QUERY_CANCELED),
  		 errmsg("canceling statement due to conflict with recovery"),
  		 errdetail_recovery_conflict()));
  			}
  			else{
  ereport(ERROR,
! 		(errcode(ERRCODE_QUERY_CANCELED),
  		 errmsg("canceling statement due to conflict with recovery"),
  		 errdetail_recovery_conflict()));
  			}
--- 2947,2959 
   */
  silent_error_while_idle = true;
  ereport(ERROR | LOG_NO_CLIENT,
! 		(errcode(ERRCODE_QUERY_CANCELED_HS),
  		 errmsg("canceling statement due to conflict with recovery"),
  		 errdetail_recovery_conflict()));
  			}
  			else{
  ereport(ERROR,
! 		(errcode(ERRCODE_QUERY_CANCELED_HS),
  		 errmsg("canceling statement due to conflict with recovery"),
  		 errdetail_recovery_conflict()));
  			}
diff --git a/src/include/utils/errcodes.h b/src/include/utils/errcodes.h
index 52c09ca..279f0e4 100644
*** a/src/include/utils/errcodes.h
--- b/src/include/utils/errcodes.h
***
*** 328,333 
--- 328,334 
  /* Class 57 - Operator Intervention (class borrowed from DB2) */
  #define ERRCODE_OPERATOR_INTERVENTION		MAKE_SQLSTATE('5','7', '0','0','0')
  #define ERRCODE_QUERY_CANCELEDMAKE_SQLSTATE('5','7', '0','1','4')
+ #define ERRCODE_QUERY_CANCELED_HS			MAKE_SQLSTATE('5','7', '0','1','5')
  #define ERRCODE_AD

Re: [HACKERS] Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

[Greg Stark]

On Sun, Feb 14, 2010 at 8:57 PM, Robert Haas  wrote:
> On a pragmatic note, if this does turn out to be a problem, it's a
> bug: and we can and do fix bugs whenever we discover them.  But the
> other part of this patch - to speed up createdb - is a feature - and
> we are very rapidly running out of time for 9.0 features.  So I'd like
> to vote for getting the feature part of this committed (assuming it's
> in good shape, of course) and we can continue to investigate the other
> issues but without quite as much urgency.

No problem, I already committed the part that overlaps so I can commit
the rest now. I just want to take extra care given how much wine I've
already had tonight...

Incidentally, sorry Andres, I forgot to credit you in the first commit.
-- 
greg

-- 
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] Re: Faster CREATE DATABASE by delaying fsync

[Mark Mielke]

On 02/14/2010 03:49 PM, Andres Freund wrote:

On Sunday 14 February 2010 21:41:02 Mark Mielke wrote:
   

The widely reported problems, though, did not tend to be a problem with
directory changes written too late - but directory changes being written
too early. That is, the directory change is written to disk, but the
file content is not. This is likely because of the "ordered journal"
mode widely used in ext3/ext4 where metadata changes are journalled, but
file pages are not journalled. Therefore, it is important for some
operations, that the file pages are pushed to disk using fsync(file),
before the metadata changes are journalled.
 

Well, but thats not a problem with pg as it fsyncs the file contents.
   


Exactly. Not a problem.


If you are concerned, enable dirsync.
 

If the filesystem already behaves that way a fsync on it should be fairly
cheap. If it doesnt behave that way doing it is correct...
   


Well, I disagree, as the whole point of this thread is that fsync() is 
*not* cheap. :-)



Besides there is no reason to fsync the directory before the checkpoint, so
dirsync would require a higher cost than doing it correctly.
   


Using "ordered" metadata journaling has approximately the same effect. 
Provided that the data is fsync()'d before the metadata is required, 
either the metadata is recorded in the journal, in which case the data 
is accessible, or the metadata is NOT recorded in the journal, in which 
case, the files will appear missing. The races that theoretically exist 
would be in situations where the data of one file references a separate 
file that does not yet exist.


You said you would try and reproduce - are you going to try and 
reproduce on ext3/ext4 with ordered journalling enabled? I think 
reproducing outside of a case such as CREATE DATABASE would be 
difficult. It would have to be something like:


open(O_CREAT)/write()/fsync()/close() of new data file, where data 
gets written, but directory data is not yet written out to journal
open()/.../write()/fsync()/close() of existing file to point to new 
data file, but directory data is still not yet written out to journal

crash

In this case, "dirsync" should be effective at closing this hole.

As for cost? Well, most PostgreSQL data is stored within file content, 
not directory metadata. I think "dirsync" might slow down some 
operations like CREATE DATABASE or "rm -fr", but I would not expect it 
to effect day-to-day performance of the database under real load. Many 
operating systems enable the equivalent of "dirsync" by default. I 
believe Solaris does this, for example, and other than slowing down "rm 
-fr", I don't recall any real complaints about the cost of "dirsync".


After writing the above, I'm seriously considering adding "dirsync" to 
my /db mounts that hold PostgreSQL and MySQL data.


Cheers,
mark

--
Mark Mielke


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

[HACKERS] Explain buffers display units.

[Greg Stark]

So this is what I did about my two complaints earlier about the
explain buffer patch.

a) Changed the line description to "Total Buffer Usage" which at least
hints that it's something more akin to the "Total runtime" listed at
the bottom than the "actual time".

b) Used units of memory -- I formatted them with 3 significant digits
(unless the unit is bytes or kB where that would be silly). It's just
what looked best to my eye.

I'm finding "hit" and "read" kind of confusing myself but don't really
have any better idea. It's not entirely clear whether read is the
total accesses out of which some are cache hits or if they're two
disjoint sets.

postgres=# explain (analyze,buffers) select * from x limit 1;
   QUERY PLAN
-
 Limit  (cost=0.00..266.68 rows=1 width=105) (actual
time=0.023..53.964 rows=1 loops=1)
   Total Buffer Usage: shared hit=8kB read=1.30MB
   ->  Seq Scan on x  (cost=0.00..10667.00 rows=40 width=105)
(actual time=0.019..20.311 rows=1 loops=1)
 Total Buffer Usage: shared hit=8kB read=1.30MB
 Total runtime: 71.074 ms
(5 rows)

-- 
greg
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***
*** 98,104  static void ExplainJSONLineEnding(ExplainState *es);
  static void ExplainYAMLLineStarting(ExplainState *es);
  static void escape_json(StringInfo buf, const char *str);
  static void escape_yaml(StringInfo buf, const char *str);
! 
  
  /*
   * ExplainQuery -
--- 98,104 
  static void ExplainYAMLLineStarting(ExplainState *es);
  static void escape_json(StringInfo buf, const char *str);
  static void escape_yaml(StringInfo buf, const char *str);
! static double normalize_memory(double amount, char **unit, int *precision);
  
  /*
   * ExplainQuery -
***
*** 1081,1127  ExplainNode(Plan *plan, PlanState *planstate,
  			if (has_shared || has_local || has_temp)
  			{
  appendStringInfoSpaces(es->str, es->indent * 2);
! appendStringInfoString(es->str, "Buffers:");
  
  if (has_shared)
  {
  	appendStringInfoString(es->str, " shared");
! 	if (usage->shared_blks_hit > 0)
! 		appendStringInfo(es->str, " hit=%ld",
! 			usage->shared_blks_hit);
  	if (usage->shared_blks_read > 0)
! 		appendStringInfo(es->str, " read=%ld",
! 			usage->shared_blks_read);
  	if (usage->shared_blks_written > 0)
! 		appendStringInfo(es->str, " written=%ld",
! 			usage->shared_blks_written);
  	if (has_local || has_temp)
  		appendStringInfoChar(es->str, ',');
  }
  if (has_local)
  {
! 	appendStringInfoString(es->str, " local");
! 	if (usage->local_blks_hit > 0)
! 		appendStringInfo(es->str, " hit=%ld",
! 			usage->local_blks_hit);
! 	if (usage->local_blks_read > 0)
! 		appendStringInfo(es->str, " read=%ld",
! 			usage->local_blks_read);
! 	if (usage->local_blks_written > 0)
! 		appendStringInfo(es->str, " written=%ld",
! 			usage->local_blks_written);
  	if (has_temp)
  		appendStringInfoChar(es->str, ',');
  }
  if (has_temp)
  {
  	appendStringInfoString(es->str, " temp");
  	if (usage->temp_blks_read > 0)
! 		appendStringInfo(es->str, " read=%ld",
! 			usage->temp_blks_read);
! 	if (usage->temp_blks_written > 0)
! 		appendStringInfo(es->str, " written=%ld",
! 			usage->temp_blks_written);
  }
  appendStringInfoChar(es->str, '\n');
  			}
--- 1081,1143 
  			if (has_shared || has_local || has_temp)
  			{
  appendStringInfoSpaces(es->str, es->indent * 2);
! appendStringInfoString(es->str, "Total Buffer Usage:");
  
  if (has_shared)
  {
+ 	char *hit_unit, *read_unit, *written_unit;
+ 	int   hit_prec,  read_prec,  written_prec;
+ 	double hit_mem  = normalize_memory((double)usage->shared_blks_hit  * BLCKSZ, &hit_unit,  &hit_prec);
+ 	double read_mem = normalize_memory((double)usage->shared_blks_read * BLCKSZ, &read_unit, &read_prec);
+ 	double written_mem  = normalize_memory((double)usage->shared_blks_written  * BLCKSZ, &written_unit,  &written_prec);
+ 
  	appendStringInfoString(es->str, " shared");
! 		appendStringInfo(es->str, " hit=%.*f%s", 
! 		 hit_prec, hit_mem, hit_unit);
  	if (usage->shared_blks_read > 0)
! 		appendStringInfo(es->str, " read=%.*f%s",
! 		 read_prec, read_mem, read_unit);
  	if (usage->shared_blks_written > 0)
! 		appendStringInfo(es->str, " written=%.*f%s",
! 		 written_prec, written_mem, written_unit);
  	if (has_local || has_temp)
  		appendStringInfoChar(es->str, ',');
  }
  if (has_local)
  {
! 	char *hit_unit, *read_unit, *written_unit;
! 	int   hit_prec,  read_prec,  written_prec;
! 	double hit_mem  = normalize_memory((double)usage->loc

[HACKERS] LISTEN/NOTIFY and notification timing guarantees

[Tom Lane]

The proposed new implementation of listen/notify works by shoving all
of a transaction's outgoing notifies into the global queue during
pre-commit, then sending PROCSIG_NOTIFY_INTERRUPT to listening backends
post-commit.  When a listening backend scans the queue, if it hits a
message from a transaction that hasn't yet committed nor aborted, it
abandons queue scanning, expecting to resume scanning when it gets
another PROCSIG_NOTIFY_INTERRUPT.  This means that a transaction that is
still hanging fire on commit can block receipt of notifies from
already-committed transactions, if they queued after it did.  While the
old implementation never made any hard guarantees about the time
interval between commit and receipt of notify, it still seems to me that
there are some potential surprises here, and I don't recall if they were
all analyzed in the previous discussions.  So bear with me a second:

1. In the previous code, a transaction "hanging fire on commit" (ie,
with pg_listener changes made, but not committed) would be holding
exclusive lock on pg_listener.  So it would block things even worse
than now, as we couldn't queue new items either.  An uncommitted queue
entry seems to behave about the same as that lock would, in that it
prevents all listeners from seeing any new messages.  AFAICS, therefore,
this isn't objectionable in itself.

2. Since the pre-commit code releases AsyncQueueLock between pages,
it is possible for the messages of different transactions to get
interleaved in the queue, which not only means that they'd be delivered
interleaved but also that it's possible for a listener to deliver some
notifications of a transaction, and only later (perhaps many
transactions later) deliver the rest.  The existing code can also
deliver notifications of different transactions interleaved, but AFAICS
it can never deliver some notifications of one transaction and then
deliver more of them in a different batch.  By the time any listener
gets to scan pg_listener, a sending transaction is either committed or
not, it cannot commit partway through a scan (because of the locking
done on pg_listener).

3. It is possible for a backend's own self-notifies to not be delivered
immediately after commit, if they are queued behind some other
uncommitted transaction's messages.  That wasn't possible before either.

I'm not sure how probable it is that applications might be coded in a
way that relies on the properties lost according to point #2 or #3.
It seems rather scary though, particularly because if there were such a
dependency, it would be easy to never see the misbehavior during testing.

We could fix #2 by not releasing AsyncQueueLock between pages when
queuing messages.  This has no obvious downsides as far as I can see;
if anything it ought to save some cycles and contention.  We could fix
#3 by re-instituting the special code path that previously existed for
self-notifies, ie send them to the client directly from AtCommit_Notify
and ignore self-notifies coming back from the queue.  This would mean
that a backend might see its own self-notifies in a different order
relative to other backends' messages than other backends do --- but that
was the case in the old coding as well.  I think preserving the
property that self-notifies are delivered immediately upon commit might
be more important than that.

Comments?

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] psql tab completion for DO blocks

[Takahiro Itagaki]

David Fetter  wrote:

> > DO { [ LANGUAGE lang_name ] | code } ...
> Good catch :)

The tab completion patch and documentation fix were committed.
Thanks.

Regards,
---
Takahiro Itagaki
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] CommitFest Status Summary - 2010-02-14

[Andrew Dunstan]

Robert Haas wrote:

We're down to 5 patches remaining, and 1 day remaining, so it's time
to try to wrap things up.


* Package namespace and Safe init cleanup for plperl.  Andrew Dunstan
is taking care of this one, I believe.

  


I will get this in, with changes as discussed recently.

I also have two small action items I want to get into the alpha:

   * change perl warnings to emit messages at WARNING rather than
 NOTICE level as recently discussed. This can probably be done as
 part of the above patch.
   * add the query text to auto-explain output, as I proposed some time
 ago, and was generally approved. I think it's desirable for us to
 have this from the get-go for XML explain output. This isn't in
 the commitfest, but the patch is very small, and it's really a
 cleanup item, I think.

I will be returning home in the next 24 hours and will try to get all 
this in within 24 hours of that. But I can't make it by tomorrow. I'm 
too tired now and I want to avoid mistakes.


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] Explain buffers display units.

[Greg Smith]

Greg Stark wrote:

b) Used units of memory -- I formatted them with 3 significant digits
(unless the unit is bytes or kB where that would be silly). It's just
what looked best to my eye.
  


How does this compare with what comes out of pg_size_pretty 
(src/backend/utils/adt/dbsize.c)? I already have code floating around 
that parses the output from pg_size_pretty when I'm slurping in things 
from PostgreSQL, and it's not immediately obvious to me what having a 
format that's similar to but not quite the same as that one is buying here.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   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] Streaming Replication on win32

[Fujii Masao]

On Sun, Feb 14, 2010 at 11:52 PM, Magnus Hagander  wrote:
> Sorry about the delay in responding to this.

Thanks for the response.

> Remember that the win32 code *always* puts the socket in non-blocking
> mode. So we can't just "teach the layer about it". We need some way to
> pass the information down that this is actually something we want to
> be non-blocking, and it can't be the normal flag on the socket. I
> don't really have an idea of where else we'd put it though :( It's in
> the port structure, but not beyond it.

Right.

BTW, pq_getbyte_if_available() always changes the socket to non-blocking
and blocking mode before and after calling secure_read(), respectively.
This code seems wrong on win32. Because, as you said, the socket is always
in non-blocking mode on win32. We should change pq_getbyte_if_available()
so as not to change the socket mode only in win32?

> What we could do, is have an ugly global flag specifically for the
> use-case we have here. Assuming we do create a plataform specific
> pq_getbyte_if_available(), the code-path that would have trouble now
> would be when we call pq_getbyte_if_available(), and it in turns asks
> the socket if there is data, there is, but we end up calling back into
> the SSL code to fetch the data, and it gets an incomplete packet.
> Correct? So the path is basically:
>
> pq_getbyte_if_available() -> secure_read() -> SSL_read() ->
> my_sock_read() -> pgwin32_recv()
>
> Given that we know we are working on a single socket here, we could
> use a global flag to tell pgwin32_recv() to become nonblocking. We
> could set this flag directly in the win32-specific version of
> pq_getbyte_if_available(), and make sure it's cleared as soon as we
> exit.
>
> It will obviously fail if we do anything on a *different* socket
> during this time, so it has to be set for a very short time. But that
> seems doable. And we don't call any socket stuff from signal handlers
> so that shouldn't cause issues.

Agreed. Here is the patch which does that (including the above-mentioned
change). I haven't tested it yet because I failed in creating the build
environment for the MSVC :( I'll try to create that again, and test it.
Though I'm not sure how long it takes.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/src/backend/libpq/pqcomm.c
--- b/src/backend/libpq/pqcomm.c
***
*** 837,845  pq_getbyte_if_available(unsigned char *c)
--- 837,849 
  	}
  
  	/* Temporarily put the socket into non-blocking mode */
+ #ifdef WIN32
+ 	pgwin32_noblock = 1;
+ #else
  	if (!pg_set_noblock(MyProcPort->sock))
  		ereport(ERROR,
  (errmsg("couldn't put socket to non-blocking mode: %m")));
+ #endif
  	MyProcPort->noblock = true;
  	PG_TRY();
  	{
***
*** 851,866  pq_getbyte_if_available(unsigned char *c)
--- 855,878 
  		 * The rest of the backend code assumes the socket is in blocking
  		 * mode, so treat failure as FATAL.
  		 */
+ #ifdef WIN32
+ 		pgwin32_noblock = 0;
+ #else
  		if (!pg_set_block(MyProcPort->sock))
  			ereport(FATAL,
  	(errmsg("couldn't put socket to blocking mode: %m")));
+ #endif
  		MyProcPort->noblock = false;
  		PG_RE_THROW();
  	}
  	PG_END_TRY();
+ #ifdef WIN32
+ 	pgwin32_noblock = 0;
+ #else
  	if (!pg_set_block(MyProcPort->sock))
  		ereport(FATAL,
  (errmsg("couldn't put socket to blocking mode: %m")));
+ #endif
  	MyProcPort->noblock = false;
  
  	return r;
*** a/src/backend/port/win32/socket.c
--- b/src/backend/port/win32/socket.c
***
*** 13,18 
--- 13,26 
  
  #include "postgres.h"
  
+ /*
+  * This indicates whether pgwin32_recv() is blocked until the receive
+  * operation has been finished. A value of zero blocks it even if
+  * the socket is set to non-blocking mode. A non-zero value makes it
+  * return immediately whether data is available or not.
+  */
+ int	pgwin32_noblock = 0;
+ 
  #undef socket
  #undef accept
  #undef connect
***
*** 315,321  pgwin32_recv(SOCKET s, char *buf, int len, int f)
  	for (n = 0; n < 5; n++)
  	{
  		if (pgwin32_waitforsinglesocket(s, FD_READ | FD_CLOSE | FD_ACCEPT,
! 		INFINITE) == 0)
  			return -1;			/* errno already set */
  
  		r = WSARecv(s, &wbuf, 1, &b, &flags, NULL, NULL);
--- 323,330 
  	for (n = 0; n < 5; n++)
  	{
  		if (pgwin32_waitforsinglesocket(s, FD_READ | FD_CLOSE | FD_ACCEPT,
! 		(pgwin32_noblock == 0) ? INFINITE : 0)
! 			== 0)
  			return -1;			/* errno already set */
  
  		r = WSARecv(s, &wbuf, 1, &b, &flags, NULL, NULL);
*** a/src/include/port/win32.h
--- b/src/include/port/win32.h
***
*** 283,288  int			pgwin32_send(SOCKET s, char *buf, int len, int flags);
--- 283,290 
  const char *pgwin32_socket_strerror(int err);
  int			pgwin32_waitforsinglesocket(SOCKET s, int what, int timeout);
  
+ extern int	pgwin32_noblock;
+ 
  /* in backend/port/win32/security.c */
  extern int	pgwin32_is_admin(void);
  extern int	pg

Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL

[Fujii Masao]

On Sat, Feb 13, 2010 at 1:10 AM, Heikki Linnakangas
 wrote:
> Are you thinking of a scenario where remove_command gets stuck, and
> prevents bgwriter from performing restartpoints while it's stuck?

Yes. If there is the archive in the remote server and the network outage
happens, remove_command might get stuck, I'm afraid.

> You
> have trouble if restore_command gets stuck like that as well, so I think
> we can require that the remove_command returns in a reasonable period of
> time, ie. in a few minutes.

Oh, you are right!

BTW, we need to note that remove_command approach would be useless if one
archive is shared by multiple standbys. One standby might wrongly remove
the archived WAL file that has been still required for another standby.
In this case, we need to have the job script that calculates the archived
WAL files that are required by no standbys, and removes them.

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] Parameter name standby_mode

[Fujii Masao]

On Fri, Feb 12, 2010 at 11:46 PM, Tom Lane  wrote:
> Even more to the point is that some of them, like PGPORT, are highly
> likely to be set in a server's environment to point to the server
> itself.  It would be extremely dangerous to automatically try to start
> replication just because we find those set.  In fact, I would argue that
> we should fix things so that any such variables inherited from the
> server environment are intentionally *NOT* used for making SR
> connections.

There are many environment variables which libpq automatically uses.
Which variables should not be used for SR connection? All?

If both primary_conninfo and environment variables are not given,
the default value (e.g., port = 5432) is automatically used for SR
connection. Is this OK? or NG as well as the environment variables?

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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after

[Tom Lane]

st...@postgresql.org (Greg Stark) writes:
> Log Message:
> ---
> Speed up CREATE DATABASE by deferring the fsyncs until after copying
> all the data and using posix_fadvise to nudge the OS into flushing it
> earlier. This also hopefully makes CREATE DATABASE avoid spamming the
> cache.

The buildfarm indicates that this patch has got some serious issues.

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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after

[Tom Lane]

I wrote:
> The buildfarm indicates that this patch has got some serious issues.

Actually, looking closer, some of the Windows machines started failing
after the *earlier* patch to add directory fsyncs.

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] Re: [COMMITTERS] pgsql: Reduce the chatter to the log when starting a standby server.

[Fujii Masao]

On Sat, Feb 13, 2010 at 4:33 AM, Richard Huxton  wrote:
> 2. Why have a finish.replication file, rather than "SELECT
> pg_finish_replication()"?

I thought that the trigger file method was more easy-to-use for the
existing users since it had been used in pg_standby for a long time
so far. So I used it. But I agree that activating the standby via
SQL would be useful for some cases.

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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl

[Richard Huxton]

On 12/02/10 23:10, Tim Bunce wrote:

There was some discussion a few weeks ago about inter-stored-procedure
calling from PL/Perl.



I'd greatly appreciate any feedback.


Looks great.


PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from 
PostgreSQL PL/Perl


I don't think you show an example with an explicit schema name being 
used. Can't hurt to make it obvious.



 $seqn = call('nextval(regclass)', $sequence_name);


Is there any value in having a two-stage interface?

$seq_fn = get_call('nextval(regclass)');
$foo1   = $seq_fn->($seq1);
$foo2   = $seq_fn->($seq2);

--
  Richard Huxton
  Archonet Ltd

--
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] Re: [COMMITTERS] pgsql: Reduce the chatter to the log when starting a standby server.

[Heikki Linnakangas]

Fujii Masao wrote:
> On Sat, Feb 13, 2010 at 4:33 AM, Richard Huxton  wrote:
>> 2. Why have a finish.replication file, rather than "SELECT
>> pg_finish_replication()"?
> 
> I thought that the trigger file method was more easy-to-use for the
> existing users since it had been used in pg_standby for a long time
> so far. So I used it.

Also, you might not want to open the standby for connections (ie.
recovery_connections='off'). And even if you wanted, it might not open
because of some of the corner-case limitations in Hot Standby, so you
wouldn't be able to failover.

> But I agree that activating the standby via
> SQL would be useful for some cases.

Yeah.

-- 
  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