Re: postgres external table

Craig Ringer wrote:
> On 19/01/2010 1:13 AM, Vincenzo Romano wrote:
>
>> Another case, Tom, could be when the file is updated from a non-DB
>> application and you need to synchronize
>> the data with other DB applications ...
>
> How can that work without a transactional file system, though? If the
> external process writes to the file while you're half-way through
> reading it, what's the database to do? In general, how do external
> tables cope with the fact that they're on non-transactional storage?

With Oracle's implementation, you version the input files (timestamp or
something) and then do ALTER TABLE to change the location the external
table points to.  That will block waiting for exclusive access before it
fires, then you get a clean switch to the new location.  There is no
smartness here to cope with weird behavior built-in here--I expect it
will just crash the query.  One thing you always have to be careful
about when using these is that an external table might return a weird
query error under odd circumstances such as you describe, which you
might not normally expect from a simple SELECT.


--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com