Timeline for Unable to output data from tables in pgAdmin4 due to missing oid operator
Current License: CC BY-SA 4.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 10, 2019 at 7:40 | comment | added | Trashmonk | So I looked at how my tables were being created, they all have WITH (OIDS = FALSE), so that's not the issue. I reinstalled postgresql and rebuilt my database which has fixed the issue for now. Hopefully the new version of PGAdmin, will have fixed the issue that @Erwin Brandstetter has highlighted. | |
| Sep 10, 2019 at 0:09 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 148 characters in body
|
| Sep 9, 2019 at 23:55 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 228 characters in body
|
| Sep 6, 2019 at 23:22 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 10 characters in body
|
| Sep 6, 2019 at 23:20 | comment | added | Erwin Brandstetter | @DanielVérité: Thanks, I've posted a note to pgadmin-hackers: postgresql.org/message-id/…. And I improved the answer with your input. | |
| Sep 6, 2019 at 23:16 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
improve with input from Daniel
|
| Sep 6, 2019 at 22:10 | comment | added | Daniel Vérité | @ErwinBrandstetter: it looks very much like a simple bug in pgAdmin4. The [big]int<-->oid conversions by postgres seem okay to me. See GetNewOidWithIndex for how OIDs collisions are dealt with after an OID-wraparound (there is a performance penalty for collisions). Also the OID counter is per instance, not per database, so another db could be responsible for over-consuming OIDs. | |
| Sep 6, 2019 at 16:42 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 19 characters in body
|
| Sep 6, 2019 at 16:21 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 28 characters in body
|
| Sep 6, 2019 at 16:15 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
deleted 1 character in body
|
| Sep 6, 2019 at 16:12 | comment | added | Erwin Brandstetter |
@DanielVérité: I updated with clearer explanation. Looks like a bug in pgAdmin4? Do you think the Postgres int <--> oid conversion makes sense? I find it a bit surprising that a cast to bigint yields a different result than a cast to int. Guess binary coercion is just fastest ...
|
|
| Sep 6, 2019 at 16:09 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
update
|
| Sep 6, 2019 at 11:30 | comment | added | Daniel Vérité | @Trashmonk: OID wraparound occurs at 2^32, so you still have 2^32-2775922889, or about 1.5 billion OIDs to consume before that. Still, it's a good idea to check if you have per-row OIDs on some tables, and a very high rate of create/drop objects would also have that effect. | |
| Sep 6, 2019 at 5:05 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 108 characters in body
|
| Sep 6, 2019 at 5:02 | comment | added | Erwin Brandstetter |
To avoid confusion: queries using a WITH clause (CTEs) are unrelated. I was asking about the WITH OIDS clause of the CREATE TABLE command, which would burn OIDs for all rows in user tables.
|
|
| Sep 6, 2019 at 4:59 | comment | added | Trashmonk | Ah that makes sense, we do have some queries that use WITH expensively. So I guess we burned through all the OIDS as the query SELECT * FROM pg_class WHERE oid = 2775922889; returns a result. I think its time to redesign the queries. Thank you for your help! | |
| Sep 6, 2019 at 4:53 | vote | accept | Trashmonk | ||
| Sep 6, 2019 at 4:38 | history | answered | Erwin Brandstetter | CC BY-SA 4.0 |