Re: [HACKERS] 16-bit page checksums for 9.2
On Sat, Jan 7, 2012 at 11:09 AM, Simon Riggs si...@2ndquadrant.com wrote:
On Sat, Jan 7, 2012 at 10:55 AM, Simon Riggs si...@2ndquadrant.com wrote:
So there isn't any problem with there being incorrect checksums on
blocks and you can turn the parameter on and off as often and as
easily as you want. I think it can be USERSET but I wouldn't want to
encourage users to see turning it off as a performance tuning feature.
If the admin turns it on for the server, its on, so its SIGHUP.
Any holes in that I haven't noticed?
And of course, as soon as I wrote that I thought of the problem. We
mustn't make a write that hasn't been covered by a FPW, so we must
know ahead of time whether to WAL log hints or not. We can't simply
turn it on/off any longer, now that we have to WAL log hint bits also.
So thanks for making me think of that.
We *could* make it turn on/off at each checkpoint, but its easier just
to say that it can be turned on/off at server start.
Attached patch v6 now handles hint bits and checksums correctly,
following Heikki's comments.
In recovery, setting a hint doesn't dirty a block if it wasn't already
dirty. So we can write some hints, and we can set others but not write
them.
Lots of comments in the code.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0cc3296..3cb8d2a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1701,6 +1701,47 @@ SET ENABLE_SEQSCAN TO OFF;
/listitem
/varlistentry
+ varlistentry id=guc-page-checksums xreflabel=page_checksums
+ indexterm
+ primaryvarnamepage_checksums/ configuration parameter/primary
+ /indexterm
+ termvarnamepage_checksums/varname (typeboolean/type)/term
+ listitem
+ para
+When this parameter is on, the productnamePostgreSQL/ server
+calculates checksums when it writes main database pages to disk,
+flagging the page as checksum protected. When this parameter is off,
+no checksum is written, only a standard watermark in the page header.
+The database may thus contain a mix of pages with checksums and pages
+without checksums.
+ /para
+
+ para
+When pages are read into shared buffers any page flagged with a
+checksum has the checksum re-calculated and compared against the
+stored value to provide greatly improved validation of page contents.
+ /para
+
+ para
+Writes via temp_buffers are not checksummed.
+ /para
+
+ para
+Turning this parameter off speeds normal operation, but
+might allow data corruption to go unnoticed. The checksum uses
+16-bit checksums, using the fast Fletcher 16 algorithm. With this
+parameter enabled there is still a non-zero probability that an error
+could go undetected, as well as a non-zero probability of false
+positives.
+ /para
+
+ para
+This parameter can only be set at server start.
+The default is literaloff/.
+ /para
+ /listitem
+ /varlistentry
+
varlistentry id=guc-wal-buffers xreflabel=wal_buffers
termvarnamewal_buffers/varname (typeinteger/type)/term
indexterm
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 8e65962..c9538d3 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -708,6 +708,7 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata)
bool updrqst;
bool doPageWrites;
bool isLogSwitch = (rmid == RM_XLOG_ID info == XLOG_SWITCH);
+ bool IsHint = (rmid == RM_SMGR_ID info == XLOG_SMGR_HINT);
/* cross-check on whether we should be here or not */
if (!XLogInsertAllowed())
@@ -975,6 +976,18 @@ begin:;
}
/*
+ * If this is a hint record and we don't need a backup block then
+ * we have no more work to do and can exit quickly without inserting
+ * a WAL record at all. In that case return InvalidXLogRecPtr.
+ */
+ if (IsHint !(info XLR_BKP_BLOCK_MASK))
+ {
+ LWLockRelease(WALInsertLock);
+ END_CRIT_SECTION();
+ return InvalidXLogRecPtr;
+ }
+
+ /*
* If there isn't enough space on the current XLOG page for a record
* header, advance to the next page (leaving the unused space as zeroes).
*/
@@ -3670,6 +3683,13 @@ RestoreBkpBlocks(XLogRecPtr lsn, XLogRecord *record, bool cleanup)
BLCKSZ - (bkpb.hole_offset + bkpb.hole_length));
}
+ /*
+ * Any checksum set on this page will be invalid. We don't need
+ * to reset it here since it will be reset before being written
+ * but it seems worth doing this for general sanity and hygiene.
+ */
+ PageSetPageSizeAndVersion(page, BLCKSZ, PG_PAGE_LAYOUT_VERSION);
+
PageSetLSN(page, lsn);
PageSetTLI(page, ThisTimeLineID);
MarkBufferDirty(buffer);
diff --git
[HACKERS] CLOG contention, part 2
Recent results from Robert show clog contention is still an issue.
In various discussions Tom noted that pages prior to RecentXmin are
readonly and we might find a way to make use of that fact in providing
different mechanisms or resources.
I've taken that idea and used it to build a second Clog cache, known
as ClogHistory which allows access to the read-only tail of pages in
the clog. Once a page has been written to for the last time, it will
be accessed via the ClogHistory Slru in preference to the normal Clog
Slru. This separates historical accesses by readers from current write
access by committers. Historical access doesn't force dirty writes,
nor are commits made to wait when historical access occurs.
The patch is very simple because all the writes still continue through
the normal route, so is suitable for 9.2.
I'm no longer working on clog partitioning patch for this release.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
index 69b6ef3..6ff6894 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -37,6 +37,7 @@
#include access/transam.h
#include miscadmin.h
#include pg_trace.h
+#include utils/snapmgr.h
/*
* Defines for CLOG page sizes. A page is the same BLCKSZ as is used
@@ -70,10 +71,17 @@
/*
* Link to shared-memory data structures for CLOG control
+ *
+ * As of 9.2, we have 2 structures for commit log data.
+ * ClogCtl manages the main read/write part of the commit log, while
+ * the ClogHistoryCtl manages the now read-only, older part. ClogHistory
+ * removes contention from the path of transaction commits.
*/
static SlruCtlData ClogCtlData;
+static SlruCtlData ClogHistoryCtlData;
-#define ClogCtl (ClogCtlData)
+#define ClogCtl (ClogCtlData)
+#define ClogHistoryCtl (ClogHistoryCtlData)
static int ZeroCLOGPage(int pageno, bool writeXlog);
@@ -296,6 +304,10 @@ TransactionIdSetPageStatus(TransactionId xid, int nsubxids,
/* ... then the main transaction */
TransactionIdSetStatusBit(xid, status, lsn, slotno);
+
+ /* When we commit advance ClogCtl's shared RecentXminPageno if needed */
+ if (ClogCtl-shared-RecentXminPageno TransactionIdToPage(RecentXmin))
+ ClogCtl-shared-RecentXminPageno = TransactionIdToPage(RecentXmin);
}
/* Set the subtransactions */
@@ -387,6 +399,8 @@ TransactionIdSetStatusBit(TransactionId xid, XidStatus status, XLogRecPtr lsn, i
XidStatus
TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
{
+ SlruCtl clog = ClogCtl;
+ bool useClogHistory = true;
int pageno = TransactionIdToPage(xid);
int byteno = TransactionIdToByte(xid);
int bshift = TransactionIdToBIndex(xid) * CLOG_BITS_PER_XACT;
@@ -397,15 +411,35 @@ TransactionIdGetStatus(TransactionId xid, XLogRecPtr *lsn)
/* lock is acquired by SimpleLruReadPage_ReadOnly */
- slotno = SimpleLruReadPage_ReadOnly(ClogCtl, pageno, xid);
- byteptr = ClogCtl-shared-page_buffer[slotno] + byteno;
+ /*
+ * Decide whether to use main Clog or read-only ClogHistory.
+ *
+ * Our knowledge of the boundary between the two may be a little out
+ * of date, so if we try Clog and can't find it we need to try again
+ * against ClogHistory.
+ */
+ if (pageno = ClogCtl-recent_oldest_active_page_number)
+ {
+ slotno = SimpleLruReadPage_ReadOnly(clog, pageno, xid);
+ if (slotno = 0)
+ useClogHistory = false;
+ }
+
+ if (useClogHistory)
+ {
+ clog = ClogHistoryCtl;
+ slotno = SimpleLruReadPage_ReadOnly(clog, pageno, xid);
+ Assert(slotno = 0);
+ }
+
+ byteptr = clog-shared-page_buffer[slotno] + byteno;
status = (*byteptr bshift) CLOG_XACT_BITMASK;
lsnindex = GetLSNIndex(slotno, xid);
- *lsn = ClogCtl-shared-group_lsn[lsnindex];
+ *lsn = clog-shared-group_lsn[lsnindex];
- LWLockRelease(CLogControlLock);
+ LWLockRelease(clog-shared-ControlLock);
return status;
}
@@ -445,15 +479,19 @@ CLOGShmemBuffers(void)
Size
CLOGShmemSize(void)
{
- return SimpleLruShmemSize(CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE);
+ /* Reserve shmem for both ClogCtl and ClogHistoryCtl */
+ return SimpleLruShmemSize(2 * CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE);
}
void
CLOGShmemInit(void)
{
ClogCtl-PagePrecedes = CLOGPagePrecedes;
+ ClogHistoryCtl-PagePrecedes = CLOGPagePrecedes;
SimpleLruInit(ClogCtl, CLOG Ctl, CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE,
CLogControlLock, pg_clog);
+ SimpleLruInit(ClogHistoryCtl, CLOG History Ctl, CLOGShmemBuffers(), CLOG_LSNS_PER_PAGE,
+ CLogHistoryControlLock, pg_clog);
}
/*
@@ -592,6 +630,16 @@ CheckPointCLOG(void)
TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(true);
SimpleLruFlush(ClogCtl, true);
TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(true);
+
+ /*
+ * Now that we've written out all dirty buffers the only pages that
+ * will get dirty again will be pages with active transactions on them.
+ * So we can move forward the
[HACKERS] log messages for archive recovery progress
Hi, When I look into archive recovery deeply as DBA point of view, I found that it's difficult to know (1) when the recovery process switched reading WAL segments files from archive directory to pg_xlog directory, and (2) whether it succeeded applying the latest WAL segments in the pg_xlog directory. For example, when I had 47 WAL segment in the archive directory and 48 WAL segment in the pg_xlog directory, PostgreSQL said: [2011-12-08 05:59:03 JST] 9003: LOG: restored log file 00080046 from archive [2011-12-08 05:59:03 JST] 9003: LOG: restored log file 00080047 from archive cp: cannot stat `/backups/archlog/00080048': No such file or directory [2011-12-08 05:59:03 JST] 9003: LOG: record with zero length at 0/489F8B74 [2011-12-08 05:59:03 JST] 9003: LOG: redo done at 0/489F8B38 [2011-12-08 05:59:03 JST] 9003: LOG: last completed transaction was at log time 2011-12-08 05:52:01.507063+09 cp: cannot stat `/backups/archlog/0009.history': No such file or directory [2011-12-08 05:59:03 JST] 9003: LOG: selected new timeline ID: 9 [2011-12-08 05:59:03 JST] 9003: LOG: restored log file 0008.history from archive [2011-12-08 05:59:04 JST] 9003: LOG: archive recovery complete I felt it's difficult to determine whether PostgreSQL applied 48 WAL segment in the pg_xlog, or not. So, I want to propose new log messages to show archive log progress as reading WAL segments. With applying my tiny modification, the recovery process reports its progress for each WAL segment file, and also tells switching reading archive directory to pg_xlog directory explicitly as shown below. [2011-12-08 15:14:36 JST] 16758: LOG: restored log file 00080046 from archive [2011-12-08 15:14:36 JST] 16758: LOG: recoverying 00080046 [2011-12-08 15:14:36 JST] 16758: LOG: restored log file 00080047 from archive [2011-12-08 15:14:36 JST] 16758: LOG: recoverying 00080047 cp: cannot stat `/backups/archlog/00080048': No such file or directory [2011-12-08 15:14:37 JST] 16758: LOG: could not restore file 00080048 from archive [2011-12-08 15:14:37 JST] 16758: LOG: attempting to look into pg_xlog [2011-12-08 15:14:37 JST] 16758: LOG: recoverying 00080048 [2011-12-08 15:14:37 JST] 16758: LOG: record with zero length at 0/489F8B74 [2011-12-08 15:14:37 JST] 16758: LOG: redo done at 0/489F8B38 [2011-12-08 15:14:37 JST] 16758: LOG: last completed transaction was at log time 2011-12-08 05:52:01.507063+09 cp: cannot stat `/backups/archlog/0009.history': No such file or directory [2011-12-08 15:14:37 JST] 16758: LOG: selected new timeline ID: 9 [2011-12-08 15:14:37 JST] 16758: LOG: restored log file 0008.history from archive [2011-12-08 15:14:38 JST] 16758: LOG: archive recovery complete Do you think this is useful? Thanks, -- 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] [v9.2] Fix Leaky View Problem
I guess you concerned about that expected/select_views_1.out is patched, not expected/select_views.out. I'm not sure the reason why regression test script tries to make diff between results/select_views and expected/select_views_1.out. select_views.out and select_views_1.out are alternate expected output files. The regression tests pass if the actual output matches either one. Thus, you have to patch both. It was new for me. The attached patch updates both of the expected files, however, I'm not certain whether select_view.out is suitable, or not, because my results/select_view.out matched with expected/select_view_1.out. BTW, can you also resubmit the leakproof stuff as a separate patch for the last CF? Want to make sure we get that into 9.2, if at all possible. Yes, it shall be attached on the next message. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp pgsql-regtest-leaky-views.2.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] bgwriter holds onto file handles of deleted files
On 1/8/12, Alex Shulgin a...@commandprompt.com wrote: Jeff Janes jeff.ja...@gmail.com writes: It looks like it hold the file handles until either is it is killed and restarted, or until they get flushed out of vfd cache (which holds ~1000 files on my machine, so that can be a long time and lot of disk space). I don't know if this is a bug exactly, but it seems pretty unfortunate. I wonder if this is similar to what we've experienced here (follow the thread for the actual patches): http://archives.postgresql.org/pgsql-hackers/2011-06/msg00295.php Maybe you're running a version still affected by that bug? Hi Alex, I'm running git HEAD. Sorry, I should have stated that. Cheers, Jeff -- 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] Intermittent regression test failures from index-only plan changes
On Sat, Jan 7, 2012 at 5:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, if you think this is a fatal issue, then I'm afraid it's also a fatal objection to your proposal to turn SnapshotNow into an MVCC snapshot. Not just because of this, but I think its the wrong time in the cycle to be completely rethinking catalog access semantics. Or put another way, I'm too late and its too risky because the patch was becoming enormous. -- 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] Should I implement DROP INDEX CONCURRENTLY?
On Wed, Jan 4, 2012 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote:
Not having a freelist at all is probably a simpler way of avoiding the
lock contention, so I'll happily back that suggestion instead. Patch
attached, previous patch revoked.
v2 attached with cleanup of some random stuff that crept onto patch.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
diff --git a/src/backend/storage/buffer/buf_init.c b/src/backend/storage/buffer/buf_init.c
index 94cefba..9332a74 100644
--- a/src/backend/storage/buffer/buf_init.c
+++ b/src/backend/storage/buffer/buf_init.c
@@ -115,7 +115,7 @@ InitBufferPool(void)
* Initially link all the buffers together as unused. Subsequent
* management of this list is done by freelist.c.
*/
- buf-freeNext = i + 1;
+ StrategyInitFreelistBuffer(buf);
buf-io_in_progress_lock = LWLockAssign();
buf-content_lock = LWLockAssign();
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index 3e62448..6b49cae 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -27,6 +27,7 @@ typedef struct
/* Clock sweep hand: index of next buffer to consider grabbing */
int nextVictimBuffer;
+#ifdef USE_BUFMGR_FREELIST
int firstFreeBuffer; /* Head of list of unused buffers */
int lastFreeBuffer; /* Tail of list of unused buffers */
@@ -34,7 +35,7 @@ typedef struct
* NOTE: lastFreeBuffer is undefined when firstFreeBuffer is -1 (that is,
* when the list is empty)
*/
-
+#endif
/*
* Statistics. These counters should be wide enough that they can't
* overflow during a single bgwriter cycle.
@@ -134,6 +135,7 @@ StrategyGetBuffer(BufferAccessStrategy strategy, bool *lock_held)
*/
StrategyControl-numBufferAllocs++;
+#ifdef USE_BUFMGR_FREELIST
/*
* Try to get a buffer from the freelist. Note that the freeNext fields
* are considered to be protected by the BufFreelistLock not the
@@ -165,8 +167,9 @@ StrategyGetBuffer(BufferAccessStrategy strategy, bool *lock_held)
}
UnlockBufHdr(buf);
}
+#endif
- /* Nothing on the freelist, so run the clock sweep algorithm */
+ /* Run the clock sweep algorithm */
trycounter = NBuffers;
for (;;)
{
@@ -223,6 +229,7 @@ StrategyGetBuffer(BufferAccessStrategy strategy, bool *lock_held)
void
StrategyFreeBuffer(volatile BufferDesc *buf)
{
+#ifdef USE_BUFMGR_FREELIST
LWLockAcquire(BufFreelistLock, LW_EXCLUSIVE);
/*
@@ -238,6 +245,24 @@ StrategyFreeBuffer(volatile BufferDesc *buf)
}
LWLockRelease(BufFreelistLock);
+#endif
+}
+
+/*
+ * StrategyInitFreelist: put a buffer on the freelist during InitBufferPool
+ */
+void
+StrategyInitFreelistBuffer(volatile BufferDesc *buf)
+{
+#ifdef USE_BUFMGR_FREELIST
+ /*
+ * Initially link all the buffers together as unused. Subsequent
+ * management of this list is done by freelist.c.
+ */
+ buf-freeNext = i + 1;
+#else
+ buf-freeNext = FREENEXT_NOT_IN_LIST;
+#endif
}
/*
@@ -331,12 +356,14 @@ StrategyInitialize(bool init)
*/
Assert(init);
+#ifdef USE_BUFMGR_FREELIST
/*
* Grab the whole linked list of free buffers for our strategy. We
* assume it was previously set up by InitBufferPool().
*/
StrategyControl-firstFreeBuffer = 0;
StrategyControl-lastFreeBuffer = NBuffers - 1;
+#endif
/* Initialize the clock sweep pointer */
StrategyControl-nextVictimBuffer = 0;
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index e43719e..6a03d5d 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -190,6 +190,8 @@ extern bool StrategyRejectBuffer(BufferAccessStrategy strategy,
extern int StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc);
extern Size StrategyShmemSize(void);
extern void StrategyInitialize(bool init);
+extern void StrategyInitFreelistBuffer(volatile BufferDesc *buf);
+
/* buf_table.c */
extern Size BufTableShmemSize(int size);
--
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] bgwriter holds onto file handles of deleted files
On Sat, Jan 7, 2012 at 7:19 PM, Jeff Janes jeff.ja...@gmail.com wrote: I don't know if this is a bug exactly, but it seems pretty unfortunate. I'll call it a bug and I think it's mine. I've added it to the list of open items for 9.2 Thanks for testing. -- 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] pgsphere
Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Regards, Oleg On Fri, 6 Jan 2012, Andrew Dunstan wrote: On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew 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] pgsphere
Yes, I returned from Nepal a month ago :) I've sent my opinion about pgsphere in separate message. Oleg On Sat, 7 Jan 2012, Jan Urbaski wrote: - Original message - On Saturday, January 07, 2012 04:43:43 PM Dave Cramer wrote: Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. I dimly remember him mentioning traveling/hiking planning to travel around the Himalayas somewhere on -hackers. That sounds more like Oleg :) Jan 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] pgsphere
On 01/08/2012 01:19 PM, Oleg Bartunov wrote: Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. I suggest you just have Teodor add Dave as one of the project admins on pgfoundry. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LWLOCK_STATS
On Sat, Jan 7, 2012 at 5:24 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Jan 6, 2012 at 10:24 PM, Robert Haas robertmh...@gmail.com wrote: Five-minute pgbench run, scale factor 100, permanent tables, my usual config settings. Somewhat depressingly, virtually all of the interesting activity still centers around the same three locks We've seen clear evidence that the performance profile changes over time, with certain artifacts becoming more prominent. Running exactly the same tests repeatedly is useful to derive the historical perspectives, but we need a wider spread of tests to be certain that the work done is generally applicable. I'd be interested to see results from a 30 minute run, focusing on what happens in minutes 10-30, if you have time. Yeah, that seems like a good test to run. I do have time, but Nate Boley's test machine is currently otherwise occupied, so I can't run that test just now. I will run it when/if an opportunity presents itself... -- 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
[HACKERS] pg_basebackup option for handling symlinks
I've recently had a possible need for telling pg_basebackup how to handle symlinks in the remote data directory, instead of ignoring them, which is what currently happens. Possible options were recreating the symlink locally (pointing to a file on the local system) or copying the file the symlink points to. This is mainly useful in scenarios where configuration files are symlinked from the data directory. Has anyone else had the need for this? Is it worth pursuing? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql tab completion for GRANT role
psql tab completion currently only supports the form GRANT privilege ON
something TO someone (and the analogous REVOKE), but not the form GRANT
role TO someone. Here is a patch that attempts to implement the latter.
diff --git i/src/bin/psql/tab-complete.c w/src/bin/psql/tab-complete.c
index 4737062..60144a1 100644
--- i/src/bin/psql/tab-complete.c
+++ w/src/bin/psql/tab-complete.c
@@ -2209,21 +2209,52 @@ psql_completion(char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL);
/* GRANT REVOKE */
- /* Complete GRANT/REVOKE with a list of privileges */
+ /* Complete GRANT/REVOKE with a list of roles and privileges */
else if (pg_strcasecmp(prev_wd, GRANT) == 0 ||
pg_strcasecmp(prev_wd, REVOKE) == 0)
{
- static const char *const list_privilege[] =
- {SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,
- TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE,
- ALL, NULL};
-
- COMPLETE_WITH_LIST(list_privilege);
- }
- /* Complete GRANT/REVOKE sth with ON */
+ COMPLETE_WITH_QUERY(Query_for_list_of_roles
+ UNION SELECT 'SELECT'
+ UNION SELECT 'INSERT'
+ UNION SELECT 'UPDATE'
+ UNION SELECT 'DELETE'
+ UNION SELECT 'TRUNCATE'
+ UNION SELECT 'REFERENCES'
+ UNION SELECT 'TRIGGER'
+ UNION SELECT 'CREATE'
+ UNION SELECT 'CONNECT'
+ UNION SELECT 'TEMPORARY'
+ UNION SELECT 'EXECUTE'
+ UNION SELECT 'USAGE'
+ UNION SELECT 'ALL');
+ }
+ /* Complete GRANT/REVOKE privilege with ON, GRANT/REVOKE role with TO/FROM */
else if (pg_strcasecmp(prev2_wd, GRANT) == 0 ||
pg_strcasecmp(prev2_wd, REVOKE) == 0)
- COMPLETE_WITH_CONST(ON);
+ {
+ if (pg_strcasecmp(prev_wd, SELECT) == 0
+ || pg_strcasecmp(prev_wd, INSERT) == 0
+ || pg_strcasecmp(prev_wd, UPDATE) == 0
+ || pg_strcasecmp(prev_wd, DELETE) == 0
+ || pg_strcasecmp(prev_wd, TRUNCATE) == 0
+ || pg_strcasecmp(prev_wd, REFERENCES) == 0
+ || pg_strcasecmp(prev_wd, TRIGGER) == 0
+ || pg_strcasecmp(prev_wd, CREATE) == 0
+ || pg_strcasecmp(prev_wd, CONNECT) == 0
+ || pg_strcasecmp(prev_wd, TEMPORARY) == 0
+ || pg_strcasecmp(prev_wd, TEMP) == 0
+ || pg_strcasecmp(prev_wd, EXECUTE) == 0
+ || pg_strcasecmp(prev_wd, USAGE) == 0
+ || pg_strcasecmp(prev_wd, ALL) == 0)
+ COMPLETE_WITH_CONST(ON);
+ else
+ {
+ if (pg_strcasecmp(prev2_wd, GRANT) == 0)
+COMPLETE_WITH_CONST(TO);
+ else
+COMPLETE_WITH_CONST(FROM);
+ }
+ }
/*
* Complete GRANT/REVOKE sth ON with a list of tables, views, sequences,
@@ -2304,6 +2335,18 @@ psql_completion(char *text, int start, int end)
COMPLETE_WITH_CONST(FROM);
}
+ /* Complete GRANT/REVOKE * TO/FROM with username, GROUP, or PUBLIC */
+ else if (pg_strcasecmp(prev3_wd, GRANT) == 0 ||
+ pg_strcasecmp(prev_wd, TO) == 0)
+ {
+ COMPLETE_WITH_QUERY(Query_for_list_of_grant_roles);
+ }
+ else if (pg_strcasecmp(prev3_wd, REVOKE) == 0 ||
+ pg_strcasecmp(prev_wd, FROM) == 0)
+ {
+ COMPLETE_WITH_QUERY(Query_for_list_of_grant_roles);
+ }
+
/* GROUP BY */
else if (pg_strcasecmp(prev3_wd, FROM) == 0
pg_strcasecmp(prev_wd, GROUP) == 0)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Inline Extension
Hi,
The extension mechanism we added in 9.1 is aimed at allowing a fully
integrated contrib management, which was big enough a goal to preclude
doing anything else in its first release.
Now we have it and we can think some more about what features we want
covered, and a pretty obvious one that's been left out is the ability to
define and update an extension without resorting to file system support
for those extensions that do not need a shared object library. We could
have been calling that “SQL ONLY” extensions, but to simplify the
grammar support I did use the “inline” keyword so there we go.
Please find attached a WIP patch implementing that. Note that the main
core benefit to integrating this feature is the ability to easily add
regression tests for extension related features. Which is not done yet
in the attached.
I'm sending this quite soon because of the pg_dump support. When an
extension is inline, we want to dump its content, as we currently do in
the binary dump output. I had in mind that we could output a full
CREATE EXTENSION INLINE script in between some dollar-quoting rather
than adding each extension's object with a ALTER EXTENSION ... ADD line
like what pg_upgrade compatibility is currently doing.
It seems like much more work though, and I'd appreciate input about how
exactly to do that (it looks like making pg_dump reentrant, somehow).
Or some reason not to bother and just rename and share the binary
upgrade facility that Bruce already has put into pg_dump.
Here's a usage example that will certainly end up in the docs:
create extension pair inline version '1.0' schema pair not relocatable as
$pair$
CREATE TYPE pair AS ( k text, v text );
CREATE OR REPLACE FUNCTION pair(anyelement, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
CREATE OR REPLACE FUNCTION pair(text, anyelement)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
CREATE OR REPLACE FUNCTION pair(anyelement, anyelement)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair';
CREATE OR REPLACE FUNCTION pair(text, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::pair;';
CREATE OPERATOR ~ (LEFTARG = text, RIGHTARG = anyelement, PROCEDURE =
pair);
CREATE OPERATOR ~ (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE =
pair);
CREATE OPERATOR ~ (LEFTARG = anyelement, RIGHTARG = anyelement, PROCEDURE
= pair);
CREATE OPERATOR ~ (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair);
$pair$;
alter extension pair update from '1.0' to '1.1' with
$pair$
CREATE OR REPLACE FUNCTION key(pair)
RETURNS text LANGUAGE SQL AS 'SELECT ($1).k;';
CREATE OR REPLACE FUNCTION value(pair)
RETURNS text LANGUAGE SQL AS 'SELECT ($1).v;';
CREATE OPERATOR %% (RIGHTARG = pair, PROCEDURE = key);
CREATE OPERATOR %# (RIGHTARG = pair, PROCEDURE = value);
$pair$;
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index be4bbc7..4d1c18c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3020,6 +3020,13 @@
/row
row
+ entrystructfieldextinline/structfield/entry
+ entrytypebool/type/entry
+ entry/entry
+ entryTrue if extension has been created inline/entry
+ /row
+
+ row
entrystructfieldextversion/structfield/entry
entrytypetext/type/entry
entry/entry
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 7192c45..7157bc3 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -73,6 +73,8 @@ typedef struct ExtensionControlFile
bool superuser; /* must be superuser to install? */
int encoding; /* encoding of the script file, or -1 */
List *requires; /* names of prerequisite extensions */
+ bool is_inline; /* create extension inline */
+ char *script; /* script when extension is inline */
} ExtensionControlFile;
/*
@@ -590,6 +592,7 @@ read_extension_control_file(const char *extname)
control-relocatable = false;
control-superuser = true;
control-encoding = -1;
+ control-is_inline = false;
/*
* Parse the primary control file.
@@ -800,7 +803,10 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
errhint(Must be superuser to update this extension.)));
}
- filename = get_extension_script_filename(control, from_version, version);
+ if (!control-is_inline)
+ filename = get_extension_script_filename(control, from_version, version);
+ else
+ filename = INLINE; /* make compiler happy */
/*
* Force client_min_messages and log_min_messages to be at least WARNING,
@@ -856,11 +862,13 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
CurrentExtensionObject = extensionOid;
PG_TRY();
{
- char *c_sql = read_extension_script_file(control,
[HACKERS] [PATCH] Allow breaking out of hung connection attempts
When attempting to connect to a non-existent host with psql, the
connection will hang and ^C will not break the attempt. This affects two
places: startup and in interactive mode with \c. During startup, the new
behavior will be to exit psql. In interactive mode, the new behavior
will be to return to the interactive shell.
---
src/bin/psql/command.c | 17 +
src/bin/psql/startup.c |5 ++---
2 files changed, 15 insertions(+), 7 deletions(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 69fac83..74e406b 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1516,7 +1516,7 @@ static bool
do_connect(char *dbname, char *user, char *host, char *port)
{
PGconn *o_conn = pset.db,
- *n_conn;
+ *n_conn = NULL;
char *password = NULL;
if (!dbname)
@@ -1570,7 +1570,13 @@ do_connect(char *dbname, char *user, char *host, char
*port)
keywords[7] = NULL;
values[7] = NULL;
- n_conn = PQconnectdbParams(keywords, values, true);
+ if (sigsetjmp(sigint_interrupt_jmp, 1) != 0) {
+ /* got here with longjmp */
+ } else {
+ sigint_interrupt_enabled = true;
+ n_conn = PQconnectdbParams(keywords, values, true);
+ sigint_interrupt_enabled = false;
+ }
free(keywords);
free(values);
@@ -1600,7 +1606,8 @@ do_connect(char *dbname, char *user, char *host, char
*port)
*/
if (pset.cur_cmd_interactive)
{
- psql_error(%s, PQerrorMessage(n_conn));
+ if (n_conn)
+ psql_error(%s, PQerrorMessage(n_conn));
/* pset.db is left unmodified */
if (o_conn)
@@ -1608,7 +1615,9 @@ do_connect(char *dbname, char *user, char *host, char
*port)
}
else
{
- psql_error(\\connect: %s, PQerrorMessage(n_conn));
+ if (n_conn)
+ psql_error(\\connect: %s,
PQerrorMessage(n_conn));
+
if (o_conn)
{
PQfinish(o_conn);
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 8b1864c..e53d84c 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -111,8 +111,6 @@ main(int argc, char *argv[])
setvbuf(stderr, NULL, _IONBF, 0);
#endif
- setup_cancel_handler();
-
pset.progname = get_progname(argv[0]);
pset.db = NULL;
@@ -245,8 +243,9 @@ main(int argc, char *argv[])
}
/*
-* Now find something to do
+* Now find something to do (and handle cancellation, if applicable)
*/
+ setup_cancel_handler();
/*
* process file given by -f
--
1.7.7.4
Previously, these changes were submitted to -bugs:
http://archives.postgresql.org/pgsql-bugs/2012-01/msg00030.php
http://archives.postgresql.org/pgsql-bugs/2012-01/msg00036.php
Though, I think that might not have been the correct forum? I still could be
wrong with -hackers, so just let me know where to go with this if I'm still
off-base.
-Ryan Kelly
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] run check constraints only when affected columns are changed?
Currently, check constraints are verified whenever a table row is updated at all. It seems to me that we could possibly make this quite a bit more efficient if we only ran the check constraint expression when the update changes a column that is referenced by the constraint expression. Through dependency tracking, we have that information, and we already have the catalog infrastructure to store this information from primary and foreign keys. We'd just need to do some tweaking in the executor. Any thoughts on that? Possible pitfalls? -- 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] Moving more work outside WALInsertLock
On Sun, Dec 25, 2011 at 7:48 PM, Robert Haas robertmh...@gmail.com wrote: m01 tps = 631.875547 (including connections establishing) x01 tps = 611.443724 (including connections establishing) m08 tps = 4573.701237 (including connections establishing) x08 tps = 4576.242333 (including connections establishing) m16 tps = 7697.783265 (including connections establishing) x16 tps = 7837.028713 (including connections establishing) m24 tps = 11613.690878 (including connections establishing) x24 tps = 12924.027954 (including connections establishing) m32 tps = 10684.931858 (including connections establishing) x32 tps = 14168.419730 (including connections establishing) m80 tps = 10259.628774 (including connections establishing) x80 tps = 13864.651340 (including connections establishing) I think a 5% loss on 1 session is worth a 40% gain on a full loaded system. Well done Heikki. -- 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] pg_basebackup option for handling symlinks
On Sun, Jan 8, 2012 at 21:53, Peter Eisentraut pete...@gmx.net wrote: I've recently had a possible need for telling pg_basebackup how to handle symlinks in the remote data directory, instead of ignoring them, which is what currently happens. Possible options were recreating the symlink locally (pointing to a file on the local system) or copying the file the symlink points to. This is mainly useful in scenarios where configuration files are symlinked from the data directory. Has anyone else had the need for this? Is it worth pursuing? Yes. I came up to the same issue though - in one case it would've been best to copy the link, in the other case it would've been best to copy the contents of the file :S Couldn't decide which was most important... Maybe a switch would be needed? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] run check constraints only when affected columns are changed?
On 01/08/2012 03:42 PM, Peter Eisentraut wrote: Currently, check constraints are verified whenever a table row is updated at all. It seems to me that we could possibly make this quite a bit more efficient if we only ran the check constraint expression when the update changes a column that is referenced by the constraint expression. Through dependency tracking, we have that information, and we already have the catalog infrastructure to store this information from primary and foreign keys. We'd just need to do some tweaking in the executor. Any thoughts on that? Possible pitfalls? triggers? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SP-GiST versus index-only scans
Tom, There seems to exist some opportunities now with GIST which relate to geometry/geography types (but not only...): 1. Index-only scans on geometry columns with SP-GIST (being able to do a SELECT id FROM my_table WHERE mygeom...;). 2. Index clustering incuding NULL values (i.e. being able to do a CLUSTER mygeom_index ON mytable; ). This discussion suggests that at least 1. is close to be implemented. The problem of 2. has to do with handling NULL values; it's mentioned in the PostGIS manual [1]. I'm aware of kd-tree index development [2]. Don't know if clustering and index-only scans would be resolved there. But I can't find neither in the Todo List [3] ? What do you think? Yours, Stefan [2] http://postgis.refractions.net/docs/ch06.html#id2635907 [3] http://old.nabble.com/IMPORTANT%3A-%28Still%29-Seeking-Funding-for-Faster-PostGIS-Indexes-td32633545.html [3] http://wiki.postgresql.org/wiki/Todo#Indexes 2011/12/14 Tom Lane t...@sss.pgh.pa.us: Jesper Krogh jes...@krogh.cc writes: On 2011-12-14 19:48, Tom Lane wrote: I think this is somewhat wishful thinking unfortunately. The difficulty is that if the index isn't capable of reconstructing the original value, then it's probably giving only an approximate (lossy) answer, which means we'll have to visit the heap to recheck each result, which pretty much defeats the purpose of an index-only scan. I can see that it is hard to generalize, but in the tsvector case the we are indeed not capable of reconstructing the row since the positions are not stored in the index, the actual lookup is not a lossy and I'm fairly sure (based on experience) that pg dont revisit heap-tuples for checking (only for visibillity). Well, the way the tsvector code handles this stuff is that it reports the result as lossy only if the query actually poses a constraint on position (some do, some don't). That case was actually what made us move the determination of lossiness from plan time to execution time, since in the case of a non-constant tsquery, there's no way for the planner to know about it (and even with the constant case, you'd need a helper function that doesn't exist today). But this behavior is problematic for index-only scans because the planner can't tell whether a query will be lossy or not, and it makes a heck of a lot bigger difference than it used to. 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 -- 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] SP-GiST versus index-only scans
Stefan Keller sfkel...@gmail.com writes: There seems to exist some opportunities now with GIST which relate to geometry/geography types (but not only...): 1. Index-only scans on geometry columns with SP-GIST (being able to do a SELECT id FROM my_table WHERE mygeom...;). Well, if somebody builds an SP-GiST opclass for geometry, that should work. 2. Index clustering incuding NULL values (i.e. being able to do a CLUSTER mygeom_index ON mytable; ). Huh? GiST has supported nulls, and CLUSTER, since 8.2 or so. The section of the PostGIS manual you're referring to seems to be years out of date. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] collation for (expr)
Here is a patch for the following construct, specified in the SQL
standard:
collation for (expr)
returns the collation of the argument. It's similar to pg_typeof.
The SQL standard is not clear on the exact format of the return value.
I went with what ruleutils.c produces, which is useful for human
inspection and also for plugging back into SQL statements.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2e06346..822e8ad 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13637,6 +13637,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
primarypg_typeof/primary
/indexterm
+ indexterm
+primarycollation for/primary
+ /indexterm
+
para
xref linkend=functions-info-catalog-table lists functions that
extract information from the system catalogs.
@@ -13790,6 +13794,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
entrytyperegtype/type/entry
entryget the data type of any value/entry
/row
+ row
+ entryliteralfunctioncollation for (parameterany/parameter)/function/literal/entry
+ entrytypetext/type/entry
+ entryget the collation of the argument/entry
+ /row
/tbody
/tgroup
/table
@@ -13916,6 +13925,27 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
/programlisting
/para
+ para
+ The expression literalcollation for/literal returns the collation of the
+ value that is passed to it. Example:
+programlisting
+SELECT collation for (description) FROM pg_description LIMIT 1;
+ pg_collation_for
+--
+ default
+(1 row)
+
+SELECT collation for ('foo' COLLATE de_DE);
+ pg_collation_for
+--
+ de_DE
+(1 row)
+/programlisting
+ The value might be quoted and schema-qualified. If no collation is derived
+ for the argument expression, then a null value is returned. If the argument
+ is not of a collatable data type, then an error is raised.
+ /para
+
indexterm
primarycol_description/primary
/indexterm
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0ec039b..dd0e2e8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10466,6 +10466,19 @@ func_expr: func_name '(' ')' over_clause
n-location = @1;
$$ = (Node *)n;
}
+ | COLLATION FOR '(' a_expr ')'
+{
+ FuncCall *n = makeNode(FuncCall);
+ n-funcname = SystemFuncName(pg_collation_for);
+ n-args = list_make1($4);
+ n-agg_order = NIL;
+ n-agg_star = FALSE;
+ n-agg_distinct = FALSE;
+ n-func_variadic = FALSE;
+ n-over = NULL;
+ n-location = @1;
+ $$ = (Node *)n;
+}
| CURRENT_DATE
{
/*
@@ -11917,7 +11930,6 @@ unreserved_keyword:
| CLASS
| CLOSE
| CLUSTER
- | COLLATION
| COMMENT
| COMMENTS
| COMMIT
@@ -12253,6 +12265,7 @@ reserved_keyword:
| CAST
| CHECK
| COLLATE
+ | COLLATION
| COLUMN
| CONSTRAINT
| CREATE
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 9715bdd..bbac2ac 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -26,11 +26,13 @@
#include commands/dbcommands.h
#include funcapi.h
#include miscadmin.h
+#include nodes/nodeFuncs.h
#include parser/keywords.h
#include postmaster/syslogger.h
#include storage/fd.h
#include storage/pmsignal.h
#include storage/procarray.h
+#include utils/lsyscache.h
#include tcop/tcopprot.h
#include utils/builtins.h
#include utils/timestamp.h
@@ -432,3 +434,29 @@ pg_typeof(PG_FUNCTION_ARGS)
{
PG_RETURN_OID(get_fn_expr_argtype(fcinfo-flinfo, 0));
}
+
+
+/*
+ * Implementation of the COLLATE FOR expression; returns the collation
+ * of the argument.
+ */
+Datum
+pg_collation_for(PG_FUNCTION_ARGS)
+{
+ Oid typeid;
+ Oid collid;
+
+ typeid = get_fn_expr_argtype(fcinfo-flinfo, 0);
+ if (!typeid)
+ PG_RETURN_NULL();
+ if (!type_is_collatable(typeid) typeid != UNKNOWNOID)
+ ereport(ERROR,
+(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(collations are not supported by type %s,
+ format_type_be(typeid;
+
+ collid = PG_GET_COLLATION();
+ if (!collid)
+ PG_RETURN_NULL();
+ PG_RETURN_TEXT_P(cstring_to_text(generate_collation_name(collid)));
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 355c61a..e76536d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1945,6 +1945,8 @@ DESCR(convert generic options array to name/value table);
DATA(insert OID = 1619 ( pg_typeofPGNSP PGUID 12 1 0 0 0 f f f f f s 1 0 2206 2276 _null_ _null_ _null_ _null_ pg_typeof _null_ _null_ _null_ ));
DESCR(type of the argument);
+DATA(insert OID = 3163 ( pg_collation_for PGNSP PGUID 12 1 0 0 0 f f f f f s 1 0 25 2276 _null_ _null_ _null_ _null_ pg_collation_for _null_ _null_ _null_ ));
+DESCR(collation of the argument; implementation of the COLLATION FOR expression);
/* Deferrable unique constraint trigger */
Re: [HACKERS] Page Checksums
On Mon, Dec 19, 2011 at 8:18 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Double-writes would be a useful option also to reduce the size of WAL that needs to be shipped in replication. Or you could just use a filesystem that does CRCs... Double writes would reduce the size of WAL and we discussed many times we want that. Using a filesystem that does CRCs is basically saying let the filesystem cope. If that is an option, why not just turn full page writes off and let the filesystem cope? Do we really need double writes or even checksums in Postgres? What use case are we covering that isn't covered by using the right filesystem for the job? Or is that the problem? Are we implementing a feature we needed 5 years ago but don't need now? Yes, other databases have some of these features, but do we need them? Do we still need them now? Tell me we really need some or all of this and I will do my best to make it happen. -- 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] run check constraints only when affected columns are changed?
On Sun, Jan 8, 2012 at 8:42 PM, Peter Eisentraut pete...@gmx.net wrote: Currently, check constraints are verified whenever a table row is updated at all. It seems to me that we could possibly make this quite a bit more efficient if we only ran the check constraint expression when the update changes a column that is referenced by the constraint expression. Through dependency tracking, we have that information, and we already have the catalog infrastructure to store this information from primary and foreign keys. We'd just need to do some tweaking in the executor. Any thoughts on that? Possible pitfalls? It would be sensible and useful to be able to track columns that have changed during an update as we move through execution. Initially that would be columns in the SET, but could be reduced from there to actual changed columns. We have column level permissions, HOT and various other optimisations possible if that information was generally accessible. Storing a column dependency bitmap for a CHECK constraint would be a further use. We also discussed once knowing ahead of time that an UPDATE doesn't reference any indexed columns so we have no need to examine actual column changes to decide on HOT updatability. -- 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
[HACKERS] SFPUG reviewfest for 2012-01-12
Hackers, Just FYI, we're going to have a small reviewfest in San Francisco on the 12th in order to try to jump-start some of the work for CF4. Who's in charge of this CommitFest? -- 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
[HACKERS] streaming header too small
Hi, I was trying pg_basebackup on head, i used this command: postgres@jaime:/usr/local/pgsql/9.2$ bin/pg_basebackup -D $PWD/data2 -x stream -P -p 54392 i got this error 19093/19093 kB (100%), 1/1 tablespace pg_basebackup: streaming header too small: 17 pg_basebackup: child process exited with error 1 now, this streaming header size is defined in src/bin/pg_basebackup/receivelog.c as #define STREAMING_HEADER_SIZE (1+8+8+8), so WTF is this? what are these numbers? shouldn't be at least a comment explaining those? more important it's seems obvious something broke that, unless i misunderstood something which is completely possible, and that the way is do it it will broke again in the future if the header change -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
