pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedecpg 'set' failure using host vars
9+ messages / 3 participants
[nested] [flat]
* ecpg 'set' failure using host vars
@ 2008-08-16 03:47 Bosco Rama <postgres@boscorama.com>
0 siblings, 2 replies; 9+ messages in thread
From: Bosco Rama @ 2008-08-16 03:47 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
I've been using the following set statement just fine under versions
prior to 8.3.3:
exec sql char *sm;
...
sm = "myschema"; // This may not always be constant
...
exec sql set search_path to :sm;
However, a recent upgrade from 8.2.7 to 8.3.3 (on FC9) caused the
'set' statement to suddenly start throwing the dreaded error:
ERROR: syntax error at or near "$1" in line 13256
Running in debug output mode we are seeing the following:
[22245]: ecpg_execute line 13256: QUERY: set search_path to $1 with 1 parameter on connection ecpgconn
[22245]: ecpg_execute line 13256: using PQexecParams
[22245]: free_params line 13256: parameter 1 = myschema
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1"
LINE 1: set search_path to $1
^
When the schema is replaced by a literal it works just fine, thusly:
exec sql set search_path to myschema;
Did we miss something in the porting between versions?
Any help would be greatly appreciated.
Bosco.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-16 08:31 Michael Meskes <meskes@postgresql.org>
parent: Bosco Rama <postgres@boscorama.com>
1 sibling, 1 reply; 9+ messages in thread
From: Michael Meskes @ 2008-08-16 08:31 UTC (permalink / raw)
To: Bosco Rama <postgres@boscorama.com>; +Cc: pgsql-interfaces@postgresql.org, PostgreSQL Hacker <pgsql-hackers@postgresql.org>
On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
> [22245]: ecpg_execute line 13256: QUERY: set search_path to $1 with 1 parameter on connection ecpgconn
> [22245]: ecpg_execute line 13256: using PQexecParams
> [22245]: free_params line 13256: parameter 1 = myschema
> [22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1"
> LINE 1: set search_path to $1
Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-16 16:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Meskes <meskes@postgresql.org>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2008-08-16 16:01 UTC (permalink / raw)
To: Michael Meskes <meskes@postgresql.org>; +Cc: Bosco Rama <postgres@boscorama.com>; pgsql-interfaces@postgresql.org, PostgreSQL Hacker <pgsql-hackers@postgresql.org>
Michael Meskes <meskes@postgresql.org> writes:
> Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
The backend only supports parameters in plannable statements, ie
SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
lazy to check.)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-16 17:16 Bosco Rama <postgres@boscorama.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Bosco Rama @ 2008-08-16 17:16 UTC (permalink / raw)
To: ; +Cc: pgsql-interfaces
Tom Lane wrote:
> Michael Meskes <meskes@postgresql.org> writes:
>> Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
>
> The backend only supports parameters in plannable statements, ie
> SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
> lazy to check.)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-16 17:20 Bosco Rama <postgres@boscorama.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Bosco Rama @ 2008-08-16 17:20 UTC (permalink / raw)
To: pgsql-interfaces
Sorry about the 'false start' before. :-(
Tom Lane wrote:
> Michael Meskes <meskes@postgresql.org> writes:
>> Without checking the sources it seems as if PQexecParams is not able
> to handle a parameter in a set command. Can anyone confirm this?
>
> The backend only supports parameters in plannable statements, ie
> SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
> lazy to check.)
Thanks for the hint guys. I used a prepare/execute pair instead and
it worked a treat. More cumbersome but it gets the job done.
Thanks again.
Bosco.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-18 14:58 Michael Meskes <meskes@postgresql.org>
parent: Bosco Rama <postgres@boscorama.com>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Meskes @ 2008-08-18 14:58 UTC (permalink / raw)
To: Bosco Rama <postgres@boscorama.com>; +Cc: pgsql-interfaces
>>> Without checking the sources it seems as if PQexecParams is not able
> > to handle a parameter in a set command. Can anyone confirm this?
>>
>> The backend only supports parameters in plannable statements, ie
>> SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
>> lazy to check.)
>
> Thanks for the hint guys. I used a prepare/execute pair instead and
> it worked a treat. More cumbersome but it gets the job done.
I will fix this as soon as my time permits. There is logic there in ecpg to
handle parameters on the client side.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-19 10:31 Michael Meskes <meskes@postgresql.org>
parent: Bosco Rama <postgres@boscorama.com>
1 sibling, 1 reply; 9+ messages in thread
From: Michael Meskes @ 2008-08-19 10:31 UTC (permalink / raw)
To: Bosco Rama <postgres@boscorama.com>; +Cc: pgsql-interfaces
On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
> exec sql char *sm;
> ...
> sm = "myschema"; // This may not always be constant
> ...
> exec sql set search_path to :sm;
Could you please try the attached patch and tell me whether it works for you? I will then check this in into 8.3 so that the next stable release works again as it should.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
Attachments:
[text/x-diff] ecpg_set.diff (557B, ../../20080819103118.GA23760@feivel.credativ.de/2-ecpg_set.diff)
download | inline diff:
--- pgsql-ecpg/preproc/preproc.y 2008-08-18 16:56:11.000000000 +0200
+++ pgsql/src/interfaces/ecpg/preproc/preproc.y 2008-08-19 12:29:28.000000000 +0200
@@ -1247,7 +1247,16 @@
;
var_value: opt_boolean { $$ = $1; }
- | AllConst { $$ = $1; }
+ | AllConst { /* we have to check for a variable here because it has to be
+ replaced with its value on the client side */
+ if ($1[1] == '$')
+ {
+ $$ = make_str("$0");
+ free($1);
+ }
+ else
+ $$ = $1;
+ }
| ColId { $$ = $1; }
;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-20 16:45 Bosco Rama <postgres@boscorama.com>
parent: Michael Meskes <meskes@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: Bosco Rama @ 2008-08-20 16:45 UTC (permalink / raw)
To: pgsql-interfaces
Hi Michael,
Michael Meskes wrote:
> On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
>> exec sql char *sm;
>> ...
>> sm = "myschema"; // This may not always be constant
>> ...
>> exec sql set search_path to :sm;
>
> Could you please try the attached patch and tell me whether it works
> for you? I will then check this in into 8.3 so that the next stable
> release works again as it should.
Thanks for working on this.
Unfortunately I'm just an applications person and our systems use the
FC9 distribution's pre-compiled packages only (it's a sysadmin thing).
I asked them about it and they said (and I quote): "When it becomes
part of the FC9 distribution they will 'consider' (my emphasis)
upgrading the packages." *sigh*
I really appreciate the effort and the fix but for now I have to be
satisfied with the work-around you guys suggested previously. :-(
Bosco.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ecpg 'set' failure using host vars
@ 2008-08-20 16:51 Michael Meskes <meskes@postgresql.org>
parent: Bosco Rama <postgres@boscorama.com>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Meskes @ 2008-08-20 16:51 UTC (permalink / raw)
To: Bosco Rama <postgres@boscorama.com>; +Cc: pgsql-interfaces
> I really appreciate the effort and the fix but for now I have to be
> satisfied with the work-around you guys suggested previously. :-(
I see. 8.3.4 will have the fix anyway. :-)
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-08-20 16:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-16 03:47 ecpg 'set' failure using host vars Bosco Rama <postgres@boscorama.com>
2008-08-16 08:31 ` Michael Meskes <meskes@postgresql.org>
2008-08-16 16:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-16 17:16 ` Bosco Rama <postgres@boscorama.com>
2008-08-16 17:20 ` Bosco Rama <postgres@boscorama.com>
2008-08-18 14:58 ` Michael Meskes <meskes@postgresql.org>
2008-08-19 10:31 ` Michael Meskes <meskes@postgresql.org>
2008-08-20 16:45 ` Bosco Rama <postgres@boscorama.com>
2008-08-20 16:51 ` Michael Meskes <meskes@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox