public inbox for [email protected]  

help / color / mirror / Atom feed

Object IDs in Parse message
6+ messages / 3 participants
[nested] [flat]

* Object IDs in Parse message
@ 2019-09-03 20:01 Malcolm Matalka <[email protected]>
  2019-09-03 20:02 ` Re: Object IDs in Parse message Dave Cramer <[email protected]>
  2019-09-03 20:24 ` Re: Object IDs in Parse message Tom Lane <[email protected]>
  0 siblings, 2 replies; 6+ messages in thread

From: Malcolm Matalka @ 2019-09-03 20:01 UTC (permalink / raw)
  To: [email protected]

Hello, I'm implementing my own pgsql client for fun and I'm trying to
understand how to send a Parse message.  The final parameter to Parse is
a series of Int32s with the description:

Specifies the object ID of the parameter data type. Placing a zero here
is equivalent to leaving the type unspecified.

But where do I find the list of object IDs?  Doing some internet
searches didn't bring up much.  Poking around the code I came across the
file:

./src/backend/catalog/pg_type_d.h

And that has a series of OID's with integer values.  Is this the mapping
I'm looking for?

If so, It's not clear how to express some things.  For example there is
a MONEYARRAYOID, but no MONEYOID.  Would I use, for example, NUMERICOID
for money?  If so, why does MONEYARRAYOID exist rather than using
NUMERICOID?

Thanks,
/Malcolm


Attachments:

  [application/pgp-signature] signature.asc (487B, ../../[email protected]/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Object IDs in Parse message
  2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
@ 2019-09-03 20:02 ` Dave Cramer <[email protected]>
  1 sibling, 0 replies; 6+ messages in thread

From: Dave Cramer @ 2019-09-03 20:02 UTC (permalink / raw)
  To: Malcolm Matalka <[email protected]>; +Cc: [email protected]

select oid, * from pg_type

Dave Cramer

[email protected]
www.postgresintl.com


On Tue, 3 Sep 2019 at 16:01, Malcolm Matalka <[email protected]> wrote:

> Hello, I'm implementing my own pgsql client for fun and I'm trying to
> understand how to send a Parse message.  The final parameter to Parse is
> a series of Int32s with the description:
>
> Specifies the object ID of the parameter data type. Placing a zero here
> is equivalent to leaving the type unspecified.
>
> But where do I find the list of object IDs?  Doing some internet
> searches didn't bring up much.  Poking around the code I came across the
> file:
>
> ./src/backend/catalog/pg_type_d.h
>
> And that has a series of OID's with integer values.  Is this the mapping
> I'm looking for?
>
> If so, It's not clear how to express some things.  For example there is
> a MONEYARRAYOID, but no MONEYOID.  Would I use, for example, NUMERICOID
> for money?  If so, why does MONEYARRAYOID exist rather than using
> NUMERICOID?
>
> Thanks,
> /Malcolm
>


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Object IDs in Parse message
  2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
@ 2019-09-03 20:24 ` Tom Lane <[email protected]>
  2019-09-04 07:39   ` Re: Object IDs in Parse message Malcolm Matalka <[email protected]>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2019-09-03 20:24 UTC (permalink / raw)
  To: Malcolm Matalka <[email protected]>; +Cc: [email protected]

Malcolm Matalka <[email protected]> writes:
> Hello, I'm implementing my own pgsql client for fun and I'm trying to
> understand how to send a Parse message.  The final parameter to Parse is
> a series of Int32s with the description:
> Specifies the object ID of the parameter data type. Placing a zero here
> is equivalent to leaving the type unspecified.

> But where do I find the list of object IDs?

SELECT oid, typname FROM pg_type;

> If so, It's not clear how to express some things.  For example there is
> a MONEYARRAYOID, but no MONEYOID.

For historical reasons, the macro for money's OID is CASHOID.
There's no grandfathered symbol for money[], though, so that
gets a name constructed per standard rules (cf form_pg_type_symbol
in genbki.pl).

However, I fail to see why a generic client would need to know that.
If you're hard-wiring OIDs into your code for anything beyond very
basic types like int4, you're probably doing it wrong.  Remember
that PG is an extensible system and you may be called on to handle
queries that deal with non-built-in types, so even if you had
code for everything appearing in pg_type_d.h, it wouldn't be
exhaustive.  Better to look up type OIDs at runtime.  In the case
of Parse messages, you likely want to let the backend resolve
the parameter types anyway, ie just send zeroes.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Object IDs in Parse message
  2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
  2019-09-03 20:24 ` Re: Object IDs in Parse message Tom Lane <[email protected]>
@ 2019-09-04 07:39   ` Malcolm Matalka <[email protected]>
  2019-09-04 09:49     ` Re: Object IDs in Parse message Dave Cramer <[email protected]>
  2019-09-04 14:05     ` Re: Object IDs in Parse message Tom Lane <[email protected]>
  0 siblings, 2 replies; 6+ messages in thread

From: Malcolm Matalka @ 2019-09-04 07:39 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: [email protected]


Tom Lane <[email protected]> writes:

> Malcolm Matalka <[email protected]> writes:
>> Hello, I'm implementing my own pgsql client for fun and I'm trying to
>> understand how to send a Parse message.  The final parameter to Parse is
>> a series of Int32s with the description:
>> Specifies the object ID of the parameter data type. Placing a zero here
>> is equivalent to leaving the type unspecified.
>
>> But where do I find the list of object IDs?
>
> SELECT oid, typname FROM pg_type;
>
>> If so, It's not clear how to express some things.  For example there is
>> a MONEYARRAYOID, but no MONEYOID.
>
> For historical reasons, the macro for money's OID is CASHOID.
> There's no grandfathered symbol for money[], though, so that
> gets a name constructed per standard rules (cf form_pg_type_symbol
> in genbki.pl).
>
> However, I fail to see why a generic client would need to know that.
> If you're hard-wiring OIDs into your code for anything beyond very
> basic types like int4, you're probably doing it wrong.  Remember

Ok, it wasn't clear to me if and when I should pass this data in.  I
couldn't find any documentation for this translating to performance
improvement, or addressing any possible errors due to ambiguity in
types.  In general, should an interface no pass that information in on a
Parse?  Is there a reason to do it?

> that PG is an extensible system and you may be called on to handle
> queries that deal with non-built-in types, so even if you had
> code for everything appearing in pg_type_d.h, it wouldn't be
> exhaustive.  Better to look up type OIDs at runtime.  In the case
> of Parse messages, you likely want to let the backend resolve
> the parameter types anyway, ie just send zeroes.
>
> 			regards, tom lane

Thank you for the detailed response


Attachments:

  [application/pgp-signature] signature.asc (487B, ../../[email protected]/2-signature.asc)
  download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Object IDs in Parse message
  2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
  2019-09-03 20:24 ` Re: Object IDs in Parse message Tom Lane <[email protected]>
  2019-09-04 07:39   ` Re: Object IDs in Parse message Malcolm Matalka <[email protected]>
@ 2019-09-04 09:49     ` Dave Cramer <[email protected]>
  1 sibling, 0 replies; 6+ messages in thread

From: Dave Cramer @ 2019-09-04 09:49 UTC (permalink / raw)
  To: Malcolm Matalka <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected]

On Wed, 4 Sep 2019 at 03:45, Malcolm Matalka <[email protected]> wrote:

>
> Tom Lane <[email protected]> writes:
>
> > Malcolm Matalka <[email protected]> writes:
> >> Hello, I'm implementing my own pgsql client for fun and I'm trying to
> >> understand how to send a Parse message.  The final parameter to Parse is
> >> a series of Int32s with the description:
> >> Specifies the object ID of the parameter data type. Placing a zero here
> >> is equivalent to leaving the type unspecified.
> >
> >> But where do I find the list of object IDs?
> >
> > SELECT oid, typname FROM pg_type;
> >
> >> If so, It's not clear how to express some things.  For example there is
> >> a MONEYARRAYOID, but no MONEYOID.
> >
> > For historical reasons, the macro for money's OID is CASHOID.
> > There's no grandfathered symbol for money[], though, so that
> > gets a name constructed per standard rules (cf form_pg_type_symbol
> > in genbki.pl).
> >
> > However, I fail to see why a generic client would need to know that.
> > If you're hard-wiring OIDs into your code for anything beyond very
> > basic types like int4, you're probably doing it wrong.  Remember
>
> Ok, it wasn't clear to me if and when I should pass this data in.  I
> couldn't find any documentation for this translating to performance
> improvement, or addressing any possible errors due to ambiguity in
> types.  In general, should an interface no pass that information in on a
> Parse?  Is there a reason to do it?
>

Yes, if you ever decide to use binary mode for the parameters and the
results then you need to know the type
The jdbc driver and the .net driver both do this.

Dave


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Object IDs in Parse message
  2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
  2019-09-03 20:24 ` Re: Object IDs in Parse message Tom Lane <[email protected]>
  2019-09-04 07:39   ` Re: Object IDs in Parse message Malcolm Matalka <[email protected]>
@ 2019-09-04 14:05     ` Tom Lane <[email protected]>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2019-09-04 14:05 UTC (permalink / raw)
  To: Malcolm Matalka <[email protected]>; +Cc: [email protected]

Malcolm Matalka <[email protected]> writes:
> Tom Lane <[email protected]> writes:
>> However, I fail to see why a generic client would need to know that.
>> If you're hard-wiring OIDs into your code for anything beyond very
>> basic types like int4, you're probably doing it wrong.  Remember

> Ok, it wasn't clear to me if and when I should pass this data in.  I
> couldn't find any documentation for this translating to performance
> improvement, or addressing any possible errors due to ambiguity in
> types.  In general, should an interface no pass that information in on a
> Parse?  Is there a reason to do it?

You should only pass type OIDs if you know for sure what they should be,
which an interface library passing on a query generated elsewhere will
not know --- unless it provides an API for the caller to tell it.

The convention that seems to have evolved in places that lack such APIs
is to pass zeroes, and instruct human authors of queries that if the
server doesn't resolve the types of parameter placeholders the way they
want, force it with a cast in the text of the query, eg "$1::int8".
Otherwise you need to add some out-of-band notation for specifying the
types, plus a way to resolve that notation into numeric OIDs, and it's
all a giant pain in the rear for all concerned.

Passing zeroes is more workable than you might think, since the
server can correctly guess the type of a placeholder in a lot
of cases.  However, it might be best to avoid it if you intend
to pass parameter values in binary, since that will lose a lot
of the safety margin for detecting wrong guesses.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

end of thread, other threads:[~2019-09-04 14:05 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-03 20:01 Object IDs in Parse message Malcolm Matalka <[email protected]>
2019-09-03 20:02 ` Dave Cramer <[email protected]>
2019-09-03 20:24 ` Tom Lane <[email protected]>
2019-09-04 07:39   ` Malcolm Matalka <[email protected]>
2019-09-04 09:49     ` Dave Cramer <[email protected]>
2019-09-04 14:05     ` Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox