Re: PGSQL or other DB?

Scott Marlowe wrote:
> On Sat, Jan 31, 2009 at 2:13 AM, Erik Jones <ejones@engineyard.com> wrote:
>> On Jan 30, 2009, at 11:37 AM, durumdara wrote:
>
>>>  - I can add/modify a table, or a field to a table without "full lock" on
>>> the table (like DBISAM restructure). Like in FireBird, where the "add field"
>>> change only the table description. I don't know that PG supports this way of
>>> the DB modifying.
>> Nope.  PostgreSQL is an all or nothing transactional database.  I'd never
>> heard of DBISAM before you mentioned it and have never used Firebird.  After
>> doing a little reading it turns out that if you plan to use transactions at
>> all (which is very likely given even just the little you've described about
>> the applications you're building) then you should realize that altering
>> tables is not compatible with transactions and doing so will automatically
>> commit any open transactions on the table.
>
> Are talking about pgsql or some other database?  Everything in pgsql
> can be done in a transaction, except create / drop database /
> tablespace.
>
>>  Looking into Firebird I couldn't
>> find how it handles (or doesn't) that at all I but I did see that it will
>> happily let you add a new not null column with no default to a table by
>> writing nulls for the new attribute for any existing columns.  That already
>> makes me queasy.
>
> That's pretty much what pgsql does.  Why does it make you queasy?
>

I think the key is that the new column is NOT NULL, so defaulting the
new column's values to NULL results in immediate data integrity
inconsistency.

If I remember rightly, PG doesn't allow this: you have to create the
column as NULL, UPDATE and then add the NOT NULL constraint, or perhaps
(I haven't tried this) create the column with a default and then remove
it immediately afterwards.