Thread: Problem with oids for table names getting out of sync?
This is with 8.1.8, but I don't see any mention of any bug fixes that
cover this.
I've run into this sort of obscure problem. I'm using libpq with a
front end database api where I need to track column names and how
they're returned in libpq queries. What's happening is that I start out
with a set of table names when I open my database with a query:
SELECT relfilenode, relname FROM pg_class WHERE relname !~ '^(pg_|sql_)'
AND relkind = 'r'
But these don't agree with the oids when I subsequently fetch my rows
and use the following:
Oid o = PQftable(_res, i);
std::string s1 = _con->_oidTableNames[o];
_con->_oidTableNames is my own array that I've created with the above
select query. The reason I need to get table names after queries is
that queries often return with multiple cases of a single column name,
but with different table names. I need to be able to decifer the
table.column combination when people refer to a column in that way.
My problem is that the Oids returned by PQftable sometimes do not return
from the select query. For instance
Oid returned from PQftable = 654989
select relname from FROM pg_class WHERE relfilenode = 654989
returns empty.
The way I can solve this problem is by dumping and restoring the
database. That's the only way I can fix it.
I don't know how to reproduce the problem. Has anybody heard of this?
I'm going to update to the latest version this weekened. Maybe that
will fix it.
Is there any other way to get table names from libpq queries without
using the oid method I use above?
One other thing I just remembered. The next time it happens I'm going
to try a postgresql java driver with similar queries to see if the
table.column combinations in ResultSet.getString() get messed up as
well. That will tell me something. I suppose I can look and see how
they get tablename information.
Thanks
One other thing about this issue. A work around is that I can parse sql statements myself to come up with the table names. Yes, it's a bit of work, but not too bad. My question is whether or not column/table combinations are guaranteed to return in the order that the table names appear in a query. It seems like they're always returned this way. For instance: select * from projects join employees on projects.manager = employees.id; projects.id would always appear in the return list before employees.id? Omar Eljumaily wrote: > This is with 8.1.8, but I don't see any mention of any bug fixes that > cover this. > > I've run into this sort of obscure problem. I'm using libpq with a > front end database api where I need to track column names and how > they're returned in libpq queries. What's happening is that I start > out with a set of table names when I open my database with a query: > > SELECT relfilenode, relname FROM pg_class WHERE relname !~ > '^(pg_|sql_)' AND relkind = 'r' > > But these don't agree with the oids when I subsequently fetch my rows > and use the following: > > Oid o = PQftable(_res, i); > std::string s1 = _con->_oidTableNames[o]; > > _con->_oidTableNames is my own array that I've created with the above > select query. The reason I need to get table names after queries is > that queries often return with multiple cases of a single column name, > but with different table names. I need to be able to decifer the > table.column combination when people refer to a column in that way. > > My problem is that the Oids returned by PQftable sometimes do not > return from the select query. For instance > > Oid returned from PQftable = 654989 > select relname from FROM pg_class WHERE relfilenode = 654989 > returns empty. > > The way I can solve this problem is by dumping and restoring the > database. That's the only way I can fix it. > > I don't know how to reproduce the problem. Has anybody heard of > this? I'm going to update to the latest version this weekened. Maybe > that will fix it. > Is there any other way to get table names from libpq queries without > using the oid method I use above? > > One other thing I just remembered. The next time it happens I'm going > to try a postgresql java driver with similar queries to see if the > table.column combinations in ResultSet.getString() get messed up as > well. That will tell me something. I suppose I can look and see how > they get tablename information. > > Thanks > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Omar Eljumaily <omar2@omnicode.com> writes:
> I've run into this sort of obscure problem. I'm using libpq with a
> front end database api where I need to track column names and how
> they're returned in libpq queries. What's happening is that I start out
> with a set of table names when I open my database with a query:
> SELECT relfilenode, relname FROM pg_class WHERE relname !~ '^(pg_|sql_)'
> AND relkind = 'r'
> But these don't agree with the oids when I subsequently fetch my rows
> and use the following:
> Oid o = PQftable(_res, i);
Um ... are you laboring under some delusion about relfilenode being the
same as relation OID?
regards, tom lane
>> SELECT relfilenode, relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' >> AND relkind = 'r' >> > Oid o = PQftable(_res, i); > > > Um ... are you laboring under some delusion about relfilenode being the > same as relation OID? > > Apparently I am. libpq docs claim that "You can query the system table pg_class to determine exactly which table is referenced." for PQftable. I query pg_class and the only column that looks remotely like a unique oid is relfilenode. The other thing is that it works most of the time and starts to work again when I dump and restore. Thanks
omar wrote: > > >>SELECT relfilenode, relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' > >>AND relkind = 'r' > >> > > Oid o = PQftable(_res, i); > > > > > >Um ... are you laboring under some delusion about relfilenode being the > >same as relation OID? > > > > > Apparently I am. libpq docs claim that "You can query the system table > pg_class to determine exactly which table is referenced." for PQftable. > I query pg_class and the only column that looks remotely like a unique > oid is relfilenode. select oid, relname from pg_class where ... relfilenode is just the file name given to the table, which is the same as the OID when the table is just created, but changes after certain operations (CLUSTER, TRUNCATE, REINDEX for indexes, maybe others) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro and Tom, thanks so much. I was getting worried that I was going to have to ask my customers to dump and restore periodically, ugh. I think I need to learn a bit more about postgresql internals to help me with my project. Not thinking about selecting for oids is kind of embarrassing. Thanks, Omar Alvaro Herrera wrote: > omar wrote: > >>>> SELECT relfilenode, relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' >>>> AND relkind = 'r' >>>> >>>> >>> Oid o = PQftable(_res, i); >>> >>> >>> Um ... are you laboring under some delusion about relfilenode being the >>> same as relation OID? >>> >>> >>> >> Apparently I am. libpq docs claim that "You can query the system table >> pg_class to determine exactly which table is referenced." for PQftable. >> I query pg_class and the only column that looks remotely like a unique >> oid is relfilenode. >> > > select oid, relname from pg_class where ... > > relfilenode is just the file name given to the table, which is the same > as the OID when the table is just created, but changes after certain > operations (CLUSTER, TRUNCATE, REINDEX for indexes, maybe others) > >