On 15 July 2015 at 09:34, Andreas Kretschmer wrote:
> test=> explain select * from rls_test ;
> QUERY PLAN
> -
> Seq Scan on rls_test (cost=0.00..630.00 rows=91 width=335)
>Filter: (name = ("current_user"())
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) -
>
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 w
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 milro
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
On 7 February 2013 07:45, Albe Laurenz wrote:
> Gavan Schneider wrote:
>> Taking a different tangent ...
>
> Good idea.
>
>> Is there anything in the SQL standards about NOT NULL
>> constraints being deferrable?
>>
>> To my mind we should not consider implementing non-standard
>> behaviour, but if
On 7 February 2013 09:02, Bèrto ëd Sèra wrote:
> Hi
>
>> also a
>> deferrable primary key/unique constraint can't be used as the target
>> for a foreign key.
>
> ehr, why? I mean, I'm positive it cannot be used before an actual
> value is in the record, but what would be the problem, apart from
>
On 7 February 2013 08:50, Dean Rasheed wrote:
> That's actually a sensible default, because there are consequences to
> making a constraint deferrable --- it can hurt performance if a large
> number of rows need to be queued up for later checking...
Just to clarify --- PostgreSQ
On 25 February 2013 19:52, Russell Keane wrote:
> Hi,
>
>
>
> We have a table which is inserted to and update via a view (using rules /
> functions).
>
In PG 9.1 and later, the recommended way to do this is using INSTEAD
OF triggers on the view.
See the "Notes" section here:
http://www.postgresq
On 6 March 2013 14:35, Shaun Thomas wrote:
> On 03/06/2013 04:49 AM, Glyn Astill wrote:
>
>> What version of slony are you on? The specifics of what you mention
>> don't sound quite right, but it sounds very much like bug 167 which
>> was fixed in 2.1.2 if I remember correctly.
>
>
> We're on 2.1
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 redirec
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-w
> 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
u, 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.
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 creat
On 17 May 2014 13:25, Thomas Kellerer wrote:
> Hello,
>
> when playing with 9.4 beta I noticed that the result of pg_get_viewdef()
> will not include the new WITH CHECK OPTION clause when the view was created
> using it.
>
> Is that intended (if so: why?) or is this an oversight/bug?
>
Yes, that'
On 19 May 2014 02:35, Tom Lane wrote:
> Dean Rasheed writes:
>> On 17 May 2014 13:25, Thomas Kellerer wrote:
>>> when playing with 9.4 beta I noticed that the result of pg_get_viewdef()
>>> will not include the new WITH CHECK OPTION clause when the view was cre
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);
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” whic
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( .
> 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 da
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
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 m
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,
On 2 September 2012 22:42, johnlumby wrote:
> On 09/01/12 03:46, Dean Rasheed wrote:
>> 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
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), howev
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
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 t
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 resu
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 pic
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 shou
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 thi
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
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
> 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 ca
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
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 ac
> 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 innoc
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 vis
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.
>
> --
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 (D
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 differ
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
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
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 useri
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
> 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
>> - 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
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/messenge
49 matches
Mail list logo
