Not able to view text columns

[rex-sheridan]

Hello,

Apologies ahead of time if this is not the proper forum but I didn't see any pgweb questions on StackOverflow. I am trying to use pgweb to view the contents of a table with a text column. Anytime I see the results it always displays the results as some numeric value like "41006". All other columns render perfectly. I know the contents are something other than "41006" because the application I am working on renders the contents correctly. I know that the contents of the column are JSON, in case this has any effect.

Thanks, Rex Sheridan

[rex-sheridan]

I also tried a query like the following to force the text column to render:

select '''' || payload || '''' from payment_event

[sosedoff]

Hi there, which pgweb/postgres version are you using? Could you provide a scrubbed dump with a sample set of data?

[rex-sheridan]

We are using pgweb as of commit aba81334a5d1774a5a347ae45ce326ee4e91745f. We are using PostgreSQL 9.6.4.

Attached screenshots and JSON dump.

pgweb-1508514613.txt (JSON)

[sosedoff]

Hm thats strange. Could you try creating a new test db and importing the dump file i attached? Then try browsing data/running query in pgweb.

pgweb_issue_304.sql.gz

Here's what i see:

Also, i noticed that your payload number is incrementing with the record id. Not sure if that matters, just wanted to bring this up.

[rex-sheridan]

When I used your dump file everything looks fine. Not sure what is going on with my schema.

[rex-sheridan]

I was able to recreate the issue by dumping my own data and importing it to a new DB. It appears related to data populated as large objects.

I see in the dump file from my schema stuff like the following:

SELECT pg_catalog.lo_open('41006', 131072);
SELECT pg_catalog.lowrite(0, '\x7b227665...');
SELECT pg_catalog.lo_close(0);

[sosedoff]

Can you send me the SQL dump so i can reproduce and fix the issue? You can post it here or send it my email (in profile).

[rex-sheridan]

I have attached the SQL dump.

pgdump2.sql.gz

I am using the standard postgres JDBC driver to populate these objects in my application. Since the contents of the column can be arbitrarily large in my application I am using Clob functionality. I investigated the source code to see how the driver is implemented. When using Clobs the steps it takes are basically the following:

1. Invoke the LargeObjectManager as described at Chapter 7. Storing Binary Data and create a handle (oid) to a large object entry in pg_largeobject and pg_largeobject_metadata
2. Write the bytes to pg_largeobject using the handle
3. Write the handle oid value to the column of the target table

This explains why you saw numerical incrementing values in the payload column. They were oid references to pg_largeobject.

I am not sure what a good solution would look like here since a text column can also hold normal strings. How would one differentiate between an oid reference and a string? Perhaps provide a way to manually tell pgweb "this value should be dereferenced in pg_largeobject+pg_largeobject_metadata"?

[sosedoff]

@rex-sheridan i'm not sure what to do about this issue. i've never used largeobjects feature before. Given pgweb's limited feature scope i don't think it's worth supporting in the long run, although if someone wanted to create a pr i'd happy to review and merge if possible. I also don't have any time to work on something like this, there's plenty of other bugs/features that i need to take care of first. I also checked Postico to see if they support large objects - same deal, you only see the reference id.

[rex-sheridan]

Now that I am aware of the behavior I think it's totally acceptable to leave pgweb as-is. No need to clutter the application. Thanks for your help and for creating a really useful application.

[varp]

@rex-sheridan please reopen the issue, please.

[rex-sheridan]

Reopened at the request of @varp.

[sosedoff]

@varp why do you need this issue reopened? i thought we've pretty much covered all the ends here.

[varp]

@sosedoff sometime we need to do it, right? As you said, someone else might want to do PR. I thought it’s logical to leave the issue opened

[sosedoff]

Well, when time comes - sure, but it seems like a lot of hustle right now.

[sosedoff]

Closing this issue for now.