[GENERAL] Postgresql reindex hangs when table is being updated

[deep ...]

Hi all,

I'm running a web application supported by postgresql 7.4 on an unix
system. The management part of application requires reindexing of
tables. I see that whenever reindexing runs with the while the table
is getting updated/inserted into, reindexing hangs. I understand this
is because reindexing requires an exclusive lock on the table.


Is there a possible workaround? What else can I do other than stopping
the insertion/update while reindex runs?

Thanks,
Walter

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Table definition changes when a row is dropped and recreated

[deep ...]

Hi everyone,

I have a java app using pgsql 7.3 on unix platform where the latest
version of the app updates the width of a row in a table as a part of
upgrade. Since there is no straightforward 'modify' clause in
Postgresql 7.3, we're using

alter table ...  add new_column type (width);
update ... set new_column=old_column;
alter table... drop old_column;

After running this sql as a part of upgrade script, the table
definition changes in database, in that the widened column gets pushed
to the end of the definition (as can be seen through \d table_name
command). This is making the insert queries fail which were designed
with the original table definition in mind.

Assuming there is no way to know whether the insert command is running
before or after upgrade (from within the database handler part of the
app, I mean), how do we solve this problem? Backup data, Drop the
table and create it again? In that case, what happens to the indexes
and constraints?

Thanks in advance,
Walter

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/