473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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?
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
7 1378
On Fri, Jul 16, 2004 at 03:38:52 +0200,
Michal Maru?ka <mm*@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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
Bruno Wolff III <br***@wolff.to> writes:
On Fri, Jul 16, 2004 at 03:38:52 +0200,
Michal Maru?ka <mm*@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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
mm*@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4
Tom Lane <tg*@sss.pgh.pa.us> writes:
mm*@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5
mm*@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
Tom Lane <tg*@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6

Tom Lane <tg*@sss.pgh.pa.us> writes:
mm*@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
Tom Lane <tg*@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7
Tom Lane <tg*@sss.pgh.pa.us> writes:
mm*@maruska.dyndns.org (Michal =?iso-8859-2?q?Maru=B9ka?=) writes:
Tom Lane <tg*@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 ?
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bernard André | last post by:
Hi All, context: I am using Access 97 tablkes with VB. I can see records in the MDB, using Adodc and datagrid. No problem. But when doing: rsprivate.AddNew rsprivate!For =...
5
by: Leonardo Almeida | last post by:
This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a...
1
by: Leonardo Almeida | last post by:
This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a...
0
by: Sean | last post by:
I received this error when a trigger attempted to perform an update to its own table with a where clause that did not guarantee a single row. I believe this results in the iterative firing of this...
2
by: derekbarrett | last post by:
Hi, I found this article in DB2 magazine and learned about the Problem Determination Mastery Exam. I am very interested in taking the exam, however, following the links in the article leads to...
1
by: Kevin | last post by:
I'm using a "View" in SQL2000 to print records. My query on the View worked fine in VB6. I'm using the same ADODB in my VB2005 app (not ADO.NET) and I'm getting this error. There's 197 records in...
9
by: manish | last post by:
we have avariablr int bps; // bps determined during run time - - - how can we make conditional declaration of a variable y we want appropriate declaration depending upon bps.
1
by: graju80 | last post by:
Question: What are the rules that DB2 uses to determine the right datatype for a particular column for on-the-fly SQL generation? For example... The following code snippet:
0
by: graju80 | last post by:
I am kind of new to Db2... Question: What are the rules that DB2 uses to determine the right datatype for a particular column for on-the-fly SQL generation? For example...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.