On 02/16/2014 01:51 PM, Amit Kapila wrote:
On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas
<hlinnakan...@vmware.com> wrote:
>I'm pretty sure the overhead of that would be negligible, so we could always
>enable it. There are certainly a lot of scenarios where prefix/suffix
>detection alone wouldn't help, but so what.
>
>Attached is a quick patch for that, if you want to test it.
I have updated the patch to correct few problems, addressed review comments
by Andres and done some optimizations to improve CPU overhead for worst
case.
Thanks. I have to agree with Robert though that using the pglz encoding
when we're just checking for a common prefix/suffix is a pretty crappy
way of going about it [1].
As the patch stands, it includes the NULL bitmap when checking for a
common prefix. That's probably not a good idea, because it defeats the
prefix detection in a the common case that you update a field from NULL
to not-NULL or vice versa.
Attached is a rewritten version, which does the prefix/suffix tests
directly in heapam.c, and adds the prefix/suffix lengths directly as
fields in the WAL record. If you could take one more look at this
version, to check if I've missed anything.
This ought to be tested with the new logical decoding stuff as it
modified the WAL update record format which the logical decoding stuff
also relies, but I don't know anything about that.
[1]
http://www.postgresql.org/message-id/ca+tgmozstdqdku7dhcljchvmbrh1_yfouse+fuxesevnc4j...@mail.gmail.com
- Heikki
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index de4befa..cf23db5 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -6594,10 +6594,15 @@ log_heap_update(Relation reln, Buffer oldbuf,
xl_heap_header_len xlhdr;
xl_heap_header_len xlhdr_idx;
uint8 info;
+ uint16 prefix_suffix[2];
+ uint16 prefixlen = 0,
+ suffixlen = 0;
XLogRecPtr recptr;
- XLogRecData rdata[7];
+ XLogRecData rdata[9];
Page page = BufferGetPage(newbuf);
bool need_tuple_data = RelationIsLogicallyLogged(reln);
+ int nr;
+ Buffer newbufref;
/* Caller should not call me on a non-WAL-logged relation */
Assert(RelationNeedsWAL(reln));
@@ -6607,6 +6612,54 @@ log_heap_update(Relation reln, Buffer oldbuf,
else
info = XLOG_HEAP_UPDATE;
+ /*
+ * If the old and new tuple are on the same page, we only need to log
+ * the parts of the new tuple that were changed. That saves on the amount
+ * of WAL we need to write. Currently, we just count any unchanged bytes
+ * in the beginning or end of the tuples. That's quick to check, and
+ * perfectly covers the common case that only one field is updated.
+ *
+ * We could do this even if the old and new tuple are on different pages,
+ * but only if we don't make a full-page image of the old page, which is
+ * difficult to know in advance. Also, if the old tuple is corrupt for
+ * some reason, it would allow propagate the corruption to the new page,
+ * so it seems best to avoid. Under the general assumption that for long
+ * runs most updates tend to create new tuple version on same page, there
+ * should not be significant impact on WAL reduction or performance.
+ *
+ * Skip this if we're taking a full-page image of the new page, as we don't
+ * include the new tuple in the WAL record in that case.
+ */
+ if (oldbuf == newbuf && (need_tuple_data || !XLogCheckBufferNeedsBackup(newbuf)))
+ {
+ char *oldp = (char *) oldtup->t_data + oldtup->t_data->t_hoff;
+ char *newp = (char *) newtup->t_data + newtup->t_data->t_hoff;
+ int oldlen = oldtup->t_len - oldtup->t_data->t_hoff;
+ int newlen = newtup->t_len - newtup->t_data->t_hoff;
+
+ /* Check for common prefix between old and new tuple */
+ for (prefixlen = 0; prefixlen < Min(oldlen, newlen); prefixlen++)
+ {
+ if (newp[prefixlen] != oldp[prefixlen])
+ break;
+ }
+ /*
+ * Storing the length of the prefix takes 2 bytes, so we need to save
+ * at least 3 bytes or there's no point.
+ */
+ if (prefixlen < 3)
+ prefixlen = 0;
+
+ /* Same for suffix */
+ for (suffixlen = 0; suffixlen < Min(oldlen, newlen) - prefixlen; suffixlen++)
+ {
+ if (newp[newlen - suffixlen - 1] != oldp[oldlen - suffixlen - 1])
+ break;
+ }
+ if (suffixlen < 3)
+ suffixlen = 0;
+ }
+
xlrec.target.node = reln->rd_node;
xlrec.target.tid = oldtup->t_self;
xlrec.old_xmax = HeapTupleHeaderGetRawXmax(oldtup->t_data);
@@ -6619,41 +6672,119 @@ log_heap_update(Relation reln, Buffer oldbuf,
xlrec.newtid = newtup->t_self;
if (new_all_visible_cleared)
xlrec.flags |= XLOG_HEAP_NEW_ALL_VISIBLE_CLEARED;
+ if (prefixlen > 0)
+ xlrec.flags |= XLOG_HEAP_PREFIX_FROM_OLD;
+ if (suffixlen > 0)
+ xlrec.flags |= XLOG_HEAP_SUFFIX_FROM_OLD;
- rdata[0].data = (char *) &xlrec;
- rdata[0].len = SizeOfHeapUpdate;
- rdata[0].buffer = InvalidBuffer;
+ /* If new tuple is the single and first tuple on page... */
+ if (ItemPointerGetOffsetNumber(&(newtup->t_self)) == FirstOffsetNumber &&
+ PageGetMaxOffsetNumber(page) == FirstOffsetNumber)
+ {
+ info |= XLOG_HEAP_INIT_PAGE;
+ newbufref = InvalidBuffer;
+ }
+ else
+ newbufref = newbuf;
+
+ rdata[0].data = NULL;
+ rdata[0].len = 0;
+ rdata[0].buffer = oldbuf;
+ rdata[0].buffer_std = true;
rdata[0].next = &(rdata[1]);
- rdata[1].data = NULL;
- rdata[1].len = 0;
- rdata[1].buffer = oldbuf;
- rdata[1].buffer_std = true;
+ rdata[1].data = (char *) &xlrec;
+ rdata[1].len = SizeOfHeapUpdate;
+ rdata[1].buffer = InvalidBuffer;
rdata[1].next = &(rdata[2]);
+ /* prefix and/or suffix length fields */
+ if (prefixlen > 0 || suffixlen > 0)
+ {
+ if (prefixlen > 0 && suffixlen > 0)
+ {
+ prefix_suffix[0] = prefixlen;
+ prefix_suffix[1] = suffixlen;
+ rdata[2].data = (char *) &prefix_suffix;
+ rdata[2].len = 2 * sizeof(uint16);
+ }
+ else if (prefixlen > 0)
+ {
+ rdata[2].data = (char *) &prefixlen;
+ rdata[2].len = sizeof(uint16);
+ }
+ else
+ {
+ rdata[2].data = (char *) &suffixlen;
+ rdata[2].len = sizeof(uint16); /* suffix only */
+ }
+ rdata[2].buffer = newbufref;
+ rdata[2].buffer_std = true;
+ rdata[2].next = &(rdata[3]);
+ nr = 3;
+ }
+ else
+ nr = 2;
+
xlhdr.header.t_infomask2 = newtup->t_data->t_infomask2;
xlhdr.header.t_infomask = newtup->t_data->t_infomask;
xlhdr.header.t_hoff = newtup->t_data->t_hoff;
- xlhdr.t_len = newtup->t_len - offsetof(HeapTupleHeaderData, t_bits);
+ Assert(offsetof(HeapTupleHeaderData, t_bits) + prefixlen + suffixlen <= newtup->t_len);
+ xlhdr.t_len = newtup->t_len - offsetof(HeapTupleHeaderData, t_bits) - prefixlen - suffixlen;
/*
- * As with insert records, we need not store the rdata[2] segment
- * if we decide to store the whole buffer instead unless we're
- * doing logical decoding.
+ * As with insert records, we need not store this rdata segment if we
+ * decide to store the whole buffer instead, unless we're doing logical
+ * decoding.
*/
- rdata[2].data = (char *) &xlhdr;
- rdata[2].len = SizeOfHeapHeaderLen;
- rdata[2].buffer = need_tuple_data ? InvalidBuffer : newbuf;
- rdata[2].buffer_std = true;
- rdata[2].next = &(rdata[3]);
+ rdata[nr].data = (char *) &xlhdr;
+ rdata[nr].len = SizeOfHeapHeaderLen;
+ rdata[nr].buffer = need_tuple_data ? InvalidBuffer : newbufref;
+ rdata[nr].buffer_std = true;
+ rdata[nr].next = &(rdata[nr + 1]);
+ nr++;
- /* PG73FORMAT: write bitmap [+ padding] [+ oid] + data */
- rdata[3].data = (char *) newtup->t_data
- + offsetof(HeapTupleHeaderData, t_bits);
- rdata[3].len = newtup->t_len - offsetof(HeapTupleHeaderData, t_bits);
- rdata[3].buffer = need_tuple_data ? InvalidBuffer : newbuf;
- rdata[3].buffer_std = true;
- rdata[3].next = NULL;
+ /*
+ * PG73FORMAT: write bitmap [+ padding] [+ oid] + data
+ *
+ * The 'data' doesn't include the common prefix or suffix.
+ */
+ if (prefixlen == 0)
+ {
+ rdata[nr].data = ((char *) newtup->t_data) + offsetof(HeapTupleHeaderData, t_bits);
+ rdata[nr].len = newtup->t_len - offsetof(HeapTupleHeaderData, t_bits) - suffixlen;
+ rdata[nr].buffer = need_tuple_data ? InvalidBuffer : newbufref;
+ rdata[nr].buffer_std = true;
+ rdata[nr].next = NULL;
+ nr++;
+ }
+ else
+ {
+ /*
+ * Have to write the null bitmap and data after the common prefix as
+ * two separate rdata entries.
+ */
+ /* bitmap [+ padding] [+ oid] */
+ if (newtup->t_data->t_hoff - offsetof(HeapTupleHeaderData, t_bits) > 0)
+ {
+ rdata[nr - 1].next = &(rdata[nr]);
+ rdata[nr].data = ((char *) newtup->t_data) + offsetof(HeapTupleHeaderData, t_bits);
+ rdata[nr].len = newtup->t_data->t_hoff - offsetof(HeapTupleHeaderData, t_bits);
+ rdata[nr].buffer = need_tuple_data ? InvalidBuffer : newbufref;
+ rdata[nr].buffer_std = true;
+ rdata[nr].next = NULL;
+ nr++;
+ }
+
+ /* data after common prefix */
+ rdata[nr - 1].next = &(rdata[nr]);
+ rdata[nr].data = ((char *) newtup->t_data) + newtup->t_data->t_hoff + prefixlen;
+ rdata[nr].len = newtup->t_len - newtup->t_data->t_hoff - prefixlen - suffixlen;
+ rdata[nr].buffer = need_tuple_data ? InvalidBuffer : newbufref;
+ rdata[nr].buffer_std = true;
+ rdata[nr].next = NULL;
+ nr++;
+ }
/*
* Separate storage for the FPW buffer reference of the new page in the
@@ -6661,13 +6792,15 @@ log_heap_update(Relation reln, Buffer oldbuf,
*/
if (need_tuple_data)
{
- rdata[3].next = &(rdata[4]);
+ rdata[nr - 1].next = &(rdata[nr]);
+
+ rdata[nr].data = NULL,
+ rdata[nr].len = 0;
+ rdata[nr].buffer = newbufref;
+ rdata[nr].buffer_std = true;
+ rdata[nr].next = NULL;
+ nr++;
- rdata[4].data = NULL,
- rdata[4].len = 0;
- rdata[4].buffer = newbuf;
- rdata[4].buffer_std = true;
- rdata[4].next = NULL;
xlrec.flags |= XLOG_HEAP_CONTAINS_NEW_TUPLE;
/* We need to log a tuple identity */
@@ -6679,19 +6812,21 @@ log_heap_update(Relation reln, Buffer oldbuf,
xlhdr_idx.header.t_hoff = old_key_tuple->t_data->t_hoff;
xlhdr_idx.t_len = old_key_tuple->t_len;
- rdata[4].next = &(rdata[5]);
- rdata[5].data = (char *) &xlhdr_idx;
- rdata[5].len = SizeOfHeapHeaderLen;
- rdata[5].buffer = InvalidBuffer;
- rdata[5].next = &(rdata[6]);
+ rdata[nr - 1].next = &(rdata[nr]);
+ rdata[nr].data = (char *) &xlhdr_idx;
+ rdata[nr].len = SizeOfHeapHeaderLen;
+ rdata[nr].buffer = InvalidBuffer;
+ rdata[nr].next = &(rdata[nr + 1]);
+ nr++;
/* PG73FORMAT: write bitmap [+ padding] [+ oid] + data */
- rdata[6].data = (char *) old_key_tuple->t_data
+ rdata[nr].data = (char *) old_key_tuple->t_data
+ offsetof(HeapTupleHeaderData, t_bits);
- rdata[6].len = old_key_tuple->t_len
+ rdata[nr].len = old_key_tuple->t_len
- offsetof(HeapTupleHeaderData, t_bits);
- rdata[6].buffer = InvalidBuffer;
- rdata[6].next = NULL;
+ rdata[nr].buffer = InvalidBuffer;
+ rdata[nr].next = NULL;
+ nr++;
if (reln->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
xlrec.flags |= XLOG_HEAP_CONTAINS_OLD_TUPLE;
@@ -6700,19 +6835,6 @@ log_heap_update(Relation reln, Buffer oldbuf,
}
}
- /* If new tuple is the single and first tuple on page... */
- if (ItemPointerGetOffsetNumber(&(newtup->t_self)) == FirstOffsetNumber &&
- PageGetMaxOffsetNumber(page) == FirstOffsetNumber)
- {
- XLogRecData *rcur = &rdata[2];
- info |= XLOG_HEAP_INIT_PAGE;
- while (rcur != NULL)
- {
- rcur->buffer = InvalidBuffer;
- rcur = rcur->next;
- }
- }
-
recptr = XLogInsert(RM_HEAP_ID, info, rdata);
return recptr;
@@ -7739,17 +7861,25 @@ heap_xlog_update(XLogRecPtr lsn, XLogRecord *record, bool hot_update)
Page page;
OffsetNumber offnum;
ItemId lp = NULL;
+ HeapTupleData oldtup;
+ char *recdata;
HeapTupleHeader htup;
+ uint16 prefixlen = 0,
+ suffixlen = 0;
+ char *newp;
struct
{
HeapTupleHeaderData hdr;
char data[MaxHeapTupleSize];
} tbuf;
xl_heap_header_len xlhdr;
- int hsize;
uint32 newlen;
Size freespace;
+ /* to keep the compiler quiet */
+ oldtup.t_data = NULL;
+ oldtup.t_len = 0;
+
/*
* The visibility map may need to be fixed even if the heap page is
* already up-to-date.
@@ -7816,6 +7946,9 @@ heap_xlog_update(XLogRecPtr lsn, XLogRecord *record, bool hot_update)
htup = (HeapTupleHeader) PageGetItem(page, lp);
+ oldtup.t_data = htup;
+ oldtup.t_len = ItemIdGetLength(lp);
+
htup->t_infomask &= ~(HEAP_XMAX_BITS | HEAP_MOVED);
htup->t_infomask2 &= ~HEAP_KEYS_UPDATED;
if (hot_update)
@@ -7914,20 +8047,63 @@ newsame:;
if (PageGetMaxOffsetNumber(page) + 1 < offnum)
elog(PANIC, "heap_update_redo: invalid max offset number");
- hsize = SizeOfHeapUpdate + SizeOfHeapHeaderLen;
+ recdata = (char *) xlrec + SizeOfHeapUpdate;
- memcpy((char *) &xlhdr,
- (char *) xlrec + SizeOfHeapUpdate,
- SizeOfHeapHeaderLen);
- newlen = xlhdr.t_len;
- Assert(newlen <= MaxHeapTupleSize);
+ if (xlrec->flags & XLOG_HEAP_PREFIX_FROM_OLD)
+ {
+ memcpy(&prefixlen, recdata, sizeof(uint16));
+ recdata += sizeof(uint16);
+ }
+ if (xlrec->flags & XLOG_HEAP_SUFFIX_FROM_OLD)
+ {
+ memcpy(&suffixlen, recdata, sizeof(uint16));
+ recdata += sizeof(uint16);
+ }
+
+ memcpy((char *) &xlhdr, recdata, SizeOfHeapHeaderLen);
+ recdata += SizeOfHeapHeaderLen;
+
+ Assert(xlhdr.t_len + prefixlen + suffixlen <= MaxHeapTupleSize);
htup = &tbuf.hdr;
MemSet((char *) htup, 0, sizeof(HeapTupleHeaderData));
- /* PG73FORMAT: get bitmap [+ padding] [+ oid] + data */
- memcpy((char *) htup + offsetof(HeapTupleHeaderData, t_bits),
- (char *) xlrec + hsize,
- newlen);
- newlen += offsetof(HeapTupleHeaderData, t_bits);
+
+ /*
+ * Reconstruct the new tuple using the prefix and/or suffix from the old
+ * tuple, and the data stored in the WAL record.
+ */
+ newp = (char *) htup + offsetof(HeapTupleHeaderData, t_bits);
+ if (prefixlen > 0)
+ {
+ int len;
+
+ /* copy bitmap [+ padding] [+ oid] from WAL record */
+ len = xlhdr.header.t_hoff - offsetof(HeapTupleHeaderData, t_bits);
+ memcpy(newp, recdata, len);
+ recdata += len;
+ newp += len;
+
+ /* copy prefix from old tuple */
+ memcpy(newp, (char *) oldtup.t_data + oldtup.t_data->t_hoff, prefixlen);
+ newp += prefixlen;
+
+ /* copy new tuple data from WAL record */
+ len = xlhdr.t_len - (xlhdr.header.t_hoff - offsetof(HeapTupleHeaderData, t_bits));
+ memcpy(newp, recdata, len);
+ recdata += len;
+ newp += len;
+ }
+ else
+ {
+ /* copy bitmap [+ padding] [+ oid] + data, all in one go */
+ memcpy(newp, recdata, xlhdr.t_len);
+ recdata += xlhdr.t_len;
+ newp += xlhdr.t_len;
+ }
+ /* copy suffix from old tuple */
+ if (suffixlen > 0)
+ memcpy(newp, (char *) oldtup.t_data + oldtup.t_len - suffixlen, suffixlen);
+
+ newlen = offsetof(HeapTupleHeaderData, t_bits) + xlhdr.t_len + prefixlen + suffixlen;
htup->t_infomask2 = xlhdr.header.t_infomask2;
htup->t_infomask = xlhdr.header.t_infomask;
htup->t_hoff = xlhdr.header.t_hoff;
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index ad46eb0..05a83a4 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2333,6 +2333,28 @@ XLogRecPtrToBytePos(XLogRecPtr ptr)
}
/*
+ * Determine whether the buffer referenced has to be backed up. Since we don't
+ * yet have the insert lock, fullPageWrites and forcePageWrites could change
+ * later, but will not cause any problem because this function is used only to
+ * identify whether EWT is required for update.
+ */
+bool
+XLogCheckBufferNeedsBackup(Buffer buffer)
+{
+ bool doPageWrites;
+ Page page;
+
+ page = BufferGetPage(buffer);
+
+ doPageWrites = XLogCtl->Insert.fullPageWrites || XLogCtl->Insert.forcePageWrites;
+
+ if (doPageWrites && PageGetLSN(page) <= RedoRecPtr)
+ return true; /* buffer requires backup */
+
+ return false; /* buffer does not need to be backed up */
+}
+
+/*
* Determine whether the buffer referenced by an XLogRecData item has to
* be backed up, and if so fill a BkpBlock struct for it. In any case
* save the buffer's LSN at *lsn.
diff --git a/src/include/access/heapam_xlog.h b/src/include/access/heapam_xlog.h
index d4383ab..82237c9 100644
--- a/src/include/access/heapam_xlog.h
+++ b/src/include/access/heapam_xlog.h
@@ -67,6 +67,8 @@
#define XLOG_HEAP_CONTAINS_OLD_TUPLE (1<<2)
#define XLOG_HEAP_CONTAINS_OLD_KEY (1<<3)
#define XLOG_HEAP_CONTAINS_NEW_TUPLE (1<<4)
+#define XLOG_HEAP_PREFIX_FROM_OLD (1<<5)
+#define XLOG_HEAP_SUFFIX_FROM_OLD (1<<6)
/* convenience macro for checking whether any form of old tuple was logged */
#define XLOG_HEAP_CONTAINS_OLD \
@@ -179,9 +181,25 @@ typedef struct xl_heap_update
ItemPointerData newtid; /* new inserted tuple id */
uint8 old_infobits_set; /* infomask bits to set on old tuple */
uint8 flags;
- /* NEW TUPLE xl_heap_header AND TUPLE DATA FOLLOWS AT END OF STRUCT */
+
+ /*
+ * If XLOG_HEAP_PREFIX_FROM_OLD or XLOG_HEAP_SUFFIX_FROM_OLD flags are
+ * set, the prefix and/or suffix come next, as one or two uint16s.
+ *
+ * After that, xl_heap_header_len and new tuple data follows. The new
+ * tuple data and length don't include the prefix and suffix, which are
+ * copied from the old tuple on replay. The new tuple data is omitted if
+ * a full-page image of the page was taken (unless the
+ * XLOG_HEAP_CONTAINS_NEW_TUPLE flag is set, in which case it's included
+ * anyway).
+ *
+ * If XLOG_HEAP_CONTAINS_OLD_TUPLE or XLOG_HEAP_CONTAINS_OLD_KEY flags are
+ * set, another xl_heap_header_len struct and and tuple data for the old
+ * tuple follows.
+ */
} xl_heap_update;
+/* NOTE: SizeOfHeapUpdate doesn't include prefix_suffix */
#define SizeOfHeapUpdate (offsetof(xl_heap_update, flags) + sizeof(uint8))
/*
diff --git a/src/include/access/htup_details.h b/src/include/access/htup_details.h
index a3eba98..abb5620 100644
--- a/src/include/access/htup_details.h
+++ b/src/include/access/htup_details.h
@@ -740,6 +740,11 @@ extern HeapTuple heap_modify_tuple(HeapTuple tuple,
extern void heap_deform_tuple(HeapTuple tuple, TupleDesc tupleDesc,
Datum *values, bool *isnull);
+extern bool heap_delta_encode(TupleDesc tupleDesc, HeapTuple oldtup,
+ HeapTuple newtup, char *encdata, uint32 *enclen);
+extern void heap_delta_decode (char *encdata, uint32 enclen, HeapTuple oldtup,
+ HeapTuple newtup);
+
/* these three are deprecated versions of the three above: */
extern HeapTuple heap_formtuple(TupleDesc tupleDescriptor,
Datum *values, char *nulls);
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 11ab277..87402b0 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -279,6 +279,7 @@ typedef struct CheckpointStatsData
extern CheckpointStatsData CheckpointStats;
extern XLogRecPtr XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata);
+extern bool XLogCheckBufferNeedsBackup(Buffer buffer);
extern void XLogFlush(XLogRecPtr RecPtr);
extern bool XLogBackgroundFlush(void);
extern bool XLogNeedsFlush(XLogRecPtr RecPtr);
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 71b856f..bad75a2 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -97,3 +97,73 @@ SELECT a, b, char_length(c) FROM update_test;
(2 rows)
DROP TABLE update_test;
+--
+-- Test to update continuous and non continuous columns
+--
+DROP TABLE IF EXISTS update_test;
+NOTICE: table "update_test" does not exist, skipping
+CREATE TABLE update_test (
+ bser bigserial,
+ bln boolean,
+ ename VARCHAR(25),
+ perf_f float(8),
+ grade CHAR,
+ dept CHAR(5) NOT NULL,
+ dob DATE,
+ idnum INT,
+ addr VARCHAR(30) NOT NULL,
+ destn CHAR(6),
+ Gend CHAR,
+ samba BIGINT,
+ hgt float,
+ ctime TIME
+);
+INSERT INTO update_test VALUES (
+ nextval('update_test_bser_seq'::regclass),
+ TRUE,
+ 'Test',
+ 7.169,
+ 'B',
+ 'CSD',
+ '2000-01-01',
+ 520,
+ 'road2,
+ streeeeet2,
+ city2',
+ 'dcy2',
+ 'M',
+ 12000,
+ 50.4,
+ '00:00:00.0'
+);
+SELECT * from update_test;
+ bser | bln | ename | perf_f | grade | dept | dob | idnum | addr | destn | gend | samba | hgt | ctime
+------+-----+-------+--------+-------+-------+------------+-------+-----------------------------+--------+------+-------+------+----------
+ 1 | t | Test | 7.169 | B | CSD | 01-01-2000 | 520 | road2, +| dcy2 | M | 12000 | 50.4 | 00:00:00
+ | | | | | | | | streeeeet2,+| | | | |
+ | | | | | | | | city2 | | | | |
+(1 row)
+
+-- update first column
+UPDATE update_test SET bser = bser - 1 + 1;
+-- update middle column
+UPDATE update_test SET perf_f = 8.9;
+-- update last column
+UPDATE update_test SET ctime = '00:00:00.1';
+-- update 3 continuos columns
+UPDATE update_test SET destn = 'dcy2', samba = 0 WHERE Gend = 'M' and dept = 'CSD';
+-- update two non continuos columns
+UPDATE update_test SET destn = 'moved', samba = 0;
+UPDATE update_test SET bln = FALSE, hgt = 10.1;
+-- update causing some column alignment difference
+UPDATE update_test SET ename = 'Tes';
+UPDATE update_test SET dept = 'Test';
+SELECT * from update_test;
+ bser | bln | ename | perf_f | grade | dept | dob | idnum | addr | destn | gend | samba | hgt | ctime
+------+-----+-------+--------+-------+-------+------------+-------+-----------------------------+--------+------+-------+------+------------
+ 1 | f | Tes | 8.9 | B | Test | 01-01-2000 | 520 | road2, +| moved | M | 0 | 10.1 | 00:00:00.1
+ | | | | | | | | streeeeet2,+| | | | |
+ | | | | | | | | city2 | | | | |
+(1 row)
+
+DROP TABLE update_test;
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index a8a028f..bfe4ecb 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -59,3 +59,70 @@ UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
SELECT a, b, char_length(c) FROM update_test;
DROP TABLE update_test;
+
+
+--
+-- Test to update continuous and non continuous columns
+--
+
+DROP TABLE IF EXISTS update_test;
+CREATE TABLE update_test (
+ bser bigserial,
+ bln boolean,
+ ename VARCHAR(25),
+ perf_f float(8),
+ grade CHAR,
+ dept CHAR(5) NOT NULL,
+ dob DATE,
+ idnum INT,
+ addr VARCHAR(30) NOT NULL,
+ destn CHAR(6),
+ Gend CHAR,
+ samba BIGINT,
+ hgt float,
+ ctime TIME
+);
+
+INSERT INTO update_test VALUES (
+ nextval('update_test_bser_seq'::regclass),
+ TRUE,
+ 'Test',
+ 7.169,
+ 'B',
+ 'CSD',
+ '2000-01-01',
+ 520,
+ 'road2,
+ streeeeet2,
+ city2',
+ 'dcy2',
+ 'M',
+ 12000,
+ 50.4,
+ '00:00:00.0'
+);
+
+SELECT * from update_test;
+
+-- update first column
+UPDATE update_test SET bser = bser - 1 + 1;
+
+-- update middle column
+UPDATE update_test SET perf_f = 8.9;
+
+-- update last column
+UPDATE update_test SET ctime = '00:00:00.1';
+
+-- update 3 continuos columns
+UPDATE update_test SET destn = 'dcy2', samba = 0 WHERE Gend = 'M' and dept = 'CSD';
+
+-- update two non continuos columns
+UPDATE update_test SET destn = 'moved', samba = 0;
+UPDATE update_test SET bln = FALSE, hgt = 10.1;
+
+-- update causing some column alignment difference
+UPDATE update_test SET ename = 'Tes';
+UPDATE update_test SET dept = 'Test';
+
+SELECT * from update_test;
+DROP TABLE update_test;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
