6

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?

So I rename the table with:

ALTER TABLE xyz RENAME TO abc;

And my VIEW defition for the sake of example is:

CREATE VIEW foo AS SELECT * FROM xyz;

Next thing I know after I rename the table, the definition for foo has changed to point to table abc.

I'm using Postgresql 8.4

--Updated: Dec 30, 2014 at 12:41pm--

Here is a test case to illustrate what I mean:

CREATE TABLE tmp_test_a (num integer);
CREATE VIEW v_tmp_test_a AS SELECT * FROM tmp_test_a;

-- Now look at what the VIEW is using (now is using tmp_test_a, which is what it should be using):
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Now create a second table, and swap them.
CREATE TABLE tmp_test_b (num integer);
ALTER TABLE tmp_test_a RENAME TO tmp_test_c;
ALTER TABLE tmp_test_b RENAME TO tmp_test_a;
ALTER TABLE tmp_test_c RENAME TO tmp_test_b;

-- Now look at what the VIEW is using again (now is using tmp_test_b with an alias of tmp_test_a)
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Cleanup
DROP VIEW v_tmp_test_a;
DROP TABLE tmp_test_a;
DROP TABLE tmp_test_b;
  • In case anyone is wondering, I am using a duplicate of table xyz to bulk load table into without affecting the live production table, and swapping the table names when I'm done. The views however need to always use the live production tables. I have a feeling that the view definitions are somehow really using the behind the scenes OIDs for the tables they query. – Pyrite Dec 30 '14 at 4:18
  • I'd either 1) just delete xyz and rename abc, assuming this is all being done outside of peak hours or 2) drop and re-create the views as needed. This is something you're going to have to work around. – LowlyDBA Dec 30 '14 at 20:28
  • You can't delete tables that a VIEW depends on without deleting the VIEW first. Sure for right now I am re-creating the views each time, but the whole point of this is to re-load data in production tables during production without affecting concurrent users. May be there is a better way? – Pyrite Dec 30 '14 at 20:50
  • Using the CASCADE option, the view would be deleted when the table is dropped. You'd have to still re-create the view but it's one less statement to run. – LowlyDBA Dec 30 '14 at 21:02
5

The simple answer - because views reference the OID and not the object name. The name is translated back in the internal function.

A view in PostgreSQL can be thought of as an empty table with a select rewrite rule. Using your example, the internal query tree for the rewrite rule is located in the pg_rewrite.ev_action table/column and there you'll see the references to the individual table and column OIDs.

What I'd recommend doing is enclose the table rename, and a CREATE OR REPLACE VIEW statement within a single transaction, that way everything becomes relatively seamless and the view is recreated pointing to the new table's OID.

  • I ended up just replacing all the VIEWs. This to me is unacceptable, but a requirement until I can figure out a better way. I read on another site about table partitioning might be a better way to handle this scenario, but I don't know yet. Thank you for the insight though. – Pyrite Dec 31 '14 at 19:32
  • Partitioning is great if you have an easily defined partition key (e.g. a date range, a numeric range/enum, etc.). If you encapsulate it all in one transaction, it should appear near instantaneous to the end user. – Nathan Clayton Dec 31 '14 at 19:38
  • My thought was, have 2 partitions, 1 for the live data, and the other for the data being bulk reloaded, and do some kind of swap. The table name remains the same, so all the VIEWS using it don't need to be updated. Sound feasible? – Pyrite Dec 31 '14 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.