logical column ordering - Mailing list pgsql-hackers
From | Alvaro Herrera |
---|---|
Subject | logical column ordering |
Date | |
Msg-id | 20141209174146.GP1768@alvh.no-ip.org Whole thread Raw |
Responses |
Re: logical column ordering
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
Re: logical column ordering (Andres Freund <andres@anarazel.de>) Re: logical column ordering (Peter Eisentraut <peter_e@gmx.net>) |
List | pgsql-hackers |
So I've been updating my very old patch to allow logical and physical column reordering. Here's a WIP first cut for examination. There are plenty of rough edges here; most importantly there is no UI at all for column reordering other than direct UPDATEs of pg_attribute, which most likely falls afoul of cache invalidation problems. For now I'm focusing on correct processing when columns are moved logically; I haven't yet started to see how to move columns physically, but I think that part is much more localized than the logical one. Just as a reminder, this effort is an implementation of ideas that have been discussed previously; in particular, see these threads: http://www.postgresql.org/message-id/20414.1166719407@sss.pgh.pa.us (2006) http://www.postgresql.org/message-id/6843.1172126270@sss.pgh.pa.us (2007) http://www.postgresql.org/message-id/23035.1227659434@sss.pgh.pa.us (2008) To recap, this is based on the idea of having three numbers for each attribute rather than a single attnum; the first of these is attnum (a number that uniquely identifies an attribute since its inception and may or may not have any relationship to its storage position and the place it expands to through user interaction). The second is attphysnum, which indicates where it is stored in the physical structure. The third is attlognum, which indicates where it expands in "*", where must its values be placed in COPY or VALUES lists, etc --- the logical position as the user sees it. The first thing where this matters is tuple descriptor expansion in parse analysis; at this stage, things such as "*" (in "select *") are turned into a target list, which must be sorted according to attlognum. To achieve this I added a new routine to tupledescs, TupleDescGetSortedAttrs() which computes a new Attribute array and caches it in the TupleDesc for later uses; this array points to the same elements in the normal attribute list but is order by attlognum. Additionally there are a number of places that iterate on such target lists and use the iterator as the attribute number; those were modified to have a separate attribute number as attnum within the loop. Another place that needs tweaking is heapam.c, which must construct a physical tuple from Datum/nulls arrays (heap_form_tuple). In some cases the input arrays are sorted in logical column order. I have opted to add a flag that indicates whether the array is in logical order; if it is the routines compute the correct physical order. (Actually as I mentioned above I still haven't made any effort to make sure they work in the case that attnum differs from attphysnum, but this should be reasonably contained changes.) The part where I stopped just before sending the current state is this error message: alvherre=# select * from quux where (a,c) in ( select a,c from quux ); select * from quux where (a,c) in ( select a,c from quux ); ERROR: failed to find unique expression in subplan tlist I'm going to see about it while I get feedback on the rest of this patch; in particular, extra test cases that fail to work when columns have been moved around are welcome, so that I can add them to the regress test. What I have now is the basics I'm building as I go along. The regression tests show examples of some logical column renumbering (which can be done after the table already contains some data) but none of physical column renumbering (which can only be done when the table is completely empty.) My hunch is that the sample foo, bar, baz, quux tables should present plenty of opportunities to display brokenness in the planner and executor. PS: Phil Currier allegedly had a patch back in 2007-2008 that did this, or something very similar ... though he never posted a single bit of it, and then he vanished without a trace. If he's still available it would be nice to see his WIP patch, even if outdated, as it might serve as inspiration and let us know what other places need tweaking. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
pgsql-hackers by date: