BUG #5982: recursive type crashes postgres

On Fri, Apr 15, 2011 at 1:34 PM, Rikard Pavelic
<rikard.pavelic@zg.htnet.hr> wrote:

The following bug has been logged online:

Bug reference:      5982
Logged by:          Rikard Pavelic
Email address:      rikard.pavelic@zg.htnet.hr
PostgreSQL version: 9.1.alpha5
Operating system:   Windows XP SP3
Description:        recursive type crashes postgres
Details:

CREATE TYPE turtle AS
(
       name varchar
);
ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;

CREATE TABLE turtles
(
       id int,
       tortoise turtle
);

This is a duplicate: see Mar 28 thread 'Recursive containment of
composite types'. Was it decided whether to lock the functionality
down or fix it?

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

On Fri, Apr 15, 2011 at 1:34 PM, Rikard Pavelic
<rikard.pavelic@zg.htnet.hr> wrote:

CREATE TYPE turtle AS
(
name varchar
);
ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;

This is a duplicate: see Mar 28 thread 'Recursive containment of
composite types'. Was it decided whether to lock the functionality
down or fix it?

The former.

regression=# CREATE TYPE turtle AS
(
name varchar
);
CREATE TYPE
regression=# ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;
ERROR: composite type turtle cannot be made a member of itself
regression=#

regards, tom lane

On 15.4.2011 21:06, Tom Lane wrote:

The former.

regression=# CREATE TYPE turtle AS
(
name varchar
);
CREATE TYPE
regression=# ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;
ERROR: composite type turtle cannot be made a member of itself
regression=#

regards, tom lane

Todo item?

Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:

On 15.4.2011 21:06, Tom Lane wrote:

The former.

regression=# CREATE TYPE turtle AS
(
name varchar
);
CREATE TYPE
regression=# ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;
ERROR: composite type turtle cannot be made a member of itself
regression=#

Todo item?

I haven't seen anything which seems like a reasonable use case yet,
myself. If you were *actually* tracking turtles and their
offspring, that would be a completely worthless data structure. Is
there really a case where a reference to the ID of an object of like
type isn't a better solution?

-Kevin

On 15.4.2011 22:49, Kevin Grittner wrote:

Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:

On 15.4.2011 21:06, Tom Lane wrote:

The former.

regression=# CREATE TYPE turtle AS
(
name varchar
);
CREATE TYPE
regression=# ALTER TYPE turtle ADD ATTRIBUTE offspring turtle;
ERROR: composite type turtle cannot be made a member of itself
regression=#

Todo item?

I haven't seen anything which seems like a reasonable use case yet,
myself. If you were *actually* tracking turtles and their
offspring, that would be a completely worthless data structure. Is
there really a case where a reference to the ID of an object of like
type isn't a better solution?

-Kevin

I'm trying to map application and database domain as close as possible.
So it's not that I have an use case, but have a mismatch which cannot be mapped.
This feature would reduce object-relational impedance mismatch in DDD,
so I think it is worth an Todo item.
Is this design strange/stupid? Probably.
But, currently it behaves as struct. It would be nice if it behaved as class
(when attribute is nullable).

Regards,
Rikard

On Fri, Apr 15, 2011 at 3:49 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

I haven't seen anything which seems like a reasonable use case yet,
myself.  If you were *actually* tracking turtles and their
offspring, that would be a completely worthless data structure.  Is
there really a case where a reference to the ID of an object of like
type isn't a better solution?

There are lots of use cases for this. I use composite types to
marshal data to the client all the time, and recursive structures are
fairly common in many classic problems. Recursive composites fit the
bill perfectly.

merlin

On 04/15/11 2:04 PM, Rikard Pavelic wrote:

This feature would reduce object-relational impedance mismatch in DDD,

objects aren't relational. deal with it. OOPS may be a reasonable
methodology for coding, however its purely hierarchical, and doesn't map
at all well to relational calculus. round peg, square hole. There
are tools in the toolbox other than hammers, you know.

Merlin Moncure <mmoncure@gmail.com> wrote:

There are lots of use cases for this. I use composite types to
marshal data to the client all the time, and recursive structures
are fairly common in many classic problems. Recursive composites
fit the bill perfectly.

I'm trying to get my head around why SQL composite types are a good
way to marshal complex object graphs with recursion. I can see
where it could be done, I'm still not convinced that it's better
than SQL passing out data in tabular form with relationship
established by matching values. In other words, when is it a good
idea to do the relation to object mapping in the database engine?

-Kevin

On Fri, Apr 15, 2011 at 4:27 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

Merlin Moncure <mmoncure@gmail.com> wrote:

There are lots of use cases for this.  I use composite types to
marshal data to the client all the time, and recursive structures
are fairly common in many classic problems.  Recursive composites
fit the bill perfectly.

I'm trying to get my head around why SQL composite types are a good
way to marshal complex object graphs with recursion.  I can see
where it could be done, I'm still not convinced that it's better
than SQL passing out data in tabular form with relationship
established by matching values.  In other words, when is it a good
idea to do the relation to object mapping in the database engine?

You can certainly do that. however that requires the client to put
take the data and immediately put it in relational-ish data structures
so you can browse the data properly. That's a popular approach,
albeit expensive and error prone. If the database sends it back to
you constructed, you can just stupldly iterate over the returned set
and process it as you go (you could in fact stream the data directly
off the protocol of the client library supports it).

arrays, composites, etc. are just ways of setting up ad hoc structures
for passing. they serve the same purpose (at least, they way I use
them) as xml or json documents getting ripped directly in and out of
the database. maybe it's a good idea to nest data that way going and
out, and maybe it isn't, but it's fast and effective. xml and json
solve the problem but introduce a completely difference set of
headaches because they are not tightly coupled to the database...and
slow.

Consider we also have to send data to the database. I can recursively
wrap up data in the client using libpqtypes, fire it to a receiving
function which unnests it and processes it. This is a couple of
orders of magnitude faster than streaming it in over multiple queries.
I could for example, send in a entire bill of materials assembly in a
single query. I don't consider that hacky, in fact I think a lot of
classic SQL approaches are in fact hacks around not being able to do
things that way.

merlin

Merlin Moncure <mmoncure@gmail.com> wrote:

Consider we also have to send data to the database. I can
recursively wrap up data in the client using libpqtypes, fire it
to a receiving function which unnests it and processes it. This
is a couple of orders of magnitude faster than streaming it in
over multiple queries.

I'll think on that. I hadn't really considered creating an ORM in
the database engine itself, which seems to me to be what you're
describing, but I guess it couldn't be worse than having an ORM on
the other end of the wire.

Is that a hard sell to your application programmers, or do you wear
both hats?

-Kevin

On 4/15/2011 6:14 PM, Kevin Grittner wrote:

Merlin Moncure<mmoncure@gmail.com> wrote:

Consider we also have to send data to the database. I can
recursively wrap up data in the client using libpqtypes, fire it
to a receiving function which unnests it and processes it. This
is a couple of orders of magnitude faster than streaming it in
over multiple queries.

I'll think on that. I hadn't really considered creating an ORM in
the database engine itself, which seems to me to be what you're
describing, but I guess it couldn't be worse than having an ORM on
the other end of the wire.

Is that a hard sell to your application programmers, or do you wear
both hats?

libpqtypes is very easy to use, small learning curve. Should be easy for
someone with libpq experience.

Merlin is describing loop,pack,exec vs. loop,exec where the former packs an
array of parameters and performs one transaction, and the latter must execute
multiple transactions one at a time. If you take the params of an insert and
create a composite out of it, you can wrap the insert into a function that takes
an array of that composite. You are not creating any new bindings, translations
or encodings, just leveraging existing functionality a little differently.

--
Andrew Chernow
eSilo, LLC
global backup
http://www.esilo.com/