Re: [GENERAL] what are rules for?
Tom Lane wrote: > To expand on that: it's pretty hard to see how update or delete triggers > on a view would work. Insert is easy, because if left to its own > devices the system would in fact try to insert a tuple into the view > relation, and that action could fire a trigger which could redirect the > insertion someplace else. But updates and deletes require a > pre-existing target tuple, and there just aren't any of those in a view > relation. (Another way to say it is that update/delete require a CTID > column, which a view hasn't got.) > > So view update/delete appear to require a transformational-rule kind > of approach instead of an actions-on-physical-tuples kind of approach. > > If you've got a better idea we're all ears ... Would it be any easier to implement Oracle-style "instead of" triggers for views, instead of before and after triggers? Notionally this seems like a "do instead select trigger_fn()" rule, with the trigger function having complete responsibility for updating the underlying table(s). The difficultly I can see is what data to pass to the trigger function, since just passing the old and new values from the view may not be enough to work out which rows to update. But then, this is no worse than what Oracle currently does, and for many data models it is very useful. I've used rules to implement updateable views, and I would certainly have found triggers much easier to work with. In particular, certain things didn't seem to be possible at all with rules, such as "before insert" and "after delete" actions, because the "where" clause doesn't match anything at those points. With an "instead of" trigger you can obviously do whatever you want, in any order. Dean. _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- 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] what are rules for?
Tom Lane wrote: > Well, both the trigger call API and the underlying implementation deal > in CTIDs, so just airily saying "we don't need 'em" doesn't obviously > work. (Note I did not say "obviously doesn't work". Whether this is > feasible depends on much closer analysis than any of the hand-waving > that we've done so far.) > To my mind there are two really fundamental issues underlying this. > One, which is what CTID fixes, is that a view doesn't have any primary > key by which to identify which row you're talking about. (Even if > there is a candidate key implicit in the view semantics, we don't > have any way for the system to know what it is.) The Oracle "instead of" trigger ducks this issue completely. The trigger is called once per row in the view that matches the top-level "where" clause, and it is entirely up to the author of the trigger function to work out what to update (if anything). In fact the trigger is free to update an entirely different set of rows if it wants to! An obvious problem with this is that if the view has no unique key, the trigger may end up doing the same work several times over. Say I do "update my_view set a=10 where b=5", and 20 rows match "b=5". Then the trigger function will get called 20 times, and it will probably just do the same thing each time. I'm not aware of a good solution to this, other than "don't write views like that". > The other nasty little > issue is that if the view involves any non-immutable functions, it's > not necessarily the case that you can recompute the OLD row at all. Surely the results of updating a view containing non-immutable functions are going to be pretty unpredictable anyway. > Also, if the view involves expensive functions, you'd probably rather > the system *didn't* recompute them unless absolutely needed, even if > they're immutable. A transform-based approach can succeed at that, but > a trigger-based approach really can't since it needs to see materialized > OLD and NEW rows. Yes that's true. You could perhaps mitigate against this sort of performance problem by providing some mechanism for the trigger definer to select which columns to pass to trigger function. Even if the view doesn't contain expensive functions, I would expect a trigger to perform worse than a query-rewrite in cases such as a single update statement which affects multiple rows. So triggers might not be suitable for such cases, but there would also be many other cases where the performance would be similar, and then the ease-of-use and greater flexibility of triggers compared to rules would make them preferable (IMO). Dean. _ http://clk.atdmt.com/UKM/go/msnnkmgl001002ukm/direct/01/ -- 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] what are rules for?
> On Thu, Jun 26, 2008 at 5:08 AM, Dean Rasheed wrote: >> The Oracle "instead of" trigger ducks this issue completely. The >> trigger is called once per row in the view that matches the top-level >> "where" clause, and it is entirely up to the author of the trigger >> function to work out what to update (if anything). > > That sounds like exactly the sort of thing I was envisioning. > Although from what Tom said, it sounds as though "instead of" triggers > in PostgreSQL would have to be implemented in a significantly > different way from other triggers. > > How does an Oracle "instead of" trigger decide how many rows to tell > the caller were updated? Can this "return value" be modified > programmatically by the trigger? > > Mike AFAIK Oracle's "instead of" trigger has no mechanism for returning the actual number of rows updated (the trigger itself has no return value). So I guess that they are just assuming that it matches the number of times the trigger was executed, and returning that to the caller. This can almost be implemented in PostgreSQL right now, using a rule of the form "... do instead select trigger_fn()" - except, as you point out, the caller won't know how many rows were actually updated. As far as the top-level query knows, it didn't update anything, which will break some (most?) clients. Apart from that, this does actually work! Dean _ http://clk.atdmt.com/UKM/go/msnnkmgl001002ukm/direct/01/ -- 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] what are rules for?
> Date: Thu, 26 Jun 2008 12:47:04 -0500 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: Re: what are rules for? > CC: pgsql-general@postgresql.org; [EMAIL PROTECTED]; [EMAIL PROTECTED]; > [EMAIL PROTECTED] > > On Thu, Jun 26, 2008 at 12:11 PM, Dean Rasheed wrote: >> This can almost be implemented in PostgreSQL right now, using a rule of >> the form "... do instead select trigger_fn()" - except, as you point out, the >> caller won't know how many rows were actually updated. As far as the >> top-level query knows, it didn't update anything, which will break some >> (most?) clients. Apart from that, this does actually work! > > Yeah, I actually thought of that. But as you point out, many clients > would get confused. Someone pointed out in an earlier thread that a > way to fix this, for updates on a multi-table view (where most of the > complication lies), is to write a "trigger" function that updates all > the constituent tables except for one, and then write a rule that > calls that function and then updates the one remaining table itself. > This seems to work okay although I have not tested it with many > clients. > > Mike Yes that would seem to work. For UPDATE anyway. Although if it were purely DML that you were doing, you would probably be better off just having multiple UPDATE statements in the rule body. Then they would stand a better chance of being rewritten and executed more efficiently. The problem is that the rule system has a lot of subtle pitfalls waiting to trip you up. Suppose for example that your view did an inner join on the PK of 2 tables, and you tried to use that trick to implement a DELETE "trigger" to delete from both. After the first deletion, no rows in the view would match and the second delete wouldn't happen. OK, so there's an easy fix to this, but it is easy to overlook. In my case, I wanted to invoke a function after the delete, which did some complex logic relying on the tables being in their final state. So I really needed an "after delete" trigger, and this didn't seem possible with the rule system. As the documentation points out, there are some things that can't be done with rules (and also with triggers). Each has its own pros and cons in different situations. So I for one would love to see both available for views. I've used Oracle's "instead of" triggers, and they work really well*, but maybe there is some ever better way of implementing triggers on views. Dean. * Better in fact than their before and after triggers on tables, which in Oracle are much more prone to mutating table errors. _ Welcome to the next generation of Windows Live http://www.windowslive.co.uk/get-live -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] No error when column doesn't exist
I've just spent a couple of hours tracking down a bug which turned out to be a typo in my code. What surprises me is that the SQL in question didn't immediately produce an error. Here's a simplified example: CREATE TABLE foo(a int, b int); INSERT INTO foo VALUES(1,2); SELECT foo.text FROM foo; I expected that to generate an error: column foo.text does not exist. Instead it treats "foo.text" as "foo.*::text AS text": SELECT foo.text FROM foo; text --- (1,2) (1 row) If foo actually does have a column called text, this works as expected, selecting just that column. Is this a feature or a bug? Dean _ Make a mini you and download it into Windows Live Messenger http://clk.atdmt.com/UKM/go/111354029/direct/01/ -- 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] No error when column doesn't exist
> Hmm. It's a feature, but maybe a dangerous one. The expression is > being treated as text(foo), which is intentional in order to allow > use of functions as if they were virtual columns. However, then it > decides that what you've got there is a cast request. There wasn't > any ability to cast composite types to text before 8.3, so this fails > in the expected way in 8.2 and before; but in 8.3 the cast > interpretation succeeds, and away we go. > Thanks for the explanation. I see what's going on now. > foo.char and foo.varchar have similarly unexpected behavior; I think > that's probably the end of it, though, since those are the only types > that CoerceViaIO will take as targets. > ... and also any user defined domains based on those, which is what I actually had. I was unlucky enough that the row text matched the regexp on my domain, so my typo went unnoticed for a while ;-( > Maybe we could/should restrict things so that the syntax continues to > fail, but I can't think of any restrictions that don't seem like warts. > What's worse, they might break stuff that used to work. > > regards, tom lane OK, I can live with that. At least I know what to look out for now! Cheers, Dean _ Win New York holidays with Kellogg’s & Live Search http://clk.atdmt.com/UKM/go/111354033/direct/01/ -- 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] set-level update fails with unique constraint violation
2010/1/4 Daniel Verite : > David Fetter wrote: > >> The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem > > That fix has a drawback: when the unique constraint is violated, the rest of > the transaction runs with data that is somehow corrupted, with duplicate > values being visible. It may be uneasy to predict if and how the statements > following the temporary-ignored constraint violation will misbehave. > Generally, the transaction will ultimately fail and the mess will be cleaned > up by the rollback, but in the worst case it may not even fail, for instance > if the offending rows get deleted before the end. > No, deferrable constraints are more flexible than that, so you can have end-of-statement checks if that's what you want. A deferrable constraint has 2 modes of operation, depending on how you choose to define the constraint: 1). DEFERRABLE INITIALLY IMMEDIATE will result in the constraint being checked after each statement in the transaction. This will allow the i=i+1 UPDATE to succeed, but any UPDATE which causes uniqueness to be violated at the end of the statement will fail immediately, and you will have to rollback. 2). DEFERRABLE INITIALLY DEFERRED will cause the constraint check to be done at the end of the transaction (or when SET CONSTRAINTS is called). This will allow the constraint to be temporarily violated by statements inside a transaction, and if the duplicates are then deleted, the transaction will succeed. If you just specify DEFERRABLE, then INITIALLY IMMEDIATE is the default. See http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html This is all per the SQL spec, and also the same behaviour as Oracle. So there is quite a bit of flexibility - you may choose to have the constraint checked at any of these times: - after each row (the default for NON DEFERRABLE constraints) - after each statement (DEFERRABLE [INITIALLY IMMEDIATE]) - at the end of the transaction (DEFERRABLE INITIALLY DEFERRED) - whenever you want in a transaction using SET CONSTRAINTS Regards, Dean -- 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] set-level update fails with unique constraint violation
2010/1/5 Roman Neuhauser : > # jayadevan.maym...@ibsplc.com / 2010-01-04 10:03:29 +0530: >> This seems to work.. >> UPDATE x set i=i+1 >> from (select i as m from x order by m desc) y where x.i = y.m >> Jayadevan > > Thanks, that nicely achieves the illusion of atomic immediate checking. > > -- > Roman Neuhauser That is not guaranteed to work. Depending on how the optimiser does the join, the reverse ordering may not be preserved in the update. Try it for larger tables (for me it fails at 10 rows). Regards, Dean -- 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] set-level update fails with unique constraint violation
2010/1/6 Daniel Verite : > Dean Rasheed wrote: > >> So there is quite a bit of flexibility - you may choose to have the >> constraint checked at any of these times: >> - after each row (the default for NON DEFERRABLE constraints) >> - after each statement (DEFERRABLE [INITIALLY IMMEDIATE]) >> - at the end of the transaction (DEFERRABLE INITIALLY DEFERRED) >> - whenever you want in a transaction using SET CONSTRAINTS > > Thanks for clarifying that. I've just tried the different scenarios with > 8.5alpha3, and I find that these improvements are quite useful and welcome. > But still I wonder why there is that difference in behavior between NON > DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint > doesn't get deferred by using SET CONSTRAINTS. > In the first case, we get the "after each row" behavior with the pk=pk+1 > failure, as with the previous PG versions. > In the second case, we get the "after each statement" behavior which I > believe complies with the standard, contrary to the first case, and > successfully achieves the pk=pk+1 update as expected. > Personally, I would have imagined that behavior #1 would be removed once > behavior #2 was implemented, not that the two would co-exist. Is there a > reason to keep #1? > Performance is one reason (perhaps the only one?). #1 is implemented using a unique index, which is checked as each row is inserted. #2 uses triggers in addition to the unique index (conflicts are queued up to be re-checked at the end of the command/transaction). So #1 will always out-perform #2 (unless there aren't any temporary conflicts to be re-checked). > Also, I read in the current doc for 8.5: > http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command > > > "after every command" seems to describe behavior #2, not #1. > Hmm. Yes that comment is misleading in this context. Non-deferrable unique constraints are currently checked after each row. Regards, Dean -- 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] Index on immutable function call
2010/1/19 Philippe Lang : > That works just fine, but is there maybe a way of creating a slighly > more "generic" index? If I change the ">" with a "<" in the query, index > cannot of course be used. According to documentation, answer seems to be > "no"... > You could create an index on the difference: create index long_transformation_index on indexed_table ((data1-this_is_a_long_transformation(data2))); then rewrite your queries accordingly: ... WHERE data1-this_is_a_long_transformation(data2) > some const (or < some const) HTH, Dean -- 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] Help on constructing a query that matches array
2010/1/19 BlackMage :
>
> Hey all,
>
> I need help on constructing a query with arrays that matches the arrays up
> as foriegn keys to another table. For example, say that I have two tables,
> owners and pets.
>
> Owner Table
> owner_id | pet_ids
> 1 | {1,2,3}
> 2 | {2,3}
>
> Pet Table
> pet_ids | Pet Type
> 1 | Dog
> 2 | Cat
> 3 | Fish
> 4 | Bird
>
> Basically I am trying to create a SELECT query that returns the type of pets
> an owner has by matching the pet_ids up. Can anyone help me with this?
You can use the built-in unnest() array function (see
http://www.postgresql.org/docs/8.4/static/functions-array.html) to
convert the array to a set of rows which you can then join in the
standard way. For example:
select o.owner_id, o.pet_id, p.pet_type from
(select owner_id, unnest(pet_ids) as pet_id from owner) as o, pet as p
where p.pet_id = o.pet_id and owner_id=1;
Note: the unnest() function is only defined as standard in postgresql
8.4. If you have an older version, you'll need to define it yourself,
as described here:
http://wiki.postgresql.org/wiki/Array_Unnest
Regards,
Dean
--
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] Help me with this multi-table query
On 23 March 2010 11:07, Nilesh Govindarajan wrote: > Hi, > > I want to find out the userid, nodecount and comment count of the userid. > > I'm going wrong somewhere. > > Check my SQL Code- > > select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node > n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by > u.uid having u.uid <> 0 order by u.uid; > I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean -- 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] Help me with this multi-table query
On 24 March 2010 05:17, Nilesh Govindarajan wrote: > On 03/24/2010 12:45 AM, Dean Rasheed wrote: >> >> On 23 March 2010 11:07, Nilesh Govindarajan wrote: >>> >>> Hi, >>> >>> I want to find out the userid, nodecount and comment count of the userid. >>> >>> I'm going wrong somewhere. >>> >>> Check my SQL Code- >>> >>> select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join >>> node >>> n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by >>> u.uid having u.uid<> 0 order by u.uid; >>> >> >> I think you want select u.uid, count(distinct n.nid) nc , >> count(distinct c.cid) cc from ... >> otherwise you're counting each node/comment multiple times as the rows in >> the >> join multiply up (note 85 x 174 = 14790). >> >> For big tables, this could start to become inefficient, and you might >> be better off >> doing your queries 1 and 2 above as sub-queries and joining them in an >> outer query. >> >> Regards, >> Dean > > Thanks a lot !! It worked. > How to do it using subqueries ? > Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean -- 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] Any feedback on this query?
On 18 February 2011 07:19, Mike Christensen wrote: > Here's my query: > > SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating, > R.PrepTime, R.CookTime, R.OwnerId, U.Alias > FROM Recipes R > INNER JOIN Users U ON U.UserId = R.OwnerId > WHERE (R.PrepTime <= :maxprep) > ORDER BY R.Rating DESC LIMIT 100; > SELECT COUNT(*) FROM Recipes R > WHERE (R.PrepTime <= :maxprep); > > The idea is I can show the top 100 matches, and then in the UI say: > > "Displaying top 100 results out of 150 recipes." > > I'm guessing doing two queries (one to get the top 100 rows and the > other to get the total DB count) is faster than getting all the rows > and trimming the data in code (there could be tens of thousands). > What I'm guessing is since Postgres just ran the query, the second > query will be near instant since any relevant data is still in memory. > > BTW, the query can potentially be way more complicated depending on > the user-entered search criteria. > > Feedback on this approach? > The second query by itself isn't guaranteed to return the same count that the first query would without the limit, unless you have FK and NOT NULL constraints on OwnerId. If you're on 8.4 or later, you could use a window function to return the count in the first query. I'm not sure that there will be much difference in performance, but it will be less prone to errors having only one WHERE clause to maintain. So something like: SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating, R.PrepTime, R.CookTime, R.OwnerId, U.Alias, count(*) OVER () FROM Recipes R INNER JOIN Users U ON U.UserId = R.OwnerId WHERE (R.PrepTime <= :maxprep) ORDER BY R.Rating DESC LIMIT 100; Regards, Dean -- 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] Returning from insert on view
On 1 June 2011 10:32, Aleksey Chirkin wrote: > Hello! > > I need your advice. > My problem is to ensure that the right returning from insert on the view. > > For example, I have two tables: > > CREATE TABLE country (id serial, nm text); > CREATE TABLE city (id serial, country_id integer, nm text); > > And one view on table "city", which join table "county" and adds > country_nm column. > > CREATE VIEW city_view AS > SELECT city.id, city.nm, city.country_id, country.nm AS country_nm > FROM city > JOIN country ON city.country_id = country.id; > > I have two options for ensuring the returning from insert operation on view: > > 1) Create rule: > > CREATE RULE ins AS ON INSERT > TO city_view > DO INSTEAD > INSERT INTO city (nm, country_id) VALUES (NEW.nm, NEW.country_id) > RETURNING id, nm, country_id, > (SELECT nm FROM country WHERE id = city.country_id) AS country_nm; > > 2) Create trigger on view (for PostgreSQL 9.1): > > CREATE FUNCTION city_view_insert() > RETURNS trigger AS > $BODY$ > BEGIN > INSERT INTO city > ( nm, country_id ) > VALUES ( NEW.nm, NEW.country_id ) > RETURNING id INTO NEW.id; > > SELECT * INTO NEW FROM city_view WHERE id = NEW.id; > > RETURN NEW; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER; > > CREATE TRIGGER on_insert INSTEAD OF INSERT ON city_view > FOR EACH ROW > EXECUTE PROCEDURE city_view_insert(); > > It looks like a trick, and slows the insert, but it looks better and > avoids the complexities in returning. > > Perhaps there is another way (may be a trick) to ensure the returning > from the insert on the view, without a manual compilation of the > returning columns? > Selecting from the view at the end of the trigger will be slower, so if performance is a factor it would be better to just select the required columns from the underlying table, but I'm not aware of any trick to avoid listing the columns. The trigger has greater scope for flexibility and validation of the input data - maybe country names are supplied, which the trigger could validate and get the corresponding ids. Maybe both are supplied, and the trigger could check they are consistent, etc... For bulk operations the rule should out-perform the trigger, since it is just a query rewrite (like a macro definition). However, there are a lot more gotchas when it comes to writing rules. So the main advantages of the trigger are that it is less error-prone, and it is easier to write complex logic in a procedural language. Regards, Dean > Regards, > Aleksey > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- 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] Hidden Risk w/ UPDATE Cascade and Trigger-Based Validation
On 3 June 2011 01:26, David Johnston wrote: > Hi, > > > > I am trying to get a better understanding of how the following Foreign Keys > with Update Cascades and validation trigger interact. The basic setup is a > permission table where the two permission parts share a common > “group/parent” which is embedded into their id/PK and which change via the > FK cascade mechanism. Rest of my thoughts and questions follow the setup. > > > > I have the following schema (parts omitted/simplified for brevity since > everything works as expected) > > > > CREATE TABLE userstorepermission ( > > userid text NOT NULL FK UPDATE CASCADE, > > storeid text NOT NULL FK UPDATE CASCADE, > > PRIMARY KEY (userid, storeid) > > ); > > > > FUNCTION validate() RETURNS trigger AS > > SELECT groupid FROM store WHERE storeid = [NEW.storeid] INTO storegroup > > SELECT groupid FROM user WHERE userid = [NEW.userid] INTO usergroup > > > > RAISE NOTICE ‘Validating User Store Permission U:%;%, S:%;%’, NEW.userid, > usergroup, NEW.storeid, storegroup; > > > > IF (usergroup <> storegroup) THEN > > RAISE NOTICE ‘Disallow’; > > RETURN null; > > ELSE > > RAISE NOTICE ‘Allow’; > > RETURN NEW; > > > > END; > > > > CREATE TRIGGER INSERT OR UPDATE EXECUTE validate(); > > > > Basically if I change the groupid both the userid and storeid values in > userstorepermission will change as well. This is desired. When I do update > the shared groupid the following NOTICES are raised from the validation > function above: > > > > The change for groupid was TESTSGB -> TESTSGD: > > > > NOTICE: Validating User Store Permission U:tester@TESTSGB; > S:[TESTSGD]STORE01;TESTSGD [at this point apparently both user and store > have been updated and storeid in the permission table is being change] > > CONTEXT: SQL statement "UPDATE ONLY "domain"."userstorepermission" SET > "s_id" = $1 WHERE $2 OPERATOR(pg_catalog.=) "s_id"" > > > > NOTICE: Allow > > CONTEXT: SQL statement "UPDATE ONLY "domain"."userstorepermission" SET > "s_id" = $1 WHERE $2 OPERATOR(pg_catalog.=) "s_id"" > > > > NOTICE: Validating User Store Permission U:tester@TESTSGD;TESTSGD > S:[TESTSGD]STORE01;TESTSGD [and now the userid in the permission table gets > its turn] > > CONTEXT: SQL statement "UPDATE ONLY "domain"."userstorepermission" SET > "u_id" = $1 WHERE $2 OPERATOR(pg_catalog.=) "u_id"" > > > > NOTICE: Allow > > CONTEXT: SQL statement "UPDATE ONLY "domain"."userstorepermission" SET > "u_id" = $1 WHERE $2 OPERATOR(pg_catalog.=) "u_id"" > > > > The end result is that both values are changed as desired but the notices, > while they indirectly make sense (only one of the values can be update > cascaded at a time), are somewhat confusing and thus I am not sure if I am > possibly missing something that could eventually blow up in my face. I > expect other similar situations will present themselves in my model so I > want to get more understanding on at least whether what I am doing is safe > and ideally whether the CASCADE rules possibly relax intra-process > enforcement of constraints in order to allow this kind of multi-column key > update to succeed. > > > > I see BUG #5505 from January of last year where Tom confirms that the > trigger will fire but never addresses the second point about the referential > integrity check NOT FAILING since the example’s table_2 contains a value not > present in table_1… > > > > Conceptually, as long as I consistently update ALL the relevant FKs the > initial and resulting state should remain consistent but only with a > different value. I’ll probably do some more playing with “missing” a FK > Update Cascade and see whether the proper failures occurs but regardless > some thoughts and/or pointers are welcomed. > Hmm, perhaps it would be better if your validation trigger raised an exception in the "disallow" case, rather than risk silently breaking the FK (even if you get to a point where you think that can't happen). Regards, Dean -- 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] Reusing cached prepared statement slow after 5 executions
On 27 June 2011 07:50, Rob Gansevles wrote: > I can confirm, when I call ps.setPrepareThreshold(1) the query is slow > immediately, so the plan must be different with the server prepared > statements. > You can confirm that from psql by doing EXPLAIN ANALYSE SELECT ... ; and then PREPARE ps( ... ) AS SELECT ... ; EXPLAIN ANALYSE EXECUTE ps ( ... ) ; using your query and the parameters in question. It is entirely possible that the plan chosen for the prepared statement will be worse than the one used when the parameters are known at planning time. The prepared statement doesn't know what parameters are going to be used, so it can't always come up with the best plan. See the notes in the PREPARE manual page: http://www.postgresql.org/docs/9.0/static/sql-prepare.html Regards, Dean -- 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] DELETE taking too much memory
> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: >> Hi, >> >> I have a delete query taking 7.2G of ram (and counting) but I do not >> understant why so much memory is necessary. The server has 12G, and >> I'm afraid it'll go into swap. Using postgres 8.3.14. >> >> I'm purging some old data from table t1, which should cascade-delete >> referencing rows in t2. Here's an anonymized rundown : >> >> # explain delete from t1 where t1id in (select t1id from t2 where >> foo=0 and bar < '20101101'); It looks as though you're hitting one of the known issues with PostgreSQL and FKs. The FK constraint checks and CASCADE actions are implemented using AFTER triggers, which are queued up during the query to be executed at the end. For very large queries, this queue of pending triggers can become very large, using up all available memory. There's a TODO item to try to fix this for a future version of PostgreSQL (maybe I'll have another go at it for 9.2), but at the moment all versions of PostgreSQL suffer from this problem. The simplest work-around for you might be to break your deletes up into smaller chunks, say 100k or 1M rows at a time, eg: delete from t1 where t1id in (select t1id from t2 where foo=0 and bar < '20101101' limit 10); Regards, Dean -- 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] DELETE taking too much memory
On 8 July 2011 10:44, Vincent de Phily wrote: > On Friday 08 July 2011 10:05:47 Dean Rasheed wrote: >> > On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote: >> >> Hi, >> >> >> >> I have a delete query taking 7.2G of ram (and counting) but I do not >> >> understant why so much memory is necessary. The server has 12G, and >> >> I'm afraid it'll go into swap. Using postgres 8.3.14. >> >> >> >> I'm purging some old data from table t1, which should cascade-delete >> >> referencing rows in t2. Here's an anonymized rundown : >> >> >> >> # explain delete from t1 where t1id in (select t1id from t2 where >> >> foo=0 and bar < '20101101'); >> >> It looks as though you're hitting one of the known issues with >> PostgreSQL and FKs. The FK constraint checks and CASCADE actions are >> implemented using AFTER triggers, which are queued up during the query >> to be executed at the end. For very large queries, this queue of >> pending triggers can become very large, using up all available memory. >> >> There's a TODO item to try to fix this for a future version of >> PostgreSQL (maybe I'll have another go at it for 9.2), but at the >> moment all versions of PostgreSQL suffer from this problem. > > That's very interesting, and a more plausible not-optimized-yet item than my > guesses so far, thanks. Drop me a mail if you work on this, and I'll find some > time to test your code. > > I'm wondering though : this sounds like the behaviour of a "deferrable" fkey, > which AFAICS is not the default and not my case ? I haven't explored that area > of constraints yet, so there's certainly some detail that I'm missing. > Yes, it's the same issue that affects deferrable PK and FK constraints, but even non-deferrable FKs use AFTER ROW triggers that suffer from this problem. These triggers don't show up in a "\d" from psql, but they are there (try select * from pg_trigger where tgconstrrelid = 't1'::regclass) and because they fire AFTER rather than BEFORE, queuing up large numbers of them is a problem. Regards, Dean > >> The simplest work-around for you might be to break your deletes up >> into smaller chunks, say 100k or 1M rows at a time, eg: >> >> delete from t1 where t1id in (select t1id from t2 where foo=0 and bar >> < '20101101' limit 10); > > Yes, that's what we ended up doing. We canceled the query after 24h, shortly > before the OOM killer would have, and started doing things in smaller batches. > > > -- > Vincent de Phily > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delete cascade trigger runs security definer
Hi,I'm not sure if the following is a bug. I certainly found itsurprising, but
maybe more experienced users won't.I have a table with a trigger on it,
designed to run securityinvoker. In my real code this accesses a temporary
table belonging tothe invoker.Then I have second table, together with a foreign
key between them anda delete cascade from the second to the first table. It
appears thatwhen I delete from this second table, the delete cascades as
expected,but the trigger is invoked as if it were security definer, which
Ididn't expect.I've attached a short made-up test script. The delete from
'bar'works, and the trigger logs to the temporary table. However, thedelete
from 'foo' fails, unless I grant user1 access to 'temp_log'.By playing with the
trigger, it is possible to confirm that thetrigger really is running with the
permissions of user1, when it isinvoked via the delete cascade, but as user2
otherwise.Is this expected behaviour?Dean.-- Need 2 users\c - postgresDROP
OWNED BY user1;DROP OWNED BY user2;DROP USER user1;DROP USER user2;CREATE USER
user1;CREATE USER user2;-- First user\c - user1CREATE TABLE foo(a int PRIMARY
KEY);CREATE TABLE bar(a int REFERENCES foo ON DELETE CASCADE);CREATE OR REPLACE
FUNCTION bar_log_fn() RETURNS trigger AS$$BEGIN EXECUTE 'INSERT INTO temp_log
VALUES(''Deleting from bar'')'; RETURN OLD;END;$$LANGUAGE plpgsql;CREATE
TRIGGER bar_del_trigger BEFORE DELETE ON bar FOR EACH ROW EXECUTE PROCEDURE
bar_log_fn();GRANT SELECT,INSERT,UPDATE,DELETE ON foo TO user2;GRANT
SELECT,INSERT,UPDATE,DELETE ON bar TO user2;-- Second user\c - user2CREATE
TEMPORARY TABLE temp_log(log text);INSERT INTO foo VALUES(1),(2);INSERT INTO
bar VALUES(1),(2);DELETE FROM bar WHERE a=1;DELETE FROM foo WHERE a=2;SELECT *
FROM temp_log;
_
See the most popular videos on the web
http://clk.atdmt.com/GBL/go/115454061/direct/01/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delete cascade trigger runs security definer
Sorry, Opera removed all the newlines from my last post.
Trying again in Firefox...
Hi,
I'm not sure if the following is a bug. I certainly found it
surprising, but maybe more experienced users won't.
I have a table with a trigger on it, designed to run security
invoker. In my real code this accesses a temporary table belonging to
the invoker.
Then I have second table, together with a foreign key between them and
a delete cascade from the second to the first table. It appears that
when I delete from this second table, the deletes cascade as expected,
but the trigger is invoked as if it were security definer, which I
didn't expect.
I've attached a short made-up test script. The delete from 'bar'
works, and the trigger logs to the temporary table. However, the
delete from 'foo' fails, unless I grant user1 access to 'temp_log'.
By playing with the trigger, it is possible to confirm that the
trigger really is running with the permissions of user1, when it is
invoked via the delete cascade, but as user2 otherwise.
Is this expected behaviour?
Dean.
-- Need 2 users
\c - postgres
DROP OWNED BY user1;
DROP OWNED BY user2;
DROP USER user1;
DROP USER user2;
CREATE USER user1;
CREATE USER user2;
-- First user
\c - user1
CREATE TABLE foo(a int PRIMARY KEY);
CREATE TABLE bar(a int REFERENCES foo ON DELETE CASCADE);
CREATE OR REPLACE FUNCTION bar_log_fn() RETURNS trigger AS
$$
BEGIN
EXECUTE 'INSERT INTO temp_log VALUES(''Deleting from bar'')';
RETURN OLD;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER bar_del_trigger BEFORE DELETE ON bar
FOR EACH ROW EXECUTE PROCEDURE bar_log_fn();
GRANT SELECT,INSERT,UPDATE,DELETE ON foo TO user2;
GRANT SELECT,INSERT,UPDATE,DELETE ON bar TO user2;
-- Second user
\c - user2
CREATE TEMPORARY TABLE temp_log(log text);
INSERT INTO foo VALUES(1),(2);
INSERT INTO bar VALUES(1),(2);
DELETE FROM bar WHERE a=1;
DELETE FROM foo WHERE a=2;
SELECT * FROM temp_log;
_
Win £1000 John Lewis shopping sprees with BigSnapSearch.com
http://clk.atdmt.com/UKM/go/117442309/direct/01/
--
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] Delete cascade trigger runs security definer
> Referential integrity actions execute as the owner of the table, so > anything triggered by them would execute as the owner too. > > regards, tom lane Hmm, that opens up a very nasty gotcha, as shown by the script below. What user1 does looks, at first sight, fairly innocuous. However, it opens him up completely, allowing user2 to do anything in his name. Admittedly, granting ALL on a relation is not good practice, without careful thought. But I wonder how many people do it just to save typing, especially if the tables in question aren't particularly important. I couldn't find anything in the documentation that said that referential integrity actions execute as the owner of the table. So how many people looking at this script would spot the danger? Dean -- Need 2 users \c - postgres DROP OWNED BY user1; DROP OWNED BY user2; DROP USER user1; DROP USER user2; CREATE USER user1; CREATE USER user2; -- First user \c - user1 CREATE TABLE foo(a int PRIMARY KEY); CREATE TABLE bar(a int REFERENCES foo ON DELETE CASCADE); CREATE TABLE fud(a int); GRANT ALL ON foo TO user2; GRANT ALL ON bar TO user2; -- Second user \c - user2 CREATE OR REPLACE FUNCTION bar_log_fn() RETURNS trigger AS $$ BEGIN EXECUTE 'DROP TABLE fud'; EXECUTE 'CREATE TABLE fud2(a int)'; RETURN OLD; END; $$ LANGUAGE plpgsql; CREATE TRIGGER bar_del_trigger BEFORE DELETE ON bar FOR EACH ROW EXECUTE PROCEDURE bar_log_fn(); INSERT INTO foo VALUES(1); INSERT INTO bar VALUES(1); DELETE FROM foo WHERE a=1; _ See the most popular videos on the web http://clk.atdmt.com/GBL/go/115454061/direct/01/ -- 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] Pet Peeves
> rules are very very very very rarely useful. I wouldn't say that. There are many use cases where rules are just the thing. Plus they have an added performance benefit when dealing with multiple rows in a single statement. > yes, in general - I wouldn't mind to see postgresql implement fully > updatable views. > There's being a very long discussion about that on -hackers, and patch > was even in cvs-head for a bit, but got dropped. > probably enabling triggers for views would be the only way to do it, me > thinks. > I don't know how oracle guys got around it. The Oracle solution is quite useful in a large set of cases. The basic idea is this: Since a view is arbitrarily complex, there is no way, in general, that the database can know how to update it. Therefore the concept of BEFORE or AFTER triggers doesn't really make sense (before or after something the database can't do anyway). So instead, the only kind of trigger they allow on a view is an "INSTEAD OF" row-level trigger. The contract of the trigger function is that it will be invoked once for each matching row in the view, and the database will assume that the trigger will do the necessary work to update that row. Thus Oracle assumes that the number of rows updated matches the number of times that it invoked the trigger function. Apart from this last part, this is like defining a rule CREATE RULE my_rule AS ON INSERT/UPDATE/DELETE TO my_view DO INSTEAD SELECT my_fn(old.*, new.*); Of course the problem with using a rule in this way is that the query is rewritten as a SELECT, and the client is told that no rows were updated. This is where the INSTEAD OF trigger comes in handy. Dean. _ Hotmail, Messenger, Photos and more - all with the new Windows Live. Get started! http://www.download.live.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] Pet Peeves
>> - no ability to define triggers on views >> > > maybe because you can't perform insert/delete/update on them ? > Actually I was thinking the value of triggers on views is precisely to allow you to perform insert/delete/update on them. I know you can do this with rules, but there are cases when a trigger is much more convienent to work with. Dean. _ Twice the fun—Share photos while you chat with Windows Live Messenger. Learn more. http://www.microsoft.com/uk/windows/windowslive/products/messenger.aspx -- 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] Pet Peeves
The only one I can see that hasn't already been mentioned - no ability to define triggers on views Dean. _ Windows Live Messenger just got better .Video display pics, contact updates & more. http://www.download.live.com/messenger -- 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] cache lookup failed for function 72629
This sounds like a problem I have observed, which I was able to fix by restarting the Slony daemon. - Dean 2009/7/14 Lawrence Wong : > I had been using Slony-I together with another database on a server on a > different machine. I had been testing my replication constantly dropping > and creating my Slony-I tables. This is not out of the ordinary though. I > had been doing this for many days now. > > The query, I had been doing is just an update or insert into any table in > this database. There are about 40 tables. Any change to the tables in the > database results in this error. Like I said, I had restarted the PostgreSQL > service several times as well as restarted my Server several times to no > avail. I am pretty stumped and am not sure what to do short of a complete > uninstall and reinstall of PostgreSQL. Although I am not sure that would > work at this point. Probably because I'm still not sure what this error > means. Once again, like I said, it is just a simple update or insert into > my tables. I had been doing this function just a couple minutes earlier to > this message and did not do anything out of the ordinary in the time > between. > >> Date: Tue, 14 Jul 2009 09:16:05 -0400 >> Subject: Re: [GENERAL] cache lookup failed for function 72629 >> From: mmonc...@gmail.com >> To: lawrence...@hotmail.com >> CC: pgsql-general@postgresql.org >> >> On Tue, Jul 14, 2009 at 9:12 AM, Lawrence Wong >> wrote: >> > I'm sorry. What does 're-applying the function source' mean? and how >> > would >> > I do it? >> > >> > For the 'change to the table' I was talking about, I just tried to do a >> > regular update on the table using pgAdmin III. >> > >> > LW . . . Lawrence >> > >> >> well, the idea is to find the function that is causing the problem, >> make a change, and save it back with pgadmin. still, this shouldn't >> be happening, and a database restart should certainly have fixed it. >> do we have any context for this error? do you know the query that is >> causing it? >> >> merlin >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > We are your photos. Share us now with Windows Live Photos. -- 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] Deferrable constraint checking with SAVEPOINT?
On 8 June 2010 03:02, Mike Toews wrote: > Hi, > > I have a question that is not specified in the docs[1]. I am using > deferrable constraints in a transaction with SET CONSTRAINTS ALL > DEFERRED. Now I know that DEFERRED constraints are not checked until > transaction COMMIT (i.e., the end), however are they checked with > SAVEPOINT (i.e., part-way in)? > No, SAVEPOINTs will not force a check. A SAVEPOINT is nothing like a COMMIT (it's more like a nested BEGIN). The only things that will cause deferred constraints to be checked are a COMMIT or a SET CONSTRAINTS .. IMMEDIATE. Regards, Dean -- 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] How to force select to return exactly one row
2010/6/21 Andrus : > if there is no searched primary key row in ko database, select should also > return empty row. > > To get this result I added right join: > > SELECT somecolumns > FROM ko > RIGHT JOIN (SELECT 1) _forceonerow ON true > LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey > ... > LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey > WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; > The reason this won't return a row if there is no matching PK is that the WHERE clause is applied after all the joins to filter the overall result set. So to get what you want, you would need to re-arrange that to something like: SELECT original_query.* FROM ( Original query including WHERE clause ) AS original_query RIGHT JOIN (SELECT 1) AS one_row ON true; Regards, Dean -- 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] Half-applied UPDATE rule on view
On 2 July 2010 23:27, A.M. wrote: > Hello, > > I have encountered an odd behavior involving rules which the following script > demonstrates (in postgresql 8.4.3). Note that at the end of the run, the > "dud" table contains one row "spam1" when the update rule clearly contains > two inserts to the "dud" table. It seems that the update rule on "test.job" > cuts off execution after the first update executes (and succeeds)- why? The problem is that after the first update, "deprecated" is non-NULL and so no longer matches the view definition. All subsequent actions in the rule are combined with the view definition, and so find no matching rows. Rules are total pain, full of gotchas like this. You're almost certainly better off using triggers on your tables. You could patch your rule by moving the update that marks the row as deprecated to the end and changing its WHERE clause, but really you're just inviting further pain by continuing to use rules IMO. Regards, Dean -- 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] Comparison of Oracle and PostgreSQL full text search
On 28 July 2010 02:58, Howard Rogers wrote: > For what it's worth, I wrote up the performance comparison here: > http://diznix.com/dizwell/archives/153 > Thanks, very interesting results. I wonder, are the results being sorted by the database? The performance degradation for large numbers of results might be explained by it switching over from an internal to an external sort, in which case tweaking work_mem might make a difference. Of course this is pure speculation without the EXPLAIN ANALYSE output. Regards, Dean -- 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] Comparison of Oracle and PostgreSQL full text search
On 30 July 2010 00:38, Howard Rogers wrote: > I can't see any change to the sorting behaviour there. Work_mem was > set to 4096MB, shared buffers to 12228MB, temp_buffers to 1024MB, > effective_cache_size to 18442MB. > Ah yes. The sorting idea was a complete red herring. The top-N heapsort to pick the 10 best results will never use much memory. It looks like it all boils down to the sheer number of matches against the search term that have to be considered in the first case. Others on this list might have better ideas as to whether this can be improved upon. > Sadly, I won't be able to provide much further analysis or > information, because the box concerned is being wiped. The MD decided > that, as a matter of corporate governance, he couldn't punt the > company on PostgreSQL, so my experimenting days are over. Back to > Oracle: slower, but with a support contract he can sue on, I guess! > Yeah, I've been there too. Thanks and good luck, Dean > Regards > HJR > -- 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] killing idle attaches without killing others
On 4 August 2010 15:18, Vick Khera wrote: > On Wed, Aug 4, 2010 at 10:03 AM, Gauthier, Dave > wrote: >> >> How can one kill just the processes I see attached to a DB (from >> pg_stat_activity) without disturbing the others? If I need to kill the idle >> pids one ata time, which signal_name should I use for that? >> >> > > Connected to psql as a superuser, issue SELECT pg_cancel_backend(PID); where > PID is the pid of the process to close. That's a SIGINT, but it doesn't actually kill the process, it just cancels it's current query. pg_terminate_backend() sends a SIGTERM to terminate the backend. I think that function is new to 8.4, but you can still manually send the signal if you're on 8.3. http://www.postgresql.org/docs/current/static/functions-admin.html Regards, Dean -- 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] value
2010/9/16 Gissur Þórhallsson :
>> Yes. You're using RULEs where TRIGGERs would do. Change to TRIGGERs.
>
> While this could possibly solve my problem in particular; it doesn't explain
> why this is happening.
> Is this somehow expected behavior on an INSERT rule?
>
Rules can be pretty tricky things to work with, and this is one of the
well-known gotchas (to those who know it well!).
Consider the following simplified version of your example:
CREATE TABLE foo(a serial, b text);
CREATE TABLE bar(a int, b text);
CREATE RULE ins_rule AS ON INSERT TO foo
DO ALSO INSERT INTO bar VALUES(new.a, new.b);
You might think that the rule would guarantee that any insert into foo
would be mirrored with an identical insert on bar. However, this is
not the case. Consider, for example, this insert:
INSERT INTO foo(b) VALUES ('Row 1'), ('Row 2'), ('Row 3');
What the rule will actually do is cause 2 separate INSERT commands to
be executed. The first will add 3 rows to foo, choosing 3 successive
values for 'a' from the sequence. The second command is an insert into
bar, and since 'a' isn't specified, it will use the default for 'a'
from foo, causing another 3 values to be pulled from the sequence. So
the end result is:
SELECT * FROM foo;
a | b
---+---
1 | Row 1
2 | Row 2
3 | Row 3
(3 rows)
SELECT * FROM bar;
a | b
---+---
4 | Row 1
5 | Row 2
6 | Row 3
(3 rows)
which is probably not what you might expect.
It's this sort of thing that makes many people prefer triggers to rules.
Regards,
Dean
--
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] value
2010/9/16 Gissur Þórhallsson :
>> which is probably not what you might expect.
>
> No, indeed it is not.
> My solution - which seems to be working - is replacing:
> new.my_table_id
> with:
> currval(pg_get_serial_sequence('my_table', 'my_table_id'))
> in the on_insert rule
>
OK, but you still need to be careful. That trick will only work if you
insert rows one at a time into the table. If you have a single insert
that inserts multiple rows (as in my example), it would fail because
the currval() call would return the same value for each row in the
insert created by the rule.
My advice would be to convert to triggers.
Regards,
Dean
> This does the trick.
> Thanks,
> Gissur Þórhallsson
>
> Loftmyndir ehf.
> Laugavegur 13
> IS 101 Reykjavík - Iceland
> sími (tel): (+354) 540 2500
> tölvupóstur (email): gis...@loftmyndir.is
>
--
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] input out of error with haversine formula
On 15 October 2010 06:03, Vince Carney wrote: > The following will return an input out of error as the acos() function > cannot be -1 <= x <= 1. > SELECT * FROM > (SELECT *, (3959 * acos(cos(radians(37.7438640)) * > cos(radians(37.7438640)) * cos(radians(-97.4631299) - > radians(-97.4631299)) + sin(radians(37.7438640)) * > sin(radians(37.7438640 > AS distance > FROM foo) AS distances > WHERE distance < 10 > ORDER BY distance > If I break this down the following returns 1: > SELECT (cos(radians(37.7438640)) * cos(radians(37.7438640)) * > cos(radians(-97.4631299) - radians(-97.4631299)) + sin(radians(37.7438640)) > * sin(radians(37.743864000))); > acos(1) would give me 0. > Thoughts or workaround suggestions? > Thanks. > --Vince-- This form of the Haversine formula is known to suffer from large rounding errors when the distance between the points is small. It is much better to use the arcsin(..) form of this formula, which has much greater accuracy, particularly for the common case of small distances. See http://en.wikipedia.org/wiki/Great-circle_distance Regards, Dean -- 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] input out of error with haversine formula
On 16 October 2010 21:13, Vince Carney wrote: > Is it possible to do an if/else statement inside acos() to account for a > 1 > or < -1. I can't seem to get if/else working in postgres? > Try to stay on-list, so that others can benefit from the discussion. Yes you could use CASE..WHEN, but that would add extra complexity to your code without solving the underlying problem with acos(). It won't give you accurate answers when the distance between the points is small, which is usually the most common case. It's a shame that so many people quote this formula when telling people how to compute great circle distances, because it really isn't a very good approach. If you're prepared to consider a little maths, then think about this: Suppose y = cos(x) Then for small values of x (absolute value much less than 1), this can be approximated by a power series expansion: y = 1 - x^2 / 2 + O(x^4) So y is approximately 1, and small changes to x have a little effect on y. Thus computing x = acos(y) when y is approximately 1 is numerically very inaccurate. For small distances, computing Haversine using acos() involves an intermediate value for y, which is very close to 1. Rounding errors might push it slightly over 1, but even if they didn't, computing acos(y) is going to amplify those rounding errors, giving an inaccurate answer. For this reason, it is much better to use the Haversine formula that uses asin(). The power series for sin() looks like this: y = sin(x) = x - x^3 / 6 + O(x^5) So if x is very small, so is y, and small changes to x are matched by small changes to y, and the full accuracy of the computation is preserved. There's also another variant of the formula that uses atan2(), which is good for both small and large distances, but is a little more complicated. Aside from being slightly simpler, the acos() formula really has nothing to recommend it. Regards, Dean http://en.wikipedia.org/wiki/Great-circle_distance -- 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] median for postgresql 8.3
On 16 November 2010 17:37, Pavel Stehule wrote: > Hello > > see > http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html > An 8.3-compatible way of doing it is: SELECT CASE WHEN c % 2 = 0 AND c > 1 THEN (a[1]+a[2])/2 ELSE a[1] END FROM ( SELECT ARRAY(SELECT a FROM milrows ORDER BY a OFFSET (c-1)/2 LIMIT 2) AS a, c FROM (SELECT count(*) AS c FROM milrows) AS count OFFSET 0 ) AS midrows; In my tests this is faster than the analytic and array-based methods, but not by a huge amount. Regards, Dean > Regards > > Pavel Stehule > > 2010/11/16 maarten : >> Hello everyone, >> >> I was doing some analysis of data to find average delays between some >> timestamp values etc... >> When the number of rows the average is computed over is small, this can >> give distorted values. So I've obviously added a count column to see if >> the average represents much data. >> However, I would also like to add the median value to give me a pretty >> good idea of whats happening even for smaller counts. >> >> I couldn't find such an aggregate function in the manual (version 8.3) >> and some websearching didn't uncover it either. >> >> I was thinking about >> SELECT max(id) FROM test ORDER BY id ASC LIMIT >> (SELECT count(*)/2 FROM test) >> >> But two things are wrong with that: >> Limit can't use subqueries :( >> And ORDER BY gives me the error: 'must be used in aggregate function >> etc...) but I can probably work around this by using an ordered subquery >> in stead of the table directly. >> >> Furthermore, I need the median for a timestamp column, which would >> probably complicate things more than when it is a number column. >> >> I'd like to be able to do this using only the database. (So no >> programming functions, special addons etc...) >> >> Any ideas anyone? >> >> regards, >> Maarten >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- 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] Audtiting, DDL and DML in same SQL Function
On 1 February 2012 22:29, Christian Ramseyer wrote: > Hello list > > I'm trying to build a little trigger-based auditing for various web > applications. They have many users in the application layer, but they > all use the same Postgres DB and DB user. > > So I need some kind of session storage to save this application level > username for usage in my triggers, which AFAIK doesn't exist in > Postgres. Googling suggested to use a temporary table to achieve > something similar. > > Question 1: Is this really the right approach to implement this, or are > there other solutions, e.g. setting application_name to user@application > and using this in the triggers or similar workarounds? > There's an example in the manual of another way to keep session-specific data: http://www.postgresql.org/docs/current/static/plperl-global.html You can do similar things in other procedural languages too, just not in PL/pgSQL. Regards, Dean -- 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] Backslashitis
On 14 June 2012 10:03, Raghavendra wrote:
>
> On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer wrote:
>>
>> haman...@t-online.de, 14.06.2012 10:17:
>>
>>> Hi,
>>>
>>> I have a column declared as array of text. I can get a single backslash
>>> into one of the array elements by
>>> update ... set mycol[1] = E'blah \\here'
>>> If I try to update the whole array
>>> update ... set mycol = E'{"blah \\here"}'
>>> the backslash is missing. I can get two backslashes there.
>>> Is there a good way to solve the problem, other than rewriting my update
>>> script to do array updates one element at a time?
>>>
>>
>> Setting
>> standard_conforming_strings = true
>>
>> should do the trick.
>>
>>
>> http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
>>
>> In that case you don't need any escaping inside the string literals.
>>
>> Regards
>> Thomas
>>
>
> Nope..
>
> postgres=# show standard_conforming_strings ;
> standard_conforming_strings
> -
> on
> (1 row)
> postgres=# set standard_conforming_strings =on;
> SET
> postgres=# show standard_conforming_strings ;
> standard_conforming_strings
> -
> on
> (1 row)
> postgres=# update array_test set name=E'{"meet\\ing"}';
> UPDATE 2
> postgres=# select * from array_test ;
> name
> ---
> {meeting}
> {meeting}
> (2 rows)
>
> Correct me, if anything wrong.
>
> --Raghav
>
With standard conforming strings on, you could use any of the following:
update foo set a= E'{"blah here"}';
update foo set a= '{"blah \\here"}';
update foo set a= ARRAY[E'blah \\here'];
update foo set a= ARRAY['blah \here'];
I tend to prefer the ARRAY[...] constructor syntax because it doesn't
require any additional escaping of individual elements.
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
Regards,
Dean
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] RE: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?
On 31 August 2012 16:32, John Lumby wrote: > > ___ >> From: pavan.deola...@gmail.com >> Date: Fri, 31 Aug 2012 11:09:42 +0530 >> Subject: Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails >> the WHERE predicate ? >> >> On Thu, Aug 30, 2012 at 6:31 PM, John Lumby >> mailto:johnlu...@hotmail.com>> wrote: >> >> I would like to use an UPDATE RULE to modify the action performed >> when any UPDATE is attempted on a certain table, >> *including* an UPDATE which would fail because of no rows matching the WHERE. >> >> You did not mention why you need such a facility, but AFAICS RULEs will >> only be applied on the qualifying rows. So as you rightly figured out, >> you won't see them firing unless there are any qualifying rows. Is this >> not something you can achieve via statement-level triggers though ? > > Thanks Pavan; what I need to do is to intercept certain UPDATE statements > which would fail because of no rows matching the WHERE, and instead > issue a different UPDATE which will not fail but will have the same intended > effect. > > The context is a java application which uses hibernate for object-relational > mapping, > and the specific case is hibernate "optimistic locking". > > hibernate provides a way of serializing all INS/UPD/DEL operations performed > under any single "parent" row in a table that has a heirarchy defined by a > kind of self-referencing referential constraint, that is, > each row has a parent_id column pointing to some other row. > > It is possible to tell hibernate to serialize INS/UPD/DELon any particular > table. > hibernate then uses another column named "version" to do the serialization - > using a sequence like so (for example of an INS): > > 1 .SELECT parent entity of entity to be INSerted, > by specifying WHERE id = >and note its version - let's say > version = V > > 2 .INSERT the new entity with version set to 0 > > 3 .UPDATE the parent entity : set version = (V+1) > WHERE id= AND version = V > throw exception and ROLLBACK the INSERT if this UPDATE failed > (it will fail if another thread had performed another > intervening INSERT and updated parent's version) > > Now, our problem is that control of this optimistic locking behaviour is > per table, > whereas we ideally want it to operate at the level of object type within > table. > That is, in certain well-defined cases, we do not want this serialization > to be done. > My idea was to intercept the UPDATE in these cases and change the UPDATE into >UPDATE the parent entity : set version = (OLD.version+1) > > WHERE id= > so the parent's version would be set correctly but concurrent inserts would > be permitted. > > So now to your suggestion of a trigger - > Yes, I think it can be invoked in the case in question, but only if it is > defined as > a BEFORE statement trigger, not an INSTEAD OF trigger, and then it cannot > prevent the failing UPDATE from being done after it (trigger) has run. > We would really need an INSTEAD OF statement-level trigger but there is no > such capability. > > RULEs seem to be more general than triggers and I didn't see anything quite > so clear-cut > in the documentation to imply it can't be done, other than the notes I > quoted earlier > from chapter 38.3.1. How Update Rules Work > about the query trees and that the original query's qualification is always > present. > > Also, when I ran the test of the RULE, I thought it was significant that > psql showed the > name of my RULE function as though it was somehow being invoked : > update updatable set version = 2 where id = 1 and version = 1 > optlock_control > - > (0 rows) > > UPDATE 0 It shows the name of your function because your rule is rewriting the UPDATE statement, effectively turning into SELECT optlock_control() WHERE , so the function name becomes the column name of the result, but it isn't actually invoked because there are no matching rows. Even if it did work, turning an UPDATE into a SELECT like that is likely to confuse Hibernate when it tries to check the statement's return status. What you are trying to do cannot be achieved rules, and doing it this way with triggers is likely to be messy. I think you need to consider a different approach. It sounds like what you really want is finer-grained control over the Hibernate optimistic locking check. One way of doing that would be to do the check yourself in a BEFORE UPDATE ROW trigger, with something to the effect of: if new.version != old.version+1: raise concurrency error (will cause the entire transaction to be rolled back) Then you could turn off the Hibernate check and add any finer-grained control you needed in your trigger function. Regards, Dean -- Sent via pgsql-general m
