Re: \copy, transactions and permissions

On Wed, 13 Aug 2008 16:32:18 -0500
ries van Twisk <pg(at)rvt(dot)dds(dot)nl> wrote:

> On Aug 13, 2008, at 4:25 PM, Ivan Sergio Borgonovo wrote:
> > I need to write an import function with enough isolation from
> > apache daemon.
> > Code has no input other than cvs files and a signal about when to
> > start the import.
> > The sql code that will be executed will be static.
> > I may end up writing a mini-daemon that just start a SQL script
> > or just pool from cron and feed psql.
> >
> > If anyone has a better (lazier, cleaner) approach it will be very
> > welcome.
>
> Lazier would be using JasperETL or any other ETL tool

*I did a simple test and \copy works as expected inside
transactions.*

But I wasn't able to split a \copy command across several lines to
improve readability and add comments.

Such ETL tools didn't seem suited for my current needs.

I downloaded a 190Mb app that has quite a bit of dependencies, tried
to run it and gave up the first time, unpacking took too long. I gave
a glimpse to the screenshot and I got the impression that at least
for me they have a harder learning curve than refreshing my awk
knowledge.

Out of curiosity I googled for etl postgresql and found kettle.
This one was a bit smaller download and I was able to run it
immediately. Still it looked more a tool suited for managers that
have to produce reports rather than for programmers.

They are surely more powerful than a 10 line awk script but I didn't
see a quick way to script them.

eg. if I had to import 10 tables I'd have to repeat the same
click-drag-fill-connect seqence 10 times.

I've used MS DTS in SQL 2000 and I've found it has a faster learning
curve even if it seems its not as flexible as JasperETL or kettle and
I've experienced the same problem of not being able to script it.

kettle export an xml file that could be taken as a template for some
scripting work.
I didn't understand how to execute the resulting plan outside the
GUI.

I'm wondering if I didn't give them enough time or they are
definitively for use case that rarely cross my way.

I haven't used dblink in Postgresql yet but I've found the ability to
connect to different data sources (including Access and Excel) of
those ETL interesting.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it