Syntax error at end of input postgresql

Reported by

Tomas Janco tomas.janco@myinphinity.com

I have found following problem with JDBC driver:
When an updatable result set is created for a table without primary key, any update fails with error: «syntax error at end of input»
The driver generates invalid SQL query to update the table.

JDBC driver version: 42.2.18 (jre8)
Server version: PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestPgSql {
/* test table schema:
CREATE TABLE public.sample
(
id integer,
value character varying(255) COLLATE pg_catalog.»default»
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
INSERT INTO public.sample(id, value)
VALUES (1, ‘abcd’);
*/

public static void main(String args[]) throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/...?user=....&password=....");
    String sql = "SELECT * FROM sample WHERE id = 1;";
    PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_FORWARD_ONLY);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    rs.updateString("value", "something");
    rs.updateRow();
    rs.close();
}

}

Expected behavior:
The code successfully updates the table OR throws an error explaining primary key is not present in result set and is required for updatable result set.

Actual behavior:
Incorrect SQL command is generated internally: «UPDATE sample SET «value» = $1 WHERE «
The query is missing the WHERE condition expression.
This results in following exception being thrown:

Exception in thread «main» org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
at org.postgresql.jdbc.PgResultSet.updateRow(PgResultSet.java:1445)
at TestPgSql.main(TestPgSql.java:35)

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Hello,

I am trying to build a query using StringBuilder based off a variable
size ArrayList of inputs. When testing a simplest use case of one input
ID, I get an syntax error at end of input. However, if I copy the
generated SQL and run it in pgAdmin3, it is able to execute successfully.
Below is the error with loglevel2 enabled. Any advice would be much
appreciated.

SELECT seq_chromats.species_code_4, seq_chromats.fasta_header_line,
seq_chromats.fasta_sequence, seq_chromats.flag_two_reads_exist,
seq_amplicons.amplicon_name, seq_primers.primer_name FROM seq_amplicons
INNER JOIN seq_primers ON seq_primers.amplicon_name =
seq_amplicons.amplicon_name INNER JOIN seq_chromats ON
seq_chromats.primer_id = seq_primers.primer_id WHERE
seq_amplicons.amplicon_name IN ((‘0_8156_01’))
14:40:53.272 (1) PostgreSQL 9.1 JDBC4 (build 901)
14:40:53.277 (1) Trying to establish a protocol version 3 connection to
localhost:5432
14:40:53.282 (1) FE=> StartupPacket(user=sswap_agent,
database=treegenes_development, client_encoding=UTF8, DateStyle=ISO,
extra_float_digits=2)
14:40:53.286 (1) <=BE AuthenticationReqMD5(salt=3ce4592b)
14:40:53.287 (1) FE=>
Password(md5digest=md5b4b2bdfb96e02dea70d85a4890a23c4e)
14:40:53.290 (1) <=BE AuthenticationOk
14:40:53.303 (1) <=BE ParameterStatus(application_name = )
14:40:53.303 (1) <=BE ParameterStatus(client_encoding = UTF8)
14:40:53.303 (1) <=BE ParameterStatus(DateStyle = ISO, MDY)
14:40:53.303 (1) <=BE ParameterStatus(integer_datetimes = on)
14:40:53.303 (1) <=BE ParameterStatus(IntervalStyle = postgres)
14:40:53.303 (1) <=BE ParameterStatus(is_superuser = off)
14:40:53.303 (1) <=BE ParameterStatus(server_encoding = SQL_ASCII)
14:40:53.303 (1) <=BE ParameterStatus(server_version = 9.0.7)
14:40:53.303 (1) <=BE ParameterStatus(session_authorization = sswap_agent)
14:40:53.303 (1) <=BE ParameterStatus(standard_conforming_strings = off)
14:40:53.303 (1) <=BE ParameterStatus(TimeZone = US/Pacific)
14:40:53.303 (1) <=BE BackendKeyData(pid=30534,ckey=1141196332)
14:40:53.303 (1) <=BE ReadyForQuery(I)
14:40:53.305 (1) simple execute,
handler=org(dot)postgresql(dot)core(dot)SetupQueryRunner$SimpleResultHandler(at)4b0d78ec,
maxRows=0, fetchSize=0, flags=23
14:40:53.305 (1) FE=> Parse(stmt=null,query=»SET extra_float_digits =
3″,oids={})
14:40:53.307 (1) FE=> Bind(stmt=null,portal=null)
14:40:53.307 (1) FE=> Execute(portal=null,limit=1)
14:40:53.307 (1) FE=> Sync
14:40:53.309 (1) <=BE ParseComplete [null]
14:40:53.309 (1) <=BE BindComplete [null]
14:40:53.309 (1) <=BE CommandStatus(SET)
14:40:53.309 (1) <=BE ReadyForQuery(I)
14:40:53.310 (1) compatible = 9.1
14:40:53.310 (1) loglevel = 2
14:40:53.310 (1) prepare threshold = 5
getConnection returning
driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)4669b7fe]
14:40:53.338 (1) simple execute,
handler=org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)31b27882,
maxRows=0, fetchSize=0, flags=17
14:40:53.338 (1) FE=> Parse(stmt=null,query=»SELECT
seq_chromats.species_code_4, seq_chromats.fasta_header_line,
seq_chromats.fasta_sequence, seq_chromats.flag_two_reads_exist,
seq_amplicons.amplicon_name, seq_primers.primer_name FROM seq_amplicons
INNER JOIN seq_primers ON seq_primers.amplicon_name =
seq_amplicons.amplicon_name INNER JOIN seq_chromats ON
seq_chromats.primer_id = seq_primers.primer_id WHERE
seq_amplicons.amplicon_name IN («,oids={})
14:40:53.339 (1) FE=> Bind(stmt=null,portal=null)
14:40:53.339 (1) FE=> Describe(portal=null)
14:40:53.339 (1) FE=> Execute(portal=null,limit=0)
14:40:53.339 (1) FE=> Sync
14:40:53.346 (1) <=BE ErrorMessage(ERROR: syntax error at end of input
Position: 400)
org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 400
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at
edu.dendrome.sswap.QueryWithConfig.queryChromatSeqsByAmplicon(QueryWithConfig.java:380)
at
edu.dendrome.sswap.AmpliconMultiFastaService.initializeRequest(AmpliconMultiFastaService.java:88)
at
info.sswap.api.servlet.SimpleSSWAPServlet.handleRequest(SimpleSSWAPServlet.java:125)
at
info.sswap.api.servlet.AbstractSSWAPServlet.doPost(AbstractSSWAPServlet.java:658)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:680)
SQLException: SQLState(42601)
14:40:53.350 (1) <=BE ReadyForQuery(I)
ERROR: syntax error at end of input
Position: 400

Best,
-Hans