Re: System column support for partitioned tables using heap

> The column list for a partitioned table must be fixed at the time it is
> created, but we do not know what partitions might be added in the
> future, and thus we don't know whether they will have an xmax column.

Right, seeing what you're meaning there. It's fantastic that a partition
might be an FDW to a system that has no concept at all of anything like a
"system column", or something with an alternative AM to heap that has a
different set of system columns. That flexibility in partitions is super
valuable. I'd love to be able to convert old partitions into column stores,
for example. (I think that Citus offers that feature now.)

I guess if anyone ever felt it was worth the effort, maybe whatever checks
are done at attach-partition time for the column list could also enforce
meta/system columns. If missing, a shimming mechanism would be pretty
necessary.

Sounds like a lot of work for not much gain, at least in this narrow case.

Thanks again for answering.

On Tue, Jul 19, 2022 at 6:43 PM Morris de Oryx <morrisdeoryx(at)gmail(dot)com>
wrote:

> > What is motivating you to want to see the xmax value here? It's not an
> > unreasonable thing to want to do, IMHO, but it's a little bit niche so
> > I'm just curious what the motivation is.
>
> Yeah, I figured it was niche when I saw so little mention of the issue.
>
> My reason for xmax() in the result is to break down the affected rows
> count into an insert count, and a modified estimate. Not super critical,
> but helpful. I've built out some simple custom logging table in out system
> for this kind of detail, and folks have been wanting to break down rows
> submitted, rows inserted, and rows updated a bit better. Rows submitted is
> easy and rows inserted is too...update is an estimate as I'm not using
> anything fancy with xmax() to sort out what exactly happened.
>
> For clarification, we're not using an ORM, and may need to support
> straggling clients, so our push cycle works like this:
>
> * Create a view with the fields expected in the insert. I figured I'd use
> CREATE VIEW instead of CREATE TYPE as then I can quickly check out the
> details against live data, and I still get a custom compound type.
>
> * Write a function that accepts an array of view_name_type. I *love* Postgres'
> typing system, It has spoiled me forever. Can't submit badly formatted
> objects from the client, they're rejected automatically.
>
> * Write a client-side routine to package data as an array and push it into
> the insert handling function. The function unnests the array, and then the
> actual insert code draws from the unpacked values. If I need to extend the
> table, I can add a new function that knows about the revised fields, and
> revise (when necessary) earlier supported formats to map to new
> types/columns/defaults.
>
> There are few CTEs in there, including one that does the main insert and
> returns the xmax(). That lets me distinguish xmax = 0 (insert) from xmax <>
> 0 (not an insert).
>
> > I do agree with you that it would be nice if this worked better than
> > it does, but I don't really know exactly how to make that happen. The
> > column list for a partitioned table must be fixed at the time it is
> > created, but we do not know what partitions might be added in the
> > future, and thus we don't know whether they will have an xmax column.
> > I guess we could have tried to work things out so that a 0 value would
> > be passed up from children that lack an xmax column, and that would
> > allow the parent to have such a column, but I don't feel too bad that
> > we didn't do that ... should I?
>
> You should never feel bad about anything ;-) You and others on that thread
> contribute so much that I'm getting value out of.
>
> I had it in mind that it would be nice to have some kind of
> catalog/abstraction that would make it possible to interrogate what system
> columns are available on a table/partition based on access method. In my
> vague notion, that might make some of the other ideas from that thread,
> such as index-oriented stores with quite different physical layouts, easier
> to implement. But, it's all free when you aren't the one who can write the
> code.
>
> I've switched the partition-based tables back to returning * on the insert
> CTE, and then aggregating that to add to a log table and the client result.
> It's fine. A rich result summary would be very nice. As in rows
> added/modified/deleted on whatever table(s). If anyone ever decides to
> implement such a structure for MERGE, it would be nice to see it
> retrofitted to the other data modification commands where RETURNING works.
>
> On Tue, Jul 19, 2022 at 6:13 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> On Sun, Jul 17, 2022 at 9:04 PM Morris de Oryx <morrisdeoryx(at)gmail(dot)com>
>> wrote:
>> > This fails on a partitioned table because xmax() may not exist. In
>> fact, it does exist in all of those tables, but the system doesn't know how
>> to guarantee that. I know which tables are partitioned, and can downgrade
>> the result on partitioned tables to the count(*) I've been using to date.
>> But now I'm wondering if working with xmax() like this is a poor idea going
>> forward. I don't want to lean on a feature/behavior that's likely to
>> change. For example, I noticed the other day that MERGE does not support
>> RETURNING.
>> >
>> > I'd appreciate any insight or advice you can offer.
>>
>> What is motivating you to want to see the xmax value here? It's not an
>> unreasonable thing to want to do, IMHO, but it's a little bit niche so
>> I'm just curious what the motivation is.
>>
>> I do agree with you that it would be nice if this worked better than
>> it does, but I don't really know exactly how to make that happen. The
>> column list for a partitioned table must be fixed at the time it is
>> created, but we do not know what partitions might be added in the
>> future, and thus we don't know whether they will have an xmax column.
>> I guess we could have tried to work things out so that a 0 value would
>> be passed up from children that lack an xmax column, and that would
>> allow the parent to have such a column, but I don't feel too bad that
>> we didn't do that ... should I?
>>
>> --
>> Robert Haas
>> EDB: http://www.enterprisedb.com
>>
>