pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert into VIEW using RULE. Not possible to use nextval()?
6+ messages / 2 participants
[nested] [flat]
* Insert into VIEW using RULE. Not possible to use nextval()?
@ 2007-05-03 17:04 Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Collin Peters @ 2007-05-03 17:04 UTC (permalink / raw)
To: pgsql-sql
I am having the same problem that is documented elsewhere in the
archives. Namely when you have a INSERT RULE on a VIEW the nextval()
function doesn't behave properly (or it doesn't behave how I'd like it
to).
http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
I'm wondering if anything has changed for this in regards to best
practices? Suggested solutions are to change to a trigger or use
currval() for your secondary INSERTS inside the RULE.
A trigger does not apply to my case as I am basically using this as a
shortcut to manually doing two INSERTs. Is there any problems with
using the currval() approach? If I use that within the same call is
there any chance of that not returning the correct value? (e.g. if
this INSERT RULE is being called a 1000 times at once, is it
guaranteed to be correct?
Another option I see is to place the INSERT inside a LOOP. For
example instead of:
INSERT INTO user_activity_single(user_activity_id, activity_date,
user_activity_type_id, user_activity_action_id, user_id, div1)
SELECT nextval('user_activity_user_activity_id_seq'), etc....
have:
FOR mviews IN
SELECT nextval('user_activity_user_activity_id_seq') as id,
CURRENT_DATE, 1, 2, 27, 'foo'
LOOP
INSERT INTO user_activity_single(mviews.id, etc...)
END LOOP;
Performance wise this doesn't seem as good. In my case the SELECT
statement would be around 4000 records.
Any tips for me?
Regards,
Collin Peters
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert into VIEW using RULE. Not possible to use nextval()?
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
@ 2007-05-03 17:26 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-03 18:30 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Broersma Jr @ 2007-05-03 17:26 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; pgsql-sql
--- Collin Peters <cadiolis@gmail.com> wrote:
> I am having the same problem that is documented elsewhere in the
> archives. Namely when you have a INSERT RULE on a VIEW the nextval()
> function doesn't behave properly (or it doesn't behave how I'd like it
> to).
>
> http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
>
Is your problem that you can't insert more than one record at a time into your INSERTable VIEW?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert into VIEW using RULE. Not possible to use nextval()?
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-05-03 18:30 ` Collin Peters <cadiolis@gmail.com>
2007-05-03 19:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Collin Peters @ 2007-05-03 18:30 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
The exact problem is that you can't use nextval() with an INSERTable VIEW
Problem is the same as that in this post:
http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
On 5/3/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> --- Collin Peters <cadiolis@gmail.com> wrote:
>
> > I am having the same problem that is documented elsewhere in the
> > archives. Namely when you have a INSERT RULE on a VIEW the nextval()
> > function doesn't behave properly (or it doesn't behave how I'd like it
> > to).
> >
> > http://archives.postgresql.org/pgsql-sql/2003-07/msg00333.php
> >
>
> Is your problem that you can't insert more than one record at a time into your INSERTable VIEW?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert into VIEW using RULE. Not possible to use nextval()?
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-03 18:30 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
@ 2007-05-03 19:26 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-04 21:51 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Broersma Jr @ 2007-05-03 19:26 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
--- Collin Peters <cadiolis@gmail.com> wrote:
> The exact problem is that you can't use nextval() with an INSERTable VIEW
I apoligize if I am not understanding your problem correctly. I am unsure as to intent behind
using nextval() as opposed to currval().
I do not know if the following DDL will help but I can show you how I preform inserts across
multiple tables using the rule system:
CREATE RULE Vschematic_insert AS ON INSERT TO Docs.Vschematic
DO INSTEAD (
INSERT INTO Docs.Document ( did, doccode, docnum, docdisc)
VALUES ( DEFAULT, 'schematic', New.docnum, New.docdisc);
INSERT INTO Docs.Drawing ( did, doccode, title1, title2, title3)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic', New.title1, New.title2, New.title3);
INSERT INTO Docs.Schematic ( did, doccode)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic')
);
For reference the table DDL follows:
CREATE TABLE docs.document (
did serial PRIMARY KEY,
doccode varchar(30) not null,
docnum varchar(30) unique not null,
docdisc text not null default '',
constraint
document_doccode_chk
check ( doccode in ( 'cpf', 'logicsystem', 'processdetail',
'electricaldetail', 'locationplan', 'logicdiagram',
'loopdiagram', 'schematic', 'wiringdiagram', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail', 'functionaldiscription',
'datasheet', 'processmaterialspec',
'loopfoldermiscellaneous', 'loopfolderorficeplate',
'loopfolderinstallation', 'loopfolderswitch',
'loopfolderxmtrctrlind', 'loopfoldercontrolvalve',
'loopfolderanalyzer', 'loopfolderworkscope',
'loopfolderdocumentation')));
CREATE TABLE docs.drawing
(
did integer primary key references
docs.document(did) on delete cascade,
doccode varchar(30) not null,
title1 varchar(50) not null,
title2 varchar(50) not null,
title3 varchar(50) not null,
constraint
drawing_doccode_chk
check ( doccode in ( 'processdetail', 'electricaldetail', 'locationplan',
'logicdiagram', 'loopdiagram', 'schematic', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail'))) ;
CREATE TABLE docs.schematic
(
did integer primary key references
docs.drawing(did) on delete cascade,
doccode varchar(30) not null,
cid integer references equ.lcp(cid),
constraint
schematic_doccode_chk
check ( doccode = 'schematic')) ;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert into VIEW using RULE. Not possible to use nextval()?
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-03 18:30 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 19:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-05-04 21:51 ` Collin Peters <cadiolis@gmail.com>
2007-05-04 22:16 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Collin Peters @ 2007-05-04 21:51 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
Hi Richard,
Your situation is the same as mine. The only difference is that I
actually had a call to nextval() in the actual call to the VIEW. In
your case this would be something like "INSERT INTO
Vschematic_insert(...". So if you were to try "INSERT INTO
Vschematic_insert(nextval('foo_sql'), ..." and then try to use that
value inside the RULE it will fail.
Your solution works great assuming two things:
1. You don't need to use the ID for anything else (which is true in
your case and actually in mine as well)
2. currval() is guaranteed to be correct within the RULE. This was my
second question. If I call "INSERT INTO Vschematic_insert(..."
simultaneously 1000 times is it guaranteed to be correct?
On 5/3/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> --- Collin Peters <cadiolis@gmail.com> wrote:
>
> > The exact problem is that you can't use nextval() with an INSERTable VIEW
>
> I apoligize if I am not understanding your problem correctly. I am unsure as to intent behind
> using nextval() as opposed to currval().
>
> I do not know if the following DDL will help but I can show you how I preform inserts across
> multiple tables using the rule system:
>
> CREATE RULE Vschematic_insert AS ON INSERT TO Docs.Vschematic
> DO INSTEAD (
> INSERT INTO Docs.Document ( did, doccode, docnum, docdisc)
> VALUES ( DEFAULT, 'schematic', New.docnum, New.docdisc);
>
> INSERT INTO Docs.Drawing ( did, doccode, title1, title2, title3)
> VALUES ( Currval('Docs.Document_did_seq'), 'schematic', New.title1, New.title2, New.title3);
>
> INSERT INTO Docs.Schematic ( did, doccode)
> VALUES ( Currval('Docs.Document_did_seq'), 'schematic')
> );
>
> For reference the table DDL follows:
>
> CREATE TABLE docs.document (
> did serial PRIMARY KEY,
> doccode varchar(30) not null,
> docnum varchar(30) unique not null,
> docdisc text not null default '',
>
> constraint
> document_doccode_chk
> check ( doccode in ( 'cpf', 'logicsystem', 'processdetail',
> 'electricaldetail', 'locationplan', 'logicdiagram',
> 'loopdiagram', 'schematic', 'wiringdiagram', 'pid',
> 'isometric', 'airsupplydetail', 'mountingdetail',
> 'pnuematicdetail', 'functionaldiscription',
> 'datasheet', 'processmaterialspec',
> 'loopfoldermiscellaneous', 'loopfolderorficeplate',
> 'loopfolderinstallation', 'loopfolderswitch',
> 'loopfolderxmtrctrlind', 'loopfoldercontrolvalve',
> 'loopfolderanalyzer', 'loopfolderworkscope',
> 'loopfolderdocumentation')));
>
> CREATE TABLE docs.drawing
> (
> did integer primary key references
> docs.document(did) on delete cascade,
> doccode varchar(30) not null,
> title1 varchar(50) not null,
> title2 varchar(50) not null,
> title3 varchar(50) not null,
>
> constraint
> drawing_doccode_chk
> check ( doccode in ( 'processdetail', 'electricaldetail', 'locationplan',
> 'logicdiagram', 'loopdiagram', 'schematic', 'pid',
> 'isometric', 'airsupplydetail', 'mountingdetail',
> 'pnuematicdetail'))) ;
>
> CREATE TABLE docs.schematic
> (
> did integer primary key references
> docs.drawing(did) on delete cascade,
> doccode varchar(30) not null,
> cid integer references equ.lcp(cid),
>
> constraint
> schematic_doccode_chk
> check ( doccode = 'schematic')) ;
>
> Regards,
> Richard Broersma Jr.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Insert into VIEW using RULE. Not possible to use nextval()?
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-03 18:30 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 19:26 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-04 21:51 ` Re: Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
@ 2007-05-04 22:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Broersma Jr @ 2007-05-04 22:16 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
--- Collin Peters <cadiolis@gmail.com> wrote:
> 2. currval() is guaranteed to be correct within the RULE. This was my
> second question. If I call "INSERT INTO Vschematic_insert(..."
> simultaneously 1000 times is it guaranteed to be correct?
To illistrate using psudo code:
//This will always work:
For i = 1 to 1000 do
INSERT INTO Vschematic VALUES ( .. some unique values .. );
Next i
//These will always fail:
INSERT INTO Vschematic SELECT * FROM A_TABLE LIMIT 1000;
or
INSERT INTO Vschematic VALUES ( unique vals 1),(unique vals 2), ..., (unique vals n);
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-05-04 22:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-03 17:04 Insert into VIEW using RULE. Not possible to use nextval()? Collin Peters <cadiolis@gmail.com>
2007-05-03 17:26 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-03 18:30 ` Collin Peters <cadiolis@gmail.com>
2007-05-03 19:26 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-05-04 21:51 ` Collin Peters <cadiolis@gmail.com>
2007-05-04 22:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox