[GENERAL] How to convert a double value to a numeric datum type in pgsql?
Hi all, I'm talking about the source code of pgsql and I want to know how the typing system works in pgsql. A few functions can help us do the type conversion, for example: Int32GetDatum -- convert a integer to a datum Float8GetDatum -- convert double to a datum cstring_to_text -- convert a string to a text ... but there are a lot of types in pgsql,how to choose the corresponding function? or is there a more automatic way to do the type conversion(raw chars to a datum, suppose I have the corresponding Form_pg_type instance)? I ask this question because I don't know how to convert a double value to a numeric datum(the field's type is numeric(10,2)), pg_type shows that numeric's typlen is -1 whose length is variable thus Float8GetDatum is not working..
[GENERAL] What is the correct way to get the content of a varchar field in pgsql's source code
Hi all,
I've learnt from the source code to open a table and scan it, like this:
Relation qma = try_relation_open(qmappersta, AccessShareLock);
if(qma!=NULL){
HeapScanDesc scan= heap_beginscan(qma,SnapshotNow,0,NULL);
HeapTuple tup;
TupleDesc tupDesc= RelationGetDescr(qma);
Datum *values;
bool *nulls;
int value1;
char value2;
while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL){
heap_deform_tuple(tup,tupDesc,values, nulls);
/* int values can be retrieved easily */
value1=values[0];
/* how to retrieve the content of a varchar field here? */
/* What I've tried: */
value2=VARDATA_ANY(values[2]);
/* But value2 seems to be incorrect
if the original data is abcd, then I will get abcd2 here(in
most cases, a 2 is appended to the data...), I'm wondering how to get
the data properly...
I've also tried VARDATA macro and encountered with the same
problem.
*/
}
}
Thanks!
[GENERAL] Breakpoints are not triggered in analyze.c (debugging Postgresql in Visual studio)
Hi all, I've set up a developing environment on my windows using Visual Studio 2012, everything works fine, except that the breakpoints set in analyze.c are not triggered in debug mode (breakpoints in main.c and some initialization code worked well), and I'm sure that line has been executed since my own messages have been printed on the console. Does anyone have any experience debugging postgresql in Visual Studio? Thanks!
[GENERAL] How are pg_operator and pg_type related with each other?
Hi all, I'm studying pg_statistic table and I find that column staop is related to pg_operator, and different data types relate to different staop, but I don't know where pgsql stores the mapping between pg_type and pg_operator, does anyone have any idea about it? thanks!
Re: [GENERAL] How are pg_operator and pg_type related with each other?
Hi Amit I understand, I've read the source code of analyze.c and implemented a java version. Stakind1(most common values) indicates = operator and stakind2(histogram) indicates operator by default, I'm wondering where I can find the corresponding operatorID of eq/lt for a specific data type. For example, = operator for the type varchar is texteq and operator for varchar is text_lt = operator for the type int4 is int4eq and operator for int4 is int4lt etc. And another question is that how to update or insert a column with type of anyarray, since I want to mock the statistic data of tables, the type of stavalues in pg_statistic is anyarray, is there any way to manually modify that column, by some kind of function or hook? If I ran the query like: UPDATE pg_statistic SET stavalues2=array[1,2,8] WHERE ... Then I will get error 42804 indicates that the expected type is anyarry but text[] is found in the query. Thanks very much! 2014/1/15 Amit Langote amitlangot...@gmail.com On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 ygnhz...@gmail.com wrote: Hi all, I'm studying pg_statistic table and I find that column staop is related to pg_operator, and different data types relate to different staop, but I don't know where pgsql stores the mapping between pg_type and pg_operator, does anyone have any idea about it? thanks! Rather, different kinds of statistics are related to different operators. So, staop2 would refer to an operator suitable/applicable for the statistics of kind stakind2. For example stakind2 for some attribute could be value 3 which refers to statistic kind histogram. In this case, staop2 for the same attribute could refer to operator, say, because this particular operator could benefit from histogram distribution of values. (off course, may be overloaded for different types; but point to take from this is that any uses the statistic called histogram.) -- Amit Langote
Re: [GENERAL] How to specify/mock the statistic data of tables in PostgreSQL
I see, thanks. I'm looking into the source code of statistic part now, and I'm a little confused about the column staop presented in table pg_statistic, in the pg_statisitc.h, the comment says: /* * To allow keeping statistics on different kinds of datatypes, * we do not hard-wire any particular meaning for the remaining * statistical fields. Instead, we provide several slots in which * statistical data can be placed. Each slot includes: * kind integer code identifying kind of data (see below) * op OID of associated operator, if needed * numbers float4 array (for statistical values) * values anyarray (for representations of data values) * The ID and operator fields are never NULL; they are zeroes in an * unused slot. The numbers and values fields are NULL in an unused * slot, and might also be NULL in a used slot if the slot kind has * no need for one or the other. * */ And, //line 194 : In a most common values slot, staop is the OID of the = operator used to decide whether values are the same or not. //line 206 : A histogram slot describes the distribution of scalar data. staop is the OID of the operator that describes the sort ordering. I don't understand the function of staop here, how is it used in optimizer, is there any example ? thanks! 2014/1/10 Amit Langote amitlangot...@gmail.com On Fri, Jan 10, 2014 at 11:19 PM, Atri Sharma atri.j...@gmail.com wrote: Sent from my iPad On 10-Jan-2014, at 19:42, ygnhzeus ygnhz...@gmail.com wrote: Thanks for your reply. So correlation is not related to the calculation of selectivity right? If I force PostgreSQL not to optimize the join order (by setting join_collapse_limit and from_collapse_limit to 1) , is there any other factor that may affect the structure of execution plan regardless of the data access method. 2014-01-10 ygnhzeus 发件人：Amit Langote amitlangot...@gmail.com 发送时间：2014-01-10 22:00 主题：Re: [GENERAL] How to specify/mock the statistic data of tables in PostgreSQL 收件人：ygnhzeusygnhz...@gmail.com 抄送：pgsql-generalpgsql-general@postgresql.org AFAIK, correlation is involved in calculation of the costs that are used for deciding the type of access.If the correlation is low, index scan can lead to quite some random reads, hence leading to higher costs. Ah, I forgot to mention this point about how planner uses correlation for access method selection. And selectivity is a function of statistical distribution of column values described in pg_statistic by histograms, most common values (with their occurrence frequencies), number of distinct values, etc. It has nothing to do with correlation. -- Amit Langote
