pgbench

On 10/03/2013 06:21 AM, Simeó Reig wrote:

Hello

I was doing a performance test with pgbench with a pretty long queries
and I have the next error:

$ pgbench -n -c1 -T 3 -f veins_pgbench.sql pdn
Client 0 aborted in state 0: ERROR: syntax error at end of input
LINE 1: ...(abc.persones.provincia = abc.poblacions.cod_provinc
^
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)

I believe pgbench has a very low limit with the queries you can put
inside a file with the 't' option.
Am I right? How can avoid it ?

Well first you say 't' option but show 'T' option, they are different.

Second the error is reporting a syntax error in your script, so I would
look there first.

My best regards

Simeó Reig
Barcelona (Spain)

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A 2013-10-03 15:51, Adrian Klaver escrigué:

On 10/03/2013 06:21 AM, Simeó Reig wrote:

Hello

I was doing a performance test with pgbench with a pretty long
queries
and I have the next error:

$ pgbench -n -c1 -T 3 -f veins_pgbench.sql pdn
Client 0 aborted in state 0: ERROR: syntax error at end of input
LINE 1: ...(abc.persones.provincia = abc.poblacions.cod_provinc
^
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)

I believe pgbench has a very low limit with the queries you can put
inside a file with the 't' option.
Am I right? How can avoid it ?

Well first you say 't' option but show 'T' option, they are different.

Second the error is reporting a syntax error in your script, so I
would look there first.

Yes, I did I mistake. I would say 'f' option (file option) not 't'
option, sorry . But no, there is no mistake with the script, I can do:

# psql -d pdn < veins_pgbench.sql

and it works perfectly

thanks Adrian, I'm almost sure that the problem is the query is too
long for pgbench (1600 characters)

Best regards

Simeó Reig
Barcelona (Spain)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/03/2013 07:11 AM, Simeó Reig wrote:

A 2013-10-03 15:51, Adrian Klaver escrigué:

On 10/03/2013 06:21 AM, Simeó Reig wrote:

Hello

I was doing a performance test with pgbench with a pretty long queries
and I have the next error:

$ pgbench -n -c1 -T 3 -f veins_pgbench.sql pdn
Client 0 aborted in state 0: ERROR: syntax error at end of input
LINE 1: ...(abc.persones.provincia = abc.poblacions.cod_provinc
^
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)

I believe pgbench has a very low limit with the queries you can put
inside a file with the 't' option.
Am I right? How can avoid it ?

Well first you say 't' option but show 'T' option, they are different.

Second the error is reporting a syntax error in your script, so I
would look there first.

Yes, I did I mistake. I would say 'f' option (file option) not 't'
option, sorry . But no, there is no mistake with the script, I can do:

# psql -d pdn < veins_pgbench.sql

and it works perfectly

thanks Adrian, I'm almost sure that the problem is the query is too long
for pgbench (1600 characters)

You have not shown the query, but could you be running into the belwo:

http://www.postgresql.org/docs/9.3/interactive/pgbench.html

"The format of a script file is one SQL command per line; multiline SQL
commands are not supported. Empty lines and lines beginning with -- are
ignored. Script file lines can also be "meta commands", which are
interpreted by pgbench itself, as described below."

Best regards

Simeó Reig
Barcelona (Spain)

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A 2013-10-03 16:16, Adrian Klaver escrigué:

On 10/03/2013 07:11 AM, Simeó Reig wrote:

A 2013-10-03 15:51, Adrian Klaver escrigué:

On 10/03/2013 06:21 AM, Simeó Reig wrote:

Hello

I was doing a performance test with pgbench with a pretty long
queries
and I have the next error:

$ pgbench -n -c1 -T 3 -f veins_pgbench.sql pdn
Client 0 aborted in state 0: ERROR: syntax error at end of input
LINE 1: ...(abc.persones.provincia = abc.poblacions.cod_provinc
^
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)

I believe pgbench has a very low limit with the queries you can
put
inside a file with the 't' option.
Am I right? How can avoid it ?

Well first you say 't' option but show 'T' option, they are
different.

Second the error is reporting a syntax error in your script, so I
would look there first.

Yes, I did I mistake. I would say 'f' option (file option) not 't'
option, sorry . But no, there is no mistake with the script, I can
do:

# psql -d pdn < veins_pgbench.sql

and it works perfectly

thanks Adrian, I'm almost sure that the problem is the query is too
long
for pgbench (1600 characters)

You have not shown the query, but could you be running into the belwo:

http://www.postgresql.org/docs/9.3/interactive/pgbench.html

"The format of a script file is one SQL command per line; multiline
SQL commands are not supported. Empty lines and lines beginning with
-- are ignored. Script file lines can also be "meta commands", which
are interpreted by pgbench itself, as described below."

I did it, and I read it all ... I'm use to work with postgresql and
freeBSD world since too years ago ;-)

Thanks again Adrian

Simeó Reig
Barcelona (Spain)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/03/2013 07:23 AM, Simeó Reig wrote:

You have not shown the query, but could you be running into the belwo:

http://www.postgresql.org/docs/9.3/interactive/pgbench.html

"The format of a script file is one SQL command per line; multiline
SQL commands are not supported. Empty lines and lines beginning with
-- are ignored. Script file lines can also be "meta commands", which
are interpreted by pgbench itself, as described below."

I did it, and I read it all ... I'm use to work with postgresql and
freeBSD world since too years ago ;-)

Obviously pgbench has issues with the syntax in the file. Without the
contents of the file I am just doing some educated guessing. To get to a
solution you will need to show your custom file.

Thanks again Adrian

Simeó Reig
Barcelona (Spain)

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A 2013-10-03 16:40, Adrian Klaver escrigué:

On 10/03/2013 07:23 AM, Simeó Reig wrote:

You have not shown the query, but could you be running into the
belwo:

http://www.postgresql.org/docs/9.3/interactive/pgbench.html

"The format of a script file is one SQL command per line; multiline
SQL commands are not supported. Empty lines and lines beginning with
-- are ignored. Script file lines can also be "meta commands", which
are interpreted by pgbench itself, as described below."

I did it, and I read it all ... I'm use to work with postgresql and
freeBSD world since too years ago ;-)

Obviously pgbench has issues with the syntax in the file. Without the
contents of the file I am just doing some educated guessing. To get to
a solution you will need to show your custom file.

Yes you are right, It has an issue with the syntax, because it only
reads a portion of the query, and obviously it's not correct if you do
it.

Unfortunately I can't put the sql here, but I repeat: I could do:

psql -d databasename > scrip_file.sql

and it works perfectly, and It is a one line query.

I did it with many queries since today, but I think this is too long

Many many thanks for your interest Adrian

Simeó Reig
Barcelona (Spain)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Il 03/10/2013 16:11, Simeó Reig ha scritto:

A 2013-10-03 15:51, Adrian Klaver escrigué:

On 10/03/2013 06:21 AM, Simeó Reig wrote:

Hello

I was doing a performance test with pgbench with a pretty long
queries
and I have the next error:

$ pgbench -n -c1 -T 3 -f veins_pgbench.sql pdn
Client 0 aborted in state 0: ERROR: syntax error at end of input
LINE 1: ...(abc.persones.provincia = abc.poblacions.cod_provinc
^
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 0
tps = 0.000000 (including connections establishing)
tps = 0.000000 (excluding connections establishing)

I believe pgbench has a very low limit with the queries you can put
inside a file with the 't' option.
Am I right? How can avoid it ?

Well first you say 't' option but show 'T' option, they are different.

Second the error is reporting a syntax error in your script, so I
would look there first.

Yes, I did I mistake. I would say 'f' option (file option) not 't'
option, sorry . But no, there is no mistake with the script, I can do:

# psql -d pdn < veins_pgbench.sql

and it works perfectly

thanks Adrian, I'm almost sure that the problem is the query is too
long for pgbench (1600 characters)

The format of the script file has to be one SQL command per line;
multiline SQL commands are not supported, and empty lines are ignored.
This could bring to errors. Could this be your case?

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/03/2013 07:48 AM, Simeó Reig wrote:

A 2013-10-03 16:40, Adrian Klaver escrigué:

On 10/03/2013 07:23 AM, Simeó Reig wrote:

Unfortunately I can't put the sql here, but I repeat: I could do:

psql -d databasename > scrip_file.sql

and it works perfectly, and It is a one line query.

I would hope not, the arrow is going the wrong way:) At any rate psql !=
pgbench.

I did it with many queries since today, but I think this is too long

Looks like this is one of these things to walk away from for a time and
then come back too. Good luck.

Many many thanks for your interest Adrian

Simeó Reig
Barcelona (Spain)

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Giuseppe Broccolo wrote:

The format of the script file has to be one SQL command per line;
multiline SQL commands are not supported, and empty lines are
ignored. This could bring to errors. Could this be your case?

Multiline SQL commands are not supported? Well that sucks, because only
BUFSIZ chars are read from each line. In my platform that's 8192, but
maybe in Sime�'s case it's shorter .. or maybe his query really is
longer than 8192 bytes.

This smells like a pgbench bug to me.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Multiline SQL commands are not supported? Well that sucks, because only
BUFSIZ chars are read from each line. In my platform that's 8192, but
maybe in Simeó's case it's shorter .. or maybe his query really is
longer than 8192 bytes.

This smells like a pgbench bug to me.

Yeah, I ran into that line-length limit in pgbench a couple months ago.
We really oughta remove the restriction; seems like that shouldn't be
hard.

I'm less enthused about introducing a multiline-command feature; that'd
require inventing some escape syntax that's not there now, and making it
100% backwards compatible might be hard.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

A 2013-10-03 17:50, Alvaro Herrera escrigué:

Giuseppe Broccolo wrote:

The format of the script file has to be one SQL command per line;
multiline SQL commands are not supported, and empty lines are
ignored. This could bring to errors. Could this be your case?

Multiline SQL commands are not supported? Well that sucks, because
only
BUFSIZ chars are read from each line. In my platform that's 8192, but
maybe in Simeó's case it's shorter .. or maybe his query really is
longer than 8192 bytes.

This smells like a pgbench bug to me.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Álvaro, you hit the nail on the head!

I have seen that in stdio.h BUFSIZ is defined like 1024 in freeBSD 9.0,
probably for this reason I can't test this query (1657 characters), but
I'm able to test shorter queries.

Finally I have done a plsql procedure to call the query from pgbench.
Meanwhile I will try to figure out if I can increase this variable
without affect the system.

Thanks to all

Simeó Reig
Barcelona (Spain)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
can you share the plsql procedure to call the query from pgbench
i'm running in the same issue cause i have a long query that i want to
submit to pgbench

There are any news for fix this pgbench issue ?

--
View this message in context: http://postgresql.nabble.com/pgbench-tp5773225p5830455.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general