Re: [GENERAL] Problem with oids for table names getting out of sync?
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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with oids for table names getting out of sync?
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with oids for table names getting out of sync?
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with oids for table names getting out of sync?
Omar Eljumaily <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problem with oids for table names getting out of sync?
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
