X_CMAE_Category: 0,0 Undefined,Undefined
X-CNFS-Analysis: v=1.0 c=1 a=yojgaC0HzEAA:10 a=gra_WR_tcWEA:10
	a=epTmVMiNAAAA:8 a=daG4ckW05iqxjUrFwaAA:9
	a=5Fsn9OnScdccTRu1gCoA:7 a=LkU-xPszoZ2DJaCgnTmVZrd_I1YA:4
	a=cvn8laQl214A:10 a=LY0hPdMaydYA:10
X-CM-Score: 0
X-Scanned-by: Cloudmark Authority Engine
Authentication-Results: smtp07.embarq.synacor.com
	smtp.user=amsden_linux@embarqmail.com; auth=pass (LOGIN)
Received: from [71.2.202.190] ([71.2.202.190:40818] helo=[192.168.0.30])
	by mailrelay.embarq.synacor.com (envelope-from
	<amsden_linux@embarqmail.com>)
	(ecelerity 2.2.1.28 r(22594)) with ESMTPA
	id 57/07-06326-5053F484; Tue, 10 Jun 2008 22:14:29 -0400
Subject: Re: pqlib large object error
From: Edward Amsden <amsden_linux@embarqmail.com>
To: pgsql-interfaces@postgresql.org
In-Reply-To: <27739.1213147492@sss.pgh.pa.us>
References: <1213118594.9123.11.camel@dad-desktop2>
	<21991.1213122593@sss.pgh.pa.us>
	<1213130242.15557.3.camel@dad-desktop2>
	<24396.1213130996@sss.pgh.pa.us>
	<1213134089.15557.22.camel@dad-desktop2>
	<26209.1213139125@sss.pgh.pa.us>
	<1213146291.19203.4.camel@dad-desktop2>
	<27739.1213147492@sss.pgh.pa.us>
Content-Type: text/plain
Date: Tue, 10 Jun 2008 22:14:14 -0400
Message-Id: <1213150454.19203.17.camel@dad-desktop2>
Mime-Version: 1.0
X-Mailer: Evolution 2.22.2 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/12
X-Sequence-Number: 6755

On Tue, 2008-06-10 at 21:24 -0400, Tom Lane wrote:
> Edward Amsden <amsden_linux@embarqmail.com> writes:
> > Thanks for all your help. I'm somewhat amateur with C and even less
> > experienced with PostgreSQL (I'm a recent MySQL convert). Even after
> > some googling, I have no idea what this BEGIN block is. Is it C or is it
> > SQL? That probably makes me a n00b. :-|
> 
> You need a SQL "BEGIN" (or "START TRANSACTION") command and a SQL
> "COMMIT" (or "END") command around anything that involves having a
> large object descriptor open.  It might help to look at the sample
> program here:
> 
> http://www.postgresql.org/docs/8.3/static/lo-examplesect.html
> 
> It's not amazingly well commented :-(, but the lines
> 
> 	res = PQexec(conn, "begin");
> 	res = PQexec(conn, "end");
> 
> are *not* optional.
Thanks for your help with this.	I successfully wrote about 7MB in and
read it back out :-)
> 
> Meanwhile, I'm still wondering what happened to your pg_largeobject
> index.  You said you saw the problem in multiple databases, which
> suggests that the index was broken in template1 and then the damage
> was propagated to other databases by CREATE DATABASE.  Can you still
> see a problem if you make your program connect to some other database?
There is no issue with other databases. I created another database, and
had no issues.

It is my understanding that it shouldn't be database-dependent, since
the only part of large objects that is stored outside of the postgres
database is references to the large object OIDs.

Again, thanks for your help. Let me know if I should check on anything
else regarding the index corruption.

Also, I will post a link to the tutorial in my blog as a reply to this
thread as soon as the blog post is up.

Edward