Re: [GENERAL] table and column information from cursor? - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [GENERAL] table and column information from cursor?
Whole thread Raw
List pgsql-hackers
John DeSoi <> writes:
> I was wondering if there is some way I'm missing to get the table and 
> column information from a cursor. If I fetch from a cursor, the table 
> OID and column number values are 0 in the row description. If I execute 
> the same query directly without a cursor, the row description has the 
> correct values for table OID and column number. I'm using the v3 
> protocol via a socket with PostgreSQL 8.0.

This looks a bit messy to fix.  The information exists in the Portal
where the cursor itself is stored, but the FETCH is executed in a
different Portal that does not have it.  If you trace through the code
you find the immediate failure in printtup_startup, which only knows how
to dig the targetlist out of a plain SELECT Query:
   /*    * If this is a retrieve, and we are supposed to emit row    * descriptions, then we send back the tuple
descriptorof the tuples.    */   if (operation == CMD_SELECT && myState->sendDescrip)   {       List
       if (portal->strategy == PORTAL_ONE_SELECT)           targetlist = ((Query *)
linitial(portal->parseTrees))->targetList;      else           targetlist = NIL;
       SendRowDescriptionMessage(typeinfo, targetlist, portal->formats);   }

We could probably kluge this code to be able to recognize a FETCH as
well, and then somehow dig the targetlist out of the cursor's portal,
but it sounds pretty ugly ... and it surely wouldn't scale to other
sorts of utility statements that return tuples, although offhand I can't
think of any for which there'd be a clear connection of returned columns
to table columns.

One way to fix this would be to pass the cursor's Portal, and not the
FETCH command's Portal, to the DestReceiver --- not sure how messy that
would be.  Otherwise it seems like we need a cleaner way of decorating a
Portal with original-column info, so that a FETCH command could arrange
to copy the info into the Portal where it's executing.

More generally, it may be that attaching the original-column info to
targetlist entries was a bad idea in the first place, and we ought to
keep it someplace else ... not sure where would be better though.

Thoughts anyone?
        regards, tom lane

pgsql-hackers by date:

From: Tom Lane
Subject: Re: Optimizing maximum/minimum queries (yet again)
From: Simon Riggs
Subject: Re: prepared statements don't log arguments?