Hi,
This seems like it must be a common question, but Google can't find
the answer for me. How do I view the currently open row-level locks?
The manual says that the table pg_locks doesn't contain row-level
locks, since they are stored on disk rather than in memory, but it
doesn't say how one can
On Fri, Jun 27, 2008 at 3:06 AM, Dean Rasheed <[EMAIL PROTECTED]> wrote:
>> 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 exc
On Thu, Jun 26, 2008 at 12:11 PM, Dean Rasheed <[EMAIL PROTECTED]> 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
On Thu, Jun 26, 2008 at 5:08 AM, Dean Rasheed <[EMAIL PROTECTED]> 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
On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane <[EMAIL PROTECTED]> 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
On Mon, Jun 23, 2008 at 11:54 PM, Adam Rich <[EMAIL PROTECTED]> wrote:
>> Can you describe, or point me to somewhere which describes, all the
>> things you can do with a rule that you can't do with a trigger? The
>> only examples of rules in the manual are (1) logging, which I've just
>> been told
In another thread, Tom Lane <[EMAIL PROTECTED]> wrote:
> Well, the rule system is fundamentally a macro-expansion mechanism,
> and multiple-evaluation risks come with that territory. There are
> things you can do with macro expansion that can't be done any other
> way, so I don't think that that d
On Mon, Jun 23, 2008 at 8:46 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Hmm ... that might be a bug, but in any case, wouldn't it be wiser to do
>
> CREATE RULE _insert AS ON INSERT TO tv DO INSTEAD
> INSERT INTO test (name) VALUES (NEW.name) RETURNING test.*;
Well, what I'm really trying to do is
Hi,
What are the rules about what tables/views can be referenced from the
RETURNING clause of an INSERT?
I am particularly puzzled by the following. Given these definitions:
CREATE TABLE test (id serial primary key, name varchar);
CREATE VIEW tv AS SELECT * FROM test;
This works:
CREATE RULE
On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
> The only way I could find to make this work is to use a rule and wrap the
> inner "insert returning" in a function.
>
> create or replace function newperson (studentinfo) returns setof person as
> $$
> declare
> arec person%r
Thanks to everyone who responded to this thread; although I have not
gotten a complete solution I have learned a lot about how rules and
triggers work. One particular question that is still unanswered:
On Mon, Jun 16, 2008 at 9:49 PM, Michael Shulman <[EMAIL PROTECTED]> wrote:
> The
On Tue, Jun 17, 2008 at 10:15 AM, Michael Shulman <[EMAIL PROTECTED]> wrote:
> <[EMAIL PROTECTED]> wrote:
>> CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
>> DO INSTEAD
>> (
>> INSERT INTO person ...;
>> INSERT INTO student(person_id,
On Tue, Jun 17, 2008 at 11:50 AM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> While your individual update rules are firing for each of your tables
> from T1 thru T[n] to change your OLD row to NEW. Another client could
> also at the same time be updating any of the other tables before and
> aft
On Tue, Jun 17, 2008 at 12:24 AM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> Anyway, here is a link discussing a generalized vertical partitioned
> view. Perhaps it can give you some idea to get yourself rolling.
> http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php
Thank you ver
On Tue, Jun 17, 2008 at 7:56 AM, Philippe Grégoire
<[EMAIL PROTECTED]> wrote:
> CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
> DO INSTEAD
> (
> INSERT INTO person ...;
> INSERT INTO student(person_id,...) VALUES
> (currval('person_person_id_seq'),...);
> );
I initially thought of thi
On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <[EMAIL PROTECTED]> wrote:
> The only way I could find to make this work is to use a rule and wrap the
> inner "insert returning" in a function.
Thanks, this works! Although it feels like something of a hack;
shouldn't there be a more elegant solution?
On Mon, Jun 16, 2008 at 10:27 PM, Craig Ringer
<[EMAIL PROTECTED]> wrote:
>> I want to be able to do INSERTs on "studentinfo" and have rows created
>> in both "person" and "student". This requires first inserting into
>> "person", capturing the "person_id" of the resulting row, and using it
>> to
On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
>> I can write a trigger
>> function that does the right thing, with 'INSERT ... RETURNING
>> person_id INTO ...', but Postgres will not let me add an INSERT
>> trigger to a view; it says 'ERROR: "studentinfo" is not a table
Hi,
This feels like a very basic question but I cannot figure it out.
Suppose I have two tables and a view that combines their data:
CREATE TABLE person
(person_id SERIAL PRIMARY KEY,
...);
CREATE TABLE student
(student_id SERIAL PRIMARY KEY,
person_id INTEGER REFERENCES person,
...)
CR
19 matches
Mail list logo
