Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

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 - postgresql.org/docs/9.1/static/sql-createtrigger.html. 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 - comments.gmane.org/gmane.comp.db.postgresql.slony1.general/… –  Pavel Apr 10 at 8:31
add comment

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...)

http://slony.info/documentation/stmtsetaddtable.html

There is a check in the code to enforce this http://slony.info/documentation/function.setaddtable-int-integer-integer-text-name-text.html.

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
add comment

Your Answer

 
discard

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.