Thread: PQftable insufficient for primary key determination

Imagine i have a table A with primary key "p" and another attribute called "data":

SELECT * from A, A, B;

here, i will have 2 columns "data", PQftable tells me that they are from A, but
which of the 2 columns "p" is the primary key of the tuple (of the table A)?


Is it possible to get distinguish the 2 instances of the table A?

On Fri, Jul 16, 2004 at 03:38:52 +0200,
  Michal Maru?ka <mmc@maruska.dyndns.org> wrote:
>
>
> Imagine i have a table A with primary key "p" and another attribute called "data":
>
> SELECT * from A, A, B;
>
> here, i will have 2 columns "data", PQftable tells me that they are from A, but
> which of the 2 columns "p" is the primary key of the tuple (of the table A)?
>
>
> Is it possible to get distinguish the 2 instances of the table A?

You can use aliases to distinguish between two references to the same
table in a query. Your question doesn't make a lot of sense though, since
you haven't said which of the two copies of A you are interested in.

Bruno Wolff III <bruno@wolff.to> writes:

> On Fri, Jul 16, 2004 at 03:38:52 +0200,
>   Michal Maru?ka <mmc@maruska.dyndns.org> wrote:
>>
>>
>> Imagine i have a table A with primary key "p" and another attribute called "data":
>>
>> SELECT * from A, A, B;
>>
>> here, i will have 2 columns "data", PQftable tells me that they are from A, but
>> which of the 2 columns "p" is the primary key of the tuple (of the table A)?
>>
>>
>> Is it possible to get distinguish the 2 instances of the table A?



> You can use aliases to distinguish between two references to the same
> table in a query. Your question doesn't make a lot of sense though, since
> you haven't said which of the two copies of A you are interested in.

i don't want to force the user to distinguish 'manually', nor depend on it.

EXPLAIN VERBOSE {query}  seems to provide that information, if i walk down
:varno, i *guess*. I haven't found any reference documentation  on the output of
the EXPLAIN VERBOSE.


So, my question is: should i look at the code which walks that tree
(probably related to the function SendRowDescriptionMessage), or
is this code (which provides the distinguishing info) already available?

mmc@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
> So, my question is: should i look at the code which walks that tree
> (probably related to the function SendRowDescriptionMessage), or
> is this code (which provides the distinguishing info) already available?

The problem is you haven't said what it is you want to distinguish.
Yes, p is the primary key of A ... so then what?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> mmc@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
>> So, my question is: should i look at the code which walks that tree
>> (probably related to the function SendRowDescriptionMessage), or
>> is this code (which provides the distinguishing info) already available?
>
> The problem is you haven't said what it is you want to distinguish.
> Yes, p is the primary key of A ... so then what?
>


hm, i should have written "primary key _value_".

SELECT * from A, A ....

gives a result table like:

p | data | p |data |....
--------------------
1 |  xxx | 2 | yyy | ...


Now you edit the value  'yyy'  and want to commit this change to the DB:

update A set data = 'zzz' where  p = primary-key-value;

How to determine what to use for 'primary-key-value'? The value from the 1st
column (PQftable gives A) or from the 3rd column (PQftable gives A again)?


thanks

mmc@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> The problem is you haven't said what it is you want to distinguish.

> SELECT * from A, A ....

> gives a result table like:

> p | data | p |data |....
> --------------------
> 1 |  xxx | 2 | yyy | ...


> Now you edit the value  'yyy'  and want to commit this change to the DB:

> update A set data = 'zzz' where  p = primary-key-value;

> How to determine what to use for 'primary-key-value'? The value from the 1st
> column (PQftable gives A) or from the 3rd column (PQftable gives A again)?

I don't think this is a well-defined problem.  What does the user think
he's doing when he edits yyy of the join output?  There may be multiple
copies of that value in the output table, if the row that it came from
joined to multiple rows in the other tables.  In that case it would be
impossible to alter a single field value without changing other rows of
the displayed result.  So at least in the general case, I don't think
it makes sense to allow editing of fields of join results.

If you have knowledge about the form of the query that's sufficient to
guarantee that this problem won't occur, then I'd suggest taking another
look at that knowledge and seeing if it doesn't offer a solution.  But
in the perfectly general form that you've stated the issue, I don't see
a solution.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> mmc@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> The problem is you haven't said what it is you want to distinguish.
>
>> SELECT * from A, A ....
>
>> gives a result table like:
>
>> p | data | p |data |....
>> --------------------
>> 1 |  xxx | 2 | yyy | ...
>
>
>> Now you edit the value  'yyy'  and want to commit this change to the DB:
>
>> update A set data = 'zzz' where  p = primary-key-value;
>
>> How to determine what to use for 'primary-key-value'? The value from the 1st
>> column (PQftable gives A) or from the 3rd column (PQftable gives A again)?



> I don't think this is a well-defined problem.  What does the user think
> he's doing when he edits yyy of the join output?  There may be multiple
> copies of that value in the output table, if the row that it came from
> joined to multiple rows in the other tables.  In that case it would be
> impossible to alter a single field value without changing other rows of
> the displayed result.  So at least in the general case, I don't think
> it makes sense to allow editing of fields of join results.

That should be solvable by the data editing applications. Having the mapping
(result-column -> relation instance), and detecting the primary key should be
sufficient to solve it, imo.

I want to offer the user the possibility to submit hand written SQL, and edit
what is 'editable' (and possibly propagating the changes to other cells of the result).


But i still think, that the code which provides the PQftable info walks the plan
'tree' following the :varno & other info which i don't know well, and in the
end, having some index to an array of used 'instances' of relations, translates
the index into a plain relname, _throwing_ away a possibly useful info (which
could group some columns as coming from the same 'instance'/ tuples).


> If you have knowledge about the form of the query that's sufficient to
> guarantee that this problem won't occur, then I'd suggest taking another
> look at that knowledge and seeing if it doesn't offer a solution.  But
> in the perfectly general form that you've stated the issue, I don't see
> a solution.

This should be a general data editor. Sure, the user should include enough
attributes in the SELECT.  And it should work w/ VIEWs too.

And i hope to obtain this info from the information
provided by the server itself.


>             regards, tom lane

thanks for your attention.

On Fri, 16 Jul 2004, Tom Lane wrote:

> I don't think this is a well-defined problem.  What does the user think
> he's doing when he edits yyy of the join output?  There may be multiple
> copies of that value in the output table, if the row that it came from
> joined to multiple rows in the other tables.  In that case it would be
> impossible to alter a single field value without changing other rows of
> the displayed result.  So at least in the general case, I don't think
> it makes sense to allow editing of fields of join results.

I agree, but something that would be nice along these lines is the ability
to detect if the results from a user provided query are from a join.  For
example the JDBC driver supports updatable ResultSets and right now the
detection code to determine if a given query can be updated involves a
very broken inspection of the query string.  I was thinking this could be
done by checking the source tables of the result.  At the moment this
can't tell if a self join is involved.  It would also have problems in
a situation where a join was performed but only columns from one table
were selected.  Allowing an update here could affect multiple rows of the
result, but still should only affect one source row.  I don't like this
idea, but I don't have any better ideas than trying to implement a full
sql parser in the JDBC driver.  Perhaps this will have to wait for
updatable cursors.

Kris Jurka

Tom Lane <tgl@sss.pgh.pa.us> writes:

> mmc@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> The problem is you haven't said what it is you want to distinguish.
>
>> SELECT * from A, A ....
>
>> gives a result table like:
>
>> p | data | p |data |....
>> --------------------
>> 1 |  xxx | 2 | yyy | ...
>
>
>> Now you edit the value  'yyy'  and want to commit this change to the DB:
>
>> update A set data = 'zzz' where  p = primary-key-value;
>
>> How to determine what to use for 'primary-key-value'? The value from the 1st
>> column (PQftable gives A) or from the 3rd column (PQftable gives A again)?
>
> I don't think this is a well-defined problem.  What does the user think
> he's doing when he edits yyy of the join output?  There may be multiple
> copies of that value in the output table, if the row that it came from
> joined to multiple rows in the other tables.  In that case it would be
> impossible to alter a single field value without changing other rows of
> the displayed result.  So at least in the general case, I don't think
> it makes sense to allow editing of fields of join results.
>
> If you have knowledge about the form of the query that's sufficient to
> guarantee that this problem won't occur, then I'd suggest taking another
> look at that knowledge and seeing if it doesn't offer a solution.  But
> in the perfectly general form that you've stated the issue, I don't see
> a solution.

i have looked a bit at the comments in header files. I seem to understand, that
what i want is:

in SendRowDescriptionMessage()  src/backend/access/common/printtup.c

when it does
             pq_sendint(&buf, res->resorigtbl, 4);

i would like to add

     Var  *source = (Var*) ( ((TargetEntry *) lfirst(targetlist))->expr );

     pq_sendint(&buf, source->varnoold, 4);


i should probably test if the ->expr is of type  Var by looking at the  NodeTag
type;   But i don't have an idea what condition to test.


And of course i don't have an idea how to extend the protocol, to send this
value w/o breaking other things.


>             regards, tom lane

hints, please ?