Instead trigger on a view to update base tables ?
In a view of three joined tables, I install a INSTEAD OF trigger fx on the
view. The fx contains a list of felds/columns variable associated to each
base tables.
When an update operation occurs, I am successfully generating the target list
of colums altered on
Each base table. ( comparing OLD v NEW ) and attempting some dynamic sql
generation in my trigger fx.
I am taking the list of modified fields on the view, and attempting an update
on appropriate base tables.
In this sample case "language_preference" was modified on the view and should
update the admn.user base table
EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE id =
$2)', USER_SETTING, USER_SETTING )
USING NEW, NEW.id;
When this executes my exception handler generates "err syntax error at or near
\"$1\"
The formatted statement on my base table (admin.user ) that is throwing this
is executing would be:
UPDATE admin.user SET (language_preference) = ( SELECT language_preference FROM
$1 ) WHERE id = $2)"
Feel Like Im close but missing something fundamental.
I also an update variant
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] )
Which I thought might be applicable. but still googling for sample
implementation.
Thanks for any guidance in this method or better methods to update the base
tables.
Regards
Dave Day
Rename a column if not already renamed.?
I have a function used by my patch files which attempts to determine if the old
column name exists, if so it proceeds with the rename.
CREATE OR REPLACE FUNCTION sys.rename_column(schema_table_ regclass, old_name_
text, new_name text)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
BEGIN
IF EXISTS(SELECT 1 FROM pg_attribute
WHERE attrelid = schema_table_
AND attname = old_name_
AND NOT attisdropped) THEN
EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_, old_name_,
new_name);
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$function$;
This seems to function correctly except.
If I am upgrading a site from Postgres 9.6 to version 11.3 and the column has
already been renamed in the 9.6 branch the above existence test fails
and an exception is generated.
It all seems to work correctly if I repeat this same patch in the 11.3 branch.
The function definition is the same for both branches.
I suspect I am overlooking some fundamental issue here.
Anyone with a thought.
Thanks
Dave Day
RE: Rename a column if not already renamed.?
Thanks for the feedback.
The error is something like column already exists and
Quoting issue is Unlikely, and as I mentioned it works as expected if Im
repeating the patch in 11.3
And I assume would if I was in 9.6.
I suspect it has something to do with the content, or lack of, in the
pg_attribute table following an upgrade.
-Original Message-
From: Luca Ferrari [mailto:fluca1...@gmail.com]
Sent: Tuesday, August 20, 2019 3:41 PM
To: Day, David
Cc: pgsql-gene...@postgresql.org
Subject: Re: Rename a column if not already renamed.?
On Tue, Aug 20, 2019 at 9:07 PM Day, David wrote:
> EXECUTE format ('ALTER TABLE %s RENAME %s TO %s', schema_table_,
> old_name_, new_name);
Hard to say without the error, but any chance there is a quoting problem?
EXECUTE format ('ALTER TABLE %s RENAME %I TO %I', schema_table_, old_name_,
new_name);
Luca
RE: Rename a column if not already renamed.?
I agree the function could be improved to deal with both old and new name existing simultaneously. That is almost certainly the root cause, and one that I would confirm if the tester and site were currently available to me. Our work flow for this scenario is something like: 1. 9.6 pg_dump takes a snapshot of our 9.6 database. 2. Postgres is upgraded/freshly installed to 11.3.. 3. The 9.6 database is restored using the version 11 pg_restore tool. 4. Once our application process starts up, it sees there is a patch available in it's old branch that is one greater then it's restored 9.6 content. That happens to be a merge patch which resets the expectations. It attempts to apply all patches in the new branch since the point of divergence and runs into my current issue. It occurs to me I could simply put an exception handler in the rename column function and I would likely proceed merrily along. But curiosity is killing me and the cat. What is causing the old name to persist in the pg_attribute table after the rename. ? Would a stale function referencing the old column name be a contributor? Regards Dave Day -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, August 20, 2019 4:57 PM To: Day, David Cc: Luca Ferrari ; pgsql-gene...@postgresql.org Subject: Re: Rename a column if not already renamed.? "Day, David" writes: > The error is something like column already exists and Not sure about the workflow this function is used within, but maybe you need to consider what to do when both the old and new column names exist. Because that sure sounds like what is happening. regards, tom lane
RE: Rename a column if not already renamed.?
Restoring into 11.3 instance the 9.6 dump ? -> yes. For the upgrade scenario, I confirmed that both old column name and new name are in the pg_attribute table at the time the patch attempts to rename it. Why both is a big question. However, It is easy enough to re-write the column rename function to deal with the simultaneous possibility. I will include the redefined function in the merge patch and see how it goes. I'll update the thread after some further exploration. Thanks all for your assistance. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, August 21, 2019 11:47 AM To: Day, David ; Tom Lane Cc: Luca Ferrari ; pgsql-gene...@postgresql.org Subject: Re: Rename a column if not already renamed.? On 8/21/19 7:52 AM, Day, David wrote: > I agree the function could be improved to deal with both old and new name > existing simultaneously. > That is almost certainly the root cause, and one that I would confirm if the > tester and site were currently available to me. > > Our work flow for this scenario is something like: > > 1. 9.6 pg_dump takes a snapshot of our 9.6 database. > 2. Postgres is upgraded/freshly installed to 11.3.. > 3. The 9.6 database is restored using the version 11 pg_restore tool. In 3) you are restoring to the new 11.3 instance, correct? > > 4. Once our application process starts up, it sees there is a patch available > in it's old branch that is one greater then it's restored 9.6 content. > That happens to be a merge patch which resets the expectations. > It attempts to apply all patches in the new branch since the point of > divergence and runs into my current issue. > > It occurs to me I could simply put an exception handler in the rename column > function and I would likely proceed merrily along. > But curiosity is killing me and the cat. What is causing the old name to > persist in the pg_attribute table after the rename. ? If you are indeed working on the new instance pg_attribute would have no 'memory' of the dropped column. It would seem to me to come down to what is passed into sys.rename_column() as old_name_, new_name. > > Would a stale function referencing the old column name be a contributor? > > > Regards > > > Dave Day > > > > > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Tuesday, August 20, 2019 4:57 PM > To: Day, David > Cc: Luca Ferrari ; pgsql-gene...@postgresql.org > Subject: Re: Rename a column if not already renamed.? > > "Day, David" writes: >> The error is something like column already exists and > > Not sure about the workflow this function is used within, but maybe you need > to consider what to do when both the old and new column names exist. > Because that sure sounds like what is happening. > > regards, tom lane > > > > > -- Adrian Klaver adrian.kla...@aklaver.com
RE: Rename a column if not already renamed.?
Hi, Finally resolved this. Bottom-line some stupidity-bad analysis on my part. Scenario was - changes were ported from trunk back to a branch and then rolling that branch back into trunk. Altering the rename_column fx to check that old and new name did not exist was a necessary for merge process to complete. I ended up with an additional patch in trunk that would only be relevant to a upgraded system, to DROP IF EXISTS old_column name that was re-added by a trunk patch to when the branch rolled forward. Obviously nothing to do with 9.6 -> 11.3 postgres upgrade. Again thanks to all for assistance Dave -Original Message- From: Day, David Sent: Wednesday, August 21, 2019 2:58 PM To: 'Adrian Klaver' ; Tom Lane Cc: Luca Ferrari ; pgsql-gene...@postgresql.org Subject: RE: Rename a column if not already renamed.? Restoring into 11.3 instance the 9.6 dump ? -> yes. For the upgrade scenario, I confirmed that both old column name and new name are in the pg_attribute table at the time the patch attempts to rename it. Why both is a big question. However, It is easy enough to re-write the column rename function to deal with the simultaneous possibility. I will include the redefined function in the merge patch and see how it goes. I'll update the thread after some further exploration. Thanks all for your assistance. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, August 21, 2019 11:47 AM To: Day, David ; Tom Lane Cc: Luca Ferrari ; pgsql-gene...@postgresql.org Subject: Re: Rename a column if not already renamed.? On 8/21/19 7:52 AM, Day, David wrote: > I agree the function could be improved to deal with both old and new name > existing simultaneously. > That is almost certainly the root cause, and one that I would confirm if the > tester and site were currently available to me. > > Our work flow for this scenario is something like: > > 1. 9.6 pg_dump takes a snapshot of our 9.6 database. > 2. Postgres is upgraded/freshly installed to 11.3.. > 3. The 9.6 database is restored using the version 11 pg_restore tool. In 3) you are restoring to the new 11.3 instance, correct? > > 4. Once our application process starts up, it sees there is a patch available > in it's old branch that is one greater then it's restored 9.6 content. > That happens to be a merge patch which resets the expectations. > It attempts to apply all patches in the new branch since the point of > divergence and runs into my current issue. > > It occurs to me I could simply put an exception handler in the rename column > function and I would likely proceed merrily along. > But curiosity is killing me and the cat. What is causing the old name to > persist in the pg_attribute table after the rename. ? If you are indeed working on the new instance pg_attribute would have no 'memory' of the dropped column. It would seem to me to come down to what is passed into sys.rename_column() as old_name_, new_name. > > Would a stale function referencing the old column name be a contributor? > > > Regards > > > Dave Day > > > > > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Tuesday, August 20, 2019 4:57 PM > To: Day, David > Cc: Luca Ferrari ; pgsql-gene...@postgresql.org > Subject: Re: Rename a column if not already renamed.? > > "Day, David" writes: >> The error is something like column already exists and > > Not sure about the workflow this function is used within, but maybe you need > to consider what to do when both the old and new column names exist. > Because that sure sounds like what is happening. > > regards, tom lane > > > > > -- Adrian Klaver adrian.kla...@aklaver.com
Views and triggers more then one row returned by subquery.
My presumption of views and instead of trigger behavior is that the VIEW first gets populated with the WHERE filter and then the "DELETE or UPDATE" operation will fire against each of the rendered view rows. ( ? ) If this is true then I can't explain the more then one row returned error. [11-1] user=redcom, db=ace_db, app=psql, client=[local] ERROR: 21000: more than one row returned by a subquery used as an expression [11-2] user=redcom, db=ace_db, app=psql, client=[local] LOCATION: ExecScanSubPlan, nodeSubplan.c:347 [11-3] user=redcom, db=ace_db, app=psql, client=[local] STATEMENT: delete from public.rule_example where rule_head=30; I would think that if the subquery matter was in my code and I would get a stack trace with a better indicator then this. SO I am presuming it is in a failure to understand the VEIW-TRIGGER process. my view is modeling a table of translation "steps" in a phone switch application. "steps" are the components of "rules" and rules are components of "folders". The VIEW columns of step, rule_seq, and a rule number are relative and derived from column content. My triggers operate on rows.step_id which is a unique value across rules and folders. ace_db=# select * from public.rule_example where rule_head=30; folder_id | rule_head | rule_seq | rule_step | step_id | opta | optb ---+---+--+---+-+--+-- 1 |30 |1 | 1 | 30 |0 |0 1 |30 |1 | 2 | 40 |0 |0 (2 rows) However when the scope of my delete is not step scoped it fails: ace_db=# delete from public.rule_example where rule_head=30; ERROR: more than one row returned by a subquery used as an expression ace_db=# I was expecting each row of the rendered DELETE VIEW to be executed in succession. That is one step at a time. Apparently that is not happening ??? Base Table. ace_db=# select * from public.my_translator; folder_id | folder_seq | entry_type | opta | optb | step_id ---+++--+--+- 1 | 1 | 0 |0 |0 | 10 1 | 2 | 6 |0 |0 | 20 1 | 3 | 0 |0 |0 | 30 1 | 4 | 6 |0 |0 | 40 1 | 5 | 0 |0 |0 | 50 1 | 6 | 6 |0 |0 | 60 (6 rows) View of Base Table. ace_db=# select * from public.rule_example; folder_id | rule_head | rule_seq | rule_step | step_id | opta | optb ---+---+--+---+-+--+-- 1 |10 |1 | 1 | 10 |0 |0 1 |10 |1 | 2 | 20 |0 |0 1 |30 |2 | 1 | 30 |0 |0 1 |30 |2 | 2 | 40 |0 |0 1 |50 |3 | 1 | 50 |0 |0 1 |50 |3 | 2 | 60 |0 |0 (6 rows) My trigger works appropriately if the scope is a step. ace_db=# delete from public.rule_example where step_id=20; DELETE 1 ace_db=# delete from public.rule_example where step_id=10; DELETE 1 Thanks for any insights that might make this work when the scope of the WHERE is rule or folder targeted. Dave Day --- Here is code that represent above.. . DROP TABLE IF EXISTS public.my_translator CASCADE; CREATE TABLE public.my_translator ( folder_id int not null, folder_seq int not null, -- 1-n, each rule in folder has unique value. entry_type int default 0,-- 0 rule start, 6 rule append optA int default 0, optB int default 0, step_id int primary key ); --Add sample date to my_translator table. INSERT INTO public.my_translator (folder_id, folder_seq, entry_type, step_id ) VALUES -- insert three 2 step rules into folder 1 ( 1,1,0,10), -- folder one, folder seq 1, rule 1 step 1 ( 1,2,6,20), -- folder one, folder seq 2, rule 1 step 2 ( 1,3,0,30), -- folder one, folder seq 3, rule 2 step 1 ( 1,4,6,40), -- folder one, folder seq 4, rule 2 step 2 ( 1,5,0,50), -- folder one, folder seq 5, rule 3 step 1 ( 1,6,6,60); -- folder one, folder seq 6, rule 3 step 2 -- A function that determines a rules order within a folder. CREATE OR REPLACE FUNCTION public.get_rule_seq( _fid int, _fseq int ) RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE _rule_seq int := 1; BEGIN -- given a folder and step within that folder determine what -- the conceptual rule sequence is for the step. WITH rules AS ( SELECT folder_seq, row_number() over ( ORDER by folder_seq ASC) FROM public.my_translator tx WHERE tx.folder_id = _fid AND entry_type = 0 ) SELECT MAX (row_number) FROM rules WHERE folder_seq <= _fseq INTO _rule
Re: Views and triggers more then one row returned by subquery.
Tom, You are exactly right about STABLE needed on the get_rule_seq function. That resolved my issues. I've been burned before by using a function within a view/WHERE situation and the function was not marked STABLE. I need to start taking PREMAGEN. I Kind of came to the same conclusion in parrallel after I ran the explain analyze on the select and wondered why the hell it was going about it like that. Yes keeping the steps of a rule properly associated and ordered with a folder is mind bending. Sorry you had to see it. Thanks so much for analysis and comments.. Regards Dave From: Tom Lane Sent: Tuesday, January 12, 2021 6:24 PM To: Day, David Cc: pgsql-general@lists.postgresql.org Subject: Re: Views and triggers more then one row returned by subquery. "Day, David" writes: > My presumption of views and instead of trigger behavior is that the VIEW > first gets populated with the WHERE filter and then the "DELETE or UPDATE" > operation will fire against each of the rendered view rows. ( ? ) > If this is true then I can't explain the more then one row returned error. This code makes my head hurt :-( However, it's fairly easy to tell that the trigger successfully completes on the first view row (you can check that by sticking some RAISE NOTICE commands in it) and then the error is thrown while evaluating the next view row. The error has to be complaining about the "WITH rule_heads ..." subquery in the view's targetlist; the only other subquery is the MAX() subquery, which most certainly isn't going to return more than one row. The trigger is evidently running rule_delete_and_decrement(), which I am not interested in deconstructing in full, but I can see that it modifies the contents of the my_translator table. So what must be happening is that the "WITH rule_heads ..." subquery is returning more than one row after that modification occurs. I have a rough theory as to why, though I'm not planning on tracing it down in detail. The result of the WITH clause itself *does not see the deletion*, as specified somewhere in our fine manual. (That part is consistent with your expectation that the view output doesn't change while this is all going on: my_translator is being scanned using the original query snapshot, so the subquery doesn't see the already-applied changes.) So when we re-execute the subquery at the second view row, the "WITH rule_heads" output is the same as before. On the other hand, the get_rule_seq() function is going to see the updated contents of my_translator, since it's declared VOLATILE. I think that this inconsistency results in more than one row getting let through the WHERE filter, and voila we get the error. You might be able to fix this by marking get_rule_seq() as STABLE so that it sees the same snapshot as the calling query. At least, when I change it to stable I don't see the error anymore. Whether things are then consistent with your intent, I can't say. But I will say that this code is an unmaintainable pile of spaghetti, because when the side-effects occur and where they're visible is going to be almost impossible to keep track of. regards, tom lane
plpgsql and intarray extension; int[] - int[] operator does not exist ?
I have installed the intarray extension installed in the public schema and am
attempting to use this in a plpgsql trigger function from another schema.
When the triggers executes this I get an exception to the effect
{
"hint": "No operator matches the given name and argument type(s). You might
need to add explicit type casts.",
"details": null,
"code": "42883",
"message": "operator does not exist: integer[] - integer[]"
}
However, If I write a similar test function and attempt similar array
arithmetic successfully from a different schema in a non-trigger function It
recognizes the intarray methods.
CREATE OR REPLACE FUNCTION admin.djd_test()
RETURNS integer[] AS
$BODY$
DECLARE
_old_tag_ids INTEGER[];
_new_tag_ids INTEGER[];
BEGIN
_old_tag_ids := ARRAY[1,2,3];
_new_tag_ids := ARRAY[3,4,5];
RETURN _old_tag_ids - _new_tag_ids;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Of course in the trigger function the declared int[] arrays the content is
dynamically initialized.
Any suggestions as to why the int[] operations are not understood in the
trigger context.?
.
Thanks
Dave Day
RE: plpgsql and intarray extension; int[] - int[] operator does not exist ?
Tom I was thinking something similar after finding that my test function recreated in the problematic schema would execute correctly As one user-role but not another and that they had different search_path settings. After adding public to search patch for that role all was good. The error message "no operator matches the given name and argument type." does not make me easily come around to a search path issue. In any event thanks much for the assistance. Issue resolved. Dave -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, November 19, 2018 12:56 PM To: Day, David Cc: pgsql-gene...@postgresql.org Subject: Re: plpgsql and intarray extension; int[] - int[] operator does not exist ? "Day, David" writes: > Any suggestions as to why the int[] operations are not understood in the > trigger context.? The search_path in the trigger probably doesn't include public. You could add a "SET search_path = whatever" clause to the trigger function definition to ensure it runs with a predictable path. regards, tom lane
RE: Permission to refresh materialized view
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, March 13, 2019 10:24 AM To: Johann Spies ; pgsql-gene...@postgresql.org Subject: Re: Permission to refresh materialized view On 3/13/19 6:27 AM, Johann Spies wrote: > We did run this query: > > /GRANT ALL ON ALL TABLES IN SCHEMA X TO USER Y;/ / / But user Y gets > the message that he has to be the owner of a materialized view to be > able to refresh it. What is the exact message? > > Is that intended behaviour? Is there a way to enable the user to > refresh materialized views in that schema? What is the definition of the view? > > Regards > Johann > // > > -- > Because experiencing your loyal love is better than life itself, my > lips will praise you. (Psalm 63:3) -- Adrian Klaver adrian.kla...@aklaver.com -- Lacking the actual error message however: Y might lack usage on the containing schema ? Y might lack privilege on a function or sequence contained in the view ? Dave Day
pglogical extension. - 2 node master <-> master logical replication ?
Anyone with a test scripts or link to an example of bi-directional master using
pglogical. ?
I am led to believe from the documentation that this should be possible. (?)
"Limited multi-master support with conflict resolution exists, but mutual
replication connections must be added individually"
Which is left unexplained.
.
Environment: FreeBSD11.3, Postgres 11.2, pglogical 2.2.1
Pglogical installed on both hosts.
pg_hba.conf and postgresql.conf options adjusted per recommendations.
Using pglogical I have set up a one way replication between hosts for a table
successfully, but cannot succeed in the admin of the reverse subscription.
On attempting the reverse subscription from host1 to host2
select pglogical.create_subscription('alabama_sub',
'host=alabama port=5432 dbname=ace_db user=replicator',
'{connections}', false, false, '{}' )
could not connect to the postgresql server: FATAL: role "pgsql" does not exist
DETAIL: dsn was: host=georgia port=5432 dbname=ace_db
--
Wrong dsn and role ?
The postgres installed superuser role is not pgsql.
I did not see this issue in the working subscription direction.
Anyone with a test scripts or link to an example of bi-directional master using
pglogical or a suggestion as
to where I went wrong on the setup.
Best Regards
Dave
attempting to retrieve column names from information schema fails.
Hi, I'm looking on some insights on the following problem on retrieving table column names from the information schema ( postgres 9.6.6 FreeBSD 11.1 ) When my client starts up up. Each thread ( 10 ) run this command. select column_name from information_schema.columns where table_name = 'pep_port_log'; I have encountered two instances of testing Where the server does not respond to that command. Otherwise I can interact with the database. I end up restoring the database to recover. At the point of failure, Using pgamdinIII , I find the information schema is visible. In general any catalog "column" related table fails to respond with data. ( I end up cancelling the query, as it just appears to hang. Normal response would be a second or two ) The tables that it should be representing i.e. pep_port_log is present and viewable in it's schema. A sample of other non-column related objects return data in the information schema. Shutting down the database/postgres and restarting does not clear the issue. I do not recall seeing any locks that would help explain the issue. Nothing yet has caught my eye in the postgres log related to this circumstance. I'll add that this all works 99.99% of the time. I have only experienced this twice, while exploring load testing of the total system. The client side was started, running and has coredumped. The client side has a pool of connections (10) that as a matter of routine Drop and re-establish a connection on a round robin basis. One of the threads may have been running the above command at the time the client coredumps in an unrelated matter. It is on the subsequent restart of the client that this command starts to fail and the client fails to reach a runnable condition as it blocks on this query. I have no clue the point at which the information_schema. column relations got out of wack. Any suggestions as to how these column related relations might become corrupted or Debug/inspection measure that I should attempt on the next rare occurrence? Best Regards Dave Day
RE: attempting to retrieve column names from information schema fails.
Thank you Tom. I'll log that suggestion with our internal trouble report for actions to take on it's next occurrence. I should have thought of using your suggested view. It's been some time since I have had any suspicious postgresql behavior to explore and had forgotten about some of the better power tools. Admin skills get rusty. Best Dave -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, December 04, 2017 11:08 AM To: Day, David Cc: pgsql-gene...@postgresql.org Subject: Re: attempting to retrieve column names from information schema fails. "Day, David" writes: > I'm looking on some insights on the following problem on retrieving > table column names from the information schema ( postgres 9.6.6 FreeBSD 11.1 > ) When my client starts up up. Each thread ( 10 ) run this command. > select column_name from information_schema.columns where table_name = > 'pep_port_log'; I have encountered two instances of testing Where the server > does not respond to that command. Hm. Next time it happens, look into pg_stat_activity to see if the backend process is waiting, and if so for what, and what other processes are waiting. (I'm wondering about undetected deadlocks, for instance.) If it's running, maybe you could collect some stack traces to try to narrow down what it's doing. Actually, a stack trace would be useful if it's waiting, too. https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD regards, tom lane
