Thread: Re: Complexity of contrib types
Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > Doing complex stuff like indexing with contrib stuff is tricky, and one > reason we want to move stuff out of there as it becomes popular. It is > just too hard for someone not experienced with the code to implement. > Add to this the fact that the oid at the time of contrib installation > will change every time you install it, so it is even harder/impossible > to automate. We should develop (or at least prominently promote _and_ document) some kind of file/package format or tool (maybe like illustra datablades), that would standardize the layout of contrib types. Also, the need to manually get oids is a real show-stopper. A short-time solution would be to develop functions that return these oids, like get_proc_oid_for(proc_name,arg1_type,arg2_type,...). The real solution would of course be extending the (Postgre)SQL language to find the OIDs automatically, like Oracle currently does for its COMMENT statement, an equivalent of which could be used in PostgreSQL to insert values in pg_description on the fly. Hannu
> Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > > > Doing complex stuff like indexing with contrib stuff is tricky, and one > > reason we want to move stuff out of there as it becomes popular. It is > > just too hard for someone not experienced with the code to implement. > > Add to this the fact that the oid at the time of contrib installation > > will change every time you install it, so it is even harder/impossible > > to automate. > > We should develop (or at least prominently promote _and_ document) some > kind of file/package format or tool (maybe like illustra datablades), > that would standardize the layout of contrib types. > > Also, the need to manually get oids is a real show-stopper. > A short-time solution would be to develop functions that return these > oids, > like get_proc_oid_for(proc_name,arg1_type,arg2_type,...). Can't they SELECT from pg_proc? > > The real solution would of course be extending the (Postgre)SQL language > to find the OIDs automatically, like Oracle currently does for its > COMMENT > statement, an equivalent of which could be used in PostgreSQL to insert > values in pg_description on the fly. We return oid's as part of an INSERT. Is that what you meant? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Hannu Krosing <hannu@trust.ee> writes:
> We should develop (or at least prominently promote _and_ document) some
> kind of file/package format or tool (maybe like illustra datablades),
> that would standardize the layout of contrib types.
> Also, the need to manually get oids is a real show-stopper.
Yes. I've been thinking off and on about some homegrown data types
(not general-purpose enough to be worthwhile even as contrib material).
But the admin overhead seems like a real pain. If we want to promote
Postgres' type system as a major benefit, we ought to work harder at
making it easy to add locally-defined types.
regards, tom lane
Bruce Momjian wrote: > > > Bruce Momjian <maillist@candle.pha.pa.us> wrote: > > > > > > Doing complex stuff like indexing with contrib stuff is tricky, and one > > > reason we want to move stuff out of there as it becomes popular. It is > > > just too hard for someone not experienced with the code to implement. > > > Add to this the fact that the oid at the time of contrib installation > > > will change every time you install it, so it is even harder/impossible > > > to automate. > > > > We should develop (or at least prominently promote _and_ document) some > > kind of file/package format or tool (maybe like illustra datablades), > > that would standardize the layout of contrib types. > > > > Also, the need to manually get oids is a real show-stopper. > > A short-time solution would be to develop functions that return these > > oids, > > like get_proc_oid_for(proc_name,arg1_type,arg2_type,...). > > Can't they SELECT from pg_proc? Making it a function would probably make the type-addition script easier. > > > > The real solution would of course be extending the (Postgre)SQL language > > to find the OIDs automatically, like Oracle currently does for its > > COMMENT > > statement, an equivalent of which could be used in PostgreSQL to insert > > values in pg_description on the fly. > > We return oid's as part of an INSERT. Is that what you meant? It is very hard (probably impossible) to use them from a psql script. If I remember the syntax right (have'nt used Oracle for >=2 years), I could do: COMMENT "this is a nice table" ON TABLE nice_table; COMMENT "this is an unnecessary field from a nice table" ON FIELD nice_table.unnecessary_field; Of course, to fully support it we would need a much improved foreign key support, so that we could set the ON DELETE CASCADE for the commented on tables, and do it so that the foreign key can references _any_ table ;). If we could manage that, we could really call PostgreSQL an OO database. Hannu
> > Can't they SELECT from pg_proc?
>
> Making it a function would probably make the type-addition script
> easier.
I guess, but much less flexible.
>
> > >
> > > The real solution would of course be extending the (Postgre)SQL language
> > > to find the OIDs automatically, like Oracle currently does for its
> > > COMMENT
> > > statement, an equivalent of which could be used in PostgreSQL to insert
> > > values in pg_description on the fly.
> >
> > We return oid's as part of an INSERT. Is that what you meant?
>
> It is very hard (probably impossible) to use them from a psql script.
>
> If I remember the syntax right (have'nt used Oracle for >=2 years),
> I could do:
>
> COMMENT "this is a nice table" ON TABLE nice_table;
> COMMENT "this is an unnecessary field from a nice table"
> ON FIELD nice_table.unnecessary_field;
>
> Of course, to fully support it we would need a much improved foreign
> key support, so that we could set the ON DELETE CASCADE for the
> commented on tables, and do it so that the foreign key can references
> _any_ table ;).
>
> If we could manage that, we could really call PostgreSQL an OO database.
We could create a function that returned the previously inserted oid,
and use that in the next query.
insert into test values (4);
update test3 set val = lastoid();
Just remember the lastoid inserted in the backend code. Seems easy. Do
you want it added to the TODO list.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Bruce Momjian wrote: > > > We could create a function that returned the previously inserted oid, > and use that in the next query. > > insert into test values (4); > update test3 set val = lastoid(); > > Just remember the lastoid inserted in the backend code. Seems easy. Do > you want it added to the TODO list. Yes. It could be used in several places. But I'm currently not aware about the future of oid's 'at the large'. I have understood that we are on a way of getting rid of OIDs for non-system tables (and having to re-implement them using triggers and sequences where/when needed)? Hannu
> Bruce Momjian wrote: > > > > > > We could create a function that returned the previously inserted oid, > > and use that in the next query. > > > > insert into test values (4); > > update test3 set val = lastoid(); > > > > Just remember the lastoid inserted in the backend code. Seems easy. Do > > you want it added to the TODO list. > > Yes. It could be used in several places. > > But I'm currently not aware about the future of oid's 'at the large'. > > I have understood that we are on a way of getting rid of OIDs for > non-system tables (and having to re-implement them using triggers > and sequences where/when needed)? OIDs are in SQL-92(?), so we will have to keep them. I believe we may someday make them optional on user tables. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)