Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication
You mean at the user permissions level? Yes, I could, but would mean doing so table by table, which is not our current structure. I guess there is nothing at the database level. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Making subscribers read only in Postgres 10 logical replication
Hi I'm testing out logical replication on PostgreSQL 10. Is there a setting to make subscribers read-only slaves like with Slony. Currently I can insert into the Publisher and the Subscriber. If there is a conflict, i.e. same record exists in both, then all replication gets backed up (even to other tables) till that one record is resolved. Thanks RV -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Partitioned postgres tables don't need update trigger??
Perfect! Thanks for the response! Just wanted to make sure I wasn't missing anything. -- View this message in context: http://postgresql.nabble.com/Partitioned-postgres-tables-don-t-need-update-trigger-tp5906403p5906415.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partitioned postgres tables don't need update trigger??
I am working with partitioned tables. I have partitioned based on date and I have the INSERT trigger in place, I don't have an Update or Delete Trigger but both updates and deletes against the master table work correctly. I am not sure how these are working without triggers. Any insight? So, this deletes the right number of rows : / delete from torque.test_master where tstamp ='2012-08-03 03:00:00'; / And this updates the right rows : / update torque.test_master set system='zzz' where tstamp ='2012-08-03 04:00:00';/ *Here are the sample tables.* /CREATE TABLE torque.test_master( testmstr_seq_id bigserial NOT NULL, tstamp timestamp without time zone NOT NULL, system text NOT NULL, CONSTRAINT pk_testmstr_id PRIMARY KEY (testmstr_seq_id))WITH ( OIDS=TRUE); CREATE TABLE torque.test_y2012m08( CONSTRAINT pk_test_y2012m08_id PRIMARY KEY (testmstr_seq_id), CONSTRAINT test_y2012m08_log_tstamp_check CHECK (tstamp >= '2012-08-01 00:00:00'::timestamp without time zone AND tstamp < '2012-09-01 00:00:00'::timestamp without time zone))INHERITS (torque.test_master)WITH ( OIDS=TRUE); CREATE OR REPLACE FUNCTION torque.test_child_insert() RETURNS trigger AS$BODY$BEGIN IF ( new.tstamp >= '2012-08-01' AND new.tstamp < '2012-09-01') THEN INSERT INTO torque.test_y2012m08 VALUES (NEW.*); ELSEIF ( new.tstamp >= '2015-05-01' AND new.ltstamp < '2015-06-01') THEN INSERT INTO torque.test_y2015m05 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the test_child_insert() function!'; END IF; RETURN NULL;END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; / CREATE TRIGGER testmaster_insert_trigger BEFORE INSERT ON torque.test_master FOR EACH ROW EXECUTE PROCEDURE torque.test_child_insert(); -- View this message in context: http://postgresql.nabble.com/Partitioned-postgres-tables-don-t-need-update-trigger-tp5906403.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Partitioned tables do not return affected row counts to client
We are looking to move from one large table to partitioned tables. Since the inserts and updates are made to the master table and then inserted into the appropriate partitioned table based on the trigger rules, the affected_rows returned to the client (PHP in this case) is always 0. We have been using the affected_rows to check various things on the client end. Is there an alternative to check for rows affected with partitioned tables? Seems like a pretty big missing feature. Thanks RV -- View this message in context: http://postgresql.nabble.com/Partitioned-tables-do-not-return-affected-row-counts-to-client-tp5906112.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Inserting into a master table with partitions does not return rows affected.
I am moving towards a partitioned schema. I use a function to insert into the table. If the INSERT fails because of duplicates I do an UPDATE. This works fine currently on the non-partitioned table because I can use GET DIAGNOSTICS to get the row count on the INSERT. But when I use the Master table to insert into the partitions, GET DIAGNOSTICS always returns 0. So there is no way of knowing whether a row was inserted (I am catching the unique violation exception to do the UPDATE). What is a good alternative? We are on 9.4, so the UPSERT is not yet available to me. There should be some way to know if data was inserted into the partition. Thanks RV -- View this message in context: http://postgresql.nabble.com/Inserting-into-a-master-table-with-partitions-does-not-return-rows-affected-tp5902708.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Primary key vs unique index
Yes I am using that option for one of my POstgres 9.1 database and it works well. But its still an issue with Foreign keys, which you need to drop and recreate . Also I use Slony for replication and it uses the primary key to check repl. So I don't want that to be interrupted by dropping PK and recreating PK. Thanks RV -- View this message in context: http://postgresql.1045698.n5.nabble.com/Primary-key-vs-unique-index-tp3883778p5715729.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Primary key vs unique index
We are experiencing a similar problem, even though we are on 8.4 and have been for a while, and have autovacuum turned on. I have regular concurrent reindexes on the indexes but the primary key is seriously bloated. I was considering doing the same thing, that is, create another primary key that is built on a sequence ( primarily for slony) and then change my current multi-column primary key to a unique index. Have you been able to work around the problem in any other way? Thanks RV -- View this message in context: http://postgresql.1045698.n5.nabble.com/Primary-key-vs-unique-index-tp3883778p5715712.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
