Обсуждение: Re: :PgSQL: More Queestions

On Wednesday, November 20, 2002, at 08:36  AM, Jeff Urlwin wrote:

> See the other posts.  They did a better job of describing it.

Right, thanks.

> I'm not sure what it's really trying to do, either, really...

Heh. Thank God we have Tim Bunce to explain it to use mere mortals. ;-)

>> Maybe it's just too complex, because, looking at DBD::ODBC's
>> dbd_preparse(), the handling of literals in the query seems a good 
>> deal
>> more straight-forward (though it doesn't appear to handle '\'' or "\""
>> -- am I reading that right?
>
> Nope, it handles " or '.
>
>     if (*src == '"' || *src == '\'') {
>         etc...
>     }

It doesn't appear to handle "...""...", though, right? Or am I missing 
it?

>> Ah, that makes sense. Not sure if it's an issue for PostgreSQL, but I
>> doesn't appear to be much of an overhead to set it on a per-execute
>> basis...
>
> Actually, if you can get away with doing it only once, the first 
> execute, go
> with it.  DBD::ODBC tries to do that, but rechecks under two 
> conditions:
>     1) we "know" there are multiple result sets in this query via already
> experiencing it
>     2) the user sets a DBD::ODBC private attributed to recheck the result 
> set
> types (this is to support nasty things like stored procedures 
> returning only
> one result set per call, but a different result set based upon the 
> input
> (yes, I've seen this!).

Bleh!
> My advice: if you don't have to support multiple result sets, do it 
once per
> execute.  If you setup that "flag" to avoid re-doing work and find 
> that you
> need to support multiple-result sets, you can always clear the flag...
 I'll have to check with the PostgreSQL folks on this.

PostgreSQL folks, can the same statement return a different number of 
fields on different executes? I'm guessing yes for something like this, 
though:
  CREATE TABLE foo ( bar int, bat, text);
  SELECT * FROM foo;  -- Returns two fields.
  ALTER TABLE foo ADD COLUMN fat int;
  SELECT * FROM foo;  -- Returns three fields.

> I would make the statement that DBD::Oracle may provide a better 
> reference
> on the pre-parse stuff.  DBD::ODBC's is probably a bit watered down 
> from
> DBD::Oracle -- especially because I'm avoiding comments.

Yep, thanks, I'll check it out.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e                                               Jabber:
Theory@jabber.org

On Wed, Nov 20, 2002 at 07:09:34PM -0800, David Wheeler wrote:
> 
> PostgreSQL folks, can the same statement return a different number of 
> fields on different executes? I'm guessing yes for something like this, 
> though:
> 
>   CREATE TABLE foo ( bar int, bat, text);
> 
>   SELECT * FROM foo;  -- Returns two fields.
> 
>   ALTER TABLE foo ADD COLUMN fat int;
> 
>   SELECT * FROM foo;  -- Returns three fields.

I suspect there are quite a few  drivers that wouldn't do the right
thing in that situation (schema change between two executes of a
prepared statement).

You could either arrange the code to ignore an extra column
(on the right), or make it re-describe if the column count changes.

But they'll always be ways to cause problems - such as changing the
type of a column.

Increasing paranoia yields decreasing performance. I doubt it's worth
worrying about. In general people should avoid "select *" if there's
a risk that the schema will change.

Tim.

On Wed, 20 Nov 2002, David Wheeler wrote:

> 
> >> Maybe it's just too complex, because, looking at DBD::ODBC's
> >> dbd_preparse(), the handling of literals in the query seems a good 
> >> deal
> >> more straight-forward (though it doesn't appear to handle '\'' or "\""
> >> -- am I reading that right?
> >
> > Nope, it handles " or '.
> >
> >     if (*src == '"' || *src == '\'') {
> >         etc...
> >     }
> 
> It doesn't appear to handle "...""...", though, right? Or am I missing 
> it?
> 

So you have a "". On the first " of the medial "" cluster, the code will
set in_literal to 0, but the very next character is a " so it will set
in_literal right back to '"'.  Now, if there were something between the "s 
it would not be in_literal, see? || am I just serving to confuse?

> PostgreSQL folks, can the same statement return a different number of 
> fields on different executes? I'm guessing yes for something like this, 
> though:
> 
>    CREATE TABLE foo ( bar int, bat, text);
> 
>    SELECT * FROM foo;  -- Returns two fields.
> 
>    ALTER TABLE foo ADD COLUMN fat int;
> 
>    SELECT * FROM foo;  -- Returns three fields.
> 

But using prepared statements:

test=# create table foo (foo integer); 
CREATE TABLE
test=# prepare cached (varchar)  AS select * from foo where foo= $1;
PREPARE
test=# insert into foo values (1);
INSERT 16982 1
test=# execute cached (1);foo 
-----  1
(1 row)

test=# alter table foo add column bar varchar;
ALTER TABLE
test=# execute cached (1);foo 
-----  1
(1 row)

test=# select * from bar;
ERROR:  Relation "bar" does not exist
test=# select * from foo;foo | bar 
-----+-----  1 | 
(1 row)



-r

> >> Maybe it's just too complex, because, looking at DBD::ODBC's
> >> dbd_preparse(), the handling of literals in the query seems a good
> >> deal
> >> more straight-forward (though it doesn't appear to handle '\'' or "\""
> >> -- am I reading that right?
> >
> > Nope, it handles " or '.
> >
> >     if (*src == '"' || *src == '\'') {
> >         etc...
> >     }
>
> It doesn't appear to handle "...""...", though, right? Or am I missing
> it?

Actually, it does, but completely "accidentally".  DBD::ODBC is not
concerned with the fact that there are " inside the ", so:the first quote sets in_literal=1 (true)the second quote sets
in_literal=0(false)the third puts us right back in_literal=1, so we can keep processing.
 
Since there should be an even number of " marks, I think we're OK here.

Seems too simple, but, it works.  There may be a case that gets me, but I
don't think so.

>
>  > My advice: if you don't have to support multiple result sets, do it
> once per
> > execute.  If you setup that "flag" to avoid re-doing work and find
> > that you
> > need to support multiple-result sets, you can always clear the flag...
>
>   I'll have to check with the PostgreSQL folks on this.
>
> PostgreSQL folks, can the same statement return a different number of
> fields on different executes? I'm guessing yes for something like this,
> though:
>
>    CREATE TABLE foo ( bar int, bat, text);
>
>    SELECT * FROM foo;  -- Returns two fields.
>
>    ALTER TABLE foo ADD COLUMN fat int;
>
>    SELECT * FROM foo;  -- Returns three fields.

You probably wouldn't have that prepared as one statement.
Using, say, SQL Server, you can do something like (pseudo code here with
some exaggeration, but I can actually provide test code that someone sent):
sp_pain_to_deal_with(int i) as
    if (i == 1)        select a, b, c from foo;    /* returns a, b, c as result set */    elseif (i == 2)        delete
fromfoo;            /* returns a count, not a result set */    else        select d, a, b, f from foo;    /* returns
somethingcompletely different */end;
 

$sth = $dbh->prepare({ call sp_pain_to_deal_with(?)});
$sth->execute(1);
$sth->execute(2);
$sth->execute(3);

all return different result sets.

Or, the more "normal" case:$sth = $dbh->prepare("insert a, b, c into foo values (?, ?, ?); select
@@identidy;");
which, in one "shot" insert into the table and gets back the
auto-incremented id for the table (again, there is probably a lot of syntax
issue with the above, but the concept is there).



Regards,

Jeff