[GENERAL] data type
Hi, I am writing some function in postgres pl/sql. My function is of type St_ABC((select obj_geom from XYZ),(select boundary_geom from boundary)) I have table XYZ with 20,000 tuples and in boundary, i have only one geometry. In postgres, ST_intersects(obj_geom, boundary_geom) checks each obj_geom with boundary_geom and returns true/false. It returns true/false 20,000 times I want to write function that return only one true/false according to my calculation. So, create or replace function ST_ABC(?, geometry) returns boolean Which type of data type will be used in above function (in place of ?) that can collect more than one row(20,000) ? Thanks Raj
[GENERAL] Composite index structure
Hi all, I want to construct an "Composite Index Structure" i.e. a combination of gist and btree. What i am thinking is that first creating a Rtree structure that is pointing to another Btree structure. For example, Suppose i want to find vehicles between 2 to 4 pm on 14/2/2011 on X road. I am thinking of creating rtree structure for road network and then btree for time. For reaching X road i use Rtree, and from there btree begin i.e. leaf node of rtree contains the pointer to root node of btree ( in this way i have all time belonging to X road) My question is that how to implement this composite index structure in postgres? Let us suppose, if i create mygist index, then i have to write my own operator class? or can i use gist index as it is and btree tree as it is. I mean their operator class and their gist methods but how to establish linkage between them? Any idea ?? Thanks Raj
[GENERAL] Understanding Datum
Hi, I am understanding the postgres code. In code, i just want to see what are values that are passing through the variables? Can you please tell me if the variable is of type Datum, then how to print its value? Because i dont the variable v type. And also what the structure of Datum? Thanks, Raj
Re: [GENERAL] Understanding Datum
Hi,
In postgres, typedef uintptr_t Datum
Datum is getting value from PG_GETARG_POINTER(1);
But, now problem is how would i know the type of PG_GETARG_POINTER(1)
(postgres internally pass this argument) to figure out datum type?
Can you tell detailed structure of Datum, so i can print the value?? How to
find out what type of pointer argument is PG_GETARG_POINTER(1)??
Thanks,
Nirmesh
On Wed, Mar 23, 2011 at 11:40 PM, Radosław Smogura wrote:
> Nick Raj Wednesday 23 March 2011 18:45:41
> > Hi,
> > I am understanding the postgres code. In code, i just want to see what
> are
> > values that are passing through the variables?
> > Can you please tell me if the variable is of type Datum, then how to
> print
> > its value? Because i dont the variable v type.
> >
> > And also what the structure of Datum?
> >
> > Thanks,
> > Raj
>
> The structure is explained in one of headers, generally Datum is pointer.
> It
> points to memory containing at first four bytes integer describing size of
> data in datum (use macro to extract this), and then, it's followed by bytes
> containing data.
>
> Actually almost each object is represented by structure like this
> struct something {
>int4 size; //Required
> // Here put what you want
> }
> see headers.
>
> If you want to present data from datum you need to 1) check what type of
> data
> datum has (datum doesn't contain this) 2) Find datum representation for
> this
> type.
>
> Regards,
> Radek
>
Re: [GENERAL] Understanding Datum
If Datum contains only the value (not having type specific info.), then
Suppose i want to print the Datum V value (already defined in postgres)
then printf("%??", V);
Because V is assigned by PG_GETARG_POINTER(1);
I don't having the information of type Datum.
How to print the value of Datum in postgres?
On Thu, Mar 24, 2011 at 2:35 AM, Tom Lane wrote:
> Nick Raj writes:
> > In postgres, typedef uintptr_t Datum
> > Datum is getting value from PG_GETARG_POINTER(1);
> > But, now problem is how would i know the type of PG_GETARG_POINTER(1)
> > (postgres internally pass this argument) to figure out datum type?
>
> Datum does not carry any type information, only a value. Functions are
> typically coded to know their input types a priori. If you want to
> write code that is not type-specific then you'd better be passing around
> type OIDs as well as values.
>
>regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] Integrating New Data Type
Hi all, I have defined a new data type. I have defined in and out function for that data type. But i want to know how to integrate this data type with postgres (how postgres compile my code or know my datatype) ? Thanks, Nirmesh
[GENERAL] Global Variables in plpgsql
Hi, Can anyone know how to define global variable in plpgsql? Thanks Regards, Raj
[GENERAL] Typecast
Hi, Can anybody tell me how to typecast data type Point into Datum? Thanks Nick
Re: [GENERAL] Typecast
Thanks dude On Mon, Apr 18, 2011 at 2:25 PM, Chetan Suttraway < chetan.suttra...@enterprisedb.com> wrote: > > > On Fri, Apr 15, 2011 at 10:29 PM, Nick Raj wrote: > >> Hi, >> Can anybody tell me how to typecast data type Point into Datum? >> >> Thanks >> Nick >> > > Assuming you are referring to c-code, > > Point somepoint; > Datum result; > > result = PointPGetDatum(&somepoint) > > You can also checkout src/backend/utils/adt/geo_ops.c which has code for > conversion between > datum and point and vice-versa. > > > > -- > Regards, > Chetan Suttraway > EnterpriseDB <http://www.enterprisedb.com/>, The Enterprise > PostgreSQL<http://www.enterprisedb.com/> > company. > > > >
[GENERAL] Defining input function for new datatype
Hi,
I am defining a new data type called mpoint
i.e.
typedef struct mpoint
{
Point p;
Timestamp t;
} mpoint;
For defining input/output function
1 Datum mpoint_in(PG_FUNCTION_ARGS)
2 {
3
4mpoint *result;
5char *pnt=(char *)malloc (sizeof (20));
6char *ts=(char *)malloc (sizeof (20));
7result= (mpoint *) palloc(sizeof(mpoint));
8char *st = PG_GETARG_CSTRING(0);
9mpoint_decode(st,pnt,ts);
// st breaks down into pnt that corresponds to Point and ts corresponds to
Timestamp
10
11 result->p = point_in(PointerGetDatum(pnt));//
point_in (input function for point that assigns x, y into point)
12 result-> t = timestamp_in(PointerGetDatum(ts)); // similar
for timestamp
13
14 PG_RETURN_MPOINT_P(result);
15 }
line no 11 warning: passing argument 1 of ‘point_in’ makes pointer from
integer without a cast
../../../include/utils/geo_decls.h:191: note: expected
‘FunctionCallInfo’ but argument is of type ‘unsigned int’
line no 11 error: incompatible types when assigning to type ‘Point’ from
type ‘Datum’
line no 12 warning: passing argument 1 of ‘timestamp_in’ makes pointer from
integer without a cast
../../../include/utils/timestamp.h:205: note: expected
‘FunctionCallInfo’ but argument is of type ‘unsigned int’
Can anybody figure out what kind of mistake i am doing?
Also, why it got related to 'FunctionCallInfo' ?
Thanks
Nick
[GENERAL] Debug Contrib/cube code
Hi, I am using postgresql-8.4.6. I want to debug the contrib/cube code. Can we able to debug that cube code? Because there is no .configure file to enable debug. Is there is any way to change make file to enable debug? Thanks Nick
Re: [GENERAL] Debug Contrib/cube code
Hi, I am able to debug postgres by --enable-debug but breakpoint in cube is not working. Can you more elaborate on your second point? Even i also tried to change makefile of contrib/cube by -g(debug) but it gives error. Any idea is welcome. ps: sorry for late reply Thanks On Thu, May 12, 2011 at 6:42 AM, Joshua Tolley wrote: > On Fri, May 06, 2011 at 10:43:23AM +0530, Nick Raj wrote: > > Hi, > > I am using postgresql-8.4.6. I want to debug the contrib/cube code. Can > we > > able to debug that cube code? Because there is no .configure file to > > enable debug. Is there is any way to change make file to enable debug? > > If your postgres build uses --enable-debug, cube should use it as well. It > determines this either through pg_config, or by referring to makefiles in > the > postgres source directory contrib/cube lives in. > > -- > Joshua Tolley / eggyknap > End Point Corporation > http://www.endpoint.com > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAk3LNAsACgkQRiRfCGf1UMMtUQCeJHmpy5PrHX8UG48sYe3EHlmQ > SdEAnA9WCw5vFcklpCrJYDVc/yqo9FtH > =FKXd > -END PGP SIGNATURE- > >
[GENERAL] Toast datum
Hi, #define DatumGetNDBOX(x)((NDBOX*)DatumGetPointer(x)) #define PG_GETARG_NDBOX(x)DatumGetNDBOX( PG_DETOAST_DATUM(PG_GETARG_DATUM(x)) ) Now i have to define #define NDBOXGetDatum(x) ()PointerGetDatum(x) Is there any need to replace this ?? with some toastable thing or is it sufficient? If some toastable require then, what is that macro? Thanks
[GENERAL] arguments are not toastable
Hi,
I have defined some function and also used NDBOX structure that having
variable length.
typedef struct NDBOX
{
int32vl_len_;/* varlena length */
unsigned int dim;
doublex[1];
} NDBOX;
When i called my function, it gives NDBOX to be null
On debugging, i found out ,FunctionInvokeCall invokes fmgr_oldstyle
function, for getting argument
if (fnextra->arg_toastable[i])//this returns false, not able to get
arguments
fcinfo->arg[i] =
PointerGetDatum(PG_DETOAST_DATUM(fcinfo->arg[i]));
}
Why it is going into fmgr_oldstyle if the arguments is not toasted? "How to
get arguments toastable??" and even my table pg_class.reltoastrelid entry is
zero.
Thanks
[GENERAL] disable seqscan
Hi, I have build an index. When, i execute the query, it gives the result by sequential scan, not by using my index. I have already run vacuum analyze to collect some statistics regarding table. May be sequential scan is giving faster execution time than my indexing. But i want to know how much time it would take in my indexing. For that, i have set enable_seqscan=off in postgresql.conf. But it still going through sequential scan. Even i tried to set for a particular session, by "set enable_seqscan=off" on psql terminal. It again going by sequential scan. Does any one having an idea to force postgres to use index scan? Thanks Nick
Re: [GENERAL] disable seqscan
On Mon, May 23, 2011 at 5:44 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Andrew Sullivan wrote:
>
> > On Mon, May 23, 2011 at 05:31:04PM +0530, Nick Raj wrote:
> > > Hi,
> > > I have build an index. When, i execute the query, it gives the result
> by
> > > sequential scan, not by using my index.
> >
> > > For that, i have set enable_seqscan=off in postgresql.conf. But it
> still
> > > going through sequential scan.
> >
> > It sounds like your index can't actually be used to satisfy your
> > query. Without seeing the table definition, index definition, and
> > query, however, it's pretty hard to give you a real answer.
>
> ... and the output produced by
>
> explain analyse
>
> Explain analyze of my query
> explain analyze select * from vehicle_stindex where
> ndpoint_overlap('(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13
> 11:11:11)',stpoint);
> QUERY
> PLAN
>
> --
> Seq Scan on vehicle_stindex (cost=100.00..1050870.86
> rows=698823 width=66) (actual time=3285.106..3285.106 rows=0 loops=1)
>Filter: ndpoint_overlap('(116.40,39.30,2008-02-11
> 11:11:11+05:30),(117.80,39.98,2008-02-13 11:11:11+05:30)'::ndpoint,
> stpoint)
> Total runtime: 3285.153 ms
> (3 rows)
>
>
> Table Defination
>
> Table "public.vehicle_stindex"
> Column | Type | Modifiers
> -+-+---
> regno | text|
> stpoint | ndpoint |
> Indexes:
> "stindex" gist (stpoint)
>
> It has 2099192 tuples.
>
>
> Index defination
> create index stindex on vehicle_stindex using gist(stpoint).
>
>
> I have defined a datatype called ndpoint. It works same as contrib/cube
> code (cube datatype).
> Query is working fine. I mean no error from query or my datatype. All are
> giving right result.
>
If anything more to mention, then tell me
Nick
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] disable seqscan
On Mon, May 23, 2011 at 7:35 PM, Tom Lane wrote:
> Nick Raj writes:
> >> Andrew Sullivan wrote:
> >>> It sounds like your index can't actually be used to satisfy your
> >>> query. Without seeing the table definition, index definition, and
> >>> query, however, it's pretty hard to give you a real answer.
>
> >> explain analyze select * from vehicle_stindex where
> >> ndpoint_overlap('(116.4,39.3,2008/02/11
> 11:11:11),(117.8,39.98,2008/02/13
> >> 11:11:11)',stpoint);
>
> >> I have defined a datatype called ndpoint. It works same as contrib/cube
> >> code (cube datatype).
>
> Indexes can only be used with WHERE conditions that are of the form
>indexed_column operator some_expression
> where the operator is one of those belonging to the index's operator
> class. You haven't told us what operators you put into the operator
> class for this new data type, but in any case the function
> ndpoint_overlap is not one of them.
>
>regards, tom lane
>
CREATE OR REPLACE FUNCTION ndpoint_overlap(ndpoint, ndpoint) RETURNS
bool AS '$libdir/ndpoint','ndpoint_overlap' LANGUAGE CIMMUTABLE STRICT;
CREATE OPERATOR && (
LEFTARG = ndpoint, RIGHTARG = ndpoint, PROCEDURE = ndpoint_overlap,
COMMUTATOR = '&&',
RESTRICT = areasel, JOIN = areajoinsel
);
CREATE OPERATOR CLASS gist_ndpoint_ops
DEFAULT FOR TYPE ndpoint USING gist AS
OPERATOR3&&, ..
One think i am not able to understand is, if i use ndpoint_overlap method it
is going for seq. scan every time but if i use && operator it is using index
scan. Why it is so?
Look below for their explain analyze statement
1. explain analyze select * from vehicle_stindex where
ndpoint_overlap('(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13
11:11:11)',stpoint);
QUERY
PLAN
--
Seq Scan on vehicle_stindex (cost=100.00..1050870.86
rows=698823 width=66) (actual time=599.300..599.300 rows=0 loops=1)
Filter: ndpoint_overlap('(116.40,39.30,2008-02-11
11:11:11+05:30),(117.80,39.98,2008-02-13 11:11:11+05:30)'::ndpoint,
stpoint)
Total runtime: 599.337 ms
(3 rows)
2. explain analyze select * from vehicle_stindex where
'(116.4,39.3,2008/02/11 11:11:11),(117.8,39.98,2008/02/13 11:11:11)' &&
stpoint;
QUERY
PLAN
-
Index Scan using stindex on vehicle_stindex (cost=0.00..58542.00
rows=10482 width=66) (actual time=0.866..0.866 rows=0 loops=1)
Index Cond: ('(116.40,39.30,2008-02-11
11:11:11+05:30),(117.80,39.98,2008-02-13 11:11:11+05:30)'::ndpoint
&& stpoint)
Total runtime: 0.916 ms
(3 rows)
Why these is happening?
[GENERAL] Index Size
Hi,
Cube code provided by postgres contrib folder. It uses the NDBOX structure.
On creating index, it's size increase at a high rate.
On inserting some tuple and creating indexes its behaviour is shown below.
1. When there is only one tuple
select pg_size_pretty(pg_relation_
size('cubtest')); //Table size without index
pg_size_pretty
8192 bytes
(1 row)
select pg_size_pretty(pg_total_relation_size('cubtest')); //Table size with
index
pg_size_pretty
16 kB
(1 row)
i.e. Index size in nearly 8kB
2. When tuples are 20,000
Table Size without index - 1.6 MB
Table Size with index - 11 MB
i.e. Index size is nearly 9.4 MB
3. When tuples are 5 lakh
Table Size without index - 40 MB
Table Size with index - 2117 MB
i.e. Index size is nearly 2077 MB ~ 2GB
It is taking nearly 20-25 min for creating index for 5 lakh tuples.
Can some one tell me why index is becoming so large?
How to compress or reduce its size?
Thanks
Nick
Re: [GENERAL] Index Size
On Tue, May 31, 2011 at 8:50 AM, Tom Lane wrote: > Craig Ringer writes: > > On 05/30/2011 08:53 PM, Nick Raj wrote: > >> Cube code provided by postgres contrib folder. It uses the NDBOX > structure. > >> On creating index, it's size increase at a high rate. > > > [snip] > > >> Can some one tell me why index is becoming so large? > >> How to compress or reduce its size? > > > It'd help if you included some more details: > > > - Your PostgreSQL version > > In particular, I wonder whether his version contains this fix: > > Author: Robert Haas > Branch: master [4fa0a23c7] 2010-11-14 21:27:34 -0500 > Branch: REL9_0_STABLE Release: REL9_0_2 [e6b380251] 2010-11-14 21:27:34 > -0500 > Branch: REL8_4_STABLE Release: REL8_4_6 [2519b8268] 2010-11-14 21:27:34 > -0500 > Branch: REL8_3_STABLE Release: REL8_3_13 [d589e4070] 2010-11-14 21:27:34 > -0500 > Branch: REL8_2_STABLE Release: REL8_2_19 [e642ca767] 2010-11-14 21:27:34 > -0500 > Branch: REL8_1_STABLE Release: REL8_1_23 [0e27a7319] 2010-11-14 21:27:34 > -0500 > >Fix bug in cube picksplit algorithm. > >Alexander Korotkov > > > Is this bug fixed? postgresql 9.0 having this bug or not? Thanks
[GENERAL] Pl/pgsql function
I am implementing some pl/pgsql functions.
Is there any way to change the input
for example- I got some value by $1. I want to modify this value (means
split that value), Can we do this and how?
Second thing,
Suppose i defined a function test as
select test('geom',the_geom,time) from tablename
.
Inside body,
How can i get the tablename inside the body (because i haven't pass table
name to function)
..
end
