Re: [SQL] trigger parameters, what am I doing wrong ??
With some version (but I don't remember which) I had the same problem. I solved it by assigning TG_ARGV[0] to a variable and use the variable in the RAISE NOTICE. >>> Tom Lane <[EMAIL PROTECTED]> 2008-10-09 19:22 >>> "Marcin Krawczyk" <[EMAIL PROTECTED]> writes: > And here's what RAISE NOTICE looks like : NOTICE: TG_ARGV = , > TG_NARGS = 0, par = > What's wrong with it ?? I'm running 8.1.4 Works for me: regression=# insert into test_table values(1); INSERT 0 1 regression=# update test_table set f1 = 2; NOTICE: TG_ARGV = 42, TG_NARGS = 1, par = 42 UPDATE 1 You need to show a more complete example of what you're doing. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] replicating a table in several databases
Hi all. Im facing a situation where i have to replicate a table from database A in databases B,C,F and so on. The first (and only) thing i have in mind is using triggers with dblink for comunications with the other DB's. I dont even like the idea of replicating tables across databases, but it is kind of an order :( Since dblink is not transactional, it seems error prone over time, so the tables will be inconsistent sooner or later, right? Do any have some less error-prone idea for this? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 100% CPU at concurent access
I find the problem is in my outer procedure, because it has no sleep there, and I change it calling pg_sleep: -- Function: "TestProcOuter"() -- DROP FUNCTION "TestProcOuter"(); CREATE OR REPLACE FUNCTION "TestProcOuter"() RETURNS integer AS $BODY$ DECLARE Loops int4 := 1; BEGIN LOOP RAISE NOTICE 'TestProcOuter: % loop', Loops; IF 0 = "TestProcInner"() THEN EXIT; -- LOOP END IF; Loops = Loops + 1; PERFORM pg_sleep(4); END LOOP; RETURN 0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION "TestProcOuter"() OWNER TO postgres; With this change, I found the first session succeeds, the CPU is not rised anymore, but the second session doesn't succeed even after the first one finish successfully. It fails forever. Why ? What have I make to succeed ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] replicating a table in several databases
[EMAIL PROTECTED] (Gerardo Herzig) writes: > Hi all. Im facing a situation where i have to replicate a table from > database A in databases B,C,F and so on. > > The first (and only) thing i have in mind is using triggers with dblink > for comunications with the other DB's. > > I dont even like the idea of replicating tables across databases, but it > is kind of an order :( > > Since dblink is not transactional, it seems error prone over time, so > the tables will be inconsistent sooner or later, right? > > Do any have some less error-prone idea for this? Two answers tend to come up: a) Slony-I (which I work on; URL below) b) Londiste (originated by "Skype folks"; see also "Skype Tools") Slony-I has more documentation; Londiste is reputed to be a bit easier to configure (though being somewhat documentation-light may still leave the bar set pretty high). -- let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;; http://slony.info/ "My nostalgia for Icon makes me forget about any of the bad things. I don't have much nostalgia for Perl, so its faults I remember." -- Scott Gilbert comp.lang.python -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with delete trigger: how to allow only triggers to delete a row?
I have a table where some rows are originally entered, and others are logically deduced from original rows by triggers. For instance, if one row contains data indicating that "A < B" and another row indicates "B < C", the triggers will generate a new row indicating that "A < C". All deduced rows have a boolean attribute (named "deduced") that is TRUE only if the row was generated by such a deduction. A value of FALSE indicates that the row was original data, entered by a user. When original data is modified, the triggers are responsible for removing any deduced rows that are now invalid and generating new rows that are now implied. I would like to make it so that the only way that deduced rows can be deleted is through the actions of these triggers; I don't want a user inadvertently deleting a deduction when the underlying premises (the original rows that were used to generate the deduced rows) still imply that deduction is valid. Users should only be able to manipulate the original data. I can create a trigger that will prevent deletion of deduced rows easily enough, but I'm not sure how to let rows targeted for deletion by these deduction triggers through. Is there a way to pass some sort of state indicator into a trigger? Is this at all possible? Thanks in advance, Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
-- Original message -- From: "Christopher Maier" <[EMAIL PROTECTED]> > I have a table where some rows are originally entered, and others are > logically deduced from original rows by triggers. For instance, if > one row contains data indicating that "A < B" and another row > indicates "B < C", the triggers will generate a new row indicating > that "A < C". All deduced rows have a boolean attribute (named > "deduced") that is TRUE only if the row was generated by such a > deduction. A value of FALSE indicates that the row was original data, > entered by a user. > > When original data is modified, the triggers are responsible for > removing any deduced rows that are now invalid and generating new rows > that are now implied. I would like to make it so that the only way > that deduced rows can be deleted is through the actions of these > triggers; I don't want a user inadvertently deleting a deduction when > the underlying premises (the original rows that were used to generate > the deduced rows) still imply that deduction is valid. Users should > only be able to manipulate the original data. > > I can create a trigger that will prevent deletion of deduced rows > easily enough, but I'm not sure how to let rows targeted for deletion > by these deduction triggers through. Is there a way to pass some sort > of state indicator into a trigger? Is this at all possible? > > Thanks in advance, > Chris > > > -- >From the manual >http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html: Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value Could you have the the trigger examine the row to see if it meets the criteria for deletion. If it does RETURN a NON NULL value so the trigger completes, otherwise RETURN NULL to prevent the DELETE. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Oct 10, 2008, at 1:23 PM, Adrian Klaver wrote: Could you have the the trigger examine the row to see if it meets the criteria for deletion. If it does RETURN a NON NULL value so the trigger completes, otherwise RETURN NULL to prevent the DELETE. Thanks for your reply, Adrian. This is indeed part of the solution. My problem concerns the determination of the criteria for deletion. Each row has a boolean attribute that says whether it is deduced or not and that can be inspected readily enough. However, I need to restrict deletion based on, for lack of a better term, "where" the DELETE command comes from. I do not want someone sitting at a psql console to be able to type: DELETE FROM my_table WHERE deduced IS TRUE; This should fail because users should only be able to delete non- deduced rows. However, when a user deletes a non-deduced row, my triggers issue DELETE commands for all deduced rows that are logically derived from that non-deduced row. Back to the example from my original post, if I have two rows in the table that say, in effect: A < B B < C then the triggers will generate a row that says "A < C". If the user then deletes the "B < C" row, the triggers will delete the "A < C" row, because there is no longer any support for this. I need to figure out how to block DELETEs from the user, while allowing DELETEs that come from the triggers. If I could pass along some kind of flag or parameter with the DELETE commands issued from the triggers, then that might do it, but my understanding of trigger parameters is that they are the same for all invocations on a particular table, whereas I need them to be different for each call of the trigger. I'm currently using plpgsql, but I'm open to other PL languages or architectural restructurings if that's necessary. I'm just stumped as to how to achieve this effect. I hope that clarifies things. Thanks again, Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
Christopher Maier wrote: > However, I need to restrict deletion based on, for lack of a better > term, "where" the DELETE command comes from. I do not want someone > sitting at a psql console to be able to type: > > DELETE FROM my_table WHERE deduced IS TRUE; Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote: Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. Thanks, Alvaro. So you're suggesting I create a function like this: CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN ... -- do various checks ... DELETE FROM my_table WHERE id = identifier; ... END; $$; Correct? That sounds like it would work. If at all possible, I'd like to keep the "interface" the same for all my tables, though (i.e., users don't have to be concerned with whether they can do regular SQL deletes, or if they have to call a special function). I suppose that can ultimately be hidden, though. I will try this approach and see how it works out. If there is any other way to achieve this goal, however, I would be interested to hear. Thanks again. --Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
Christopher Maier wrote: > Correct? That sounds like it would work. If at all possible, I'd like > to keep the "interface" the same for all my tables, though (i.e., users > don't have to be concerned with whether they can do regular SQL deletes, > or if they have to call a special function). Hmm, maybe you can turn a regular DELETE into a function call by using an INSTEAD rule, but I'm not sure. That way they would just do a plain DELETE and the sec-def function would be called instead. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SELECT multiple MAX(id)s ?
Hello list, table diary_entry entry_id SERIAL PK d_entry_date_time timestamp without time zone d_entry_company_id integer d_entry_location_id integer d_entry_shift_id integer d_user_id integer d_entry_header text ... Get the last entries from companies and their locations? The last, i.e. the biggest entry_id holds also the latest date value within one company and its locations. One can not add an entry before the previuos one is 'closed'. Names for the companies, their different locations, or outlets if you like, users and shifts are stored in company, location, user and shift tables respectively. Again something I could do with a bunch of JOIN queries and loops + more LEFT JOIN queries within the output loops, but could this be done in a one single clever (sub select?) query? Output (php) should be something like: Date | User | Shift | Company | Location - 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... Someone please give me a start kick? TIA and have a nice weekend too! -- Aarni Burglars usually come in through your windows. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Looks like you should revoke DELETE privilege from plain users, and > have your delete trigger be a security definer function. There would be > another security definer function to delete non-deduced rows which users > can call directly. That seems overly complicated to use. If the triggers that are privileged to delete deduced rows run as a special user, couldn't the validation triggers look at CURRENT_USER to see whether to allow the delete of a deduced row or not? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Friday 10 October 2008 11:25:05 am Christopher Maier wrote: > On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote: > > Looks like you should revoke DELETE privilege from plain users, and > > have your delete trigger be a security definer function. There > > would be > > another security definer function to delete non-deduced rows which > > users > > can call directly. > > Thanks, Alvaro. So you're suggesting I create a function like this: > > CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID > LANGUAGE plpgsql SECURITY DEFINER AS $$ > BEGIN > ... > -- do various checks > ... > DELETE FROM my_table WHERE id = identifier; > ... > END; > $$; > > Correct? That sounds like it would work. If at all possible, I'd > like to keep the "interface" the same for all my tables, though (i.e., > users don't have to be concerned with whether they can do regular SQL > deletes, or if they have to call a special function). I suppose that > can ultimately be hidden, though. > > I will try this approach and see how it works out. If there is any > other way to achieve this goal, however, I would be interested to hear. > > Thanks again. > > --Chris A possible approach, not fully tested. REVOKE DELETE from normal users as suggested above. GRANT DELETE to privileged_user Semi psuedo-code below. CREATE OR REPLACE FUNCTION check_delete RETURNS TRIGGER AS $Body$ BEGIN IF current_user != 'privileged_user' AND old.deduced = 'f' THEN SET LOCAL ROLE 'privileged_user'; --Do your sanity checks and create DELETE statements RETURN OLD; ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN RETURN NULL; ELSIF current_user = 'priviliged_user' THEN RETURN OLD END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Oct 10, 2008, at 4:53 PM, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. That seems overly complicated to use. If the triggers that are privileged to delete deduced rows run as a special user, couldn't the validation triggers look at CURRENT_USER to see whether to allow the delete of a deduced row or not? regards, tom lane That sounds like the best approach, Tom. I've already implemented Alvaro's suggestion, which works nicely. It should be a simple matter to add in the current_user check. I'll give that a whirl and see how it goes. Thanks for all the great suggestions, everyone. Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Friday 10 October 2008 1:57:28 pm Adrian Klaver wrote: > On Friday 10 October 2008 11:25:05 am Christopher Maier wrote: > > On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote: > > > Looks like you should revoke DELETE privilege from plain users, and > > > have your delete trigger be a security definer function. There > > > would be > > > another security definer function to delete non-deduced rows which > > > users > > > can call directly. > > > > Thanks, Alvaro. So you're suggesting I create a function like this: > > > > CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID > > LANGUAGE plpgsql SECURITY DEFINER AS $$ > > BEGIN > > ... > > -- do various checks > > ... > > DELETE FROM my_table WHERE id = identifier; > > ... > > END; > > $$; > > > > Correct? That sounds like it would work. If at all possible, I'd > > like to keep the "interface" the same for all my tables, though (i.e., > > users don't have to be concerned with whether they can do regular SQL > > deletes, or if they have to call a special function). I suppose that > > can ultimately be hidden, though. > > > > I will try this approach and see how it works out. If there is any > > other way to achieve this goal, however, I would be interested to hear. > > > > Thanks again. > > > > --Chris > > A possible approach, not fully tested. > REVOKE DELETE from normal users as suggested above. > GRANT DELETE to privileged_user Oops the above is wrong. In testing I used a login role that automatically inherited the privileged role I was using below. Using a different login role showed me the error. > > Semi psuedo-code below. > > CREATE OR REPLACE FUNCTION check_delete RETURNS TRIGGER AS > $Body$ > BEGIN > IF current_user != 'privileged_user' AND old.deduced = 'f' THEN > SET LOCAL ROLE 'privileged_user'; > --Do your sanity checks and create DELETE statements > RETURN OLD; > ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN > RETURN NULL; > ELSIF current_user = 'priviliged_user' THEN > RETURN OLD > > > END; > > $Body$ > LANGUAGE plpgsql; The above would still work as long as the privileged role(user) was not assigned to normal users and the privileged role had DELETE rights to the table. Also the function would need to be created with the privileges necessary to do the SET ROLE. > -- > Adrian Klaver > [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
