BUG #14397: Updatable VIEW doesn't work with ON CONFLICT from PgPreparedStatement
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:
I have a view defined as:
CREATE OR REPLACE VIEW TEST AS SELECT * FROM TEST_1 WITH CASCADED CHECK
OPTION;
This is necessary to abstract from table name while application code is
working via view name (to implement a kind of simple partitioning)
When I run the following query on view:
insert into test (c1, c2, c3) values (?, ?, ?) on conflict (c1, c2) do
update set c3=excluded.c3
I get the following error:
An exception or error caused a run to abort: ERROR: ON CONFLICT is not
supported on table "test" used as a catalog table
Position: 98
org.postgresql.util.PSQLException: ERROR: ON CONFLICT is not supported on
table "encrypted_value" used as a catalog table
Position: 98
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:217)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:421)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
at
org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:137)
If I run the same query without prepared statement from command line all
works fine
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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 PgPreparedStatementLooks to me like a bug with PgPreparedStatement
Have you reported the bug to pgjdbc?
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:[...]
An exception or error caused a run to abort: ERROR: ON CONFLICT is not
supported on table "test" used as a catalog table
Any chance you cross-posted this to Stack Overflow? A Google search on
"used as a catalog table" turns up a post from 5 days ago that is awfully
similar.
The 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 chime in.
David J.
Hi David and Peter,
Yes, I did this cross posting to http://stackoverflow.com/
questions/40260920/updatable-view-doesnt-work-with-on-
conflict-in-postgres-9-5 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:
Show quoted text
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:[...]
An exception or error caused a run to abort: ERROR: ON CONFLICT is not
supported on table "test" used as a catalog tableAny chance you cross-posted this to Stack Overflow? A Google search on
"used as a catalog table" turns up a post from 5 days ago that is awfully
similar.http://stackoverflow.com/questions/40260920/updatable-view-
doesnt-work-with-on-conflict-in-postgres-9-5The 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 chime
in.David J.
On Mon, Oct 31, 2016 at 10:27 AM, Grygoriy Gonchar <
grygoriy.gonchar@kreditech.com> wrote:
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:[...]
An exception or error caused a run to abort: ERROR: ON CONFLICT is not
supported on table "test" used as a catalog tableAny chance you cross-posted this to Stack Overflow? A Google search on
"used as a catalog table" turns up a post from 5 days ago that is awfully
similar.http://stackoverflow.com/questions/40260920/updatable-view-
doesnt-work-with-on-conflict-in-postgres-9-5The 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 chime
in.David J.
"""
Hi David and Peter,
Yes, I did this cross posting to
http://stackoverflow.com/questions/40260920/updatable-view-doesnt-work-with-on-conflict-in-postgres-9-5
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?
"""
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.