Subject: VLDB Features

From: Simon Riggs <[email-masked]>
Date: Tue, 11 Dec 2007 11:11:48 +0000

I'm starting work on next projects for 8.4.

Many applications have the need to store very large data volumes for
both archival and analysis. The analytic databases are commonly known as
Data Warehouses, though there isn't a common term for large archival
data stores. The use cases for those can often be blurred and many
people see those as only one use case. My initial interest is in the
large archival data stores.

One of the main issues to be faced is simply data maintenance and
management. Loading, deleting, vacuuming data all takes time. Those
issues relate mainly to the size of the data store rather than any
particular workload, so I'm calling that set of required features "Very
Large Database" (or VLDB) features.

VLDB Features I'm expecting to work on are
- Read Only Tables/WORM tables
- Advanced Partitioning
- Compression
plus related performance features

Details of those will be covered in separate mails over next few weeks
and months. So just to let everybody know that's where I'm headed, so
you see the big picture with me.

I'll be working on other projects as well, many of which I've listed
here: http://developer.postgresql.org/index.php/Simon_Riggs%
27_Development_Projects  I expect the list is too long to complete for
8.4, but I'm allowing for various issues arising during development.

So specific discussion on other mails as they arrive, please.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

---

From: Josh Berkus <[email-masked]>
Date: Tue, 11 Dec 2007 10:53:11 -0800

Simon.

> plus related performance features

Just so you don't lose sight of it, one of the biggest VLDB features we're 
missing is fault-tolerant bulk load.  Unfortunately, I don't know anyone 
who's working on it.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Hannu Krosing <[email-masked]>
Date: Tue, 11 Dec 2007 23:08:01 +0200

Ühel kenal päeval, T, 2007-12-11 kell 10:53, kirjutas Josh Berkus:
> missing is fault-tolerant bulk load. 

What do you mean by fault-tolerant here ?

Just 

COPY ... WITH ERRORS TO ...

or something more advanced, like bulkload which can be continued after
crash ?

--------------
Hannu

---

From: Simon Riggs <[email-masked]>
Date: Tue, 11 Dec 2007 21:31:17 +0000

> missing is fault-tolerant bulk load.  Unfortunately, I don't know anyone 

Not lost sight of it; I have a design, but I have to prioritise also.

---

From: Josh Berkus <[email-masked]>
Date: Tue, 11 Dec 2007 15:31:25 -0800

Hannu,

> COPY ... WITH ERRORS TO ...

Yeah, that's a start.

> or something more advanced, like bulkload which can be continued after

Well, we could also use a loader which automatically parallelized, but that 
functionality can be done at the middleware level.  WITH ERRORS is the 
most critical part.

Here's the other VLDB features we're missing:

Parallel Query
Windowing Functions
Parallel Index Build (not sure how this works exactly, but it speeds Oracle 
up considerably)
On-disk Bitmap Index (anyone game to finish GP patch?)

Simon, we should start a VLDB-Postgres developer wiki page.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Neil Conway <[email-masked]>
Date: Tue, 11 Dec 2007 15:41:01 -0800

> missing is fault-tolerant bulk load.

I actually had to cook up a version of this for Truviso recently. I'll
take a look at submitting a cleaned-up implementation for 8.4.

-Neil

---

From: Simon Riggs <[email-masked]>
Date: Tue, 11 Dec 2007 23:57:23 +0000

> On-disk Bitmap Index (anyone game to finish GP patch?)

I would call those VLDB Data Warehousing features to differentiate
between that and the use of VLDBs for other purposes.

I'd add Materialized View support in the planner, as well as saying its
more important than parallel query, IMHO. MVs are to DW what indexes are
to OLTP. It's the same as indexes vs. seqscan; you can speed up the seq
scan or you can avoid it. Brute force is cool, but being smarter is even
better. 

The reason they don't normally show up high on anybody's feature list is
that the TPC benchmarks specifically disallow them, which as I once
observed is very good support for them being a useful feature in
practice. (Oracle originally brought out MV support as a way of
improving their TPC scores at a time when Teradata was wiping the floor
with parallel query implementation).

---

From: Greg Smith <[email-masked]>
Date: Tue, 11 Dec 2007 19:11:19 -0500 (EST)

> missing is fault-tolerant bulk load.  Unfortunately, I don't know anyone

I'm curious what you feel is missing that pgloader doesn't fill that 
requirement:  http://pgfoundry.org/projects/pgloader/

--
* Greg Smith [email-masked] http://www.gregsmith.com Baltimore, MD

---

From: Josh Berkus <[email-masked]>
Date: Tue, 11 Dec 2007 20:36:06 -0800

Greg,

> requirement:  http://pgfoundry.org/projects/pgloader/

Because pgloader is implemented in middleware, it carries a very high overhead 
if you have bad rows.  As little as 1% bad rows will slow down loading by 20% 
due to retries.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Dimitri Fontaine <[email-masked]>
Date: Wed, 12 Dec 2007 11:19:02 +0100

Hi,

Le mercredi 12 décembre 2007, Josh Berkus a écrit :
> Because pgloader is implemented in middleware, it carries a very high
> overhead if you have bad rows.  As little as 1% bad rows will slow down
> loading by 20% due to retries.

Not that much, in fact, I'd say.
pgloader allows its user to configure how large a COPY buffer to use (global 
parameter as of now, could easily be a per-section configuration knob, just 
didn't see any need for this yet).
It's the 'copy_every' parameter as seen on the man page here:
  http://pgloader.projects.postgresql.org/#toc4

pgloader will obviously prepare a in-memory buffer of copy_every tuples to 
give to COPY, and in case of error will cut it and retry. Classic dichotomy 
approach, from initial implementation by Jan Wieck.

So you can easily balance the error recovery costs against the COPY bulk size.

Note also that the overall loading time with pgloader is not scaling the same 
as the COPY buffer size, the optimal choice depends on the dataset --- and 
the data massaging pgloader has to make on it ---, and I've experienced best 
results with 10000 and 15000 tuples buffers so far.

FYI, now the pgloader topic is on the table, the next items I think I'm gonna 
develop for it are configurable behavior on errors tuples (load to another 
table when pk error, e.g.), and some limited ddl-partioning support.

I'm playing with the idea for pgloader to be able to read some partitioning 
schemes (parsing CHECK constraint on inherited tables) and load directly into 
the right partitions.
That would of course be done only when configured this way, and if constraints 
are misread it would only result in a lot more rejected rows than expected, 
and you still can retry using your insert trigger instead of pgloader buggy 
smartness.

Comments welcome, regards,
-- 
dim

---

From: Simon Riggs <[email-masked]>
Date: Wed, 12 Dec 2007 15:43:56 +0000

> Simon, we should start a VLDB-Postgres developer wiki page.

http://developer.postgresql.org/index.php/DataWarehousing

---

From: Markus Schiltknecht <[email-masked]>
Date: Wed, 12 Dec 2007 19:23:49 +0100

Hi,

> Here's the other VLDB features we're missing:

Uh.. this only makes sense in a distributed database, no? I've thought 
about parallel querying on top of Postgres-R. Does it make sense 
implementing some form of parallel querying apart from the distribution 
or replication engine?

> Windowing Functions

Isn't Gavin Sherry working on this? Haven't read anything from him lately...

> Parallel Index Build (not sure how this works exactly, but it speeds Oracle 

Sounds interesting *turs-away-to-google*

> On-disk Bitmap Index (anyone game to finish GP patch?)

Anybody having an idea of what's missing there (besides good use cases, 
which some people doubt)? Again: Gavin?

> Simon, we should start a VLDB-Postgres developer wiki page.

Thanks, Simon, wiki page looks good!

Regards

Markus

---

From: Josh Berkus <[email-masked]>
Date: Wed, 12 Dec 2007 10:55:53 -0800

Markus,

> implementing some form of parallel querying apart from the distribution

Sure.  Imagine you have a 5TB database on a machine with 8 cores and only one 
concurrent user.  You'd like to have 1 core doing I/O, and say 4-5 cores 
dividing the scan and join processing into 4-5 chunks.

I'd say implementing a separate I/O worker would be the first step towards 
this; if we could avoid doing I/O in the same process/thread where we're 
doing row parsing it would speed up large scans by 100%.  I know Oracle does 
this, and their large-table-I/O is 30-40% faster than ours despite having 
less efficient storage.

Maybe Greenplum or EnterpriseDB will contribute something.  ;-)

> Isn't Gavin Sherry working on this? Haven't read anything from him

Me neither.  Swallowed by Greenplum and France.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Markus Schiltknecht <[email-masked]>
Date: Wed, 12 Dec 2007 20:26:16 +0100

Hi Josh,

> dividing the scan and join processing into 4-5 chunks.

Ah, right, thank for enlightenment. Heck, I'm definitely too focused on 
replication and distributed databases :-)

However, there's certainly a great deal of an intersection between 
parallel processing on different machines and parallel processing on 
multiple CPUs - especially considering NUMA architecture. 
*comes-to-think-again*...

> Me neither.  Swallowed by Greenplum and France.

Hm.. good for him, I guess!

Regards

Markus

---

From: Gavin Sherry <[email-masked]>
Date: Wed, 12 Dec 2007 21:46:16 +0100

> Hm.. good for him, I guess!

Yes, I'm around -- just extremely busy with a big release at Greenplum as 
well as other Real Life stuff.

Thanks,

Gavin

---

From: "Joshua D. Drake" <[email-masked]>
Date: Wed, 12 Dec 2007 12:50:16 -0800

> Greenplum as well as other Real Life stuff.

For those of us here who have no idea what you are talking about can
you define what "Real Life" is like?

Joshua D. Drake

- -- 
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ 
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

---

From: Gregory Stark <[email-masked]>
Date: Thu, 13 Dec 2007 00:21:58 +0000

>> implementing some form of parallel querying apart from the distribution

Yes, but not for the reasons Josh describes.

> this, and their large-table-I/O is 30-40% faster than ours despite having 

Oracle is using Direct I/O so they need the reader and writer threads to avoid
blocking on i/o all the time. We count on the OS doing readahead and buffering
our writes so we don't have to. Direct I/O and needing some way to do
asynchronous writes and reads are directly tied.

Where Parallel query is useful is when you have queries that involve a
substantial amount of cpu resources, especially if you have a very fast I/O
system which can saturate the bandwidth to a single cpu.

So for example if you have a merge join which requires sorting both sides of
the query you could easily have subprocesses handle those sorts allowing you
to bring two processors to bear on the problem instead of being limited to a
single processor.

On Oracle Parallel Query goes great with partitioned tables. Their query
planner will almost always turn the partition scans into parallel scans and
use separate processors to scan different partitions. 

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

---

From: Markus Schiltknecht <[email-masked]>
Date: Thu, 13 Dec 2007 16:21:04 +0100

Hello Gregory,

> asynchronous writes and reads are directly tied.

Yeah, except in cases where we can tell ahead non-sequential reads. 
Which admittedly doesn't come up too frequently and can probably be 
handled with posix_fadvice - as you are currently testing.

> system which can saturate the bandwidth to a single cpu.

Full ACK, the very same applies to parallel querying on shared-nothing 
clusters. Those can help if the bandwidth to all processing cores 
together becomes the bottleneck (and the resulting data is relatively 
small compared to the input data).

For example, Sun's UltraSparc T2 features only 8 PCIe lanes for those 8 
cores, so you end up with 250 MiB/sec per core or about 32 MiB/sec per 
thread on average. To be fair: their 10 Gig Ethernet ports don't go via 
PCIe, so you get an additional 2x 1 GiB/sec for the complete chip. And 
memory bandwidth looks a lot better: Sun claims 60+ GiB/sec, leaving 
almost 8 GiB/sec per core or 1 GiB/sec per thread.

If my calculations for Intel are correct, a Quad Xeon with a 1.33 GHz 
FSB has around 21 GiB/sec throughput to main memory, giving 5 GiB/sec 
per core. (Why are these numbers so hard to find? It looks like Intel 
deliberately obfuscates them with FSB MHz or Giga-transactions per sec 
and the like.)

Regards

Markus

---

From: Hannu Krosing <[email-masked]>
Date: Fri, 14 Dec 2007 14:48:50 +0200

> take a look at submitting a cleaned-up implementation for 8.4.

How did you do it ?

Did you enchance COPY command or was it something completely new ?

-----------
Hannu

---

From: Neil Conway <[email-masked]>
Date: Fri, 14 Dec 2007 11:27:03 -0800

> Did you enchance COPY command or was it something completely new ?

By modifying COPY: COPY IGNORE ERRORS or some such would instruct COPY
to drop (and log) rows that contain malformed data. That is, rows with
too many or too few columns, rows that result in constraint violations,
and rows containing columns where the data type's input function raises
an error. The last case is the only thing that would be a bit tricky to
implement, I think: you could use PG_TRY() around the InputFunctionCall,
but I guess you'd need a subtransaction to ensure that you reset your
state correctly after catching an error.

-Neil

---

From: Andrew Dunstan <[email-masked]>
Date: Fri, 14 Dec 2007 14:39:49 -0500

> state correctly after catching an error.

Ideally I think you would put the failing input line in another table, 
or maybe another file. If a table, it would probably have to be as bytea.

cheers

andrew

---

From: Tom Lane <[email-masked]>
Date: Fri, 14 Dec 2007 18:22:41 -0500

> state correctly after catching an error.

Yeah.  It's the subtransaction per row that's daunting --- not only the
cycles spent for that, but the ensuing limitation to 4G rows imported
per COPY.

If we could somehow only do a subtransaction per failure, things would
be much better, but I don't see how.

			regards, tom lane

---

From: Neil Conway <[email-masked]>
Date: Fri, 14 Dec 2007 16:18:50 -0800

> be much better, but I don't see how.

One approach would be to essentially implement the pg_bulkloader
approach inside the backend. That is, begin by doing a subtransaction
for every k rows (with k = 1000, say). If you get any errors, then
either repeat the process with k/2 until you locate the individual
row(s) causing the trouble, or perhaps just immediately switch to k = 1.
Fairly ugly though, and would be quite slow for data sets with a high
proportion of erroneous data.

Another approach would be to distinguish between errors that require a
subtransaction to recover to a consistent state, and less serious errors
that don't have this requirement (e.g. invalid input to a data type
input function). If all the errors that we want to tolerate during a
bulk load fall into the latter category, we can do without
subtransactions.

-Neil

---

From: Tom Lane <[email-masked]>
Date: Fri, 14 Dec 2007 20:03:56 -0500

> proportion of erroneous data.

You could make it self-tuning, perhaps: initially, or after an error,
set k = 1, and increase k after a successful set of rows.

> bulk load fall into the latter category, we can do without

I think such an approach is doomed to hopeless unreliability.  There is
no concept of an error that doesn't require a transaction abort in the
system now, and that doesn't seem to me like something that can be
successfully bolted on after the fact.  Also, there's a lot of
bookkeeping (eg buffer pins) that has to be cleaned up regardless of the
exact nature of the error, and all those mechanisms are hung off
transactions.

			regards, tom lane

---

From: Trent Shipley <[email-masked]>
Date: Fri, 14 Dec 2007 18:30:16 -0700

> cycles spent for that, but the ensuing limitation to 4G rows imported

You could extend the COPY FROM syntax with a COMMIT EVERY n clause.  This 
would help with the 4G subtransaction limit.  The cost to the ETL process is 
that a simple rollback would not be guaranteed send the process back to it's 
initial state.  There are easy ways to deal with the rollback issue though.  

A {NO} RETRY {USING algorithm} clause might be useful.   If the NO RETRY 
option is selected then the COPY FROM can run without subtransactions and in 
excess of the 4G per transaction limit.  NO RETRY should be the default since 
it preserves the legacy behavior of COPY FROM.

You could have an EXCEPTIONS TO {filename|STDERR} clause. I would not give the 
option of sending exceptions to a table since they are presumably malformed, 
otherwise they would not be exceptions.  (Users should re-process exception 
files if they want an if good then table a else exception to table b ...)

EXCEPTIONS TO and NO RETRY would be mutually exclusive.

---

From: Josh Berkus <[email-masked]>
Date: Fri, 14 Dec 2007 20:34:57 -0800

Tom,

> exact nature of the error, and all those mechanisms are hung off

There's no way we can do a transactionless load, then?  I'm thinking of the 
load-into-new-partition which is a single pass/fail operation.  Would 
ignoring individual row errors in for this case still cause these kinds of 
problems?

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Tom Lane <[email-masked]>
Date: Sat, 15 Dec 2007 01:12:58 -0500

> ignoring individual row errors in for this case still cause these kinds of 

Given that COPY fires triggers and runs CHECK constraints, there is no
part of the system that cannot be exercised during COPY.  So I think
supposing that we can just deal with some simplified subset of reality
is mere folly.

			regards, tom lane

---

From: NikhilS <[email-masked]>
Date: Sat, 15 Dec 2007 13:02:51 +0530

Hi,

> bulk load fall into the latter category, we can do without

I think errors which occur after we have done a fast_heap_insert of the
tuple generated from the current input row are the ones which would require
the subtransaction to recover. Examples could be unique/primary key
violation errors or FKey/triggers related errors. Any errors which occur
before doing the heap_insert should not require any recovery according to
me.

The overhead of having a subtransaction per row is a very valid concern. But
instead of using a per insert or a batch insert substraction, I am
thinking that we can start off a subtraction and continue it till we
encounter a failure. The moment an error is encountered, since we have the
offending (already in heap) tuple around, we can call a simple_heap_delete
on the same and commit (instead of aborting) this subtransaction after doing
some minor cleanup. This current input data row can also be logged into a
bad file. Recall that we need to only handle those errors in which the
simple_heap_insert is successful, but the index insertion or the after row
insert trigger causes an error. The rest of the load then can go ahead with
the start of a new subtransaction.

Regards,
Nikhils

---

From: Tom Lane <[email-masked]>
Date: Sat, 15 Dec 2007 02:44:56 -0500

NikhilS <[email-masked]> writes:
> Any errors which occur before doing the heap_insert should not require
> any recovery according to me.

A sufficient (though far from all-encompassing) rejoinder to that is
"triggers and CHECK constraints can do anything".

> encounter a failure.

What of failures that occur only at (sub)transaction commit, such as
foreign key checks?

			regards, tom lane

---

From: Simon Riggs <[email-masked]>
Date: Sat, 15 Dec 2007 09:14:16 +0000

> cycles spent for that, but the ensuing limitation to 4G rows imported

I'd suggest doing everything at block level
- wrap each new block of data in a subtransaction
- apply data to the table block by block (can still work with FSM). 
- apply indexes in bulk for each block, unique ones first. 

That then gives you a limit of more than 500 trillion rows, which should
be enough for anyone.

---

From: Hannu Krosing <[email-masked]>
Date: Sat, 15 Dec 2007 13:10:30 +0200

> supposing that we can just deal with some simplified subset of reality

But can't we _define_ such a subset, where we can do a transactionless
load ?

I don't think that most DW/VLDB schemas fire complex triggers or custom
data-modifying functions inside CHECK's.

Then we could just run the remaining simple CHECK constraints ourselves
and not abort on non-check, but just log the rows ?

The COPY ... WITH ERRORS TO ... would essentially become a big
conditional RULE through which the incoming data is processed.

------------------
Hannu

---

From: Trent Shipley <[email-masked]>
Date: Sat, 15 Dec 2007 17:59:20 -0700

On Saturday 2007-12-15 02:14, Simon Riggs wrote:
> > > implement, I think: you could use PG_TRY() around the
> > > InputFunctionCall, but I guess you'd need a subtransaction to ensure
> > > that you reset your state correctly after catching an error.
> >

Wouldn't it only give you more than 500T rows in the best case?  If it hits a 
bad row it has to back off and roll forward one row and one subtransaction at 
a time for the failed block.  So in the worst case, where there is at least 
one exception row per block, I think you would still wind up with only a 
capacity of 4G rows.

---

From: Neil Conway <[email-masked]>
Date: Sat, 15 Dec 2007 22:07:46 -0800

> requirement:  http://pgfoundry.org/projects/pgloader/

For complicated ETL, I agree that using an external tool makes the most
sense. But I think there is still merit in adding support to COPY for
the simple case of trying to load a data file that has some corrupted,
invalid or duplicate records.

-Neil

---

From: "Pavel Stehule" <[email-masked]>
Date: Sun, 16 Dec 2007 07:39:32 +0100

> invalid or duplicate records.

Any simple enhancing of COPY is welcome. I lost lot of time with
repeated imports.

Regards
Pavel

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to [email-masked] so that your
>        message can get through to the mailing list cleanly
>

---

From: Tom Lane <[email-masked]>
Date: Sun, 16 Dec 2007 12:27:36 -0500

> But can't we _define_ such a subset, where we can do a transactionless

Sure ... but you'll find that it's not large enough to be useful.
Once you remove all the interesting consistency checks such as
unique indexes and foreign keys, the COPY will tend to go through
just fine, and then you're still stuck trying to weed out bad data
without very good tools for it.  The only errors we could really
separate out without subtransaction fencing are extremely trivial
ones like too many or too few fields on a line ... which can be
caught with a sed script.

			regards, tom lane

---

From: NikhilS <[email-masked]>
Date: Mon, 17 Dec 2007 13:05:35 +0530

Hi,

On Dec 15, 2007 1:14 PM, Tom Lane <[email-masked]> wrote:

> > encounter a failure.The moment an error is encountered, since we have
> the offending >(already in heap) tuple around, we can call a
> simple_heap_delete on the same and commit >(instead of aborting) this
> subtransaction
>

What if we identify and define a subset where we could do subtransactions
based COPY? The following could be supported:

* A subset of triggers and CHECK constraints which do not move the tuple
around. (Identifying this subset might be an issue though?)
* Primary/unique key indexes

As Hannu mentioned elsewhere in this thread, there should not be very many
instances of complex triggers/CHECKs around? And  may be in those instances
(and also the foreign key checks case), the behaviour could default to use a
per-subtransaction-per-row or even the existing single transaction model?

Regards,
Nikhils

---

From: "Michał Zaborowski" <[email-masked]>
Date: Tue, 18 Dec 2007 16:01:52 +0100

> caught with a sed script.
I have dump file. I would like to load it ASAP.
Constraints will be applied at the end, so any problem can be detected.
I would like it to be as direct as possible and as bulk as possibe - just
allocate pages and fill them with the data. Maybe it should be different
mode - single user or so. Right now I can save some IO - like turn off
fsync, but that is all :(

I got something like that:
http://www.tbray.org/ongoing/When/200x/2007/10/30/WF-Results
I have no idea how to load single file in many threads, but... the point is
that it can be much faster that single-thread load - surprisingly - at
least for me.

-- 
Regards,
  Michał Zaborowski (TeXXaS)

---

From: Decibel! <[email-masked]>
Date: Tue, 18 Dec 2007 10:41:20 -0600

On Dec 12, 2007, at 1:26 PM, Markus Schiltknecht wrote:
> Josh Berkus wrote:
>> Sure.  Imagine you have a 5TB database on a machine with 8 cores  
>> and only one concurrent user.  You'd like to have 1 core doing I/ 
>> O, and say 4-5 cores dividing the scan and join processing into  
>> 4-5 chunks.
>
> Ah, right, thank for enlightenment. Heck, I'm definitely too  
> focused on replication and distributed databases :-)
>
> parallel processing on different machines and parallel processing  
> on multiple CPUs - especially considering NUMA architecture. *comes- 
> to-think-again*...

Except that doing something in-machine is often far simpler than  
trying to go cross-machine, especially when that something is a  
background reader.

Let's walk before we run. :)
-- 
Decibel!, aka Jim C. Nasby, Database Architect  [email-masked]
Give your computer some brain candy! www.distributed.net Team #1828

---

From: Josh Berkus <[email-masked]>
Date: Thu, 20 Dec 2007 12:04:26 -0800

Tom,

> caught with a sed script.

Speaking as someone who did a LOT of DW load design only a couple years ago, 
I'll say that the "special case" of no triggers, no constraint checks except 
length, and type-safety check actually constitutes about 50% of DW bulk 
loading.  The only exception to that is unique indexes, which would normally 
be included and would be the difficult thing.

Also, "special case bulk loading" would in fact give users of other types of 
applications a lot more flexibility -- they could always load into a holding 
table just to clean up the type safety issues and then merge into the real 
table.  

So I don't agree that the "load into new partition without dependancies" is 
too much of a special case to be worth pursuing.  It might be a bad idea for 
other reasons, but not because it's too obscure.

--Josh