psql tab completion bug and possible fix

From: Ian Barwick <barwick(at)gmx(dot)net>
To: pgsql-patches(at)postgresql(dot)org
Subject: psql tab completion bug and possible fix
Date: 2003-10-14 21:14:48
Message-ID: 200310142314.48692.barwick@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches


Recently I've been seeing regular but very occasional errors like the
following while using psql:

test=> BEGIN ;
BEGIN
test=> UPDATE language SET name_native = 'Français' WHERE lang_id='fr';
ERROR: current transaction is aborted, commands ignored until end of
transaction block

where the UPDATE statement itself is entirely correct and is executed
correctly when a new transaction is started. Unfortunately I was never able
to reproduce the error and thought it might be some kind of beta flakiness,
until it turned up in a 7.3 installation too.

The culprit is the following section of psql's tab-complete.c , around line
1248:

/* WHERE */
/* Simple case of the word before the where being the table name */
else if (strcasecmp(prev_wd, "WHERE") == 0)
COMPLETE_WITH_ATTR(prev2_wd);

which is correct for SELECT statements. Where the line contains an UPDATE
statement however, and tab is pressed after WHERE, the word before WHERE is
passed to the backend via a sprintf-generated query with the %s between single
quotes, i.e. in the above case
AND c.relname='%s'
is translated to
AND c.relname=''Français''

which is causing a silent error and the transaction failure.

I don't see a simple solution to cater for UPDATE syntax in this context
(you'd need to keep track of whether the statement begins with SELECT
or UPDATE), though it might be a good todo item.

A quick (but not dirty) fix for this and other current or future potential
corner cases would be to ensure any statements executed by the tab completion
functions are quoted correctly, so even if the statement does not produce any
results for tab completion, at least it cannot cause mysterious transaction
errors (and associated doubts about PostgreSQL's stability ;-).

A patch for this using PQescapeString (is there another preferred method?) is
attached as a possible solution.

Ian Barwick
barwick(at)gmx(dot)net

Attachment Content-Type Size
tab-complete.c.diff text/x-diff 7.8 KB

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message Andrew Dunstan 2003-10-14 21:18:23 Re: fix for strict-alias warnings
Previous Message Manfred Spraul 2003-10-14 21:01:14 Re: fix for strict-alias warnings