Thread: OIDs and JDBC
Hello, When I delete a row that contains a large object (better said an OID pointing to a large object) using JDBC, does this large object becomes orphan ? Must I remove manually the large object or is it remove automatically ? I am using Postgresql v7.0.2. Thanks in advance. Alejandro Alberola Arias Boix i Oltra, S.L. alberola@bio.es
You have to remove the LOs manually and after this delete the
row that contains the OID...
By the way, How can I remove the orphans LOs of a database ? Is
there a vaccum command that works for LOs ?
Nelson
Alejandro Alberola Arias wrote:
> Hello,
>
> When I delete a row that contains a large object
> (better said an OID pointing to a large object) using
> JDBC, does this large object becomes orphan ?
> Must I remove manually the large object or is it
> remove automatically ?
>
> I am using Postgresql v7.0.2.
>
> Thanks in advance.
>
> Alejandro Alberola Arias
> Boix i Oltra, S.L.
> alberola@bio.es
Nelson Ferreira Jr <nelson@radix.com.br> writes:
> By the way, How can I remove the orphans LOs of a database ? Is
> there a vaccum command that works for LOs ?
There's something called vacuumlo in the contrib area of the
distribution. I've never looked at it but the name sounds promising...
regards, tom lane
Is there away to access a postgres database using a shell script, like bash or korn? Mike
On Fri, 6 Oct 2000, Mike Sears wrote:
> Is there away to access a postgres database using a shell script, like bash
> or korn?
You can use
psql -c <query>
or
psql -f <filename>
to do command-line stuff inside of a script. But then you have to figure
out how to parse the result. You're probably better off going the next
step and using Perl.
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Imagination is more important than knowledge.
-- Albert Einstein
On Fri, Oct 06, 2000 at 11:17:08AM -0400, bmccoy@chapelperilous.net wrote:
> On Fri, 6 Oct 2000, Mike Sears wrote:
> > Is there away to access a postgres database using a shell script, like bash
> > or korn?
>
> You can use
>
> psql -c <query>
>
> or
>
> psql -f <filename>
>
> to do command-line stuff inside of a script. But then you have to figure
> out how to parse the result. You're probably better off going the next
> step and using Perl.
i generally use:
psql -A -t -q -d database -c "sql query" > tempfile
if [ $? -ne 0 ] ; then
echo command failed
exit
fi
awk -F\| -f script.awk tempfile
.....
there is a commandline options somewhere to change he field seperator, but
i forget it now.
you could do it all in one line and pipe the psql into awk/whatever.
but i've found that checking the return status of psql before actually using
the data is a useful thing. 8^)
--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
Jim Mercer writes: > psql -A -t -q -d database -c "sql query" > tempfile > if [ $? -ne 0 ] ; then > echo command failed > exit > fi > awk -F\| -f script.awk tempfile > ..... > > there is a commandline options somewhere to change he field seperator, but > i forget it now. It's also -F! -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Mike Sears writes: > Is there away to access a postgres database using a shell script, like bash > or korn? There's pgbash, which might be what you want, but I've never tried it. Of course psql works as well. Processing several output rows with more than one field might pose a problem in any case. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Fri, Oct 06, 2000 at 06:05:41PM +0200, Peter Eisentraut wrote: > Mike Sears writes: > > Is there away to access a postgres database using a shell script, like bash > > or korn? > > There's pgbash, which might be what you want, but I've never tried it. > Of course psql works as well. Processing several output rows with more > than one field might pose a problem in any case. one day, i'm gonna hack up a pgawk. i think that would be most awesome. -- [ Jim Mercer jim@reptiles.org +1 416 410-5633 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
On Fri, 6 Oct 2000, Jim Mercer wrote:
> > There's pgbash, which might be what you want, but I've never tried it.
> > Of course psql works as well. Processing several output rows with more
> > than one field might pose a problem in any case.
>
> one day, i'm gonna hack up a pgawk.
>
> i think that would be most awesome.
Heh, while yer at it, how about pgsed, pgyacc, pglex, pggrep, etc.? You
could have all of your familiar tools tailored to do postgres!
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
panic: kernel trap (ignored)
On Fri, 6 Oct 2000, Alejandro Alberola Arias wrote: > Hello, > > When I delete a row that contains a large object > (better said an OID pointing to a large object) using > JDBC, does this large object becomes orphan ? > Must I remove manually the large object or is it > remove automatically ? Yes. This is because there is no actuall link between the row and the BLOB. In the source (src/contrib/lo) there is a simple example trigger that I wrote some time ago, that you can associate with a table to monitor DELETE and UPDATE operations, and if a field of type OID is affected, so is the associated large object. It's not perfect, but it works. Peter -- Peter T Mount peter@retep.org.uk http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
On Fri, 6 Oct 2000, Nelson Ferreira Jr wrote: > You have to remove the LOs manually and after this delete the > row that contains the OID... > By the way, How can I remove the orphans LOs of a database ? Is > there a vaccum command that works for LOs ? No, as it would be database/application specific. Ie: a large object is only called an orphan because it's not actually refered to by anything in the database (other than in the system tables). You can still use it if you know it's oid, which you can get by a simple query on the system tables. Peter -- Peter T Mount peter@retep.org.uk http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
> On Fri, Oct 06, 2000 at 06:05:41PM +0200, Peter Eisentraut wrote: > > Mike Sears writes: > > > Is there away to access a postgres database using a shell script, like bash > > > or korn? > > > > There's pgbash, which might be what you want, but I've never tried it. > > Of course psql works as well. Processing several output rows with more > > than one field might pose a problem in any case. > > one day, i'm gonna hack up a pgawk. > > i think that would be most awesome. More properly, that would be "awksome." -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Mike Sears wrote: > Is there away to access a postgres database using a shell script, like bash > or korn? > > Mike > The solution I came up with to access a database (originally Sybase, the later msql and PostgreSQL) from the shell (sh, ksh) and awk was to write a program (called getsql) that accepted SQL statements from stdin and wrote the result to stdout as delimited fields (the default was the bar [|] character). I would then create 2 named pipes and run getsql in the background reading from one named pipe and writing to the other named pipe. The program that needed access (sh, ksh, awk, etc) would then write queries to the named pipe that getsql was reading from and get the results from the named pipe getsql writes to. If you are interested, I can send you the source code for getsql, or if there is enough interest, I can post the code to the list. -- ____ | Billy G. Allie | Domain....: Bill.Allie@mug.org | /| | 7436 Hartwell | Compuserve: 76337,2061 |-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com |/ |LLIE | (313) 582-1540 |