PostgreSQL - what about 'SELECT relname FROM pg_class ...' ?

Databases
1

Whenever a single select statement ran against the PG database, I have seen a following ‘SELECT relname FROM pg_class WHERE oid=?’ (obviously from the DB driver, not from the app’s code). The DB returns the name of the table I queried with the previous select statement …

But why?

postgresql.log:
2016-04-02 13:21:25 CEST [15763-6] cdr@test LOG: statement: SELECT * FROM Team
2016-04-02 13:21:26 CEST [15763-7] cdr@test LOG: statement: SELECT relname FROM pg_class WHERE oid=25596

2

the record set retrieved from postgres tells us the OID - not the name of the table - and in order to create an editable recordset (if possible) we need the name

3

Thanks Norman,

but I still do not understand what is going on. PostgreSQL will only return the oid for a record/result when the table was created as ‘create table … with oids’. As my tables do not use this switch (and a ‘select oid,* from mytable’ fails as ‘column oid does not exists’), a select can not return the oid of the record.

2nd: the statement ‘select relname from pg_class where oid = ?’ will always return the tablename of the prior select … but why?

3rd: if you have a large number of selects, there is a great overhead by sending always this additional ‘select relname from pg_class …’ - spec. for network based DBs.

4

I’d always create table with them but …

Because the plugin needs it in the event you edit the recordset returned

[quote=259139:@Chris Drauch]
3rd: if you have a large number of selects, there is a great overhead by sending always this additional ‘select relname from pg_class …’ - spec. for network based DBs.[/quote]
Since a query has, in the Xojo API, no way to say “and do not give me a recordset that is editable” there’s no way to avoid this at present

5

we are talking here about two different OIDs:

  • The WITH (OIDS=TRUE) one is assigning an OID per record within the table, which is disabled by default.

Please see: http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS

but: http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-NOTES

Why couldn’t the call to pg_class be postponed till Recordset.Edit is invoked?

6

[quote=260446:@Tobias Bussmann]
Why couldn’t the call to pg_class be postponed till Recordset.Edit is invoked?[/quote]

  1. Because that isn’t how the code is structured
  2. Because by then it’s too late since the query you got the data from is long gone & closed
7

thank you for the explanation

8

Hello again,

I would like to suggest caching of the tables OIDs (pg_class) - that would be a great performance win (and reduce network load).

@Tobias: refering to http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-NOTES ->

Notes

Using OIDs in new applications is not recommended: where possible, using a SERIAL or other sequence generator as the table’s primary key is preferred. However, if your application does make use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint on the oid column of that table, to ensure that OIDs in the table will indeed uniquely identify rows even after counter wraparound. Avoid assuming that OIDs are unique across tables; if you need a database-wide unique identifier, use the combination of tableoid and row OID for the purpose.

Maybe using UUID would even be better …

@Norman: how is the internal recordset of XOJO structured if there is no (Postgres) OID available at recordlevel or how do you manage to form a unique key within the recordset?

9

Record sets need to select the primary key of any given table in order to support editing
That way individual rows can reliably be updated