Search Postgresql Archives

Re: pgbench and timestamps

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 24 Jun 2020 at 20:41, Jaime Soler <jaime.soler@xxxxxxxxx> wrote:
>
> Hi, does anybody know what is wrong with pgbench in this case ?. Here is a simple query to generate a random date in a interval time.sql:
>
>  (select timestamp '2005-09-01' + random() * ( timestamp '2006-03-01 00:00:00' -  timestamp '2005-09-01 00:00:00' ));
> pgbench -c 2 -j 2 -M prepared --file time.sql -h localhost -d picp -U postgres -p 5432
> ERROR:  la sintaxis de entrada no es válida para tipo timestamp: «2006-03-01 00$1$2»
>
> I don't know why pgbench use  timestamp: «2006-03-01 00$1$2» instead of timestamp '2006-03-01 00:00:00'

I've not debugged it, but it looks like pgbench thinks that :00 is a
pgbench variable and is replacing each instance with a query
parameter.

https://www.postgresql.org/docs/12/pgbench.html says:

"There is a simple variable-substitution facility for script files.
Variable names must consist of letters (including non-Latin letters),
digits, and underscores. Variables can be set by the command-line -D
option, explained above, or by the meta commands explained below. In
addition to any variables preset by -D command-line options, there are
a few variables that are preset automatically, listed in Table 257. A
value specified for these variables using -D takes precedence over the
automatic presets. Once set, a variable's value can be inserted into a
SQL command by writing :variablename. When running more than one
client session, each session has its own set of variables. pgbench
supports up to 255 variable uses in one statement."

I don't often do much with pgbench and variables, but there are a few
things that surprise me here.

1) That pgbench replaces variables within single quotes, and;
2) that we still think it's a variable name when it starts with a digit, and;
3) We replace variables that are undefined.

I won't pretend to be familiar enough with pgbench internals to know
if there's any reasonable reasons why we do each of the above, but...

I guess you could work around this problem by just not putting the
midnight time in your timestamp. However, that might not work so well
if you want to specify a time other than midnight.

David






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux