[HACKERS] Re: [BUGS] BUG #9210: PostgreSQL string store bug? not enforce check with correct characterSET/encoding
HI,
Thanks very much.
We use dblink or foreign table migrate datas instead pg_dump now resolve
the error data load problem.
--
公益是一辈子的事,I'm Digoal,Just Do It.
At 2014-02-14 04:49:08,"Tom Lane" wrote:
>dig...@126.com writes:
>> select t, t::bytea from convert_from('\xeec1', 'sql_ascii') as g(t);
>> [ fails to check that string is valid in database encoding ]
>
>Hm, yeah. Normal input to the database goes through pg_any_to_server(),
>which will apply a validation step if the source encoding is SQL_ASCII
>and the destination encoding is something else. However, pg_convert and
>some other places call pg_do_encoding_conversion() directly, and that
>function will just quietly do nothing if either encoding is SQL_ASCII.
>
>The minimum-refactoring solution to this would be to tweak
>pg_do_encoding_conversion() so that if the src_encoding is SQL_ASCII but
>the dest_encoding isn't, it does pg_verify_mbstr() rather than nothing.
>
>I'm not sure if this would break anything we need to have work,
>though. Thoughts? Do we want to back-patch such a change?
>
> regards, tom lane
[HACKERS] Let PostgreSQL's On Schedule checkpoint write buffer smooth spread cycle by tuning IsCheckpointOnSchedule?
tSegments;
if (progress < elapsed_xlogs)
{
ckpt_cached_elapsed = elapsed_xlogs;
return false;
}
}
}
/*
* Check progress against time elapsed and checkpoint_timeout.
*/
gettimeofday(&now, NULL);
elapsed_time = ((double) ((pg_time_t) now.tv_sec - ckpt_start_time)
+
now.tv_usec / 100.0) /
CheckPointTimeout;
if (progress < elapsed_time)
{
ckpt_cached_elapsed = elapsed_time;
return false;
}
/* It looks like we're on schedule. */
return true;
}
# gmake && gmake install
$ pg_ctl restart -m fast
Test again:
progress: 291.0 s, 63144.9 tps, lat 0.426 ms stddev 0.383
progress: 292.0 s, 55063.7 tps, lat 0.480 ms stddev 1.433
progress: 293.0 s, 12225.3 tps, lat 2.238 ms stddev 4.460
progress: 294.0 s, 16436.4 tps, lat 1.621 ms stddev 4.043
progress: 295.0 s, 18516.5 tps, lat 1.444 ms stddev 3.286
progress: 296.0 s, 21983.7 tps, lat 1.251 ms stddev 2.941
progress: 297.0 s, 25759.7 tps, lat 1.034 ms stddev 2.356
progress: 298.0 s, 33139.4 tps, lat 0.821 ms stddev 1.676
progress: 299.0 s, 41904.9 tps, lat 0.644 ms stddev 1.134
progress: 300.0 s, 52432.9 tps, lat 0.513 ms stddev 0.470
progress: 301.0 s, 57115.4 tps, lat 0.471 ms stddev 0.325
progress: 302.0 s, 59422.1 tps, lat 0.452 ms stddev 0.297
progress: 303.0 s, 59860.5 tps, lat 0.449 ms stddev 0.309
We can see checkpointer wiriter buffer smooth(spread time perid) this time.
checkpoint start
--end-
buffer__sync__start num_buffers: 262144, dirty_buffers: 156761
r1_or_w2 2, pid: 22334, min: 51, max: 137, avg: 60, sum: 52016, count: 860
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 108, avg: 58, sum: 35526, count: 604
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 145, avg: 71, sum: 39779, count: 559
--end-
r1_or_w2 2, pid: 22334, min: 52, max: 172, avg: 79, sum: 47279, count: 594
--end-
r1_or_w2 2, pid: 22334, min: 44, max: 160, avg: 63, sum: 36907, count: 581
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 113, avg: 61, sum: 33895, count: 552
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 116, avg: 61, sum: 38177, count: 617
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 113, avg: 62, sum: 34199, count: 550
--end-
r1_or_w2 2, pid: 22334, min: 53, max: 109, avg: 65, sum: 39842, count: 606
--end-
r1_or_w2 2, pid: 22334, min: 50, max: 118, avg: 64, sum: 35099, count: 545
--end-
r1_or_w2 2, pid: 22334, min: 50, max: 107, avg: 64, sum: 39027, count: 606
--end-
r1_or_w2 2, pid: 22334, min: 51, max: 114, avg: 62, sum: 34054, count: 545
--end-
r1_or_w2 2, pid: 22334, min: 47, max: 106, avg: 63, sum: 38573, count: 605
--end-
r1_or_w2 2, pid: 22334, min: 48, max: 101, avg: 62, sum: 38051, count: 607
--end-
r1_or_w2 2, pid: 22334, min: 42, max: 103, avg: 61, sum: 33596, count: 545
But there is also a little problem, When PostgreSQL write xlog reach
checkpoint_segments earlier then checkpoint_timeout, the next checkpoint
will start soon, so we must tuning the checkpoint_segments larger when the
checkpoint occure busy.
Regards,
Digoal
--
公益是一辈子的事,I'm Digoal,Just Do It.
[HACKERS] Can we add syntax for references auto create index or not.
When we create table, some column use foreign key references. Now PostgreSQL don't create index for the FK, and there is no problem. But when some body need the index to speed up the query within these APP, they need to add the index manual one-by-one when has many tables. If we can add syntax for auto create index for FK by user's choose, and default not create?
Re: [HACKERS] Can we add syntax for references auto create index or not.
2015-05-28 1:41 GMT+08:00 David G. Johnston : > On Tue, May 26, 2015 at 7:03 PM, digoal zhou > wrote: > >> When we create table, some column use foreign key references. >> Now PostgreSQL don't create index for the FK, and there is no problem. >> But when some body need the index to speed up the query within these APP, >> they need to add the index manual one-by-one when has many tables. >> If we can add syntax for auto create index for FK by user's choose, and >> default not create? >> > > I presume you mean to modify the CREATE TABLE statement. > > How does this help solve the problem "when some body need the index to > speed up the query"? > Sometimes, when user need delete FK's parent table's tuple, FK has an index will improve the performance. or sometime, user need query FK table with FK's condition (assume it need the btree index). There is no other database product do the thing, but if PG has the syntax(when add FK, user can choose whether or not create index), users will happy to use it. Regards, digoal > > My first impression is that I like the idea. The fact that it is not > supported by the SQL standard is a drawback Do any other vendors do this? > > David J. > >
