[HACKERS] Writing values to relation using bytearray ...

[Kedar Potdar]

Hi,

I am trying to write values of different types to relation using following
code.

if(typbyval)
{
min_ba = (bytea *) palloc(len+1+VARHDRSZ);
memcpy(VARDATA(min_ba), &min_datum, len);
SET_VARSIZE(min_ba, len+VARHDRSZ);
VARDATA(min_ba)[len] = '\0';
values[Anum_pg_partition_minval-1]= (Datum)min_ba ;

max_ba = (bytea *) palloc(len+1+VARHDRSZ);
memcpy(VARDATA(max_ba), &max_datum, len);
SET_VARSIZE(max_ba, len+VARHDRSZ);
VARDATA(max_ba)[len] = '\0';
values[Anum_pg_partition_maxval-1]=(Datum)max_ba;
}
else
{
values[Anum_pg_partition_minval-1]=min_datum;
values[Anum_pg_partition_maxval-1]=max_datum;
}

These values are then written to relation using heap_form_tuple() and
simple_heap_insert() functions.

I am using following code to read the values from relation.

part_attr = heap_getattr (pg_parttup,Anum_pg_partition_maxval,
pg_partrel->rd_att,&isnull);
if ( typbyval )
{
short_datum = 0;
memcpy(&short_datum, VARDATA_ANY(part_attr), len);
part_attr = short_datum;
}
else if (len != -1 )
part_attr = (Datum)VARDATA_ANY(part_attr);


The aforementioned code works fine for types like int, data, text and I can
read values from the relation correctly. The problem arises for type
"float8" which is not "by value" type and it has fixed length (8) where I
can't read the values written to relation correctly.

Am i missing something here?

Thanking you in anticipation.

With warm regards,
--
Kedar.

Re: [HACKERS] Writing values to relation using bytearray ...

[Kedar Potdar]

Thanks Greg, for showing interest.

The problem here is I need to store values of different types into bytearray
column of relation.

On Fri, Mar 6, 2009 at 4:33 PM, Greg Stark  wrote:

> On Fri, Mar 6, 2009 at 10:03 AM, Kedar Potdar 
> wrote:
> >
> > The aforementioned code works fine for types like int, data, text and I
> can
> > read values from the relation correctly. The problem arises for type
> > "float8" which is not "by value" type and it has fixed length (8) where I
> > can't read the values written to relation correctly.
> >
> > Am i missing something here?
>
> Well as you've correctly diagnosed, not all byvalue data types are
> variable-length.
>
> This code all seems unnecessary. The whole point of heap_form_datum
> and heap_deform_datum/heap_getattr is that you don't have to worry
> about all this. there are also functions like datumCopy() but you
> probably don't even need them here, you can just put the datums you
> have handy into the values[] array and pass that to heap_form_tuple --
> it'll copy them into the resulting tuple so once you've formed the
> tuple you don't have to worry about the lifetime of the original
> datums. heap_deform_tuple() and heap_getattr can return pointers into
> the original tuple so you do have to be careful to copy them if you
> need them to survive the original tuple -- but you might not be
> anyways.
>
>
> --
> greg
>

Re: [HACKERS] Writing values to relation using bytearray ...

[Kedar Potdar]

Thanks Tom for your interest.

I could find a workaround for the issue wherein the value of type
which is not stored "by value" and has fixed data length, is being
stored in string format to the relation.

While retrieving from relation, this value is converted by using
"coerce_to_specific_type()"
 to its native type datum represation as required.

This is a bit of overhead and I'd like to find more efficient solution
and will look pg_statistics data store.

Regards,
-
Kedar

- sent from a mobile device.

On 3/6/09, Tom Lane  wrote:
> Kedar Potdar  writes:
>> The problem here is I need to store values of different types into
>> bytearray
>> column of relation.
>
> Perhaps you should study the ANALYZE code.  AFAICS your requirements are
> not different from those of the pg_statistic data store.  You should do
> things the same way they are done there, if only to reduce the surprise
> factor for readers of the code.
>
>   regards, tom lane
>

-- 
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] Partitioning feature ...

[Kedar Potdar]

Hi Nikhil,

Update operation is performed as a combination of 'delete' and 'insert'.

In Update trigger, the row is deleted from relation according to it's
'ctid'. A look-up on system catalog for partitions is performed to identify
the target table by evaluating values of partition-key attributes, of the
given row. The constraints of this target table are evaluated for this new
row and if found valid, the row is inserted.

Regards,
--
Kedar.



On Mon, Mar 23, 2009 at 5:09 PM, Nikhil Sontakke <
nikhil.sonta...@enterprisedb.com> wrote:

> Hi Kedar,
>
>>
>>
>> The syntax used conforms to most of the suggestions mentioned in
>> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
>> barring the following:
>> -- Specification of partition names is optional. System will be able to
>> generate partition names in such cases.
>> -- Sub partitioning
>>
>
> I was wondering if there is a need to mention the type of partition while
> dropping it.
>
> E.g
> ALTER table x DROP RANGE PARTITION x_part;
>
> The type of partition (RANGE, HASH) could be dropped according to me.
>
>
>>
>> We are maintaining a system catalog(pg_partition) for partition meta-data.
>> System will look-up this table to find appropriate partition to operate on.
>> System internally uses low-level 'C' triggers to row-movement.
>>
>
> Can you elaborate more on how do you handle updates with these triggers?
>
> Regards,
> Nikhils
> --
> http://www.enterprisedb.com
>

Re: [HACKERS] Partitioning feature ...

[Kedar Potdar]

Hi Emmanuel,

Thanks for your time. This is a WIP patch and we will integrate your
suggestions/comments as appropriate.

Regards,
--
Kedar.

On Fri, Mar 27, 2009 at 3:38 AM, Emmanuel Cecchet wrote:

> Hi Kedar,
>
> First of all, congratulations for the excellent work.
> I have some comments and questions.
>
> In get_relevent_partition (btw, relevant is spelled with an a) you are
> maintaining 2 lists.


> Oops! 'a' typographical error.

I guess this is only useful for multi-column partitions, right?
> If you have a single column partition (without subpartitions), I think you
> could directly return on the first match (without maintaining any list)
> since you guarantee that there is no overlap between partitions.
> A simple but effective optimization for inserts consists of caching the
> last partition used (consecutive inserts often go to the same partition) and
> try it first before going through the whole loop.

> Yep.

>
>
> The update trigger should first check if the tuple needs to be moved. If
> the updated tuple still matches the constraints of the partitions it will
> not have to be moved and will save a lot of overhead.

> Yes. We agree on that.

>
>
> The COPY operation should probably be optimized to use the same code as the
> one in the insert trigger for partitioned tables. I guess some code could be
> factorized in COPY to make the inserts more efficient.
>
> The current trigger approach should prevent other triggers to be added to
> the table, or you should make sure that the partition trigger is always the
> one to execute last.

>  As triggers are executed in order of their names, we've prefixed the
trigger names with "zz". This should work fine as long as no-one uses
trigger-name which starts with "zz".

>
> As we don't have automatic partition creation, it would be interesting to
> have an optional mechanism to deal with tuples that don't match any
> partition (very useful when you do bulk insert and some new data require a
> new partition). Having a simple overflow partition or an error logging
> mechanism would definitely help to identify these tuples and prevent things
> like large COPY operations to fail.

> Will get back on this.

>
>
>
> Looking forward to your responses,
> Emmanuel
>
>
>> We are implementing table partitioning feature to support Range and Hash
>> partitions. Please find attached, the WIP patch and test-cases.
>>
>> The syntax used conforms to most of the suggestions mentioned in
>> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
>> barring the following:
>> -- Specification of partition names is optional. System will be able to
>> generate partition names in such cases.
>> -- Sub partitioning
>>
>> We are maintaining a system catalog(pg_partition) for partition meta-data.
>> System will look-up this table to find appropriate partition to operate on.
>> System internally uses low-level 'C' triggers to row-movement.
>>
>> Regards,
>> --
>> Kedar.
>>
>>
>>
>> 
>>
>>
>>
>>
>
>
> --
> Emmanuel Cecchet
> Aster Data Systems
> Web: http://www.asterdata.com
>
>

[HACKERS] Automating Partitions in PostgreSQL - Query on syntax

[Kedar Potdar]

Hi ,



We are working on a
patchto
automate partitioning in
PostgreSQL.



For Range partitions, we have proposed the syntax which is as follows –



*CREATE TABLE emp (*

*emp_id  int not null primary key,*

*designation text not null,*

*locationvarchar(50) not null,*

*jdate   date not null,*

*ctc float not null*

* *

*)*

*PARTITION BY RANGE (emp_id)*

*(*

*emp_500 (START 1 END 500),*

*emp_1500 (START 500 END 1500),*

*emp_4000 (START 1520 END 4000)*

*);*



As observed in this syntax, user needs to specify explicitly, the min and
max values of a range for a given partition.

With this design, partition ranges are inherently allowed to be fragmented
and non-contiguous. As ‘gaps’ are allowed

in the ranges, we’re also supporting an ‘overflow’ partition, so that any
row, which does not satisfy constraints of any

existing partitions, does not stall a big UPDATE operation and such rows are
preserved.(in overflow table)



However, Oracle uses user-friendly syntax but makes it compulsion that
partition ranges *have* to be contiguous.



*PARTITION BY RANGE (emp_id)*

*(*

*Partition emp_500  values less than (500),*

*Partition emp_1500 values less than (1500),*

*Partition emp_4000 values less than (4000),*

*Partition emp_max  values less than (maxvalue)*

*);*



As it does not allow fragmented ranges, it automatically removes the need
for an ‘overflow’ partition.



The syntax proposed by us is more flexible and would handle both the cases
of ranges with gaps or ranges without gaps.



I want to seek general opinion from the community on preferences between
user-friendly ‘Oracle’ syntax, and a more generic syntax that allows ‘gaps’
in partition ranges?



Regards,

--

Kedar

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

[Kedar Potdar]

Hi Greg,

Thanks for your response.

Currently, such records are left in the overflow partition and its
responsibility
of user to insert them into partitioned-table which will then re-direct
those to
appropriate partitions.

Regards,
--
Kedar.

On Tue, Apr 21, 2009 at 5:29 PM, Greg Stark  wrote:

> On Tue, Apr 21, 2009 at 12:50 PM, Kedar Potdar 
> wrote:
> > I want to seek general opinion from the community on preferences between
> > user-friendly ‘Oracle’ syntax, and a more generic syntax that allows
> ‘gaps’
> > in partition ranges?
>
>
> What happens to records in the overflow table when you add a new
> partition whose range covers their values?
>
> --
> greg
>

Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax

[Kedar Potdar]

2009/4/21 Dickson S. Guedes 

> Em Ter, 2009-04-21 às 17:20 +0530, Kedar Potdar escreveu:
> > Hi ,
> >
> > We are working on a patch to automate partitioning in PostgreSQL.
>
> Nice. ":)
>
> > For Range partitions, we have proposed the syntax which is as
> > follows
> >
> > (...)
> > PARTITION BY RANGE (emp_id)
> > (
> > emp_500 (START 1 END 500),
> > emp_1500 (START 500 END 1500),
> > emp_4000 (START 1520 END 4000)
> > );
>
> What if I need more columns to set the partitions?


You can do so by using command like this,

CREATE TABLE emp (
emp_id  int not null primary key,
designation text not null,
locationvarchar(50) not null,
jdate   date not null,
ctc float not null

)
PARTITION BY RANGE (jdate, emp_id)
(
emp_500 (START '01-01-1980',1 END '01-01-1990',500),
emp_1500(START '01-01-1990',500 END '01-01-2000',1500+10),
emp_4000 (START '1 Jan 2000',1500+20 END 'Jan 1, 2010', 4000-50)
);

You can have multiple columns as partition key attributes and values for
these attributes should appear in the order specified.


>
>
> []s
> --
> Dickson S. Guedes
> mail/xmpp: gue...@guedesoft.net - skype: guediz
> http://guedesoft.net - http://planeta.postgresql.org.br
>

Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2

[Kedar Potdar]

PFA the required header file.

Regards,
--
Kedar.

On Tue, Jun 9, 2009 at 12:26 AM, Jaime Casanova <
jcasa...@systemguards.com.ec> wrote:

> On Mon, Jun 8, 2009 at 1:38 PM, Grzegorz Jaskiewicz 
> wrote:
> >
> > make[3]: *** No rule to make target
> `../../../src/include/catalog/pg_partition.h', needed by `postgres.bki'.
>  Stop.
>
> there is no pg_partition.h file in the patch, please send it
>
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>
/*-
 *
 * pg_partition.h
 *definition of the system "partition" relation (pg_partition)
 *along with the relation's initial contents.
 *
 *
 * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
 *
 * $PostgreSQL: pgsql/src/include/catalog/pg_partition.h,v 1.0 2009/02/03 
03:57:34 tgl Exp $
 *
 * NOTES
 *the genbki.sh script reads this file and generates .bki
 *information from the DATA() statements.
 *
 *-
 */
#ifndef PG_PARTITION_H
#define PG_PARTITION_H

#include "catalog/genbki.h"

/* 
 *  pg_partition definition.  cpp turns this into
 *  typedef struct FormData_pg_partitions
 * 
 */
#define PartitionRelationId 2336

CATALOG(pg_partition,2336) 
{
Oid partrelid;  /* partition table Oid */
Oid parentrelid;/* Parent table Oid */
int2parttype;   /* Type of partition, list, hash, range */
Oid partkey;/* partition key Oid */
Oid keytype;/* type of partition key */
int2keyorder;   /* order of the key in multi-key partitions */
bytea   minval;
bytea   maxval; /* min and max for range partition */
bytea   listval;
int2hashval;/* hash value */
} FormData_pg_partition;

/* 
 *  Form_pg_partitions corresponds to a pointer to a tuple with
 *  the format of pg_partitions relation.
 * 
 */
typedef FormData_pg_partition *Form_pg_partition;

/* 
 *  compiler constants for pg_partitions
 * 
 */
#define Natts_pg_partition  10
#define Anum_pg_partition_partrelid 1
#define Anum_pg_partition_parentrelid   2
#define Anum_pg_partition_parttype  3
#define Anum_pg_partition_partkey   4
#define Anum_pg_partition_minval7
#define Anum_pg_partition_maxval8
#define Anum_pg_partition_listval   9
#define Anum_pg_partition_hashval   10
#define Anum_pg_partition_keytype   5
#define Anum_pg_partition_keyorder  6

#endif   /* PG_PARTITIONS_H */

-- 
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] Patch for automating partitions in PostgreSQL 8.4 Beta 2

[Kedar Potdar]

PFA. This file is to be kept in 'pgsql_init' base directory.

On Tue, Jun 9, 2009 at 12:54 PM, Grzegorz Jaskiewicz 
wrote:

> gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
>  -bundle -multiply_defined suppress  regress.o -bundle_loader
> ../../../src/backend/postgres  -L../../../src/port   -o regress.so
> cp ../../../contrib/spi/refint.so refint.so
> cp ../../../contrib/spi/autoinc.so autoinc.so
> gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
>  pg_regress.o pg_regress_main.o -L../../../src/port -Wl,-dead_strip_dylibs
>  -lpgport -lz -lreadline -lm  -o pg_regress
> make -C config all
> make[1]: Nothing to be done for `all'.
>
> /bin/sh: /Users/gj/Projects/postgres-head/pgsql/partition.sh: No such file
> or directory
>
> make: *** [all] Error 127
>
>
>


partition.sh
Description: Bourne shell script

-- 
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] Patch for automating partitions in PostgreSQL 8.4 Beta 2

[Kedar Potdar]

I did a fresh checkout and applied patch and added files and it works at my
end.

Is there any problem with formatting of the file? May be some
characters('\') missing in conversion?

On Tue, Jun 9, 2009 at 4:14 PM, gj  wrote:

> still doesn't work:
>
> make[1]: Leaving directory `/home/gjaskie/Projects/postgres/pgsql/config'
> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 14: a.keyorder,:
> command not found
> /home/gjaskie/Projects/postgres/pgsql/partition.sh: line 15: where: command
> not
> found
> : command not foundcts/postgres/pgsql/partition.sh: line 16:
>
> Please make sure you test patches before sending here, on clean checkout!.
>
>
>
>

Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2

[Kedar Potdar]

Hi Nikhil,

I am sorry for the late reply. :(

Please find inline my comments.

On Tue, Jun 9, 2009 at 2:54 PM, Nikhil Sontakke <
nikhil.sonta...@enterprisedb.com> wrote:

> Hi,
>
>
>>
>> The patch automates table partitioning to support Range and Hash
>> partitions. Please refer to attached readme file for further details.
>>
>> The syntax used conforms to most of the suggestions mentioned in
>> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php,
>> barring the following:
>> -- Specification of partition names is optional. System will be able to
>> generate partition names in such cases.
>> -- Sub partitioning
>>
>
> Some comments based on a brief glance of the patch:
>
> - The logic to execute the partition triggers last still needs some more
> work IMHO. Relying on just the names might not get accepted. I think you
> should pay attention to Andrew Dunstan's suggestion in an earlier mail to
> have tgkind enumerations to generalize the same or discuss it further.
>
> "the scheme should turn tgisconstraint into a multi-valued item (tgkind:
> 'u' = userland, 'c'= constraint, 'p' = partition or some such)."
>

Kedar >> I am working on to turn tgisconstraint into a multi-values item to
categorize user, constraint and partition triggers. In doing so, I am
thinking of adding adding 'PARTITION' keyword to existing syntax to create
partition triggers i.e. CREATE PARTITION TRIGGER ... The partition triggers
would now be identified with the type rather than naming scheme though
naming scheme for partition triggers would still be there.


>
> - Similarly, assigning "of_relname_oid" names to overflow tables also might
> not work. The best way ahead could be to invent a new relkind
> RELKIND_OVERFLOW to handle it. Or maybe we can have a new schema pg_overflow
> to store the overflow relation with the same name (suffixed with _overflow
> to make it clearer) as the parent relation too.  The relkind solution might
> be cleaner though. This might need further discussion. In general, it is
> definitely not a bad idea to discuss such sub-problems on the list :)
>

Kedar >> I will look at it once done with catagorizing partition triggers. I
am inclined toward using a new relkind like RELKIND_OVERFLOW.


>
>
> - Am I reading the patch correctly that you do not end up creating indexes
> on the children tables? That is a big problem!


Kedar >> As Srinath K is working on global indexes, the merge of
partitioning and global indexes should be able to extend indexes created on
partitioned table to children as well.


>
>
> - You can remove the remnants of the first patch like the
> MutateColumnRefs() function, for example (I agree this is WIP, but
> unwanted/unused functions unnecessarily add to the size). With large
> patches, the more precise the patch, the better it will be for
> reviewers/readers.


Kedar >> Yeah.


>
>
> Great work all in all!


Kedar >> Thanks! Looking forward to your continued co-operation.


>
>
> Regards,
> Nikhils
> --
> http://www.enterprisedb.com
>

Re: [HACKERS] Patch for automating partitions in PostgreSQL 8.4 Beta 2

[Kedar Potdar]

Yes. I am working to integrate some of the comments received for the patch.
I would be able to post latest patch in the next week.

Regards,
--
Kedar.



On Tue, Jul 7, 2009 at 10:18 AM, Jaime Casanova <
jcasa...@systemguards.com.ec> wrote:

> On Mon, Jun 8, 2009 at 9:02 AM, Kedar Potdar
> wrote:
> > Hi,
> >
> > PFA patch, readme for automating partitions in PostgreSQL 8.4 Beta 2 and
> > testcases.
> >
>
> if you are still working on this, can you please update the patch to cvs
> head?
>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>



-- 
Regards,
--
Kedar.

Read 'me' - http://kpotdar.livejournal.com