Lists: | pgsql-hackers |
---|
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2014-11-22 03:49:26 |
Message-ID: | 547007C6.8040204@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
A common mistake is to try and qualify column references on the LHS of
SET in UPDATE. The error message can be a bit cryptic at times, too.
I've attached a patch to try and improve the situation a bit:
=# create table foo(bar int);
CREATE TABLE
=# update foo set foo.bar = 1;
ERROR: column "foo" of relation "foo" does not exist
LINE 1: update foo set foo.bar = 1;
^
HINT: Target column references in UPDATE must not be qualified
=# update foo set notfoo.bar = 1; -- no hint
ERROR: column "notfoo" of relation "foo" does not exist
LINE 1: update foo set notfoo.bar = 1;
^
=# update foo set foo.bar[1] = 1;
ERROR: column "foo" of relation "foo" does not exist
LINE 1: update foo set foo.bar[1] = 1;
^
HINT: Target column references in UPDATE must not be qualified
I'll add this to the next CF, but feel free to bash it before that.
.marko
Attachment | Content-Type | Size |
---|---|---|
update_qlf_hint.v1.patch | text/plain | 1.7 KB |
From: | Peter Geoghegan <pg(at)heroku(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2014-11-22 04:11:20 |
Message-ID: | CAM3SWZTjOUO=tv8SnRxD=8CAcE+Wz2hVj0vn-c0J3K+7tPMW3g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Fri, Nov 21, 2014 at 7:49 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> A common mistake is to try and qualify column references on the LHS of SET
> in UPDATE.
I think that this is a good idea, but as written the patch doesn't
handle aliases correctly:
postgres=# create table foo (val text);
CREATE TABLE
postgres=# update foo f set val = 'bar' where f.val != 'fd';
UPDATE 0
postgres=# update foo f set f.val = 'bar' where f.val != 'fd';
ERROR: 42703: column "f" of relation "foo" does not exist
LINE 1: update foo f set f.val = 'bar' where f.val != 'fd';
^
LOCATION: transformUpdateStmt, analyze.c:2015
--
Peter Geoghegan
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Peter Geoghegan <pg(at)heroku(dot)com> |
Cc: | "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2014-11-22 11:58:28 |
Message-ID: | 54707A64.6060500@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 2014-11-22 05:11, Peter Geoghegan wrote:
> On Fri, Nov 21, 2014 at 7:49 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> A common mistake is to try and qualify column references on the LHS of SET
>> in UPDATE.
>
> I think that this is a good idea, but as written the patch doesn't
> handle aliases correctly:
>
> postgres=# create table foo (val text);
> CREATE TABLE
> postgres=# update foo f set val = 'bar' where f.val != 'fd';
> UPDATE 0
> postgres=# update foo f set f.val = 'bar' where f.val != 'fd';
> ERROR: 42703: column "f" of relation "foo" does not exist
> LINE 1: update foo f set f.val = 'bar' where f.val != 'fd';
> ^
> LOCATION: transformUpdateStmt, analyze.c:2015
Good point! Changed in v2, attached.
.marko
Attachment | Content-Type | Size |
---|---|---|
update_qlf_hint.v2.patch | text/plain | 1.8 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2014-11-22 17:02:16 |
Message-ID: | 9691.1416675736@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Marko Tiikkaja <marko(at)joh(dot)to> writes:
> A common mistake is to try and qualify column references on the LHS of
> SET in UPDATE. The error message can be a bit cryptic at times, too.
Perhaps, but this hint is not much of an improvement:
> HINT: Target column references in UPDATE must not be qualified
because target column references *can* be "qualified", for example
if you have a composite column datatype you can assign directly to
one of its fields. (This ambiguity is exactly why we don't simply
allow the case.) So I don't think that "qualified" is a sufficiently
precise phrase to be helpful. Possibly something along the lines of
HINT: Do not qualify an UPDATE target column with the name of the table.
Also, the coding technique used here is poor, because the hint text
will not be exposed for translation. The usual pattern is
need_hint ? errhint("Message text here.") : 0
Also, as far as Peter's point goes, it would likely make sense to
issue this hint if the column basename is *either* the alias name
or the underlying table name.
regards, tom lane
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2014-11-22 17:34:09 |
Message-ID: | 5470C911.5040800@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 2014-11-22 18:02, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> A common mistake is to try and qualify column references on the LHS of
>> SET in UPDATE. The error message can be a bit cryptic at times, too.
>
> Perhaps, but this hint is not much of an improvement:
>
>> HINT: Target column references in UPDATE must not be qualified
>
> because target column references *can* be "qualified", for example
> if you have a composite column datatype you can assign directly to
> one of its fields. (This ambiguity is exactly why we don't simply
> allow the case.) So I don't think that "qualified" is a sufficiently
> precise phrase to be helpful. Possibly something along the lines of
>
> HINT: Do not qualify an UPDATE target column with the name of the table.
Sounds good to me. I didn't expect anyone to like the wording of the
hint in the first place ;-)
> Also, the coding technique used here is poor, because the hint text
> will not be exposed for translation. The usual pattern is
>
> need_hint ? errhint("Message text here.") : 0
Oops. I just copied what the first relevant grep of errhint did, which
was from postgres_fdw. But its hints will already have been translated.
Will fix.
> Also, as far as Peter's point goes, it would likely make sense to
> issue this hint if the column basename is *either* the alias name
> or the underlying table name.
Yeah, I thought about that too, but I thought that might be weird if
there's an alias in FROM with the name of the table, e.g:
UPDATE foo f1 SET foo.a = 1 FROM foo;
But I don't really care too much either way.
.marko
From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-01-07 17:22:37 |
Message-ID: | 20150107172237.GZ1457@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
We're waiting for an updated version here, right?
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-01-07 17:28:41 |
Message-ID: | 54AD6CC9.1020404@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 1/7/15 6:22 PM, Alvaro Herrera wrote:
> We're waiting for an updated version here, right?
Yeah. (The CF entry is also set to Waiting on Author, which seems
appropriate.)
.marko
From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-01-15 08:04:50 |
Message-ID: | CAB7nPqQ9y=+mSO__1KQFPmi7bzTqLTU24Z9TDeXVS0wtY1+WFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Thu, Jan 8, 2015 at 2:28 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> Yeah. (The CF entry is also set to Waiting on Author, which seems
> appropriate.)
Seeing nothing happening here for quite some time, marked as returned
with feedback..
--
Michael
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-09-01 21:19:28 |
Message-ID: | 55E61660.7020508@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hi,
Hopefully nobody minds if I slip this to the commit fest that started
today? The attached patch should address all the comments from the 9.5
cycle.
.m
Attachment | Content-Type | Size |
---|---|---|
update_qlf_hint.v3.patch | text/plain | 1.8 KB |
From: | Marko Tiikkaja <marko(at)joh(dot)to> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-09-01 21:24:01 |
Message-ID: | 55E61771.4040008@joh.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 2015-09-01 23:19, I wrote:
> Hopefully nobody minds if I slip this to the commit fest that started
> today? The attached patch should address all the comments from the 9.5
> cycle.
Apparently the CF app will. Meh. Whatever. Ignore then, I guess.
.m
From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-12-07 12:14:55 |
Message-ID: | CAB7nPqQoYYChbg-6=o9uAG7e2rh2gqZEPsKhQdQhGE3k=2wxxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Wed, Sep 2, 2015 at 6:19 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> Hopefully nobody minds if I slip this to the commit fest that started
> today? The attached patch should address all the comments from the 9.5
> cycle.
>
All the previous comments have been addressed. Perhaps some regression
tests would have some value?
--
Michael
From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2015-12-24 02:15:49 |
Message-ID: | CAB7nPqSYbhBLe=4P0G=SfoUeTYa4j3EYFBPdaozrJUqE7QeKWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Mon, Dec 7, 2015 at 9:14 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
>
>
> On Wed, Sep 2, 2015 at 6:19 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>>
>> Hopefully nobody minds if I slip this to the commit fest that started
>> today? The attached patch should address all the comments from the 9.5
>> cycle.
>
>
> All the previous comments have been addressed. Perhaps some regression tests
> would have some value?
This thread has been stalling for a couple of weeks now. I have marked
it as "returned with feedback". Marko, if you are still working on
this patch, could you add some regression tests and then move it to
the next CF?
--
Michael
From: | David Steele <david(at)pgmasters(dot)net> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Marko Tiikkaja <marko(at)joh(dot)to> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-hackers >> PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: HINTing on UPDATE foo SET foo.bar = ..; |
Date: | 2016-03-09 22:39:33 |
Message-ID: | 56E0A625.2010106@pgmasters.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 12/23/15 9:15 PM, Michael Paquier wrote:
> On Mon, Dec 7, 2015 at 9:14 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Wed, Sep 2, 2015 at 6:19 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>>> Hopefully nobody minds if I slip this to the commit fest that started
>>> today? The attached patch should address all the comments from the 9.5
>>> cycle.
>> All the previous comments have been addressed. Perhaps some regression tests
>> would have some value?
> This thread has been stalling for a couple of weeks now. I have marked
> it as "returned with feedback". Marko, if you are still working on
> this patch, could you add some regression tests and then move it to
> the next CF?
This was submitted to the 2016-03 CF but no new patch was supplied and
there's been no activity on the thread for months. I'm marking it as
"returned with feedback."
Marko, if can address Michael's concerns or supply a new patch I'll be
happy to open the CF entry again.
Thanks,
--
-David
david(at)pgmasters(dot)net