On Mon, 9 Jul 2007, Altaf Malik wrote:
> I have a table with two columns of type varchar and the table name
> contains two dollars. I am unable to prepare a statement for this table.
> Here is the code:
>
> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
> values( ? , ?)");
> Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
This is a bug in dollar quote parsing. It assumes that any potential
dollar quote start will have an end tag. When it doesn't find the end it
reports the end of the query, so the driver ends up skipping over the "?"
parameters.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
> This is a bug in dollar quote parsing. It assumes that any potential
> dollar quote start will have an end tag.
If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name. (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)
regards, tom lane
Kris Jurka writes:
> On Mon, 9 Jul 2007, Altaf Malik wrote:
>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>> values( ? , ?)");
> This is a bug in dollar quote parsing. It assumes that any potential
> dollar quote start will have an end tag.
If it thinks that's a dollar quote start, it's wrong already... that's
a perfectly valid table name. (Although personally I'd suggest that the
OP avoid dollar signs in identifiers, since they're none too portable.)
regards, tom lane
> I think $$ should start a quote instead of $x$. Or $ character have special
> meaning but if there is one dollar does not happen anything wrong. Are $$ and
> $x$ equal? If yes, what happens with the character(s) between the two dollar
> signs?
$anything$ starts a quote and ends with $anything$ as in:
postgres=# select $foo$bar$foo$;
?column?
----------
bar
(1 row)
However the $ must start a new token:
postgres=# select foo$foo$bar$foo$;
ERROR: column "foo$foo$bar$foo$" does not exist
LINE 1: select foo$foo$bar$foo$;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
I guess I missed that case when implementing dollar quoting in the
driver. I will have a look at it shortly.
Best Regards
Michael Paesold
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
sorry for not coming back to this earlier. Yeah, this is no real solution.
I thought about checking the character directly before the dollar quote.
But I have not had the time to look at the grammer if that is enough to fix
all error cases... I will do so tomorrow.
Best Regards
Michael Paesold
Kris Jurka schrieb:
>
>
> On Tue, 10 Jul 2007, Michael Paesold wrote:
>
>> Tom Lane schrieb:
>>> Kris Jurka <bo...@ejurka.com> writes:
>>>> On Mon, 9 Jul 2007, Altaf Malik wrote:
>>>>> PreparedStatement pstmt = con.prepareStatement("insert into a$b$c
>>>>> values( ? , ?)");
>>>
>>>> This is a bug in dollar quote parsing. It assumes that any
>>>> potential dollar quote start will have an end tag.
>>>
>>> If it thinks that's a dollar quote start, it's wrong already... that's
>>> a perfectly valid table name. (Although personally I'd suggest that the
>>> OP avoid dollar signs in identifiers, since they're none too portable.)
>>
>> I guess I missed that case when implementing dollar quoting in the
>> driver. I will have a look at it shortly.
>>
>
> Did you ever get a chance to look at this? The attached patch fixes the
> original complaint by ensuring that the ending tag exists, but as Tom
> pointed out that's still not good enough as the second attached test
> case demonstrates.
>
> Kris Jurka
>
---------------------------(end of broadcast)---------------------------
Well, I think we have several simple options (apart from implementing a
real parser in the driver ;-)...
1) In parseDollarQuotes(), we look at the character directly before the
$-char and compare it against all valid identifier characters. If it is,
we don't have a possible dollar-quote start:
From scan.l:
ident_start [A-Za-z\200-\377_]
ident_cont [A-Za-z\200-\377_0-9\$]
2) Same concept as 1), but instead create a new method parseIdentifier()
in the Parser class that would be used to detect and "skip" all regular
keywords and identifiers in the query string, and therefore avoid the
current problem. Seems like a performance loss though, because we would
do more checks on each character.
3) Again in parseDollarQuotes(), we look at the character directly
before the $-char and see if it is an *allowed* character before a
dollar-quote. But what is allowed? Operators, a comma, parenthesis, etc.
... this is much harder to get right.
Looking at gram.y and scan.l (...but I'm no expert) I can't see another
case of a dollar-character that I missed, except the one allowed in
ident_cont... so option 1) seems like the easiest solution. I will send
a patch implementing option 1 shortly.
Regarding your patch: I'm not sure if we should apply it, too. You will
probably get a different error messages in case of an unterminated
dollar quote. I haven't looked at the possible results.
My original reasoning was that if there is an unterminated quote, this
should not cause us parsing what is inside the quotes. So I would leave
it as-is. Another option would be to throw an "unterminated
dollar-quote" exception directly in the driver, which would be more
helpful in some cases.
Best Regards
Michael Paesold
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Ok, here is the patch. It also adds your tests exposing the bug to the
test suite. The patch fixes both test cases and passes all other
regression tests (at least with JDK 1.5....). Does it look OK to you?
Should we do anything additional about error-ing out when we detect an
unterminated dollar-quote (see previous mail)?
On Thu, 18 Oct 2007, Michael Paesold wrote:
> Ok, here is the patch. It also adds your tests exposing the bug to the test
> suite. The patch fixes both test cases and passes all other regression tests
> (at least with JDK 1.5....). Does it look OK to you?
Looks good. Applied.
> Should we do anything additional about error-ing out when we detect an
> unterminated dollar-quote (see previous mail)?
>
I don't think that's necessary. We don't do so for string literals,
quoted identifiers, or block comments.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster