Обсуждение: Re: [JDBC] How to just "link" to some data feed
On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <oliver@opencloud.com> wrote: > That's essentially the same as the COPY you quoted in your original email, > isn't it? So.. what exactly is it you want to do that COPY doesn't do? ~ well, actually, not exactly; based on: ~ http://postgresql.com.cn/docs/8.3/static/sql-copy.html ~ COPY <table_name> [FROM|TO] <data_feed> <OPTIONS> ~ import/export the data into/out of PG, so you will be essentially duplicating the data and having to synch it. This is exactly what I am trying to avoid, I would like for PG to handle the data right from the data feed ~ > Anyway, it's not really JDBC related. ~ Well, no, but I was hoping to get an answer here because I mostly access PG through jdbc and also because java developer would generally be more inclined to these types of DB-independent data formats, reusing, transferring issues ~ lbrtchx
Albretch Mueller wrote: > On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <oliver@opencloud.com> wrote: >> That's essentially the same as the COPY you quoted in your original email, >> isn't it? So.. what exactly is it you want to do that COPY doesn't do? > ~ > well, actually, not exactly; based on: > ~ > http://postgresql.com.cn/docs/8.3/static/sql-copy.html > ~ > COPY <table_name> [FROM|TO] <data_feed> <OPTIONS> > ~ > import/export the data into/out of PG, so you will be essentially > duplicating the data and having to synch it. This is exactly what I am > trying to avoid, I would like for PG to handle the data right from the > data feed As Dave said, PG won't magically keep the data up to date for you, you will need some external process to do the synchronization with the feed. That could use COPY if it wanted .. Then you said: > Hmm! Doesn't PG have a way to do something like this, say in MySQL: > > load data local infile 'uniq.csv' into table tblUniq > fields terminated by ',' > enclosed by '"' > lines terminated by '\n' > (uniqName, uniqCity, uniqComments) > > and even in low end (not real) DBs like MS Access? But isn't this doing exactly what PG's COPY does - loads data, once, from a local file, with no ongoing synchronization? > Is there a technical reason for that, or should I apply for a RFE? Personally I don't see this sort of synchronization as something that you want the core DB to be doing anyway. The rules for how you get the data, how often you check for updates, how you merge the updates, and so on are very application specific. -O
On Tue, Jun 3, 2008 at 9:58 PM, Albretch Mueller <lbrtchx@gmail.com> wrote: > On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <oliver@opencloud.com> wrote: >> That's essentially the same as the COPY you quoted in your original email, >> isn't it? So.. what exactly is it you want to do that COPY doesn't do? > ~ > well, actually, not exactly; based on: > ~ > http://postgresql.com.cn/docs/8.3/static/sql-copy.html > ~ > COPY <table_name> [FROM|TO] <data_feed> <OPTIONS> > ~ > import/export the data into/out of PG, so you will be essentially > duplicating the data and having to synch it. This is exactly what I am > trying to avoid, I would like for PG to handle the data right from the > data feed I think what you're looking for is the equivalent to oracles external tables which invoke sqlldr every time you access them in the background. No such animal in the pg universe that I know of. > Well, no, but I was hoping to get an answer here because I mostly > access PG through jdbc and also because java developer would generally > be more inclined to these types of DB-independent data formats, > reusing, transferring issues Removed pgsql-jdbc from cc list. You're still better off sending to the right list. And so are we. The general list has a much larger readership than jdbc, and it's far more likely you'll run into someone with oracle experience here who knows about the external table format, etc...
Scott Marlowe wrote: > On Tue, Jun 3, 2008 at 9:58 PM, Albretch Mueller > <lbrtchx@gmail.com> wrote: > > On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett > <oliver@opencloud.com> wrote: > >> That's essentially the same as the COPY you quoted in your > original email, > >> isn't it? So.. what exactly is it you want to do that COPY > doesn't do? > > ~ > > well, actually, not exactly; based on: > > ~ > > http://postgresql.com.cn/docs/8.3/static/sql-copy.html > > ~ > > COPY <table_name> [FROM|TO] <data_feed> <OPTIONS> > > ~ > > import/export the data into/out of PG, so you will be essentially > > duplicating the data and having to synch it. This is > exactly what I am > > trying to avoid, I would like for PG to handle the data > right from the > > data feed > > I think what you're looking for is the equivalent to oracles external > tables which invoke sqlldr every time you access them in the > background. No such animal in the pg universe that I know of. There was a similar discussion of this on -hackers in April. Closest to this idea was http://archives.postgresql.org/pgsql-hackers/2008-04/msg00250.php Regards, Stephen Denne. -- At the Datamail Group we value teamwork, respect, achievement, client focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. The Datamail Group, through our GoGreen programme, is committed to environmental sustainability. Help us in our efforts by not printing this email. __________________________________________________________________ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __________________________________________________________________
Albretch Mueller wrote: > import/export the data into/out of PG, so you will be essentially > duplicating the data and having to synch it. This is exactly what I am > trying to avoid, I would like for PG to handle the data right from the > data feed You could write a set-returning function that reads the file, and perhaps a view on top of that. As the file is read on every invocation, that's only practical for small tables, though. It's likely a better idea to just COPY the table into the database periodically, or perhaps write an external script that watches the modification timestamp on the file and triggers a reload whenever it changes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com