pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedpqlib large object error
13+ messages / 3 participants
[nested] [flat]
* pqlib large object error
@ 2008-06-10 17:23 Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:06 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Edward Amsden @ 2008-06-10 17:23 UTC (permalink / raw)
To: pgsql-interfaces
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 unused OID?
Thanks,
Edward
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-10 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-06-10 18:29 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-10 20:37 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Edward Amsden @ 2008-06-10 20:37 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-10 20:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:46 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 23:05 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2008-06-10 20:49 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-10 21:46 ` Edward Amsden <amsden_linux@embarqmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Edward Amsden @ 2008-06-10 21:46 UTC (permalink / raw)
To: pgsql-interfaces
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 it cannot 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-10 23:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 01:04 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-06-10 23:05 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 23:05 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-11 01:04 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-11 01:24 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 11:47 ` Re: pqlib large object error Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 13+ messages in thread
From: Edward Amsden @ 2008-06-11 01:04 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 23:05 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 01:04 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-11 01:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 02:14 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-06-11 01:24 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 23:05 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 01:04 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-11 01:24 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-11 02:14 ` Edward Amsden <amsden_linux@embarqmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Edward Amsden @ 2008-06-11 02:14 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 23:05 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 01:04 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-11 11:47 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2008-06-11 11:47 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-10 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:16 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2008-06-10 21:06 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 21:06 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-10 21:16 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 21:21 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Edward Amsden @ 2008-06-10 21:16 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pqlib large object error
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 21:06 ` Re: pqlib large object error Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:16 ` Re: pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
@ 2008-06-10 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2008-06-10 21:21 UTC (permalink / raw)
To: Edward Amsden <amsden_linux@embarqmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-06-11 11:47 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-10 17:23 pqlib large object error Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 20:37 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 20:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:46 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 23:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 01:04 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-11 01:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-11 02:14 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-11 11:47 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-06-10 21:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-10 21:16 ` Edward Amsden <amsden_linux@embarqmail.com>
2008-06-10 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox