Обсуждение: BUG #14397: Updatable VIEW doesn't work with ON CONFLICT from PgPreparedStatement

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5NwpMb2dnZWQgYnk6ICAg
ICAgICAgIEdyeWdvcml5IEdvbmNoYXIKRW1haWwgYWRkcmVzczogICAgICBn
cnlnb3JpeS5nb25jaGFyQGtyZWRpdGVjaC5jb20KUG9zdGdyZVNRTCB2ZXJz
aW9uOiA5LjUuNApPcGVyYXRpbmcgc3lzdGVtOiAgIHg4Nl82NC1wYy1saW51
eC1nbnUsIGNvbXBpbGVkIGJ5IGdjYyAoVWJ1bnR1IDQuOC4yCkRlc2NyaXB0
aW9uOiAgICAgICAgCgpJIGhhdmUgYSB2aWV3IGRlZmluZWQgYXM6DQpDUkVB
VEUgT1IgUkVQTEFDRSBWSUVXIFRFU1QgQVMgU0VMRUNUICogRlJPTSBURVNU
XzEgV0lUSCBDQVNDQURFRCBDSEVDSwpPUFRJT047DQoNClRoaXMgaXMgbmVj
ZXNzYXJ5IHRvIGFic3RyYWN0IGZyb20gdGFibGUgbmFtZSB3aGlsZSBhcHBs
aWNhdGlvbiBjb2RlIGlzCndvcmtpbmcgdmlhIHZpZXcgbmFtZSAodG8gaW1w
bGVtZW50IGEga2luZCBvZiBzaW1wbGUgcGFydGl0aW9uaW5nKQ0KDQpXaGVu
IEkgcnVuIHRoZSBmb2xsb3dpbmcgcXVlcnkgb24gdmlldzoNCiANCiAgIGlu
c2VydCBpbnRvIHRlc3QgKGMxLCBjMiwgYzMpIHZhbHVlcyAoPywgPywgPykg
b24gY29uZmxpY3QgKGMxLCBjMikgZG8KdXBkYXRlIHNldCBjMz1leGNsdWRl
ZC5jMw0KDQpJIGdldCB0aGUgZm9sbG93aW5nIGVycm9yOg0KDQpBbiBleGNl
cHRpb24gb3IgZXJyb3IgY2F1c2VkIGEgcnVuIHRvIGFib3J0OiBFUlJPUjog
T04gQ09ORkxJQ1QgaXMgbm90CnN1cHBvcnRlZCBvbiB0YWJsZSAidGVzdCIg
dXNlZCBhcyBhIGNhdGFsb2cgdGFibGUNCiAgUG9zaXRpb246IDk4IA0Kb3Jn
LnBvc3RncmVzcWwudXRpbC5QU1FMRXhjZXB0aW9uOiBFUlJPUjogT04gQ09O
RkxJQ1QgaXMgbm90IHN1cHBvcnRlZCBvbgp0YWJsZSAiZW5jcnlwdGVkX3Zh
bHVlIiB1c2VkIGFzIGEgY2F0YWxvZyB0YWJsZQ0KICBQb3NpdGlvbjogOTgN
CglhdApvcmcucG9zdGdyZXNxbC5jb3JlLnYzLlF1ZXJ5RXhlY3V0b3JJbXBs
LnJlY2VpdmVFcnJvclJlc3BvbnNlKFF1ZXJ5RXhlY3V0b3JJbXBsLmphdmE6
MjMxMCkNCglhdApvcmcucG9zdGdyZXNxbC5jb3JlLnYzLlF1ZXJ5RXhlY3V0
b3JJbXBsLnByb2Nlc3NSZXN1bHRzKFF1ZXJ5RXhlY3V0b3JJbXBsLmphdmE6
MjAyMykNCglhdApvcmcucG9zdGdyZXNxbC5jb3JlLnYzLlF1ZXJ5RXhlY3V0
b3JJbXBsLmV4ZWN1dGUoUXVlcnlFeGVjdXRvckltcGwuamF2YToyMTcpDQoJ
YXQgb3JnLnBvc3RncmVzcWwuamRiYy5QZ1N0YXRlbWVudC5leGVjdXRlKFBn
U3RhdGVtZW50LmphdmE6NDIxKQ0KCWF0Cm9yZy5wb3N0Z3Jlc3FsLmpkYmMu
UGdQcmVwYXJlZFN0YXRlbWVudC5leGVjdXRlV2l0aEZsYWdzKFBnUHJlcGFy
ZWRTdGF0ZW1lbnQuamF2YToxNjYpDQoJYXQKb3JnLnBvc3RncmVzcWwuamRi
Yy5QZ1ByZXBhcmVkU3RhdGVtZW50LmV4ZWN1dGVVcGRhdGUoUGdQcmVwYXJl
ZFN0YXRlbWVudC5qYXZhOjEzNykNCg0KSWYgSSBydW4gdGhlIHNhbWUgcXVl
cnkgd2l0aG91dCBwcmVwYXJlZCBzdGF0ZW1lbnQgZnJvbSBjb21tYW5kIGxp
bmUgYWxsCndvcmtzIGZpbmUNCg0KSWYgSSBydW4gdGhlIHNhbWUgcXVlcnkg
b24gdGFibGUgZGlyZWN0bHkgYWxsIHdvcmtzIGZpbmUgYm90aCBmcm9tIGNv
bW1hbmQKbGluZSBhbmQgUGdQcmVwYXJlZFN0YXRlbWVudA0KDQpMb29rcyB0
byBtZSBsaWtlIGEgYnVnIHdpdGggUGdQcmVwYXJlZFN0YXRlbWVudAoK

On Wed, Oct 26, 2016 at 2:24 AM,  <grygoriy.gonchar@kreditech.com> wrote:
> If I run the same query on table directly all works fine both from command
> line and PgPreparedStatement
>
> Looks to me like a bug with PgPreparedStatement

Have you reported the bug to pgjdbc?


--
Peter Geoghegan

On Wed, Oct 26, 2016 at 2:24 AM, <grygoriy.gonchar@kreditech.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14397
> Logged by:          Grygoriy Gonchar
> Email address:      grygoriy.gonchar@kreditech.com
> PostgreSQL version: 9.5.4
> Operating system:   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2
> Description:
>
> =E2=80=8B[...]=E2=80=8B
>
>
> An exception or error caused a run to abort: ERROR: ON CONFLICT is not
> supported on table "test" used as a catalog table
>

=E2=80=8BAny chance you cross-posted this to Stack Overflow?  A Google sear=
ch on
"used as a catalog table" turns up a post from 5 days ago that is awfully
similar.

=E2=80=8B
http://stackoverflow.com/questions/40260920/updatable-view-doesnt-work-with=
-on-conflict-in-postgres-9-5
=E2=80=8B

=E2=80=8BThe conclusion there is that we seem to have a RLS interaction fai=
lure.  I
suspect this isn't a code bug but a known, if under-documented, limitation
though others more versed with these two features need to chime in.

David J.

On Mon, Oct 31, 2016 at 10:27 AM, Grygoriy Gonchar <
grygoriy.gonchar@kreditech.com> wrote:

> =E2=80=8B
> On Mon, Oct 31, 2016 at 6:09 PM, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>>
>>
>> On Wed, Oct 26, 2016 at 2:24 AM, <grygoriy.gonchar@kreditech.com> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      14397
>>> Logged by:          Grygoriy Gonchar
>>> Email address:      grygoriy.gonchar@kreditech.com
>>> PostgreSQL version: 9.5.4
>>> Operating system:   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2
>>> Description:
>>>
>>> =E2=80=8B[...]=E2=80=8B
>>>
>>>
>>> An exception or error caused a run to abort: ERROR: ON CONFLICT is not
>>> supported on table "test" used as a catalog table
>>>
>>
>> =E2=80=8BAny chance you cross-posted this to Stack Overflow?  A Google s=
earch on
>> "used as a catalog table" turns up a post from 5 days ago that is awfull=
y
>> similar.
>>
>> =E2=80=8Bhttp://stackoverflow.com/questions/40260920/updatable-view-
>> doesnt-work-with-on-conflict-in-postgres-9-5=E2=80=8B
>>
>> =E2=80=8BThe conclusion there is that we seem to have a RLS interaction =
failure.
>> I suspect this isn't a code bug but a known, if under-documented,
>> limitation though others more versed with these two features need to chi=
me
>> in.
>>
>> David J.
>>
> =E2=80=8B"""=E2=80=8B
=E2=80=8BHi David and Peter,

Yes, I did this cross posting to =E2=80=8B
http://stackoverflow.com/questions/40260920/updatable-view-doesnt-work-with=
-on-conflict-in-postgres-9-5=E2=80=8B
So it's not a bug but a limitation which is quite logical but is not
reflected in documentation. According to Postgres bug reporting guideline
"If it is not clear from the documentation whether you can do something or
not, please report that too; it is a bug in the documentation" should I
create a separate bug to improve documentation or can we use this one?
=E2=80=8B"""

Please don't top-post and, in the future, mention any cross-posting that
you do.

This bug report will suffice as a request for improved documentation if
indeed that is the ultimate problem.

David J.

Hi David and Peter,

Yes, I did this cross posting to =E2=80=8Bhttp://stackoverflow.com/
questions/40260920/updatable-view-doesnt-work-with-on-
conflict-in-postgres-9-5=E2=80=8B So it's not a bug but a limitation which =
is quite
logical but is not reflected in documentation. According to Postgres bug
reporting guideline
<https://www.postgresql.org/docs/current/static/bug-reporting.html> "If it
is not clear from the documentation whether you can do something or not,
please report that too; it is a bug in the documentation" should I create a
separate bug to improve documentation or can we use this one?

Thanks,
Grygoriy

On Mon, Oct 31, 2016 at 6:09 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

>
>
> On Wed, Oct 26, 2016 at 2:24 AM, <grygoriy.gonchar@kreditech.com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      14397
>> Logged by:          Grygoriy Gonchar
>> Email address:      grygoriy.gonchar@kreditech.com
>> PostgreSQL version: 9.5.4
>> Operating system:   x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2
>> Description:
>>
>> =E2=80=8B[...]=E2=80=8B
>>
>>
>> An exception or error caused a run to abort: ERROR: ON CONFLICT is not
>> supported on table "test" used as a catalog table
>>
>
> =E2=80=8BAny chance you cross-posted this to Stack Overflow?  A Google se=
arch on
> "used as a catalog table" turns up a post from 5 days ago that is awfully
> similar.
>
> =E2=80=8Bhttp://stackoverflow.com/questions/40260920/updatable-view-
> doesnt-work-with-on-conflict-in-postgres-9-5=E2=80=8B
>
> =E2=80=8BThe conclusion there is that we seem to have a RLS interaction f=
ailure.
> I suspect this isn't a code bug but a known, if under-documented,
> limitation though others more versed with these two features need to chim=
e
> in.
>
> David J.
>