Re: [HACKERS] autovacuum and reloptions
Alvaro Herrera wrote: Euler Taveira de Oliveira escribió: Alvaro Herrera escreveu: Alvaro Herrera escribió: I have a separate branch on which I keep the old patch from Euler updated to the current reloptions code; so it is probably very similar to what Euler just sent. (I'll have a look at that soon anyway.) Huh, nevermind -- I thought that Euler had just sent an updated version of his patch, but only now I noticed that the message I was looking at is dated Nov. 21st. And I did it (It is even listed in the wiki). Last version was [1]. I fixed some problems and added some docs. Yes, the autovacuum patch I have is loosely based on that one IIRC. (I modified it heavily though.) Could someone udpate the status of this item in the commitfest wiki? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Controlling hot standby
As the patch stands, there's no way to disable hot standby. The server always opens for read-only connections as soon as it can. That might not be what you want. I think we need a GUC to enable/disable hot standby. It would become handy if the unimaginable happens and there's a bug in the hot standby code that prevents a server from recovering. -- 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] Pluggable Indexes
On Thu, 2009-01-22 at 18:45 -0500, Tom Lane wrote: There are other recent examples of proposed hooks that in fact failed to be useful because of some oversight or other, and it was not until we insisted on seeing a live use of the hooks that this became apparent. (IIRC, one or both of the planner-related hooks that are new in 8.4 had such issues.) Thank you for your support of the plugin concept. You make good points and are completely correct about the earlier plugin. The additional plugin capability was filling a gap that had been left when the planner plugin was added in 8.3. A similar thing happened with executor plugins IIRC. So I agree, new and complex plugin APIs need a working example otherwise they'll be wrong. In the current case, index APIs are already well known, so that API is unlikely to be a problem. The actual rmgr plugin API is very simple, since its intention is only to add or edit entries onto the internal RmgrTable (in memory) after which everything is well defined already. This is probably the simplest API that has been added in recent times. I'm happy to make the WAL filter plugin work correctly in all cases. It was intended as a demonstration only, but if that is a problem it is easily fixed. One of my clients has requested filtering capability alongside hot standby, so I will deliver it, even if that is rejected for reasons outside of my hands (such as timing). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] deductive databases in postgreSQL
Yes it's an option, but you cannot rely on the typical consulting company to do that. Do you know any specialized consulting boutique or individual developer that could do that? Carlos Gonzalez-Cadenas CEO, ExperienceOn - New generation search http://www.experienceon.com Mobile: +34 652 911 201 Skype: carlosgonzalezcadenas LinkedIn: http://www.linkedin.com/in/carlosgonzalezcadenas On Thu, Jan 22, 2009 at 7:16 PM, decibel deci...@decibel.org wrote: On Jan 22, 2009, at 8:24 AM, Euler Taveira de Oliveira wrote: No one that I know of. Well, it is a long road. The addition of a data type xml is recent (8.3). We lack a set of features like indexing, a new data structure (?), XQuery, XPath improvement and, so on [1]. Don't expect much of these TODO items completed before the next two releases (unless you want to take a stab). You could also possibly pay a consulting company to implement it, but even that isn't as easy as it may sound. :) -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Re: [HACKERS] Pluggable Indexes
Hmm, IIRC it is based on a monotonically increasing number. It could have been anything. LSN was just a monotonically increasing number that would be available if WAL was implemented first (or in parallel). You are right, but without WAL-logging we would need to implement some kind of sequence :) -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Controlling hot standby
On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote: As the patch stands, there's no way to disable hot standby. The server always opens for read-only connections as soon as it can. That might not be what you want. I think we need a GUC to enable/disable hot standby. It would become handy if the unimaginable happens and there's a bug in the hot standby code that prevents a server from recovering. Heaven forbid! I'll add it now, default = on. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
On Fri, 2009-01-23 at 10:05 +, Simon Riggs wrote: I'll add it now, default = on. Did you mean off? -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Controlling hot standby
On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote: On Fri, 2009-01-23 at 10:05 +, Simon Riggs wrote: I'll add it now, default = on. Did you mean off? No, do you? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote: As the patch stands, there's no way to disable hot standby. The server always opens for read-only connections as soon as it can. That might not be what you want. I think we need a GUC to enable/disable hot standby. It would become handy if the unimaginable happens and there's a bug in the hot standby code that prevents a server from recovering. Currently recovery processing is only active during archive recovery, never during crash recovery, for the above reason. I think we need a GUC to enable/disable hot standby. I presume you mean something that will stop us keeping track of snapshots and preventing users from connecting? Suggestion: hot_standby = on | off (on is default) (in recovery.conf) Would that include starting the bgwriter during recovery? Or should be a separate boolean? If needed, should this be in postgresql.conf or in recovery.conf? Suggestion: bgwriter_during_recovery = on | off (on is default) (in postgresql.conf) If we do have this it may be deprecated in later releases if code is stable and we have no reason to de-select this. This second option will give us a performance boost and low failover time, just without being able to run queries. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
Simon Riggs wrote: On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote: As the patch stands, there's no way to disable hot standby. The server always opens for read-only connections as soon as it can. That might not be what you want. I think we need a GUC to enable/disable hot standby. It would become handy if the unimaginable happens and there's a bug in the hot standby code that prevents a server from recovering. Currently recovery processing is only active during archive recovery, never during crash recovery, for the above reason. Oh, ok. Is the bgwriter still launched in crash recovery, and do we start accepting connections as soon as the replay finishes, before the first checkpoint is finished? I think we need a GUC to enable/disable hot standby. I presume you mean something that will stop us keeping track of snapshots and preventing users from connecting? Suggestion: hot_standby = on | off (on is default) (in recovery.conf) Right. Would that include starting the bgwriter during recovery? Or should be a separate boolean? If needed, should this be in postgresql.conf or in recovery.conf? I don't think that's necessary. -- 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] Controlling hot standby
On Fri, 2009-01-23 at 14:28 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Fri, 2009-01-23 at 10:35 +0200, Heikki Linnakangas wrote: As the patch stands, there's no way to disable hot standby. The server always opens for read-only connections as soon as it can. That might not be what you want. I think we need a GUC to enable/disable hot standby. It would become handy if the unimaginable happens and there's a bug in the hot standby code that prevents a server from recovering. Currently recovery processing is only active during archive recovery, never during crash recovery, for the above reason. Oh, ok. Is the bgwriter still launched in crash recovery, Currently in the patch we never launch bgwriter in crash recovery. and do we start accepting connections as soon as the replay finishes, before the first checkpoint is finished? Which means we never use fast start behaviour. We write a full checkpoint and only then do we open for business normally. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] [PATCHES] GIN improvements
I'm very sorry, but v0.24 has a silly bug with not initialized value :(. New version is attached -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ fast_insert_gin-0.25.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] reloptions - RELOPT_KIND_ALL
I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Zdenek diff -Nrc pgsql_spacereserve.4cf1ae611238/src/backend/access/common/reloptions.c pgsql_spacereserve/src/backend/access/common/reloptions.c *** pgsql_spacereserve.4cf1ae611238/src/backend/access/common/reloptions.c 2009-01-23 14:09:43.544267884 +0100 --- pgsql_spacereserve/src/backend/access/common/reloptions.c 2009-01-23 14:09:43.548835990 +0100 *** *** 592,598 /* Build a list of expected options, based on kind */ for (i = 0; relOpts[i]; i++) ! if (relOpts[i]-kind == kind) numoptions++; if (numoptions == 0) --- 609,615 /* Build a list of expected options, based on kind */ for (i = 0; relOpts[i]; i++) ! if (relOpts[i]-kind == kind || relOpts[i]-kind == RELOPT_KIND_ALL) numoptions++; if (numoptions == 0) *** *** 605,611 for (i = 0, j = 0; relOpts[i]; i++) { ! if (relOpts[i]-kind == kind) { reloptions[j].gen = relOpts[i]; reloptions[j].isset = false; --- 622,628 for (i = 0, j = 0; relOpts[i]; i++) { ! if (relOpts[i]-kind == kind || relOpts[i]-kind == RELOPT_KIND_ALL) { reloptions[j].gen = relOpts[i]; reloptions[j].isset = false; diff -Nrc pgsql_spacereserve.4cf1ae611238/src/include/access/reloptions.h pgsql_spacereserve/src/include/access/reloptions.h *** pgsql_spacereserve.4cf1ae611238/src/include/access/reloptions.h 2009-01-23 14:09:43.546952126 +0100 --- pgsql_spacereserve/src/include/access/reloptions.h 2009-01-23 14:09:43.549952702 +0100 *** *** 32,37 --- 32,38 /* kinds supported by reloptions */ typedef enum relopt_kind { + RELOPT_KIND_ALL, RELOPT_KIND_HEAP, /* XXX do we need a separate kind for TOAST tables? */ RELOPT_KIND_BTREE, -- 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] [PATCH] reloptions - RELOPT_KIND_ALL
Zdenek Kotala escreveu: I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Doesn't work. One of the reasons to separate relation kinds was that different kinds have different min/max values (see fillfactor for an example). -- Euler Taveira de Oliveira http://www.timbira.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] [PATCH] reloptions - RELOPT_KIND_ALL
Zdenek Kotala wrote: I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Do you have an example use case for this? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] [PATCH] reloptions - RELOPT_KIND_ALL
Alvaro Herrera píše v pá 23. 01. 2009 v 11:04 -0300: Zdenek Kotala wrote: I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Do you have an example use case for this? I use it in my space reservation patch. I going to send it soon. The default value is zero in all relkind and max value as well. It seems to me waste a space to copy and paste same lines several times. Zdenek -- 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] [PATCH] reloptions - RELOPT_KIND_ALL
Zdenek Kotala wrote: Alvaro Herrera píše v pá 23. 01. 2009 v 11:04 -0300: Zdenek Kotala wrote: I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Do you have an example use case for this? I use it in my space reservation patch. I going to send it soon. The default value is zero in all relkind and max value as well. It seems to me waste a space to copy and paste same lines several times. Hmm, but is it really applicable to all relkinds? Is it applicable to GIN indexes for example? Keep in mind that GIN does not even have fillfactor. How are you going to make space reservation work for GIN? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Hot Standby (v9d)
Simon Riggs wrote: * Put corrected version into GIT * Produce outstanding items as patch-on-patch via GIT I've applied the hot standby patch and recovery infra v9 patch to branches in my git repository, and pushed those to: git://git.postgresql.org/git/~hlinnaka/pgsql.git You can clone that to get started. I've set those branches up so that the hot standby branch is branched off from the recovery infra branch. I'd like to keep the two separate, as the recovery infra patch is useful on it's own, and can be reviewed separately. As a teaser, I made a couple of minor changes after importing your patches. For the sake of the archives, I've also included those changes as patches here. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com From 6d583356063c9d3c8d0e69233a40065bc5d7bde1 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas hei...@enterprisedb.com Date: Fri, 23 Jan 2009 14:37:05 +0200 Subject: [PATCH] Remove padding in XLogCtl; might be a good idea, but let's keep it simple for now. Also remove the XLogCtl-mode_lck spinlock, which is pretty pointless for a single boolean that's only written by one process. --- src/backend/access/transam/xlog.c | 24 +--- 1 files changed, 1 insertions(+), 23 deletions(-) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index fcf5657..42c4552 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -340,18 +340,11 @@ typedef struct XLogCtlWrite /* * Total shared-memory state for XLOG. - * - * This small structure is accessed by many backends, so we take care to - * pad out the parts of the structure so they can be accessed by separate - * CPUs without causing false sharing cache flushes. Padding is generous - * to allow for a wide variety of CPU architectures. */ -#define XLOGCTL_BUFFER_SPACING 128 typedef struct XLogCtlData { /* Protected by WALInsertLock: */ XLogCtlInsert Insert; - char InsertPadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogCtlInsert)]; /* Protected by info_lck: */ XLogwrtRqst LogwrtRqst; @@ -359,16 +352,9 @@ typedef struct XLogCtlData uint32 ckptXidEpoch; /* nextXID epoch of latest checkpoint */ TransactionId ckptXid; XLogRecPtr asyncCommitLSN; /* LSN of newest async commit */ - /* add data structure padding for above info_lck declarations */ - char InfoPadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogwrtRqst) - - sizeof(XLogwrtResult) - - sizeof(uint32) - - sizeof(TransactionId) - - sizeof(XLogRecPtr)]; /* Protected by WALWriteLock: */ XLogCtlWrite Write; - char WritePadding[XLOGCTL_BUFFER_SPACING - sizeof(XLogCtlWrite)]; /* * These values do not change after startup, although the pointed-to pages @@ -390,11 +376,8 @@ typedef struct XLogCtlData * always during Recovery Processing Mode. This allows us to identify * code executed *during* Recovery Processing Mode but not necessarily * by Startup process itself. - * - * Protected by mode_lck */ bool SharedRecoveryProcessingMode; - slock_t mode_lck; /* * recovery target control information @@ -410,8 +393,6 @@ typedef struct XLogCtlData TransactionId recoveryLastXid; XLogRecPtr recoveryLastRecPtr; - char InfoLockPadding[XLOGCTL_BUFFER_SPACING]; - slock_t info_lck; /* locks shared variables shown above */ } XLogCtlData; @@ -4399,7 +4380,6 @@ XLOGShmemInit(void) XLogCtl-XLogCacheBlck = XLOGbuffers - 1; XLogCtl-Insert.currpage = (XLogPageHeader) (XLogCtl-pages); SpinLockInit(XLogCtl-info_lck); - SpinLockInit(XLogCtl-mode_lck); /* * If we are not in bootstrap mode, pg_control should already exist. Read @@ -6183,9 +6163,7 @@ IsRecoveryProcessingMode(void) if (xlogctl == NULL) return false; - SpinLockAcquire(xlogctl-mode_lck); - LocalRecoveryProcessingMode = XLogCtl-SharedRecoveryProcessingMode; - SpinLockRelease(xlogctl-mode_lck); + LocalRecoveryProcessingMode = xlogctl-SharedRecoveryProcessingMode; } knownProcessingMode = true; -- 1.5.6.5 From 4061ac8f84cc699bf0f417689f853791089ed472 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas hei...@enterprisedb.com Date: Fri, 23 Jan 2009 15:55:33 +0200 Subject: [PATCH] Remove knownProcessingMode variable. --- src/backend/access/transam/xlog.c | 19 --- 1 files changed, 8 insertions(+), 11 deletions(-) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 7e480e2..e64fb48 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -126,9 +126,11 @@ bool InRecovery = false; /* Are we recovering using offline XLOG archives? */ static bool InArchiveRecovery = false; -/* Local copy of shared RecoveryProcessingMode state */ +/* + * Local copy of shared RecoveryProcessingMode state. True actually + * means not known, need to check the shared state + */ static bool LocalRecoveryProcessingMode = true; -static bool
[HACKERS] AIX 4.3 getaddrinfo busted
AIX 4.3 was released in late 1999, so I thought it was worth mentioning. I only have AIX 4.3 and 6.1, so I have no idea how AIX 5 handles this. AIX 6.1 works fine. Anyways, the service argument to getaddrinfo is busted on AIX 4.3, thus src/backend/libpq/ip.c pg_getaddrinfo_all() is busted on this platform. It fails with EAI_NODATA Host not found. If this argument is left NULL, everything works. I can supply a patch to fix this. My suggestion would be to always supply a NULL service to getaddrinfo. After a successful call, set the port if it was provided ... htons((unsigned short)atoi(servname)). This approach avoids a configure check. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] AIX 4.3 getaddrinfo busted
Andrew Chernow wrote: AIX 4.3 was released in late 1999, so I thought it was worth mentioning. I only have AIX 4.3 and 6.1, so I have no idea how AIX 5 handles this. AIX 6.1 works fine. Anyways, the service argument to getaddrinfo is busted on AIX 4.3, thus src/backend/libpq/ip.c pg_getaddrinfo_all() is busted on this platform. It fails with EAI_NODATA Host not found. If this argument is left NULL, everything works. I can supply a patch to fix this. My suggestion would be to always supply a NULL service to getaddrinfo. After a successful call, set the port if it was provided ... htons((unsigned short)atoi(servname)). This approach avoids a configure check. Why would we risk breaking other platforms to avoid an AIX bug? At best we can put a code comment in that section of the code. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] AIX 4.3 getaddrinfo busted
On Fri, Jan 23, 2009 at 9:18 AM, Andrew Chernow a...@esilo.com wrote: AIX 4.3 was released in late 1999, so I thought it was worth mentioning. I only have AIX 4.3 and 6.1, so I have no idea how AIX 5 handles this. AIX 6.1 works fine. Anyways, the service argument to getaddrinfo is busted on AIX 4.3, thus src/backend/libpq/ip.c pg_getaddrinfo_all() is busted on this platform. It fails with EAI_NODATA Host not found. If this argument is left NULL, everything works. I can supply a patch to fix this. My suggestion would be to always supply a NULL service to getaddrinfo. After a successful call, set the port if it was provided ... htons((unsigned short)atoi(servname)). This approach avoids a configure check. FYI: There are AIX 5.3 nodes on BuildFarm - if the change is a regression, it will be noticed :-). -- http://linuxfinances.info/info/linuxdistributions.html George Burns - You can't help getting older, but you don't have to get old. -- 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] AIX 4.3 getaddrinfo busted
Bruce Momjian wrote: Andrew Chernow wrote: AIX 4.3 was released in late 1999, so I thought it was worth mentioning. I only have AIX 4.3 and 6.1, so I have no idea how AIX 5 handles this. AIX 6.1 works fine. Anyways, the service argument to getaddrinfo is busted on AIX 4.3, thus src/backend/libpq/ip.c pg_getaddrinfo_all() is busted on this platform. It fails with EAI_NODATA Host not found. If this argument is left NULL, everything works. I can supply a patch to fix this. My suggestion would be to always supply a NULL service to getaddrinfo. After a successful call, set the port if it was provided ... htons((unsigned short)atoi(servname)). This approach avoids a configure check. Why would we risk breaking other platforms to avoid an AIX bug? At best we can put a code comment in that section of the code. IMO, there is no risk. getaddrinfo allows a NULL second argument on every platform I have worked with. I can't see this breaking anything. Our internal libraries do it this way for this exact reason, and it works on a large number of platforms. But, it can be done so that it only affects AIX machines. There is already a #ifdef _AIX block in this function so we can handle all AIX versions as I have suggested and let everyone else run the same old code. #ifdef _AIX getaddrinfo(hostname, NULL /* servname */, ); // manually set the port #else getaddrinfo(hostname, servname, ); /* same code as now */ #endif -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] [PATCH] reloptions - RELOPT_KIND_ALL
Alvaro Herrera píše v pá 23. 01. 2009 v 11:14 -0300: Zdenek Kotala wrote: Alvaro Herrera píše v pá 23. 01. 2009 v 11:04 -0300: Zdenek Kotala wrote: I attached patch which add capability to have single record for all realation kind in the reloption list. It is usefull in situation when all parameters are same for all relation kinds. Do you have an example use case for this? I use it in my space reservation patch. I going to send it soon. The default value is zero in all relkind and max value as well. It seems to me waste a space to copy and paste same lines several times. Hmm, but is it really applicable to all relkinds? Is it applicable to GIN indexes for example? Keep in mind that GIN does not even have fillfactor. How are you going to make space reservation work for GIN? Yes it is. I need two new reloptions - rs_perpage and rs_pertuple. I don't use fillfactor. I modified PageGetFreeSpace function to return correct value. These two options are applicable on all relkind (including toast*) which use standard page layout - currently all AM. Zdenek * I know that toast is not supported yet. -- 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] AIX 4.3 getaddrinfo busted
Christopher Browne wrote: FYI: There are AIX 5.3 nodes on BuildFarm - if the change is a regression, it will be noticed :-). This confirms that its an isolated AIX 4.3 bug. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Controlling hot standby
Simon Riggs wrote: On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote: On Fri, 2009-01-23 at 10:05 +, Simon Riggs wrote: I'll add it now, default = on. Did you mean off? No, do you? Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] AIX 4.3 getaddrinfo busted
Andrew Chernow wrote: Bruce Momjian wrote: Andrew Chernow wrote: AIX 4.3 was released in late 1999, so I thought it was worth mentioning. I only have AIX 4.3 and 6.1, so I have no idea how AIX 5 handles this. AIX 6.1 works fine. Anyways, the service argument to getaddrinfo is busted on AIX 4.3, thus src/backend/libpq/ip.c pg_getaddrinfo_all() is busted on this platform. It fails with EAI_NODATA Host not found. If this argument is left NULL, everything works. I can supply a patch to fix this. My suggestion would be to always supply a NULL service to getaddrinfo. After a successful call, set the port if it was provided ... htons((unsigned short)atoi(servname)). This approach avoids a configure check. Why would we risk breaking other platforms to avoid an AIX bug? At best we can put a code comment in that section of the code. IMO, there is no risk. getaddrinfo allows a NULL second argument on every platform I have worked with. I can't see this breaking anything. Our internal libraries do it this way for this exact reason, and it works on a large number of platforms. But, it can be done so that it only affects AIX machines. There is already a #ifdef _AIX block in this function so we can handle all AIX versions as I have suggested and let everyone else run the same old code. #ifdef _AIX getaddrinfo(hostname, NULL /* servname */, ); // manually set the port #else getaddrinfo(hostname, servname, ); /* same code as now */ #endif Well, the platform is from 1999 and we have never had bug reports on this, even though the code has been there for quite a few years; I just don't see the motivation for the change. If you really want this platform to work, I would submit a patch that tests for a C compiler symbol or #define that is only defined for that platform and make service = null in that case. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] AIX 4.3 getaddrinfo busted
Andrew Chernow wrote: Christopher Browne wrote: FYI: There are AIX 5.3 nodes on BuildFarm - if the change is a regression, it will be noticed :-). This confirms that its an isolated AIX 4.3 bug. It confirms that the bug exists in 4.3 but not on 5.3; not sure how you can make assumptions about intermediate releases. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Controlling hot standby
On 1/23/09, Alvaro Herrera alvhe...@commandprompt.com wrote: Simon Riggs wrote: On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote: On Fri, 2009-01-23 at 10:05 +, Simon Riggs wrote: I'll add it now, default = on. Did you mean off? No, do you? Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. Is 'hot standby' going to be the official moniker for the feature? (not 'standby replication', or something else?). I wonder if we should pick something more descriptive. merlin -- 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] deductive databases in postgreSQL
On Fri, Jan 23, 2009 at 10:10:55AM +0100, Carlos Gonzalez-Cadenas wrote: Yes it's an option, but you cannot rely on the typical consulting company to do that. Do you know any specialized consulting boutique or individual developer that could do that? Sending an email to pgsql-j...@postgresql.org couldn't hurt. -- Sam http://samason.me.uk/ -- 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] Controlling hot standby
Merlin Moncure wrote: Is 'hot standby' going to be the official moniker for the feature? (not 'standby replication', or something else?). I wonder if we should pick something more descriptive. Could also be something like allow_connections_during_recovery. I'd keep the word replication out of this.. -- 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] AIX 4.3 getaddrinfo busted
Bruce Momjian wrote: If you really want this platform to work, I would submit a patch that tests for a C compiler symbol or #define that is only defined for that platform and make service = null in that case. I am not aware of such an animal. I looked at the output of touch x.c gcc -v -dM -E x.c but didn't find anything when compared to AIX 6.1. We could set one in configure (like _AIXVER43) or use _AIX + uname(). How about checking for the failure case, where getaddrinfo returns EAI_NODATA on _AIX with a non-NULL servname. If that happens, call getaddrinfo again with a NULL servname. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] problem with archive_command as suggested by documentation
On Fri, 2009-01-23 at 08:20 +0100, Albe Laurenz wrote: Perhaps it should suggest something like: test ! -f .../%f cp %p .../%f.tmp mv .../%f.tmp .../%f ie. copy under a different filename first, and rename the file in place after it's completely written, assuming that mv is atomic. It gets a bit complicated, though. That's a good idea (although it could lead to race conditions in the extremely rare case that two clusters want to archive equally named files at the same time). I'll write a patch for that and send it as basis for a discussion. The example is to help you understand things, not to solve every case. I think it should start simply and then have additional comments later. I don't think that particular example is a good one since the whole point of the archive is that it should be off-server. If we're going to be exact about the example then we should give a more realistic one, like using scp. Unfortunately, there is no secure-remote-move command, so doing the above with scp would resend the whole file 3 times. I think it's better to write a script... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Merlin Moncure wrote: Is 'hot standby' going to be the official moniker for the feature? (not 'standby replication', or something else?). I wonder if we should pick something more descriptive. Could also be something like allow_connections_during_recovery. I'd keep the word replication out of this.. Just wondering, but does this make more sense as a recovery.conf parameter? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] AIX 4.3 getaddrinfo busted
Andrew Chernow a...@esilo.com writes: Bruce Momjian wrote: Why would we risk breaking other platforms to avoid an AIX bug? At best we can put a code comment in that section of the code. IMO, there is no risk. getaddrinfo allows a NULL second argument on every platform I have worked with. The portability risk is in the manually set the port part. 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] Table Partitioning Feature
Hi Emmanuel, Please find my comments in-lined: On 1/23/09, Emmanuel Cecchet m...@frogthinker.org wrote: Amit, You might want to put this on the http://wiki.postgresql.org/wiki/Table_partitioning wiki page. Sure. How does your timeline look like for this implementation? The implementation is planned as follows: - Partition table commands ++ An intermediate patch in Feb end ++ Final patch in mid March - Global Index: Mid March - Optimizer changes for partitioned table: May I would be happy to contribute C triggers to your implementation. From what I understood in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00269.php, you already have an implementation that parses the grammar and generates rules as if someone had written them. Is this code available? We have just started with the implementation, i will post the grammar rules next week. Regarding the use of triggers to push/move data to partitions, what if someone declares triggers on partitions? Especially if you have subpartitions, let's consider the case where there is a trigger on the parent, child and grandchild. If I do an insert in the parent, the user trigger on the parent will be executed, then the partition trigger that decides to move to the grandchild. Are we going to bypass the child trigger? We are not supporting sub-partitioning - There is just one level of partitioning. If we also want fast COPY operations on partitioned table, we could have an optimized implementation that could bypass triggers and move the tuple directly to the appropriate child table. We will definitely consider to implement fast COPY after we are done with the planned tasks. Thanks, Amit Thanks for this big contribution, Emmanuel Hi, We are implementing table partitioning feature to support - the attached commands. The syntax conforms to most of the suggestion mentioned in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring the following: -- Specification of partition names is optional. System will be able to generate partition names in such cases. -- sub partitioning We are using pgsql triggers to push/move data to appropriate partitions, but we will definitely consider moving to C language triggers as suggested by manu. - Global non-partitioned indexes (that will extend all the partitions). - Foreign key support for tables referring to partitioned tables. Please feel free to post your comments and suggestions. Thanks, Amit Persistent Systems -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: m...@frogthinker.org Skype: emmanuel_cecchet
Re: [HACKERS] [PATCH] reloptions - RELOPT_KIND_ALL
Zdenek Kotala zdenek.kot...@sun.com writes: Alvaro Herrera pÃÅ¡e v pá 23. 01. 2009 v 11:04 -0300: Do you have an example use case for this? I use it in my space reservation patch. I going to send it soon. Haven't we been over that ground already? A user-settable reloption is not a reasonable solution to a space-reservation problem. The potential for errors of commission and omission is too great. 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] Hot Standby (v9d)
On Fri, 2009-01-23 at 16:14 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: * Put corrected version into GIT * Produce outstanding items as patch-on-patch via GIT I've applied the hot standby patch and recovery infra v9 patch to branches in my git repository, and pushed those to: git://git.postgresql.org/git/~hlinnaka/pgsql.git You can clone that to get started. I've set those branches up so that the hot standby branch is branched off from the recovery infra branch. I'd like to keep the two separate, as the recovery infra patch is useful on it's own, and can be reviewed separately. As a teaser, I made a couple of minor changes after importing your patches. For the sake of the archives, I've also included those changes as patches here. OK, I'll look at those. I've fixed 6 bugs today (!), so I'd rather go for the new version coming out in an hour. That's too many to unpick unfortunately. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-01-23 at 12:58 +0200, Devrim GÜNDÜZ wrote: On Fri, 2009-01-23 at 10:05 +, Simon Riggs wrote: I'll add it now, default = on. Did you mean off? No, do you? Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. It is on by default. Why would you want it off by default? We want people to use the feature and turn it off only if they need to for some reason. It is only used during archive recovery, so it is already off if you're using some other kind of replication. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Pluggable Indexes
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2009-01-22 at 18:45 -0500, Tom Lane wrote: There are other recent examples of proposed hooks that in fact failed to be useful because of some oversight or other, and it was not until we insisted on seeing a live use of the hooks that this became apparent. In the current case, index APIs are already well known, so that API is unlikely to be a problem. The actual rmgr plugin API is very simple, since its intention is only to add or edit entries onto the internal RmgrTable (in memory) after which everything is well defined already. Right, the WAL-record-processing API is not really at issue, since it's been proven internally to the core code. My concern is with the other part, namely exactly how are we going to identify and install additional rmgrs. There was substantial debate about that when it first came up, so you're not likely to convince me that it's such an open-and-shut case as to not need supporting evidence. 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] problem with archive_command as suggested by documentation
Albe Laurenz laurenz.a...@wien.gv.at writes: Heikki Linnakangas wrote: Well, the documentation states the reason to do that: This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory) But isn't it true that the vast majority of people have only one PostgreSQL cluster per machine, and it is highly unlikely that somebody else creates a file with the same name as a WAL segment in the archive directory? That's not the point. You'd typically be sending the WAL archive to another machine (via NFS or FTP or whatever), and it's not very hard at all to imagine accidentally setting up two different machines to point at the same archive directory on the same backup server. For instance, imagine that you're cloning your DB in preparation for an upgrade. You'll probably start by copying your configuration file... 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] AIX 4.3 getaddrinfo busted
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Bruce Momjian wrote: Why would we risk breaking other platforms to avoid an AIX bug? At best we can put a code comment in that section of the code. IMO, there is no risk. getaddrinfo allows a NULL second argument on every platform I have worked with. The portability risk is in the manually set the port part. Right. If this method is limited to _AIX and only when the failure case occurs, there are no portability issues. I've already confirmed my fix works on 4.3 and 6.1. The buildfarm will tell us if 5.3 works (very likely it will). -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Hot standby, conflict resolution
The FATAL and ERROR cancellation modes are quite different. In FATAL mode, you just want to kill the backend. The target connection doesn't need to know the LSN. In ERROR mode, you don't really want to interrupt the target backend. In ReadBuffer, you're checking a global variable, BufferRecoveryConflictPending on each call, and if it's set, you check the buffer's LSN against the LSN of the earliest LSN conflicting LSN, and throw an error if it's greater than that. Why do we jump through so many hoops to get the earliest conflicting LSN to where it's needed? At the moment: 1. Startup process sets the LSN in the target backend's PGPROC entry, and signals it with SIGINT. 2. The target backend receives the signal; ProcessInterrupts is called either immediately or at the next CHECK_FOR_INTERRUPTS() call. 3. ProcessInterrupts reads the value from PGPROC, and passes it to bufmgr.c ISTM that if ReadBuffer read the value directly from the PGPROC entry, there would be no need for the signaling (in the ERROR mode). Correct me if I'm wrong, but I thought the idea of this new conflict resolution was that the startup process doesn't need to wait for the target backend to die. Instead, the target backend knows to commit suicide if it stumbles into a buffer that's been modified in a conflicting way. Looking at ResolveRecoveryConflictWithVirtualXIDs, it looks like we still wait. -- 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] Hot Standby (v9d)
Simon Riggs wrote: On Fri, 2009-01-23 at 16:14 +0200, Heikki Linnakangas wrote: I made a couple of minor changes after importing your patches. I've applied them both to v9g, attached here. If you wouldn't mind redoing the initial step, I will promise not to do anything else to the code, except via patch on GIT. No problem. I don't need to do it from scratch, I'll just apply the changes from that patch as an incremental commit. Done, you can see it in my git repo now too. -- 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] Hot Standby (v9d)
@@ -1601,6 +1602,24 @@ BufferProcessRecoveryConflictsIfAny(volatile BufferDesc *bufHdr) { XLogRecPtr bufLSN = BufferGetLSN(bufHdr); + /* +* If the buffer is recent we may need to cancel ourselves +* rather than risk returning a wrong answer. This test is +* too conservative, but it is correct. +* +* We only need to cancel the current subtransaction. +* Once we've handled the error then other subtransactions can +* continue processing. Note that we do *not* reset the +* BufferRecoveryConflictLSN at subcommit/abort, but we do +* reset it if we release our last remaining sbapshot. +* see SnapshotResetXmin() +* Is it really enough to cancel just the current subtransaction? What if it's a serializable transaction? -- 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] Controlling hot standby
On Fri, 2009-01-23 at 17:07 +0200, Heikki Linnakangas wrote: Merlin Moncure wrote: Is 'hot standby' going to be the official moniker for the feature? (not 'standby replication', or something else?). I wonder if we should pick something more descriptive. Could also be something like allow_connections_during_recovery. I'd keep the word replication out of this.. I suspect that it is used much more during hot standby than during simple recovery, which would mean something you do after unclean shutdown or when restoring from backup. maybe allow_hot_slaves ;) -- -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Controlling hot standby
Simon Riggs wrote: On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote: Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. It is on by default. Why would you want it off by default? Would it slow down the normal recovery after a crash if I don't have any slaves? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Controlling hot standby
Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote: Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. It is on by default. Why would you want it off by default? Would it slow down the normal recovery after a crash if I don't have any slaves? Not in any meaningful way, I'd imagine. -- 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] Controlling hot standby
Alvaro Herrera alvhe...@commandprompt.com wrote: Simon Riggs wrote: It is on by default. Why would you want it off by default? Would it slow down the normal recovery after a crash if I don't have any slaves? And how about during traditional PITR recovery? -Kevin -- 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] AIX 4.3 getaddrinfo busted
Andrew Chernow a...@esilo.com writes: Tom Lane wrote: The portability risk is in the manually set the port part. Right. If this method is limited to _AIX and only when the failure case occurs, there are no portability issues. That seems like unnecessary complexity (which carries its own risks). I thought this sketch was about right: #ifdef _AIX getaddrinfo(hostname, NULL /* servname */, ); // manually set the port #else getaddrinfo(hostname, servname, ); /* same code as now */ #endif although please flip it around to ifndef. The simpler and more general case should usually be first to aid the reader. 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] Controlling hot standby
Could also be something like allow_connections_during_recovery. +1 (should we say continuous recovery?) I'd keep the word replication out of this.. +1. ...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] AIX 4.3 getaddrinfo busted
BTW, what about the comments in ip.c to the effect that some versions of AIX fail when getaddrinfo's second argument *is* null? Right at the moment I'm wondering why we are going to change the code now to support a ten-year-old OS version that evidently no one has tried to use Postgres on before. 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] AIX 4.3 getaddrinfo busted
Tom Lane wrote: BTW, what about the comments in ip.c to the effect that some versions of AIX fail when getaddrinfo's second argument *is* null? For starters, it indicates that sin_port is not zero'd properly. That wouldn't matter here since the plan is to manually set the port in this case, since providing it to getaddrinfo is broken. Also, this is why I suggested only doing a NULL 2nd arg within the failure case. rc = getaddrinfo(hostname, service, ); #ifdef _AIX if(rc == EAI_NODATA servname *servname) if(!(rc = getaddrinfo(hostname, NULL, ))) /* try again */ /* set sin_port or sin6_port */ #endif // code continues as is from here down. Or, set a define in configure indicating aix 4.3 or indicating the 2nd arg to getaddrinfo must be NULL (the former being much easier). I don't think we have to resort to configure checks though. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Controlling hot standby
On Fri, 2009-01-23 at 13:28 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-01-23 at 11:28 -0300, Alvaro Herrera wrote: Depends on the setting :-) It is hot_standby=off by default, right? I think having a double negative disable_hot_standby=off would be awkward. It is on by default. Why would you want it off by default? Would it slow down the normal recovery after a crash if I don't have any slaves? No -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] AIX 4.3 getaddrinfo busted
On 1/23/09, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment I'm wondering why we are going to change the code now to support a ten-year-old OS version that evidently no one has tried to use Postgres on before. I'd like to address this observation. You may have noticed that eSilo has been contributing a number of patches to Postgres involving legacy systems. Understandably, there is very little overlap between modern versions of Postgres and these aging unixen. However, quite a few of these systems are still in production serving legacy applications. eSilo provides backup software. Based on customer feedback we came up with a list of systems that are under-represented by current backup solutions in the industry . For various reasons, we decided to involve libpq in our backup client and market aggressively to platforms for which there are very few backup options. libpq is quite portable, but there are a few understandable nits that have popped up here and there over time for older systems. We are providing fixes for those nits to the community. merlin -- 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] Controlling hot standby
On Fri, 2009-01-23 at 10:35 -0600, Kevin Grittner wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: Simon Riggs wrote: It is on by default. Why would you want it off by default? Would it slow down the normal recovery after a crash if I don't have any slaves? And how about during traditional PITR recovery? It is possible that it might slow down a traditional PITR recovery. But we enable the bgwriter to do checkpoints if we have it enabled, which were a major cause of slow down during PITR anyway. There are considerable benefits to having it turned on during PITR Please read this to see why http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] autovacuum and reloptions
Alvaro Herrera escribió: Here's my proposed patch. There is a bug in the handling of TOAST tables; I'm sending this as a WIP to add it to the commitfest status page for this patch. Sorry, that was a really stupid bug. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. diff -u src/backend/postmaster/autovacuum.c src/backend/postmaster/autovacuum.c --- src/backend/postmaster/autovacuum.c 23 Jan 2009 15:56:56 - +++ src/backend/postmaster/autovacuum.c 23 Jan 2009 16:49:37 - @@ -1900,7 +1900,7 @@ /* create hash table for toast - main relid mapping */ MemSet(ctl, 0, sizeof(ctl)); ctl.keysize = sizeof(Oid); - ctl.entrysize = sizeof(Oid) * 2; + ctl.entrysize = sizeof(av_relation); ctl.hash = oid_hash; table_toast_map = hash_create(TOAST to main relid map, -- 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] Controlling hot standby
Simon Riggs si...@2ndquadrant.com wrote: There are considerable benefits to having it turned on during PITR Please read this to see why http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery Am I reading this right? What I get out of it is that users can connect to the database during recovery, like with psql or JDBC? Any user can query recovery completion status and progress, while a superuser can also step through transactions in various ways? Can the data in the database be queried while recovery is paused? If I'm understanding this, and data can be queried during a pause, I can certainly see use cases for this; but it might be important to be able to start the recovery in a paused state to avoid going too far in the transaction stream before a connection can be established and recovery paused. -Kevin -- 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: Automatic view update rules Bernd Helmle
pet...@postgresql.org (Peter Eisentraut) writes: Automatic view update rules This patch is still a few bricks shy of a load ... within a few moments of starting to look at it I'd noticed two different failure conditions regression=# \d box_tbl Table public.box_tbl Column | Type | Modifiers +--+--- f1 | box | regression=# create view v1 as select * from box_tbl; ERROR: could not identify an equality operator for type box regression=# create view v1 as select box_tbl from box_tbl; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. and I'm sure there are quite a few more. These things are not that hard to fix in themselves, but what disturbs me more is the basic nature of the generated rules. regression=# create view v1 as select * from int8_tbl where q1 1000; NOTICE: CREATE VIEW has created automatic view update rules CREATE VIEW regression=# \d v1 View public.v1 Column | Type | Modifiers ++--- q1 | bigint | q2 | bigint | View definition: SELECT int8_tbl.q1, int8_tbl.q2 FROM int8_tbl WHERE int8_tbl.q1 1000; Rules: _DELETE AS ON DELETE TO v1 DO INSTEAD DELETE FROM int8_tbl WHERE (old.q1 IS NULL AND int8_tbl.q1 IS NULL OR old.q1 = int8_tbl.q1) AND (old.q2 IS NULL AND int8_tbl.q2 IS NULL OR old.q2 = int8_tbl.q2) RETURNING old.q1, old.q2 _INSERT AS ON INSERT TO v1 DO INSTEAD INSERT INTO int8_tbl (q1, q2) VALUES (new.q1, new.q2) RETURNING new.q1, new.q2 _UPDATE AS ON UPDATE TO v1 DO INSTEAD UPDATE int8_tbl SET q1 = new.q1, q2 = new.q2 WHERE (old.q1 IS NULL AND int8_tbl.q1 IS NULL OR old.q1 = int8_tbl.q1) AND (old.q2 IS NULL AND int8_tbl.q2 IS NULL OR old.q2 = int8_tbl.q2) RETURNING new.q1, new.q2 This has got two big problems. The first is the incredibly inefficient nature of the resulting plans, e.g, regression=# explain update v1 set q1 = q1 + 1000 where q1 = 42; QUERY PLAN -- -- Nested Loop (cost=0.00..2.20 rows=1 width=22) Join Filter: public.int8_tbl.q1 IS NULL) AND (public.int8_tbl.q1 IS NULL)) OR (public.int8_tbl.q1 = public.int8_tbl.q1)) AND (((public.int8_tbl.q2 IS NULL) AND (public.int8_tbl.q2 IS NULL)) OR (public.int8_tbl.q2 = public.int8_tbl.q2))) - Seq Scan on int8_tbl (cost=0.00..1.07 rows=1 width=16) Filter: ((q1 1000) AND (q1 = 42)) - Seq Scan on int8_tbl (cost=0.00..1.05 rows=5 width=22) (5 rows) If we ship this, we will be a laughingstock. The other problem (which is related to the first failure condition exhibited above) is the assumption that the default btree equality operator for a data type is real equality. Even if it exists, that's a bad assumption --- it falls down for float8 and numeric let alone any more-interesting datatypes such as the geometric types. It would probably be better if we insisted that the view's base be a plain relation and used ctid equality in the update rules (which will in turn require supporting TidScan as an inner join indexscan, but that's doable). In short, I don't feel that this was ready to be applied. It's probably fixable with a week or so's work, but do we want to be expending that kind of effort on it at this stage of the release cycle? 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] AIX 4.3 getaddrinfo busted
Andrew Chernow a...@esilo.com writes: Tom Lane wrote: BTW, what about the comments in ip.c to the effect that some versions of AIX fail when getaddrinfo's second argument *is* null? For starters, it indicates that sin_port is not zero'd properly. That wouldn't matter here since the plan is to manually set the port in this case, since providing it to getaddrinfo is broken. Hmm ... so actually we could get *rid* of that special case if we added this one. Okay, I withdraw the complaint. We should simply not rely on getaddrinfo to do anything right at all w.r.t. the port if we are running on AIX. Pass NULL for servname and set the port ourselves in all cases. 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] Hot standby, conflict resolution
On Fri, 2009-01-23 at 17:51 +0200, Heikki Linnakangas wrote: In ERROR mode, you don't really want to interrupt the target backend. In ReadBuffer, you're checking a global variable, BufferRecoveryConflictPending on each call, and if it's set, you check the buffer's LSN against the LSN of the earliest LSN conflicting LSN, and throw an error if it's greater than that. Why do we jump through so many hoops to get the earliest conflicting LSN to where it's needed? At the moment: 1. Startup process sets the LSN in the target backend's PGPROC entry, and signals it with SIGINT. 2. The target backend receives the signal; ProcessInterrupts is called either immediately or at the next CHECK_FOR_INTERRUPTS() call. 3. ProcessInterrupts reads the value from PGPROC, and passes it to bufmgr.c ISTM that if ReadBuffer read the value directly from the PGPROC entry, there would be no need for the signaling (in the ERROR mode). That is possible and I considered it. If we did it that way we would need to read the PGPROC each time we read a buffer. AFAICS we would need to use a spinlock to do that since reading an XLogRecPtr would not be atomic. So doing it the way I've done it allows us to use a local variable which can be more easily cached and avoids the locking overhead. We do still need to signal anyway for the FATAL case, so we're not significantly affecting the patch footprint by changing that. Correct me if I'm wrong, but I thought the idea of this new conflict resolution was that the startup process doesn't need to wait for the target backend to die. Instead, the target backend knows to commit suicide if it stumbles into a buffer that's been modified in a conflicting way. Looking at ResolveRecoveryConflictWithVirtualXIDs, it looks like we still wait. err, no, that's just an oversight, not intentional. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot Standby (v9d)
On Fri, 2009-01-23 at 18:22 +0200, Heikki Linnakangas wrote: @@ -1601,6 +1602,24 @@ BufferProcessRecoveryConflictsIfAny(volatile BufferDesc *bufHdr) { XLogRecPtr bufLSN = BufferGetLSN(bufHdr); + /* +* If the buffer is recent we may need to cancel ourselves +* rather than risk returning a wrong answer. This test is +* too conservative, but it is correct. +* +* We only need to cancel the current subtransaction. +* Once we've handled the error then other subtransactions can +* continue processing. Note that we do *not* reset the +* BufferRecoveryConflictLSN at subcommit/abort, but we do +* reset it if we release our last remaining sbapshot. +* see SnapshotResetXmin() +* Is it really enough to cancel just the current subtransaction? What if it's a serializable transaction? I did originally think that when I first looked at the problem. I'm sorry if I say that a lot. If you have a serializable transaction with subtransactions that suffers a serializability error it only cancels the current subtransaction. That means it's snapshot is still valid and can be used again. By analogy, as long as a transaction does not see any data that is inconsistent with its snapshot it seems OK for it to continue. So I think it is correct. (Bizarrely, this might mean that if we did this programatically in a loop we might keep the system busy for some time while it continually re-reads data and fails. But that's another story). You remind me that we can now do what Kevin has requested and throw a errcode(ERRCODE_T_R_SERIALIZATION_FAILURE) at this point, which I agree is the most easily understood way of describing this error. (I was sorely tempted to make it snapshot too old, as a joke). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Controlling hot standby
On Fri, 2009-01-23 at 12:17 -0600, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: There are considerable benefits to having it turned on during PITR Please read this to see why http://wiki.postgresql.org/wiki/Hot_Standby#Dynamic_Control_of_Recovery Am I reading this right? What I get out of it is that users can connect to the database during recovery, like with psql or JDBC? Any user can query recovery completion status and progress, while a superuser can also step through transactions in various ways? Can the data in the database be queried while recovery is paused? That's a big Yes to all of that. Exactly what all this is for. If I'm understanding this, and data can be queried during a pause, I can certainly see use cases for this; but it might be important to be able to start the recovery in a paused state to avoid going too far in the transaction stream before a connection can be established and recovery paused. OK, that's a simple addition. Thanks for the suggestion. I'll make it pause just after it changes state, so you can connect and tell it to progress as far as you like. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot Standby (v9d)
Simon Riggs wrote: If you have a serializable transaction with subtransactions that suffers a serializability error it only cancels the current subtransaction. That means it's snapshot is still valid and can be used again. By analogy, as long as a transaction does not see any data that is inconsistent with its snapshot it seems OK for it to continue. So I think it is correct. Yeah, you're right. How bizarre. (I was sorely tempted to make it snapshot too old, as a joke). Yeah, that is a very describing message, actually. If there wasn't any precedence to that, I believe we might have came up with exactly that message ourselves. -- 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] AIX 4.3 getaddrinfo busted
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Tom Lane wrote: BTW, what about the comments in ip.c to the effect that some versions of AIX fail when getaddrinfo's second argument *is* null? For starters, it indicates that sin_port is not zero'd properly. That wouldn't matter here since the plan is to manually set the port in this case, since providing it to getaddrinfo is broken. Hmm ... so actually we could get *rid* of that special case if we added this one. Okay, I withdraw the complaint. We should simply not rely on getaddrinfo to do anything right at all w.r.t. the port if we are running on AIX. Pass NULL for servname and set the port ourselves in all cases. regards, tom lane Done. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ Index: src/backend/libpq/ip.c === RCS file: /projects/cvsroot/pgsql/src/backend/libpq/ip.c,v retrieving revision 1.43 diff -C6 -r1.43 ip.c *** src/backend/libpq/ip.c 1 Jan 2009 17:23:42 - 1.43 --- src/backend/libpq/ip.c 23 Jan 2009 19:14:07 - *** *** 71,112 #ifdef HAVE_UNIX_SOCKETS if (hintp-ai_family == AF_UNIX) return getaddrinfo_unix(servname, hintp, result); #endif /* NULL has special meaning to getaddrinfo(). */ rc = getaddrinfo((!hostname || hostname[0] == '\0') ? NULL : hostname, servname, hintp, result); ! #ifdef _AIX /* * It seems some versions of AIX's getaddrinfo don't reliably zero !* sin_port when servname is NULL, so clean up after it. */ ! if (servname == NULL rc == 0) { struct addrinfo *addr; for (addr = *result; addr; addr = addr-ai_next) { switch (addr-ai_family) { case AF_INET: ! ((struct sockaddr_in *) addr-ai_addr)-sin_port = htons(0); break; #ifdef HAVE_IPV6 case AF_INET6: ! ((struct sockaddr_in6 *) addr-ai_addr)-sin6_port = htons(0); break; #endif } } } ! #endif return rc; } /* --- 71,124 #ifdef HAVE_UNIX_SOCKETS if (hintp-ai_family == AF_UNIX) return getaddrinfo_unix(servname, hintp, result); #endif + #ifndef _AIX /* NULL has special meaning to getaddrinfo(). */ rc = getaddrinfo((!hostname || hostname[0] == '\0') ? NULL : hostname, servname, hintp, result); ! #else ! /* NULL hostname has special meaning to getaddrinfo(). We have to !* set servname to NULL because some AIX versions, like 4.3, always !* fail with EAI_NODATA if not NULL. !*/ ! rc = getaddrinfo((!hostname || hostname[0] == '\0') ? NULL : hostname, !NULL, hintp, result); /* * It seems some versions of AIX's getaddrinfo don't reliably zero !* sin_port when servname is NULL, so clean up after it. Also, !* manually set the port when servname is provided. */ ! if(rc == 0) { struct addrinfo *addr; + unsigned short port = 0; + + if(servname *servname) + port = htons((unsigned short)atoi(servname)); for (addr = *result; addr; addr = addr-ai_next) { switch (addr-ai_family) { case AF_INET: ! ((struct sockaddr_in *) addr-ai_addr)-sin_port = port; break; #ifdef HAVE_IPV6 case AF_INET6: ! ((struct sockaddr_in6 *) addr-ai_addr)-sin6_port = port; break; #endif } } } ! #endif /* !_AIX */ return rc; } /* -- 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] AIX 4.3 getaddrinfo busted
Merlin Moncure wrote: On 1/23/09, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment I'm wondering why we are going to change the code now to support a ten-year-old OS version that evidently no one has tried to use Postgres on before. I'd like to address this observation. You may have noticed that eSilo has been contributing a number of patches to Postgres involving legacy systems. Understandably, there is very little overlap between modern versions of Postgres and these aging unixen. However, quite a few of these systems are still in production serving legacy applications. eSilo provides backup software. Based on customer feedback we came up with a list of systems that are under-represented by current backup solutions in the industry . For various reasons, we decided to involve libpq in our backup client and market aggressively to platforms for which there are very few backup options. libpq is quite portable, but there are a few understandable nits that have popped up here and there over time for older systems. We are providing fixes for those nits to the community. merlin I will add that its not our hobby to find obscure libpq and/or platform bugs on fossil machines. Actually, its a rather horrible and frustrating task. We simply have some unusual requirements for our new product. What we found amazing is how many requests we get for these old systems. We are just painfully filling that need. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Hot standby, conflict resolution
Simon Riggs wrote: On Fri, 2009-01-23 at 17:51 +0200, Heikki Linnakangas wrote: ISTM that if ReadBuffer read the value directly from the PGPROC entry, there would be no need for the signaling (in the ERROR mode). That is possible and I considered it. If we did it that way we would need to read the PGPROC each time we read a buffer. AFAICS we would need to use a spinlock to do that since reading an XLogRecPtr would not be atomic. Hmm, I think you could make it lock-free by ordering the instructions carefully. Not sure it's worth the code complexity, though. Correct me if I'm wrong, but I thought the idea of this new conflict resolution was that the startup process doesn't need to wait for the target backend to die. Instead, the target backend knows to commit suicide if it stumbles into a buffer that's been modified in a conflicting way. Looking at ResolveRecoveryConflictWithVirtualXIDs, it looks like we still wait. err, no, that's just an oversight, not intentional. Ok, then I think we have a little race condition. The startup process doesn't get any reply indicating that the target backend has processed the SIGINT and set the cached conflict LSN. The target backend might move ahead using the old LSN for a little while, even though the startup process has already gone ahead and replayed a vacuum record. Another tiny issue is that it looks like a new conflict LSN always overwrites the old one. But you should always use the oldest conflicted LSN in the checks, not the newest. -- 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] AIX 4.3 getaddrinfo busted
Andrew Chernow a...@esilo.com writes: Tom Lane wrote: Hmm ... so actually we could get *rid* of that special case if we added this one. Okay, I withdraw the complaint. Done. Were you hoping this would get back-patched, and if so how far? 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] AIX 4.3 getaddrinfo busted
Tom Lane wrote: Andrew Chernow a...@esilo.com writes: Tom Lane wrote: Hmm ... so actually we could get *rid* of that special case if we added this one. Okay, I withdraw the complaint. Done. Were you hoping this would get back-patched, and if so how far? No, we don't need a back-patch. We need way too many features in 8.4 ... like the really amazing libpq-events feature :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Hot Standby (v9d)
Heikki Linnakangas wrote: Simon Riggs wrote: If you have a serializable transaction with subtransactions that suffers a serializability error it only cancels the current subtransaction. That means it's snapshot is still valid and can be used again. By analogy, as long as a transaction does not see any data that is inconsistent with its snapshot it seems OK for it to continue. So I think it is correct. Yeah, you're right. How bizarre. It was argued this way to me way back when subtransactions were written. Originally I had written it in such a way as to abort the whole transaction, on the rationale that if you're blindly restarting the subtransaction after a serialization error, it would result in a (maybe infinite) loop. I think the reason it only aborts the subxact is that that's what all other errors do, so why would this one act differently. Hmm, now that I think about it, I think it was deadlock errors not serialization errors ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] AIX 4.3 getaddrinfo busted
Andrew Chernow a...@esilo.com writes: Tom Lane wrote: Were you hoping this would get back-patched, and if so how far? No, we don't need a back-patch. We need way too many features in 8.4 ... like the really amazing libpq-events feature :) OK, applied to HEAD with some tiny editorialization. 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] Hot standby, conflict resolution
On Fri, 2009-01-23 at 21:30 +0200, Heikki Linnakangas wrote: Correct me if I'm wrong, but I thought the idea of this new conflict resolution was that the startup process doesn't need to wait for the target backend to die. Instead, the target backend knows to commit suicide if it stumbles into a buffer that's been modified in a conflicting way. Looking at ResolveRecoveryConflictWithVirtualXIDs, it looks like we still wait. err, no, that's just an oversight, not intentional. Ok, then I think we have a little race condition. The startup process doesn't get any reply indicating that the target backend has processed the SIGINT and set the cached conflict LSN. The target backend might move ahead using the old LSN for a little while, even though the startup process has already gone ahead and replayed a vacuum record. Hah! You had me either way. Very neat :-) I'll think some more and reply, but not tonight. Another tiny issue is that it looks like a new conflict LSN always overwrites the old one. But you should always use the oldest conflicted LSN in the checks, not the newest. OK -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Pluggable Indexes
On Fri, 2009-01-23 at 10:33 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, 2009-01-22 at 18:45 -0500, Tom Lane wrote: There are other recent examples of proposed hooks that in fact failed to be useful because of some oversight or other, and it was not until we insisted on seeing a live use of the hooks that this became apparent. In the current case, index APIs are already well known, so that API is unlikely to be a problem. The actual rmgr plugin API is very simple, since its intention is only to add or edit entries onto the internal RmgrTable (in memory) after which everything is well defined already. Right, the WAL-record-processing API is not really at issue, since it's been proven internally to the core code. My concern is with the other part, namely exactly how are we going to identify and install additional rmgrs. There was substantial debate about that when it first came up, so you're not likely to convince me that it's such an open-and-shut case as to not need supporting evidence. I hear your objection and will answer it, for the record at least. We can load arbitrary code into any normal backend. I just want to be able to do the same with the startup process. It can't be much of a discussion since the API is essentially just the same as _PG_init(), or shmem_startup_hook. We took the risk with planner hook, and missed something. We took the risk with RequestAddinShmemSpace() and missed something. There wasn't any backlash or problem as a result though and we haven't even backpatched the additional hooks. They were inspired additions. Why is such a simple hook in Startup such a big deal? What would be wrong in fixing any problem in the next release, just as we've done in the other examples? If we didn't already have chapters in the manual on index extensibility I would have to agree. We could regard this patch as fixing an oversight in index extensibility, presumably when WAL was created. The patch is just * a hook in StartupXLOG to allow loading arbitrary code into Startup * some slight redefinition of RmgrTable to allow arbitrary code to add or modify the contents of that table of functions. (Being able to modify the table is an not necessary for index extensions, but is for other uses). * some safeguards people requested Buggy code in shmem_startup_hook could do just as much damage at startup or in a crash situation, but we have no safeguards there and nobody has said a single word against that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot Standby (v9d)
If you have a serializable transaction with subtransactions that suffers a serializability error it only cancels the current subtransaction. This is a complete tangent to your work, but I wonder if this is really right. I mean it's not as if we could have srrialized the transaction as a whole with respect to whatever other transaction we failed. -- 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] AIX 4.3 getaddrinfo busted
Merlin Moncure wrote: On 1/23/09, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment I'm wondering why we are going to change the code now to support a ten-year-old OS version that evidently no one has tried to use Postgres on before. I'd like to address this observation. You may have noticed that eSilo has been contributing a number of patches to Postgres involving legacy systems. Understandably, there is very little overlap between modern versions of Postgres and these aging unixen. However, quite a few of these systems are still in production serving legacy applications. eSilo provides backup software. Based on customer feedback we came up with a list of systems that are under-represented by current backup solutions in the industry . For various reasons, we decided to involve libpq in our backup client and market aggressively to platforms for which there are very few backup options. libpq is quite portable, but there are a few understandable nits that have popped up here and there over time for older systems. We are providing fixes for those nits to the community. Well, this helps explain why were are getting these problems reports only now. How many hacks do you have that we don't support, aside from the threading one for HPUX? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] duplicated tables
Hi there, yesterday, testing GIN fast update with CVS HEAD I was able to crash backend (Teodor already fixed the problem in 0.25 version of the patch) and after restarting backend I found duplicated tables. How this can be possible and is this what somebody can see after crash ? List of relations Schema | Name | Type | Owner +---+---+-- public | tt| table | postgres public | tt| table | postgres public | tt| table | postgres public | tt| table | postgres public | tt| table | postgres public | tt2 | table | postgres public | tt2 | table | postgres public | tt2 | table | postgres public | tt2 | table | postgres public | tt2 | table | postgres public | ttins | table | postgres public | ttins | table | postgres public | ttins | table | postgres public | ttins | table | postgres public | ttins | table | postgres (15 rows) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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: Automatic view update rules Bernd Helmle
--On 23. Januar 2009 13:28:27 -0500 Tom Lane t...@sss.pgh.pa.us wrote: In short, I don't feel that this was ready to be applied. It's probably fixable with a week or so's work, but do we want to be expending that kind of effort on it at this stage of the release cycle? Uh well, i'd be happier if such review comments would have been made earlier in the CommitFest. If i understand you correctly we have the choice between a) revert this patch, fix all remaining issues which will likely postpone this for 8.5 b) don't revert, but try to fix the issues currently existing in HEAD. It seems you're unsure wether b) is an option at all, because the amount of remaining work exceeds the time left for this release cycle? To be honest: I'm disappointed. If it tooks only a few steps to identify those (obviously important) issues, i get the opinion that there's very few motivating interest in this functionality (And yes, i'm annoyed about myself to not consider those operator issues). Bernd -- 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] New pg_dump patch -- document statistics collector exception (REVISED PATCH)
Here's a revision (thanks Robert Treat for the spelling corrextion). If there are no other objections, how do I nominate it for consideration? -Bryce Index: pg_dump.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.106 diff -c -2 -r1.106 pg_dump.sgml *** pg_dump.sgml 5 Jan 2009 16:54:36 - 1.106 --- pg_dump.sgml 23 Jan 2009 20:57:36 - *** *** 727,733 termenvarPGDATABASE/envar/term termenvarPGHOST/envar/term termenvarPGPORT/envar/term - termenvarPGUSER/envar/term listitem para --- 727,734 termenvarPGDATABASE/envar/term termenvarPGHOST/envar/term + termenvarPGOPTIONS/envar/term termenvarPGPORT/envar/term + termenvarPGUSER/envar/term listitem para *** *** 735,738 --- 736,740 /para /listitem + /varlistentry /variablelist *** *** 758,761 --- 760,772 library will apply. /para + para +The database activity of applicationpg_dump/application is normally collected by the +statistics collector. If this is undesirable, you can set parameters +literalstats_block_level/literal and literalstats_row_level/literal to false +via the applicationlibpq/application envarPGOPTIONS/envar environment variable, +or via literalALTER USER/literal. + /para + + /refsect1 Index: pg_dumpall.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v retrieving revision 1.74 diff -c -2 -r1.74 pg_dumpall.sgml *** pg_dumpall.sgml 5 Jan 2009 16:54:36 - 1.74 --- pg_dumpall.sgml 23 Jan 2009 20:57:36 - *** *** 446,449 --- 446,450 varlistentry termenvarPGHOST/envar/term + termenvarPGOPTIONS/envar/term termenvarPGPORT/envar/term termenvarPGUSER/envar/term Index: pg_restore.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.77 diff -c -2 -r1.77 pg_restore.sgml *** pg_restore.sgml 5 Jan 2009 16:54:36 - 1.77 --- pg_restore.sgml 23 Jan 2009 20:57:36 - *** *** 538,541 --- 538,542 varlistentry termenvarPGHOST/envar/term + termenvarPGOPTIONS/envar/term termenvarPGPORT/envar/term termenvarPGUSER/envar/term -- 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] AIX 4.3 getaddrinfo busted
Bruce Momjian wrote: Well, this helps explain why were are getting these problems reports only now. How many hacks do you have that we don't support, aside from the threading one for HPUX? We submit them as we find them. We've submitted for windows, hpux, solaris and aix. Still have not tested SCO openserver and unixware yet. The only thing we have that we have not submitted, are features we don't feel are of general interest: query white list system and bandwidth throttling in the backend on a per session basis. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Bernd Helmle wrote: If i understand you correctly we have the choice between a) revert this patch, fix all remaining issues which will likely postpone this for 8.5 b) don't revert, but try to fix the issues currently existing in HEAD. c) revert and expect an updated patch to apply very soon before this commitfest is finished -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] plpgsql: numeric assignment to an integer variable errors out
Hello I tested patch v2.0, and I thing, so this patch should be used as bug fix. It has same or little bit better speed than current and solve some problems with numeric's implicit casting in plpgsql. But this is only an half solution. The core of problem is in lazy casting of plpgsql. We need to modify execution plan for pl expression when result is different than destination type. For almost expression we know destination type. there is some sample of effectiveness: postgres=# create or replace function test1() returns int as $$ declare s int := 0; begin for i in 1..10 loop s := 4e3; -- numeric end loop; return s; end; $$ language plpgsql immutable; CREATE FUNCTION Time: 5,140 ms postgres=# create or replace function test2() returns int as $$ declare s int := 0; begin for i in 1..10 loop s := 4e3::int; end loop; return s; end; $$ language plpgsql immutable; CREATE FUNCTION Time: 416,240 ms postgres=# select test1(); test1 --- 4000 (1 row) Time: 161,048 ms postgres=# select test2(); test2 --- 4000 (1 row) Time: 68,110 ms postgres=# select test1(); test1 --- 4000 (1 row) Time: 171,020 ms postgres=# select test2(); test2 --- 4000 (1 row) Time: 61,771 ms postgres=# Regards Pavel Stehule 2009/1/22 Bruce Momjian br...@momjian.us: Nikhil Sontakke wrote: PFA, patch which uses find_coercion_pathway to find a direct COERCION_PATH_FUNC function and uses that if it is available. Or is there a better approach? Seems to handle the above issue with this patch. +1 I thing, so some values should by cached, current patch could by slow. Agreed, it can slow things down a bit especially since we are only interested in the COERCION_PATH_FUNC case. What we need is a much simpler pathway function which searches in the SysCache and returns back with the valid/invalid castfunc immediately. PFA, version 2.0 of this patch with these changes in place. I could have added a generic function in parse_coerce.c, but thought the use case was restricted to plpgsql and hence I have kept it within pl_exec.c for now. Where are we on this? 8.5? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] New pg_dump patch -- document statistics collector exception (REVISED PATCH)
Bryce Nesbitt wrote: Here's a revision (thanks Robert Treat for the spelling corrextion). If there are no other objections, how do I nominate it for consideration? -Bryce You already have. Mind you, in the future when you're not continuing a discussion from a code patch, you should submit doc patches to pgsql-docs. But I'm sure Peter has seen it here. --Josh -- 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] Pluggable Indexes
Simon Riggs si...@2ndquadrant.com writes: On Fri, 2009-01-23 at 10:33 -0500, Tom Lane wrote: Right, the WAL-record-processing API is not really at issue, since it's been proven internally to the core code. My concern is with the other part, namely exactly how are we going to identify and install additional rmgrs. The patch is just * a hook in StartupXLOG to allow loading arbitrary code into Startup * some slight redefinition of RmgrTable to allow arbitrary code to add or modify the contents of that table of functions. (Being able to modify the table is an not necessary for index extensions, but is for other uses). * some safeguards people requested Well, that really seems to just prove my point. You've defined a hook and not thought carefully about how people will use it. The main thing that I can see right now that we'd need is some way to determine who gets which rmgr index. (Maybe community assignment of numbers --- similar to what we've defined for pg_statistic kind codes --- is fine, or maybe it isn't; in any case we need an answer for that before this hook can be considered usable.) Furthermore, maybe that's not the only problem. I'd feel a lot better about this if the hook patch were done in parallel with development of actual WAL support in an actual external indexam. As was suggested earlier, we could do something like building hash as an external module for the sake of this development, so it's not like I'm demanding someone write a whole AM from scratch for this. But putting in the hook and leaving people to invent their own ways of using it is a recipe for conflicts. 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] Hot Standby (v9d)
On Fri, 2009-01-23 at 20:13 +, Greg Stark wrote: If you have a serializable transaction with subtransactions that suffers a serializability error it only cancels the current subtransaction. This is a complete tangent to your work, but I wonder if this is really right. I mean it's not as if we could have srrialized the transaction as a whole with respect to whatever other transaction we failed. Isn't this back to the discussion about PostgreSQL serializability versus true serializability? I agree that's not true serializability. Regards, Jeff Davis -- 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] New pg_dump patch -- document statistics collector exception (REVISED PATCH)
Bryce Nesbitt escreveu: Here's a revision (thanks Robert Treat for the spelling corrextion). If there are no other objections, how do I nominate it for consideration? Added to next commit fest [1]. [1] http://wiki.postgresql.org/wiki/CommitFest_2009-First -- Euler Taveira de Oliveira http://www.timbira.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] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
Bernd Helmle maili...@oopsware.de writes: --On 23. Januar 2009 13:28:27 -0500 Tom Lane t...@sss.pgh.pa.us wrote: In short, I don't feel that this was ready to be applied. Uh well, i'd be happier if such review comments would have been made earlier in the CommitFest. [ shrug... ] I've been busting my butt since 1 November to try to review everything. Some things are going to get left to the end. I have to admit having ranked this one lower because it was marked WIP for a good part of the commitfest, and so I'd assumed it was not really a serious candidate to get applied. Anyway, it's here now, and what we have to figure out is whether it's fixable on a time scale that's realistic for 8.4. I would really rather sidestep the whole btree-equality issue if possible, but that doesn't seem possible without some amount of changes to the rule mechanism itself. The idea I was toying with when I posted earlier is that the rules should look more like on update to view do instead update base_table set c1 = new.c1, etc where base_table.ctid = old.ctid but of course that doesn't work as-is because views don't expose old.ctid, and even if they did (which doesn't seem impossible) we'd need some planner fixes in order to get a non-silly plan out of it, because joins on ctid aren't implemented very well today. Another gotcha is that read-committed updates wouldn't work properly. If the row first identified by the view has been outdated by someone else's update, we're supposed to try to apply the update to the newest version of the row, if it still passes the update's WHERE clause. This would fail a priori with the ctid-based approach since the new row version is guaranteed not to have the same ctid. Even in the current equate-all-the-visible-fields approach it doesn't work if the someone else updated any of the visible fields: the row would now fail one of the added where conditions, which have got nothing to do with anything that the user wrote, so it's not expected behavior. I'm inclined to think that this is all pretty much insoluble within the current rule mechanism. The existing definition of rules makes it basically impossible to do INSTEAD UPDATE or INSTEAD DELETE without creating a self-join; if we don't get around that somehow we're never going to be very satisfied with either the performance or the corner-case semantics of this thing. What we get now from a rewritten view update is something that looks like UPDATE base_table new SET ... FROM base_table old WHERE view's-conditions-on-old AND user's-conditions-on-old AND exposed-fields-of-new-and-old-are-equal and just replacing the last part of that with a ctid equality is only nibbling at the margins of its suckiness. What we really want is that the rewritten query is just UPDATE base_table SET ... WHERE view's-conditions AND user's-conditions with no join at all. Perhaps the right answer is to invent some new rule syntax to redirect inserts/updates/deletes, say something like on update to foo do instead redirect to bar and then put some logic that's not so much different from what you've got here into the rule engine itself ... or maybe better, just have the rule engine automatically try to redirect if it's faced with having to raise error for lack of a rule? It seems to me that the rule engine has probably got all the infrastructure needed to convert the query the way we'd like, we just don't have a suitable API to tell it to do that. 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: Automatic view update rules Bernd Helmle
On Fri, 2009-01-23 at 17:32 -0500, Tom Lane wrote: Bernd Helmle maili...@oopsware.de writes: --On 23. Januar 2009 13:28:27 -0500 Tom Lane t...@sss.pgh.pa.us wrote: In short, I don't feel that this was ready to be applied. Uh well, i'd be happier if such review comments would have been made earlier in the CommitFest. [ shrug... ] I've been busting my butt since 1 November to try to review everything. Some things are going to get left to the end. I don't think anyone is suggesting differently and if they are I will be happy to go all JD on them. I think the author is just (rightfully) frustrated at the process in general. We lack certain resources. *shrug* The good news is :) this release cycle has been much better than any previous release cycle that I have been a part of. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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: Automatic view update rules Bernd Helmle
Bernd, To be honest: I'm disappointed. If it tooks only a few steps to identify those (obviously important) issues, i get the opinion that there's very few motivating interest in this functionality (And yes, i'm annoyed about myself to not consider those operator issues). Well, that *is* the problem with getting your patch into the last commitfest, and why I tried to get everyone to submit earlier. You only have to miss one difficult problem to miss the release. If it makes you feel any better, I certainly didn't think of the operator issue, and neither did Robert. --Josh -- 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] New pg_dump patch -- document statistics collector exception (REVISED PATCH)
Euler Taveira de Oliveira wrote: Bryce Nesbitt escreveu: Here's a revision (thanks Robert Treat for the spelling corrextion). If there are no other objections, how do I nominate it for consideration? Added to next commit fest [1]. Um, not necessary. We're still accepting new doc patches, and will until the end of beta. --Josh -- 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] duplicated tables
Oleg Bartunov o...@sai.msu.su writes: yesterday, testing GIN fast update with CVS HEAD I was able to crash backend (Teodor already fixed the problem in 0.25 version of the patch) and after restarting backend I found duplicated tables. How this can be possible and is this what somebody can see after crash ? That doesn't look like duplicated tables so much as duplicated pg_namespace rows --- try doing \d's query by hand and adding display of pg_class.ctid and pg_namespace.ctid. However, if that theory is correct then the next question is what you were doing to pg_namespace... 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: Automatic view update rules Bernd Helmle
On Fri, Jan 23, 2009 at 5:52 PM, Josh Berkus j...@agliodbs.com wrote: Bernd, If it makes you feel any better, I certainly didn't think of the operator issue, and neither did Robert. to be honest, i feel like that was commented in the last (or the last before the last) release cycle well this patch originally appears. but i have no time in this moment to confirm that -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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: Automatic view update rules Bernd Helmle
--On 23. Januar 2009 17:32:55 -0500 Tom Lane t...@sss.pgh.pa.us wrote: Bernd Helmle maili...@oopsware.de writes: --On 23. Januar 2009 13:28:27 -0500 Tom Lane t...@sss.pgh.pa.us wrote: In short, I don't feel that this was ready to be applied. Uh well, i'd be happier if such review comments would have been made earlier in the CommitFest. [ shrug... ] I've been busting my butt since 1 November to try to review everything. Some things are going to get left to the end. I have to admit having ranked this one lower because it was marked WIP for a good part of the commitfest, and so I'd assumed it was not really a serious candidate to get applied. Oh, please, don't get me wrong: i never intended to attack you personally. I can imagine how much of work you are faced with this release. I got the feeling that it's simply the wrong way chosen, a little bit frustrating, isn't it? Apologize for that. Anyway, it's here now, and what we have to figure out is whether it's fixable on a time scale that's realistic for 8.4. I would really rather sidestep the whole btree-equality issue if possible, but that doesn't seem possible without some amount of changes to the rule mechanism itself. The idea I was toying with when I posted earlier is that the rules should look more like on update to view do instead update base_table set c1 = new.c1, etc where base_table.ctid = old.ctid but of course that doesn't work as-is because views don't expose old.ctid, and even if they did (which doesn't seem impossible) we'd need some planner fixes in order to get a non-silly plan out of it, because joins on ctid aren't implemented very well today. Another gotcha is that read-committed updates wouldn't work properly. If the row first identified by the view has been outdated by someone else's update, we're supposed to try to apply the update to the newest version of the row, if it still passes the update's WHERE clause. This would fail a priori with the ctid-based approach since the new row version is guaranteed not to have the same ctid. Even in the current equate-all-the-visible-fields approach it doesn't work if the someone else updated any of the visible fields: the row would now fail one of the added where conditions, which have got nothing to do with anything that the user wrote, so it's not expected behavior. Yeah, that's exactly the same feeling i got when reading your last mail. I'm very uncomfortable now that we know the real gotchas with the whole rule approach. Normally you'll get some ideas when thinking about a solution, but instead i have to think omg, is that really doable within the rewriter in any ways? getting disappointed. What we get now from a rewritten view update is something that looks like UPDATE base_table new SET ... FROM base_table old WHERE view's-conditions-on-old AND user's-conditions-on-old AND exposed-fields-of-new-and-old-are-equal and just replacing the last part of that with a ctid equality is only nibbling at the margins of its suckiness. What we really want is that the rewritten query is just UPDATE base_table SET ... WHERE view's-conditions AND user's-conditions with no join at all. Perhaps the right answer is to invent some new rule syntax to redirect inserts/updates/deletes, say something like on update to foo do instead redirect to bar Hmm this would mean that the rewriter bypasses all the rule stuff itself when faced with a view update and completely replacing the original query? Looks kinda of it. Oracle has INSTEAD OF triggers which are going to do nearly the same thing, afaiks. Bernd -- 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: Automatic view update rules Bernd Helmle
On Fri, Jan 23, 2009 at 5:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps the right answer is to invent some new rule syntax to redirect inserts/updates/deletes, say something like on update to foo do instead redirect to bar and then put some logic that's not so much different from what you've got here into the rule engine itself ... or maybe better, just have the rule engine automatically try to redirect if it's faced with having to raise error for lack of a rule? It seems to me that the rule engine has probably got all the infrastructure needed to convert the query the way we'd like, we just don't have a suitable API to tell it to do that. and what about default values? if we redirect we will have to use the table's default (something i like) and AFAIU we won't have the ability to change it for the view at least not without manually create a new DO INSTEAD rule (something i don't like)... i'm missing something? or can we implement such REDIRECT with the ability to respect view's own defaults? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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: Automatic view update rules Bernd Helmle
--On 23. Januar 2009 18:02:55 -0500 Jaime Casanova jcasa...@systemguards.com.ec wrote: to be honest, i feel like that was commented in the last (or the last before the last) release cycle well this patch originally appears. I know that i've changed something in the operator lookup code regarding some discussions last year, but i can't remember. Anyways, we have to fix it in some other way. And of course, CTID looks like a mess (and i'm sure there are much more issues with them than we can imagine now, because it's in some direction theoretically much the same problem as multi action rules). Bernd -- 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: Automatic view update rules Bernd Helmle
--On 23. Januar 2009 18:07:38 -0500 Jaime Casanova jcasa...@systemguards.com.ec wrote: and what about default values? if we redirect we will have to use the table's default (something i like) and AFAIU we won't have the ability to change it for the view at least not without manually create a new DO INSTEAD rule (something i don't like)... i'm missing something? or can we implement such REDIRECT with the ability to respect view's own defaults? It's too late for me to think technically about it, but you're right, something to keep this behavior would be nice. I don't know wether the standard has a notion about such behavior, too (have to look at it). Note that a possible solution obviously has to allow the old behavior, so in the first place this behavior can be easily restored. Bernd -- 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: Automatic view update rules Bernd Helmle
Jaime Casanova jcasa...@systemguards.com.ec writes: On Fri, Jan 23, 2009 at 5:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Perhaps the right answer is to invent some new rule syntax to redirect inserts/updates/deletes, say something like on update to foo do instead redirect to bar and what about default values? I don't see the issue. View defaults would get inserted the same way they are now. There is another thing that's bothering me, though, which is that the present approach to dumping rules isn't adequate. Consider the following scenario: 1. You create a view that the system considers updatable, so it creates some automatic rules. 2. You don't want those rules, so you delete them, leaving you with the traditional behavior where attempted inserts etc on the view fail. 3. All is well until you dump and restore, whereupon you'll be swearing at those ^...@#! rules having come back. I think that we probably want the rules to show up automatically during an upgrade from an older version, but it does not follow that they should come back after being intentionally removed from an 8.4 installation. (This is *particularly* true if we are unable to squash every last one of the semantic gotchas; but even if we can, it's not impossible that someone might want the no-update behavior for some views.) We could imagine attaching a no auto rules please property to views (hm, perhaps this is an application for reloptions for a view). Or we could invent a new rule action type DO INSTEAD ERROR, so that you could get the failure behavior as the result of a rule manually substituted for the automatic ones. But right now there's a hole in the definition. 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: Automatic view update rules Bernd Helmle
Uh well, i'd be happier if such review comments would have been made earlier in the CommitFest. Well, as one of original reviewers of this patch, I feel a little bad that I didn't consider these issues - the rules looked messy to me, but I didn't consider that the whole approach might be wrong. But... I have to admit I didn't look at this patch very hard. When I first reviewed it on November 11th, it didn't even pass regression tests, and you didn't submit a new version until December 26th, by which time I had long since moved onto other things. In the future, I think we should have an expectation that resubmits within the same CommitFest should happen within a week, and that if no revision is forthcoming within two weeks the patch is declared dead (and the submitter can add it to the next CommitFest when they resubmit). Don't think I'm picking on you, either: there was quite a bit of it this CommitFest, and it's bad, because: - reviewers are afraid to move on to new patches, because they don't know when or if they'll suddenly be called upon to go re-review old patches, and - the commitfest takes forever, which is probably hard on the committers as well as the reviewers, and - when the FINAL commitfest takes this long, it creates an extremely long window during which it's hard to get started on any new work for 8.5. On the flip side, as I've said before, some of the big patches were not reviewed until quite late. I think next time we should focus on assigning reviewers to the big patches first (maybe two reviewers each just to make sure we get good coverage...) and then review the smaller patches afterwards. But that's a separate issue from how long the submitter takes to respond to feedback once it's given. ...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] Hot Standby (v9d)
Simon Riggs wrote: On Thu, 2009-01-22 at 22:35 +, Simon Riggs wrote: * Bug fix v9 over next few days version 9g - please use this for testing now I'm doing some test runs with this now. I notice an old flatfiles related bug has reappeared: master: =# create database test; slave: =# select datname from pg_database where datname like 'test'; datname - test (1 row) postgres=# \c test FATAL: database test does not exist Previous connection kept -- 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] foreign_data test fails with non-C locale
Zdenek Kotala wrote: Andrew Dunstan píše v pá 09. 01. 2009 v 12:16 -0500: Sure, we can easily have buildfarm's initdb step set any locale (and encoding, for that matter) we like. That's a simple change. Will be possible to set more locales and run tests without recompilation on all of them? For example I have installed all Solaris'es locales on my animal, but currently it means that I need perform whole cycle for each locale. I'm working on this. Yes, you will be able to specify a list of locales to check. For each locale the following tests will be run: installcheck, pl-installcheck, and contrib-installcheck. However, our tests are still a bit short of working across locales. PL-check gives the diff below on PLTCL tests under en_US locale. I guess the simplest answer is to add an alternative result file. cheers andrew select * from T_pkey1 order by key1 using @, key2; key1 | key2 | txt --+--+-- - 1 | KEY1-3 | should work 1 | key1-1 | test key 1 | key1-2 | test key 1 | key1-3 | test key 2 | key2-3 | test key 2 | key2-9 | test key (6 rows) --- 166,175 select * from T_pkey1 order by key1 using @, key2; key1 | key2 | txt --+--+-- 1 | key1-1 | test key 1 | key1-2 | test key 1 | key1-3 | test key + 1 | KEY1-3 | should work 2 | key2-3 | test key 2 | key2-9 | test key (6 rows) -- 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] deductive databases in postgreSQL
At the risk of excluding people... I know that 2ndQuadrant and Command Prompt will develop features for hire. I'm not sure if EnterpriseDB will or not. And yes, post is pgsql-jobs. On Jan 23, 2009, at 3:10 AM, Carlos Gonzalez-Cadenas wrote: Yes it's an option, but you cannot rely on the typical consulting company to do that. Do you know any specialized consulting boutique or individual developer that could do that? Carlos Gonzalez-Cadenas CEO, ExperienceOn - New generation search http://www.experienceon.com Mobile: +34 652 911 201 Skype: carlosgonzalezcadenas LinkedIn: http://www.linkedin.com/in/carlosgonzalezcadenas On Thu, Jan 22, 2009 at 7:16 PM, decibel deci...@decibel.org wrote: On Jan 22, 2009, at 8:24 AM, Euler Taveira de Oliveira wrote: No one that I know of. Well, it is a long road. The addition of a data type xml is recent (8.3). We lack a set of features like indexing, a new data structure (?), XQuery, XPath improvement and, so on [1]. Don't expect much of these TODO items completed before the next two releases (unless you want to take a stab). You could also possibly pay a consulting company to implement it, but even that isn't as easy as it may sound. :) -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers