New answers tagged

2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


2

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT. In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them? So, create your trigger as follows: CREATE TRIGGER TR1 AFTER DELETE ON ...


0

Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction. Here's how the trick goes. Open one postgres session, then type your command : begin; \dn+ While the transaction still running, open another postgres session, and query the ...


1

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events). The function code would be something like this: CREATE OR REPLACE FUNCTION synchronize() RETURNS trigger AS $$ ...


4

Up until 9.4 You can go and ask the system catalogs (pg_type, to be precise): SELECT rolname FROM pg_type t JOIN pg_authid r ON typowner = r.oid WHERE typname = 'bla'; usename ───────── dezso From PostgreSQL 9.5 on From this version on, there is a new object identifier type called regrole, and it makes the query a bit simpler: SELECT ...


1

The postgres user by default has no password. To remove a user password (in this case for the postgres user/role): alter role postgres password null;


0

If newer messages consistently have larger ID values in the message table than the older messages, perhaps you could simplify the filter conditions in your subqueries by removing the checks involving message_last_write_id_*. Those checks may seem to be limiting the number of rows in the result set (thus potentially speeding up the subqueries), but in ...



Top 50 recent answers are included