Re: [gnome-db] postgresql provider performance

["Vivien Malerba" <vmalerba gmail com>]

On Jan 30, 2008 4:04 PM, Mark Johnson <mrj001 shaw ca> wrote:
> 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.

I believe the information is in the existence of a row returned or no.

> > 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?

It's in "V4-branch" branch in the Gnome SVN repository
(http://svn.gnome.org/viewvc/libgda/branches/V4-branch/).

Tell me of any problem you experience...

Cheers!