Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #718

PostgreSQL 9.1.4 and dblink UPDATE rule not working right

Show all headers | View raw

Hi Everyone,

New user to Postgres, and trying to learn the ins and outs. I've worked with Oracle and Informix in the past.

I have a system that has two databases which need to share data between the two. In the case I've been asked to work on, it has to do with inter-office memos.

CompanyA database and CompanyB database each have tables for storing office memos, with fields similar to emails. You have a sender, recipient, date, time, subject, and message.

What we would like to do is place all these memos into a separate database, and use views to allow access from both companies.

So in a test environment, I created my memo database (called memoq01), and then point my two company database tables (texasq01 and caliq01) to the memo database using views and the dblink tool.

Here's an example of one of the tables:

CREATE VIEW emremu_rec
AS SELECT *
   FROM dblink('dbname=memoq01 port=5434', 'SELECT * FROM emremu_rec')
AS emremu_rec(
   EMU_EMU         char(8),
   EMU_FRST_NM     char(15),
   EMU_LST_NM      char(20),
   EMU_DPT         char(15),
   EMU_USG_STS     char(1) );

The view works great for SELECT, and I added a rule for INSERTing new memo users that also works fine.

My problem comes in when I try to do an update of the record. Let's say the employee changed their last name, or switched departments. For that I use an UPDATE rule like the one below:

CREATE RULE emremu_vw_update
AS ON UPDATE TO emremu_rec
DO INSTEAD
   (SELECT dblink_exec('dbname=memoq01 port=5434',
   'UPDATE emremu_rec SET
    EMU_FRST_NM =   ' || quote_nullable(NEW.EMU_FRST_NM) || ',
    EMU_LST_NM =    ' || quote_nullable(NEW.EMU_LST_NM)  || ',
    EMU_DPT =       ' || quote_nullable(NEW.EMU_DPT)     || ',
    EMU_USG_STS =   ' || quote_nullable(NEW.EMU_USG_STS) || '
    WHERE EMU_EMU = ' || quote_nullable(NEW.EMU_EMU)     || ';')
   );

The problem is that I am getting an error message that the "Record was changed by another user. Update aborted." Even though I know I am currently the only user in the database accessing these tables and data. (It's on my test server)

QUESTIONS:

Could this be happening because I am querying records for UPDATE, but my initial query using dblink SELECT is still active?

Is there some way to tell the system not to lock the rows during any initial SELECT, so my UPDATE statement will succeed?

Is there a better way I should approach this or is dblink_exec the best way? Feel free to point me to a manual or doc if I'm on the wrong track.

Thanks in advance.

SteveN. 

Back to comp.databases.postgresql | Previous | Next | Find similar

Thread

PostgreSQL 9.1.4 and dblink UPDATE rule not working right inverasln@gmail.com - 2016-06-16 11:27 -0700

csiph-web