Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can I feed some SQL that creates a view to Slony?

I have some SQL that does CREATE OR REPLACE VIEW name_of_view and so on, passes it to slonik_execute_script, and then tries to run slonik_create_set for a temporary Slony set containing only that view.

I get rejected with ERROR: Slony-I: table "public"."name_of_view" has no primary key error. To my knowledge, views don't support primary keys.

Does this mean there is no way, using the Slony admin scripts, to add a view to replication, and that I should just install it manually on all members of my replication cluster?

share|improve this question
up vote 3 down vote accepted

Slony is trigger based. Since you cannot have triggers on views you can not use Slony to replicate views.

However, you can achieve the desired effect by using Slony to replicate the tables used by your views and duplicate the view definitions on your slave servers.

share|improve this answer
You can have triggers on views since PostgreSQL 9.1 - But since current versions of Slony support older Postgres versions, your point seems to be valid. On the other hand, I found reports of someone experimenting with materialized views, so it seems to be possible somehow -… – Pavel V. Apr 10 '14 at 8:31

According to the docs only ordinary tables are supported:

Slony-I: setAddTable_int(): public.my_view is not a regular table You can only replicate (at least, using SET ADD TABLE) objects that are ordinary tables. That doesn't include views or indexes. (Indexes can come along for the ride, but you don't ask to replicate an index...)

There is a check in the code to enforce this

The error message you get seems correct as I assume you haven't specified any primary or unique key in slon_tools.conf for the view. But what if you actually specify a primary or unique key - which must be a column returned by the view? Then I would expect slonik_create_set to pass the primary/unique key check and instead fail with the error:

Slony-I: setAddTable_int(): "public"."my_name_of_view" is not a regular table

That is my 2 cents.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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