Regarding column reordering project for GSoc 2012

On 03/17/2012 04:39 AM, Atri Sharma wrote:

Hi All,

I am Atri Sharma.I am a C developer with experience in database
designing and database management systems.I have worked with
MySql,Oracle and other database systems.I have designed databases for
various projects as well.

I would be interested in developing the column reordering project by a
single ALTER command.I have an idea in mind that would solve
theproblem in a very easy way.

Please let me know how to proceed further on this.

You should review the history on this. See
<http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.org&gt;
as a good place to start.

You should be aware that anything that doesn't follow the previous
discussions is likely to be rejected. But if you can do something that
does implement what was previously discussed, that would be awesome.

cheers

andrew

Thanks Andrew.

I went through the link. Here is my idea:

I believe we can build a "middle" layer that encapsulates the physical
details from the user and gives him/her a VIEW that is actually generated by
the system itself. That means, we have a system such as:

LOGICAL LAYER
--------------
MIDDLE LAYER
--------------
PHYSICAL LAYER.

Now, the punch would be, the middle layer would be on the client. The middle
layer is more of an interface for the user which he/she can completely
customize.

Now, what is actually happening is that each component is mapped to their
physical address. I am planning to make a struct which will be the mapping
between the physical address and the logical address.I have not yet decided
on the design of the struct,but I plan an id , the type and the physical
address.When a component is used in the middle layer,it is represented in an
instantiation of the struct which is unchangeable throughout.Whenever we
need to customize the middle layer,we just need to reorder the components.

For our specific case,the reordering of table columns,we need not change the
schema or the physical ordering.All we need to do is,reorder the
components.One ALTER statement(with added customized clauses) will do the
reordering.The mapping remains constant,because,the struct instantiations of
the components remain constant,hence,the mapping remains constant and we can
access the physical address of any component at any time.

Please let me know your feedback on the idea.

Atri

-----Original Message-----
From: Andrew Dunstan [mailto:andrew@dunslane.net]
Sent: 17 March 2012 21:30
To: Atri Sharma
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

On 03/17/2012 04:39 AM, Atri Sharma wrote:

Hi All,

I am Atri Sharma.I am a C developer with experience in database
designing and database management systems.I have worked with
MySql,Oracle and other database systems.I have designed databases for
various projects as well.

I would be interested in developing the column reordering project by a
single ALTER command.I have an idea in mind that would solve
theproblem in a very easy way.

Please let me know how to proceed further on this.

You should review the history on this. See
<http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.or
g>
as a good place to start.

You should be aware that anything that doesn't follow the previous
discussions is likely to be rejected. But if you can do something that
does implement what was previously discussed, that would be awesome.

cheers

andrew

Hi all,

In follow up to my last mail, attached below, for performance, we can build
a graph with edges having weight in sense of performance wise and padding
saving wise . Then , we can build a minimal spanning tree, and the tree
generated would be our order of columns . When any changes are made ,
changes in the graph will be done and minimal spanning tree would be
recomputed.

Atri

-----Original Message-----
From: Atri Sharma [mailto:atri.jiit@gmail.com]
Sent: 17 March 2012 22:43
To: 'Andrew Dunstan'
Cc: 'pgsql-hackers@postgresql.org'
Subject: RE: [HACKERS] Regarding column reordering project for GSoc 2012

Thanks Andrew.

I went through the link. Here is my idea:

I believe we can build a "middle" layer that encapsulates the physical
details from the user and gives him/her a VIEW that is actually generated by
the system itself. That means, we have a system such as:

LOGICAL LAYER
--------------
MIDDLE LAYER
--------------
PHYSICAL LAYER.

Now, the punch would be, the middle layer would be on the client. The middle
layer is more of an interface for the user which he/she can completely
customize.

Now, what is actually happening is that each component is mapped to their
physical address. I am planning to make a struct which will be the mapping
between the physical address and the logical address.I have not yet decided
on the design of the struct,but I plan an id , the type and the physical
address.When a component is used in the middle layer,it is represented in an
instantiation of the struct which is unchangeable throughout.Whenever we
need to customize the middle layer,we just need to reorder the components.

For our specific case,the reordering of table columns,we need not change the
schema or the physical ordering.All we need to do is,reorder the
components.One ALTER statement(with added customized clauses) will do the
reordering.The mapping remains constant,because,the struct instantiations of
the components remain constant,hence,the mapping remains constant and we can
access the physical address of any component at any time.

Please let me know your feedback on the idea.

Atri

-----Original Message-----
From: Andrew Dunstan [mailto:andrew@dunslane.net]
Sent: 17 March 2012 21:30
To: Atri Sharma
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

On 03/17/2012 04:39 AM, Atri Sharma wrote:

Hi All,

I am Atri Sharma.I am a C developer with experience in database
designing and database management systems.I have worked with
MySql,Oracle and other database systems.I have designed databases for
various projects as well.

I would be interested in developing the column reordering project by a
single ALTER command.I have an idea in mind that would solve
theproblem in a very easy way.

Please let me know how to proceed further on this.

You should review the history on this. See
<http://archives.postgresql.org/message-id/1324412114-sup-9608@alvh.no-ip.or
g>
as a good place to start.

You should be aware that anything that doesn't follow the previous
discussions is likely to be rejected. But if you can do something that
does implement what was previously discussed, that would be awesome.

cheers

andrew

Excerpts from Atri Sharma's message of sáb mar 17 05:39:10 -0300 2012:

Hi All,

I am Atri Sharma.I am a C developer with experience in database
designing and database management systems.I have worked with
MySql,Oracle and other database systems.I have designed databases for
various projects as well.

I would be interested in developing the column reordering project by a
single ALTER command.I have an idea in mind that would solve
theproblem in a very easy way.

This is far too complex to be done in a GSoC project.

I don't see how having a middle layer client-side would fix anything.
You'd need to explain how it'd work, but to me that description sounds
bogus.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

HI Alvaro,
I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it.

Please let me know if I should re-send the mail trail.

Atri

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: 18 March 2012 00:17
To: Atri Sharma
Cc: Pg Hackers
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

Excerpts from Atri Sharma's message of sáb mar 17 05:39:10 -0300 2012:

Hi All,

I am Atri Sharma.I am a C developer with experience in database
designing and database management systems.I have worked with
MySql,Oracle and other database systems.I have designed databases for
various projects as well.

I would be interested in developing the column reordering project by a
single ALTER command.I have an idea in mind that would solve
theproblem in a very easy way.

This is far too complex to be done in a GSoC project.

I don't see how having a middle layer client-side would fix anything.
You'd need to explain how it'd work, but to me that description sounds
bogus.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Excerpts from Atri Sharma's message of sáb mar 17 15:48:13 -0300 2012:

HI Alvaro,
I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it.

I read it, but I don't see how is the client involved.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi Alvaro,
Thanks for going through my description.

I was going through the posts,when I realised that if we give each client the flexibility of defining his/her own order with complete control,that would be probably the right way(IMHO).

I mean,if the user sees,not the physical implementation,but a customizable interface instead,which is mapped to the physical implementation,then,we can solve to some extent,the problem of logical/physical decoupling.

If you are free,I can come on IRC right now and explain my idea to you.

Atri

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: 18 March 2012 00:34
To: Atri Sharma
Cc: Pg Hackers
Subject: RE: [HACKERS] Regarding column reordering project for GSoc 2012

Excerpts from Atri Sharma's message of sáb mar 17 15:48:13 -0300 2012:

HI Alvaro,
I did send a complete description to the mailing list, based on your past discussions. I don’t know if you got it.

I read it, but I don't see how is the client involved.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Saturday, March 17, 2012, Atri Sharma <atri.jiit@gmail.com> wrote:

Hi Alvaro,
Thanks for going through my description.

I was going through the posts,when I realised that if we give each client

the flexibility of defining his/her own order with complete control,that
would be probably the right way(IMHO).

I mean,if the user sees,not the physical implementation,but a

customizable interface instead,which is mapped to the physical
implementation,then,we can solve to some extent,the problem of
logical/physical decoupling.

Anything done in the client bar changing the column it orders on (which
ideally should be avoided too if practical) is going to lead to pain and
suffering as some will do it, some won't, and a few might get it wrong.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Dave,

I believe,with the ALTER command,we plan to give the user power to modify
ordering . Don't we?

Atri

From: Dave Page [mailto:dpage@pgadmin.org]
Sent: 18 March 2012 00:46
To: Atri Sharma
Cc: Alvaro Herrera; Pg Hackers
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

On Saturday, March 17, 2012, Atri Sharma <atri.jiit@gmail.com> wrote:

Hi Alvaro,
Thanks for going through my description.

I was going through the posts,when I realised that if we give each client

the flexibility of defining his/her own order with complete control,that
would be probably the right way(IMHO).

I mean,if the user sees,not the physical implementation,but a customizable

interface instead,which is mapped to the physical implementation,then,we can
solve to some extent,the problem of logical/physical decoupling.

Anything done in the client bar changing the column it orders on (which
ideally should be avoided too if practical) is going to lead to pain and
suffering as some will do it, some won't, and a few might get it wrong.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sat, Mar 17, 2012 at 2:16 PM, Atri Sharma <atri.jiit@gmail.com> wrote:

Hi Dave,

I believe,with the ALTER command,we plan to give the user power to modify
ordering . Don’t we?

And how is that different from a view that orders the columns as the
user wishes?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

On 03/17/2012 03:07 PM, Atri Sharma wrote:

Hi Alvaro,
Thanks for going through my description.

I was going through the posts,when I realised that if we give each client the flexibility of defining his/her own order with complete control,that would be probably the right way(IMHO).

I mean,if the user sees,not the physical implementation,but a customizable interface instead,which is mapped to the physical implementation,then,we can solve to some extent,the problem of logical/physical decoupling.

I think your design is a complete non-starter. Please review what was
previously discussed. If you're not going to attempt that then my strong
advice is to try a different project.

If you are free,I can come on IRC right now and explain my idea to you.

The place to discuss designs is on the mailing lists. That's the way we
do things. The vast majority of hackers are not to be found in IRC.

cheers

andrew

On Saturday, March 17, 2012, Atri Sharma <atri.jiit@gmail.com> wrote:

Hi Dave,

I believe,with the ALTER command,we plan to give the user power to modify

ordering . Don’t we?

Probably, but that's not relevant to my point as far as I can see - I was
objecting to your suggestion that the "middle layer" that translates the
physical to logical ordering and vice-versa (if I understood your
description correctly) be in the client.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 03/17/2012 04:42 PM, Dave Page wrote:

On Saturday, March 17, 2012, Atri Sharma <atri.jiit@gmail.com
<mailto:atri.jiit@gmail.com>> wrote:

Hi Dave,

I believe,with the ALTER command,we plan to give the user power to

modify ordering . Don’t we?

Probably, but that's not relevant to my point as far as I can see - I
was objecting to your suggestion that the "middle layer" that
translates the physical to logical ordering and vice-versa (if I
understood your description correctly) be in the client.

Mine too. We don't want a column ordering that's different for everyone.
That's a recipe for mass confusion. We want to be able to mutate the
ordering for everyone, and for everyone to see the same ordering. That
means it MUST be done purely in the backend (as indeed all ALTER TABLE
commands must be.)

cheers

andrew

On Sat, Mar 17, 2012 at 1:48 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Mine too. We don't want a column ordering that's different for everyone.
That's a recipe for mass confusion. We want to be able to mutate the
ordering for everyone, and for everyone to see the same ordering. That means
it MUST be done purely in the backend (as indeed all ALTER TABLE commands
must be.)

I am in agreement. This state would have to live in the session
between client and server, so that semantic analysis could do the
right thing by looking at the environment on the backend, too. Life
is already really difficult because of session state: consider
connection poolers, which are both common and have subtle caveats that
are close to being completely silent if one uses the wrong construct.
The only reason people get away with this in practice is because most
of them do not use constructs like temp tables and dblink in
conjunction with pooling, but if they did, the prospect of what could
happen when sessions get crossed is a nightmare (clearly, I'd like a
way to treat this, because session state is also useful, but that's
not the here and now...)

Reordering attributes totally backends-side, though, would be a
wonderful feature, and already has plenty of work just to realize the
physical/logical decoupling in the backend, so I have a lot of
enthusiasm for the project in general.

--
fdr

-----Original Message-----
From: Daniel Farina [mailto:daniel@heroku.com]
Sent: 18 March 2012 06:30
To: Andrew Dunstan
Cc: Dave Page; Atri Sharma; Alvaro Herrera; Pg Hackers
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

On Sat, Mar 17, 2012 at 1:48 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

Mine too. We don't want a column ordering that's different for everyone.
That's a recipe for mass confusion. We want to be able to mutate the
ordering for everyone, and for everyone to see the same ordering. That

means

it MUST be done purely in the backend (as indeed all ALTER TABLE commands
must be.)

I am in agreement. This state would have to live in the session
between client and server, so that semantic analysis could do the
right thing by looking at the environment on the backend, too. Life
is already really difficult because of session state: consider
connection poolers, which are both common and have subtle caveats that
are close to being completely silent if one uses the wrong construct.
The only reason people get away with this in practice is because most
of them do not use constructs like temp tables and dblink in
conjunction with pooling, but if they did, the prospect of what could
happen when sessions get crossed is a nightmare (clearly, I'd like a
way to treat this, because session state is also useful, but that's
not the here and now...)

Reordering attributes totally backends-side, though, would be a
wonderful feature, and already has plenty of work just to realize the
physical/logical decoupling in the backend, so I have a lot of
enthusiasm for the project in general.

--
Fdr

-------------------------------

Hi All,

First of all, thanks a million for all the wonderful feedbacks. It has
really taught me a lot in just 8 hours.

I am understanding the scenario now. I will take a little of your time to
modify my original idea:

The "middle layer" still exists, but NOT on the individual client side ,
rather , on the server side. That is, we maintain the middle layer on the
server, and it is same for all the users. We can mutate the ordering, and
changes would be reflected in all the clients, since they are all accessing
the same middle layer, present on the server.

The mapping structure, according to me, should still remain the same.

Please let me know your feedback and corrections.

Atri

-----Original Message-----
From: jcasanov@systemguards.com.ec [mailto:jcasanov@systemguards.com.ec] On
Behalf Of Jaime Casanova
Sent: 18 March 2012 01:04
To: Atri Sharma
Cc: Dave Page; Alvaro Herrera; Pg Hackers
Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012

On Sat, Mar 17, 2012 at 2:16 PM, Atri Sharma <atri.jiit@gmail.com> wrote:

Hi Dave,

I believe,with the ALTER command,we plan to give the user power to modify
ordering . Don’t we?

And how is that different from a view that orders the columns as the
user wishes?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

--------------------------------------

Hi Jaime ,

Well, I believe, my idea is more towards the layers already present in
database systems, rather than views.

1) Views are just a window to some(or all) part of a database. There are
many restriction on views, none of which are applicable to our middle layer.
This is so because, even though the middle layer is encapsulating the
addressing and mapping details from the user , it still provides complete
access and rights to the database according to the user's privilege level.

2) Views are optional, wherever our middle layer is built right in the
database. It is not optional. User can only see the middle layer. It cannot
directly interact with the underlying layers.

Please let me know your feedback and comments.

Atri

Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012:

I am understanding the scenario now. I will take a little of your time to
modify my original idea:

The "middle layer" still exists, but NOT on the individual client side ,
rather , on the server side. That is, we maintain the middle layer on the
server, and it is same for all the users. We can mutate the ordering, and
changes would be reflected in all the clients, since they are all accessing
the same middle layer, present on the server.

I will take a little of your time to ask whether you read the older
threads on the topic -- the one that Andrew Dunstan linked to, and the
older ones, to which that email links to? This whole area has been
extensively discussed before.

In any case, I repeat what I said before: this is way too complex a
topic for it to make a good GSoC project (unless you're already
intimately familiar with the parser and executor code.) You need to
pick something smaller, more localized.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Mar 19, 2012 at 11:51 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012:

I am understanding the scenario now. I will take a little of your time to
modify my original idea:

The "middle layer" still exists, but NOT on the individual client side ,
rather , on the server side. That is, we maintain the middle layer on the
server, and it is same for all the users. We can mutate the ordering, and
changes would be reflected in all the clients, since they are all accessing
the same middle layer, present on the server.

I will take a little of your time to ask whether you read the older
threads on the topic -- the one that Andrew Dunstan linked to, and the
older ones, to which that email links to?  This whole area has been
extensively discussed before.

In any case, I repeat what I said before: this is way too complex a
topic for it to make a good GSoC project (unless you're already
intimately familiar with the parser and executor code.)  You need to
pick something smaller, more localized.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

------------------------------

Hi Alvaro,

Thanks for your reply.Yes,I have read the earlier posts.

I am familiar with database systems.I have worked on them before and I
have done designing of some very basic databases.

Please guide me to some area where I can research for some gsoc project.

Thanks,

Atri

--
Regards,

Atri
l'apprenant

On Tue, Mar 20, 2012 at 12:08 AM, Atri Sharma <atri.jiit@gmail.com> wrote:

On Mon, Mar 19, 2012 at 11:51 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Atri Sharma's message of dom mar 18 01:05:23 -0300 2012:

I am understanding the scenario now. I will take a little of your time to
modify my original idea:

The "middle layer" still exists, but NOT on the individual client side ,
rather , on the server side. That is, we maintain the middle layer on the
server, and it is same for all the users. We can mutate the ordering, and
changes would be reflected in all the clients, since they are all accessing
the same middle layer, present on the server.

I will take a little of your time to ask whether you read the older
threads on the topic -- the one that Andrew Dunstan linked to, and the
older ones, to which that email links to?  This whole area has been
extensively discussed before.

In any case, I repeat what I said before: this is way too complex a
topic for it to make a good GSoC project (unless you're already
intimately familiar with the parser and executor code.)  You need to
pick something smaller, more localized.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

------------------------------

Hi Alvaro,

Thanks for your reply.Yes,I have read the earlier posts.

I am familiar with database systems.I have worked on them before and I
have done designing of some very basic databases.

Please guide me to some area where I can research for some gsoc project.

Thanks,

Atri

--
Regards,

Atri
l'apprenant

----------------------
Hi Alvaro,

I was just going through PGfoundry and I think I will be able to work
on the PL/Java project.Please let me know If I can proceed further on
ot for GSOC and also,if currently there is any work that needs to be
done on PL/java.
--
Regards,

Atri
l'apprenant