Re: [HACKERS] Identity projection
Hello, I've read the discussion held so far and am satisfied that apply this patch only for Result node. I applied the patch and found that it worked pretty fine for me. Thank you and I also think that we may send this to committers. # It makes me fee ill at ease that the roles of us look inverted :-p At Wed, 13 Feb 2013 09:08:21 +0530, Amit Kapila amit.kap...@huawei.com wrote in 001801ce099b$92b2df20$b8189d60$@kap...@huawei.com I have updated the patch as per comments from Tom and Heikki. If you can verify it, then IMO it can be marked as 'Ready For Committer' Would you please do that? Regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2.3 crashes during archive recovery
Sorry, I omitted to show how we found this issue. In HA DB cluster cosists of Pacemaker and PostgreSQL, PostgreSQL is stopped by 'pg_ctl stop -m i' regardless of situation. On the other hand, PosrgreSQL RA(Rsource Agent) is obliged to start the master node via hot standby state because of the restriction of the state transition of Pacemaker, So the simply stopping and then starting the master node can fall into this situation. Hmm, I just realized a little problem with that approach. If you take a base backup using an atomic filesystem backup from a running server, and start archive recovery from that, that's essentially the same thing as Kyotaro's test case. Regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Identity projection
On Friday, February 15, 2013 2:03 PM Kyotaro HORIGUCHI wrote: Hello, I've read the discussion held so far and am satisfied that apply this patch only for Result node. I applied the patch and found that it worked pretty fine for me. Thank you and I also think that we may send this to committers. # It makes me fee ill at ease that the roles of us look inverted :-p At Wed, 13 Feb 2013 09:08:21 +0530, Amit Kapila amit.kap...@huawei.com wrote in 001801ce099b$92b2df20$b8189d60$@kap...@huawei.com I have updated the patch as per comments from Tom and Heikki. If you can verify it, then IMO it can be marked as 'Ready For Committer' Would you please do that? Done. With Regards, Amit Kapila. -- 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] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
Alvaro Herrera alvhe...@2ndquadrant.com writes: - Who wants to mentor for GSOC? I am open to being a mentor. Me too. -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2.3 crashes during archive recovery
On Wed, Feb 13, 2013 at 10:52 PM, Simon Riggs si...@2ndquadrant.com wrote: The problem is that we startup Hot Standby before we hit the min recovery point because that isn't recorded. For me, the thing to do is to make the min recovery point == end of WAL when state is DB_IN_PRODUCTION. That way we don't need to do any new writes and we don't need to risk people seeing inconsistent results if they do this. While this solution would help solve my issue, it assumes that the correct amount of WAL files are actually there. Currently the docs for setting up a standby refer to 24.3.4. Recovering Using a Continuous Archive Backup, and that step recommends emptying the contents of pg_xlog. If this is chosen as the solution the docs should be adjusted to recommend using pg_basebackup -x for setting up the standby. As a related point, pointing standby setup to that section has confused at least one of my clients. That chapter is rather scarily complicated compared to what's usually necessary. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
Hello, Can you guys send me a link to a where to start page ? Thanks, Sirbu Nicolae-Cezar
Re: [HACKERS] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
On Fri, Feb 15, 2013 at 2:51 PM, Dimitri Fontaine dimi...@2ndquadrant.frwrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: - Who wants to mentor for GSOC? I am open to being a mentor. Me too. I'm ready for mentoring too. And I will encourage students in my university to apply proposals to PostgreSQL. -- With best regards, Alexander Korotkov.
Re: [HACKERS] Temporal features in PostgreSQL
Hello, I'm also interested in this topic. I'm also interested in this topic and work on system-time temporal extension. Here I wrote down design of my solution few months ago https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is basically the same as in your solution with some minor differences. I've added a requirement in the system here: the table to be versioned must have a PK (I dislike _entry_id usage but this sounds good othwise). I then define a EXCLUDE WITH GIST (pk with =, sys_period with ), thus getting expected UNIQUEness also in the history. Vlad, is your source code in a public versionning system (github, bucket, etc) ? It will ease the process to participate to your extension... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] 9.2.3 crashes during archive recovery
On 15.02.2013 13:05, Ants Aasma wrote:
On Wed, Feb 13, 2013 at 10:52 PM, Simon Riggssi...@2ndquadrant.com wrote:
The problem is that we startup Hot Standby before we hit the min
recovery point because that isn't recorded. For me, the thing to do is
to make the min recovery point == end of WAL when state is
DB_IN_PRODUCTION. That way we don't need to do any new writes and we
don't need to risk people seeing inconsistent results if they do this.
While this solution would help solve my issue, it assumes that the
correct amount of WAL files are actually there. Currently the docs for
setting up a standby refer to 24.3.4. Recovering Using a Continuous
Archive Backup, and that step recommends emptying the contents of
pg_xlog. If this is chosen as the solution the docs should be adjusted
to recommend using pg_basebackup -x for setting up the standby.
When the backup is taken using pg_start_backup or pg_basebackup,
minRecoveryPoint is set correctly anyway, and it's OK to clear out
pg_xlog. It's only if you take the backup using an atomic filesystem
snapshot, or just kill -9 the server and take a backup while it's not
running, that we have a problem. In those scenarios, you should not
clear pg_xlog.
Attached is a patch for git master. The basic idea is to split
InArchiveRecovery into two variables, InArchiveRecovery and
ArchiveRecoveryRequested. ArchiveRecoveryRequested is set when
recovery.conf exists. But if we don't know how far we need to recover,
we first perform crash recovery with InArchiveRecovery=false. When we
reach the end of WAL in pg_xlog, InArchiveRecovery is set, and we
continue with normal archive recovery.
As a
related point, pointing standby setup to that section has confused at
least one of my clients. That chapter is rather scarily complicated
compared to what's usually necessary.
Yeah, it probably could use some editing, as the underlying code has
evolved a lot since it was written. The suggestion to clear out pg_xlog
seems like an unnecessary complication. It's safe to do so, if you
restore with an archive, but unnecessary.
The File System Level Backup chapter
(http://www.postgresql.org/docs/devel/static/backup-file.html) probably
should mention pg_basebackup -x, too.
Docs patches are welcome..
- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 479c14d..b4e7830 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -189,7 +189,18 @@ static bool LocalHotStandbyActive = false;
*/
static int LocalXLogInsertAllowed = -1;
-/* Are we recovering using offline XLOG archives? (only valid in the startup process) */
+/*
+ * When ArchiveRecoveryRequested is set, archive recovery was
+ * requested (recovery.conf file was present). When InArchiveRecovery is set,
+ * we are currently recovering using offline XLOG archives. (these variables
+ * are only valid in the startup process).
+ *
+ * When ArchiveRecoveryRequested is true, but InArchiveRecovery is false, we're
+ * currently performing crash recovery using only XLOG files in pg_xlog, but
+ * will switch to using offline XLOG archives as soon as we reach the end of
+ * WAL in pg_xlog.
+*/
+static bool ArchiveRecoveryRequested = false;
bool InArchiveRecovery = false;
/* Was the last xlog file restored from archive, or local? */
@@ -207,10 +218,12 @@ static TimestampTz recoveryTargetTime;
static char *recoveryTargetName;
/* options taken from recovery.conf for XLOG streaming */
-bool StandbyMode = false;
+static bool StandbyModeRequested = false;
static char *PrimaryConnInfo = NULL;
static char *TriggerFile = NULL;
+bool StandbyMode = false;
+
/* whether request for fast promotion has been made yet */
static bool fast_promote = false;
@@ -3217,10 +3230,10 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
private-emode = emode;
private-randAccess = (RecPtr != InvalidXLogRecPtr);
- /* This is the first try to read this page. */
+ /* This is the first attempt to read this page. */
lastSourceFailed = false;
- do
+ for (;;)
{
char *errormsg;
@@ -3229,8 +3242,6 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
EndRecPtr = xlogreader-EndRecPtr;
if (record == NULL)
{
- lastSourceFailed = true;
-
if (readFile = 0)
{
close(readFile);
@@ -3247,22 +3258,16 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
ereport(emode_for_corrupt_record(emode,
RecPtr ? RecPtr : EndRecPtr),
(errmsg_internal(%s, errormsg) /* already translated */));
-
- /* Give up, or retry if we're in standby mode. */
- continue;
}
-
/*
* Check page TLI is one of the expected values.
*/
- if (!tliInHistory(xlogreader-latestPageTLI, expectedTLEs))
+ else if (!tliInHistory(xlogreader-latestPageTLI, expectedTLEs))
{
char fname[MAXFNAMELEN];
XLogSegNo segno;
int32 offset;
- lastSourceFailed =
Re: [HACKERS] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
On 15.02.2013 14:29, Sîrbu Nicolae-Cezar wrote: Hello, Can you guys send me a link to a where to start page ? Take a look at the Project Ideas page from last year, and the project TODO list. See http://www.postgresql.org/developer/summerofcode/#ideas. One approach is to pick a research paper on some algorithm or technique that's applicable to databases, and then implement that in PostgreSQL. - Heikki -- 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] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
Can't we have something related to machine learning? I was thinking of extending a technique where we can fill in some missing values in a data set for the user if he wants us to using some standard ml algorithms. Atri Sent from my iPad On 15-Feb-2013, at 19:26, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.02.2013 14:29, Sîrbu Nicolae-Cezar wrote: Hello, Can you guys send me a link to a where to start page ? Take a look at the Project Ideas page from last year, and the project TODO list. See http://www.postgresql.org/developer/summerofcode/#ideas. One approach is to pick a research paper on some algorithm or technique that's applicable to databases, and then implement that in PostgreSQL. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Unarchived WALs deleted after crash
On 14.02.2013 17:45, Jehan-Guillaume de Rorthais wrote:
I am facing an unexpected behavior on a 9.2.2 cluster that I can
reproduce on current HEAD.
On a cluster with archive enabled but failing, after a crash of
postmaster, the checkpoint occurring before leaving the recovery mode
deletes any additional WALs, even those waiting to be archived.
...
Is it expected ?
No, it's a bug. Ouch. It was introduced in 9.2, by commit
5286105800c7d5902f98f32e11b209c471c0c69c:
- /*
- * Normally we don't delete old XLOG files during recovery to
- * avoid accidentally deleting a file that looks stale due to a
- * bug or hardware issue, but in fact contains important data.
- * During streaming recovery, however, we will eventually fill the
- * disk if we never clean up, so we have to. That's not an issue
- * with file-based archive recovery because in that case we
- * restore one XLOG file at a time, on-demand, and with a
- * different filename that can't be confused with regular XLOG
- * files.
- */
- if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name))
+ if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name))
[ delete the file ]
With that commit, we started to keep WAL segments restored from the
archive in pg_xlog, so we needed to start deleting old segments during
archive recovery, even when streaming replication was not active. But
the above change was to broad; we started to delete old segments also
during crash recovery.
The above should check InArchiveRecovery, ie. only delete old files when
in archive recovery, not when in crash recovery. But there's one little
complication: InArchiveRecovery is currently only valid in the startup
process, so we'll need to also share it in shared memory, so that the
checkpointer process can access it.
I propose the attached patch to fix it.
- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 30d877b..dabb094 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -418,6 +418,7 @@ typedef struct XLogCtlData
* recovery. Protected by info_lck.
*/
bool SharedRecoveryInProgress;
+ bool SharedInArchiveRecovery;
/*
* SharedHotStandbyActive indicates if we're still in crash or archive
@@ -622,6 +623,7 @@ static void XLogArchiveCleanup(const char *xlog);
static void readRecoveryCommandFile(void);
static void exitArchiveRecovery(TimeLineID endTLI,
uint32 endLogId, uint32 endLogSeg);
+static bool ArchiveRecoveryInProgress(void);
static bool recoveryStopsHere(XLogRecord *record, bool *includeThis);
static void recoveryPausesHere(void);
static void SetLatestXTime(TimestampTz xtime);
@@ -3571,7 +3573,7 @@ RemoveOldXlogFiles(uint32 log, uint32 seg, XLogRecPtr endptr)
strspn(xlde-d_name, 0123456789ABCDEF) == 24
strcmp(xlde-d_name + 8, lastoff + 8) = 0)
{
- if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name))
+ if (ArchiveRecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name))
{
snprintf(path, MAXPGPATH, XLOGDIR /%s, xlde-d_name);
@@ -5289,6 +5291,7 @@ XLOGShmemInit(void)
*/
XLogCtl-XLogCacheBlck = XLOGbuffers - 1;
XLogCtl-SharedRecoveryInProgress = true;
+ XLogCtl-SharedInArchiveRecovery = false;
XLogCtl-SharedHotStandbyActive = false;
XLogCtl-WalWriterSleeping = false;
XLogCtl-Insert.currpage = (XLogPageHeader) (XLogCtl-pages);
@@ -5680,6 +5683,7 @@ readRecoveryCommandFile(void)
/* Enable fetching from archive recovery area */
InArchiveRecovery = true;
+ XLogCtl-SharedInArchiveRecovery = true;
/*
* If user specified recovery_target_timeline, validate it or compute the
@@ -5718,11 +5722,16 @@ exitArchiveRecovery(TimeLineID endTLI, uint32 endLogId, uint32 endLogSeg)
{
char recoveryPath[MAXPGPATH];
char xlogpath[MAXPGPATH];
+ /* use volatile pointer to prevent code rearrangement */
+ volatile XLogCtlData *xlogctl = XLogCtl;
/*
* We are no longer in archive recovery state.
*/
InArchiveRecovery = false;
+ SpinLockAcquire(xlogctl-info_lck);
+ xlogctl-SharedInArchiveRecovery = false;
+ SpinLockRelease(xlogctl-info_lck);
/*
* Update min recovery point one last time.
@@ -7315,6 +7324,25 @@ RecoveryInProgress(void)
}
/*
+ * Are we currently in archive recovery? In the startup process, you can just
+ * check InArchiveRecovery variable instead.
+ */
+static bool
+ArchiveRecoveryInProgress()
+{
+ bool result;
+ /* use volatile pointer to prevent code rearrangement */
+ volatile XLogCtlData *xlogctl = XLogCtl;
+
+ /* spinlock is essential on machines with weak memory ordering! */
+ SpinLockAcquire(xlogctl-info_lck);
+ result = xlogctl-SharedInArchiveRecovery;
+ SpinLockRelease(xlogctl-info_lck);
+
+ return result;
+}
+
+/*
* Is HotStandby active yet? This is only important in special backends
* since normal backends won't ever be able to connect until this returns
* true. Postmaster knows this by way of signal, not via shared
Re: [HACKERS] I think we need PRE_COMMIT events for (Sub)XactCallbacks
Pavan Deolasee pavan.deola...@gmail.com writes: I noticed you added a pre event for commit/prepare/subcommit. That looks good. Is there a case to add it for abort/subabort too ? I wonder if we would want to do some cleanup on the foreign servers before the transaction is abort-recorded on the main server. I don't really think this is needed. The reason to have a pre-commit step is to be able to throw an error and prevent commit from occurring. In the abort case, there is no corresponding need to be able to change the local transaction's result. So you can just do whatever you need to do in the existing ABORT event. I'd just as soon not add overhead to commit/abort without a demonstrated need. For example, if someone wants to implement a 2PC using transaction callbacks and need a mechanism to rollback prepared transactions because some foreign server refused to prepare, I'm not sure if she can use XACT_EVENT_ABORT because that callback is called while interrupts are disabled and so it may not be safe to communicate with the foreign servers. [ shrug... ] An interrupt means abort the current transaction and clean up. If you're already trying to do that, it means nothing. It's not like you could choose to not clean up. If we did have a pre-abort event, throwing an error from it would just cause control to come right back and do it again. (Having said that, we will have to take a closer look at the postgres_fdw code and make sure that it tries to avoid getting stuck at ABORT time.) How about supporting all three modes such as 1. the current behaviour of immediately committing at the end of a statement, 2. a full 2PC and 3. what you are proposing. Well, the current behavior is simply broken IMO, and no it's not any faster --- you still need to send a commit/abort command, it's just different timing. As for #2, fine, *you* implement that. I'm not volunteering. First question is which XA manager you're going to assume is controlling 2PC matters. 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] Call for Google Summer of Code mentors, admins
Hello, Josh. You wrote: JB Folks, JB Once again, Google is holding Summer of Code. We need to assess whether JB we want to participate this year. JB Questions: JB - Who wants to mentor for GSOC? JB - Who can admin for GSOC? Thom? JB - Please suggest project ideas for GSOC My suggestion is to rewrite (add) pg_dump and pg_restore utilities as libraries (.so, .dll .dylib). For me as a developer it will be a cool feature. And I can be a mentor for this project of course! JB - Students seeing this -- please speak up if you have projects you plan JB to submit. JB -- JB Josh Berkus JB PostgreSQL Experts Inc. JB http://pgexperts.com -- With best wishes, Pavel mailto:pa...@gf.microolap.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] Unarchived WALs deleted after crash
On 15 February 2013 14:31, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 14.02.2013 17:45, Jehan-Guillaume de Rorthais wrote: I am facing an unexpected behavior on a 9.2.2 cluster that I can reproduce on current HEAD. On a cluster with archive enabled but failing, after a crash of postmaster, the checkpoint occurring before leaving the recovery mode deletes any additional WALs, even those waiting to be archived. ... Is it expected ? No, it's a bug. Ouch. It was introduced in 9.2, by commit 5286105800c7d5902f98f32e11b209c471c0c69c: Thanks for tracking that down. - /* - * Normally we don't delete old XLOG files during recovery to - * avoid accidentally deleting a file that looks stale due to a - * bug or hardware issue, but in fact contains important data. - * During streaming recovery, however, we will eventually fill the - * disk if we never clean up, so we have to. That's not an issue - * with file-based archive recovery because in that case we - * restore one XLOG file at a time, on-demand, and with a - * different filename that can't be confused with regular XLOG - * files. - */ - if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name)) + if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name)) [ delete the file ] With that commit, we started to keep WAL segments restored from the archive in pg_xlog, so we needed to start deleting old segments during archive recovery, even when streaming replication was not active. But the above change was to broad; we started to delete old segments also during crash recovery. The above should check InArchiveRecovery, ie. only delete old files when in archive recovery, not when in crash recovery. But there's one little complication: InArchiveRecovery is currently only valid in the startup process, so we'll need to also share it in shared memory, so that the checkpointer process can access it. I propose the attached patch to fix it. Agree with your diagnosis and fix. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
Tomas Vondra escribió: On 14.2.2013 20:23, Alvaro Herrera wrote: The problem here is that creating these dummy entries will cause a difference in autovacuum behavior. Autovacuum will skip processing databases with no pgstat entry, and the intended reason is that if there's no pgstat entry it's because the database doesn't have enough activity. Now perhaps we want to change that, but it should be an explicit decision taken after discussion and thought, not side effect from an unrelated patch. I don't see how that changes the autovacuum behavior. Can you explain that a bit more? As I see it, with the old (single-file version) the autovacuum worker would get exacly the same thing, i.e. no stats at all. See in autovacuum.c the calls to pgstat_fetch_stat_dbentry(). Most of them check for NULL result and act differently depending on that. Returning a valid (not NULL) entry full of zeroes is not the same. I didn't actually try to reproduce a problem. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unarchived WALs deleted after crash
On 15.02.2013 17:12, Simon Riggs wrote: On 15 February 2013 14:31, Heikki Linnakangashlinnakan...@vmware.com wrote: - /* - * Normally we don't delete old XLOG files during recovery to - * avoid accidentally deleting a file that looks stale due to a - * bug or hardware issue, but in fact contains important data. - * During streaming recovery, however, we will eventually fill the - * disk if we never clean up, so we have to. That's not an issue - * with file-based archive recovery because in that case we - * restore one XLOG file at a time, on-demand, and with a - * different filename that can't be confused with regular XLOG - * files. - */ - if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name)) + if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name)) [ delete the file ] With that commit, we started to keep WAL segments restored from the archive in pg_xlog, so we needed to start deleting old segments during archive recovery, even when streaming replication was not active. But the above change was to broad; we started to delete old segments also during crash recovery. The above should check InArchiveRecovery, ie. only delete old files when in archive recovery, not when in crash recovery. But there's one little complication: InArchiveRecovery is currently only valid in the startup process, so we'll need to also share it in shared memory, so that the checkpointer process can access it. I propose the attached patch to fix it. Agree with your diagnosis and fix. Ok, committed. For the sake of the archives, attached is a script based on Jehan-Guillaume's description that I used for testing (incidentally based on Kyotaro's script to reproduce an unrelated problem in another thread). Thanks for the report! - Heikki unarchived-wal-removed.sh Description: Bourne shell script -- 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] Unarchived WALs deleted after crash
On Fri, Feb 15, 2013 at 11:31 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 14.02.2013 17:45, Jehan-Guillaume de Rorthais wrote: I am facing an unexpected behavior on a 9.2.2 cluster that I can reproduce on current HEAD. On a cluster with archive enabled but failing, after a crash of postmaster, the checkpoint occurring before leaving the recovery mode deletes any additional WALs, even those waiting to be archived. ... Is it expected ? No, it's a bug. Ouch. It was introduced in 9.2, by commit 5286105800c7d5902f98f32e11b209c471c0c69c: Oh, sorry for my mistake. - /* - * Normally we don't delete old XLOG files during recovery to - * avoid accidentally deleting a file that looks stale due to a - * bug or hardware issue, but in fact contains important data. - * During streaming recovery, however, we will eventually fill the - * disk if we never clean up, so we have to. That's not an issue - * with file-based archive recovery because in that case we - * restore one XLOG file at a time, on-demand, and with a - * different filename that can't be confused with regular XLOG - * files. - */ - if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name)) + if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name)) [ delete the file ] With that commit, we started to keep WAL segments restored from the archive in pg_xlog, so we needed to start deleting old segments during archive recovery, even when streaming replication was not active. But the above change was to broad; we started to delete old segments also during crash recovery. The above should check InArchiveRecovery, ie. only delete old files when in archive recovery, not when in crash recovery. But there's one little complication: InArchiveRecovery is currently only valid in the startup process, so we'll need to also share it in shared memory, so that the checkpointer process can access it. I propose the attached patch to fix it. At least in 9.2, when the archived file is restored into pg_xlog, its xxx.done archive status file is created. So we don't need to check InArchiveRecovery when deleting old WAL files. Checking whether xxx.done exists is enough. Unfortunately in HEAD, xxx.done file is not created when restoring archived file because of absence of the patch. We need to implement that first. Regards, -- Fujii Masao -- 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] Unarchived WALs deleted after crash
On 15.02.2013 18:10, Fujii Masao wrote: On Fri, Feb 15, 2013 at 11:31 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: - /* - * Normally we don't delete old XLOG files during recovery to - * avoid accidentally deleting a file that looks stale due to a - * bug or hardware issue, but in fact contains important data. - * During streaming recovery, however, we will eventually fill the - * disk if we never clean up, so we have to. That's not an issue - * with file-based archive recovery because in that case we - * restore one XLOG file at a time, on-demand, and with a - * different filename that can't be confused with regular XLOG - * files. - */ - if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name)) + if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name)) [ delete the file ] With that commit, we started to keep WAL segments restored from the archive in pg_xlog, so we needed to start deleting old segments during archive recovery, even when streaming replication was not active. But the above change was to broad; we started to delete old segments also during crash recovery. The above should check InArchiveRecovery, ie. only delete old files when in archive recovery, not when in crash recovery. But there's one little complication: InArchiveRecovery is currently only valid in the startup process, so we'll need to also share it in shared memory, so that the checkpointer process can access it. I propose the attached patch to fix it. At least in 9.2, when the archived file is restored into pg_xlog, its xxx.done archive status file is created. So we don't need to check InArchiveRecovery when deleting old WAL files. Checking whether xxx.done exists is enough. Hmm, what about streamed WAL files? I guess we could go back to the pre-9.2 coding, and check WalRcvInProgress(). But I didn't actually like that too much, it seems rather random that old streamed files are recycled when wal receiver is running at the time of restartpoint, and otherwise not. Because whether wal receiver is running at the time the restartpoint happens has little to do with which files were created by streaming replication. With the right pattern of streaming files from the master, but always being teporarily disconnected when the restartpoint runs, you could still accumulate WAL files infinitely. Unfortunately in HEAD, xxx.done file is not created when restoring archived file because of absence of the patch. We need to implement that first. Ah yeah, that thing again.. (http://www.postgresql.org/message-id/50df5ba7.6070...@vmware.com) I'm going to forward-port that patch now, before it's forgotten again. It's not clear to me what the holdup was on this, but whatever the bigger patch we've been waiting for is, it can just as well be done on top of the forward-port. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Prevent restored WAL files from being archived again Re: Unnecessary WAL archiving after failover
On Sun, Dec 30, 2012 at 6:07 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 02.10.2012 21:20, Fujii Masao wrote: On Wed, Oct 3, 2012 at 3:11 AM, Simon Riggssi...@2ndquadrant.com wrote: but its not high on my radar right now unless you can explain why it should be higher. It may not be high, but I'm just worried that we are likely to forget to apply that change into HEAD if we postpone it furthermore. Ping? I haven't been paying much attention to this, but please commit the 9.2 fix to HEAD. This just caused a small merge conflict when I tried to backport (or rather, forward-port) a patch. We do more changes to HEAD later. Sorry for the late. I attached the patch for HEAD. Since I've not understood completely the recent change related to handling of the timeline yet, the patch might treat with the timeline wrongly. I'm not sure if this patch has something to do with the recent change, though. I appreciate it if you review carefully the patch. Regards, -- Fujii Masao dont_archive_restored_walfile_v2.patch Description: Binary data -- 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] src/ports/pgcheckdir.c - Ignore dot directories...
On Thu, Feb 14, 2013 at 07:21:27PM -0500, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Agreed. The attached patch modifies pg_check_dir() to report about
invisible and lost+found directory entries, and give more helpful
messages to the user.
I'm not terribly thrilled with special-casing 'lost+found' like that,
since it's an extremely filesystem-dependent thing that even today
probably only applies to a minority of our installed platforms.
The special case for dotfiles might be useful, not because of any
connection to mount points but just because someone might forget
that such could be lurking in a directory that looks empty.
I was ready to give up on this patch, but then I thought, what
percentage does lost+found and dot-file-only directories cover for mount
points? What other cases are there?
This updated version of the patch reports about dot files if they are
the _only_ files in the directory, and it suggests a top-level mount
point might be the cause.
Does this help?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
new file mode 100644
index 2ea3f6e..b8faf9c
*** a/src/bin/initdb/initdb.c
--- b/src/bin/initdb/initdb.c
*** void setup_signals(void);
*** 257,262
--- 257,263
void setup_text_search(void);
void create_data_directory(void);
void create_xlog_symlink(void);
+ void warn_on_mount_point(int error);
void initialize_data_directory(void);
*** setup_signals(void)
*** 3144,3150
void
create_data_directory(void)
{
! switch (pg_check_dir(pg_data))
{
case 0:
/* PGDATA not there, must create it */
--- 3145,3153
void
create_data_directory(void)
{
! int ret;
!
! switch ((ret = pg_check_dir(pg_data)))
{
case 0:
/* PGDATA not there, must create it */
*** create_data_directory(void)
*** 3179,3193
break;
case 2:
/* Present and not empty */
fprintf(stderr,
_(%s: directory \%s\ exists but is not empty\n),
progname, pg_data);
! fprintf(stderr,
! _(If you want to create a new database system, either remove or empty\n
! the directory \%s\ or run %s\n
! with an argument other than \%s\.\n),
! pg_data, progname, pg_data);
exit(1); /* no further message needed */
default:
--- 3182,3201
break;
case 2:
+ case 3:
+ case 4:
/* Present and not empty */
fprintf(stderr,
_(%s: directory \%s\ exists but is not empty\n),
progname, pg_data);
! if (ret != 4)
! warn_on_mount_point(ret);
! else
! fprintf(stderr,
! _(If you want to create a new database system, either remove or empty\n
! the directory \%s\ or run %s\n
! with an argument other than \%s\.\n),
! pg_data, progname, pg_data);
exit(1); /* no further message needed */
default:
*** create_xlog_symlink(void)
*** 3206,3211
--- 3214,3220
if (strcmp(xlog_dir, ) != 0)
{
char *linkloc;
+ int ret;
/* clean up xlog directory name, check it's absolute */
canonicalize_path(xlog_dir);
*** create_xlog_symlink(void)
*** 3216,3222
}
/* check if the specified xlog directory exists/is empty */
! switch (pg_check_dir(xlog_dir))
{
case 0:
/* xlog directory not there, must create it */
--- 3225,3231
}
/* check if the specified xlog directory exists/is empty */
! switch ((ret = pg_check_dir(xlog_dir)))
{
case 0:
/* xlog directory not there, must create it */
*** create_xlog_symlink(void)
*** 3255,3268
break;
case 2:
/* Present and not empty */
fprintf(stderr,
_(%s: directory \%s\ exists but is not empty\n),
progname, xlog_dir);
! fprintf(stderr,
! _(If you want to store the transaction log there, either\n
! remove or empty the directory \%s\.\n),
! xlog_dir);
exit_nicely();
default:
--- 3264,3282
break;
case 2:
+ case 3:
+ case 4:
/* Present and not empty */
fprintf(stderr,
_(%s: directory \%s\ exists but is not empty\n),
progname, xlog_dir);
! if (ret != 4)
! warn_on_mount_point(ret);
! else
! fprintf(stderr,
! _(If you want to store the transaction log there, either\n
! remove or empty the directory \%s\.\n),
! xlog_dir);
exit_nicely();
default:
*** create_xlog_symlink(void)
*** 3291,3296
--- 3305,3325
}
+ void
+ warn_on_mount_point(int error)
+ {
+ if (error == 2)
+ fprintf(stderr,
+ _(It contains a dot-prefixed/invisible file, perhaps due to it being a mount point.\n));
+ else if (error == 3)
+ fprintf(stderr,
+
Re: [HACKERS] Unarchived WALs deleted after crash
On Sat, Feb 16, 2013 at 2:07 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.02.2013 18:10, Fujii Masao wrote: On Fri, Feb 15, 2013 at 11:31 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: - /* - * Normally we don't delete old XLOG files during recovery to - * avoid accidentally deleting a file that looks stale due to a - * bug or hardware issue, but in fact contains important data. - * During streaming recovery, however, we will eventually fill the - * disk if we never clean up, so we have to. That's not an issue - * with file-based archive recovery because in that case we - * restore one XLOG file at a time, on-demand, and with a - * different filename that can't be confused with regular XLOG - * files. - */ - if (WalRcvInProgress() || XLogArchiveCheckDone(xlde-d_name)) + if (RecoveryInProgress() || XLogArchiveCheckDone(xlde-d_name)) [ delete the file ] With that commit, we started to keep WAL segments restored from the archive in pg_xlog, so we needed to start deleting old segments during archive recovery, even when streaming replication was not active. But the above change was to broad; we started to delete old segments also during crash recovery. The above should check InArchiveRecovery, ie. only delete old files when in archive recovery, not when in crash recovery. But there's one little complication: InArchiveRecovery is currently only valid in the startup process, so we'll need to also share it in shared memory, so that the checkpointer process can access it. I propose the attached patch to fix it. At least in 9.2, when the archived file is restored into pg_xlog, its xxx.done archive status file is created. So we don't need to check InArchiveRecovery when deleting old WAL files. Checking whether xxx.done exists is enough. Hmm, what about streamed WAL files? I guess we could go back to the pre-9.2 coding, and check WalRcvInProgress(). But I didn't actually like that too much, it seems rather random that old streamed files are recycled when wal receiver is running at the time of restartpoint, and otherwise not. Because whether wal receiver is running at the time the restartpoint happens has little to do with which files were created by streaming replication. With the right pattern of streaming files from the master, but always being teporarily disconnected when the restartpoint runs, you could still accumulate WAL files infinitely. Walreceiver always creates .done file when it closes the already-flushed WAL file and switches WAL file to next. So we also don't need to check WalRcvInProgress(). Unfortunately in HEAD, xxx.done file is not created when restoring archived file because of absence of the patch. We need to implement that first. Ah yeah, that thing again.. (http://www.postgresql.org/message-id/50df5ba7.6070...@vmware.com) I'm going to forward-port that patch now, before it's forgotten again. It's not clear to me what the holdup was on this, but whatever the bigger patch we've been waiting for is, it can just as well be done on top of the forward-port. I posted the patch to that thread. Regards, -- Fujii Masao -- 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] JSON Function Bike Shedding
On Tue, Feb 12, 2013 at 2:18 PM, David E. Wheeler da...@justatheory.com wrote: Hello Hackers, If you dislike bike-shedding (and who does?), delete this email and the ensuing thread right now. You have been warned! I have been playing with Andrew’s JSON enhancements and really enjoying them. I am already using them in code I’m developing for production deployment in a month or two. Kudos! However, I am not so keen on the function names. They all start with json_! This mostly feels redundant to me, since the types of the parameters are part of the function signature. Therefore, I would like to propose different names: Existing Name Proposed Name -- json_array_length() array_length() or length() or size() json_each() each_json() json_each_as_text() each_text() json_get() get_json() json_get_as_text() get_text() json_get_path() get_json() json_get_path_as_text() get_text() json_object_keys() get_keys() json_populate_record() record() or row() json_populate_recordset() records() or rows() json_unnest() get_values() json_agg() collect_json() Note that I have given json_get() and json_get_path() the same names, as it seems to me that the former is the same as the latter, with only one parameter. Same for json_get_as_text() and json_get_path_as_text(). I realize I'm in the minority here, but -1 from me on all of this. Should we also rename xml_is_well_formed() to just is_well_formed()? string_agg() to agg()? Eventually we will have more data types, and some of them will have functions that could also be called rows() or get_values(), but it's unlikely that they'll have exactly the same behavior, which will start to make things confusing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unarchived WALs deleted after crash
On 15 February 2013 17:07, Heikki Linnakangas hlinnakan...@vmware.com wrote: Unfortunately in HEAD, xxx.done file is not created when restoring archived file because of absence of the patch. We need to implement that first. Ah yeah, that thing again.. (http://www.postgresql.org/message-id/50df5ba7.6070...@vmware.com) I'm going to forward-port that patch now, before it's forgotten again. It's not clear to me what the holdup was on this, but whatever the bigger patch we've been waiting for is, it can just as well be done on top of the forward-port. Agreed. I wouldn't wait for a better version now. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
On 02/15/2013 06:03 AM, Atri Sharma wrote: Can't we have something related to machine learning? I was thinking of extending a technique where we can fill in some missing values in a data set for the user if he wants us to using some standard ml algorithms. Take a look at MADLib. My suggestion would be extending the MADlib functions; there's plenty of unimplemented ML algrothims which could be added to it. http://madlib.net/ -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unarchived WALs deleted after crash
On 15.02.2013 19:16, Fujii Masao wrote: On Sat, Feb 16, 2013 at 2:07 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.02.2013 18:10, Fujii Masao wrote: At least in 9.2, when the archived file is restored into pg_xlog, its xxx.done archive status file is created. So we don't need to check InArchiveRecovery when deleting old WAL files. Checking whether xxx.done exists is enough. Hmm, what about streamed WAL files? I guess we could go back to the pre-9.2 coding, and check WalRcvInProgress(). But I didn't actually like that too much, it seems rather random that old streamed files are recycled when wal receiver is running at the time of restartpoint, and otherwise not. Because whether wal receiver is running at the time the restartpoint happens has little to do with which files were created by streaming replication. With the right pattern of streaming files from the master, but always being teporarily disconnected when the restartpoint runs, you could still accumulate WAL files infinitely. Walreceiver always creates .done file when it closes the already-flushed WAL file and switches WAL file to next. So we also don't need to check WalRcvInProgress(). Ah, I missed that part of the patch. Okay, agreed, that's a better fix. I committed your forward-port of the 9.2 patch to master, reverted my earlier fix for this bug, and simply removed the InArchiveRecovery/ArchiveRecoveryInProgress()/RecoveryInProgress() condition from RemoveOldXlogFiles(). - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: archive_timeout behaviour when archive_mode is off (was Re: [HACKERS] Too frequent checkpoints ?)
On Fri, Feb 15, 2013 at 3:12 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: (changing subject) On Thu, Feb 14, 2013 at 11:48 PM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Feb 11, 2013 at 5:46 PM, Pavan Deolasee I also noticed that the WAL file switch happens after archive_timeout seconds irrespective of whether archive_mode is turned ON or not. This happens because we don't check if XLogArchivingActive() in CheckArchiveTimeout() function. It looks wrong to me. +1 to fix this. I've not heard the use case where archive_timeout needs to be used even in not archive mode... Ok, I will write a patch to fix this. I wonder if this is worth backpatching though. The code is like that for a long time and the fact that we haven't heard any complaints about it, may be its not worth fixing in the stable branches. But I wonder if anyone else thinks otherwise. On second thought, there might be the use case. Imagine the case where you disable archive_mode and instead use pg_receivexlog for WAL archiving purpose. WAL records are streamed from the server to pg_receivexlog in realtime, so basically archive_timeout is not required to be set in this case. But you might need to periodically verify the correctness of the connection between the server and pg_receivexlog. You can do that by checking whether new WAL file has been streamed since last check. But if there is no activity in the server, you cannot use that verification method. In this case, archive_timeout is useful even when archive_mode is not enabled. This is very narrow case, but as you said, we haven't heard any complaints about it, so I'm inclined not to fix it Regards, -- Fujii Masao -- 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] Materialized views WIP patch
On 1/25/13 1:00 AM, Kevin Grittner wrote: New patch rebased, fixes issues raised by Thom Brown, and addresses some of your points. This patch doesn't apply anymore, so I just took a superficial look. I think the intended functionality and the interfaces look pretty good. Documentation looks complete, tests are there. I have a couple of notes: * What you call WITH [NO] DATA, Oracle calls BUILD IMMEDIATE/DEFERRED. It might be better to use that as well then. * You use fields named relkind in the parse nodes, but they don't actually contain relkind values, which is confusing. I'd just name the field is_matview or something. * More generally, I wouldn't be so fond of combining the parse handling of CREATE TABLE AS and CREATE MATERIALIZED VIEW. They are similar, but then again so are a lot of other things. * Some of the terminology is inconsistent. A materialized view is sometimes called valid, populated, or built, with approximately the same meaning. Personally, I would settle on built, as per above, but it should be one term only. * I find the name of the relisvalid column a bit confusing. Especially because it only applies to materialized views, and there is already a meaning of valid for indexes. (Recall that indexes are also stored in pg_class, but they are concerned about indisvalid.) I would name it something like relmvbuilt. Btw., half of the patch seems to consist of updating places referring to relkind. Is something wrong with the meaning of relkind that this sort of thing is required? Maybe these places should be operating in terms of features, not accessing relkind directly. -- 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] Unarchived WALs deleted after crash
On 15 February 2013 16:10, Fujii Masao masao.fu...@gmail.com wrote: I propose the attached patch to fix it. At least in 9.2, when the archived file is restored into pg_xlog, its xxx.done archive status file is created. So we don't need to check InArchiveRecovery when deleting old WAL files. Checking whether xxx.done exists is enough. I don't agree. The extra test Heikki put in was useful and helps avoid issues when we get the .done creation wrong, or when people delete them. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
I would love to mentor if anybody would be willing to take a project in it. Atri Sent from my iPad On 15-Feb-2013, at 23:04, Josh Berkus j...@agliodbs.com wrote: On 02/15/2013 06:03 AM, Atri Sharma wrote: Can't we have something related to machine learning? I was thinking of extending a technique where we can fill in some missing values in a data set for the user if he wants us to using some standard ml algorithms. Take a look at MADLib. My suggestion would be extending the MADlib functions; there's plenty of unimplemented ML algrothims which could be added to it. http://madlib.net/ -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
On 15.2.2013 16:38, Alvaro Herrera wrote: Tomas Vondra escribió: On 14.2.2013 20:23, Alvaro Herrera wrote: The problem here is that creating these dummy entries will cause a difference in autovacuum behavior. Autovacuum will skip processing databases with no pgstat entry, and the intended reason is that if there's no pgstat entry it's because the database doesn't have enough activity. Now perhaps we want to change that, but it should be an explicit decision taken after discussion and thought, not side effect from an unrelated patch. I don't see how that changes the autovacuum behavior. Can you explain that a bit more? As I see it, with the old (single-file version) the autovacuum worker would get exacly the same thing, i.e. no stats at all. See in autovacuum.c the calls to pgstat_fetch_stat_dbentry(). Most of them check for NULL result and act differently depending on that. Returning a valid (not NULL) entry full of zeroes is not the same. I didn't actually try to reproduce a problem. E, but why would the patched code return entry full of zeroes and not NULL as before? The dummy files serve single purpose - confirm that the collector attempted to write info for the particular database (and did not found any data for that). All it contains is a timestamp of the write - nothing else. So the worker will read the global file (containing list of stats for dbs) and then will get NULL just like the old code. Because the database is not there and the patch does not change that at all. Tomas -- 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] [pgsql-advocacy] Call for Google Summer of Code mentors, admins
Josh Berkus wrote: Folks, Once again, Google is holding Summer of Code. We need to assess whether we want to participate this year. Questions: - Who wants to mentor for GSOC? I am open to being a mentor too. Saludos, Gilberto Castillo La Habana, Cuba --- This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu Visit our web-site: http://www.kaspersky.com, http://www.viruslist.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] JSON Function Bike Shedding
On Feb 15, 2013, at 9:25 AM, Robert Haas robertmh...@gmail.com wrote: I realize I'm in the minority here, but -1 from me on all of this. Should we also rename xml_is_well_formed() to just is_well_formed()? That would be nice, but I think that ship done sunk. string_agg() to agg()? Would love a different name, but IIRC that followed array_agg(), which was dictated by the SQL standard, in its infinite wisdom. See also =. Eventually we will have more data types, and some of them will have functions that could also be called rows() or get_values(), but it's unlikely that they'll have exactly the same behavior, which will start to make things confusing. Well, they will have to take account of *this* precedent and act accordingly. Much easier for them to look back at what has been done here than for us to look forward to something that today exists as no more than a twinkle in your eye. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [RFC] ideas for a new Python DBAPI driver (was Re: [HACKERS] libpq test suite)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Il 15/02/2013 02:45, Andrew McNamara ha scritto:
For my Python DBAPI2 PostgreSQL driver I plan the following optimizations:
I suggest you have a look at my Python ocpgdb driver:
http://code.google.com/p/ocpgdb/
Thanks, I did not know it.
It uses the v3 binary protocol exclusively (to avoid the usual escaping
security issues). A number of gotchyas were discovered along the way -
in particular, you must be a lot more careful about types (as you note
in a later reply).
Note that this query:
curs.execute('SELECT * FROM foo WHERE bah %s', '2006-1-1')
is IMHO incorrect, as per DBAPI 2.0:
http://www.python.org/dev/peps/pep-0249/#type-objects-and-constructors
There were also some issues with the v3 protocol,
most of which have been fixed now.
I hope the issues are discussed in the commit log messages; I'm creating
a git local mirror of the svn repository.
ocpgdb does not implement everything, just the bits I needed. That said,
other people/projects are using it in production, and it's proven to be
fast and stable.
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAlEemTYACgkQscQJ24LbaUTujgCfZhrNTsqy/PvRJ4qwLVqy8QVT
lNwAnjsJooEv/vss32RNMKEISOdZ16F1
=nKO5
-END PGP SIGNATURE-
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [RFC] ideas for a new Python DBAPI driver (was Re: [HACKERS] libpq test suite)
On 2/14/13 2:42 PM, Marko Tiikkaja wrote: I think the reason this doesn't work is that in order to prepare a query you need to know the parameter types, but you don't know that in Python, or at least with the way the DB-API works. For example, if you write cur.execute(SELECT * FROM tbl WHERE a = %s AND b = %s, (val1, val2)) what types will you pass to PQsendQueryParams? Pardon me if this is obvious, but why would you need to pass any types at all? Assuming we're still talking about PQsendQueryParams and not an explicit prepare/execute cycle.. Well, PQsendQueryParams() requires types to be passed, doesn't it? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [RFC] ideas for a new Python DBAPI driver (was Re: [HACKERS] libpq test suite)
On Fri, Feb 15, 2013 at 9:28 PM, Peter Eisentraut pete...@gmx.net wrote: On 2/14/13 2:42 PM, Marko Tiikkaja wrote: I think the reason this doesn't work is that in order to prepare a query you need to know the parameter types, but you don't know that in Python, or at least with the way the DB-API works. For example, if you write cur.execute(SELECT * FROM tbl WHERE a = %s AND b = %s, (val1, val2)) what types will you pass to PQsendQueryParams? Pardon me if this is obvious, but why would you need to pass any types at all? Assuming we're still talking about PQsendQueryParams and not an explicit prepare/execute cycle.. Well, PQsendQueryParams() requires types to be passed, doesn't it? No, not necessarily: they are inferred by the context if they are not specified. I've had in mind for a long time to use the *Params() functions in psycopg (although it would be largely not backwards compatible, hence to be done on user request and not by default). Psycopg has all the degrees of freedom in keeping the two implementations alive (the non-*params for backward compatibility, the *params for future usage). I'd drafted a plan on the psycopg ML some times ago. But I don't have a timeline for that: it's a major work and without pressing motivations to do it. -- Daniele -- 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] sql_drop Event Trigger
On Thu, Feb 14, 2013 at 3:39 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: Wait, I'm confused. I had a note to myself to come back and review this, but now that I look at it, I didn't think that patch was pending review. Alvaro, Tom, and I all made comments that seems to impinge upon that design rather heavily. No? The current design follows exactly your comments and design requests. Tom and Álvaro comments are the ones you did answer to saying that it's not 9.3 material, but next release at best, subject to heavy refactoring. What did I miss? Well, there's this, upon which we surely have not achieved consensus: http://www.postgresql.org/message-id/ca+tgmobq6ngsxguihwqcygf0q+7y9zhnerepo3s1vswkknw...@mail.gmail.com And then Tom also wrote this, which is kind of a good point, too: Well, a list of object OIDs is of exactly zero use once the command has been carried out. So I don't think that that represents a useful or even very testable feature on its own, if there's no provision to fire user code while the OIDs are still in the catalogs. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote: There is one odd aspect to pg_dump, but I think the way it is behaving is the best way to handle it, although I invite other opinions. If you load from pg_dump output, it will try to populated materialized views which were populated on dump, and leave the ones which were not scannable because the contents had not been generated in an empty and unscannable state on restore. That much seems pretty obvious. Where it gets a little tricky is if mva is generated with data, and mvb is generated based on mva. Then mva is truncated. Then you dump. mvb was populated at the time of the dump, but its contents can't be regenerated on restore because mva is not scannable. As the patch currently stands, you get an error on the attempt to REFRESH mvb. I think that's a good thing, but I'm open to arguments to the contrary. Hmm, anything that means a dump-and-restore can fail seems like a bad thing to me. There's nothing outrageous about that scenario. It's arguable what state dump-and-restore should leave the new database in, but I don't see why it shouldn't work. I predict we'll end up with unhappy users if we leave it like this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] pgstattuple/pgstatindex enhancement
(2013/02/15 1:55), Robert Haas wrote:
On Tue, Feb 12, 2013 at 10:22 AM, Satoshi Nagayasu sn...@uptime.jp wrote:
(1) Fix pgstatindex arguments to work same as pgstattuple.
As the document describes, pgstattuple accepts 'schema.table'
expression and oid of the table, but pgstatindex doesn't.
(because I didn't add that when I created pgstatindex...)
http://www.postgresql.org/docs/devel/static/pgstattuple.html
So, I'd like to change pgstatindex arguments to allow
schema name and oid.
Does it make sense?
Not sure. It seems nice, but it's also a backward-compatibility
break. So I don't know.
Yeah, actually, the backward-compatibility issue is the first thing
I have considered, and now I think we can keep it.
Now, pgstattuple() function accepts following syntax:
pgstattuple('table') -- table name (searches in search_path)
pgstattuple('schema.table') -- schema and table name
pgstattuple(1234) -- oid
and pgstatindex() function only accepts below so far:
pgstatindex('index') -- index name (searches in search_path)
Then, we can easily add new syntax:
pgstatindex('schema.index') -- schema and index name
pgstatindex(1234) -- oid
I think this would allow us to modify pgstatindex() without breaking
the backward-compatibility.
(2) Enhance pgstattuple/pgstatindex to allow block sampling.
Now, we have large tables and indexes in PostgreSQL, and these are
growing day by day.
pgstattuple and pgstatindex are both very important to keep database
performance well, but doing full-scans on large tables and indexes
would generate big performance impact.
So, now I think pgstattuple and pgstatindex should support
'block sampling' to collect block statistics with avoiding full-scans.
With this block sampling feature, pgstattuple/pgstatindex would be
able to collect block statistics from 1~10% of the blocks in the
table/index if the table/index is large (maybe 10GB or more).
Now that sounds really nice.
Thanks. I will try it.
Regards,
--
Satoshi Nagayasu sn...@uptime.jp
Uptime Technologies, LLC. http://www.uptime.jp
--
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] Materialized views WIP patch
On Fri, Feb 15, 2013 at 08:24:16PM -0500, Robert Haas wrote: On Fri, Feb 15, 2013 at 8:01 PM, Kevin Grittner kgri...@ymail.com wrote: There is one odd aspect to pg_dump, but I think the way it is behaving is the best way to handle it, although I invite other opinions. If you load from pg_dump output, it will try to populated materialized views which were populated on dump, and leave the ones which were not scannable because the contents had not been generated in an empty and unscannable state on restore. That much seems pretty obvious. Where it gets a little tricky is if mva is generated with data, and mvb is generated based on mva. Then mva is truncated. Then you dump. mvb was populated at the time of the dump, but its contents can't be regenerated on restore because mva is not scannable. As the patch currently stands, you get an error on the attempt to REFRESH mvb. I think that's a good thing, but I'm open to arguments to the contrary. Hmm, anything that means a dump-and-restore can fail seems like a bad thing to me. There's nothing outrageous about that scenario. It's arguable what state dump-and-restore should leave the new database in, but I don't see why it shouldn't work. I predict we'll end up with unhappy users if we leave it like this. pg_upgrade is going to fail on that pg_restore error. :-( -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
