Re: [gnome-db] postgresql provider performance

[Mark Johnson <mrj001 shaw ca>]

Vivien Malerba wrote:
> > Second, what is the point of all the "Select 1..." queries after the
> > "deallocate"?  There are two of them for every column in the table
> > referenced by the prepared statement (created by the postgres provider's
> > implementation of gda_query_execute).  They do not produce meaningful
> > results when executed in PostgreSQL's  psql client.  The where condition
> > appears to be trying to check for unique and primary key constraints.
> > However, the "1" in the select fields is a constant!
> >     
>
> It seems the "SELECT 1..." query is sent whenever
> gda_data_model_describe_column() is called, and it can be optimized to
> be called only once...
>   
But what information does it provide?  Here is a sample session from psql:
issue001_db=# \d+ slots
                        Table "public.slots"
     Column       |          Type          | Modifiers | Description
-------------------+------------------------+-----------+-------------
slot_id           | integer                | not null  |
obj_guid          | character(32)          | not null  |
name              | character varying(500) | not null  |
slot_type         | integer                | not null  |
int64_val         | bigint                 |           |
string_val        | character varying(500) |           |
double_val        | bigint                 |           |
timespec_val      | date                   |           |
guid_val          | character(32)          |           |
numeric_val_num   | bigint                 |           |
numeric_val_denom | bigint                 |           |
Indexes:
   "slots_pkey" PRIMARY KEY, btree (slot_id)
Has OIDs: no

issue001_db=# SELECT 1 FROM pg_catalog.pg_class c, 
pg_catalog.pg_constraint c2, pg_catalog.pg_attribute a WHERE c.relname = 
'slots' AND c.oid = c2.conrelid and a.attrelid = c.oid and c2.contype = 
'P' and c2.conkey[1] = a.attnum and a.attname = 'slot_id';
?column?
----------
(0 rows)

There is not a single row returned by this query.  'slot-id' is the 
primary key column, which seems to be something this query might be 
looking for.  Does this possibly behave differently in an older version 
of PostgreSQL?  I am using 8.2.6.
> However, I'm in the process of modifying the way database providers
> are implemented so I'll do a full review of the PostgreSQL's provider
> implementation in the near future and won't work on it before that
> except if you really need it.
>
> Also, my understanding is that you are trying to make the Libgda
> backend of Gnucash working correctly, if I'm wrong, forget the rest of
> this paragraph. As I'm currently working on the V4 version of Libgda,
> I've started to introduce some API changes to make the library easier
> to use and understand and easier to implement providers, but with some
> API changes, so I would suggest that you work on that backend with the
> still-unreleased V4 version which is already useable to avoid having
> to make yet another port to V4.
>
>   
> > Should I file a bug against the postgresql provider?
> >     
>
> Yes, please, so I can keep a reminder of that specific problem.
>   
Bug513149
http://bugzilla.gnome.org/show_bug.cgi?id=513149

I am testing the gda backend for gnucash.  I would be happy to use the 
unstable version 4.0 to try it.  Where do I find the code?  I did not 
see a branch for working on 4.0.  Is it trunk I should try for this?
> Regards,
>
> Vivien
>   
Mark