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
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
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.
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
The OID of the table itself, which is always present. That is the OID the driver is querying for. It most likely refers to the result of the PQftable command in the libpg by which the orign of a PGresult can be determined: http://www.postgresql.org/docs/9.5/static/libpq-exec.html#LIBPQ-PQFTABLE
Why couldn’t the call to pg_class be postponed till Recordset.Edit is invoked?
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?