New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add SELECT command tag on CommandComplete? #958

Closed
jorsol opened this Issue Sep 25, 2017 · 6 comments

Comments

Projects
None yet
3 participants
@jorsol
Contributor

jorsol commented Sep 25, 2017

Does it make sense to add support for SELECT command tag to get the retrieved rows?

PostgreSQL 9.0+ protocol supports CommandComplete:

For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved.

int count = Statement.executeUpdate("CREATE TEMP TABLE another_tablecount AS "
    + "select true test from generate_series(1,25);");

if (connection.haveMinimumServerVersion(ServerVersion.v9_0)) {
  assertEquals("Pg9.0+ returns count for select commands", 25, count);
} else {
  assertEquals("Pg8.2 to 8.4 return nothing", 0, count);
}
@polobo

This comment has been minimized.

polobo commented Sep 25, 2017

You've only demonstrated the "CREATE TABLE AS" command. I'd question that a plain SELECT works in this manner.

@jorsol

This comment has been minimized.

Contributor

jorsol commented Sep 25, 2017

A SELECT INTO will work in this manner.

int count = Statement.executeUpdate("SELECT true INTO into_table FROM generate_series(1,15);");
if (connection.haveMinimumServerVersion(ServerVersion.v9_0)) {
  assertEquals("Pg9.0+ returns count for select commands", 15, count);
} else {
  assertEquals("Pg8.2 to 8.4 return nothing", 0, count);
}

A plain SELECT will not work, not because the protocol don't return the count, but because a plain select in executeUpdate produces a ResultSet:

SQLException - the given SQL statement produces a ResultSet object

So the questions is the same, in the scenarios where it works CREATE TABLE AS and SELECT INTO, does it make sense to get the retrieved rows? might be useful?

@davecramer

This comment has been minimized.

Member

davecramer commented Sep 25, 2017

@jorsol

This comment has been minimized.

Contributor

jorsol commented Sep 25, 2017

@davecramer not exactly, when you run a SELECT, the PostgreSQL protocol return a command tag with the rows retrieved, a PLAIN SELECT do not return the count per JDBC specification, but for uses cases like CREATE TABLE AS and SELECT INTO where it do not generate a result set (it creates a table from a select) it can return the count of rows "inserted", in other words you are not "reading" all the rows.

@jorsol

This comment has been minimized.

Contributor

jorsol commented Sep 25, 2017

I have added the PR #960 to show this. The change is pretty trivial.

@polobo

This comment has been minimized.

polobo commented Sep 25, 2017

Unless the JDBC-spec, in the interests of cross-database compatibility, has something to say on the topic I don't see why we wouldn't report the provided information to the user.

CREATE TABLE AS and SELECT INTO are effectively synonyms; the former being generally preferred and less likely to be confused with the similarly-named pl/pgsql feature.

@vlsi vlsi closed this in 097db5e Sep 28, 2017

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

feat: parse command complete message via regex (pgjdbc#962)
Replaces command status whitelist based parsing with a regex approach to
handle generic COMMAND OID COUNT or COMMAND COUNT responses. If the
response does not match the regex then parsing is skipped. This should allow
for automatically supporting new server responses of that same form as well as
skipping any that cannot be parsed.

Fixes pgjdbc#958

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

feat: parse command complete message via regex (pgjdbc#962)
Replaces command status whitelist based parsing with a regex approach to
handle generic COMMAND OID COUNT or COMMAND COUNT responses. If the
response does not match the regex then parsing is skipped. This should allow
for automatically supporting new server responses of that same form as well as
skipping any that cannot be parsed.

Fixes pgjdbc#958
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment