Re: enum types and binary queries

I noticed that enums are not available to be queried as binary through
the protocol. Is this a known issue? Too late to fix for 8.3? This
is kind of a pain, because it forces any query that returns an enum to
return the entire result as text. afaik, enums are the only POD type
to behave this way.

postgres=# create type foo as enum('foo');
CREATE TYPE

postgres=# copy (select 'foo'::foo) to '/home/postgres/foo.txt' binary;
ERROR: no binary output function available for type foo

merlin

Merlin Moncure wrote:
> I noticed that enums are not available to be queried as binary through
> the protocol. Is this a known issue? Too late to fix for 8.3? This
> is kind of a pain, because it forces any query that returns an enum to
> return the entire result as text. afaik, enums are the only POD type
> to behave this way.
>
> postgres=# create type foo as enum('foo');
> CREATE TYPE
>
> postgres=# copy (select 'foo'::foo) to '/home/postgres/foo.txt' binary;
> ERROR: no binary output function available for type foo
>

The trouble is that an enum doesn't have an immutable internal binary value.

I guess we could say that the binary value is the integer offset of the
value in the enum ordering, and translate it back on input. Providing
the binary IO funcs shouldn't be too hard if we do that, unless I'm
missing something.

cheers

andrew

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> I noticed that enums are not available to be queried as binary through
> the protocol.

What do you think the binary representation ought to be? Copying OIDs
seems pretty useless.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Merlin Moncure wrote:
>> I noticed that enums are not available to be queried as binary through
>> the protocol.

> The trouble is that an enum doesn't have an immutable internal binary value.

> I guess we could say that the binary value is the integer offset of the
> value in the enum ordering, and translate it back on input. Providing
> the binary IO funcs shouldn't be too hard if we do that, unless I'm
> missing something.

That would be expensive to get, wouldn't it? The main point of binary
I/O is to be fast, so I'm not excited about an expensive conversion.

What if we just defined the binary format as being identical to the text
format, ie, the enum label? A bit silly but at least it would eliminate
the gotcha that binary I/O fails.

regards, tom lane

> What if we just defined the binary format as being identical to the text
> format, ie, the enum label? A bit silly but at least it would eliminate
> the gotcha that binary I/O fails.
>
Seems like it would make a lot more sense to the client application that
way too...

-- Korry

Tom Lane wrote:
> What if we just defined the binary format as being identical to the text
> format, ie, the enum label? A bit silly but at least it would eliminate
> the gotcha that binary I/O fails.
>
>
>

So we should pretty much mimic text_send/text_recv? If so I can probably
get that done fairly quickly.

cheers

andrew

On 8/30/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > I noticed that enums are not available to be queried as binary through
> > the protocol.
>
> What do you think the binary representation ought to be? Copying OIDs
> seems pretty useless.

I'm on the fence on this one.

I actually think this would be ok, if you mean pg_enum.oid, or the
string would be fine too. I agree that binary protocol is supposed to
be fast, and I can prefetch the pg_enum table to the client and do the
magic there. Many other binary formats do similarly inscrutable
things.

One other very small observation: afaict, there appears to be no way
to list enum contents in psql (although you can list the enums
themselves in the type listing). Maybe this should be possible? I'm
willing to take a stab at these things if Andrew is busy.

merlin

Merlin Moncure wrote:
>
> One other very small observation: afaict, there appears to be no way
> to list enum contents in psql (although you can list the enums
> themselves in the type listing).

select enum_range(null::myenumtype);

> I'm willing to take a stab at these things if Andrew is busy.
>
>
>

I should have a cut of binary I/O done today, which I will send to you
for testing and TomD for comment. We need to get it in by beta as it's a
catalog change.

cheers

andrew

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 8/30/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What do you think the binary representation ought to be? Copying OIDs
>> seems pretty useless.

> I actually think this would be ok, if you mean pg_enum.oid, or the
> string would be fine too. I agree that binary protocol is supposed to
> be fast, and I can prefetch the pg_enum table to the client and do the
> magic there. Many other binary formats do similarly inscrutable
> things.

Well, inscrutable is one thing and unportable is another. It's supposed
to be possible to reload binary COPY data into a fresh database --- with
maybe some restrictions on the architecture being similar, for the more
machine-specific datatypes such as float. If we emit raw OIDs then this
will never work, since the same type definition made in a fresh database
would have the same OIDs only by awe-inspiring coincidence.

Andrew's idea of using the enum ordinal value would meet that test, but
at least with the current layout of pg_enum it would be quite expensive
to do the conversion in either direction --- you'd have to fetch
multiple catalog rows. I think we'd have to add another column showing
the ordinal value, and put an index on it, to make I/O reasonably fast.
Doesn't really seem worth it.

regards, tom lane

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:

> On 8/30/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
>> > I noticed that enums are not available to be queried as binary through
>> > the protocol.
>>
>> What do you think the binary representation ought to be? Copying OIDs
>> seems pretty useless.
>
> I'm on the fence on this one.
>
> I actually think this would be ok, if you mean pg_enum.oid, or the
> string would be fine too. I agree that binary protocol is supposed to
> be fast, and I can prefetch the pg_enum table to the client and do the
> magic there. Many other binary formats do similarly inscrutable
> things.

I think it would be ok only if a pg_dump/pg_restore reliably restored the same
oid->enum value mapping. Otherwise a binary dump is useless. But as I
understand it that's the case currently, is it?

You would also have to have some way for a client to simply look up the enum
mapping. Something like an SRF that returned setof(oid,name).

My first instinct was to just use the enum name but I'm starting to think this
would be better. It is more in line with the promise of enums which is that
the names are just labels and the data internally is fixed size.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark escribió:

> I think it would be ok only if a pg_dump/pg_restore reliably restored the same
> oid->enum value mapping. Otherwise a binary dump is useless. But as I
> understand it that's the case currently, is it?

That doesn't work if the dump is restored on a database that already has
those OIDs used for another enum. The easy workaround would be to
"rewrite" the data to use the new OID, but believe me, you don't want to
go down that route, lest madness await you at the end.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"

Tom Lane wrote:
>
> Andrew's idea of using the enum ordinal value would meet that test, but
> at least with the current layout of pg_enum it would be quite expensive
> to do the conversion in either direction --- you'd have to fetch
> multiple catalog rows. I think we'd have to add another column showing
> the ordinal value, and put an index on it, to make I/O reasonably fast.
> Doesn't really seem worth it.
>
>
>

Yeah. I think we should treat enums just as we do text, for this purpose.

Here's a patch (minus catalog bump) which I think does that.

cheers

andrew

"Alvaro Herrera" <alvherre(at)commandprompt(dot)com> writes:

> Gregory Stark escribió:
>
>> I think it would be ok only if a pg_dump/pg_restore reliably restored the same
>> oid->enum value mapping. Otherwise a binary dump is useless. But as I
>> understand it that's the case currently, is it?

er, lost a "not" in the editing of that, sorry.

> That doesn't work if the dump is restored on a database that already has
> those OIDs used for another enum. The easy workaround would be to
> "rewrite" the data to use the new OID, but believe me, you don't want to
> go down that route, lest madness await you at the end.

enum OIDs are unique across enums? This seems like a strange way to do it. I
recall conversations about this a while back though and there were limitations
of the type system that led to this, right?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> enum OIDs are unique across enums? This seems like a strange way to do it.

That decision was already made, we are not revisiting it (at least not
for 8.3).

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Here's a patch (minus catalog bump) which I think does that.

Looks sane in a very quick once-over, but I didn't test it.

regards, tom lane

Gregory Stark wrote:
> enum OIDs are unique across enums? This seems like a strange way to do it. I
> recall conversations about this a while back though and there were limitations
> of the type system that led to this, right?
>

No, not the type system as such. It stems from this quote from Tom:

> If an output function depends on anything more than the contents of
> the object it's handed, it's vulnerable to being lied to.
> http://archives.postgresql.org/pgsql-hackers/2005-04/msg00998.php

So the value passed to the enum_out function has to be sufficiently
unique to be able to look up the label. This arrangement got the best
combination of compactness and simplicity that we could come up with at
the time.

Incidentally, FWIW, I have heard tales of considerable speedup from
people being able to avoid using FKs/lookup tables by using enums.

cheers

andrew

On Fri, Aug 31, 2007 at 08:49:05AM -0400, Merlin Moncure wrote:
> On 8/30/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > > I noticed that enums are not available to be queried as binary through
> > > the protocol.
> >
> > What do you think the binary representation ought to be? Copying OIDs
> > seems pretty useless.
>
> I'm on the fence on this one.
>
> I actually think this would be ok, if you mean pg_enum.oid, or the
> string would be fine too. I agree that binary protocol is supposed to
> be fast, and I can prefetch the pg_enum table to the client and do the
> magic there. Many other binary formats do similarly inscrutable
> things.

The last time I worked on a project where we had C code access the
database, we added stuff to map C enums to ints in the database (along
with a parent table to store the enum label). ISTM it'd be good if we
had a way to get the numeric index out of an enum. I also like the idea
of having a fixed ordering to the labels in an enum.

> One other very small observation: afaict, there appears to be no way
> to list enum contents in psql (although you can list the enums
> themselves in the type listing). Maybe this should be possible? I'm
> willing to take a stab at these things if Andrew is busy.

Is there an SRF that will return this info? ISTM you should be able to
get the labels programmatically as well as via psql.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Decibel! wrote:
>
> The last time I worked on a project where we had C code access the
> database, we added stuff to map C enums to ints in the database (along
> with a parent table to store the enum label). ISTM it'd be good if we
> had a way to get the numeric index out of an enum.

If you mean here that you want to find the position in the ordering of
an enum value, it would be trivial to write, searching in the results
from enum_range().

Next cycle it might be worth adding a column to pg_enum with the
explicit order. My serious worry, though, is that it might lead people
to think they could alter that column and thereby change the ordering,
which of course they can't. (Quite apart from anything else, a mutable
ordering would play havoc with enums used in indexes.)

The technique of using a lookup table that you seem to refer to doesn't
need any special support from the catalogs or the type system. It's used
today in millions of applications. But it can have quite a high cost in
extra joins required to get the labels and extra application complexity.
For a case where the values in the domain of labels are truly fixed,
enums offer a much more performant and much simpler way to go.

> I also like the idea
> of having a fixed ordering to the labels in an enum.
>

I do not understand what this sentence means. The ordering *is* fixed -
it is defined by the order in which the labels are given in the create
type statement. And the oids that are assigned to the values in pg_enum
are sorted before being assigned to the labels precisely so that they
reflect this ordering. So rest assured that a given enum type will have
a fixed ordering, and it will be consistent across a dump/restore. What
will not necessarily be consistent is the actual oids used, making the
oids unsuitable for use in binary output as noted upthread.
>
>> One other very small observation: afaict, there appears to be no way
>> to list enum contents in psql (although you can list the enums
>> themselves in the type listing). Maybe this should be possible? I'm
>> willing to take a stab at these things if Andrew is busy.
>>
>
> Is there an SRF that will return this info? ISTM you should be able to
> get the labels programmatically as well as via psql.
>

Maybe you need to read
http://developer.postgresql.org/pgdocs/postgres/functions-enum.html to
see info we have made available. We fully expect this list of functions
to grow as we discover how enums are used in practice.

cheers

andrew

On Fri, Aug 31, 2007 at 01:41:47PM -0400, Andrew Dunstan wrote:
> The technique of using a lookup table that you seem to refer to doesn't
> need any special support from the catalogs or the type system. It's used
> today in millions of applications. But it can have quite a high cost in
> extra joins required to get the labels and extra application complexity.
> For a case where the values in the domain of labels are truly fixed,
> enums offer a much more performant and much simpler way to go.

AIUI, in C code it's easiest to deal with the int value that a C enum
gets, rather than dealing with a label coming back from the database. I
know that's what we did where I worked; the enum column stored the
corresponding C int, and that's what was used as the PK in the lookup
table.

ISTM it'd be good if we could do the same with our enums (pass the int
value back instead of a label).

> >I also like the idea
> >of having a fixed ordering to the labels in an enum.
> >
>
> I do not understand what this sentence means. The ordering *is* fixed -
> it is defined by the order in which the labels are given in the create
> type statement. And the oids that are assigned to the values in pg_enum
> are sorted before being assigned to the labels precisely so that they
> reflect this ordering. So rest assured that a given enum type will have
> a fixed ordering, and it will be consistent across a dump/restore. What
> will not necessarily be consistent is the actual oids used, making the
> oids unsuitable for use in binary output as noted upthread.

What if the OID counter wraps in the middle of adding the labels? (IE:
create a 4 label ENUM when the OID counter is 1 number away from
wrapping).

If we ever add support for adding additional labels to enums this could
be an issue too.

> Maybe you need to read
> http://developer.postgresql.org/pgdocs/postgres/functions-enum.html to
> see info we have made available. We fully expect this list of functions
> to grow as we discover how enums are used in practice.

Looks good... should still be a psql command, imo. Perhaps as part of
\dT...
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Decibel! wrote:
> On Fri, Aug 31, 2007 at 01:41:47PM -0400, Andrew Dunstan wrote:
>
>> The technique of using a lookup table that you seem to refer to doesn't
>> need any special support from the catalogs or the type system. It's used
>> today in millions of applications. But it can have quite a high cost in
>> extra joins required to get the labels and extra application complexity.
>> For a case where the values in the domain of labels are truly fixed,
>> enums offer a much more performant and much simpler way to go.
>>
>
> AIUI, in C code it's easiest to deal with the int value that a C enum
> gets, rather than dealing with a label coming back from the database. I
> know that's what we did where I worked; the enum column stored the
> corresponding C int, and that's what was used as the PK in the lookup
> table.
>
> ISTM it'd be good if we could do the same with our enums (pass the int
> value back instead of a label).
>

Jim, you and I have had this discussion before. The answer is the same
as the last time you asked about this: because it breaks the abstraction.

If the C code doesn't need the label then why store it at all? You can
just make the database field an int.

If you want to get the ordering offset of a particular enum value you
can write a function in about 10 lines of C that will give it to you.

>
>>> I also like the idea
>>> of having a fixed ordering to the labels in an enum.
>>>
>>>
>> I do not understand what this sentence means. The ordering *is* fixed -
>> it is defined by the order in which the labels are given in the create
>> type statement. And the oids that are assigned to the values in pg_enum
>> are sorted before being assigned to the labels precisely so that they
>> reflect this ordering. So rest assured that a given enum type will have
>> a fixed ordering, and it will be consistent across a dump/restore. What
>> will not necessarily be consistent is the actual oids used, making the
>> oids unsuitable for use in binary output as noted upthread.
>>
>
> What if the OID counter wraps in the middle of adding the labels? (IE:
> create a 4 label ENUM when the OID counter is 1 number away from
> wrapping).
>

It will not be a problem. I have just explained that we sort them first.
This is a furfy that has been raised before and explained before. See
pg_enum.c starting around line 52. In particular:

/* sort them, just in case counter wrapped from high to low */
qsort(oids, n, sizeof(Oid), oid_cmp);

> If we ever add support for adding additional labels to enums this could
> be an issue too.
>

I doubt we will be doing it. You can get the effect by defining a new
type and using the old labels.

>
>> Maybe you need to read
>> http://developer.postgresql.org/pgdocs/postgres/functions-enum.html to
>> see info we have made available. We fully expect this list of functions
>> to grow as we discover how enums are used in practice.
>>
>
> Looks good... should still be a psql command, imo. Perhaps as part of
> \dT...
>

We'd have to special case enums, or provide a special \d command to
handle them. Not sure either is worth it when we have the functions anyway.

cheers

andrew

On 8/31/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > Here's a patch (minus catalog bump) which I think does that.
>
> Looks sane in a very quick once-over, but I didn't test it.

works fine (here was my test). thanks for quick resolution to this
issue. strings returned in binary format is IMO ok.

enum.c:
include "libpq-fe.h"
#include "string.h"
#include <stdlib.h>

int main(int argc, char **argv)
{
PGconn *c = PQconnectdb("user=postgres");
PGresult *r;
r = PQexecParams(c, "select 'foo'::foo", 0, NULL, NULL, NULL, NULL, 1);

ExecStatusType t = PQresultStatus(r);

if(t != PGRES_COMMAND_OK & t != PGRES_TUPLES_OK)
{
printf("%s", PQresultErrorMessage(r));
exit(1);
}

char* f = PQgetvalue(r,0,0);
int len = 3;
int format = 1;
PQclear(r);

r = PQexecParams(c, "select $1::foo", 1, NULL, (const char* const
*)&f, &len, &format, 1);

if(t != PGRES_COMMAND_OK & t != PGRES_TUPLES_OK)
{
printf("%s", PQresultErrorMessage(r));
exit(1);
}

PQfinish(c);
}

On 8/31/07, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On 8/31/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > > Here's a patch (minus catalog bump) which I think does that.
> >
> > Looks sane in a very quick once-over, but I didn't test it.
>
> works fine (here was my test). thanks for quick resolution to this
> issue. strings returned in binary format is IMO ok.
> if(t != PGRES_COMMAND_OK & t != PGRES_TUPLES_OK)

oops, this line was wrong. the enum is fine though.

merlin

Andrew Dunstan wrote:
>
>
> This is a furfy that has been raised before and explained before.

Of course, as usual I misspelled it, the word is "furphy". I didn't
realise that it was an Australianism. It means more or less "a red
herring". Wikipedia says that it is a term particularly popular in
Australian politics, and as some people know my late father was a noted
practitioner of that art ;-)

cheers

andrew

Merlin Moncure wrote:
> On 8/31/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>
>>> Here's a patch (minus catalog bump) which I think does that.
>>>
>> Looks sane in a very quick once-over, but I didn't test it.
>>
>
> works fine (here was my test). thanks for quick resolution to this
> issue. strings returned in binary format is IMO ok.
>
>
>

Patch applied (with catalog bump).

cheers

andrew