Thread: Can't find relation oid
Hi, I have the following problem: I use libpq inside SRF functions (like in dblink) and I create some tables to store results coming from remotly executed queries. (These tables are not temporary cause I want to use them later as a form of cache.) Then I try to open these tables to get the results. Actually I modify an already available Query to refer to these new tables instead of the correspoding local tables. To do so I need the Oids of theses new tables which I get using the following query: select oid from pg_class where relname='foo' The problem is that when the new tables are created for the first time, the above query returns no results and the whole thing fails.Everything works fine if they have already been created by a previous attempt to run my SRF. I tried to isolate each step (creating tables, retrieving results) in a new transaction block (like in testlibpq.c example in the documentation) but with no result. Maybe the whole process described above runs in a sigle transaction block i.e. that of my SRF(?). I'm a little confused here. Any ideas? Regards, Ntinos Katsaros
On Mon, Jan 03, 2005 at 02:32:44PM +0200, ntinos@aueb.gr wrote: > Hi, > > I have the following problem: > > I use libpq inside SRF functions (like in dblink) and I create some tables > to store results coming from remotly executed queries. (These tables are > not temporary cause I want to use them later as a form of cache.) Then I > try to open these tables to get the results. Actually I modify an already > available Query to refer to these new tables instead of the correspoding > local tables. To do so I need the Oids of theses new tables which I get > using the following query: > > select oid from pg_class where relname='foo' What language? What version of postgresql? It's not exactly clear from your description but is there a reason you are using libpq and not the SPI interface? If you create a connection using libpq to the same database then the queries executed through that connection will not be able to see the transaction of the SRF you are writing. The SPI interface is for executing queries within the same transaction. http://www.postgresql.org/docs/current/static/spi.html If this doesn't help maybe you should post an example (with code) of what your problem actually is. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout writes: > > On Mon, Jan 03, 2005 at 02:32:44PM +0200, ntinos@aueb.gr wrote: >> Hi, >> >> I have the following problem: >> >> I use libpq inside SRF functions (like in dblink) and I create some tables >> to store results coming from remotly executed queries. (These tables are >> not temporary cause I want to use them later as a form of cache.) Then I >> try to open these tables to get the results. Actually I modify an already >> available Query to refer to these new tables instead of the correspoding >> local tables. To do so I need the Oids of theses new tables which I get >> using the following query: >> >> select oid from pg_class where relname='foo' > > What language? What version of postgresql? I'm using C on PostgreSQL 7.4.2 > It's not exactly clear from your description but is there a reason you > are using libpq and not the SPI interface? >If you create a connection > using libpq to the same database then the queries executed through that > connection will not be able to see the transaction of the SRF you are > writing. The SPI interface is for executing queries within the same > transaction. Now I think I understand what happens. You see, I used libpq to create the tables and SPI to retrieve the Oids ($%&^#$%&!!). When I tried to also retrieve the Oid using libp it worked. I suppose using SPI both to create the tables and retrieve the Oids will be the right solution. I'll try it! > Hope this helps, It did help to understand what was going on! Thanks!! Ntinos Katsaros