SEO

Andrew Dunstan wrote:
>
> Bruce Momjian wrote:
>> Log Message:
>> -----------
>> Update:
>>
>> < * Allow adding enumerated values to an existing enumerated data
>>
>>> * Allow adding/removing enumerated values to an existing enumerated data
>
> Where did this come from? Adding values anywhere except on the end of
> the enumeration list will be fraught with danger, as will removing them.
> In essence, either operation would entail rewriting every table that
> used the type. Anything else carries a major risk of corruption. That
> seems like a pretty bad idea.

We already support rewriting tables ... (albeit only one at a time, I
admit. Doing it for more than one can cause deadlocks).

Still, if the user wants to pay the cost, why should we prohibit it?

Perhaps we should add a pg_depend entries on tables using the type (or
we have them already), and disallow modifying it unless there are no
users of it. So the user can create a new enum with the options he
wants, then rewrite his tables one by one, then drop the original.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 26, 2008 at 4:27 AM, Alvaro Herrera wrote:
> Andrew Dunstan wrote:
> >
> > Bruce Momjian wrote:
> >> Log Message:
> >> -----------
> >> Update:
> >>
> >> >>
> >>> * Allow adding/removing enumerated values to an existing enumerated data
> >
> > Where did this come from? Adding values anywhere except on the end of
> > the enumeration list will be fraught with danger, as will removing them.
> > In essence, either operation would entail rewriting every table that
> > used the type. Anything else carries a major risk of corruption. That
> > seems like a pretty bad idea.
>
> We already support rewriting tables ... (albeit only one at a time, I
> admit. Doing it for more than one can cause deadlocks).
>
> Still, if the user wants to pay the cost, why should we prohibit it?
>

I agree with Alvaro's sentiment here, but it does seem likely that
adding an value to the end of an enum list is much lower-hanging fruit
than add/remove.

Has anyone had a close look at how hard it would be allow just the
"add to the end" capability?

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIEiWO5YBsbHkuyV0RAo5pAKDMQ7aAbJJyIe74c+PacX VXg5chXACdEnv3
sFiNsSf193/C9HpW5UVhYWs=
=jOzI
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Brendan Jurd" <direvus@gmail.com> writes:
> Has anyone had a close look at how hard it would be allow just the
> "add to the end" capability?

The problem is you can't guarantee anything about the ordering of the
new value relative to the old ones. The OID it's assigned might be
after them, or before them if the OID counter has wrapped around,
or (with much smaller probability) between two existing ones.

This is something we consciously gave up when we selected the current
ENUM implementation.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 26, 2008 at 6:02 AM, Tom Lane wrote:
> "Brendan Jurd" writes:
> > Has anyone had a close look at how hard it would be allow just the
> > "add to the end" capability?
>
> The problem is you can't guarantee anything about the ordering of the
> new value relative to the old ones. The OID it's assigned might be
> after them, or before them if the OID counter has wrapped around,
> or (with much smaller probability) between two existing ones.
>

I see. So to add a value on to the end with guaranteed ordering, you
would have to rebuild the enum from scratch anyway.

Then there's no technical difference at all in implementing "add to
the end" and "add/remove".

Thanks for the answer.

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIEjrO5YBsbHkuyV0RAogmAKC3u8wogvrUNSfFUx/PbKyS6U2/DgCgjDSu
z+VNazq7LiRdiU4oUvKL0jc=
=obXq
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Apr 26, 2008 at 12:10 AM, Brendan Jurd <direvus@gmail.com> wrote:
> Has anyone had a close look at how hard it would be allow just the
> "add to the end" capability?

If the OIDs haven't wrapped around since the enum was created, it's
trivial. If they have, well someone with more OID-fu than me will have
to explain what we'd have to do to guarantee getting a new OID higher
than a certain value. And if your highest enum oid happens to be
4^32-1, you're in trouble .

I wonder if it's worth revisiting the decision to save enums on disk
as oids. The very first idea that I had was to have an enum value as
the combination of both an enum id and the ordinal value. We would
presumably make both say 16bits so we could still be be passed by
value. This would restrict us to 2^16 enum types per database and 2^16
values per type, but if anyone is getting within an order of magnitude
of either limit I'd be very interested in seeing what they're doing.

The nice thing about the above approach is that we could space out the
ordinal values so as to allow people to insert a fair number of extra
values before forcing a rewrite of the table. The only thing we really
couldn't handle that way would be reordering - we'd need an extra
layer of indirection for that, which would have some performance
penalties. None of the standard operators for enums require a syscache
lookup currently, only I/O does,

Cheers

Tom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Brendan Jurd escribió:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Sat, Apr 26, 2008 at 6:02 AM, Tom Lane wrote:
> > "Brendan Jurd" writes:
> > > Has anyone had a close look at how hard it would be allow just the
> > > "add to the end" capability?
> >
> > The problem is you can't guarantee anything about the ordering of the
> > new value relative to the old ones. The OID it's assigned might be
> > after them, or before them if the OID counter has wrapped around,
> > or (with much smaller probability) between two existing ones.
>
> I see. So to add a value on to the end with guaranteed ordering, you
> would have to rebuild the enum from scratch anyway.

The other alternative is to make the system generate the new OIDs in
such a way that the ordering is preserved. This, of course, has a lot
of problems of its own.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Apr 25, 2008 at 11:57 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> We already support rewriting tables ... (albeit only one at a time, I
> admit. Doing it for more than one can cause deadlocks).
>
> Still, if the user wants to pay the cost, why should we prohibit it?

One scenario I'm not happy about is this: the friendly db admin has
happily added an extra value to the end before and the operation has
been a snap - no rewriting required. But this time either a) oid
wraparound has occurred, b) she's inserted one or c) she's reordered
them. Bam - we start rewriting the entire database. That's not the
kind of surprise I like giving people, and the current situation of
either don't allow updates at all, or the alternative to surprises of
always rewrite everything seem pretty deficient. And I don't want to
only allow updates if they won't cause a rewrite, it's
nondeterministic.

I've already suggested some alternatives in the reply to Brendan that
would solve some of this, but I suppose another gross-seeming way to
stop surprise rewrites would be to never do one unless given a FORCE
REWRITE clause on the ALTER statement or something like that, and fail
if a rewrite is required not specified.

> So the user can create a new enum with the options he
> wants, then rewrite his tables one by one, then drop the original.

They can pretty much do this now, they just need to define an implicit
cast I think.

Cheers

Tom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 26, 2008 at 6:19 AM, Tom Dunstan wrote:
> I wonder if it's worth revisiting the decision to save enums on disk
> as oids. The very first idea that I had was to have an enum value as
> the combination of both an enum id and the ordinal value.

That's very intuitive.

> The nice thing about the above approach is that we could space out the
> ordinal values so as to allow people to insert a fair number of extra
> values before forcing a rewrite of the table. The only thing we really
> couldn't handle that way would be reordering - we'd need an extra
> layer of indirection for that, which would have some performance
> penalties. None of the standard operators for enums require a syscache
> lookup currently, only I/O does,
>

Wouldn't you still be able to do a reorder with a table rewrite?

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFIEkBC5YBsbHkuyV0RAggsAJ0btEoQTTwiakLPRg/sixcXFCroLwCgleri
Q/JebP+AWErEl+w2+QHL16o=
=YUGG
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Tom Dunstan" <pgsql@tomd.cc> writes:
> I wonder if it's worth revisiting the decision to save enums on disk
> as oids. The very first idea that I had was to have an enum value as
> the combination of both an enum id and the ordinal value. We would
> presumably make both say 16bits so we could still be be passed by
> value. This would restrict us to 2^16 enum types per database and 2^16
> values per type, but if anyone is getting within an order of magnitude
> of either limit I'd be very interested in seeing what they're doing.

I seem to remember that we discussed that and rejected it, but I don't
remember the reasoning...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Brendan Jurd" <direvus@gmail.com> writes:
> On Sat, Apr 26, 2008 at 6:33 AM, Tom Dunstan wrote:
>> I've already suggested some alternatives in the reply to Brendan that
>> would solve some of this, but I suppose another gross-seeming way to
>> stop surprise rewrites would be to never do one unless given a FORCE
>> REWRITE clause on the ALTER statement or something like that, and fail
>> if a rewrite is required not specified.

> That would be okay too, but I think I'd prefer proceeding with the
> rewrite after emitting a NOTICE. If the db admin decides not to go
> ahead, or wait to do it after hours, she can always hit ^C, right?

The more I think about it, the less I think that we want to support such
a feature at all. Consider that it'd require taking a fairly strong
lock (surely at least locking out other writers) on every table using
the enum, in who-knows-what order. The odds of completing without
deadlock seem to be right about nil.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers