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 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 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 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
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 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 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 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 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 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 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 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
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 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 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 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 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 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 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 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 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) -
>
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
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
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
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 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 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
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 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
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 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
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
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/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/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/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
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
> 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
> 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
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
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
> 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
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
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.
> 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
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
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
49 matches
Mail list logo
