Thread: pqlib large object error
I am writing some proof-of-concept code for a project which involves
using PostgreSQL's large object facility to store audio data. Currently,
the goal for this code is to test the performance of reading audio data
from a file and writing it to a large object. I am learning as I go, but
I cannot seem to figure this error message out:
ERROR: duplicate key value violates unique constraint "pg_largeobject_loid_pn_index"
I am using the following code to create the large object: lo_1 = lo_creat(connect1, INV_READ|INV_WRITE); if
(lo_1> 0) { ... (open and write) } else { fprintf (stderr, "Unable to create large object: %s\n",
PQerrorMessage(connect1)); }
where connect1 is the pointer to my connection. Why is there a duplicate key if lo_creat() is supposed to assign an
unusedOID?
Thanks,
Edward
Edward Amsden <amsden_linux@embarqmail.com> writes:
> I cannot seem to figure this error message out:
> ERROR: duplicate key value violates unique constraint "pg_largeobject_loid_pn_index"
Huh, that shouldn't happen. Is it repeatable? What PG version
is your server?
regards, tom lane
On Tue, 2008-06-10 at 14:29 -0400, Tom Lane wrote: > Edward Amsden <amsden_linux@embarqmail.com> writes: > > I cannot seem to figure this error message out: > > > ERROR: duplicate key value violates unique constraint "pg_largeobject_loid_pn_index" > > Huh, that shouldn't happen. Is it repeatable? Yes. It happens every time the program runs, no matter what db or user, tried unix socket and tcp connect > What PG version > is your server? 8.3.1, running on Ubuntu Linux > > regards, tom lane
Edward Amsden <amsden_linux@embarqmail.com> writes:
> On Tue, 2008-06-10 at 14:29 -0400, Tom Lane wrote:
>> Edward Amsden <amsden_linux@embarqmail.com> writes:
>>> I cannot seem to figure this error message out:
>>>
>>> ERROR: duplicate key value violates unique constraint "pg_largeobject_loid_pn_index"
>>
>> Huh, that shouldn't happen. Is it repeatable?
> Yes. It happens every time the program runs, no matter what db or user,
> tried unix socket and tcp connect
>> What PG version
>> is your server?
> 8.3.1, running on Ubuntu Linux
Hmph. The only thing I can think of is that that index is corrupt,
but it seems unlikely that it would be so in all databases. What
happens if you REINDEX pg_largeobject?
regards, tom lane
Edward Amsden <amsden_linux@embarqmail.com> writes:
> I am using the following code to create the large object:
> lo_1 = lo_creat(connect1, INV_READ|INV_WRITE);
> if (lo_1 > 0) {
> ... (open and write)
> } else {
> fprintf (stderr, "Unable to create large object: %s\n", PQerrorMessage(connect1));
> }
BTW, what type do you have lo_1 declared as? It strikes me that if it's
signed int32, and your OID counter is past 2 billion, this code would
fail because it would misinterpret a valid OID as negative. The printf
would then report whatever the last error on the connection had been.
This still wouldn't explain why you're getting the duplicate-key error,
but it might at least point to the proper place to look.
regards, tom lane
On Tue, 2008-06-10 at 17:06 -0400, Tom Lane wrote:
> Edward Amsden <amsden_linux@embarqmail.com> writes:
> > I am using the following code to create the large object:
> > lo_1 = lo_creat(connect1, INV_READ|INV_WRITE);
> > if (lo_1 > 0) {
> > ... (open and write)
> > } else {
> > fprintf (stderr, "Unable to create large object: %s\n", PQerrorMessage(connect1));
> > }
>
> BTW, what type do you have lo_1 declared as? It strikes me that if it's
> signed int32, and your OID counter is past 2 billion, this code would
> fail because it would misinterpret a valid OID as negative. The printf
> would then report whatever the last error on the connection had been.
> This still wouldn't explain why you're getting the duplicate-key error,
> but it might at least point to the proper place to look.
>
It is defined as the Oid type from pqlib-fs.h
> regards, tom lane
Edward Amsden <amsden_linux@embarqmail.com> writes:
> On Tue, 2008-06-10 at 17:06 -0400, Tom Lane wrote:
>> BTW, what type do you have lo_1 declared as?
> It is defined as the Oid type from pqlib-fs.h
OK, scratch that theory then. Did you have any success with reindexing?
regards, tom lane
On Tue, 2008-06-10 at 16:49 -0400, Tom Lane wrote:
> Edward Amsden <amsden_linux@embarqmail.com> writes:
> > On Tue, 2008-06-10 at 14:29 -0400, Tom Lane wrote:
> >> Edward Amsden <amsden_linux@embarqmail.com> writes:
> >>> I cannot seem to figure this error message out:
> >>>
> >>> ERROR: duplicate key value violates unique constraint "pg_largeobject_loid_pn_index"
> >>
> >> Huh, that shouldn't happen. Is it repeatable?
>
> > Yes. It happens every time the program runs, no matter what db or user,
> > tried unix socket and tcp connect
>
> >> What PG version
> >> is your server?
>
> > 8.3.1, running on Ubuntu Linux
>
> Hmph. The only thing I can think of is that that index is corrupt,
> but it seems unlikely that it would be so in all databases. What
> happens if you REINDEX pg_largeobject?
The error has disappeared. Now it can't write to the object. lo_open
seems to be successful, but lo_write is failing with:
> ERROR: invalid large-object descriptor: 0
0 is a valid value (I checked) but it appears it cannot write to the object. lo_open should return a negative value if
itcannot create the handle
Here is my code:
if (PQstatus(connect1) == CONNECTION_OK) { lo_1 = lo_creat(connect1, INV_READ|INV_WRITE); if (lo_1 > 0) {
lo_1_fd= lo_open(connect1, lo_1, INV_READ|INV_WRITE); written = lo_write(connect1, lo_1_fd, filedata, (size_t)
filelen); if (written == filelen) { printf ("Write successful, %d bytes written.\n", written);
}else if (written < 0) { printf ("Write unsuccessful: %s\n", PQerrorMessage(connect1)); } else {
printf ("Write partially successful. %d of %d bytes written.\n",
written, filelen); } } else { fprintf (stderr, "Unable to create large object: %s\n",
PQerrorMessage(connect1)); }} else { fprintf (stderr, "Unable to open connection: %s\n",
PQerrorMessage(connect1));}
Let me know if it's unreadable, my mail client can do funny things with
indents.
> regards, tom lane
Edward Amsden <amsden_linux@embarqmail.com> writes:
> On Tue, 2008-06-10 at 16:49 -0400, Tom Lane wrote:
>> Hmph. The only thing I can think of is that that index is corrupt,
>> but it seems unlikely that it would be so in all databases. What
>> happens if you REINDEX pg_largeobject?
> The error has disappeared.
Hmm, so something did happen to your index.
> Now it can't write to the object. lo_open
> seems to be successful, but lo_write is failing with:
>> ERROR: invalid large-object descriptor: 0
You forgot to put the open-and-use-descriptor sequence inside a
BEGIN block.
regards, tom lane
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. :-| If you could explain I'd appreciate it :-) BTW once I get this working I'm going to write a PostgreSQL Large Object tutorial on my blog, so others don't have to struggle with the code like I am :-) On Tue, 2008-06-10 at 19:05 -0400, Tom Lane wrote: > Edward Amsden <amsden_linux@embarqmail.com> writes: > > On Tue, 2008-06-10 at 16:49 -0400, Tom Lane wrote: > >> Hmph. The only thing I can think of is that that index is corrupt, > >> but it seems unlikely that it would be so in all databases. What > >> happens if you REINDEX pg_largeobject? > > > The error has disappeared. > > Hmm, so something did happen to your index. > > > Now it can't write to the object. lo_open > > seems to be successful, but lo_write is failing with: > >> ERROR: invalid large-object descriptor: 0 > > You forgot to put the open-and-use-descriptor sequence inside a > BEGIN block. > > regards, tom lane
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. 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? regards, tom lane
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
Edward Amsden wrote:
> 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. :-| If you could explain I'd
> appreciate it :-)
PQexec("BEGIN");
... do some fooling with large objects ...
PQexec("COMMIT");
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support