Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

[高增琦]

Hi,

I found the last commit changed as:

```
/* skip invalid characters */
do {
(*scan_length)++;
-   } while (**scan_length != ' ' && **scan_length != '\0' &&
isdigit(**scan_length));
+   } while (isdigit(**scan_length));
return false;
}
```

It will still return false if we got non-digital characters after ".",
then it will error out "invalid input syntax for type int" for "a" . (if
input is "7.a")

Although this error message is not wrong, I think it should be better to
give error message as "invalid input syntax for type int" for "7.a".
This could be done by delete "return false;" after "while(...)", let
the following if to decide which to return.


2017-11-02 15:25 GMT+08:00 Michael Meskes :

> > I am afraid the changes may separate "7.a" to "7" and "a", then error
> > out
> > with "invalid input syntax for type int" for "a".
>
> Which is correct, is it not?
>
> > How about changes as below? (use following the if to decide true or
> > false)
> > ...
> >return false;
> > +} while (isdigit(**scan_length));
>
> Yes, this is certainly correct and better than what I committed. What
> was I thinking yesterday?
>
> I think the same function is used for identifying garbage in floats
> which might ask for different logic. Let me check.
>
> Michael
> --
> Michael Meskes
> Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
> Meskes at (Debian|Postgresql) dot Org
> Jabber: michael at xmpp dot meskes dot org
> VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

[高增琦]

Diff from the head:
(use the following if to decide true or false)

```
diff --git a/src/interfaces/ecpg/ecpglib/data.c
b/src/interfaces/ecpg/ecpglib/data.c
index 5375934..1621e7b 100644
--- a/src/interfaces/ecpg/ecpglib/data.c
+++ b/src/interfaces/ecpg/ecpglib/data.c
@@ -57,8 +57,7 @@ garbage_left(enum ARRAY_TYPE isarray, char **scan_length,
enum COMPAT_MODE compa
 /* skip invalid characters */
 do {
 (*scan_length)++;
-} while (**scan_length != ' ' && **scan_length != '\0' &&
isdigit(**scan_length));
-return false;
+} while (isdigit(**scan_length));
 }

 if (**scan_length != ' ' && **scan_length != '\0')

```

2017-11-02 11:07 GMT+08:00 高增琦 <pgf...@gmail.com>:

> Thanks for commit.
>
> I am afraid the changes may separate "7.a" to "7" and "a", then error out
> with "invalid input syntax for type int" for "a".
>
> How about changes as below? (use following the if to decide true or false)
>
> ```
> -} while (**scan_length != ' ' && **scan_length != '\0');
> -return false;
> +} while (isdigit(**scan_length));
> ```
>
> 2017-11-01 20:35 GMT+08:00 Michael Meskes <mes...@postgresql.org>:
>
>> > Any comments?
>>
>> Sorry, I've been working through the backlog of three weeks of
>> traveling.
>>
>> > > I tried some tests with ecpg informix mode.
>> > > When trying to store float data into a integer var, I got endless
>> > > loop.
>> > >
>> > > The reason is:
>> > > In informix mode, ecpg can accept
>> > > string form of float number when processing query result.
>> > > During checking the string form of float number, it seems
>> > > that ecpg forgot to skip characters after '.'.
>> > > Then outer loop will never stop because it hopes to see '\0'.
>> > >
>> > > The first patch will reproduce the problem in ecpg's regress test.
>> > > The second patch tries to fix it in simple way.
>>
>> Thanks for spotting and fixing. I changed your patch slightly and made
>> it check if the rest of the data is indeed digits, or else it would
>> accept something like "7.hello" as "7".
>>
>> Committed.
>>
>> Michael
>> --
>> Michael Meskes
>> Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
>> Meskes at (Debian|Postgresql) dot Org
>> Jabber: michael at xmpp dot meskes dot org
>> VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
>>
>
>
>
> --
> GaoZengqi
> pgf...@gmail.com
> zengqi...@gmail.com
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

[高增琦]

Thanks for commit.

I am afraid the changes may separate "7.a" to "7" and "a", then error out
with "invalid input syntax for type int" for "a".

How about changes as below? (use following the if to decide true or false)

```
-} while (**scan_length != ' ' && **scan_length != '\0');
-return false;
+} while (isdigit(**scan_length));
```

2017-11-01 20:35 GMT+08:00 Michael Meskes :

> > Any comments?
>
> Sorry, I've been working through the backlog of three weeks of
> traveling.
>
> > > I tried some tests with ecpg informix mode.
> > > When trying to store float data into a integer var, I got endless
> > > loop.
> > >
> > > The reason is:
> > > In informix mode, ecpg can accept
> > > string form of float number when processing query result.
> > > During checking the string form of float number, it seems
> > > that ecpg forgot to skip characters after '.'.
> > > Then outer loop will never stop because it hopes to see '\0'.
> > >
> > > The first patch will reproduce the problem in ecpg's regress test.
> > > The second patch tries to fix it in simple way.
>
> Thanks for spotting and fixing. I changed your patch slightly and made
> it check if the rest of the data is indeed digits, or else it would
> accept something like "7.hello" as "7".
>
> Committed.
>
> Michael
> --
> Michael Meskes
> Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
> Meskes at (Debian|Postgresql) dot Org
> Jabber: michael at xmpp dot meskes dot org
> VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Try to fix endless loop in ecpg with informix mode

[高增琦]

Any comments?

2017-10-26 16:03 GMT+08:00 高增琦 <pgf...@gmail.com>:

> Hi,
>
> I tried some tests with ecpg informix mode.
> When trying to store float data into a integer var, I got endless loop.
>
> The reason is:
> In informix mode, ecpg can accept
> string form of float number when processing query result.
> During checking the string form of float number, it seems
> that ecpg forgot to skip characters after '.'.
> Then outer loop will never stop because it hopes to see '\0'.
>
> The first patch will reproduce the problem in ecpg's regress test.
> The second patch tries to fix it in simple way.
>
> --
> GaoZengqi
> pgf...@gmail.com
> zengqi...@gmail.com
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

[HACKERS] Try to fix endless loop in ecpg with informix mode

[高增琦]

Hi,

I tried some tests with ecpg informix mode.
When trying to store float data into a integer var, I got endless loop.

The reason is:
In informix mode, ecpg can accept
string form of float number when processing query result.
During checking the string form of float number, it seems
that ecpg forgot to skip characters after '.'.
Then outer loop will never stop because it hopes to see '\0'.

The first patch will reproduce the problem in ecpg's regress test.
The second patch tries to fix it in simple way.

-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com


0001-Edit-ecpg-regress-test-to-trigger-endless-loop.patch
Description: Binary data


0002-Fix-endless-loop-in-ecpg-with-informix-mode.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

[HACKERS] typo for using "OBJECT_TYPE" for "security label on domain" in "gram.y"

[高增琦]

Commit: 3f88672a4e4d8e648d24ccc65937da61c7660854 add "security label on
domain"
in "gram.y", and set "objtype" to "OBJECT_TYPE".

Is this a typo?

-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Dropping a partitioned table takes too long

[高增琦]

It seems that in 'load_relcache_init_file()', we forget to initialize
'rd_pdcxt' of relcache.

2017-04-27 0:33 GMT+08:00 Robert Haas :

> On Wed, Apr 26, 2017 at 12:22 PM, Tom Lane  wrote:
> > Robert Haas  writes:
> >> On Tue, Apr 25, 2017 at 10:05 PM, Amit Langote
> >>  wrote:
> >>> Your patch seems to be a much better solution to the problem, thanks.
> >
> >> Does anyone wish to object to this patch as untimely?
> >
> >> If not, I'll commit it.
> >
> > It's certainly not untimely to address such problems.  What I'm wondering
> > is if we should commit both patches.  Avoiding an unnecessary heap_open
> > is certainly a good thing, but it seems like the memory leak addressed
> > by the first patch might still be of concern in other scenarios.
>
> I will defer to you on that.  If you think that patch is a good idea,
> please have at it.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Dropping a partitioned table takes too long

[高增琦]

The attached patch try to replace 'heap_open' with 'LockRelationOid' when
locking parent table.
It improved dropping a table with 7000 partitions.

2017-04-25 15:07 GMT+08:00 Amit Langote :

> $SUBJECT, if the table has, say, 2000 partitions.
>
> The main reason seems to be that RelationBuildPartitionDesc() will be
> called that many times within the same transaction, which perhaps we
> cannot do much about right away.  But one thing we could do is to reduce
> the impact of memory allocations it does.  They are currently leaked into
> the caller's context, which may not be reset immediately (such as
> PortalHeapMemory).  Instead of doing it in the caller's context, use a
> temporary context that is deleted before returning.  Attached is a patch
> for that.  On my local development VM, `drop table
> table_with_2000_partitions` finished in 27 seconds with the patch instead
> of more than 20 minutes that it currently takes.
>
> Thoughts?
>
> Adding this to the open items list.
>
> Thanks,
> Amit
>
> PS: this was actually mentioned by Ragnar Ouchterlony who reported some
> bugs back in declarative partitioning in January [1]
>
> [1]
> https://www.postgresql.org/message-id/17d89e08-874b-c1b1-
> aa46-12d5afb26235%40agama.tv
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com


0001-Don-t-building-a-relcache-entry-since-we-don-t-need-.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] Do we support using agg or window functions in delete statement?

[高增琦]

Thanks a lot for reply.

2017-01-11 20:46 GMT+08:00 Tom Lane :

> =?UTF-8?B?6auY5aKe55Cm?=  writes:
> > In transformDeleteStmt:
>
> > qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
> > qry->hasAggs = pstate->p_hasAggs;
> > if (pstate->p_hasAggs)
> > parseCheckAggregates(pstate, qry);
>
> > Do we support using agg or window function in delete statement?
> > Or, this code should be removed?
>
> I think it's dead code given the syntactic limitations on DELETE,
> but I would not be in favor of removing it.  Better to have it there to
> keep transformDeleteStmt looking as much as possible like the other ones.
> It's not like that's either expensive or a lot of code.
>

At present, only transformSelectStmt and transformSetOperationStmt
has parseCheckAggregates. All other transformXXXStmt don't contain it.

This inconsistency makes me have the question at the first mail.
I think it maybe better to do something.



>
> An example of why this would be penny-wise and pound-foolish is that
> we might choose to apply the check that you can't write aggregates in
> DELETE inside parseCheckAggregates.  (We don't, but it's not an impossible
> future restructuring.)
>
> regards, tom lane
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

[HACKERS] Do we support using agg or window functions in delete statement?

[高增琦]

Hi,

In transformDeleteStmt:

qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);

These code set agg and window function status for delete query,
but there is no similar code in transformInsertStmt and
transformUpdateStmt.

Do we support using agg or window function in delete statement?
Or, this code should be removed?

Some history of these code:
1. 1996-7-9 "Postgres95 1.01 Distribution - Virgin Sources":
Introduce agg check for insert/update/delete.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/analyze.c;h=504e557abee8651596a9219dca069fcd20ecdaac;hb=d31084e9d1118b25fd16580d9d8c2924b5740dff

in transformDeleteStmt:
/* make sure we don't have aggregates in the where clause */
if (pstate->p_numAgg > 0)
parseCheckAggregates(pstate, qry);
in transformInsertStmt:
if (pstate->p_numAgg > 0)
finalizeAggregates(pstate, qry);
in transformUpdateStmt:
/* make sure we don't have aggregates in the where clause */
if (pstate->p_numAgg > 0)
parseCheckAggregates(pstate, qry);

2. 2006-6-21 "Disallow aggregate functions in UPDATE commands (unless
within a sub-SELECT)":
Change parseCheckAggregates to ereport in transformUpdateStmt.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=4e30d2b96f3f58176f74aa0061660f47ca0b6426;hp=566c9a0488df68c94effea9e3f59d82da930eb18;hb=1f5ca045a435bc6aa9c98d7296973925c5208add;hpb=e256bafaa2aec06dd9dc9493c4e600319ab11525

in transformUpdateStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
 errmsg("cannot use aggregate function in UPDATE")));

3. 2006-8-2 "Add support for multi-row VALUES clauses as part of INSERT
statements":
Change parseCheckAggregates in insert to ereport.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=4f7001b6f1ac4fdd9fe65cba835eeb2513f8ad06;hp=b086afe8ca25a8d91314d352b847c47f3a05d32e;hb=9caafda579f699b43fa4c89bf13a2331ef00611e;hpb=d307c428cbb7c426e40163d234d993e644bbcc6b

in transformInsertStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
 errmsg("cannot use aggregate function in VALUES")));

4. 2008-12-28 "Support window functions a la SQL:2008.":
Add window function related check for insert/update/delete.
(use the same style as agg)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=70688655cce18ac317faeafa2b51225f320fe493;hp=cdac02b71db69399e00b4a63eefe0d2f9f481ad0;hb=95b07bc7f5010233f52f9d11da74e2e5b653b0a7;hpb=38e9348282e9d078487147ba8a85aebec54e3a08

in transformDeleteStmt:
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
if (pstate->p_hasWindowFuncs)
parseCheckWindowFuncs(pstate, qry);
in transformInsertStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
 errmsg("cannot use aggregate function in VALUES"),
 parser_errposition(pstate,
locate_agg_of_level((Node *) qry, 0;
if (pstate->p_hasWindowFuncs)
ereport(ERROR,
(errcode(ERRCODE_WINDOWING_ERROR),
 errmsg("cannot use window function in VALUES"),
 parser_errposition(pstate,
locate_windowfunc((Node *) qry;
in transformUpdateStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
 errmsg("cannot use aggregate function in UPDATE"),
 parser_errposition(pstate,
locate_agg_of_level((Node *) qry, 0;
if (pstate->p_hasWindowFuncs)
ereport(ERROR,
(errcode(ERRCODE_WINDOWING_ERROR),
 errmsg("cannot use window function in UPDATE"),
 parser_errposition(pstate,
locate_windowfunc((Node *) qry;

5. 2012-8-10 "Centralize the logic for detecting misplaced aggregates,
window funcs, etc.":
Remove ereport in update/insert.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=6c3d89a14f6b1f19176864af4a0ea18eebd9f4bd;hp=93ef7246c51850aca1292e9d6388a0f97a0b;hb=eaccfded98a9c677d3a2e849c1747ec90e8596a6;hpb=b3055ab4fb5839a872bfe354b2b5ac31e6903ed6

Thanks


-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Declarative partitioning - another take

[高增琦]

Server crash(failed assertion) when two "insert" in one SQL:

Step to reproduce:
create table t(a int, b int) partition by range(a);
create table t_p1 partition of t for values from (1) to (100);
create table t_p2 partition of t for values from (100) to (200);
create table t_p3 partition of t for values from (200) to (300);

create table b(a int, b int);
with a(a,b) as(insert into t values(3, 3) returning a, b) insert into b
select * from a;

Please check it.

2017-01-04 14:11 GMT+08:00 Rajkumar Raghuwanshi <
rajkumar.raghuwan...@enterprisedb.com>:

> On Wed, Jan 4, 2017 at 10:37 AM, Amit Langote <
> langote_amit...@lab.ntt.co.jp> wrote:
>
>> On 2017/01/03 19:04, Rajkumar Raghuwanshi wrote:
>> > On Tue, Dec 27, 2016 at 3:24 PM, Amit Langote wrote:
>> >>
>> >> Attached patch should fix the same.
>> >
>> > I have applied attached patch, server crash for range is fixed, but
>> still
>> > getting crash for multi-level list partitioning insert.
>> >
>> > postgres=# CREATE TABLE test_ml_l (a int, b int, c varchar) PARTITION BY
>> > LIST(c);
>>
>> [ ... ]
>>
>> > postgres=# INSERT INTO test_ml_l SELECT i, i, to_char(i/50, 'FM')
>> FROM
>> > generate_series(0, 599, 2) i;
>> > server closed the connection unexpectedly
>> > This probably means the server terminated abnormally
>> > before or while processing the request.
>> > The connection to the server was lost. Attempting reset: Failed.
>>
>> Hm, that's odd.  I tried your new example, but didn't get the crash.
>>
>> Thanks,
>> Amit
>>
>
> Thanks, I have pulled latest sources from git, and then applied patch
> "fix-wrong-ecxt_scantuple-crash.patch", Not getting crash now, may be I
> have missed something last time.
>
>


-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] Declarative partitioning - another take

[高增琦]

Hi ,

I tried "COPY FROM"  in the git version. It inserts rows to wrong partition.

step to reproduce:
create table t(a int, b int) partition by range(a);
create table t_p1 partition of t for values from (1) to (100);
create table t_p2 partition of t for values from (100) to (200);
create table t_p3 partition of t for values from (200) to (300);
insert into t values(1,1);
insert into t values(101,101);
insert into t values(201,201);
copy (select * from t) to '/tmp/test2.txt';
copy t from '/tmp/test2.txt';
select * from t_p1;

result:
postgres=# select * from t_p1;
  a  |  b
-+-
   1 |   1
   1 |   1
 101 | 101
 201 | 201
(4 rows)

I think the argument "BulkInsertState" used in CopyFrom/heap_insert
is related to this problem. Please check it.

Thanks.





2016-12-27 17:30 GMT+08:00 Rajkumar Raghuwanshi <
rajkumar.raghuwan...@enterprisedb.com>:

> Hi Amit,
>
> I have pulled latest sources from git and tried to create multi-level
> partition,  getting a server crash, below are steps to reproduce. please
> check if it is reproducible in your machine also.
>
> postgres=# CREATE TABLE test_ml (a int, b int, c varchar) PARTITION BY
> RANGE(a);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p1 PARTITION OF test_ml FOR VALUES FROM
> (0) TO (250) PARTITION BY RANGE (b);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p1_p1 PARTITION OF test_ml_p1 FOR VALUES
> FROM (0) TO (100);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p1_p2 PARTITION OF test_ml_p1 FOR VALUES
> FROM (100) TO (250);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p2 PARTITION OF test_ml FOR VALUES FROM
> (250) TO (500) PARTITION BY RANGE (c);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p2_p1 PARTITION OF test_ml_p2 FOR VALUES
> FROM ('0250') TO ('0400');
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p2_p2 PARTITION OF test_ml_p2 FOR VALUES
> FROM ('0400') TO ('0500');
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p3 PARTITION OF test_ml FOR VALUES FROM
> (500) TO (600) PARTITION BY RANGE ((b + a));
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p3_p1 PARTITION OF test_ml_p3 FOR VALUES
> FROM (1000) TO (1100);
> CREATE TABLE
> postgres=# CREATE TABLE test_ml_p3_p2 PARTITION OF test_ml_p3 FOR VALUES
> FROM (1100) TO (1200);
> CREATE TABLE
> postgres=# INSERT INTO test_ml SELECT i, i, to_char(i, 'FM') FROM
> generate_series(0, 599, 2) i;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> Thanks & Regards,
> Rajkumar Raghuwanshi
> QMG, EnterpriseDB Corporation
>
>
>


-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] How can I build OSSP UUID support on Windows to avoid duplicate UUIDs?

[高增琦]

: (

still don't know how to build ossp-uuid on windows with MSVC.
Saito san's patch doesn't fix all errors during compiling...

I will try to combine this patch and the win32build on sf.net

Same questions again:
How was the dll file in the community binary built?
How to avoid duplicate UUIDs then?

thanks a lot.

2013-11-05 2:49 GMT+08:00 Christopher Browne :

> On Thu, Oct 31, 2013 at 3:42 PM, Robert Haas 
> wrote:
> > On Thu, Oct 31, 2013 at 2:44 PM, Garick Hamlin 
> wrote:
> >> I think using /dev/urandom directly would be surprising. At least it
> would
> >> have probably have taken me a while to figure out what was depleting the
> >> entropy pool here.
> >
> > Perhaps so; a bigger problem IMHO is that it's not portable. I think
> > the only way to solve this problem is to import (or have an option to
> > link with) a strong, sophisticated PRNG with much larger internal
> > state than pg_lrand48, which uses precisely 48 bits of internal state.
> > For this kind of thing, I'm fairly sure that we need something with
> > at least 128 bits of internal state (as wide as the random value we
> > want to generate) and I suspect it might be advantageous to have
> > something a whole lot wider, maybe a few kB.
>
> I mentioned the notion of building an entropy pool, into which one might
> add various sorts of random inputs, under separate cover...
>
> The last time I had need of a rather non-repeating RNG, I went with
> a Fibonacci-based one, namely Mersenne Twister...
>
> 
>
> The sample has 624 integers (presumably that means 624x32 bits) as
> its internal state. Apparently not terribly suitable for cryptographic
> purposes,
> but definitely highly non-repetitive, which is what we're notably
> worried about for UUIDs.
> --
> When confronted by a difficult problem, solve it by reducing it to the
> question, "How would the Lone Ranger handle this?"
>



-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] why do we need create tuplestore for each fetch?

[高增琦]

Thanks for you reply.

I found query without cursor is faster then query with server-side cursor
and several fetches.
But I have a large result set to retrieve from database. I have to choose
server-side cursor
to avoid out-of-memory problem.

When I try to debug the cursor and fetch, I found this unexpected behavior.
I think maybe
the tuplestore slows the cursor. (maybe I should do some profile later)

I want to change the code, but I am afraid there are important reasons for
the tuplestore.
Therefore, I post it to this list for help: why create tuplestore for each
fetch?

p.s. a large fetch may turn tuplestore to use buffer file, and slow the
performance very much.

On Mon, Dec 19, 2011 at 9:06 PM, Robert Haas robertmh...@gmail.com wrote:

 On Thu, Dec 15, 2011 at 8:30 AM, 高增琦 pgf...@gmail.com wrote:
  I found this several days ago when I try to debug a fetch of cursor.
  And I have sent a mail to this list, but no one reply...
  Maybe this is a very simple problem, please help me, thanks a lot...
 
  Here is the example:
  create table t (a int);
  insert into t values (1),(3),(5),(7),(9);
  insert into t select a+1 from t;
  begin;
  declare c cursor for select * from t order by a;
  fetch 3 in c;
  fetch 3 in c;
  fetch 3 in c;
 
  In 'PortalRun', a fetch stmt will be treated with PORTAL_UTIL_SELECT,
  and then a tuplestore will be created in 'FillPortalStore' in the
  fetch stmt's portal.
 
  In 'FillPortalStore', all result will be store at that tuplestore,
  Then, go back to 'PortalRun'; next,  'PortalRunSelect' will send this
  results to client...
 
  My problem is: why do we need create that tuplestore as an
  middle storeage? why do not we just send these result to clent
  at the first time?

 Good question.  I wouldn't expect it to matter very much for a
 three-row fetch, but maybe it does for larger ones?  What is your
 motivation for investigating this?

 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company




-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

[HACKERS] why do we need create tuplestore for each fetch?

[高增琦]

Hi everyone,

I found this several days ago when I try to debug a fetch of cursor.
And I have sent a mail to this list, but no one reply...
Maybe this is a very simple problem, please help me, thanks a lot...

Here is the example:
create table t (a int);
insert into t values (1),(3),(5),(7),(9);
insert into t select a+1 from t;
begin;
declare c cursor for select * from t order by a;
fetch 3 in c;
fetch 3 in c;
fetch 3 in c;

In 'PortalRun', a fetch stmt will be treated with PORTAL_UTIL_SELECT,
and then a tuplestore will be created in 'FillPortalStore' in the
fetch stmt's portal.

In 'FillPortalStore', all result will be store at that tuplestore,
Then, go back to 'PortalRun'; next,  'PortalRunSelect' will send this
results to client...

My problem is: why do we need create that tuplestore as an
middle storeage? why do not we just send these result to clent
at the first time?

Thank you very much.

-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

[HACKERS] Why create tuplestore for each fetch?

[高增琦]

Hi,

I am reading code about cursor and fetch ...
Here is a test:

create table t (a int);
insert into t values (1),(3),(5),(7),(9);
insert into t select a+1 from t;
begin;
declare c cursor for select * from t order by a;
fetch 3 in c;
fetch 3 in c;
fetch 3 in c;

In func PortalRun, FillPortalStore(portal, isTopLevel) will create a
tuplestore for each query...
Why create tuplestore for each fetch?

-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] crash-safe visibility map, take four

[高增琦]

On Wed, Mar 30, 2011 at 8:52 PM, Heikki Linnakangas 
heikki.linnakan...@enterprisedb.com wrote:

 On 30.03.2011 06:24, 高增琦 wrote:

 Should we do full-page write for visibilitymap all the time?
 Now, when clear visiblitymap, there is no full-page write for vm
 since we don't save buffer info in insert/update/delete's log.

 The full-page write is used to protect pages from disk failure. Without
 it,
 1) set vm: the vm bits that should be set to 1 may still be 0
 2) clear vm: the vm bits that should be set to 0 may still be 1
 Are these true? Or the page is totally unpredictable?


 Not quite. The WAL replay will set or clear vm bits, regardless of full
 page writes. Full page writes protect from torn pages, ie. the problem where
 some operations on a page have made it to disk while others have not. That's
 not a problem for VM pages, as each bit on the page can be set or cleared
 individually. But for something like a heap page where you have an offset in
 the beginning of the page that points to the tuple elsewhere on the page,
 you have to ensure that they stay in sync, even if you don't otherwise care
 if the update makes it to disk or not.


Consider a example:
1. delete on two pages, emits two log (1, page1, vm_clear_1), (2, page2,
vm_clear_2)
2. vm_clear_1 and vm_clear_2 on same vm page
3. checkpoint, and vm page get torned, vm_clear_2 was lost
4. delete another page, emits one log (3, page1, vm_clear_3), vm_clear_3
still on that vm page
5. power down
6. startup, redo will replay all change after checkpoint, but vm_clear_2
will never be cleared
Am I right?



  Another question:
 To address the problem in
 http://archives.postgresql.org/pgsql-hackers/2010-02/msg02097.php
 , should we just clear the vm before the log of insert/update/delete?
 This may reduce the performance, is there another solution?


 Yeah, that's a straightforward way to fix it. I don't think the performance
 hit will be too bad. But we need to be careful not to hold locks while doing
 I/O, which might require some rearrangement of the code. We might want to do
 a similar dance that we do in vacuum, and call visibilitymap_pin first, then
 lock and update the heap page, and then set the VM bit while holding the
 lock on the heap page.


Do you mean we should lock the heap page first, then get the blocknumber,
then release heap page,
then pin the vm's page, then lock both heap page and vm page?
As Robert Haas said, when lock the heap page again, may there isnot enough
free space on it.
Is there a way just stop the checkpoint for a while?

Thanks.
GaoZengqi

Re: [HACKERS] crash-safe visibility map, take four

[高增琦]

2011/3/30 Robert Haas robertmh...@gmail.com

 Maybe we could check PD_ALL_VISIBLE before
 taking the buffer lock - if it appears to be set, then we pin the
 visibility map page before taking the buffer lock.  Otherwise, we take
 the buffer lock at once.  Either way, once we have the lock, we
 recheck the bit.  Only if it's set and we haven't got a pin do we need
 to do the drop-lock-pin-reacquire-lock dance.  Is that at all
 sensible?


But only lock can make sure the page has enough free space.
If we try the drop-lock-...-lock dance, we may fall into a dead loop.

-- 
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com

Re: [HACKERS] crash-safe visibility map, take four

[高增琦]

Hi,

Should we do full-page write for visibilitymap all the time?
Now, when clear visiblitymap, there is no full-page write for vm
since we don't save buffer info in insert/update/delete's log.

The full-page write is used to protect pages from disk failure. Without it,
1) set vm: the vm bits that should be set to 1 may still be 0
2) clear vm: the vm bits that should be set to 0 may still be 1
Are these true? Or the page is totally unpredictable?

Another question:
To address the problem in
http://archives.postgresql.org/pgsql-hackers/2010-02/msg02097.php
, should we just clear the vm before the log of insert/update/delete?
This may reduce the performance, is there another solution?

Thanks.

--
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com


On Fri, Mar 25, 2011 at 6:05 AM, Simon Riggs si...@2ndquadrant.com wrote:

 On Wed, Mar 23, 2011 at 6:16 AM, Jesper Krogh jes...@krogh.cc wrote:
  On 2011-03-22 21:43, Robert Haas wrote:
 
  I took a crack at implementing the first approach described above,
  which seems to be by far the simplest idea we've come up with to date.
   Patch attached.  It doesn't seem to be that complicated, which could
  mean either that it's not that complicated or that I'm missing
  something.  Feel free to point and snicker in the latter case.
 
  Looks simple, but there is now benefit...

 Your tests and discussion remind me that I haven't yet seen any tests
 that show that index-only scans would be useful for performance.

 Everyone just seems to be assuming that they make a huge difference,
 and that the difference is practically realisable in a common
 workload.

 Perhaps that's already been done and I just didn't notice?

 --
  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] visibility map

[高增琦]

Can we just log the change of VM in log_heap_clean() for redo?
Thanks

--
GaoZengqi
pgf...@gmail.com
zengqi...@gmail.com


On Tue, Nov 23, 2010 at 3:24 AM, Robert Haas robertmh...@gmail.com wrote:

 On Mon, Jun 14, 2010 at 1:19 AM, Heikki Linnakangas
 heikki.linnakan...@enterprisedb.com wrote:
  I *think* that the answer to this parenthesized question is no.
  When we vacuum a page, we set the LSN on both the heap page and the
  visibility map page.  Therefore, neither of them can get written to
  disk until the WAL record is flushed, but they could get flushed in
  either order.  So the visibility map page could get flushed before the
  heap page, as the non-parenthesized portion of the comment indicates.
 
  Right.
 
  However, at least in theory, it seems like we could fix this up during
  redo.
 
  Setting a bit in the visibility map is currently not WAL-logged, but yes
  once we add WAL-logging, that's straightforward to fix.

 Eh, so.  Suppose - for the sake of argument - we do the following:

 1. Allocate an additional infomask(2) bit that means xmin is frozen,
 no need to call XidInMVCCSnapshot().  When we freeze a tuple, we set
 this bit in lieu of overwriting xmin.  Note that freezing pages is
 already WAL-logged, so redo is possible.

 2. Modify VACUUM so that, when the page is observed to be all-visible,
 it will freeze all tuples on the page, set PD_ALL_VISIBLE, and set the
 visibility map bit, writing a single XLOG record for the whole
 operation (possibly piggybacking on XLOG_HEAP2_CLEAN if the same
 vacuum already removed tuples; otherwise and/or when no tuples were
 removed writing XLOG_HEAP2_FREEZE or some new record type).  This
 loses no forensic information because of (1).  (If the page is NOT
 observed to be all-visible, we freeze individual tuples only when they
 hit the current age thresholds.)

 Setting the visibility map bit is now crash-safe.

 Please poke holes.

 --
 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