Обсуждение: pgbench
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 ? My best regards Simeó Reig Barcelona (Spain)
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
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)
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
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)
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
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)
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
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
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
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
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)
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.