Thread: Self referencing composite datatype

Self referencing composite datatype

From
Sameer Thakur
Date:
Hello,
I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node.
create type Node as (r integer, s integer, children Node []); 
But i get error type Node[] does not exist. I understand that Node is not defined hence the error.
But how do i get around this problem? 

regards
Sameer

Re: Self referencing composite datatype

From
Chris Travers
Date:



On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.com> wrote:
Hello,
I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node.
create type Node as (r integer, s integer, children Node []); 
But i get error type Node[] does not exist. I understand that Node is not defined hence the error.
But how do i get around this problem? 

What exactly are you trying to accomplish?  I can think of a number of ways.

For example, suppose we have a table like:

create table node (
   id int primary key,
   parent int references node(id),
   content text not null
);

We could create a function like this:

CREATE FUNCTION children(node) RETURNS node[] LANGUAGE SQL AS
$$
SELECT array_agg(node) FROM node WHERE parent=$1.id;
$$;

Then we could still do:

select n.children FROM node n WHERE id = 123;

Note that causes two separate scans, but should work.

Best Wishes,
Chris Travers 

regards
Sameer



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Self referencing composite datatype

From
David Johnston
Date:
Sameer Thakur wrote
> Hello,
> I wanted to create a composite datatype to represent a Node. So it would
> have a few attributes and an array of type Node which is the children of
> this node.
> create type Node as (r integer, s integer, children Node []);
> But i get error type Node[] does not exist. I understand that Node is not
> defined hence the error.
> But how do i get around this problem?

In theory if you are using 9.1 or later you can first create the node type
and then alter it to include a children attribute with the self-referencing
type-array.

begin;

create type node as (r integer, s integer);
alter type node add attribute children node[];

end;

I'm running 9.0 so cannot readily test this at the moment.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Self-referencing-composite-datatype-tp5766635p5766651.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Self referencing composite datatype

From
Igor Neyman
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of David Johnston
> Sent: Wednesday, August 07, 2013 10:35 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Self referencing composite datatype
>
> Sameer Thakur wrote
> > Hello,
> > I wanted to create a composite datatype to represent a Node. So it
> > would have a few attributes and an array of type Node which is the
> > children of this node.
> > create type Node as (r integer, s integer, children Node []); But i
> > get error type Node[] does not exist. I understand that Node is not
> > defined hence the error.
> > But how do i get around this problem?
>
> In theory if you are using 9.1 or later you can first create the node type and
> then alter it to include a children attribute with the self-referencing type-
> array.
>
> begin;
>
> create type node as (r integer, s integer); alter type node add attribute
> children node[];
>
> end;
>
> I'm running 9.0 so cannot readily test this at the moment.
>
> David J.
>

Under 9.2.2 I'm getting an error:

ERROR:  composite type node cannot be made a member of itself

********** Error **********

ERROR: composite type node cannot be made a member of itself
SQL state: 42P16

Regards,
Igor Neyman


Re: Self referencing composite datatype

From
David Johnston
Date:
Igor Neyman wrote
>>
>> create type node as (r integer, s integer); alter type node add attribute
>> children node[];
>>
>
> Under 9.2.2 I'm getting an error:
>
> ERROR:  composite type node cannot be made a member of itself

I'm not sure why the limitation exists (probably something to do with
avoiding infinite recursion) but even if it could be fixed it wouldn't be
for at least a year (version 9.4 or greater) before you'd see it so you will
need to find an alternative solution to your problem.

So with Chris' suggestion you store the node data in a highly detailed form
with parent node id foreign keys then use a function to dynamically generate
the "children" data.  The syntax he is using is intermediate-level
PostgreSQL but well described in the documentation (somewhere).  Basically,

table.virtual_column == virtual_column(table)

so by creating a function named "children" taking a "node" record/table-type
as input you can use a shorthand form to actually call the function.

These are equivalent:

SELECT node.*, node.children FROM node
SELECT node.*, children(node) FROM node

noting the fact the "node.*" will NOT give you the children; you must still
explicitly invoke the function somehow.

Other solutions are possible but as we do not know the use case meaningful
but more specific solutions are hard to envision or suggest.  Chris'
solution is fairly generic in nature and quite useful once you understand
what exactly is going on.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Self-referencing-composite-datatype-tp5766635p5766662.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Self referencing composite datatype

From
Sergey Konoplev
Date:
On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.com> wrote:
> I wanted to create a composite datatype to represent a Node. So it would
> have a few attributes and an array of type Node which is the children of
> this node.
> create type Node as (r integer, s integer, children Node []);
> But i get error type Node[] does not exist. I understand that Node is not
> defined hence the error.
> But how do i get around this problem?

I just wonder how are you going to use this kind of types?

In 9.3 you will be able to use foreign keys with arrays like it is describe here
http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

eg.

create table node as (
  id integer primary key,
  r integer, s integer,
  children integer[] element references node
);

so you could download 9.3rc2 and experimant with it.

Now (on <=9.2.x) you can create the table without FK

create table node as (
  id integer primary key,
  r integer, s integer,
  children integer[]
);

and check integrity by triggers.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com


Re: Self referencing composite datatype

From
Sergey Konoplev
Date:
On Wed, Aug 7, 2013 at 7:11 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> so you could download 9.3rc2 and experimant with it.

Sorry, 9.3beta2 of course.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com


Re: Self referencing composite datatype

From
Sameer Thakur
Date:


On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur <samthakur74@gmail.com> wrote:
Hello,
I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node.
create type Node as (r integer, s integer, children Node []); 
But i get error type Node[] does not exist. I understand that Node is not defined hence the error.
But how do i get around this problem? 

What exactly are you trying to accomplish?  I can think of a number of ways.

For example, suppose we have a table like:

create table node (
   id int primary key,
   parent int references node(id),
   content text not null
);

We could create a function like this:

CREATE FUNCTION children(node) RETURNS node[] LANGUAGE SQL AS
$$
SELECT array_agg(node) FROM node WHERE parent=$1.id;
$$;

Then we could still do:

select n.children FROM node n WHERE id = 123;

Note that causes two separate scans, but should work.


Thank you. 
I am trying to capture plan statistics for every node in the plan tree. For this i have plan view which has plan specific information like planid,plan_text, and root_node of type Node.

Type Node has node specific statistics like estimated_startup_cost, estimated_actual_cost etc. It also has child_nodes of type Node[]. i face the problem of self referencing composite type in defining Node type. 

The reason i wanted a type Node is because while initializing memory for my contrib module i have GUC parameter specifying max number of Nodes. I was thinking that it is possible to initialize memory with a sizeof(Node). 

Still trying to figure out how using a table storing Node will help me in figuring out how much initial memory can be allocated

regards
Sameer

Re: Self referencing composite datatype

From
Alban Hertroys
Date:
On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote:

> create table node as (
>  id integer primary key,
>  r integer, s integer,
>  children integer[] element references node
> );
>
> so you could download 9.3rc2 and experimant with it.
>
> Now (on <=9.2.x) you can create the table without FK
>
> create table node as (
>  id integer primary key,
>  r integer, s integer,
>  children integer[]
> );
>
> and check integrity by triggers.


Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child node.
That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since PG
9.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



Re: Self referencing composite datatype

From
Sergey Konoplev
Date:
On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys <haramrae@gmail.com> wrote:
> On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote:
>> create table node as (
>>  id integer primary key,
>>  r integer, s integer,
>>  children integer[]
>> );
>>
>> and check integrity by triggers.
>
>
> Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child
node.
> That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since
PG9. 

That particular moment I thought it was about graphs. Later OP
mentioned tree, so yes, it is better to use parent reference here.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com


Re: Self referencing composite datatype

From
Chris Travers
Date:



On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys <haramrae@gmail.com> wrote:
On Aug 8, 2013, at 4:11, Sergey Konoplev <gray.ru@gmail.com> wrote:

> create table node as (
>  id integer primary key,
>  r integer, s integer,
>  children integer[] element references node
> );
>
> so you could download 9.3rc2 and experimant with it.
>
> Now (on <=9.2.x) you can create the table without FK
>
> create table node as (
>  id integer primary key,
>  r integer, s integer,
>  children integer[]
> );
>
> and check integrity by triggers.


Or, instead of attempting to reference all child nodes from the parent, reference the parent node from each child node.
That's been supported in PG versions like forever and can be queried fairly efficiently using recursive CTE's since PG 9.

If you do this, have a position number, and use that for ordering.  You need some sort of ordinality here.

Best Wishes,
Chris Travers 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.