Sridhar N Bamandlapally writes:
> Is there a way to avoid creating rule under creation of view ?
If you mean the ON SELECT rule, no. A view basically *is* an ON SELECT
rule; there's not very much else to it. What usefulness do you imagine
you'd get from a view without ON SELECT?
On 01/28/2013 02:19 AM, Leif Jensen wrote:
Hello.
We have a system that has been running using PostgreSQL 8.4. We have now
upgraded to PostgreSQL 9.1. The system has several rules on views and now most
of these does not seem to work anymore. It is rather simple rules with mainly
only
Martijn van Oosterhout writes:
> On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote:
>> Why are the individual indices not useful? The tests that the
>> query does -- equality on key and realm and ordering on at --
>> are each supported by indices. Does it have to do with the cost
>> of l
On Fri, Aug 24, 2012 at 09:32:32AM +, Jason Dusek wrote:
> 2012/8/23 Tom Lane :
> > Jason Dusek writes:
> >> CREATE TABLE kv
> >> ( k bytea NOT NULL,
> >> at timestamptz NOT NULL,
> >> realm bytea NOT NULL,
> >> v bytea NOT NULL );
> >> CREATE INDEX ON kv USING hash(k);
> >>
2012/8/23 Tom Lane :
> Jason Dusek writes:
>> I have a simple table of keys and values which periodically
>> receives updated values. It's desirable to keep older values
>> but, most of the time, we query only for the latest value of a
>> particular key.
>
>> CREATE TABLE kv
>> ( k bytea NOT N
Jason Dusek writes:
> I have a simple table of keys and values which periodically
> receives updated values. It's desirable to keep older values
> but, most of the time, we query only for the latest value of a
> particular key.
> CREATE TABLE kv
> ( k bytea NOT NULL,
> at timestamptz NOT
Hi,
On 16 February 2012 01:14, Robert James wrote:
> What rules of thumb exist for:
> * How often a table needs to be vacuumed?
> * How often a table needs to be analyzed?
> * How to tune Autovacuum?
I prefer to use autovacuum daemon and sets thresholds on per table
basis i.e. sets reasonable de
Thanks. What about auto-analyze? When will they be analyzed by default?
And what actions generally require new analyze?
On 2/15/12, Bruce Momjian wrote:
> On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote:
>> What rules of thumb exist for:
>> * How often a table needs to be vacuumed?
On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote:
> What rules of thumb exist for:
> * How often a table needs to be vacuumed?
> * How often a table needs to be analyzed?
> * How to tune Autovacuum?
>
> I have a large DB server, and I'm concerned that it's not being
> autovaccumed and
You're right, rules are perfect for very limited and narrow cases. And
make it very hard to write complicated queries against. (i.e., updates
that only touch few columns, likewise with inserts).
I'm guessing the upside is that rules are faster then triggers.
--
Sent via pgsql-general mailing list
> -Original Message-
> From: Gregg Jaskiewicz [mailto:gryz...@gmail.com]
> Sent: Friday, September 30, 2011 5:18 AM
> To: Igor Neyman
> Cc: Ondrej Ivanič; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Rules going away
>
> speaking of DO INSTEAD, for inser
speaking of DO INSTEAD, for insert/update case. Try using RETURNING
with that and rules ;) Good luck
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> -Original Message-
> From: Ondrej Ivanič [mailto:ondrej.iva...@gmail.com]
> Sent: Wednesday, September 28, 2011 6:47 PM
> To: pgsql-general@postgresql.org
> Subject: Re: Rules going away
>
> Hi,
>
> > folks, don't use RULES! use triggers -- and as much as possible, keep
> > triggers sim
Hi,
> folks, don't use RULES! use triggers -- and as much as possible, keep
> triggers simple, short, and to the point (simple validation, custom
> RI, auditing/logging, etc).
I like them :). 'DO INSTEAD' rules are great for partitioning so you
can insert (or update) to parent table and 'DO INSTE
On Wed, Sep 28, 2011 at 10:53 AM, Rob Sargent wrote:
>
>
> On 09/28/2011 08:34 AM, Tom Lane wrote:
>> Andrew Sullivan writes:
>>> On Wed, Sep 28, 2011 at 06:20:04AM -0700, David Fetter wrote:
There's an even better reason not to use rules: they're going away in
a not too distant version
On 09/28/2011 08:34 AM, Tom Lane wrote:
> Andrew Sullivan writes:
>> On Wed, Sep 28, 2011 at 06:20:04AM -0700, David Fetter wrote:
>>> There's an even better reason not to use rules: they're going away in
>>> a not too distant version of PostgreSQL.
>> Really? How? I thought views were done us
On 6 Jul 2010, at 13:03, Andre Lopes wrote:
> Hi Alban,
>
> But in my application I have more than one way of uniquely identify the
> record. Could be by the email field or by the id field.
Unique is unique. There is no other record that could possibly be identified by
the same unique identifi
Hi Alban,
But in my application I have more than one way of uniquely identify the
record. Could be by the email field or by the id field.
Thera are update that are done by the WHERE email clause and other by the
WHERE id clause.
It is possible to deal with this?
Best Regards,
On Tue, Jul 6,
On 6 Jul 2010, at 12:28, Andre Lopes wrote:
> Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but
> I have some doubts about it... let me explain...
>
> Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know
> how to use the clause WHERE in the UPDA
Le mercredi 9 décembre 2009 à 17:04:32, George Silva a écrit :
> Hello all again :)
>
> I have a question: is it possible to replicate the behavior of a rule
> INSTEAD of with a TRIGGER?
>
> For some specific reasons i need to use a trigger to update a
> secondary table, but i don't want the chan
Le mercredi 9 décembre 2009 à 12:38:33, George Silva a écrit :
> Hello guys,
>
> Still having some trouble with this.
>
> I'm trying to use a case when, but postgres is still giving me syntax
> error.
>
> CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo
> DO INSTEAD
> (
> CASE SELECT e
Hello guys,
Still having some trouble with this.
I'm trying to use a case when, but postgres is still giving me syntax error.
CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo
DO INSTEAD
(
CASE SELECT exists(SELECT 1 FROM versioning.foo_version_1 WHERE
OLD.oid = NEW.oid) as a WHEN a=true
On Wednesday 9. December 2009 09.06.12 Guillaume Lelarge wrote:
> Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit :
> > [...]
> > I can't seem to understand why a simples if is not working on the
> > creation of rules.
> >
> > I tried both ways (am i missing something?):
> >
>
> Sur
Le mercredi 9 décembre 2009 à 01:52:03, George Silva a écrit :
> [...]
> I can't seem to understand why a simples if is not working on the
> creation of rules.
>
> I tried both ways (am i missing something?):
>
Sure. IF does not exist in SQL, and you can only put SQL statements in a rule.
Inste
James B. Byrne wrote:
> The situation is this. A dependent table relationship is episodic. In other
> words, a product might be available for a period of time, then not available,
> then available again. Or, a firm might be a client for a period, then not,
> then again. Or a person might be an
Albe Laurenz wrote:
srdjan wrote:
What is the desired response to
INSERT INTO vvv (name, town, num) VALUES ('Karl', 'Leipzig', 18);
Should this generate an error message, do nothing, or insert something?
In this case you shouldn't be able to do this insert.
Should it generate
Albe Laurenz wrote:
srdjan wrote:
I'll try to explain my problem with an example.
-- I've got 2 tables and one view
CREATE TABLE a (name varchar(20) primary key, num integer);
CREATE TABLE b (town varchar(15), name varchar(20) references a(name));
CREATE VIEW vvv AS SELECT * FROM a NATUR
srdjan wrote:
>>> -- I've got 2 tables and one view
>>> CREATE TABLE a (name varchar(20) primary key, num integer);
>>> CREATE TABLE b (town varchar(15), name varchar(20) references a(name));
>>>
>>> insert into a values ('tom',5);
>>> insert into a values ('paul',99);
>>> insert into a value
srdjan wrote:
>> What is the desired response to
>>
>> INSERT INTO vvv (name, town, num) VALUES ('Karl', 'Leipzig', 18);
>>
>> Should this generate an error message, do nothing, or insert something?
>
> In this case you shouldn't be able to do this insert.
Should it generate an error message or
srdjan wrote:
> I'll try to explain my problem with an example.
>
> -- I've got 2 tables and one view
> CREATE TABLE a (name varchar(20) primary key, num integer);
> CREATE TABLE b (town varchar(15), name varchar(20) references a(name));
>
> CREATE VIEW vvv AS SELECT * FROM a NATURAL JOIN b;
On Nov 26, 2007, at 6:09 PM, Simon Riggs wrote:
On Mon, 2007-11-26 at 16:01 -0800, Alex Vinogradovs wrote:
I've got a data warehouse with pretty high rate of insert into
partitioned tables. What I've noticed, is that rule-based
partitioning
seems to be somewhat slower than insertions made dir
On Mon, 2007-11-26 at 16:01 -0800, Alex Vinogradovs wrote:
> I've got a data warehouse with pretty high rate of insert into
> partitioned tables. What I've noticed, is that rule-based partitioning
> seems to be somewhat slower than insertions made directly into
> partitions through execution of dy
Josh Harrison escribió:
> Hello,
> I have a question in the postgres document chapter 34. Rules and Command
> Status.
> The last paragraph of that page says that
>
> "The programmer can ensure that any desired INSTEAD rule is the one that
> sets the command status in the second case, by giving it
"Roberto Icardi" <[EMAIL PROTECTED]> writes:
> Everything works perfectly... BUT the query returns always 0 rows affected,
> even if one record is inserted or deleted in friends_hobbies...why??
The rules for this are explained here:
http://www.postgresql.org/docs/8.2/static/rules-status.html
If
Martijn van Oosterhout writes:
> Well, I notice that the SQL standard defines something called WITH, so
> what you want is something like:
> WITH OLD AS ( SELECT blah )
> DO
> ( UPDATE
> ; DELETE )
I think it'd be a mistake to assume that WITH would fix Markus'
complaint. I haven't studied th
On Sun, May 14, 2006 at 08:01:39AM +0200, Markus Schiltknecht wrote:
> > What you probably want is a function that is given the row and then
> > executes the two statements on a per row basis. This has the effect you
> > want but gives up the major benefit of rules, wholesale query
> > restructurin
Hi Martijn,
On Fri, 2006-05-12 at 18:05 +0200, Martijn van Oosterhout wrote:
> But it can't really. In the example that started this thread, there are
> two seperate rules and after rewriting the executor will be presented
> two seperate queries.
Ah, thank you, that explains the difficulties with
On Fri, May 12, 2006 at 04:22:00PM +0200, Markus Schiltknecht wrote:
> > There have been discussions about the problems
>
> Do you have some pointers here? I did not find relevant discussions in
> the archives (of gmane.org)
Hmm, maybe not so much discussions as comments from developers when the
> Well, triggers cannot be used to create writeable views, can they?
The documentation says that triggers can be used on views but only for Inserts;
not updates or
deletes.
http://www.postgresql.org/docs/8.1/interactive/rules-triggers.html
Regards,
Richard
---(end of b
On Fri, 2006-05-12 at 15:57 +0200, Martijn van Oosterhout wrote:
> It's a known problem. It's also one of the reasons why triggers are
> recommended over rules. And it's not desirable behaviour.
Well, triggers cannot be used to create writeable views, can they?
> There have been discussions about
On Fri, May 12, 2006 at 03:51:18PM +0200, Markus Schiltknecht wrote:
> Hi,
>
> I was trying to create an updateable view. Suddenly I got foreign key
> violations when using nextval('myseq').
>
> As I understand, the rewriter does something similar to a simple text
> replacement (I guess copying t
This is best done by using a trigger on the table.
http://www.varlena.com/GeneralBits/38.php shows how
to do it with both a trigger and with a rule.
--elein
[EMAIL PROTECTED]
On Fri, May 05, 2006 at 04:48:28PM -0400, Sean Hamilton wrote:
> I have to log each time a user selects data from a table
On 5/5/06, Sean Hamilton <[EMAIL PROTECTED]> wrote:
I have to log each time a user selects data from a table by inserting
record in another table.
For example
I have table xzy
user test selects id, name from table xyz.
I want to insert into table xyz_log
user, action, fields, timestamp
test, sele
Sean,
I am looking to do the same thing...
I have so far posted with little relevant response to pgsql-admin,
pgsql-general and pgsql-sql
If you get any help please forward my way...
I will do the same..
What I can tell you is that it is possible to write the Select
Statements submitted to the
On Jan 11, 2006, at 21:15 , William Leite Araújo wrote:
CREATE OR REPLACE RULE select_public_cliente AS ON SELECT TO
public.cliente_tipo
ERROR: event qualifications are not implemented for rules on SELECT
As the error message indicates, RULEs are not applicable to ON
SELECT. Use a vie
On 8/22/05, Pailloncy Jean-Gerard <[EMAIL PROTECTED]> wrote:
Hi,With PostgreSQL 7.4.6.I have a table aaa (id serial, maj timestamp without time zone)I defined to function from_timestamp and to_timestamp to doconversion between text and timestamp with few checks and tricks.
I defined a view aab (id
Thanks for the info guys; got a better understanding now.
--
Randall Perry
sysTame
Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales
http://www.systame.com/
---(end of broadcast)---
TIP 3: Have you checked our e
Am Dienstag, 26. Juli 2005 23:53 schrieb Randall Perry:
> Read the Rules section of the manual and the section on Rules vs Triggers.
>
> From what I get triggers are necessary for column constraints. As far as
> speed, it seems there are some differences between how fast rules/triggers
> would do t
# [EMAIL PROTECTED] / 2005-07-26 17:53:35 -0400:
> Read the Rules section of the manual and the section on Rules vs Triggers.
>
> From what I get triggers are necessary for column constraints. As far as
> speed, it seems there are some differences between how fast rules/triggers
> would do the sam
As a new user, I recently had the same problem. I then realized what the
problem is.
You want to select from sharp_p1 instead of selecting from sharp. That
means you never actually select from sharp. Since nothing was selected,
postgres can't do anything useful with the WHERE clause. To put it
On Mon, Dec 06, 2004 at 04:15:54PM +1100, Jamie Deppeler wrote:
> i have a join table eg
>
> primarykey
> field1
> field2
>
> and based on SQL Select have the values of field1 inserted into a new
> table which will scroll which will launch a trigger but im having
> problems getting this to wor
>> Planning on witting a rule for a view, and i was wondering if anyone
>> could suggest a good Internet resource?
>
> http://www.postgresql.org
>
> (Sorry, couldn't resist.)
>
But here is a simple working example of making a view updatable:
CREATE TABLE consumable (
consumable_pk serial NOT
> Planning on witting a rule for a view, and i was wondering if anyone
> could suggest a good Internet resource?
http://www.postgresql.org
(Sorry, couldn't resist.)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map sett
Have you looked at the html docs? It looks like "34.2. Views and the Rule
System" might be what you are looking for, but I didn't check it closely.
On Thursday 02 December 2004 05:34 pm, Jamie Deppeler saith:
> HI,
>
> Planning on witting a rule for a view, and i was wondering if anyone
> could s
Hi,
Citing Jamie Deppeler <[EMAIL PROTECTED]>:
> sample sql querty
>
> INSERT INTO schema.table2
> (
> "field1",
> "field2",
> "field3",
> "field4",
> "field5",
> "field6",
> "field7",
> "field8",
> "field9",
> )
> VALUES
> (
> SELECT
>
On Wed, 2004-11-17 at 12:49, Robert Fitzpatrick wrote:
> On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote:
> > On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:
> >
> > > I have a view that used union all to merge three tables together. I was
> > > hoping to create a rule, one for
On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote:
> On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:
>
> > I have a view that used union all to merge three tables together. I was
> > hoping to create a rule, one for each table using the WHERE condition of
> > the rule to determin
On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote:
> I have a view that used union all to merge three tables together. I was
> hoping to create a rule, one for each table using the WHERE condition of
> the rule to determine which table gets updated. Is this possible?
See the CREA
After my last email, I added the rest of the rule actions. So the
relevant part of the schema now looks like this:
CREATE RULE update_msg_stats
AS ON INSERT TO messages DO (
UPDATE users SET num_posts = num_posts + 1
WHERE users.id = new.poster;
UPDATE threads SET
Neil Conway <[EMAIL PROTECTED]> writes:
> I would expect the rule it cause a bit of overhead (maybe
> taking twice or three times as long as w/o the rule), but
> it's taking ~52x longer.
Ouch.
> I've tried creating an index on messages.poster, but it has
> no effect (performance is the same). I
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Is the INSERT rule re-ordering mentioned a TODO item?
Darn if I know. I threw the thought out for discussion, but didn't
see any comments. I'm not in a hurry to change it, unless there's
consensus that we should.
regards, tom
Is the INSERT rule re-ordering mentioned a TODO item?
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I thought an INSERT rule with an UPDATE action would work on the same
> > table, but that fails. Seems the rule is firing before the INSERT
> > happens.
>
> Yes, a trigger is the right way to d
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I thought an INSERT rule with an UPDATE action would work on the same
> > table, but that fails. Seems the rule is firing before the INSERT
> > happens.
>
> Yes, a trigger is the right way to do surgery on a tuple before it is
> stored. Rules are
Although not exactly what you were asking about, it might be easier to get
the effect with a before insert trigger written in plpgsql.
(only minimally tested -- and against a 6.5 db - and replace the 100 and 0.1
with real values)
create function checktriggerfunc() returns opaque as '
begin
if (N
64 matches
Mail list logo
