Thread: Passing relation metadata to Exec routine
Hi hackers!
--
While working on Pluggable TOAST [1] we found out that creation
of new relation with CREATE TABLE AS... or CREATE TABLE LIKE -
method
static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into)
does not receive any metadata from columns or tables used in query
(if any). It makes sense to pass not only column type and size, but
all other metadata - like attoptions,base relation OID (and, maybe,
reloptions), if the column from existing relation was used.
A good example is the creation of new relation from base one where
some other Toaster was assigned to a column - it seems reasonable
that the same column in new table must have the same Toaster assigned
as the base one. And we already have a couple of other practical uses
for the metadata passed along with column definitions.
Any thoughts or suggestions?
Nikita Malakhov <hukutoc@gmail.com> writes:
> While working on Pluggable TOAST [1] we found out that creation
> of new relation with CREATE TABLE AS... or CREATE TABLE LIKE -
> method
> static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into)
> does not receive any metadata from columns or tables used in query
> (if any). It makes sense to pass not only column type and size, but
> all other metadata - like attoptions,base relation OID (and, maybe,
> reloptions), if the column from existing relation was used.
I am very, very skeptical of the premise here.
CREATE TABLE AS creates a table based on the output of a query.
That query could involve arbitrarily complex processing -- joins,
grouping, what-have-you. I don't see how it makes sense to
consider the properties of the base table(s) in deciding how to
create the output table. I certainly do not think it'd be sane for
that to behave differently depending on how complicated the query is.
As for CREATE TABLE LIKE, the point of that is to create a table
by copying a *specified* set of properties of a reference table.
I don't think letting an access method copy some other properties
behind the user's back is a great idea. If you've got things that
it'd make sense to be able to inherit, let's discuss adding more
LIKE options to support that --- in which case the implementation
would presumably pass the data through in a non-back-door fashion.
regards, tom lane
Hi Tom!
Thank you for your feedback. I agree that for complex columns
created with joins, grouping, etc considering properties of the base
table does not make sense at all.
But for CREATE TABLE LIKE and simple columns that are inherited
from some existing relations - it does, if we consider some advanced
properties and from user's perspective - want our new table [columns]
to behave exactly as the base ones (in some ways like encryption,
storage, compression methods, etc). LIKE options is a good idea,
thank you, but when we CREATE TABLE AS - maybe, we take it
into account too?
I agree that passing these parameters in a backdoor fashion is not
very transparent and user-friendly, too.
On Tue, Dec 27, 2022 at 12:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nikita Malakhov <hukutoc@gmail.com> writes:
> While working on Pluggable TOAST [1] we found out that creation
> of new relation with CREATE TABLE AS... or CREATE TABLE LIKE -
> method
> static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into)
> does not receive any metadata from columns or tables used in query
> (if any). It makes sense to pass not only column type and size, but
> all other metadata - like attoptions,base relation OID (and, maybe,
> reloptions), if the column from existing relation was used.
I am very, very skeptical of the premise here.
CREATE TABLE AS creates a table based on the output of a query.
That query could involve arbitrarily complex processing -- joins,
grouping, what-have-you. I don't see how it makes sense to
consider the properties of the base table(s) in deciding how to
create the output table. I certainly do not think it'd be sane for
that to behave differently depending on how complicated the query is.
As for CREATE TABLE LIKE, the point of that is to create a table
by copying a *specified* set of properties of a reference table.
I don't think letting an access method copy some other properties
behind the user's back is a great idea. If you've got things that
it'd make sense to be able to inherit, let's discuss adding more
LIKE options to support that --- in which case the implementation
would presumably pass the data through in a non-back-door fashion.
regards, tom lane