Thread: BUG #5982: recursive type crashes postgres
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
);
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: =A0 =A0 =A05982 > Logged by: =A0 =A0 =A0 =A0 =A0Rikard Pavelic > Email address: =A0 =A0 =A0rikard.pavelic@zg.htnet.hr > PostgreSQL version: 9.1.alpha5 > Operating system: =A0 Windows XP SP3 > Description: =A0 =A0 =A0 =A0recursive type crashes postgres > Details: > > CREATE TYPE turtle AS > ( > =A0 =A0 =A0 =A0name varchar > ); > ALTER TYPE turtle ADD ATTRIBUTE offspring turtle; > > CREATE TABLE turtles > ( > =A0 =A0 =A0 =A0id int, > =A0 =A0 =A0 =A0tortoise 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. =A0If you were *actually* tracking turtles and their > offspring, that would be a completely worthless data structure. =A0Is > 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. =A0I use composite types to >> marshal data to the client all the time, and recursive structures >> are fairly common in many classic problems. =A0Recursive 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. =A0I 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. =A0In 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/